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.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

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  (char dbType, String dbName, String usuari, String password)
	{
		try
		{
			ResultSet taulesBaseDades;
			String[] tipus = {"TABLE"};
			int i=1;

			switch(dbType)
			{
			case 'M': case'm':
				Class.forName("com.mysql.jdbc.Driver");

				String url = "jdbc:mysql://localhost:3306/"+dbName;
				cn = DriverManager.getConnection(url, usuari, password);
				break;
			case 'A': case 'a':
				Class.forName("net.ucanacces.jdbc.UcanaccessDriver");
				cn = DriverManager.getConnection("jdbc:ucanaccess://"+dbName+".mdb");
				break;
			}
			st = cn.createStatement();
			psProduct = cn.prepareStatement("INSERT INTO products "
					+ "(productCode, productName, quantityInStock, buyPrice, msrp)"
					+ "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=?");
			psInsertOrder = cn.prepareStatement("INSERT INTO orders "
					+ "(orderNumber, orderDate, requiredDate, shippedDate, status, comments, customerNumber)"
					+ "VALUES (?,?,?,?,?,?,?)");
		}
		catch (Exception e)
		{
			System.out.println(e.getMessage());
		}
	}

	private boolean TypeEsValid(char type) {
		return type =='a'|| type =='A'||type =='m'||type =='M';
	}

	public boolean InsertModel(Product p) throws SQLException
	{
		psProduct.setString(1, p.getProductName());
		psProduct.setString(2, p.getProductName());
		psProduct.setInt(3, p.getQuantityStock());

		return false;
	}
	public boolean InsertCustomer(Customer c)
	{
		return false;
	}

	public boolean InsertComanda(Order o, OrderDetail od) throws SQLException
	{
		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))
            {
				InsertOrder(o);
				InsertOrderDetail(od);
            }
            else
            	throw new Exception("Aquest customer no existeix");

			cn.commit();
		}
		catch (Exception err)
		{
			cn.rollback();
		}
		finally
		{
			cn.setAutoCommit(true);
		}

		return false;
	}
	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))
        {
			psInsertOrder.setInt(1, o.getNumber());
			psInsertOrder.setString(2, o.getDate());
			psInsertOrder.setString(3, o.getRequiredDate());
			psInsertOrder.setString(4, o.getShippedDate());
			psInsertOrder.setString(5, o.getStatus());
			psInsertOrder.setString(6, o.getComments());
			psInsertOrder.setInt(7, o.getCustomerNumber());
        }
        else
        	throw new Exception("Aquest customer no existeix");
        resultat = psInsertOrder.execute();
		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))
        {
			for(LineOrder lo : od.getLineOrders())
			{
				String sql = "INSERT INTO ordersdetails "
						+ "(orderNumber, orderDate, requiredDate, shippedDate, status, comments, customerNumber)"
						+ "VALUES (" + od.getNumber() + "," + lo.getProductCode()+ "," + lo.getQuantityOrdered() + "," + lo.getPriceEach() + "," +  lo.getOrderLineNumber() + ")";
				st.executeUpdate(sql);
			}
        }
        else
        	throw new Exception("Aquest customer no existeix");
        return true;

	}

	public Order GetOrder(int codiComanda) throws SQLException
	{
		Order ordre = null;
		psGetOrder.setInt(1, codiComanda);
		ResultSet rs = psGetOrder.executeQuery();
		if(rs.next())
		{
			ordre = new Order(rs.getInt("orderNumber"), rs.getString("orderDate"), rs.getString("requiredDate"), rs.getString("shippedDate"), rs.getString("status"), rs.getString("comments"), rs.getInt("customerNumber"));
		}
		return ordre;
	}
	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")));
			i++;
		}
		detallOrdre = new OrderDetail(rs.getInt("orderNumber"), lineOrders);
		return detallOrdre;
	}
	public double GetTotalOrder(int codiComanda)
	{
		return 0;
	}
}