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

use crate::types::error::{HorizonError, PostgresError, Result};
use crate::types::model::BearingTimeRecordSpecification;
use sqlx::{Postgres, QueryBuilder};

use super::PostgresRepository;

impl PostgresRepository {
    /// Batch-insert bearing-time-record specs (multi-row INSERT) and return their
    /// server-generated ids. Used to give each `DataRow` a distinct
    /// `specification_id`. Not timed — this is benchmark setup.
    pub async fn bulk_insert_bearing_time_record_specification(
        &self,
        specs: &[BearingTimeRecordSpecification],
    ) -> Result<Vec<Uuid>> {
        const COLUMN_COUNT: usize = 20;
        #[allow(
            clippy::decimal_literal_representation,
            reason = "Integer representation is more readable for this applications"
        )]
        const MAX_PARAMETER_COUNT: usize = 65_535;
        #[allow(
            clippy::integer_division,
            clippy::integer_division_remainder_used,
            reason = "Can only perform whole writes at once"
        )]
        let max_writes = MAX_PARAMETER_COUNT / COLUMN_COUNT;

        let now = Utc::now();

        let mut id_vector: Vec<Uuid> = Vec::with_capacity(specs.len());
        for chunk in specs.chunks(max_writes) {
            let mut query_builder: QueryBuilder<Postgres> = QueryBuilder::new(
                "INSERT INTO horizon_public.bearing_time_record_specification \
                 (id, amplitude_unit_mode, baffle_bearing_list, bearing_bin_count, \
                  elevation_increment, focus_range, frequency_spacing, heading_data_type, \
                  heading_vector_index, lower_elevation, max_frequency, max_pixel, \
                  min_frequency, name, fft_sample_count, normalizer, organization_id, \
                  upper_elevation, update_rate, modified_datetime) ",
            );
            query_builder.push_values(chunk, |mut b, spec| {
                b.push("COALESCE(")
                    .push_bind_unseparated(spec.id)
                    .push_unseparated(", gen_random_uuid())")
                    .push_bind(spec.amplitude_unit_mode.clone())
                    .push_bind(spec.baffle_bearing_list.clone())
                    .push_bind(spec.bearing_bin_count)
                    .push_bind(spec.elevation_increment)
                    .push_bind(spec.focus_range.clone())
                    .push_bind(spec.frequency_spacing)
                    .push_bind(spec.heading_data_type.clone())
                    .push_bind(spec.heading_vector_index)
                    .push_bind(spec.lower_elevation)
                    .push_bind(spec.max_frequency)
                    .push_bind(spec.max_pixel)
                    .push_bind(spec.min_frequency)
                    .push_bind(spec.name.clone())
                    .push_bind(spec.fft_sample_count)
                    .push_bind(spec.normalizer.clone())
                    .push_bind(self.organization_id.or(spec.organization_id))
                    .push_bind(spec.upper_elevation)
                    // update_rate is an interval column; convert the bound bigint ms.
                    .push_bind(spec.update_rate_ms)
                    .push_unseparated("::bigint * interval '1 millisecond'")
                    .push_bind(now);
            });
            query_builder.push(" RETURNING id");

            let id_batch: Vec<Uuid> = query_builder
                .build_query_scalar()
                .fetch_all(&self.pool)
                .await?;
            id_vector.extend(id_batch);
        }
        Ok(id_vector)
    }

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

    /// Create a new bearing-time-record specification.
    pub async fn insert_bearing_time_record_specification(
        &self,
        spec: &BearingTimeRecordSpecification,
    ) -> Result<BearingTimeRecordSpecification> {
        let now = Utc::now();
        let org = self.organization_id.or(spec.organization_id);
        Ok(sqlx::query_as!(
            BearingTimeRecordSpecification,
            r#"
            INSERT INTO horizon_public.bearing_time_record_specification
                (id, amplitude_unit_mode, baffle_bearing_list, bearing_bin_count, elevation_increment,
                 focus_range, frequency_spacing,
                 heading_data_type, heading_vector_index, lower_elevation,
                 max_frequency, max_pixel,
                 min_frequency, name, fft_sample_count, normalizer, organization_id,
                 upper_elevation, update_rate, modified_datetime)
            VALUES (COALESCE($1, gen_random_uuid()), $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18,
                    $19::bigint * interval '1 millisecond', $20)
            RETURNING id, amplitude_unit_mode, created_datetime, modified_datetime,
                      baffle_bearing_list, bearing_bin_count, elevation_increment,
                      focus_range, frequency_spacing,
                      heading_data_type, heading_vector_index, lower_elevation,
                      max_frequency, max_pixel,
                      min_frequency, name, fft_sample_count, normalizer, organization_id,
                      upper_elevation,
                      (EXTRACT(EPOCH FROM update_rate) * 1000)::bigint as "update_rate_ms: i64"
            "#,
            spec.id,
            spec.amplitude_unit_mode,
            spec.baffle_bearing_list.as_deref(),
            spec.bearing_bin_count,
            spec.elevation_increment,
            spec.focus_range,
            spec.frequency_spacing,
            spec.heading_data_type,
            spec.heading_vector_index,
            spec.lower_elevation,
            spec.max_frequency,
            spec.max_pixel,
            spec.min_frequency,
            spec.name,
            spec.fft_sample_count,
            spec.normalizer,
            org,
            spec.upper_elevation,
            spec.update_rate_ms,
            now,
        )
        .fetch_one(&self.pool)
        .await?)
    }

    /// List all bearing-time-record specification records.
    pub async fn list_bearing_time_record_specifications(
        &self,
    ) -> Result<Vec<BearingTimeRecordSpecification>> {
        Ok(sqlx::query_as!(
            BearingTimeRecordSpecification,
            r#"
            SELECT id, amplitude_unit_mode, created_datetime, modified_datetime,
                   baffle_bearing_list, bearing_bin_count, elevation_increment,
                   focus_range, frequency_spacing,
                   heading_data_type, heading_vector_index, lower_elevation,
                   max_frequency, max_pixel,
                   min_frequency, name, fft_sample_count, normalizer, organization_id,
                   upper_elevation,
                   (EXTRACT(EPOCH FROM update_rate) * 1000)::bigint as "update_rate_ms: i64"
            FROM horizon_public.bearing_time_record_specification
            "#,
        )
        .fetch_all(&self.pool)
        .await?)
    }

    /// Read a bearing-time-record specification by ID.
    pub async fn read_bearing_time_record_specification(
        &self,
        id: Uuid,
    ) -> Result<Option<BearingTimeRecordSpecification>> {
        Ok(sqlx::query_as!(
            BearingTimeRecordSpecification,
            r#"
            SELECT id, amplitude_unit_mode, created_datetime, modified_datetime,
                   baffle_bearing_list, bearing_bin_count, elevation_increment,
                   focus_range, frequency_spacing,
                   heading_data_type, heading_vector_index, lower_elevation,
                   max_frequency, max_pixel,
                   min_frequency, name, fft_sample_count, normalizer, organization_id,
                   upper_elevation,
                   (EXTRACT(EPOCH FROM update_rate) * 1000)::bigint as "update_rate_ms: i64"
            FROM horizon_public.bearing_time_record_specification WHERE id = $1
            "#,
            id,
        )
        .fetch_optional(&self.pool)
        .await?)
    }

    /// Update an existing bearing-time-record specification by ID.
    ///
    /// Returns `HorizonError::Postgres(PostgresError::NotFound)` if no row
    /// with the given ID exists. Matches Python `BaseRepository.update()`
    /// semantics. `update_rate_ms` is converted to a Postgres interval on
    /// write and back to milliseconds on read.
    pub async fn update_bearing_time_record_specification(
        &self,
        spec: &BearingTimeRecordSpecification,
    ) -> Result<BearingTimeRecordSpecification> {
        let now = Utc::now();
        let org = self.organization_id.or(spec.organization_id);
        let result = sqlx::query_as!(
            BearingTimeRecordSpecification,
            r#"
            UPDATE horizon_public.bearing_time_record_specification
            SET
                amplitude_unit_mode = $2,
                baffle_bearing_list = $3,
                bearing_bin_count = $4,
                elevation_increment = $5,
                focus_range = $6,
                frequency_spacing = $7,
                heading_data_type = $8,
                heading_vector_index = $9,
                lower_elevation = $10,
                max_frequency = $11,
                max_pixel = $12,
                min_frequency = $13,
                name = $14,
                fft_sample_count = $15,
                normalizer = $16,
                organization_id = $17,
                upper_elevation = $18,
                update_rate = $19::bigint * interval '1 millisecond',
                modified_datetime = $20
            WHERE id = $1
            RETURNING id, amplitude_unit_mode, created_datetime, modified_datetime,
                      baffle_bearing_list, bearing_bin_count, elevation_increment,
                      focus_range, frequency_spacing,
                      heading_data_type, heading_vector_index, lower_elevation,
                      max_frequency, max_pixel,
                      min_frequency, name, fft_sample_count, normalizer, organization_id,
                      upper_elevation,
                      (EXTRACT(EPOCH FROM update_rate) * 1000)::bigint as "update_rate_ms: i64"
            "#,
            spec.id,
            spec.amplitude_unit_mode,
            spec.baffle_bearing_list.as_deref(),
            spec.bearing_bin_count,
            spec.elevation_increment,
            spec.focus_range,
            spec.frequency_spacing,
            spec.heading_data_type,
            spec.heading_vector_index,
            spec.lower_elevation,
            spec.max_frequency,
            spec.max_pixel,
            spec.min_frequency,
            spec.name,
            spec.fft_sample_count,
            spec.normalizer,
            org,
            spec.upper_elevation,
            spec.update_rate_ms,
            now,
        )
        .fetch_optional(&self.pool)
        .await?;

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

    /// Insert a bearing-time-record specification or update it if a row with
    /// the same ID already exists. `update_rate_ms` is converted to a
    /// Postgres interval on write and back to milliseconds on read.
    pub async fn upsert_bearing_time_record_specification(
        &self,
        spec: &BearingTimeRecordSpecification,
    ) -> Result<BearingTimeRecordSpecification> {
        let now = Utc::now();
        let org = self.organization_id.or(spec.organization_id);
        Ok(sqlx::query_as!(
            BearingTimeRecordSpecification,
            r#"
            INSERT INTO horizon_public.bearing_time_record_specification
                (id, amplitude_unit_mode, baffle_bearing_list, bearing_bin_count, elevation_increment,
                 focus_range, frequency_spacing,
                 heading_data_type, heading_vector_index, lower_elevation,
                 max_frequency, max_pixel,
                 min_frequency, name, fft_sample_count, normalizer, organization_id,
                 upper_elevation, update_rate, modified_datetime)
            VALUES (COALESCE($1, gen_random_uuid()), $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18,
                    $19::bigint * interval '1 millisecond', $20)
            ON CONFLICT (id) DO UPDATE SET
                amplitude_unit_mode = EXCLUDED.amplitude_unit_mode,
                baffle_bearing_list = EXCLUDED.baffle_bearing_list,
                bearing_bin_count = EXCLUDED.bearing_bin_count,
                elevation_increment = EXCLUDED.elevation_increment,
                focus_range = EXCLUDED.focus_range,
                frequency_spacing = EXCLUDED.frequency_spacing,
                heading_data_type = EXCLUDED.heading_data_type,
                heading_vector_index = EXCLUDED.heading_vector_index,
                lower_elevation = EXCLUDED.lower_elevation,
                max_frequency = EXCLUDED.max_frequency, max_pixel = EXCLUDED.max_pixel,
                min_frequency = EXCLUDED.min_frequency, name = EXCLUDED.name,
                fft_sample_count = EXCLUDED.fft_sample_count, normalizer = EXCLUDED.normalizer,
                organization_id = EXCLUDED.organization_id,
                upper_elevation = EXCLUDED.upper_elevation,
                update_rate = EXCLUDED.update_rate,
                modified_datetime = EXCLUDED.modified_datetime
            RETURNING id, amplitude_unit_mode, created_datetime, modified_datetime,
                      baffle_bearing_list, bearing_bin_count, elevation_increment,
                      focus_range, frequency_spacing,
                      heading_data_type, heading_vector_index, lower_elevation,
                      max_frequency, max_pixel,
                      min_frequency, name, fft_sample_count, normalizer, organization_id,
                      upper_elevation,
                      (EXTRACT(EPOCH FROM update_rate) * 1000)::bigint as "update_rate_ms: i64"
            "#,
            spec.id,
            spec.amplitude_unit_mode,
            spec.baffle_bearing_list.as_deref(),
            spec.bearing_bin_count,
            spec.elevation_increment,
            spec.focus_range,
            spec.frequency_spacing,
            spec.heading_data_type,
            spec.heading_vector_index,
            spec.lower_elevation,
            spec.max_frequency,
            spec.max_pixel,
            spec.min_frequency,
            spec.name,
            spec.fft_sample_count,
            spec.normalizer,
            org,
            spec.upper_elevation,
            spec.update_rate_ms,
            now,
        )
        .fetch_one(&self.pool)
        .await?)
    }
}
