//! User account CRUD, profile updates, and lookup queries.
//!
//! The core reads and writes are here; everything with a life of its own is a
//! sibling.

mod admin_queries;
mod lifecycle;
mod moderation;
mod onboarding;
mod preferences;
mod stripe;

pub use admin_queries::*;
pub use lifecycle::*;
pub use moderation::*;
pub use onboarding::*;
pub use preferences::*;
pub use stripe::*;

use sqlx::PgPool;

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

/// Insert a new user and return the created row.
#[tracing::instrument(skip_all)]
pub async fn create_user(
    pool: &PgPool,
    username: &Username,
    email: &Email,
    password_hash: &str,
) -> Result<DbUser> {
    let user = sqlx::query_as::<_, DbUser>(
        r"
        INSERT INTO users (username, email, password_hash)
        VALUES ($1, $2, $3)
        RETURNING *
        ",
    )
    .bind(username)
    .bind(email)
    .bind(password_hash)
    .fetch_one(pool)
    .await?;

    Ok(user)
}

/// Insert a durable example-marketplace creator (see [`crate::seed`]).
///
/// Unlike [`create_sandbox_user`], this leaves `is_sandbox` at its `FALSE`
/// default so the account and its projects appear on every public surface
/// (discover/browse/search gate only on `is_sandbox = FALSE`). It grants
/// `can_create_projects`, marks the email verified, and pins the top
/// `creator_tier` so no capability gate blocks the item spread seeded in later
/// phases. Only ever called by the `--seed-examples` flow, which is itself
/// confined to testnot/localhost by [`crate::seed::run`]'s guards.
#[tracing::instrument(skip_all)]
pub async fn create_example_creator(
    pool: &PgPool,
    username: &Username,
    email: &Email,
    password_hash: &str,
) -> Result<DbUser> {
    let user = sqlx::query_as::<_, DbUser>(
        r"
        INSERT INTO users (
            username, email, password_hash,
            can_create_projects, email_verified, creator_tier
        )
        VALUES ($1, $2, $3, TRUE, TRUE, 'everything')
        RETURNING *
        ",
    )
    .bind(username)
    .bind(email)
    .bind(password_hash)
    .fetch_one(pool)
    .await?;

    Ok(user)
}

/// Fetch a user by primary key. Returns `None` if not found.
#[tracing::instrument(skip_all)]
pub async fn get_user_by_id(pool: &PgPool, id: UserId) -> Result<Option<DbUser>> {
    let user = sqlx::query_as::<_, DbUser>("SELECT * FROM users WHERE id = $1")
        .bind(id)
        .fetch_optional(pool)
        .await?;

    Ok(user)
}

/// Fetch multiple users by ID in a single query.
#[tracing::instrument(skip_all)]
pub async fn get_users_by_ids(pool: &PgPool, ids: &[UserId]) -> Result<Vec<DbUser>> {
    let users = sqlx::query_as::<_, DbUser>("SELECT * FROM users WHERE id = ANY($1)")
        .bind(ids)
        .fetch_all(pool)
        .await?;
    Ok(users)
}

/// Fetch a user by username. Returns `None` if not found.
#[tracing::instrument(skip_all)]
pub async fn get_user_by_username(pool: &PgPool, username: &Username) -> Result<Option<DbUser>> {
    let user = sqlx::query_as::<_, DbUser>("SELECT * FROM users WHERE username = $1")
        .bind(username)
        .fetch_optional(pool)
        .await?;

    Ok(user)
}

/// Fetch a user by email address. Returns `None` if not found.
#[tracing::instrument(skip_all)]
pub async fn get_user_by_email(pool: &PgPool, email: &Email) -> Result<Option<DbUser>> {
    let user = sqlx::query_as::<_, DbUser>("SELECT * FROM users WHERE email = $1")
        .bind(email)
        .fetch_optional(pool)
        .await?;

    Ok(user)
}

/// Look up a verified user by email (case-insensitive).
/// Returns the user ID if a verified account exists with that email.
#[tracing::instrument(skip_all)]
pub async fn get_verified_user_id_by_email(pool: &PgPool, email: &Email) -> Result<Option<UserId>> {
    let id = sqlx::query_scalar(
        "SELECT id FROM users WHERE LOWER(email) = LOWER($1) AND email_verified = true",
    )
    .bind(email)
    .fetch_optional(pool)
    .await?;

    Ok(id)
}

/// Update a user's display name and/or bio (COALESCE keeps existing values when `None`).
#[tracing::instrument(skip_all)]
pub async fn update_user_profile(
    pool: &PgPool,
    id: UserId,
    display_name: Option<&str>,
    bio: Option<&str>,
) -> Result<DbUser> {
    let user = sqlx::query_as::<_, DbUser>(
        r"
        UPDATE users
        SET display_name = COALESCE($2, display_name),
            bio = COALESCE($3, bio)
        WHERE id = $1
        RETURNING *
        ",
    )
    .bind(id)
    .bind(display_name)
    .bind(bio)
    .fetch_one(pool)
    .await?;

    Ok(user)
}

