import sqlite3, json
from pathlib import Path
import os

if __name__ == "__main__":
    DB_PATH = Path(__file__).parent / "app" / "db" / "data" / "keys.db"

    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()

    c.executescript('''
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'))
);

CREATE TABLE IF NOT EXISTS bank_accounts (
    username         TEXT PRIMARY KEY,
    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'))
);
''')

    users = [('alice','alice123'),('bob','bob456'),('charlie','charlie789'),('diana','diana321'),('ethan','ethan654')]
    for u, p in users:
        c.execute('INSERT OR IGNORE INTO users (username, password, role) VALUES (?, ?, ?)', (u, p, 'user'))

    accounts = [
      ('alice','Alice Sharma','SB10000001234','Savings',87450.0,12300.50,'INR','SBKX0001001','Andheri West Mumbai','[{"date":"2026-05-30","description":"UPI Swiggy","amount":-340.0,"balance":87450.0}]','[{"type":"Home Loan","principal":3500000.0,"outstanding":2840000.0,"emi":28500.0,"rate":8.5,"next_due":"2026-06-05"}]','[{"number":"XXXX-XXXX-XXXX-4321","limit":150000.0,"outstanding":23400.0,"due_date":"2026-06-15","minimum_due":1170.0}]', None),
      ('bob','Bob Verma','SB10000005678','Savings',123580.75,8000.0,'INR','SBKX0001002','Bandra Mumbai','[{"date":"2026-05-29","description":"Salary Credit","amount":72000.0,"balance":123580.75}]','[]','[{"number":"XXXX-XXXX-XXXX-8765","limit":200000.0,"outstanding":45600.0,"due_date":"2026-06-18","minimum_due":2280.0}]', None),
      ('charlie','Charlie Menon','SB10000009012','Current',45200.0,67800.0,'INR','SBKX0001003','Koramangala Bengaluru','[{"date":"2026-05-31","description":"GST Refund","amount":12000.0,"balance":45200.0}]','[{"type":"Business Loan","principal":1000000.0,"outstanding":620000.0,"emi":22000.0,"rate":11.5,"next_due":"2026-06-10"}]','[]', None),
      ('diana','Diana Kapoor','SB10000003456','Savings',234900.50,15500.0,'INR','SBKX0001004','Connaught Place Delhi','[{"date":"2026-05-28","description":"Salary Credit","amount":95000.0,"balance":234900.5}]','[{"type":"Car Loan","principal":800000.0,"outstanding":340000.0,"emi":14200.0,"rate":9.2,"next_due":"2026-06-07"}]','[{"number":"XXXX-XXXX-XXXX-2222","limit":500000.0,"outstanding":112000.0,"due_date":"2026-06-20","minimum_due":5600.0}]', None),
      ('ethan','Ethan DSouza','SB10000007890','Savings',18600.0,3200.0,'INR','SBKX0001005','Fort Mumbai','[{"date":"2026-05-28","description":"Salary Credit","amount":38000.0,"balance":19050.0}]','[{"type":"Personal Loan","principal":300000.0,"outstanding":187000.0,"emi":8900.0,"rate":14.0,"next_due":"2026-06-24"}]','[]', None),
    ]

    for a in accounts:
        c.execute('INSERT OR REPLACE INTO bank_accounts VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)', a)

    conn.commit()
    conn.close()
    print('Done! 5 users seeded.')