use rusqlite::Connection;
use uuid::Uuid;

use crate::{Agent, Experience, Note, Project, Tag};

fn uid() -> String {
    Uuid::new_v4().to_string()[..8].to_string()
}

fn now_millis() -> i64 {
    chrono::Utc::now().timestamp_millis()
}

pub struct KiokStore {
    conn: Connection,
}

impl KiokStore {
    pub fn new(conn: Connection) -> Self {
        Self { conn }
    }

    // --- Agent ---

    pub fn create_agent(&self, name: &str) -> rusqlite::Result<Agent> {
        let agent = Agent {
            id: uid(),
            name: name.to_string(),
            project_id: None,
            created_at: now_millis(),
        };
        self.conn.execute(
            "INSERT INTO agents (id, name, created_at) VALUES (?1, ?2, ?3)",
            (&agent.id, &agent.name, agent.created_at),
        )?;
        Ok(agent)
    }

    pub fn create_agent_in_project(&self, name: &str, project_id: &str) -> rusqlite::Result<Agent> {
        let agent = Agent {
            id: uid(),
            name: name.to_string(),
            project_id: Some(project_id.to_string()),
            created_at: now_millis(),
        };
        self.conn.execute(
            "INSERT INTO agents (id, name, project_id, created_at) VALUES (?1, ?2, ?3, ?4)",
            (&agent.id, &agent.name, &agent.project_id, agent.created_at),
        )?;
        Ok(agent)
    }

    fn row_to_agent(row: &rusqlite::Row) -> rusqlite::Result<Agent> {
        Ok(Agent {
            id: row.get(0)?,
            name: row.get(1)?,
            project_id: row.get(2)?,
            created_at: row.get(3)?,
        })
    }

    pub fn list_agents(&self) -> rusqlite::Result<Vec<Agent>> {
        let mut stmt = self
            .conn
            .prepare("SELECT id, name, project_id, created_at FROM agents ORDER BY created_at DESC")?;
        let rows = stmt.query_map([], Self::row_to_agent)?;
        rows.collect()
    }

    pub fn get_agent_by_name(&self, name: &str) -> rusqlite::Result<Option<Agent>> {
        let mut stmt = self
            .conn
            .prepare("SELECT id, name, project_id, created_at FROM agents WHERE name = ?1")?;
        let mut rows = stmt.query_map([name], Self::row_to_agent)?;
        match rows.next() {
            Some(row) => Ok(Some(row?)),
            None => Ok(None),
        }
    }

    /// Returns (agent, created). If agent doesn't exist, auto-creates it.
    pub fn get_or_create_agent(&self, name: &str) -> rusqlite::Result<(Agent, bool)> {
        match self.get_agent_by_name(name)? {
            Some(agent) => Ok((agent, false)),
            None => Ok((self.create_agent(name)?, true)),
        }
    }

    // --- Experience ---

    pub fn save_experience(
        &self,
        agent_name: &str,
        lesson: &str,
        input_summary: Option<&str>,
    ) -> rusqlite::Result<Experience> {
        self.save_experience_full(agent_name, lesson, input_summary, None, None)
    }

    pub fn save_experience_full(
        &self,
        agent_name: &str,
        lesson: &str,
        input_summary: Option<&str>,
        source_session: Option<&str>,
        source_file: Option<&str>,
    ) -> rusqlite::Result<Experience> {
        let agent = self
            .get_agent_by_name(agent_name)?
            .ok_or_else(|| rusqlite::Error::QueryReturnedNoRows)?;

        let exp = Experience {
            id: uid(),
            agent_id: agent.id,
            input_summary: input_summary.map(|s| s.to_string()),
            lesson: lesson.to_string(),
            refined_lesson: None,
            source_session: source_session.map(|s| s.to_string()),
            source_file: source_file.map(|s| s.to_string()),
            created_at: now_millis(),
        };
        self.conn.execute(
            "INSERT INTO experiences (id, agent_id, input_summary, lesson, source_session, source_file, created_at)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
            (&exp.id, &exp.agent_id, &exp.input_summary, &exp.lesson,
             &exp.source_session, &exp.source_file, exp.created_at),
        )?;
        Ok(exp)
    }

