use serde::{Deserialize, Serialize};
use sqlx::FromRow;

#[derive(Debug, Serialize, Deserialize, FromRow, Clone, PartialEq)]
pub struct WeatherThreshold {
    pub id: i64,
    pub user_id: i64,
    pub condition_type: String,
    pub threshold_value: f64,
    pub operator: String,
    pub enabled: bool,
    pub description: Option<String>,
}

pub struct WeatherThresholdRepository<'a> {
    pub db: &'a sqlx::SqlitePool,
}

impl<'a> WeatherThresholdRepository<'a> {
    pub async fn list_thresholds(&self, user_id: i64) -> Result<Vec<WeatherThreshold>, sqlx::Error> {
        sqlx::query_as::<_, WeatherThreshold>(
            "SELECT id, user_id, condition_type, threshold_value, operator, enabled, description FROM weather_thresholds WHERE user_id = ?"
        )
        .bind(user_id)
        .fetch_all(self.db)
        .await
    }

    pub async fn get_threshold(&self, id: i64, user_id: i64) -> Result<Option<WeatherThreshold>, sqlx::Error> {
        sqlx::query_as::<_, WeatherThreshold>(
            "SELECT id, user_id, condition_type, threshold_value, operator, enabled, description FROM weather_thresholds WHERE id = ? AND user_id = ?"
        )
        .bind(id)
        .bind(user_id)
        .fetch_optional(self.db)
        .await
    }

    pub async fn create_threshold(&self, user_id: i64, condition_type: String, threshold_value: f64, operator: String, enabled: bool, description: Option<String>) -> Result<WeatherThreshold, sqlx::Error> {
        sqlx::query_as::<_, WeatherThreshold>(
            "INSERT INTO weather_thresholds (user_id, condition_type, threshold_value, operator, enabled, description) VALUES (?, ?, ?, ?, ?, ?) RETURNING id, user_id, condition_type, threshold_value, operator, enabled, description"
        )
        .bind(user_id)
        .bind(condition_type)
        .bind(threshold_value)
        .bind(operator)
        .bind(enabled)
        .bind(description)
        .fetch_one(self.db)
        .await
    }

    pub async fn update_threshold(&self, id: i64, user_id: i64, condition_type: String, threshold_value: f64, operator: String, enabled: bool, description: Option<String>) -> Result<WeatherThreshold, sqlx::Error> {
        sqlx::query_as::<_, WeatherThreshold>(
            "UPDATE weather_thresholds SET condition_type = ?, threshold_value = ?, operator = ?, enabled = ?, description = ? WHERE id = ? AND user_id = ? RETURNING id, user_id, condition_type, threshold_value, operator, enabled, description"
        )
        .bind(condition_type)
        .bind(threshold_value)
        .bind(operator)
        .bind(enabled)
        .bind(description)
        .bind(id)
        .bind(user_id)
        .fetch_one(self.db)
        .await
    }

    pub async fn delete_threshold(&self, id: i64, user_id: i64) -> Result<(), sqlx::Error> {
        sqlx::query("DELETE FROM weather_thresholds WHERE id = ? AND user_id = ?")
            .bind(id)
            .bind(user_id)
            .execute(self.db)
            .await?;
        Ok(())
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::users::{UserRepository, UserRole};
    use sqlx::{SqlitePool, Executor};
    use tokio;

    async fn setup_db() -> SqlitePool {
        let pool = SqlitePool::connect(":memory:").await.unwrap();
        pool.execute(
            "CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id TEXT NOT NULL UNIQUE,
                role TEXT NOT NULL DEFAULT 'user'
            );"
        ).await.unwrap();
        pool.execute(
            "CREATE TABLE weather_thresholds (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                condition_type TEXT NOT NULL,
                threshold_value REAL NOT NULL,
                operator TEXT NOT NULL,
                enabled BOOLEAN NOT NULL DEFAULT 1,
                description TEXT,
                FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
            );"
        ).await.unwrap();
        pool
    }

    async fn create_user(pool: &SqlitePool) -> i64 {
        let repo = UserRepository { db: pool };
        let user = repo.create_user(None, Some(UserRole::User)).await.unwrap();
        user.id
    }

    #[tokio::test]
    async fn test_create_and_get_threshold() {
        let db = setup_db().await;
        let user_id = create_user(&db).await;
        let repo = WeatherThresholdRepository { db: &db };
        let th = repo.create_threshold(user_id, "wind_speed".to_string(), 10.0, ">".to_string(), true, Some("desc".to_string())).await.unwrap();
        let fetched = repo.get_threshold(th.id, user_id).await.unwrap().unwrap();
        assert_eq!(fetched.condition_type, "wind_speed");
        assert_eq!(fetched.threshold_value, 10.0);
        assert_eq!(fetched.operator, ">"
        );
        assert_eq!(fetched.enabled, true);
        assert_eq!(fetched.description, Some("desc".to_string()));
    }

    #[tokio::test]
    async fn test_update_threshold() {
        let db = setup_db().await;
        let user_id = create_user(&db).await;
        let repo = WeatherThresholdRepository { db: &db };
        let th = repo.create_threshold(user_id, "wind_speed".to_string(), 10.0, ">".to_string(), true, None).await.unwrap();
        let updated = repo.update_threshold(th.id, user_id, "rain".to_string(), 5.0, "<".to_string(), false, Some("rain desc".to_string())).await.unwrap();
        assert_eq!(updated.condition_type, "rain");
        assert_eq!(updated.threshold_value, 5.0);
        assert_eq!(updated.operator, "<");
        assert_eq!(updated.enabled, false);
        assert_eq!(updated.description, Some("rain desc".to_string()));
    }

    #[tokio::test]
    async fn test_delete_threshold() {
        let db = setup_db().await;
        let user_id = create_user(&db).await;
        let repo = WeatherThresholdRepository { db: &db };
        let th = repo.create_threshold(user_id, "wind_speed".to_string(), 10.0, ">".to_string(), true, None).await.unwrap();
        repo.delete_threshold(th.id, user_id).await.unwrap();
        let fetched = repo.get_threshold(th.id, user_id).await.unwrap();
        assert!(fetched.is_none());
    }

    #[tokio::test]
    async fn test_list_thresholds() {
        let db = setup_db().await;
        let user_id = create_user(&db).await;
        let repo = WeatherThresholdRepository { db: &db };
        repo.create_threshold(user_id, "wind_speed".to_string(), 10.0, ">".to_string(), true, None).await.unwrap();
        repo.create_threshold(user_id, "rain".to_string(), 5.0, "<".to_string(), false, None).await.unwrap();
        let thresholds = repo.list_thresholds(user_id).await.unwrap();
        assert_eq!(thresholds.len(), 2);
    }
}