//! Local SQLite cache: anything looked up once is read back without network.
//!
//! Lives in `~/.cache/drae/entries.db` (or `$XDG_CACHE_HOME`). It stores the
//! already-normalized entry as JSON, so it survives changes to the DLE markup
//! and lets the user search inside definitions already downloaded.

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

use anyhow::{Context, Result};
use rusqlite::{params, Connection, OptionalExtension};

use crate::model::Entry;

pub struct Cache {
    conn: Connection,
}

impl Cache {
    pub fn open(path: &Path) -> Result<Self> {
        if let Some(dir) = path.parent() {
            std::fs::create_dir_all(dir)
                .with_context(|| format!("no se pudo crear {}", dir.display()))?;
        }
        let conn = Connection::open(path)
            .with_context(|| format!("no se pudo abrir la caché en {}", path.display()))?;
        conn.execute_batch(
            "PRAGMA journal_mode = WAL;
             CREATE TABLE IF NOT EXISTS entries (
                 key        TEXT PRIMARY KEY,
                 word       TEXT NOT NULL,
                 json       TEXT NOT NULL,
                 source     TEXT NOT NULL,
                 fetched_at INTEGER NOT NULL
             );",
        )
        .context("no se pudo preparar la caché")?;
        Ok(Cache { conn })
    }

    /// Default path: `$XDG_CACHE_HOME/drae/entries.db`.
    pub fn default_path() -> PathBuf {
        let base = std::env::var_os("XDG_CACHE_HOME")
            .map(PathBuf::from)
            .or_else(|| std::env::var_os("HOME").map(|h| PathBuf::from(h).join(".cache")))
            .unwrap_or_else(|| PathBuf::from(".cache"));
        base.join("drae").join("entries.db")
    }

    pub fn get(&self, word: &str) -> Result<Option<Entry>> {
        let json: Option<String> = self
            .conn
            .query_row(
                "SELECT json FROM entries WHERE key = ?1",
                params![key(word)],
                |r| r.get(0),
            )
            .optional()?;
        // An entry written by an older version of the model is silently
        // ignored and downloaded again.
        Ok(json.and_then(|j| serde_json::from_str(&j).ok()))
    }

    pub fn put(&self, word: &str, entry: &Entry) -> Result<()> {
        self.conn.execute(
            "INSERT INTO entries (key, word, json, source, fetched_at)
             VALUES (?1, ?2, ?3, ?4, ?5)
             ON CONFLICT(key) DO UPDATE SET
                 json = excluded.json,
                 source = excluded.source,
                 fetched_at = excluded.fetched_at",
            params![
                key(word),
                entry.word,
                serde_json::to_string(entry)?,
                entry.source,
                now(),
            ],
        )?;
        Ok(())
    }

    /// Stored words, most recent first.
    pub fn list(&self, limit: usize) -> Result<Vec<(String, String, i64)>> {
        let mut stmt = self.conn.prepare(
            "SELECT word, source, fetched_at FROM entries
             ORDER BY fetched_at DESC LIMIT ?1",
        )?;
        let rows = stmt
            .query_map(params![limit as i64], |r| {
                Ok((r.get(0)?, r.get(1)?, r.get(2)?))
            })?
            .collect::<rusqlite::Result<Vec<_>>>()?;
        Ok(rows)
    }

    /// Searches for a text inside the definitions already downloaded.
    pub fn search(&self, needle: &str, limit: usize) -> Result<Vec<(String, String)>> {
        let mut stmt = self.conn.prepare(
            "SELECT word, json FROM entries
             WHERE json LIKE ?1 ESCAPE '\\'
             ORDER BY word LIMIT ?2",
        )?;
        let pattern = format!("%{}%", escape_like(needle));
        let rows = stmt
            .query_map(params![pattern, limit as i64], |r| {
                Ok((r.get(0)?, r.get(1)?))
            })?
            .collect::<rusqlite::Result<Vec<_>>>()?;
        Ok(rows)
    }

    pub fn count(&self) -> Result<i64> {
        Ok(self
            .conn
            .query_row("SELECT COUNT(*) FROM entries", [], |r| r.get(0))?)
    }

