//! Analytics queries: time-bucketed revenue and period-over-period comparisons.

use chrono::{DateTime, Datelike, Utc};
use sqlx::PgPool;
use uuid::Uuid;

use super::{Cents, FollowTargetType, ItemId, ProjectId, UserId};
use crate::error::Result;

/// Time range for analytics queries.
pub enum TimeRange {
    Days7,
    Days30,
    Days90,
    All,
}

impl std::str::FromStr for TimeRange {
    type Err = ();
    fn from_str(s: &str) -> std::result::Result<Self, ()> {
        match s {
            "7d" => Ok(Self::Days7),
            "30d" => Ok(Self::Days30),
            "90d" => Ok(Self::Days90),
            "all" => Ok(Self::All),
            _ => Err(()),
        }
    }
}

impl std::fmt::Display for TimeRange {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        match self {
            Self::Days7 => f.write_str("7d"),
            Self::Days30 => f.write_str("30d"),
            Self::Days90 => f.write_str("90d"),
            Self::All => f.write_str("all"),
        }
    }
}

impl TimeRange {
    /// SQL interval string for the current period, or `None` for All.
    ///
    /// INVARIANT: These values are interpolated into SQL via format!. They MUST be
    /// compile-time constants with no user input. The exhaustive match ensures
    /// new variants require explicit SQL strings.
    pub(crate) fn interval_sql(&self) -> Option<&'static str> {
        match self {
            Self::Days7 => Some("7 days"),
            Self::Days30 => Some("30 days"),
            Self::Days90 => Some("90 days"),
            Self::All => None,
        }
    }

    /// SQL date_trunc bucket size: day for short ranges, week for 90d, month for All.
    ///
    /// SAFETY: Interpolated into SQL via format!. Must be compile-time constants.
    pub(crate) fn bucket_sql(&self) -> &'static str {
        match self {
            Self::Days7 | Self::Days30 => "day",
            Self::Days90 => "week",
            Self::All => "month",
        }
    }
}

/// A single time bucket in a revenue timeseries.
pub struct TimeBucket {
    pub label: String,
    pub revenue_cents: Cents,
    pub sales_count: i64,
}

/// Period-over-period comparison data for stat cards.
pub struct PeriodComparison {
    pub current_revenue_cents: Cents,
    pub previous_revenue_cents: Cents,
    pub current_sales: i64,
    pub previous_sales: i64,
    pub current_followers: i64,
    pub previous_followers: i64,
}

impl PeriodComparison {
    /// Percentage change in revenue, e.g. `("+42%", true)`. None if no previous data.
    #[tracing::instrument(skip_all)]
    pub fn revenue_change(&self) -> Option<(String, bool)> {
        pct_change(self.current_revenue_cents.as_i64(), self.previous_revenue_cents.as_i64())
    }

    /// Percentage change in sales count.
    #[tracing::instrument(skip_all)]
    pub fn sales_change(&self) -> Option<(String, bool)> {
        pct_change(self.current_sales, self.previous_sales)
    }

    /// Percentage change in follower count.
    #[tracing::instrument(skip_all)]
    pub fn followers_change(&self) -> Option<(String, bool)> {
        pct_change(self.current_followers, self.previous_followers)
    }
}

/// Compute percentage change text. Returns None when previous is zero.
pub(crate) fn pct_change(current: i64, previous: i64) -> Option<(String, bool)> {
    if previous == 0 {
        return None;
    }
    let pct = ((current - previous) as f64 / previous as f64 * 100.0).round() as i64;
    let is_positive = pct >= 0;
    let text = if is_positive {
        format!("+{pct}%")
    } else {
        format!("{pct}%")
    };
    Some((text, is_positive))
}

/// Format a bucket timestamp into a human-readable label.
pub(crate) fn format_bucket_label(dt: &DateTime<Utc>, range: &TimeRange) -> String {
    match range {
        TimeRange::Days7 | TimeRange::Days30 => dt.format("%b %-d").to_string(),
        TimeRange::Days90 => format!("Week {}", dt.iso_week().week()),
        TimeRange::All => dt.format("%b %Y").to_string(),
    }
}

