package database;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import javax.xml.crypto.Data;

import it.polito.latazza.data.Beverage;
import it.polito.latazza.data.BoxPurchase;
import it.polito.latazza.data.Consumption;
import it.polito.latazza.data.DataImpl;
import it.polito.latazza.data.Employee;
import it.polito.latazza.data.Recharge;
import it.polito.latazza.data.Transaction;
import it.polito.latazza.exceptions.BeverageException;
import it.polito.latazza.exceptions.EmployeeException;

public class InterfaceDB {

    private static Connection conn;
    private static String sql;
    private static Statement statement;


     public static void createNewDatabase() {

            String url = "jdbc:sqlite:latazza.db";
             statement = null;

            try (Connection conn = DriverManager.getConnection(url)) {
                if (conn != null) {
                    DatabaseMetaData meta = conn.getMetaData();
                    System.out.println("The driver name is " + meta.getDriverName());
                    System.out.println("A new database has been created.");

                   statement=conn.createStatement();
                    sql = "CREATE TABLE IF NOT EXISTS EMPLOYEE" +
                    "(ID INTEGER NOT NULL PRIMARY KEY,"+
                            "NAME TEXT NOT NULL,"+
                    "SURNAME TEXT NOT NULL,"+
                            "BALANCE INT NOT NULL)";
                    statement.executeUpdate(sql);
                    statement.close();




                    statement=conn.createStatement();
                    sql = "CREATE TABLE IF NOT EXISTS LOGS" +
                            "(ID INT PRIMARY KEY,"+
                            "D STRING NOT NULL,"+
                            "RECHARGE INT NOT NULL,"+
                            "BUY INT NOT NULL,"+
                            "VISITOR INT NOT NULL,"+
                            "CB INT NOT NULL,"+
                            "IDEMP INT NOT NULL,"+
                            "IDBEV INT NOT NULL,"+
                            "AMQU NOT NULL)";
                    statement.executeUpdate(sql);
                    statement.close();

                    statement=conn.createStatement();
                    sql = "CREATE TABLE IF NOT EXISTS LATAZZAACCOUNT" +
                    "(ID INT PRIMARY KEY NOT NULL, BALANCE INT)";
                    statement.executeUpdate(sql);
                    statement.close();

                    statement=conn.createStatement();
                    sql = "CREATE TABLE IF NOT EXISTS BEVERAGE" +
                    "(ID INT PRIMARY KEY,"+
                        "NAME TEXT NOT NULL,"+
                    "PRICE INT NOT NULL,"+
                            "CAPSULES_BOX INT NOT NULL,"+
                    "BOX_PRICE INT NOT NULL,"+
                            "QUANTITY INT NOT NULL)";
                    statement.executeUpdate(sql);
                    statement.close();





                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

    public static void connect() {
        conn = null;
        try {
            // db parameters
            String url = "jdbc:sqlite:latazza.db";
            // create a connection to the database
            conn = DriverManager.getConnection(url);

            System.out.println("Connection to SQLite has been established.");

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } /*finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }*/
    }

    public static List<Employee> getEmployees()
    {
        List<Employee> employees = new ArrayList<Employee>();

        try
        {
            statement=conn.createStatement();
            sql="SELECT * FROM EMPLOYEE";
            ResultSet rs = statement.executeQuery(sql);

            while(rs.next())
            {
                Employee employee = new Employee(rs.getInt("ID"),rs.getString("NAME"),rs.getString("SURNAME"),rs.getInt("BALANCE"));
                employees.add(employee);
                Employee.setLast(rs.getInt("ID"));
            }

            statement.close();

        }catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }
         return employees;
    }

    public static void insertEmployee(Integer id,String name, String surname, Integer balance)
    {
        try
        {
            statement=conn.createStatement();
            sql="INSERT INTO EMPLOYEE (ID,NAME,SURNAME,BALANCE) "+"VALUES("+id+",'"+name+"','"+surname+"',"+balance+");";
            statement.executeUpdate(sql);
            statement.close();

        }catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }


    }

    public static void updateEmployee(Integer id, String name, String surname, Integer balance)
    {
        try
        {
            statement=conn.createStatement();
            sql="UPDATE EMPLOYEE SET NAME='"+name+"',SURNAME='"+surname+"',BALANCE="+balance+" WHERE ID="+id+";";
            statement.executeUpdate(sql);
            statement.close();


        }catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }

    }


