"""SQLite database initialization and connection management.

Replaces PostgreSQL (async SQLAlchemy) + Memgraph (Neo4j driver).
Single SQLite file per project at ~/.soagila/projects/{hash}/soagila.db

### Multi-project routing (v0.7.0)

The daemon serves multiple VS Code / Desktop / Browser clients at once,
each potentially in a different workspace. To prevent one project's
memories from leaking into another's, connections are pooled **per
project hash**, and per-request code can pin a specific project via an
async-local context variable (``current_project_hash``).

Lookup order inside ``get_db()``:
    1. Explicit argument ``project_hash=...``
    2. ContextVar ``current_project_hash`` set by the HTTP router middleware
    3. Legacy fallback: ``get_project_db_path()`` (single-project mode)

The legacy ``_db`` singleton is still populated for callers that never
set a context (background scheduler, CLI tools) so existing code keeps
working without modification.
"""

import contextvars
import json
import logging
from datetime import datetime, timezone
from pathlib import Path

import aiosqlite

from soagila.config import get_project_db_path, get_project_dir

logger = logging.getLogger(__name__)

# Legacy single-process connection (used by background scheduler, CLI)
_db: aiosqlite.Connection | None = None
_global_db: aiosqlite.Connection | None = None

# Per-project-hash connection pool.
# Key: 16-char project_hash. Value: open aiosqlite connection.
_project_pool: dict[str, aiosqlite.Connection] = {}

# Async-local variable set by HTTP middleware on every request.
# When unset (CLI mode), falls back to the legacy single-project default.
current_project_hash: contextvars.ContextVar[str | None] = contextvars.ContextVar(
    "current_project_hash",
    default=None,
)


def _project_db_path(project_hash: str) -> str:
    """Return the absolute path to a specific project's DB file."""
    from soagila.config import get_soagila_home
    project_dir = get_soagila_home() / "projects" / project_hash
    project_dir.mkdir(parents=True, exist_ok=True)
    return str(project_dir / "soagila.db")


async def _open_project_connection(db_path: str) -> aiosqlite.Connection:
    """Open a new WAL-mode SQLite connection with sensible defaults."""
    conn = await aiosqlite.connect(db_path, timeout=30.0)
    conn.row_factory = aiosqlite.Row
    await conn.execute("PRAGMA journal_mode=WAL")
    await conn.execute("PRAGMA foreign_keys=ON")
    await conn.execute("PRAGMA busy_timeout=30000")
    await conn.execute("PRAGMA synchronous=NORMAL")
    await conn.execute("PRAGMA cache_size=10000")
    await conn.execute("PRAGMA temp_store=MEMORY")
    return conn


async def get_db(project_hash: str | None = None) -> aiosqlite.Connection:
    """Resolve and return the SQLite connection for the active project.

    Resolution order:
        1. ``project_hash`` argument (explicit override)
        2. ``current_project_hash`` ContextVar (set per HTTP request)
        3. Legacy default: ``get_project_db_path()`` (single-project mode)

    Connections are pooled per hash so concurrent requests to the same
    project share one aiosqlite connection and the daemon-wide write
    lock serialises writes against it.
    """
    global _db

    resolved_hash = project_hash or current_project_hash.get()

    if resolved_hash:
        existing = _project_pool.get(resolved_hash)
        if existing is not None:
            return existing
        db_path = _project_db_path(resolved_hash)
        logger.info(f"Opening pooled SQLite DB: {db_path}")
        conn = await _open_project_connection(db_path)
        _project_pool[resolved_hash] = conn
        # If legacy singleton is empty, seed it so background tasks still
        # have a reasonable default. First-seen project wins.
        if _db is None:
            _db = conn
        return conn

    # Legacy fallback: no project pinned, use the process-level default.
    if _db is None:
        db_path = get_project_db_path()
        logger.info(f"Opening SQLite database (legacy default): {db_path}")
        _db = await _open_project_connection(db_path)
    return _db


