from __future__ import annotations

import json
import sqlite3
from contextlib import contextmanager
from pathlib import Path
from typing import Iterator


SCHEMA = """
PRAGMA foreign_keys=ON;
CREATE TABLE IF NOT EXISTS schema_migrations(
 version INTEGER PRIMARY KEY,
 applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS users(
 id TEXT PRIMARY KEY,
 username TEXT NOT NULL UNIQUE COLLATE NOCASE,
 password_hash TEXT NOT NULL,
 role TEXT NOT NULL CHECK(role IN ('owner')),
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS conversations(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 title TEXT NOT NULL,
 model_key TEXT NOT NULL,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 version INTEGER NOT NULL DEFAULT 1,
 deleted_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_conversations_user_updated
 ON conversations(user_id, updated_at DESC);
CREATE TABLE IF NOT EXISTS messages(
 id TEXT PRIMARY KEY,
 conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
 role TEXT NOT NULL CHECK(role IN ('system','user','assistant')),
 content TEXT NOT NULL DEFAULT '',
 reasoning TEXT NOT NULL DEFAULT '',
 status TEXT NOT NULL DEFAULT 'complete',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_messages_conversation_created
 ON messages(conversation_id, created_at);
CREATE TABLE IF NOT EXISTS model_preferences(
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 model_key TEXT NOT NULL,
 preferences_json TEXT NOT NULL,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 version INTEGER NOT NULL DEFAULT 1,
 PRIMARY KEY(user_id, model_key)
);
CREATE TABLE IF NOT EXISTS runs(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
 assistant_message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
 model_key TEXT NOT NULL,
 status TEXT NOT NULL,
 error_code TEXT,
 error_message TEXT,
 cancel_requested INTEGER NOT NULL DEFAULT 0,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS run_events(
 run_id TEXT NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
 seq INTEGER NOT NULL,
 event_type TEXT NOT NULL,
 data_json TEXT NOT NULL,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(run_id, seq)
);

CREATE TABLE IF NOT EXISTS files(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 original_name TEXT NOT NULL,
 stored_name TEXT NOT NULL,
 mime_type TEXT NOT NULL,
 size_bytes INTEGER NOT NULL CHECK(size_bytes >= 0),
 sha256 TEXT NOT NULL,
 kind TEXT NOT NULL CHECK(kind IN ('text','image','zip','binary')),
 status TEXT NOT NULL DEFAULT 'ready' CHECK(status IN ('ready','blocked','deleted')),
 text_content TEXT NOT NULL DEFAULT '',
 zip_inventory_json TEXT NOT NULL DEFAULT '[]',
 warnings_json TEXT NOT NULL DEFAULT '[]',
 private_metadata_cipher BLOB,
 encryption_version INTEGER NOT NULL DEFAULT 0,
 key_version INTEGER NOT NULL DEFAULT 0,
 stored_size_bytes INTEGER NOT NULL DEFAULT 0,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_files_user_created
 ON files(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_files_sha256
 ON files(user_id, sha256);

CREATE TABLE IF NOT EXISTS message_attachments(
 message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
 file_id TEXT NOT NULL REFERENCES files(id) ON DELETE RESTRICT,
 position INTEGER NOT NULL DEFAULT 0,
 PRIMARY KEY(message_id, file_id)
);
CREATE INDEX IF NOT EXISTS idx_message_attachments_file
 ON message_attachments(file_id);

CREATE TABLE IF NOT EXISTS jobs(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 kind TEXT NOT NULL,
 title TEXT NOT NULL,
 status TEXT NOT NULL CHECK(status IN ('queued','running','awaiting_approval','blocked','partial','failed','successful','cancelled')),
 progress INTEGER NOT NULL DEFAULT 0 CHECK(progress BETWEEN 0 AND 100),
 input_json TEXT NOT NULL DEFAULT '{}',
 result_json TEXT NOT NULL DEFAULT '{}',
 error_code TEXT,
 error_message TEXT,
 cancel_requested INTEGER NOT NULL DEFAULT 0,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_jobs_user_updated
 ON jobs(user_id, updated_at DESC);
CREATE TABLE IF NOT EXISTS job_events(
 job_id TEXT NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
 seq INTEGER NOT NULL,
 event_type TEXT NOT NULL,
 data_json TEXT NOT NULL,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(job_id, seq)
);

CREATE TABLE IF NOT EXISTS tci_sessions(
 user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
 session_id TEXT NOT NULL,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS memories(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 scope TEXT NOT NULL CHECK(scope IN ('global','workspace','temporary')),
 title TEXT NOT NULL,
 content TEXT NOT NULL,
 source TEXT NOT NULL DEFAULT 'manual',
 enabled INTEGER NOT NULL DEFAULT 1,
 expires_at TEXT,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_memories_user_updated
 ON memories(user_id, updated_at DESC);

CREATE TABLE IF NOT EXISTS skills(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 name TEXT NOT NULL,
 description TEXT NOT NULL DEFAULT '',
 version INTEGER NOT NULL DEFAULT 1,
 instructions TEXT NOT NULL,
 input_schema_json TEXT NOT NULL DEFAULT '{}',
 output_format TEXT NOT NULL DEFAULT 'text',
 permitted_tools_json TEXT NOT NULL DEFAULT '[]',
 risk_boundaries TEXT NOT NULL DEFAULT '',
 status TEXT NOT NULL DEFAULT 'draft' CHECK(status IN ('draft','published','archived')),
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE(user_id, name, version)
);
CREATE INDEX IF NOT EXISTS idx_skills_user_updated
 ON skills(user_id, updated_at DESC);

CREATE TABLE IF NOT EXISTS audit_log(
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
 action TEXT NOT NULL,
 target_type TEXT,
 target_id TEXT,
 detail_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at DESC);

CREATE TABLE IF NOT EXISTS user_settings(
 user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
 settings_json TEXT NOT NULL DEFAULT '{}',
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 version INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS sync_operations(
 id TEXT NOT NULL,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 operation_type TEXT NOT NULL,
 status TEXT NOT NULL CHECK(status IN ('applied','conflict','failed')),
 request_json TEXT NOT NULL DEFAULT '{}',
 result_json TEXT NOT NULL DEFAULT '{}',
 error_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(user_id,id)
);
CREATE INDEX IF NOT EXISTS idx_sync_operations_user_updated
 ON sync_operations(user_id,updated_at DESC);

CREATE TABLE IF NOT EXISTS sync_changes(
 seq INTEGER PRIMARY KEY AUTOINCREMENT,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 entity_type TEXT NOT NULL,
 entity_id TEXT NOT NULL,
 action TEXT NOT NULL,
 version INTEGER NOT NULL DEFAULT 1,
 payload_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_sync_changes_user_seq
 ON sync_changes(user_id,seq);


CREATE TABLE IF NOT EXISTS workspaces(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 name TEXT NOT NULL,
 kind TEXT NOT NULL CHECK(kind IN ('tools','website','swarm')),
 root_path TEXT NOT NULL,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_workspaces_user_updated ON workspaces(user_id,updated_at DESC);

CREATE TABLE IF NOT EXISTS tool_operations(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
 tool_id TEXT NOT NULL,
 tool_version INTEGER NOT NULL,
 risk_class TEXT NOT NULL,
 status TEXT NOT NULL CHECK(status IN ('awaiting_approval','queued','running','successful','failed','cancelled','rejected')),
 arguments_json TEXT NOT NULL DEFAULT '{}',
 result_json TEXT NOT NULL DEFAULT '{}',
 error_json TEXT NOT NULL DEFAULT '{}',
 approval_required INTEGER NOT NULL DEFAULT 0,
 approved_at TEXT,
 cancel_requested INTEGER NOT NULL DEFAULT 0,
 idempotency_key TEXT,
 actor_type TEXT NOT NULL DEFAULT 'user',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE(user_id,idempotency_key)
);
CREATE INDEX IF NOT EXISTS idx_tool_operations_user_updated ON tool_operations(user_id,updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_tool_operations_workspace ON tool_operations(workspace_id,updated_at DESC);
CREATE TABLE IF NOT EXISTS tool_events(
 operation_id TEXT NOT NULL REFERENCES tool_operations(id) ON DELETE CASCADE,
 seq INTEGER NOT NULL,
 event_type TEXT NOT NULL,
 data_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(operation_id,seq)
);

CREATE TABLE IF NOT EXISTS website_projects(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
 name TEXT NOT NULL,
 project_type TEXT NOT NULL CHECK(project_type IN ('static','react')),
 model_key TEXT NOT NULL CHECK(model_key IN ('k2.6','k2.7-code')),
 status TEXT NOT NULL DEFAULT 'ready' CHECK(status IN ('draft','generating','testing','ready','failed','archived')),
 requirements_json TEXT NOT NULL DEFAULT '{}',
 current_version INTEGER NOT NULL DEFAULT 1,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_website_projects_user_updated ON website_projects(user_id,updated_at DESC);
CREATE TABLE IF NOT EXISTS website_files(
 project_id TEXT NOT NULL REFERENCES website_projects(id) ON DELETE CASCADE,
 path TEXT NOT NULL,
 content TEXT NOT NULL DEFAULT '',
 mime_type TEXT NOT NULL DEFAULT 'text/plain',
 sha256 TEXT NOT NULL,
 version INTEGER NOT NULL DEFAULT 1,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(project_id,path)
);
CREATE TABLE IF NOT EXISTS website_versions(
 project_id TEXT NOT NULL REFERENCES website_projects(id) ON DELETE CASCADE,
 version INTEGER NOT NULL,
 parent_version INTEGER,
 summary TEXT NOT NULL,
 manifest_json TEXT NOT NULL DEFAULT '[]',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(project_id,version)
);
CREATE TABLE IF NOT EXISTS website_version_files(
 project_id TEXT NOT NULL,
 version INTEGER NOT NULL,
 path TEXT NOT NULL,
 content TEXT NOT NULL DEFAULT '',
 mime_type TEXT NOT NULL DEFAULT 'text/plain',
 sha256 TEXT NOT NULL,
 PRIMARY KEY(project_id,version,path),
 FOREIGN KEY(project_id,version) REFERENCES website_versions(project_id,version) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS website_builds(
 id TEXT PRIMARY KEY,
 project_id TEXT NOT NULL REFERENCES website_projects(id) ON DELETE CASCADE,
 status TEXT NOT NULL CHECK(status IN ('queued','running','successful','failed','cancelled')),
 model_key TEXT NOT NULL,
 instruction TEXT NOT NULL DEFAULT '',
 result_json TEXT NOT NULL DEFAULT '{}',
 error_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_website_builds_project ON website_builds(project_id,updated_at DESC);

CREATE TABLE IF NOT EXISTS swarms(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
 objective TEXT NOT NULL,
 status TEXT NOT NULL CHECK(status IN ('draft','queued','running','awaiting_approval','successful','failed','cancelled','partial')),
 coordinator_model_key TEXT NOT NULL DEFAULT 'k2.6',
 coder_model_key TEXT NOT NULL DEFAULT 'k2.7-code',
 max_tool_calls INTEGER NOT NULL DEFAULT 40,
 max_seconds INTEGER NOT NULL DEFAULT 900,
 max_iterations INTEGER NOT NULL DEFAULT 3,
 used_tool_calls INTEGER NOT NULL DEFAULT 0,
 cancel_requested INTEGER NOT NULL DEFAULT 0,
 result_json TEXT NOT NULL DEFAULT '{}',
 error_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_swarms_user_updated ON swarms(user_id,updated_at DESC);
CREATE TABLE IF NOT EXISTS swarm_tasks(
 id TEXT PRIMARY KEY,
 swarm_id TEXT NOT NULL REFERENCES swarms(id) ON DELETE CASCADE,
 role TEXT NOT NULL,
 title TEXT NOT NULL,
 sequence INTEGER NOT NULL,
 depends_on_json TEXT NOT NULL DEFAULT '[]',
 model_key TEXT NOT NULL,
 status TEXT NOT NULL CHECK(status IN ('blocked','queued','awaiting_approval','running','successful','failed','cancelled','skipped')),
 input_json TEXT NOT NULL DEFAULT '{}',
 result_json TEXT NOT NULL DEFAULT '{}',
 error_json TEXT NOT NULL DEFAULT '{}',
 lease_paths_json TEXT NOT NULL DEFAULT '[]',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_swarm_tasks_swarm_sequence ON swarm_tasks(swarm_id,sequence);
CREATE TABLE IF NOT EXISTS swarm_events(
 swarm_id TEXT NOT NULL REFERENCES swarms(id) ON DELETE CASCADE,
 seq INTEGER NOT NULL,
 event_type TEXT NOT NULL,
 data_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(swarm_id,seq)
);


CREATE TABLE IF NOT EXISTS website_test_results(
 id TEXT PRIMARY KEY,
 project_id TEXT NOT NULL REFERENCES website_projects(id) ON DELETE CASCADE,
 version INTEGER NOT NULL,
 status TEXT NOT NULL CHECK(status IN ('successful','failed')),
 result_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_website_tests_project ON website_test_results(project_id,created_at DESC);

CREATE TABLE IF NOT EXISTS workspace_file_leases(
 workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
 path TEXT NOT NULL,
 swarm_id TEXT REFERENCES swarms(id) ON DELETE CASCADE,
 task_id TEXT REFERENCES swarm_tasks(id) ON DELETE CASCADE,
 lease_token TEXT NOT NULL,
 expires_at TEXT NOT NULL,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(workspace_id,path)
);
CREATE INDEX IF NOT EXISTS idx_workspace_file_leases_swarm ON workspace_file_leases(swarm_id);

CREATE TABLE IF NOT EXISTS swarm_checkpoints(
 swarm_id TEXT NOT NULL REFERENCES swarms(id) ON DELETE CASCADE,
 sequence INTEGER NOT NULL,
 state_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(swarm_id,sequence)
);


CREATE TABLE IF NOT EXISTS tool_policies(
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
 tool_id TEXT NOT NULL,
 decision TEXT NOT NULL CHECK(decision IN ('ask','allow','deny')),
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(user_id,workspace_id,tool_id)
);
CREATE TABLE IF NOT EXISTS tool_agent_sessions(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
 model_key TEXT NOT NULL CHECK(model_key IN ('k2.6','k2.7-code')),
 approval_mode TEXT NOT NULL CHECK(approval_mode IN ('manual','balanced','autonomous')),
 status TEXT NOT NULL CHECK(status IN ('queued','running','awaiting_approval','successful','failed','cancelled','partial')),
 prompt TEXT NOT NULL,
 messages_json TEXT NOT NULL DEFAULT '[]',
 max_steps INTEGER NOT NULL DEFAULT 8,
 used_steps INTEGER NOT NULL DEFAULT 0,
 max_tool_calls INTEGER NOT NULL DEFAULT 12,
 used_tool_calls INTEGER NOT NULL DEFAULT 0,
 current_operation_id TEXT REFERENCES tool_operations(id) ON DELETE SET NULL,
 result_json TEXT NOT NULL DEFAULT '{}',
 error_json TEXT NOT NULL DEFAULT '{}',
 cancel_requested INTEGER NOT NULL DEFAULT 0,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_tool_agent_sessions_user_updated ON tool_agent_sessions(user_id,updated_at DESC);
CREATE TABLE IF NOT EXISTS tool_agent_steps(
 session_id TEXT NOT NULL REFERENCES tool_agent_sessions(id) ON DELETE CASCADE,
 sequence INTEGER NOT NULL,
 step_type TEXT NOT NULL,
 data_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(session_id,sequence)
);

CREATE TABLE IF NOT EXISTS website_diffs(
 id TEXT PRIMARY KEY,
 project_id TEXT NOT NULL REFERENCES website_projects(id) ON DELETE CASCADE,
 version INTEGER NOT NULL,
 path TEXT NOT NULL,
 change_type TEXT NOT NULL CHECK(change_type IN ('added','modified','deleted')),
 before_sha256 TEXT,
 after_sha256 TEXT,
 unified_diff TEXT NOT NULL DEFAULT '',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_website_diffs_project_version ON website_diffs(project_id,version,path);

CREATE TABLE IF NOT EXISTS swarm_approvals(
 id TEXT PRIMARY KEY,
 swarm_id TEXT NOT NULL REFERENCES swarms(id) ON DELETE CASCADE,
 task_id TEXT NOT NULL REFERENCES swarm_tasks(id) ON DELETE CASCADE,
 status TEXT NOT NULL CHECK(status IN ('pending','approved','rejected')),
 rationale TEXT NOT NULL DEFAULT '',
 decided_at TEXT,
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE(swarm_id,task_id)
);

CREATE TABLE IF NOT EXISTS audio_sessions(
 id TEXT PRIMARY KEY,
 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 conversation_id TEXT REFERENCES conversations(id) ON DELETE SET NULL,
 model_key TEXT NOT NULL CHECK(model_key IN ('k2.6','k2.7-code')),
 mode TEXT NOT NULL CHECK(mode IN ('dictation','conversation')),
 state TEXT NOT NULL,
 voice TEXT NOT NULL,
 current_turn_id TEXT,
 stats_json TEXT NOT NULL DEFAULT '{}',
 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_audio_sessions_user_updated ON audio_sessions(user_id,updated_at DESC);
"""


