package dbConnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import shoppingCart.Product;

public class DbConnector {

    Connection connection = null;
    public static void main(String[] args) {

        DbConnector dbConnector = new DbConnector();
        dbConnector.connectToDb();
//      dbConnector.createTable();
//      dbConnector.insertSampleData();
        dbConnector.getAllProducts();
    }

    public Connection connectToDb() {
        try {
            connection = DriverManager.getConnection("jdbc:postgresql://localhost:5433/CORBA_2", "postgres",
                    "P@ss_to_maciek_85");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return connection;
    }

    public void closeConnection() {
        try {
            if (!connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("Db connection closed");
    }

    public void createTable() {
        String createTableProducts = "CREATE TABLE PRODUCTS " + "(idProduct SERIAL PRIMARY KEY,  "
                + "productName varchar(255) unique not null, " + "productPrice  decimal not null,"
                + "productQuantity INTEGER not null) ";
        try {
            Statement statement = connection.createStatement();
            statement.executeUpdate(createTableProducts);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void insertSampleData() {
        String insert = "INSERT INTO PRODUCTS VALUES(1,'BEER', 2.5, 10) ";
        String insert1 = "INSERT INTO PRODUCTS VALUES(2,'WATER', 3.0, 10) ";
        String insert2 = "INSERT INTO PRODUCTS VALUES(3, 'BREAD', 2.5, 15) ";
        String insert3 = "INSERT INTO PRODUCTS VALUES(4, 'BUTTER', 5.0, 30) ";

        try {
            Statement statement = connection.createStatement();
            statement.executeUpdate(insert);
            statement.executeUpdate(insert1);
            statement.executeUpdate(insert2);
            statement.executeUpdate(insert3);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public Product[] getAllProducts() {
        ResultSet resultSet = null;
        Product[] productTable = null;
        String selectStatement = "Select idProduct, productName, productPrice, productQuantity from PRODUCTS";
        try {
            Statement statement = connection.createStatement();
            resultSet = statement.executeQuery(selectStatement);

            productTable = new Product[4];
            int i = 0;
            while(resultSet.next()) {
                Product p = new Product(resultSet.getInt("idProduct"), resultSet.getString("productName"), resultSet.getDouble("productPrice"), resultSet.getInt("productQuantity"));
                productTable[i] = p;
                i++;
            }
            resultSet.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return productTable;
    }
}