import sqlite3
from datetime import datetime
from decimal import Decimal, InvalidOperation

from pypos.core.base_model import BaseModel
from pypos.core.database.schema_migrator import run_schema_migrations_once
from pypos.core.utils.db_helper import connect_sqlite
from pypos.core.utils.path_utils import get_db_path


class VoucherModel(BaseModel):
    def __init__(self, db_path: str = None):
        super().__init__()
        self.db_path = db_path or get_db_path()
        self._ensure_tables()

    def _ensure_tables(self):
        # edited by glg
        # Fail-fast migrasi schema agar error startup tidak laten.
        ok = run_schema_migrations_once(self.db_path, strict=False)
        if bool(ok):
            return
        message = (
            "VOUCHER_SCHEMA_MIGRATION_FAILED: "
            f"run_schema_migrations_once(strict=False) mengembalikan False untuk db_path={self.db_path}"
        )
        self.log_error(message)
        raise RuntimeError(message)

    # edited by glg
    def _to_decimal(self, value, default="0"):
        try:
            return Decimal(str(value if value is not None else default))
        except (InvalidOperation, ValueError, TypeError):
            return Decimal(str(default))

    # edited by glg
    # Dukungan koneksi eksternal agar create_voucher bisa ikut transaksi atomik caller.
    def create_voucher(
        self,
        kode: str,
        nilai: float,
        return_id=None,
        transaksi_id=None,
        customer_id=None,
        conn=None,
    ):
        own_conn = conn is None
        conn = conn or connect_sqlite(self.db_path)
        try:
            cur = conn.cursor()
            cur.execute("PRAGMA table_info(voucher_return)")
            cols = [row[1] for row in cur.fetchall()]
            nilai_dec = max(Decimal("0"), self._to_decimal(nilai, "0"))
            nilai_float = float(nilai_dec)
            if "nilai" in cols:
                cur.execute(
                    """
                    INSERT INTO voucher_return
                    (return_id, transaksi_id, customer_id, kode, nilai, nilai_awal, saldo, status, dtime_terbit)
                    VALUES (?, ?, ?, ?, ?, ?, ?, 'aktif', ?)
                    """,
                    (
                        return_id,
                        transaksi_id,
                        customer_id,
                        kode,
                        nilai_float,
                        nilai_float,
                        nilai_float,
                        datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                    ),
                )
            else:
                cur.execute(
                    """
                    INSERT INTO voucher_return
                    (return_id, transaksi_id, customer_id, kode, nilai_awal, saldo, status, dtime_terbit)
                    VALUES (?, ?, ?, ?, ?, ?, 'aktif', ?)
                    """,
                    (
                        return_id,
                        transaksi_id,
                        customer_id,
                        kode,
                        nilai_float,
                        nilai_float,
                        datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                    ),
                )
            if own_conn:
                conn.commit()
            return cur.lastrowid
        except (sqlite3.Error, RuntimeError, TypeError, ValueError, InvalidOperation):
            if own_conn:
                try:
                    conn.rollback()
                except (sqlite3.Error, RuntimeError, AttributeError, TypeError):
                    pass
            raise
        finally:
            if own_conn:
                conn.close()

    def get_voucher(self, kode: str):
        conn = connect_sqlite(self.db_path)
        conn.row_factory = sqlite3.Row
        try:
            cur = conn.cursor()
            cur.execute(
                """
                SELECT id, kode, nilai_awal, saldo, status, dtime_terbit, dtime_expired
                FROM voucher_return
                WHERE kode = ?
                """,
                (kode,),
            )
            row = cur.fetchone()
            return dict(row) if row else None
        finally:
            conn.close()

    # edited by glg
    # Dukungan koneksi eksternal agar apply_usage bisa ikut transaksi atomik caller.
    def apply_usage(self, kode: str, transaksi_id: str, nilai_pakai: float, conn=None):
        own_conn = conn is None
        conn = conn or connect_sqlite(self.db_path)
        conn.row_factory = sqlite3.Row
        try:
            cur = conn.cursor()
            cur.execute(
                "SELECT id, saldo, status FROM voucher_return WHERE kode = ?",
                (kode,),
            )
            row = cur.fetchone()
            if not row:
                return False, "Voucher tidak ditemukan."
            if str(row["status"]).lower() != "aktif":
                return False, "Voucher sudah tidak aktif."

            saldo_dec = max(Decimal("0"), self._to_decimal(row["saldo"], "0"))
            nilai_pakai_dec = max(Decimal("0"), self._to_decimal(nilai_pakai, "0"))
            if nilai_pakai_dec <= Decimal("0"):
                return False, "Nilai voucher tidak valid."
            if nilai_pakai_dec > saldo_dec:
                return False, "Saldo voucher tidak mencukupi."

            cur.execute(
                """
                INSERT INTO voucher_usage (voucher_id, transaksi_id, nilai_pakai, dtime)
                VALUES (?, ?, ?, ?)
                """,
                (
                    row["id"],
                    transaksi_id,
                    float(nilai_pakai_dec),
                    datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                ),
            )
            saldo_baru_dec = saldo_dec - nilai_pakai_dec
            status_baru = "habis" if saldo_baru_dec <= Decimal("0") else "aktif"
            cur.execute(
                "UPDATE voucher_return SET saldo = ?, status = ? WHERE id = ?",
                (float(max(Decimal("0"), saldo_baru_dec)), status_baru, row["id"]),
            )
            if own_conn:
                conn.commit()
            return True, float(max(Decimal("0"), saldo_baru_dec))
        except (sqlite3.Error, RuntimeError, TypeError, ValueError, InvalidOperation):
            if own_conn:
                try:
                    conn.rollback()
                except (sqlite3.Error, RuntimeError, AttributeError, TypeError):
                    pass
            raise
        finally:
            if own_conn:
                conn.close()