//! Core semantic layer types: Model, Dimension, Metric, Relationship

use serde::{Deserialize, Serialize};

use super::segment::Segment;

/// Dimension type classification
#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize, Default)]
#[serde(rename_all = "lowercase")]
pub enum DimensionType {
    #[default]
    Categorical,
    Time,
    Boolean,
    Numeric,
}

/// A dimension represents a grouping attribute
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Dimension {
    pub name: String,
    #[serde(default)]
    pub r#type: DimensionType,
    /// SQL expression (defaults to name if not provided)
    pub sql: Option<String>,
    /// Time granularity (for time dimensions)
    pub granularity: Option<String>,
    /// Supported granularities for time dimensions
    #[serde(default)]
    pub supported_granularities: Option<Vec<String>>,
    /// Human-readable label
    pub label: Option<String>,
    /// Description
    pub description: Option<String>,
    /// Adapter-specific metadata payload
    #[serde(default)]
    pub metadata: Option<serde_json::Value>,
    /// Arbitrary extension metadata
    #[serde(default)]
    pub meta: Option<serde_json::Value>,
    /// Display format string
    #[serde(default)]
    pub format: Option<String>,
    /// Named display format
    #[serde(default)]
    pub value_format_name: Option<String>,
    /// Parent dimension name for hierarchies
    #[serde(default)]
    pub parent: Option<String>,
    /// Window expression projected in model CTEs.
    #[serde(default)]
    pub window: Option<String>,
    /// Whether dimension is visible in API/UI.
    #[serde(default = "default_true")]
    pub public: bool,
}

impl Dimension {
    pub fn new(name: impl Into<String>) -> Self {
        Self {
            name: name.into(),
            r#type: DimensionType::Categorical,
            sql: None,
            granularity: None,
            supported_granularities: None,
            label: None,
            description: None,
            metadata: None,
            meta: None,
            format: None,
            value_format_name: None,
            parent: None,
            window: None,
            public: true,
        }
    }

    pub fn categorical(name: impl Into<String>) -> Self {
        Self::new(name)
    }

    pub fn time(name: impl Into<String>) -> Self {
        Self {
            r#type: DimensionType::Time,
            ..Self::new(name)
        }
    }

    pub fn with_sql(mut self, sql: impl Into<String>) -> Self {
        self.sql = Some(sql.into());
        self
    }

    pub fn with_granularity(mut self, granularity: impl Into<String>) -> Self {
        self.granularity = Some(granularity.into());
        self
    }

    /// Returns the SQL expression for this dimension
    pub fn sql_expr(&self) -> &str {
        self.sql.as_deref().unwrap_or(&self.name)
    }

    /// Returns the window expression when configured, otherwise the row-level SQL expression.
    pub fn window_sql_expr(&self) -> &str {
        self.window.as_deref().unwrap_or_else(|| self.sql_expr())
    }

    /// Returns SQL with time granularity applied (DATE_TRUNC)
    pub fn sql_with_granularity(&self, granularity: Option<&str>) -> String {
        let base_sql = self.sql_expr();
        match granularity.or(self.granularity.as_deref()) {
            Some(g) => format!("DATE_TRUNC('{g}', {base_sql})"),
            None => base_sql.to_string(),
        }
    }
}

/// Aggregation function type
#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize, Default)]
#[serde(rename_all = "snake_case")]
pub enum Aggregation {
    #[default]
    Sum,
    Count,
    CountDistinct,
    Avg,
    Min,
    Max,
    Median,
    Stddev,
    StddevPop,
    Variance,
    VariancePop,
    /// Raw expression that already contains aggregation (e.g., SUM(amount) * 2)
    Expression,
}

impl Aggregation {
    pub fn as_sql(&self) -> &'static str {
        match self {
            Aggregation::Sum => "SUM",
            Aggregation::Count => "COUNT",
            Aggregation::CountDistinct => "COUNT(DISTINCT",
            Aggregation::Avg => "AVG",
            Aggregation::Min => "MIN",
            Aggregation::Max => "MAX",
            Aggregation::Median => "MEDIAN",
            Aggregation::Stddev => "STDDEV",
            Aggregation::StddevPop => "STDDEV_POP",
            Aggregation::Variance => "VARIANCE",
            Aggregation::VariancePop => "VAR_POP",
            Aggregation::Expression => "", // Not used - expression stored in sql field
        }
    }
}

