#!/usr/bin/env -S uv run --script
# /// script
# requires-python = ">=3.13"
# dependencies = ["pyyaml>=6.0"]
# ///
"""
Cortex CLI - Personal knowledge compiler utilities

Mechanical operations for the Cortex knowledge system: setup, scanning,
triage, batch planning, extraction, and index rebuilding. The LLM handles
the actual knowledge compilation; this script handles the bookkeeping.
"""

import hashlib
import os
import re
import sqlite3
import subprocess
import sys
from pathlib import Path
from typing import NoReturn

import yaml

# --- File classification ---

FILE_TYPES = {
    "text": {".md", ".txt"},
    "pdf": {".pdf"},
    "document": {".docx", ".doc", ".rtf", ".pptx", ".xlsx", ".xls"},
    "web": {".html", ".htm"},
    "image": {".png", ".jpg", ".jpeg", ".gif", ".webp", ".svg", ".bmp", ".tiff", ".heic"},
    "audio": {".mp3", ".wav", ".m4a", ".ogg", ".flac", ".aac", ".opus", ".aiff"},
    "video": {".mp4", ".mov", ".avi", ".mkv", ".webm"},
}

# Reverse lookup: extension -> file type
EXT_TO_TYPE: dict[str, str] = {}
for ftype, exts in FILE_TYPES.items():
    for ext in exts:
        EXT_TO_TYPE[ext] = ftype

# Types that an LLM can read and should be downloaded if online-only
READABLE_TYPES = {"text", "pdf", "document", "web"}

# Types that are cataloged but not read by an LLM
CATALOG_TYPES = {"image", "audio", "video"}

SENSITIVE_PATTERNS = {
    ".env", ".ssh", ".aws", ".gnupg", ".git", ".ds_store",
    "__pycache__", "node_modules", ".obsidian", ".netrc", ".npmrc", ".pgpass",
    ".kube", ".docker",
}
SENSITIVE_EXTENSIONS = {".pem", ".key", ".p12", ".pfx", ".jks", ".keystore", ".keychain", ".keychain-db"}
SENSITIVE_PREFIXES = ("secret", "credentials", ".env", "id_rsa", "id_ed25519", "id_ecdsa")

KNOWLEDGE_CATEGORIES = [
    "people", "ventures", "topics", "synthesis", "decisions",
    "learning", "research",
]

EXTRA_DIRECTORIES = ["learning/archive", "daily"]

CONFIG_DIR = Path.home() / ".config" / "cortex"
CONFIG_FILE = CONFIG_DIR / "config"


def error(message: str, hint: str | None = None) -> NoReturn:
    """Print error message to stderr and exit."""
    print(f"Error: {message}", file=sys.stderr)
    if hint:
        print(hint, file=sys.stderr)
    sys.exit(1)


def load_config() -> dict[str, str]:
    """Load config from ~/.config/cortex/config."""
    if not CONFIG_FILE.exists():
        error(
            "Cortex not configured",
            "Run `cortex setup` to detect cloud storage and initialize the store.",
        )
    config = {}
    for line in CONFIG_FILE.read_text().splitlines():
        line = line.strip()
        if not line or line.startswith("#"):
            continue
        if "=" in line:
            key, _, value = line.partition("=")
            config[key.strip()] = os.path.expanduser(value.strip())
    return config


def get_store_path() -> Path:
    """Get the cortex store path from config."""
    config = load_config()
    store = config.get("CORTEX_STORE_PATH")
    if not store:
        error("CORTEX_STORE_PATH not set in config", "Run `cortex setup` to fix.")
    path = Path(store)
    if not path.exists():
        error(
            f"Cortex store not found at {path}",
            "Is cloud storage running? Run `cortex setup` to reconfigure.",
        )
    return path


def get_db_path() -> Path:
    """Get the SQLite database path."""
    return get_store_path() / "cortex.db"


def get_db() -> sqlite3.Connection:
    """Open SQLite connection with WAL mode."""
    db = sqlite3.connect(str(get_db_path()))
    db.execute("PRAGMA journal_mode=WAL")
    db.execute("PRAGMA foreign_keys=ON")
    db.row_factory = sqlite3.Row
    return db


def init_db(db_path: Path) -> None:
    """Create the SQLite schema."""
    db = sqlite3.connect(str(db_path))
    db.execute("PRAGMA journal_mode=WAL")
    db.executescript("""
        CREATE TABLE IF NOT EXISTS sources (
            path TEXT PRIMARY KEY,
            hash TEXT,
            status TEXT NOT NULL DEFAULT 'new',
            file_type TEXT,
            file_size INTEGER,
            online_only INTEGER DEFAULT 0,
            source_date TEXT,
            discovered_at TEXT NOT NULL,
            ingested_at TEXT,
            error TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_sources_status ON sources(status);
        CREATE INDEX IF NOT EXISTS idx_sources_hash ON sources(hash);
        CREATE INDEX IF NOT EXISTS idx_sources_date ON sources(source_date);
    """)
    db.commit()
    db.close()


