import sqlite3, math
from datetime import datetime
from dataclasses import dataclass

@dataclass
class DetailTransaksi:
    produk_id: int
    produk_nama: str
    produk_ord_hrg: float
    produk_ord_jml: int
    produk_jenis: str
    produk_ord_diskon: float

    def __iter__(self):
        return iter((
            self.produk_id,
            self.produk_nama,
            self.produk_ord_hrg,
            self.produk_ord_jml,
            self.produk_jenis,
            self.produk_ord_diskon,
        ))

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

    def get_produk_autocomplete(self):
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        cursor.execute("""
            SELECT p.id, p.nama, 
                   MAX(CASE WHEN price.jenis_value = 'harga_list' THEN price.nilai ELSE NULL END) AS harga_jual
            FROM produk p
            INNER JOIN price ON p.id = price.produk_id
            GROUP BY p.id, p.nama
            ORDER BY p.nama;
        """)
        results = cursor.fetchall()
        conn.close()

        barang_list = []
        mapping = {}
        for row in results:
            text = f"{row['nama']} - {row['harga_jual']}"
            barang_list.append(text)
            mapping[text] = {
                "id": row["id"], "nama": row["nama"], "harga_jual": row["harga_jual"]
            }
        return barang_list, mapping

    def cari_barang_by_id(self, id_produk, jumlah_beli):
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        result = {
            "id": id_produk,
            "barcode" : None,
            "nama": None,
            "harga": None,
            "harga_normal": None,
            "flag_diskon_grosir": 0,
            "flag_diskon_free": 0,
            "diskon_persen": 0,
            "keterangan_diskon": "",
            "free_produk_nama": None,
            "kelipatan": None,
            "jumlah_free": 0,
            "jumlah": jumlah_beli
        }

        try:
            cursor.execute("""
                SELECT produk.nama, diskon.persen, diskon.nilai, diskon.harga,
                       (diskon.nilai + diskon.harga) AS harga_setelah_diskon,produk.barcode
                FROM produk
                INNER JOIN diskon ON produk.id = diskon.produk_id
                WHERE produk.id = ? AND diskon.jenis = 'produk_grosir'
                  AND ? BETWEEN diskon.minim AND COALESCE(NULLIF(diskon.maxim, 0), 10000)
                ORDER BY diskon.id DESC LIMIT 1
            """, (id_produk, jumlah_beli))
            grosir = cursor.fetchone()
            if grosir:
                result.update({
                    
                    "nama" : grosir["nama"],
                    "flag_diskon_grosir": 1,
                    "harga": grosir["harga_setelah_diskon"],
                    "diskon_persen": grosir["persen"],
                    "keterangan_diskon": f"Diskon {grosir['persen']}% = {grosir['nilai']}",
                    "barcode" : grosir["barcode"]
                })

            cursor.execute("""
                SELECT diskon.free_produk_nama, diskon.kelipatan, diskon.minim , produk.barcode
                FROM produk
                INNER JOIN diskon ON produk.id = diskon.produk_id
                WHERE produk.id = ? AND diskon.jenis = 'free_produk'
                  AND DATE('now') BETWEEN diskon.dtime_start AND diskon.dtime_end
                  AND ? BETWEEN diskon.minim AND COALESCE(NULLIF(diskon.maxim, 0), 10000)
                ORDER BY diskon.id DESC LIMIT 1
            """, (id_produk, jumlah_beli))
            free = cursor.fetchone()
            if free:
                result.update({
                    "flag_diskon_free": 1,
                    "free_produk_nama": free["free_produk_nama"],
                    "kelipatan": free["kelipatan"],
                    "jumlah_free": math.floor(jumlah_beli / free["minim"]),
                    "barcode" : free["barcode"]
                })

            if not result["harga"]:
                cursor.execute("""
                    SELECT p.nama, 
                           MAX(CASE WHEN price.jenis_value = 'harga_list' THEN price.nilai END) AS harga_jual,p.barcode
                    FROM produk p
                    INNER JOIN price ON p.id = price.produk_id
                    WHERE p.id = ?
                    GROUP BY p.nama LIMIT 1
                """, (id_produk,))
                produk = cursor.fetchone()
                if produk:
                    result.update({
                        "harga": produk["harga_jual"],
                        "harga_normal": produk["harga_jual"],
                        "nama": produk["nama"],
                        "barcode" : produk["barcode"]
                    })
        except Exception as e:
            print("DB Error:", e)
        finally:
            conn.close()

        return result
    def cari_barang_by_nama(self, nama_barang):
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM produk WHERE nama LIKE ?", (f"%{nama_barang}%",))
        row = cursor.fetchone()
        conn.close()
        return dict(row) if row else None
    def cari_barang_by_barcode(self, barcode):
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM produk WHERE barcode = ?", (barcode,))
        row = cursor.fetchone()
        conn.close()
        return dict(row) if row else None

    def simpan_transaksi(self, transaksi_data, detail_data):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        print('masuk ke simpan transaksi')
        try:
            cursor.execute("""
                INSERT INTO transaksi_copy (nomer, dtime, transaksi_nilai, diskon, ppn, total_harga, 
                customers_id, customers_nama, fulldate, oleh_id, oleh_nama, jenis_label, transaksi_jenis, settlement_id)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, transaksi_data)
            transaksi_id = cursor.lastrowid

            # for detail in detail_data:
            #     cursor.execute("""
            #         INSERT INTO transaksi_data_copy (
            #             transaksi_id, produk_id, produk_nama, produk_ord_hrg,
            #             produk_ord_jml, produk_jenis, produk_ord_diskon
            #         ) VALUES (?, ?, ?, ?, ?, ?, ?)
            #     """, (transaksi_id, *detail))

            for detail in detail_data:
                cursor.execute("""
                    INSERT INTO transaksi_data_copy (
                        transaksi_id, produk_id, produk_nama, produk_ord_hrg,
                        produk_ord_jml, produk_jenis, produk_ord_diskon
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                """, detail.to_tuple_with_transaksi_id(transaksi_id))
                
            conn.commit()
            return transaksi_id
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            conn.close()

# def simpan_transaksi_f9(self, transaksi_data, detail_data):
#     # Pastikan index 11 (ke-12) ada sebelum kita set
#     if len(transaksi_data) > 11:
#         transaksi_data[11] = 'simpan_transaksi'
#     else:
#         raise ValueError("Data transaksi tidak memiliki cukup elemen untuk disimpan (butuh minimal 12 elemen)")

#     conn = sqlite3.connect(self.db_path)
#     cursor = conn.cursor()
#     try:
#         cursor.execute("""
#             INSERT INTO transaksi_copy (
#                 nomer, dtime, transaksi_nilai, diskon, ppn, total_harga, 
#                 customers_id, customers_nama, fulldate, oleh_id, oleh_nama, 
#                 jenis_label, transaksi_jenis, settlement_id
#             )
#             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
#         """, transaksi_data)

#         transaksi_id = cursor.lastrowid

#         for detail in detail_data:
#             cursor.execute("""
#                 INSERT INTO transaksi_data_copy (
#                     transaksi_id, produk_id, produk_nama, produk_ord_hrg,
#                     produk_ord_jml, produk_jenis, produk_ord_diskon
#                 ) VALUES (?, ?, ?, ?, ?, ?, ?)
#             """, (transaksi_id, *detail))

#         conn.commit()
#         return transaksi_id
#     except Exception as e:
#         conn.rollback()
#         raise e
#     finally:
#         conn.close()
    def simpan_transaksi_f9(self, transaksi_data, detail_data):
    
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        try:
            cursor.execute("""
                INSERT INTO transaksi_copy (
                    nomer, dtime, transaksi_nilai, diskon, ppn, total_harga, 
                    customers_id, customers_nama, fulldate, oleh_id, oleh_nama, 
                    jenis_label, transaksi_jenis, settlement_id
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, transaksi_data)

            transaksi_id = cursor.lastrowid

            # for detail in detail_data:
            #     cursor.execute("""
            #         INSERT INTO transaksi_data_copy (
            #             transaksi_id, produk_id, produk_nama, produk_ord_hrg,
            #             produk_ord_jml, produk_jenis, produk_ord_diskon
            #         ) VALUES (?, ?, ?, ?, ?, ?, ?)
            #     """, (transaksi_id, *detail))
            for detail in detail_data:
                cursor.execute("""
                    INSERT INTO transaksi_data_copy (
                        transaksi_id, produk_id, produk_nama, produk_ord_hrg,
                        produk_ord_jml, produk_jenis, produk_ord_diskon
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                """, detail.to_tuple_with_transaksi_id(transaksi_id))

            conn.commit()
            return transaksi_id
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            conn.close()