from __future__ import annotations

from pathlib import Path
import json
import sqlite3
from typing import Any


SCHEMA = """
CREATE TABLE IF NOT EXISTS conversations (
    conversation_id TEXT PRIMARY KEY,
    source TEXT NOT NULL,
    title TEXT NOT NULL,
    created_at TEXT,
    updated_at TEXT,
    raw_path TEXT,
    metadata_json TEXT
);

CREATE TABLE IF NOT EXISTS messages (
    message_id TEXT PRIMARY KEY,
    conversation_id TEXT NOT NULL,
    source TEXT NOT NULL,
    role TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TEXT,
    metadata_json TEXT
);

CREATE TABLE IF NOT EXISTS chunks (
    chunk_id TEXT PRIMARY KEY,
    conversation_id TEXT NOT NULL,
    source TEXT NOT NULL,
    title TEXT NOT NULL,
    message_start INTEGER NOT NULL,
    message_end INTEGER NOT NULL,
    text TEXT NOT NULL,
    created_at TEXT,
    updated_at TEXT,
    metadata_json TEXT
);

CREATE VIRTUAL TABLE IF NOT EXISTS chunks_fts USING fts5(
    chunk_id UNINDEXED,
    conversation_id UNINDEXED,
    source UNINDEXED,
    title,
    text,
    content='chunks',
    content_rowid='rowid'
);
"""


def build_index(db_path: Path, conversations: list[dict[str, Any]], messages: list[dict[str, Any]], chunks: list[dict[str, Any]]) -> None:
    db_path.parent.mkdir(parents=True, exist_ok=True)
    if db_path.exists():
        db_path.unlink()
    conn = sqlite3.connect(db_path)
    try:
        conn.executescript(SCHEMA)
        for conversation in conversations:
            conn.execute(
                "INSERT OR REPLACE INTO conversations VALUES (?, ?, ?, ?, ?, ?, ?)",
                (
                    conversation.get("conversation_id"),
                    conversation.get("source"),
                    conversation.get("title"),
                    conversation.get("created_at"),
                    conversation.get("updated_at"),
                    conversation.get("raw_path"),
                    json.dumps(conversation.get("metadata", {}), ensure_ascii=False),
                ),
            )
        for message in messages:
            conn.execute(
                "INSERT OR REPLACE INTO messages VALUES (?, ?, ?, ?, ?, ?, ?)",
                (
                    message.get("message_id"),
                    message.get("conversation_id"),
                    message.get("source"),
                    message.get("role"),
                    message.get("content"),
                    message.get("created_at"),
                    json.dumps(message.get("metadata", {}), ensure_ascii=False),
                ),
            )
        for chunk in chunks:
            conn.execute(
                "INSERT OR REPLACE INTO chunks VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                (
                    chunk.get("chunk_id"),
                    chunk.get("conversation_id"),
                    chunk.get("source"),
                    chunk.get("title"),
                    chunk.get("message_start"),
                    chunk.get("message_end"),
                    chunk.get("text"),
                    chunk.get("created_at"),
                    chunk.get("updated_at"),
                    json.dumps(chunk.get("metadata", {}), ensure_ascii=False),
                ),
            )
        conn.execute("INSERT INTO chunks_fts(chunks_fts) VALUES('rebuild')")
        conn.commit()
    finally:
        conn.close()