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

batch_context = BatchContext.get_instance()
logger = get_logger('DCF施設統合マスタ作成')


def exec():
    """DCF施設統合マスタ作成"""

    # アルトマーク取込が行われていない場合は処理をスキップする
    if not batch_context.is_ultmarc_imported:
        logger.info('アルトマーク取込が行われていないため、DCF施設統合マスタ作成処理をスキップします。')
        return

    db = Database.get_instance()
    try:
        db.connect()
        db.begin()
        logger.debug('DCF施設統合マスタ作成処理開始')
        # COM施設からDCF施設統合マスタに登録
        (is_add_dcf_inst_merge, duplication_inst_records) = _insert_dcf_inst_merge_from_com_inst(db)
        db.commit()
        # DCF施設統合マスタ追加のログを出力する
        if is_add_dcf_inst_merge:
            logger.info('[NOTICE]DCF施設統合マスタが追加されました。')
            _output_add_dcf_inst_merge_log(duplication_inst_records)
        logger.debug('DCF施設統合マスタ作成処理終了')
    except Exception as e:
        db.rollback()
        raise BatchOperationException(e)
    finally:
        db.disconnect()


def _insert_dcf_inst_merge_from_com_inst(db: Database) -> tuple[bool, list[dict]]:
    # com_instからdcf_inst_mergeにinsert
    # 重複コードがあるデータを取得する(処理日～システム日付を対象)
    try:
        sql = """
            SELECT
                ci.dcf_dsf_inst_cd AS dcf_dsf_inst_cd,
                ci.form_inst_name_kanji AS form_inst_name_kanji,
                ci.dup_opp_cd AS dup_opp_cd,
                (
                    SELECT
                        dupci.form_inst_name_kanji
                    FROM
                        src05.com_inst AS dupci
                    WHERE
                        dupci.dcf_dsf_inst_cd = ci.dup_opp_cd
                ) AS dup_inst_name_kanji,
                DATE_FORMAT((src05.get_syor_date() + INTERVAL 1 MONTH), '%Y%m') AS sys_update_date
            FROM
                src05.com_inst AS ci
            WHERE
                    CHAR_LENGTH(ci.dup_opp_cd) > 0
                AND ci.delete_sche_reason_cd = 'D'
                AND ci.abolish_ymd IS NULL
                AND ci.sys_update_date BETWEEN src05.get_syor_date() AND SYSDATE()
                AND NOT EXISTS (
                    SELECT
                        dim.dcf_inst_cd
                    FROM
                        src05.dcf_inst_merge AS dim
                    WHERE
                        dim.dcf_inst_cd = ci.dcf_dsf_inst_cd
                )
                AND (
                    EXISTS (
                        SELECT
                            eci.inst_cd
                        FROM
                            src05.emp_chg_inst AS eci
                        WHERE
                            eci.inst_cd = ci.dcf_dsf_inst_cd
                    )
                    OR (
                        SELECT
                            sl.inst_cd
                        FROM
                            src05.sales_lau AS sl
                        WHERE
                            sl.inst_cd = ci.dcf_dsf_inst_cd
                    )
                )
        """
        duplication_inst_records = db.execute_select(sql)
        logging_sql(logger, sql)
        logger.info('施設統合対象データの取得に成功')
    except Exception as e:
        logger.debug('施設統合対象データの取得に失敗')
        raise e

    if len(duplication_inst_records) == 0:
        logger.info('施設統合対象データはありません')
        return (False, None)

    # DCF施設統合マスタ追加
    values_clauses = []
    params = {}
    for clauses_no, row in enumerate(duplication_inst_records, start=1):
        dcf_inst_cd_arr = f'dcf_inst_cd{clauses_no}'
        dup_opp_cd_arr = f'dup_opp_cd{clauses_no}'
        tekiyo_month_arr = f'tekiyo_month{clauses_no}'
        values_clause = f'(:{dcf_inst_cd_arr}, :{dup_opp_cd_arr}, :{tekiyo_month_arr}, "Y", CURRENT_USER(), SYSDATE(), CURRENT_USER(), SYSDATE())'
        values_clauses.append(values_clause)
        params[dcf_inst_cd_arr] = row['dcf_dsf_inst_cd']
        params[dup_opp_cd_arr] = row['dup_opp_cd']
        params[tekiyo_month_arr] = row['sys_update_date']

    insert_sql = f"""
        INSERT INTO
            src05.dcf_inst_merge (
                dcf_inst_cd,
                dup_opp_cd,
                tekiyo_month,
                enabled_flg,
                creater,
                create_date,
                updater,
                update_date
            ) VALUES
            {','.join(values_clauses)}
    """

    try:
        elapsed_time = ElapsedTime()
        res = db.execute(insert_sql, params)
        logging_sql(logger, insert_sql)
        logger.info(f'COM施設からDCF施設統合マスタに登録成功, {res.rowcount} 行更新 ({elapsed_time.of})')
    except Exception as e:
        logger.debug('COM施設からDCF施設統合マスタの登録に失敗')
        raise e

    return (True, duplication_inst_records)


def _output_add_dcf_inst_merge_log(duplication_inst_records: list[dict]):
    sys_update_date = duplication_inst_records[0]['sys_update_date']
    set_year_month = '{set_year}年{set_month}月'.format(
        set_year=sys_update_date[0:4],
        set_month=sys_update_date[-2:]
    )

    add_dct_inst_merge = 'DCF施設コード　{dcf_dsf_inst_cd}　{form_inst_name_kanji}, 　重複時相手先コード　{dup_opp_cd}　{dup_inst_name_kanji}'
    add_dct_inst_merge_list = []
    for row in duplication_inst_records:
        add_dct_inst_merge_list.append(add_dct_inst_merge.format(**row))
    add_dct_inst_merge_list = '\n'.join(add_dct_inst_merge_list)

    # 顧客報告用にログ出力
    logger.info(
        f"""DCF施設統合マスタが追加されました。
**********************************************************
適用月度　{set_year_month}
**********************************************************
{add_dct_inst_merge_list}
**********************************************************
合計　{len(duplication_inst_records)}件"""
    )
    return
