#!/usr/bin/env python3
"""
Seed the database with ethalon investigation dialogues.

Reads all JSON files from seeds/investigations/, creates one project per
unique repo, one chat per file, and inserts the full message history
(user → tool steps → final assistant answer).

Safe to run multiple times: existing seed projects/chats are skipped.

Usage:
    python scripts/seed_investigations.py [--reset]

Flags:
    --reset   Delete all existing seed data before inserting (idempotent fresh start).
"""

import json
import os
import re
import sqlite3
import sys
from pathlib import Path

# ── Paths ──────────────────────────────────────────────────────────────────
ROOT = Path(__file__).resolve().parent.parent
SEEDS_DIR = ROOT / "seeds" / "investigations"

# Respect AI_INSIDER_DATA_DIR env var (set by launcher for production binary)
_data_dir_env = os.environ.get("AI_INSIDER_DATA_DIR")
if _data_dir_env:
    DB_PATH = Path(_data_dir_env) / "app.db"
else:
    DB_PATH = ROOT / "data" / "app.db"

APP_VERSION = "0.9.1744"

RESET = "--reset" in sys.argv


def _extract_done_payload(tool_calls: list) -> dict | None:
    """Extract payload from a Done tool_call's arguments, if present."""
    for tc in tool_calls or []:
        fn = tc.get("function", {})
        if fn.get("name") == "Done":
            try:
                return json.loads(fn["arguments"])
            except (json.JSONDecodeError, KeyError):
                pass
    return None


def parse_investigation(path: Path) -> dict:
    """Parse an ethalon investigation JSON into seed-ready structure."""
    data = json.loads(path.read_text(encoding="utf-8"))
    messages = data["messages"]

    # Filename: {owner}--{repo}--q{n}.json
    stem = path.stem  # e.g. "AFNetworking--AFNetworking--q10"
    m = re.match(r"^(.+?)--(.+?)--q(\d+)$", stem)
    if m:
        owner, repo, qnum = m.group(1), m.group(2), int(m.group(3))
        project_name = f"{owner}/{repo}"
    else:
        project_name = stem
        qnum = 1

    # Extract user question
    user_msg = next((msg for msg in messages if msg["role"] == "user"), None)
    question = user_msg["content"] if user_msg else "Investigation"

    # Chat title = first 80 chars of question
    title = question[:80].strip()

    # Build seed messages
    seed_messages = []
    for msg in messages:
        role = msg["role"]

        if role == "system":
            continue  # app doesn't store system messages

        if role == "tool":
            continue  # tool results stripped from seed files

        if role == "user":
            seed_messages.append({
                "role": "user",
                "content": msg["content"],
            })
            continue

        if role == "assistant":
            tool_calls = msg.get("tool_calls")
            payload = _extract_done_payload(tool_calls)

            if payload:
                # Final answer: assistant called Done — store as answer message
                sources = payload.get("sources", [])
                normalized_sources = [
                    {
                        "file_path": src.get("file_path", src.get("path", "")),
                        "line_numbers": src.get("line_numbers", src.get("lines", "")),
                        "relevance": src.get("relevance", ""),
                    }
                    for src in sources
                ]
                seed_messages.append({
                    "role": "assistant",
                    "content": payload.get("answer", ""),
                    "sources": json.dumps(normalized_sources),
                    "confidence": payload.get("confidence", "medium"),
                    "confidence_reasoning": payload.get("confidence_reasoning", ""),
                })
            else:
                # Intermediate step
                seed_messages.append({
                    "role": "assistant",
                    "content": msg.get("content"),
                    "tool_calls": json.dumps(tool_calls) if tool_calls else None,
                })
            continue

    return {
        "project_name": project_name,
        "question": question,
        "title": title,
        "messages": seed_messages,
    }


def seed(db_path: Path, seeds_dir: Path) -> None:
    if not db_path.exists():
        print(f"✗  Database not found at {db_path}")
        print("   Run 'make dev' or 'make start' first to initialise the DB.")
        sys.exit(1)

    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA foreign_keys = ON")

    if RESET:
        print("→  --reset: removing existing seed data …")
        conn.execute(
            "DELETE FROM projects WHERE path LIKE '/demo/%'",
        )
        conn.commit()
        print("   Done.\n")

    files = sorted(seeds_dir.glob("*.json"))
    if not files:
        print(f"✗  No JSON files found in {seeds_dir}")
        sys.exit(1)

    inserted_projects = 0
    inserted_chats = 0
    inserted_messages = 0
    skipped = 0

    for path in files:
        inv = parse_investigation(path)
        project_name = inv["project_name"]
        demo_path = f"/demo/{project_name}"

        # Upsert project (skip if already exists)
        row = conn.execute(
            "SELECT id FROM projects WHERE path = ?", (demo_path,)
        ).fetchone()
        if row:
            project_id = row[0]
        else:
            cur = conn.execute(
                "INSERT INTO projects (name, path, app_version) VALUES (?, ?, ?)",
                (project_name, demo_path, APP_VERSION),
            )
            project_id = cur.lastrowid
            inserted_projects += 1

        # Skip if a chat with this title already exists for the project
        existing = conn.execute(
            "SELECT id FROM chats WHERE project_id = ? AND title = ?",
            (project_id, inv["title"]),
        ).fetchone()
        if existing:
            skipped += 1
            print(f"   skip  {path.name}  (already seeded)")
            continue

        # Insert chat
        cur = conn.execute(
            "INSERT INTO chats (project_id, title, app_version) VALUES (?, ?, ?)",
            (project_id, inv["title"], APP_VERSION),
        )
        chat_id = cur.lastrowid
        inserted_chats += 1

        # Insert messages
        for msg in inv["messages"]:
            conn.execute(
                """INSERT INTO messages
                   (chat_id, role, content, tool_calls, tool_call_id,
                    tool_name, sources, confidence, confidence_reasoning, app_version)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                (
                    chat_id,
                    msg["role"],
                    msg.get("content"),
                    msg.get("tool_calls"),
                    msg.get("tool_call_id"),
                    msg.get("tool_name"),
                    msg.get("sources"),
                    msg.get("confidence"),
                    msg.get("confidence_reasoning"),
                    APP_VERSION,
                ),
            )
            inserted_messages += 1

        print(f"   ✓  {path.name}  →  project={project_name!r}  chat={inv['title'][:60]!r}")

    conn.commit()
    conn.close()

    print()
    print(f"Done. projects={inserted_projects}  chats={inserted_chats}  "
          f"messages={inserted_messages}  skipped={skipped}")


if __name__ == "__main__":
    seed(DB_PATH, SEEDS_DIR)
