import json
import os
import sqlite3
from datetime import datetime

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, "birrah_pos.db")
SCHEMA_PATH = os.path.join(BASE_DIR, "schema.sql")


def get_conn(db_path=None):
    conn = sqlite3.connect(db_path or DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON;")
    return conn


def init_db(db_path=None):
    conn = get_conn(db_path)
    with open(SCHEMA_PATH, "r", encoding="utf-8") as f:
        conn.executescript(f.read())
    # Seed default data only if users table is empty
    cur = conn.execute("SELECT COUNT(*) AS cnt FROM users")
    if cur.fetchone()["cnt"] == 0:
        seed_defaults(conn)
    conn.commit()
    conn.close()


def seed_defaults(conn):
    users = [
        ("st0", "System Admin", "admin", "admin123", "Admin", "All Locations", 120000, 0, 1, "2026-11-20 03:40",
         json.dumps(["pos", "purchases", "templates", "customers", "inventory", "staff", "reports", "ai", "fbr"])),
        ("st3", "Usman Malik", "manager", "manager123", "Manager", "All Locations", 95000, 1.0, 1, "2026-11-20 01:10",
         json.dumps(["pos", "purchases", "templates", "customers", "inventory", "reports", "ai"])),
        ("st1", "Saad Ahmed", "cashier", "cashier123", "Cashier", "Main Store — Branch 1", 55000, 1.5, 1, "2026-11-20 02:05",
         json.dumps(["pos", "templates", "customers"])),
        ("st2", "Areeba Khan", "areeba", "areeba123", "Salesperson", "City Outlet — Branch 2", 48000, 3.0, 1, "Never",
         json.dumps(["pos", "templates", "customers", "reports"]))
    ]
    conn.executemany(
        "INSERT OR IGNORE INTO users VALUES (?,?,?,?,?,?,?,?,?,?,?)", users
    )

    for cat in ["General Retail", "FMCG & Daily", "Wholesale Pack", "Services"]:
        conn.execute("INSERT OR IGNORE INTO categories (name) VALUES (?)", (cat,))

    products = [
        ("p1", "ITEM-101", "Standard", "Single", "Pcs", "Premium Retail Product A", "General Retail", 2500, 1700, 24, 6, 1, "99990000", "LOT-101A", "", 18, "p2"),
        ("p2", "ITEM-102", "Standard", "Variable", "Pcs", "Multi-Size / Variant Item B", "General Retail", 1800, 1150, 5, 8, 1, "99990001", "LOT-102B", "", 18, "p1"),
        ("p3", "FMCG-201", "Prime", "Single", "Pack", "Daily Consumer Pack (500g)", "FMCG & Daily", 950, 680, 40, 10, 1, "21069090", "BCH-26A", "2026-12-05", 18, "p4"),
        ("p4", "FMCG-202", "Prime", "Single", "Box", "Essential Pantry Box (12 Units)", "FMCG & Daily", 3200, 2400, 18, 6, 1, "19059000", "BCH-26B", "2026-11-28", 0, "p3"),
        ("p5", "WHL-301", "BulkCo", "Single", "Box", "Wholesale Master Carton (24 Pcs)", "Wholesale Pack", 14500, 11200, 12, 4, 1, "39269099", "LOT-W301", "", 18, "p6"),
        ("p6", "WHL-302", "BulkCo", "Single", "Kg", "Bulk Supply Material (10 Kg)", "Wholesale Pack", 6400, 4900, 15, 5, 1, "39232900", "LOT-W302", "", 18, "p5"),
        ("p7", "SRV-401", "In-House", "Single", "Pcs", "Standard Service / Consultation Fee", "Services", 3500, 500, 99, 0, 0, "98000000", "SRV-STD", "", 15, "p8"),
        ("p8", "SRV-402", "In-House", "Single", "Pcs", "Express Installation / Delivery Service", "Services", 1500, 300, 99, 0, 0, "98000001", "SRV-EXP", "", 15, "p7"),
    ]
    conn.executemany(
        "INSERT OR IGNORE INTO products VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", products
    )

    contacts = [
        ("walkin", "customer", "Walk-In Customer", "", "", "", 0, 0, 0, 1),
        ("c1", "customer", "Bilal Enterprises", "0333-2104589", "", "7192045-1", 18500, 150000, 15, 0),
        ("c2", "both", "Hamza Siddiqui", "0300-8219941", "42301-8841209-3", "", 4500, 50000, 30, 0),
        ("c3", "customer", "Al-Falah Trading Co.", "0321-9088712", "", "5541902-8", 32000, 100000, 7, 0),
        ("s1", "supplier", "National Wholesale Suppliers", "0345-8821009", "", "9021455-2", 24000, 300000, 30, 0),
    ]
    conn.executemany(
        "INSERT OR IGNORE INTO contacts VALUES (?,?,?,?,?,?,?,?,?,?)", contacts
    )

    ledgers = [
        ("c1", "2026-11-12 14:20", "Opening Balance", "OPB-01", 12009, 0, 12009),
        ("c1", "2026-11-20 02:35", "Credit Sale (INV-2026-1003)", "INV-2026-1003", 6491, 0, 18500),
        ("c2", "2026-11-15 18:10", "Opening Balance", "OPB-02", 4500, 0, 4500),
        ("c2", "2026-11-20 01:15", "Sale Paid (INV-2026-1001)", "INV-2026-1001", 17111, 17111, 4500),
        ("c3", "2026-11-10 11:00", "Opening Balance", "OPB-03", 42000, 0, 42000),
        ("c3", "2026-11-18 16:40", "Payment Received (RAAST)", "PAY-104", 0, 10000, 32000),
        ("s1", "2026-11-14 10:00", "Purchase PO-501 (Partial)", "PO-501", 40620, 16620, 24000),
    ]
    conn.executemany(
        "INSERT INTO contact_ledger (contact_id, entry_date, entry_type, ref_no, debit, credit, balance) VALUES (?,?,?,?,?,?,?)",
        ledgers
    )

    invoices = [
        ("INV-2026-1003", "PENDING-OFFLINE-SYNC", "2026-11-20 02:35:11", "c1", "Bilal Enterprises", "0333-2104589", "", "7192045-1", "st2", 1, 1, "", "queued", 1, 0, 5500, 0, 5500, 990, 6491, 12009, 0, 0, 18500, 4),
        ("INV-2026-1002", "814920-201126020845-1002", "2026-11-20 02:08:45", "walkin", "Walk-In Customer", "0321-2940118", "", "", "st1", 5, 1, "", "valid", 1, 0, 6700, 0, 6700, 525, 7226, 0, 7226, 0, 0, 2),
        ("INV-2026-1001", "814920-201126011520-1001", "2026-11-20 01:15:20", "c2", "Hamza Siddiqui", "0300-8219941", "42301-8841209-3", "", "st1", 2, 1, "", "valid", 1, 0, 14500, 0, 14500, 2610, 17111, 4500, 17111, 0, 4500, 1),
    ]
    conn.executemany(
        "INSERT OR IGNORE INTO invoices VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", invoices
    )

    inv_items = [
        ("INV-2026-1003", "p3", "FMCG-201", "Daily Consumer Pack (500g)", "FMCG & Daily", "21069090", 2, 950, 680, 18, 0, 1900, 342, 2242),
        ("INV-2026-1003", "p2", "ITEM-102", "Multi-Size / Variant Item B", "General Retail", "99990001", 2, 1800, 1150, 18, 0, 3600, 648, 4248),
        ("INV-2026-1002", "p4", "FMCG-202", "Essential Pantry Box (12 Units)", "FMCG & Daily", "19059000", 1, 3200, 2400, 0, 0, 3200, 0, 3200),
        ("INV-2026-1002", "p7", "SRV-401", "Standard Service / Consultation Fee", "Services", "98000000", 1, 3500, 500, 15, 0, 3500, 525, 4025),
        ("INV-2026-1001", "p5", "WHL-301", "Wholesale Master Carton (24 Pcs)", "Wholesale Pack", "39269099", 1, 14500, 11200, 18, 0, 14500, 2610, 17110),
    ]
    conn.executemany(
        "INSERT INTO invoice_items (invoice_usin, product_id, sku, name, category, pct_code, qty, price, cost, tax_rate, line_discount, sale_value, tax_amount, line_total) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
        inv_items
    )

    purchases = [
        ("PO-501", "2026-11-14", "s1", "National Wholesale Suppliers", "p1", "Premium Retail Product A", 20, 1700, 500, 18, 6120, 40620, 16620, 24000, "LOT-101A", "", "supplier_po501.pdf", "partial"),
        ("PO-502", "2026-11-18", "c2", "Hamza Siddiqui", "p3", "Daily Consumer Pack (500g)", 30, 680, 300, 18, 3672, 24372, 24372, 0, "BCH-26A", "2026-12-05", "batch_po502.pdf", "paid"),
    ]
    conn.executemany(
        "INSERT OR IGNORE INTO purchases VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", purchases
    )

    bills = [
        ("BILL-2026-101", "2026-11-16", "National Wholesale Suppliers", "Bulk Carton Stock & Freight Charges", 22000, 18, 3960, 25960, "2026-11-30", "Unpaid"),
        ("BILL-2026-102", "2026-11-19", "City Logistics & Courier", "Monthly Express Delivery Settlement", 8500, 15, 1275, 9775, "2026-11-25", "Paid"),
    ]
    conn.executemany("INSERT OR IGNORE INTO bills VALUES (?,?,?,?,?,?,?,?,?,?)", bills)

    quotations = [
        ("QT-2026-201", "2026-11-19 16:30:00", "c1", "Bilal Enterprises", "2026-12-04", "Bulk order delivery within 48 hours", json.dumps([{"id": "p5", "qty": 2}]), 0, 34220),
    ]
    conn.executemany("INSERT OR IGNORE INTO quotations VALUES (?,?,?,?,?,?,?,?,?)", quotations)

    expenses = [
        ("ex1", "2026-11-05", "Shop Rent", 45000, "Main Branch Monthly Rent"),
        ("ex2", "2026-11-12", "Electricity & Utilities", 14500, "Commercial Utility Bill"),
    ]
    conn.executemany("INSERT OR IGNORE INTO expenses VALUES (?,?,?,?,?)", expenses)

    conn.execute(
        "INSERT INTO stock_adjustments (adj_date, sku, product, delta, reason) VALUES (?,?,?,?,?)",
        ("2026-11-17 15:20", "FMCG-201", "Daily Consumer Pack (500g)", -1, "Damaged packaging during audit")
    )

    std_tpl = {
        "logoDataUrl": "",
        "logoSize": 60,
        "companyName": "Birrah Universal Business",
        "tagline": "Quality Products & Professional Services",
        "address": "Main Branch, Commercial Area, Karachi, Pakistan",
        "phone": "+92 300 0000000",
        "email": "info@birrahbusiness.com",
        "layout": "a4_modern",
        "accent": "#047857",
        "invTitle": "SALES TAX INVOICE",
        "billTitle": "BILL / PAYMENT VOUCHER",
        "quoteTitle": "OFFICIAL QUOTATION",
        "invPrefix": "INV-2026-",
        "billPrefix": "BILL-2026-",
        "quotePrefix": "QT-2026-",
        "showLogo": True,
        "showTaxId": True,
        "showPrevBal": True,
        "showQr": True,
        "showTaxCols": True,
        "showSig": True,
        "bankInfo": "Bank: Meezan Bank | Title: Birrah Business | RAAST: 0300-0000000",
        "terms": "Thank you for your business! Goods exchanged within 7 days with original invoice."
    }
    conn.execute(
        "INSERT OR REPLACE INTO standard_template (id, template_json) VALUES (1, ?)",
        (json.dumps(std_tpl),)
    )


# ==================== RELATIONAL CRUD API FUNCTIONS ====================

def export_full_state(db_path=None):
    conn = get_conn(db_path)
    users = []
    for r in conn.execute("SELECT * FROM users"):
        users.append({
            "id": r["id"], "name": r["name"], "username": r["username"], "password": r["password"],
            "role": r["role"], "location": r["location"], "salary": r["salary"], "commPct": r["comm_pct"],
            "active": bool(r["active"]), "lastLogin": r["last_login"], "perms": json.loads(r["perms_json"])
        })

    categories = [r["name"] for r in conn.execute("SELECT name FROM categories ORDER BY id")]

    products = []
    for r in conn.execute("SELECT * FROM products"):
        products.append({
            "id": r["id"], "sku": r["sku"], "brand": r["brand"], "prodType": r["prod_type"],
            "unit": r["unit"], "name": r["name"], "category": r["category"], "price": r["price"],
            "cost": r["cost"], "stock": r["stock"], "reorder": r["reorder"],
            "manageStock": bool(r["manage_stock"]), "pctCode": r["pct_code"], "imeiLot": r["imei_lot"],
            "expiry": r["expiry"], "taxRate": r["tax_rate"], "pairWith": r["pair_with"]
        })

    customers = []
    for r in conn.execute("SELECT * FROM contacts"):
        led = [
            {"date": l["entry_date"], "type": l["entry_type"], "ref": l["ref_no"],
             "debit": l["debit"], "credit": l["credit"], "balance": l["balance"]}
            for l in conn.execute("SELECT * FROM contact_ledger WHERE contact_id=? ORDER BY id", (r["id"],))
        ]
        customers.append({
            "id": r["id"], "contactType": r["contact_type"], "name": r["name"], "phone": r["phone"],
            "cnic": r["cnic"], "ntn": r["ntn"], "dueBalance": r["due_balance"],
            "creditLimit": r["credit_limit"], "payTerms": r["pay_terms"],
            "isWalkIn": bool(r["is_walkin"]), "ledger": led
        })

    invoices = []
    for r in conn.execute("SELECT * FROM invoices ORDER BY date_time DESC"):
        items = [
            {
                "id": it["product_id"], "sku": it["sku"], "name": it["name"], "category": it["category"],
                "pctCode": it["pct_code"], "qty": it["qty"], "price": it["price"], "cost": it["cost"],
                "taxRate": it["tax_rate"], "lineDiscount": it["line_discount"], "saleValue": it["sale_value"],
                "taxAmount": it["tax_amount"], "lineTotal": it["line_total"]
            }
            for it in conn.execute("SELECT * FROM invoice_items WHERE invoice_usin=?", (r["usin"],))
        ]
        invoices.append({
            "usin": r["usin"], "fbrInvoiceNumber": r["fbr_invoice_number"], "dateTime": r["date_time"],
            "customerId": r["customer_id"], "buyerName": r["buyer_name"], "buyerPhone": r["buyer_phone"],
            "buyerCnic": r["buyer_cnic"], "buyerNtn": r["buyer_ntn"], "agentId": r["agent_id"],
            "paymentMode": r["payment_mode"], "invoiceType": r["invoice_type"], "refUsin": r["ref_usin"],
            "status": r["status"], "posFee": r["pos_fee"], "shipping": r["shipping"],
            "subtotal": r["subtotal"], "discountAmount": r["discount_amount"],
            "netSaleValue": r["net_sale_value"], "totalTax": r["total_tax"], "grandTotal": r["grand_total"],
            "previousBalance": r["previous_balance"], "paidAmount": r["paid_amount"],
            "changeReturn": r["change_return"], "closingBalance": r["closing_balance"],
            "totalQty": r["total_qty"], "items": items
        })

    purchases = [
        {
            "id": r["id"], "date": r["purchase_date"], "supplierId": r["supplier_id"],
            "supplierName": r["supplier_name"], "productId": r["product_id"], "productName": r["product_name"],
            "qty": r["qty"], "unitCost": r["unit_cost"], "shipping": r["shipping"], "taxRate": r["tax_rate"],
            "taxAmount": r["tax_amount"], "total": r["total"], "paid": r["paid"], "due": r["due"],
            "lot": r["lot"], "expiry": r["expiry"], "docName": r["doc_name"], "status": r["status"]
        }
        for r in conn.execute("SELECT * FROM purchases ORDER BY purchase_date DESC")
    ]

    bills = [
        {
            "id": r["id"], "date": r["bill_date"], "party": r["party"], "desc": r["description"],
            "amount": r["amount"], "taxRate": r["tax_rate"], "taxAmount": r["tax_amount"],
            "total": r["total"], "dueDate": r["due_date"], "status": r["status"]
        }
        for r in conn.execute("SELECT * FROM bills ORDER BY bill_date DESC")
    ]

    quotations = [
        {
            "id": r["id"], "date": r["quote_date"], "customerId": r["customer_id"],
            "customerName": r["customer_name"], "validUntil": r["valid_until"],
            "notes": r["notes"], "cart": json.loads(r["cart_json"]), "discount": r["discount"],
            "total": r["total"]
        }
        for r in conn.execute("SELECT * FROM quotations ORDER BY quote_date DESC")
    ]

    expenses = [
        {"id": r["id"], "date": r["expense_date"], "category": r["category"], "amount": r["amount"], "note": r["note"]}
        for r in conn.execute("SELECT * FROM expenses ORDER BY expense_date DESC")
    ]

    stock_adj = [
        {"date": r["adj_date"], "sku": r["sku"], "product": r["product"], "delta": r["delta"], "reason": r["reason"]}
        for r in conn.execute("SELECT * FROM stock_adjustments ORDER BY id DESC")
    ]

    tpl_row = conn.execute("SELECT template_json FROM standard_template WHERE id=1").fetchone()
    std_tpl = json.loads(tpl_row["template_json"]) if tpl_row else {}

    kv = {r["key"]: json.loads(r["value_json"]) for r in conn.execute("SELECT key, value_json FROM app_state_kv")}
    conn.close()

    res = {
        "staff": users,
        "categories": categories,
        "products": products,
        "customers": customers,
        "invoices": invoices,
        "purchases": purchases,
        "bills": bills,
        "draftQuotes": quotations,
        "expenses": expenses,
        "stockAdjustments": stock_adj,
        "standardTemplate": std_tpl,
    }
    res.update(kv)
    return res


def sync_full_state(payload, db_path=None):
    """Persists full application state atomically into all relational SQLite tables."""
    conn = get_conn(db_path)
    try:
        conn.execute("PRAGMA foreign_keys = OFF;")
        if "staff" in payload:
            conn.execute("DELETE FROM users")
            for u in payload["staff"]:
                conn.execute(
                    "INSERT INTO users VALUES (?,?,?,?,?,?,?,?,?,?,?)",
                    (u["id"], u["name"], u["username"], u["password"], u["role"], u.get("location", ""),
                     u.get("salary", 0), u.get("commPct", 0), 1 if u.get("active", True) else 0,
                     u.get("lastLogin", "Never"), json.dumps(u.get("perms", ["pos"])))
                )
        if "categories" in payload:
            conn.execute("DELETE FROM categories")
            for c in payload["categories"]:
                conn.execute("INSERT OR IGNORE INTO categories (name) VALUES (?)", (c,))
        if "products" in payload:
            conn.execute("DELETE FROM products")
            for p in payload["products"]:
                conn.execute(
                    "INSERT INTO products VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    (p["id"], p["sku"], p.get("brand", "Standard"), p.get("prodType", "Single"),
                     p.get("unit", "Pcs"), p["name"], p.get("category", "General Retail"),
                     p["price"], p["cost"], p.get("stock", 0), p.get("reorder", 5),
                     1 if p.get("manageStock", True) else 0, p.get("pctCode", "99990000"),
                     p.get("imeiLot", ""), p.get("expiry", ""), p.get("taxRate", 18), p.get("pairWith", ""))
                )
        if "customers" in payload:
            conn.execute("DELETE FROM contact_ledger")
            conn.execute("DELETE FROM contacts")
            for c in payload["customers"]:
                conn.execute(
                    "INSERT INTO contacts VALUES (?,?,?,?,?,?,?,?,?,?)",
                    (c["id"], c.get("contactType", "customer"), c["name"], c.get("phone", ""),
                     c.get("cnic", ""), c.get("ntn", ""), c.get("dueBalance", 0),
                     c.get("creditLimit", 0), c.get("payTerms", 0), 1 if c.get("isWalkIn") else 0)
                )
                for l in c.get("ledger", []):
                    conn.execute(
                        "INSERT INTO contact_ledger (contact_id, entry_date, entry_type, ref_no, debit, credit, balance) VALUES (?,?,?,?,?,?,?)",
                        (c["id"], l.get("date", ""), l.get("type", ""), l.get("ref", ""),
                         l.get("debit", 0), l.get("credit", 0), l.get("balance", 0))
                    )
        if "invoices" in payload:
            conn.execute("DELETE FROM invoice_items")
            conn.execute("DELETE FROM invoices")
            for inv in payload["invoices"]:
                conn.execute(
                    "INSERT INTO invoices VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    (inv["usin"], inv.get("fbrInvoiceNumber", ""), inv.get("dateTime", ""),
                     inv.get("customerId"), inv.get("buyerName", "Walk-In Customer"),
                     inv.get("buyerPhone", ""), inv.get("buyerCnic", ""), inv.get("buyerNtn", ""),
                     inv.get("agentId", ""), inv.get("paymentMode", 1), inv.get("invoiceType", 1),
                     inv.get("refUsin", ""), inv.get("status", "valid"), inv.get("posFee", 1),
                     inv.get("shipping", 0), inv.get("subtotal", 0), inv.get("discountAmount", 0),
                     inv.get("netSaleValue", 0), inv.get("totalTax", 0), inv.get("grandTotal", 0),
                     inv.get("previousBalance", 0), inv.get("paidAmount", 0), inv.get("changeReturn", 0),
                     inv.get("closingBalance", 0), inv.get("totalQty", 0))
                )
                for it in inv.get("items", []):
                    conn.execute(
                        "INSERT INTO invoice_items (invoice_usin, product_id, sku, name, category, pct_code, qty, price, cost, tax_rate, line_discount, sale_value, tax_amount, line_total) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                        (inv["usin"], it.get("id"), it.get("sku", ""), it.get("name", ""),
                         it.get("category", "General Retail"), it.get("pctCode", "99990000"),
                         it.get("qty", 1), it.get("price", 0), it.get("cost", 0), it.get("taxRate", 18),
                         it.get("lineDiscount", 0), it.get("saleValue", 0), it.get("taxAmount", 0), it.get("lineTotal", 0))
                    )
        if "purchases" in payload:
            conn.execute("DELETE FROM purchases")
            for po in payload["purchases"]:
                conn.execute(
                    "INSERT INTO purchases VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    (po["id"], po.get("date", ""), po.get("supplierId"), po.get("supplierName", ""),
                     po.get("productId"), po.get("productName", ""), po.get("qty", 1), po.get("unitCost", 0),
                     po.get("shipping", 0), po.get("taxRate", 18), po.get("taxAmount", 0), po.get("total", 0),
                     po.get("paid", 0), po.get("due", 0), po.get("lot", ""), po.get("expiry", ""),
                     po.get("docName", ""), po.get("status", "paid"))
                )
        if "bills" in payload:
            conn.execute("DELETE FROM bills")
            for b in payload["bills"]:
                conn.execute(
                    "INSERT INTO bills VALUES (?,?,?,?,?,?,?,?,?,?)",
                    (b["id"], b.get("date", ""), b.get("party", ""), b.get("desc", ""),
                     b.get("amount", 0), b.get("taxRate", 18), b.get("taxAmount", 0),
                     b.get("total", 0), b.get("dueDate", ""), b.get("status", "Unpaid"))
                )
        if "draftQuotes" in payload:
            conn.execute("DELETE FROM quotations")
            for q in payload["draftQuotes"]:
                conn.execute(
                    "INSERT INTO quotations VALUES (?,?,?,?,?,?,?,?,?)",
                    (q["id"], q.get("date", ""), q.get("customerId"), q.get("customerName", ""),
                     q.get("validUntil", ""), q.get("notes", ""), json.dumps(q.get("cart", [])),
                     q.get("discount", 0), q.get("total", 0))
                )
        if "expenses" in payload:
            conn.execute("DELETE FROM expenses")
            for ex in payload["expenses"]:
                conn.execute(
                    "INSERT INTO expenses VALUES (?,?,?,?,?)",
                    (ex["id"], ex.get("date", ""), ex.get("category", ""), ex.get("amount", 0), ex.get("note", ""))
                )
        if "stockAdjustments" in payload:
            conn.execute("DELETE FROM stock_adjustments")
            for a in payload["stockAdjustments"]:
                conn.execute(
                    "INSERT INTO stock_adjustments (adj_date, sku, product, delta, reason) VALUES (?,?,?,?,?)",
                    (a.get("date", ""), a.get("sku", ""), a.get("product", ""), a.get("delta", 0), a.get("reason", ""))
                )
        if "standardTemplate" in payload:
            conn.execute(
                "INSERT OR REPLACE INTO standard_template (id, template_json, updated_at) VALUES (1, ?, datetime('now'))",
                (json.dumps(payload["standardTemplate"]),)
            )

        for k in ["fbrConfig", "customization", "docTemplates", "workflows", "aiLogs", "businessIndustry", "selectedBusinessLoc", "invoiceSeq", "duePaymentsCollected", "whatsapp", "whatsappLogs", "backupConfig", "backupLogs", "openingCashInDrawer", "actualCashOnHand", "registerLogs"]:
            if k in payload:
                conn.execute(
                    "INSERT OR REPLACE INTO app_state_kv (key, value_json, updated_at) VALUES (?, ?, datetime('now'))",
                    (k, json.dumps(payload[k]))
                )
        conn.execute("PRAGMA foreign_keys = ON;")
        conn.commit()
        return {"ok": True}
    finally:
        conn.close()


