package dataaccess;

import datatypes.Customer;
import datatypes.Treatment;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class MySQLDataAccess
{
  private PreparedStatement addDataToCustomersTable;
  private PreparedStatement queryDataFromCustomersTable;
  private PreparedStatement addDataToTreatmentsTable;
  private PreparedStatement queryDataFromTreatmentsTableByCustID;
  private PreparedStatement queryIDFromCustomerTable;
  private PreparedStatement updateCustomerTableUsingID;
  private PreparedStatement updateTreatmentsTableUsingID;
  private PreparedStatement queryLastId;
  private Connection conn;

  private MySQLDataAccess(String url, String dbName, String dbPass)
    throws SQLException
  {
    if (url == null)
    {
      throw new IllegalArgumentException("url cannot be null");
    }

    if (dbName == null)
    {
      throw new IllegalArgumentException("dbName cannot be null");
    }

    if (dbPass == null)
    {
      throw new IllegalArgumentException("dbPass cannot be null");
    }

    this.conn = DriverManager.getConnection(url, dbName, dbPass);
    try
    {
      String adtct = "INSERT INTO customers(CustFirstName,CustLastName, CustTelephone, CustCellphone, CustEmail, CustSkinType) VALUES (?, ?, ?, ?, ?, ?)";

      this.addDataToCustomersTable = this.conn.prepareStatement("INSERT INTO customers(CustFirstName,CustLastName, CustTelephone, CustCellphone, CustEmail, CustSkinType) VALUES (?, ?, ?, ?, ?, ?)");

      String qdfct = "SELECT * FROM customers WHERE (CustFirstName LIKE ? AND CustLastName LIKE ?)";

      this.queryDataFromCustomersTable = this.conn.prepareStatement("SELECT * FROM customers WHERE (CustFirstName LIKE ? AND CustLastName LIKE ?)");

      String qidfct = "SELECT CustID FROM customers WHERE (CustFirstName = ? AND CustLastName = ? AND CustTelephone = ? AND CustCellphone = ? AND CustEmail = ? AND CustSkinType = ?)";

      this.queryIDFromCustomerTable = this.conn.prepareStatement("SELECT CustID FROM customers WHERE (CustFirstName = ? AND CustLastName = ? AND CustTelephone = ? AND CustCellphone = ? AND CustEmail = ? AND CustSkinType = ?)");

      String adttt = "INSERT INTO treatments(CustID, TreatmentDate,TreatmentArea, TreatmentPulseWidth, TreatmentSpotSize,TreatmentEnergy, TreatmentAmount, TreatmentComments) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

      this.addDataToTreatmentsTable = this.conn.prepareStatement("INSERT INTO treatments(CustID, TreatmentDate,TreatmentArea, TreatmentPulseWidth, TreatmentSpotSize,TreatmentEnergy, TreatmentAmount, TreatmentComments) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

      String uctui = "UPDATE customers SET CustFirstName = ?, CustLastName = ?, CustTelephone = ?, CustCellphone = ?, CustEmail = ?, CustSkinType = ? WHERE CustID = ?";

      this.updateCustomerTableUsingID = this.conn.prepareStatement("UPDATE customers SET CustFirstName = ?, CustLastName = ?, CustTelephone = ?, CustCellphone = ?, CustEmail = ?, CustSkinType = ? WHERE CustID = ?");

      String qdfttbci = "SELECT TreatmentID, TreatmentDate,TreatmentArea, TreatmentPulseWidth,TreatmentSpotSize, TreatmentEnergy, TreatmentAmount,TreatmentComments FROM treatments WHERE CustID = ?";

      this.queryDataFromTreatmentsTableByCustID = this.conn.prepareStatement("SELECT TreatmentID, TreatmentDate,TreatmentArea, TreatmentPulseWidth,TreatmentSpotSize, TreatmentEnergy, TreatmentAmount,TreatmentComments FROM treatments WHERE CustID = ?");

      String uttui = "UPDATE treatments SET TreatmentDate = ?, TreatmentArea = ?, TreatmentPulseWidth = ?, TreatmentSpotSize = ?, TreatmentEnergy = ?, TreatmentAmount = ?, TreatmentComments = ? WHERE TreatmentID = ?";

      this.updateTreatmentsTableUsingID = this.conn.prepareStatement("UPDATE treatments SET TreatmentDate = ?, TreatmentArea = ?, TreatmentPulseWidth = ?, TreatmentSpotSize = ?, TreatmentEnergy = ?, TreatmentAmount = ?, TreatmentComments = ? WHERE TreatmentID = ?");

      String qli = "SELECT LAST_INSERT_ID()";
      this.queryLastId = this.conn.prepareStatement("SELECT LAST_INSERT_ID()");
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }
  }

  public static MySQLDataAccess connect(String host, String dbName, String dbUser, String dbPass)
    throws ClassNotFoundException, SQLException
  {
    if (host == null)
    {
      throw new IllegalArgumentException("host cannot be null");
    }

    if (dbName == null)
    {
      throw new IllegalArgumentException("dbname cannot be null");
    }

    if (dbUser == null)
    {
      throw new IllegalArgumentException("dbuser cannot be null");
    }

    if (dbPass == null)
    {
      throw new IllegalArgumentException("dbpass cannot be null");
    }

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

    String url = "jdbc:mysql://" + host + "/" + dbName;

    MySQLDataAccess access = new MySQLDataAccess(url, dbUser, dbPass);

    return access;
  }

  public Connection getConnectionObject()
  {
    return this.conn;
  }

  public void InsertIntoCustomerTable(String firstName, String lastName, String telephone, String cellphone, String email, String type)
  {
    if (firstName == null)
    {
      throw new IllegalArgumentException("firstName cannot be null");
    }
    if (lastName == null)
    {
      throw new IllegalArgumentException("lastName cannot be null");
    }
    if (telephone == null)
    {
      throw new IllegalArgumentException("telephone cannot be null");
    }
    if (cellphone == null)
    {
      throw new IllegalArgumentException("cellphone cannot be null");
    }
    if (email == null)
    {
      throw new IllegalArgumentException("email cannot be null");
    }
    if (type == null)
    {
      throw new IllegalArgumentException("type cannot be null");
    }

    long telephoneLong = parsePhoneDigits(telephone);
    long cellphoneLong = parsePhoneDigits(cellphone);
    int skintype = Integer.parseInt(type);
    try
    {
      this.addDataToCustomersTable.setString(1, firstName);
      this.addDataToCustomersTable.setString(2, lastName);
      this.addDataToCustomersTable.setLong(3, telephoneLong);
      this.addDataToCustomersTable.setLong(4, cellphoneLong);
      this.addDataToCustomersTable.setString(5, email);
      this.addDataToCustomersTable.setInt(6, skintype);

      this.addDataToCustomersTable.executeUpdate();
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }
  }

  public void InsertIntoCustomerTable(Customer customer)
  {
    if (customer == null)
    {
      throw new IllegalArgumentException("customer cannot be null");
    }

    try
    {
      this.addDataToCustomersTable.setString(1, customer.getFirstName());
      this.addDataToCustomersTable.setString(2, customer.getLastName());
      this.addDataToCustomersTable.setLong(3, customer.getTelephone());
      this.addDataToCustomersTable.setLong(4, customer.getCellphone());
      this.addDataToCustomersTable.setString(5, customer.getEmail());
      this.addDataToCustomersTable.setInt(6, customer.getSkinType());

      this.addDataToCustomersTable.executeUpdate();
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }
  }

  public void InsertIntoTreatmentTable(Treatment treatment)
  {
    if (treatment == null)
    {
      throw new IllegalArgumentException("treatment cannot be null");
    }

    try
    {
      this.addDataToTreatmentsTable.setLong(1, treatment.getCustId());
      this.addDataToTreatmentsTable.setString(2, treatment.getDate());
      this.addDataToTreatmentsTable.setString(3, treatment.getArea());
      this.addDataToTreatmentsTable.setFloat(4, treatment.getPulseWidth());
      this.addDataToTreatmentsTable.setInt(5, treatment.getSpotSize());
      this.addDataToTreatmentsTable.setInt(6, treatment.getEnergy());
      this.addDataToTreatmentsTable.setFloat(7, treatment.getAmount());
      this.addDataToTreatmentsTable.setString(8, treatment.getComments());

      this.addDataToTreatmentsTable.executeUpdate();
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }
  }

  public ResultSet QueryFromCustomerTable(String firstName, String lastName)
  {
    if (firstName == null)
    {
      throw new IllegalArgumentException("firstName cannot be null");
    }
    if (lastName == null)
    {
      throw new IllegalArgumentException("lastName cannot be null");
    }

    ResultSet rs = null;
    try
    {
      this.queryDataFromCustomersTable.setString(1, "%" + firstName + "%");
      this.queryDataFromCustomersTable.setString(2, "%" + lastName + "%");

      rs = this.queryDataFromCustomersTable.executeQuery();
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }

    return rs;
  }

  public long QueryCustIDFromCustomerTable(Customer customer)
  {
    long id = -1L;
    try
    {
      this.queryIDFromCustomerTable.setString(1, customer.getFirstName());
      this.queryIDFromCustomerTable.setString(2, customer.getLastName());
      this.queryIDFromCustomerTable.setLong(3, customer.getTelephone());
      this.queryIDFromCustomerTable.setLong(4, customer.getCellphone());
      this.queryIDFromCustomerTable.setString(5, customer.getEmail());
      this.queryIDFromCustomerTable.setInt(6, customer.getSkinType());

      ResultSet rs = this.queryIDFromCustomerTable.executeQuery();

      rs.next();
      id = rs.getLong(1);
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }

    return id;
  }

  public void updateCustomersTableUsingID(Customer customer)
  {
    if (customer == null)
    {
      throw new IllegalArgumentException("customer cannot be null");
    }

    try
    {
      this.updateCustomerTableUsingID.setString(1, customer.getFirstName());
      this.updateCustomerTableUsingID.setString(2, customer.getLastName());
      this.updateCustomerTableUsingID.setLong(3, customer.getTelephone());
      this.updateCustomerTableUsingID.setLong(4, customer.getCellphone());
      this.updateCustomerTableUsingID.setString(5, customer.getEmail());
      this.updateCustomerTableUsingID.setInt(6, customer.getSkinType());
      this.updateCustomerTableUsingID.setLong(7, customer.getID());

      this.updateCustomerTableUsingID.executeUpdate();
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }
  }

  public ResultSet QueryFromTreatmentsTable(Customer customer)
  {
    if (customer == null)
    {
      throw new IllegalArgumentException("customer cannot be null");
    }

    ResultSet rs = null;
    try
    {
      long id = QueryCustIDFromCustomerTable(customer);
      this.queryDataFromTreatmentsTableByCustID.setLong(1, id);

      rs = this.queryDataFromTreatmentsTableByCustID.executeQuery();
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }

    return rs;
  }

  public void updateTreatment(Treatment treatment)
  {
    if (treatment == null)
    {
      throw new IllegalArgumentException("treatment cannot be null");
    }

    try
    {
      this.updateTreatmentsTableUsingID.setString(1, treatment.getDate());
      this.updateTreatmentsTableUsingID.setString(2, treatment.getArea());
      this.updateTreatmentsTableUsingID.setInt(3, treatment.getPulseWidth());
      this.updateTreatmentsTableUsingID.setInt(4, treatment.getSpotSize());
      this.updateTreatmentsTableUsingID.setInt(5, treatment.getEnergy());
      this.updateTreatmentsTableUsingID.setInt(6, treatment.getAmount());
      this.updateTreatmentsTableUsingID.setString(7, treatment.getComments());

      this.updateTreatmentsTableUsingID.setLong(8, treatment.getTreatmentId());

      this.updateTreatmentsTableUsingID.executeUpdate();
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }
  }

  public long getLastInsertID()
  {
    try
    {
      ResultSet rs = this.queryLastId.executeQuery();

      if (rs.next())
      {
        long id = rs.getLong(1);
        return id;
      }
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }

    return -1L;
  }

  private long parsePhoneDigits(String telephoneString)
  {
    String result = "";

    if (telephoneString == null)
    {
      throw new IllegalArgumentException("telephoneString cannot be null");
    }

    for (int i = 0; i < telephoneString.length(); ++i)
    {
      char ch = telephoneString.charAt(i);

      if (!(Character.isDigit(ch)))
        continue;
      result = result + ch;
    }

    try
    {
      long resultLong = Long.parseLong(result);

      return resultLong;
    }
    catch (NumberFormatException ex) {
    }
    return 0L;
  }

  public void installDB(String dbName)
  {
    if (dbName == null)
    {
      throw new IllegalArgumentException("dbName cannot be null");
    }

    try
    {
      Statement stmt = this.conn.createStatement();

      System.out.println("Creating DB \"" + dbName + "\"");
      stmt.execute("CREATE DATABASE IF NOT EXISTS " + dbName);

      installTables();
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }
  }

  private void installTables()
  {
    Statement stmt;
    try
    {
      stmt = this.conn.createStatement();

      stmt.execute("DROP TABLE IF EXISTS customers");

      String customerTable = "CREATE TABLE IF NOT EXISTS customers (CustID INT(11) AUTO_INCREMENT,CustFirstName VARCHAR(256),CustLastName VARCHAR(256),CustTelephone BIGINT(12),CustCellphone BIGINT(12),CustEmail VARCHAR(256),CustSkinType INT(4),PRIMARY KEY(CustID))";

      stmt.execute("CREATE TABLE IF NOT EXISTS customers (CustID INT(11) AUTO_INCREMENT,CustFirstName VARCHAR(256),CustLastName VARCHAR(256),CustTelephone BIGINT(12),CustCellphone BIGINT(12),CustEmail VARCHAR(256),CustSkinType INT(4),PRIMARY KEY(CustID))");

      System.out.println("Created table: customers");
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }

    try
    {
      ex = this.conn.createStatement();

      ex.execute("DROP TABLE IF EXISTS treatments");

      String treatmentTable = "CREATE TABLE IF NOT EXISTS treatments (TreatmentID BIGINT(11) AUTO_INCREMENT,CustID BIGINT(11),TreatmentDate VARCHAR(128),TreatmentArea VARCHAR(128),TreatmentPulseWidth INT(5),TreatmentSpotSize INT(5),TreatmentEnergy INT(5),TreatmentAmount INT(5),TreatmentComments VARCHAR(256),PRIMARY KEY(TreatmentID))";

      ex.execute("CREATE TABLE IF NOT EXISTS treatments (TreatmentID BIGINT(11) AUTO_INCREMENT,CustID BIGINT(11),TreatmentDate VARCHAR(128),TreatmentArea VARCHAR(128),TreatmentPulseWidth INT(5),TreatmentSpotSize INT(5),TreatmentEnergy INT(5),TreatmentAmount INT(5),TreatmentComments VARCHAR(256),PRIMARY KEY(TreatmentID))");

      System.out.println("Created table: treatments");
    }
    catch (SQLException ex)
    {
      System.out.println("SQL Error: " + ex.getMessage());
    }
  }
}