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('メルク施設マスタ作成')


def exec():
    """メルク施設マスタ作成"""

    # 営業日ではないかつ、アルトマーク取込が行われていない場合は処理をスキップする
    if batch_context.is_not_business_day is True and batch_context.is_ultmarc_imported is not True:
        logger.info('営業日ではない、かつ、アルトマーク取込が行われていないため、メルク施設マスタ作成処理をスキップします。')
        return

    db = Database.get_instance()
    try:
        db.connect()
        logger.debug('メルク施設マスタ作成処理開始')
        # mst_instをTruncate
        _truncate_mst_inst(db)
        # fcl_mst_vから、mst_instへInsert
        _insert_mst_inst_from_fcl_mst_v(db)
        # com_instから、mst_instへInsert
        _insert_mst_inst_from_com_inst(db)
        logger.debug('メルク施設マスタ作成処理終了')
    except Exception as e:
        raise BatchOperationException(e)
    finally:
        db.disconnect()


def _truncate_mst_inst(db: Database):
    try:
        db.execute("TRUNCATE TABLE src05.mst_inst")
    except Exception as e:
        logger.debug("メルク施設マスタの全件削除に失敗")
        raise e

    logger.debug("メルク施設マスタの全件削除に成功")
    return


def _insert_mst_inst_from_fcl_mst_v(db: Database):
    # fcl_mst_vから、mst_instへInsert
    try:
        elapsed_time = ElapsedTime()
        sql = """
            INSERT INTO
                src05.mst_inst (
                    inst_cd,
                    inst_clas_cd,
                    inst_name_form,
                    inst_name,
                    pref_cd,
                    city_cd,
                    pref_name,
                    city_name,
                    address,
                    postal_cd,
                    tel_num,
                    delete_date,
                    v_inst_cd,
                    create_date,
                    update_date
                )
            SELECT
                fmv1.v_inst_cd,
                CASE
                    WHEN fmv1.fcl_type IN ('A1', 'A0') THEN '3'
                    WHEN fmv1.fcl_type BETWEEN '20' AND '29' THEN '2'
                END AS inst_clas_cd,
                fmv1.fcl_name,
                fmv1.fcl_abb_name,
                fmv1.prft_cd,
                RIGHT(fmv1.admin_kbn, 3),
                mp.prefc_name,
                LEFT(mc.city_name, 40),
                CASE
                    WHEN fmv1.fcl_type IN ('A1', 'A0') THEN LEFT(fmv1.fmt_addr, 200)
                    WHEN fmv1.fcl_type BETWEEN '20' AND '29' THEN CONCAT(fmv1.prft_name,fmv1.city_name,fmv1.addr_line_1)
                END AS address,
                fmv1.postal_cd,
                fmv1.tel_num,
                LEFT(fmv1.closed_dt, 10),
                fmv1.v_inst_cd,
                fmv1.ins_dt,
                fmv1.upd_dt
            FROM
                src05.fcl_mst_v AS fmv1
            INNER JOIN (
                SELECT
                    v_inst_cd,
                    MAX(sub_num) AS sno
                FROM
                    src05.fcl_mst_v
                GROUP BY
                    v_inst_cd
                ) fmv2
                ON  fmv1.v_inst_cd = fmv2.v_inst_cd
                AND fmv1.sub_num = fmv2.sno
            LEFT OUTER JOIN src05.mst_prefc AS mp
                ON fmv1.prft_cd = mp.prefc_cd
            LEFT OUTER JOIN src05.mst_city AS mc
                ON  LEFT(fmv1.admin_kbn, 2) = mc.prefc_cd
                AND RIGHT(fmv1.admin_kbn, 3) = mc.city_cd
            WHERE
                    ((fmv1.fcl_type IN ('A1', 'A0')) OR fmv1.fcl_type BETWEEN '20' AND '29')
                AND fmv1.rec_sts_kbn != '9'
        """
        res = db.execute(sql)
        logging_sql(logger, sql)
        logger.info(f'Ｖ施設マスタからメルク施設マスタに登録成功, {res.rowcount} 行更新 ({elapsed_time.of})')
    except Exception as e:
        logger.debug("Ｖ施設マスタからメルク施設マスタに登録失敗")
        raise e

    return


