"""Database initialization and file operations."""
import os
import sqlite3
import threading
import atexit
import importlib.util
from pathlib import Path
from typing import Optional

from config import BRAIN_DIR, EVENTS_DIR, INDEX_DIR

_db_connection: Optional[sqlite3.Connection] = None
_lock = threading.Lock()


def init_brain() -> None:
    """Initialize brain system - create directories and database."""
    for dir_path in [EVENTS_DIR, INDEX_DIR]:
        dir_path.mkdir(parents=True, exist_ok=True)
    init_db()


def get_session_id() -> str:
    """Get or generate session ID from environment."""
    import hashlib
    import datetime
    session_id = os.environ.get('HERMES_SESSION_ID')
    if not session_id:
        session_id = f"session_{datetime.datetime.now().strftime('%Y%m%d_%H%M%S')}_{hashlib.md5(os.urandom(4)).hexdigest()[:6]}"
    return session_id


def set_session_id(session_id: str) -> None:
    """Set session ID in environment."""
    os.environ['HERMES_SESSION_ID'] = session_id


def get_current_date_dir() -> Path:
    """Get the date-based directory for today's events."""
    import datetime
    now = datetime.datetime.now()
    return EVENTS_DIR / f"{now.year}-{now.month:02d}" / f"{now.day:02d}"


def atomic_write_json(path: Path, data: dict) -> None:
    """Write JSON atomically with fsync to prevent corruption."""
    import json
    temp_path = path.with_suffix('.tmp')
    with open(temp_path, 'w') as f:
        json.dump(data, f, indent=2, default=str)
        f.flush()
        os.fsync(f.fileno())
    os.rename(temp_path, path)


def atomic_append_jsonl(path: Path, record: dict) -> None:
    """Append JSON record to JSONL file atomically."""
    import json
    import fcntl
    path.parent.mkdir(parents=True, exist_ok=True)
    with open(path, 'a') as f:
        fcntl.flock(f.fileno(), fcntl.LOCK_EX)
        try:
            f.write(json.dumps(record, default=str) + '\n')
            f.flush()
            os.fsync(f.fileno())
        finally:
            fcntl.flock(f.fileno(), fcntl.LOCK_UN)


def get_db_path() -> Path:
    """Get the SQLite index database path."""
    return INDEX_DIR / "events.db"


def init_db() -> sqlite3.Connection:
    """Initialize SQLite database for event indexing and entity store.

    Runs versioned migrations from migrations/ directory, falling back to
    inline schema creation only if no migrations exist (first boot).
    """
    global _db_connection
    if _db_connection is not None:
        return _db_connection

    INDEX_DIR.mkdir(parents=True, exist_ok=True)
    db_path = get_db_path()
    _db_connection = sqlite3.connect(str(db_path), timeout=120, check_same_thread=False)
    _db_connection.execute("PRAGMA journal_mode=WAL")
    _db_connection.execute("PRAGMA synchronous=NORMAL")
    _db_connection.execute("PRAGMA wal_autocheckpoint=1000")
    _db_connection.execute("PRAGMA foreign_keys=ON")
    _db_connection.execute("PRAGMA busy_timeout=120000")

    atexit.register(close_db)

    # Always ensure schema_versions table exists (even before migrations run)
    _ensure_schema_versions()

    # Run pending migrations
    migrations_applied = run_migrations(_db_connection)
    if migrations_applied:
        _db_connection.commit()
        return _db_connection

    # Fallback: inline schema (only if no migration modules exist — first boot)
    _create_inline_schema()
    # Mark migration 001 as applied so we don't re-create inline tables
    _mark_migration_applied("001")
    _db_connection.commit()
    return _db_connection


