/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package kanjiflash;

import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Alan
 */
public class DBSetup {
    final boolean DEBUG_PRINT = false;
    Connection C;
    String strKanji;
    String strOnYom;
    String strKunYom;
    String strEngDef;
    String strErrorList;
    String strScore;
    String strHistoryList;
    String strNonHistoryView;
    ArrayList<Kanji> KTab;
    public DBSetup(Connection c, ArrayList<Kanji> kTab){
        C = c;
        KTab = kTab;
        strKanji = "CREATE TABLE KANJI (" +
//                        "INDX INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1), "+
                        "UNICODE INT, "+
                        "JIS_CHAR VARCHAR(2), "+
                        "GRADE_LEVEL INT, "+
                        "PRIMARY KEY (UNICODE))";
         strOnYom = "CREATE TABLE ON_YOMI ("+
                        "INDX INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1), "+
                        "UNICODE INT, "+
                        "ON_NUM SMALLINT, "+  // The number of the On Yomi expression in the list
//                        "ON_INDEX SMALLINT, "+ // The number of the character position in the expression
//                        "ON_VALUE INT, "+ // The actual Unicode value for this character
                        "ON_VALUE VARCHAR(40), "+
                        "PRIMARY KEY (INDX))";
         strKunYom = "CREATE TABLE KUN_YOMI ("+
                        "INDX INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1), "+
                        "UNICODE INT, "+
                        "KUN_NUM SMALLINT, "+  // The number of the Kun Yomi expression in the list
//                        "KUN_INDEX SMALLINT, "+ // The number of the character position in the expression
//                        "KUN_VALUE INT, "+ // The actual Unicode value for this character
                        "KUN_VALUE VARCHAR(40), "+
                        "PRIMARY KEY (INDX))";         
        strEngDef = "CREATE TABLE ENGLISH_DEF (" +
                        "INDX INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1), "+
                        "UNICODE INT, "+
                        "ENG_NUM INT, "+ // The number of the English definition in the list
                        "DEFINITION VARCHAR(80), "+ // The definition string
                        "PRIMARY KEY (INDX))";
        strErrorList = "CREATE TABLE ERROR_LIST(" +
                        "INDX INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1), "+
                        "UNICODE INT, "+
                        "ERR_COUNT INT, "+ // The number of times user made an error on this particular Kanji
                        "PRIMARY KEY (INDX))"; 
        strHistoryList = "CREATE TABLE HISTORY_LIST(" +
                        "INDX INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1), "+
                        "UNICODE INT, "+
                        "DATE_USED VARCHAR(10), "+ // The date this Kanji was used
                        "PRIMARY KEY (INDX))";    
        strScore = "CREATE TABLE SCORES ("+
                   "INDX INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1), "+ 
                   "SCORE VARCHAR(5), "+
                   "PRIMARY KEY (INDX))";
        strNonHistoryView = "create view NONHISTORY_VIEW (UNICODE, JIS_CHAR, GRADE_LEVEL) "+
                            "AS SELECT UNICODE, JIS_CHAR, GRADE_LEVEL FROM KANJI "+
                            "WHERE UNICODE NOT IN (SELECT UNICODE FROM HISTORY_LIST)";
    }
  
