package mainapp;
import com.mysql.jdbc.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Map;

/**
 *
 * @author pc
 */
public class DB {
    private static DB instance = null;
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/oliveoil?useUnicode=yes&characterEncoding=UTF-8";

    //  Database credentials
    static final String USER = "root";
    static final String PASS = "";
    private static Connection conn = null;
    private static Statement stmt = null;

    protected DB(){}

    public static DB getInstance(){
        if(instance == null) {
            instance = new DB();
        }
        return instance;
    }

    public static void deleteRow(String table, String where) throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(DB_URL, USER, PASS);
        stmt = (Statement) conn.createStatement();
        String sql = "DELETE FROM `"+table+"` WHERE "+where;
        //System.out.println(sql);
        if (stmt.executeUpdate(sql) == 0) {
            throw new SQLException("Deleting failed, no rows affected.");
        }
    }

    public static int insertRow(String table, Map<String,String> fields) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(DB_URL, USER, PASS);
        stmt = (Statement) conn.createStatement();
        Iterator it = fields.entrySet().iterator();
        String keys = "";
        String values = "";
        while (it.hasNext()) {
            Map.Entry pair = (Map.Entry)it.next();
            if (keys.length() == 0) keys = keys+pair.getKey();
            else keys = keys+","+pair.getKey();

            if (values.length() == 0) {
                if (pair.getValue() != null) values = "\""+values+pair.getValue()+"\"";
                else values = "NULL";
            }
            else {
                if (pair.getValue() != null) values = values+",\""+pair.getValue()+"\"";
                else values = ", NULL";
            }
            //System.out.println(pair.getKey() + " = " + pair.getValue());
            it.remove(); // avoids a ConcurrentModificationException
        }
        String sql = "INSERT INTO "+table+" ("+keys+") VALUES ("+values+")";
        //System.out.println("--->"+sql);
        // insert
        if (stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS) == 0) {
            throw new SQLException("Insertion failed, no rows affected.");
        }
        // get insert generated id
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        return rs.getInt(1);
    }
   
    
    public static ResultSet execSelectSql(String sql) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(DB_URL, USER, PASS);

        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        return rs;
    }
    
    public static void execUpdateSql(String sql) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(DB_URL, USER, PASS);

        stmt = (Statement) conn.createStatement();
        //System.out.println("<<<<<<<<<<<<<<<<-- "+sql+" -- >>>>>>>>>>>>>>");
        stmt.executeUpdate(sql);
    }
    
   public static ResultSet selectRows(String from, String cols, String where, String order, String limit) throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = (Statement) conn.createStatement();
    String sql = "SELECT "+cols+" FROM `"+from+"`";
    if (where!=null)
        sql += " WHERE "+where;
    if (order!=null)
        sql += " ORDER BY "+order;
    if (limit!=null)
        sql += " LIMIT "+limit;
    System.out.println(sql);
    ResultSet rs = stmt.executeQuery(sql);
    /*while(rs.next()){
        int id  = rs.getInt("id");
        System.out.println(id);
    }*/
    return rs;
   }
}
