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

use crate::state::session_gates::ExecutionGateStateFile;

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

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

pub(crate) fn read(conn: &Connection) -> Result<Option<ExecutionGateStateFile>> {
    let mut stmt = conn.prepare(
        "SELECT schema_version, seeded_from, gates, revision
         FROM execution_gates WHERE id = 1",
    )?;

    let result = stmt.query_row([], |row| {
        let gates_json: String = row.get(2)?;
        let gates = serde_json::from_str(&gates_json).map_err(|error| {
            rusqlite::Error::FromSqlConversionFailure(
                2,
                rusqlite::types::Type::Text,
                Box::new(error),
            )
        })?;

        Ok(ExecutionGateStateFile {
            schema_version: row.get(0)?,
            seeded_from: row.get(1)?,
            gates,
            revision: row.get(3)?,
        })
    });

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

pub(crate) fn current_revision(conn: &Connection) -> Result<u64> {
    let result = conn.query_row(
        "SELECT revision FROM execution_gates 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: &ExecutionGateStateFile) -> Result<u64> {
    let next_revision = current_revision(conn)?.saturating_add(1).max(1);
    let gates_json = serde_json::to_string(&state.gates)?;
    conn.execute(
        "INSERT OR REPLACE INTO execution_gates
            (id, schema_version, seeded_from, gates, revision)
         VALUES (1, ?1, ?2, ?3, ?4)",
        rusqlite::params![
            state.schema_version,
            state.seeded_from,
            gates_json,
            next_revision
        ],
    )?;
    Ok(next_revision)
}

pub(crate) fn write_if_revision_matches(
    conn: &Connection,
    state: &ExecutionGateStateFile,
    expected_revision: u64,
) -> Result<ExclusiveWriteResult> {
    let gates_json = serde_json::to_string(&state.gates)?;
    if expected_revision == 0 {
        let inserted = conn.execute(
            "INSERT OR IGNORE INTO execution_gates
                (id, schema_version, seeded_from, gates, revision)
             VALUES (1, ?1, ?2, ?3, 1)",
            rusqlite::params![state.schema_version, state.seeded_from, gates_json],
        )?;
        return if inserted == 1 {
            Ok(ExclusiveWriteResult::Applied { revision: 1 })
        } else {
            Ok(ExclusiveWriteResult::RevisionConflict {
                current_revision: current_revision(conn)?,
            })
        };
    }

    let updated = conn.execute(
        "UPDATE execution_gates
         SET schema_version = ?1,
             seeded_from = ?2,
             gates = ?3,
             revision = revision + 1
         WHERE id = 1 AND revision = ?4",
        rusqlite::params![
            state.schema_version,
            state.seeded_from,
            gates_json,
            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 delete(conn: &Connection) -> Result<()> {
    conn.execute("DELETE FROM execution_gates WHERE id = 1", [])?;
    Ok(())
}

pub(crate) fn delete_if_revision_matches(
    conn: &Connection,
    expected_revision: u64,
) -> Result<ExclusiveDeleteResult> {
    if expected_revision == 0 {
        let current_revision = current_revision(conn)?;
        return Ok(if current_revision == 0 {
            ExclusiveDeleteResult::IdempotentNoop
        } else {
            ExclusiveDeleteResult::RevisionConflict { current_revision }
        });
    }

    let deleted = conn.execute(
        "DELETE FROM execution_gates WHERE id = 1 AND revision = ?1",
        rusqlite::params![expected_revision],
    )?;
    if deleted == 1 {
        Ok(ExclusiveDeleteResult::Applied)
    } else {
        Ok(ExclusiveDeleteResult::RevisionConflict {
            current_revision: current_revision(conn)?,
        })
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::db::schema;
    use crate::state::session_gates::{ExecutionGate, ExecutionGateStatus};

    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());
    }

    #[test]
    fn write_and_read_roundtrip() {
        let conn = test_conn();
        let state = ExecutionGateStateFile {
            schema_version: 1,
            seeded_from: Some("handoff:immediate_actions".to_owned()),
            revision: 0,
            gates: vec![
                ExecutionGate {
                    text: "First".to_owned(),
                    status: ExecutionGateStatus::Open,
                },
                ExecutionGate {
                    text: "Second".to_owned(),
                    status: ExecutionGateStatus::Blocked,
                },
            ],
        };

        let revision = write(&conn, &state).unwrap();
        assert_eq!(revision, 1);
        let loaded = read(&conn).unwrap().expect("execution gates");
        assert_eq!(
            loaded.seeded_from.as_deref(),
            Some("handoff:immediate_actions")
        );
        assert_eq!(loaded.revision, 1);
        assert_eq!(loaded.gates.len(), 2);
        assert_eq!(loaded.gates[1].status, ExecutionGateStatus::Blocked);
    }

    #[test]
    fn delete_clears_execution_gates() {
        let conn = test_conn();
        let state = ExecutionGateStateFile {
            schema_version: 1,
            seeded_from: None,
            revision: 0,
            gates: vec![ExecutionGate {
                text: "Only".to_owned(),
                status: ExecutionGateStatus::Open,
            }],
        };
        write(&conn, &state).unwrap();
        delete(&conn).unwrap();
        assert!(read(&conn).unwrap().is_none());
    }

    #[test]
    fn write_if_revision_matches_detects_conflicts() {
        let conn = test_conn();
        let state = ExecutionGateStateFile {
            schema_version: 1,
            seeded_from: None,
            revision: 0,
            gates: vec![ExecutionGate {
                text: "Only".to_owned(),
                status: ExecutionGateStatus::Open,
            }],
        };

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

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

        let zero_conflict = delete_if_revision_matches(&conn, 0).unwrap();
        assert_eq!(
            zero_conflict,
            ExclusiveDeleteResult::RevisionConflict {
                current_revision: 1
            }
        );

        let deleted = delete_if_revision_matches(&conn, 1).unwrap();
        assert_eq!(deleted, ExclusiveDeleteResult::Applied);
        let noop = delete_if_revision_matches(&conn, 0).unwrap();
        assert_eq!(noop, ExclusiveDeleteResult::IdempotentNoop);
    }
}
