use crate::db::DbPool;
use chrono::Utc;
use serde::{Deserialize, Serialize};
use uuid::Uuid;

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct UserPool {
    pub id: String,
    pub name: String,
    pub config_json: serde_json::Value,
    pub created_at: String,
    pub updated_at: String,
}

pub fn generate_pool_id(region: &str) -> String {
    let suffix: String = Uuid::new_v4()
        .to_string()
        .replace('-', "")
        .chars()
        .take(9)
        .collect();
    format!("{region}_{suffix}")
}

pub fn create(db: &DbPool, id: &str, name: &str, config: &serde_json::Value) -> rusqlite::Result<UserPool> {
    let conn = db.lock().unwrap();
    let now = Utc::now().to_rfc3339();
    let config_str = serde_json::to_string(config).unwrap_or_default();
    conn.execute(
        "INSERT INTO user_pools (id, name, config_json, created_at, updated_at) VALUES (?1, ?2, ?3, ?4, ?5)",
        rusqlite::params![id, name, config_str, now, now],
    )?;
    Ok(UserPool {
        id: id.to_string(),
        name: name.to_string(),
        config_json: config.clone(),
        created_at: now.clone(),
        updated_at: now,
    })
}

pub fn get(db: &DbPool, id: &str) -> rusqlite::Result<Option<UserPool>> {
    let conn = db.lock().unwrap();
    let mut stmt = conn.prepare(
        "SELECT id, name, config_json, created_at, updated_at FROM user_pools WHERE id = ?1"
    )?;
    let mut rows = stmt.query(rusqlite::params![id])?;
    match rows.next()? {
        Some(row) => {
            let config_str: String = row.get(2)?;
            Ok(Some(UserPool {
                id: row.get(0)?,
                name: row.get(1)?,
                config_json: serde_json::from_str(&config_str).unwrap_or_default(),
                created_at: row.get(3)?,
                updated_at: row.get(4)?,
            }))
        }
        None => Ok(None),
    }
}

pub fn list(db: &DbPool, max_results: usize) -> rusqlite::Result<Vec<UserPool>> {
    let conn = db.lock().unwrap();
    let mut stmt = conn.prepare(
        "SELECT id, name, config_json, created_at, updated_at FROM user_pools ORDER BY created_at LIMIT ?1"
    )?;
    let rows = stmt.query_map(rusqlite::params![max_results as i64], |row| {
        let config_str: String = row.get(2)?;
        Ok(UserPool {
            id: row.get(0)?,
            name: row.get(1)?,
            config_json: serde_json::from_str(&config_str).unwrap_or_default(),
            created_at: row.get(3)?,
            updated_at: row.get(4)?,
        })
    })?;
    rows.collect()
}

pub fn delete(db: &DbPool, id: &str) -> rusqlite::Result<bool> {
    let conn = db.lock().unwrap();
    let affected = conn.execute("DELETE FROM user_pools WHERE id = ?1", rusqlite::params![id])?;
    Ok(affected > 0)
}

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

    fn setup() -> DbPool {
        let pool = db::connect_in_memory().unwrap();
        db::run_migrations(&pool).unwrap();
        pool
    }

    #[test]
    fn test_create_and_get_pool() {
        let db = setup();
        let pool = create(&db, "us-east-1_test123", "TestPool", &serde_json::json!({})).unwrap();
        assert_eq!(pool.name, "TestPool");

        let fetched = get(&db, "us-east-1_test123").unwrap().unwrap();
        assert_eq!(fetched.name, "TestPool");
    }

    #[test]
    fn test_get_nonexistent_pool() {
        let db = setup();
        let result = get(&db, "us-east-1_nope").unwrap();
        assert!(result.is_none());
    }

    #[test]
    fn test_list_pools() {
        let db = setup();
        create(&db, "us-east-1_aaa", "Pool1", &serde_json::json!({})).unwrap();
        create(&db, "us-east-1_bbb", "Pool2", &serde_json::json!({})).unwrap();
        let pools = list(&db, 10).unwrap();
        assert_eq!(pools.len(), 2);
    }

    #[test]
    fn test_delete_pool() {
        let db = setup();
        create(&db, "us-east-1_del", "ToDelete", &serde_json::json!({})).unwrap();
        assert!(delete(&db, "us-east-1_del").unwrap());
        assert!(get(&db, "us-east-1_del").unwrap().is_none());
    }

    #[test]
    fn test_delete_nonexistent_returns_false() {
        let db = setup();
        assert!(!delete(&db, "us-east-1_nope").unwrap());
    }

    #[test]
    fn test_generate_pool_id_format() {
        let id = generate_pool_id("us-east-1");
        assert!(id.starts_with("us-east-1_"));
        assert!(id.len() > 10);
    }
}
