from src.batch.ultmarc.utmp_tables.table_mapper.ultmarc_table_mapper import \
    UltmarcTableMapper
from src.batch.ultmarc.utmp_tables.tables.com_sp_field import ComSpField


class ComSpFieldMapper(UltmarcTableMapper):
    """レイアウト区分511: COM_専門分野 登録処理"""

    # レコード存在確認SQL
    RECORD_EXISTS_QUERY = """\
    SELECT
        COUNT(*) AS count_num
    FROM
        src05.com_sp_field
    WHERE
        dcf_pcf_dr_cd = :dcf_pcf_dr_cd
        and
        specialist_cd = :specialist_cd
    """
    # データ登録用SQL
    INSERT_QUERY = """\
    INSERT INTO src05.com_sp_field
    (
        dcf_pcf_dr_cd,
        specialist_cd,
        specialist_maint_div,
        specialist_flg,
        specialist_publsh_ymd,
        ackn_med_flg,
        ackn_med_publsh_ymd,
        guide_med_flg,
        guide_med_publsh_ymd,
        regist_ymd,
        delete_ymd,
        sys_regist_date,
        regist_prgm_id,
        sys_update_date,
        update_prgm_id
    )
    VALUES (
        :dcf_pcf_dr_cd,
        :specialist_cd,
        :specialist_maint_div,
        :specialist_flg,
        :specialist_publsh_ymd,
        :ackn_med_flg,
        :ackn_med_publsh_ymd,
        :guide_med_flg,
        :guide_med_publsh_ymd,
        :execute_date_str_ymd,
        NULL,
        :execute_datetime,
        :program_name,
        :execute_datetime,
        :program_name
    )
    """

    UPDATE_QUERY = """\
    UPDATE
        src05.com_sp_field
    SET
        {update_columns}
        update_ymd = :execute_date_str_ymd,
        sys_update_date = :execute_datetime,
        update_prgm_id = :program_name
    WHERE
        dcf_pcf_dr_cd = :dcf_pcf_dr_cd
        and
        specialist_cd = :specialist_cd
    """

    # 『修正区分がB(修正)かつ専門医メンテナンス区分が1(退職)』の場合、物理削除
    PHYSICAL_DELETE_QUERY = """\
    DELETE FROM
        src05.com_sp_field
    WHERE
        dcf_pcf_dr_cd = :dcf_pcf_dr_cd
        and
        specialist_cd = :specialist_cd
    """
    record: ComSpField

    def __init__(self, record: list[str], db) -> None:
        super().__init__(record, db, ComSpField)
        program_name = __name__.split('.')[-1]  # 当モジュール名(現行から変わっている)
        # モジュール名をクエリパラメータに設定
        self.query_parameter['program_name'] = program_name
        # 読み込んだレコード値もクエリパラメータに追加
        self.query_parameter = {**self.query_parameter, **self.record.to_sql_parameter()}

    def make_query(self):
        # 『修正区分がB(修正)かつ専門医メンテナンス区分が1(退職)』の場合、物理削除
        if self.record.maint_flag == 'B' and self.record.specialist_maint_div == '1':
            self.queries.append(self.PHYSICAL_DELETE_QUERY)
            return

        # 追加、更新の場合
        self.queries.append(self.__make_upsert_query())
        return

    def __make_upsert_query(self):
        set_clauses = []  # 設定項目

        # レコードの存在確認
        record_count = self.db.execute_select(self.RECORD_EXISTS_QUERY, self.query_parameter)
        # 存在しない場合はInsert
        if record_count[0]['count_num'] == 0:
            return self.INSERT_QUERY

        # 存在する場合ではUpdate
        if len(self.record.specialist_maint_div) > 0:
            set_clauses.append('specialist_maint_div = :specialist_maint_div')

        if len(self.record.specialist_flg) > 0:
            set_clauses.append('specialist_flg = :specialist_flg')
            set_clauses.append('specialist_publsh_ymd = :specialist_publsh_ymd')
            if self.record.specialist_flg == '@':
                self.query_parameter['specialist_flg'] = None
                self.query_parameter['specialist_publsh_ymd'] = None

        if len(self.record.ackn_med_flg) > 0:
            set_clauses.append('ackn_med_flg = :ackn_med_flg')
            set_clauses.append('ackn_med_publsh_ymd = :ackn_med_publsh_ymd')
            if self.record.ackn_med_flg == '@':
                self.query_parameter['ackn_med_flg'] = None
                self.query_parameter['ackn_med_publsh_ymd'] = None

        if len(self.record.guide_med_flg) > 0:
            set_clauses.append('guide_med_flg = :guide_med_flg')
            set_clauses.append('guide_med_publsh_ymd = :guide_med_publsh_ymd')
            if self.record.guide_med_flg == '@':
                self.query_parameter['guide_med_flg'] = None
                self.query_parameter['guide_med_publsh_ymd'] = None

        update_columns = ','.join(set_clauses)
        # 何も更新項目が無い場合はNoneとする（更新処理は行わない）
        if len(update_columns) == 0:
            return None
        else:
            # 末尾にカンマを付けてSET句を完成させる
            update_columns += ','
            update_query = self.UPDATE_QUERY.format(
                update_columns=update_columns
            )
            return update_query
