//! SQLite-backed index and event log.
//!
//! Markdown stays the source of truth for notes; this database is a *derived*
//! cache plus an append-only event log for things that have no natural home in
//! a note (who changed what, when, from which surface). Deleting the file is
//! always safe: everything in `notes`/`links` is rebuilt from the vault on the
//! next scan, and only `events` is genuine history.

use std::path::Path;
use std::time::{Duration, SystemTime, UNIX_EPOCH};

use rusqlite::{params, Connection, OptionalExtension};
use serde::{Deserialize, Serialize};
use serde_json::Value;

use crate::vault::{NoteEntry, SearchHit, VaultError};

const DB_DIR: &str = ".tasklens";
const DB_FILE: &str = "tasklens.db";
const SCHEMA_VERSION: i64 = 1;
const BUSY_TIMEOUT: Duration = Duration::from_secs(5);

/// Where the index lives for a vault.
pub fn database_path(root: &Path) -> std::path::PathBuf {
    root.join(DB_DIR).join(DB_FILE)
}

fn now_ms() -> i64 {
    SystemTime::now()
        .duration_since(UNIX_EPOCH)
        .map(|duration| duration.as_millis() as i64)
        .unwrap_or(0)
}

fn to_error(problem: rusqlite::Error) -> VaultError {
    VaultError(format!("索引数据库: {problem}"))
}

/// A parsed note plus its body, the unit the index stores.
pub struct IndexedNote {
    pub entry: NoteEntry,
    pub body: String,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct TaskEvent {
    pub id: i64,
    pub path: String,
    pub kind: String,
    pub detail: String,
    pub at_ms: i64,
    pub source: String,
}

impl TaskEvent {
    /// Detail parsed as JSON, so callers do not have to guess the shape.
    pub fn fields(&self) -> Value {
        serde_json::from_str(&self.detail).unwrap_or(Value::Null)
    }
}

pub struct Db {
    conn: Connection,
}

impl Db {
    pub fn open(root: &Path) -> Result<Db, VaultError> {
        let directory = root.join(DB_DIR);
        std::fs::create_dir_all(&directory)?;
        let conn = Connection::open(directory.join(DB_FILE)).map_err(to_error)?;
        conn.busy_timeout(BUSY_TIMEOUT).map_err(to_error)?;
        conn.execute_batch(
            "PRAGMA journal_mode = WAL;
             PRAGMA synchronous = NORMAL;",
        )
        .map_err(to_error)?;
        let db = Db { conn };
        db.migrate()?;
        Ok(db)
    }

    /// Open without failing the caller: the index is optional by design.
    pub fn try_open(root: &Path) -> Option<Db> {
        Db::open(root).ok()
    }

    fn migrate(&self) -> Result<(), VaultError> {
        self.conn
            .execute_batch(
                "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
                 CREATE TABLE IF NOT EXISTS notes (
                     path TEXT PRIMARY KEY,
                     name TEXT NOT NULL,
                     folder TEXT NOT NULL,
                     title TEXT,
                     note_type TEXT,
                     frontmatter TEXT NOT NULL,
                     links TEXT NOT NULL,
                     body TEXT NOT NULL,
                     snippet TEXT NOT NULL,
                     modified_ms INTEGER NOT NULL,
                     size INTEGER NOT NULL,
                     words INTEGER NOT NULL,
                     open_tasks INTEGER NOT NULL,
                     done_tasks INTEGER NOT NULL,
                     is_template INTEGER NOT NULL
                 );
                 CREATE INDEX IF NOT EXISTS notes_type ON notes(note_type);
                 CREATE TABLE IF NOT EXISTS links (
                     source TEXT NOT NULL,
                     target TEXT NOT NULL,
                     PRIMARY KEY (source, target)
                 );
                 CREATE INDEX IF NOT EXISTS links_target ON links(target);
                 CREATE TABLE IF NOT EXISTS events (
                     id INTEGER PRIMARY KEY AUTOINCREMENT,
                     path TEXT NOT NULL,
                     kind TEXT NOT NULL,
                     detail TEXT NOT NULL,
                     at_ms INTEGER NOT NULL,
                     source TEXT NOT NULL
                 );
                 CREATE INDEX IF NOT EXISTS events_path ON events(path, at_ms DESC);",
            )
            .map_err(to_error)?;

        let version: Option<String> = self
            .conn
            .query_row("SELECT value FROM meta WHERE key = 'schema_version'", [], |row| {
                row.get(0)
            })
            .optional()
            .map_err(to_error)?;

        match version.and_then(|value| value.parse::<i64>().ok()) {
            Some(SCHEMA_VERSION) => Ok(()),
            Some(_) => {
                // Derived tables are disposable; rebuild them, keep events.
                self.conn
                    .execute_batch("DELETE FROM notes; DELETE FROM links;")
                    .map_err(to_error)?;
                self.write_version()
            }
            None => self.write_version(),
        }
    }

