package rule;

import java.sql.*;
import java.util.Collections;
import java.util.Hashtable;
import java.util.Vector;

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import rule.db.*;

public class TableInstance {

    public TableInstance(Vector c, ISet iSet, int k) throws SQLException {
        int start = DbTools.getTableStartId();

        Connection conn = DBConnection.getConnection();
        Statement stmt = conn.createStatement();
        conn.setAutoCommit(false);

        for (int i = 0; i < c.size(); i++) {
            String type = (String) c.elementAt(i);
            /*
             * stmt.execute("INSERT INTO TBL_TYPES (K, TYPE) VALUES (" + k + ", " +
             * type + ")");
             */
            start = start + i + 1;
            String tblName = "T" + start;
            stmt.execute("CREATE TABLE " + tblName + "(" + type
                    + " INTEGER, id integer)");
            stmt
                    .execute("INSERT INTO TBL_TBLNAMES (ID, K, TABLENAME, TYPESTR) VALUES ("
                            + start
                            + ", "
                            + k
                            + ", '"
                            + tblName
                            + "', '"
                            + type + "')");
            // for (int j = 0; j < iSet.getInstanceNumber(); j++) {
            Vector ids = iSet.getIdByType(type);
            for (int z = 0; z < ids.size(); z++) {
                int id = ((Integer) ids.elementAt(z)).intValue();
                stmt.execute("INSERT INTO " + tblName + "(" + type
                        + ", id) values(" + id + ", 0)");
            }
            // }
        }
        conn.commit();
        stmt.close();
    }

    public TableInstance(InterfaceEPC epc, Vector c, TableInstance tk, int k)
            throws SQLException {
        // get max used serial number
        int start = DbTools.getTableStartId();
        // ---end of get max used serial number

        // print c
        /*
         * System.out
         * .println("=====================================================");
         * System.out.println("k=" + k); for (int h = 0; h < c.size(); h++) {
         * Vector hhh = (Vector) c.elementAt(h);
         * System.out.println(hhh.toString()); } System.out
         * .println("=====================================================");
         */
        // ---end print c
        Connection conn = null;
        Statement stmt = null;
        //conn.setAutoCommit(false);

        for (int i = 0; i < c.size(); i++) {
            conn = DBConnection.getConnection();
            stmt = conn.createStatement();
            conn.setAutoCommit(false);
            Vector types = (Vector) c.elementAt(i);
            // System.out.println("k=" + k + ",types=" + types.toString());
            Collections.sort(types); // sort types by type name
            String typeStr = Rule.vectorToString(types); // typeStr used to
            // get table name
            int tblId = start + i + 1; // get a table serial number
            String tblName = "T" + tblId; // generate a new table name
            StringBuffer sql = new StringBuffer(); // create table sql
            StringBuffer sql2 = new StringBuffer(); // create index sql
            sql.append("CREATE TABLE ").append(tblName).append(" (");
            sql2.append("CREATE UNIQUE INDEX IDX_").append(tblName).append(
                    " ON ").append(tblName).append("(");
            System.out.println("--->types=" + types.toString());
            // use all type as column name
            for (int j = 0; j < k + 1; j++) {
                String type = (String) types.elementAt(j);
                //System.out.println("--->type=" + type + ", j=" + j);
                sql.append(type).append(" INTEGER, ");
                sql2.append(type).append(",");
            }
            sql.append(" ID INTEGER)");
            String sql2Str = sql2.toString();
            sql2Str = sql2Str.substring(0, sql2Str.length() - 1) + ")";
            stmt.execute(sql.toString());
            stmt.execute(sql2Str);
            // insert new table name into the table names table
            stmt
                    .execute("INSERT INTO TBL_TBLNAMES (ID, K, TABLENAME, TYPESTR) VALUES ("
                            + tblId
                            + ", "
                            + (k + 1)
                            + ", '"
                            + tblName
                            + "', '"
                            + typeStr + "')");
            // for (int j = 0; j < iSet.getInstanceNumber(); j++) {
            // wait for add
            if (k == 1) {
                String type1 = (String) types.elementAt(0);
                String type2 = (String) types.elementAt(1);
                String sql1 = "SELECT TABLENAME FROM TBL_TBLNAMES WHERE TYPESTR = '"
                        + type1 + "'";
                // System.out.println("sql1=" + sql1);
                ResultSet nmRslt1 = stmt.executeQuery(sql1);

                String rTable1 = "";
                if (nmRslt1.next()) {
                    rTable1 = nmRslt1.getString("TABLENAME");
                    // System.out.println("==>rTable1=" + rTable1);
                }
                nmRslt1.close();
                String rTable2 = "";
                String sqltbl2 = "SELECT TABLENAME FROM TBL_TBLNAMES WHERE TYPESTR = '"
                        + type2 + "'";
                // System.out.println(sqltbl2);
                ResultSet nmRslt2 = stmt.executeQuery(sqltbl2);
                if (nmRslt2.next()) {
                    rTable2 = nmRslt2.getString("TABLENAME");
                }
                nmRslt2.close();
                // System.out.println("==>rTable2=" + rTable2);
                String sql3 = "SELECT " + type1 + " FROM " + rTable1;
                // System.out.println("sql3=" + sql3);
                ResultSet rst1 = stmt.executeQuery(sql3);
                Vector values1 = new Vector();
                while (rst1.next()) {
                    int value1 = rst1.getInt(type1);
                    values1.add(new Integer(value1));
                }
                rst1.close();
                String sql4 = "SELECT " + type2 + " FROM " + rTable2;
                // System.out.println(sql4);
                ResultSet rst2 = stmt.executeQuery(sql4);
                Vector values2 = new Vector();
                while (rst2.next()) {
                    int value2 = rst2.getInt(type2);
                    values2.add(new Integer(value2));
                }
                rst2.close();
                for (int m = 0; m < values1.size(); m++) {
                    int value1 = ((Integer) values1.elementAt(m)).intValue();
                    for (int n = 0; n < values2.size(); n++) {
                        int value2 = ((Integer) values2.elementAt(n))
                                .intValue();
                        stmt.execute("INSERT INTO " + tblName + "(" + type1
                                + ", " + type2 + ")" + " VALUES (" + value1
                                + ", " + value2 + ")");
                    }
                }
                stmt.close();
                conn.commit();
                conn.close();
            } else {
                //genInstance(types, stmt, tblName); // the old method
                stmt.close();
                conn.commit();
                conn.close();
                genInstance2(types, tblName); // the new method
            }
        }
        checkEpc(epc.getEpc(), c, k);
    }