    pub fn search_experiences(
        &self,
        agent_name: &str,
        query: &str,
        limit: usize,
    ) -> rusqlite::Result<Vec<Experience>> {
        let agent = match self.get_agent_by_name(agent_name)? {
            Some(a) => a,
            None => return Ok(vec![]),
        };

        // Quote each word to escape FTS5 operators (e.g. - = NOT)
        let fts_query = query
            .split_whitespace()
            .map(|w| format!("\"{}\"", w.replace('"', "\"\"")))
            .collect::<Vec<_>>()
            .join(" ");

        let mut stmt = self.conn.prepare(
            "SELECT e.id, e.agent_id, e.input_summary, e.lesson, e.refined_lesson,
                    e.source_session, e.source_file, e.created_at
             FROM experiences e
             JOIN experiences_fts f ON e.rowid = f.rowid
             WHERE e.agent_id = ?1 AND experiences_fts MATCH ?2
             ORDER BY rank
             LIMIT ?3",
        )?;
        let rows = stmt.query_map(rusqlite::params![agent.id, fts_query, limit], Self::row_to_experience)?;
        rows.collect()
    }

    pub fn list_experiences(&self, agent_name: &str) -> rusqlite::Result<Vec<Experience>> {
        let agent = match self.get_agent_by_name(agent_name)? {
            Some(a) => a,
            None => return Ok(vec![]),
        };

        let mut stmt = self.conn.prepare(
            "SELECT id, agent_id, input_summary, lesson, refined_lesson,
                    source_session, source_file, created_at
             FROM experiences WHERE agent_id = ?1 ORDER BY created_at DESC",
        )?;
        let rows = stmt.query_map([&agent.id], Self::row_to_experience)?;
        rows.collect()
    }

    fn row_to_experience(row: &rusqlite::Row) -> rusqlite::Result<Experience> {
        Ok(Experience {
            id: row.get(0)?,
            agent_id: row.get(1)?,
            input_summary: row.get(2)?,
            lesson: row.get(3)?,
            refined_lesson: row.get(4)?,
            source_session: row.get(5)?,
            source_file: row.get(6)?,
            created_at: row.get(7)?,
        })
    }

    pub fn forget_experience(&self, id: &str) -> rusqlite::Result<bool> {
        let changes = self
            .conn
            .execute("DELETE FROM experiences WHERE id = ?1", [id])?;
        Ok(changes > 0)
    }

    // --- Project ---

    pub fn create_project(&self, name: &str, description: Option<&str>) -> rusqlite::Result<Project> {
        let project = Project {
            id: uid(),
            name: name.to_string(),
            description: description.map(|s| s.to_string()),
            created_at: now_millis(),
        };
        self.conn.execute(
            "INSERT INTO projects (id, name, description, created_at) VALUES (?1, ?2, ?3, ?4)",
            (&project.id, &project.name, &project.description, project.created_at),
        )?;
        Ok(project)
    }