# --- Cloud storage detection ---

def detect_cloud_storage() -> list[tuple[str, Path]]:
    """Detect available cloud storage paths."""
    found = []
    home = Path.home()

    dropbox = home / "Dropbox"
    if dropbox.is_dir():
        found.append(("dropbox", dropbox))

    cloud_storage = home / "Library" / "CloudStorage"
    if cloud_storage.is_dir():
        for entry in cloud_storage.iterdir():
            if entry.name.startswith("GoogleDrive-") and entry.is_dir():
                my_drive = entry / "My Drive"
                if my_drive.is_dir():
                    found.append(("google-drive", my_drive))
                    break

    gdrive_linux = home / "google-drive"
    if gdrive_linux.is_dir():
        found.append(("google-drive", gdrive_linux))

    return found


# --- Hash ---

def compute_hash(file_path: Path) -> str | None:
    """Compute MD5 hash of file content. Returns None if file is not readable (online-only or empty)."""
    h = hashlib.md5()
    try:
        with open(file_path, "rb") as f:
            chunk = f.read(8192)
            if not chunk:
                # No content — online-only or truly empty. Either way, nothing to hash.
                return None
            h.update(chunk)
            for chunk in iter(lambda: f.read(8192), b""):
                h.update(chunk)
    except OSError:
        return None
    return h.hexdigest()


# --- Sensitivity check ---

def is_sensitive(path: Path) -> bool:
    """Check if a file matches sensitive patterns."""
    name = path.name.lower()
    for part in path.parts:
        if part.lower() in SENSITIVE_PATTERNS:
            return True
    if path.suffix.lower() in SENSITIVE_EXTENSIONS:
        return True
    for prefix in SENSITIVE_PREFIXES:
        if name.startswith(prefix):
            return True
    return False


def classify_file(path: Path) -> str:
    """Classify a file by its extension."""
    return EXT_TO_TYPE.get(path.suffix.lower(), "other")


# Date patterns found in filenames and paths
DATE_PATTERNS = [
    # YYYY-MM-DD in filename or path
    re.compile(r"(\d{4})-(\d{2})-(\d{2})"),
    # YYYY_MM_DD
    re.compile(r"(\d{4})_(\d{2})_(\d{2})"),
    # Fireflies/Limitless style: no explicit date but has ID suffix
    # Fall back to file mtime for these
]


def _match_date(text: str) -> str | None:
    """Try to extract a YYYY-MM-DD date from text. Returns None if no valid date found."""
    for pattern in DATE_PATTERNS:
        match = pattern.search(text)
        if match:
            year, month, day = int(match.group(1)), int(match.group(2)), int(match.group(3))
            if 2000 <= year <= 2030 and 1 <= month <= 12 and 1 <= day <= 31:
                return f"{year:04d}-{month:02d}-{day:02d}"
    return None


def extract_source_date(path: Path) -> str | None:
    """Extract a date from filename/path, falling back to file mtime.

    Searches filename first (more specific), then full path, then mtime.
    Returns YYYY-MM-DD or None.
    """
    # Filename first — most specific date
    result = _match_date(path.name)
    if result:
        return result

    # Full path — catches date-named directories
    result = _match_date(str(path))
    if result:
        return result

    # Fall back to file modification time
    try:
        mtime = path.stat().st_mtime
        from datetime import datetime, timezone
        dt = datetime.fromtimestamp(mtime, tz=timezone.utc)
        return dt.strftime("%Y-%m-%d")
    except OSError:
        return None


# --- Setup ---

