//! Rebuildable SQLite cache for the SDD MCP surface.
//!
//! The cache is deliberately derived from Markdown artifacts and JSONL traces.
//! It can be deleted at any time; canonical state stays in `docs/` and `.sdd/`.

use anyhow::{Context, Result};
use rusqlite::{params, Connection, OptionalExtension};
use serde::Serialize;
use serde_json::{json, Value};
use std::collections::{BTreeMap, BTreeSet};
use std::fs;
use std::path::{Path, PathBuf};
use std::time::{SystemTime, UNIX_EPOCH};
use walkdir::WalkDir;

use crate::{contract, runtime};

const SCHEMA_VERSION: i64 = 1;
const CACHE_REL_PATH: &str = ".sdd/cache/sdd.sqlite";
const MAX_BODY_CHARS: usize = 12_000;

#[derive(Clone, Debug, Serialize)]
pub struct CacheStatus {
    pub path: String,
    pub exists: bool,
    pub fresh: bool,
    pub rebuilt: bool,
    pub reason: String,
    pub source_count: usize,
    pub orchestration_count: usize,
    pub artifact_count: usize,
    pub trace_event_count: usize,
}

#[derive(Clone, Debug, Serialize)]
pub struct SearchResult {
    pub kind: String,
    pub slug: Option<String>,
    pub stage: Option<String>,
    pub run_id: Option<String>,
    pub path: String,
    pub title: String,
    pub snippet: String,
    pub modified_ms: i64,
}

#[derive(Clone, Debug)]
struct SourceFile {
    rel_path: String,
    abs_path: PathBuf,
    modified_ms: i64,
    len: i64,
}

#[derive(Clone, Debug)]
struct SearchItem {
    kind: String,
    slug: Option<String>,
    stage: Option<String>,
    run_id: Option<String>,
    path: String,
    title: String,
    body: String,
    modified_ms: i64,
}

pub fn cache_path(root: &Path) -> PathBuf {
    root.join(CACHE_REL_PATH)
}

pub fn ensure_fresh(root: &Path) -> Result<CacheStatus> {
    let sources = collect_sources(root)?;
    let path = cache_path(root);
    let reason = stale_reason(&path, &sources)?;
    let rebuilt = if reason.is_some() {
        rebuild(root, &sources)?;
        true
    } else {
        false
    };
    let counts = read_counts(&path)?;
    Ok(CacheStatus {
        path: rel(root, &path),
        exists: path.exists(),
        fresh: true,
        rebuilt,
        reason: reason.unwrap_or_else(|| "fresh".to_string()),
        source_count: sources.len(),
        orchestration_count: counts.0,
        artifact_count: counts.1,
        trace_event_count: counts.2,
    })
}

pub fn status(root: &Path) -> Result<CacheStatus> {
    let sources = collect_sources(root)?;
    let path = cache_path(root);
    let exists = path.exists();
    let reason = stale_reason(&path, &sources)?;
    let counts = if exists {
        read_counts(&path).unwrap_or((0, 0, 0))
    } else {
        (0, 0, 0)
    };
    Ok(CacheStatus {
        path: rel(root, &path),
        exists,
        fresh: reason.is_none(),
        rebuilt: false,
        reason: reason.unwrap_or_else(|| "fresh".to_string()),
        source_count: sources.len(),
        orchestration_count: counts.0,
        artifact_count: counts.1,
        trace_event_count: counts.2,
    })
}

pub fn orchestration_slugs(root: &Path) -> Result<Vec<String>> {
    let _ = ensure_fresh(root)?;
    let conn = open_existing(root)?;
    let mut stmt =
        conn.prepare("SELECT slug FROM orchestrations ORDER BY latest_modified_ms DESC, slug")?;
    let rows = stmt.query_map([], |row| row.get::<_, String>(0))?;
    rows.collect::<std::result::Result<Vec<_>, _>>()
        .map_err(Into::into)
}

