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

#[derive(Debug, Serialize, Deserialize, FromRow, Clone, PartialEq)]
pub struct Location {
    pub id: i64,
    pub latitude: f64,
    pub longitude: f64,
    pub user_id: i64,
}

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

impl<'a> LocationRepository<'a> {
    pub async fn list_locations(&self) -> Result<Vec<Location>, sqlx::Error> {
        sqlx::query_as::<_, Location>("SELECT id, latitude, longitude, user_id FROM locations")
            .fetch_all(self.db)
            .await
    }

    pub async fn get_location(&self, id: i64) -> Result<Option<Location>, sqlx::Error> {
        sqlx::query_as::<_, Location>("SELECT id, latitude, longitude, user_id FROM locations WHERE id = ?")
            .bind(id)
            .fetch_optional(self.db)
            .await
    }

    pub async fn create_location(&self, latitude: f64, longitude: f64, user_id: i64) -> Result<Location, sqlx::Error> {
        sqlx::query_as::<_, Location>(
            "INSERT INTO locations (latitude, longitude, user_id) VALUES (?, ?, ?) RETURNING id, latitude, longitude, user_id"
        )
        .bind(latitude)
        .bind(longitude)
        .bind(user_id)
        .fetch_one(self.db)
        .await
    }

    pub async fn update_location(&self, id: i64, latitude: f64, longitude: f64) -> Result<Location, sqlx::Error> {
        sqlx::query_as::<_, Location>(
            "UPDATE locations SET latitude = ?, longitude = ? WHERE id = ? RETURNING id, latitude, longitude, user_id"
        )
        .bind(latitude)
        .bind(longitude)
        .bind(id)
        .fetch_one(self.db)
        .await
    }

    pub async fn delete_location(&self, id: i64) -> Result<(), sqlx::Error> {
        sqlx::query("DELETE FROM locations WHERE id = ?")
            .bind(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 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
    }

    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_location() {
        let db = setup_db().await;
        let user_id = create_user(&db).await;
        let repo = LocationRepository { db: &db };
        let loc = repo.create_location(60.0, 24.0, user_id).await.unwrap();
        let fetched = repo.get_location(loc.id).await.unwrap().unwrap();
        assert_eq!(fetched.latitude, 60.0);
        assert_eq!(fetched.longitude, 24.0);
        assert_eq!(fetched.user_id, user_id);
    }

    #[tokio::test]
    async fn test_update_location() {
        let db = setup_db().await;
        let user_id = create_user(&db).await;
        let repo = LocationRepository { db: &db };
        let loc = repo.create_location(60.0, 24.0, user_id).await.unwrap();
        let updated = repo.update_location(loc.id, 61.0, 25.0).await.unwrap();
        assert_eq!(updated.latitude, 61.0);
        assert_eq!(updated.longitude, 25.0);
    }

    #[tokio::test]
    async fn test_delete_location() {
        let db = setup_db().await;
        let user_id = create_user(&db).await;
        let repo = LocationRepository { db: &db };
        let loc = repo.create_location(60.0, 24.0, user_id).await.unwrap();
        repo.delete_location(loc.id).await.unwrap();
        let fetched = repo.get_location(loc.id).await.unwrap();
        assert!(fetched.is_none());
    }

    #[tokio::test]
    async fn test_list_locations() {
        let db = setup_db().await;
        let user_id = create_user(&db).await;
        let repo = LocationRepository { db: &db };
        repo.create_location(60.0, 24.0, user_id).await.unwrap();
        repo.create_location(61.0, 25.0, user_id).await.unwrap();
        let locations = repo.list_locations().await.unwrap();
        assert_eq!(locations.len(), 2);
    }
}