def cmd_setup() -> None:
    """Detect cloud storage, initialize store."""
    providers = detect_cloud_storage()

    if not providers:
        error(
            "No cloud storage found",
            "Cortex requires Dropbox or Google Drive.\n"
            "Looked for:\n"
            "  ~/Dropbox/\n"
            "  ~/Library/CloudStorage/GoogleDrive-*/My Drive/\n"
            "  ~/google-drive/\n"
            "Install one and run `cortex setup` again.",
        )

    if len(providers) == 1:
        provider, base_path = providers[0]
        print(f"Found {provider} at {base_path}")
    else:
        print("Multiple cloud storage providers found:")
        for i, (name, path) in enumerate(providers, 1):
            print(f"  {i}. {name} ({path})")
        choice = input("Select provider [1]: ").strip() or "1"
        try:
            idx = int(choice) - 1
            provider, base_path = providers[idx]
        except (ValueError, IndexError):
            error("Invalid selection")

    store_path = base_path / "Knowledge Base"
    schema_path = store_path / "schema.md"

    # Create directory structure
    created = []
    for d in (
        [store_path]
        + [store_path / cat for cat in KNOWLEDGE_CATEGORIES]
        + [store_path / d for d in EXTRA_DIRECTORIES]
    ):
        if not d.exists():
            d.mkdir(parents=True, exist_ok=True)
            created.append(str(d.relative_to(store_path)))

    if created:
        print(f"Created {len(created)} directories")
    else:
        print("Store structure already exists")

    # Create initial knowledge files
    _init_knowledge_files(store_path)

    # Deploy schema.md if not present
    if not schema_path.exists():
        _deploy_schema(schema_path)
        print("Deployed schema.md")
    else:
        print("schema.md already exists (not overwriting)")

    # Create .gitignore
    gitignore = store_path / ".gitignore"
    if not gitignore.exists():
        gitignore.write_text(".obsidian/\ncortex.db\ncortex.db-wal\ncortex.db-shm\n*.log\n")
        print("Created .gitignore")

    # Initialize SQLite
    db_path = store_path / "cortex.db"
    if not db_path.exists():
        init_db(db_path)
        print("Initialized SQLite database")
    else:
        print("SQLite database already exists")

    # Save config
    CONFIG_DIR.mkdir(parents=True, exist_ok=True)
    config_content = (
        f"CORTEX_STORE_PATH={store_path}\n"
        f"CLOUD_PROVIDER={provider}\n"
    )
    CONFIG_FILE.write_text(config_content)
    print(f"Config saved to {CONFIG_FILE}")

    # Init git if needed
    git_dir = store_path / ".git"
    if not git_dir.exists():
        result = subprocess.run(["git", "init", "-q"], cwd=store_path, capture_output=True)
        if result.returncode == 0:
            print("Initialized git repository")
        else:
            print("Warning: git init failed (git may not be installed). Git tracking disabled.")

    print(f"\nCortex initialized at {store_path}")
    print(f"Provider: {provider}")
    print("Next: run `cortex scan ~/Dropbox` to discover files for ingestion.")
    print("After ingest is complete, run `cortex link` to connect to OpenClaw.")


def _init_knowledge_files(store_path: Path) -> None:
    """Create initial knowledge files if they don't exist."""
    # Root index
    index = store_path / "index.md"
    if not index.exists():
        index.write_text(
            "# Cortex Index\n\n"
            f"Last updated: {_today()}\n"
            "Total pages: 0 | Sources ingested: 0\n\n"
            "## Categories\n\n"
            "| Category | Pages | Index |\n"
            "|----------|-------|-------|\n"
            + "".join(
                f"| {cat.title().replace('-', ' ')} | 0 | "
                f"[{cat}/index.md]({cat}/index.md) |\n"
                for cat in KNOWLEDGE_CATEGORIES
            )
            + "\n## Recent Activity\n\n"
            "_No activity yet._\n"
        )

    # Category indexes
    for cat in KNOWLEDGE_CATEGORIES:
        cat_index = store_path / cat / "index.md"
        if not cat_index.exists():
            title = cat.title().replace("-", " ")
            cat_index.write_text(f"# {title}\n\n_No entries yet._\n")

    # Log
    log = store_path / ".log"
    if not log.exists():
        log.write_text("# Operation Log\n\n_No operations yet._\n")

    # Review queue
    review = store_path / "review-queue.md"
    if not review.exists():
        review.write_text(
            "# Review Queue\n\n"
            "Items below were flagged during ingest because Cortex couldn't resolve them\n"
            "confidently. Review each item, resolve it, and delete the entry.\n\n"
            "_No items pending._\n"
        )

    # Learning files
    corrections = store_path / "learning" / "corrections.md"
    if not corrections.exists():
        corrections.write_text("# Corrections\n\nAppend corrections here.\n")

    patterns = store_path / "learning" / "patterns.md"
    if not patterns.exists():
        patterns.write_text("# Patterns\n\n_No patterns detected yet._\n")