// ── Scope-aware query building ──

/// Scope determines the WHERE clause and bind parameters for transaction queries.
enum Scope {
    Item(ItemId),
    Project(ProjectId),
    User,
}

impl Scope {
    fn from_ids(item_id: Option<ItemId>, project_id: Option<ProjectId>) -> Self {
        match (item_id, project_id) {
            (Some(iid), _) => Scope::Item(iid),
            (None, Some(pid)) => Scope::Project(pid),
            (None, None) => Scope::User,
        }
    }

    /// WHERE clause fragment (assumes seller_id is $1).
    fn where_clause(&self) -> &'static str {
        match self {
            Scope::Item(_) => "seller_id = $1 AND item_id = $2 AND status = 'completed'",
            Scope::Project(_) => "t.seller_id = $1 AND t.item_id IN (SELECT id FROM items WHERE project_id = $2) AND t.status = 'completed'",
            Scope::User => "seller_id = $1 AND status = 'completed'",
        }
    }

    /// Table alias prefix: "t." for project scope (uses subquery), empty for others.
    fn table_prefix(&self) -> &'static str {
        match self {
            Scope::Project(_) => "t.",
            _ => "",
        }
    }

    /// Table alias: "transactions t" for project scope, "transactions" for others.
    fn table_name(&self) -> &'static str {
        match self {
            Scope::Project(_) => "transactions t",
            _ => "transactions",
        }
    }

    /// Bind the scope-specific parameter ($2) if applicable.
    fn bind_scope<'q, O>(
        &self,
        query: sqlx::query::QueryAs<'q, sqlx::Postgres, O, sqlx::postgres::PgArguments>,
    ) -> sqlx::query::QueryAs<'q, sqlx::Postgres, O, sqlx::postgres::PgArguments> {
        match self {
            Scope::Item(iid) => query.bind(*iid),
            Scope::Project(pid) => query.bind(*pid),
            Scope::User => query,
        }
    }

}

/// Fetch time-bucketed revenue data for a seller, optionally filtered by project or item.
#[tracing::instrument(skip_all)]
pub async fn get_revenue_timeseries(
    pool: &PgPool,
    seller_id: UserId,
    project_id: Option<ProjectId>,
    item_id: Option<ItemId>,
    range: &TimeRange,
) -> Result<Vec<TimeBucket>> {
    let bucket = range.bucket_sql();
    let scope = Scope::from_ids(item_id, project_id);
    let prefix = scope.table_prefix();
    let table = scope.table_name();
    let where_clause = scope.where_clause();

    let time_filter = match range.interval_sql() {
        Some(interval) => format!(" AND {prefix}completed_at >= NOW() - INTERVAL '{interval}'"),
        None => String::new(),
    };

    let sql = format!(
        r#"
        SELECT
            date_trunc('{bucket}', {prefix}completed_at) AS bucket,
            COALESCE(SUM({prefix}amount_cents), 0)::BIGINT,
            COUNT(*)
        FROM {table}
        WHERE {where_clause}{time_filter}
        GROUP BY bucket
        ORDER BY bucket
        LIMIT 500
        "#
    );

    let q = sqlx::query_as::<_, (DateTime<Utc>, i64, i64)>(&sql).bind(seller_id);
    let rows = scope.bind_scope(q).fetch_all(pool).await?;

    let buckets = rows
        .into_iter()
        .map(|(dt, revenue, count)| TimeBucket {
            label: format_bucket_label(&dt, range),
            revenue_cents: Cents::new(revenue),
            sales_count: count,
        })
        .collect();

    Ok(buckets)
}

