//! SQLite implementation of time session operations.
//!
//! Manages start/stop/discard timer sessions and aggregation queries.
//! At most one session per user can be active (ended_at IS NULL) at any time.

use std::collections::HashMap;
use chrono::{DateTime, Utc};
use sqlx::SqlitePool;

use goingson_core::{
    CoreError, Result, TaskId, TimeSession, TimeSessionId, TimeTrackingSummary, UserId,
};

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

/// Row struct for time session queries.
#[derive(Debug, sqlx::FromRow)]
struct TimeSessionRow {
    id: String,
    task_id: String,
    user_id: String,
    started_at: String,
    ended_at: Option<String>,
    duration_minutes: Option<i32>,
    created_at: String,
}

impl TimeSessionRow {
    fn into_session(self) -> Result<TimeSession> {
        Ok(TimeSession {
            id: parse_uuid(&self.id)?.into(),
            task_id: parse_uuid(&self.task_id)?.into(),
            user_id: parse_uuid(&self.user_id)?.into(),
            started_at: parse_datetime(&self.started_at)?,
            ended_at: self.ended_at.as_ref().map(|s| parse_datetime(s)).transpose()?,
            duration_minutes: self.duration_minutes,
            created_at: parse_datetime(&self.created_at)?,
        })
    }
}

/// Batch-fetch active sessions for a set of tasks.
/// Returns a map from TaskId to the active TimeSession (if any).
pub(crate) async fn get_active_sessions_for_tasks(
    pool: &SqlitePool,
    task_ids: &[String],
) -> Result<HashMap<TaskId, TimeSession>> {
    if task_ids.is_empty() {
        return Ok(HashMap::new());
    }

    let placeholders = task_ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
    let sql = format!(
        "SELECT id, task_id, user_id, started_at, ended_at, duration_minutes, created_at
         FROM time_sessions WHERE task_id IN ({}) AND ended_at IS NULL",
        placeholders
    );

    let mut query = sqlx::query_as::<_, TimeSessionRow>(&sql);
    for id in task_ids {
        query = query.bind(id);
    }

    let rows = query.fetch_all(pool).await.map_err(CoreError::database)?;

    let mut map = HashMap::new();
    for row in rows {
        let task_id: TaskId = parse_uuid(&row.task_id)?.into();
        map.insert(task_id, row.into_session()?);
    }

    Ok(map)
}

/// Start a timer on a task. Fails if any session is already active for the user.
/// Uses a transaction to prevent double-start from concurrent requests.
pub(crate) async fn start_timer(
    pool: &SqlitePool,
    task_id: TaskId,
    user_id: UserId,
) -> Result<TimeSession> {
    let mut tx = pool.begin().await.map_err(CoreError::database)?;

    // Check for existing active session (inside transaction to prevent race)
    let existing: Option<(String,)> = sqlx::query_as(
        "SELECT id FROM time_sessions WHERE user_id = ? AND ended_at IS NULL LIMIT 1"
    )
    .bind(user_id.to_string())
    .fetch_optional(&mut *tx)
    .await
    .map_err(CoreError::database)?;

    if existing.is_some() {
        return Err(CoreError::validation(
            "timer",
            "A timer is already running. Stop or discard it first.",
        ));
    }

    let id = TimeSessionId::new();
    let now = format_datetime_now();

    sqlx::query(
        "INSERT INTO time_sessions (id, task_id, user_id, started_at, created_at) VALUES (?, ?, ?, ?, ?)"
    )
    .bind(id.to_string())
    .bind(task_id.to_string())
    .bind(user_id.to_string())
    .bind(&now)
    .bind(&now)
    .execute(&mut *tx)
    .await
    .map_err(CoreError::database)?;

    let row = sqlx::query_as::<_, TimeSessionRow>(
        "SELECT id, task_id, user_id, started_at, ended_at, duration_minutes, created_at FROM time_sessions WHERE id = ?"
    )
    .bind(id.to_string())
    .fetch_one(&mut *tx)
    .await
    .map_err(CoreError::database)?;

    tx.commit().await.map_err(CoreError::database)?;
    row.into_session()
}