    pub fn clear(&self) -> Result<usize> {
        let n = self.conn.execute("DELETE FROM entries", [])?;
        self.conn.execute_batch("VACUUM;")?;
        Ok(n)
    }
}

/// Lookup key: lowercase, so «Casa» and «casa» share an entry.
fn key(word: &str) -> String {
    word.trim().to_lowercase()
}

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

/// Formats a Unix timestamp as `YYYY-MM-DD` without a date library, using
/// Howard Hinnant's civil-from-days algorithm.
pub fn format_date(ts: i64) -> String {
    let days = ts.div_euclid(86_400);
    let z = days + 719_468;
    let era = z.div_euclid(146_097);
    let doe = z.rem_euclid(146_097);
    let yoe = (doe - doe / 1_460 + doe / 36_524 - doe / 146_096) / 365;
    let y = yoe + era * 400;
    let doy = doe - (365 * yoe + yoe / 4 - yoe / 100);
    let mp = (5 * doy + 2) / 153;
    let d = doy - (153 * mp + 2) / 5 + 1;
    let m = if mp < 10 { mp + 3 } else { mp - 9 };
    let y = if m <= 2 { y + 1 } else { y };
    format!("{y:04}-{m:02}-{d:02}")
}

fn now() -> i64 {
    SystemTime::now()
        .duration_since(UNIX_EPOCH)
        .map(|d| d.as_secs() as i64)
        .unwrap_or(0)
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::model::Sense;

    fn entry(word: &str, text: &str) -> Entry {
        Entry {
            word: word.into(),
            etymology: None,
            senses: vec![Sense {
                text: text.into(),
                ..Default::default()
            }],
            locutions: vec![],
            conjugation: None,
            source: "dle".into(),
        }
    }

    fn cache() -> Cache {
        Cache::open(Path::new(":memory:")).unwrap()
    }

    #[test]
    fn stores_and_reads_back_an_entry() {
        let c = cache();
        c.put("casa", &entry("casa", "Edificio para habitar."))
            .unwrap();
        let got = c.get("casa").unwrap().expect("should be in the cache");
        assert_eq!(got.word, "casa");
        assert_eq!(c.count().unwrap(), 1);
    }

    #[test]
    fn lookup_is_case_insensitive() {
        let c = cache();
        c.put("Casa", &entry("casa", "Edificio.")).unwrap();
        assert!(c.get("casa").unwrap().is_some());
        assert!(c.get("CASA").unwrap().is_some());
    }

    #[test]
    fn writing_the_same_word_twice_replaces_it() {
        let c = cache();
        c.put("casa", &entry("casa", "vieja")).unwrap();
        c.put("casa", &entry("casa", "nueva")).unwrap();
        assert_eq!(c.count().unwrap(), 1);
        assert_eq!(c.get("casa").unwrap().unwrap().senses[0].text, "nueva");
    }

    #[test]
    fn searches_inside_stored_definitions() {
        let c = cache();
        c.put("casa", &entry("casa", "Edificio para habitar."))
            .unwrap();
        c.put("perro", &entry("perro", "Mamífero doméstico."))
            .unwrap();
        let hits = c.search("Edificio", 10).unwrap();
        assert_eq!(hits.len(), 1);
        assert_eq!(hits[0].0, "casa");
    }

    #[test]
    fn like_wildcards_in_the_query_are_literal() {
        let c = cache();
        c.put("casa", &entry("casa", "Edificio.")).unwrap();
        // Unescaped, "%" would match everything.
        assert!(c.search("%", 10).unwrap().is_empty());
    }

    #[test]
    fn formats_a_unix_timestamp_as_a_date() {
        assert_eq!(format_date(0), "1970-01-01");
        assert_eq!(format_date(1_757_000_000), "2025-09-04");
    }

    #[test]
    fn clear_empties_the_cache() {
        let c = cache();
        c.put("casa", &entry("casa", "Edificio.")).unwrap();
        c.clear().unwrap();
        assert_eq!(c.count().unwrap(), 0);
    }
}