def _create_inline_schema() -> None:
    """Inline schema creation — kept for backward compatibility / first boot.

    New schema changes should go in migrations/ files, not here.
    """
    # Events table
    _db_connection.execute('''
        CREATE TABLE IF NOT EXISTS events (
                id TEXT PRIMARY KEY,
                timestamp TEXT NOT NULL,
                session_id TEXT NOT NULL,
                event_type TEXT NOT NULL,
                tool TEXT,
                tags TEXT,
                context_json TEXT,
                file_paths TEXT,
                entity_id TEXT REFERENCES entities(entity_id),
                searchable_text TEXT
            )
        ''')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_timestamp ON events(timestamp)')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_session ON events(session_id)')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_type ON events(event_type)')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_events_entity ON events(entity_id)')

    # Entities table
    _db_connection.execute('''
        CREATE TABLE IF NOT EXISTS entities (
            entity_id TEXT PRIMARY KEY,
            type TEXT NOT NULL,
            category TEXT NOT NULL,
            pinned INTEGER NOT NULL DEFAULT 0,
            strength TEXT DEFAULT 'moderate',
            content TEXT NOT NULL,
            metadata TEXT DEFAULT '{}',
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL,
            access_count INTEGER NOT NULL DEFAULT 0,
            deleted INTEGER NOT NULL DEFAULT 0
        )
    ''')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_entities_category ON entities(category)')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_entities_pinned ON entities(pinned)')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(type)')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_entities_strength ON entities(strength)')

    # Edges table
    _db_connection.execute('''
        CREATE TABLE IF NOT EXISTS edges (
            entity1 TEXT NOT NULL,
            relation TEXT NOT NULL,
            entity2 TEXT NOT NULL,
            weight REAL NOT NULL DEFAULT 0.5,
            deleted INTEGER NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            PRIMARY KEY (entity1, relation, entity2),
            FOREIGN KEY (entity1) REFERENCES entities(entity_id),
            FOREIGN KEY (entity2) REFERENCES entities(entity_id)
        )
    ''')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_edges_e1 ON edges(entity1)')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_edges_e2 ON edges(entity2)')
    _db_connection.execute('CREATE INDEX IF NOT EXISTS idx_edges_rel ON edges(relation)')

    _create_entity_fts()
    _create_events_fts()

    _db_connection.execute(
        "DELETE FROM entity_fts WHERE entity_id NOT IN (SELECT entity_id FROM entities WHERE deleted = 0)"
    )
    _db_connection.execute(
        "DELETE FROM events_fts WHERE id NOT IN (SELECT id FROM events)"
    )


# ------------------------------------------------------------------
# Migration runner
# ------------------------------------------------------------------

