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_doctor import UltmarcDoctorDBModel
from src.model.db.ultmarc_doctor_info import UltmarcDoctorInfoDBModel
from src.model.request.ultmarc_doctor import UltmarcDoctorSearchModel
from src.repositories.base_repository import BaseRepository
from src.util.string_util import is_not_empty

logger = get_logger('COM_医師取得')


class UltmarcDoctorRepository(BaseRepository):

    FETCH_SQL = """\
    SELECT
        com_dr.dcf_pcf_dr_cd,
        com_dr.dr_name,
        com_inst.form_inst_name_kanji,
        com_inst.dcf_dsf_inst_cd,
        com_blng_sec.blng_sec_name,
        GROUP_CONCAT(com_trt_course.trt_course_name separator ' / ')  AS trt_course_name,
        com_post.form_post_name,
        com_alma.alma,
        com_dr.grad_y,
        mst_prefc.prefc_name,
        com_dr_wrkplace.blng_sec_cd
    FROM
        src05.com_dr
    LEFT JOIN src05.mst_prefc ON com_dr.prefc_cd = mst_prefc.prefc_cd
    LEFT JOIN src05.com_dr_wrkplace ON com_dr.dcf_pcf_dr_cd = com_dr_wrkplace.dcf_pcf_dr_cd
    LEFT JOIN src05.com_inst ON com_dr_wrkplace.dcf_dsf_inst_cd = com_inst.dcf_dsf_inst_cd
    LEFT JOIN src05.com_blng_sec ON com_dr_wrkplace.blng_sec_cd = com_blng_sec.blng_sec_cd
    LEFT JOIN src05.com_dr_trt_course ON com_dr.dcf_pcf_dr_cd = com_dr_trt_course.dcf_pcf_dr_cd
    LEFT JOIN src05.com_trt_course ON com_dr_trt_course.trt_course_cd = com_trt_course.trt_course_cd
    LEFT JOIN src05.com_post ON com_dr_wrkplace.post_cd = com_post.post_cd
    LEFT JOIN src05.com_alma ON com_dr.alma_cd = com_alma.alma_cd
    WHERE
        {where_clause}
    GROUP BY com_dr.dcf_pcf_dr_cd, com_inst.dcf_dsf_inst_cd, com_blng_sec.blng_sec_cd
    ORDER BY
        com_dr.dcf_pcf_dr_cd,
        com_dr_wrkplace.dcf_dsf_inst_cd,
        com_dr_wrkplace.blng_sec_cd,
        com_dr_trt_course.trt_course_cd
    \
    """

    def fetch_many(self, parameter: UltmarcDoctorSearchModel) -> list[UltmarcDoctorDBModel]:
        try:
            self._database.connect()
            # 文字列の検索を部分一致にするため、モデルをコピー。以降はこのコピーを使用する。
            clone_parameter = UltmarcDoctorSearchModel(**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 = [UltmarcDoctorDBModel(**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: UltmarcDoctorSearchModel):
        where_clauses: list[SQLCondition] = []

        # 医師コード
        if is_not_empty(parameter.dcf_pcf_dr_cd):
            # 必ず部分一致で検索
            parameter.dcf_pcf_dr_cd = f'%{parameter.dcf_pcf_dr_cd}%'
            where_clauses.append(SQLCondition('com_dr.dcf_pcf_dr_cd', condition.LIKE, 'dcf_pcf_dr_cd'))
        # 氏名(漢字)
        if is_not_empty(parameter.dr_name):
            # 必ず部分一致で検索
            parameter.dr_name = f'%{parameter.dr_name}%'
            where_clauses.append(SQLCondition('dr_name', condition.LIKE, 'dr_name'))
        # 氏名(かな・カナ)
        if is_not_empty(parameter.dr_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.dr_name_kana]])
            # 全角カタカナを半角カタカナへ変換
            hankaku_katakana = mojimoji.zen_to_han(zenkaku_katakana)
            parameter.dr_name_kana = f'%{hankaku_katakana}%'
            where_clauses.append(SQLCondition('dr_name_kana', condition.LIKE, 'dr_name_kana'))
        # 勤務先コード
        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.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'))
        # 勤務先名(かな・カナ)
        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.prefc_cd):
            where_clauses.append(SQLCondition('com_inst.prefc_cd', condition.EQ, 'prefc_cd'))

        # 所属部科(漢字)
        if is_not_empty(parameter.blng_sec_name):
            # 必ず部分一致で検索
            parameter.blng_sec_name = f'%{parameter.blng_sec_name}%'
            where_clauses.append(SQLCondition(
                'com_blng_sec.blng_sec_name', condition.LIKE, 'blng_sec_name'))

        # 診療科目(漢字)
        if is_not_empty(parameter.trt_course_name):
            # 必ず部分一致で検索
            parameter.trt_course_name = f'%{parameter.trt_course_name}%'
            where_clauses.append(SQLCondition('trt_course_name', condition.LIKE, 'trt_course_name'))

        # 出身大学(漢字)
        if is_not_empty(parameter.alma):
            # 必ず部分一致で検索
            parameter.alma = f'%{parameter.alma}%'
            where_clauses.append(SQLCondition('alma', condition.LIKE, 'alma'))

        # 卒年
        if is_not_empty(parameter.grad_y):
            # 必ず部分一致で検索
            parameter.grad_y = f'%{parameter.grad_y}%'
            where_clauses.append(SQLCondition('grad_y', condition.LIKE, 'grad_y'))

        # 廃業除外
        if where_clauses:
            where_clauses.append(SQLCondition(
                '', '', '(length(com_inst.abolish_ymd) = 0 OR com_inst.abolish_ymd IS NULL)', literal=True))
            where_clauses.append(SQLCondition(
                '', '', '(length(com_dr.abolish_ymd) = 0 OR com_dr.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_dr.dcf_pcf_dr_cd,
            com_dr.dr_name,
            com_dr.dr_name_kana,
            com_sex.sex,
            com_dr.birthday,
            com_alma.alma,
            com_hometown.hometown,
            com_dr.grad_y,
            com_dr.drday_y,
            com_dr.estab_y
        FROM src05.com_dr
        LEFT JOIN src05.com_sex ON com_dr.sex_cd = com_sex.sex_cd
        LEFT JOIN src05.com_alma ON com_dr.alma_cd = com_alma.alma_cd
        LEFT JOIN src05.com_hometown ON com_dr.hometown_cd = com_hometown.hometown_cd
        WHERE dcf_pcf_dr_cd = :id
    """

    def fetch_one(self, id) -> UltmarcDoctorInfoDBModel:
        try:
            self._database.connect()
            query = self.FETCH_ONE_SQL
            result = self._database.execute_select(query, {'id': id})
            models = [UltmarcDoctorInfoDBModel(**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()
