import sqlite3

from pypos.core.base_model import BaseModel
from pypos.core.utils.db_helper import connect_sqlite
from pypos.core.utils.device_utils import get_active_device_info, get_device_id
from pypos.core.utils.path_utils import get_db_path
from pypos.core.utils.sql_query_builder import render_sql_template


class DiskonModel(BaseModel):
    def __init__(self, base_url=None):
        super().__init__()
        self.base_url = str(base_url or "").rstrip("/")
        self.db_path = get_db_path()

    # edited by glg
    def _get_active_cabang_id(self):
        try:
            device_info = get_active_device_info(get_device_id()) or {}
            return int(device_info.get("cabang_id") or 0)
        except Exception:
            return 0

    def get_keterangan_grosir(self, produk_id):
        conn = connect_sqlite(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        try:
            cabang_id = self._get_active_cabang_id()
            cabang_clause = ""
            params = [produk_id]
            if cabang_id > 0:
                cabang_clause = (
                    " AND (diskon.cabang_id = ? OR diskon.cabang_id IN (-1, 0) OR diskon.cabang_id IS NULL)"
                )
                params.append(cabang_id)

            query_ket = render_sql_template(
                """
                SELECT
                    diskon.id AS diskon_id,
                    produk.barcode,
                    diskon.kelipatan,
                    produk.id AS produk_id,
                    produk.nama,
                    diskon.persen,
                    diskon.dtime AS diskon_dtime,
                    diskon.minim,
                    CASE
                        WHEN diskon.maxim = 0 THEN 100000
                        ELSE diskon.maxim
                    END AS maxim,
                    diskon.harga AS harga_diskon,
                    diskon.jenis AS jenis_diskon,
                    diskon.nilai + diskon.harga AS harga_jual,
                    diskon.nilai
                FROM produk
                INNER JOIN diskon ON produk.id = diskon.produk_id
                WHERE produk.id = ?
                AND diskon.jenis = 'produk_grosir'
                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))
                )
                {cabang_clause}
                ORDER BY diskon.minim ASC, diskon.id DESC
                """,
                cabang_clause=cabang_clause,
            )
            cursor.execute(query_ket, tuple(params))
            return [dict(row) for row in cursor.fetchall()]
        except Exception as exc:
            self.log_error(f"Error get_keterangan_grosir: {exc}")
            return []
        finally:
            cursor.close()
            conn.close()

    def get_keterangan_free(self, produk_id):
        conn = connect_sqlite(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        try:
            cabang_id = self._get_active_cabang_id()
            cabang_clause = ""
            params = [produk_id]
            if cabang_id > 0:
                cabang_clause = (
                    " AND (diskon.cabang_id = ? OR diskon.cabang_id IN (-1, 0) OR diskon.cabang_id IS NULL)"
                )
                params.append(cabang_id)

            query_ket = render_sql_template(
                """
                SELECT 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'
                  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))
                  )
                  {cabang_clause}
                ORDER BY diskon.id DESC LIMIT 1
                """,
                cabang_clause=cabang_clause,
            )
            cursor.execute(query_ket, tuple(params))
            return [dict(row) for row in cursor.fetchall()]
        except Exception as exc:
            self.log_error(f"Error get_keterangan_free: {exc}")
            return []
        finally:
            cursor.close()
            conn.close()