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 WeatherThresholdUpdateInput {
    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,
        input: WeatherThresholdUpdateInput,
    ) -> 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(input.condition_type)
        .bind(input.threshold_value)
        .bind(input.operator)
        .bind(input.enabled)
        .bind(input.description)
        .bind(input.id)
        .bind(input.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::{Executor, SqlitePool};

    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!(fetched.enabled);
        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(WeatherThresholdUpdateInput {
                id: th.id,
                user_id,
                condition_type: "rain".to_string(),
                threshold_value: 5.0,
                operator: "<".to_string(),
                enabled: false,
                description: 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!(!updated.enabled);
        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);
    }
}