import sqlite3, uuid
from datetime import datetime
from dataclasses import dataclass
from typing import List
from pypos.core.base_model import BaseModel
from pypos.core.utils.db_helper import connect_sqlite
from pypos.core.utils.path_utils import get_db_path
from pypos.core.database.schema_migrator import run_schema_migrations_once
from pypos.modules.penjualan.models.voucher_model import VoucherModel

@dataclass
class ReturnItem(BaseModel):
    produk_id: str
    produk_nama: str
    jumlah: int
    harga: float
    jenis_return: str  # full/partial

class ReturnModel(BaseModel):
    def __init__(self, db_path: str = None):
        super().__init__()
        self.db_path = db_path or get_db_path()
        self.conn = connect_sqlite(self.db_path)
        self.conn.row_factory = sqlite3.Row
        self._ensure_tables()
        self.voucher_model = VoucherModel(self.db_path)

    def _ensure_tables(self):
        run_schema_migrations_once(self.db_path, strict=False)
        self._ensure_return_columns()

    def _ensure_return_columns(self):
        run_schema_migrations_once(self.db_path, strict=False)

    # === Transaksi master search ===
    def search_transaksi_master(self, keyword: str, start_date: str = None, end_date: str = None, limit: int = 200):
        kw = f"%{keyword}%"
        params = [kw, kw, kw]
        date_clause = ""
        if start_date and end_date:
            date_clause = " AND date(dtime) BETWEEN ? AND ?"
            params.extend([start_date, end_date])
        params.append(int(limit))
        return self.conn.execute(
            """SELECT
                     id,
                     nomer,
                     dtime AS tanggal,
                     customers_nama,
                     (
                        transaksi_nilai - COALESCE(
                            (SELECT SUM(dr.harga * dr.jumlah)
                             FROM return_transaksi_penjualan r
                             JOIN detail_return_transaksi_penjualan dr ON dr.return_id = r.id
                             WHERE r.transaksi_id = transaksi.id
                            ), 0
                        )
                     ) AS transaksi_nilai
                   FROM transaksi
                   WHERE jenis_label = 'invoice'
                     AND COALESCE(trash, 0) = 0
                     AND (nomer LIKE ? OR dtime LIKE ? OR customers_nama LIKE ?)
                     {date_clause}
                   ORDER BY dtime DESC
                   LIMIT ?""".format(date_clause=date_clause),
            params
        ).fetchall()

    def load_transaksi_detail(self, transaksi_id: str):
        cur = self.conn.cursor()
        cur.execute(
            """
            SELECT produk_id, SUM(COALESCE(jumlah, 0)) AS qty_returned
            FROM detail_return_transaksi_penjualan dr
            JOIN return_transaksi_penjualan r ON r.id = dr.return_id
            WHERE r.transaksi_id = ?
            GROUP BY produk_id
            """,
            (transaksi_id,),
        )
        returned_map = {str(row[0]): int(row[1] or 0) for row in cur.fetchall()}

        cur.execute(
            "SELECT * FROM transaksi_data WHERE transaksi_id = ? AND COALESCE(trash, 0) = 0",
            (transaksi_id,),
        )
        rows = cur.fetchall()
        results = []
        for row in rows:
            data = dict(row)
            produk_id = str(data.get("produk_id"))
            qty_jual = int(data.get("produk_ord_jml") or 0)
            qty_returned = int(returned_map.get(produk_id, 0))
            qty_returnable = max(0, qty_jual - qty_returned)
            if qty_returnable <= 0:
                continue
            data["qty_returnable"] = qty_returnable
            data["qty_returned"] = qty_returned
            results.append(data)
        return results

    def _get_returnable_qty_map(self, transaksi_id: str):
        cur = self.conn.cursor()
        cur.execute(
            """
            SELECT produk_id, SUM(COALESCE(jumlah, 0)) AS qty_returned
            FROM detail_return_transaksi_penjualan dr
            JOIN return_transaksi_penjualan r ON r.id = dr.return_id
            WHERE r.transaksi_id = ?
            GROUP BY produk_id
            """,
            (transaksi_id,),
        )
        returned_map = {str(row[0]): int(row[1] or 0) for row in cur.fetchall()}

        cur.execute(
            "SELECT produk_id, SUM(COALESCE(produk_ord_jml, 0)) AS qty_jual FROM transaksi_data WHERE transaksi_id = ? AND COALESCE(trash, 0) = 0 GROUP BY produk_id",
            (transaksi_id,),
        )
        sold_rows = cur.fetchall()
        result = {}
        for row in sold_rows:
            produk_id = str(row[0])
            qty_jual = int(row[1] or 0)
            qty_returned = int(returned_map.get(produk_id, 0))
            result[produk_id] = max(0, qty_jual - qty_returned)
        return result

    def _normalize_return_items(self, transaksi_id: str, items: List[ReturnItem]):
        if not items:
            raise ValueError("Belum ada item return.")

        returnable_map = self._get_returnable_qty_map(transaksi_id)
        if not returnable_map:
            raise ValueError("Transaksi tidak memiliki item yang bisa direturn.")

        used_in_batch = {}
        normalized = []
        for it in items:
            produk_id = str(getattr(it, "produk_id", "") or "").strip()
            if not produk_id:
                raise ValueError("Produk return tidak valid.")

            try:
                qty = int(getattr(it, "jumlah", 0) or 0)
            except Exception:
                qty = 0
            if qty <= 0:
                continue

            max_qty = int(returnable_map.get(produk_id, 0))
            batch_used = int(used_in_batch.get(produk_id, 0))
            qty_available = max(0, max_qty - batch_used)
            if qty > qty_available:
                raise ValueError(
                    f"Qty return produk {getattr(it, 'produk_nama', produk_id)} melebihi sisa qty ({qty_available})."
                )

            try:
                harga = float(getattr(it, "harga", 0) or 0)
            except Exception:
                harga = 0.0
            if harga < 0:
                raise ValueError("Harga return tidak valid.")

            used_in_batch[produk_id] = batch_used + qty
            normalized.append(
                ReturnItem(
                    produk_id=produk_id,
                    produk_nama=str(getattr(it, "produk_nama", "") or ""),
                    jumlah=qty,
                    harga=harga,
                    jenis_return=str(getattr(it, "jenis_return", "partial") or "partial"),
                )
            )

        if not normalized:
            raise ValueError("Belum ada qty return yang valid.")

        # Cegah full-note return dari tab return penjualan.
        is_full_note = True
        for produk_id, qty_sisa in returnable_map.items():
            qty_batch = int(used_in_batch.get(str(produk_id), 0))
            if qty_batch < int(qty_sisa):
                is_full_note = False
                break
        if is_full_note:
            raise ValueError(
                "Return penuh satu nota tidak diizinkan. Gunakan tab Pembatalan Transaksi."
            )

        return normalized

    def insert_return(self, transaksi_id: str, items: List[ReturnItem], jenis_return: str = 'partial', refund_method: str = 'cash'):
        transaksi_id = str(transaksi_id or "").strip()
        if not transaksi_id:
            raise ValueError("Transaksi return tidak valid.")
        items = self._normalize_return_items(transaksi_id, items)

        total_return = sum(it.harga * it.jumlah for it in items)
        if total_return <= 0:
            raise ValueError("Total return harus lebih besar dari 0.")
        refund_method = str(refund_method or "cash").lower().strip()
        if refund_method not in ("cash", "voucher"):
            refund_method = "cash"
        prefix = "VCR" if refund_method == "voucher" else "RTR"
        kode_return = prefix + datetime.now().strftime("%Y%m%d%H%M%S") + uuid.uuid4().hex[:3].upper()

        cur = self.conn.cursor()
        self.log_debug(f"total return = {total_return}")
        # 1. Ambil customers_id berdasarkan transaksi_id
        cur.execute("SELECT customers_id, transaksi_nilai FROM transaksi WHERE id = ?", (transaksi_id,))
        row = cur.fetchone()
        if not row:
            raise ValueError("Transaksi tidak ditemukan.")
        customers_id = row[0] if row else None
        _nota_total = row[1] if row else 0

        # 2. Insert ke tabel master return
        cur.execute("""
            INSERT INTO return_transaksi_penjualan
            (transaksi_id, customer_id, tanggal_return, total_return, kode_voucher, nilai_voucher, jenis_return, refund_method, refund_amount)
            VALUES (?,?,?,?,?,?,?,?,?)
        """, (
            transaksi_id,
            customers_id,
            datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            total_return,
            kode_return,
            total_return,
            jenis_return,
            refund_method,
            total_return
        ))
        ret_id = cur.lastrowid


        # 3. Insert detail return
        for it in items:
            cur.execute("""
                INSERT INTO detail_return_transaksi_penjualan
                (return_id, produk_id, produk_nama, jumlah, jenis_return, harga, subtotal)
                VALUES (?,?,?,?,?,?,?)
            """, (
                ret_id,
                it.produk_id,
                it.produk_nama,
                it.jumlah,
                it.jenis_return,
                it.harga,
                it.harga * it.jumlah
            ))

        self.conn.commit()
        if refund_method == "voucher":
            self.voucher_model.create_voucher(
                kode_return,
                total_return,
                return_id=ret_id,
                transaksi_id=transaksi_id,
                customer_id=customers_id
            )
        return kode_return

    def close(self):
        self.conn.close()

    def get_history(self, keyword: str = "", start_date: str = None, end_date: str = None, limit: int = 200):
        kw = f"%{keyword}%"
        params = []
        date_clause = ""
        if start_date and end_date:
            date_clause = " AND date(r.tanggal_return) BETWEEN ? AND ?"
            params.extend([start_date, end_date])
        params.extend([kw, kw, kw, kw])
        params.append(int(limit))
        return self.conn.execute(
            """
            SELECT
                r.id AS return_id,
                r.tanggal_return,
                r.kode_voucher,
                r.total_return,
                r.refund_method,
                t.nomer AS nomer_nota,
                t.customers_nama,
                t.oleh_nama AS kasir_nama,
                (SELECT COUNT(*) FROM detail_return_transaksi_penjualan dr WHERE dr.return_id = r.id) AS jumlah_item
            FROM return_transaksi_penjualan r
            LEFT JOIN transaksi t ON t.id = r.transaksi_id
            WHERE 1=1
              {date_clause}
              AND (
                    COALESCE(r.kode_voucher, '') LIKE ?
                 OR COALESCE(t.nomer, '') LIKE ?
                 OR COALESCE(t.customers_nama, '') LIKE ?
                 OR COALESCE(t.oleh_nama, '') LIKE ?
              )
            ORDER BY r.tanggal_return DESC
            LIMIT ?
            """.format(date_clause=date_clause),
            params
        ).fetchall()