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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * Contains logic of inserting data into database
 */
public class Dao {
    private Logger log = LoggerFactory.getLogger(Dao.class.getName());
    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;
    }

    /**
     * Methods inserts files Customer, Vehicle and Repair into database.
     * @param triplet Triplet to insert into database
     */
    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 = ?";
            for (Customer c : customers) {
                PreparedStatement selectCustomerStatement = con.prepareStatement(selectCustomer);
                selectCustomerStatement.setInt(1, c.getCuOldId());
                ResultSet id = selectCustomerStatement.executeQuery();
                if (id.next()) {
                    PreparedStatement 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();
                    updateCustomerStatement.close();
                    id.close();
                } else {
                    PreparedStatement 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();
                    insertCustomerStatement.close();
                }
                selectCustomerStatement.close();
            }

            String selectVehicle = "SELECT VEH_OLD_ID FROM VEHICLE WHERE VEH_OLD_ID=?";
            String insertVehicle = "INSERT INTO VEHICLE VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            String updateVehicle = "UPDATE VEHICLE SET VEH_CU_ID=?,VEH_VIN=?,VEH_REG_DATE=?,VEH_REG_COUNTRY=?,VEH_MANUFACTURER=?,VEH_TYPE=?,VEH_BODY=?,VEH_ENGINE=?,VEH_RESTRAINT=?,VEH_MODEL=?,VEH_YEAR=?,VEH_PLANT=?,VEH_SERIAL_NUMBER=? WHERE VEH_OLD_ID=?";
            for(Vehicle v : vehicles) {
                PreparedStatement selectVehicleStatement = con.prepareStatement(selectVehicle);
                selectVehicleStatement.setInt(1, v.getVehOldId());
                ResultSet id = selectVehicleStatement.executeQuery();
                if (id.next()) {
                    PreparedStatement updateVehicleStatement = con.prepareCall(updateVehicle);
                    updateVehicleStatement.setInt(1, v.getVehCuId());
                    updateVehicleStatement.setString(2, v.getVehVin());
                    updateVehicleStatement.setDate(3, new java.sql.Date(v.getVehRegDate().getTime()));
                    updateVehicleStatement.setString(4, v.getVehRegCountry());
                    updateVehicleStatement.setString(5, v.getVehManufacturer());
                    updateVehicleStatement.setString(6, v.getVehType());
                    updateVehicleStatement.setString(7, v.getVehBody());
                    updateVehicleStatement.setString(8, v.getVehEngine());
                    updateVehicleStatement.setString(9, v.getVehRestraint());
                    updateVehicleStatement.setString(10, v.getVehModel());
                    updateVehicleStatement.setInt(11, v.getVehYear());
                    updateVehicleStatement.setString(12, v.getVehPlant());
                    updateVehicleStatement.setString(13, v.getVehSerialNumber());
                    updateVehicleStatement.setInt(14, v.getVehOldId());
                    updateVehicleStatement.executeUpdate();
                    updateVehicleStatement.close();
                    id.close();
                } else {
                    PreparedStatement insertVehicleStatement = con.prepareCall(insertVehicle);
                    insertVehicleStatement.setInt(1, v.getVehOldId());
                    insertVehicleStatement.setInt(2, v.getVehCuId());
                    insertVehicleStatement.setString(3, v.getVehVin());
                    insertVehicleStatement.setDate(4, new java.sql.Date(v.getVehRegDate().getTime()));
                    insertVehicleStatement.setString(5, v.getVehRegCountry());
                    insertVehicleStatement.setString(6, v.getVehManufacturer());
                    insertVehicleStatement.setString(7, v.getVehType());
                    insertVehicleStatement.setString(8, v.getVehBody());
                    insertVehicleStatement.setString(9, v.getVehEngine());
                    insertVehicleStatement.setString(10, v.getVehRestraint());
                    insertVehicleStatement.setString(11, v.getVehModel());
                    insertVehicleStatement.setInt(12, v.getVehYear());
                    insertVehicleStatement.setString(13, v.getVehPlant());
                    insertVehicleStatement.setString(14, v.getVehSerialNumber());
                    insertVehicleStatement.setDate(15, new java.sql.Date(v.getVehFileName().getTime()));
                    insertVehicleStatement.executeUpdate();
                    insertVehicleStatement.close();
                }
                selectVehicleStatement.close();
            }

            String selectRepair = "SELECT RE_OLD_ID FROM REPAIR WHERE RE_OLD_ID=?";
            String insertRepair = "INSERT INTO REPAIR VALUES (?,?,?,?,?,?)";
            String updateRepair = "UPDATE REPAIR SET RE_VEH_OLD_ID=?,RE_RD_ID=?,RE_RS_ID=?,RE_BILL_DATE=? WHERE RE_OLD_ID=?";
            for (Repair r : repairs) {
                PreparedStatement selectRepairStatement = con.prepareStatement(selectRepair);
                selectRepairStatement.setInt(1, r.getReOldId());
                ResultSet id = selectRepairStatement.executeQuery();
                if (id.next()) {
                    PreparedStatement updateRepairStatement = con.prepareCall(updateRepair);
                    updateRepairStatement.setInt(1, r.getReVehOldId());
                    updateRepairStatement.setInt(2, r.getReDetId());
                    updateRepairStatement.setInt(3, r.getReShopId());
                    updateRepairStatement.setDate(4, new java.sql.Date(r.getReBillDate().getTime()));
                    updateRepairStatement.setInt(5, r.getReOldId());
                    updateRepairStatement.executeUpdate();
                    updateRepairStatement.close();
                    id.close();
                } else {
                    PreparedStatement insertRepairStatement = con.prepareCall(insertRepair);
                    insertRepairStatement.setInt(1, r.getReOldId());
                    insertRepairStatement.setInt(2, r.getReVehOldId());
                    insertRepairStatement.setInt(3, r.getReDetId());
                    insertRepairStatement.setInt(4, r.getReShopId());
                    insertRepairStatement.setDate(5, new java.sql.Date(r.getReBillDate().getTime()));
                    insertRepairStatement.setDate(6, new java.sql.Date(r.getReFileName().getTime()));
                    insertRepairStatement.executeUpdate();
                    insertRepairStatement.close();
                }
                selectRepairStatement.close();
            }
            log.info("All data have been successfully inserted into the database");
            con.commit();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("Could not create connection with the database");
            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException e1) {
                    log.error("There's been an error with rollback");
                }
            }
        }
    }
}