from src.batch.ultmarc.utmp_tables.table_mapper.ultmarc_table_mapper import \
    UltmarcTableMapper
from src.batch.ultmarc.utmp_tables.tables.com_inst import ComInst


class ComInstMapper(UltmarcTableMapper):
    """レイアウト区分101: COM_施設 登録処理"""

    # レコード存在確認SQL(COM_施設)
    RECORD_EXISTS_QUERY = """\
    SELECT
        COUNT(*) AS count_num
    FROM
        src05.com_inst
    WHERE
        dcf_dsf_inst_cd = :dcfdsf_inst_code
    """

    # データ登録用SQL(COM_施設)
    INSERT_INST_QUERY = """\
    INSERT INTO src05.com_inst
    (
        dcf_dsf_inst_cd,
        inst_div_cd,
        addr_unknown_reason_cd,
        form_inst_name_kana,
        inst_name_kana,
        form_inst_name_kanji,
        inst_name_kanji,
        rltd_univ_prnt_cd,
        bed_num,
        close_flg,
        estab_sche_flg,
        close_start_ym,
        estab_sche_ym,
        ward_abolish_flg,
        inst_repre_cd,
        inst_repre_kana,
        inst_repre,
        phone_number_non_flg,
        unconf_flg,
        inst_phone_number,
        inst_addr_kana,
        inst_addr,
        postal_number,
        village_cd,
        prefc_cd,
        city_cd,
        addr_display_number,
        addr_cnt_kana,
        addr_cnt,
        manage_cd,
        delete_sche_reason_cd,
        hp_assrt_cd,
        dup_opp_cd,
        insp_item_micrb,
        insp_item_serum,
        insp_item_blood,
        insp_item_patho,
        insp_item_paras,
        insp_item_biochem,
        insp_item_ri,
        re_exam_cd,
        prmit_bed_num_other,
        prmit_bed_num_mental,
        prmit_bed_num_tuber,
        prmit_bed_num_infection,
        prmit_bed_num_sum,
        prmit_bed_num_gen,
        prmit_bed_num_rcup,
        prmit_bed_maint_ymd,
        inst_pharm_div,
        abolish_ymd,
        delete_flg,
        sys_regist_date,
        regist_prgm_id,
        sys_update_date,
        update_prgm_id
    )
    VALUES (
        :dcfdsf_inst_code,
        :hp_class_code,
        :hp_addr_lost_code,
        :hp_name_kana,
        :hp_ryaku_name_kana,
        :hp_name,
        :hp_ryaku_name,
        :univ_prnt_code,
        :bed_num,
        :close_flag,
        :open_flag,
        :close_year_month,
        :open_year_month,
        :close_flag2,
        :inst_repre_code,
        :president_kana,
        :president,
        :tel_nothing_flag,
        :uncheck_flag,
        :hp_tel,
        :hp_addr_kana,
        :hp_addr,
        :hp_zip_code,
        :village_code,
        :pref_code,
        :city_code,
        :hp_addr_number,
        :addr_cnt_kana,
        :addr_cnt,
        :mgt_class_code,
        :hpdel_code,
        :hp_kind_code,
        :dup_opp_code,
        :inspect_code1,
        :inspect_code2,
        :inspect_code3,
        :inspect_code4,
        :inspect_code5,
        :inspect_code6,
        :inspect_code7,
        :reexam_flag,
        :bed_num_gen,
        :bed_num_psy,
        :bed_num_tub,
        :bed_num_epi,
        :bed_num_sum,
        :bed_num_gen2,
        :bed_num_rest,
        :bed_class_maint_date,
        1,
        NULL,
        0,
        :execute_datetime,
        :program_name,
        :execute_datetime,
        :program_name
    )
    """

    # データ更新用SQL(COM_施設)
    UPDATE_INST_QUERY = """\
    UPDATE
        src05.com_inst
    SET
        {update_columns}
        abolish_ymd = NULL,
        sys_update_date = :execute_datetime,
        update_prgm_id = :program_name
    WHERE
        dcf_dsf_inst_cd = :dcfdsf_inst_code
    """

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

    # データ登録用SQL(COM_施設診療科目)
    INSERT_INST_TRT_QUERY = """\
    INSERT INTO src05.com_inst_trt_course
    (
        dcf_dsf_inst_cd,
        trt_course_cd,
        regist_ymd,
        sys_regist_date,
        regist_prgm_id,
        sys_update_date,
        update_prgm_id
    )
    VALUES (
        :dcfdsf_inst_code,
        {trt_course_cd},
        :execute_date_str_ymd,
        :execute_datetime,
        :program_name,
        :execute_datetime,
        :program_name
    )
    """

    # データ削除用SQL(COM_施設診療科目)
    PHYSICAL_DELETE_TRT_QUERY = """\
    DELETE FROM
        src05.com_inst_trt_course
    WHERE
        dcf_dsf_inst_cd = :dcfdsf_inst_code
    """

    # レコード存在確認SQL(COM_特養医務室)
    RECORD_EXISTS_SPCARE_QUERY = """\
    SELECT
        COUNT(*) AS count_num
    FROM
        src05.com_spcare_med_office_dat
    WHERE
        dcf_chld_inst_cd = :dcfdsf_inst_code
    """

    # データ更新用SQL(COM_特養医務室)
    UPDATE_SPCARE_QUERY = """\
    UPDATE
        src05.com_spcare_med_office_dat
    SET
        dcf_prnt_inst_cd = :dcf_prnt_inst_code,
        update_ymd = :execute_date_str_ymd,
        delete_ymd = :delete_ymd,
        sys_update_date = :execute_datetime,
        update_prgm_id = :program_name
    WHERE
        dcf_chld_inst_cd = :dcfdsf_inst_code
    """

    # データ登録用SQL(COM_特養医務室)
    INSERT_SPCARE_QUERY = """\
    INSERT INTO src05.com_spcare_med_office_dat
    (
        dcf_chld_inst_cd,
        dcf_prnt_inst_cd,
        regist_ymd,
        sys_regist_date,
        regist_prgm_id,
        sys_update_date,
        update_prgm_id
    )
    VALUES (
        :dcfdsf_inst_code,
        :dcf_prnt_inst_code,
        :execute_date_str_ymd,
        :execute_datetime,
        :program_name,
        :execute_datetime,
        :program_name
    )
    """

    record: ComInst

    def __init__(self, record: list[str], db) -> None:
        super().__init__(record, db, ComInst)
        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):
        # レコードの存在確認（施設）
        record_count = self.db.execute_select(self.RECORD_EXISTS_QUERY, self.query_parameter)
        if record_count[0]['count_num'] == 0:
            # 存在しない場合はInsert
            self.queries.append(self.INSERT_INST_QUERY)
        else:
            # 存在する場合はUpdate(施設)
            self.__make_update_query()

        # 施設診療科目の削除挿入
        self.__make_delete_insert_trt_query()

        # 特養医務室の追加更新
        self.__make_upsert_spcare_query()

        return

    def __make_update_query(self):
        # 存在する場合はUpdate(施設)
        set_clauses = []  # 設定項目

        # 施設区分コード
        if len(self.record.hp_class_code) > 0:
            set_clauses.append('inst_div_cd = :hp_class_code')

        # 正式施設名(ｶﾅ)
        if len(self.record.hp_name_kana) > 0:
            set_clauses.append('form_inst_name_kana = :hp_name_kana')

        # 略式施設名(ｶﾅ)
        if len(self.record.hp_ryaku_name_kana) > 0:
            set_clauses.append('inst_name_kana = :hp_ryaku_name_kana')

        # 正式施設名(漢字)
        if len(self.record.hp_name) > 0:
            set_clauses.append('form_inst_name_kanji = :hp_name')

        # 略式施設名(漢字)
        if len(self.record.hp_ryaku_name) > 0:
            set_clauses.append('inst_name_kanji = :hp_ryaku_name')

        # 施設住所カナ
        if len(self.record.hp_addr_kana) > 0:
            set_clauses.append('inst_addr_kana = :hp_addr_kana')

        # 施設住所
        if len(self.record.hp_addr) > 0:
            set_clauses.append('inst_addr = :hp_addr')

        # 郵便番号
        if len(self.record.hp_zip_code) > 0:
            set_clauses.append('postal_number = :hp_zip_code')

        # 町字コード・都道府県コード・市区町村コード
        if len(self.record.village_code + self.record.pref_code + self.record.city_code) > 0:
            set_clauses.append('village_cd = :village_code')
            set_clauses.append('prefc_cd = :pref_code')
            set_clauses.append('city_cd = :city_code')

        # 住所カウント
        if len(self.record.pref_code) > 0:
            set_clauses.append('addr_cnt_kana = :addr_cnt_kana')
            set_clauses.append('addr_cnt = :addr_cnt')

        # 住所表示番号
        if len(self.record.hp_addr_number) > 0:
            set_clauses.append('addr_display_number = :hp_addr_number')

        # 経営体コード
        if len(self.record.mgt_class_code) > 0:
            set_clauses.append('manage_cd = :mgt_class_code')

        # 病院種別
        if len(self.record.hp_kind_code) > 0:
            set_clauses.append('hp_assrt_cd = :hp_kind_code')

        # 再審査コード
        if len(self.record.reexam_flag) > 0:
            set_clauses.append('re_exam_cd = :reexam_flag')

        # 未確認フラグ
        if len(self.record.uncheck_flag) > 0:
            set_clauses.append('unconf_flg = :uncheck_flag')
            if self.record.uncheck_flag == '@':
                self.query_parameter['uncheck_flag'] = None

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

        # 重複時相手先コード
        if len(self.record.dup_opp_code) > 0:
            set_clauses.append('dup_opp_cd = :dup_opp_code')
            if self.record.duphp_id == '@':
                self.query_parameter['dup_opp_code'] = None

        # 住所不明理由コード
        if len(self.record.hp_addr_lost_code) > 0:
            set_clauses.append('addr_unknown_reason_cd = :hp_addr_lost_code')
            if self.record.hp_addr_lost_code == '@':
                self.query_parameter['hp_addr_lost_code'] = None

        # 電話番号なしフラグ
        if len(self.record.tel_nothing_flag) > 0:
            set_clauses.append('phone_number_non_flg = :tel_nothing_flag')
            if self.record.tel_nothing_flag == '@':
                self.query_parameter['tel_nothing_flag'] = None

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

        # 施設代表者コード
        if len(self.record.inst_repre_code) > 0:
            set_clauses.append('inst_repre_cd = :inst_repre_code')
            if self.record.president_id == '@':
                self.query_parameter['inst_repre_code'] = None

        # 代表者(ｶﾅ)
        if len(self.record.president_kana) > 0:
            set_clauses.append('inst_repre_kana = :president_kana')
            if self.record.president_kana == '@':
                self.query_parameter['president_kana'] = None

        # 代表者(漢字) ※「＠」が大文字
        if len(self.record.president) > 0:
            set_clauses.append('inst_repre = :president')
            if self.record.president == '＠':
                self.query_parameter['president'] = None

        # 開業予定ﾌﾗｸﾞ・開業予定年月
        if len(self.record.open_flag + self.record.open_year_month) > 0:
            set_clauses.append('estab_sche_flg = :open_flag')
            set_clauses.append('estab_sche_ym = :open_year_month')
            if self.record.open_flag == '@':
                self.query_parameter['open_flag'] = None
                self.query_parameter['open_year_month'] = None

        # 休院ﾌﾗｸﾞ・休院開始年月
        if len(self.record.close_flag + self.record.close_year_month) > 0:
            set_clauses.append('close_flg = :close_flag')
            set_clauses.append('close_start_ym = :close_year_month')
            if self.record.close_flag == '@':
                self.query_parameter['close_flag'] = None
                self.query_parameter['close_year_month'] = None

        # 関連大学親コード
        if len(self.record.univ_prnt_code) > 0:
            set_clauses.append('rltd_univ_prnt_cd = :univ_prnt_code')
            if self.record.assoc_parrent_id == '@':
                self.query_parameter['univ_prnt_code'] = None

        # 病棟閉鎖フラグ
        if len(self.record.close_flag2) > 0:
            set_clauses.append('ward_abolish_flg = :close_flag2')
            if self.record.close_flag2 == '@':
                self.query_parameter['close_flag2'] = None

        # 病床数(定員)
        if self.record.bed_num is not None:
            set_clauses.append('bed_num = :bed_num')
            if self.record.bed_num == '@':
                self.query_parameter['bed_num'] = None

        # 許可病床ﾒﾝﾃ日付
        if len(self.record.bed_class_maint_date) > 0:
            set_clauses.append('prmit_bed_maint_ymd = :bed_class_maint_date')
            if self.record.bed_class_maint_date == '@':
                self.query_parameter['bed_class_maint_date'] = None

        # 許可ベッド数（合計、精神、結核、感染、その他、一般病床、療養病床）
        if not self.record.prmit_bed.count(None) == len(self.record.prmit_bed):
            set_clauses.append('prmit_bed_num_sum = :bed_num_sum')
            set_clauses.append('prmit_bed_num_mental = :bed_num_psy')
            set_clauses.append('prmit_bed_num_tuber = :bed_num_tub')
            set_clauses.append('prmit_bed_num_infection = :bed_num_epi')
            set_clauses.append('prmit_bed_num_other = :bed_num_gen')
            set_clauses.append('prmit_bed_num_gen = :bed_num_gen2')
            set_clauses.append('prmit_bed_num_rcup = :bed_num_rest')
            if self.record.bed_num_sum == '@':
                self.query_parameter['bed_num_sum'] = None
                self.query_parameter['bed_num_psy'] = None
                self.query_parameter['bed_num_tub'] = None
                self.query_parameter['bed_num_epi'] = None
                self.query_parameter['bed_num_gen'] = None
                self.query_parameter['bed_num_gen2'] = None
                self.query_parameter['bed_num_rest'] = None

        # 検査項目(微生物、血清、血液、病理、寄生虫、生化、RI)
        if sum(len(item) for item in self.record.insp_item) > 0:
            set_clauses.append('insp_item_micrb = :inspect_code1')
            set_clauses.append('insp_item_serum = :inspect_code2')
            set_clauses.append('insp_item_blood = :inspect_code3')
            set_clauses.append('insp_item_patho = :inspect_code4')
            set_clauses.append('insp_item_paras = :inspect_code5')
            set_clauses.append('insp_item_biochem = :inspect_code6')
            set_clauses.append('insp_item_ri = :inspect_code7')
            if self.record.inspect_code1 == '@':
                self.query_parameter['inspect_code1'] = None
                self.query_parameter['inspect_code2'] = None
                self.query_parameter['inspect_code3'] = None
                self.query_parameter['inspect_code4'] = None
                self.query_parameter['inspect_code5'] = None
                self.query_parameter['inspect_code6'] = None
                self.query_parameter['inspect_code7'] = None

        update_columns = ','.join(set_clauses)
        # 何も更新項目が無い場合は更新処理は行わない
        if len(update_columns) == 0:
            self.queries.append(None)
            return
        else:
            update_columns += ','
            update_query = self.UPDATE_INST_QUERY.format(
                update_columns=update_columns
            )
            self.queries.append(update_query)

        return

    def __make_delete_insert_trt_query(self):
        # 施設診療科目の削除挿入
        # 診療科目（集合項目）のいずれも入力がない場合、何もしない
        if sum(len(item) for item in self.record.medsbj_code) == 0:
            return

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

        if self.record.medsbj_code[0] == "@":
            return

        # 診療科目1～60
        for medsbj_code in self.record.medsbj_code:
            if len(medsbj_code) > 0:
                insert_query = self.INSERT_INST_TRT_QUERY.format(
                    trt_course_cd=f"'{medsbj_code}'"
                )
                self.queries.append(insert_query)
        return

    def __make_upsert_spcare_query(self):
        # 特養医務室の追加、更新
        # DCF親施設コードがない場合、何もしない
        if (self.record.dcf_prnt_inst_code == "00" or len(self.record.dcf_prnt_inst_code) == 0):
            return

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

        # 存在する場合はUpdate
        if self.record.dcfhp_92id == "@":
            self.query_parameter['dcf_prnt_inst_code'] = None
            self.query_parameter['delete_ymd'] = self.query_parameter['execute_date_str_ymd']
        else:
            self.query_parameter['delete_ymd'] = None

        self.queries.append(self.UPDATE_SPCARE_QUERY)

        return
