//! Session tracking for remote revocation.

use sqlx::PgPool;

use super::{DbUserSession, UserId, UserSessionId};
use crate::error::Result;

/// Insert a tracked session row and return its ID.
#[tracing::instrument(skip_all)]
pub async fn create_user_session(
    pool: &PgPool,
    user_id: UserId,
    user_agent: Option<&str>,
    ip_address: Option<&str>,
) -> Result<UserSessionId> {
    let row = sqlx::query_scalar!(
        r#"INSERT INTO user_sessions (user_id, user_agent, ip_address) VALUES ($1, $2, $3) RETURNING id AS "id: UserSessionId""#,
        user_id as UserId,
        user_agent,
        ip_address,
    )
    .fetch_one(pool)
    .await?;

    Ok(row)
}

/// Cap a user's active sessions to the newest `max`, deleting older ones.
///
/// Bounds unbounded `user_sessions` growth from repeated logins (or an automated
/// loop). Only `kind='active'` rows are counted and pruned, `pending_2fa`
/// intermediates are never touched. Ordered by `last_active_at DESC`, so the
/// freshly-created current session (newest) is always kept and the stalest are
/// evicted. Returns the number of rows pruned.
///
/// The keep-set is ordered `last_active_at DESC, id DESC`, the `id` tie-break
/// makes the eviction deterministic when several sessions share a timestamp,
/// instead of leaving the choice to Postgres' undefined row order (Run 11 Sec LOW).
#[tracing::instrument(skip_all)]
pub async fn prune_user_sessions_over_cap(pool: &PgPool, user_id: UserId, max: i64) -> Result<u64> {
    let result = sqlx::query!(
        r#"
        DELETE FROM user_sessions
        WHERE user_id = $1 AND kind = 'active'
          AND id NOT IN (
              SELECT id FROM user_sessions
              WHERE user_id = $1 AND kind = 'active'
              ORDER BY last_active_at DESC, id DESC
              LIMIT $2
          )
        "#,
        user_id as UserId,
        max,
    )
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

/// Insert a `kind='pending_2fa'` row for an intermediate session held between
/// the password step and the TOTP/backup-code step. Exposed to
/// `delete_all_sessions_for_user` so "log out everywhere" sweeps a phisher
/// who has the password but not the second factor.
#[tracing::instrument(skip_all)]
pub async fn create_pending_2fa_session(
    pool: &PgPool,
    user_id: UserId,
    user_agent: Option<&str>,
    ip_address: Option<&str>,
) -> Result<UserSessionId> {
    let row = sqlx::query_scalar!(
        r#"INSERT INTO user_sessions (user_id, user_agent, ip_address, kind)
         VALUES ($1, $2, $3, 'pending_2fa') RETURNING id AS "id: UserSessionId""#,
        user_id as UserId,
        user_agent,
        ip_address,
    )
    .fetch_one(pool)
    .await?;

    Ok(row)
}

/// Confirm the pending_2fa tracking row is still present (i.e. wasn't swept
/// by `delete_all_sessions_for_user` while the user was at the TOTP prompt).
#[tracing::instrument(skip_all)]
pub async fn pending_2fa_session_exists(
    pool: &PgPool,
    id: UserSessionId,
    user_id: UserId,
) -> Result<bool> {
    let exists = sqlx::query_scalar!(
        r#"SELECT EXISTS(SELECT 1 FROM user_sessions WHERE id = $1 AND user_id = $2 AND kind = 'pending_2fa') AS "exists!""#,
        id as UserSessionId,
        user_id as UserId,
    )
    .fetch_one(pool)
    .await?;
    Ok(exists)
}

/// Delete a pending_2fa tracking row. Called when 2FA succeeds (the caller
/// then `track_session`s a fresh 'active' row) or when the pending state is
/// cleared (expiry, account lockout, navigation away).
///
/// Scoped by `user_id` (not id alone) so a guessed/enumerated session id can't
/// delete another user's pending_2fa row (ultra-fuzz Run 11 Sec LOW).
#[tracing::instrument(skip_all)]
pub async fn delete_pending_2fa_session(
    pool: &PgPool,
    id: UserSessionId,
    user_id: UserId,
) -> Result<()> {
    sqlx::query!(
        "DELETE FROM user_sessions WHERE id = $1 AND user_id = $2 AND kind = 'pending_2fa'",
        id as UserSessionId,
        user_id as UserId,
    )
    .execute(pool)
    .await?;
    Ok(())
}

/// Result of touching a session: whether it exists and the user's current
/// suspended status (live from the `users` table, not cached in the session).
pub struct TouchResult {
    /// `false` if the session row was deleted (revoked).
    pub valid: bool,
    /// Current `suspended_at IS NOT NULL` from the users table.
    /// Only meaningful when `valid` is `true`.
    pub suspended: bool,
    /// Current `can_create_projects` from the users table.
    /// Only meaningful when `valid` is `true`.
    pub can_create_projects: bool,
    /// Whether the user has an active Fan+ subscription.
    pub is_fan_plus: bool,
    /// Active creator tier name (e.g. "SmallFiles"), or None.
    pub creator_tier: Option<String>,
}

/// Update `last_active_at`, confirm the session still exists, and return
/// the user's current `suspended` status from the `users` table.
///
/// This ensures suspension takes effect immediately even if the session
/// was created before the admin suspended the user.
#[tracing::instrument(skip_all)]
pub async fn touch_session(pool: &PgPool, session_id: UserSessionId) -> Result<TouchResult> {
    // Single query: update last_active_at, join users for live status, and check
    // fan_plus + creator_tier via subqueries (avoids 2 extra round-trips in auth extractor).
    let row = sqlx::query!(
        r#"
        UPDATE user_sessions us
        SET last_active_at = NOW()
        FROM users u
        WHERE us.id = $1 AND u.id = us.user_id
        RETURNING
            u.suspended_at IS NOT NULL AS "suspended!",
            u.can_create_projects AS "can_create_projects!",
            EXISTS(SELECT 1 FROM fan_plus_subscriptions fps WHERE fps.user_id = u.id AND fps.status = 'active') AS "is_fan_plus!",
            (SELECT cs.tier FROM creator_subscriptions cs WHERE cs.user_id = u.id AND cs.status = 'active') AS "creator_tier"
        "#,
        session_id as UserSessionId,
    )
    .map(|r| (r.suspended, r.can_create_projects, r.is_fan_plus, r.creator_tier))
    .fetch_optional(pool)
    .await?;

    match row {
        Some((suspended, can_create_projects, is_fan_plus, creator_tier)) => Ok(TouchResult {
            valid: true,
            suspended,
            can_create_projects,
            is_fan_plus,
            creator_tier,
        }),
        None => Ok(TouchResult {
            valid: false,
            suspended: false,
            can_create_projects: false,
            is_fan_plus: false,
            creator_tier: None,
        }),
    }
}

/// List all active sessions for a user, newest first.
#[tracing::instrument(skip_all)]
pub async fn get_user_sessions(pool: &PgPool, user_id: UserId) -> Result<Vec<DbUserSession>> {
    let sessions = sqlx::query_as!(
        DbUserSession,
        r#"SELECT id AS "id: UserSessionId", user_id AS "user_id: UserId",
                  created_at AS "created_at: chrono::DateTime<chrono::Utc>",
                  last_active_at AS "last_active_at: chrono::DateTime<chrono::Utc>",
                  user_agent, ip_address
         FROM user_sessions
         WHERE user_id = $1
         ORDER BY last_active_at DESC
         LIMIT 100"#,
        user_id as UserId,
    )
    .fetch_all(pool)
    .await?;

    Ok(sessions)
}

/// Count active sessions for a user.
#[tracing::instrument(skip_all)]
pub async fn count_user_sessions(pool: &PgPool, user_id: UserId) -> Result<i64> {
    let count = sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM user_sessions WHERE user_id = $1"#,
        user_id as UserId,
    )
    .fetch_one(pool)
    .await?;

    Ok(count)
}