def _ensure_schema_versions() -> None:
    """Create the schema_versions tracking table if it doesn't exist."""
    _db_connection.execute("""
        CREATE TABLE IF NOT EXISTS schema_versions (
            version    TEXT PRIMARY KEY,
            applied_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    _db_connection.commit()


def _get_applied_migrations() -> set:
    """Return set of already-applied migration version strings."""
    rows = _db_connection.execute(
        "SELECT version FROM schema_versions ORDER BY version"
    ).fetchall()
    return {row[0] for row in rows}


def _mark_migration_applied(version: str) -> None:
    """Record that a migration has been applied."""
    _db_connection.execute(
        "INSERT OR IGNORE INTO schema_versions (version) VALUES (?)",
        (version,),
    )


def _discover_migrations() -> list:
    """Discover migration modules in migrations/ directory.

    Returns list of (version_str, module_path) sorted by version.
    """
    migrations_dir = BRAIN_DIR / "migrations"
    if not migrations_dir.exists():
        return []

    results = []
    for fpath in sorted(migrations_dir.iterdir()):
        if fpath.suffix == ".py" and fpath.stem[0].isdigit():
            version = fpath.stem  # e.g. "001", "002_add_something"
            results.append((version, str(fpath)))
    return results


def run_migrations(conn: sqlite3.Connection) -> int:
    """Run any pending migrations. Returns number applied."""
    global _db_connection
    _db_connection = conn

    _ensure_schema_versions()
    applied = _get_applied_migrations()
    migrations = _discover_migrations()

    count = 0
    for version, mpath in migrations:
        if version in applied:
            continue

        # Load migration module
        spec = importlib.util.spec_from_file_location(f"migration_{version}", mpath)
        mod = importlib.util.module_from_spec(spec)
        spec.loader.exec_module(mod)

        if not hasattr(mod, "upgrade"):
            print(f"[brain] WARNING: migration {version} has no upgrade() — skipping")
            continue

        print(f"[brain] Applying migration {version} ...")
        mod.upgrade(conn)
        _mark_migration_applied(version)
        conn.commit()
        print(f"[brain] Migration {version} applied.")
        count += 1

    return count


def _create_entity_fts() -> None:
    """Create entity FTS5 table with contraction preprocessing."""
    global _db_connection

    try:
        table_info = _db_connection.execute('PRAGMA table_info(entity_fts)').fetchall()
        col_names = [row[1] for row in table_info]
        if 'content_pp' not in col_names:
            _db_connection.execute('DROP TABLE IF EXISTS entity_fts')
            for t in ['entity_fts_insert', 'entity_fts_update_delete',
                      'entity_fts_update_undelete', 'entity_fts_update_normal',
                      'entity_fts_delete']:
                _db_connection.execute(f'DROP TRIGGER IF EXISTS {t}')
            raise Exception("schema_migration_needed")
    except Exception:
        _db_connection.execute('''
            CREATE VIRTUAL TABLE entity_fts USING fts5(entity_id, content_pp, category, type)
        ''')

    # Build preprocessing expression
    _contractions = [("won''t", "will not"), ("can''t", "cannot"), ("don''t", "do not"),
        ("doesn''t", "does not"), ("isn''t", "is not"), ("aren''t", "are not"),
        ("wasn''t", "was not"), ("weren''t", "were not"), ("haven''t", "have not"),
        ("hasn''t", "has not"), ("hadn''t", "had not"), ("wouldn''t", "would not"),
        ("couldn''t", "could not"), ("shouldn''t", "should not"), ("mustn''t", "must not"),
        ("i''m", "i am"), ("you''re", "you are"), ("he''s", "he is"), ("she''s", "she is"),
        ("it''s", "it is"), ("we''re", "we are"), ("they''re", "they are"),
        ("i''ve", "i have"), ("you''ve", "you have"), ("we''ve", "have"), ("they''ve", "they have"),
        ("i''ll", "i will"), ("you''ll", "you will"), ("he''ll", "he will"), ("she''ll", "she will"),
        ("we''ll", "we will"), ("they''ll", "they will"),
        ("i''d", "i would"), ("you''d", "you would"), ("he''d", "he would"), ("she''d", "she would"),
        ("we''d", "we would"), ("they''d", "they would"),
        ("that''s", "that is"), ("there''s", "there is"), ("who''s", "who is"), ("what''s", "what is"),
        ("didn''t", "did not"), ("let''s", "let us"), ("here''s", "here is"),
        ("shan''t", "shall not"), ("needn''t", "need not")]

    _pp = "NEW.content"
    _op = "OLD.content"
    for old, new in _contractions:
        _pp = f"REPLACE({_pp}, '{old}', '{new}')"
        _op = f"REPLACE({_op}, '{old}', '{new}')"
    _pp = f"LOWER({_pp})"
    _op = f"LOWER({_op})"

    _db_connection.execute(f'''
        CREATE TRIGGER IF NOT EXISTS entity_fts_insert AFTER INSERT ON entities
        BEGIN
            INSERT OR REPLACE INTO entity_fts(entity_id, content_pp, category, type)
            VALUES (NEW.entity_id, {_pp}, LOWER(NEW.category), LOWER(NEW.type));
        END
    ''')

    _db_connection.execute('''
        CREATE TRIGGER IF NOT EXISTS entity_fts_update_delete AFTER UPDATE ON entities
        WHEN OLD.deleted = 0 AND NEW.deleted = 1
        BEGIN
            DELETE FROM entity_fts WHERE entity_id = NEW.entity_id;
        END
    ''')

    _db_connection.execute(f'''
        CREATE TRIGGER IF NOT EXISTS entity_fts_update_undelete AFTER UPDATE ON entities
        WHEN OLD.deleted = 1 AND NEW.deleted = 0
        BEGIN
            INSERT INTO entity_fts(entity_id, content_pp, category, type)
            VALUES (NEW.entity_id, {_pp}, LOWER(NEW.category), LOWER(NEW.type));
        END
    ''')

    _db_connection.execute(f'''
        CREATE TRIGGER IF NOT EXISTS entity_fts_update_normal AFTER UPDATE ON entities
        WHEN OLD.deleted = 0 AND NEW.deleted = 0
        BEGIN
            UPDATE entity_fts SET content_pp = {_pp}, category = LOWER(NEW.category), type = LOWER(NEW.type)
            WHERE entity_id = NEW.entity_id;
        END
    ''')

    _db_connection.execute('''
        CREATE TRIGGER IF NOT EXISTS entity_fts_delete AFTER DELETE ON entities
        BEGIN
            DELETE FROM entity_fts WHERE entity_id = OLD.entity_id;
        END
    ''')

    # Purge stale FTS rows
    _db_connection.execute(
        "DELETE FROM entity_fts WHERE entity_id IN (SELECT entity_id FROM entities WHERE deleted = 1)"
    )


def _create_events_fts() -> None:
    """Create events FTS5 table."""
    global _db_connection

    try:
        table_info = _db_connection.execute('PRAGMA table_info(events_fts)').fetchall()
        col_names = [row[1] for row in table_info]
        if 'entity_id' not in col_names:
            _db_connection.execute('DROP TABLE IF EXISTS events_fts')
            for t in ['events_ai', 'events_ad', 'events_au']:
                _db_connection.execute(f'DROP TRIGGER IF EXISTS {t}')
    except Exception:
        pass

    _db_connection.execute('''
        CREATE VIRTUAL TABLE IF NOT EXISTS events_fts USING fts5(
            id UNINDEXED,
            search_text,
            entity_id UNINDEXED,
            tokenize=porter
        )
    ''')

    _search_expr = ("new.event_type || ' ' || coalesce(new.tool, '') || ' ' || "
        "coalesce(new.tags, '') || ' ' || coalesce(new.context_json, '') || ' ' || "
        "coalesce(new.file_paths, '') || ' ' || coalesce(new.searchable_text, '') || ' ' || "
        "coalesce(new.entity_id, '')")

    _db_connection.execute(f'''
        CREATE TRIGGER IF NOT EXISTS events_ai AFTER INSERT ON events BEGIN
            INSERT OR REPLACE INTO events_fts(id, search_text, entity_id)
            VALUES (new.id, {_search_expr}, new.entity_id);
        END
    ''')

    _db_connection.execute('''
        CREATE TRIGGER IF NOT EXISTS events_ad AFTER DELETE ON events BEGIN
            DELETE FROM events_fts WHERE id = old.id;
        END
    ''')

    _db_connection.execute(f'''
        CREATE TRIGGER IF NOT EXISTS events_au AFTER UPDATE ON events BEGIN
            INSERT OR REPLACE INTO events_fts(id, search_text, entity_id)
            VALUES (new.id, {_search_expr}, new.entity_id);
        END
    ''')

    # Backfill
    _select_expr = ("event_type || ' ' || coalesce(tool, '') || ' ' || "
        "coalesce(tags, '') || ' ' || coalesce(context_json, '') || ' ' || "
        "coalesce(file_paths, '') || ' ' || coalesce(searchable_text, '') || ' ' || "
        "coalesce(entity_id, '')")

    _db_connection.execute(f'''
        INSERT OR IGNORE INTO events_fts(id, search_text, entity_id)
        SELECT id, {_select_expr}, entity_id FROM events
        WHERE id NOT IN (SELECT id FROM events_fts)
    ''')


def close_db() -> None:
    """Close the database connection."""
    global _db_connection
    if _db_connection is not None:
        try:
            _db_connection.close()
        except Exception:
            pass
        _db_connection = None