def _deploy_schema(schema_path: Path) -> None:
    """Deploy the schema.md template to the store."""
    skill_dir = Path(__file__).parent
    template = skill_dir / "schema-template.md"
    if template.exists():
        schema_path.write_text(template.read_text())
    else:
        print(f"WARNING: schema-template.md not found at {template}", file=sys.stderr)
        print("  Deploying stub schema. Copy the full schema manually.", file=sys.stderr)
        schema_path.write_text(
            "# Cortex Schema\n\n"
            "This file instructs the LLM how to maintain the knowledge store.\n"
            "See the full schema-template.md in the cortex skill directory.\n"
        )


def _setup_symlink(store_path: Path) -> bool:
    """Create symlink from OpenClaw memory to store. Returns True if linked."""
    openclaw_memory = Path.home() / ".openclaw" / "memory"

    # Create ~/.openclaw/memory if it doesn't exist
    if not openclaw_memory.exists():
        openclaw_memory.mkdir(parents=True, exist_ok=True)
        print(f"Created {openclaw_memory}")

    symlink_path = openclaw_memory / "Knowledge Base"

    if symlink_path.is_symlink():
        current_target = symlink_path.resolve()
        if current_target == store_path.resolve():
            print(f"Symlink already correct: {symlink_path} -> {store_path}")
        else:
            print(f"Existing symlink points to: {current_target}")
            confirm = input(f"Update to {store_path}? [y/N]: ").strip().lower()
            if confirm not in ("y", "yes"):
                print("Symlink not updated.")
                return False
            symlink_path.unlink()
            symlink_path.symlink_to(store_path)
            print(f"Symlink created: {symlink_path} -> {store_path}")
    elif symlink_path.exists():
        print(f"Warning: {symlink_path} exists and is not a symlink. Skipping.")
        return False
    else:
        symlink_path.symlink_to(store_path)
        print(f"Symlink created: {symlink_path} -> {store_path}")

    # Create MEMORY.md routing table if it doesn't exist
    memory_md = openclaw_memory / "MEMORY.md"
    if not memory_md.exists():
        memory_md.write_text(
            "# Memory Index\n\n"
            "## Quick Links\n"
            "- [Knowledge Base Index](Knowledge Base/index.md)\n"
            "- [All People](Knowledge Base/people/index.md)\n"
            "- [Recent Decisions](Knowledge Base/decisions/index.md)\n"
        )
        print("Created MEMORY.md routing table")
    else:
        content = memory_md.read_text()
        if "Knowledge Base/index.md" not in content:
            content = content.rstrip() + "\n- [Knowledge Base Index](Knowledge Base/index.md)\n"
            memory_md.write_text(content)
            print("Added Knowledge Base entry to MEMORY.md")

    return True


# --- Status ---

def cmd_status() -> None:
    """Show store statistics."""
    store = get_store_path()
    db = get_db()

    print("# Cortex Status\n")
    print(f"Store: {store}")
    print(f"Config: {CONFIG_FILE}\n")

    # Knowledge pages per category
    total_pages = 0
    print("## Knowledge Pages\n")
    print(f"{'Category':<12} {'Pages':>5}")
    print(f"{'-'*12} {'-'*5}")
    for cat in KNOWLEDGE_CATEGORIES:
        cat_dir = store / cat
        if cat_dir.is_dir():
            pages = [
                f for f in cat_dir.iterdir()
                if f.suffix == ".md" and f.name != "index.md"
            ]
            count = len(pages)
        else:
            count = 0
        total_pages += count
        print(f"{cat:<12} {count:>5}")
    print(f"{'-'*12} {'-'*5}")
    print(f"{'Total':<12} {total_pages:>5}")

    # Source stats from SQLite
    row = db.execute("SELECT COUNT(*) as total FROM sources").fetchone()
    total_sources = row["total"]

    print(f"\n## Sources: {total_sources}\n")
    if total_sources > 0:
        rows = db.execute(
            "SELECT status, COUNT(*) as cnt FROM sources GROUP BY status ORDER BY cnt DESC"
        ).fetchall()
        for row in rows:
            print(f"  {row['status']:<12} {row['cnt']:>6}")

        # File type breakdown
        print("\n## File Types\n")
        rows = db.execute(
            "SELECT file_type, COUNT(*) as cnt FROM sources WHERE status != 'skipped' "
            "GROUP BY file_type ORDER BY cnt DESC"
        ).fetchall()
        for row in rows:
            print(f"  {row['file_type'] or 'unknown':<12} {row['cnt']:>6}")

        # Online-only count
        online = db.execute(
            "SELECT COUNT(*) as cnt FROM sources WHERE online_only = 1"
        ).fetchone()["cnt"]
        if online:
            print(f"\n## Online-only files: {online}")
            print("  These need to be synced from Dropbox before ingesting.")

    db.close()


# --- Scan ---