# Direct REST CRUD helpers for granular API endpoints & automated SQA tests
def create_product(data, db_path=None):
    conn = get_conn(db_path)
    pid = data.get("id") or f"p_{int(datetime.now().timestamp() * 1000)}"
    conn.execute(
        "INSERT INTO products VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
        (pid, data["sku"], data.get("brand", "Standard"), data.get("prodType", "Single"),
         data.get("unit", "Pcs"), data["name"], data.get("category", "General Retail"),
         float(data["price"]), float(data["cost"]), int(data.get("stock", 0)), int(data.get("reorder", 5)),
         1 if data.get("manageStock", True) else 0, data.get("pctCode", "99990000"),
         data.get("imeiLot", ""), data.get("expiry", ""), float(data.get("taxRate", 18)), data.get("pairWith", ""))
    )
    conn.commit()
    row = dict(conn.execute("SELECT * FROM products WHERE id=?", (pid,)).fetchone())
    conn.close()
    return row


def update_product(pid, data, db_path=None):
    conn = get_conn(db_path)
    cur = conn.execute("SELECT * FROM products WHERE id=?", (pid,)).fetchone()
    if not cur:
        conn.close()
        return None
    merged = dict(cur)
    mapping = {"name": "name", "sku": "sku", "brand": "brand", "category": "category", "price": "price",
               "cost": "cost", "stock": "stock", "reorder": "reorder", "taxRate": "tax_rate", "expiry": "expiry", "pctCode": "pct_code"}
    for k, col in mapping.items():
        if k in data:
            merged[col] = data[k]
    conn.execute(
        "UPDATE products SET name=?, sku=?, brand=?, category=?, price=?, cost=?, stock=?, reorder=?, tax_rate=?, expiry=?, pct_code=? WHERE id=?",
        (merged["name"], merged["sku"], merged["brand"], merged["category"], merged["price"],
         merged["cost"], merged["stock"], merged["reorder"], merged["tax_rate"], merged["expiry"], merged["pct_code"], pid)
    )
    conn.commit()
    out = dict(conn.execute("SELECT * FROM products WHERE id=?", (pid,)).fetchone())
    conn.close()
    return out


