package sample.lib;

import javafx.collections.ObservableList;

import java.sql.*;

public class db_pg {

    public static final String DB = "oidata_pg";
    public static final String URL = "jdbc:postgresql://localhost:5432/"+DB;
    public static final String USER = "stockdeveloper";
    public static final String PASS = "555555";


    /*############################################################
        DB CREATION LOGIC
    ############################################################*/

    public void createDB(String dbname) throws SQLException{
        Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "stockdeveloper", "123456");
        Statement statement = c.createStatement();
        ResultSet rs = statement.executeQuery("SELECT datname FROM pg_catalog.pg_database WHERE datname = '"+dbname+"';");
        if(!rs.next())
        {
            statement.executeUpdate("CREATE DATABASE "+dbname);
        }
    }

    /*############################################################
        DB DROP LOGIC
    ############################################################*/

    public void deleteDB() throws SQLException {
        String sql = "DROP TABLE IF EXISTS OptionData";
        try(Connection conn = this.connect();
            PreparedStatement pstmt = conn.prepareStatement(sql))
        {
            pstmt.executeUpdate();
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }
    }

    /*############################################################
        DB CONNECTION LOGIC
    ############################################################*/

    public static Connection getConnection(){
        try{
            return DriverManager.getConnection(URL, USER, PASS);
        } catch (SQLException ex) {
            throw new RuntimeException("Error connecting to the database", ex);
        }
    }

    /*############################################################
        TABLE CREATION LOGIC
    ############################################################*/

    //Table for FnO Master List
    public void createPGTableFnoMaster() throws SQLException {

        String sql = "CREATE TABLE IF NOT EXISTS FnoMaster ("
                + " id BIGSERIAL PRIMARY KEY,"
                + " symcode TEXT NOT NULL,\n"
                + " sym1 TEXT NOT NULL,\n"
                + " sym2 TEXT NOT NULL,\n"
                + " inst TEXT NOT NULL,\n"
                + " fullname TEXT NOT NULL,\n"
                + " status TEXT NOT NULL,\n"
                + " optionType CHAR(2) NOT NULL,"
                + " strikePrice REAL NOT NULL,\n"
                + " expiryDate DATE NOT NULL,\n"
                + " lotsize INT NOT NULL,\n"
                + "underlying REAL NOT NULL\n"
                + ");";

        try(Connection conn = getConnection();
            Statement stmt = conn.createStatement())
        {
            stmt.execute(sql);
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }
    }

    //Table for FnO Data Fetch Log
    public void createPGTableFnoLog() throws SQLException {

        String sql = "CREATE TABLE IF NOT EXISTS FnoLog(\n"
                + " id BIGSERIAL PRIMARY KEY,\n"
                + " lastAttempted TIMESTAMP NOT NULL,\n"
                + " lastUpdated TIMESTAMP NOT NULL\n"
                + ");";

        try(Connection conn = getConnection();
            Statement stmt = conn.createStatement())
        {
            stmt.execute(sql);
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }
    }




    //Table for FnO Data
    public void createPGTableOptionData() throws SQLException {

        String sql = "CREATE TABLE IF NOT EXISTS OptionData ("
                + " id BIGSERIAL PRIMARY KEY,"
                + " optionType CHAR(2) NOT NULL,"
                + " strikePrice REAL NOT NULL,\n"
                + " expiryDate DATE NOT NULL,\n"
                + " OI BIGINT NOT NULL,\n"
                + " OIChng BIGINT NOT NULL,\n"
                + " vol BIGINT NOT NULL,\n"
                + " IV REAL NOT NULL,\n"
                + " LTP REAL NOT NULL,\n"
                + " netChng REAL NOT NULL,\n"
                + " bidQty INTEGER NOT NULL,\n"
                + " bidPrice REAL NOT NULL,\n"
                + " ofrQty INTEGER NOT NULL,\n"
                + "ofrPrice REAL NOT NULL,\n"
                + " lastUpdate TIMESTAMP NOT NULL,\n"
                + "underlying REAL NOT NULL\n"
                + ");";

        try(Connection conn = getConnection();
            Statement stmt = conn.createStatement())
        {
            stmt.execute(sql);
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }
    }

    /*############################################################
                        DATA INSERTION LOGIC
    ############################################################*/

    public void insertToFnoList(ObservableList<fnoList> data) throws SQLException {

        try{
            Connection conn = getConnection();
            String sql ="INSERT INTO FnoList(symcode,sym1,sym2,inst) VALUES(?,?,?,?);";
            conn.setAutoCommit(false);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            int i = 0;
            for (fnoList dat : data) {
                pstmt.setString(1, dat.getSymcode());
                pstmt.setString(2, dat.getSym1());
                pstmt.setString(3, dat.getSym2());
                pstmt.setString(4, dat.getInst());
                pstmt.addBatch();
                i++;
                if (i % 1000 == 0) {
                    pstmt.executeBatch(); // Execute every 200 items.
                }
            }
            pstmt.executeBatch();
            conn.commit();
            pstmt.close();
        }
        catch(Exception e){
            //if(co)
        }
    }


    public void insertToOptionData(ObservableList<OptionData> data) throws SQLException {


        String sql = "INSERT INTO OptionData(optionType,strikePrice,expiryDate,OI,OIChng,vol,IV,LTP,netChng,bidQty,bidPrice,ofrQty,ofrPrice, lastUpdate, underlying) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        Connection conn = getConnection();
        conn.setAutoCommit(false);
        //conn.setTransactionIsolation();
        //SQLiteDatabase database = this.getWritableDatabase();
             PreparedStatement pstmt = conn.prepareStatement(sql);
            int i = 0;
            for (OptionData dat : data) {
                pstmt.setString(1, dat.getOptionType());
                pstmt.setFloat(2, Converter.toFloat(dat.getStrikePrice()));
                pstmt.setString(3, dat.getExpiryDate());
                pstmt.setInt(4, Converter.toInt(dat.getOI()));
                pstmt.setInt(5, Converter.toInt(dat.getOIChng()));
                pstmt.setLong(6, Converter.toLong(dat.getVol()));
                pstmt.setFloat(7, Converter.toFloat(dat.getIV()));
                pstmt.setFloat(8, Converter.toFloat(dat.getLTP()));
                pstmt.setFloat(9, Converter.toFloat(dat.getNetChng()));
                pstmt.setInt(10, Converter.toInt(dat.getBidQty()));
                pstmt.setFloat(11, Converter.toFloat(dat.getBidPrice()));
                pstmt.setInt(12, Converter.toInt(dat.getOfrQty()));
                pstmt.setFloat(13, Converter.toFloat(dat.getOfrPrice()));
                pstmt.setString(14, dat.getLastUpdate());
                pstmt.setFloat(15, Converter.toFloat(dat.getUnderlying()));
                pstmt.addBatch();
                i++;
                if (i % 1000 == 0) {
                    pstmt.executeBatch(); // Execute every 200 items.
                }
            }
            pstmt.executeBatch();
            conn.commit();
            pstmt.close();
    }


    /*############################################################
                    TEST RUN THIS CLASS ALONE
    ############################################################*/


    public void initialize() throws SQLException {
        this.connect();
        //this.createTableOptionData();
        //this.createTableFnoList();
    }

    public void initializePG() throws SQLException{
        db_pg database = new db_pg();
        //database.connect();
        //database.deleteDB();
        //database.initialize();
        database.createDB(DB);
        database.createPGTableOptionData();
        database.createPGTableFnoMaster();
    }


    public static void main(String[] args) throws SQLException {
        db_pg database = new db_pg();
        //database.connect();
        //database.deleteDB();
        //database.initialize();
        //database.createDB(DB);
        database.initializePG();

    }