def cmd_scan(dir_path: str) -> None:
    """Discover files, classify, hash, and store in SQLite."""
    path = Path(dir_path).expanduser().resolve()
    if not path.is_dir():
        error(f"Not a directory: {path}")

    store = get_store_path()
    db = get_db()

    # Don't scan the store itself
    store_resolved = store.resolve()

    stats = {
        "discovered": 0,
        "skipped_sensitive": 0,
        "skipped_unknown": 0,
        "already_known": 0,
        "online_only": 0,
        "triggered_downloads": 0,
        "skipped_errors": 0,
    }

    print(f"Scanning {path}...")
    batch = []
    seen_hashes: dict[str, str] = {}  # hash -> first path (in-batch dedup)
    files_processed = 0

    for root, dirs, filenames in os.walk(path):
        root_path = Path(root)

        # Skip the store directory itself
        if root_path.resolve() == store_resolved or str(root_path.resolve()).startswith(str(store_resolved) + "/"):
            continue

        # Skip hidden directories and sensitive patterns
        dirs[:] = [d for d in dirs if not d.startswith(".") and d.lower() not in SENSITIVE_PATTERNS]

        for name in sorted(filenames):
            if name.startswith("."):
                continue

            fpath = root_path / name

            if is_sensitive(fpath):
                stats["skipped_sensitive"] += 1
                continue

            file_type = classify_file(fpath)
            if file_type == "other":
                stats["skipped_unknown"] += 1
                continue

            abs_path = str(fpath.resolve())

            # Check if already in database
            existing = db.execute(
                "SELECT hash, status, online_only FROM sources WHERE path = ?", (abs_path,)
            ).fetchone()
            if existing:
                # Retry hash for previously online-only files that may now be accessible
                if existing["hash"] is None and existing["online_only"]:
                    file_hash = compute_hash(fpath)
                    if file_hash:
                        db.execute(
                            "UPDATE sources SET hash = ?, online_only = 0 WHERE path = ?",
                            (file_hash, abs_path),
                        )
                stats["already_known"] += 1
                continue

            # Get file size
            try:
                file_size = fpath.stat().st_size
            except OSError:
                stats["skipped_errors"] += 1
                continue

            # Extract source date from filename/path/mtime
            source_date = extract_source_date(fpath)

            # Catalog files (images, audio, video) — don't hash, just record metadata
            if file_type in CATALOG_TYPES:
                batch.append((
                    abs_path, None, "new", file_type, file_size,
                    0, source_date, _now(), None, None,
                ))
                stats["discovered"] += 1
                files_processed += 1
                if files_processed % 1000 == 0:
                    print(f"  ...{files_processed} files processed")
                    _insert_sources(db, batch)
                    db.commit()
                    batch = []
                continue

            # Readable files — hash content (triggers download for online-only)
            file_hash = compute_hash(fpath)
            is_online = file_hash is None

            if is_online:
                stats["online_only"] += 1
                if file_type in READABLE_TYPES:
                    stats["triggered_downloads"] += 1

            # Check for duplicate content by hash (DB + in-memory for current batch)
            if file_hash:
                dup_path = seen_hashes.get(file_hash)
                if not dup_path:
                    dup = db.execute(
                        "SELECT path FROM sources WHERE hash = ?",
                        (file_hash,),
                    ).fetchone()
                    if dup:
                        dup_path = dup["path"]
                if dup_path:
                    batch.append((
                        abs_path, file_hash, "skipped", file_type, file_size,
                        0, source_date, _now(),
                        None, f"Duplicate of {dup_path}",
                    ))
                    stats["discovered"] += 1
                    files_processed += 1
                    if files_processed % 1000 == 0:
                        print(f"  ...{files_processed} files processed")
                        _insert_sources(db, batch)
                        db.commit()
                        batch = []
                    continue

            if file_hash:
                seen_hashes[file_hash] = abs_path

            batch.append((
                abs_path, file_hash, "new", file_type, file_size,
                1 if is_online else 0, source_date, _now(), None, None,
            ))
            stats["discovered"] += 1
            files_processed += 1

            if files_processed % 1000 == 0:
                print(f"  ...{files_processed} files processed")
                _insert_sources(db, batch)
                db.commit()
                batch = []

    # Final batch
    if batch:
        _insert_sources(db, batch)

    db.commit()
    db.close()

    print(f"\n## Scan Results\n")
    print(f"  New files discovered: {stats['discovered']}")
    print(f"  Already in database:  {stats['already_known']}")
    print(f"  Skipped (sensitive):  {stats['skipped_sensitive']}")
    print(f"  Skipped (unknown):    {stats['skipped_unknown']}")
    if stats["skipped_errors"]:
        print(f"  Skipped (read error): {stats['skipped_errors']}")
    if stats["online_only"]:
        print(f"  Online-only:          {stats['online_only']}")
        print(f"  Downloads triggered:  {stats['triggered_downloads']}")
        print("\n  Online-only files will sync from Dropbox in the background.")
        print("  Re-run `cortex scan` after they sync to compute hashes.")