def delete_product(pid, db_path=None):
    conn = get_conn(db_path)
    cur = conn.execute("DELETE FROM products WHERE id=?", (pid,))
    conn.commit()
    deleted = cur.rowcount > 0
    conn.close()
    return deleted


def create_contact(data, db_path=None):
    conn = get_conn(db_path)
    cid = data.get("id") or f"c_{int(datetime.now().timestamp() * 1000)}"
    bal = float(data.get("dueBalance", 0))
    conn.execute(
        "INSERT INTO contacts VALUES (?,?,?,?,?,?,?,?,?,?)",
        (cid, data.get("contactType", "customer"), data["name"], data.get("phone", ""),
         data.get("cnic", ""), data.get("ntn", ""), bal, float(data.get("creditLimit", 100000)),
         int(data.get("payTerms", 15)), 0)
    )
    if bal > 0:
        conn.execute(
            "INSERT INTO contact_ledger (contact_id, entry_date, entry_type, ref_no, debit, credit, balance) VALUES (?,?,?,?,?,?,?)",
            (cid, datetime.now().strftime("%Y-%m-%d %H:%M"), "Opening Balance", "OPB", bal, 0, bal)
        )
    conn.commit()
    row = dict(conn.execute("SELECT * FROM contacts WHERE id=?", (cid,)).fetchone())
    conn.close()
    return row


