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_view import BioSalesViewModel
from src.model.request.bio import BioModel
from src.repositories.base_repository import BaseRepository
from src.util.string_util import is_not_empty

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


class BioSalesViewRepository(BaseRepository):
    FETCH_SQL = """\
    SELECT
        (
            CASE
                WHEN LEFT(bs.v_tran_cd, 1) = 2
                AND bs.qty >= 1 THEN CONCAT('-', bs.qty)
                ELSE bs.qty
            END
        ) AS amt_fugo,
        bs.*,
        ln.ser_num,
        ln.lot_num,
        ln.expr_dt
    FROM
        src05.bio_sales_view bs
    LEFT OUTER JOIN
        src05.lot_num_mst ln
         ON bs.mkr_cd = ln.ser_num
         AND bs.rec_lot_num = ln.lot_num
    WHERE
        {where_clause}
    ORDER BY
        bs.rec_whs_cd,
        bs.rec_whs_sub_cd,
        bs.rev_hsdnymd_srk,
        bs.slip_mgt_num
        ASC\
    """

    def fetch_many(self, parameter: BioModel) -> list[BioSalesViewModel]:
        try:
            self._database.connect()
            logger.debug('DB参照実行')
            where_clause = self.__build_condition(parameter)
            query = self.FETCH_SQL.format(where_clause=where_clause)
            logger.debug(f'SQL: {query}')
            result = self._database.execute_select(query, parameter.dict())
            logger.debug(f'count= {len(result)}')
            models = [BioSalesViewModel(**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 fetch_as_data_frame(self, parameter: BioModel):
        try:
            self._database.connect()
            logger.debug('DB参照実行')
            where_clause = self.__build_condition(parameter)
            query = self.FETCH_SQL.format(where_clause=where_clause)
            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
        finally:
            self._database.disconnect()

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

        # 卸(コード/サブコード)
        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 rec_lot_num in '%' else condition.EQ
            where_clauses.append(SQLCondition('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