/// Metric type
#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize, Default)]
#[serde(rename_all = "snake_case")]
pub enum MetricType {
    #[default]
    Simple,
    Derived,
    Ratio,
    Cumulative,
    #[serde(alias = "timecomparison")]
    TimeComparison,
    Conversion,
    Retention,
    Cohort,
}

/// Time comparison type
#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
#[serde(rename_all = "lowercase")]
pub enum ComparisonType {
    Yoy, // Year over year
    Mom, // Month over month
    Wow, // Week over week
    Dod, // Day over day
    Qoq, // Quarter over quarter
    PriorPeriod,
}

/// Time comparison calculation method
#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize, Default)]
#[serde(rename_all = "snake_case")]
pub enum ComparisonCalculation {
    Difference,
    #[default]
    PercentChange,
    Ratio,
}

/// Time grain for period-to-date calculations
#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
#[serde(rename_all = "lowercase")]
pub enum TimeGrain {
    Day,
    Week,
    Month,
    Quarter,
    Year,
}

/// Inner per-entity aggregate for cohort metrics.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct CohortInnerMetric {
    pub name: String,
    #[serde(default)]
    pub agg: Option<Aggregation>,
    #[serde(default)]
    pub sql: Option<String>,
}

/// A metric represents a business measure (aggregation)
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Metric {
    pub name: String,
    /// Parent metric to inherit from.
    #[serde(default)]
    pub extends: Option<String>,
    #[serde(default)]
    pub r#type: MetricType,
    /// Aggregation function (for simple metrics)
    pub agg: Option<Aggregation>,
    /// SQL expression
    pub sql: Option<String>,
    /// Numerator metric (for ratio metrics)
    pub numerator: Option<String>,
    /// Denominator metric (for ratio metrics)
    pub denominator: Option<String>,
    /// Time offset for ratio denominator (e.g. "1 month")
    #[serde(default)]
    pub offset_window: Option<String>,
    /// Filters to apply
    #[serde(default)]
    pub filters: Vec<String>,
    /// Human-readable label
    pub label: Option<String>,
    /// Description
    pub description: Option<String>,
    /// Adapter-specific metadata payload.
    #[serde(default)]
    pub metadata: Option<serde_json::Value>,
    /// Arbitrary extension metadata.
    #[serde(default)]
    pub meta: Option<serde_json::Value>,

    // Cumulative metric fields
    /// Time window for cumulative (e.g., "7 days")
    #[serde(default)]
    pub window: Option<String>,
    /// Grain for period-to-date (e.g., month for MTD)
    #[serde(default)]
    pub grain_to_date: Option<TimeGrain>,
    /// Raw SQL expression used inside a window function
    #[serde(default)]
    pub window_expression: Option<String>,
    /// Explicit window frame clause
    #[serde(default)]
    pub window_frame: Option<String>,
    /// ORDER BY column override for window metrics
    #[serde(default)]
    pub window_order: Option<String>,

    // Time comparison fields
    /// Base metric for time comparison
    #[serde(default)]
    pub base_metric: Option<String>,
    /// Type of time comparison
    #[serde(default)]
    pub comparison_type: Option<ComparisonType>,
    /// Custom time offset (e.g., "1 month")
    #[serde(default)]
    pub time_offset: Option<String>,
    /// Comparison calculation method
    #[serde(default)]
    pub calculation: Option<ComparisonCalculation>,

    // Conversion metric fields
    /// Entity identifier expression (e.g. user_id)
    #[serde(default)]
    pub entity: Option<String>,
    /// Base event predicate/value
    #[serde(default)]
    pub base_event: Option<String>,
    /// Conversion event predicate/value
    #[serde(default)]
    pub conversion_event: Option<String>,
    /// Conversion window (e.g. "7 days")
    #[serde(default)]
    pub conversion_window: Option<String>,
    /// N-step funnel filter expressions.
    #[serde(default)]
    pub steps: Option<Vec<String>>,

    // Retention metric fields
    /// Cohort-defining event predicate.
    #[serde(default)]
    pub cohort_event: Option<String>,
    /// Activity event predicate.
    #[serde(default)]
    pub activity_event: Option<String>,
    /// Number of retention periods to include.
    #[serde(default)]
    pub periods: Option<usize>,
    /// Retention time grain.
    #[serde(default)]
    pub retention_granularity: Option<String>,

    // Cohort metric fields
    /// Per-entity inner aggregations.
    #[serde(default)]
    pub inner_metrics: Option<Vec<CohortInnerMetric>>,
    /// Dimensions carried through from inner to outer aggregation.
    #[serde(default)]
    pub entity_dimensions: Option<Vec<String>>,
    /// HAVING predicate applied to the inner aggregation.
    #[serde(default)]
    pub having: Option<String>,

    // Display formatting
    /// Default value when result is NULL
    #[serde(default)]
    pub fill_nulls_with: Option<serde_json::Value>,
    /// Display format string (e.g., "$#,##0.00", "0.00%")
    #[serde(default)]
    pub format: Option<String>,
    /// Named format (e.g., "usd", "percent", "decimal_2")
    #[serde(default)]
    pub value_format_name: Option<String>,
    /// Fields to include in drill-down results
    #[serde(default)]
    pub drill_fields: Option<Vec<String>>,
    /// Dimension across which this metric is non-additive
    #[serde(default)]
    pub non_additive_dimension: Option<String>,
    /// Whether metric is visible in API/UI.
    #[serde(default = "default_true")]
    pub public: bool,
}