pub fn search_cached_or_direct(root: &Path, query: &str, limit: usize) -> Result<Value> {
    match search(root, query, limit) {
        Ok(results) => Ok(json!({
            "query": query,
            "limit": limit,
            "source": "cache",
            "results": results,
        })),
        Err(error) => {
            let results = search_direct(root, query, limit)?;
            Ok(json!({
                "query": query,
                "limit": limit,
                "source": "direct-fallback",
                "fallback_reason": error.to_string(),
                "results": results,
            }))
        }
    }
}

pub fn search(root: &Path, query: &str, limit: usize) -> Result<Vec<SearchResult>> {
    let _ = ensure_fresh(root)?;
    let conn = open_existing(root)?;
    query_items(&conn, query, limit)
}

pub fn search_direct(root: &Path, query: &str, limit: usize) -> Result<Vec<SearchResult>> {
    let sources = collect_sources(root)?;
    let items = build_search_items(root, &sources)?;
    let needle = query.trim().to_ascii_lowercase();
    let mut out = items
        .into_iter()
        .filter(|item| {
            needle.is_empty()
                || item.title.to_ascii_lowercase().contains(&needle)
                || item.path.to_ascii_lowercase().contains(&needle)
                || item.body.to_ascii_lowercase().contains(&needle)
        })
        .map(|item| SearchResult {
            kind: item.kind,
            slug: item.slug,
            stage: item.stage,
            run_id: item.run_id,
            path: item.path,
            title: item.title,
            snippet: snippet_for(&item.body, query),
            modified_ms: item.modified_ms,
        })
        .collect::<Vec<_>>();
    out.sort_by(|a, b| {
        b.modified_ms
            .cmp(&a.modified_ms)
            .then_with(|| a.path.cmp(&b.path))
    });
    out.truncate(limit.clamp(1, 50));
    Ok(out)
}

pub fn doctor_value(root: &Path) -> Value {
    match status(root) {
        Ok(status) => json!({
            "status": if status.exists && status.fresh { "pass" } else { "warn" },
            "cache": status,
        }),
        Err(error) => {
            json!({
                "status": "warn",
                "error": error.to_string(),
                "cache": Value::Null,
            })
        }
    }
}

fn collect_sources(root: &Path) -> Result<Vec<SourceFile>> {
    let mut files = Vec::new();
    let docs = root.join("docs");
    if docs.exists() {
        for entry in WalkDir::new(&docs)
            .max_depth(3)
            .into_iter()
            .filter_map(|entry| entry.ok())
            .filter(|entry| entry.file_type().is_file())
        {
            let path = entry.path();
            if path.extension().and_then(|value| value.to_str()) == Some("md")
                || path.file_name().and_then(|value| value.to_str())
                    == Some("traceability-map.yaml")
            {
                push_source(root, &mut files, path)?;
            }
        }
    }
    for file in runtime::trace::TRACE_FILES {
        let path = root.join(".sdd").join(file);
        if path.exists() {
            push_source(root, &mut files, &path)?;
        }
    }
    let excludes = runtime::agent_context::merged_excludes(
        &crate::domain::providers::ContextMaterializationConfig::default(),
    );
    for entry in WalkDir::new(root)
        .max_depth(4)
        .into_iter()
        .filter_entry(|entry| {
            let rel = rel(root, entry.path());
            !runtime::agent_context::path_excluded(&rel, &excludes)
        })
        .filter_map(|entry| entry.ok())
        .filter(|entry| entry.file_type().is_file())
    {
        let rel_path = rel(root, entry.path());
        if is_agent_context_source(&rel_path) && !files.iter().any(|file| file.rel_path == rel_path)
        {
            push_source(root, &mut files, entry.path())?;
        }
    }
    files.sort_by(|a, b| a.rel_path.cmp(&b.rel_path));
    Ok(files)
}