    fn write_version(&self) -> Result<(), VaultError> {
        self.conn
            .execute(
                "INSERT INTO meta (key, value) VALUES ('schema_version', ?1)
                 ON CONFLICT(key) DO UPDATE SET value = excluded.value",
                params![SCHEMA_VERSION.to_string()],
            )
            .map_err(to_error)?;
        Ok(())
    }

    /// (path, modified_ms, size) for every indexed note — the delta baseline.
    pub fn file_states(&self) -> Result<Vec<(String, i64, u64)>, VaultError> {
        let mut statement = self
            .conn
            .prepare("SELECT path, modified_ms, size FROM notes")
            .map_err(to_error)?;
        let rows = statement
            .query_map([], |row| {
                Ok((row.get::<_, String>(0)?, row.get::<_, i64>(1)?, row.get::<_, u64>(2)?))
            })
            .map_err(to_error)?;
        let mut states = Vec::new();
        for row in rows {
            states.push(row.map_err(to_error)?);
        }
        Ok(states)
    }

    pub fn put(&self, note: &IndexedNote) -> Result<(), VaultError> {
        let entry = &note.entry;
        self.conn
            .execute(
                "INSERT INTO notes (
                     path, name, folder, title, note_type, frontmatter, links, body, snippet,
                     modified_ms, size, words, open_tasks, done_tasks, is_template
                 ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15)
                 ON CONFLICT(path) DO UPDATE SET
                     name = excluded.name, folder = excluded.folder, title = excluded.title,
                     note_type = excluded.note_type, frontmatter = excluded.frontmatter,
                     links = excluded.links, body = excluded.body, snippet = excluded.snippet,
                     modified_ms = excluded.modified_ms, size = excluded.size, words = excluded.words,
                     open_tasks = excluded.open_tasks, done_tasks = excluded.done_tasks,
                     is_template = excluded.is_template",
                params![
                    entry.path,
                    entry.name,
                    entry.folder,
                    entry.title,
                    entry.note_type,
                    serde_json::to_string(&entry.frontmatter).unwrap_or_else(|_| "{}".to_string()),
                    serde_json::to_string(&entry.links).unwrap_or_else(|_| "[]".to_string()),
                    note.body,
                    entry.snippet,
                    entry.modified_ms,
                    entry.size as i64,
                    entry.words,
                    entry.open_tasks,
                    entry.done_tasks,
                    i64::from(entry.is_template),
                ],
            )
            .map_err(to_error)?;

        self.conn
            .execute("DELETE FROM links WHERE source = ?1", params![entry.path])
            .map_err(to_error)?;
        for target in &entry.links {
            self.conn
                .execute(
                    "INSERT OR IGNORE INTO links (source, target) VALUES (?1, ?2)",
                    params![entry.path, target],
                )
                .map_err(to_error)?;
        }
        Ok(())
    }

    /// Drop a single note from the index (rename, trash).
    pub fn remove(&self, path: &str) -> Result<(), VaultError> {
        self.conn
            .execute("DELETE FROM notes WHERE path = ?1", params![path])
            .map_err(to_error)?;
        self.conn
            .execute("DELETE FROM links WHERE source = ?1", params![path])
            .map_err(to_error)?;
        Ok(())
    }

    /// Drop index rows for paths that no longer exist in the vault.
    pub fn retain_only(&self, paths: &[String]) -> Result<(), VaultError> {
        let live: std::collections::HashSet<&str> = paths.iter().map(String::as_str).collect();
        let mut statement = self
            .conn
            .prepare("SELECT path FROM notes")
            .map_err(to_error)?;
        let rows = statement
            .query_map([], |row| row.get::<_, String>(0))
            .map_err(to_error)?;
        let mut stale = Vec::new();
        for row in rows {
            let path = row.map_err(to_error)?;
            if !live.contains(path.as_str()) {
                stale.push(path);
            }
        }
        for path in stale {
            self.remove(&path)?;
        }
        Ok(())
    }

    pub fn entries(&self) -> Result<Vec<NoteEntry>, VaultError> {
        let mut statement = self
            .conn
            .prepare(
                "SELECT path, name, folder, title, note_type, frontmatter, links, snippet,
                        modified_ms, size, words, open_tasks, done_tasks, is_template
                 FROM notes ORDER BY modified_ms DESC, path ASC",
            )
            .map_err(to_error)?;
        let rows = statement
            .query_map([], |row| {
                Ok(NoteEntry {
                    path: row.get(0)?,
                    name: row.get(1)?,
                    folder: row.get(2)?,
                    title: row.get(3)?,
                    note_type: row.get(4)?,
                    frontmatter: row
                        .get::<_, String>(5)
                        .ok()
                        .and_then(|text| serde_json::from_str(&text).ok())
                        .unwrap_or(Value::Object(Default::default())),
                    links: row
                        .get::<_, String>(6)
                        .ok()
                        .and_then(|text| serde_json::from_str(&text).ok())
                        .unwrap_or_default(),
                    snippet: row.get(7)?,
                    modified_ms: row.get(8)?,
                    size: row.get::<_, i64>(9)? as u64,
                    words: row.get(10)?,
                    open_tasks: row.get(11)?,
                    done_tasks: row.get(12)?,
                    is_template: row.get::<_, i64>(13)? != 0,
                })
            })
            .map_err(to_error)?;
        let mut entries = Vec::new();
        for row in rows {
            entries.push(row.map_err(to_error)?);
        }
        Ok(entries)
    }

