backend/app/erp/data.py

"""Simulated retail ERP data warehouse (SQLite) — the knowledgebase the ERP DocIQ
chatbot reasons over, and the source domain for the fine-tuning dataset.

Deterministic: a fixed RNG seed makes the whole warehouse reproducible, so NLQ
answers, analytics, evals and the fine-tune dataset are all stable across runs.

Schema (a retail accounts-payable / procurement slice):
  vendors(vendor_id, name, region, category, payment_terms, on_time_rate, risk_tier)
  products(sku, name, category, unit_cost, unit_price)
  purchase_orders(po_id, vendor_id, order_date, status, region, amount)
  po_lines(po_id, sku, qty, unit_price, line_total)
  invoices(invoice_id, po_id, vendor_id, invoice_date, due_date, amount, tax,
           total, status, paid_date, days_to_pay)
  gl_entries(entry_id, invoice_id, account, cost_center, period, amount)
  inventory(sku, region, on_hand, reorder_point, monthly_demand)
  returns(return_id, sku, region, return_date, qty, reason, refund_amount)

This is intentionally a *small* but internally-consistent dataset: invoices roll up
from PO lines, GL entries roll up from invoices, returns reference real SKUs, so
analytics ("why did spend spike in Q2", "top vendors by late payments") are answerable
from the data rather than canned.
"""
from __future__ import annotations

import random
import sqlite3
import threading
from datetime import date, timedelta
from pathlib import Path

SEED = 20260101

REGIONS = ["Northeast", "Midwest", "South", "West"]
CATEGORIES = ["Fixtures", "Electronics", "Apparel", "Grocery", "Packaging", "Logistics"]
RISK = ["low", "low", "low", "medium", "medium", "high"]

VENDOR_NAMES = [
    "Meridian Industrial", "Nordic Fixture Works", "BrightLite Electronics", "Halcyon Build",
    "Cascade Apparel Co", "Summit Packaging", "BlueRiver Logistics", "Orchard Grocery Supply",
    "PrimeEdge Components", "Vertex Retail Systems", "Granite State Goods", "Copperline Textiles",
    "Lakeside Distribution", "IronGate Hardware", "Pinnacle Foods", "Aurora Display Group",
]

PRODUCTS = [
    ("SKU-1001", "Heavy-gauge shelf unit", "Fixtures", 142.0, 189.0),
    ("SKU-1002", "LED retail strip 2m", "Electronics", 14.5, 22.4),
    ("SKU-1003", "Endcap display birch", "Fixtures", 232.0, 310.0),
    ("SKU-1004", "Thermal receipt rolls", "Packaging", 1.1, 2.4),
    ("SKU-1005", "Barcode scanner USB", "Electronics", 38.0, 59.0),
    ("SKU-1006", "Store associate polo", "Apparel", 9.2, 18.0),
    ("SKU-1007", "Pallet wrap roll", "Packaging", 18.0, 27.5),
    ("SKU-1008", "Organic coffee 1kg", "Grocery", 8.5, 14.0),
    ("SKU-1009", "Freight pallet move", "Logistics", 22.0, 35.0),
    ("SKU-1010", "Security tag pack", "Electronics", 4.0, 7.5),
    ("SKU-1011", "Checkout counter mat", "Fixtures", 26.0, 41.0),
    ("SKU-1012", "Reusable tote bag", "Apparel", 2.3, 5.0),
]

ACCOUNTS = {
    "Fixtures": "5000-Store-Fit-Out", "Electronics": "5100-IT-Equipment",
    "Apparel": "5200-Uniforms", "Grocery": "5300-COGS-Grocery",
    "Packaging": "5400-Supplies", "Logistics": "5500-Freight",
}
RETURN_REASONS = ["damaged", "wrong item", "defective", "overstock", "late delivery"]


