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.bio_sales_lot import (BioSalesLotCountDBModel,
                                        BioSalesLotDBModel)
from src.model.request.bio import BioModel
from src.repositories.base_repository import BaseRepository
from src.system_var import environment
from src.util.string_util import is_not_empty

logger = get_logger('生物由来参照')


class BioSalesLotRepository(BaseRepository):
    FETCH_SQL = """\
    SELECT
        data_kind,
        slip_mgt_num,
        rec_ymd,
        rec_whs_cd,
        rec_whs_sub_cd,
        whs_name,
        rec_whs_org_cd,
        rec_urag_num,
        rev_hsdnymd_srk,
        rec_tran_kbn,
        tran_kbn_name,
        mkr_cd,
        rec_comm_cd,
        product_name,
        whs_rep_comm_name,
        nonyu_fcl_cd,
        rec_nonyu_fcl_name,
        whs_rep_nonyu_fcl_name,
        rec_nonyu_fcl_addr,
        whs_rep_nonyu_fcl_addr,
        rec_lot_num,
        qty,
        expr_dt,
        data_kbn,
        err_dtl_kind,
        bef_slip_mgt_num,
        ins_usr,
        ins_dt,
        inst_cd,
        inst_name_form,
        address,
        tel_num,
        -- Excel出力のため、数値型のコード値を文字列として取得(Veeva卸コード、Veeva卸組織コード、Veeva取引区分コード)
        CAST(v_whs_cd AS CHAR) AS v_whs_cd,
        CAST(v_whsorg_cd AS CHAR) AS v_whsorg_cd,
        whs_org_name,
        CAST(v_tran_cd AS CHAR) AS v_tran_cd,
        iko_flg
    FROM
        src05.bio_sales_lot
    WHERE
        {where_clause}
    ORDER BY
        rec_whs_cd,
        rec_whs_sub_cd,
        rev_hsdnymd_srk,
        slip_mgt_num
        ASC
    LIMIT {limit}\
    """

    def fetch_many(self, parameter: BioModel) -> list[BioSalesLotDBModel]:
        try:
            logger.debug('DB参照実行')
            where_clause = self.__build_condition(parameter)
            # ページングで取得するため、LIMIT,OFFSETを指定
            limit_clause = self.__build_paging_limit_clauses(parameter)
            query = self.FETCH_SQL.format(where_clause=where_clause, limit=limit_clause)
            logger.debug(f'SQL: {query}')
            result = self._database.execute_select(query, parameter.model_dump())
            models = [BioSalesLotDBModel(**r) for r in result]
            return models
        except Exception as e:
            logger.exception(f"DB Error : Exception={e.args}")
            raise e

    def fetch_as_data_frame(self, parameter: BioModel, limitation: int):
        try:
            logger.debug('DB参照実行')
            where_clause = self.__build_condition(parameter)
            query = self.FETCH_SQL.format(where_clause=where_clause, limit=limitation)
            logger.debug(f'SQL: {query}')
            df = self._to_data_frame(query, parameter)
            logger.debug(f'count= {len(df.index)}')
            # ログ出力のため、クエリも返却
            return df, query
        except Exception as e:
            logger.exception(f"DB Error : Exception={e.args}")
            raise e

    COUNT_SQL = """\
    SELECT
        COUNT(*) AS count
    FROM
        (
            SELECT 1
            FROM src05.bio_sales_lot
            WHERE
                {where_clause}
            LIMIT {limit}
        ) AS t\
    """

    def fetch_count(self, parameter: BioModel) -> list[BioSalesLotDBModel]:
        try:
            logger.debug('DB参照実行')
            where_clause = self.__build_condition(parameter)
            # システムとしての最大取得件数 + 1まで取る
            query = self.COUNT_SQL.format(where_clause=where_clause, limit=environment.BIO_SEARCH_RESULT_MAX_COUNT + 1)
            logger.debug(f'SQL: {query}')
            result = self._database.execute_select(query, parameter.model_dump())
            models = [BioSalesLotCountDBModel(**r) for r in result]
            count = models[0].count if len(models) > 0 else 0
            logger.debug(f'count= {count}')
            return count
        except Exception as e:
            logger.exception(f"DB Error : Exception={e.args}")
            raise e

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

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

        # 卸(コード/サブコード)
        if is_not_empty(parameter.rec_whs_cd) and is_not_empty(parameter.rec_whs_sub_cd):
            where_clauses.append(SQLCondition('rec_whs_cd', condition.EQ, 'rec_whs_cd'))
            where_clauses.append(SQLCondition('rec_whs_sub_cd', condition.EQ, 'rec_whs_sub_cd'))
        # データ種別
        if is_not_empty(parameter.slip_org_kbn):
            where_clauses.append(SQLCondition('slip_org_kbn', condition.EQ, 'slip_org_kbn'))
        # 処理日 開始日
        if is_not_empty(parameter.rec_ymd_from):
            where_clauses.append(SQLCondition('rec_ymd', condition.GE, 'rec_ymd_from'))
        # 処理日 終了日
        if is_not_empty(parameter.rec_ymd_to):
            where_clauses.append(SQLCondition('rec_ymd', condition.LE, 'rec_ymd_to'))
        # ロット番号
        if is_not_empty(parameter.rec_lot_num):
            rec_lot_num = parameter.rec_lot_num
            # あいまい検索文字列('%')が含まれる場合は'LIKE'、でなければ'='で検索
            rec_lot_num_comparator = condition.LIKE if '%' in rec_lot_num else condition.EQ
            where_clauses.append(SQLCondition('TRIM(rec_lot_num)', rec_lot_num_comparator, 'rec_lot_num'))
        # データ区分
        if is_not_empty(parameter.data_kbn):
            where_clauses.append(SQLCondition('data_kbn', condition.EQ, 'data_kbn'))
        # 製品
        if is_not_empty(parameter.mkr_cd):
            where_clauses.append(SQLCondition('mkr_cd', condition.EQ, 'mkr_cd'))
        # 発伝年月日 開始日
        if is_not_empty(parameter.rev_hsdnymd_srk_from):
            where_clauses.append(SQLCondition('rev_hsdnymd_srk', condition.GE, 'rev_hsdnymd_srk_from'))
        # 発伝年月日 終了日
        if is_not_empty(parameter.rev_hsdnymd_srk_to):
            where_clauses.append(SQLCondition('rev_hsdnymd_srk', condition.LE, 'rev_hsdnymd_srk_to'))
        # 移行フラグ
        # チェックが入っていない場合、移行対象(IKO_FLG = '*')を省く
        if parameter.iko_flg is None:
            where_clauses.append(SQLCondition('iko_flg', condition.IS, 'NULL', literal=True))
        # 固定条件
        # Viewで返されるロット番号9件をNull以外で抽出
        where_clauses.append(SQLCondition('LENGTH(TRIM(rec_lot_num))', condition.GT, '0', literal=True))

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

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

    def __build_paging_limit_clauses(self, parameter: BioModel) -> str:
        page_size = parameter.pageSize or 0
        page_number = parameter.pageNumber or 0

        return f'{page_size} OFFSET {(page_number - 1) * page_size}'