     public static void insertBeverage(Integer id,Integer quantity,Integer price, Integer capsulesPerBox, Integer boxPrice, String name)
        {
            try
            {
                Statement statement=conn.createStatement();
                String sql="INSERT INTO BEVERAGE (ID,NAME,PRICE,CAPSULES_BOX,BOX_PRICE,QUANTITY)"
                        +"VALUES("+id+",'" +name+"',"+price+","+capsulesPerBox+","+boxPrice+","+quantity+");";
                //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
                statement.executeUpdate(sql);
                statement.close();

            }catch (SQLException e)
            {
                System.out.println(e.getMessage());
            }

        }

     public static void updateBeverage(Integer id, Integer capsulesPerBox, Integer boxPrice, String name,Integer quantity)
        {
            try
            {
                Statement statement=conn.createStatement();
                String sql="UPDATE BEVERAGE SET NAME = '"+name+"', CAPSULES_BOX = "+capsulesPerBox+", BOX_PRICE = "+boxPrice+", QUANTITY = "+quantity+" WHERE ID = "+id+";";
                statement.executeUpdate(sql);
                statement.close();

            }catch (SQLException e)
            {
                System.out.println(e.getMessage());
            }

        }

     public static List<Beverage> getBeverages() {
            List<Beverage> beverages = new ArrayList<Beverage>();
            String sql="SELECT * FROM BEVERAGE";
            Statement statement = null;
            try{


                statement=conn.createStatement();
                ResultSet rs = statement.executeQuery(sql);
                while(rs.next())
                {
                  Beverage b = new Beverage(rs.getInt("ID"),rs.getInt("PRICE"),rs.getInt("QUANTITY"),rs.getInt("CAPSULES_BOX"),rs.getInt("BOX_PRICE"),rs.getString("NAME"));
                  beverages.add(b);
                  Beverage.setLast(rs.getInt("ID"));
                }
                 statement.close();

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }

            return beverages;
        }

     public static Integer getBalance() {



            Statement statement;
            Statement statement1;
            Integer balance=0;

            try {
                statement=conn.createStatement();
                sql="INSERT INTO LATAZZAACCOUNT (ID, BALANCE) "
                        +"VALUES(0,0);";
                statement.executeUpdate(sql);
                statement.close();
            } catch (SQLException e) {
                System.out.println("RIGA GIA' PRESENTE");
                System.out.println(e.getMessage());

                sql="SELECT * FROM LATAZZAACCOUNT";
                ResultSet rs;
                try {
                    statement1=conn.createStatement();
                    rs = statement1.executeQuery(sql);
                    while(rs.next())
                       {

                           balance=rs.getInt("BALANCE");
                       }
                    statement1.close();

                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }



            }
            return balance;

     }

    public static void updateAccount(Integer amount) {

        String sql;
        Statement statement;

        try {
            statement=conn.createStatement();
            sql="UPDATE LATAZZAACCOUNT SET BALANCE="+amount+" WHERE ID=0;";
            statement.executeUpdate(sql);
            statement.close();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

    }

    public static void logBox(Integer id, String date,Integer idb,Integer qnt)
    {
        try
        {
            Statement statement=conn.createStatement();
            String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
                    +"VALUES("+id+",'" +date+"',0,1,0,0,0,"+idb+"," + qnt + ");";
            //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
            statement.executeUpdate(sql);
            statement.close();

        }catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }

    }


    public static void logRecharge(Integer id, String date,Integer amount,Integer idEmp)


    {
        try
        {
            Statement statement=conn.createStatement();
            String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
                    +"VALUES("+id+",'" +date+"',1,0,0,0,"+idEmp+",0," + amount + ");";
            //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
            statement.executeUpdate(sql);
            statement.close();

        }catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }

    }


public static void logEmployee(Integer id, String date,Integer idEm,Integer idBe, Integer qnt, Integer m)


    {
        try
        {
            Statement statement=conn.createStatement();
            String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
                    +"VALUES("+id+",'" +date+"',0,0,0,"+m+","+idEm+","+idBe+"," + qnt + ");";
            //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
            statement.executeUpdate(sql);
            statement.close();

        }catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }

    }

public static void logVisitor(Integer id, String date,Integer idBe, Integer qnt)


