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

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import javafx.beans.value.ChangeListener;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.concurrent.Task;

/**
 *
 * @author Alan
 */
public class DBAccess {
    private Connection C;
    Calendar CAL;
    KanjiFlashController KFC;
    public DBAccess(Connection conn, KanjiFlashController kfc){
        C = conn;
        CAL = GregorianCalendar.getInstance();  
        KFC = kfc;
    }
    public ArrayList<Kanji> getKanjiSampleByMaxGrade(int numKanji, int maxGrade, boolean useLower){ // selects sample up to max Grade Level
        String getKanji2;
        if (useLower){
            getKanji2 = "SELECT * FROM NONHISTORY_VIEW WHERE GRADE_LEVEL <= ?";
        }
        else {
            getKanji2 = "SELECT * FROM NONHISTORY_VIEW WHERE GRADE_LEVEL = ?";
        }
        String getOn = "SELECT ON_VALUE FROM ON_YOMI WHERE UNICODE = ?";
        String getKun = "SELECT KUN_VALUE FROM KUN_YOMI WHERE UNICODE = ?";
        String getEng = "SELECT DEFINITION FROM ENGLISH_DEF WHERE UNICODE = ?";
        PreparedStatement pst;
        ResultSet rs = null;
        ArrayList<Kanji> testList = new ArrayList<>();
        ArrayList<Kanji> sample = new ArrayList<>();
        try {  // Get all possible Kanji for this grade level
            pst = C.prepareStatement(getKanji2, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pst.setInt(1, maxGrade);
            rs = pst.executeQuery();
//            KFC.updateProgress(.1);
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                while (!rs.isAfterLast()){
                    sample.add(new Kanji(rs.getInt("UNICODE"), rs.getString("JIS_CHAR"), rs.getInt("GRADE_LEVEL")));                      
                    rs.next();
                }
            }
//            KFC.updateProgress(.2);
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        } 
        // Randomize the remaining list
        Collections.shuffle(sample);
//        KFC.updateProgress(.3);
        // get the top numKanji elements of the list
        Iterator it = sample.iterator();
        int testCnt = 0;
        for (Kanji kj : sample){
            testList.add(kj);
            testCnt++;
            if (testCnt >= numKanji)
                break;
        }
            
        sample.clear();
        // Fill in the on, kun, english data tables for each kanji
        for (Kanji kj : testList){
            try {
                pst = C.prepareStatement(getOn, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                pst.setInt(1, kj.unicode);
                rs = pst.executeQuery();
                if ((rs != null) && rs.isBeforeFirst()){
                    rs.first();
                    while (!rs.isAfterLast()){
                        kj.onVal.add(rs.getString("ON_VALUE"));
                        rs.next();
                    }
                }
//                KFC.updateProgress(.4);
            } catch (SQLException sq){
                if (kanjiflash.KanjiFlash.STACK_TRACE)
                    sq.printStackTrace();
            }
            try {
                pst = C.prepareStatement(getKun, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                pst.setInt(1, kj.unicode);
                rs = pst.executeQuery();
                if ((rs != null) && rs.isBeforeFirst()){
                    rs.first();
                    while (!rs.isAfterLast()){
                        kj.kunVal.add(rs.getString("KUN_VALUE"));
                        rs.next();
                    }
                }
//                KFC.updateProgress(.6);
            } catch (SQLException sq){
                if (kanjiflash.KanjiFlash.STACK_TRACE)
                    sq.printStackTrace();
            } 
            try {
                pst = C.prepareStatement(getEng, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                pst.setInt(1, kj.unicode);
                rs = pst.executeQuery();
                if ((rs != null) && rs.isBeforeFirst()){
                    rs.first();
                    while (!rs.isAfterLast()){
                        kj.englishDef.add(rs.getString("DEFINITION"));
                        rs.next();
                    }
                }
//                KFC.updateProgress(.8);
            } catch (SQLException sq){
                if (kanjiflash.KanjiFlash.STACK_TRACE)
                    sq.printStackTrace();
            }  
        }
//        addHistoryToList(testList);
//        KFC.updateProgress(1.0);
        return testList;
    }

    public ArrayList<Kanji> getKanjiSampleFromErrList(int testSize){
        ArrayList<KanjiError> errList = getErrorList();
        Collections.shuffle(errList);
        ArrayList<Kanji> sample = new ArrayList<Kanji>();
        int testCnt = 0;
        for (KanjiError kE : errList){
            sample.add(getKanjiByUnicode(kE.unicode));
            testCnt++;
            if (testCnt >= testSize)
                break;
        }
        return sample;
    }
    public Kanji getKanjiByUnicode(int unicode){ // Gets a single Kanji by Unicode value
        Kanji KJ = new Kanji(unicode);
        String getGrade = "SELECT JIS_CHAR, GRADE_LEVEL FROM KANJI WHERE UNICODE = ?";
        String getOn = "SELECT ON_VALUE FROM ON_YOMI WHERE UNICODE = ?";
        String getKun = "SELECT KUN_VALUE FROM KUN_YOMI WHERE UNICODE = ?";
        String getEng = "SELECT DEFINITION FROM ENGLISH_DEF WHERE UNICODE = ?";
        PreparedStatement pst;
        ResultSet rs = null;        
        try {
            pst = C.prepareStatement(getGrade, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pst.setInt(1, unicode);
            rs = pst.executeQuery();
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                KJ.gradeLevel = rs.getInt("GRADE_LEVEL");
                KJ.JISChar = rs.getString("JIS_CHAR");
            }
            else {
                KJ.gradeLevel = -1;
                KJ.JISChar = "";
            }    
            pst = C.prepareStatement(getOn, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pst.clearParameters();
            pst.setInt(1, unicode);
            rs = pst.executeQuery();
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                while (!rs.isAfterLast()){
                    KJ.onVal.add(rs.getString("ON_VALUE"));
                    rs.next();
                }
            }
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }
        try {
            pst = C.prepareStatement(getKun, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pst.setInt(1, unicode);
            rs = pst.executeQuery();
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                while (!rs.isAfterLast()){
                    KJ.kunVal.add(rs.getString("KUN_VALUE"));
                    rs.next();
                }
            }
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        } 
        try {
            pst = C.prepareStatement(getEng, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pst.setInt(1, unicode);
            rs = pst.executeQuery();
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                while (!rs.isAfterLast()){
                    KJ.englishDef.add(rs.getString("DEFINITION"));
                    rs.next();
                }
            }
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        } 
        return KJ;
    }
    public ArrayList<KanjiError> getErrorList(){
        ArrayList<KanjiError> errList = new ArrayList<KanjiError>();
        String getErr = "SELECT * FROM ERROR_LIST";
        PreparedStatement pst;
        ResultSet rs = null;        
        try {
            pst = C.prepareStatement(getErr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery();
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                while (!rs.isAfterLast()){
                   errList.add(new KanjiError(rs.getInt("UNICODE"), rs.getInt("ERR_COUNT")));
                   rs.next();
                }
            }
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        } 
        return errList;
    }
    public ArrayList<KanjiHistory> getHistoryList(){
        ArrayList<KanjiHistory> kHis =  new ArrayList<KanjiHistory>();
        String getHis = "SELECT * FROM HISTORY_LIST";
        PreparedStatement pst;
        ResultSet rs = null;        
        try {
            pst = C.prepareStatement(getHis, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery();
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                while (!rs.isAfterLast()){
                   kHis.add(new KanjiHistory(rs.getInt("UNICODE"), rs.getString("DATE_USED")));
                   rs.next();
                }
            }
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        } 
        return kHis;        
    }
    public void clearErrorList(){
        String getHis = "DELETE FROM ERROR_LIST";
        PreparedStatement pst;
        ResultSet rs = null;        
        try {
            pst = C.prepareStatement(getHis);
            int count = pst.executeUpdate();
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }        
    }
    /*
     *  Delete a Kanji from the error list
     */
    public void removeError(Kanji kj){
        String delKan = "DELETE FROM ERROR_LIST WHERE UNICODE = ?";
        PreparedStatement pst;
        ResultSet rs = null;        
        try {
            pst = C.prepareStatement(delKan);
            pst.setInt(1, kj.unicode);
            int count = pst.executeUpdate();
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }        
    }
    public void clearHistoryList(){
        String getHis = "DELETE FROM HISTORY_LIST";
        PreparedStatement pst;
        ResultSet rs = null;        
        try {
            pst = C.prepareStatement(getHis);
            int count = pst.executeUpdate();
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }        
    }
    public void clearHistoryListAfter(GregorianCalendar cal){
        
    }
    public void addHistoryToList(ArrayList<Kanji> kList){
        String addHis = "INSERT INTO HISTORY_LIST (UNICODE, DATE_USED) VALUES (?, ?)";
        Calendar cal = new GregorianCalendar().getInstance();
        PreparedStatement pst;
        try {
            pst = C.prepareStatement(addHis);
            for (Kanji kj : kList){
                KanjiHistory kHis = new KanjiHistory(kj.unicode, cal.get(Calendar.YEAR),
                                    cal.get(Calendar.MONTH), cal.get(Calendar.DAY_OF_MONTH));
                pst.clearParameters();
                pst.setInt(1, kHis.unicode);
                pst.setString(2, kHis.toString());
                pst.executeUpdate();
            }
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }
    }
    public void addItemToHistory(Kanji kj){
        String addHis = "INSERT INTO HISTORY_LIST (UNICODE, DATE_USED) VALUES (?, ?)";
        PreparedStatement pst;
        try {
            pst = C.prepareStatement(addHis);
            pst.clearParameters();
            pst.setInt(1, kj.unicode);
            pst.setString(2, String.format("%d/%d/%d", CAL.get(Calendar.MONTH),
                CAL.get(Calendar.DAY_OF_MONTH), CAL.get(Calendar.YEAR)));
            pst.executeUpdate();
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }
    }    
    public void addErrorsToList(ArrayList<KanjiError> kanErr){
        String findErr = "SELECT ERR_COUNT FROM ERROR_LIST WHERE UNICODE = ?";
        String upErr = "UPDATE ERROR_LIST SET ERR_COUNT = ? WHERE UNICODE = ?";
        String addErr = "INSERT INTO ERROR_LIST (UNICODE, ERR_COUNT) VALUES (?, ?)";
        PreparedStatement pst;
        ResultSet rs = null;
        boolean foundErr = false;
        int errCount = 0;
        for (KanjiError kE : kanErr){
            foundErr = false;
            try {
                pst = C.prepareStatement(findErr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 
                pst.setInt(1, kE.unicode);
                rs = pst.executeQuery();
                if ((rs != null) && rs.isBeforeFirst()){
                    rs.first();
                    foundErr = true;
                    errCount = rs.getInt("ERR_COUNT");
                }
                if (foundErr){ // Update the existing error count
                    pst = C.prepareStatement(upErr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    pst.clearParameters();
                    pst.setInt(1, (errCount+1));
                    pst.setInt(2, kE.unicode);
                    pst.executeUpdate();
                }
                else { // Add a new error item to the list
                    pst = C.prepareStatement(addErr);
                    pst.clearParameters();
                    pst.setInt(1, kE.unicode);
                    pst.setInt(2, 1);
                    int count = pst.executeUpdate();
                    
                }
            } catch (SQLException sq){
                if (kanjiflash.KanjiFlash.STACK_TRACE)
                    sq.printStackTrace();
            }
        }
    }
    public int[][] getGradeLevelCounts(){
        int[][] gradeLevels = new int[11][2];
        String getGrades = "SELECT GRADE_LEVEL, COUNT(UNICODE) AS KTOTAL FROM KANJI "+
                            "GROUP BY GRADE_LEVEL";
        PreparedStatement pst;
        ResultSet rs = null;
        try {
            pst = C.prepareStatement(getGrades, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery();
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                int k = 0;
                while (!rs.isAfterLast()){
                    int grade = rs.getInt("GRADE_LEVEL");
                    int cnt = rs.getInt("KTOTAL");
//                    System.out.println("grade = "+grade+" ktotal = "+cnt);
                    gradeLevels[k][0] = grade;
                    gradeLevels[k][1] = cnt;
                    rs.next();
                    k++;
                }
            }
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }
        return gradeLevels;
    }
    public ObservableList<String> getScores(){
        ArrayList<String> scores = new ArrayList<String>();
        String getScores = "SELECT INDX, SCORE FROM SCORES "+
                            "ORDER BY INDX";
        PreparedStatement pst;
        ResultSet rs = null;
        try {
            pst = C.prepareStatement(getScores, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery();
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                while (!rs.isAfterLast()){
                    scores.add(rs.getString("SCORE"));
                    rs.next();
                }
            }
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }
        ObservableList<String> scoresList = FXCollections.observableList(scores);
        return scoresList;       
    }
    public void addScoreToList(String newScore){
        String addScore = "INSERT INTO SCORES (SCORE) VALUES (?)";
        PreparedStatement pst;
        try {
            pst = C.prepareStatement(addScore);
            pst.clearParameters();
            pst.setString(1, newScore);
            pst.executeUpdate();
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }
    }
    public void clearScoreList(){
        String addScore = "DELETE FROM SCORES";
        PreparedStatement pst;
        try {
            pst = C.prepareStatement(addScore);
            pst.clearParameters();
            pst.executeUpdate();
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        }
    } 
    public void printKanjiHistory(){
        ArrayList<KanjiHistory> kjh = new ArrayList<KanjiHistory>();
        String getHis = "SELECT * FROM HISTORY_LIST";
        PreparedStatement pst;
        ResultSet rs = null;
        int cnt = 0;
        try {
            pst = C.prepareStatement(getHis, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery();
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                while (!rs.isAfterLast()){
                    cnt++;
                    System.out.println("Unicode = "+rs.getInt("UNICODE")+" Date: "+rs.getString("DATE_USED"));
                    rs.next();
                }
            }
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();            
        }
        System.out.println("Total in history list = "+cnt);
    }
    public int getHistoryCount(){
        String getHis = "SELECT COUNT(UNICODE) AS TCNT FROM HISTORY_LIST";
        PreparedStatement pst;
        ResultSet rs = null;
        int cnt = 0;
        try {
            pst = C.prepareStatement(getHis, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery();
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                cnt = rs.getInt("TCNT");
            }
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();            
        }        
        return cnt;
    }
    public void getProblemKanji(int maxGrade){ // Finds kanji with no readings
        String getKanji2 = "SELECT * FROM KANJI WHERE GRADE_LEVEL = ?";
        String getOn = "SELECT ON_VALUE FROM ON_YOMI WHERE UNICODE = ?";
        String getKun = "SELECT KUN_VALUE FROM KUN_YOMI WHERE UNICODE = ?";
        PreparedStatement pst;
        ResultSet rs = null;
        ArrayList<Kanji> testList = new ArrayList<>();
        ArrayList<Kanji> sample = new ArrayList<>();
        try {  // Get all possible Kanji for this grade level
            pst = C.prepareStatement(getKanji2, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pst.setInt(1, maxGrade);
            rs = pst.executeQuery();
//            KFC.updateProgress(.1);
            if ((rs != null) && rs.isBeforeFirst()){
                rs.first();
                while (!rs.isAfterLast()){
                    sample.add(new Kanji(rs.getInt("UNICODE"), rs.getString("JIS_CHAR"), rs.getInt("GRADE_LEVEL")));                      
                    rs.next();
                }
            }
//            KFC.updateProgress(.2);
        } catch (SQLException sq){
            if (kanjiflash.KanjiFlash.STACK_TRACE)
                sq.printStackTrace();
        } 
        for (Kanji kj : sample){
            testList.add(kj);
        }
        sample.clear();
        // Fill in the on, kun, english data tables for each kanji
        for (Kanji kj : testList){
            try {
                pst = C.prepareStatement(getOn, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                pst.setInt(1, kj.unicode);
                rs = pst.executeQuery();
                if ((rs != null) && rs.isBeforeFirst()){
                    rs.first();
                    while (!rs.isAfterLast()){
                        kj.onVal.add(rs.getString("ON_VALUE"));
                        rs.next();
                    }
                }
//                KFC.updateProgress(.4);
            } catch (SQLException sq){
                if (kanjiflash.KanjiFlash.STACK_TRACE)
                    sq.printStackTrace();
            }
            try {
                pst = C.prepareStatement(getKun, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                pst.setInt(1, kj.unicode);
                rs = pst.executeQuery();
                if ((rs != null) && rs.isBeforeFirst()){
                    rs.first();
                    while (!rs.isAfterLast()){
                        kj.kunVal.add(rs.getString("KUN_VALUE"));
                        rs.next();
                    }
                }
//                KFC.updateProgress(.6);
            } catch (SQLException sq){
                if (kanjiflash.KanjiFlash.STACK_TRACE)
                    sq.printStackTrace();
            } 
        }
        System.out.println("Looking for problem Kanji:");
        for (Kanji kj : testList){
            if ((kj.kunVal.size() == 0) && (kj.onVal.size() == 0))
                System.out.println("Unicode = "+String.format("%x",kj.unicode)+
                        " Character = "+kj.JISChar);
        }
    }    
}