def _insert_sources(db: sqlite3.Connection, batch: list[tuple]) -> None:
    """Batch insert source records."""
    db.executemany(
        "INSERT OR IGNORE INTO sources "
        "(path, hash, status, file_type, file_size, online_only, source_date, discovered_at, ingested_at, error) "
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        batch,
    )


# --- Triage ---

# Triage patterns — files matching these are low-value
TRIAGE_SKIP_PREFIXES = [
    "A Brief ",        # Limitless ambient fragments
    "A Short ",
    "An Unrelated ",
    "A Single ",
]

TRIAGE_MIN_SIZE = 500  # bytes — skip files smaller than this


def cmd_triage() -> None:
    """Pre-filter low-value files in the database."""
    db = get_db()

    # Count files eligible for triage
    total = db.execute(
        "SELECT COUNT(*) as cnt FROM sources WHERE status = 'new'"
    ).fetchone()["cnt"]

    if total == 0:
        print("No new files to triage. Run `cortex scan` first.")
        db.close()
        return

    print(f"Triaging {total} new files...\n")
    skipped = 0

    # Skip by filename prefix (match only the filename after the last /)
    for prefix in TRIAGE_SKIP_PREFIXES:
        # Fetch candidates and filter in Python for accurate filename matching
        candidates = db.execute(
            "SELECT path FROM sources WHERE status = 'new' AND path LIKE ?",
            (f"%/{prefix}%",),
        ).fetchall()
        paths_to_skip = [r["path"] for r in candidates if Path(r["path"]).name.startswith(prefix)]
        if paths_to_skip:
            db.executemany(
                "UPDATE sources SET status = 'skipped', error = ? WHERE path = ?",
                [(f"Triage: filename prefix '{prefix}'", p) for p in paths_to_skip],
            )
            skipped += len(paths_to_skip)

    # Skip tiny files (likely empty or trivial)
    cursor = db.execute(
        "UPDATE sources SET status = 'skipped', error = 'Triage: file too small' "
        "WHERE status = 'new' AND file_size < ? AND file_type IN ('text', 'code')",
        (TRIAGE_MIN_SIZE,),
    )
    skipped += cursor.rowcount

    # Skip duplicate Otter Transcripts (same name exists in Otter/)
    cursor = db.execute("""
        UPDATE sources SET status = 'skipped', error = 'Triage: duplicate transcript directory'
        WHERE status = 'new' AND path LIKE '%/Otter Transcripts/%'
        AND EXISTS (
            SELECT 1 FROM sources s2
            WHERE s2.path LIKE '%/Otter/%'
            AND s2.path != sources.path
            AND s2.hash = sources.hash
        )
    """)
    skipped += cursor.rowcount

    db.commit()

    remaining = db.execute(
        "SELECT COUNT(*) as cnt FROM sources WHERE status = 'new'"
    ).fetchone()["cnt"]

    print(f"  Skipped: {skipped}")
    print(f"  Remaining: {remaining}")
    print(f"\nRun `cortex plan` to see prioritized ingest batches.")

    db.close()


# --- Plan ---

def _extract_group(path_str: str) -> str:
    """Extract a meaningful group name from a file path.

    Returns the first two directory levels under a cloud storage root
    (e.g. 'Data For AI/Conversations') for natural grouping in plan output.
    """
    parts = Path(path_str).parts
    for i, part in enumerate(parts):
        if part in ("Dropbox", "My Drive", "google-drive"):
            remaining = parts[i + 1 : -1]  # dirs between root and filename
            if not remaining:
                return part
            return "/".join(remaining[:2])
    # Fallback: parent directory name
    return Path(path_str).parent.name or "root"


