# app/db/database.py
# ──────────────────────────────────────────────────
#  Lightweight SQLite store — only manages API keys.
#  No rate limit tables. No complex queries.
# ──────────────────────────────────────────────────

import sqlite3
import secrets
from pathlib import Path
from contextlib import contextmanager
import json

DB_PATH = Path(__file__).parent / "data" / "keys.db"


def init_db():
    DB_PATH.parent.mkdir(exist_ok=True)
    with _conn() as c:
        c.execute("""
            CREATE TABLE IF NOT EXISTS api_keys (
                key          TEXT PRIMARY KEY,
                client_name  TEXT NOT NULL,
                active       INTEGER DEFAULT 1,
                created_at   TEXT DEFAULT (datetime('now'))
            )
        """)
        c.execute("""
            CREATE TABLE IF NOT EXISTS users (
                username   TEXT PRIMARY KEY,
                password   TEXT NOT NULL,
                role       TEXT NOT NULL DEFAULT 'user',
                created_at TEXT DEFAULT (datetime('now'))
            )
        """)
        c.execute("""
            CREATE TABLE IF NOT EXISTS chat_history (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT NOT NULL,
                client_key TEXT NOT NULL,
                role       TEXT NOT NULL,
                content    TEXT NOT NULL,
                created_at TEXT DEFAULT (datetime('now'))
            )
        """)
        c.execute("""
            CREATE TABLE IF NOT EXISTS context_config (
                id              INTEGER PRIMARY KEY CHECK (id = 1),
                allowed_topics  TEXT NOT NULL,
                blocked_phrases TEXT NOT NULL,
                knowledge_base  TEXT NOT NULL,
                system_prompt   TEXT NOT NULL,
                updated_at      TEXT DEFAULT (datetime('now'))
            )
        """)
        if c.execute("SELECT COUNT(*) FROM context_config").fetchone()[0] == 0:
            import json
            from app.core import context as _default_ctx
            c.execute("""
                INSERT INTO context_config (id, allowed_topics, blocked_phrases, knowledge_base, system_prompt)
                VALUES (1, ?, ?, ?, ?)
            """, (
                json.dumps(_default_ctx.ALLOWED_TOPICS),
                json.dumps(_default_ctx.BLOCKED_PHRASES),
                json.dumps(_default_ctx.KNOWLEDGE_BASE),
                json.dumps(_default_ctx.SYSTEM_PROMPT),
            ))

        c.execute("""
            CREATE TABLE IF NOT EXISTS bank_accounts (
                username          TEXT PRIMARY KEY REFERENCES users(username),
                full_name         TEXT NOT NULL,
                account_number    TEXT NOT NULL UNIQUE,
                account_type      TEXT NOT NULL DEFAULT 'Savings',
                savings_balance   REAL NOT NULL DEFAULT 0.0,
                checking_balance  REAL NOT NULL DEFAULT 0.0,
                currency          TEXT NOT NULL DEFAULT 'INR',
                ifsc_code         TEXT NOT NULL DEFAULT 'SBKX0001234',
                branch            TEXT NOT NULL DEFAULT 'Main Branch',
                transactions      TEXT NOT NULL DEFAULT '[]',
                loans             TEXT NOT NULL DEFAULT '[]',
                credit_cards      TEXT NOT NULL DEFAULT '[]',
                updated_at        TEXT DEFAULT (datetime('now'))
            )
        """)

@contextmanager
def _conn():
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()


# ── CRUD ──────────────────────────────────────────

def create_key(client_name: str) -> str:
    """Generate and store a new API key. Returns the key string."""
    key = "cb_" + secrets.token_urlsafe(32)
    with _conn() as c:
        c.execute(
            "INSERT INTO api_keys (key, client_name) VALUES (?, ?)",
            (key, client_name),
        )
    return key


def get_key(api_key: str) -> dict | None:
    """Return key data if the key exists and is active, else None."""
    with _conn() as c:
        row = c.execute(
            "SELECT * FROM api_keys WHERE key = ? AND active = 1",
            (api_key,),
        ).fetchone()
    return dict(row) if row else None


def list_keys() -> list[dict]:
    """Return all keys (active and inactive)."""
    with _conn() as c:
        rows = c.execute(
            "SELECT key, client_name, active, created_at FROM api_keys ORDER BY created_at DESC"
        ).fetchall()
    return [dict(r) for r in rows]


def revoke_key(api_key: str) -> bool:
    """Deactivate a key. Returns True if the key existed."""
    with _conn() as c:
        cur = c.execute(
            "UPDATE api_keys SET active = 0 WHERE key = ?", (api_key,)
        )
    return cur.rowcount > 0


def create_user(username: str, password: str, role: str = "user") -> bool:
    """Insert a new user. Returns False if username already exists."""
    try:
        with _conn() as c:
            c.execute(
                "INSERT INTO users (username, password, role) VALUES (?, ?, ?)",
                (username, password, role),
            )
        return True
    except sqlite3.IntegrityError:
        return False