async def close_db():
    """Close the legacy SQLite connection and drain the project pool."""
    global _db
    if _db is not None:
        await _db.close()
        _db = None
        logger.info("SQLite connection closed")
    for hash_key, conn in list(_project_pool.items()):
        try:
            await conn.close()
        except Exception as exc:
            logger.debug(f"Pool close failed for {hash_key}: {exc}")
    _project_pool.clear()


async def get_global_db() -> aiosqlite.Connection:
    """Get or create the global semantic memory pool connection."""
    global _global_db
    if _global_db is None:
        from soagila.config import get_soagila_home
        db_path = str(get_soagila_home() / "global_semantic.db")
        logger.info(f"Opening global semantic DB: {db_path}")
        _global_db = await aiosqlite.connect(db_path, timeout=5.0)
        _global_db.row_factory = aiosqlite.Row
        await _global_db.execute("PRAGMA journal_mode=WAL")
        await _global_db.execute("PRAGMA busy_timeout=5000")
    return _global_db


async def close_global_db():
    """Close the global semantic DB connection."""
    global _global_db
    if _global_db is not None:
        await _global_db.close()
        _global_db = None


async def init_global_database():
    """Initialize the global semantic memory pool schema."""
    db = await get_global_db()
    await db.executescript(GLOBAL_SCHEMA_SQL)
    await db.commit()
    logger.info("Global semantic DB initialized")


async def init_database(project_hash: str | None = None):
    """Create all tables for a project DB if they don't exist.

    Safe to call multiple times (idempotent). When called with a
    specific ``project_hash`` it creates the schema for that project
    specifically, which is how the HTTP router handles first-time
    access to a new workspace.
    """
    db = await get_db(project_hash=project_hash)

    # Write project metadata
    _write_project_metadata(project_hash)

    await db.executescript(SCHEMA_SQL)
    await db.commit()

    # Run migrations (add columns that may not exist in older DBs)
    await _run_migrations(db)

    logger.info("SQLite schema initialized")


async def _run_migrations(db: aiosqlite.Connection):
    """Add columns introduced in later versions (safe to re-run)."""
    migrations = [
        # v0.4.0 — Cross-Agent Protocol receipt chain
        ("decision_receipts", "source_agent_id", "TEXT"),
        ("decision_receipts", "target_agent_id", "TEXT"),
        ("decision_receipts", "cross_agent_ref", "TEXT"),
        # v0.6.0 — Memory Inheritance
        ("semantic_memories", "source_project_hash", "TEXT"),
        ("semantic_memories", "inheritance_depth", "INTEGER DEFAULT 0"),
        ("semantic_memories", "inherited_at", "TEXT"),
    ]
    for table, column, col_type in migrations:
        try:
            await db.execute(f"ALTER TABLE {table} ADD COLUMN {column} {col_type}")
            await db.commit()
            logger.debug(f"Migration: added {table}.{column}")
        except Exception:
            pass  # Column already exists


def _write_project_metadata(project_hash: str | None = None):
    """Write project.json for human-readable project listing.

    When ``project_hash`` is provided we write metadata into that hash's
    directory (daemon multi-project mode). Otherwise we fall back to
    the legacy single-project resolution.
    """
    import os
    if project_hash:
        from soagila.config import get_soagila_home
        project_dir = get_soagila_home() / "projects" / project_hash
        project_dir.mkdir(parents=True, exist_ok=True)
    else:
        project_dir = get_project_dir()
    meta_path = project_dir / "project.json"
    if not meta_path.exists():
        cwd = os.environ.get("SOAGILA_PROJECT_CWD", os.getcwd())
        meta = {
            "cwd": cwd,
            "name": Path(cwd).name,
            "created_at": datetime.now(timezone.utc).isoformat(),
        }
        meta_path.write_text(json.dumps(meta, indent=2))


# ─── SQLite Schema ───────────────────────────────────────────────────────────