def cmd_plan() -> None:
    """Show files ready for ingestion, grouped by source directory."""
    db = get_db()

    # Readable files (LLM will process these)
    readable = db.execute(
        "SELECT path, file_type, file_size, source_date FROM sources "
        "WHERE status = 'new' AND file_type NOT IN ('image', 'audio', 'video') "
        "ORDER BY source_date ASC NULLS LAST"
    ).fetchall()

    # Catalog files (metadata only, no LLM)
    catalog = db.execute(
        "SELECT COUNT(*) as cnt, SUM(file_size) as total_size FROM sources "
        "WHERE status = 'new' AND file_type IN ('image', 'audio', 'video')"
    ).fetchone()

    if not readable and not catalog["cnt"]:
        print("No files ready for ingestion. Run `cortex scan` and `cortex triage` first.")
        db.close()
        return

    if readable:
        # Group by source directory
        groups: dict[str, list[sqlite3.Row]] = {}
        for row in readable:
            group = _extract_group(row["path"])
            groups.setdefault(group, []).append(row)

        # Sort groups by their oldest source date (rows already sorted ASC by query)
        def _group_oldest(name: str) -> str:
            dates = [r["source_date"] for r in groups[name] if r["source_date"]]
            return dates[0] if dates else "9999-99-99"

        print("## Files to Ingest\n")
        for group_name in sorted(groups.keys(), key=_group_oldest):
            group_rows = groups[group_name]
            total_size = sum(r["file_size"] or 0 for r in group_rows)
            dates = [r["source_date"] for r in group_rows if r["source_date"]]
            date_range = f" ({dates[0]} to {dates[-1]})" if dates else ""

            print(f"### {group_name}")
            print(f"{len(group_rows)} files, {total_size / 1024 / 1024:.1f} MB{date_range}\n")

            for row in group_rows[:10]:
                date = row["source_date"] or "no-date"
                print(f"  {date}  {row['file_type']:<8}  {Path(row['path']).name}")

            if len(group_rows) > 10:
                print(f"  ... and {len(group_rows) - 10} more")
            print()

    if catalog["cnt"]:
        size_mb = (catalog["total_size"] or 0) / 1024 / 1024
        print(f"### Media (catalog only)")
        print(f"{catalog['cnt']} files, {size_mb:.1f} MB — no LLM processing needed\n")

    # Summary
    print("## Summary\n")
    total = db.execute(
        "SELECT COUNT(*) as cnt FROM sources WHERE status = 'new'"
    ).fetchone()["cnt"]
    complete = db.execute(
        "SELECT COUNT(*) as cnt FROM sources WHERE status = 'complete'"
    ).fetchone()["cnt"]
    skipped = db.execute(
        "SELECT COUNT(*) as cnt FROM sources WHERE status = 'skipped'"
    ).fetchone()["cnt"]
    errors = db.execute(
        "SELECT COUNT(*) as cnt FROM sources WHERE status = 'error'"
    ).fetchone()["cnt"]
    online = db.execute(
        "SELECT COUNT(*) as cnt FROM sources WHERE online_only = 1 AND status = 'new'"
    ).fetchone()["cnt"]

    print(f"  Ready to ingest: {total}")
    print(f"  Already complete: {complete}")
    print(f"  Skipped: {skipped}")
    if errors:
        print(f"  Errors: {errors}")
    if online:
        print(f"  Waiting for sync: {online}")

    db.close()




# --- Link ---

def cmd_link() -> None:
    """Create symlink from OpenClaw memory to the Cortex store.

    Run this AFTER ingest is complete. Each new file in the symlinked
    directory triggers an OpenClaw re-index, so linking during bulk
    ingest would cause constant re-indexing churn.
    """
    store = get_store_path()
    if _setup_symlink(store):
        print("\nCortex store is now linked to OpenClaw memory.")
        print("OpenClaw agents can navigate via: Knowledge Base/index.md")


# --- Rebuild Index ---

def _parse_frontmatter(file_path: Path) -> dict | None:
    """Parse YAML frontmatter from a markdown file."""
    try:
        content = file_path.read_text()
    except (OSError, UnicodeDecodeError):
        return None

    if not content.startswith("---\n"):
        return None

    end = content.find("\n---\n", 4)
    if end == -1:
        if content.endswith("\n---"):
            end = len(content) - 4
        else:
            return None

    try:
        result = yaml.safe_load(content[4:end])
    except yaml.YAMLError:
        return None

    if not isinstance(result, dict):
        return None
    return result


