"""
db.py — a small banking SQL database (SQLite, in-memory) with deterministic seed
data. This is the relational "system of record" an NL->SQL tool queries.

Schema (a classic normalized banking model):
    customers(id, name, segment)
    accounts(id, customer_id -> customers.id, type, balance, currency)
    transactions(id, account_id -> accounts.id, date, description, amount, category)

`amount` is signed: negative = money out (spend), positive = money in.
"""
from __future__ import annotations

import sqlite3

SCHEMA = """
CREATE TABLE customers (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL, segment TEXT
);
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL,
    type TEXT, balance REAL, currency TEXT DEFAULT 'AED',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE transactions (
    id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL,
    date TEXT, description TEXT, amount REAL, category TEXT,
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);
"""

_CUSTOMERS = [
    (1, "Amal Al Mansoori", "retail"),
    (2, "Omar Hassan", "retail"),
]
_ACCOUNTS = [
    (10, 1, "current", 1250.00, "AED"),
    (11, 1, "savings", 50000.00, "AED"),
    (20, 2, "current", 40.00, "AED"),
]
_TXNS = [
    # Amal — account 10 (current)
    (100, 10, "2026-06-09", "Salary",            9000.00, "income"),
    (101, 10, "2026-06-10", "Carrefour Grocery",  -85.50, "groceries"),
    (102, 10, "2026-05-28", "DEWA Utility",       -420.00, "utilities"),
    (103, 10, "2026-05-15", "Noon Online",        -310.25, "shopping"),
    (104, 10, "2026-06-12", "Carrefour Grocery",  -64.00, "groceries"),
    # Amal — account 11 (savings)
    (110, 11, "2026-06-01", "Interest Credit",      62.50, "income"),
    # Omar — account 20
    (200, 20, "2026-06-11", "Salik Toll",          -16.00, "transport"),
]


def make_db() -> sqlite3.Connection:
    """Build a fresh in-memory DB. Returns a read/write connection used by the
    seeder; the query layer opens a SEPARATE read-only view (see nl2sql.py)."""
    conn = sqlite3.connect(":memory:")
    conn.executescript(SCHEMA)
    conn.executemany("INSERT INTO customers VALUES (?,?,?)", _CUSTOMERS)
    conn.executemany("INSERT INTO accounts VALUES (?,?,?,?,?)", _ACCOUNTS)
    conn.executemany("INSERT INTO transactions VALUES (?,?,?,?,?,?)", _TXNS)
    conn.commit()
    conn.row_factory = sqlite3.Row
    return conn