{
    try
    {
        Statement statement=conn.createStatement();
        String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
                +"VALUES("+id+",'" +date+"',0,0,1,0,0,"+idBe+"," + qnt + ");";
        //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
        statement.executeUpdate(sql);
        statement.close();

    }catch (SQLException e)
    {
        System.out.println(e.getMessage());
    }

}

public static List<Transaction> getLogs(DataImpl d) {

    List<Transaction> transactionDB = new ArrayList<Transaction>();
    Integer id,idBev,idEmp,qnt,price,quantity,capsulesPerBox,boxPrice,balance,visitor, mode;
    String date;
    String name,surname;

    //BOXPURCHASE
    try
    {
        Statement statement=conn.createStatement();
        String sql="SELECT * FROM LOGS WHERE BUY=1";

        ResultSet rs = statement.executeQuery(sql);
        while(rs.next())
        {
          id=rs.getInt("ID");
          idBev=rs.getInt("IDBEV");
          date=rs.getString("D");
          qnt=rs.getInt("AMQU");

          quantity=d.getBeverageCapsules(idBev);
          boxPrice=d.getBeverageBoxPrice(idBev);
          capsulesPerBox=d.getBeverageCapsulesPerBox(idBev);
          name=d.getBeverageName(idBev);
          price=boxPrice/capsulesPerBox;

          Beverage b = new Beverage(id,price,quantity,capsulesPerBox,boxPrice,name);
          BoxPurchase box = new BoxPurchase(id,date,b,qnt);
          transactionDB.add(box);
          Transaction.setLast(rs.getInt("ID"));
        }
         statement.close();

    }catch (SQLException e)
    {
        System.out.println(e.getMessage());
    } catch (BeverageException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }



    //RECHARGE

    try
    {
        Statement statement=conn.createStatement();
        String sql="SELECT * FROM LOGS WHERE RECHARGE=1";

        ResultSet rs = statement.executeQuery(sql);
        while(rs.next())
        {
          id=rs.getInt("ID");
          idEmp=rs.getInt("IDEMP");
          //date=rs.getDate("D");
          date=rs.getString("D");

          qnt=rs.getInt("AMQU");

          name=d.getEmployeeName(idEmp);
          surname=d.getEmployeeSurname(idEmp);
          balance=d.getEmployeeBalance(idEmp);

          Employee e = new Employee(idEmp,name,surname,balance);
          Recharge r = new Recharge(id,date,qnt,e);
          transactionDB.add(r);
          Transaction.setLast(rs.getInt("ID"));
        }
         statement.close();

    }catch (SQLException e)
    {
        System.out.println(e.getMessage());
    } catch (EmployeeException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }



    try
    {
        Statement statement=conn.createStatement();
        String sql="SELECT * FROM LOGS WHERE BUY=0 AND RECHARGE=0";

        ResultSet rs = statement.executeQuery(sql);
        while(rs.next())
        {
            id=rs.getInt("ID");
            idEmp=rs.getInt("IDEMP");
            //date=rs.getDate("D");
            date= rs.getString("D");
            qnt=rs.getInt("AMQU");
            visitor=rs.getInt("VISITOR");
            mode=rs.getInt("CB");
            idBev=rs.getInt("IDBEV");

          name=d.getEmployeeName(idEmp);
          surname=d.getEmployeeSurname(idEmp);
          balance=d.getEmployeeBalance(idEmp);

          Employee e = new Employee(idEmp,name,surname,balance);

          quantity=d.getBeverageCapsules(idBev);
          boxPrice=d.getBeverageBoxPrice(idBev);
          capsulesPerBox=d.getBeverageCapsulesPerBox(idBev);
          name=d.getBeverageName(idBev);
          price=boxPrice/capsulesPerBox;

          Beverage b = new Beverage(id,price,quantity,capsulesPerBox,boxPrice,name);
          Consumption c;


          if(visitor==1) {
              c = new Consumption(id, date,b,null,qnt,"VISITOR");

          } //non è visitor
          else if(mode==0){
              //è cash
                c = new Consumption(id,date,b,e,qnt,"CASH");

            }else {
            //è balance
                c = new Consumption(id,date,b,e,qnt,"BALANCE");

            }

        transactionDB.add(c);
          Transaction.setLast(rs.getInt("ID"));
        }
         statement.close();

    }catch (SQLException e)
    {
        System.out.println(e.getMessage());
    } catch (EmployeeException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (BeverageException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }



    return transactionDB;
}



}