//! SQLite implementation of the StatsRepository.
//!
//! Provides aggregated statistics for the dashboard view including:
//! - Task counts (overdue, due today, due this week)
//! - Unread email counts
//! - Upcoming events
//! - High-urgency task list

use async_trait::async_trait;
use chrono::{DateTime, Duration, Utc};
use sqlx::SqlitePool;
use goingson_core::{CoreError, DashboardStats, HighUrgencyTask, Result, StatsRepository, UserId};

use crate::utils::{format_datetime, parse_datetime};

/// SQLite-backed implementation of [`StatsRepository`].
///
/// Computes dashboard statistics via optimized COUNT queries.
/// Returns aggregated metrics across tasks, emails, events, and projects.
pub struct SqliteStatsRepository { pool: SqlitePool }

impl SqliteStatsRepository {
    /// Creates a new repository instance with the given connection pool.
    #[tracing::instrument(skip_all)]
    pub fn new(pool: SqlitePool) -> Self { Self { pool } }
}

#[async_trait]
impl StatsRepository for SqliteStatsRepository {
    #[tracing::instrument(skip_all)]
    async fn get_dashboard_stats(
        &self,
        user_id: UserId,
        now: DateTime<Utc>,
        today_start: DateTime<Utc>,
        tomorrow_start: DateTime<Utc>,
        week_end: DateTime<Utc>,
    ) -> Result<DashboardStats> {
        let user_id_str = user_id.to_string();
        let now_str = format_datetime(&now);
        let today_start_str = format_datetime(&today_start);
        let tomorrow_start_str = format_datetime(&tomorrow_start);
        let week_end_str = format_datetime(&week_end);
        let events_end_str = format_datetime(&(now + Duration::days(7)));

        // Batch all 6 scalar counts into a single query using subqueries.
        // Day windows are bound UTC instants of the user's local day; all
        // predicates compare the bare indexed column (no date()/datetime()
        // wrapper) so they stay sargable on idx_tasks_due / start_time.
        #[derive(sqlx::FromRow)]
        struct StatsRow {
            tasks_due_today: i64,
            tasks_due_this_week: i64,
            overdue_count: i64,
            unread_emails: i64,
            upcoming_events: i64,
            active_projects: i64,
        }

        let stats: StatsRow = sqlx::query_as(
            "SELECT \
                (SELECT COUNT(*) FROM tasks WHERE user_id = ?1 \
                    AND status NOT IN ('Completed', 'Deleted') \
                    AND due IS NOT NULL AND due >= ?3 AND due < ?4) AS tasks_due_today, \
                (SELECT COUNT(*) FROM tasks WHERE user_id = ?1 \
                    AND status NOT IN ('Completed', 'Deleted') \
                    AND due IS NOT NULL AND due >= ?3 AND due < ?5) AS tasks_due_this_week, \
                (SELECT COUNT(*) FROM tasks WHERE user_id = ?1 \
                    AND status NOT IN ('Completed', 'Deleted') \
                    AND due IS NOT NULL AND due < ?2) AS overdue_count, \
                (SELECT COUNT(*) FROM emails WHERE user_id = ?1 \
                    AND is_read = 0) AS unread_emails, \
                (SELECT COUNT(*) FROM events WHERE user_id = ?1 \
                    AND start_time >= ?2 AND start_time <= ?6) AS upcoming_events, \
                (SELECT COUNT(*) FROM projects WHERE user_id = ?1 \
                    AND status = 'Active') AS active_projects")
            .bind(&user_id_str)        // ?1
            .bind(&now_str)            // ?2
            .bind(&today_start_str)    // ?3
            .bind(&tomorrow_start_str) // ?4
            .bind(&week_end_str)       // ?5
            .bind(&events_end_str)     // ?6
            .fetch_one(&self.pool).await.map_err(CoreError::database)?;

        // High-urgency tasks returns rows, so it remains a separate query.
        #[derive(sqlx::FromRow)]
        struct HighUrgencyRow { id: String, description: String, urgency: f64, status: String, due: Option<String> }

        let rows: Vec<HighUrgencyRow> = sqlx::query_as(
            "SELECT id, description, urgency, status, due FROM tasks \
                WHERE user_id = ? AND status NOT IN ('Completed', 'Deleted') \
                ORDER BY urgency DESC LIMIT 5")
            .bind(&user_id_str)
            .fetch_all(&self.pool).await.map_err(CoreError::database)?;

        let high_urgency_tasks = rows.into_iter().map(|row| {
            HighUrgencyTask {
                id: row.id,
                description: row.description,
                urgency: row.urgency,
                status: row.status,
                due: row.due.and_then(|d| parse_datetime(&d).ok()).map(|dt| dt.to_rfc3339()),
            }
        }).collect();

        Ok(DashboardStats {
            tasks_due_today: stats.tasks_due_today,
            tasks_due_this_week: stats.tasks_due_this_week,
            overdue_count: stats.overdue_count,
            unread_emails: stats.unread_emails,
            upcoming_events: stats.upcoming_events,
            active_projects: stats.active_projects,
            high_urgency_tasks,
        })
    }
}