SCHEMA_SQL = """
-- ═══════════════════════════════════════════════════════════════════════
-- SOAGILA for Code — SQLite Schema
-- Ported from PostgreSQL (backend/db/postgres_models.py)
-- ═══════════════════════════════════════════════════════════════════════

-- Episodic Memories (Tier 2: event-based, subject to Ebbinghaus decay)
CREATE TABLE IF NOT EXISTS episodic_memories (
    id                  TEXT PRIMARY KEY,
    user_id             TEXT NOT NULL DEFAULT 'local',
    org_id              TEXT NOT NULL DEFAULT 'local',
    encrypted_content   BLOB NOT NULL,
    encryption_key_ref  TEXT NOT NULL,
    embedding           TEXT,           -- JSON array of floats
    content_hash        TEXT NOT NULL,
    pii_tags            TEXT DEFAULT '[]',   -- JSON array
    classification      TEXT,           -- personal/financial/medical/general
    topic_tags          TEXT DEFAULT '[]',   -- JSON array
    confidence_score    REAL DEFAULT 1.0,
    relevance_score     REAL DEFAULT 1.0,
    access_count        INTEGER DEFAULT 0,
    last_accessed       TEXT,           -- ISO 8601
    decay_rate          REAL DEFAULT 0.1,
    stability           REAL DEFAULT 1.0,
    is_canary           INTEGER DEFAULT 0,  -- boolean
    is_decayed          INTEGER DEFAULT 0,  -- boolean
    source_type         TEXT DEFAULT 'chat', -- chat/api/system/promotion
    session_id          TEXT,
    created_at          TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    updated_at          TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);
CREATE INDEX IF NOT EXISTS idx_episodic_user ON episodic_memories(user_id);
CREATE INDEX IF NOT EXISTS idx_episodic_relevance ON episodic_memories(relevance_score)
    WHERE is_decayed = 0;
CREATE INDEX IF NOT EXISTS idx_episodic_canary ON episodic_memories(is_canary)
    WHERE is_canary = 1;
CREATE INDEX IF NOT EXISTS idx_episodic_session ON episodic_memories(session_id);
CREATE INDEX IF NOT EXISTS idx_episodic_created ON episodic_memories(created_at);


-- Semantic Memories (Tier 3: generalized patterns, PII-stripped)
CREATE TABLE IF NOT EXISTS semantic_memories (
    id                      TEXT PRIMARY KEY,
    org_id                  TEXT NOT NULL DEFAULT 'local',
    user_id                 TEXT,
    encrypted_content       BLOB NOT NULL,
    encryption_key_ref      TEXT NOT NULL,
    embedding               TEXT,           -- JSON array of floats
    pattern_description     TEXT,
    confidence_score        REAL DEFAULT 1.0,
    source_episodic_count   INTEGER DEFAULT 0,
    source_episodic_ids     TEXT DEFAULT '[]',   -- JSON array
    pii_verified_clean      INTEGER DEFAULT 0,   -- boolean
    created_at              TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    updated_at              TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);
CREATE INDEX IF NOT EXISTS idx_semantic_org ON semantic_memories(org_id);
CREATE INDEX IF NOT EXISTS idx_semantic_user ON semantic_memories(user_id);


-- Decision Receipts (IDL: Immutable Decision Ledger — Merkle chain)
CREATE TABLE IF NOT EXISTS decision_receipts (
    id                  TEXT PRIMARY KEY,
    session_id          TEXT,
    user_id             TEXT DEFAULT 'local',
    org_id              TEXT DEFAULT 'local',
    receipt_type        TEXT NOT NULL,   -- query/store/promote/arbitrate/decrypt/annihilate
    memories_accessed   TEXT DEFAULT '[]',   -- JSON array
    memories_created    TEXT DEFAULT '[]',   -- JSON array
    arbitration_result  TEXT,           -- JSON object
    context_hash        TEXT,
    response_hash       TEXT,
    merkle_hash         TEXT NOT NULL,
    prev_merkle_hash    TEXT,
    metadata            TEXT DEFAULT '{}',   -- JSON object
    created_at          TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);
CREATE INDEX IF NOT EXISTS idx_receipts_session ON decision_receipts(session_id);
CREATE INDEX IF NOT EXISTS idx_receipts_user ON decision_receipts(user_id);
CREATE INDEX IF NOT EXISTS idx_receipts_created ON decision_receipts(created_at);
CREATE INDEX IF NOT EXISTS idx_receipts_merkle ON decision_receipts(merkle_hash);
CREATE INDEX IF NOT EXISTS idx_receipts_type ON decision_receipts(receipt_type);


-- Behavioral Profiles (DNS: Behavioral Anomaly Scoring baselines)
CREATE TABLE IF NOT EXISTS behavioral_profiles (
    id                      TEXT PRIMARY KEY,
    user_id                 TEXT UNIQUE NOT NULL DEFAULT 'local',
    avg_session_duration    REAL,
    typical_work_hours      TEXT,       -- JSON {start, end}
    avg_queries_per_session REAL,
    common_topics           TEXT DEFAULT '[]',   -- JSON array
    typing_cadence_hash     TEXT,
    avg_events_per_hour     REAL,
    total_events            INTEGER DEFAULT 0,
    baseline_updated_at     TEXT,
    created_at              TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);


-- Anomaly Events (DNS: detected behavioral anomalies)
CREATE TABLE IF NOT EXISTS anomaly_events (
    id              TEXT PRIMARY KEY,
    user_id         TEXT DEFAULT 'local',
    session_id      TEXT,
    bas_score       REAL NOT NULL,
    anomaly_type    TEXT,           -- unusual_hour/frequency/access_pattern/topic/geo
    details         TEXT DEFAULT '{}',   -- JSON
    action_taken    TEXT,           -- logged/enhanced_monitoring/restricted/mfa/frozen
    resolved        INTEGER DEFAULT 0,   -- boolean
    created_at      TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);
CREATE INDEX IF NOT EXISTS idx_anomalies_user ON anomaly_events(user_id);
CREATE INDEX IF NOT EXISTS idx_anomalies_score ON anomaly_events(bas_score);


-- Canary Tokens (honeypot memories for intrusion detection)
CREATE TABLE IF NOT EXISTS canary_tokens (
    id                  TEXT PRIMARY KEY,
    org_id              TEXT DEFAULT 'local',
    episodic_memory_id  TEXT REFERENCES episodic_memories(id),
    token_type          TEXT,           -- financial/personal/medical
    is_triggered        INTEGER DEFAULT 0,   -- boolean
    triggered_by        TEXT,
    triggered_at        TEXT,
    created_at          TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);


-- Annihilation Requests (SDIJAP: cryptographic data destruction)
CREATE TABLE IF NOT EXISTS annihilation_requests (
    id                  TEXT PRIMARY KEY,
    user_id             TEXT DEFAULT 'local',
    org_id              TEXT DEFAULT 'local',
    status              TEXT DEFAULT 'pending',  -- pending/cooling/executing/completed
    cooling_ends_at     TEXT,
    confirmation_token  TEXT,
    memories_targeted   INTEGER,
    memories_destroyed  INTEGER DEFAULT 0,
    initiated_at        TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    completed_at        TEXT
);


-- ═══════════════════════════════════════════════════════════════════════
-- NEW TABLES (not in PostgreSQL original)
-- ═══════════════════════════════════════════════════════════════════════

-- Memory Relationships (replaces Memgraph graph edges)
CREATE TABLE IF NOT EXISTS memory_relationships (
    id                  TEXT PRIMARY KEY,
    source_id           TEXT NOT NULL,
    target_id           TEXT NOT NULL,
    relationship_type   TEXT NOT NULL,   -- RELATED_TO/SAME_SESSION/SAME_TOPIC/FOLLOWED_BY/PROMOTED_TO
    strength            REAL DEFAULT 1.0,
    created_at          TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    UNIQUE(source_id, target_id, relationship_type)
);
CREATE INDEX IF NOT EXISTS idx_rel_source ON memory_relationships(source_id);
CREATE INDEX IF NOT EXISTS idx_rel_target ON memory_relationships(target_id);
CREATE INDEX IF NOT EXISTS idx_rel_type ON memory_relationships(relationship_type);


-- Topics (replaces Memgraph :Topic nodes)
CREATE TABLE IF NOT EXISTS topics (
    name    TEXT PRIMARY KEY
);

-- Memory-Topic junction (replaces Memgraph :ABOUT edges)
CREATE TABLE IF NOT EXISTS memory_topics (
    memory_id   TEXT NOT NULL,
    topic_name  TEXT NOT NULL REFERENCES topics(name),
    PRIMARY KEY (memory_id, topic_name)
);
CREATE INDEX IF NOT EXISTS idx_mt_topic ON memory_topics(topic_name);


-- Encryption Keys (persistent key store — keys were in-memory only before)
CREATE TABLE IF NOT EXISTS encryption_keys (
    key_ref         TEXT PRIMARY KEY,
    encrypted_key   BLOB NOT NULL,      -- AES key encrypted with master key
    purpose         TEXT NOT NULL,       -- org_master/user_mek/session
    created_at      TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);


-- License Cache (local cache of license validation)
CREATE TABLE IF NOT EXISTS license_cache (
    key             TEXT PRIMARY KEY,
    tier            TEXT NOT NULL,
    expires_at      TEXT,
    last_checked    TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);


-- PMP Metrics (Predictive Memory Priming accuracy tracking)
CREATE TABLE IF NOT EXISTS pmp_metrics (
    id                  TEXT PRIMARY KEY DEFAULT 'pmp_singleton',
    total_primes        INTEGER DEFAULT 0,
    cache_hits          INTEGER DEFAULT 0,
    cache_misses        INTEGER DEFAULT 0,
    predictions_made    INTEGER DEFAULT 0,
    predictions_used    INTEGER DEFAULT 0,
    avg_prime_latency_ms REAL DEFAULT 0.0,
    last_patterns_json  TEXT DEFAULT '{}',
    thresholds_json     TEXT DEFAULT '{}',
    updated_at          TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);
"""


