package saitMLS.persistence.clientale;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import saitMLS.exceptions.clientale.InvalidPhoneNumberException;
import saitMLS.exceptions.clientale.InvalidPostalCodeException;
import saitMLS.persistence.Broker;
import saitMLS.problemDomain.Clientale.Client;

/**
 * Implementation of the abstract class Broker which regulates how the client randomAccessFile database is
 * accessed by the ClientWindow.
 * @author Taylor Doud
 *
 */
public class ClientBroker implements Broker
{
    private static ClientBroker cb;
    private ArrayList<Client> searchList;
    private long maxId = 0;

    private Statement stmt;
    private Connection conn;
    private ResultSet rs;

    /**
     * Constructor to be called when the getBroker method is called. Determines whether a new
     * Binary file is created upon launch.
     * @throws FileNotFoundException
     */
    private ClientBroker() throws FileNotFoundException
    {
        try {
            DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
            String url = "jdbc:oracle:thin:@" + CredentialStore.HOSTNAME + ":" + CredentialStore.PORT;// + ":"
            //              + CredentialStore.SID;
            // jdbc:oracle:thin:@HOSTNAME:PORT:SID
            conn = DriverManager.getConnection(url, CredentialStore.USER, CredentialStore.PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        recordCount();
    }

    /**
     * Method to control the generation of ClientBroker.
     * @return ClientBroker constructor
     */
    public static ClientBroker getBroker()
    {
        if(cb == null)
        {
            try
            {
                cb = new ClientBroker();
            }
            catch (FileNotFoundException e)
            {
                e.printStackTrace();
            }
        }
        return cb;
    }
    /**
     * Method to write a client object into the binary file.
     * @param client client which will be written into the Binary file;
     * @throws IOException
     */
    private void writeRecord(Client client) throws IOException
    {
        try
        {
            String query = "INSERT INTO client (id, firstname, lastname, address, postalCode, phoneNumber, clientType) values (?,?,?,?,?,?,?)";
            PreparedStatement pStat = conn.prepareStatement(query);
            pStat.setLong(1, client.getId());
            pStat.setString(2, client.getFirstName());
            pStat.setString(3, client.getLastName());
            pStat.setString(4, client.getAddress());
            pStat.setString(5, client.getPostalCode());
            pStat.setString(6, client.getPhoneNum());
            pStat.setString(7, String.valueOf(client.getType()));

            int rowCount = pStat.executeUpdate();
            System.out.println("row Count = " + rowCount);
            pStat.close();
        }
        catch (SQLException e)
        {
                e.printStackTrace();
        }
    }
    /**
     * Method to close Broker and save all information back into the text file.
     */
    @Override
    public void closeBroker()
    {
        try
        {
            rs.close();
            conn.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
    /**
     * Method to save an object back into the binary file to later be saved into the text file.
     */
    @Override
    public boolean persist(Object arg0)
    {
        boolean flag = false;
        Client client = (Client)(arg0);
        try
        {
            if(client.getId()==0)
            {
                client.setId(maxId);
                maxId++;
                writeRecord(client);
                flag = true;
            }
            else
            {

                String query = "UPDATE client SET firstname = ?, lastname = ?, address = ?, postalCode = ?, phoneNumber = ?, clientType = ? WHERE id = ?";
                PreparedStatement pStat = conn.prepareStatement(query);

                pStat.setString(1, client.getFirstName());
                pStat.setString(2, client.getLastName());
                pStat.setString(3, client.getAddress());
                pStat.setString(4, client.getPostalCode());
                pStat.setString(5, client.getPhoneNum());
                pStat.setString(6, String.valueOf(client.getType()));
                pStat.setLong(7, client.getId());

                int rowCount = pStat.executeUpdate();
                System.out.println("row Count = " + rowCount);
                pStat.close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * Method to remove the object from the binary file.
     * @param Client object to be removed.
     */
    @Override
    public boolean remove(Object arg0) {
        boolean flag = false;
        Client client = (Client)(arg0);
        long id = client.getId();

        try {
            String delete = "DELETE FROM client WHERE id = ?";
            PreparedStatement pStat = conn.prepareStatement(delete);
            pStat.setLong(1, id);
            int rowCount = pStat.executeUpdate();
            System.out.println("row Count = " + rowCount);
            pStat.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return flag = true;
    }
    /**
     * Method to search determine which search criteria to search the binary file form.
     */
    @Override
    public List search(String item, String type) {
        try
        {
            switch(type)
            {
                case "id":
                    searchId(item);
                    break;
                case "type":
                    searchType(item);
                    break;
                case "name":
                    searchName(item);
                    break;
            }
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        return searchList;
    }
    /**
     * Method to search the binary file for an ID number.
     * @param item Id to be search for.
     * @return returns a list of clients with the matching ID number
     * @throws IOException
     */
    private List searchId(String item) throws IOException
    {
        Client client;
        searchList = new ArrayList<Client>();
        long searchedId = Long.parseLong(item);

        System.out.println(searchedId);
        try{
        String selectSQL = "SELECT id,firstname,lastname,address,postalCode,phoneNumber,clientType FROM client WHERE id = ?";
        PreparedStatement pStat = conn.prepareStatement(selectSQL);
        pStat.setLong(1, searchedId);
        rs = pStat.executeQuery();
            while (rs.next())
            {
                client = new Client(rs.getString("id")+";"+rs.getString("firstname")+";"+rs.getString("lastname")+";"+rs.getString("address")+";"+rs.getString("postalCode")+";"+rs.getString("phoneNumber")+";"+rs.getString("clientType"));
                searchList.add(client);
            }
        pStat.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return searchList;
    }
    /**
     * Method to search the binary file for a client type.
     * @param item type to be search for.
     * @return returns a list of clients with the matching client type.
     * @throws IOException
     */
    private List searchType(String item) throws IOException
    {
        Client client;
        searchList = new ArrayList<Client>();

        try{
        String selectSQL = "SELECT id,firstname,lastname,address,postalCode,phoneNumber,clientType FROM client WHERE clientType = ?";
        PreparedStatement pStat = conn.prepareStatement(selectSQL);
        pStat.setString(1, item);
        rs = pStat.executeQuery();
            while (rs.next())
            {
                client = new Client(rs.getString("id")+";"+rs.getString("firstname")+";"+rs.getString("lastname")+";"+rs.getString("address")+";"+rs.getString("postalCode")+";"+rs.getString("phoneNumber")+";"+rs.getString("clientType"));
                searchList.add(client);
            }
        pStat.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return searchList;
    }
    /**
     * Method to search the binary file for a Name.
     * @param item Name to be search for.
     * @return returns a list of clients with the matching Name.
     * @throws IOException
     */
    private List searchName(String item) throws IOException
    {
        System.out.println(item);
        Client client;
        searchList = new ArrayList<Client>();

        try{
        String selectSQL = "SELECT id,firstname,lastname,address,postalCode,phoneNumber,clientType FROM client WHERE lastname = ?";
        PreparedStatement pStat = conn.prepareStatement(selectSQL);
        pStat.setString(1, item);
        rs = pStat.executeQuery();
        System.out.println(rs.toString());
            while (rs.next())
            {
                System.out.println(rs.toString());
                client = new Client(rs.getString("id")+";"+rs.getString("firstname")+";"+rs.getString("lastname")+";"+rs.getString("address")+";"+rs.getString("postalCode")+";"+rs.getString("phoneNumber")+";"+rs.getString("clientType"));
                searchList.add(client);
            }
        pStat.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return searchList;
    }

    private void recordCount()
    {

        try {
            stmt = conn.createStatement();
            String query = "SELECT MAX(id) FROM client";
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                maxId = rs.getLong("MAX(id)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}