from src.batch.ultmarc.utmp_tables.table_mapper.ultmarc_table_mapper import \
    UltmarcTableMapper
from src.batch.ultmarc.utmp_tables.tables.com_dr import ComDr


class ComDrMapper(UltmarcTableMapper):
    """レイアウト区分501: COM_医師 登録処理"""

    # レコード存在確認SQL
    RECORD_EXISTS_QUERY = """\
    SELECT
        COUNT(*) AS count_num
    FROM
        src05.com_dr
    WHERE
        dcf_pcf_dr_cd = :dcf_pcf_dr_cd
    """

    # データ登録用SQL
    INSERT_QUERY = """\
    INSERT INTO src05.com_dr
    (
        dcf_pcf_dr_cd,
        dr_name_kana,
        dr_name,
        birthday_era,
        birthday_year,
        birthday_month,
        birthday_day,
        birthday,
        hometown_cd,
        estab_era,
        estab_year,
        estab_y,
        home_phone_number,
        home_addr_kana,
        home_addr,
        home_postal_number,
        addr_village_cd,
        prefc_cd,
        city_cd,
        addr_display_number,
        addr_cnt_kana,
        addr_cnt,
        dr_circle_cd,
        estab_div_cd,
        sex_cd,
        delete_sche_reason_cd,
        addr_unknown_reason_cd,
        alma_cd,
        depart_disc_cd,
        grad_era,
        grad_year,
        grad_y,
        lump_regist_flg,
        opp_dup_cd,
        dr_ph_div,
        use_stop_div,
        use_stop_reason_cd,
        use_stop_regist_ymd,
        use_stop_cancel_ymd,
        drday_era,
        drday_year,
        drday_y,
        abolish_ymd,
        delete_flg,
        sys_regist_date,
        regist_prgm_id,
        sys_update_date,
        update_prgm_id
    )
    VALUES (
        :dcf_pcf_dr_cd,
        :dr_name_kana,
        :dr_name,
        :birthday_era,
        :birthday_year,
        :birthday_month,
        :birthday_day,
        :birth_day,
        :home_town_code,
        :pract_yearera,
        :pract_year,
        :estab_y,
        :dr_tel,
        :dr_addr_kana,
        :dr_addr,
        :dr_zip_code,
        :addr_village_cd,
        :pref_code,
        :city_code,
        :dr_addr_num,
        :addr_cnt_kana,
        :addr_cnt,
        :medassoci_code,
        :pract_class_code,
        :sex_code,
        :drdel_code,
        :dr_addr_lost_code,
        :graduniv_code,
        :graduniv_dept_code,
        :grad_yearera,
        :grad_year,
        :grad_y,
        :bskregst_flag,
        :opp_dup_code,
        1,
        :use_stop_flag,
        :use_stopc_ode,
        :cre_stop_date,
        :release_date,
        :drda_yera,
        :drday_year,
        :drday_y,
        NULL,
        0,
        :execute_datetime,
        :program_name,
        :execute_datetime,
        :program_name
    )
    """

    UPDATE_QUERY = """\
    UPDATE
        src05.com_dr
    SET
        {update_columns}
        dr_ph_div = 1,
        abolish_ymd = NULL,
        delete_flg = 0,
        sys_update_date = :execute_datetime,
        update_prgm_id = :program_name
    WHERE
        dcf_pcf_dr_cd = :dcf_pcf_dr_cd
    """

    # 修正区分が「C(削除)」の場合の更新SQL
    LOGICAL_DELETE_QUERY = """\
    UPDATE
        src05.com_dr
    SET
        abolish_ymd = :maint_date,
        sys_update_date = :execute_date_str_ymd,
        update_prgm_id = :program_name
    WHERE
        dcf_pcf_dr_cd = :dcf_pcf_dr_cd
    """

    # 西暦取得SQL
    YEAR_GET_QUERY = """\
    SELECT
        year AS year
    FROM
        src05.com_era
    WHERE
        era_cd = :era_cd
    """

    # COM_医師診療科目の物理削除SQL
    PHYSICAL_DELETE_QUERY_TRT = """\
    DELETE FROM
        src05.com_dr_trt_course
    WHERE
        dcf_pcf_dr_cd = :dcf_pcf_dr_cd
    """

    # COM_医師診療科目の登録用SQL
    INSERT_QUERY_TRT = """\
    INSERT INTO src05.com_dr_trt_course
    (
        trt_course_cd,
        sequence,
        dcf_pcf_dr_cd,
        regist_ymd,
        sys_regist_date,
        regist_prgm_id,
        sys_update_date,
        update_prgm_id
    )
    VALUES (
        {trt_course_code},
        {trt_sequence},
        :dcf_pcf_dr_cd,
        :execute_date_str_ymd,
        :execute_datetime,
        :program_name,
        :execute_datetime,
        :program_name
    )
    """

    record: ComDr

    def __init__(self, record: list[str], db) -> None:
        super().__init__(record, db, ComDr)
        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):
        # 修正区分がC(削除)の場合、論理削除
        if self.record.maint_flag == 'C':
            self.queries.append(self.LOGICAL_DELETE_QUERY)
            return

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

    def __make_upsert_query(self):
        # 西暦の取得
        self.__set_era()

        # レコードの存在確認
        record_count = self.db.execute_select(self.RECORD_EXISTS_QUERY, self.query_parameter)
        # 存在しない場合はInsert
        if record_count[0]['count_num'] == 0:
            self.queries.append(self.INSERT_QUERY)
            # COM_医師診療科目 削除登録
            self.__make_delete_insert_trt_query()
            return

        # 存在する場合ではUpdate
        # COM_医師診療科目 削除登録
        self.__make_delete_insert_trt_query()

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

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

        # 削除予定理由
        if len(self.record.drdel_code) > 0:
            set_clauses.append('delete_sche_reason_cd = :drdel_code')
            if self.record.drdel_code == '@':
                self.query_parameter['drdel_code'] = None

        # 重複時相手先ｺｰﾄﾞ
        if len(self.record.opp_dup_code) > 0:
            set_clauses.append('opp_dup_cd = :opp_dup_code')
            if self.record.reptdr_id == '@':
                self.query_parameter['opp_dup_code'] = None

        # 医師名(漢字)
        if len(self.record.dr_name) > 0:
            set_clauses.append('dr_name = :dr_name')

        # 医師名(ｶﾅ)
        if len(self.record.dr_name_kana) > 0:
            set_clauses.append('dr_name_kana = :dr_name_kana')

        # 性別
        if len(self.record.sex_code) > 0:
            set_clauses.append('sex_cd = :sex_code')

        # 生年月日
        if len(self.record.birthday_era + self.record.birthday_year + self.record.birthday_month + self.record.birthday_day) > 0:
            set_clauses.append('birthday_era = :birthday_era')
            set_clauses.append('birthday_year = :birthday_year')
            set_clauses.append('birthday_month = :birthday_month')
            set_clauses.append('birthday_day = :birthday_day')
            set_clauses.append('birthday = :birth_day')
            if self.record.birthday_era == '@':
                self.query_parameter['birthday_era'] = None
                self.query_parameter['birthday_year'] = None
                self.query_parameter['birthday_month'] = None
                self.query_parameter['birthday_day'] = None
                self.query_parameter['birth_day'] = None

        # 出身都道府県ｺｰﾄﾞ
        if len(self.record.home_town_code) > 0:
            set_clauses.append('hometown_cd = :home_town_code')

        # 医師会ｺｰﾄﾞ
        if len(self.record.medassoci_code) > 0:
            set_clauses.append('dr_circle_cd = :medassoci_code')

        # 卒年
        if len(self.record.grad_yearera + self.record.grad_year) > 0:
            set_clauses.append('grad_year = :grad_year')
            set_clauses.append('grad_era = :grad_yearera')
            set_clauses.append('grad_y = :grad_y')
            if self.record.grad_yearera == '@':
                self.query_parameter['grad_yearera'] = None
                self.query_parameter['grad_year'] = None
                self.query_parameter['grad_y'] = None

        # 出身校ｺｰﾄﾞ
        if len(self.record.graduniv_code) > 0:
            set_clauses.append('alma_cd = :graduniv_code')

        # 出身校学部識別ｺｰﾄﾞ
        if len(self.record.graduniv_dept_code) > 0:
            set_clauses.append('depart_disc_cd = :graduniv_dept_code')

        # 登録年
        if len(self.record.drda_yera + self.record.drday_year) > 0:
            set_clauses.append('drday_era = :drda_yera')
            set_clauses.append('drday_year = :drday_year')
            set_clauses.append('drday_y = :drday_y')
            if self.record.drda_yera == '@':
                self.query_parameter['drda_yera'] = None
                self.query_parameter['drday_year'] = None
                self.query_parameter['drday_y'] = None

        # 住所不明
        if len(self.record.dr_addr_lost_code) > 0:
            set_clauses.append('addr_unknown_reason_cd = :dr_addr_lost_code')
            if self.record.dr_addr_lost_code == '@':
                self.query_parameter['dr_addr_lost_code'] = None

        # 住所
        # 集合項目のいずれかに入力がある場合に更新
        if sum(len(item) for item in self.record.address_aggregation_items) > 0:
            set_clauses.append('home_addr_kana = :dr_addr_kana')
            set_clauses.append('home_addr = :dr_addr')
            set_clauses.append('home_postal_number = :dr_zip_code')
            set_clauses.append('addr_village_cd = :addr_village_cd')
            set_clauses.append('prefc_cd = :pref_code')
            set_clauses.append('city_cd = :city_code')
            set_clauses.append('addr_display_number = :dr_addr_num')
            set_clauses.append('addr_cnt_kana = :addr_cnt_kana')
            set_clauses.append('addr_cnt = :addr_cnt')

        # 自宅電話番号
        if len(self.record.dr_tel) > 0:
            set_clauses.append('home_phone_number = :dr_tel')
            if self.record.dr_tel == '@':
                self.query_parameter['dr_tel'] = None

        # 利用停止区分
        if len(self.record.use_stop_flag) > 0:
            set_clauses.append('use_stop_div = :use_stop_flag')
            if self.record.use_stop_flag == '@':
                self.query_parameter['use_stop_flag'] = None

        # 利用停止理由
        if len(self.record.use_stopc_ode) > 0:
            set_clauses.append('use_stop_reason_cd = :use_stopc_ode')
            if self.record.use_stopc_ode == '@':
                self.query_parameter['use_stopc_ode'] = None

        # 利用停止登録年月日
        if len(self.record.cre_stop_date) > 0:
            set_clauses.append('use_stop_regist_ymd = :cre_stop_date')
            if self.record.cre_stop_date == '@':
                self.query_parameter['cre_stop_date'] = None

        # 利用停止解除年月日
        if len(self.record.release_date) > 0:
            set_clauses.append('use_stop_cancel_ymd = :release_date')
            if self.record.release_date == '@':
                self.query_parameter['release_date'] = None

        # 開勤区分
        if len(self.record.pract_class_code) > 0:
            set_clauses.append('estab_div_cd = :pract_class_code')

        # 開業年
        if len(self.record.pract_yearera + self.record.pract_year) > 0:
            set_clauses.append('estab_era = :pract_yearera')
            set_clauses.append('estab_year = :pract_year')
            set_clauses.append('estab_y = :estab_y')
            if self.record.pract_yearera == '@':
                self.query_parameter['pract_yearera'] = None
                self.query_parameter['pract_year'] = None
                self.query_parameter['estab_y'] = None

        # 一括登録ﾌﾗｸﾞ
        if len(self.record.bskregst_flag) > 0:
            set_clauses.append('lump_regist_flg = :bskregst_flag')

        return set_clauses

    def __make_delete_insert_trt_query(self):
        # 診療科目（集合項目）のいずれかに入力がある場合
        if sum(len(item) for item in self.record.medsbj_code_items) == 0:
            return

        # 削除
        self.queries.append(self.PHYSICAL_DELETE_QUERY_TRT)

        # @マークの場合は、登録しない
        if self.record.medsbj_code1 == '@':
            return

        # 登録
        for num, m_code in enumerate(self.record.medsbj_code_items, start=1):
            if len(m_code) > 0:
                insert_trt_query = self.INSERT_QUERY_TRT.format(
                    trt_course_code=f':medsbj_code{num}',
                    trt_sequence=num
                )
                self.queries.append(insert_trt_query)

        return

    def __set_era(self):
        # 西暦の取得

        # 生年月日（西暦）
        if self.record.birthday_era != "@" and len(self.record.birthday_era) > 0 and len(self.record.birthday_year) > 0:
            self.query_parameter['era_cd'] = self.record.birthday_era
            record_year = self.db.execute_select(self.YEAR_GET_QUERY, self.query_parameter)
            ans_year = int(record_year[0]['year']) + int(self.record.birthday_year)
            self.query_parameter['birth_day'] = ''.join([str(ans_year), self.record.birthday_month, self.record.birthday_day])

        # 開業年（西暦）
        if self.record.pract_yearera != "@" and len(self.record.pract_yearera) > 0 and len(self.record.pract_year) > 0:
            self.query_parameter['era_cd'] = self.record.pract_yearera
            record_year = self.db.execute_select(self.YEAR_GET_QUERY, self.query_parameter)
            self.query_parameter['estab_y'] = str(int(record_year[0]['year']) + int(self.record.pract_year))

        # 卒業年（西暦）
        if self.record.grad_yearera != "@" and len(self.record.grad_yearera) > 0 and len(self.record.grad_year) > 0:
            self.query_parameter['era_cd'] = self.record.grad_yearera
            record_year = self.db.execute_select(self.YEAR_GET_QUERY, self.query_parameter)
            self.query_parameter['grad_y'] = str(int(record_year[0]['year']) + int(self.record.grad_year))

        # 登録年（西暦）
        if self.record.drda_yera != "@" and len(self.record.drda_yera) > 0 and len(self.record.drday_year) > 0:
            self.query_parameter['era_cd'] = self.record.drda_yera
            record_year = self.db.execute_select(self.YEAR_GET_QUERY, self.query_parameter)
            self.query_parameter['drday_y'] = str(int(record_year[0]['year']) + int(self.record.drday_year))

        return
