from src.batch.ultmarc.utmp_tables.table_mapper.ultmarc_table_mapper import \
    UltmarcTableMapper
from src.batch.ultmarc.utmp_tables.tables.com_prefc_med_base import ComPrefcMedBase


class ComPrefcMedBaseMapper(UltmarcTableMapper):
    """レイアウト区分132: COM_都道府県医療機能情報(基本)"""

    # レコード存在確認SQL
    RECORD_EXISTS_QUERY = """\
    SELECT
        COUNT(*) AS count_num
    FROM
        src05.com_prefc_med_base
    WHERE
        dcf_dsf_inst_cd = :dcfdsf_inst_code
    """
    # データ登録用SQL
    INSERT_QUERY = """\
    INSERT INTO src05.com_prefc_med_base
    (
        dcf_dsf_inst_cd,
        info_date,
        home_page,
        hppre_flg,
        expre_flg,
        trial_flg,
        trial_contcount,
        trialwhet_from,
        trialwhet_to,
        equipment_flg,
        cos_disease_flg,
        cos_surgery,
        specialclinic_flg,
        establishment_flg,
        critical_flg,
        cop_system,
        sys_exists_flg,
        sys_inspection,
        sys_prescription,
        sys_reserv,
        icduse_flg,
        echart_flg,
        fulltime_flg,
        fulltime_count,
        ge_patient_avg,
        mt_patient_avg,
        mc_patient_avg,
        ca_patient_avg,
        pys_patient_avg,
        tub_patient_avg,
        inf_patient_avg,
        patient_avg_sum,
        patient_avg_from,
        patient_avg_to,
        cl_patient_avg,
        cl_patient_avg_from,
        cl_patient_avg_to,
        hm_patient_avg,
        hm_patient_avg_from,
        hm_patient_avg_to,
        ge_patient_ex,
        mt_patient_ex,
        mc_patient_ex,
        ca_patient_ex,
        pys_patient_ex,
        tub_patient_ex,
        inf_patient_ex,
        patient_ex_sum,
        patient_ex_from,
        patient_ex_to,
        cl_patient_ex,
        cl_patient_ex_from,
        cl_patient_ex_to,
        hm_patient_ex,
        hm_patient_ex_from,
        hm_patient_ex_to,
        ge_stay_avg,
        mt_stay_avg,
        mc_stay_avg,
        ca_stay_avg,
        pys_stay_avg,
        tub_stay_avg,
        inf_stay_avg,
        stay_avg_sum,
        stay_avg_from,
        stay_avg_to,
        regist_ymd,
        sys_regist_date,
        regist_prgm_id,
        sys_update_date,
        update_prgm_id
    )
    VALUES (
        :dcfdsf_inst_code,
        :info_date,
        :home_page,
        :hppre_flg,
        :expre_flg,
        :trial_flg,
        :trial_cont_count,
        :trial_whet_from,
        :trial_whet_to,
        :equipment_flg,
        :cos_disease_flg,
        :cos_surgery,
        :specialclinic_flg,
        :establishment_flg,
        :critical_flg,
        :cop_system,
        :sys_exists_flg,
        :sys_inspection,
        :sys_prescription,
        :sys_reserv,
        :icduse_flg,
        :echart_flg,
        :fulltime_flg,
        :fulltime_count,
        :ge_patient_avg,
        :mt_patient_avg,
        :mc_patient_avg,
        :ca_patient_avg,
        :pys_patient_avg,
        :tub_patient_avg,
        :inf_patient_avg,
        :patient_avg_sum,
        :patient_avg_from,
        :patient_avg_to,
        :cl_patient_avg,
        :cl_patient_avg_from,
        :cl_patient_avg_to,
        :hm_patient_avg,
        :hm_patient_avg_from,
        :hm_patient_avg_to,
        :ge_patient_ex,
        :mt_patient_ex,
        :mc_patient_ex,
        :ca_patient_ex,
        :pys_patient_ex,
        :tub_patient_ex,
        :inf_patient_ex,
        :patient_ex_sum,
        :patient_ex_from,
        :patient_ex_to,
        :cl_patient_ex,
        :cl_patient_ex_from,
        :cl_patient_ex_to,
        :hm_patient_ex,
        :hm_patient_ex_from,
        :hm_patient_ex_to,
        :ge_stay_avg,
        :mt_stay_avg,
        :mc_stay_avg,
        :ca_stay_avg,
        :pys_stay_avg,
        :tub_stay_avg,
        :inf_stay_avg,
        :stay_avg_sum,
        :stay_avg_from,
        :stay_avg_to,
        :execute_date_str_ymd,
        :execute_datetime,
        :program_name,
        :execute_datetime,
        :program_name
    )
    """

    # データ変更用SQL
    UPDATE_QUERY = """\
    UPDATE
        src05.com_prefc_med_base
    SET
        {update_columns}
        update_ymd = :execute_date_str_ymd,
        sys_update_date = :execute_datetime,
        update_prgm_id = :program_name
    WHERE
        dcf_dsf_inst_cd = :dcfdsf_inst_code
    """

    # 『修正区分がB(修正)かつ追加削除区分が1(退職)』の場合、物理削除
    PHYSICAL_DELETE_QUERY = """\
    DELETE FROM
        src05.com_prefc_med_base
    WHERE
        dcf_dsf_inst_cd = :dcfdsf_inst_code
    """

    record: ComPrefcMedBase

    def __init__(self, record: list[str], db) -> None:
        super().__init__(record, db, ComPrefcMedBase)
        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.adddel_div == '1':
            self.queries.append(self.PHYSICAL_DELETE_QUERY)
            return

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

    def __make_upsert_query(self):
        # レコードの存在確認
        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
        return self.__make_update_query()

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

        # 情報年月日
        if len(self.record.info_date) > 0:
            set_clauses.append('info_date = :info_date')
            if self.record.info_date == '@':
                self.query_parameter['info_date'] = None

        # 案内用ﾎｰﾑﾍﾟｰｼﾞｱﾄﾞﾚｽ
        if len(self.record.home_page) > 0:
            set_clauses.append('home_page = :home_page')
            if self.record.home_page == '@':
                self.query_parameter['home_page'] = None

        # 院内処方ﾌﾗｸﾞ
        if len(self.record.hppre_flg) > 0:
            set_clauses.append('hppre_flg = :hppre_flg')
            if self.record.hppre_flg == '@':
                self.query_parameter['hppre_flg'] = None

        # 院外処方ﾌﾗｸﾞ
        if len(self.record.expre_flg) > 0:
            set_clauses.append('expre_flg = :expre_flg')
            if self.record.expre_flg == '@':
                self.query_parameter['expre_flg'] = None

        # 治験の実施
        if len(self.record.trial_flg) > 0:
            set_clauses.append('trial_flg = :trial_flg')
            set_clauses.append('trial_contcount = :trial_cont_count')
            set_clauses.append('trialwhet_from = :trial_whet_from')
            set_clauses.append('trialwhet_to = :trial_whet_to')
            if self.record.trial_flg == '@':
                self.query_parameter['trial_flg'] = None
                self.query_parameter['trial_cont_count'] = None
                self.query_parameter['trial_whet_from'] = None
                self.query_parameter['trial_whet_to'] = None

        # 保有する施設設備ﾌﾗｸﾞ
        if len(self.record.equipment_flg) > 0:
            set_clauses.append('equipment_flg = :equipment_flg')
            if self.record.equipment_flg == '@':
                self.query_parameter['equipment_flg'] = None

        # 対応することができる疾患･治療の内容ﾌﾗｸﾞ
        if len(self.record.cos_disease_flg) > 0:
            set_clauses.append('cos_disease_flg = :cos_disease_flg')
            if self.record.cos_disease_flg == '@':
                self.query_parameter['cos_disease_flg'] = None

        # 対応することができる短期滞在手術ﾌﾗｸﾞ
        if len(self.record.cos_surgery) > 0:
            set_clauses.append('cos_surgery = :cos_surgery')
            if self.record.cos_surgery == '@':
                self.query_parameter['cos_surgery'] = None

        # 専門外来ﾌﾗｸﾞ
        if len(self.record.specialclinic_flg) > 0:
            set_clauses.append('specialclinic_flg = :specialclinic_flg')
            if self.record.specialclinic_flg == '@':
                self.query_parameter['specialclinic_flg'] = None

        # 地域医療連携体制_窓口設置ﾌﾗｸﾞ
        if len(self.record.establishment_flg) > 0:
            set_clauses.append('establishment_flg = :establishment_flg')
            if self.record.establishment_flg == '@':
                self.query_parameter['establishment_flg'] = None

        # 地域医療連携体制_地域連携ﾊﾟｽﾌﾗｸﾞ
        if len(self.record.critical_flg) > 0:
            set_clauses.append('critical_flg = :critical_flg')
            if self.record.critical_flg == '@':
                self.query_parameter['critical_flg'] = None

        # 入院診療計画策定時における院内の連携体制
        if len(self.record.cop_system) > 0:
            set_clauses.append('cop_system = :cop_system')
            if self.record.cop_system == '@':
                self.query_parameter['cop_system'] = None

        # ｵｰﾀﾞﾘﾝｸﾞｼｽﾃﾑ
        if len(self.record.sys_exists_flg) > 0:
            set_clauses.append('sys_exists_flg = :sys_exists_flg')
            set_clauses.append('sys_inspection = :sys_inspection')
            set_clauses.append('sys_prescription = :sys_prescription')
            set_clauses.append('sys_reserv = :sys_reserv')
            if self.record.sys_exists_flg == '@':
                self.query_parameter['sys_exists_flg'] = None
                self.query_parameter['sys_inspection'] = None
                self.query_parameter['sys_prescription'] = None
                self.query_parameter['sys_reserv'] = None

        # ICDｺｰﾄﾞ利用ﾌﾗｸﾞ
        if len(self.record.icduse_flg) > 0:
            set_clauses.append('icduse_flg = :icduse_flg')
            if self.record.icduse_flg == '@':
                self.query_parameter['icduse_flg'] = None

        # 電子ｶﾙﾃﾌﾗｸ
        if len(self.record.echart_flg) > 0:
            set_clauses.append('echart_flg = :echart_flg')
            if self.record.echart_flg == '@':
                self.query_parameter['echart_flg'] = None

        # 専任従事者
        if len(self.record.fulltime_flg) > 0:
            set_clauses.append('fulltime_flg = :fulltime_flg')
            set_clauses.append('fulltime_count = :fulltime_count')
            if self.record.fulltime_flg == '@':
                self.query_parameter['fulltime_flg'] = None
                self.query_parameter['fulltime_count'] = None

        # 病床患者数平均
        if len(self.record.ge_patient_avg) > 0:
            set_clauses.append('ge_patient_avg = :ge_patient_avg')
            set_clauses.append('mt_patient_avg = :mt_patient_avg')
            set_clauses.append('mc_patient_avg = :mc_patient_avg')
            set_clauses.append('ca_patient_avg = :ca_patient_avg')
            set_clauses.append('pys_patient_avg = :pys_patient_avg')
            set_clauses.append('tub_patient_avg = :inf_patient_avg')
            set_clauses.append('inf_patient_avg = :tub_patient_avg')
            set_clauses.append('patient_avg_sum = :patient_avg_sum')
            set_clauses.append('patient_avg_from = :patient_avg_from')
            set_clauses.append('patient_avg_to = :patient_avg_to')
            if self.record.ge_patient_avg == '@':
                self.query_parameter['ge_patient_avg'] = None
                self.query_parameter['mt_patient_avg'] = None
                self.query_parameter['mc_patient_avg'] = None
                self.query_parameter['ca_patient_avg'] = None
                self.query_parameter['pys_patient_avg'] = None
                self.query_parameter['inf_patient_avg'] = None
                self.query_parameter['tub_patient_avg'] = None
                self.query_parameter['patient_avg_sum'] = None
                self.query_parameter['patient_avg_from'] = None
                self.query_parameter['patient_avg_to'] = None

        # 患者数平均
        if len(self.record.cl_patient_avg) > 0:
            set_clauses.append('cl_patient_avg = :cl_patient_avg')
            set_clauses.append('cl_patient_avg_from = :cl_patient_avg_from')
            set_clauses.append('cl_patient_avg_to = :cl_patient_avg_to')
            if self.record.cl_patient_avg == '@':
                self.query_parameter['cl_patient_avg'] = None
                self.query_parameter['cl_patient_avg_from'] = None
                self.query_parameter['cl_patient_avg_to'] = None

        # 患者数平均
        if len(self.record.hm_patient_avg) > 0:
            set_clauses.append('hm_patient_avg = :hm_patient_avg')
            set_clauses.append('hm_patient_avg_from = :hm_patient_avg_from')
            set_clauses.append('hm_patient_avg_to = :hm_patient_avg_to')
            if self.record.hm_patient_avg == '@':
                self.query_parameter['hm_patient_avg'] = None
                self.query_parameter['hm_patient_avg_from'] = None
                self.query_parameter['hm_patient_avg_to'] = None

        # 患者数延数
        if len(self.record.ge_patient_ex) > 0:
            set_clauses.append('ge_patient_ex = :ge_patient_ex')
            set_clauses.append('mt_patient_ex = :mt_patient_ex')
            set_clauses.append('mc_patient_ex = :mc_patient_ex')
            set_clauses.append('ca_patient_ex = :ca_patient_ex')
            set_clauses.append('pys_patient_ex = :pys_patient_ex')
            set_clauses.append('tub_patient_ex = :tub_patient_ex')
            set_clauses.append('inf_patient_ex = :inf_patient_ex')
            set_clauses.append('patient_ex_sum = :patient_ex_sum')
            set_clauses.append('patient_ex_from = :patient_ex_from')
            set_clauses.append('patient_ex_to = :patient_ex_to')
            if self.record.ge_patient_ex == '@':
                self.query_parameter['ge_patient_ex'] = None
                self.query_parameter['mt_patient_ex'] = None
                self.query_parameter['mc_patient_ex'] = None
                self.query_parameter['ca_patient_ex'] = None
                self.query_parameter['pys_patient_ex'] = None
                self.query_parameter['tub_patient_ex'] = None
                self.query_parameter['inf_patient_ex'] = None
                self.query_parameter['patient_ex_sum'] = None
                self.query_parameter['patient_ex_from'] = None
                self.query_parameter['patient_ex_to'] = None

        # 患者数延数
        if len(self.record.cl_patient_ex) > 0:
            set_clauses.append('cl_patient_ex = :cl_patient_ex')
            set_clauses.append('cl_patient_ex_from = :cl_patient_ex_from')
            set_clauses.append('cl_patient_ex_to = :cl_patient_ex_to')
            if self.record.cl_patient_ex == '@':
                self.query_parameter['cl_patient_ex'] = None
                self.query_parameter['cl_patient_ex_from'] = None
                self.query_parameter['cl_patient_ex_to'] = None

        # 患者数延数
        if len(self.record.hm_patient_ex) > 0:
            set_clauses.append('hm_patient_ex = :hm_patient_ex')
            set_clauses.append('hm_patient_ex_from = :hm_patient_ex_from')
            set_clauses.append('hm_patient_ex_to = :hm_patient_ex_to')
            if self.record.hm_patient_ex == '@':
                self.query_parameter['hm_patient_ex'] = None
                self.query_parameter['hm_patient_ex_from'] = None
                self.query_parameter['hm_patient_ex_to'] = None

        # 平均在院日数
        if len(self.record.ge_stay_avg) > 0:
            set_clauses.append('ge_stay_avg = :ge_stay_avg')
            set_clauses.append('mt_stay_avg = :mt_stay_avg')
            set_clauses.append('mc_stay_avg = :mc_stay_avg')
            set_clauses.append('ca_stay_avg = :ca_stay_avg')
            set_clauses.append('pys_stay_avg = :pys_stay_avg')
            set_clauses.append('tub_stay_avg = :tub_stay_avg')
            set_clauses.append('inf_stay_avg = :inf_stay_avg')
            set_clauses.append('stay_avg_sum = :stay_avg_sum')
            set_clauses.append('stay_avg_from = :stay_avg_from')
            set_clauses.append('stay_avg_to = :stay_avg_to')
            if self.record.ge_stay_avg == '@':
                self.query_parameter['ge_stay_avg'] = None
                self.query_parameter['mt_stay_avg'] = None
                self.query_parameter['mc_stay_avg'] = None
                self.query_parameter['ca_stay_avg'] = None
                self.query_parameter['pys_stay_avg'] = None
                self.query_parameter['tub_stay_avg'] = None
                self.query_parameter['inf_stay_avg'] = None
                self.query_parameter['stay_avg_sum'] = None
                self.query_parameter['stay_avg_from'] = None
                self.query_parameter['stay_avg_to'] = 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
