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) throws SQLException {
        DatabaseConnection Db = new DatabaseConnection();
        try (Connection con = Db.getConnection(connectionString, userName, password)) {
            List<Customer> customers = triplet.getCustomers();
            List<Vehicle> vehicles = triplet.getVehicles();
            List<Repair> repairs = triplet.getRepairs();
            con.setAutoCommit(false);
            final int batchSize = 1000;
            int count = 0;
            String callProcedureUpdateCustomer = "{call INSERT_OR_UPDATE_CUSTOMER(?,?,?,?,?,?,?)}";
            CallableStatement insertUpdateCustomer = con.prepareCall(callProcedureUpdateCustomer);
            for (Customer c : customers) {
                insertUpdateCustomer.setInt(1, c.getCuOldId());
                insertUpdateCustomer.setString(2, c.getCuName());
                insertUpdateCustomer.setString(3, c.getCuType().toString());
                insertUpdateCustomer.setDate(4, new java.sql.Date(c.getCuOriginDate().getTime()));
                insertUpdateCustomer.setDate(5, new java.sql.Date(c.getCuRegDate().getTime()));
                insertUpdateCustomer.setInt(6, c.getCuPhoneNumber());
                insertUpdateCustomer.setDate(7, new java.sql.Date(c.getCuFileDate().getTime()));
                insertUpdateCustomer.addBatch();
                if(++count % batchSize == 0) {
                    insertUpdateCustomer.executeBatch();
                }
                String callProcedureUpdateVehicle = "{call INSERT_OR_UPDATE_VEHICLE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
                CallableStatement insertUpdateVehicle = con.prepareCall(callProcedureUpdateVehicle);
                for(Vehicle v : c.getVehicles()) {
                    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();

                    String callProcedureUpdateRepair = "{call INSERT_OR_UPDATE_REPAIR(?,?,?,?,?,?)}";
                    CallableStatement insertUpdateRepair = con.prepareCall(callProcedureUpdateRepair);
                    for (Repair r : repairs) {
                        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.executeBatch();
                    insertUpdateRepair.close();
                }
                insertUpdateVehicle.executeBatch();
                insertUpdateVehicle.close();
            }
            insertUpdateCustomer.executeBatch();
            insertUpdateCustomer.close();

            con.setAutoCommit(true);
        }
    }
}