//! 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 sqlx::SqlitePool;
use goingson_core::{CoreError, DashboardStats, HighUrgencyTask, Result, StatsRepository, UserId};

use crate::utils::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) -> Result<DashboardStats> {
        let user_id_str = user_id.to_string();

        // Batch all 6 scalar counts into a single query using subqueries.
        #[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 date(due) = date('now')) AS tasks_due_today, \
                (SELECT COUNT(*) FROM tasks WHERE user_id = ?1 \
                    AND status NOT IN ('Completed', 'Deleted') \
                    AND due IS NOT NULL AND date(due) >= date('now') \
                    AND date(due) <= date('now', '+7 days')) 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 datetime(due) < datetime('now')) 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 datetime(start_time) >= datetime('now') \
                    AND datetime(start_time) <= datetime('now', '+7 days')) AS upcoming_events, \
                (SELECT COUNT(*) FROM projects WHERE user_id = ?1 \
                    AND status = 'Active') AS active_projects")
            .bind(&user_id_str)
            .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,
        })
    }
}