fn push_source(root: &Path, files: &mut Vec<SourceFile>, path: &Path) -> Result<()> {
    let metadata =
        fs::metadata(path).with_context(|| format!("reading metadata {}", path.display()))?;
    files.push(SourceFile {
        rel_path: rel(root, path),
        abs_path: path.to_path_buf(),
        modified_ms: modified_ms(&metadata),
        len: metadata.len() as i64,
    });
    Ok(())
}

fn stale_reason(path: &Path, sources: &[SourceFile]) -> Result<Option<String>> {
    if !path.exists() {
        return Ok(Some("missing-cache".to_string()));
    }
    let conn = Connection::open(path)?;
    let version: i64 = conn.pragma_query_value(None, "user_version", |row| row.get(0))?;
    if version != SCHEMA_VERSION {
        return Ok(Some(format!("schema-version-{version}")));
    }
    let count: i64 = conn.query_row("SELECT COUNT(*) FROM sources", [], |row| row.get(0))?;
    if count as usize != sources.len() {
        return Ok(Some("source-count-changed".to_string()));
    }
    let mut stmt = conn.prepare("SELECT modified_ms, len FROM sources WHERE path = ?1")?;
    for source in sources {
        let stored = stmt
            .query_row(params![source.rel_path.as_str()], |row| {
                Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?))
            })
            .optional()?;
        if stored != Some((source.modified_ms, source.len)) {
            return Ok(Some(format!("source-changed:{}", source.rel_path)));
        }
    }
    Ok(None)
}

fn rebuild(root: &Path, sources: &[SourceFile]) -> Result<()> {
    let path = cache_path(root);
    if let Some(parent) = path.parent() {
        fs::create_dir_all(parent)?;
    }
    let mut conn = Connection::open(&path)?;
    conn.pragma_update(None, "journal_mode", "WAL")?;
    conn.pragma_update(None, "busy_timeout", 2_000)?;
    conn.pragma_update(None, "user_version", SCHEMA_VERSION)?;
    create_schema(&conn)?;
    let tx = conn.transaction()?;
    tx.execute("DELETE FROM sources", [])?;
    tx.execute("DELETE FROM orchestrations", [])?;
    tx.execute("DELETE FROM artifacts", [])?;
    tx.execute("DELETE FROM trace_events", [])?;
    tx.execute("DELETE FROM search_items", [])?;
    for source in sources {
        tx.execute(
            "INSERT INTO sources(path, modified_ms, len) VALUES (?1, ?2, ?3)",
            params![source.rel_path.as_str(), source.modified_ms, source.len],
        )?;
    }
    let items = build_search_items(root, sources)?;
    let mut orchestrations = BTreeMap::<String, (usize, i64)>::new();
    for item in &items {
        if item.kind == "artifact" {
            if let Some(slug) = item.slug.as_deref() {
                let entry = orchestrations.entry(slug.to_string()).or_insert((0, 0));
                entry.0 += 1;
                entry.1 = entry.1.max(item.modified_ms);
                tx.execute(
                    "INSERT INTO artifacts(slug, stage, path, title, modified_ms, size) VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
                    params![
                        slug,
                        item.stage.as_deref().unwrap_or(""),
                        item.path.as_str(),
                        item.title.as_str(),
                        item.modified_ms,
                        item.body.len() as i64
                    ],
                )?;
            }
        }
        if item.kind == "trace" {
            tx.execute(
                "INSERT INTO trace_events(run_id, orchestration, stage, kind, status, target, ts, body) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
                params![
                    item.run_id.as_deref().unwrap_or(""),
                    item.slug.as_deref(),
                    item.stage.as_deref(),
                    item.title.as_str(),
                    "",
                    item.path.as_str(),
                    item.modified_ms.to_string(),
                    item.body.as_str()
                ],
            )?;
        }
        tx.execute(
            "INSERT INTO search_items(kind, slug, stage, run_id, path, title, body, modified_ms) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
            params![
                item.kind.as_str(),
                item.slug.as_deref(),
                item.stage.as_deref(),
                item.run_id.as_deref(),
                item.path.as_str(),
                item.title.as_str(),
                item.body.as_str(),
                item.modified_ms
            ],
        )?;
    }
    for (slug, (artifact_count, latest_modified_ms)) in orchestrations {
        tx.execute(
            "INSERT INTO orchestrations(slug, path, artifact_count, latest_modified_ms) VALUES (?1, ?2, ?3, ?4)",
            params![
                slug,
                format!("docs/{slug}"),
                artifact_count as i64,
                latest_modified_ms
            ],
        )?;
    }
    tx.execute(
        "INSERT OR REPLACE INTO meta(key, value) VALUES ('rebuilt_at_ms', ?1)",
        params![now_ms().to_string()],
    )?;
    tx.commit()?;
    Ok(())
}

