use anyhow::Result;
use rusqlite::Connection;

use crate::state::escalation::{EscalationEntry, EscalationKind};

pub(crate) fn list(conn: &Connection) -> Result<Vec<EscalationEntry>> {
    let mut stmt = conn.prepare(
        "SELECT id, kind, reason, created_at_epoch_s, session_id
         FROM escalation ORDER BY created_at_epoch_s ASC",
    )?;

    let entries = stmt
        .query_map([], |row| {
            let kind_str: String = row.get(1)?;
            Ok(RawEscalation {
                id: row.get(0)?,
                kind: kind_str,
                reason: row.get(2)?,
                created_at_epoch_s: row.get(3)?,
                session_id: row.get(4)?,
            })
        })?
        .collect::<Result<Vec<_>, _>>()?;

    entries
        .into_iter()
        .map(|raw| raw.into_entry())
        .collect::<Result<Vec<_>>>()
}

pub(crate) fn get_by_id(conn: &Connection, id: &str) -> Result<Option<EscalationEntry>> {
    let mut stmt = conn.prepare(
        "SELECT id, kind, reason, created_at_epoch_s, session_id
         FROM escalation WHERE id = ?1",
    )?;
    let result = stmt.query_row([id], |row| {
        let kind_str: String = row.get(1)?;
        Ok(RawEscalation {
            id: row.get(0)?,
            kind: kind_str,
            reason: row.get(2)?,
            created_at_epoch_s: row.get(3)?,
            session_id: row.get(4)?,
        })
    });

    match result {
        Ok(raw) => Ok(Some(raw.into_entry()?)),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
        Err(error) => Err(error.into()),
    }
}

pub(crate) fn insert(conn: &Connection, entry: &EscalationEntry) -> Result<()> {
    conn.execute(
        "INSERT INTO escalation (id, kind, reason, created_at_epoch_s, session_id)
         VALUES (?1, ?2, ?3, ?4, ?5)",
        rusqlite::params![
            entry.id,
            kind_to_str(entry.kind),
            entry.reason,
            entry.created_at_epoch_s,
            entry.session_id,
        ],
    )?;
    Ok(())
}

pub(crate) fn insert_or_ignore(conn: &Connection, entry: &EscalationEntry) -> Result<()> {
    conn.execute(
        "INSERT OR IGNORE INTO escalation (id, kind, reason, created_at_epoch_s, session_id)
         VALUES (?1, ?2, ?3, ?4, ?5)",
        rusqlite::params![
            entry.id,
            kind_to_str(entry.kind),
            entry.reason,
            entry.created_at_epoch_s,
            entry.session_id,
        ],
    )?;
    Ok(())
}

pub(crate) fn delete_by_id(conn: &Connection, id: &str) -> Result<bool> {
    let count = conn.execute("DELETE FROM escalation WHERE id = ?1", [id])?;
    Ok(count > 0)
}

pub(crate) fn clear_all(conn: &Connection) -> Result<()> {
    conn.execute("DELETE FROM escalation", [])?;
    Ok(())
}

pub(crate) fn clear_non_blocking(conn: &Connection) -> Result<u64> {
    let count = conn.execute("DELETE FROM escalation WHERE kind = 'non_blocking'", [])? as u64;
    Ok(count)
}

#[cfg(test)]
pub(crate) fn has_blocking(conn: &Connection) -> Result<bool> {
    let count: i64 = conn.query_row(
        "SELECT COUNT(*) FROM escalation WHERE kind = 'blocking'",
        [],
        |row| row.get(0),
    )?;
    Ok(count > 0)
}

fn kind_to_str(kind: EscalationKind) -> &'static str {
    match kind {
        EscalationKind::Blocking => "blocking",
        EscalationKind::NonBlocking => "non_blocking",
    }
}

fn kind_from_str(s: &str) -> Result<EscalationKind> {
    match s {
        "blocking" => Ok(EscalationKind::Blocking),
        "non_blocking" => Ok(EscalationKind::NonBlocking),
        other => anyhow::bail!("unknown escalation kind: {other}"),
    }
}

struct RawEscalation {
    id: String,
    kind: String,
    reason: String,
    created_at_epoch_s: u64,
    session_id: Option<String>,
}