def get_user(username: str) -> dict | None:
    """Return user data if found, else None."""
    with _conn() as c:
        row = c.execute(
            "SELECT username, password, role FROM users WHERE username = ?",
            (username,),
        ).fetchone()
    return dict(row) if row else None


def list_users() -> list[dict]:
    """Return all users."""
    with _conn() as c:
        rows = c.execute(
            "SELECT username, role, created_at FROM users ORDER BY created_at DESC"
        ).fetchall()
    return [dict(r) for r in rows]

# ── Chat History ───────────────────────────────────

def save_message(session_id: str, client_key: str, role: str, content: str):
    with _conn() as c:
        c.execute(
            "INSERT INTO chat_history (session_id, client_key, role, content) VALUES (?, ?, ?, ?)",
            (session_id, client_key, role, content),
        )

def get_history(session_id: str, client_key: str) -> list[dict]:
    with _conn() as c:
        rows = c.execute(
            "SELECT role, content FROM chat_history WHERE session_id=? AND client_key=? ORDER BY id ASC",
            (session_id, client_key),
        ).fetchall()
    return [dict(r) for r in rows]

def clear_history(session_id: str, client_key: str):
    with _conn() as c:
        c.execute(
            "DELETE FROM chat_history WHERE session_id=? AND client_key=?",
            (session_id, client_key),
        )

# ── Context Config ────────────────────────────────

def get_context() -> dict:
    with _conn() as c:
        row = c.execute("SELECT * FROM context_config WHERE id=1").fetchone()
    d = dict(row)
    d["allowed_topics"]  = json.loads(d["allowed_topics"])
    d["blocked_phrases"] = json.loads(d["blocked_phrases"])
    return d

def update_context(allowed_topics=None, blocked_phrases=None, knowledge_base=None, system_prompt=None):
    current = get_context()
    with _conn() as c:
        c.execute("""
            UPDATE context_config SET
                allowed_topics  = ?,
                blocked_phrases = ?,
                knowledge_base  = ?,
                system_prompt   = ?,
                updated_at      = datetime('now')
            WHERE id = 1
        """, (
            json.dumps(allowed_topics  if allowed_topics  is not None else current["allowed_topics"]),
            json.dumps(blocked_phrases if blocked_phrases is not None else current["blocked_phrases"]),
            knowledge_base  if knowledge_base  is not None else current["knowledge_base"],
            system_prompt   if system_prompt   is not None else current["system_prompt"],
        ))

# ── Bank Account CRUD ─────────────────────────────

def get_account(username: str) -> dict | None:
    with _conn() as c:
        row = c.execute(
            "SELECT * FROM bank_accounts WHERE username = ?",
            (username,),
        ).fetchone()
    if not row:
        return None
    d = dict(row)
    d["transactions"] = json.loads(d["transactions"])
    d["loans"]        = json.loads(d["loans"])
    d["credit_cards"] = json.loads(d["credit_cards"])
    return d


def build_account_context(username: str) -> str:
    acct = get_account(username)
    if not acct:
        return ""
    lines = [
        f"=== ACCOUNT CONTEXT FOR {acct['full_name'].upper()} ===",
        f"Account Number : ...{acct['account_number'][-4:]}",
        f"Account Type   : {acct['account_type']}",
        f"Branch         : {acct['branch']}",
        f"IFSC Code      : {acct['ifsc_code']}",
        f"Savings Balance : {acct['currency']} {acct['savings_balance']:,.2f}",
        f"Checking Balance: {acct['currency']} {acct['checking_balance']:,.2f}",
        "",
        "--- Recent Transactions (last 5) ---",
    ]
    for t in acct["transactions"][:5]:
        sign = "+" if t["amount"] >= 0 else ""
        lines.append(f"  {t['date']}  {t['description']:<30}  {sign}{t['amount']:,.2f}")
    if acct["loans"]:
        lines.append("\n--- Active Loans ---")
        for ln in acct["loans"]:
            lines.append(
                f"  {ln['type']}: Outstanding {acct['currency']} {ln['outstanding']:,.2f} "
                f"| EMI {acct['currency']} {ln['emi']:,.2f}/mo @ {ln['rate']}% "
                f"| Next due {ln['next_due']}"
            )
    if acct["credit_cards"]:
        lines.append("\n--- Credit Cards ---")
        for cc in acct["credit_cards"]:
            lines.append(
                f"  {cc['number']}: Outstanding {acct['currency']} {cc['outstanding']:,.2f} "
                f"| Limit {acct['currency']} {cc['limit']:,.2f} "
                f"| Due {cc['due_date']} | Min due {acct['currency']} {cc['minimum_due']:,.2f}"
            )
    lines.append("=== END ACCOUNT CONTEXT ===")
    return "\n".join(lines)

if __name__ == "__main__":
    print("Initializing database...")
    init_db()
    print(f"Database created at: {DB_PATH}")