"""SQLite connection + schema. Mirrors the tracking-setup db.py conventions.

`init_db()` is idempotent (CREATE TABLE IF NOT EXISTS), safe to run on every
startup. The full schema for all phases lives here so we don't migrate piecemeal.
"""

import os
import sqlite3
from pathlib import Path
from typing import Iterator

DB_PATH = Path(os.environ.get("DB_PATH", "/data/app.db"))


def get_conn() -> sqlite3.Connection:
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH, detect_types=sqlite3.PARSE_DECLTYPES, timeout=15)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA journal_mode = WAL")
    return conn


def init_db() -> None:
    conn = get_conn()
    try:
        conn.executescript("""
        -- Accounts. role: 'admin' (rian), 'manager' (devs who can create forms/clients),
        -- 'client' (sees only granted forms). No CHECK on role — validated in app code.
        -- Login is "Sign in with BW" (Pattern B): bw_username is the linked BW identity;
        -- password_hash is legacy/unused. Accounts are matched on first BW login by
        -- bw_username, then email (to link a pre-provisioned row).
        CREATE TABLE IF NOT EXISTS clients (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE,
            bw_username TEXT UNIQUE,             -- linked BW account (NULL until first BW login)
            password_hash TEXT,                 -- legacy/unused under BW Auth
            role TEXT NOT NULL DEFAULT 'client',
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_login_at TIMESTAMP
        );

        -- One row per form. public_key is embedded in client HTML (NOT a secret).
        CREATE TABLE IF NOT EXISTS forms (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            public_key TEXT NOT NULL UNIQUE,
            client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
            name TEXT NOT NULL,
            -- SERVER-SIDE recipient list (comma-separated). NEVER taken from POST body.
            notify_recipients TEXT NOT NULL DEFAULT '',
            reply_to_field TEXT NOT NULL DEFAULT 'email',
            allowed_origins TEXT NOT NULL DEFAULT '',   -- comma list; empty = learn mode
            redirect_url TEXT NOT NULL DEFAULT '',
            from_address TEXT,                          -- NULL => EMAIL_FROM default
            autoresponse_enabled INTEGER NOT NULL DEFAULT 0,
            autoresponse_subject TEXT NOT NULL DEFAULT '',
            autoresponse_body TEXT NOT NULL DEFAULT '',
            honeypot_field TEXT NOT NULL DEFAULT '_hp',
            min_submit_seconds INTEGER NOT NULL DEFAULT 2,
            rate_limit_per_hour INTEGER NOT NULL DEFAULT 60,
            active INTEGER NOT NULL DEFAULT 1,
            created_by INTEGER REFERENCES clients(id) ON DELETE SET NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        -- Many-to-many: which users may view which forms' entries.
        -- (forms.client_id is kept as a legacy single-owner pointer.)
        CREATE TABLE IF NOT EXISTS form_access (
            form_id INTEGER NOT NULL REFERENCES forms(id) ON DELETE CASCADE,
            client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (form_id, client_id)
        );

        -- Per-user API tokens for the headless management CLI. Only the SHA-256
        -- hash is stored; the plaintext is shown once at creation.
        CREATE TABLE IF NOT EXISTS api_tokens (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
            token_hash TEXT NOT NULL UNIQUE,
            label TEXT NOT NULL DEFAULT '',
            revoked INTEGER NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_used_at TIMESTAMP
        );

        -- One row per submitted form (Gravity-Forms-style entry store).
        CREATE TABLE IF NOT EXISTS submissions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            form_id INTEGER NOT NULL REFERENCES forms(id) ON DELETE CASCADE,
            payload_json TEXT NOT NULL,
            submitter_ip TEXT,
            user_agent TEXT,
            origin TEXT,
            status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new','read','spam')),
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        -- Notification/retry queue. The asyncio worker drains pending/failed.
        CREATE TABLE IF NOT EXISTS notifications (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            submission_id INTEGER NOT NULL REFERENCES submissions(id) ON DELETE CASCADE,
            kind TEXT NOT NULL DEFAULT 'notify' CHECK (kind IN ('notify','autoresponse')),
            recipient TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','sent','failed')),
            attempts INTEGER NOT NULL DEFAULT 0,
            max_attempts INTEGER NOT NULL DEFAULT 6,
            next_attempt_at TIMESTAMP,
            last_error TEXT,
            provider_message_id TEXT,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        -- Per-submission debug timeline. Write generously — this is the debug log.
        CREATE TABLE IF NOT EXISTS events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            submission_id INTEGER REFERENCES submissions(id) ON DELETE CASCADE,
            form_id INTEGER REFERENCES forms(id) ON DELETE CASCADE,
            level TEXT NOT NULL DEFAULT 'info' CHECK (level IN ('info','warn','error')),
            message TEXT NOT NULL,
            data_json TEXT,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS password_reset (
            token TEXT PRIMARY KEY,
            client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
            purpose TEXT NOT NULL DEFAULT 'reset' CHECK (purpose IN ('reset','setup')),
            expires_at TIMESTAMP NOT NULL,
            used_at TIMESTAMP,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE INDEX IF NOT EXISTS idx_forms_client ON forms(client_id);
        CREATE INDEX IF NOT EXISTS idx_submissions_form ON submissions(form_id, created_at);
        CREATE INDEX IF NOT EXISTS idx_notifications_drain
            ON notifications(status, next_attempt_at);
        CREATE INDEX IF NOT EXISTS idx_notifications_sub ON notifications(submission_id);
        CREATE INDEX IF NOT EXISTS idx_events_sub ON events(submission_id, created_at);
        CREATE INDEX IF NOT EXISTS idx_form_access_client ON form_access(client_id);
        CREATE INDEX IF NOT EXISTS idx_api_tokens_hash ON api_tokens(token_hash);
        """)
        _migrate(conn)
        conn.commit()
    finally:
        conn.close()