impl RawEscalation {
    fn into_entry(self) -> Result<EscalationEntry> {
        Ok(EscalationEntry {
            id: self.id,
            kind: kind_from_str(&self.kind)?,
            reason: self.reason,
            created_at_epoch_s: self.created_at_epoch_s,
            session_id: self.session_id,
        })
    }
}

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

    fn test_conn() -> Connection {
        let conn = Connection::open_in_memory().unwrap();
        schema::initialize(&conn, None).unwrap();
        conn
    }

    fn blocking_entry(id: &str) -> EscalationEntry {
        EscalationEntry {
            id: id.to_owned(),
            kind: EscalationKind::Blocking,
            reason: "needs review".to_owned(),
            created_at_epoch_s: 1000,
            session_id: Some("ses_01".to_owned()),
        }
    }

    fn non_blocking_entry(id: &str) -> EscalationEntry {
        EscalationEntry {
            id: id.to_owned(),
            kind: EscalationKind::NonBlocking,
            reason: "informational".to_owned(),
            created_at_epoch_s: 2000,
            session_id: None,
        }
    }

    #[test]
    fn list_returns_empty_initially() {
        let conn = test_conn();
        assert!(list(&conn).unwrap().is_empty());
        assert!(!has_blocking(&conn).unwrap());
    }

    #[test]
    fn insert_and_list_roundtrip() {
        let conn = test_conn();
        insert(&conn, &blocking_entry("esc_1")).unwrap();
        insert(&conn, &non_blocking_entry("esc_2")).unwrap();

        let entries = list(&conn).unwrap();
        assert_eq!(entries.len(), 2);
        assert_eq!(entries[0].id, "esc_1");
        assert_eq!(entries[0].kind, EscalationKind::Blocking);
        assert_eq!(entries[0].session_id.as_deref(), Some("ses_01"));
        assert_eq!(entries[1].id, "esc_2");
        assert_eq!(entries[1].kind, EscalationKind::NonBlocking);
        assert!(entries[1].session_id.is_none());
    }

    #[test]
    fn get_by_id_returns_matching_entry() {
        let conn = test_conn();
        insert(&conn, &blocking_entry("esc_1")).unwrap();

        let loaded = get_by_id(&conn, "esc_1").unwrap().expect("entry");
        assert_eq!(loaded.id, "esc_1");
        assert!(get_by_id(&conn, "missing").unwrap().is_none());
    }

    #[test]
    fn delete_by_id_removes_specific_entry() {
        let conn = test_conn();
        insert(&conn, &blocking_entry("esc_1")).unwrap();
        insert(&conn, &non_blocking_entry("esc_2")).unwrap();

        assert!(delete_by_id(&conn, "esc_1").unwrap());
        assert!(!delete_by_id(&conn, "esc_nonexistent").unwrap());

        let entries = list(&conn).unwrap();
        assert_eq!(entries.len(), 1);
        assert_eq!(entries[0].id, "esc_2");
    }

    #[test]
    fn clear_all_removes_everything() {
        let conn = test_conn();
        insert(&conn, &blocking_entry("esc_1")).unwrap();
        insert(&conn, &non_blocking_entry("esc_2")).unwrap();

        clear_all(&conn).unwrap();
        assert!(list(&conn).unwrap().is_empty());
    }

    #[test]
    fn clear_non_blocking_keeps_blocking() {
        let conn = test_conn();
        insert(&conn, &blocking_entry("esc_1")).unwrap();
        insert(&conn, &non_blocking_entry("esc_2")).unwrap();
        insert(&conn, &non_blocking_entry("esc_3")).unwrap();

        let cleared = clear_non_blocking(&conn).unwrap();
        assert_eq!(cleared, 2);

        let entries = list(&conn).unwrap();
        assert_eq!(entries.len(), 1);
        assert_eq!(entries[0].id, "esc_1");
    }

    #[test]
    fn has_blocking_detects_blocking() {
        let conn = test_conn();
        assert!(!has_blocking(&conn).unwrap());

        insert(&conn, &non_blocking_entry("esc_1")).unwrap();
        assert!(!has_blocking(&conn).unwrap());

        insert(&conn, &blocking_entry("esc_2")).unwrap();
        assert!(has_blocking(&conn).unwrap());
    }
}
