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

use crate::state::runtime::{RuntimeHandoffItem, RuntimeHandoffState};

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub(crate) enum ExclusiveWriteResult {
    Applied { revision: u64 },
    RevisionConflict { current_revision: u64 },
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub(crate) struct StoredHandoffState {
    pub(crate) state: RuntimeHandoffState,
    pub(crate) revision: u64,
}

pub(crate) fn read(conn: &Connection) -> Result<Option<RuntimeHandoffState>> {
    Ok(read_record(conn)?.map(|record| record.state))
}

pub(crate) fn read_record(conn: &Connection) -> Result<Option<StoredHandoffState>> {
    let mut stmt = conn.prepare(
        "SELECT title, immediate_actions, completed_state,
                operational_guardrails, key_files, definition_of_done, revision
         FROM handoff WHERE id = 1",
    )?;

    let result = stmt.query_row([], |row| {
        Ok(RawHandoff {
            title: row.get(0)?,
            immediate_actions: row.get(1)?,
            completed_state: row.get(2)?,
            operational_guardrails: row.get(3)?,
            key_files: row.get(4)?,
            definition_of_done: row.get(5)?,
            revision: row.get(6)?,
        })
    });

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

pub(crate) fn current_revision(conn: &Connection) -> Result<u64> {
    let result = conn.query_row("SELECT revision FROM handoff WHERE id = 1", [], |row| {
        row.get(0)
    });
    match result {
        Ok(revision) => Ok(revision),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(0),
        Err(error) => Err(error.into()),
    }
}

pub(crate) fn write(conn: &Connection, state: &RuntimeHandoffState) -> Result<u64> {
    let next_revision = current_revision(conn)?.saturating_add(1).max(1);
    conn.execute(
        "INSERT OR REPLACE INTO handoff
            (id, schema_version, title, immediate_actions, completed_state,
             operational_guardrails, key_files, definition_of_done, revision)
         VALUES (1, 1, ?1, ?2, ?3, ?4, ?5, ?6, ?7)",
        rusqlite::params![
            state.title,
            serde_json::to_string(&state.immediate_actions)?,
            serde_json::to_string(&state.completed_state)?,
            serde_json::to_string(&state.operational_guardrails)?,
            serde_json::to_string(&state.key_files)?,
            serde_json::to_string(&state.definition_of_done)?,
            next_revision,
        ],
    )?;
    Ok(next_revision)
}

pub(crate) fn write_if_revision_matches(
    conn: &Connection,
    state: &RuntimeHandoffState,
    expected_revision: u64,
) -> Result<ExclusiveWriteResult> {
    if expected_revision == 0 {
        let inserted = conn.execute(
            "INSERT OR IGNORE INTO handoff
                (id, schema_version, title, immediate_actions, completed_state,
                 operational_guardrails, key_files, definition_of_done, revision)
             VALUES (1, 1, ?1, ?2, ?3, ?4, ?5, ?6, 1)",
            rusqlite::params![
                state.title,
                serde_json::to_string(&state.immediate_actions)?,
                serde_json::to_string(&state.completed_state)?,
                serde_json::to_string(&state.operational_guardrails)?,
                serde_json::to_string(&state.key_files)?,
                serde_json::to_string(&state.definition_of_done)?,
            ],
        )?;
        return if inserted == 1 {
            Ok(ExclusiveWriteResult::Applied { revision: 1 })
        } else {
            Ok(ExclusiveWriteResult::RevisionConflict {
                current_revision: current_revision(conn)?,
            })
        };
    }

    let updated = conn.execute(
        "UPDATE handoff
         SET title = ?1,
             immediate_actions = ?2,
             completed_state = ?3,
             operational_guardrails = ?4,
             key_files = ?5,
             definition_of_done = ?6,
             revision = revision + 1
         WHERE id = 1 AND revision = ?7",
        rusqlite::params![
            state.title,
            serde_json::to_string(&state.immediate_actions)?,
            serde_json::to_string(&state.completed_state)?,
            serde_json::to_string(&state.operational_guardrails)?,
            serde_json::to_string(&state.key_files)?,
            serde_json::to_string(&state.definition_of_done)?,
            expected_revision,
        ],
    )?;
    if updated == 1 {
        Ok(ExclusiveWriteResult::Applied {
            revision: expected_revision.saturating_add(1).max(1),
        })
    } else {
        Ok(ExclusiveWriteResult::RevisionConflict {
            current_revision: current_revision(conn)?,
        })
    }
}

pub(crate) fn exists(conn: &Connection) -> Result<bool> {
    let count: i64 = conn.query_row("SELECT COUNT(*) FROM handoff WHERE id = 1", [], |row| {
        row.get(0)
    })?;
    Ok(count > 0)
}

struct RawHandoff {
    title: String,
    immediate_actions: String,
    completed_state: String,
    operational_guardrails: String,
    key_files: String,
    definition_of_done: String,
    revision: u64,
}

impl RawHandoff {
    fn into_state(self) -> Result<StoredHandoffState> {
        Ok(StoredHandoffState {
            state: RuntimeHandoffState {
                title: self.title,
                immediate_actions: serde_json::from_str::<Vec<RuntimeHandoffItem>>(
                    &self.immediate_actions,
                )?,
                completed_state: serde_json::from_str(&self.completed_state)?,
                operational_guardrails: serde_json::from_str(&self.operational_guardrails)?,
                key_files: serde_json::from_str(&self.key_files)?,
                definition_of_done: serde_json::from_str(&self.definition_of_done)?,
            },
            revision: self.revision,
        })
    }
}

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

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

    #[test]
    fn read_returns_none_when_empty() {
        let conn = test_conn();
        assert!(read(&conn).unwrap().is_none());
        assert!(!exists(&conn).unwrap());
    }

    #[test]
    fn write_and_read_roundtrip() {
        let conn = test_conn();
        let state = RuntimeHandoffState {
            title: "Next: SQLite migration".to_owned(),
            immediate_actions: vec![RuntimeHandoffItem {
                text: "Implement db module.".to_owned(),
                lifecycle: RuntimeLifecycle::Active,
            }],
            completed_state: vec![RuntimeHandoffItem {
                text: "Schema created.".to_owned(),
                lifecycle: RuntimeLifecycle::Active,
            }],
            operational_guardrails: vec![],
            key_files: vec![RuntimeHandoffItem {
                text: "`src/db/mod.rs`".to_owned(),
                lifecycle: RuntimeLifecycle::Active,
            }],
            definition_of_done: vec![],
        };

        let revision = write(&conn, &state).unwrap();
        assert_eq!(revision, 1);
        assert!(exists(&conn).unwrap());

        let loaded = read_record(&conn).unwrap().expect("should exist");
        assert_eq!(loaded.revision, 1);
        let loaded = loaded.state;
        assert_eq!(loaded.title, state.title);
        assert_eq!(loaded.immediate_actions.len(), 1);
        assert_eq!(loaded.immediate_actions[0].text, "Implement db module.");
        assert_eq!(loaded.completed_state.len(), 1);
        assert_eq!(loaded.key_files.len(), 1);
    }

    #[test]
    fn write_overwrites_existing() {
        let conn = test_conn();
        let state1 = RuntimeHandoffState {
            title: "First".to_owned(),
            ..RuntimeHandoffState::default()
        };
        let state2 = RuntimeHandoffState {
            title: "Second".to_owned(),
            ..RuntimeHandoffState::default()
        };

        assert_eq!(write(&conn, &state1).unwrap(), 1);
        assert_eq!(write(&conn, &state2).unwrap(), 2);

        let loaded = read_record(&conn).unwrap().expect("should exist");
        assert_eq!(loaded.revision, 2);
        let loaded = loaded.state;
        assert_eq!(loaded.title, "Second");
    }

    fn delete(conn: &Connection) -> Result<()> {
        conn.execute("DELETE FROM handoff WHERE id = 1", [])?;
        Ok(())
    }

    #[test]
    fn delete_removes_row() {
        let conn = test_conn();
        write(&conn, &RuntimeHandoffState::default()).unwrap();
        assert!(exists(&conn).unwrap());

        delete(&conn).unwrap();
        assert!(!exists(&conn).unwrap());
        assert!(read(&conn).unwrap().is_none());
    }

    #[test]
    fn write_if_revision_matches_detects_conflicts() {
        let conn = test_conn();
        let first = RuntimeHandoffState {
            title: "First".to_owned(),
            ..RuntimeHandoffState::default()
        };
        let second = RuntimeHandoffState {
            title: "Second".to_owned(),
            ..RuntimeHandoffState::default()
        };

        let first_result = write_if_revision_matches(&conn, &first, 0).unwrap();
        assert_eq!(first_result, ExclusiveWriteResult::Applied { revision: 1 });

        let conflict = write_if_revision_matches(&conn, &second, 0).unwrap();
        assert_eq!(
            conflict,
            ExclusiveWriteResult::RevisionConflict {
                current_revision: 1
            }
        );

        let applied = write_if_revision_matches(&conn, &second, 1).unwrap();
        assert_eq!(applied, ExclusiveWriteResult::Applied { revision: 2 });
    }
}
