package com.bankaudit.dao;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.model.ExcelMaintEntity;
import org.hibernate.Session;
import org.jboss.logging.Logger;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.List;

@Repository("excelMaintEntityDao")
public class ExcelMaintEntityDaoImpl extends AbstractDao implements ExcelMaintEntityDao {

    static final Logger logger = Logger.getLogger(ExcelMaintEntityDaoImpl.class);

    @Override
    public void updateTempMaintEntity(Integer uploadDocId) {
        try {
            Session session = getSession();
            // Update query for State Code
            String updateStateCodeSQL = "UPDATE temp_maint_entity t " +
                    "JOIN maint_address c ON t.state_name = c.state_name " +
                    "SET t.state_code = c.state_code";

            session.createNativeQuery(updateStateCodeSQL).executeUpdate();
            session.flush();
            System.out.println("State Code updated in the temporary table successfully.");

            // Update query for Country Code
            String updateCountryCodeSQL = "UPDATE temp_maint_entity t " +
                    "JOIN maint_address c ON t.country_name = c.country_name " +
                    "SET t.country_code = c.country_alpha3_code";
            session.createNativeQuery(updateCountryCodeSQL).executeUpdate();
            session.flush();
            System.out.println("Country Code updated in the temporary table successfully.");

            // Update query for District Code
            String updateDistrictCodeSQL = "UPDATE temp_maint_entity t " +
                    "JOIN maint_address c ON t.district_name = c.district_name " +
                    "SET t.district_code = c.district_code";
            session.createNativeQuery(updateDistrictCodeSQL).executeUpdate();
            session.flush();
            System.out.println("District Code updated in the temporary table successfully.");

            // Update query for City Code
            String updateCityCodeSQL = "UPDATE temp_maint_entity t " +
                    "JOIN maint_address c ON t.city_name = c.city_name " +
                    "SET t.city_code = c.city_code";
            session.createNativeQuery(updateCityCodeSQL).executeUpdate();
            session.flush();
            System.out.println("City Code updated in the temporary table successfully.");

            // Update query for Unit Type Code
            String updateUnitTypeCodeSQL = "UPDATE temp_maint_entity t " +
                    "JOIN general_parameter g ON g.key_1='UNIT_TYPE' AND t.unit_type_name = g.description " +
                    "SET t.unit_type_ofc = g.value";
            session.createNativeQuery(updateUnitTypeCodeSQL).executeUpdate();
            session.flush();
            System.out.println("Unit Type Code updated in the temporary table successfully.");

            // Update query for Classification Code
            String updateClassificationCodeSQL = "UPDATE temp_maint_entity t " +
                    "JOIN general_parameter g ON g.key_1='CLASSIFICATION' AND t.classification_name = g.description " +
                    "SET t.classification_code = g.value";
            session.createNativeQuery(updateClassificationCodeSQL).executeUpdate();
            session.flush();
            System.out.println("Classification Code updated in the temporary table successfully.");

            // Update query for Weekly and Fortnight Code
            String updateFortnightCodeSQL = "UPDATE temp_maint_entity t " +
                    "JOIN general_parameter g ON g.key_1='FORTNIGHT' AND t.fortnightly_holiday_name = g.description " +
                    "SET t.fortnightly_holiday_code = g.value";
            session.createNativeQuery(updateFortnightCodeSQL).executeUpdate();
            session.flush();
            System.out.println("Weekly and Fortnight Code updated in the temporary table successfully.");

            // Update query for Type of Entity Code

            String updateTypeOfEntityCodeSQL = "UPDATE temp_maint_entity t " +
                    "JOIN general_parameter g ON g.key_1='TYPE_OF' AND g.key_2='ENTITY' AND t.type_of_entity_name = g.description " +
                    "SET t.type_of_entity_code = g.value";
            session.createNativeQuery(updateTypeOfEntityCodeSQL).executeUpdate();
            session.flush();
            System.out.println("Type of Entity Code updated in the temporary table successfully.");

            // Update query for Entity Status Code
            String updateEntityStatusCodeSQL = "UPDATE temp_maint_entity t " +
                    "JOIN general_parameter g ON g.key_1='ENTITY_STATUS' AND t.entity_status = g.description " +
                    "SET t.entity_status_code = g.value";
            session.createNativeQuery(updateEntityStatusCodeSQL).executeUpdate();
            session.flush();
            System.out.println("Entity Status Code updated in the temporary table successfully.");

            // Update query for Status Code
            String updateStatusCodeSQL = "UPDATE temp_maint_entity t " +
                    "JOIN general_parameter g ON g.key_1='STATUS' AND g.key_2='STATUS' AND t.status = g.description " +
                    "SET t.status_code = g.value";
            session.createNativeQuery(updateStatusCodeSQL).executeUpdate();
            session.flush();
            System.out.println("Status Code updated in the temporary table successfully.");

            String insertDataMaintEnitySQL = "INSERT INTO maint_entity ("
                    + "legal_entity_code, level_code, unit_code, parent_unit_code, unit_name, "
                    + "address1, address2, address3, city, pincode, "
                    + "district, state, country, "
                    + "contact_person, landline1, landline2, mobile1, "
                    + "mobile2, mailid, u_criticality, unit_type, "
                    + "classification, weekly_holiday, "
                    + "fortnightly_holiday, "
                    + "unit_open_date, unit_head_user, unit_head_inception_date, "
                    + "type_of_entity, entity_status, status, maker, maker_timestamp, checker, checker_timestamp, businessdate_timestamp, sysdate_timestamp ) "
                    + "SELECT "
                    + "legal_entity_code, level_code, unit_code, parent_unit_code, unit_name, "
                    + "address1, address2, address3, city_code, pincode, "
                    + "district_code, state_code, country_code, "
                    + "contact_person, landline1, landline2, mobile1, "
                    + "mobile2, mailid, u_criticality, unit_type_ofc, "
                    + "classification_code, weekly_holiday_code, "
                    + "fortnightly_holiday_code, "
                    + "unit_open_date, unit_head_user, unit_head_inception_date, "
                    + "type_of_entity_code, entity_status_code, status_code, maker, maker_timestamp, checker, checker_timestamp, businessdate_timestamp, sysdate_timestamp "
                    + "FROM temp_maint_entity where uploaded_id = :uploadDocId";
            session.createNativeQuery(insertDataMaintEnitySQL)
                    .setParameter("uploadDocId", uploadDocId).executeUpdate();
            System.out.println("Finally Data inserted into the maint_entity table successfully.");
            session.flush();

        } catch (Exception e) {
            e.printStackTrace(System.out);
        }
    }

    @Override
    public List<ExcelMaintEntity> getExcelMaintData(String legalEntityCode, String userId) {
        Session session=getSession();
        session.setDefaultReadOnly(true);
        logger.info("get Excel Total data...." + userId);
        List<ExcelMaintEntity> excelMaintEntityList = session.createQuery(" from ExcelMaintEntity ex ").list();
        logger.info("After get Excel Total data...." + userId);
        return excelMaintEntityList;
    }

    @Override
    public void deletePreviousExcelEntityData() {
        Session session=getSession();
        String deleteQuery = "delete from excel_maint_entity";
        session.createNativeQuery(deleteQuery).executeUpdate();
    }

}
