//! An account's arc after it exists: deactivate, terminate, delete, and the
//! sandbox accounts that expire on their own.

use sqlx::PgPool;

use crate::db::UserId;
use crate::db::models::DbUser;
use crate::db::validated_types::{Email, Username};
use crate::error::Result;

/// Self-deactivate an account (enter limbo state).
///
/// Bumps `jwt_invalidated_at` so any outstanding SyncKit JWTs minted from
/// this account stop authenticating immediately.
#[tracing::instrument(skip_all)]
pub async fn deactivate_user(pool: &PgPool, id: UserId) -> Result<()> {
    sqlx::query(
        "UPDATE users SET deactivated_at = NOW(), jwt_invalidated_at = NOW(), updated_at = NOW() WHERE id = $1",
    )
    .bind(id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Reactivate a self-deactivated account.
#[tracing::instrument(skip_all)]
pub async fn reactivate_user(pool: &PgPool, id: UserId) -> Result<()> {
    sqlx::query("UPDATE users SET deactivated_at = NULL, updated_at = NOW() WHERE id = $1")
        .bind(id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Admin: permanently terminate an account (enforcement ladder step 4).
/// The user has 30 days to export data. After that, the scheduler deletes the account.
/// The account must already be suspended.
#[tracing::instrument(skip_all)]
pub async fn terminate_user(pool: &PgPool, id: UserId) -> Result<()> {
    sqlx::query(
        "UPDATE users SET terminated_at = NOW(), jwt_invalidated_at = NOW(), updated_at = NOW() WHERE id = $1",
    )
    .bind(id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Get user IDs of terminated accounts whose 30-day export window has expired.
#[tracing::instrument(skip_all)]
pub async fn get_expired_terminated_ids(pool: &PgPool) -> Result<Vec<UserId>> {
    let ids: Vec<UserId> = sqlx::query_scalar(
        r"
        SELECT id FROM users
        WHERE terminated_at IS NOT NULL
          AND terminated_at < NOW() - INTERVAL '30 days'
        ORDER BY terminated_at
        LIMIT 1000
        ",
    )
    .fetch_all(pool)
    .await?;

    Ok(ids)
}

/// Permanently delete a user by ID.
///
/// `pub(crate)` and not for direct handler use: go through
/// [`crate::AppState::delete_user_account`], which also purges the in-memory
/// caches keyed to the user (domain_cache). Deleting here alone would leave a
/// stale, never-revalidated cache entry.
#[tracing::instrument(skip_all)]
pub(crate) async fn delete_user(pool: &PgPool, id: UserId) -> Result<()> {
    sqlx::query("DELETE FROM users WHERE id = $1")
        .bind(id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Check whether this creator has any completed sales (transactions where they were the seller).
#[tracing::instrument(skip_all)]
pub async fn has_completed_sales(pool: &PgPool, id: UserId) -> Result<bool> {
    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM transactions WHERE seller_id = $1 AND status = 'completed'",
    )
    .bind(id)
    .fetch_one(pool)
    .await?;

    Ok(count > 0)
}

/// Schedule content removal 90 days from now. The user row is hidden from public
/// views but items remain accessible to buyers who previously purchased them.
/// After 90 days the scheduler deletes S3 objects and the user row.
#[tracing::instrument(skip_all)]
pub async fn schedule_content_removal(pool: &PgPool, id: UserId) -> Result<()> {
    sqlx::query(
        r"
        UPDATE users
        SET content_removal_at = NOW() + INTERVAL '90 days',
            deactivated_at = NOW(),
            updated_at = NOW()
        WHERE id = $1
        ",
    )
    .bind(id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Get user IDs whose 90-day content removal grace period has expired.
#[tracing::instrument(skip_all)]
pub async fn get_expired_content_removal_ids(pool: &PgPool) -> Result<Vec<UserId>> {
    let ids: Vec<UserId> = sqlx::query_scalar(
        r"
        SELECT id FROM users
        WHERE content_removal_at IS NOT NULL
          AND content_removal_at < NOW()
        ORDER BY content_removal_at
        LIMIT 1000
        ",
    )
    .fetch_all(pool)
    .await?;

    Ok(ids)
}

/// Create an ephemeral sandbox user. Returns the created row.
///
/// The user gets `can_create_projects = true`, `email_verified = true`,
/// a SmallFiles creator tier, and a tight storage cap. The row is
/// automatically cleaned up by the scheduler after `sandbox_expires_at`.
#[tracing::instrument(skip_all)]
pub async fn create_sandbox_user(
    pool: &PgPool,
    username: &Username,
    email: &Email,
    password_hash: &str,
    expiry_secs: i64,
) -> Result<DbUser> {
    let user = sqlx::query_as::<_, DbUser>(
        r"
        INSERT INTO users (
            username, email, password_hash,
            is_sandbox, sandbox_expires_at,
            can_create_projects, email_verified,
            creator_tier
        )
        VALUES (
            $1, $2, $3,
            TRUE, NOW() + make_interval(secs => $4::float8),
            TRUE, TRUE,
            'small_files'
        )
        RETURNING *
        ",
    )
    .bind(username)
    .bind(email)
    .bind(password_hash)
    .bind(expiry_secs as f64)
    .fetch_one(pool)
    .await?;

    Ok(user)
}

/// Return IDs of sandbox users whose expiry has passed.
#[tracing::instrument(skip_all)]
pub async fn get_expired_sandbox_ids(pool: &PgPool) -> Result<Vec<UserId>> {
    // Per-tick LIMIT bounds the supervisor's input list (the scheduler re-ticks
    // and the WHERE re-excludes already-deleted rows, so the remainder is picked
    // up next tick). Run #12 INFO, keeps a pathological mass-expiry from loading
    // an unbounded id vec even though concurrency is already capped at 4.
    let ids = sqlx::query_scalar::<_, UserId>(
        "SELECT id FROM users WHERE is_sandbox = TRUE AND sandbox_expires_at < NOW() \
         ORDER BY sandbox_expires_at LIMIT 1000",
    )
    .fetch_all(pool)
    .await?;

    Ok(ids)
}

/// Count active (non-expired) sandbox accounts created from a given IP.
/// Used to enforce the per-IP concurrent sandbox cap.
#[tracing::instrument(skip_all)]
pub async fn count_active_sandboxes_by_ip(pool: &PgPool, ip: &str) -> Result<i64> {
    let count: i64 = sqlx::query_scalar(
        r"
        SELECT COUNT(*) FROM users u
        JOIN user_sessions us ON us.user_id = u.id
        WHERE u.is_sandbox = TRUE
          AND u.sandbox_expires_at > NOW()
          AND us.ip_address = $1
        ",
    )
    .bind(ip)
    .fetch_one(pool)
    .await?;

    Ok(count)
}

/// Set the creator_paused_at timestamp (voluntary pause).
#[tracing::instrument(skip_all)]
pub async fn pause_creator(pool: &PgPool, user_id: UserId) -> Result<()> {
    sqlx::query("UPDATE users SET creator_paused_at = NOW(), updated_at = NOW() WHERE id = $1")
        .bind(user_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Clear the creator_paused_at timestamp (resume from voluntary pause).
#[tracing::instrument(skip_all)]
pub async fn unpause_creator(pool: &PgPool, user_id: UserId) -> Result<()> {
    sqlx::query("UPDATE users SET creator_paused_at = NULL, updated_at = NOW() WHERE id = $1")
        .bind(user_id)
        .execute(pool)
        .await?;

    Ok(())
}