class ErpWarehouse:
    """Read-mostly SQLite warehouse with a guarded NLQ query surface."""

    def __init__(self, db_path: str | Path) -> None:
        self.db_path = Path(db_path)
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self._lock = threading.Lock()
        self._conn = sqlite3.connect(str(self.db_path), check_same_thread=False)
        self._conn.row_factory = sqlite3.Row
        if not self._has_data():
            self._build()

    def _has_data(self) -> bool:
        try:
            return self._conn.execute("SELECT 1 FROM invoices LIMIT 1").fetchone() is not None
        except sqlite3.OperationalError:
            return False

    # --- schema + seed --------------------------------------------------------
    def _build(self) -> None:
        rng = random.Random(SEED)
        with self._lock:
            c = self._conn
            c.executescript(
                """
                DROP TABLE IF EXISTS vendors; DROP TABLE IF EXISTS products;
                DROP TABLE IF EXISTS purchase_orders; DROP TABLE IF EXISTS po_lines;
                DROP TABLE IF EXISTS invoices; DROP TABLE IF EXISTS gl_entries;
                DROP TABLE IF EXISTS inventory; DROP TABLE IF EXISTS returns;
                CREATE TABLE vendors(vendor_id TEXT PRIMARY KEY, name TEXT, region TEXT,
                    category TEXT, payment_terms TEXT, on_time_rate REAL, risk_tier TEXT);
                CREATE TABLE products(sku TEXT PRIMARY KEY, name TEXT, category TEXT,
                    unit_cost REAL, unit_price REAL);
                CREATE TABLE purchase_orders(po_id TEXT PRIMARY KEY, vendor_id TEXT,
                    order_date TEXT, status TEXT, region TEXT, amount REAL);
                CREATE TABLE po_lines(po_id TEXT, sku TEXT, qty INTEGER, unit_price REAL,
                    line_total REAL);
                CREATE TABLE invoices(invoice_id TEXT PRIMARY KEY, po_id TEXT, vendor_id TEXT,
                    invoice_date TEXT, due_date TEXT, amount REAL, tax REAL, total REAL,
                    status TEXT, paid_date TEXT, days_to_pay INTEGER);
                CREATE TABLE gl_entries(entry_id TEXT PRIMARY KEY, invoice_id TEXT, account TEXT,
                    cost_center TEXT, period TEXT, amount REAL);
                CREATE TABLE inventory(sku TEXT, region TEXT, on_hand INTEGER,
                    reorder_point INTEGER, monthly_demand INTEGER);
                CREATE TABLE returns(return_id TEXT, sku TEXT, region TEXT, return_date TEXT,
                    qty INTEGER, reason TEXT, refund_amount REAL);
                """
            )
            # vendors
            vendors = []
            for i, nm in enumerate(VENDOR_NAMES):
                cat = CATEGORIES[i % len(CATEGORIES)]
                vid = f"V-{1000+i}"
                terms = rng.choice(["Net 30", "Net 30", "Net 45", "Net 60"])
                on_time = round(rng.uniform(0.72, 0.99), 3)
                vendors.append((vid, nm, rng.choice(REGIONS), cat, terms, on_time, RISK[i % len(RISK)]))
            c.executemany("INSERT INTO vendors VALUES (?,?,?,?,?,?,?)", vendors)
            c.executemany("INSERT INTO products VALUES (?,?,?,?,?)", PRODUCTS)

            prod_by_cat: dict[str, list] = {}
            for p in PRODUCTS:
                prod_by_cat.setdefault(p[2], []).append(p)

            # 12 months of POs → invoices → GL. A deliberate Q2 spend spike on Fixtures
            # (store-remodel program) makes "why did spend rise" answerable from data.
            po_n = inv_n = gl_n = 0
            start = date(2025, 7, 1)
            for month in range(12):
                m_date = (start + timedelta(days=30 * month))
                period = m_date.strftime("%Y-%m")
                # base order volume, with a Fixtures surge in 2026 Q2 (months 9-11)
                n_orders = rng.randint(10, 16)
                surge = month in (9, 10, 11)
                for _ in range(n_orders):
                    v = rng.choice(vendors)
                    vid, vcat, vregion, terms, on_time = v[0], v[3], v[2], v[4], v[5]
                    # bias product to vendor category; surge picks Fixtures
                    cat = "Fixtures" if (surge and rng.random() < 0.45) else vcat
                    pool = prod_by_cat.get(cat) or PRODUCTS
                    po_n += 1
                    po_id = f"PO-{2000+po_n}"
                    od = m_date + timedelta(days=rng.randint(0, 27))
                    n_lines = rng.randint(1, 4)
                    amount = 0.0
                    lines = []
                    for _ in range(n_lines):
                        p = rng.choice(pool)
                        qty = rng.randint(2, 40) * (3 if (surge and cat == "Fixtures") else 1)
                        unit = round(p[4] * rng.uniform(0.95, 1.05), 2)
                        lt = round(qty * unit, 2)
                        amount += lt
                        lines.append((po_id, p[0], qty, unit, lt))
                    status = rng.choice(["received", "received", "received", "open", "cancelled"])
                    c.execute("INSERT INTO purchase_orders VALUES (?,?,?,?,?,?)",
                              (po_id, vid, od.isoformat(), status, vregion, round(amount, 2)))
                    c.executemany("INSERT INTO po_lines VALUES (?,?,?,?,?)", lines)
                    if status == "cancelled":
                        continue
                    # invoice
                    inv_n += 1
                    inv_id = f"INV-{5000+inv_n}"
                    idate = od + timedelta(days=rng.randint(1, 10))
                    term_days = int(terms.split()[1])
                    due = idate + timedelta(days=term_days)
                    tax = round(amount * 0.0825, 2)
                    total = round(amount + tax, 2)
                    paid = rng.random() < 0.82
                    if paid:
                        # late if vendor has low on-time rate
                        late = rng.random() > on_time
                        dd = rng.randint(term_days + 3, term_days + 25) if late else rng.randint(8, term_days)
                        paid_date = (idate + timedelta(days=dd)).isoformat()
                        istatus = "paid"
                        days_to_pay = dd
                    else:
                        paid_date, istatus, days_to_pay = None, "open", None
                    c.execute("INSERT INTO invoices VALUES (?,?,?,?,?,?,?,?,?,?,?)",
                              (inv_id, po_id, vid, idate.isoformat(), due.isoformat(),
                               round(amount, 2), tax, total, istatus, paid_date, days_to_pay))
                    gl_n += 1
                    c.execute("INSERT INTO gl_entries VALUES (?,?,?,?,?,?)",
                              (f"GL-{9000+gl_n}", inv_id, ACCOUNTS.get(cat, "5900-Other"),
                               f"CC-{vregion[:3].upper()}", period, total))
            # inventory + returns
            for p in PRODUCTS:
                for r in REGIONS:
                    dem = rng.randint(20, 200)
                    c.execute("INSERT INTO inventory VALUES (?,?,?,?,?)",
                              (p[0], r, rng.randint(0, 400), int(dem * 0.5), dem))
            ret_n = 0
            for _ in range(60):
                p = rng.choice(PRODUCTS)
                ret_n += 1
                rdate = (start + timedelta(days=rng.randint(0, 360)))
                qty = rng.randint(1, 12)
                c.execute("INSERT INTO returns VALUES (?,?,?,?,?,?,?)",
                          (f"R-{7000+ret_n}", p[0], rng.choice(REGIONS), rdate.isoformat(),
                           qty, rng.choice(RETURN_REASONS), round(qty * p[4], 2)))
            c.commit()

    # --- guarded query surface (for NLQ) --------------------------------------
    def query(self, sql: str, limit: int = 200) -> tuple[list[str], list[list]]:
        """Execute a single read-only SELECT. Raises ValueError on anything unsafe."""
        safe = sql.strip().rstrip(";").strip()
        low = safe.lower()
        if not low.startswith(("select", "with")):
            raise ValueError("only SELECT/WITH queries are allowed")
        forbidden = (" insert ", " update ", " delete ", " drop ", " alter ", " create ",
                     " attach ", " pragma ", " replace ", "--", ";")
        padded = f" {low} "
        for f in forbidden:
            if f in padded:
                raise ValueError(f"forbidden token in query: {f.strip()!r}")
        if " limit " not in low:
            safe = f"{safe} LIMIT {limit}"
        with self._lock:
            cur = self._conn.execute(safe)
            rows = cur.fetchall()
            cols = [d[0] for d in cur.description]
        return cols, [list(r) for r in rows]

    def scalar(self, sql: str):
        cols, rows = self.query(sql, limit=1)
        return rows[0][0] if rows else None

    def table_counts(self) -> dict:
        out = {}
        for t in ("vendors", "products", "purchase_orders", "po_lines", "invoices",
                  "gl_entries", "inventory", "returns"):
            out[t] = self.scalar(f"SELECT COUNT(*) FROM {t}")
        return out


