# edited by glg
import re
import sqlite3
import tempfile
from pathlib import Path

import pytest

from pypos.core.utils import sql_query_builder
from pypos.modules.penjualan.models.settlement_model import SettlementModel


pytestmark = [pytest.mark.unit]


def _seed_basic_schema(db_path):
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS transaksi (
            id INTEGER PRIMARY KEY,
            nomer TEXT,
            dtime TEXT,
            customers_nama TEXT,
            oleh_id INTEGER DEFAULT 0,
            oleh_nama TEXT,
            cabang_id INTEGER DEFAULT 0,
            cabang_nama TEXT,
            transaksi_nilai REAL,
            transaksi_bulat REAL,
            transaksi_dibayar REAL,
            settlement_id INTEGER,
            bank_nama TEXT,
            pembayaran_non_tunai REAL DEFAULT 0,
            trash INTEGER DEFAULT 0
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS transaksi_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            transaksi_id TEXT,
            produk_id TEXT,
            produk_ord_hrg REAL,
            produk_ord_jml INTEGER,
            trash INTEGER DEFAULT 0
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS transaksi_settlement (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            counter TEXT,
            oleh_id INTEGER,
            oleh_dtime TEXT,
            approval_id INTEGER,
            approval_counter TEXT,
            approval_nama TEXT,
            status INTEGER,
            data_transaksi_id TEXT,
            cabang_id INTEGER,
            oleh_nama TEXT,
            cabang_nama TEXT
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS settlement_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            tanggal TEXT,
            admin TEXT,
            kasir TEXT,
            total_harus REAL DEFAULT 0,
            total_disetor REAL DEFAULT 0,
            total_non_tunai REAL DEFAULT 0,
            total_refund_cash REAL DEFAULT 0,
            selisih REAL DEFAULT 0,
            status TEXT,
            data_transaksi_id TEXT
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS return_transaksi_penjualan (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            transaksi_id TEXT NOT NULL,
            refund_method TEXT NULL,
            refund_amount REAL NULL
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS detail_return_transaksi_penjualan (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            return_id INTEGER NOT NULL,
            produk_id TEXT NOT NULL,
            jumlah INTEGER NOT NULL DEFAULT 0,
            harga REAL NOT NULL DEFAULT 0
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS voucher_return (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            return_id INTEGER NULL,
            transaksi_id TEXT NULL,
            customer_id TEXT NULL,
            kode TEXT UNIQUE NOT NULL,
            nilai_awal REAL NOT NULL,
            saldo REAL NOT NULL,
            status TEXT NOT NULL DEFAULT 'aktif',
            dtime_terbit TEXT NOT NULL,
            dtime_expired TEXT NULL
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS voucher_usage (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            voucher_id INTEGER NOT NULL,
            transaksi_id TEXT NOT NULL,
            nilai_pakai REAL NOT NULL,
            dtime TEXT NOT NULL
        )
        """
    )
    cur.executemany(
        """
        INSERT INTO transaksi
        (id, nomer, dtime, customers_nama, oleh_id, oleh_nama, cabang_id, cabang_nama,
         transaksi_nilai, transaksi_bulat, transaksi_dibayar, settlement_id, bank_nama, pembayaran_non_tunai, trash)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)
        """,
        [
            (
                1,
                "INV-TUNAI-001",
                "2026-04-16 10:00:00",
                "Cust A",
                10,
                "Kasir A",
                101,
                "Cabang A",
                100000,
                100000,
                100000,
                1,
                "Tunai",
                0,
            ),
            (
                2,
                "INV-DEBIT-001",
                "2026-04-16 10:05:00",
                "Cust B",
                10,
                "Kasir A",
                101,
                "Cabang A",
                50000,
                50000,
                50000,
                1,
                "Debit",
                50000,
            ),
        ],
    )
    conn.commit()
    conn.close()


def _legacy_quote_identifier_without_qualified_support(identifier, strict=True):
    _ = strict
    ident = str(identifier or "").strip()
    if not ident or "." in ident:
        raise ValueError("Identifier SQL tidak valid: %s" % ident)
    if not re.match(r"^[A-Za-z_][A-Za-z0-9_]*$", ident):
        raise ValueError("Identifier SQL tidak valid: %s" % ident)
    return '"%s"' % ident


def test_settlement_atomic_tetap_jalan_saat_helper_identifier_legacy(monkeypatch):
    with tempfile.TemporaryDirectory(ignore_cleanup_errors=True) as td:
        db_path = str(Path(td) / "settlement_identifier_legacy.db")
        _seed_basic_schema(db_path)

        monkeypatch.setattr(
            sql_query_builder,
            "quote_sql_identifier",
            _legacy_quote_identifier_without_qualified_support,
        )

        model = SettlementModel(db_path)
        result = model.execute_settlement_atomic(
            kasir_id=10,
            kasir_nama="Kasir A",
            cabang_id=101,
            cabang_nama="Cabang A",
            transaksi_list=[{"tanggal": "2026-04-16", "kasir": "Kasir A"}],
            approval_id=99,
            approval_nama="admin",
            transaksi_ids_override=[1, 2],
            admin="admin",
            total_disetor=100000,
            trace_id="compat-legacy-identifier",
        )

        assert bool((result or {}).get("ok")) is True
        assert str((result or {}).get("counter") or "").startswith("ST-")


def test_ringkasan_settlement_tetap_terhitung_saat_helper_identifier_legacy(monkeypatch):
    with tempfile.TemporaryDirectory(ignore_cleanup_errors=True) as td:
        db_path = str(Path(td) / "settlement_identifier_summary.db")
        _seed_basic_schema(db_path)

        monkeypatch.setattr(
            sql_query_builder,
            "quote_sql_identifier",
            _legacy_quote_identifier_without_qualified_support,
        )

        model = SettlementModel(db_path)
        totals = model.hitung_total_per_metode_by_ids([1, 2])
        details = model.get_transaksi_by_ids([1, 2])

        assert round(float(totals.get("Tunai") or 0), 2) == 100000.0
        assert round(float(totals.get("Non Tunai") or 0), 2) == 50000.0
        assert len(details) == 2