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

public class DBManager {
    //---------------------------------
    private static final String JDBC_DRIVER = "org.h2.Driver";
    private static final String JDBC_URL    = "jdbc:h2:file:./resources/DB/movieDB";
    private static final String DB_USER     = "root";
    private static final String DB_PASS     = "";
    private static final String QR_T0 =                                 //Create scheme
            "CREATE TABLE TBLMOVIE ("
            +"IDMOV 	INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"FNAME 	VARCHAR(200) NOT NULL,"
            +"FPATH     VARCHAR(200) NOT NULL,"
            +"IMDBURL 	VARCHAR(200) NOT NULL,"
            +"IMDBRAT 	VARCHAR(10)  NOT NULL,"
            +"IMDBMET 	VARCHAR(10)  NOT NULL,"
            +"POSTPATH 	VARCHAR(200) NOT NULL,"
            +"MNAME 	VARCHAR(128) NOT NULL,"
            +"MYEAR 	VARCHAR(10)  NOT NULL,"
            +"MDURATION VARCHAR(20)  NOT NULL,"
            +"MSUMMARY 	CLOB NOT NULL,"
            +"IDDIR 	INTEGER UNSIGNED NOT NULL,"
            +"IDIMDBC 	INTEGER UNSIGNED NOT NULL,"
            +"PRIMARY KEY (IDMOV), UNIQUE KEY (FNAME));"
            //-------------------------
            +"CREATE TABLE TBLGENRE ("
            +"IDNUM 	INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"ANAME 	VARCHAR(45) NOT NULL,"
            +"PRIMARY KEY (IDNUM), UNIQUE KEY (ANAME));"
            //-------------------------
            +"CREATE TABLE TBLCONTENT ("
            +"IDNUM     INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"ANAME 		VARCHAR(45) NOT NULL,"
            +"PRIMARY KEY (IDNUM), UNIQUE KEY (ANAME));"
            //-------------------------
            +"CREATE TABLE TBLLANG ("
            +"IDNUM 	INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"ANAME     VARCHAR(45) NOT NULL,"
            +"PRIMARY KEY (IDNUM), UNIQUE KEY (ANAME));"
            //-------------------------
            +"CREATE TABLE TBLCOUNTRY ("
            +"IDNUM     INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"ANAME 	VARCHAR(45) NOT NULL,"
            +"PRIMARY KEY (IDNUM), UNIQUE KEY (ANAME));"
            //-------------------------
            +"CREATE TABLE TBLDIRECTOR ("
            +"IDNUM     INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"ANAME     VARCHAR(60) NOT NULL,"
            +"PRIMARY KEY (IDNUM), UNIQUE KEY (ANAME));"
            //-------------------------
            +"CREATE TABLE TBLACTOR ("
            +"IDNUM 	INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"ANAME 	VARCHAR(60) NOT NULL,"
            +"PRIMARY KEY (IDNUM), UNIQUE KEY (ANAME));"
            //-------------------------
            +"CREATE TABLE TBLWRITER ("
            +"IDNUM 	INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"ANAME 		VARCHAR(60) NOT NULL,"
            +"PRIMARY KEY (IDNUM), UNIQUE KEY (ANAME));"
            //-------------------------
            +"CREATE TABLE TBLGENREMIX ("
            +"IDNUM     INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"IDMOV     INTEGER UNSIGNED NOT NULL,"
            +"IDATT 	INTEGER UNSIGNED NOT NULL,"
            +"PRIMARY KEY (IDNUM));"
            //-------------------------
            +"CREATE TABLE TBLACTORMIX ("
            +"IDNUM     INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"IDMOV     INTEGER UNSIGNED NOT NULL,"
            +"IDATT     INTEGER UNSIGNED NOT NULL,"
            +"PRIMARY KEY (IDNUM));"
            //-------------------------
            +"CREATE TABLE TBLWRITERMIX ("
            +"IDNUM 	INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"IDMOV 	INTEGER UNSIGNED NOT NULL,"
            +"IDATT 	INTEGER UNSIGNED NOT NULL,"
            +"PRIMARY KEY (IDNUM));"
            //-------------------------
            +"CREATE TABLE TBLCOUNTRYMIX ("
            +"IDNUM 	INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"IDMOV 	INTEGER UNSIGNED NOT NULL,"
            +"IDATT	    INTEGER UNSIGNED NOT NULL,"
            +"PRIMARY KEY (IDNUM));"
            //-------------------------
            +"CREATE TABLE TBLLANGMIX ("
            +"IDNUM 	INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"
            +"IDMOV 	INTEGER UNSIGNED NOT NULL,"
            +"IDATT	    INTEGER UNSIGNED NOT NULL,"
            +"PRIMARY KEY (IDNUM));"
            //-------------------------------------------------------
            +"INSERT INTO TBLGENRE      (ANAME) VALUES ('Empty');"
            +"INSERT INTO TBLCONTENT    (ANAME) VALUES ('Empty');"
            +"INSERT INTO TBLLANG       (ANAME) VALUES ('Empty');"
            +"INSERT INTO TBLCOUNTRY    (ANAME) VALUES ('Empty');"
            +"INSERT INTO TBLDIRECTOR   (ANAME) VALUES ('Empty');"
            +"INSERT INTO TBLACTOR      (ANAME) VALUES ('Empty');"
            +"INSERT INTO TBLWRITER     (ANAME) VALUES ('Empty');";
    private static final String QR_T4 =                                 //Insert movie
            "INSERT INTO TBLMOVIE "                                     //Insert movie
            +"(FNAME, FPATH, IMDBURL, IMDBRAT, IMDBMET, POSTPATH, MNAME, MYEAR, "
            +"MDURATION, MSUMMARY, IDDIR, IDIMDBC) "
            +"VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%d','%d')";
    private static final String QR_T5 =                                 //Select movie
            "SELECT "
            +"M.IMDBURL, M.IMDBRAT, M.IMDBMET, M.POSTPATH, M.MNAME, M.MYEAR, M.MDURATION, M.MSUMMARY, "
            +"D.ANAME AS DIRECTOR, C.ANAME AS IMDBCONT "
            +"FROM TBLMOVIE AS M "
            +"INNER JOIN TBLDIRECTOR    AS D ON (D.IDNUM = M.IDDIR) "
            +"INNER JOIN TBLCONTENT     AS C ON (C.IDNUM = M.IDIMDBC) "
            +"WHERE M.IDMOV = '%d'";
    private static final String QR_T6 =                                 //Select atts
            "SELECT "
            +"A.ANAME "
            +"FROM %s AS M "
            +"INNER JOIN %s AS A ON (A.IDNUM = M.IDATT) "
            +"WHERE M.IDMOV = '%d'";
    //---------------------------------
    private Connection  conn;
    private Statement   stt;
    //---------------------------------
    public DBManager() {
        if(openConnection()) {
            System.out.println("Connected... Searching for DB tables... ");
            try {
                stt.execute("SELECT COUNT(*) FROM TBLMOVIE");
                System.out.println("Ready!");
            } catch (Exception ex) {
                System.out.println("Tables not found... Creating tables");
                if(createDBTables()) System.out.println("OK!");
                else System.out.println("We are having problems opening the DB... Sorry!");
            }
        }
    }
    public void closeDB() {
        try {
            conn.close();
        } catch (Exception ex) {
            System.out.println("Error: "+ex);
        }
    }
    //---------------------------------
    private boolean createDBTables() {
        try {
            stt.execute(QR_T0);
            return true;
        } catch (Exception ex) {
            System.out.println("Error: "+ex);
            return false;
        }
    }
    private boolean openConnection() {
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(JDBC_URL,DB_USER,DB_PASS);
            stt = conn.createStatement();
            return true;
        } catch (Exception ex) {
            System.out.println("Error: "+ex);
            return false;
        }
    }
    //---------------------------------
    private int getMovieID(Movie mov) {
        if((mov != null)&&(mov.getFile() != null)) {
            File    faux = mov.getFile();
            try {
                String query = "SELECT IDMOV FROM TBLMOVIE WHERE FNAME = '"+faux.getName()+"'";
                ResultSet res = stt.executeQuery(query);
                if (res.next()) return res.getInt("IDMOV");
                else return -1;
            } catch (Exception ex) { return -1; }
        } else return -1;
    }
    private int checkReturnID(String table, String value) {
        if(value.equals("")||value.equals(" ")) {  //No data, get 'Empty'
            return 1;
        } else {
            String query;
            try { //Try to insert the data
                query = "INSERT INTO "+table+" (ANAME) VALUES ('"+value+"')";
                stt.execute(query);
            } catch (Exception ex) { }
            //Try to retrieve the ID
            try {
                query = "SELECT  IDNUM FROM "+table+" WHERE ANAME = '"+value+"'";
                ResultSet res = stt.executeQuery(query);
                res.next();
                return res.getInt("IDNUM");
            } catch (Exception ex) {
                return 1;
            }
        }
    }
    private boolean exeQueryNR(String query) {
        try {
            return stt.execute(query);
        } catch (Exception ex) {
            return false;
        }
    }
    //---------------------------------
    private void innerAttribute(ArrayList<String> att, String tbl, String tblmix, int movieid) {
        try {
            if(att != null) {
                int iditem;
                for (String item : att) {
                    item   = item.replace("'","''");
                    iditem = checkReturnID(tbl,item);
                    stt.execute("INSERT INTO "+tblmix+" (IDMOV,IDATT) VALUES ('"+movieid+"','"+iditem+"')");
                }
            }
        } catch (Exception ex) {}
    }
    private ArrayList<String> selectAttribute(String tbl, String tblmix, int movieid) {
        try {
            ArrayList<String> aux = new ArrayList<>();
            ResultSet res = stt.executeQuery("SELECT A.ANAME AS ANAME FROM "+tblmix+" AS M INNER JOIN "+tbl+" AS A ON (A.IDNUM = M.IDATT) WHERE M.IDMOV = '"+movieid+"'");
            while(res.next())
                aux.add(res.getString("ANAME"));
            return aux;
        } catch (Exception ex) {
            return null;
        }
    }
    //---------------------------------
    public Statement getStt() {
        if(conn != null) return stt;
        else return null;
    }
    public boolean isMovie(Movie mov) {
        if(getMovieID(mov) != -1)   return true;
        else                        return false;
    }
    public void setMovie(Movie mov) {
        String query;
        File faux = mov.getFile();
        if(faux != null) {
            //Get Index's to basic table relation
            int idDir = checkReturnID("TBLDIRECTOR", mov.getDirector());
            int idICt = checkReturnID("TBLCONTENT",  mov.getIMDb_ContRat());
            //Load basic info to the DB
            query = String.format(QR_T4,
                    faux.getName().replace("'","''"),
                    faux.getPath().replace("'","''"),
                    mov.getIMDb_url().replace("'","''"),
                    mov.getIMDb_rating().replace("'","''"),
                    mov.getIMDb_metascore().replace("'","''"),
                    mov.getPosterPath().replace("'","''"),
                    mov.getName().replace("'","''"),
                    mov.getYear().replace("'","''"),
                    mov.getDuration().replace("'","''"),
                    mov.getSummary().replace("'","''"),
                    idDir,
                    idICt);
            exeQueryNR(query);
            int idMov = getMovieID(mov);
            mov.setID(idMov);
            //----------
            innerAttribute(mov.getActors(), "TBLACTOR","TBLACTORMIX",idMov);
            innerAttribute(mov.getGenres(), "TBLGENRE","TBLGENREMIX",idMov);
            innerAttribute(mov.getCountry(), "TBLCOUNTRY","TBLCOUNTRYMIX",idMov);
            innerAttribute(mov.getWritters(), "TBLWRITER","TBLWRITERMIX",idMov);
            innerAttribute(mov.getLanguage(), "TBLLANG","TBLLANGMIX",idMov);
        }
    }
    public void getMovie(Movie mov) {
        if(isMovie(mov)) {
            mov.setID(getMovieID(mov));
            try {
                int     iaux;
                String  query;
                ResultSet res;
                //----------
                query = String.format(QR_T5,mov.getID());
                res = stt.executeQuery(query);
                if(res.next()) {
                    iaux = mov.getID();
                    mov.setIMDb_url(res.getString("IMDBURL"));
                    mov.setIMDb_rating(res.getString("IMDBRAT"));
                    mov.setIMDb_metascore(res.getString("IMDBMET"));
                    mov.setPosterPath(res.getString("POSTPATH"));
                    mov.setPoster(mov.getPosterPath());
                    mov.setName(res.getString("MNAME"));
                    mov.setYear(res.getString("MYEAR"));
                    mov.setDuration(res.getString("MDURATION"));
                    mov.setSummary(res.getString("MSUMMARY"));
                    mov.setDirector(res.getString("DIRECTOR"));
                    mov.setContRat(res.getString("IMDBCONT"));
                    mov.setActors(selectAttribute("TBLACTOR","TBLACTORMIX",iaux));
                    mov.setGenres(selectAttribute("TBLGENRE","TBLGENREMIX",iaux));
                    mov.setCountry(selectAttribute("TBLCOUNTRY","TBLCOUNTRYMIX",iaux));
                    mov.setWritters(selectAttribute("TBLWRITER","TBLWRITERMIX",iaux));
                    mov.setLanguage(selectAttribute("TBLLANG","TBLLANGMIX",iaux));
                }
            } catch (Exception ex) {
                System.out.println("Error: "+ex);
            }
        }
    }
}