package Clases;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import org.hsqldb.types.Types;

public class AccesMySQL {
    private Connection cn;
    private Statement st;
    private PreparedStatement psProduct;
    private PreparedStatement psInsertOrder;
    private PreparedStatement psGetOrder;
    private PreparedStatement psGetOrderDetail;
    private CallableStatement cs;
    public AccesMySQL  (String dbName, String usuari, String password) throws ClassNotFoundException, SQLException
    {
            ResultSet taulesBaseDades;
            String[] tipus = {"TABLE"};
            //int i=1;

            Class.forName("com.mysql.jdbc.Driver");

            String url = "jdbc:mysql://+localhost:3306/"+dbName;
            cn = DriverManager.getConnection(url, usuari, password);
            st = cn.createStatement();

            psProduct = cn.prepareStatement("INSERT INTO products "
                    + "(productCode, productName, quantityInStock, buyPrice, msrp)"
                    + "VALUES (?,?,?,?,?)");
            psInsertOrder = cn.prepareStatement("INSERT INTO orders "
                    + "(orderNumber, orderDate, requiredDate, shippedDate, status, comments, customerNumber)"
                    + "VALUES (?,?,?,?,?,?,?)");
            psGetOrder = cn.prepareStatement("SELECT orderNumber, orderDate, requiredDate, shippedDate, status, comments, customerNumber"
                    + "FROM orders"
                    + "WHERE orderNumber=?");
            psGetOrderDetail = cn.prepareStatement("SELECT orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber"
                    + "FROM orderdetails"
                    + "WHERE orderNumber=?");
    }

    public boolean InsertModel(Product p) throws SQLException
    {
        boolean resultat;
        psProduct.setString(1, p.getProductCode());
        psProduct.setString(2, p.getProductName());
        psProduct.setInt(3,p.getQuantityStock());
        psProduct.setDouble(4, p.getBuyPrice());
        psProduct.setDouble(5,p.getMsrp());

        return psProduct.executeUpdate()!= 0;
    }
    public boolean InsertCustomer(Customer c) throws Exception
    {
        Boolean resultat;
        cs = cn.prepareCall("{call ExisteixCustomer(?, ?)}");
        cs.registerOutParameter(2, Types.BOOLEAN);
        cs.setInt(1,c.getNumber());
        cs.execute();
        if (!cs.getBoolean(2))
        {
            String sql = "INSERT INTO customers"
                    + "(customerNumber,customerName,contactLastName,contactFirstName,"
                    + "phone,addressLine1,addressLine2,city,state,"
                    + "postalCode,country,salesRepEmployeeNumber,creditLimit)"
                    + "VALUES ("+c.getNumber()+",'"+c.getName()+"','"
                    +c.getContactLastName()+"','"+c.getContactFirstName()
                    +"','"+c.getPhone()+"','"+c.getAddressLine1()+"',"
                    +(c.getAddressLine2().equals("NULL")?
                            "NULL" :"'"+c.getAddressLine2()+"'")
                    +",'"+c.getCity()+"',"
                    +(c.getState().equals("NULL")?
                            "NULL":"'"+c.getState()+"'")
                    +",'"+c.getPostalCode()+"','"+c.getCountry()
                    +"',"+ (c.getEmployeeNumber()==-1 ?
                            "NULL":String.valueOf(c.getEmployeeNumber()))
                    +","+(c.getCreditLimit()==-1 ?
                            "NULL":String.valueOf(c.getCreditLimit())+")");
            //System.out.println(sql);
            resultat = st.executeUpdate(sql) != 0;
        }
        else
            throw new Exception("Aquest customer ja existeix");
        return resultat;


    }