impl Metric {
    pub fn new(name: impl Into<String>) -> Self {
        Self {
            name: name.into(),
            extends: None,
            r#type: MetricType::Simple,
            agg: Some(Aggregation::Sum),
            sql: None,
            numerator: None,
            denominator: None,
            offset_window: None,
            filters: Vec::new(),
            label: None,
            description: None,
            metadata: None,
            meta: None,
            window: None,
            grain_to_date: None,
            window_expression: None,
            window_frame: None,
            window_order: None,
            base_metric: None,
            comparison_type: None,
            time_offset: None,
            calculation: None,
            entity: None,
            base_event: None,
            conversion_event: None,
            conversion_window: None,
            steps: None,
            cohort_event: None,
            activity_event: None,
            periods: None,
            retention_granularity: None,
            inner_metrics: None,
            entity_dimensions: None,
            having: None,
            fill_nulls_with: None,
            format: None,
            value_format_name: None,
            drill_fields: None,
            non_additive_dimension: None,
            public: true,
        }
    }

    pub fn sum(name: impl Into<String>, sql: impl Into<String>) -> Self {
        Self {
            agg: Some(Aggregation::Sum),
            sql: Some(sql.into()),
            ..Self::new(name)
        }
    }

    pub fn count(name: impl Into<String>) -> Self {
        Self {
            agg: Some(Aggregation::Count),
            sql: Some("*".into()),
            ..Self::new(name)
        }
    }

    pub fn count_distinct(name: impl Into<String>, sql: impl Into<String>) -> Self {
        Self {
            agg: Some(Aggregation::CountDistinct),
            sql: Some(sql.into()),
            ..Self::new(name)
        }
    }

    pub fn avg(name: impl Into<String>, sql: impl Into<String>) -> Self {
        Self {
            agg: Some(Aggregation::Avg),
            sql: Some(sql.into()),
            ..Self::new(name)
        }
    }

    pub fn derived(name: impl Into<String>, sql: impl Into<String>) -> Self {
        Self {
            r#type: MetricType::Derived,
            agg: None,
            sql: Some(sql.into()),
            ..Self::new(name)
        }
    }

    pub fn ratio(
        name: impl Into<String>,
        numerator: impl Into<String>,
        denominator: impl Into<String>,
    ) -> Self {
        Self {
            r#type: MetricType::Ratio,
            agg: None,
            numerator: Some(numerator.into()),
            denominator: Some(denominator.into()),
            ..Self::new(name)
        }
    }

    /// Create a cumulative (running total) metric
    pub fn cumulative(name: impl Into<String>, base_metric: impl Into<String>) -> Self {
        Self {
            r#type: MetricType::Cumulative,
            agg: None,
            sql: Some(base_metric.into()),
            ..Self::new(name)
        }
    }

    /// Create a period-to-date metric (MTD, YTD, etc.)
    pub fn period_to_date(
        name: impl Into<String>,
        base_metric: impl Into<String>,
        grain: TimeGrain,
    ) -> Self {
        Self {
            r#type: MetricType::Cumulative,
            agg: None,
            sql: Some(base_metric.into()),
            grain_to_date: Some(grain),
            ..Self::new(name)
        }
    }