/// Delete a single session, scoped to the owning user. Returns `true` if deleted.
#[tracing::instrument(skip_all)]
pub async fn delete_user_session(
    pool: &PgPool,
    session_id: UserSessionId,
    user_id: UserId,
) -> Result<bool> {
    let rows = sqlx::query!(
        "DELETE FROM user_sessions WHERE id = $1 AND user_id = $2",
        session_id as UserSessionId,
        user_id as UserId,
    )
    .execute(pool)
    .await?;

    Ok(rows.rows_affected() > 0)
}

/// Delete a session row, scoped to a specific user.
///
/// The user scoping isn't strictly required for correctness in the current
/// caller (logout reads its own tracking ID out of the session and we
/// trust that), but the unscoped signature was an easy footgun, anyone
/// who later wired this up with an attacker-controllable session_id could
/// delete arbitrary rows. Requiring user_id in the signature keeps the
/// SQL pinned to "this user, this row" so that misuse fails fast.
#[tracing::instrument(skip_all)]
pub async fn delete_session_by_id(
    pool: &PgPool,
    session_id: UserSessionId,
    user_id: UserId,
) -> Result<bool> {
    let rows = sqlx::query!(
        "DELETE FROM user_sessions WHERE id = $1 AND user_id = $2",
        session_id as UserSessionId,
        user_id as UserId,
    )
    .execute(pool)
    .await?;

    Ok(rows.rows_affected() > 0)
}

