package com.example.barokahstore.data

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import java.text.SimpleDateFormat
import java.util.Calendar
import java.util.Date
import java.util.Locale

class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_NAME = "barokah_store.db"
        private const val DATABASE_VERSION = 3

        // Singleton instance
        @Volatile
        private var instance: DatabaseHelper? = null

        fun getInstance(context: Context): DatabaseHelper {
            return instance ?: synchronized(this) {
                instance ?: DatabaseHelper(context.applicationContext).also { instance = it }
            }
        }
    }

    override fun onCreate(db: SQLiteDatabase) {
        // Create tables
        db.execSQL("""
            CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL,
                name TEXT NOT NULL,
                role TEXT NOT NULL DEFAULT 'kasir'
            )
        """)

        db.execSQL("""
            CREATE TABLE categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE
            )
        """)

        db.execSQL("""
            CREATE TABLE products (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                barcode TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL,
                buy_price REAL NOT NULL DEFAULT 0.0,
                sell_price REAL NOT NULL DEFAULT 0.0,
                stock INTEGER NOT NULL DEFAULT 0,
                unit TEXT NOT NULL DEFAULT 'Pcs',
                category_id INTEGER,
                FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
            )
        """)

        db.execSQL("""
            CREATE TABLE transactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                invoice_no TEXT NOT NULL UNIQUE,
                user_id INTEGER NOT NULL,
                total_amount REAL NOT NULL DEFAULT 0.0,
                discount REAL NOT NULL DEFAULT 0.0,
                grand_total REAL NOT NULL DEFAULT 0.0,
                pay REAL NOT NULL DEFAULT 0.0,
                change_amount REAL NOT NULL DEFAULT 0.0,
                payment_type TEXT NOT NULL DEFAULT 'cash',
                customer_name TEXT,
                due_date TEXT,
                status TEXT NOT NULL DEFAULT 'paid',
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
            )
        """)

        db.execSQL("""
            CREATE TABLE transaction_details (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                transaction_id INTEGER NOT NULL,
                product_id INTEGER,
                product_name TEXT NOT NULL DEFAULT '',
                price REAL NOT NULL,
                qty INTEGER NOT NULL,
                subtotal REAL NOT NULL,
                FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
                FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
            )
        """)

        db.execSQL("""
            CREATE TABLE receivables (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                transaction_id INTEGER,
                customer_name TEXT NOT NULL,
                total_amount REAL NOT NULL,
                paid_amount REAL NOT NULL DEFAULT 0.0,
                due_date TEXT,
                notes TEXT,
                status TEXT NOT NULL DEFAULT 'unpaid',
                paid_by TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
            )
        """)

        db.execSQL("""
            CREATE TABLE receivable_payments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                receivable_id INTEGER NOT NULL,
                pay_amount REAL NOT NULL,
                payment_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                note TEXT,
                FOREIGN KEY (receivable_id) REFERENCES receivables(id) ON DELETE CASCADE
            )
        """)

        db.execSQL("""
            CREATE TABLE attendance (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                check_in TEXT NOT NULL,
                check_out TEXT,
                date TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'present',
                notes TEXT,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        """)

        db.execSQL("""
            CREATE TABLE settings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                store_name TEXT NOT NULL DEFAULT 'Toko Barokah',
                store_address TEXT NOT NULL,
                store_phone TEXT NOT NULL DEFAULT '',
                receipt_header TEXT,
                receipt_footer TEXT,
                low_stock_threshold INTEGER NOT NULL DEFAULT 10
            )
        """)

        // Create indexes
        db.execSQL("CREATE INDEX idx_attendance_date ON attendance(date)")
        db.execSQL("CREATE INDEX idx_transactions_created_at ON transactions(created_at)")
        db.execSQL("CREATE INDEX idx_receivables_due_date ON receivables(due_date)")
        db.execSQL("CREATE INDEX idx_receivables_status ON receivables(status)")
        db.execSQL("CREATE INDEX idx_receivables_status_due_date ON receivables(status, due_date)")

        // Seed data
        seedData(db)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Drop existing tables and recreate
        db.execSQL("DROP TABLE IF EXISTS settings")
        db.execSQL("DROP TABLE IF EXISTS attendance")
        db.execSQL("DROP TABLE IF EXISTS receivable_payments")
        db.execSQL("DROP TABLE IF EXISTS receivables")
        db.execSQL("DROP TABLE IF EXISTS transaction_details")
        db.execSQL("DROP TABLE IF EXISTS transactions")
        db.execSQL("DROP TABLE IF EXISTS products")
        db.execSQL("DROP TABLE IF EXISTS categories")
        db.execSQL("DROP TABLE IF EXISTS users")
        onCreate(db)
    }

    override fun onConfigure(db: SQLiteDatabase) {
        super.onConfigure(db)
        db.setForeignKeyConstraintsEnabled(true)
    }

    private fun seedData(db: SQLiteDatabase) {
        // Users
        db.execSQL("INSERT INTO users (id, username, password, name, role) VALUES (1, 'admin', 'admin', 'Administrator', 'admin')")
        db.execSQL("INSERT INTO users (id, username, password, name, role) VALUES (2, 'kasir', 'kasir', 'Kasir Toko', 'kasir')")

        // Settings
        db.execSQL("""
            INSERT INTO settings (id, store_name, store_address, store_phone, receipt_header, receipt_footer, low_stock_threshold)
            VALUES (1, 'Toko Barokah', 'Jl. Barokah Raya No. 45, Jakarta', '081234567890', 'Terima Kasih Telah Berbelanja', 'Barang yang sudah dibeli tidak dapat ditukar/dikembalikan.', 10)
        """)

        // Categories
        db.execSQL("INSERT INTO categories (id, name) VALUES (1, 'Makanan')")
        db.execSQL("INSERT INTO categories (id, name) VALUES (2, 'Minuman')")
        db.execSQL("INSERT INTO categories (id, name) VALUES (3, 'Kebutuhan Rumah')")
        db.execSQL("INSERT INTO categories (id, name) VALUES (4, 'Lain-lain')")

        // Products
        db.execSQL("INSERT INTO products (barcode, name, buy_price, sell_price, stock, unit, category_id) VALUES ('8992741901115', 'Indomie Goreng Spesial', 2600.0, 3100.0, 120, 'Bks', 1)")
        db.execSQL("INSERT INTO products (barcode, name, buy_price, sell_price, stock, unit, category_id) VALUES ('8992741901116', 'Indomie Rasa Soto Lamongan', 2600.0, 3100.0, 8, 'Bks', 1)")
        db.execSQL("INSERT INTO products (barcode, name, buy_price, sell_price, stock, unit, category_id) VALUES ('8991389221151', 'Aqua Gelas 220ml', 500.0, 1000.0, 240, 'Pcs', 2)")
        db.execSQL("INSERT INTO products (barcode, name, buy_price, sell_price, stock, unit, category_id) VALUES ('8991038100111', 'Teh Botol Sosro 450ml', 3500.0, 4500.0, 45, 'Botol', 2)")
        db.execSQL("INSERT INTO products (barcode, name, buy_price, sell_price, stock, unit, category_id) VALUES ('8999999039912', 'Pepsodent Pencegah Gigi Berlubang 190g', 12500.0, 15000.0, 15, 'Pcs', 3)")
        db.execSQL("INSERT INTO products (barcode, name, buy_price, sell_price, stock, unit, category_id) VALUES ('8992696404455', 'Lifebuoy Sabun Mandi Cair Mild Care 400ml', 22000.0, 26500.0, 5, 'Pcs', 3)")
        db.execSQL("INSERT INTO products (barcode, name, buy_price, sell_price, stock, unit, category_id) VALUES ('8998866100224', 'Gula Pasir Gulaku 1kg', 14500.0, 17000.0, 25, 'Bks', 1)")
        db.execSQL("INSERT INTO products (barcode, name, buy_price, sell_price, stock, unit, category_id) VALUES ('8992759600109', 'Minyak Goreng Bimoli 2L', 28000.0, 33500.0, 18, 'Pouch', 1)")
        db.execSQL("INSERT INTO products (barcode, name, buy_price, sell_price, stock, unit, category_id) VALUES ('8993005118123', 'Susu Kental Manis Frisian Flag Cokelat', 11000.0, 13000.0, 12, 'Kaleng', 1)")
        db.execSQL("INSERT INTO products (barcode, name, buy_price, sell_price, stock, unit, category_id) VALUES ('8991001101235', 'Beras Ramos 5kg', 62000.0, 71500.0, 14, 'Karung', 1)")
    }

    // --- USER MANAGEMENT ---
    fun login(username: String, password: String): User? {
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT id, username, name, role FROM users WHERE username = ? AND password = ?", arrayOf(username, password))
        var user: User? = null
        if (cursor.moveToFirst()) {
            user = User(
                cursor.getInt(0),
                cursor.getString(1),
                cursor.getString(2),
                cursor.getString(3)
            )
        }
        cursor.close()
        return user
    }

    fun getAllUsers(): List<User> {
        val list = mutableListOf<User>()
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT id, username, name, role FROM users ORDER BY id ASC", null)
        while (cursor.moveToNext()) {
            list.add(User(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3)))
        }
        cursor.close()
        return list
    }

    fun insertUser(username: String, passwordText: String, name: String, role: String): Boolean {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put("username", username)
            put("password", passwordText)
            put("name", name)
            put("role", role)
        }
        return db.insert("users", null, values) != -1L
    }

    fun updateUser(id: Int, username: String, name: String, role: String, passwordText: String? = null): Boolean {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put("username", username)
            put("name", name)
            put("role", role)
            if (!passwordText.isNullOrEmpty()) {
                put("password", passwordText)
            }
        }
        return db.update("users", values, "id = ?", arrayOf(id.toString())) > 0
    }

    fun deleteUser(id: Int): Boolean {
        val db = this.writableDatabase
        return db.delete("users", "id = ?", arrayOf(id.toString())) > 0
    }

    // --- CATEGORIES MANAGEMENT ---
    fun getAllCategories(): List<Category> {
        val list = mutableListOf<Category>()
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT id, name FROM categories ORDER BY name ASC", null)
        while (cursor.moveToNext()) {
            list.add(Category(cursor.getInt(0), cursor.getString(1)))
        }
        cursor.close()
        return list
    }

    fun insertCategory(name: String): Boolean {
        val db = this.writableDatabase
        val values = ContentValues().apply { put("name", name) }
        return db.insert("categories", null, values) != -1L
    }

    fun updateCategory(id: Int, name: String): Boolean {
        val db = this.writableDatabase
        val values = ContentValues().apply { put("name", name) }
        return db.update("categories", values, "id = ?", arrayOf(id.toString())) > 0
    }

    fun deleteCategory(id: Int): Boolean {
        val db = this.writableDatabase
        return db.delete("categories", "id = ?", arrayOf(id.toString())) > 0
    }

    // --- PRODUCTS MANAGEMENT ---
    fun getAllProducts(): List<Product> {
        val list = mutableListOf<Product>()
        val db = this.readableDatabase
        val cursor = db.rawQuery("""
            SELECT p.id, p.barcode, p.name, p.buy_price, p.sell_price, p.stock, p.unit, p.category_id, c.name as category_name
            FROM products p
            LEFT JOIN categories c ON p.category_id = c.id
            ORDER BY p.name ASC
        """, null)
        while (cursor.moveToNext()) {
            list.add(
                Product(
                    cursor.getInt(0),
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getDouble(3),
                    cursor.getDouble(4),
                    cursor.getInt(5),
                    cursor.getString(6),
                    if (cursor.isNull(7)) null else cursor.getInt(7),
                    cursor.getString(8)
                )
            )
        }
        cursor.close()
        return list
    }

    fun getProductByBarcode(barcode: String): Product? {
        val db = this.readableDatabase
        val cursor = db.rawQuery("""
            SELECT p.id, p.barcode, p.name, p.buy_price, p.sell_price, p.stock, p.unit, p.category_id, c.name as category_name
            FROM products p
            LEFT JOIN categories c ON p.category_id = c.id
            WHERE p.barcode = ?
        """, arrayOf(barcode))
        var product: Product? = null
        if (cursor.moveToFirst()) {
            product = Product(
                cursor.getInt(0),
                cursor.getString(1),
                cursor.getString(2),
                cursor.getDouble(3),
                cursor.getDouble(4),
                cursor.getInt(5),
                cursor.getString(6),
                if (cursor.isNull(7)) null else cursor.getInt(7),
                cursor.getString(8)
            )
        }
        cursor.close()
        return product
    }

    fun insertProduct(barcode: String, name: String, buyPrice: Double, sellPrice: Double, stock: Int, unit: String, categoryId: Int?): Boolean {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put("barcode", barcode)
            put("name", name)
            put("buy_price", buyPrice)
            put("sell_price", sellPrice)
            put("stock", stock)
            put("unit", unit)
            if (categoryId != null) put("category_id", categoryId) else putNull("category_id")
        }
        return db.insert("products", null, values) != -1L
    }

    fun updateProduct(id: Int, barcode: String, name: String, buyPrice: Double, sellPrice: Double, stock: Int, unit: String, categoryId: Int?): Boolean {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put("barcode", barcode)
            put("name", name)
            put("buy_price", buyPrice)
            put("sell_price", sellPrice)
            put("stock", stock)
            put("unit", unit)
            if (categoryId != null) put("category_id", categoryId) else putNull("category_id")
        }
        return db.update("products", values, "id = ?", arrayOf(id.toString())) > 0
    }

    fun deleteProduct(id: Int): Boolean {
        val db = this.writableDatabase
        return db.delete("products", "id = ?", arrayOf(id.toString())) > 0
    }

    // --- TRANSACTION & CASHIER LAYER ---
    fun getNextInvoiceNumber(): String {
        val sdf = SimpleDateFormat("yyyyMMdd", Locale.getDefault())
        val dateStr = sdf.format(Date())
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT COUNT(*) FROM transactions WHERE invoice_no LIKE ?", arrayOf("INV-$dateStr-%"))
        var count = 0
        if (cursor.moveToFirst()) {
            count = cursor.getInt(0)
        }
        cursor.close()
        val num = (count + 1).toString().padStart(4, '0')
        return "INV-$dateStr-$num"
    }

    fun saveTransaction(
        userId: Int,
        items: List<Pair<Product, Int>>, // Product and quantity
        discount: Double,
        paymentType: String, // 'cash' or 'piutang'
        customerName: String?,
        dueDate: String?,
        payAmount: Double
    ): Pair<Boolean, String> {
        val db = this.writableDatabase
        db.beginTransaction()
        try {
            val invoiceNo = getNextInvoiceNumber()
            var totalAmount = 0.0
            for ((prod, qty) in items) {
                totalAmount += prod.sellPrice * qty
            }
            val grandTotal = totalAmount - discount
            var changeAmount = 0.0
            var status = "paid"
            if (paymentType == "cash") {
                changeAmount = payAmount - grandTotal
            } else {
                status = "unpaid"
            }

            // Insert Transaction
            val txVal = ContentValues().apply {
                put("invoice_no", invoiceNo)
                put("user_id", userId)
                put("total_amount", totalAmount)
                put("discount", discount)
                put("grand_total", grandTotal)
                put("pay", payAmount)
                put("change_amount", changeAmount)
                put("payment_type", paymentType)
                put("customer_name", customerName)
                put("due_date", dueDate)
                put("status", status)
                put("created_at", SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault()).format(Date()))
            }
            val txId = db.insert("transactions", null, txVal)
            if (txId == -1L) throw Exception("Failed to insert transaction")

            // Insert details and decrease stock
            for ((prod, qty) in items) {
                val detailVal = ContentValues().apply {
                    put("transaction_id", txId)
                    put("product_id", prod.id)
                    put("product_name", prod.name)
                    put("price", prod.sellPrice)
                    put("qty", qty)
                    put("subtotal", prod.sellPrice * qty)
                }
                db.insert("transaction_details", null, detailVal)

                // Decrease stock
                val newStock = (prod.stock - qty).coerceAtLeast(0)
                db.execSQL("UPDATE products SET stock = ? WHERE id = ?", arrayOf(newStock, prod.id))
            }

            // If Piutang, insert receivable
            if (paymentType == "piutang") {
                val recVal = ContentValues().apply {
                    put("transaction_id", txId)
                    put("customer_name", customerName ?: "Pelanggan Umum")
                    put("total_amount", grandTotal)
                    put("paid_amount", payAmount)
                    put("due_date", dueDate ?: "")
                    put("status", if (payAmount > 0) "partial" else "unpaid")
                    put("created_at", SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault()).format(Date()))
                }
                db.insert("receivables", null, recVal)

                // Insert initial receivable payment if payAmount > 0
                if (payAmount > 0) {
                    val recIdCursor = db.rawQuery("SELECT last_insert_rowid()", null)
                    if (recIdCursor.moveToFirst()) {
                        val recId = recIdCursor.getInt(0)
                        val payVal = ContentValues().apply {
                            put("receivable_id", recId)
                            put("pay_amount", payAmount)
                            put("note", "Pembayaran awal saat transaksi")
                            put("payment_date", SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault()).format(Date()))
                        }
                        db.insert("receivable_payments", null, payVal)
                    }
                    recIdCursor.close()
                }
            }

            db.setTransactionSuccessful()
            return Pair(true, invoiceNo)
        } catch (e: Exception) {
            e.printStackTrace()
            return Pair(false, "")
        } finally {
            db.endTransaction()
        }
    }

    // --- RECEIVABLES LAYER ---
    fun getAllReceivables(statusFilter: String? = null): List<Receivable> {
        val list = mutableListOf<Receivable>()
        val db = this.readableDatabase
        var query = """
            SELECT r.id, r.transaction_id, COALESCE(t.invoice_no, 'MANUAL') as invoice_no, r.customer_name, r.total_amount, r.paid_amount, r.due_date, r.notes, r.status, r.paid_by, r.created_at
            FROM receivables r
            LEFT JOIN transactions t ON r.transaction_id = t.id
        """
        val args = mutableListOf<String>()
        if (statusFilter != null && statusFilter != "all") {
            query += " WHERE r.status = ?"
            args.add(statusFilter)
        }
        query += " ORDER BY r.due_date ASC"

        val cursor = db.rawQuery(query, if (args.isEmpty()) null else args.toTypedArray())
        while (cursor.moveToNext()) {
            list.add(
                Receivable(
                    cursor.getInt(0),
                    if (cursor.isNull(1)) null else cursor.getInt(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    cursor.getDouble(4),
                    cursor.getDouble(5),
                    if (cursor.isNull(6)) null else cursor.getString(6),
                    if (cursor.isNull(7)) null else cursor.getString(7),
                    cursor.getString(8),
                    if (cursor.isNull(9)) null else cursor.getString(9),
                    cursor.getString(10)
                )
            )
        }
        cursor.close()
        return list
    }

    fun getReceivablePayments(receivableId: Int): List<ReceivablePayment> {
        val list = mutableListOf<ReceivablePayment>()
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT id, receivable_id, pay_amount, payment_date, note FROM receivable_payments WHERE receivable_id = ? ORDER BY payment_date DESC", arrayOf(receivableId.toString()))
        while (cursor.moveToNext()) {
            list.add(
                ReceivablePayment(
                    cursor.getInt(0),
                    cursor.getInt(1),
                    cursor.getDouble(2),
                    cursor.getString(3),
                    cursor.getString(4)
                )
            )
        }
        cursor.close()
        return list
    }

    fun payReceivableInstallment(receivableId: Int, amount: Double, note: String?, paidBy: String?): Boolean {
        val db = this.writableDatabase
        db.beginTransaction()
        try {
            // Get current receivable
            val cursor = db.rawQuery("SELECT total_amount, paid_amount, transaction_id FROM receivables WHERE id = ?", arrayOf(receivableId.toString()))
            if (!cursor.moveToFirst()) throw Exception("Receivable not found")
            val total = cursor.getDouble(0)
            val currentPaid = cursor.getDouble(1)
            val txId = if (cursor.isNull(2)) null else cursor.getInt(2)
            cursor.close()

            val newPaid = currentPaid + amount
            val status = when {
                newPaid >= total -> "paid"
                newPaid > 0 -> "partial"
                else -> "unpaid"
            }

            // Update receivables
            val recVal = ContentValues().apply {
                put("paid_amount", newPaid)
                put("status", status)
                if (paidBy != null) put("paid_by", paidBy)
            }
            db.update("receivables", recVal, "id = ?", arrayOf(receivableId.toString()))

            // Insert payment log
            val payVal = ContentValues().apply {
                put("receivable_id", receivableId)
                put("pay_amount", amount)
                put("note", note)
                put("payment_date", SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault()).format(Date()))
            }
            db.insert("receivable_payments", null, payVal)

            // If fully paid, update transaction status too
            if (status == "paid" && txId != null) {
                val txVal = ContentValues().apply {
                    put("status", "paid")
                    put("pay", total) // Update pay to match grand_total
                    put("change_amount", 0.0)
                }
                db.update("transactions", txVal, "id = ?", arrayOf(txId.toString()))
            }

            db.setTransactionSuccessful()
            return true
        } catch (e: Exception) {
            e.printStackTrace()
            return false
        } finally {
            db.endTransaction()
        }
    }

    fun insertManualReceivable(customerName: String, totalAmount: Double, notes: String): Boolean {
        val db = this.writableDatabase
        db.beginTransaction()
        try {
            val recVal = ContentValues().apply {
                putNull("transaction_id")
                put("customer_name", customerName)
                put("total_amount", totalAmount)
                put("paid_amount", 0.0)
                putNull("due_date")
                put("notes", notes)
                put("status", "unpaid")
                put("created_at", SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault()).format(Date()))
            }
            val recId = db.insert("receivables", null, recVal)
            if (recId == -1L) throw Exception("Failed to insert manual receivable")
            db.setTransactionSuccessful()
            return true
        } catch (e: Exception) {
            e.printStackTrace()
            return false
        } finally {
            db.endTransaction()
        }
    }

    fun markReceivableAsPaid(receivableId: Int, officerName: String): Boolean {
        val db = this.writableDatabase
        db.beginTransaction()
        try {
            val cursor = db.rawQuery("SELECT total_amount, paid_amount, transaction_id FROM receivables WHERE id = ?", arrayOf(receivableId.toString()))
            if (!cursor.moveToFirst()) throw Exception("Receivable not found")
            val total = cursor.getDouble(0)
            val currentPaid = cursor.getDouble(1)
            val txId = if (cursor.isNull(2)) null else cursor.getInt(2)
            cursor.close()

            val amountToPay = total - currentPaid
            if (amountToPay <= 0.0) {
                db.setTransactionSuccessful()
                return true
            }

            val timeStr = SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault()).format(Date())
            val paidByInfo = "$officerName pada $timeStr"

            val recVal = ContentValues().apply {
                put("paid_amount", total)
                put("status", "paid")
                put("paid_by", paidByInfo)
            }
            db.update("receivables", recVal, "id = ?", arrayOf(receivableId.toString()))

            val payVal = ContentValues().apply {
                put("receivable_id", receivableId)
                put("pay_amount", amountToPay)
                put("note", "Pelunasan oleh $officerName")
                put("payment_date", timeStr)
            }
            db.insert("receivable_payments", null, payVal)

            if (txId != null) {
                val txVal = ContentValues().apply {
                    put("status", "paid")
                    put("pay", total)
                    put("change_amount", 0.0)
                }
                db.update("transactions", txVal, "id = ?", arrayOf(txId.toString()))
            }

            db.setTransactionSuccessful()
            return true
        } catch (e: Exception) {
            e.printStackTrace()
            return false
        } finally {
            db.endTransaction()
        }
    }

    // --- SALES HISTORY LAYER ---
    fun getAllTransactions(invoiceQuery: String? = null, dateQuery: String? = null): List<Transaction> {
        val list = mutableListOf<Transaction>()
        val db = this.readableDatabase
        var query = """
            SELECT t.id, t.invoice_no, t.user_id, u.name as cashier_name, t.total_amount, t.discount, t.grand_total, t.pay, t.change_amount, t.payment_type, t.customer_name, t.due_date, t.status, t.created_at
            FROM transactions t
            JOIN users u ON t.user_id = u.id
        """
        val conditions = mutableListOf<String>()
        val args = mutableListOf<String>()

        if (!invoiceQuery.isNullOrEmpty()) {
            conditions.add("t.invoice_no LIKE ?")
            args.add("%$invoiceQuery%")
        }
        if (!dateQuery.isNullOrEmpty()) {
            conditions.add("DATE(t.created_at) = ?")
            args.add(dateQuery)
        }

        if (conditions.isNotEmpty()) {
            query += " WHERE " + conditions.joinToString(" AND ")
        }
        query += " ORDER BY t.created_at DESC"

        val cursor = db.rawQuery(query, if (args.isEmpty()) null else args.toTypedArray())
        while (cursor.moveToNext()) {
            list.add(
                Transaction(
                    cursor.getInt(0),
                    cursor.getString(1),
                    cursor.getInt(2),
                    cursor.getString(3),
                    cursor.getDouble(4),
                    cursor.getDouble(5),
                    cursor.getDouble(6),
                    cursor.getDouble(7),
                    cursor.getDouble(8),
                    cursor.getString(9),
                    cursor.getString(10),
                    cursor.getString(11),
                    cursor.getString(12),
                    cursor.getString(13)
                )
            )
        }
        cursor.close()
        return list
    }

    fun getTransactionById(txId: Int): Transaction? {
        val db = this.readableDatabase
        val cursor = db.rawQuery("""
            SELECT t.id, t.invoice_no, t.user_id, u.name as cashier_name, t.total_amount, t.discount, t.grand_total, t.pay, t.change_amount, t.payment_type, t.customer_name, t.due_date, t.status, t.created_at
            FROM transactions t
            JOIN users u ON t.user_id = u.id
            WHERE t.id = ?
        """, arrayOf(txId.toString()))
        var transaction: Transaction? = null
        if (cursor.moveToFirst()) {
            transaction = Transaction(
                cursor.getInt(0),
                cursor.getString(1),
                cursor.getInt(2),
                cursor.getString(3),
                cursor.getDouble(4),
                cursor.getDouble(5),
                cursor.getDouble(6),
                cursor.getDouble(7),
                cursor.getDouble(8),
                cursor.getString(9),
                cursor.getString(10),
                cursor.getString(11),
                cursor.getString(12),
                cursor.getString(13)
            )
        }
        cursor.close()
        return transaction
    }

    fun getTransactionDetails(transactionId: Int): List<TransactionDetail> {
        val list = mutableListOf<TransactionDetail>()
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT id, transaction_id, product_id, product_name, price, qty, subtotal FROM transaction_details WHERE transaction_id = ?", arrayOf(transactionId.toString()))
        while (cursor.moveToNext()) {
            list.add(
                TransactionDetail(
                    cursor.getInt(0),
                    cursor.getInt(1),
                    if (cursor.isNull(2)) null else cursor.getInt(2),
                    cursor.getString(3),
                    cursor.getDouble(4),
                    cursor.getInt(5),
                    cursor.getDouble(6)
                )
            )
        }
        cursor.close()
        return list
    }

    fun deleteTransaction(txId: Int): Boolean {
        val db = this.writableDatabase
        db.beginTransaction()
        try {
            // Restore product stocks
            val details = getTransactionDetails(txId)
            for (item in details) {
                if (item.productId != null) {
                    db.execSQL("UPDATE products SET stock = stock + ? WHERE id = ?", arrayOf(item.qty, item.productId))
                }
            }
            // Cascaded delete due to foreign keys if setup, or manual
            db.delete("transactions", "id = ?", arrayOf(txId.toString()))
            db.setTransactionSuccessful()
            return true
        } catch (e: Exception) {
            e.printStackTrace()
            return false
        } finally {
            db.endTransaction()
        }
    }

    // --- ATTENDANCE LAYER ---
    fun getAttendanceForUserToday(userId: Int): Attendance? {
        val todayStr = SimpleDateFormat("yyyy-MM-dd", Locale.getDefault()).format(Date())
        val db = this.readableDatabase
        val cursor = db.rawQuery("""
            SELECT a.id, a.user_id, u.name, a.check_in, a.check_out, a.date, a.status, a.notes
            FROM attendance a
            JOIN users u ON a.user_id = u.id
            WHERE a.user_id = ? AND a.date = ?
            ORDER BY a.check_in DESC LIMIT 1
        """, arrayOf(userId.toString(), todayStr))
        var attendance: Attendance? = null
        if (cursor.moveToFirst()) {
            attendance = Attendance(
                cursor.getInt(0),
                cursor.getInt(1),
                cursor.getString(2),
                cursor.getString(3),
                if (cursor.isNull(4)) null else cursor.getString(4),
                cursor.getString(5),
                cursor.getString(6),
                cursor.getString(7)
            )
        }
        cursor.close()
        return attendance
    }

    fun checkInUser(userId: Int, notes: String?): Boolean {
        val db = this.writableDatabase
        val todayStr = SimpleDateFormat("yyyy-MM-dd", Locale.getDefault()).format(Date())
        val timeStr = SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault()).format(Date())

        // Check if already checked in
        val current = getAttendanceForUserToday(userId)
        if (current != null) return false

        // Determine status (e.g. check-in after 08:00 is late)
        val calendar = Calendar.getInstance()
        val hour = calendar.get(Calendar.HOUR_OF_DAY)
        val status = if (hour >= 8) "late" else "present"

        val values = ContentValues().apply {
            put("user_id", userId)
            put("check_in", timeStr)
            put("date", todayStr)
            put("status", status)
            put("notes", notes)
        }
        return db.insert("attendance", null, values) != -1L
    }

    fun checkOutUser(userId: Int): Boolean {
        val db = this.writableDatabase
        val timeStr = SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault()).format(Date())
        val current = getAttendanceForUserToday(userId) ?: return false
        if (current.checkOut != null) return false

        val values = ContentValues().apply { put("check_out", timeStr) }
        return db.update("attendance", values, "id = ?", arrayOf(current.id.toString())) > 0
    }

    fun getAllAttendanceRecords(): List<Attendance> {
        val list = mutableListOf<Attendance>()
        val db = this.readableDatabase
        val cursor = db.rawQuery("""
            SELECT a.id, a.user_id, u.name, a.check_in, a.check_out, a.date, a.status, a.notes
            FROM attendance a
            JOIN users u ON a.user_id = u.id
            ORDER BY a.check_in DESC
        """, null)
        while (cursor.moveToNext()) {
            list.add(
                Attendance(
                    cursor.getInt(0),
                    cursor.getInt(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    if (cursor.isNull(4)) null else cursor.getString(4),
                    cursor.getString(5),
                    cursor.getString(6),
                    cursor.getString(7)
                )
            )
        }
        cursor.close()
        return list
    }

    fun getActiveStaffCountToday(): Int {
        val todayStr = SimpleDateFormat("yyyy-MM-dd", Locale.getDefault()).format(Date())
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT COUNT(DISTINCT user_id) FROM attendance WHERE date = ? AND check_out IS NULL", arrayOf(todayStr))
        var count = 0
        if (cursor.moveToFirst()) {
            count = cursor.getInt(0)
        }
        cursor.close()
        return count
    }

    // --- SCHEDULES LAYER REMOVED ---

    // --- SETTINGS LAYER ---
    fun getSettings(): Settings {
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT id, store_name, store_address, store_phone, receipt_header, receipt_footer, low_stock_threshold FROM settings WHERE id = 1", null)
        var settings = Settings(
            1, "Toko Barokah", "Jl. Barokah Raya No. 45, Jakarta", "081234567890",
            "Terima Kasih Telah Berbelanja", "Barang yang sudah dibeli tidak dapat ditukar/dikembalikan.", 10
        )
        if (cursor.moveToFirst()) {
            settings = Settings(
                cursor.getInt(0),
                cursor.getString(1),
                cursor.getString(2),
                cursor.getString(3),
                cursor.getString(4),
                cursor.getString(5),
                cursor.getInt(6)
            )
        }
        cursor.close()
        return settings
    }

    fun updateSettings(storeName: String, storeAddress: String, storePhone: String, receiptHeader: String, receiptFooter: String, threshold: Int): Boolean {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put("store_name", storeName)
            put("store_address", storeAddress)
            put("store_phone", storePhone)
            put("receipt_header", receiptHeader)
            put("receipt_footer", receiptFooter)
            put("low_stock_threshold", threshold)
        }
        return db.update("settings", values, "id = 1", null) > 0
    }

    // --- REPORTS & DASHBOARD METRICS ---
    fun getTodaySales(isAdmin: Boolean, userId: Int): Double {
        val todayStr = SimpleDateFormat("yyyy-MM-dd", Locale.getDefault()).format(Date())
        val db = this.readableDatabase
        val query = if (isAdmin) {
            "SELECT SUM(grand_total) FROM transactions WHERE DATE(created_at) = ?"
        } else {
            "SELECT SUM(grand_total) FROM transactions WHERE DATE(created_at) = ? AND user_id = ?"
        }
        val args = if (isAdmin) arrayOf(todayStr) else arrayOf(todayStr, userId.toString())
        val cursor = db.rawQuery(query, args)
        var total = 0.0
        if (cursor.moveToFirst()) {
            total = cursor.getDouble(0)
        }
        cursor.close()
        return total
    }

    fun getActivePiutangAmount(isAdmin: Boolean, userId: Int): Double {
        val db = this.readableDatabase
        val query = if (isAdmin) {
            "SELECT SUM(total_amount - paid_amount) FROM receivables WHERE status != 'paid'"
        } else {
            "SELECT SUM(r.total_amount - r.paid_amount) FROM receivables r JOIN transactions t ON r.transaction_id = t.id WHERE r.status != 'paid' AND t.user_id = ?"
        }
        val args = if (isAdmin) null else arrayOf(userId.toString())
        val cursor = db.rawQuery(query, args)
        var total = 0.0
        if (cursor.moveToFirst()) {
            total = cursor.getDouble(0)
        }
        cursor.close()
        return total
    }

    fun getLowStockCount(threshold: Int): Int {
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT COUNT(*) FROM products WHERE stock <= ?", arrayOf(threshold.toString()))
        var count = 0
        if (cursor.moveToFirst()) {
            count = cursor.getInt(0)
        }
        cursor.close()
        return count
    }

    fun getLowStockProducts(threshold: Int): List<Product> {
        val list = mutableListOf<Product>()
        val db = this.readableDatabase
        val cursor = db.rawQuery("""
            SELECT id, barcode, name, buy_price, sell_price, stock, unit, category_id
            FROM products WHERE stock <= ? ORDER BY stock ASC LIMIT 5
        """, arrayOf(threshold.toString()))
        while (cursor.moveToNext()) {
            list.add(Product(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getDouble(3), cursor.getDouble(4), cursor.getInt(5), cursor.getString(6), if (cursor.isNull(7)) null else cursor.getInt(7)))
        }
        cursor.close()
        return list
    }

    fun getSalesChartData(isAdmin: Boolean, userId: Int): List<Pair<String, Double>> {
        val list = mutableListOf<Pair<String, Double>>()
        val db = this.readableDatabase
        val sdfDb = SimpleDateFormat("yyyy-MM-dd", Locale.getDefault())
        val sdfLabel = SimpleDateFormat("dd MMM", Locale.getDefault())

        for (i in 6 downTo 0) {
            val calendar = Calendar.getInstance()
            calendar.add(Calendar.DAY_OF_YEAR, -i)
            val dateDbStr = sdfDb.format(calendar.time)
            val labelStr = sdfLabel.format(calendar.time)

            val query = if (isAdmin) {
                "SELECT SUM(grand_total) FROM transactions WHERE DATE(created_at) = ?"
            } else {
                "SELECT SUM(grand_total) FROM transactions WHERE DATE(created_at) = ? AND user_id = ?"
            }
            val args = if (isAdmin) arrayOf(dateDbStr) else arrayOf(dateDbStr, userId.toString())
            val cursor = db.rawQuery(query, args)
            var sum = 0.0
            if (cursor.moveToFirst() && !cursor.isNull(0)) {
                sum = cursor.getDouble(0)
            }
            cursor.close()
            list.add(Pair(labelStr, sum))
        }
        return list
    }

    fun getSalesReport(startDate: String, endDate: String): List<SalesReport> {
        val list = mutableListOf<SalesReport>()
        val db = this.readableDatabase
        val cursor = db.rawQuery("""
            SELECT DATE(t.created_at) as sales_date,
                   SUM(t.grand_total) as revenue,
                   SUM(
                     (SELECT SUM(d.qty * p.buy_price) 
                      FROM transaction_details d 
                      LEFT JOIN products p ON d.product_id = p.id 
                      WHERE d.transaction_id = t.id)
                   ) as cost
            FROM transactions t
            WHERE DATE(t.created_at) BETWEEN ? AND ?
            GROUP BY DATE(t.created_at)
            ORDER BY sales_date ASC
        """, arrayOf(startDate, endDate))

        while (cursor.moveToNext()) {
            val date = cursor.getString(0)
            val revenue = cursor.getDouble(1)
            val cost = cursor.getDouble(2)
            val profit = revenue - cost
            list.add(SalesReport(date, revenue, cost, profit))
        }
        cursor.close()
        return list
    }

    fun getBestSellers(startDate: String, endDate: String): List<BestSellerProduct> {
        val list = mutableListOf<BestSellerProduct>()
        val db = this.readableDatabase
        val cursor = db.rawQuery("""
            SELECT d.product_name, SUM(d.qty) as total_qty, SUM(d.subtotal) as total_subtotal
            FROM transaction_details d
            JOIN transactions t ON d.transaction_id = t.id
            WHERE DATE(t.created_at) BETWEEN ? AND ?
            GROUP BY d.product_name
            ORDER BY total_qty DESC LIMIT 5
        """, arrayOf(startDate, endDate))

        while (cursor.moveToNext()) {
            list.add(BestSellerProduct(cursor.getString(0), cursor.getInt(1), cursor.getDouble(2)))
        }
        cursor.close()
        return list
    }
}