/// Stop the active timer on a task. Updates duration_minutes and the task's actual_minutes cache.
/// Uses a transaction so session end and task actual_minutes are updated atomically.
pub(crate) async fn stop_timer(
    pool: &SqlitePool,
    task_id: TaskId,
    user_id: UserId,
) -> Result<Option<TimeSession>> {
    // Find active session for this task
    let row = sqlx::query_as::<_, TimeSessionRow>(
        "SELECT id, task_id, user_id, started_at, ended_at, duration_minutes, created_at
         FROM time_sessions WHERE task_id = ? AND user_id = ? AND ended_at IS NULL"
    )
    .bind(task_id.to_string())
    .bind(user_id.to_string())
    .fetch_optional(pool)
    .await
    .map_err(CoreError::database)?;

    let row = match row {
        Some(r) => r,
        None => return Ok(None),
    };

    let started_at = parse_datetime(&row.started_at)?;
    let now = Utc::now();
    let duration = (now - started_at).num_minutes().max(0) as i32;
    let now_str = format_datetime(&now);

    let mut tx = pool.begin().await.map_err(CoreError::database)?;

    // Update the session
    sqlx::query(
        "UPDATE time_sessions SET ended_at = ?, duration_minutes = ? WHERE id = ?"
    )
    .bind(&now_str)
    .bind(duration)
    .bind(&row.id)
    .execute(&mut *tx)
    .await
    .map_err(CoreError::database)?;

    // Update the task's actual_minutes cache
    sqlx::query(
        "UPDATE tasks SET actual_minutes = actual_minutes + ? WHERE id = ?"
    )
    .bind(duration)
    .bind(task_id.to_string())
    .execute(&mut *tx)
    .await
    .map_err(CoreError::database)?;

    tx.commit().await.map_err(CoreError::database)?;

    // Fetch updated session
    let updated = sqlx::query_as::<_, TimeSessionRow>(
        "SELECT id, task_id, user_id, started_at, ended_at, duration_minutes, created_at FROM time_sessions WHERE id = ?"
    )
    .bind(&row.id)
    .fetch_one(pool)
    .await
    .map_err(CoreError::database)?;

    Ok(Some(updated.into_session()?))
}

/// Discard the active timer without updating actual_minutes.
pub(crate) async fn discard_timer(
    pool: &SqlitePool,
    task_id: TaskId,
    user_id: UserId,
) -> Result<bool> {
    let result = sqlx::query(
        "DELETE FROM time_sessions WHERE task_id = ? AND user_id = ? AND ended_at IS NULL"
    )
    .bind(task_id.to_string())
    .bind(user_id.to_string())
    .execute(pool)
    .await
    .map_err(CoreError::database)?;

    Ok(result.rows_affected() > 0)
}

/// Get the currently active timer for a user with the task description.
pub(crate) async fn get_active_timer(
    pool: &SqlitePool,
    user_id: UserId,
) -> Result<Option<(TimeSession, String)>> {
    #[derive(sqlx::FromRow)]
    #[allow(dead_code)]
    struct ActiveTimerRow {
        id: String,
        task_id: String,
        user_id: String,
        started_at: String,
        ended_at: Option<String>,
        duration_minutes: Option<i32>,
        created_at: String,
        task_description: String,
    }

    let row = sqlx::query_as::<_, ActiveTimerRow>(
        "SELECT ts.id, ts.task_id, ts.user_id, ts.started_at, ts.ended_at, ts.duration_minutes, ts.created_at,
                t.description as task_description
         FROM time_sessions ts
         JOIN tasks t ON t.id = ts.task_id
         WHERE ts.user_id = ? AND ts.ended_at IS NULL
         LIMIT 1"
    )
    .bind(user_id.to_string())
    .fetch_optional(pool)
    .await
    .map_err(CoreError::database)?;

    match row {
        Some(r) => {
            let session = TimeSession {
                id: parse_uuid(&r.id)?.into(),
                task_id: parse_uuid(&r.task_id)?.into(),
                user_id: parse_uuid(&r.user_id)?.into(),
                started_at: parse_datetime(&r.started_at)?,
                ended_at: None,
                duration_minutes: r.duration_minutes,
                created_at: parse_datetime(&r.created_at)?,
            };
            Ok(Some((session, r.task_description)))
        }
        None => Ok(None),
    }
}

