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.master_mente_count import MasterMenteCountModel
from src.model.request.master_mainte_csvdl import MasterMainteCsvDlModel
from src.repositories.base_repository import BaseRepository
from src.util.string_util import is_not_empty

logger = get_logger('従業員担当施設マスタ')


class EmpChgInstRepository(BaseRepository):

    def to_jst(self):
        self._database.to_jst()

    def begin(self):
        self._database.begin()

    def commit(self):
        self._database.commit()

    def rollback(self):
        self._database.rollback()

    INSERT_SQL = """\
        INSERT INTO {table_name}
        (
            inst_cd,
            ta_cd,
            emp_chg_type_cd,
            emp_cd,
            bu_cd,
            start_date,
            end_date,
            main_chg_flg,
            enabled_flg,
            creater,
            create_date,
            updater,
            update_date
        )
        VALUES (
            :inst_cd,
            :ta_cd,
            :emp_chg_type_cd,
            :emp_cd,
            :bu_cd,
            :start_date,
            :end_date,
            '1',
            'Y',
            :create_user_name,
            NOW(),
            :update_user_name,
            NOW()
        )
    """

    def insert_emp_chg_inst(self, inst_cd, ta_cd, emp_chg_type_cd, emp_cd, bu_cd, start_date,
                            end_date, create_user_name, table_name):
        try:
            query = self.INSERT_SQL.format(table_name=table_name)
            self._database.execute(query, {
                'inst_cd': inst_cd,
                'ta_cd': ta_cd,
                'emp_chg_type_cd': emp_chg_type_cd,
                'emp_cd': emp_cd,
                'bu_cd': bu_cd,
                'start_date': start_date,
                'end_date': end_date,
                'create_user_name': create_user_name,
                'update_user_name': create_user_name
            })
        except Exception as e:
            logger.exception(f'DB Error : Exception={e.args}')
            raise e

    UPDATE_END_DATE_SQL = """\
        UPDATE
            {table_name}
        SET
            end_date = :end_date,
            updater = :update_user_name,
            update_date = NOW()
        WHERE
                inst_cd = :inst_cd
            AND ta_cd = :ta_cd
            AND emp_chg_type_cd = :emp_chg_type_cd
            AND start_date = :start_date
    """

    def end_emp_chg_inst(self, inst_cd, ta_cd, emp_chg_type_cd, start_date,
                         end_date, update_user_name, table_name):
        try:
            query = self.UPDATE_END_DATE_SQL.format(table_name=table_name)
            self._database.execute(query, {
                'inst_cd': inst_cd,
                'ta_cd': ta_cd,
                'emp_chg_type_cd': emp_chg_type_cd,
                'start_date': start_date,
                'end_date': end_date,
                'update_user_name': update_user_name
            })
        except Exception as e:
            logger.exception(f'DB Error : Exception={e.args}')
            raise e

    UPDATE_EMP_CD_SQL = """\
        UPDATE
            {table_name}
        SET
            emp_cd = :emp_cd,
            updater = :update_user_name,
            update_date = NOW()
        where
                inst_cd = :inst_cd
            AND ta_cd = :ta_cd
            AND emp_chg_type_cd = :emp_chg_type_cd
            AND start_date = :start_date
    """

    def modify_emp_chg_inst(self, inst_cd, ta_cd, start_date, emp_chg_type_cd, emp_cd, update_user_name, table_name):
        try:
            query = self.UPDATE_EMP_CD_SQL.format(table_name=table_name)
            self._database.execute(query, {
                'inst_cd': inst_cd,
                'ta_cd': ta_cd,
                'emp_chg_type_cd': emp_chg_type_cd,
                'start_date': start_date,
                'emp_cd': emp_cd,
                'update_user_name': update_user_name
            })
        except Exception as e:
            logger.exception(f'DB Error : Exception={e.args}')
            raise e

    FETCH_COUNT_SQL = """\
        SELECT
            COUNT(*) AS count
        FROM
            {table_name}
        WHERE
                inst_cd = :inst_cd
            AND ta_cd = :ta_cd
            AND emp_chg_type_cd = :emp_chg_type_cd
            AND start_date = :start_date
    """

    def fetch_count(self, inst_cd, ta_cd, emp_chg_type_cd, start_date, table_name) -> MasterMenteCountModel:
        try:
            query = self.FETCH_COUNT_SQL.format(table_name=table_name)
            result = self._database.execute_select(query, {'inst_cd': inst_cd,  'ta_cd': ta_cd,
                                                           'emp_chg_type_cd': emp_chg_type_cd, 'start_date': start_date})
            models = [MasterMenteCountModel(**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

    FETCH_SQL = """\
        SELECT DISTINCT
            eci.inst_cd AS inst_cd,
            mi.inst_name AS inst_name,
            eci.ta_cd AS ta_cd,
            eci.emp_chg_type_cd AS emp_chg_type_cd,
            eci.emp_cd AS emp_cd,
            CONCAT(emp.emp_name_family, " ", emp.emp_name_first) AS emp_name_full,
            eci.bu_cd AS bu_cd,
            bu.bu_name AS bu_name,
            eci.start_date AS start_date,
            eci.end_date AS end_date,
            eci.creater AS creater,
            eci.create_date AS create_date,
            eci.updater AS updater,
            eci.update_date AS update_date
        FROM
            {table_name} AS eci
        LEFT JOIN mst_inst AS mi
            ON eci.inst_cd = mi.inst_cd
        LEFT JOIN emp
            ON eci.emp_cd = emp.emp_cd
        LEFT JOIN bu
            ON eci.bu_cd = bu.bu_cd
        WHERE
            {where_clause}
    """

    def fetch_as_data_frame(self, table_name: str, parameter: MasterMainteCsvDlModel):
        try:
            where_clause = self.__build_condition(parameter)
            query = self.FETCH_SQL.format(table_name=table_name, where_clause=where_clause)
            logger.debug(f'SQL: {query}')
            df = self._to_data_frame(query, parameter)
            logger.debug(f'count= {df.shape[0]}')
            return df
        except Exception as e:
            logger.exception(f'DB Error : Exception={e.args}')
            raise e

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

        # 検索条件が指定されずにSQLが壊れることを予防するため、常に真の固定条件を追加しておく
        where_clauses.append(SQLCondition('', '', '1 = 1', literal=True))

        # 領域コードが入力されていた場合
        if is_not_empty(parameter.ta_cd):
            parameter.adapt_ta_cd = f'%{parameter.ta_cd}%'
            where_clauses.append(SQLCondition('eci.ta_cd', condition.LIKE, 'adapt_ta_cd'))

        # 施設コードが入力されていた場合
        if is_not_empty(parameter.inst_cd):
            parameter.adapt_inst_cd = f'%{parameter.inst_cd}%'
            where_clauses.append(SQLCondition('eci.inst_cd', condition.LIKE, 'adapt_inst_cd'))

        # MUIDが入力されていた場合
        if is_not_empty(parameter.emp_cd):
            parameter.adapt_emp_cd = f'%{parameter.emp_cd}%'
            where_clauses.append(SQLCondition('eci.emp_cd', condition.LIKE, 'adapt_emp_cd'))

        # 担当者種別コードが入力されていた場合
        if is_not_empty(parameter.emp_chg_type_cd):
            parameter.adapt_emp_chg_type_cd = f'%{parameter.emp_chg_type_cd}%'
            where_clauses.append(SQLCondition('eci.emp_chg_type_cd', condition.LIKE, 'adapt_emp_chg_type_cd'))

        # 適用期間内が入力されていた場合
        if is_not_empty(parameter.adapt_apply_date_from):
            where_clauses.append(SQLCondition('eci.start_date', condition.LE, 'adapt_apply_date_from'))
            where_clauses.append(SQLCondition('eci.end_date', condition.GE, 'adapt_apply_date_from'))

        # 適用開始日（FROM）が入力されていた場合
        if is_not_empty(parameter.adapt_start_date_from):
            where_clauses.append(SQLCondition('eci.start_date', condition.GE, 'adapt_start_date_from'))

        # 適用開始日（TO）が入力されていた場合
        if is_not_empty(parameter.adapt_start_date_to):
            where_clauses.append(SQLCondition('eci.start_date', condition.LE, 'adapt_start_date_to'))

        # 適用終了日（FROM）が入力されていた場合
        if is_not_empty(parameter.adapt_end_date_from):
            where_clauses.append(SQLCondition('eci.end_date', condition.GE, 'adapt_end_date_from'))

        # 適用終了日（TO）が入力されていた場合
        if is_not_empty(parameter.adapt_end_date_to):
            where_clauses.append(SQLCondition('eci.end_date', condition.LE, 'adapt_end_date_to'))

        # データ作成日（FROM）が入力されていた場合
        if is_not_empty(parameter.adapt_create_date_from):
            where_clauses.append(SQLCondition('eci.create_date', condition.GE, 'adapt_create_date_from'))

        # データ作成日（TO）が入力されていた場合
        if is_not_empty(parameter.adapt_create_date_to):
            where_clauses.append(SQLCondition('eci.create_date', condition.LE, 'adapt_create_date_to'))

        # データ更新日（FROM）が入力されていた場合
        if is_not_empty(parameter.adapt_update_date_from):
            where_clauses.append(SQLCondition('eci.update_date', condition.GE, 'adapt_update_date_from'))

        # データ更新日（TO）が入力されていた場合
        if is_not_empty(parameter.adapt_update_date_to):
            where_clauses.append(SQLCondition('eci.update_date', condition.LE, 'adapt_update_date_to'))

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

        logger.debug(f'条件設定終了:{where_clauses_str}')
        return where_clauses_str

    DELETE_SQL = "DELETE FROM emp_chg_inst_wrk"

    def delete_dummy_table(self):
        try:
            query = self.DELETE_SQL
            self._database.execute(query)
        except Exception as e:
            logger.exception(f'DB Error : Exception={e.args}')
            raise e

    COPY_TABLE_SQL = "INSERT INTO emp_chg_inst_wrk SELECT * FROM emp_chg_inst"

    def copy_real_to_dummy(self):
        try:
            query = self.COPY_TABLE_SQL
            self._database.execute(query)
        except Exception as e:
            logger.exception(f'DB Error : Exception={e.args}')
            raise e
