package sql_interaction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import domain.Employee;
import domain.Item;

public class Item_sql
{
    // jdbc protocol
    private static String dbname = "REMOVED";
    private static String db_username = "REMOVED";
    private static String db_password = "REMOVED";

    private Connection connection = null;
    private Statement statement = null;
    private ResultSet queryResult = null;
    private PreparedStatement pstmt = null;
    private static String query = "";

    //Constructor
    /**
     * Takes a newItem and add it to the database
     * @param newItem
     */
    public void addNewItem(Item newItem, Employee emp)
    {
        Integer id_cardinality = 0;
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(dbname, db_username, db_password);
            // *******************      Generates the new id for the new Item   *********************

            query = "SELECT MAX(Item_id) FROM Item";
            statement=connection.createStatement();;
            queryResult = statement.executeQuery(query);

            queryResult.next();

            id_cardinality = Integer.parseInt(queryResult.getObject(1).toString());
            id_cardinality++; //increase Item_id by 1, for the new Item
            newItem.setItem_id(id_cardinality.toString());
            //*************************     Insert new Item into the DB     ****************
            query = "INSERT INTO Item VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

            pstmt = connection.prepareStatement(query);
            pstmt.clearParameters();
            pstmt.setString(1, newItem.getItem_id());
            pstmt.setString(2, newItem.getItem_name());
            pstmt.setString(3, newItem.getCategory());
            pstmt.setString(4, newItem.getSize());
            pstmt.setString(5, newItem.getAvailable());
            pstmt.setString(6, newItem.getCost());
            pstmt.setString(7, newItem.getSales_price());
            pstmt.setString(8, emp.getEmployee_id());
            pstmt.executeUpdate();

            connection.close();
        } catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
    }

    /**
     * Upgrade item onto the database
     * @param newItem
     */
    public void updateItem(Item upgradeItem)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(dbname, db_username, db_password);

            //*************************     Insert new Item into the DB     ****************
            query = "UPDATE Item SET Item_name = ?,Category = ?, Size = ?, Available = ?, Cost = ?, Sales_Price = ? " +
                    "WHERE Item_id = ?";

            pstmt = connection.prepareStatement(query);
            pstmt.clearParameters();
            pstmt.setString(1, upgradeItem.getItem_name());
            pstmt.setString(2, upgradeItem.getCategory());
            pstmt.setString(3, upgradeItem.getSize());
            pstmt.setString(4, upgradeItem.getAvailable());
            pstmt.setString(5, upgradeItem.getCost());
            pstmt.setString(6, upgradeItem.getSales_price());
            pstmt.setString(7, upgradeItem.getItem_id());

            pstmt.executeUpdate();

            connection.close();
        } catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
    }
    /**
     * Select query
     * @param id
     * @return
     */
    public ArrayList<Item> selectItem(String id)
    {
        ArrayList<Item> arrayItems = new ArrayList<Item>();
        Item tempItem = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(dbname, db_username, db_password);

            //*************************     Insert new Employee into the DB     ****************
            // Select street FROM MyTable WHERE street LIKE '%Peninsula%'

            if (id.matches("") || id.matches("<Search Product Name>")) // display the complete table
            {
                query = "SELECT * FROM Item ORDER BY Item_name";
            } else // partial string searchable
            {
                query = "SELECT * FROM Item WHERE Item_name LIKE \"%" + id +"%\"";
            }

            statement = connection.createStatement();
            queryResult = statement.executeQuery(query);

            //queryResult.next();
            while(queryResult.next())
            {
                tempItem = new Item(
                        queryResult.getObject(1).toString(),
                        queryResult.getObject(2).toString(),
                        queryResult.getObject(3).toString(),
                        queryResult.getObject(4).toString(),
                        queryResult.getObject(5).toString(),
                        queryResult.getObject(6).toString(),
                        queryResult.getObject(7).toString());

                arrayItems.add(tempItem);
            }
             connection.close();

        } catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
        return arrayItems;
    }
    /**
     * Reduce Item availability
     * @param newItem
     */
    public void reduceAvilability(String upgradeItem)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(dbname, db_username, db_password);

            //*************************     Insert new Item into the DB     ****************
            query = "UPDATE Item SET  Available = Available - 1 " +
                    "WHERE Item_id = ?";

            pstmt = connection.prepareStatement(query);
            pstmt.clearParameters();
            pstmt.setString(1, upgradeItem);

            pstmt.executeUpdate();

            connection.close();
        } catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
    }
}