fn create_schema(conn: &Connection) -> Result<()> {
    conn.execute_batch(
        r#"
        CREATE TABLE IF NOT EXISTS meta(key TEXT PRIMARY KEY, value TEXT NOT NULL);
        CREATE TABLE IF NOT EXISTS sources(path TEXT PRIMARY KEY, modified_ms INTEGER NOT NULL, len INTEGER NOT NULL);
        CREATE TABLE IF NOT EXISTS orchestrations(slug TEXT PRIMARY KEY, path TEXT NOT NULL, artifact_count INTEGER NOT NULL, latest_modified_ms INTEGER NOT NULL);
        CREATE TABLE IF NOT EXISTS artifacts(slug TEXT NOT NULL, stage TEXT NOT NULL, path TEXT NOT NULL, title TEXT NOT NULL, modified_ms INTEGER NOT NULL, size INTEGER NOT NULL);
        CREATE TABLE IF NOT EXISTS trace_events(run_id TEXT NOT NULL, orchestration TEXT, stage TEXT, kind TEXT NOT NULL, status TEXT, target TEXT, ts TEXT, body TEXT NOT NULL);
        CREATE TABLE IF NOT EXISTS search_items(kind TEXT NOT NULL, slug TEXT, stage TEXT, run_id TEXT, path TEXT NOT NULL, title TEXT NOT NULL, body TEXT NOT NULL, modified_ms INTEGER NOT NULL);
        CREATE INDEX IF NOT EXISTS idx_search_items_modified ON search_items(modified_ms DESC);
        CREATE INDEX IF NOT EXISTS idx_search_items_slug ON search_items(slug);
        "#,
    )?;
    Ok(())
}

fn build_search_items(root: &Path, sources: &[SourceFile]) -> Result<Vec<SearchItem>> {
    let mut items = Vec::new();
    let artifact_files = sources
        .iter()
        .filter(|source| source.rel_path.starts_with("docs/"))
        .filter(|source| source.rel_path.ends_with(".md"))
        .collect::<Vec<_>>();
    for source in artifact_files {
        let Some((slug, filename)) = docs_slug_and_file(&source.rel_path) else {
            continue;
        };
        let Some(stage) = Path::new(filename)
            .file_stem()
            .and_then(|stem| stem.to_str())
            .and_then(contract::stage_by_filename_stem)
        else {
            continue;
        };
        let text = read_redacted_limited(&source.abs_path)?;
        items.push(SearchItem {
            kind: "artifact".to_string(),
            slug: Some(slug.to_string()),
            stage: Some(stage.key.to_string()),
            run_id: None,
            path: source.rel_path.clone(),
            title: markdown_title(&text).unwrap_or_else(|| stage.label.to_string()),
            body: text,
            modified_ms: source.modified_ms,
        });
    }
    for source in sources
        .iter()
        .filter(|source| is_agent_context_source(&source.rel_path))
    {
        let text = read_redacted_limited(&source.abs_path)?;
        let (slug, title) = if let Some((slug, _)) = docs_slug_and_file(&source.rel_path) {
            (
                Some(slug.to_string()),
                markdown_title(&text).unwrap_or_else(|| "Context Recommendations".to_string()),
            )
        } else {
            (
                None,
                markdown_title(&text).unwrap_or_else(|| source.rel_path.clone()),
            )
        };
        items.push(SearchItem {
            kind: "context".to_string(),
            slug,
            stage: None,
            run_id: None,
            path: source.rel_path.clone(),
            title,
            body: text,
            modified_ms: source.modified_ms,
        });
    }
    for event in runtime::trace::read_events(root)? {
        let body = runtime::redaction::redact_text(&serde_json::to_string(&event.to_value())?);
        items.push(SearchItem {
            kind: "trace".to_string(),
            slug: event.orchestration.clone(),
            stage: event.stage.clone(),
            run_id: Some(event.run_id.clone()),
            path: format!(".sdd/{}", event.kind),
            title: event.kind,
            body: truncate_chars(&body, MAX_BODY_CHARS),
            modified_ms: parse_trace_time_ms(&event.ts).unwrap_or(0),
        });
    }
    let mut seen = BTreeSet::new();
    items.retain(|item| {
        seen.insert(format!(
            "{}:{}:{}:{}",
            item.kind,
            item.path,
            item.stage.as_deref().unwrap_or(""),
            item.run_id.as_deref().unwrap_or("")
        ))
    });
    Ok(items)
}

