package database;

import java.sql.*;
import java.util.ArrayList;
import javax.swing.DefaultListModel;

import shoppingCart.Product;
import graphicUnitInterface.User;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class DerbyDB {
    private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
    private static final String JDBC_URL = "jdbc:derby:DerbyDB;create=True";
    private static String SQL_STATEMENT;
    private static final DerbyDB Singleton = new DerbyDB();

    private DerbyDB(){}

    public static DerbyDB getInstance() {
        return Singleton;
    }

    public void initialize() throws ClassNotFoundException{
        Class.forName(DRIVER);
        Connection connect;

        try {
            connect = DriverManager.getConnection(JDBC_URL);
            DatabaseMetaData data = connect.getMetaData();
            ResultSet res = data.getTables(null, null, null, new String[]{"TABLE"});

            if(!res.next()) {

                connect.createStatement().execute("CREATE TABLE CATEGORIA (idCat INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), nome VARCHAR(20) NOT NULL, descr VARCHAR(40) NOT NULL)");
                connect.createStatement().execute("INSERT INTO CATEGORIA (nome, descr) VALUES " +
                                                    "('Smartphone', 'Smartphone di ultima generazione')," +
                                                    "('Action Camera', 'Registra tutto cio che vuoi')," +
                                                    "('Laptop', 'Porta con te tutti i tuoi file ed i tuoi programmi')," +
                                                    "('Monitor', 'Acquista solo il meglio per il tuo computer fisso')");
                System.out.println("CATEGORIA table created and populated");


                connect.createStatement().execute("CREATE TABLE PRODOTTO (idProd INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), nome VARCHAR(30) NOT NULL, descr VARCHAR(45), stock INTEGER NOT NULL, prezzo FLOAT NOT NULL, idCat INTEGER NOT NULL, CONSTRAINT idCat FOREIGN KEY (idCat) REFERENCES CATEGORIA)");
                connect.createStatement().execute("INSERT INTO PRODOTTO (nome, descr, stock, prezzo, idCat) VALUES " +
                                                    "('Samsung S7', 'Smartphone di moda della casa Samsung', 13, 649.90, 1)," +
                                                    "('HTC 10', 'Ultima uscita in casa HTC', 5, 699.90, 1)," +
                                                    "('Xiaomi Yi Camera', 'Action Camera di Xiaomi', 21, 71.90, 2)," +
                                                    "('DELL XPS 13', 'Silent Laptop con grandezza dello schermo pari a 13 pollici', 7, 1149.00, 3)," +
                                                    "('Asus ROG G752', 'Laptop da Gaming: gaming without limits', 8, 1362.59, 3)," +
                                                    "('Dell Ultrasharp U2515H', 'Monitor IPS dal grande rendimento, 25 pollici', 15, 324.99, 4)," +
                                                    "('Asus ROG SWIFT PG278Q', 'Monitor 27 pollici da gaming', 2, 657.79, 4)," +
                                                    "('LG G5', 'Il nuovo LG G5 supera tutti i limiti', 11, 410.00, 1)");
                System.out.println("PRODOTTO table created and populated");


                connect.createStatement().execute("CREATE TABLE CLIENTE (nick VARCHAR(15) NOT NULL PRIMARY KEY, pw VARCHAR(20) NOT NULL, email VARCHAR(60) NOT NULL, nome VARCHAR(20) NOT NULL, cognome VARCHAR(25) NOT NULL, via VARCHAR(30) NOT NULL, citta VARCHAR(25) NOT NULL, cap CHAR(5) NOT NULL, tel VARCHAR(15) NOT NULL)");
                connect.createStatement().execute("INSERT INTO CLIENTE VALUES " +
                                                    "('cristyB', 'canecasa', 'cristinabuonomo@gmail.com', 'Cristina', 'Buonomo', 'Via Europa 55', 'Pollena Trocchia', '80040', '0816953652')," +
                                                    "('theOne', 'pass1234', 'antonioesposito@gmail.com', 'Antonio', 'Esposito', 'Via Toledo 33', 'Napoli', '80134', '0815693256')," +
                                                    "('Maradona10', 'grandenapoli', 'gennysavastano@gmail.com', 'Genny', 'Savastano', 'Via Broggia 15', 'Napoli', '80135' '0815694563')," +
                                                    "('C1r0', 'baubau', 'ciromantovani@gmail.com', 'Ciro', 'Mantovani', 'Corso Umberto I 144', 'Napoli', '80138', '0815698878')");
                System.out.println("CLIENTE table created and populated");


                connect.createStatement().execute("CREATE TABLE ADMIN (nick VARCHAR(15) NOT NULL PRIMARY KEY, pw VARCHAR(20) NOT NULL, email VARCHAR(60) NOT NULL)");
                connect.createStatement().execute("INSERT INTO ADMIN VALUES " +
                                                    "('admin', 'root', 'federicocuozzo@gmail.com')," +
                                                    "('admin2', 'toor', 'admin2@gmail.com')");
                System.out.println("ADMIN table created and populated");


                connect.createStatement().execute("CREATE TABLE ACQUISTA (dataAcquisto DATE NOT NULL, oraAcquisto TIME NOT NULL, nick VARCHAR(15) NOT NULL, idProd INTEGER NOT NULL, CONSTRAINT nick FOREIGN KEY (nick) REFERENCES CLIENTE, CONSTRAINT idProd FOREIGN KEY (idProd) REFERENCES PRODOTTO, PRIMARY KEY (dataAcquisto, oraAcquisto))");
                System.out.println("ACQUISTA table created");
            }
        }
        catch (SQLException exc) {
            exc.printStackTrace();
        }
    }

public User checkAdmin(String nick, String pw) throws SQLException {
        Connection connect = DriverManager.getConnection(JDBC_URL);
        Statement stat = connect.createStatement();
        User user = null;

        SQL_STATEMENT = "SELECT * FROM ADMIN WHERE ADMIN.nick = '"+ nick +"' AND ADMIN.pw ='"+ pw +"'";

        ResultSet res = stat.executeQuery(SQL_STATEMENT);

        if(res.next())
            user = new User(res.getString(1), res.getString(2), res.getString(3), null, null, null, null, null, null);

        if(stat != null)
            stat.close();
        if(connect != null)
            connect.close();

        return user;
    }