def update_contact(cid, data, db_path=None):
    conn = get_conn(db_path)
    cur = conn.execute("SELECT * FROM contacts WHERE id=?", (cid,)).fetchone()
    if not cur:
        conn.close()
        return None
    merged = dict(cur)
    for k, col in [("name", "name"), ("contactType", "contact_type"), ("phone", "phone"), ("cnic", "cnic"),
                   ("ntn", "ntn"), ("dueBalance", "due_balance"), ("creditLimit", "credit_limit"), ("payTerms", "pay_terms")]:
        if k in data:
            merged[col] = data[k]
    conn.execute(
        "UPDATE contacts SET name=?, contact_type=?, phone=?, cnic=?, ntn=?, due_balance=?, credit_limit=?, pay_terms=? WHERE id=?",
        (merged["name"], merged["contact_type"], merged["phone"], merged["cnic"], merged["ntn"],
         merged["due_balance"], merged["credit_limit"], merged["pay_terms"], cid)
    )
    conn.commit()
    out = dict(conn.execute("SELECT * FROM contacts WHERE id=?", (cid,)).fetchone())
    conn.close()
    return out


def delete_contact(cid, db_path=None):
    conn = get_conn(db_path)
    cur = conn.execute("DELETE FROM contacts WHERE id=? AND is_walkin=0", (cid,))
    conn.commit()
    deleted = cur.rowcount > 0
    conn.close()
    return deleted