    /// Create a time comparison metric (YoY, MoM, etc.)
    pub fn time_comparison(
        name: impl Into<String>,
        base_metric: impl Into<String>,
        comparison: ComparisonType,
    ) -> Self {
        Self {
            r#type: MetricType::TimeComparison,
            agg: None,
            base_metric: Some(base_metric.into()),
            comparison_type: Some(comparison),
            ..Self::new(name)
        }
    }

    pub fn with_filter(mut self, filter: impl Into<String>) -> Self {
        self.filters.push(filter.into());
        self
    }

    pub fn with_format(mut self, format: impl Into<String>) -> Self {
        self.format = Some(format.into());
        self
    }

    pub fn with_fill_nulls(mut self, value: serde_json::Value) -> Self {
        self.fill_nulls_with = Some(value);
        self
    }

    pub fn with_calculation(mut self, calc: ComparisonCalculation) -> Self {
        self.calculation = Some(calc);
        self
    }

    /// Returns the SQL expression for this metric
    pub fn sql_expr(&self) -> &str {
        self.sql.as_deref().unwrap_or(&self.name)
    }

    /// Converts metric to SQL aggregation expression
    pub fn to_sql(&self, alias: Option<&str>) -> String {
        let prefix = alias.map(|a| format!("{a}.")).unwrap_or_default();

        match self.r#type {
            MetricType::Simple => {
                let agg = self.agg.as_ref().unwrap_or(&Aggregation::Sum);
                // COUNT without explicit sql defaults to COUNT(*)
                let full_expr = if *agg == Aggregation::Count && self.sql.is_none() {
                    "*".to_string()
                } else {
                    let sql_expr = self.sql_expr();
                    if sql_expr == "*" {
                        "*".to_string()
                    } else {
                        format!("{prefix}{sql_expr}")
                    }
                };

                match agg {
                    Aggregation::CountDistinct => format!("COUNT(DISTINCT {full_expr})"),
                    _ => format!("{}({})", agg.as_sql(), full_expr),
                }
            }
            MetricType::Derived => self.sql_expr().to_string(),
            MetricType::Ratio => {
                // Ratio is computed from other metrics
                format!(
                    "({}) / NULLIF({}, 0)",
                    self.numerator.as_deref().unwrap_or("1"),
                    self.denominator.as_deref().unwrap_or("1")
                )
            }
            MetricType::Cumulative => {
                // Cumulative metrics need window functions - this is a placeholder
                // The actual implementation depends on time dimension context
                let base = self.sql_expr();
                if self.grain_to_date.is_some() {
                    // Period-to-date: SUM() with window reset at period boundary
                    format!("SUM({base}) /* cumulative, grain_to_date */")
                } else if let Some(window) = &self.window {
                    // Rolling window: SUM() OVER (ORDER BY time ROWS BETWEEN window AND CURRENT ROW)
                    format!("SUM({base}) /* cumulative, window: {window} */")
                } else {
                    // Simple running total
                    format!("SUM({base}) /* cumulative */")
                }
            }
            MetricType::TimeComparison => {
                // Time comparison metrics compare current vs prior period
                let base = self.base_metric.as_deref().unwrap_or(&self.name);
                let comparison = self
                    .comparison_type
                    .as_ref()
                    .map(|c| format!("{c:?}").to_lowercase())
                    .unwrap_or_else(|| "prior_period".to_string());
                let calc = self
                    .calculation
                    .as_ref()
                    .unwrap_or(&ComparisonCalculation::PercentChange);

                match calc {
                    ComparisonCalculation::Difference => {
                        format!("({base} - LAG({base}) OVER ()) /* {comparison} */")
                    }
                    ComparisonCalculation::PercentChange => {
                        format!(
                            "(({base} - LAG({base}) OVER ()) / NULLIF(LAG({base}) OVER (), 0)) /* {comparison} */"
                        )
                    }
                    ComparisonCalculation::Ratio => {
                        format!("({base} / NULLIF(LAG({base}) OVER (), 0)) /* {comparison} */")
                    }
                }
            }
            MetricType::Conversion | MetricType::Retention | MetricType::Cohort => {
                "NULL /* complex metric */".to_string()
            }
        }
    }

    /// Check if this is a simple aggregation (not a complex metric)
    pub fn is_simple_aggregation(&self) -> bool {
        self.r#type == MetricType::Simple && self.agg.is_some()
    }
}