/// Delete expired session records (inactive longer than the given threshold).
/// Returns the number of rows removed.
#[tracing::instrument(skip_all)]
pub async fn prune_expired_sessions(
    pool: &PgPool,
    stale_threshold: chrono::DateTime<chrono::Utc>,
) -> Result<u64> {
    // runtime-checked: binds a chrono DateTime<Utc> param; a bind param's type can't be overridden in the macro when sqlx time+chrono features are unified.
    let result = sqlx::query("DELETE FROM user_sessions WHERE last_active_at < $1")
        .bind(stale_threshold)
        .execute(pool)
        .await?;

    Ok(result.rows_affected())
}

/// Delete all sessions for a user except the current one. Returns count deleted.
#[tracing::instrument(skip_all)]
pub async fn delete_other_sessions(
    pool: &PgPool,
    current_session_id: UserSessionId,
    user_id: UserId,
) -> Result<Vec<UserSessionId>> {
    let ids = sqlx::query_scalar!(
        r#"DELETE FROM user_sessions WHERE user_id = $1 AND id != $2 RETURNING id AS "id: UserSessionId""#,
        user_id as UserId,
        current_session_id as UserSessionId,
    )
    .fetch_all(pool)
    .await?;

    Ok(ids)
}

/// Delete ALL sessions for a user. Returns the deleted session IDs (for cache eviction).
///
/// Also bumps `users.jwt_invalidated_at`, without this, a stolen SyncKit JWT
/// would survive a "log out everywhere" until its natural expiry. Both writes
/// run in a single transaction so a partial failure can't leave the JWTs alive
/// after the session rows are gone.
#[tracing::instrument(skip_all)]
pub async fn delete_all_sessions_for_user(
    pool: &PgPool,
    user_id: UserId,
) -> Result<Vec<UserSessionId>> {
    let mut tx = pool.begin().await?;

    let ids = sqlx::query_scalar!(
        r#"DELETE FROM user_sessions WHERE user_id = $1 RETURNING id AS "id: UserSessionId""#,
        user_id as UserId,
    )
    .fetch_all(&mut *tx)
    .await?;

    sqlx::query!(
        "UPDATE users SET jwt_invalidated_at = NOW() WHERE id = $1",
        user_id as UserId,
    )
    .execute(&mut *tx)
    .await?;

    tx.commit().await?;

    Ok(ids)
}