use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::FromRow;
use utoipa::ToSchema;

#[derive(Debug, Serialize, Deserialize, FromRow, Clone, PartialEq, ToSchema)]
pub struct WeatherApiData {
    pub id: i64,
    pub user_id: i64,
    pub location_id: i64,
    pub api_type: String,
    pub data: String, // JSON data from the API
    #[sqlx(rename = "fetched_at")]
    pub fetched_at: String, // Store as string, convert when needed
}

impl WeatherApiData {
    #[allow(dead_code)]
    pub fn fetched_at_datetime(&self) -> Result<DateTime<Utc>, chrono::ParseError> {
        DateTime::parse_from_rfc3339(&self.fetched_at).map(|dt| dt.with_timezone(&Utc))
    }
}

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

impl<'a> WeatherApiDataRepository<'a> {
    pub async fn create_weather_data(
        &self,
        user_id: i64,
        location_id: i64,
        api_type: String,
        data: String,
    ) -> Result<WeatherApiData, sqlx::Error> {
        sqlx::query_as::<_, WeatherApiData>(
            "INSERT INTO weather_api_data (user_id, location_id, api_type, data) VALUES (?, ?, ?, ?) RETURNING id, user_id, location_id, api_type, data, fetched_at"
        )
        .bind(user_id)
        .bind(location_id)
        .bind(api_type)
        .bind(data)
        .fetch_one(self.db)
        .await
    }

    #[allow(dead_code)]
    pub async fn get_latest_weather_data(
        &self,
        user_id: i64,
        location_id: i64,
        api_type: &str,
    ) -> Result<Option<WeatherApiData>, sqlx::Error> {
        sqlx::query_as::<_, WeatherApiData>(
            "SELECT id, user_id, location_id, api_type, data, fetched_at FROM weather_api_data WHERE user_id = ? AND location_id = ? AND api_type = ? ORDER BY fetched_at DESC LIMIT 1"
        )
        .bind(user_id)
        .bind(location_id)
        .bind(api_type)
        .fetch_optional(self.db)
        .await
    }

    #[allow(dead_code)]
    pub async fn list_weather_data_by_user(
        &self,
        user_id: i64,
        limit: Option<i64>,
    ) -> Result<Vec<WeatherApiData>, sqlx::Error> {
        let limit = limit.unwrap_or(100);
        sqlx::query_as::<_, WeatherApiData>(
            "SELECT id, user_id, location_id, api_type, data, fetched_at FROM weather_api_data WHERE user_id = ? ORDER BY fetched_at DESC LIMIT ?"
        )
        .bind(user_id)
        .bind(limit)
        .fetch_all(self.db)
        .await
    }

    #[allow(dead_code)]
    pub async fn delete_old_weather_data(&self, days_old: i64) -> Result<u64, sqlx::Error> {
        let result = sqlx::query(
            "DELETE FROM weather_api_data WHERE fetched_at < datetime('now', '-{} days')",
        )
        .bind(days_old)
        .execute(self.db)
        .await?;
        Ok(result.rows_affected())
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::locations::LocationRepository;
    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 locations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                latitude REAL NOT NULL,
                longitude REAL NOT NULL,
                user_id INTEGER NOT NULL,
                FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE NO ACTION
            );",
        )
        .await
        .unwrap();
        pool.execute(
            "CREATE TABLE weather_api_data (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                location_id INTEGER NOT NULL,
                api_type TEXT NOT NULL DEFAULT 'openweathermap',
                data TEXT NOT NULL,
                fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY(location_id) REFERENCES locations(id) ON DELETE CASCADE
            );",
        )
        .await
        .unwrap();
        pool
    }

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

    async fn create_location(pool: &SqlitePool, user_id: i64) -> i64 {
        let repo = LocationRepository { db: pool };
        let location = repo
            .create_location(60.1699, 24.9384, user_id) // Helsinki coordinates
            .await
            .unwrap();
        location.id
    }

    #[tokio::test]
    async fn test_create_and_get_weather_data() {
        let pool = setup_db().await;
        let user_id = create_user(&pool).await;
        let location_id = create_location(&pool, user_id).await;

        let repo = WeatherApiDataRepository { db: &pool };
        let test_data = r#"{"temp": 20.5, "humidity": 65}"#.to_string();

        let weather_data = repo
            .create_weather_data(
                user_id,
                location_id,
                "openweathermap".to_string(),
                test_data.clone(),
            )
            .await
            .unwrap();

        assert_eq!(weather_data.user_id, user_id);
        assert_eq!(weather_data.location_id, location_id);
        assert_eq!(weather_data.api_type, "openweathermap");
        assert_eq!(weather_data.data, test_data);

        let latest = repo
            .get_latest_weather_data(user_id, location_id, "openweathermap")
            .await
            .unwrap()
            .unwrap();

        assert_eq!(latest.id, weather_data.id);
        assert_eq!(latest.data, test_data);
    }

    #[tokio::test]
    async fn test_list_weather_data_by_user() {
        let pool = setup_db().await;
        let user_id = create_user(&pool).await;
        let location_id = create_location(&pool, user_id).await;

        let repo = WeatherApiDataRepository { db: &pool };
        let test_data1 = r#"{"temp": 20.5}"#.to_string();
        let test_data2 = r#"{"temp": 22.0}"#.to_string();

        repo.create_weather_data(
            user_id,
            location_id,
            "openweathermap".to_string(),
            test_data1.clone(),
        )
        .await
        .unwrap();
        repo.create_weather_data(
            user_id,
            location_id,
            "openweathermap".to_string(),
            test_data2.clone(),
        )
        .await
        .unwrap();

        let data_list = repo
            .list_weather_data_by_user(user_id, Some(10))
            .await
            .unwrap();
        assert_eq!(data_list.len(), 2);
        // Check that both data entries are present (order may vary)
        let data_values: Vec<&str> = data_list.iter().map(|d| d.data.as_str()).collect();
        assert!(data_values.contains(&test_data1.as_str()));
        assert!(data_values.contains(&test_data2.as_str()));
    }
}