# ─── Global Semantic DB Schema ──────────────────────────────────────────────

GLOBAL_SCHEMA_SQL = """
-- Global Semantic Memory Pool (Memory Inheritance — Faz 6)
-- Shared across all projects at ~/.soagila/global_semantic.db

CREATE TABLE IF NOT EXISTS global_semantic_memories (
    id                      TEXT PRIMARY KEY,
    source_project_hash     TEXT NOT NULL,
    source_project_name     TEXT DEFAULT '',
    source_memory_id        TEXT NOT NULL,
    org_id                  TEXT NOT NULL DEFAULT 'local',
    user_id                 TEXT,
    encrypted_content       BLOB NOT NULL,
    encryption_key_ref      TEXT NOT NULL,
    embedding               TEXT,
    pattern_description     TEXT,
    confidence_score        REAL NOT NULL,
    original_confidence     REAL NOT NULL,
    source_episodic_count   INTEGER DEFAULT 0,
    pii_verified_clean      INTEGER DEFAULT 1,
    inheritance_depth       INTEGER DEFAULT 1,
    inherited_at            TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    created_at              TEXT,
    UNIQUE(source_project_hash, source_memory_id)
);
CREATE INDEX IF NOT EXISTS idx_global_sem_project ON global_semantic_memories(source_project_hash);
CREATE INDEX IF NOT EXISTS idx_global_sem_confidence ON global_semantic_memories(confidence_score);

CREATE TABLE IF NOT EXISTS global_memory_topics (
    memory_id   TEXT NOT NULL,
    topic_name  TEXT NOT NULL,
    PRIMARY KEY (memory_id, topic_name)
);
CREATE INDEX IF NOT EXISTS idx_gmt_topic ON global_memory_topics(topic_name);
"""
