"""SQLite connection + schema migrations.

Single source of truth for the schema. `init_db()` is idempotent — every migration
is wrapped in a check that's safe to run repeatedly.
"""

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)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA journal_mode = WAL")
    return conn


def _table_columns(conn: sqlite3.Connection, table: str) -> set[str]:
    return {row["name"] for row in conn.execute(f"PRAGMA table_info({table})")}


def _table_exists(conn: sqlite3.Connection, table: str) -> bool:
    cur = conn.execute(
        "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (table,)
    )
    return cur.fetchone() is not None


def init_db() -> None:
    conn = get_conn()
    try:
        conn.executescript("""
        CREATE TABLE IF NOT EXISTS organization (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            kind TEXT NOT NULL CHECK (kind IN ('master','single_client')),
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS google_account_ctx (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            organization_id INTEGER NOT NULL REFERENCES organization(id) ON DELETE CASCADE,
            google_email TEXT NOT NULL,
            oauth_tokens_enc TEXT,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS client (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            organization_id INTEGER NOT NULL REFERENCES organization(id) ON DELETE RESTRICT,
            name TEXT NOT NULL,
            primary_url TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'active',
            google_ctx_id INTEGER REFERENCES google_account_ctx(id),
            permission_confirmed_by INTEGER,
            permission_confirmed_at TIMESTAMP,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (organization_id, name)
        );

        CREATE TABLE IF NOT EXISTS user (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL UNIQUE,
            password_hash TEXT,
            name TEXT,
            role TEXT NOT NULL CHECK (role IN ('admin','internal','client')),
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_login_at TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS user_organization_access (
            user_id INTEGER NOT NULL REFERENCES user(id) ON DELETE CASCADE,
            organization_id INTEGER NOT NULL REFERENCES organization(id) ON DELETE CASCADE,
            PRIMARY KEY (user_id, organization_id)
        );

        CREATE TABLE IF NOT EXISTS user_client_access (
            user_id INTEGER NOT NULL REFERENCES user(id) ON DELETE CASCADE,
            client_id INTEGER NOT NULL REFERENCES client(id) ON DELETE CASCADE,
            role TEXT NOT NULL CHECK (role IN ('owner','editor','reviewer')),
            PRIMARY KEY (user_id, client_id)
        );

        CREATE TABLE IF NOT EXISTS crawl_run (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            client_id INTEGER NOT NULL REFERENCES client(id) ON DELETE CASCADE,
            started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            finished_at TIMESTAMP,
            status TEXT NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending','running','done','failed','cost_exceeded')),
            pages_crawled_json TEXT,
            screenshots_dir TEXT,
            ai_spend_cents INTEGER NOT NULL DEFAULT 0,
            claude_model TEXT,
            error TEXT
        );

        CREATE TABLE IF NOT EXISTS report (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            client_id INTEGER NOT NULL REFERENCES client(id) ON DELETE CASCADE,
            crawl_run_id INTEGER REFERENCES crawl_run(id),
            version INTEGER NOT NULL DEFAULT 1,
            status TEXT NOT NULL DEFAULT 'draft'
                CHECK (status IN ('draft','sent','in_review','approved','implementing','done')),
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            sent_to_client_at TIMESTAMP,
            approved_at TIMESTAMP,
            title TEXT,
            summary_md TEXT
        );

        CREATE TABLE IF NOT EXISTS action (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            report_id INTEGER NOT NULL REFERENCES report(id) ON DELETE CASCADE,
            kind TEXT NOT NULL,
            title TEXT NOT NULL,
            proposed_spec_json TEXT NOT NULL,
            notes_md TEXT,
            status TEXT NOT NULL DEFAULT 'proposed'
                CHECK (status IN ('proposed','approved','rejected','edited')),
            sort_order INTEGER NOT NULL DEFAULT 0,
            reviewed_by_user_id INTEGER REFERENCES user(id),
            reviewed_at TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS action_comment (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            action_id INTEGER NOT NULL REFERENCES action(id) ON DELETE CASCADE,
            user_id INTEGER NOT NULL REFERENCES user(id),
            body TEXT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS magic_link (
            token TEXT PRIMARY KEY,
            user_id INTEGER NOT NULL REFERENCES user(id) ON DELETE CASCADE,
            client_id_scope INTEGER REFERENCES client(id),
            expires_at TIMESTAMP NOT NULL,
            used_at TIMESTAMP,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS audit_event (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            actor_user_id INTEGER REFERENCES user(id),
            client_id INTEGER REFERENCES client(id),
            kind TEXT NOT NULL,
            payload_json TEXT,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE INDEX IF NOT EXISTS idx_crawl_run_client ON crawl_run(client_id);
        CREATE INDEX IF NOT EXISTS idx_report_client ON report(client_id);
        CREATE INDEX IF NOT EXISTS idx_action_report ON action(report_id);
        CREATE INDEX IF NOT EXISTS idx_action_comment_action ON action_comment(action_id);
        CREATE INDEX IF NOT EXISTS idx_audit_event_client ON audit_event(client_id);
        CREATE INDEX IF NOT EXISTS idx_user_role ON user(role);
        """)
        conn.commit()
    finally:
        conn.close()


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:
        cur = conn.execute(sql, params)
        return cur.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:
    """Run multiple writes in a single transaction."""
    conn = get_conn()
    try:
        for sql, params in statements:
            conn.execute(sql, params)
        conn.commit()
    finally:
        conn.close()