def connect(path: Path | str) -> sqlite3.Connection:
    path = Path(path)
    path.parent.mkdir(parents=True, exist_ok=True)
    db = sqlite3.connect(str(path), timeout=10, isolation_level=None, check_same_thread=False)
    db.row_factory = sqlite3.Row
    db.execute("PRAGMA foreign_keys=ON")
    db.execute("PRAGMA journal_mode=WAL")
    db.execute("PRAGMA synchronous=NORMAL")
    db.execute("PRAGMA busy_timeout=10000")
    return db


def _ensure_column(db: sqlite3.Connection, table: str, name: str, declaration: str) -> None:
    columns = {row[1] for row in db.execute(f"PRAGMA table_info({table})").fetchall()}
    if name not in columns:
        db.execute(f"ALTER TABLE {table} ADD COLUMN {name} {declaration}")


def initialise(path: Path | str) -> None:
    db = connect(path)
    try:
        db.executescript(SCHEMA)
        _ensure_column(db, "conversations", "version", "INTEGER NOT NULL DEFAULT 1")
        _ensure_column(db, "model_preferences", "version", "INTEGER NOT NULL DEFAULT 1")
        _ensure_column(db, "files", "private_metadata_cipher", "BLOB")
        _ensure_column(db, "files", "encryption_version", "INTEGER NOT NULL DEFAULT 0")
        _ensure_column(db, "files", "key_version", "INTEGER NOT NULL DEFAULT 0")
        _ensure_column(db, "files", "stored_size_bytes", "INTEGER NOT NULL DEFAULT 0")
        _ensure_column(db, "user_settings", "version", "INTEGER NOT NULL DEFAULT 1")
        _ensure_column(db, "tool_operations", "parent_session_id", "TEXT")
        _ensure_column(db, "website_projects", "wizard_stage", "INTEGER NOT NULL DEFAULT 4")
        _ensure_column(db, "website_projects", "theme_json", "TEXT NOT NULL DEFAULT '{}'")
        _ensure_column(db, "swarms", "approval_mode", "TEXT NOT NULL DEFAULT 'balanced'")
        _ensure_column(db, "swarms", "max_parallelism", "INTEGER NOT NULL DEFAULT 2")
        _ensure_column(db, "swarms", "max_tokens", "INTEGER NOT NULL DEFAULT 32000")
        _ensure_column(db, "swarms", "used_tokens", "INTEGER NOT NULL DEFAULT 0")
        _ensure_column(db, "swarm_tasks", "iteration", "INTEGER NOT NULL DEFAULT 1")
        for version in range(1, 15):
            db.execute("INSERT OR IGNORE INTO schema_migrations(version) VALUES(?)", (version,))
    finally:
        db.close()


@contextmanager
def transaction(db: sqlite3.Connection) -> Iterator[sqlite3.Connection]:
    db.execute("BEGIN IMMEDIATE")
    try:
        yield db
        db.execute("COMMIT")
    except Exception:
        db.execute("ROLLBACK")
        raise


def json_text(value: object) -> str:
    return json.dumps(value, separators=(",", ":"), ensure_ascii=False)