use chrono::Utc;
use tracing::debug;
use uuid::Uuid;

use crate::types::error::{HorizonError, PostgresError, Result};
use crate::types::model::MissionPlatform;

use super::PostgresRepository;

impl PostgresRepository {
    /// Delete a mission-platform link by ID. Returns the number of rows removed.
    pub async fn delete_mission_platform(&self, id: Uuid) -> Result<u64> {
        let result = sqlx::query!(
            "DELETE FROM horizon_public.mission_platform WHERE id = $1",
            id
        )
        .execute(&self.pool)
        .await?;
        Ok(result.rows_affected())
    }

    /// Create a new mission-platform link.
    pub async fn insert_mission_platform(
        &self,
        mission_platform: &MissionPlatform,
    ) -> Result<MissionPlatform> {
        let now = Utc::now();
        let org = self.organization_id.or(mission_platform.organization_id);
        let result = sqlx::query_as!(
            MissionPlatform,
            r#"
            INSERT INTO horizon_public.mission_platform
                (id, mission_id, platform_id, organization_id, modified_datetime)
            VALUES (COALESCE($1, gen_random_uuid()), $2, $3, $4, $5)
            RETURNING id, created_datetime, modified_datetime,
                      mission_id, platform_id, organization_id
            "#,
            mission_platform.id,
            mission_platform.mission_id,
            mission_platform.platform_id,
            org,
            now,
        )
        .fetch_one(&self.pool)
        .await?;
        debug!(
            mission_platform_id = ?result.id,
            mission_id = ?result.mission_id,
            platform_id = ?result.platform_id,
            "insert_mission_platform"
        );
        Ok(result)
    }

    /// List all mission-platform link records.
    pub async fn list_mission_platforms(&self) -> Result<Vec<MissionPlatform>> {
        Ok(sqlx::query_as!(
            MissionPlatform,
            r#"
            SELECT id, created_datetime, modified_datetime,
                   mission_id, platform_id, organization_id
            FROM horizon_public.mission_platform
            "#,
        )
        .fetch_all(&self.pool)
        .await?)
    }

    /// Read a mission-platform link by ID.
    pub async fn read_mission_platform(&self, id: Uuid) -> Result<Option<MissionPlatform>> {
        Ok(sqlx::query_as!(
            MissionPlatform,
            r#"
            SELECT id, created_datetime, modified_datetime,
                   mission_id, platform_id, organization_id
            FROM horizon_public.mission_platform WHERE id = $1
            "#,
            id,
        )
        .fetch_optional(&self.pool)
        .await?)
    }

    /// Update an existing mission-platform link by ID.
    ///
    /// Returns `HorizonError::Postgres(PostgresError::NotFound)` if no row
    /// with the given ID exists. Matches Python `BaseRepository.update()`
    /// semantics.
    pub async fn update_mission_platform(
        &self,
        mission_platform: &MissionPlatform,
    ) -> Result<MissionPlatform> {
        let now = Utc::now();
        let org = self.organization_id.or(mission_platform.organization_id);
        let result = sqlx::query_as!(
            MissionPlatform,
            r#"
            UPDATE horizon_public.mission_platform
            SET
                mission_id = $2,
                platform_id = $3,
                organization_id = $4,
                modified_datetime = $5
            WHERE id = $1
            RETURNING id, created_datetime, modified_datetime,
                      mission_id, platform_id, organization_id
            "#,
            mission_platform.id,
            mission_platform.mission_id,
            mission_platform.platform_id,
            org,
            now,
        )
        .fetch_optional(&self.pool)
        .await?;

        result.ok_or_else(|| {
            HorizonError::Postgres(PostgresError::NotFound {
                entity: "mission_platform".to_owned(),
                id: mission_platform.id.into(),
            })
        })
    }
}
