use anyhow::{Context, Result};
use chrono::Utc;
use rusqlite::{params, Connection};
use serde::{Deserialize, Serialize};
use std::path::PathBuf;

use crate::scoring::{self, DecayConfig};

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Preference {
    pub id: i64,
    pub item: String,
    pub reason: String,
    pub keywords: Vec<String>,  // synonym/related-term tags for richer recall
    pub base_score: f64,        // 0–10: 0 = hard limit, 10 = strong love
    pub source: String,         // "analysis" | "manual"
    pub added_at: String,
    pub last_seen: String,      // reset on every reinforcement; drives decay clock
    pub score_exponent: f64,    // α = (10 − s) / 10
    pub decay_coefficient: f64, // d(t) ∈ [floor, 1.0]
    pub effective_weight: f64,  // w = s · d(t)^α
}

pub struct Db {
    conn: Connection,
}

impl Db {
    pub fn open(path: &PathBuf) -> Result<Self> {
        if let Some(parent) = path.parent() {
            std::fs::create_dir_all(parent)?;
        }
        let conn = Connection::open(path)
            .with_context(|| format!("opening SQLite at {}", path.display()))?;
        let db = Self { conn };
        db.migrate()?;
        Ok(db)
    }

    // ── Schema migration ─────────────────────────────────────────────────────

    fn schema_version(&self) -> i64 {
        self.conn
            .query_row(
                "SELECT COALESCE((SELECT value FROM schema_meta WHERE key='version'), '1')",
                [],
                |r| r.get::<_, String>(0),
            )
            .map(|s| s.parse().unwrap_or(1))
            .unwrap_or(1)
    }