# Compact schema description handed to the NLQ model (kept byte-stable for caching).
ERP_SCHEMA_DOC = """ERP warehouse schema (SQLite, retail procurement / AP):
- vendors(vendor_id, name, region, category, payment_terms, on_time_rate, risk_tier)
- products(sku, name, category, unit_cost, unit_price)
- purchase_orders(po_id, vendor_id, order_date, status, region, amount)
- po_lines(po_id, sku, qty, unit_price, line_total)
- invoices(invoice_id, po_id, vendor_id, invoice_date, due_date, amount, tax, total, status, paid_date, days_to_pay)
- gl_entries(entry_id, invoice_id, account, cost_center, period, amount)   -- period is 'YYYY-MM'
- inventory(sku, region, on_hand, reorder_point, monthly_demand)
- returns(return_id, sku, region, return_date, qty, reason, refund_amount)
Notes: invoices.status in ('paid','open'); a payment is LATE when days_to_pay > payment_terms days.
Spend = invoices.total. Dates are ISO 'YYYY-MM-DD'. gl_entries.period groups spend by month."""

EXAMPLE_QUESTIONS = [
    "What was total invoiced spend by month?",
    "Who are the top 5 vendors by spend?",
    "Which vendors paid late most often?",
    "Why did spend rise in Q2 2026?",
    "What is the late-payment rate overall?",
    "Show spend by category.",
    "Summarize accounts payable health.",
    "Which SKUs are below reorder point?",
    "What is the total value of open (unpaid) invoices?",
    "Top return reasons by refund amount?",
]

_WAREHOUSE: ErpWarehouse | None = None


def get_warehouse(settings) -> ErpWarehouse:
    """Process-wide singleton, seeded under the writable dir."""
    global _WAREHOUSE
    if _WAREHOUSE is None:
        path = getattr(settings, "erp_db_path", None) or (settings.writable_dir / "erp.db")
        _WAREHOUSE = ErpWarehouse(path)
    return _WAREHOUSE