fn query_items(conn: &Connection, query: &str, limit: usize) -> Result<Vec<SearchResult>> {
    let limit = limit.clamp(1, 50) as i64;
    let needle = query.trim();
    let mut results = Vec::new();
    if needle.is_empty() {
        let mut stmt = conn.prepare(
            "SELECT kind, slug, stage, run_id, path, title, body, modified_ms FROM search_items ORDER BY modified_ms DESC, path LIMIT ?1",
        )?;
        let rows = stmt.query_map(params![limit], row_to_search_result)?;
        for row in rows {
            results.push(row?);
        }
    } else {
        let pattern = format!("%{}%", needle.replace('%', "\\%").replace('_', "\\_"));
        let mut stmt = conn.prepare(
            "SELECT kind, slug, stage, run_id, path, title, body, modified_ms FROM search_items WHERE title LIKE ?1 ESCAPE '\\' OR body LIKE ?1 ESCAPE '\\' OR path LIKE ?1 ESCAPE '\\' ORDER BY modified_ms DESC, path LIMIT ?2",
        )?;
        let rows = stmt.query_map(params![pattern, limit], row_to_search_result)?;
        for row in rows {
            results.push(row?);
        }
        for result in &mut results {
            result.snippet = snippet_for(&result.snippet, needle);
        }
    }
    Ok(results)
}

fn row_to_search_result(row: &rusqlite::Row<'_>) -> rusqlite::Result<SearchResult> {
    let body: String = row.get(6)?;
    Ok(SearchResult {
        kind: row.get(0)?,
        slug: row.get(1)?,
        stage: row.get(2)?,
        run_id: row.get(3)?,
        path: row.get(4)?,
        title: row.get(5)?,
        snippet: truncate_chars(&body, 320),
        modified_ms: row.get(7)?,
    })
}

fn open_existing(root: &Path) -> Result<Connection> {
    let path = cache_path(root);
    Connection::open(&path).with_context(|| format!("opening {}", path.display()))
}

fn read_counts(path: &Path) -> Result<(usize, usize, usize)> {
    let conn = Connection::open(path)?;
    let orchestrations = count_table(&conn, "orchestrations")?;
    let artifacts = count_table(&conn, "artifacts")?;
    let trace_events = count_table(&conn, "trace_events")?;
    Ok((orchestrations, artifacts, trace_events))
}

fn count_table(conn: &Connection, table: &str) -> Result<usize> {
    let sql = format!("SELECT COUNT(*) FROM {table}");
    let count: i64 = conn.query_row(&sql, [], |row| row.get(0))?;
    Ok(count as usize)
}

