package com.cgi.crdb.dao;

import com.cgi.crdb.connection.DatabaseConnection;
import com.cgi.crdb.models.Customer;
import com.cgi.crdb.models.Repair;
import com.cgi.crdb.models.Vehicle;
import com.cgi.crdb.parser.Triplet;

import java.sql.*;
import java.util.List;

public class Dao {
    private String connectionString;
    private String userName;
    private String password;

    public Dao(String connectionString, String userName, String password) {
        this.connectionString = connectionString;
        this.userName = userName;
        this.password = password;
    }

    public void testConnection() throws SQLException {
        DatabaseConnection db = new DatabaseConnection();
        try (Connection con = db.getConnection(connectionString, userName, password)) {
            System.out.println("I am in!");
        }
    }

    public void insertIntoDatabase(Triplet triplet) {
        DatabaseConnection db = new DatabaseConnection();
        Connection con = null;
        try {
            con = db.getConnection(connectionString, userName, password);
            List<Customer> customers = triplet.getCustomers();
            List<Vehicle> vehicles = triplet.getVehicles();
            List<Repair> repairs = triplet.getRepairs();
            con.setAutoCommit(false);
            String selectCustomer = "SELECT CU_OLD_ID FROM CUSTOMER WHERE CU_OLD_ID=?";
            String insertCustomer = "INSERT INTO CUSTOMER VALUES (?,?,?,?,?,?,?)";
            String updateCustomer = "UPDATE CUSTOMER SET CU_NAME=?,CU_TYPE=?,CU_ORIGIN_DATE=?,CU_REG_DATE=?,CU_PHONE_NUMBER=? WHERE CU_OLD_ID = ?";
            PreparedStatement insertCustomerStatement = null;
            PreparedStatement selectCustomerStatement = null;
            PreparedStatement updateCustomerStatement = null;
            for (Customer c : customers) {
                selectCustomerStatement = con.prepareStatement(selectCustomer);
                selectCustomerStatement.setInt(1, c.getCuOldId());
                ResultSet id = selectCustomerStatement.executeQuery();
                if (id.next()) {
                    updateCustomerStatement = con.prepareStatement(updateCustomer);
                    updateCustomerStatement.setString(1, c.getCuName());
                    updateCustomerStatement.setString(2, c.getCuType().toString());
                    updateCustomerStatement.setDate(3, new java.sql.Date(c.getCuOriginDate().getTime()));
                    updateCustomerStatement.setDate(4, new java.sql.Date(c.getCuRegDate().getTime()));
                    updateCustomerStatement.setInt(5, c.getCuPhoneNumber());
                    updateCustomerStatement.setInt(6, c.getCuOldId());
                    updateCustomerStatement.executeUpdate();
                } else {
                    insertCustomerStatement = con.prepareStatement(insertCustomer);
                    insertCustomerStatement.setInt(1, c.getCuOldId());
                    insertCustomerStatement.setString(2, c.getCuName());
                    insertCustomerStatement.setString(3, c.getCuType().toString());
                    insertCustomerStatement.setDate(4, new java.sql.Date(c.getCuOriginDate().getTime()));
                    insertCustomerStatement.setDate(5, new java.sql.Date(c.getCuRegDate().getTime()));
                    insertCustomerStatement.setInt(6, c.getCuPhoneNumber());
                    insertCustomerStatement.setDate(7, new java.sql.Date(c.getCuFileDate().getTime()));
                    insertCustomerStatement.executeUpdate();
                }


//                if(++count % batchSize == 0) {
//                    insertUpdateCustomer.executeBatch();
//                }
            }
            //insertCustomerStatement.executeBatch();

            String callProcedureUpdateVehicle = "{call INSERT_OR_UPDATE_VEHICLE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
            CallableStatement insertUpdateVehicle = null;
            for(Vehicle v : vehicles) {
                insertUpdateVehicle = con.prepareCall(callProcedureUpdateVehicle);
                insertUpdateVehicle.setInt(1, v.getVehOldId());
                insertUpdateVehicle.setInt(2, v.getVehCuId());
                insertUpdateVehicle.setString(3, v.getVehVin());
                insertUpdateVehicle.setDate(4, new java.sql.Date(v.getVehRegDate().getTime()));
                insertUpdateVehicle.setString(5, v.getVehRegCountry());
                insertUpdateVehicle.setString(6, v.getVehManufacturer());
                insertUpdateVehicle.setString(7, v.getVehType());
                insertUpdateVehicle.setString(8, v.getVehBody());
                insertUpdateVehicle.setString(9, v.getVehEngine());
                insertUpdateVehicle.setString(10, v.getVehRestraint());
                insertUpdateVehicle.setString(11, v.getVehModel());
                insertUpdateVehicle.setInt(12, v.getVehYear());
                insertUpdateVehicle.setString(13, v.getVehPlant());
                insertUpdateVehicle.setString(14, v.getVehSerialNumber());
                insertUpdateVehicle.setDate(15, new java.sql.Date(v.getVehFileName().getTime()));
                insertUpdateVehicle.addBatch();

                //insertUpdateVehicle.addBatch();
//                if(++count % batchSize == 0) {
//                    insertUpdateVehicle.executeBatch();
//                }
            }
            insertUpdateVehicle.executeBatch();

            String callProcedureUpdateRepair = "{call INSERT_OR_UPDATE_REPAIR(?,?,?,?,?,?)}";
            CallableStatement insertUpdateRepair = null;
            for (Repair r : repairs) {
                insertUpdateRepair = con.prepareCall(callProcedureUpdateRepair);
                insertUpdateRepair.setInt(1, r.getReOldId());
                insertUpdateRepair.setInt(2, r.getReVehOldId());
                insertUpdateRepair.setInt(3, r.getReDetId());
                insertUpdateRepair.setInt(4, r.getReShopId());
                insertUpdateRepair.setDate(5, new java.sql.Date(r.getReBillDate().getTime()));
                insertUpdateRepair.setDate(6, new java.sql.Date(r.getReFileName().getTime()));
                insertUpdateRepair.addBatch();

                //insertUpdateRepair.addBatch();
//                if(++count % batchSize == 0) {
//                    insertUpdateRepair.executeBatch();
//                }
            }
            insertUpdateRepair.executeBatch();
            if (insertCustomerStatement != null) {
                insertCustomerStatement.close();
            }
            if (updateCustomerStatement != null) {
                updateCustomerStatement.close();
            }
            if (selectCustomerStatement != null) {
                selectCustomerStatement.close();
            }
            if (selectCustomerStatement != null) {
                insertUpdateVehicle.close();
            }
            if (selectCustomerStatement != null) {
                insertUpdateRepair.close();
            }

            con.commit();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }
}