/// Relationship type between models
#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize, Default)]
#[serde(rename_all = "snake_case")]
pub enum RelationshipType {
    #[default]
    ManyToOne,
    OneToOne,
    OneToMany,
    ManyToMany,
}

/// A relationship defines how models join together
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Relationship {
    /// Target model name
    pub name: String,
    #[serde(default)]
    pub r#type: RelationshipType,
    /// Foreign key column.
    /// Defaults to `{name}_id` for `many_to_one`, and `id` for one-to-one/one-to-many compatibility.
    pub foreign_key: Option<String>,
    /// Foreign key columns for composite relationships
    #[serde(default)]
    pub foreign_key_columns: Option<Vec<String>>,
    /// Primary key in related model (defaults to "id")
    pub primary_key: Option<String>,
    /// Primary key columns in related model for composite relationships
    #[serde(default)]
    pub primary_key_columns: Option<Vec<String>>,
    /// Junction model for many-to-many relationships
    #[serde(default)]
    pub through: Option<String>,
    /// Foreign key in junction model pointing to this model
    #[serde(default)]
    pub through_foreign_key: Option<String>,
    /// Foreign key columns in junction model pointing to this model
    #[serde(default)]
    pub through_foreign_key_columns: Option<Vec<String>>,
    /// Foreign key in junction model pointing to related model
    #[serde(default)]
    pub related_foreign_key: Option<String>,
    /// Foreign key columns in junction model pointing to related model
    #[serde(default)]
    pub related_foreign_key_columns: Option<Vec<String>>,
    /// Custom SQL join condition (overrides FK/PK)
    /// Use {from} and {to} placeholders for table aliases
    #[serde(default)]
    pub sql: Option<String>,
    /// Adapter-specific metadata payload.
    #[serde(default)]
    pub metadata: Option<serde_json::Value>,
}

impl Relationship {
    pub fn new(target: impl Into<String>) -> Self {
        Self {
            name: target.into(),
            r#type: RelationshipType::ManyToOne,
            foreign_key: None,
            foreign_key_columns: None,
            primary_key: None,
            primary_key_columns: None,
            through: None,
            through_foreign_key: None,
            through_foreign_key_columns: None,
            related_foreign_key: None,
            related_foreign_key_columns: None,
            sql: None,
            metadata: None,
        }
    }

    pub fn many_to_one(target: impl Into<String>) -> Self {
        Self::new(target)
    }

    pub fn one_to_many(target: impl Into<String>) -> Self {
        Self {
            r#type: RelationshipType::OneToMany,
            ..Self::new(target)
        }
    }

    pub fn with_keys(
        mut self,
        foreign_key: impl Into<String>,
        primary_key: impl Into<String>,
    ) -> Self {
        let foreign_key = foreign_key.into();
        let primary_key = primary_key.into();
        self.foreign_key = Some(foreign_key.clone());
        self.foreign_key_columns = Some(vec![foreign_key]);
        self.primary_key = Some(primary_key.clone());
        self.primary_key_columns = Some(vec![primary_key]);
        self
    }

    pub fn with_key_columns(
        mut self,
        foreign_keys: Vec<String>,
        primary_keys: Vec<String>,
    ) -> Self {
        self.foreign_key = foreign_keys.first().cloned();
        self.foreign_key_columns = Some(foreign_keys);
        self.primary_key = primary_keys.first().cloned();
        self.primary_key_columns = Some(primary_keys);
        self
    }

    /// Set a custom SQL join condition (overrides FK/PK)
    /// Use {from} and {to} placeholders for table aliases
    /// Example: "{from}.date BETWEEN {to}.start_date AND {to}.end_date"
    pub fn with_condition(mut self, sql: impl Into<String>) -> Self {
        self.sql = Some(sql.into());
        self
    }

    /// Returns the foreign key column name
    pub fn fk(&self) -> String {
        self.foreign_key_columns()
            .into_iter()
            .next()
            .unwrap_or_else(|| format!("{}_id", self.name))
    }

    /// Returns the primary key column name in the related model
    pub fn pk(&self) -> String {
        self.primary_key_columns()
            .into_iter()
            .next()
            .unwrap_or_else(|| "id".to_string())
    }