def cmd_rebuild_index() -> None:
    """Regenerate all indexes from knowledge page frontmatter."""
    store = get_store_path()

    category_entries: dict[str, list[tuple[str, str, str]]] = {
        cat: [] for cat in KNOWLEDGE_CATEGORIES
    }
    errors = []

    for cat in KNOWLEDGE_CATEGORIES:
        cat_dir = store / cat
        if not cat_dir.is_dir():
            continue
        for page in sorted(cat_dir.iterdir()):
            if page.name == "index.md" or page.suffix != ".md":
                continue
            fm = _parse_frontmatter(page)
            if fm is None:
                errors.append(f"No frontmatter: {cat}/{page.name}")
                continue
            title = fm.get("title", page.stem.replace("-", " ").title())
            tags = fm.get("tags", [])
            desc = ", ".join(tags[:3]) if tags else ""
            category_entries[cat].append((page.name, title, desc))

    # Write category indexes
    total_pages = 0
    for cat, entries in category_entries.items():
        total_pages += len(entries)
        cat_title = cat.title().replace("-", " ")
        lines = [f"# {cat_title}\n"]
        if entries:
            for rel_path, title, desc in entries:
                suffix = f" — {desc}" if desc else ""
                lines.append(f"- [{title}]({rel_path}){suffix}")
        else:
            lines.append("_No entries yet._")
        cat_index = store / cat / "index.md"
        cat_index.write_text("\n".join(lines) + "\n")

    # Write root index
    db_path = get_db_path()
    completed_count = 0
    if db_path.exists():
        db = get_db()
        completed_count = db.execute(
            "SELECT COUNT(*) as cnt FROM sources WHERE status = 'complete'"
        ).fetchone()["cnt"]
        db.close()

    lines = [
        "# Cortex Index\n",
        f"Last updated: {_today()}",
        f"Total pages: {total_pages} | Sources ingested: {completed_count}\n",
        "## Categories\n",
        "| Category | Pages | Index |",
        "|----------|-------|-------|",
    ]
    for cat in KNOWLEDGE_CATEGORIES:
        cat_title = cat.title().replace("-", " ")
        count = len(category_entries[cat])
        lines.append(f"| {cat_title} | {count} | [{cat}/index.md]({cat}/index.md) |")

    # Preserve recent activity from existing index
    existing_index = store / "index.md"
    if existing_index.exists():
        content = existing_index.read_text()
        activity_match = re.search(r"## Recent Activity.*", content, re.DOTALL)
        if activity_match:
            lines.append("")
            lines.append(activity_match.group(0).rstrip())
    else:
        lines.append("\n## Recent Activity\n")
        lines.append("_No activity yet._")

    existing_index.write_text("\n".join(lines) + "\n")

    print(f"Rebuilt indexes: {total_pages} pages across {len(KNOWLEDGE_CATEGORIES)} categories")
    if errors:
        print(f"\nWarnings ({len(errors)}):")
        for e in errors:
            print(f"  - {e}")


# --- Helpers ---

def _today() -> str:
    """Current date as YYYY-MM-DD."""
    from datetime import datetime, timezone
    return datetime.now(timezone.utc).strftime("%Y-%m-%d")


def _now() -> str:
    """Current timestamp as ISO 8601."""
    from datetime import datetime, timezone
    return datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")


# --- CLI router ---

USAGE = """\
Usage: cortex <command> [args]

Commands:
  setup                     Detect cloud storage, initialize store
  status                    Show store statistics
  scan <dir>                Discover files, classify, hash, store in SQLite
  triage                    Pre-filter low-value files in the database
  plan                      Show files grouped by directory, sorted oldest-first
  rebuild-index             Regenerate all indexes from page frontmatter
  link                      Symlink store into OpenClaw memory (run AFTER ingest)

For document extraction (PDF, DOCX, PPTX, etc.), use docling directly:
  docling convert <file> --format md
  Install: uv tool install docling

Cortex is a personal knowledge compiler. The CLI handles bulk mechanical
operations (scanning, triage, indexing). The LLM handles knowledge
compilation by following schema.md instructions.\
"""


def main() -> None:
    args = sys.argv[1:]
    if not args or args[0] in ("-h", "--help", "help"):
        print(USAGE)
        sys.exit(0)

    cmd = args[0]

    if cmd == "setup":
        cmd_setup()
    elif cmd == "status":
        cmd_status()
    elif cmd == "scan":
        if len(args) < 2:
            error("Missing directory path", "Usage: cortex scan <dir>")
        cmd_scan(args[1])
    elif cmd == "triage":
        cmd_triage()
    elif cmd == "plan":
        cmd_plan()
    elif cmd == "rebuild-index":
        cmd_rebuild_index()
    elif cmd == "link":
        cmd_link()
    else:
        error(f"Unknown command: {cmd}", "Run `cortex help` for available commands.")


if __name__ == "__main__":
    try:
        main()
    except KeyboardInterrupt:
        print("\nInterrupted.", file=sys.stderr)
        sys.exit(130)
    except Exception as e:
        print(f"cortex: unexpected error: {e}", file=sys.stderr)
        print("If this persists, delete ~/.config/cortex/config and run `cortex setup`.", file=sys.stderr)
        sys.exit(1)
