"""Versioned, forward-only SQLite schema migrations.

Every schema change is a numbered migration recorded in ``schema_migrations``.
Migrations are applied in order at startup (or with ``python manage.py migrate``).
Rules for adding one:

1. Append a new ``(version, name, function)`` entry to ``MIGRATIONS``; never
   edit or renumber a migration that has shipped.
2. Make the function safe on databases created by any older release.
3. Add a regression test and a CHANGELOG entry.

Private databases receive an automatic pre-migration backup when an existing
database has pending migrations (see ``services/backup.py``).
"""
import hashlib
import json
import sqlite3
from datetime import datetime, timezone


def _columns(conn, table):
    return {row[1] for row in conn.execute(f'PRAGMA table_info({table})')}


def _table_exists(conn, name):
    return conn.execute("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (name,)).fetchone() is not None


# --------------------------------------------------------------------------- 1
def m001_baseline(conn):
    """Original v1/v2 tables. CREATE IF NOT EXISTS keeps older databases intact."""
    conn.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            role TEXT DEFAULT 'user',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )""")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS loans (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            loan_number TEXT UNIQUE NOT NULL,
            borrower_last TEXT,
            borrower_first TEXT,
            loan_program TEXT,
            loan_channel TEXT,
            loan_status TEXT,
            loan_purpose TEXT,
            property_purpose TEXT,
            loan_amount REAL,
            note_rate REAL,
            ltv REAL,
            credit_score INTEGER,
            broker_company TEXT,
            account_executive TEXT,
            underwriter TEXT,
            processor TEXT,
            registered_date TEXT,
            underwriting_date TEXT,
            approved_date TEXT,
            clear_to_close_date TEXT,
            docs_out_date TEXT,
            docs_back_date TEXT,
            scheduled_funding TEXT,
            funded_date TEXT,
            rate_locked_date TEXT,
            investor_name TEXT,
            broker_lock_price REAL,
            investor_lock_price REAL,
            gross_profit REAL,
            net_profit REAL,
            status_locked INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )""")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS broker_companies (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL,
            nmls_id TEXT,
            address TEXT,
            city TEXT,
            state TEXT,
            zip_code TEXT,
            phone TEXT,
            status TEXT DEFAULT 'Active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )""")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS loan_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            loan_id INTEGER REFERENCES loans(id),
            event_type TEXT NOT NULL,
            details TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )""")
    conn.execute("CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT)")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS audit_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            actor_user_id INTEGER,
            action TEXT NOT NULL,
            category TEXT NOT NULL,
            subject TEXT,
            details TEXT,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        )""")
    conn.execute('CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_events(created_at)')


# --------------------------------------------------------------------------- 2
def m002_user_profiles(conn):
    """Profile fields and role normalization (formerly ad hoc ALTERs in core.py)."""
    existing = _columns(conn, 'users')
    additions = {
        'display_name': 'TEXT', 'email': 'TEXT', 'job_title': 'TEXT',
        'timezone': "TEXT DEFAULT 'America/Los_Angeles'",
        'is_active': 'INTEGER NOT NULL DEFAULT 1',
        'last_login_at': 'TEXT',
    }
    for name, declaration in additions.items():
        if name not in existing:
            conn.execute(f'ALTER TABLE users ADD COLUMN {name} {declaration}')
    conn.execute("UPDATE users SET role = 'viewer' WHERE role = 'user' OR role IS NULL")
    conn.execute('CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email) WHERE email IS NOT NULL')


# --------------------------------------------------------------------------- 3
def m003_loan_events_cascade(conn):
    """Rebuild loan_events with ON DELETE CASCADE and an actor column.

    The original foreign key had no cascade, which made ``INSERT OR REPLACE``
    imports fail for any loan with history.
    """
    conn.execute("""
        CREATE TABLE loan_events_v3 (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            loan_id INTEGER NOT NULL REFERENCES loans(id) ON DELETE CASCADE,
            event_type TEXT NOT NULL,
            details TEXT,
            actor_user_id INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )""")
    conn.execute("""
        INSERT INTO loan_events_v3 (id, loan_id, event_type, details, created_at)
        SELECT e.id, e.loan_id, e.event_type, e.details, e.created_at
        FROM loan_events e JOIN loans l ON l.id = e.loan_id""")
    conn.execute('DROP TABLE loan_events')
    conn.execute('ALTER TABLE loan_events_v3 RENAME TO loan_events')
    conn.execute('CREATE INDEX IF NOT EXISTS idx_loan_events_loan ON loan_events(loan_id, created_at)')


# --------------------------------------------------------------------------- 4
def audit_row_hash(prev_hash, row):
    """Canonical SHA-256 for one audit row, chained to the previous row."""
    payload = json.dumps({
        'prev': prev_hash or '',
        'actor_user_id': row['actor_user_id'],
        'action': row['action'],
        'category': row['category'],
        'subject': row['subject'] or '',
        'details': row['details'] or '',
        'created_at': row['created_at'],
    }, sort_keys=True, separators=(',', ':'))
    return hashlib.sha256(payload.encode('utf-8')).hexdigest()