    pub fn foreign_key_columns(&self) -> Vec<String> {
        self.foreign_key_columns
            .clone()
            .filter(|columns| !columns.is_empty())
            .or_else(|| self.foreign_key.clone().map(|key| vec![key]))
            .unwrap_or_else(|| {
                if self.r#type == RelationshipType::ManyToOne {
                    vec![format!("{}_id", self.name)]
                } else {
                    vec!["id".to_string()]
                }
            })
    }

    pub fn primary_key_columns(&self) -> Vec<String> {
        self.primary_key_columns
            .clone()
            .filter(|columns| !columns.is_empty())
            .or_else(|| self.primary_key.clone().map(|key| vec![key]))
            .unwrap_or_else(|| vec!["id".to_string()])
    }

    /// Returns the custom SQL condition if set
    pub fn custom_condition(&self) -> Option<&str> {
        self.sql.as_deref()
    }

    /// Get junction keys for many-to-many relationships.
    /// Returns (source_fk_in_through, target_fk_in_through).
    pub fn junction_keys(&self) -> (Option<String>, Option<String>) {
        if self.r#type != RelationshipType::ManyToMany {
            return (None, None);
        }
        let (source_keys, target_keys) = self.junction_key_columns();
        (
            source_keys.into_iter().next(),
            target_keys.into_iter().next(),
        )
    }

    /// Get junction key columns for many-to-many relationships.
    pub fn junction_key_columns(&self) -> (Vec<String>, Vec<String>) {
        if self.r#type != RelationshipType::ManyToMany {
            return (Vec::new(), Vec::new());
        }

        let source_keys = self
            .through_foreign_key_columns
            .clone()
            .filter(|columns| !columns.is_empty())
            .or_else(|| self.through_foreign_key.clone().map(|key| vec![key]))
            .or_else(|| {
                self.foreign_key_columns
                    .clone()
                    .filter(|columns| !columns.is_empty())
            })
            .or_else(|| self.foreign_key.clone().map(|key| vec![key]))
            .unwrap_or_default();
        let target_keys = self
            .related_foreign_key_columns
            .clone()
            .filter(|columns| !columns.is_empty())
            .or_else(|| self.related_foreign_key.clone().map(|key| vec![key]))
            .unwrap_or_default();

        (source_keys, target_keys)
    }
}

#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize, Default)]
#[serde(rename_all = "snake_case")]
pub enum PreAggregationType {
    #[default]
    Rollup,
    OriginalSql,
    RollupJoin,
    Lambda,
}

fn default_true() -> bool {
    true
}

fn default_false() -> bool {
    false
}

fn default_index_type() -> String {
    "regular".to_string()
}

/// Refresh strategy configuration for pre-aggregations.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct RefreshKey {
    #[serde(default)]
    pub every: Option<String>,
    #[serde(default)]
    pub sql: Option<String>,
    #[serde(default = "default_false")]
    pub incremental: bool,
    #[serde(default)]
    pub update_window: Option<String>,
}

/// Index definition for pre-aggregation performance.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Index {
    pub name: String,
    #[serde(default)]
    pub columns: Vec<String>,
    #[serde(default = "default_index_type", rename = "type")]
    pub index_type: String,
}

/// Pre-aggregation definition for query routing.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct PreAggregation {
    pub name: String,
    #[serde(default, rename = "type")]
    pub preagg_type: PreAggregationType,
    #[serde(default)]
    pub sql: Option<String>,
    #[serde(default)]
    pub measures: Option<Vec<String>>,
    #[serde(default)]
    pub dimensions: Option<Vec<String>>,
    #[serde(default)]
    pub time_dimension: Option<String>,
    #[serde(default)]
    pub granularity: Option<String>,
    #[serde(default)]
    pub partition_granularity: Option<String>,
    #[serde(default)]
    pub build_range_start: Option<String>,
    #[serde(default)]
    pub build_range_end: Option<String>,
    #[serde(default = "default_true")]
    pub scheduled_refresh: bool,
    #[serde(default)]
    pub refresh_key: Option<RefreshKey>,
    #[serde(default)]
    pub indexes: Option<Vec<Index>>,
    #[serde(default)]
    pub meta: Option<serde_json::Value>,
}