def _insert_mst_inst_from_com_inst(db: Database):
    # オプティマイザのderived_mergeフラグをoffにする
    try:
        sql = """
            SET SESSION optimizer_switch = 'derived_merge=off'
        """
        db.execute(sql)
        logger.debug("オプティマイザのderived_mergeフラグ = Off")
    except Exception as e:
        logger.debug("オプティマイザのderived_mergeフラグの値変更に失敗")
        raise e

    # com_instから、mst_instへInsert
    try:
        elapsed_time = ElapsedTime()
        sql = """
            INSERT INTO
                src05.mst_inst (
                    inst_cd,
                    inst_clas_cd,
                    inst_name_form,
                    inst_name,
                    pref_cd,
                    city_cd,
                    pref_name,
                    city_name,
                    address,
                    postal_cd,
                    tel_num,
                    bed_num,
                    manage_cd,
                    manage_name,
                    delete_date,
                    inst_div_cd,
                    inst_div_name,
                    v_inst_cd,
                    creater,
                    create_date,
                    updater,
                    update_date
                )
            SELECT
                ci.dcf_dsf_inst_cd,
                '1',
                ci.form_inst_name_kanji,
                ci.inst_name_kanji,
                ci.prefc_cd,
                ci.city_cd,
                mp.prefc_name,
                LEFT(mc.city_name, 40),
                ci.inst_addr,
                ci.postal_number,
                ci.inst_phone_number,
                ci.bed_num,
                ci.manage_cd,
                LEFT(cm.manage_name, 40),
                ci.abolish_ymd,
                ci.inst_div_cd,
                LEFT(cid.inst_div_name, 40),
                mcmv.hco_vid_v,
                ci.create_user,
                ci.regist_date,
                ci.update_user,
                ci.update_date
            FROM
                src05.com_inst AS ci
            LEFT OUTER JOIN src05.mst_prefc AS mp
                ON ci.prefc_cd = mp.prefc_cd
            LEFT OUTER JOIN src05.mst_city AS mc
                ON  ci.prefc_cd = mc.prefc_cd
                AND ci.city_cd = mc.city_cd
            LEFT OUTER JOIN src05.com_manage AS cm
                ON ci.manage_cd = cm.manage_cd
            LEFT OUTER JOIN src05.com_inst_div AS cid
                ON ci.inst_div_cd = cid.inst_div_cd
            LEFT OUTER JOIN (                                                  -- MDBコード変換表を使用してV施設コードを取得するためのテーブル結合
                SELECT
                    mcmv4.*
                FROM (
                    SELECT
                        mcmv1.*
                    FROM
                        src05.mdb_cnv_mst_v AS mcmv1
                    INNER JOIN (
                        SELECT
                            mcmv2.hco_vid_v,
                            MAX(mcmv2.sub_num) AS sno                          -- MDBコード変換データの枝番MAX
                        FROM
                            src05.mdb_cnv_mst_v mcmv2
                        WHERE
                                mcmv2.rec_sts_kbn != '9'                       -- 状態区分9（削除）以外 and 適用開始≦日付テーブル.処理日
                            AND src05.get_syor_date() >= mcmv2.start_date
                        GROUP BY
                            mcmv2.hco_vid_v
                    ) AS mcmv3
                    ON  mcmv1.hco_vid_v = mcmv3.hco_vid_v
                    AND mcmv1.sub_num = mcmv3.sno
                ) AS mcmv4
                INNER JOIN (
                    SELECT
                        MIN(mcmv8.hco_vid_v) AS hvv,                           -- 1つのMDBコードに対し、複数のV施設コードが割り当てられている場合、最小のV施設コードを選択する
                        mcmv8.mdb_cd
                    FROM (
                        SELECT
                            mcmv5.*
                        FROM
                            src05.mdb_cnv_mst_v AS mcmv5
                        INNER JOIN (
                            SELECT
                                mcmv6.hco_vid_v,
                                MAX(mcmv6.sub_num) AS sno                      -- MDBコード変換データの枝番MAX
                            FROM
                                src05.mdb_cnv_mst_v AS mcmv6
                            WHERE
                                    mcmv6.rec_sts_kbn != '9'                   -- 状態区分9（削除）以外 and 適用開始≦日付テーブル.処理日
                                AND src05.get_syor_date() >= mcmv6.start_date
                            GROUP BY
                                mcmv6.hco_vid_v
                        ) AS mcmv7
                        ON  mcmv5.hco_vid_v = mcmv7.hco_vid_v
                        AND mcmv5.sub_num = mcmv7.sno
                    ) AS mcmv8
                    GROUP BY
                        mcmv8.mdb_cd
                ) AS mcmv9
                ON  mcmv4.mdb_cd = mcmv9.mdb_cd
                AND mcmv4.hco_vid_v = mcmv9.hvv
            ) AS mcmv
            ON ci.dcf_dsf_inst_cd = mcmv.mdb_cd
        """
        res = db.execute(sql)
        logging_sql(logger, sql)
        logger.info(f'COM施設からメルク施設マスタに登録成功, {res.rowcount} 行更新 ({elapsed_time.of})')
    except Exception as e:
        logger.debug("COM施設からメルク施設マスタに登録失敗")
        raise e

    return
