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

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Group {
    pub pool_id: String,
    pub group_name: String,
    pub description: Option<String>,
    pub precedence: Option<i32>,
    pub created_at: String,
    pub updated_at: String,
}

pub fn create(
    db: &DbPool,
    pool_id: &str,
    group_name: &str,
    description: Option<&str>,
    precedence: Option<i32>,
) -> rusqlite::Result<Group> {
    let conn = db.lock().unwrap();
    let now = Utc::now().to_rfc3339();
    conn.execute(
        "INSERT INTO groups (pool_id, group_name, description, precedence, created_at, updated_at) VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
        rusqlite::params![pool_id, group_name, description, precedence, now, now],
    )?;
    Ok(Group {
        pool_id: pool_id.to_string(),
        group_name: group_name.to_string(),
        description: description.map(String::from),
        precedence,
        created_at: now.clone(),
        updated_at: now,
    })
}

pub fn list(db: &DbPool, pool_id: &str, limit: usize) -> rusqlite::Result<Vec<Group>> {
    let conn = db.lock().unwrap();
    let mut stmt = conn.prepare(
        "SELECT pool_id, group_name, description, precedence, created_at, updated_at FROM groups WHERE pool_id = ?1 ORDER BY group_name LIMIT ?2"
    )?;
    let rows = stmt.query_map(rusqlite::params![pool_id, limit as i64], |row| {
        Ok(Group {
            pool_id: row.get(0)?,
            group_name: row.get(1)?,
            description: row.get(2)?,
            precedence: row.get(3)?,
            created_at: row.get(4)?,
            updated_at: row.get(5)?,
        })
    })?;
    rows.collect()
}

pub fn add_user_to_group(db: &DbPool, pool_id: &str, username: &str, group_name: &str) -> rusqlite::Result<()> {
    let conn = db.lock().unwrap();
    conn.execute(
        "INSERT OR IGNORE INTO user_groups (pool_id, username, group_name) VALUES (?1, ?2, ?3)",
        rusqlite::params![pool_id, username, group_name],
    )?;
    Ok(())
}

pub fn list_groups_for_user(db: &DbPool, pool_id: &str, username: &str) -> rusqlite::Result<Vec<Group>> {
    let conn = db.lock().unwrap();
    let mut stmt = conn.prepare(
        "SELECT g.pool_id, g.group_name, g.description, g.precedence, g.created_at, g.updated_at
         FROM groups g
         JOIN user_groups ug ON g.pool_id = ug.pool_id AND g.group_name = ug.group_name
         WHERE ug.pool_id = ?1 AND ug.username = ?2
         ORDER BY g.group_name"
    )?;
    let rows = stmt.query_map(rusqlite::params![pool_id, username], |row| {
        Ok(Group {
            pool_id: row.get(0)?,
            group_name: row.get(1)?,
            description: row.get(2)?,
            precedence: row.get(3)?,
            created_at: row.get(4)?,
            updated_at: row.get(5)?,
        })
    })?;
    rows.collect()
}

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

    fn setup() -> DbPool {
        let pool = db::connect_in_memory().unwrap();
        db::run_migrations(&pool).unwrap();
        db::pools::create(&pool, "us-east-1_test", "Test", &serde_json::json!({})).unwrap();
        db::users::create(&pool, "us-east-1_test", "alice", None, "CONFIRMED", &[]).unwrap();
        pool
    }

    #[test]
    fn test_create_and_list_groups() {
        let db = setup();
        create(&db, "us-east-1_test", "admins", Some("Admin group"), Some(1)).unwrap();
        create(&db, "us-east-1_test", "users", None, None).unwrap();
        let groups = list(&db, "us-east-1_test", 10).unwrap();
        assert_eq!(groups.len(), 2);
        assert_eq!(groups[0].group_name, "admins");
    }

    #[test]
    fn test_add_user_to_group_and_list() {
        let db = setup();
        create(&db, "us-east-1_test", "admins", None, None).unwrap();
        add_user_to_group(&db, "us-east-1_test", "alice", "admins").unwrap();
        let groups = list_groups_for_user(&db, "us-east-1_test", "alice").unwrap();
        assert_eq!(groups.len(), 1);
        assert_eq!(groups[0].group_name, "admins");
    }

    #[test]
    fn test_add_user_to_group_idempotent() {
        let db = setup();
        create(&db, "us-east-1_test", "admins", None, None).unwrap();
        add_user_to_group(&db, "us-east-1_test", "alice", "admins").unwrap();
        add_user_to_group(&db, "us-east-1_test", "alice", "admins").unwrap();
        let groups = list_groups_for_user(&db, "us-east-1_test", "alice").unwrap();
        assert_eq!(groups.len(), 1);
    }
}
