"""Create an isolated external SQLite sample for connector testing.

Usage: python tools/create_external_sample.py
No existing database is ever overwritten.
"""
import os
import random
import sqlite3
from datetime import date, timedelta
from pathlib import Path

root = Path(__file__).resolve().parents[1]
target = Path(os.environ.get('MORTGAGE_DATA_DIR', root / 'data')) / 'external_example.db'
target.parent.mkdir(parents=True, exist_ok=True)
if target.exists():
    raise SystemExit(f'Sample already exists at {target}. Nothing changed.')

rng = random.Random(2105)
today = date.today()
conn = sqlite3.connect(target)
try:
    conn.execute('''CREATE TABLE external_loans (
        loan_number TEXT PRIMARY KEY, loan_status TEXT, loan_channel TEXT,
        loan_program TEXT, loan_amount REAL, note_rate REAL, registered_date TEXT,
        funded_date TEXT, gross_profit REAL, broker_company TEXT, rate_locked_date TEXT
    )''')
    rows = []
    for i in range(1, 81):
        amount = rng.randint(28, 110) * 10000
        status = 'Funded' if i <= 55 else rng.choice(['Loan Open', 'Underwriting', 'Approved', 'Clear to Close'])
        funded = today - timedelta(days=rng.randint(1, 320)) if status == 'Funded' else None
        registered = (funded or today) - timedelta(days=rng.randint(4, 38))
        locked = registered + timedelta(days=3)
        rows.append((f'EXT-DEMO-{i:04d}', status, rng.choice(['Wholesale', 'Retail', 'Correspondent']),
                     rng.choice(['Conventional', 'FHA', 'VA']), amount, round(rng.uniform(5.8, 7.2), 2),
                     registered.isoformat(), funded.isoformat() if funded else None,
                     round(amount * 0.014, 2) if funded else None,
                     rng.choice(['Sample Partner A', 'Sample Partner B', 'Sample Partner C']), locked.isoformat()))
    conn.executemany('INSERT INTO external_loans VALUES (?,?,?,?,?,?,?,?,?,?,?)', rows)
    conn.execute('CREATE VIEW mortgage_dashboard_source AS SELECT * FROM external_loans')
    conn.commit()
finally:
    conn.close()
print(f'Created {len(rows)} synthetic external loans at {target}')
print(f'PowerShell: $env:MORTGAGE_EXTERNAL_URL = "sqlite:///{target.as_posix()}"')