    // types{a, b, c, d}
    // use {a, b, c} and {a, b, d} to generate instance
    //20051007 add connection parameter
    private void genInstance2(Vector types, String tblName) throws SQLException {
        Vector v1 = new Vector();
        Vector v2 = new Vector();
        Vector key = new Vector();
        Vector tempVec = new Vector();
        for (int i = 0; i < types.size(); i++) {
            if (i < types.size() - 2) {
                v1.add(types.elementAt(i));
                v2.add(types.elementAt(i));
                key.add(types.elementAt(i));
            }
            if (i == types.size() - 2) {
                v1.add(types.elementAt(i));
                tempVec.add(types.elementAt(i));
            }
            if (i == types.size() - 1) {
                v2.add(types.elementAt(i));
                tempVec.add(types.elementAt(i));
            }
        }
        Collections.sort(v1);
        Collections.sort(v2);

        String tblName1 = getTableName(v1);
        String tblName2 = getTableName(v2);
        genOneTblDataFromTwo(tblName, tblName1, tblName2, types, key, tempVec);
    }

    private String getTableName(Vector v) throws SQLException {
        Connection conn = DBConnection.getConnection();
        Statement stmt = conn.createStatement();
        //      generatetblNameStr1 "abc"
        String tblNameStr = Rule.vectorToString(v);
        // use tblNameStr1 "abc" to get mapped table name
        ResultSet nmRslt = stmt
                .executeQuery("SELECT TABLENAME FROM TBL_TBLNAMES WHERE TYPESTR = '"
                        + tblNameStr + "'");
        String tableName = "";
        if (nmRslt.next()) {
            tableName = nmRslt.getString("TABLENAME");
        }
        nmRslt.close();
        stmt.close();
        conn.close();
        return tableName;
    }

