# edited by glg
import sqlite3
from typing import Any, Dict, List

from pypos.core.utils.sql_query_builder import build_sql_with_identifier_in_clause


class TransactionExportSnapshotQueryService:
    """
    Ekstraksi query snapshot transaksi agar hotspot service utama lebih kecil.
    """

    def __init__(self, export_service):
        self.export_service = export_service

    def fetch_transaksi_data_by_transaksi_ids(self, transaksi_ids: List[int], limit: int = 0):
        svc = self.export_service
        if not transaksi_ids:
            return []
        query, query_params = build_sql_with_identifier_in_clause(
            sql_prefix="SELECT * FROM transaksi_data WHERE",
            sql_suffix='ORDER BY "id" ASC',
            identifier="transaksi_id",
            values=transaksi_ids,
            cast_int=True,
            positive_only=True,
        )
        conn = svc._connect()
        try:
            cur = conn.cursor()
            params = list(query_params)
            if limit and int(limit) > 0:
                query += " LIMIT ?"
                params.append(int(limit))
            cur.execute(query, tuple(params))
            return [dict(r) for r in (cur.fetchall() or [])]
        finally:
            conn.close()

    def fetch_rows_by_foreign_key(
        self,
        table_name: str,
        key_column: str,
        key_values: List[int],
        order_column: str = "id",
        limit: int = 0,
    ):
        svc = self.export_service
        if not key_values:
            return []
        table_sql = svc._quote_identifier(table_name)
        order_sql = svc._quote_identifier(order_column)
        query_prefix = f"SELECT * FROM {table_sql} WHERE"  # nosec B608
        query_suffix = f"ORDER BY {order_sql} ASC"  # nosec B608
        query, query_params = build_sql_with_identifier_in_clause(
            sql_prefix=query_prefix,
            sql_suffix=query_suffix,
            identifier=key_column,
            values=key_values,
            cast_int=True,
            positive_only=True,
        )
        conn = svc._connect()
        try:
            cur = conn.cursor()
            params = list(query_params)
            if limit and int(limit) > 0:
                query += " LIMIT ?"
                params.append(int(limit))
            cur.execute(query, tuple(params))
            return [dict(r) for r in (cur.fetchall() or [])]
        finally:
            conn.close()

    def fetch_detail_return_rows_for_return_ids(self, return_ids: List[int]) -> List[Dict[str, Any]]:
        svc = self.export_service
        normalized_ids = svc._parse_int_list(return_ids)
        if not normalized_ids or not svc._table_exists("detail_return_transaksi_penjualan"):
            return []
        query, query_params = build_sql_with_identifier_in_clause(
            sql_prefix="SELECT * FROM detail_return_transaksi_penjualan WHERE",
            sql_suffix='ORDER BY "id" ASC',
            identifier="return_id",
            values=normalized_ids,
            cast_int=True,
            positive_only=True,
        )
        conn = svc._connect()
        try:
            cur = conn.cursor()
            cur.execute(query, query_params)
            rows = cur.fetchall() or []
            return [dict(row) for row in rows if isinstance(row, (sqlite3.Row, dict))]
        except sqlite3.Error:
            return []
        finally:
            conn.close()

    def fetch_produk_snapshot(self, produk_ids: List[int]) -> Dict[str, Dict[str, Any]]:
        svc = self.export_service
        normalized_ids: List[int] = []
        seen = set()
        for raw_id in produk_ids or []:
            parsed_id = svc._to_int(raw_id, 0)
            if parsed_id <= 0 or parsed_id in seen:
                continue
            seen.add(parsed_id)
            normalized_ids.append(parsed_id)
        if not normalized_ids or not svc._table_exists("produk"):
            return {}
        query, query_params = build_sql_with_identifier_in_clause(
            sql_prefix=(
                "SELECT id, nama, barcode, satuan, satuan_id, coa_code, hpp, tipe_pajak, ppn, ppn_produk "
                "FROM produk WHERE"
            ),
            sql_suffix="",
            identifier="id",
            values=normalized_ids,
            cast_int=True,
            positive_only=True,
        )
        conn = svc._connect()
        try:
            cur = conn.cursor()
            cur.execute(query, query_params)
            rows = cur.fetchall() or []
            mapped: Dict[str, Dict[str, Any]] = {}
            for row in rows:
                data = dict(row)
                mapped[str(svc._to_int(data.get("id"), 0))] = data
            return mapped
        finally:
            conn.close()

    def fetch_diskon_free_snapshot(self, produk_ids: List[int]) -> Dict[str, Dict[str, Any]]:
        svc = self.export_service
        normalized_ids: List[int] = []
        seen = set()
        for raw_id in produk_ids or []:
            parsed_id = svc._to_int(raw_id, 0)
            if parsed_id <= 0 or parsed_id in seen:
                continue
            seen.add(parsed_id)
            normalized_ids.append(parsed_id)
        if not normalized_ids or not svc._table_exists("diskon"):
            return {}
        query, params = build_sql_with_identifier_in_clause(
            sql_prefix=(
                "SELECT id, produk_id, free_produk_id, free_produk_nama, kelipatan, minim, quota_global, quota_used "
                "FROM diskon WHERE jenis = 'free_produk' AND"
            ),
            sql_suffix='ORDER BY "produk_id" ASC, "id" DESC',
            identifier="produk_id",
            values=normalized_ids,
            cast_int=True,
            positive_only=True,
        )
        conn = svc._connect()
        try:
            cur = conn.cursor()
            cur.execute(query, params)
            rows = cur.fetchall() or []
            mapped: Dict[str, Dict[str, Any]] = {}
            for row in rows:
                data = dict(row)
                key = str(svc._to_int(data.get("produk_id"), 0))
                if not key or key == "0" or key in mapped:
                    continue
                mapped[key] = data
            return mapped
        finally:
            conn.close()