    fn set_version(&self, v: i64) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO schema_meta (key, value) VALUES ('version', ?1)",
            params![v.to_string()],
        )?;
        Ok(())
    }

    fn migrate(&self) -> Result<()> {
        self.conn.execute_batch("
            PRAGMA journal_mode=WAL;
            CREATE TABLE IF NOT EXISTS schema_meta (
                key   TEXT PRIMARY KEY,
                value TEXT NOT NULL
            );
        ")?;

        let version = self.schema_version();

        if version < 2 {
            self.conn.execute_batch("
                DROP TABLE IF EXISTS preferences;
                DROP TABLE IF EXISTS sessions;
                DROP TABLE IF EXISTS decay_config;
            ")?;
        }

        // Ensure supporting tables always exist
        self.conn.execute_batch("
            CREATE TABLE IF NOT EXISTS decay_config (
                id              INTEGER PRIMARY KEY CHECK(id = 1),
                half_life_days  REAL    NOT NULL DEFAULT 30.0,
                floor           REAL    NOT NULL DEFAULT 0.1
            );
            INSERT OR IGNORE INTO decay_config (id, half_life_days, floor)
                VALUES (1, 30.0, 0.1);

            CREATE TABLE IF NOT EXISTS sessions (
                id           INTEGER PRIMARY KEY AUTOINCREMENT,
                analyzed_at  TEXT    NOT NULL,
                extracted    INTEGER NOT NULL DEFAULT 0,
                content_hash TEXT    NOT NULL DEFAULT ''
            );
            CREATE UNIQUE INDEX IF NOT EXISTS idx_sessions_hash
                ON sessions(content_hash) WHERE content_hash != '';
        ")?;

        if version < 5 {
            // Rebuild preferences table: drop category, unique on LOWER(item) only.
            // For any duplicate items (same item under different old categories),
            // keep the row whose base_score is furthest from neutral (5.0).
            self.conn.execute_batch("
                CREATE TABLE IF NOT EXISTS preferences_v5 (
                    id          INTEGER PRIMARY KEY AUTOINCREMENT,
                    item        TEXT    NOT NULL,
                    reason      TEXT    NOT NULL,
                    keywords    TEXT    NOT NULL DEFAULT '',
                    base_score  REAL    NOT NULL DEFAULT 5.0,
                    source      TEXT    NOT NULL DEFAULT 'manual',
                    added_at    TEXT    NOT NULL,
                    last_seen   TEXT    NOT NULL
                );
                CREATE UNIQUE INDEX IF NOT EXISTS idx_pref_item_v5
                    ON preferences_v5(LOWER(item));
            ")?;

            // Copy from old table if it exists (versions 2–4 had category column).
            let old_exists: bool = self.conn
                .query_row(
                    "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='preferences'",
                    [],
                    |r| r.get::<_, i64>(0),
                )
                .unwrap_or(0) > 0;

            if old_exists {
                // Order by extremity so INSERT OR IGNORE keeps the most salient entry
                // when the same item appeared under multiple old categories.
                self.conn.execute_batch("
                    INSERT OR IGNORE INTO preferences_v5
                        (item, reason, keywords, base_score, source, added_at, last_seen)
                    SELECT item,
                           reason,
                           COALESCE(keywords, ''),
                           base_score,
                           source,
                           added_at,
                           last_seen
                    FROM preferences
                    ORDER BY ABS(base_score - 5.0) DESC, last_seen DESC;

                    DROP TABLE preferences;
                ")?;
            }

            self.conn.execute_batch("
                ALTER TABLE preferences_v5 RENAME TO preferences;
            ")?;
        } else {
            // Fresh database at v5+: create the table directly
            self.conn.execute_batch("
                CREATE TABLE IF NOT EXISTS preferences (
                    id          INTEGER PRIMARY KEY AUTOINCREMENT,
                    item        TEXT    NOT NULL,
                    reason      TEXT    NOT NULL,
                    keywords    TEXT    NOT NULL DEFAULT '',
                    base_score  REAL    NOT NULL DEFAULT 5.0,
                    source      TEXT    NOT NULL DEFAULT 'manual',
                    added_at    TEXT    NOT NULL,
                    last_seen   TEXT    NOT NULL
                );
                CREATE UNIQUE INDEX IF NOT EXISTS idx_pref_item
                    ON preferences(LOWER(item));
            ")?;
        }

        self.set_version(5)?;
        Ok(())
    }

    // ── Decay config ─────────────────────────────────────────────────────────

    pub fn get_decay_config(&self) -> Result<DecayConfig> {
        self.conn
            .query_row(
                "SELECT half_life_days, floor FROM decay_config WHERE id = 1",
                [],
                |row| Ok(DecayConfig { half_life_days: row.get(0)?, floor: row.get(1)? }),
            )
            .map_err(Into::into)
    }

    pub fn set_decay_config(&self, half_life_days: f64, floor: f64) -> Result<()> {
        self.conn.execute(
            "UPDATE decay_config SET half_life_days = ?1, floor = ?2 WHERE id = 1",
            params![half_life_days, floor],
        )?;
        Ok(())
    }

    // ── Row hydration ────────────────────────────────────────────────────────

    // SELECT column order: id(0) item(1) reason(2) keywords(3) base_score(4)
    //                      source(5) added_at(6) last_seen(7)
    fn hydrate(row: &rusqlite::Row, cfg: &DecayConfig) -> rusqlite::Result<Preference> {
        let last_seen: String = row.get(7)?;
        let keywords_raw: String = row.get(3)?;
        let base_score: f64 = row.get(4)?;

        let s = scoring::compute(base_score, &last_seen, cfg);

        Ok(Preference {
            id:                row.get(0)?,
            item:              row.get(1)?,
            reason:            row.get(2)?,
            keywords: keywords_raw
                .split(',')
                .map(str::trim)
                .filter(|s| !s.is_empty())
                .map(String::from)
                .collect(),
            base_score,
            source:            row.get(5)?,
            added_at:          row.get(6)?,
            last_seen,
            score_exponent:    s.score_exponent,
            decay_coefficient: s.decay_coefficient,
            effective_weight:  s.effective_weight,
        })
    }

    // ── Writes ────────────────────────────────────────────────────────────────

    /// Upsert with score smoothing (new = old×0.4 + incoming×0.6) and last_seen refresh.
    pub fn upsert(
        &self,
        item: &str,
        reason: &str,
        keywords: &[String],
        base_score: f64,
        source: &str,
    ) -> Result<()> {
        let now = Utc::now().to_rfc3339();
        let kw = keywords.join(", ");
        self.conn.execute(
            "INSERT INTO preferences
                (item, reason, keywords, base_score, source, added_at, last_seen)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?6)
             ON CONFLICT(LOWER(item)) DO UPDATE SET
                reason     = excluded.reason,
                keywords   = excluded.keywords,
                base_score = base_score * 0.4 + excluded.base_score * 0.6,
                source     = excluded.source,
                last_seen  = excluded.last_seen",
            params![item, reason, kw, base_score, source, now],
        )?;
        Ok(())
    }

    /// Bulk-refresh last_seen for a list of row IDs (used internally by recall).
    fn touch_ids(&self, ids: Vec<i64>) -> Result<()> {
        if ids.is_empty() {
            return Ok(());
        }
        let now = Utc::now().to_rfc3339();
        let placeholders = ids.iter().enumerate()
            .map(|(i, _)| format!("?{}", i + 2))
            .collect::<Vec<_>>()
            .join(", ");
        let sql = format!(
            "UPDATE preferences SET last_seen = ?1 WHERE id IN ({})",
            placeholders
        );
        let mut stmt = self.conn.prepare(&sql)?;
        stmt.execute(rusqlite::params_from_iter(
            std::iter::once(rusqlite::types::Value::Text(now))
                .chain(ids.iter().map(|&id| rusqlite::types::Value::Integer(id))),
        ))?;
        Ok(())
    }

    /// Reset the decay clock for one entry without changing its score or reason.
    /// Returns true if the entry was found and updated, false if not found.
    pub fn touch(&self, item: &str) -> Result<bool> {
        let now = Utc::now().to_rfc3339();
        let n = self.conn.execute(
            "UPDATE preferences SET last_seen = ?1 WHERE LOWER(item) = LOWER(?2)",
            params![now, item],
        )?;
        Ok(n > 0)
    }

    pub fn remove(&self, item: &str) -> Result<bool> {
        let n = self.conn.execute(
            "DELETE FROM preferences WHERE LOWER(item) = LOWER(?1)",
            params![item],
        )?;
        Ok(n > 0)
    }

    /// Replace the keywords on an existing entry. Returns true if the entry was found.
    pub fn tag(&self, item: &str, keywords: &[String]) -> Result<bool> {
        let kw = keywords.iter().map(|k| k.to_lowercase()).collect::<Vec<_>>().join(", ");
        let n = self.conn.execute(
            "UPDATE preferences SET keywords = ?1 WHERE LOWER(item) = LOWER(?2)",
            params![kw, item],
        )?;
        Ok(n > 0)
    }

    /// Return all keywords with their entry counts.
    pub fn all_keywords(&self) -> Result<Vec<(String, usize)>> {
        let mut stmt = self.conn.prepare(
            "SELECT keywords FROM preferences WHERE keywords != ''",
        )?;
        let rows: Vec<String> = stmt.query_map([], |r| r.get(0))?
            .collect::<rusqlite::Result<_>>()?;

        let mut counts: std::collections::HashMap<String, usize> = std::collections::HashMap::new();
        for row in rows {
            for kw in row.split(',').map(str::trim).filter(|s| !s.is_empty()) {
                *counts.entry(kw.to_string()).or_insert(0) += 1;
            }
        }
        Ok(counts.into_iter().collect())
    }

    pub fn clear(&self) -> Result<()> {
        self.conn
            .execute_batch("DELETE FROM preferences; DELETE FROM sessions;")?;
        Ok(())
    }

    pub fn has_analyzed(&self, hash: &str) -> Result<bool> {
        let n: i64 = self.conn.query_row(
            "SELECT COUNT(*) FROM sessions WHERE content_hash = ?1",
            params![hash],
            |r| r.get(0),
        )?;
        Ok(n > 0)
    }

    pub fn log_session(&self, extracted: usize, hash: &str) -> Result<()> {
        let now = Utc::now().to_rfc3339();
        self.conn.execute(
            "INSERT OR IGNORE INTO sessions (analyzed_at, extracted, content_hash)
             VALUES (?1, ?2, ?3)",
            params![now, extracted as i64, hash],
        )?;
        Ok(())
    }

    // ── Reads ─────────────────────────────────────────────────────────────────

    const SELECT: &'static str =
        "SELECT id, item, reason, keywords, base_score, source, added_at, last_seen
         FROM preferences";

    fn sorted(mut rows: Vec<Preference>) -> Vec<Preference> {
        rows.sort_by(|a, b| b.effective_weight.partial_cmp(&a.effective_weight).unwrap());
        rows
    }

    pub fn all(&self) -> Result<Vec<Preference>> {
        let cfg = self.get_decay_config()?;
        let mut stmt = self.conn.prepare(Self::SELECT)?;
        let rows: Vec<Preference> = stmt
            .query_map([], |row| Self::hydrate(row, &cfg))?
            .collect::<rusqlite::Result<_>>()?;
        Ok(Self::sorted(rows))
    }

    pub fn top(&self, n: usize, score_ranges: &[(f64, f64)]) -> Result<Vec<Preference>> {
        let mut rows = self.recall(&[], score_ranges, None, false)?;
        rows.truncate(n);
        Ok(rows)
    }

    /// Flexible recall: keyword search OR'd across queries, score filter OR'd across ranges.
    ///
    /// - `queries` empty  → no keyword filter (matches all entries)
    /// - `score_ranges` empty → no score filter
    /// - `limit` applied after sorting by effective_weight
    pub fn recall(
        &self,
        queries: &[&str],
        score_ranges: &[(f64, f64)],
        limit: Option<usize>,
        touch: bool,
    ) -> Result<Vec<Preference>> {
        let cfg = self.get_decay_config()?;

        let mut where_clauses: Vec<String> = Vec::new();
        let mut bind_vals: Vec<rusqlite::types::Value> = Vec::new();
        let mut p = 1usize; // 1-based SQL param index

        // Keyword filter: each query becomes one OR clause across all text fields
        if !queries.is_empty() {
            let kw_parts: Vec<String> = queries.iter().map(|q| {
                let pattern = format!("%{}%", q.to_lowercase());
                bind_vals.push(rusqlite::types::Value::Text(pattern));
                let i = p; p += 1;
                format!("(LOWER(item) LIKE ?{i} OR LOWER(reason) LIKE ?{i} OR LOWER(keywords) LIKE ?{i})")
            }).collect();
            where_clauses.push(format!("({})", kw_parts.join(" OR ")));
        }

        // Score range filter: multiple ranges are OR'd together
        if !score_ranges.is_empty() {
            let range_parts: Vec<String> = score_ranges.iter().map(|(min, max)| {
                let i = p; p += 2;
                bind_vals.push(rusqlite::types::Value::Real(*min));
                bind_vals.push(rusqlite::types::Value::Real(*max));
                format!("(base_score >= ?{i} AND base_score <= ?{})", i + 1)
            }).collect();
            where_clauses.push(format!("({})", range_parts.join(" OR ")));
        }

        let sql = if where_clauses.is_empty() {
            Self::SELECT.to_string()
        } else {
            format!("{} WHERE {}", Self::SELECT, where_clauses.join(" AND "))
        };

        let mut stmt = self.conn.prepare(&sql)?;
        let rows: Vec<Preference> = stmt
            .query_map(rusqlite::params_from_iter(bind_vals), |row| Self::hydrate(row, &cfg))?
            .collect::<rusqlite::Result<_>>()?;

        let mut rows = Self::sorted(rows);
        if let Some(n) = limit {
            rows.truncate(n);
        }
        if touch {
            self.touch_ids(rows.iter().map(|p| p.id).collect())?;
        }
        Ok(rows)
    }

    pub fn stats(&self) -> Result<Stats> {
        let count = |sql: &str| -> rusqlite::Result<usize> {
            self.conn.query_row(sql, [], |r| r.get(0))
        };
        Ok(Stats {
            high:     count("SELECT COUNT(*) FROM preferences WHERE base_score >= 7.0")?,
            mid:      count("SELECT COUNT(*) FROM preferences WHERE base_score > 3.0 AND base_score < 7.0")?,
            low:      count("SELECT COUNT(*) FROM preferences WHERE base_score <= 3.0")?,
            sessions: count("SELECT COUNT(*) FROM sessions")?,
        })
    }
}

#[derive(Debug)]
pub struct Stats {
    pub high: usize,      // score ≥ 7: strong likes
    pub mid: usize,       // score 4–6: neutral habits
    pub low: usize,       // score ≤ 3: dislikes / limits
    pub sessions: usize,
}

impl Stats {
    pub fn total(&self) -> usize {
        self.high + self.mid + self.low
    }
}