impl PreAggregation {
    /// Returns pre-aggregation table name in [database.][schema.]model_preagg_name form.
    pub fn table_name(
        &self,
        model_name: &str,
        database: Option<&str>,
        schema: Option<&str>,
    ) -> String {
        let mut table_name = format!("{model_name}_preagg_{}", self.name);
        if let Some(schema) = schema {
            table_name = format!("{schema}.{table_name}");
        }
        if let Some(database) = database {
            table_name = format!("{database}.{table_name}");
        }
        table_name
    }
}

/// A model represents a table or view with semantic definitions
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Model {
    pub name: String,
    /// Physical table name
    pub table: Option<String>,
    /// SQL expression for derived tables
    pub sql: Option<String>,
    /// Remote source URI
    #[serde(default)]
    pub source_uri: Option<String>,
    /// Parent model name for inheritance
    #[serde(default)]
    pub extends: Option<String>,
    /// Primary key column
    pub primary_key: String,
    /// Primary key columns for composite keys
    #[serde(default)]
    pub primary_key_columns: Vec<String>,
    /// Unique key constraints
    #[serde(default)]
    pub unique_keys: Option<Vec<Vec<String>>>,
    /// Dimensions (grouping attributes)
    #[serde(default)]
    pub dimensions: Vec<Dimension>,
    /// Metrics (aggregations)
    #[serde(default)]
    pub metrics: Vec<Metric>,
    /// Relationships to other models
    #[serde(default)]
    pub relationships: Vec<Relationship>,
    /// Segments (reusable filters)
    #[serde(default)]
    pub segments: Vec<Segment>,
    /// Pre-aggregations for query routing
    #[serde(default)]
    pub pre_aggregations: Vec<PreAggregation>,
    /// Default time dimension auto-included when querying this model's metrics
    #[serde(default)]
    pub default_time_dimension: Option<String>,
    /// Default grain used with default_time_dimension
    #[serde(default)]
    pub default_grain: Option<String>,
    /// Human-readable label
    pub label: Option<String>,
    /// Description
    pub description: Option<String>,
    /// Adapter-specific metadata payload.
    #[serde(default)]
    pub metadata: Option<serde_json::Value>,
    /// Arbitrary extension metadata.
    #[serde(default)]
    pub meta: Option<serde_json::Value>,
}

impl Model {
    pub fn new(name: impl Into<String>, primary_key: impl Into<String>) -> Self {
        let primary_key = primary_key.into();
        Self {
            name: name.into(),
            table: None,
            sql: None,
            source_uri: None,
            extends: None,
            primary_key: primary_key.clone(),
            primary_key_columns: vec![primary_key],
            unique_keys: None,
            dimensions: Vec::new(),
            metrics: Vec::new(),
            relationships: Vec::new(),
            segments: Vec::new(),
            pre_aggregations: Vec::new(),
            default_time_dimension: None,
            default_grain: None,
            label: None,
            description: None,
            metadata: None,
            meta: None,
        }
    }

    pub fn with_primary_key_columns(mut self, primary_key_columns: Vec<String>) -> Self {
        if let Some(primary_key) = primary_key_columns.first() {
            self.primary_key = primary_key.clone();
        }
        self.primary_key_columns = primary_key_columns;
        self
    }

    pub fn with_table(mut self, table: impl Into<String>) -> Self {
        self.table = Some(table.into());
        self
    }

    pub fn with_sql(mut self, sql: impl Into<String>) -> Self {
        self.sql = Some(sql.into());
        self
    }

    pub fn with_dimension(mut self, dimension: Dimension) -> Self {
        self.dimensions.push(dimension);
        self
    }

    pub fn with_metric(mut self, metric: Metric) -> Self {
        self.metrics.push(metric);
        self
    }

    pub fn with_relationship(mut self, relationship: Relationship) -> Self {
        self.relationships.push(relationship);
        self
    }

    pub fn with_segment(mut self, segment: Segment) -> Self {
        self.segments.push(segment);
        self
    }

    pub fn with_pre_aggregation(mut self, pre_aggregation: PreAggregation) -> Self {
        self.pre_aggregations.push(pre_aggregation);
        self
    }

    /// Returns the table name or model name as fallback
    pub fn table_name(&self) -> &str {
        self.table.as_deref().unwrap_or(&self.name)
    }