/// Store a user's custom profile-page source (the original, pre-sanitization),
/// stamp `custom_pages_updated_at`, and bump the cache generation. The source is
/// re-sanitized on render; see [`crate::custom_pages`].
#[tracing::instrument(skip_all, fields(user_id = %id))]
pub async fn update_user_custom_page<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    id: UserId,
    custom_html: &str,
    custom_css: &str,
) -> Result<DbUser> {
    let user = sqlx::query_as::<_, DbUser>(
        r"
        UPDATE users
        SET custom_html = $2,
            custom_css = $3,
            custom_pages_updated_at = now(),
            cache_generation = cache_generation + 1
        WHERE id = $1
        RETURNING *
        ",
    )
    .bind(id)
    .bind(custom_html)
    .bind(custom_css)
    .fetch_one(executor)
    .await?;
    Ok(user)
}

/// Clear a user's custom profile page back to the platform default.
pub async fn reset_user_custom_page(pool: &PgPool, id: UserId) -> Result<()> {
    sqlx::query(
        "UPDATE users SET custom_html = '', custom_css = '', \
         custom_pages_updated_at = NULL, cache_generation = cache_generation + 1 WHERE id = $1",
    )
    .bind(id)
    .execute(pool)
    .await?;
    Ok(())
}

/// Set or clear a user's creator theme for their public profile. `None` clears
/// to the platform default. The id is validated against the embedded registry
/// before this call.
#[tracing::instrument(skip_all)]
pub async fn update_user_theme(pool: &PgPool, id: UserId, theme_id: Option<&str>) -> Result<()> {
    sqlx::query("UPDATE users SET theme_id = $2, updated_at = NOW() WHERE id = $1")
        .bind(id)
        .bind(theme_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Set a user's SSH console theme. Takes a `makeover::ThemeSelection` string
/// (a bundled theme id, or `"system"`), validated before this call.
#[tracing::instrument(skip_all)]
pub async fn update_user_console_theme(pool: &PgPool, id: UserId, selection: &str) -> Result<()> {
    sqlx::query("UPDATE users SET console_theme = $2, updated_at = NOW() WHERE id = $1")
        .bind(id)
        .bind(selection)
        .execute(pool)
        .await?;

    Ok(())
}

/// Replace a user's password hash and invalidate outstanding JWTs.
#[tracing::instrument(skip_all)]
pub async fn update_user_password(pool: &PgPool, id: UserId, password_hash: &str) -> Result<()> {
    sqlx::query("UPDATE users SET password_hash = $2, jwt_invalidated_at = NOW() WHERE id = $1")
        .bind(id)
        .bind(password_hash)
        .execute(pool)
        .await?;

    Ok(())
}

/// Increment the user's feed key version, revoking their current personal-feed
/// URL. Returns the new version (folded into the next URL's HMAC).
#[tracing::instrument(skip_all)]
pub async fn bump_feed_key_version(pool: &PgPool, id: UserId) -> Result<i32> {
    let (version,): (i32,) = sqlx::query_as(
        "UPDATE users SET feed_key_version = feed_key_version + 1, updated_at = NOW() \
         WHERE id = $1 RETURNING feed_key_version",
    )
    .bind(id)
    .fetch_one(pool)
    .await?;

    Ok(version)
}

/// Mark a user's email as verified
#[tracing::instrument(skip_all)]
pub async fn verify_user_email(pool: &PgPool, user_id: UserId) -> Result<()> {
    sqlx::query(
        r"
        UPDATE users
        SET email_verified = true,
            email_verification_token = NULL,
            updated_at = NOW()
        WHERE id = $1
        ",
    )
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Fetch the current cache generation for a user (cheap, indexed lookup).
#[tracing::instrument(skip_all)]
pub async fn get_cache_generation(pool: &PgPool, user_id: UserId) -> Result<i64> {
    let generation =
        sqlx::query_scalar::<_, i64>("SELECT cache_generation FROM users WHERE id = $1")
            .bind(user_id)
            .fetch_one(pool)
            .await?;

    Ok(generation)
}

/// Atomically increment the user's cache generation counter.
/// Call after any write that changes user-visible dashboard data.
#[tracing::instrument(skip_all)]
pub async fn bump_cache_generation(pool: &PgPool, user_id: UserId) -> Result<()> {
    sqlx::query("UPDATE users SET cache_generation = cache_generation + 1 WHERE id = $1")
        .bind(user_id)
        .execute(pool)
        .await?;

    Ok(())
}