# edited by glg
import math
import sqlite3


class TransaksiLookupHargaService:
    """
    Domain service lookup harga/produk.
    Menjaga tanggung jawab query lookup terpisah dari orchestration simpan transaksi.
    """

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

    def get_produk_autocomplete(self, keyword="", limit=50):
        key = str(keyword or "").strip()
        m = self.model
        try:
            safe_limit = max(5, int(limit or 50))
        except (TypeError, ValueError):
            safe_limit = 50

        if not key:
            rows = m._fetch_autocomplete_rows(
                where_clause="1=1",
                order_clause="p.nama ASC",
                params=(safe_limit,),
            )
            return m._to_autocomplete_payload(rows)

        exact_key = key
        nama_prefix_start = key
        nama_prefix_end = f"{key}\uffff"
        contains_key = f"%{key}%"
        numeric_key = "".join(ch for ch in key if ch.isdigit())
        barcode_prefix_start = numeric_key if numeric_key else key
        barcode_prefix_end = f"{barcode_prefix_start}\uffff"
        where_clause = (
            "("
            "(p.nama >= ? AND p.nama < ?) OR "
            "(p.barcode >= ? AND p.barcode < ?) OR "
            "p.nama = ? OR p.barcode = ?"
            ")"
        )
        order_clause = (
            "CASE "
            "WHEN p.barcode = ? THEN 0 "
            "WHEN p.nama = ? THEN 1 "
            "WHEN (p.barcode >= ? AND p.barcode < ?) THEN 2 "
            "WHEN (p.nama >= ? AND p.nama < ?) THEN 3 "
            "ELSE 4 END, p.nama ASC"
        )
        rows = m._fetch_autocomplete_rows(
            where_clause=where_clause,
            order_clause=order_clause,
            params=(
                nama_prefix_start,
                nama_prefix_end,
                barcode_prefix_start,
                barcode_prefix_end,
                exact_key,
                exact_key,
                exact_key,
                exact_key,
                barcode_prefix_start,
                barcode_prefix_end,
                nama_prefix_start,
                nama_prefix_end,
                safe_limit,
            ),
        )

        if len(rows) < safe_limit and len(key) >= m._get_autocomplete_contains_min_keyword():
            existing_ids = {str(row["id"]) for row in rows}
            remaining = safe_limit - len(rows)
            if existing_ids:
                placeholders = ",".join(["?"] * len(existing_ids))
                exclude_clause = f" AND p.id NOT IN ({placeholders})"
                extra_params = tuple(existing_ids)
            else:
                exclude_clause = ""
                extra_params = ()
            contains_where = f"(p.nama LIKE ? OR p.barcode LIKE ?){exclude_clause}"
            contains_rows = m._fetch_autocomplete_rows(
                where_clause=contains_where,
                order_clause="p.nama ASC",
                params=(contains_key, contains_key) + extra_params + (remaining,),
            )
            rows.extend(contains_rows)

        return m._to_autocomplete_payload(rows[:safe_limit])

    # edited by glg
    @staticmethod
    def _build_result_template(id_produk, jumlah_beli):
        return {
            "id": id_produk,
            "barcode": None,
            "nama": None,
            "harga": None,
            "harga_normal": None,
            "flag_diskon_grosir": 0,
            "flag_diskon_free": 0,
            "diskon_persen": 0,
            "keterangan_diskon": "",
            "keterangan_diskon_free": "",
            "free_produk_id": None,
            "free_produk_nama": None,
            "kelipatan": None,
            "jumlah_free": 0,
            "jumlah": jumlah_beli,
            "hpp": None,
            "satuan": None,
        }

    # edited by glg
    @staticmethod
    def _build_diskon_scope(id_produk, cabang_id):
        cabang_diskon_clause = ""
        diskon_base_params = [id_produk]
        if int(cabang_id or 0) > 0:
            cabang_diskon_clause = (
                " AND (diskon.cabang_id = ? OR diskon.cabang_id IN (-1, 0) OR diskon.cabang_id IS NULL)"
            )
            diskon_base_params.append(cabang_id)
        diskon_active_clause = """
            AND COALESCE(diskon.status, 1) = 1
            AND COALESCE(diskon.trash, 0) = 0
            AND (
                (diskon.dtime_start IS NULL OR DATE('now') >= DATE(diskon.dtime_start))
                AND (diskon.dtime_end IS NULL OR DATE('now') <= DATE(diskon.dtime_end))
            )
        """
        return cabang_diskon_clause, diskon_base_params, diskon_active_clause

    # edited by glg
    @staticmethod
    def _query_produk(cursor, id_produk):
        cursor.execute(
            """
            SELECT nama, barcode, hpp, satuan
            FROM produk
            WHERE id = ?
            LIMIT 1
            """,
            (id_produk,),
        )
        return cursor.fetchone()

    # edited by glg
    @staticmethod
    def _apply_produk(result, produk):
        if not produk:
            return
        result.update(
            {
                "nama": produk["nama"],
                "barcode": produk["barcode"],
                "hpp": produk["hpp"],
                "satuan": produk["satuan"],
            }
        )

    # edited by glg
    @staticmethod
    def _apply_harga_list(m, cursor, result, id_produk, cabang_id):
        harga_list = m._fetch_latest_harga_list(cursor, id_produk, cabang_id=cabang_id)
        if harga_list <= 0:
            return
        result.update(
            {
                "harga": harga_list,
                "harga_normal": harga_list,
            }
        )

    # edited by glg
    @staticmethod
    def _query_grosir_exists(
        cursor,
        *,
        diskon_active_clause,
        cabang_diskon_clause,
        diskon_base_params,
    ):
        cursor.execute(
            f"""
            SELECT diskon.id
            FROM diskon
            WHERE diskon.produk_id = ? AND diskon.jenis = 'produk_grosir'
            {diskon_active_clause}
            {cabang_diskon_clause}
            ORDER BY diskon.minim ASC, diskon.id DESC
            LIMIT 1
            """,
            tuple(diskon_base_params),
        )
        return cursor.fetchone()

    # edited by glg
    @staticmethod
    def _query_grosir_nominal(
        cursor,
        *,
        jumlah_beli,
        diskon_active_clause,
        cabang_diskon_clause,
        diskon_base_params,
    ):
        diskon_nominal_params = list(diskon_base_params)
        diskon_nominal_params.append(jumlah_beli)
        cursor.execute(
            f"""
            SELECT
                diskon.id,
                diskon.persen,
                diskon.nilai,
                diskon.harga,
                (diskon.nilai + diskon.harga) AS harga_setelah_diskon
            FROM diskon
            WHERE diskon.produk_id = ? AND diskon.jenis = 'produk_grosir'
            {diskon_active_clause}
            {cabang_diskon_clause}
              AND ? BETWEEN diskon.minim AND COALESCE(NULLIF(diskon.maxim, 0), 10000)
            ORDER BY diskon.minim DESC, diskon.id DESC
            LIMIT 1
            """,
            tuple(diskon_nominal_params),
        )
        return cursor.fetchone()

    # edited by glg
    @staticmethod
    def _apply_grosir_nominal(m, result, nominal_grosir, id_produk, jumlah_beli):
        if not nominal_grosir:
            return
        harga_normal = m._as_float(result.get("harga"), 0.0)
        harga_setelah_diskon = m._as_float(nominal_grosir["harga_setelah_diskon"], 0.0)
        persen_val = m._as_float(nominal_grosir["persen"], 0.0)
        nilai_val = m._as_float(nominal_grosir["nilai"], 0.0)

        if m._is_reasonable_grosir_base(harga_normal, harga_setelah_diskon):
            if harga_normal <= 0 and harga_setelah_diskon > 0:
                result.update(
                    {
                        "harga": harga_setelah_diskon,
                        "harga_normal": harga_setelah_diskon,
                    }
                )
            result.update(
                {
                    "diskon_persen": persen_val,
                    "keterangan_diskon": f"Diskon {round(persen_val)}% = {round(nilai_val)}",
                }
            )
            return

        fallback_persen = 0.0
        fallback_nilai = 0.0
        if harga_normal > 0 and 0 < nilai_val < harga_normal:
            fallback_nilai = nilai_val
            fallback_persen = (nilai_val / harga_normal) * 100
        elif harga_normal > 0 and 0 < persen_val < 100:
            fallback_persen = persen_val
            fallback_nilai = (harga_normal * persen_val) / 100

        if fallback_persen > 0:
            result.update(
                {
                    "diskon_persen": fallback_persen,
                    "keterangan_diskon": f"Diskon {round(fallback_persen)}% = {round(fallback_nilai)}",
                }
            )
            m.log_warning(
                "[HargaGrosir] Tier anomali gunakan fallback nilai/persen "
                f"produk_id={id_produk} qty={jumlah_beli} "
                f"harga_normal={harga_normal} harga_tier={harga_setelah_diskon} "
                f"persen={persen_val} nilai={nilai_val}"
            )
            return

        m.log_warning(
            "[HargaGrosir] Abaikan tier tidak wajar "
            f"produk_id={id_produk} qty={jumlah_beli} "
            f"harga_normal={harga_normal} harga_tier={harga_setelah_diskon}"
        )

    # edited by glg
    def _apply_diskon_grosir(
        self,
        m,
        cursor,
        *,
        id_produk,
        jumlah_beli,
        result,
        diskon_active_clause,
        cabang_diskon_clause,
        diskon_base_params,
    ):
        grosir = self._query_grosir_exists(
            cursor,
            diskon_active_clause=diskon_active_clause,
            cabang_diskon_clause=cabang_diskon_clause,
            diskon_base_params=diskon_base_params,
        )
        if not grosir:
            return

        result.update(
            {
                "flag_diskon_grosir": 1,
                "diskon_persen": 0,
                "keterangan_diskon": "Diskon 0 % = 0",
            }
        )
        nominal_grosir = self._query_grosir_nominal(
            cursor,
            jumlah_beli=jumlah_beli,
            diskon_active_clause=diskon_active_clause,
            cabang_diskon_clause=cabang_diskon_clause,
            diskon_base_params=diskon_base_params,
        )
        self._apply_grosir_nominal(m, result, nominal_grosir, id_produk, jumlah_beli)

    # edited by glg
    @staticmethod
    def _query_diskon_free(
        cursor,
        *,
        diskon_active_clause,
        cabang_diskon_clause,
        diskon_base_params,
    ):
        cursor.execute(
            f"""
            SELECT
                diskon.free_produk_id,
                diskon.free_produk_nama,
                diskon.kelipatan,
                diskon.minim,
                produk.barcode,
                produk.hpp,
                produk.satuan
            FROM produk
            INNER JOIN diskon ON produk.id = diskon.produk_id
            WHERE produk.id = ? AND diskon.jenis = 'free_produk'
              {diskon_active_clause}
              {cabang_diskon_clause}
            ORDER BY diskon.id DESC LIMIT 1
            """,
            tuple(diskon_base_params),
        )
        return cursor.fetchone()

    # edited by glg
    @staticmethod
    def _query_diskon_free_nominal(
        cursor,
        *,
        jumlah_beli,
        diskon_active_clause,
        cabang_diskon_clause,
        diskon_base_params,
    ):
        free_nominal_params = list(diskon_base_params)
        free_nominal_params.append(jumlah_beli)
        cursor.execute(
            f"""
            SELECT
                diskon.free_produk_id,
                diskon.free_produk_nama,
                diskon.kelipatan,
                diskon.minim,
                produk.barcode,
                produk.hpp,
                produk.satuan
            FROM produk
            INNER JOIN diskon ON produk.id = diskon.produk_id
            WHERE produk.id = ? AND diskon.jenis = 'free_produk'
              {diskon_active_clause}
              {cabang_diskon_clause}
              AND ? BETWEEN diskon.minim AND COALESCE(NULLIF(diskon.maxim, 0), 10000)
            ORDER BY diskon.id DESC LIMIT 1
            """,
            tuple(free_nominal_params),
        )
        return cursor.fetchone()

    # edited by glg
    @staticmethod
    def _apply_diskon_free_base(result, free):
        result.update(
            {
                "flag_diskon_free": 1,
                "free_produk_id": free["free_produk_id"],
                "free_produk_nama": free["free_produk_nama"],
                "kelipatan": free["kelipatan"],
                "jumlah_free": 0,
                "barcode": free["barcode"],
                "hpp": free["hpp"],
                "satuan": free["satuan"],
            }
        )
        if free["kelipatan"] == 1:
            result.update(
                {
                    "keterangan_diskon_free": (
                        f'gratis produk {free["free_produk_nama"]} berlaku kelipatan setiap pembelian {free["minim"]}'
                    )
                }
            )
            return
        result.update(
            {
                "keterangan_diskon_free": (
                    f'gratis produk {free["free_produk_nama"]} tidak berlaku kelipatan minimal pembelian {free["minim"]}'
                )
            }
        )

    # edited by glg
    @staticmethod
    def _apply_diskon_free_nominal(m, result, nominal_free, jumlah_beli):
        if not nominal_free:
            return
        minim_free = m._as_positive_int(nominal_free["minim"], 1)
        result.update(
            {
                "flag_diskon_free": 1,
                "free_produk_id": nominal_free["free_produk_id"],
                "free_produk_nama": nominal_free["free_produk_nama"],
                "kelipatan": nominal_free["kelipatan"],
                "jumlah_free": math.floor(jumlah_beli / max(1, minim_free)),
                "barcode": nominal_free["barcode"],
                "hpp": nominal_free["hpp"],
                "satuan": nominal_free["satuan"],
            }
        )

    # edited by glg
    def _apply_diskon_free(
        self,
        m,
        cursor,
        *,
        jumlah_beli,
        result,
        diskon_active_clause,
        cabang_diskon_clause,
        diskon_base_params,
    ):
        free = self._query_diskon_free(
            cursor,
            diskon_active_clause=diskon_active_clause,
            cabang_diskon_clause=cabang_diskon_clause,
            diskon_base_params=diskon_base_params,
        )
        if not free:
            return
        m.log_debug(
            f"aktifkan flag diskon free nama={free['free_produk_nama']} kelipatan={free['kelipatan']}"
        )
        self._apply_diskon_free_base(result, free)
        nominal_free = self._query_diskon_free_nominal(
            cursor,
            jumlah_beli=jumlah_beli,
            diskon_active_clause=diskon_active_clause,
            cabang_diskon_clause=cabang_diskon_clause,
            diskon_base_params=diskon_base_params,
        )
        if nominal_free:
            m.log_debug("mau update nominal free")
        self._apply_diskon_free_nominal(m, result, nominal_free, jumlah_beli)

    # edited by glg
    @staticmethod
    def _apply_harga_fallback_if_missing(m, cursor, result, id_produk, cabang_id):
        if result.get("harga"):
            return
        harga_fallback = m._fetch_latest_harga_list(cursor, id_produk, cabang_id=cabang_id)
        if harga_fallback <= 0:
            return
        result.update(
            {
                "harga": harga_fallback,
                "harga_normal": harga_fallback,
            }
        )

    def cari_barang_by_id(self, id_produk, jumlah_beli):
        m = self.model
        conn = m._connect_read_fast()
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        result = self._build_result_template(id_produk=id_produk, jumlah_beli=jumlah_beli)
        try:
            cabang_id = m._get_active_cabang_id_for_pricing()
            produk = self._query_produk(cursor, id_produk)
            self._apply_produk(result, produk)
            self._apply_harga_list(m, cursor, result, id_produk, cabang_id)
            (
                cabang_diskon_clause,
                diskon_base_params,
                diskon_active_clause,
            ) = self._build_diskon_scope(id_produk=id_produk, cabang_id=cabang_id)
            self._apply_diskon_grosir(
                m,
                cursor,
                id_produk=id_produk,
                jumlah_beli=jumlah_beli,
                result=result,
                diskon_active_clause=diskon_active_clause,
                cabang_diskon_clause=cabang_diskon_clause,
                diskon_base_params=diskon_base_params,
            )
            self._apply_diskon_free(
                m,
                cursor,
                jumlah_beli=jumlah_beli,
                result=result,
                diskon_active_clause=diskon_active_clause,
                cabang_diskon_clause=cabang_diskon_clause,
                diskon_base_params=diskon_base_params,
            )
            self._apply_harga_fallback_if_missing(m, cursor, result, id_produk, cabang_id)
        except (sqlite3.Error, TypeError, ValueError, KeyError) as exc:
            m.log_error(f"DB Error: {exc}")
        finally:
            conn.close()

        return result