    pub fn primary_keys(&self) -> Vec<String> {
        if self.primary_key_columns.is_empty() {
            vec![self.primary_key.clone()]
        } else {
            self.primary_key_columns.clone()
        }
    }

    /// Returns the table source (table name or SQL subquery)
    pub fn table_source(&self) -> String {
        if let Some(sql) = &self.sql {
            format!("({sql})")
        } else {
            self.table_name().to_string()
        }
    }

    /// Find a dimension by name
    pub fn get_dimension(&self, name: &str) -> Option<&Dimension> {
        self.dimensions.iter().find(|d| d.name == name)
    }

    /// Find a metric by name
    pub fn get_metric(&self, name: &str) -> Option<&Metric> {
        self.metrics.iter().find(|m| m.name == name)
    }

    /// Find a relationship by target model name
    pub fn get_relationship(&self, target: &str) -> Option<&Relationship> {
        self.relationships.iter().find(|r| r.name == target)
    }

    /// Find a segment by name
    pub fn get_segment(&self, name: &str) -> Option<&Segment> {
        self.segments.iter().find(|s| s.name == name)
    }

    /// Find a pre-aggregation by name
    pub fn get_pre_aggregation(&self, name: &str) -> Option<&PreAggregation> {
        self.pre_aggregations.iter().find(|p| p.name == name)
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_dimension_sql_expr() {
        let dim = Dimension::new("status");
        assert_eq!(dim.sql_expr(), "status");

        let dim = Dimension::new("order_status").with_sql("status");
        assert_eq!(dim.sql_expr(), "status");
    }

    #[test]
    fn test_metric_to_sql() {
        let metric = Metric::sum("revenue", "amount");
        assert_eq!(metric.to_sql(None), "SUM(amount)");
        assert_eq!(metric.to_sql(Some("o")), "SUM(o.amount)");

        let metric = Metric::count("order_count");
        assert_eq!(metric.to_sql(None), "COUNT(*)");

        // COUNT without explicit sql (simulates parsed definition)
        let metric = Metric {
            name: "order_count".to_string(),
            extends: None,
            agg: Some(Aggregation::Count),
            sql: None,
            ..Metric::new("order_count")
        };
        assert_eq!(metric.to_sql(None), "COUNT(*)");

        let metric = Metric::count_distinct("unique_customers", "customer_id");
        assert_eq!(metric.to_sql(Some("o")), "COUNT(DISTINCT o.customer_id)");

        let metric = Metric {
            name: "revenue_stddev".to_string(),
            extends: None,
            agg: Some(Aggregation::Stddev),
            sql: Some("amount".to_string()),
            ..Metric::new("revenue_stddev")
        };
        assert_eq!(metric.to_sql(Some("o")), "STDDEV(o.amount)");

        let metric = Metric {
            name: "revenue_variance_pop".to_string(),
            extends: None,
            agg: Some(Aggregation::VariancePop),
            sql: Some("amount".to_string()),
            ..Metric::new("revenue_variance_pop")
        };
        assert_eq!(metric.to_sql(None), "VAR_POP(amount)");
    }

    #[test]
    fn test_relationship_default_foreign_keys_match_native_contract() {
        let rel = Relationship::many_to_one("customers");
        assert_eq!(rel.foreign_key_columns(), vec!["customers_id".to_string()]);
        assert_eq!(rel.primary_key_columns(), vec!["id".to_string()]);

        let rel = Relationship::one_to_many("orders");
        assert_eq!(rel.foreign_key_columns(), vec!["id".to_string()]);
        assert_eq!(rel.primary_key_columns(), vec!["id".to_string()]);

        let mut rel = Relationship::new("profile");
        rel.r#type = RelationshipType::OneToOne;
        assert_eq!(rel.foreign_key_columns(), vec!["id".to_string()]);
        assert_eq!(rel.primary_key_columns(), vec!["id".to_string()]);
    }

    #[test]
    fn test_model_builder() {
        let model = Model::new("orders", "order_id")
            .with_table("public.orders")
            .with_dimension(Dimension::categorical("status"))
            .with_metric(Metric::sum("revenue", "amount"))
            .with_relationship(Relationship::many_to_one("customers"));

        assert_eq!(model.table_name(), "public.orders");
        assert!(model.get_dimension("status").is_some());
        assert!(model.get_metric("revenue").is_some());
        assert!(model.get_relationship("customers").is_some());
    }
}