def _migrate(conn: sqlite3.Connection) -> None:
    """Idempotent migrations for DBs created by an earlier schema."""
    # 1) clients.role used to have CHECK (role IN ('admin','client')). Rebuild without
    #    the CHECK so 'staff' is allowed. legacy_alter_table keeps child-table FKs
    #    pointing at 'clients' (not the renamed temp).
    row = conn.execute(
        "SELECT sql FROM sqlite_master WHERE type='table' AND name='clients'").fetchone()
    if row and "CHECK" in (row["sql"] or ""):
        conn.execute("PRAGMA foreign_keys=OFF")
        conn.execute("PRAGMA legacy_alter_table=ON")
        conn.executescript("""
            ALTER TABLE clients RENAME TO _clients_old;
            CREATE TABLE clients (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE,
                password_hash TEXT,
                role TEXT NOT NULL DEFAULT 'client',
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                last_login_at TIMESTAMP
            );
            INSERT INTO clients (id, name, email, password_hash, role, created_at, last_login_at)
                SELECT id, name, email, password_hash, role, created_at, last_login_at
                FROM _clients_old;
            DROP TABLE _clients_old;
        """)
        conn.execute("PRAGMA legacy_alter_table=OFF")
        conn.execute("PRAGMA foreign_keys=ON")

    # 2) forms.created_by added later.
    cols = {r["name"] for r in conn.execute("PRAGMA table_info(forms)")}
    if "created_by" not in cols:
        conn.execute("ALTER TABLE forms ADD COLUMN created_by INTEGER REFERENCES clients(id)")
    conn.execute("CREATE INDEX IF NOT EXISTS idx_forms_creator ON forms(created_by)")

    # 2b) clients.bw_username added for BW Auth (Pattern B).
    ccols = {r["name"] for r in conn.execute("PRAGMA table_info(clients)")}
    if "bw_username" not in ccols:
        conn.execute("ALTER TABLE clients ADD COLUMN bw_username TEXT")
    conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_clients_bw ON clients(bw_username) "
                 "WHERE bw_username IS NOT NULL")
    # Legacy 'staff' role → 'manager' (rename; no-op if none exist).
    conn.execute("UPDATE clients SET role='manager' WHERE role='staff'")

    # 3) Backfill form_access from the legacy single-owner forms.client_id.
    conn.execute(
        "INSERT OR IGNORE INTO form_access (form_id, client_id) "
        "SELECT id, client_id FROM forms WHERE client_id IS NOT NULL"
    )


def query(sql: str, params: tuple = ()) -> list[sqlite3.Row]:
    conn = get_conn()
    try:
        return list(conn.execute(sql, params))
    finally:
        conn.close()


def query_one(sql: str, params: tuple = ()) -> sqlite3.Row | None:
    conn = get_conn()
    try:
        return conn.execute(sql, params).fetchone()
    finally:
        conn.close()


def execute(sql: str, params: tuple = ()) -> int:
    """Returns lastrowid for inserts; rowcount for updates/deletes."""
    conn = get_conn()
    try:
        cur = conn.execute(sql, params)
        conn.commit()
        return cur.lastrowid if cur.lastrowid else cur.rowcount
    finally:
        conn.close()


def execute_many(statements: Iterator[tuple[str, tuple]]) -> None:
    conn = get_conn()
    try:
        for sql, params in statements:
            conn.execute(sql, params)
        conn.commit()
    finally:
        conn.close()