from src.logging.get_logger import get_logger
from src.model.db.ultmarc_doctor_wrkplace import UltmarcDoctorWrkplaceDBModel
from src.model.db.ultmarc_doctor_wrkplace_count import \
    UltmarcDoctorWrkplaceCountDBModel
from src.repositories.base_repository import BaseRepository

logger = get_logger('COM_医師勤務先取得')


class UltmarcDoctorWrkplaceRepository(BaseRepository):

    FETCH_SQL = """\
        SELECT
            com_inst.dcf_dsf_inst_cd,
            com_inst.inst_name_kanji,
            com_blng_sec.blng_sec_name,
            univ_post.form_post_name AS univ_post_name,
            post.form_post_name AS post_name,
            com_dr_wrkplace.aply_start_ymd
        FROM src05.com_dr
        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_post as univ_post ON com_dr_wrkplace.identity_cd = univ_post.post_cd
        LEFT JOIN src05.com_post as post ON com_dr_wrkplace.post_cd = post.post_cd
        WHERE com_dr.dcf_pcf_dr_cd = :id
        ORDER BY com_dr_wrkplace.aply_start_ymd DESC
    """

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

    FETCH_COUNT_SQL = """\
        SELECT COUNT(*) AS count
        FROM src05.com_dr_wrkplace
        WHERE dcf_dsf_inst_cd = :id
    """

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