def m004_audit_hash_chain(conn):
    """Tamper-evident audit log: every row stores its own and the previous hash."""
    existing = _columns(conn, 'audit_events')
    for name in ('prev_hash', 'row_hash'):
        if name not in existing:
            conn.execute(f'ALTER TABLE audit_events ADD COLUMN {name} TEXT')
    prev = ''
    rows = conn.execute('SELECT id, actor_user_id, action, category, subject, details, created_at '
                        'FROM audit_events ORDER BY id').fetchall()
    for row in rows:
        record = dict(zip(('id', 'actor_user_id', 'action', 'category', 'subject', 'details', 'created_at'), row))
        digest = audit_row_hash(prev, record)
        conn.execute('UPDATE audit_events SET prev_hash=?, row_hash=? WHERE id=?', (prev, digest, record['id']))
        prev = digest
    # Updates/deletes of audit rows are blocked inside the database itself.
    conn.execute("""CREATE TRIGGER IF NOT EXISTS trg_audit_no_update BEFORE UPDATE ON audit_events
                    BEGIN SELECT RAISE(ABORT, 'audit_events is append-only'); END""")
    conn.execute("""CREATE TRIGGER IF NOT EXISTS trg_audit_no_delete BEFORE DELETE ON audit_events
                    BEGIN SELECT RAISE(ABORT, 'audit_events is append-only'); END""")


# --------------------------------------------------------------------------- 5
def m005_login_attempts(conn):
    conn.execute("""
        CREATE TABLE IF NOT EXISTS login_attempts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL,
            remote_addr TEXT,
            success INTEGER NOT NULL,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        )""")
    conn.execute('CREATE INDEX IF NOT EXISTS idx_login_attempts_user ON login_attempts(username, created_at)')
    conn.execute('CREATE INDEX IF NOT EXISTS idx_login_attempts_addr ON login_attempts(remote_addr, created_at)')


# --------------------------------------------------------------------------- 6
def m006_import_batches(conn):
    """Provenance for every CSV import: who, when, which file (by hash), outcome."""
    conn.execute("""
        CREATE TABLE IF NOT EXISTS import_batches (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kind TEXT NOT NULL,
            filename TEXT,
            file_sha256 TEXT NOT NULL,
            mode TEXT NOT NULL,
            rows_read INTEGER NOT NULL DEFAULT 0,
            rows_inserted INTEGER NOT NULL DEFAULT 0,
            rows_updated INTEGER NOT NULL DEFAULT 0,
            rows_rejected INTEGER NOT NULL DEFAULT 0,
            status TEXT NOT NULL,
            actor_user_id INTEGER,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        )""")
    if 'import_batch_id' not in _columns(conn, 'loans'):
        conn.execute('ALTER TABLE loans ADD COLUMN import_batch_id INTEGER')


# --------------------------------------------------------------------------- 7
def m007_reporting_indexes(conn):
    for name, cols in (('idx_loans_status', 'loan_status'), ('idx_loans_funded', 'funded_date'),
                       ('idx_loans_registered', 'registered_date'), ('idx_loans_broker', 'broker_company'),
                       ('idx_loans_channel', 'loan_channel')):
        conn.execute(f'CREATE INDEX IF NOT EXISTS {name} ON loans({cols})')


MIGRATIONS = (
    (1, 'baseline schema', m001_baseline),
    (2, 'user profile columns', m002_user_profiles),
    (3, 'loan_events cascade + actor', m003_loan_events_cascade),
    (4, 'tamper-evident audit chain', m004_audit_hash_chain),
    (5, 'login attempt tracking', m005_login_attempts),
    (6, 'import batch provenance', m006_import_batches),
    (7, 'reporting indexes', m007_reporting_indexes),
)
LATEST_VERSION = MIGRATIONS[-1][0]


def current_version(conn):
    if not _table_exists(conn, 'schema_migrations'):
        return 0
    return conn.execute('SELECT COALESCE(MAX(version), 0) FROM schema_migrations').fetchone()[0]


def pending(conn):
    version = current_version(conn)
    return [m for m in MIGRATIONS if m[0] > version]


def has_user_data(conn):
    """True for an existing database (worth backing up before migrating)."""
    return _table_exists(conn, 'loans') or _table_exists(conn, 'users')


def apply_migrations(db_path):
    """Apply all pending migrations, each in its own transaction. Returns versions applied."""
    conn = sqlite3.connect(db_path, isolation_level=None)
    applied = []
    try:
        conn.execute("""CREATE TABLE IF NOT EXISTS schema_migrations (
                            version INTEGER PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL)""")
        # Table rebuilds need FK enforcement off; it is re-verified after each step.
        conn.execute('PRAGMA foreign_keys=OFF')
        for version, name, function in pending(conn):
            conn.execute('BEGIN IMMEDIATE')
            try:
                function(conn)
                problems = conn.execute('PRAGMA foreign_key_check').fetchall()
                if problems:
                    raise RuntimeError(f'Migration {version} left {len(problems)} foreign key violations')
                conn.execute('INSERT INTO schema_migrations (version, name, applied_at) VALUES (?, ?, ?)',
                             (version, name, datetime.now(timezone.utc).strftime('%Y-%m-%d %H:%M:%S')))
                conn.execute('COMMIT')
                applied.append(version)
            except Exception:
                conn.execute('ROLLBACK')
                raise
    finally:
        conn.execute('PRAGMA foreign_keys=ON')
        conn.close()
    return applied