def create_user(data, db_path=None):
    conn = get_conn(db_path)
    uid = data.get("id") or f"st_{int(datetime.now().timestamp() * 1000)}"
    conn.execute(
        "INSERT INTO users VALUES (?,?,?,?,?,?,?,?,?,?,?)",
        (uid, data["name"], data["username"].lower(), data["password"], data.get("role", "Cashier"),
         data.get("location", "Main Store — Branch 1"), float(data.get("salary", 0)),
         float(data.get("commPct", 0)), 1 if data.get("active", True) else 0, "Never",
         json.dumps(data.get("perms", ["pos", "templates", "customers"])))
    )
    conn.commit()
    row = dict(conn.execute("SELECT * FROM users WHERE id=?", (uid,)).fetchone())
    conn.close()
    return row


def update_user(uid, data, db_path=None):
    conn = get_conn(db_path)
    cur = conn.execute("SELECT * FROM users WHERE id=?", (uid,)).fetchone()
    if not cur:
        conn.close()
        return None
    merged = dict(cur)
    for k, col in [("name", "name"), ("username", "username"), ("password", "password"), ("role", "role"),
                   ("location", "location"), ("salary", "salary"), ("commPct", "comm_pct")]:
        if k in data:
            merged[col] = data[k]
    if "active" in data:
        merged["active"] = 1 if data["active"] else 0
    if "perms" in data:
        merged["perms_json"] = json.dumps(data["perms"])
    conn.execute(
        "UPDATE users SET name=?, username=?, password=?, role=?, location=?, salary=?, comm_pct=?, active=?, perms_json=? WHERE id=?",
        (merged["name"], merged["username"], merged["password"], merged["role"], merged["location"],
         merged["salary"], merged["comm_pct"], merged["active"], merged["perms_json"], uid)
    )
    conn.commit()
    out = dict(conn.execute("SELECT * FROM users WHERE id=?", (uid,)).fetchone())
    conn.close()
    return out


