feat: add weather pooler and config

1 files changed, 227 insertions, 0 deletions

diff --git a/src/weather_api_data.rs b/src/weather_api_data.rs
new file mode 100644
index 0000000..14a1cac
--- /dev/null
+++ b/src/weather_api_data.rs
@@ -0,0 +1,227 @@
+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()));
+    }
+}