from src.batch.batch_functions import logging_sql
from src.batch.common.batch_context import BatchContext
from src.db.database import Database
from src.error.exceptions import BatchOperationException
from src.logging.get_logger import get_logger
from src.time.elapsed_time import ElapsedTime

logger = get_logger('施設担当者マスタ洗替')
batch_context = BatchContext.get_instance()


def exec():
    db = Database.get_instance()
    try:
        db.connect()
        logger.debug('##########################')
        logger.debug('START Changing Employee in charge of institution PGM.')
        # `emp_chg_inst_lau`をTruncate
        _truncate_emp_chg_inst_lau(db)
        # emp_chg_inst から、`emp_chg_inst_lau`へInsert
        _insert_into_emp_chg_inst_lau_from_emp_chg_inst(db)
        # v_inst_merge_tから、emp_chg_inst_lauをUpdate
        _update_emp_chg_inst_lau_from_v_inst_merge_t(db)
        # inst_merge_tから、emp_chg_inst_lauをUpdate
        _update_emp_chg_inst_lau_from_inst_merge_t(db)
        logger.debug('##########################')
        logger.debug('End All Processing PGM.')
    except Exception as e:
        raise BatchOperationException(e)
    finally:
        db.disconnect()


def _truncate_emp_chg_inst_lau(db: Database):
    logger.debug("##########################")
    try:
        db.execute("TRUNCATE TABLE src05.emp_chg_inst_lau")
    except Exception as e:
        logger.debug("Error! Truncate Table `emp_chg_inst_lau` is Failed!!!")
        raise e

    logger.debug("Table `emp_chg_inst_lau` was truncated!")
    return


def _insert_into_emp_chg_inst_lau_from_emp_chg_inst(db: Database):
    logger.debug("##########################")
    try:
        elapsed_time = ElapsedTime()
        sql = """
            INSERT INTO
                src05.emp_chg_inst_lau
            SELECT
                inst_cd,
                ta_cd,emp_cd,
                bu_cd,
                start_date,
                end_date,
                main_chg_flg,
                enabled_flg,
                creater,
                create_date,
                updater,
                update_date,
                NULL -- lua_ope_dt
            FROM
                src05.emp_chg_inst
            WHERE
                enabled_flg = 'Y'
        """
        res = db.execute(sql)
        logging_sql(logger, sql)
        logger.info(f'Query OK, {res.rowcount} rows affected ({elapsed_time.of})')
    except Exception as e:
        logger.debug("Error! Insert into `emp_chg_inst_lau` from `emp_chg_inst` was failed!!!")
        raise e
    logger.debug("Success! Insert into `emp_chg_inst_lau` from `emp_chg_inst` was inserted!")

    return


def _update_emp_chg_inst_lau_from_v_inst_merge_t(db: Database):
    # v_inst_merge_tの元となるvop_hco_merge_vはデータが作られないため、この洗い替え処理は基本空振りする
    logger.debug("##########################")
    try:
        select_result = db.execute_select(
            """
            SELECT
                COUNT(v_inst_cd) AS row_count
            FROM
                internal05.v_inst_merge_t
            """
        )
    except Exception as e:
        logger.debug("Error! `v_inst_merge_t` Table count error!")
        raise e
    count = [row for row in select_result][0]['row_count']
    if count == 0:
        logger.info('v_inst_merge_t Table Data is not exists!')
        return

    logger.info('v_inst_merge_t Table Data is exists!')
    # v_inst_merge_t から、emp_chg_inst_lauをUpdateします
    logger.debug("##########################")
    logger.debug("#### UPDATE DATA #########")
    logger.debug("##########################")
    try:
        elapsed_time = ElapsedTime()
        update_sql = """
            UPDATE
                src05.emp_chg_inst_lau el, internal05.v_inst_merge_t vimt
            SET
                el.inst_cd = vimt.v_inst_cd_merge,
                el.lua_ope_dt = SYSDATE()
            WHERE
                el.inst_cd = vimt.v_inst_cd
        """
        res = db.execute(update_sql)
        logging_sql(logger, update_sql)
        logger.info(f'Query OK, {res.rowcount} rows affected ({elapsed_time.of})')
    except Exception as e:
        logger.debug("emp_chg_inst_lau.v_inst_cd could not set!")
        raise e

    logger.debug("emp_chg_inst_lau.v_inst_cd was set!")

    return


def _update_emp_chg_inst_lau_from_inst_merge_t(db: Database):
    # inst_merge_tから、emp_chg_inst_lauをUpdate
    # Get count from INST_MERGE_T
    logger.debug("##########################")
    try:
        select_result = db.execute_select(
            """
            SELECT
                COUNT(dcf_dsf_inst_cd) AS row_count
            FROM
                internal05.inst_merge_t
            """
        )
    except Exception as e:
        logger.debug("Error! Getting Count of internal05.inst_merge_t was failed!")
        raise e

    count = [row for row in select_result][0]['row_count']
    if count == 0:
        logger.info('inst_merge_t Table Data is not exists!')
        return

    logger.debug('inst_merge_t Table Data is exists!')
    # inst_merge_tから、emp_chg_inst_lauをUpdate
    logger.debug("##########################")
    logger.debug("#### UPDATE DATA #########")
    logger.debug("##########################")
    try:
        elapsed_time = ElapsedTime()
        update_sql = """
            UPDATE
                src05.emp_chg_inst_lau el, internal05.inst_merge_t imt
            SET
                el.inst_cd = imt.dup_opp_cd,
                el.lua_ope_dt = SYSDATE()
            WHERE
                el.inst_cd = imt.dcf_dsf_inst_cd
        """
        res = db.execute(update_sql)
        logging_sql(logger, update_sql)
        logger.info(f'Query OK, {res.rowcount} rows affected ({elapsed_time.of})')
    except Exception as e:
        logger.debug("emp_chg_inst_lau.v_inst_cd could not set!")
        raise e

    logger.debug("emp_chg_inst_lau.v_inst_cd was set!")

    return