def delete_user(uid, db_path=None):
    conn = get_conn(db_path)
    cur = conn.execute("DELETE FROM users WHERE id=?", (uid,))
    conn.commit()
    deleted = cur.rowcount > 0
    conn.close()
    return deleted


def create_invoice_tx(data, db_path=None):
    """Atomic checkout transaction: inserts invoice + items, deducts stock, updates customer Khata balance & ledger."""
    conn = get_conn(db_path)
    try:
        usin = data["usin"]
        cid = data.get("customerId", "walkin")
        cust = conn.execute("SELECT * FROM contacts WHERE id=?", (cid,)).fetchone()
        prev_bal = float(cust["due_balance"]) if cust else 0.0
        grand = float(data["grandTotal"])
        paid = float(data.get("paidAmount", grand))
        closing = max(0.0, (prev_bal + grand) - min(paid, prev_bal + grand))

        conn.execute(
            "INSERT INTO invoices VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
            (usin, data.get("fbrInvoiceNumber", f"814920-{usin}"), data.get("dateTime", datetime.now().strftime("%Y-%m-%d %H:%M:%S")),
             cid, data.get("buyerName", cust["name"] if cust else "Walk-In Customer"),
             data.get("buyerPhone", ""), data.get("buyerCnic", ""), data.get("buyerNtn", ""),
             data.get("agentId", ""), int(data.get("paymentMode", 1)), int(data.get("invoiceType", 1)),
             data.get("refUsin", ""), data.get("status", "valid"), float(data.get("posFee", 1)),
             float(data.get("shipping", 0)), float(data.get("subtotal", grand)), float(data.get("discountAmount", 0)),
             float(data.get("netSaleValue", grand)), float(data.get("totalTax", 0)), grand,
             prev_bal, paid, max(0.0, paid - (prev_bal + grand)), closing, int(data.get("totalQty", 1)))
        )
        for it in data.get("items", []):
            conn.execute(
                "INSERT INTO invoice_items (invoice_usin, product_id, sku, name, category, pct_code, qty, price, cost, tax_rate, line_discount, sale_value, tax_amount, line_total) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                (usin, it.get("id"), it["sku"], it["name"], it.get("category", "General Retail"),
                 it.get("pctCode", "99990000"), int(it["qty"]), float(it["price"]), float(it.get("cost", 0)),
                 float(it.get("taxRate", 18)), float(it.get("lineDiscount", 0)), float(it.get("saleValue", 0)),
                 float(it.get("taxAmount", 0)), float(it.get("lineTotal", 0)))
            )
            if it.get("id"):
                conn.execute("UPDATE products SET stock = MAX(0, stock - ?) WHERE id=? AND manage_stock=1", (int(it["qty"]), it["id"]))

        if cust and not cust["is_walkin"]:
            conn.execute("UPDATE contacts SET due_balance=? WHERE id=?", (closing, cid))
            conn.execute(
                "INSERT INTO contact_ledger (contact_id, entry_date, entry_type, ref_no, debit, credit, balance) VALUES (?,?,?,?,?,?,?)",
                (cid, datetime.now().strftime("%Y-%m-%d %H:%M"), f"POS Sale ({usin})", usin, grand, min(paid, prev_bal + grand), closing)
            )
        conn.commit()
        inv_row = dict(conn.execute("SELECT * FROM invoices WHERE usin=?", (usin,)).fetchone())
        return inv_row
    finally:
        conn.close()


def get_customer_report(cid, db_path=None):
    conn = get_conn(db_path)
    cust = conn.execute("SELECT * FROM contacts WHERE id=?", (cid,)).fetchone()
    if not cust:
        conn.close()
        return None
    invs = [dict(r) for r in conn.execute("SELECT * FROM invoices WHERE customer_id=? ORDER BY date_time DESC", (cid,))]
    items = [
        dict(r) for r in conn.execute(
            "SELECT i.date_time, i.usin, i.fbr_invoice_number, it.sku, it.name, it.qty, it.price, it.tax_rate, it.line_total "
            "FROM invoice_items it JOIN invoices i ON it.invoice_usin = i.usin WHERE i.customer_id=? ORDER BY i.date_time DESC",
            (cid,)
        )
    ]
    conn.close()
    return {"customer": dict(cust), "invoices": invs, "productsBought": items}