    public boolean setupDBTables(){
        if ((KTab == null) || (KTab.size() == 0)){
            return false;
        }    
        Statement statement = null;
        try {
            statement = C.createStatement();
            statement.execute(strKanji);
        } catch (SQLException ex) {
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                ex.printStackTrace();
            return false;
        }
        if (DEBUG_PRINT)
            System.out.print("Kanji table set up\n");
        try {
            statement = C.createStatement();
            statement.execute(strOnYom);
        } catch (SQLException ex) {
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                ex.printStackTrace();
            return false;
        }
        if (DEBUG_PRINT)
            System.out.print("On Yomi table set up\n");
        try {
            statement = C.createStatement();
            statement.execute(strKunYom);
        } catch (SQLException ex) {
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                ex.printStackTrace();
            return false;
        }
        if (DEBUG_PRINT)
            System.out.print("Kun Yomi table set up\n");
        try {
            statement = C.createStatement();
            statement.execute(strEngDef);
        } catch (SQLException ex) {
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                ex.printStackTrace();
            return false;
        }
        if (DEBUG_PRINT)
            System.out.print("English Definitions table set up\n");
        try {
            statement = C.createStatement();
            statement.execute(strErrorList);
        } catch (SQLException ex) {
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                ex.printStackTrace();
            return false;
        }
        if (DEBUG_PRINT)
            System.out.print("Error List table set up\n"); 
        try {
            statement = C.createStatement();
            statement.execute(strHistoryList);
        } catch (SQLException ex) {
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                ex.printStackTrace();
            return false;
        }
        if (DEBUG_PRINT)
            System.out.print("History List table set up\n");
        try {
            statement = C.createStatement();
            statement.execute(strScore);
        } catch (SQLException ex) {
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                ex.printStackTrace();
            return false;
        }
        if (DEBUG_PRINT)
            System.out.print("Score table set up\n");   
        try {
            statement = C.createStatement();
            statement.execute(strNonHistoryView);
        } catch (SQLException ex) {
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                ex.printStackTrace();
            return false;
        }
        if (DEBUG_PRINT)
            System.out.print("Non-history view set up\n");         
/*
 *   Put the data from the Kanji table into the database so we don't have to 
 *   parse the Kanjidic file every time we run the program
 */
        String setupKJ =  "INSERT INTO KANJI (UNICODE, JIS_CHAR, GRADE_LEVEL) "+
                        "VALUES (?, ?, ?)";
        String setupON = "INSERT INTO ON_YOMI (UNICODE, "+
                        "ON_NUM, ON_VALUE) "+
                        "VALUES (?, ?, ?)";
        String setupKUN = "INSERT INTO KUN_YOMI (UNICODE, "+
                        "KUN_NUM, KUN_VALUE) "+
                        "VALUES (?, ?, ?)";   
        String setupENG = "INSERT INTO ENGLISH_DEF (UNICODE, ENG_NUM, DEFINITION) "+
                          "VALUES (?, ?, ?)";
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        PreparedStatement pst3 = null;
        PreparedStatement pst4 = null;
        try {
            pst1 = C.prepareStatement(setupKJ);
            pst2 = C.prepareStatement(setupON);
            pst3 = C.prepareStatement(setupKUN);
            pst4 = C.prepareStatement(setupENG);            
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }        
        for (Kanji kj : KTab){
            try {
                pst1.clearParameters();
                pst1.setInt(1, kj.unicode);
                pst1.setString(2, kj.JISChar);
                pst1.setInt(3, kj.gradeLevel);
                pst1.executeUpdate();
            } catch (SQLException sq){
                if (kanjiflash.KanjiFlash.STACK_TRACE)
                    sq.printStackTrace();                
            }
            int onNum = 0;
            for (String str : kj.onVal){
                try {
                    pst2.clearParameters();
                    pst2.setInt(1, kj.unicode);
                    pst2.setShort(2, (short)onNum);
                    pst2.setString(3, str);
                    pst2.executeUpdate();
                } catch (SQLException sq){
                    if (kanjiflash.KanjiFlash.STACK_TRACE)
                        sq.printStackTrace();                         
                } 
                onNum++;                   
 
            }
            int kunNum = 0;
            for (String str : kj.kunVal){
                    try {
                        pst3.clearParameters();
                        pst3.setInt(1, kj.unicode);
                        pst3.setShort(2, (short)kunNum);
                        pst3.setString(3, str);
                        pst3.executeUpdate();
                    } catch (SQLException sq){
                        if (kanjiflash.KanjiFlash.STACK_TRACE)
                            sq.printStackTrace();                         
                    } 
                    kunNum++;                   
            }            
            int engNum = 0;
            for (String  str : kj.englishDef){
                try {
                    pst4.clearParameters();
                    pst4.setInt(1, kj.unicode);
                    pst4.setShort(2, (short)engNum);
                    pst4.setString(3, str);
                    pst4.executeUpdate();
                } catch (SQLException sq){
                    if (kanjiflash.KanjiFlash.STACK_TRACE)
                        sq.printStackTrace();                         
                } 
                engNum++;                   
            }            
        }
        return true;
    }

}