    public boolean InsertComanda(Order o, OrderDetail od) throws SQLException
    {
        boolean resultat = true;
        try
        {
            cn.setAutoCommit(false);

            cs = cn.prepareCall("{call ExisteixCustomer(?, ?)}");
            cs.registerOutParameter(2, Types.BOOLEAN);
            cs.setInt(1, o.getCustomerNumber());
            cs.execute();
            if (cs.getBoolean(2))
            {
                if (!InsertOrder(o))
                    throw new Exception("Order no s'ha pogut inserir");

                if (!InsertOrderDetail(od))
                    throw new Exception("Order Detail no s'ha pogut inserir");
            }
            else
                throw new Exception("Aquest customer no existeix");

            cn.commit();
        }
        catch (Exception err)
        {
            System.out.println(err.getMessage());
            resultat = false;
            //cn.rollback();
        }
        finally
        {
            cn.setAutoCommit(true);
        }

        return resultat;
    }
    private boolean InsertOrder(Order o) throws Exception
    {
        boolean resultat;
        cs = cn.prepareCall("{call ExisteixOrder(?, ?)}");
        cs.registerOutParameter(2, Types.BOOLEAN);
        cs.setInt(1, o.getNumber());
        cs.execute();
        if (!cs.getBoolean(2))//No Existeix codi Order
        {
            psInsertOrder.setInt(1, o.getNumber());
            psInsertOrder.setString(2, o.getDate());
            psInsertOrder.setString(3, o.getRequiredDate());
            psInsertOrder.setString(4,
                    (o.getShippedDate().equals("NULL")?
                    null:o.getShippedDate()));
            psInsertOrder.setString(5, o.getStatus());
            psInsertOrder.setString(6,
                    (o.getComments().equals("NULL")?
                            null:o.getComments()));
            psInsertOrder.setInt(7, o.getCustomerNumber());
        }
        else
            return false;
        resultat = psInsertOrder.executeUpdate() != 0;
        return resultat;
    }
    private boolean InsertOrderDetail(OrderDetail od) throws Exception
    {
        cs = cn.prepareCall("{call ExisteixOrder(?, ?)}");
        cs.registerOutParameter(2, Types.BOOLEAN);
        cs.setInt(1, od.getNumber());
        cs.execute();
        if (cs.getBoolean(2))//Existeix codi Order
        {
            for(LineOrder lo : od.getLineOrders())
            {
                String sql = "INSERT INTO orderDetails "
                        + "(orderNumber, productCode, quantityOrdered,"
                        + " priceEach, orderLineNumber)"
                        + " VALUES (" + od.getNumber() + ",'" + lo.getProductCode()+"',"
                        + lo.getQuantityOrdered() + "," + lo.getPriceEach()+ ","
                        +  lo.getOrderLineNumber() + ")";
                //System.out.println(sql);
                if (st.executeUpdate(sql)==0)
                    throw new Exception("No s'ha pogut insertar line order: "+lo.getOrderLineNumber());
            }
        }
        else
            return false;

        return true;
    }

    public Order GetOrder(int codiComanda) throws SQLException
    {
        Order order = null;
        psGetOrder.setInt(1, codiComanda);
        ResultSet rs = psGetOrder.executeQuery();
        if(rs.next())
        {
            order = new Order(rs.getInt("orderNumber"),
                    rs.getString("orderDate"),
                    rs.getString("requiredDate"),
                    rs.getString("shippedDate"),
                    rs.getString("status"),
                    rs.getString("comments"),
                    rs.getInt("customerNumber"));
        }
        return order;
    }
    public OrderDetail GetOrderDetail(int codiComanda) throws SQLException
    {
        OrderDetail detallOrdre = null;
        ArrayList<LineOrder> lineOrders;
        int i = 0;
        psGetOrder.setInt(1, codiComanda);
        ResultSet rs = psGetOrder.executeQuery();
        lineOrders = new ArrayList<LineOrder>();
        while(rs.next())
        {
            lineOrders.set(i, new LineOrder(rs.getString("productCode"),
                    rs.getInt("quantityOrdered"),
                    rs.getDouble("priceEach"),
                    rs.getInt("orderLineNumber"),
                    rs.getInt("orderNumber")));
            i++;
        }
        detallOrdre = new OrderDetail(rs.getInt("orderNumber"), lineOrders);
        return detallOrdre;
    }
    public double GetTotalOrder(int codiComanda) throws SQLException
    {
    	double resultat = 0;
        cs = cn.prepareCall("{call ImportTotal(?, ?)}");
        cs.registerOutParameter(2, Types.DOUBLE);
        cs.setInt(1, codiComanda);
        cs.execute();
        return cs.getDouble(2);
    }
}