import sqlite3
from decimal import Decimal, InvalidOperation

from pypos.core.utils.db_helper import connect_sqlite
from pypos.modules.penjualan.models.pembayaran_model import PaymentResult


class PembayaranResultService:
    def __init__(self, db_path):
        self.db_path = db_path

    def _fallback_bank_info(self, mode, jenis_kartu=""):
        mode = str(mode or "").strip().lower()
        if mode == "tunai":
            return 101, "Tunai"
        if mode == "kredit":
            label = f"Credit {jenis_kartu}".strip() if jenis_kartu else "Credit"
            return 103, label
        if mode == "debit":
            label = f"Debit {jenis_kartu}".strip() if jenis_kartu else "Debit"
            return 104, label
        return 0, "-"

    # edited by glg
    def _to_decimal(self, value, default="0"):
        raw = value
        if isinstance(raw, str):
            raw = raw.strip().replace(",", ".")
        if raw in (None, ""):
            raw = default
        try:
            return Decimal(str(raw))
        except (InvalidOperation, ValueError, TypeError):
            return Decimal(str(default))

    # edited by glg
    def _safe_close_conn(self, conn):
        if conn is None:
            return
        try:
            conn.close()
        except sqlite3.Error:
            return

    def _query_bank_exact(self, candidates):
        clean = [str(x).strip() for x in candidates if str(x or "").strip()]
        if not clean:
            return None
        conn = None
        try:
            conn = connect_sqlite(self.db_path)
            conn.row_factory = None
            cur = conn.cursor()
            try:
                for name in clean:
                    cur.execute(
                        "SELECT id, nama FROM bank WHERE lower(nama)=lower(?) ORDER BY id ASC LIMIT 1",
                        (name,),
                    )
                    row = cur.fetchone()
                    if row:
                        return int(row[0]), str(row[1] or "")
            finally:
                cur.close()
            return None
        except sqlite3.Error:
            return None
        finally:
            self._safe_close_conn(conn)

    def _query_bank_like_mode(self, mode):
        mode = str(mode or "").strip().lower()
        conn = None
        try:
            conn = connect_sqlite(self.db_path)
            conn.row_factory = None
            cur = conn.cursor()
            try:
                if mode == "tunai":
                    cur.execute(
                        "SELECT id, nama FROM bank WHERE lower(nama) LIKE '%tunai%' ORDER BY id ASC LIMIT 1"
                    )
                    row = cur.fetchone()
                    if row:
                        return int(row[0]), str(row[1] or "")
                    return None
                if mode == "kredit":
                    cur.execute(
                        """
                        SELECT id, nama
                        FROM bank
                        WHERE lower(nama) NOT LIKE '%edc%'
                          AND lower(nama) NOT LIKE '%tunai%'
                          AND lower(nama) NOT LIKE '%debit%'
                        ORDER BY id ASC
                        LIMIT 1
                        """
                    )
                    row = cur.fetchone()
                    if row:
                        return int(row[0]), str(row[1] or "")
                    return None
                if mode == "debit":
                    cur.execute(
                        """
                        SELECT id, nama
                        FROM bank
                        WHERE lower(nama) NOT LIKE '%edc%'
                          AND lower(nama) NOT LIKE '%tunai%'
                          AND lower(nama) LIKE '%debit%'
                        ORDER BY id ASC
                        LIMIT 1
                        """
                    )
                    row = cur.fetchone()
                    if row:
                        return int(row[0]), str(row[1] or "")
                    return None
                return None
            finally:
                cur.close()
            return None
        except sqlite3.Error:
            return None
        finally:
            self._safe_close_conn(conn)

    def resolve_bank_info(self, mode, jenis_kartu=""):
        mode_norm = str(mode or "").strip().lower()
        fallback_id, fallback_nama = self._fallback_bank_info(mode_norm, jenis_kartu)
        if mode_norm == "tunai":
            candidates = ["Tunai", fallback_nama]
        elif mode_norm == "kredit":
            candidates = [jenis_kartu, f"Credit {jenis_kartu}", fallback_nama]
        elif mode_norm == "debit":
            candidates = [jenis_kartu, f"Debit {jenis_kartu}", fallback_nama]
        else:
            candidates = [fallback_nama]

        exact = self._query_bank_exact(candidates)
        if exact:
            return exact
        like_mode = self._query_bank_like_mode(mode_norm)
        if like_mode:
            return like_mode
        return fallback_id, fallback_nama

    def create_result(
        self,
        mode,
        jumlah_dibayar,
        total_dibayar,
        diskon_persen,
        diskon_nilai=0.0,
        jenis_edc="",
        jenis_kartu="",
        kartu="",
        approval_code="",
        voucher_code="",
        voucher_amount=0.0,
    ):
        metode = str(mode or "").strip().lower()
        if metode == "credit":
            metode = "kredit"
        bank_id, bank_nama = self.resolve_bank_info(metode, jenis_kartu=jenis_kartu)
        jumlah_dibayar_dec = self._to_decimal(jumlah_dibayar, "0")
        total_dibayar_dec = self._to_decimal(total_dibayar, "0")
        diskon_nilai_dec = max(Decimal("0"), self._to_decimal(diskon_nilai, "0"))
        diskon_persen_dec = max(Decimal("0"), self._to_decimal(diskon_persen, "0"))
        voucher_amount_dec = max(Decimal("0"), self._to_decimal(voucher_amount, "0"))
        kembali_dec = Decimal("0")
        if metode == "tunai":
            kembali_dec = jumlah_dibayar_dec - total_dibayar_dec
        diskon_nilai_real = float(diskon_nilai_dec)
        diskon_persen_real = float(diskon_persen_dec)
        jumlah_dibayar_real = float(jumlah_dibayar_dec)
        total_dibayar_real = float(total_dibayar_dec)
        kembali_real = float(kembali_dec)

        return PaymentResult(
            metode=metode,
            jumlah_dibayar=jumlah_dibayar_real,
            total_harus_dibayar=total_dibayar_real,
            kembalian=kembali_real,
            # edited by glg
            # diskon_rp wajib nominal real (bukan persen) untuk konsistensi payload export.
            diskon_rp=diskon_nilai_real,
            diskon_member_persen=diskon_persen_real,
            diskon_tambahan_persen=diskon_persen_real,
            diskon_tambahan_nilai=diskon_nilai_real,
            total_dibayar=total_dibayar_real,
            kembali=kembali_real,
            kartu=str(kartu or ""),
            approval_code=str(approval_code or ""),
            jenis_edc=str(jenis_edc or ""),
            jenis_kartu=str(jenis_kartu or ""),
            settlement_id=1,
            bank_id=int(bank_id or 0),
            bank_nama=str(bank_nama or "-"),
            jumlah_bayar=jumlah_dibayar_real,
            voucher_code=str(voucher_code or ""),
            voucher_amount=float(voucher_amount_dec),
        )