use chrono::Utc;
use uuid::Uuid;

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

use super::PostgresRepository;

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

    /// Create an audio specification.
    pub async fn insert_audio_specification(
        &self,
        spec: &AudioSpecification,
    ) -> Result<AudioSpecification> {
        let now = Utc::now();
        let org = self.organization_id.or(spec.organization_id);
        Ok(sqlx::query_as!(
            AudioSpecification,
            r#"
            INSERT INTO horizon_public.audio_specification
                (id, sample_rate, bit_depth, channel_count, channel_index,
                 encoding, organization_id, modified_datetime)
            VALUES (COALESCE($1, gen_random_uuid()), $2, $3, $4, $5, $6, $7, $8)
            RETURNING id, created_datetime, modified_datetime,
                      sample_rate, bit_depth, channel_count, channel_index,
                      encoding, organization_id
            "#,
            spec.id,
            spec.sample_rate,
            spec.bit_depth,
            spec.channel_count,
            spec.channel_index,
            spec.encoding,
            org,
            now,
        )
        .fetch_one(&self.pool)
        .await?)
    }

    /// List all audio specification records.
    pub async fn list_audio_specifications(&self) -> Result<Vec<AudioSpecification>> {
        Ok(sqlx::query_as!(
            AudioSpecification,
            r#"
            SELECT id, created_datetime, modified_datetime,
                   sample_rate, bit_depth, channel_count, channel_index,
                   encoding, organization_id
            FROM horizon_public.audio_specification
            "#,
        )
        .fetch_all(&self.pool)
        .await?)
    }

    /// Read an audio specification by ID.
    pub async fn read_audio_specification(&self, id: Uuid) -> Result<Option<AudioSpecification>> {
        Ok(sqlx::query_as!(
            AudioSpecification,
            r#"
            SELECT id, created_datetime, modified_datetime,
                   sample_rate, bit_depth, channel_count, channel_index,
                   encoding, organization_id
            FROM horizon_public.audio_specification WHERE id = $1
            "#,
            id,
        )
        .fetch_optional(&self.pool)
        .await?)
    }

    /// Update an existing audio specification by ID.
    ///
    /// Returns `HorizonError::Postgres(PostgresError::NotFound)` if no row
    /// with the given ID exists. Matches Python `BaseRepository.update()`
    /// semantics.
    pub async fn update_audio_specification(
        &self,
        spec: &AudioSpecification,
    ) -> Result<AudioSpecification> {
        let now = Utc::now();
        let org = self.organization_id.or(spec.organization_id);
        let result = sqlx::query_as!(
            AudioSpecification,
            r#"
            UPDATE horizon_public.audio_specification
            SET
                sample_rate = $2,
                bit_depth = $3,
                channel_count = $4,
                channel_index = $5,
                encoding = $6,
                organization_id = $7,
                modified_datetime = $8
            WHERE id = $1
            RETURNING id, created_datetime, modified_datetime,
                      sample_rate, bit_depth, channel_count, channel_index,
                      encoding, organization_id
            "#,
            spec.id,
            spec.sample_rate,
            spec.bit_depth,
            spec.channel_count,
            spec.channel_index,
            spec.encoding,
            org,
            now,
        )
        .fetch_optional(&self.pool)
        .await?;

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

    /// Insert an audio specification or update it if a row with the same ID
    /// already exists. Sets `modified_datetime` to now and falls back to the
    /// repository's `organization_id` when the model does not provide one.
    pub async fn upsert_audio_specification(
        &self,
        spec: &AudioSpecification,
    ) -> Result<AudioSpecification> {
        let now = Utc::now();
        let org = self.organization_id.or(spec.organization_id);
        Ok(sqlx::query_as!(
            AudioSpecification,
            r#"
            INSERT INTO horizon_public.audio_specification
                (id, sample_rate, bit_depth, channel_count, channel_index,
                 encoding, organization_id, modified_datetime)
            VALUES (COALESCE($1, gen_random_uuid()), $2, $3, $4, $5, $6, $7, $8)
            ON CONFLICT (id) DO UPDATE SET
                sample_rate = EXCLUDED.sample_rate,
                bit_depth = EXCLUDED.bit_depth,
                channel_count = EXCLUDED.channel_count,
                channel_index = EXCLUDED.channel_index,
                encoding = EXCLUDED.encoding,
                organization_id = EXCLUDED.organization_id,
                modified_datetime = EXCLUDED.modified_datetime
            RETURNING id, created_datetime, modified_datetime,
                      sample_rate, bit_depth, channel_count, channel_index,
                      encoding, organization_id
            "#,
            spec.id,
            spec.sample_rate,
            spec.bit_depth,
            spec.channel_count,
            spec.channel_index,
            spec.encoding,
            org,
            now,
        )
        .fetch_one(&self.pool)
        .await?)
    }
}
