import mojimoji

from src.db import sql_condition as condition
from src.db.sql_condition import SQLCondition
from src.logging.get_logger import get_logger
from src.model.db.ultmarc_inst import UltmarcInstDBModel
from src.model.db.ultmarc_inst_info import UltmarcInstInfoDBModel
from src.model.request.ultmarc_inst import UltmarcInstSearchModel
from src.repositories.base_repository import BaseRepository
from src.util.string_util import is_not_empty

logger = get_logger('COM_施設取得')


class UltmarcInstRepository(BaseRepository):

    FETCH_SQL = """\
        SELECT
        dcf_dsf_inst_cd,
        abolish_ymd,
        delete_sche_reason_cd,
        form_inst_name_kanji,
        inst_addr,
        postal_number,
        inst_phone_number,
        inst_div_name,
        hp_assrt_name,
        prefc_name
        FROM src05.com_inst
        LEFT JOIN src05.mst_prefc ON com_inst.prefc_cd = mst_prefc.prefc_cd
        LEFT JOIN src05.com_inst_div ON com_inst.inst_div_cd = com_inst_div.inst_div_cd
        LEFT JOIN src05.com_hp_assrt ON com_inst.hp_assrt_cd = com_hp_assrt.hp_assrt_cd
        WHERE {where_clause}
        ORDER BY dcf_dsf_inst_cd
    \
    """

    def fetch_many(self, parameter: UltmarcInstSearchModel) -> list[UltmarcInstDBModel]:
        try:
            self._database.connect()
            # 文字列の検索を部分一致にするため、モデルをコピー。以降はこのコピーを使用する。
            clone_parameter = UltmarcInstSearchModel(**parameter.dict())
            where_clause = self.__build_condition(clone_parameter)
            query = self.FETCH_SQL.format(where_clause=where_clause)
            result = self._database.execute_select(query, clone_parameter.dict())
            models = [UltmarcInstDBModel(**r) for r in result]

            return models
        except Exception as e:
            logger.exception(f"DB Error : Exception={e.args}")
            raise e
        finally:
            self._database.disconnect()

    def __build_condition(self, parameter: UltmarcInstSearchModel):
        where_clauses: list[SQLCondition] = []

        # ULT施設コード
        if is_not_empty(parameter.dcf_dsf_inst_cd):
            # 部分一致検索
            parameter.dcf_dsf_inst_cd = f'%{parameter.dcf_dsf_inst_cd}%'
            where_clauses.append(SQLCondition('com_inst.dcf_dsf_inst_cd', condition.LIKE, 'dcf_dsf_inst_cd'))

        # 施設区分
        if is_not_empty(parameter.inst_div_cd):
            where_clauses.append(SQLCondition('com_inst.inst_div_cd', condition.EQ, 'inst_div_cd'))

        # ULT施設名(漢字)
        if is_not_empty(parameter.form_inst_name_kanji):
            # 部分一致検索
            parameter.form_inst_name_kanji = f'%{parameter.form_inst_name_kanji}%'
            where_clauses.append(SQLCondition('form_inst_name_kanji', condition.LIKE, 'form_inst_name_kanji'))

        # ULT施設名(カナ)
        if is_not_empty(parameter.form_inst_name_kana):
            # 部分一致検索
            # ひらがなを全角カタカナへ変換
            zenkaku_katakana = ''.join([chr(n + 96) if (12352 < n and n < 12439) or n == 12445 or n == 12446 else chr(n)
                                        for n in [ord(c) for c in parameter.form_inst_name_kana]])
            # 全角カタカナを半角カタカナへ変換
            hankaku_katakana = mojimoji.zen_to_han(zenkaku_katakana)
            parameter.form_inst_name_kana = f'%{hankaku_katakana}%'
            where_clauses.append(SQLCondition('form_inst_name_kana', condition.LIKE, 'form_inst_name_kana'))

        # 郵便番号
        if is_not_empty(parameter.postal_number):
            # 前方一致検索
            parameter.postal_number = f'{parameter.postal_number}%'
            where_clauses.append(SQLCondition('postal_number', condition.LIKE, 'postal_number'))

        # 電話番号
        if is_not_empty(parameter.inst_phone_number):
            # 前方一致検索
            parameter.inst_phone_number = f'{parameter.inst_phone_number}%'
            where_clauses.append(SQLCondition('inst_phone_number', condition.LIKE, 'inst_phone_number'))

        # 勤務先都道府県
        if is_not_empty(parameter.prefc_cd):
            where_clauses.append(SQLCondition('com_inst.prefc_cd', condition.EQ, 'prefc_cd'))

        # ULT施設住所
        if is_not_empty(parameter.inst_addr):
            # 部分一致検索
            parameter.inst_addr = f'%{parameter.inst_addr}%'
            where_clauses.append(SQLCondition('inst_addr', condition.LIKE, 'inst_addr'))

        # 削除表示フラグ
        if is_not_empty(parameter.delFlg) is False:
            # 論理和での検索
            where_clauses.append(SQLCondition('', '', '(length(abolish_ymd) = 0 OR abolish_ymd IS NULL)', literal=True))

        where_clauses_str = ' AND '.join([condition.apply() for condition in where_clauses])

        return where_clauses_str

    FETCH_ONE_SQL = """\
        SELECT
        com_inst.dcf_dsf_inst_cd,
        com_inst.unconf_flg,
        com_inst.dup_opp_cd,
        com_inst.close_start_ym,
        com_inst.close_flg,
        com_inst.delete_sche_reason_cd,
        com_inst.abolish_ymd,
        com_inst.estab_sche_ym,
        com_inst.estab_sche_flg,
        com_inst.form_inst_name_kana,
        com_inst.form_inst_name_kanji,
        com_inst.inst_name_kana,
        com_inst.inst_name_kanji,
        com_inst.manage_cd,
        com_inst.postal_number,
        com_inst.inst_phone_number,
        com_inst.addr_unknown_reason_cd,
        com_inst.phone_number_non_flg,
        com_inst.inst_addr_kana,
        com_inst.inst_addr,
        com_inst.re_exam_cd,
        com_inst.rltd_univ_prnt_cd,
        com_inst.insp_item_micrb,
        com_inst.insp_item_serum,
        com_inst.insp_item_blood,
        com_inst.insp_item_patho,
        com_inst.insp_item_paras,
        com_inst.insp_item_biochem,
        com_inst.insp_item_ri,
        com_inst.prmit_bed_num_gen,
        com_inst.prmit_bed_num_rcup,
        com_inst.prmit_bed_num_mental,
        com_inst.prmit_bed_num_infection,
        com_inst.prmit_bed_num_tuber,
        com_inst.prmit_bed_num_other,
        com_inst.prmit_bed_num_sum,
        com_inst.ward_abolish_flg,
        com_inst.bed_num,
        com_inst.prmit_bed_maint_ymd,
        com_inst.inst_repre_cd,
        com_inst.inst_repre_kana,
        com_inst.inst_repre,
        com_inst.sys_update_date,
        com_inst_delete_sche_reason.delete_sche_reason,
        com_inst_div.inst_div_name,
        com_manage.manage_name,
        com_hp_assrt.hp_assrt_name,
        parent_inst.form_inst_name_kanji as parent_name,
        com_spcare_med_office_dat.dcf_prnt_inst_cd
        FROM src05.com_inst
        LEFT JOIN src05.com_inst_div ON com_inst.inst_div_cd = com_inst_div.inst_div_cd
        LEFT JOIN src05.com_inst_delete_sche_reason ON
        com_inst.delete_sche_reason_cd = com_inst_delete_sche_reason.delete_sche_reason_cd
        LEFT JOIN src05.com_manage ON com_inst.manage_cd = com_manage.manage_cd
        LEFT JOIN src05.com_inst_addr_unknown_reason ON
        com_inst.addr_unknown_reason_cd = com_inst_addr_unknown_reason.addr_unknown_reason_cd
        LEFT JOIN src05.com_hp_assrt ON com_hp_assrt.hp_assrt_cd = com_inst.hp_assrt_cd
        LEFT JOIN src05.com_re_exam ON com_inst.re_exam_cd = com_re_exam.re_exam_cd
        LEFT JOIN src05.com_spcare_med_office_dat ON
        com_inst.dcf_dsf_inst_cd = com_spcare_med_office_dat.dcf_chld_inst_cd
        LEFT JOIN src05.com_inst as parent_inst ON com_inst.rltd_univ_prnt_cd = parent_inst.dcf_dsf_inst_cd
        WHERE com_inst.dcf_dsf_inst_cd = :id
    \
    """

    def fetch_one(self, id) -> UltmarcInstInfoDBModel:
        try:
            self._database.connect()
            query = self.FETCH_ONE_SQL
            result = self._database.execute_select(query, {'id': id})
            models = [UltmarcInstInfoDBModel(**r) for r in result]
            if len(models) == 0:
                return None
            return models[0]
        except Exception as e:
            logger.exception(f"DB Error : Exception={e.args}")
            raise e
        finally:
            self._database.disconnect()