    /// Full-text search over the indexed bodies: no filesystem reads.
    pub fn search(&self, query: &str, limit: usize) -> Result<Vec<SearchHit>, VaultError> {
        let needle = query.trim().to_lowercase();
        if needle.is_empty() {
            return Ok(Vec::new());
        }
        let pattern = format!("%{}%", escape_like(&needle));
        let mut statement = self
            .conn
            .prepare(
                "SELECT path, name, title, note_type, body FROM notes
                 WHERE lower(title) LIKE ?1 ESCAPE '\\'
                    OR lower(name) LIKE ?1 ESCAPE '\\'
                    OR lower(body) LIKE ?1 ESCAPE '\\'
                 ORDER BY modified_ms DESC
                 LIMIT ?2",
            )
            .map_err(to_error)?;
        let rows = statement
            .query_map(params![pattern, (limit * 4) as i64], |row| {
                Ok((
                    row.get::<_, String>(0)?,
                    row.get::<_, String>(1)?,
                    row.get::<_, Option<String>>(2)?,
                    row.get::<_, Option<String>>(3)?,
                    row.get::<_, String>(4)?,
                ))
            })
            .map_err(to_error)?;

        let mut hits = Vec::new();
        for row in rows {
            let (path, name, title, note_type, body) = row.map_err(to_error)?;
            if hits.len() >= limit {
                break;
            }
            let label = title.clone().unwrap_or_else(|| name.clone());
            if label.to_lowercase().contains(&needle) {
                hits.push(SearchHit {
                    path: path.clone(),
                    title: label.clone(),
                    note_type: note_type.clone(),
                    line: 0,
                    text: label.clone(),
                });
            }
            for (index, line) in body.lines().enumerate() {
                if hits.len() >= limit {
                    break;
                }
                if line.to_lowercase().contains(&needle) {
                    hits.push(SearchHit {
                        path: path.clone(),
                        title: label.clone(),
                        note_type: note_type.clone(),
                        line: index + 1,
                        text: line.trim().chars().take(160).collect(),
                    });
                }
            }
        }
        Ok(hits)
    }

    pub fn record_event(
        &self,
        path: &str,
        kind: &str,
        detail: &str,
        source: &str,
    ) -> Result<(), VaultError> {
        self.conn
            .execute(
                "INSERT INTO events (path, kind, detail, at_ms, source) VALUES (?1, ?2, ?3, ?4, ?5)",
                params![path, kind, detail, now_ms(), source],
            )
            .map_err(to_error)?;
        Ok(())
    }

    pub fn events(&self, path: Option<&str>, limit: usize) -> Result<Vec<TaskEvent>, VaultError> {
        let limit = if limit == 0 { 200 } else { limit } as i64;
        let mut events = Vec::new();
        match path {
            Some(path) => {
                let mut statement = self
                    .conn
                    .prepare(
                        "SELECT id, path, kind, detail, at_ms, source FROM events
                         WHERE path = ?1 ORDER BY at_ms DESC, id DESC LIMIT ?2",
                    )
                    .map_err(to_error)?;
                let rows = statement
                    .query_map(params![path, limit], map_event)
                    .map_err(to_error)?;
                for row in rows {
                    events.push(row.map_err(to_error)?);
                }
            }
            None => {
                let mut statement = self
                    .conn
                    .prepare(
                        "SELECT id, path, kind, detail, at_ms, source FROM events
                         ORDER BY at_ms DESC, id DESC LIMIT ?1",
                    )
                    .map_err(to_error)?;
                let rows = statement.query_map(params![limit], map_event).map_err(to_error)?;
                for row in rows {
                    events.push(row.map_err(to_error)?);
                }
            }
        }
        Ok(events)
    }
}

fn map_event(row: &rusqlite::Row<'_>) -> rusqlite::Result<TaskEvent> {
    Ok(TaskEvent {
        id: row.get(0)?,
        path: row.get(1)?,
        kind: row.get(2)?,
        detail: row.get(3)?,
        at_ms: row.get(4)?,
        source: row.get(5)?,
    })
}

fn escape_like(text: &str) -> String {
    text.replace('\\', "\\\\").replace('%', "\\%").replace('_', "\\_")
}