    //20051007 add connection as parameter
    private void genOneTblDataFromTwo(String tblName, String tblName1,
            String tblName2, Vector types, Vector key, Vector tempVec)
            throws SQLException {

        //
        StringBuffer qSql = new StringBuffer();
        qSql.append("SELECT ");
        StringBuffer where = new StringBuffer();
        where.append(" WHERE ");
        // System.out.println("-------->1");
        for (int l = 0; l < key.size(); l++) {
            qSql.append(tblName1).append(".").append((String) key.elementAt(l));

            where.append(tblName1).append(".")
                    .append((String) key.elementAt(l)).append(" = ").append(
                            tblName2).append(".").append(
                            (String) key.elementAt(l));
            if (l != key.size() - 1) {
                qSql.append(", ");
                where.append(" AND ");
            }
        }
        // System.out.println("-------->2");
        if (key.size() > 0) {
            qSql.append(", ");
        }
        // System.out.println("-------->3");
        for (int z = 0; z < tempVec.size(); z++) {
            qSql.append((String) tempVec.elementAt(z));
            if (z != tempVec.size() - 1) {
                qSql.append(", ");
            }
        }
        // System.out.println("-------->4");
        qSql.append(" FROM ").append(tblName1).append(", ").append(tblName2)
                .append(where.toString());
        System.out.println("qSql=" + qSql.toString());
        //ResultSet results = stmt.executeQuery(qSql.toString());
        //Vector values = new Vector();
        // System.out.println("-------->5");
        /*
         * while (results.next()) { Hashtable ht = new Hashtable(); for (int s =
         * 0; s < types.size(); s++) { String tempType = (String)
         * types.elementAt(s); int value = results.getInt(tempType);
         * ht.put(tempType, new Integer(value)); } //values.add(ht);
         * insertValue(ht, tblName, types); conn.commit(); }
         */
        Connection conn = DBConnection.getConnection();
        CachedRowSet crs = new CachedRowSetImpl();
        crs.setPageSize(100);
        crs.setCommand(qSql.toString());
        crs.execute(conn);
        //results.close();
        /*while (crs.next()) {
            Hashtable ht = new Hashtable();
            for (int s = 0; s < types.size(); s++) {
                String tempType = (String) types.elementAt(s);
                int value = crs.getInt(tempType);
                ht.put(tempType, new Integer(value));
            }
            insertValue(ht, tblName, types);
        }*/
        while (crs.nextPage()) {
            while (crs.next()) {
                Hashtable ht = new Hashtable();
                for (int s = 0; s < types.size(); s++) {
                    String tempType = (String) types.elementAt(s);
                    int value = crs.getInt(tempType);
                    ht.put(tempType, new Integer(value));
                }
                insertValue(ht, tblName, types);
            }
        }
        crs.close();
        conn.close();
        // System.out.println("-------->6");
        // System.out.println("size=" + values.size());
        //for (int size = 0; size < values.size(); size++) {
        //move content to method insertValue
        //for fix error: java.lang.OutOfMemoryError: Java heap space
        //}
        // System.out.println("-------->7");
    }

    private void insertValue(Hashtable ht, String tblName, Vector types)
            throws SQLException {
        Connection conn = DBConnection.getConnection();
        Statement stmt = conn.createStatement();

        //Hashtable ht = (Hashtable) values.elementAt(size);
        StringBuffer insertStr = new StringBuffer();
        StringBuffer valueStr = new StringBuffer();
        insertStr.append(" INSERT INTO ").append(tblName).append(" (");
        valueStr.append(" VALUES (");
        for (int s = 0; s < types.size(); s++) {
            String tempType = (String) types.elementAt(s);
            insertStr.append(tempType);
            valueStr.append(((Integer) ht.get(tempType)).intValue());
            if (s != types.size() - 1) {
                insertStr.append(", ");
                valueStr.append(", ");
            }
        }
        valueStr.append(")");
        insertStr.append(") ").append(valueStr.toString());
        // System.out.println("insertStr=" + insertStr);
        try {
            stmt.execute(insertStr.toString());
        } catch (Exception e) {
            // do nothing
        }
        conn.commit();
        stmt.close();
        conn.close();
    }

    // types{a, b, c, d}
    // use {a, b, c}, {a, b, d}, {b, c, d}, {a, c, d} to generate instance
    private void genInstance(Vector types, Statement stmt, String tblName,
            Connection conn) throws SQLException {

        // get out all sub set, for example:
        // {a, b, c, d} => {c,d}, {b,d}, {b,c}, {a,d}, {a,c}, {a,b}
        Vector keys = getSubSet(types);
        // System.out.println("@@@keys.size=" + keys.size());
        for (int m = 0; m < keys.size(); m++) {
            // System.out.println("2@@@keys.size=" + keys.size() + ",
            // m="
            // + m);
            Vector key = (Vector) keys.elementAt(m); // such as {a,b}
            Vector tempVec = new Vector();
            // use {a, b, c, d} init tempVec
            tempVec = initVector(types);
            // System.out.println("types=" + types.toString() + ", key="
            // + key.toString());
            // remove {a, b} from {a, b, c, d}
            for (int n = 0; n < key.size(); n++) {
                tempVec.remove(key.elementAt(n));
            }
            // now tempVec is {c, d}
            Vector v1 = initVector(key); // use {a, b} init v1
            v1.add(tempVec.elementAt(0)); // add "c" of {c, d} into v1
            Collections.sort(v1); // sort v1 {a, b, c}
            Vector v2 = initVector(key); // use {a, b} init v2
            v2.add(tempVec.elementAt(1)); // add "d" of {c, d} into v2
            Collections.sort(v2); // sor v2 {a, b, d}
            String tblName1 = getTableName(v1);
            String tblName2 = getTableName(v2);
            genOneTblDataFromTwo(tblName, tblName1, tblName2, types, key,
                    tempVec);
            conn.commit();
        }

    }