    pub fn list_projects(&self) -> rusqlite::Result<Vec<Project>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, name, description, created_at FROM projects ORDER BY created_at DESC",
        )?;
        let rows = stmt.query_map([], |row| {
            Ok(Project {
                id: row.get(0)?,
                name: row.get(1)?,
                description: row.get(2)?,
                created_at: row.get(3)?,
            })
        })?;
        rows.collect()
    }

    pub fn assign_agent_to_project(&self, agent_name: &str, project_name: &str) -> rusqlite::Result<()> {
        self.conn.execute(
            "UPDATE agents SET project_id = (SELECT id FROM projects WHERE name = ?1) WHERE name = ?2",
            [project_name, agent_name],
        )?;
        Ok(())
    }

    // --- Note ---

    pub fn add_note(&self, experience_id: &str, content: &str) -> rusqlite::Result<Note> {
        let note = Note {
            id: uid(),
            experience_id: experience_id.to_string(),
            content: content.to_string(),
            created_at: now_millis(),
        };
        self.conn.execute(
            "INSERT INTO notes (id, experience_id, content, created_at) VALUES (?1, ?2, ?3, ?4)",
            (&note.id, &note.experience_id, &note.content, note.created_at),
        )?;
        Ok(note)
    }

    pub fn list_notes(&self, experience_id: &str) -> rusqlite::Result<Vec<Note>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, experience_id, content, created_at FROM notes WHERE experience_id = ?1 ORDER BY created_at",
        )?;
        let rows = stmt.query_map([experience_id], |row| {
            Ok(Note {
                id: row.get(0)?,
                experience_id: row.get(1)?,
                content: row.get(2)?,
                created_at: row.get(3)?,
            })
        })?;
        rows.collect()
    }

    // --- Tag ---

    pub fn create_tag(&self, name: &str, color: Option<&str>) -> rusqlite::Result<Tag> {
        let tag = Tag {
            id: uid(),
            name: name.to_string(),
            color: color.map(|s| s.to_string()),
        };
        self.conn.execute(
            "INSERT INTO tags (id, name, color) VALUES (?1, ?2, ?3)",
            (&tag.id, &tag.name, &tag.color),
        )?;
        Ok(tag)
    }

    pub fn list_tags(&self) -> rusqlite::Result<Vec<Tag>> {
        let mut stmt = self.conn.prepare("SELECT id, name, color FROM tags ORDER BY name")?;
        let rows = stmt.query_map([], |row| {
            Ok(Tag { id: row.get(0)?, name: row.get(1)?, color: row.get(2)? })
        })?;
        rows.collect()
    }

    pub fn tag_experience(&self, experience_id: &str, tag_name: &str) -> rusqlite::Result<()> {
        self.conn.execute(
            "INSERT OR IGNORE INTO experience_tags (experience_id, tag_id)
             VALUES (?1, (SELECT id FROM tags WHERE name = ?2))",
            [experience_id, tag_name],
        )?;
        Ok(())
    }

    pub fn get_experience_tags(&self, experience_id: &str) -> rusqlite::Result<Vec<Tag>> {
        let mut stmt = self.conn.prepare(
            "SELECT t.id, t.name, t.color FROM tags t
             JOIN experience_tags et ON t.id = et.tag_id
             WHERE et.experience_id = ?1 ORDER BY t.name",
        )?;
        let rows = stmt.query_map([experience_id], |row| {
            Ok(Tag { id: row.get(0)?, name: row.get(1)?, color: row.get(2)? })
        })?;
        rows.collect()
    }

    // --- Refined Lesson ---

    pub fn update_refined_lesson(&self, experience_id: &str, refined: &str) -> rusqlite::Result<bool> {
        let changes = self.conn.execute(
            "UPDATE experiences SET refined_lesson = ?1 WHERE id = ?2",
            [refined, experience_id],
        )?;
        Ok(changes > 0)
    }

    // --- Access Log ---

    pub fn log_access(&self, experience_id: &str, action: &str, session_id: Option<&str>) -> rusqlite::Result<()> {
        self.conn.execute(
            "INSERT INTO access_log (experience_id, action, session_id, created_at) VALUES (?1, ?2, ?3, ?4)",
            rusqlite::params![experience_id, action, session_id, now_millis()],
        )?;
        Ok(())
    }

    pub fn get_access_count(&self, experience_id: &str) -> rusqlite::Result<i64> {
        self.conn.query_row(
            "SELECT COUNT(*) FROM access_log WHERE experience_id = ?1",
            [experience_id],
            |row| row.get(0),
        )
    }

    // --- Usage Log ---

    pub fn log_usage(&self, action: &str, agent_name: Option<&str>) -> rusqlite::Result<()> {
        self.conn.execute(
            "INSERT INTO usage_log (action, agent_name, created_at) VALUES (?1, ?2, ?3)",
            rusqlite::params![action, agent_name, now_millis()],
        )?;
        Ok(())
    }

    // --- Stats ---

    pub fn count_agents(&self) -> rusqlite::Result<i64> {
        self.conn
            .query_row("SELECT COUNT(*) FROM agents", [], |row| row.get(0))
    }

    pub fn count_experiences(&self) -> rusqlite::Result<i64> {
        self.conn
            .query_row("SELECT COUNT(*) FROM experiences", [], |row| row.get(0))
    }

    pub fn count_usage(&self, action: &str) -> rusqlite::Result<i64> {
        self.conn.query_row(
            "SELECT COUNT(*) FROM usage_log WHERE action = ?1",
            [action],
            |row| row.get(0),
        )
    }

    pub fn top_agents(&self, limit: usize) -> rusqlite::Result<Vec<(String, i64)>> {
        let mut stmt = self.conn.prepare(
            "SELECT a.name, COUNT(e.id) as cnt
             FROM agents a LEFT JOIN experiences e ON a.id = e.agent_id
             GROUP BY a.id ORDER BY cnt DESC LIMIT ?1",
        )?;
        let rows = stmt.query_map([limit], |row| {
            Ok((row.get::<_, String>(0)?, row.get::<_, i64>(1)?))
        })?;
        rows.collect()
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::db::schema::init_db;

    fn test_store() -> KiokStore {
        let conn = init_db(":memory:").unwrap();
        KiokStore::new(conn)
    }

    #[test]
    fn create_and_list_agents() {
        let store = test_store();
        store.create_agent("alpha").unwrap();
        store.create_agent("beta").unwrap();

        let agents = store.list_agents().unwrap();
        assert_eq!(agents.len(), 2);
    }

    #[test]
    fn get_agent_by_name() {
        let store = test_store();
        store.create_agent("alpha").unwrap();

        assert!(store.get_agent_by_name("alpha").unwrap().is_some());
        assert!(store.get_agent_by_name("nope").unwrap().is_none());
    }

    #[test]
    fn duplicate_agent_name_fails() {
        let store = test_store();
        store.create_agent("alpha").unwrap();
        assert!(store.create_agent("alpha").is_err());
    }

    #[test]
    fn save_and_list_experiences() {
        let store = test_store();
        store.create_agent("a").unwrap();
        store.save_experience("a", "lesson one", None).unwrap();
        store
            .save_experience("a", "lesson two", Some("ctx"))
            .unwrap();

        let list = store.list_experiences("a").unwrap();
        assert_eq!(list.len(), 2);
        // DESC order — newest first
        assert!(list.iter().any(|e| e.input_summary.is_none()));
        assert!(list.iter().any(|e| e.input_summary.as_deref() == Some("ctx")));
    }

    #[test]
    fn save_to_missing_agent_fails() {
        let store = test_store();
        assert!(store.save_experience("ghost", "x", None).is_err());
    }

    #[test]
    fn search_missing_agent_returns_empty() {
        let store = test_store();
        let results = store.search_experiences("ghost", "anything", 10).unwrap();
        assert!(results.is_empty());
    }

    #[test]
    fn list_missing_agent_returns_empty() {
        let store = test_store();
        let results = store.list_experiences("ghost").unwrap();
        assert!(results.is_empty());
    }

    #[test]
    fn get_or_create_agent() {
        let store = test_store();

        let (agent, created) = store.get_or_create_agent("auto").unwrap();
        assert!(created);
        assert_eq!(agent.name, "auto");

        let (agent2, created2) = store.get_or_create_agent("auto").unwrap();
        assert!(!created2);
        assert_eq!(agent.id, agent2.id);
    }

    #[test]
    fn usage_log_and_stats() {
        let store = test_store();
        store.create_agent("a").unwrap();
        store.save_experience("a", "lesson", None).unwrap();

        store.log_usage("save", Some("a")).unwrap();
        store.log_usage("prime", None).unwrap();
        store.log_usage("prime", None).unwrap();

        assert_eq!(store.count_agents().unwrap(), 1);
        assert_eq!(store.count_experiences().unwrap(), 1);
        assert_eq!(store.count_usage("save").unwrap(), 1);
        assert_eq!(store.count_usage("prime").unwrap(), 2);

        let top = store.top_agents(5).unwrap();
        assert_eq!(top.len(), 1);
        assert_eq!(top[0], ("a".to_string(), 1));
    }

    #[test]
    fn search_fts() {
        let store = test_store();
        store.create_agent("a").unwrap();
        store
            .save_experience("a", "batch inserts need transactions", None)
            .unwrap();
        store
            .save_experience("a", "use connection pooling", None)
            .unwrap();

        let results = store.search_experiences("a", "transactions", 10).unwrap();
        assert_eq!(results.len(), 1);
        assert!(results[0].lesson.contains("transactions"));
    }

    #[test]
    fn forget_experience() {
        let store = test_store();
        store.create_agent("a").unwrap();
        let exp = store.save_experience("a", "temp", None).unwrap();

        assert!(store.forget_experience(&exp.id).unwrap());
        assert!(!store.forget_experience(&exp.id).unwrap());
        assert_eq!(store.list_experiences("a").unwrap().len(), 0);
    }
}