fn docs_slug_and_file(rel_path: &str) -> Option<(&str, &str)> {
    let mut parts = rel_path.split('/');
    if parts.next()? != "docs" {
        return None;
    }
    let slug = parts.next()?;
    let filename = parts.next()?;
    if parts.next().is_some() {
        return None;
    }
    Some((slug, filename))
}

fn is_agent_context_source(rel_path: &str) -> bool {
    matches!(rel_path, "AGENTS.md" | "CLAUDE.md")
        || rel_path.ends_with("/AGENTS.md")
        || rel_path.ends_with("/CLAUDE.md")
        || rel_path.ends_with("/00-context-recommendations.md")
}

fn markdown_title(text: &str) -> Option<String> {
    text.lines()
        .map(str::trim)
        .find_map(|line| line.strip_prefix("# ").map(str::trim))
        .filter(|line| !line.is_empty())
        .map(str::to_string)
}

fn read_redacted_limited(path: &Path) -> Result<String> {
    let text = fs::read_to_string(path).with_context(|| format!("reading {}", path.display()))?;
    Ok(truncate_chars(
        &runtime::redaction::redact_text(&text),
        MAX_BODY_CHARS,
    ))
}

fn snippet_for(text: &str, query: &str) -> String {
    let query = query.trim().to_ascii_lowercase();
    if query.is_empty() {
        return truncate_chars(text, 320);
    }
    let lower = text.to_ascii_lowercase();
    let Some(index) = lower.find(&query) else {
        return truncate_chars(text, 320);
    };
    let start = index.saturating_sub(120);
    truncate_chars(&text[start..], 320)
}

fn truncate_chars(text: &str, max: usize) -> String {
    if text.chars().count() <= max {
        return text.to_string();
    }
    text.chars().take(max).collect::<String>()
}

fn modified_ms(metadata: &fs::Metadata) -> i64 {
    metadata
        .modified()
        .ok()
        .and_then(|time| time.duration_since(UNIX_EPOCH).ok())
        .map(|duration| duration.as_millis() as i64)
        .unwrap_or(0)
}

fn parse_trace_time_ms(value: &str) -> Option<i64> {
    chrono::DateTime::parse_from_rfc3339(value)
        .ok()
        .map(|dt| dt.timestamp_millis())
}

fn now_ms() -> i64 {
    SystemTime::now()
        .duration_since(UNIX_EPOCH)
        .unwrap_or_default()
        .as_millis() as i64
}

fn rel(root: &Path, path: &Path) -> String {
    path.strip_prefix(root)
        .unwrap_or(path)
        .to_string_lossy()
        .replace('\\', "/")
}

#[cfg(test)]
mod tests {
    use super::*;
    use tempfile::tempdir;

    #[test]
    fn cache_rebuilds_when_artifact_changes() {
        let root = tempdir().unwrap();
        let docs = root.path().join("docs/demo");
        fs::create_dir_all(&docs).unwrap();
        fs::write(docs.join("02-prd.md"), "# PRD\n\nBusca inicial").unwrap();

        let first = ensure_fresh(root.path()).unwrap();
        assert!(first.rebuilt);
        assert_eq!(first.artifact_count, 1);

        let second = ensure_fresh(root.path()).unwrap();
        assert!(!second.rebuilt);

        fs::write(docs.join("02-prd.md"), "# PRD\n\nBusca alterada").unwrap();
        let third = ensure_fresh(root.path()).unwrap();
        assert!(third.rebuilt);
        assert_eq!(third.artifact_count, 1);
    }

    #[test]
    fn search_falls_back_without_cache() {
        let root = tempdir().unwrap();
        let docs = root.path().join("docs/demo");
        fs::create_dir_all(&docs).unwrap();
        fs::write(
            docs.join("03-techspec.md"),
            "# Tech Spec\n\nContrato MCP clean",
        )
        .unwrap();

        let value = search_cached_or_direct(root.path(), "MCP", 10).unwrap();
        assert_eq!(value["source"], "cache");
        assert_eq!(value["results"].as_array().unwrap().len(), 1);
    }
}
