/// Defines a standard ordered aggregate function (e.g., `array_agg`).
///
/// This macro generates the base aggregate struct and its variants for `DISTINCT`
/// and `ORDER BY`, along with helper methods for `.filter()` and `.over()`.
#[macro_export]
macro_rules! define_ordered_aggregate {
    (
        $(#[$meta:meta])*
        $fn_name:ident, $Base:ident, $Ordered:ident, $Distinct:ident, $DistinctOrdered:ident,
        $sql_name:literal, $ret_type:ty
    ) => {
        #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)] #[diesel(aggregate)]
        pub struct $Base<E> { expr: E }
        #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)] #[diesel(aggregate)]
        pub struct $Ordered<E, O> { expr: E, order: O }
        #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)] #[diesel(aggregate)]
        pub struct $Distinct<E> { expr: E }
        #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)] #[diesel(aggregate)]
        pub struct $DistinctOrdered<E, O> { expr: E, order: O }

        // ── Builders ─────────────────────────────────────────────────────────
        impl<E> $Base<E> {
            pub fn order_by<O>(self, order: O) -> $Ordered<E, O> { $Ordered { expr: self.expr, order } }
            pub fn distinct(self) -> $Distinct<E> { $Distinct { expr: self.expr } }
            pub fn filter<F>(self, cond: F) -> $crate::FilteredAgg<Self, F> { $crate::FilteredAgg { agg: self, filter: cond } }
            pub fn over(self) -> $crate::OverClause<Self, $crate::NoSpec, $crate::NoSpec, $crate::NoFrame> { $crate::OverClause { agg: self, partition: $crate::NoSpec, order: $crate::NoSpec, frame: $crate::NoFrame } }
        }
        impl<E, O> $Ordered<E, O> {
            pub fn filter<F>(self, cond: F) -> $crate::FilteredAgg<Self, F> { $crate::FilteredAgg { agg: self, filter: cond } }
            pub fn over(self) -> $crate::OverClause<Self, $crate::NoSpec, $crate::NoSpec, $crate::NoFrame> { $crate::OverClause { agg: self, partition: $crate::NoSpec, order: $crate::NoSpec, frame: $crate::NoFrame } }
        }
        impl<E> $Distinct<E> {
            pub fn order_by<O>(self, order: O) -> $DistinctOrdered<E, O> { $DistinctOrdered { expr: self.expr, order } }
            pub fn filter<F>(self, cond: F) -> $crate::FilteredAgg<Self, F> { $crate::FilteredAgg { agg: self, filter: cond } }
            pub fn over(self) -> $crate::OverClause<Self, $crate::NoSpec, $crate::NoSpec, $crate::NoFrame> { $crate::OverClause { agg: self, partition: $crate::NoSpec, order: $crate::NoSpec, frame: $crate::NoFrame } }
        }
        impl<E, O> $DistinctOrdered<E, O> {
            pub fn filter<F>(self, cond: F) -> $crate::FilteredAgg<Self, F> { $crate::FilteredAgg { agg: self, filter: cond } }
            pub fn over(self) -> $crate::OverClause<Self, $crate::NoSpec, $crate::NoSpec, $crate::NoFrame> { $crate::OverClause { agg: self, partition: $crate::NoSpec, order: $crate::NoSpec, frame: $crate::NoFrame } }
        }

        impl<E> diesel::expression::Expression for $Base<E> where E: diesel::expression::Expression, E::SqlType: SingleValue { type SqlType = $ret_type; }
        impl<E, QS: ?Sized> diesel::expression::AppearsOnTable<QS> for $Base<E> where E: diesel::expression::AppearsOnTable<QS>, E::SqlType: SingleValue {}
        impl<E, QS: ?Sized> diesel::expression::SelectableExpression<QS> for $Base<E> where E: diesel::expression::SelectableExpression<QS>, E::SqlType: SingleValue, Self: diesel::expression::AppearsOnTable<QS> {}
        impl<E, GB> diesel::expression::ValidGrouping<GB> for $Base<E> where E: diesel::expression::Expression, E::SqlType: SingleValue { type IsAggregate = diesel::expression::is_aggregate::Yes; }

        impl<E, O> diesel::expression::Expression for $Ordered<E, O> where E: diesel::expression::Expression, E::SqlType: SingleValue { type SqlType = $ret_type; }
        impl<E, O, QS: ?Sized> diesel::expression::AppearsOnTable<QS> for $Ordered<E, O> where E: diesel::expression::AppearsOnTable<QS>, O: diesel::expression::AppearsOnTable<QS>, E::SqlType: SingleValue {}
        impl<E, O, QS: ?Sized> diesel::expression::SelectableExpression<QS> for $Ordered<E, O> where E: diesel::expression::SelectableExpression<QS>, O: diesel::expression::SelectableExpression<QS>, E::SqlType: SingleValue, Self: diesel::expression::AppearsOnTable<QS> {}
        impl<E, O, GB> diesel::expression::ValidGrouping<GB> for $Ordered<E, O> where E: diesel::expression::Expression, E::SqlType: SingleValue { type IsAggregate = diesel::expression::is_aggregate::Yes; }

        impl<E> diesel::expression::Expression for $Distinct<E> where E: diesel::expression::Expression, E::SqlType: SingleValue { type SqlType = $ret_type; }
        impl<E, QS: ?Sized> diesel::expression::AppearsOnTable<QS> for $Distinct<E> where E: diesel::expression::AppearsOnTable<QS>, E::SqlType: SingleValue {}
        impl<E, QS: ?Sized> diesel::expression::SelectableExpression<QS> for $Distinct<E> where E: diesel::expression::SelectableExpression<QS>, E::SqlType: SingleValue, Self: diesel::expression::AppearsOnTable<QS> {}
        impl<E, GB> diesel::expression::ValidGrouping<GB> for $Distinct<E> where E: diesel::expression::Expression, E::SqlType: SingleValue { type IsAggregate = diesel::expression::is_aggregate::Yes; }

        impl<E, O> diesel::expression::Expression for $DistinctOrdered<E, O> where E: diesel::expression::Expression, E::SqlType: SingleValue { type SqlType = $ret_type; }
        impl<E, O, QS: ?Sized> diesel::expression::AppearsOnTable<QS> for $DistinctOrdered<E, O> where E: diesel::expression::AppearsOnTable<QS>, O: diesel::expression::AppearsOnTable<QS>, E::SqlType: SingleValue {}
        impl<E, O, QS: ?Sized> diesel::expression::SelectableExpression<QS> for $DistinctOrdered<E, O> where E: diesel::expression::SelectableExpression<QS>, O: diesel::expression::SelectableExpression<QS>, E::SqlType: SingleValue, Self: diesel::expression::AppearsOnTable<QS> {}
        impl<E, O, GB> diesel::expression::ValidGrouping<GB> for $DistinctOrdered<E, O> where E: diesel::expression::Expression, E::SqlType: SingleValue { type IsAggregate = diesel::expression::is_aggregate::Yes; }

        // ── QueryFragment ────────────────────────────────────────────────────
        impl<E, DB: Backend> QueryFragment<DB> for $Base<E> where E: QueryFragment<DB> {
            fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> { out.push_sql(concat!($sql_name, "(")); self.expr.walk_ast(out.reborrow())?; out.push_sql(")"); Ok(()) } }
        impl<E, O, DB: Backend> QueryFragment<DB> for $Ordered<E, O> where E: QueryFragment<DB>, O: QueryFragment<DB> {
            fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> { out.push_sql(concat!($sql_name, "(")); self.expr.walk_ast(out.reborrow())?; out.push_sql(" ORDER BY "); self.order.walk_ast(out.reborrow())?; out.push_sql(")"); Ok(()) } }
        impl<E, DB: Backend> QueryFragment<DB> for $Distinct<E> where E: QueryFragment<DB> {
            fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> { out.push_sql(concat!($sql_name, "(DISTINCT ")); self.expr.walk_ast(out.reborrow())?; out.push_sql(")"); Ok(()) } }
        impl<E, O, DB: Backend> QueryFragment<DB> for $DistinctOrdered<E, O> where E: QueryFragment<DB>, O: QueryFragment<DB> {
            fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> { out.push_sql(concat!($sql_name, "(DISTINCT ")); self.expr.walk_ast(out.reborrow())?; out.push_sql(" ORDER BY "); self.order.walk_ast(out.reborrow())?; out.push_sql(")"); Ok(()) } }

        $(#[$meta])*
        pub fn $fn_name<E>(expr: E) -> $Base<E> { $Base { expr } }
    };
}

/// Defines a 2-argument ordered aggregate function (e.g., `string_agg`).
#[macro_export]
macro_rules! define_ordered_aggregate_2_args {
    (
        $(#[$meta:meta])*
        $fn_name:ident, $Base:ident, $Ordered:ident,
        $sql_name:literal, $ret_type:ty
    ) => {
        #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)] #[diesel(aggregate)]
        pub struct $Base<E1, E2> { expr1: E1, expr2: E2 }
        #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)] #[diesel(aggregate)]
        pub struct $Ordered<E1, E2, O> { expr1: E1, expr2: E2, order: O }

        impl<E1, E2> $Base<E1, E2> {
            pub fn order_by<O>(self, order: O) -> $Ordered<E1, E2, O> { $Ordered { expr1: self.expr1, expr2: self.expr2, order } }
            pub fn filter<F>(self, cond: F) -> $crate::FilteredAgg<Self, F> { $crate::FilteredAgg { agg: self, filter: cond } }
            pub fn over(self) -> $crate::OverClause<Self, $crate::NoSpec, $crate::NoSpec, $crate::NoFrame> { $crate::OverClause { agg: self, partition: $crate::NoSpec, order: $crate::NoSpec, frame: $crate::NoFrame } }
        }
        impl<E1, E2, O> $Ordered<E1, E2, O> {
            pub fn filter<F>(self, cond: F) -> $crate::FilteredAgg<Self, F> { $crate::FilteredAgg { agg: self, filter: cond } }
            pub fn over(self) -> $crate::OverClause<Self, $crate::NoSpec, $crate::NoSpec, $crate::NoFrame> { $crate::OverClause { agg: self, partition: $crate::NoSpec, order: $crate::NoSpec, frame: $crate::NoFrame } }
        }

        impl<E1, E2> diesel::expression::Expression for $Base<E1, E2> where E1: diesel::expression::Expression, E2: diesel::expression::Expression, E1::SqlType: SingleValue, E2::SqlType: SingleValue { type SqlType = $ret_type; }
        impl<E1, E2, QS: ?Sized> diesel::expression::AppearsOnTable<QS> for $Base<E1, E2> where E1: diesel::expression::AppearsOnTable<QS>, E2: diesel::expression::AppearsOnTable<QS>, E1::SqlType: SingleValue, E2::SqlType: SingleValue {}
        impl<E1, E2, QS: ?Sized> diesel::expression::SelectableExpression<QS> for $Base<E1, E2> where E1: diesel::expression::SelectableExpression<QS>, E2: diesel::expression::SelectableExpression<QS>, E1::SqlType: SingleValue, E2::SqlType: SingleValue, Self: diesel::expression::AppearsOnTable<QS> {}
        impl<E1, E2, GB> diesel::expression::ValidGrouping<GB> for $Base<E1, E2> where E1: diesel::expression::Expression, E2: diesel::expression::Expression, E1::SqlType: SingleValue, E2::SqlType: SingleValue { type IsAggregate = diesel::expression::is_aggregate::Yes; }

        impl<E1, E2, O> diesel::expression::Expression for $Ordered<E1, E2, O> where E1: diesel::expression::Expression, E2: diesel::expression::Expression, E1::SqlType: SingleValue, E2::SqlType: SingleValue { type SqlType = $ret_type; }
        impl<E1, E2, O, QS: ?Sized> diesel::expression::AppearsOnTable<QS> for $Ordered<E1, E2, O> where E1: diesel::expression::AppearsOnTable<QS>, E2: diesel::expression::AppearsOnTable<QS>, O: diesel::expression::AppearsOnTable<QS>, E1::SqlType: SingleValue, E2::SqlType: SingleValue {}
        impl<E1, E2, O, QS: ?Sized> diesel::expression::SelectableExpression<QS> for $Ordered<E1, E2, O> where E1: diesel::expression::SelectableExpression<QS>, E2: diesel::expression::SelectableExpression<QS>, O: diesel::expression::SelectableExpression<QS>, E1::SqlType: SingleValue, E2::SqlType: SingleValue, Self: diesel::expression::AppearsOnTable<QS> {}
        impl<E1, E2, O, GB> diesel::expression::ValidGrouping<GB> for $Ordered<E1, E2, O> where E1: diesel::expression::Expression, E2: diesel::expression::Expression, E1::SqlType: SingleValue, E2::SqlType: SingleValue { type IsAggregate = diesel::expression::is_aggregate::Yes; }

        impl<E1, E2, DB: Backend> QueryFragment<DB> for $Base<E1, E2> where E1: QueryFragment<DB>, E2: QueryFragment<DB> {
            fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> { out.push_sql(concat!($sql_name, "(")); self.expr1.walk_ast(out.reborrow())?; out.push_sql(", "); self.expr2.walk_ast(out.reborrow())?; out.push_sql(")"); Ok(()) } }
        impl<E1, E2, O, DB: Backend> QueryFragment<DB> for $Ordered<E1, E2, O> where E1: QueryFragment<DB>, E2: QueryFragment<DB>, O: QueryFragment<DB> {
            fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> { out.push_sql(concat!($sql_name, "(")); self.expr1.walk_ast(out.reborrow())?; out.push_sql(", "); self.expr2.walk_ast(out.reborrow())?; out.push_sql(" ORDER BY "); self.order.walk_ast(out.reborrow())?; out.push_sql(")"); Ok(()) } }

        $(#[$meta])*
        pub fn $fn_name<E1, E2>(expr1: E1, expr2: E2) -> $Base<E1, E2> { $Base { expr1, expr2 } }
    };
}

/// Defines an ordered-set aggregate function (e.g., `percentile_cont`).
///
/// These functions use the `WITHIN GROUP (ORDER BY ...)` syntax.
#[macro_export]
macro_rules! define_ordered_set_aggregate {
    (
        $(#[$meta:meta])*
        $fn_name:ident,
        $Base:ident,
        $Ordered:ident,
        $sql_name:literal
    ) => {
        #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)]
        #[diesel(aggregate)]
        pub struct $Base<Args> { args: Args }

        #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)]
        #[diesel(aggregate)]
        pub struct $Ordered<Args, O> { args: Args, order: O }

        // ── Type-State Builders ──────────────────────────────────────────────
        impl<Args> $Base<Args> {
            /// Converts to `$sql_name(args) WITHIN GROUP (ORDER BY order)`
            pub fn within_group<O>(self, order: O) -> $Ordered<Args, O> {
                $Ordered { args: self.args, order }
            }
        }

        impl<Args, O> $Ordered<Args, O> {
            pub fn filter<F>(self, cond: F) -> $crate::FilteredAgg<Self, F> {
                $crate::FilteredAgg { agg: self, filter: cond }
            }
            pub fn over(self) -> $crate::OverClause<Self, $crate::NoSpec, $crate::NoSpec, $crate::NoFrame> {
                $crate::OverClause { agg: self, partition: $crate::NoSpec, order: $crate::NoSpec, frame: $crate::NoFrame }
            }
        }

        // ── Diesel 2.x Traits ────────────────────────────────────────────────
        // ⚠️ Base intentionally omits `Expression` to prevent invalid SQL like:
        //    .select(percentile_cont(0.5))  ← Missing WITHIN GROUP (compile error)

        // ✅ Ordered inherits SqlType directly from the ORDER BY column
        impl<Args, O> diesel::expression::Expression for $Ordered<Args, O>
        where
            O: diesel::expression::Expression,
            O::SqlType: diesel::sql_types::SingleValue,
        {
            type SqlType = diesel::sql_types::Nullable<O::SqlType>;
        }

        impl<Args, O, QS: ?Sized> diesel::expression::AppearsOnTable<QS> for $Ordered<Args, O>
        where O: diesel::expression::Expression + diesel::expression::AppearsOnTable<QS>,
              O::SqlType: diesel::sql_types::SingleValue {}

        impl<Args, O, QS: ?Sized> diesel::expression::SelectableExpression<QS> for $Ordered<Args, O>
        where O: diesel::expression::Expression + diesel::expression::SelectableExpression<QS>,
              O::SqlType: diesel::sql_types::SingleValue, Self: diesel::expression::AppearsOnTable<QS> {}

        impl<Args, O, GB> diesel::expression::ValidGrouping<GB> for $Ordered<Args, O>
        where O: diesel::expression::Expression + diesel::expression::ValidGrouping<GB>,
              O::SqlType: diesel::sql_types::SingleValue,
        { type IsAggregate = diesel::expression::is_aggregate::Yes; }

        // ── SQL Generation ───────────────────────────────────────────────────
        impl<Args, O, DB: diesel::backend::Backend> diesel::query_builder::QueryFragment<DB> for $Ordered<Args, O>
        where Args: diesel::query_builder::QueryFragment<DB>, O: diesel::query_builder::QueryFragment<DB> {
            fn walk_ast<'b>(&'b self, mut out: diesel::query_builder::AstPass<'_, 'b, DB>) -> diesel::QueryResult<()> {
                out.push_sql(concat!($sql_name, "("));
                self.args.walk_ast(out.reborrow())?;
                out.push_sql(") WITHIN GROUP (ORDER BY ");
                self.order.walk_ast(out.reborrow())?;
                out.push_sql(")");
                Ok(())
            }
        }

        $(#[$meta])*
        pub fn $fn_name<Args>(args: Args) -> $Base<Args> {
            $Base { args }
        }
    };
}


/// Defines an ordered-set aggregate function that returns an array (e.g., `percentile_cont` with an array of fractions).
#[macro_export]
macro_rules! define_ordered_set_aggregate_array {
    (
        $(#[$meta:meta])*
        $fn_name:ident,
        $Base:ident,
        $Ordered:ident,
        $sql_name:literal
    ) => {
        #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)]
        #[diesel(aggregate)]
        pub struct $Base<Args> { args: Args }

        #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)]
        #[diesel(aggregate)]
        pub struct $Ordered<Args, O> { args: Args, order: O }

        // ── Type-State Builders ──────────────────────────────────────────────
        impl<Args> $Base<Args> {
            pub fn within_group<O>(self, order: O) -> $Ordered<Args, O> {
                $Ordered { args: self.args, order }
            }
        }

        impl<Args, O> $Ordered<Args, O> {
            pub fn filter<F>(self, cond: F) -> $crate::FilteredAgg<Self, F> {
                $crate::FilteredAgg { agg: self, filter: cond }
            }
            pub fn over(self) -> $crate::OverClause<Self, $crate::NoSpec, $crate::NoSpec, $crate::NoFrame> {
                $crate::OverClause { agg: self, partition: $crate::NoSpec, order: $crate::NoSpec, frame: $crate::NoFrame }
            }
        }

        // ── Diesel 2.x Traits ────────────────────────────────────────────────
        // ✅ KEY DIFFERENCE: Returns Nullable<Array<O::SqlType>>
        impl<Args, O> diesel::expression::Expression for $Ordered<Args, O>
        where
            O: diesel::expression::Expression,
            O::SqlType: diesel::sql_types::SingleValue,
        {
            type SqlType = diesel::sql_types::Nullable<diesel::sql_types::Array<O::SqlType>>;
        }

        impl<Args, O, QS: ?Sized> diesel::expression::AppearsOnTable<QS> for $Ordered<Args, O>
        where O: diesel::expression::Expression + diesel::expression::AppearsOnTable<QS>,
              O::SqlType: diesel::sql_types::SingleValue {}

        impl<Args, O, QS: ?Sized> diesel::expression::SelectableExpression<QS> for $Ordered<Args, O>
        where O: diesel::expression::Expression + diesel::expression::SelectableExpression<QS>,
              O::SqlType: diesel::sql_types::SingleValue, Self: diesel::expression::AppearsOnTable<QS> {}

        impl<Args, O, GB> diesel::expression::ValidGrouping<GB> for $Ordered<Args, O>
        where O: diesel::expression::Expression + diesel::expression::ValidGrouping<GB>,
              O::SqlType: diesel::sql_types::SingleValue,
        { type IsAggregate = diesel::expression::is_aggregate::Yes; }

        // ── SQL Generation ───────────────────────────────────────────────────
        impl<Args, O, DB: diesel::backend::Backend> diesel::query_builder::QueryFragment<DB> for $Ordered<Args, O>
        where Args: diesel::query_builder::QueryFragment<DB>, O: diesel::query_builder::QueryFragment<DB> {
            fn walk_ast<'b>(&'b self, mut out: diesel::query_builder::AstPass<'_, 'b, DB>) -> diesel::QueryResult<()> {
                out.push_sql(concat!($sql_name, "("));
                self.args.walk_ast(out.reborrow())?;
                out.push_sql(") WITHIN GROUP (ORDER BY ");
                self.order.walk_ast(out.reborrow())?;
                out.push_sql(")");
                Ok(())
            }
        }

        $(#[$meta])*
        pub fn $fn_name<Args>(args: Args) -> $Base<Args> {
            $Base { args }
        }
    };
}