/// Fetch period-over-period comparison data for stat cards.
///
/// Compares the current period against the previous period of the same length.
/// For `TimeRange::All`, previous values are zero (no comparison possible).
#[tracing::instrument(skip_all)]
pub async fn get_period_comparison(
    pool: &PgPool,
    seller_id: UserId,
    project_id: Option<ProjectId>,
    item_id: Option<ItemId>,
    range: &TimeRange,
) -> Result<PeriodComparison> {
    let (current_revenue, prev_revenue, current_sales, prev_sales) =
        get_transaction_comparison(pool, seller_id, project_id, item_id, range).await?;

    let (current_followers, prev_followers) =
        get_follower_comparison(pool, seller_id, project_id, item_id, range).await?;

    Ok(PeriodComparison {
        current_revenue_cents: Cents::new(current_revenue),
        previous_revenue_cents: Cents::new(prev_revenue),
        current_sales,
        previous_sales: prev_sales,
        current_followers,
        previous_followers: prev_followers,
    })
}

/// Transaction revenue/sales comparison using FILTER (WHERE ...) conditional aggregation.
async fn get_transaction_comparison(
    pool: &PgPool,
    seller_id: UserId,
    project_id: Option<ProjectId>,
    item_id: Option<ItemId>,
    range: &TimeRange,
) -> Result<(i64, i64, i64, i64)> {
    let scope = Scope::from_ids(item_id, project_id);
    let prefix = scope.table_prefix();
    let table = scope.table_name();
    let where_clause = scope.where_clause();

    let Some(interval) = range.interval_sql() else {
        // All time: just sum everything, no previous period
        let sql = format!(
            r#"
            SELECT
                COALESCE(SUM({prefix}amount_cents), 0)::BIGINT,
                COUNT(*)
            FROM {table}
            WHERE {where_clause}
            "#
        );
        let q = sqlx::query_as::<_, (i64, i64)>(&sql).bind(seller_id);
        let row = scope.bind_scope(q).fetch_one(pool).await?;
        return Ok((row.0, 0, row.1, 0));
    };

    // Current vs previous period using FILTER
    let sql = format!(
        r#"
        SELECT
            COALESCE(SUM({prefix}amount_cents) FILTER (WHERE {prefix}completed_at >= NOW() - INTERVAL '{interval}'), 0)::BIGINT,
            COUNT(*) FILTER (WHERE {prefix}completed_at >= NOW() - INTERVAL '{interval}'),
            COALESCE(SUM({prefix}amount_cents) FILTER (WHERE {prefix}completed_at < NOW() - INTERVAL '{interval}'), 0)::BIGINT,
            COUNT(*) FILTER (WHERE {prefix}completed_at < NOW() - INTERVAL '{interval}')
        FROM {table}
        WHERE {where_clause}
          AND {prefix}completed_at >= NOW() - INTERVAL '{interval}' * 2
        "#
    );

    let q = sqlx::query_as::<_, (i64, i64, i64, i64)>(&sql).bind(seller_id);
    let row = scope.bind_scope(q).fetch_one(pool).await?;

    Ok((row.0, row.2, row.1, row.3))
}