    // {a, b, c} => {a}, {b}, {c}
    // {a, b, c, d} => {c,d}, {b,d}, {b,c}, {a,d}, {a,c}, {a,b}
    private Vector getSubSet(Vector inSet) {
        Vector ret = new Vector();
        Vector subVec = new Vector();
        for (int i = 0; i < inSet.size(); i++) {
            for (int j = i + 1; j < inSet.size(); j++) {
                Vector tempVec = initVector(inSet);
                String type1 = (String) tempVec.elementAt(i);
                String type2 = (String) tempVec.elementAt(j);
                tempVec.remove(type1);
                tempVec.remove(type2);
                subVec = initVector(tempVec);
                ret.add(subVec);
            }
        }
        return ret;
    }

    // init a vector use the input vector and return it
    private Vector initVector(Vector inSet) {
        Vector ret = new Vector();
        for (int i = 0; i < inSet.size(); i++) {
            ret.add(i, inSet.elementAt(i));
        }
        return ret;
    }

    private void checkEpc(Vector epc, Vector c, int k) throws SQLException {
        if ((k + 1) < 2) {
            return;
        }
        Connection conn = DBConnection.getConnection();
        Statement stmt = conn.createStatement();

        for (int i = 0; i < c.size(); i++) {
            Vector types = (Vector) c.elementAt(i);
            Collections.sort(types);
            String typeStr = Rule.vectorToString(types);
            ResultSet rslt = stmt
                    .executeQuery("SELECT TABLENAME FROM TBL_TBLNAMES WHERE TYPESTR = '"
                            + typeStr + "'");
            String tableName = "";
            if (rslt.next()) {
                tableName = rslt.getString("TABLENAME");
            }
            rslt.close();
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT ");
            for (int j = 0; j < types.size(); j++) {
                sql.append((String) types.elementAt(j));
                if (j != (types.size() - 1)) {
                    sql.append(", ");
                }
            }
            sql.append(" FROM ").append(tableName);
            // System.out.println(sql.toString());
            ResultSet results = stmt.executeQuery(sql.toString());
            Vector values = new Vector();
            while (results.next()) {
                Hashtable ht = new Hashtable();
                for (int z = 0; z < types.size(); z++) {
                    String type = (String) types.elementAt(z);
                    int value = results.getInt(type);
                    ht.put(type, new Integer(value));
                }
                values.add(ht);
            }
            results.close();
            for (int size = 0; size < values.size(); size++) {
                Hashtable ht = (Hashtable) values.elementAt(size);
                boolean bl = false;
                for (int l = 0; l < epc.size(); l++) {
                    Vector elements = (Vector) epc.elementAt(l);
                    boolean fit = true;
                    for (int m = 0; m < types.size(); m++) {
                        String type = (String) types.elementAt(m);
                        int id = ((Integer) ht.get(type)).intValue();
                        boolean exist = false;
                        for (int n = 0; n < elements.size(); n++) {
                            IInstance inst = (IInstance) elements.elementAt(n);
                            if (inst.getType().equalsIgnoreCase(type)
                                    && inst.getId() == id) {
                                exist = true;
                                break;
                            }
                        }
                        if (exist) {
                            continue;
                        } else {
                            fit = false;
                            break;
                        }
                    }
                    if (fit) {
                        bl = true;
                        break;
                    } else {
                        continue;
                    }
                }
                if (!bl) {
                    StringBuffer delSql = new StringBuffer();
                    delSql.append("DELETE FROM ").append(tableName).append(
                            " WHERE ");
                    for (int z = 0; z < types.size(); z++) {
                        String type = (String) types.elementAt(z);
                        int value = ((Integer) ht.get(type)).intValue();
                        delSql.append(type).append(" = ").append(value);
                        if (z != (types.size() - 1)) {
                            delSql.append(" AND ");
                        }
                    }
                    stmt.execute(delSql.toString());
                }
            }
        }
        conn.commit();
        stmt.close();
        conn.close();
    }
}