/// List all time sessions for a task.
pub(crate) async fn list_time_sessions(
    pool: &SqlitePool,
    task_id: TaskId,
    user_id: UserId,
) -> Result<Vec<TimeSession>> {
    let rows = sqlx::query_as::<_, TimeSessionRow>(
        "SELECT id, task_id, user_id, started_at, ended_at, duration_minutes, created_at
         FROM time_sessions WHERE task_id = ? AND user_id = ?
         ORDER BY started_at DESC"
    )
    .bind(task_id.to_string())
    .bind(user_id.to_string())
    .fetch_all(pool)
    .await
    .map_err(CoreError::database)?;

    rows.into_iter().map(|r| r.into_session()).collect()
}

/// Get aggregated time tracking summary grouped by project and date.
pub(crate) async fn get_time_summary(
    pool: &SqlitePool,
    user_id: UserId,
    start: DateTime<Utc>,
    end: DateTime<Utc>,
) -> Result<Vec<TimeTrackingSummary>> {
    #[derive(sqlx::FromRow)]
    struct SummaryRow {
        project_id: Option<String>,
        project_name: Option<String>,
        date: String,
        total_minutes: i32,
        session_count: i32,
    }

    let start_str = format_datetime(&start);
    let end_str = format_datetime(&end);

    let rows = sqlx::query_as::<_, SummaryRow>(
        "SELECT t.project_id, p.name as project_name,
                date(ts.started_at) as date,
                CAST(COALESCE(SUM(ts.duration_minutes), 0) AS INTEGER) as total_minutes,
                CAST(COUNT(*) AS INTEGER) as session_count
         FROM time_sessions ts
         JOIN tasks t ON t.id = ts.task_id
         LEFT JOIN projects p ON p.id = t.project_id
         WHERE ts.user_id = ? AND ts.ended_at IS NOT NULL
           AND ts.started_at >= ? AND ts.started_at < ?
         GROUP BY t.project_id, date(ts.started_at)
         ORDER BY date DESC, total_minutes DESC"
    )
    .bind(user_id.to_string())
    .bind(&start_str)
    .bind(&end_str)
    .fetch_all(pool)
    .await
    .map_err(CoreError::database)?;

    rows.into_iter().map(|r| {
        Ok(TimeTrackingSummary {
            project_id: r.project_id.as_ref().map(|s| parse_uuid(s)).transpose()?.map(Into::into),
            project_name: r.project_name,
            date: r.date,
            total_minutes: r.total_minutes,
            session_count: r.session_count,
        })
    }).collect()
}

/// Log a manual time entry (completed session, no live timer).
pub(crate) async fn log_manual_time(
    pool: &SqlitePool,
    task_id: TaskId,
    user_id: UserId,
    minutes: i32,
    date: DateTime<Utc>,
) -> Result<TimeSession> {
    use chrono::Duration;

    let id = TimeSessionId::new();
    let started_at = date;
    let ended_at = date + Duration::minutes(minutes as i64);
    let now = Utc::now();

    let started_str = format_datetime(&started_at);
    let ended_str = format_datetime(&ended_at);
    let created_str = format_datetime(&now);

    sqlx::query(
        "INSERT INTO time_sessions (id, task_id, user_id, started_at, ended_at, duration_minutes, created_at)
         VALUES (?, ?, ?, ?, ?, ?, ?)"
    )
    .bind(id.to_string())
    .bind(task_id.to_string())
    .bind(user_id.to_string())
    .bind(&started_str)
    .bind(&ended_str)
    .bind(minutes)
    .bind(&created_str)
    .execute(pool)
    .await
    .map_err(CoreError::database)?;

    // Update task's cached actual_minutes
    sqlx::query(
        "UPDATE tasks SET actual_minutes = COALESCE(actual_minutes, 0) + ?, updated_at = ?
         WHERE id = ? AND user_id = ?"
    )
    .bind(minutes)
    .bind(&created_str)
    .bind(task_id.to_string())
    .bind(user_id.to_string())
    .execute(pool)
    .await
    .map_err(CoreError::database)?;

    Ok(TimeSession {
        id,
        task_id,
        user_id,
        started_at,
        ended_at: Some(ended_at),
        duration_minutes: Some(minutes),
        created_at: now,
    })
}