/// Follower delta comparison. Users and projects have followers; items do not.
async fn get_follower_comparison(
    pool: &PgPool,
    seller_id: UserId,
    project_id: Option<ProjectId>,
    item_id: Option<ItemId>,
    range: &TimeRange,
) -> Result<(i64, i64)> {
    // Items don't have followers
    if item_id.is_some() {
        return Ok((0, 0));
    }

    let (target_type, target_id): (FollowTargetType, Uuid) = match project_id {
        Some(pid) => (FollowTargetType::Project, pid.into()),
        None => (FollowTargetType::User, seller_id.into()),
    };

    let Some(interval) = range.interval_sql() else {
        // All time: just total count, no previous
        let row: (i64,) = sqlx::query_as(
            "SELECT COUNT(*) FROM follows WHERE target_type = $1 AND target_id = $2",
        )
        .bind(target_type)
        .bind(target_id)
        .fetch_one(pool)
        .await?;
        return Ok((row.0, 0));
    };

    let row: (i64, i64) = sqlx::query_as(
        &format!(
            r#"
            SELECT
                COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '{interval}'),
                COUNT(*) FILTER (WHERE created_at < NOW() - INTERVAL '{interval}')
            FROM follows
            WHERE target_type = $1
              AND target_id = $2
              AND created_at >= NOW() - INTERVAL '{interval}' * 2
            "#
        ),
    )
    .bind(target_type)
    .bind(target_id)
    .fetch_one(pool)
    .await?;

    Ok((row.0, row.1))
}

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

    #[test]
    fn time_range_from_str() {
        assert!(matches!("7d".parse::<TimeRange>(), Ok(TimeRange::Days7)));
        assert!(matches!("30d".parse::<TimeRange>(), Ok(TimeRange::Days30)));
        assert!(matches!("90d".parse::<TimeRange>(), Ok(TimeRange::Days90)));
        assert!(matches!("all".parse::<TimeRange>(), Ok(TimeRange::All)));
        assert!("bad".parse::<TimeRange>().is_err());
    }

    #[test]
    fn time_range_display_roundtrip() {
        for s in ["7d", "30d", "90d", "all"] {
            let range: TimeRange = s.parse().unwrap();
            assert_eq!(range.to_string(), s);
        }
    }

    #[test]
    fn time_range_interval_sql() {
        assert_eq!(TimeRange::Days7.interval_sql(), Some("7 days"));
        assert_eq!(TimeRange::Days30.interval_sql(), Some("30 days"));
        assert_eq!(TimeRange::Days90.interval_sql(), Some("90 days"));
        assert_eq!(TimeRange::All.interval_sql(), None);
    }

    #[test]
    fn time_range_bucket_sql() {
        assert_eq!(TimeRange::Days7.bucket_sql(), "day");
        assert_eq!(TimeRange::Days30.bucket_sql(), "day");
        assert_eq!(TimeRange::Days90.bucket_sql(), "week");
        assert_eq!(TimeRange::All.bucket_sql(), "month");
    }

    #[test]
    fn pct_change_positive() {
        let (text, positive) = pct_change(142, 100).unwrap();
        assert_eq!(text, "+42%");
        assert!(positive);
    }

    #[test]
    fn pct_change_negative() {
        let (text, positive) = pct_change(50, 100).unwrap();
        assert_eq!(text, "-50%");
        assert!(!positive);
    }

    #[test]
    fn pct_change_zero_previous() {
        assert!(pct_change(100, 0).is_none());
    }

    #[test]
    fn pct_change_no_change() {
        let (text, positive) = pct_change(100, 100).unwrap();
        assert_eq!(text, "+0%");
        assert!(positive);
    }

    #[test]
    fn format_label_day() {
        let dt = "2026-03-01T00:00:00Z".parse::<DateTime<Utc>>().unwrap();
        assert_eq!(format_bucket_label(&dt, &TimeRange::Days7), "Mar 1");
        assert_eq!(format_bucket_label(&dt, &TimeRange::Days30), "Mar 1");
    }

    #[test]
    fn format_label_week() {
        let dt = "2026-03-01T00:00:00Z".parse::<DateTime<Utc>>().unwrap();
        let label = format_bucket_label(&dt, &TimeRange::Days90);
        assert!(label.starts_with("Week "));
    }

    #[test]
    fn format_label_month() {
        let dt = "2026-01-01T00:00:00Z".parse::<DateTime<Utc>>().unwrap();
        assert_eq!(format_bucket_label(&dt, &TimeRange::All), "Jan 2026");
    }

    #[test]
    fn period_comparison_helpers() {
        let pc = PeriodComparison {
            current_revenue_cents: Cents::new(200),
            previous_revenue_cents: Cents::new(100),
            current_sales: 10,
            previous_sales: 20,
            current_followers: 50,
            previous_followers: 0,
        };

        let (rev_text, rev_pos) = pc.revenue_change().unwrap();
        assert_eq!(rev_text, "+100%");
        assert!(rev_pos);

        let (sales_text, sales_pos) = pc.sales_change().unwrap();
        assert_eq!(sales_text, "-50%");
        assert!(!sales_pos);

        assert!(pc.followers_change().is_none());
    }
}