//! Creator waitlist and wave management queries.

use sqlx::{PgPool, Postgres};

use super::enums::{SelectionMethod, WaitlistStatus};
use super::models::*;
use super::{CreatorWaveId, UserId, WaitlistEntryId};
use crate::error::Result;

/// Submit a new creator waitlist application.
#[tracing::instrument(skip_all)]
pub async fn create_waitlist_entry(pool: &PgPool, user_id: UserId, pitch: &str) -> Result<DbWaitlistEntry> {
    let entry = sqlx::query_as::<_, DbWaitlistEntry>(
        r#"
        INSERT INTO creator_waitlist (user_id, pitch)
        VALUES ($1, $2)
        RETURNING *
        "#,
    )
    .bind(user_id)
    .bind(pitch)
    .fetch_one(pool)
    .await?;

    Ok(entry)
}

/// Fetch a user's waitlist entry. Returns `None` if they haven't applied.
#[tracing::instrument(skip_all)]
pub async fn get_waitlist_entry_by_user(pool: &PgPool, user_id: UserId) -> Result<Option<DbWaitlistEntry>> {
    let entry = sqlx::query_as::<_, DbWaitlistEntry>(
        "SELECT * FROM creator_waitlist WHERE user_id = $1",
    )
    .bind(user_id)
    .fetch_optional(pool)
    .await?;

    Ok(entry)
}

/// Create a waitlist entry for an invited user (no pitch required).
#[tracing::instrument(skip_all)]
pub async fn create_invited_waitlist_entry(
    pool: &PgPool,
    user_id: UserId,
    invited_by_user_id: UserId,
) -> Result<DbWaitlistEntry> {
    let entry = sqlx::query_as::<_, DbWaitlistEntry>(
        r#"
        INSERT INTO creator_waitlist (user_id, pitch, selection_method, invited_by_user_id)
        VALUES ($1, NULL, 'invited', $2)
        RETURNING *
        "#,
    )
    .bind(user_id)
    .bind(invited_by_user_id)
    .fetch_one(pool)
    .await?;

    Ok(entry)
}

/// List waitlist entries for the admin dashboard, optionally filtered by status.
#[tracing::instrument(skip_all)]
pub async fn get_admin_waitlist(pool: &PgPool, status_filter: Option<&str>) -> Result<Vec<DbAdminWaitlistRow>> {
    let rows = if let Some(status) = status_filter {
        sqlx::query_as::<_, DbAdminWaitlistRow>(
            r#"
            SELECT cw.id, cw.user_id, cw.pitch, cw.status, cw.selection_method,
                   cw.admin_note, cw.created_at, cw.reviewed_at,
                   u.username, u.email, u.email_verified,
                   cw.invited_by_user_id,
                   inv.username AS invited_by_username
            FROM creator_waitlist cw
            JOIN users u ON u.id = cw.user_id
            LEFT JOIN users inv ON inv.id = cw.invited_by_user_id
            WHERE cw.status = $1
            ORDER BY cw.created_at DESC
            LIMIT 1000
            "#,
        )
        .bind(status)
        .fetch_all(pool)
        .await?
    } else {
        sqlx::query_as::<_, DbAdminWaitlistRow>(
            r#"
            SELECT cw.id, cw.user_id, cw.pitch, cw.status, cw.selection_method,
                   cw.admin_note, cw.created_at, cw.reviewed_at,
                   u.username, u.email, u.email_verified,
                   cw.invited_by_user_id,
                   inv.username AS invited_by_username
            FROM creator_waitlist cw
            JOIN users u ON u.id = cw.user_id
            LEFT JOIN users inv ON inv.id = cw.invited_by_user_id
            ORDER BY cw.created_at DESC
            LIMIT 1000
            "#,
        )
        .fetch_all(pool)
        .await?
    };

    Ok(rows)
}

/// Update a waitlist entry's status, selection method, and wave assignment.
#[tracing::instrument(skip_all)]
pub async fn update_waitlist_status(
    pool: &PgPool,
    id: WaitlistEntryId,
    status: WaitlistStatus,
    method: Option<SelectionMethod>,
    wave_id: Option<CreatorWaveId>,
) -> Result<DbWaitlistEntry> {
    let entry = sqlx::query_as::<_, DbWaitlistEntry>(
        r#"
        UPDATE creator_waitlist
        SET status = $2, selection_method = $3, wave_id = $4, reviewed_at = NOW()
        WHERE id = $1
        RETURNING *
        "#,
    )
    .bind(id)
    .bind(status)
    .bind(method)
    .bind(wave_id)
    .fetch_one(pool)
    .await?;

    Ok(entry)
}

/// Grant a user the ability to create projects (set `can_create_projects = true`).
#[tracing::instrument(skip_all)]
pub async fn grant_creator_access<'e, E>(executor: E, user_id: UserId) -> Result<()>
where
    E: sqlx::Executor<'e, Database = Postgres>,
{
    sqlx::query("UPDATE users SET can_create_projects = true WHERE id = $1")
        .bind(user_id)
        .execute(executor)
        .await?;

    Ok(())
}

/// Record a new creator wave with its selection counts.
#[tracing::instrument(skip_all)]
pub async fn create_wave<'e, E>(
    executor: E,
    wave_number: i32,
    hand_picked: i32,
    lottery: i32,
    eligible: i32,
    note: Option<&str>,
) -> Result<DbCreatorWave>
where
    E: sqlx::Executor<'e, Database = Postgres>,
{
    let wave = sqlx::query_as::<_, DbCreatorWave>(
        r#"
        INSERT INTO creator_waves (wave_number, hand_picked_count, lottery_count, total_eligible, note)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING *
        "#,
    )
    .bind(wave_number)
    .bind(hand_picked)
    .bind(lottery)
    .bind(eligible)
    .bind(note)
    .fetch_one(executor)
    .await?;

    Ok(wave)
}

/// List all creator waves, newest first.
#[tracing::instrument(skip_all)]
pub async fn get_all_waves(pool: &PgPool) -> Result<Vec<DbCreatorWave>> {
    let waves = sqlx::query_as::<_, DbCreatorWave>(
        "SELECT * FROM creator_waves ORDER BY wave_number DESC LIMIT 500",
    )
    .fetch_all(pool)
    .await?;

    Ok(waves)
}

/// Get the next sequential wave number (max + 1).
#[tracing::instrument(skip_all)]
pub async fn get_next_wave_number<'e, E>(executor: E) -> Result<i32>
where
    E: sqlx::Executor<'e, Database = Postgres>,
{
    let next: (i32,) = sqlx::query_as(
        "SELECT COALESCE(MAX(wave_number), 0) + 1 FROM creator_waves",
    )
    .fetch_one(executor)
    .await?;

    Ok(next.0)
}

/// Count pending waitlist entries with verified emails (eligible for lottery).
#[tracing::instrument(skip_all)]
pub async fn get_lottery_eligible_count<'e, E>(executor: E) -> Result<i64>
where
    E: sqlx::Executor<'e, Database = Postgres>,
{
    let count: (i64,) = sqlx::query_as(
        r#"
        SELECT COUNT(*) FROM creator_waitlist cw
        JOIN users u ON u.id = cw.user_id
        WHERE cw.status = 'pending' AND u.email_verified = true
        "#,
    )
    .fetch_one(executor)
    .await?;

    Ok(count.0)
}

/// Run lottery: randomly select `count` eligible entries, mark them approved with wave_id.
/// Returns the selected entries.
#[tracing::instrument(skip_all)]
pub async fn run_lottery<'e, E>(executor: E, wave_id: CreatorWaveId, count: i32) -> Result<Vec<DbWaitlistEntry>>
where
    E: sqlx::Executor<'e, Database = Postgres>,
{
    let entries = sqlx::query_as::<_, DbWaitlistEntry>(
        r#"
        UPDATE creator_waitlist
        SET status = 'approved', selection_method = 'lottery',
            wave_id = $1, reviewed_at = NOW()
        WHERE id IN (
            SELECT cw.id FROM creator_waitlist cw
            JOIN users u ON u.id = cw.user_id
            WHERE cw.status = 'pending' AND u.email_verified = true
            ORDER BY random() LIMIT $2
        )
        RETURNING *
        "#,
    )
    .bind(wave_id)
    .bind(count)
    .fetch_all(executor)
    .await?;

    Ok(entries)
}

/// Get aggregate waitlist counts grouped by status.
#[tracing::instrument(skip_all)]
pub async fn get_waitlist_stats(pool: &PgPool) -> Result<DbWaitlistStats> {
    let stats: DbWaitlistStats = sqlx::query_as(
        r#"
        SELECT
            COUNT(*) FILTER (WHERE status = 'pending') as pending,
            COUNT(*) FILTER (WHERE status = 'approved') as approved,
            COUNT(*) FILTER (WHERE status = 'spam') as spam
        FROM creator_waitlist
        "#,
    )
    .fetch_one(pool)
    .await?;

    Ok(stats)
}

/// Count users who have been granted creator access.
#[tracing::instrument(skip_all)]
pub async fn count_active_creators(pool: &PgPool) -> Result<i64> {
    let count: (i64,) = sqlx::query_as(
        "SELECT COUNT(*) FROM users WHERE can_create_projects = true",
    )
    .fetch_one(pool)
    .await?;

    Ok(count.0)
}

/// Count waitlist entries still in "pending" status.
#[tracing::instrument(skip_all)]
pub async fn count_waitlist_pending(pool: &PgPool) -> Result<i64> {
    let count: (i64,) = sqlx::query_as(
        "SELECT COUNT(*) FROM creator_waitlist WHERE status = 'pending'",
    )
    .fetch_one(pool)
    .await?;

    Ok(count.0)
}

/// Assign wave_id to hand-picked entries that don't have one yet
#[tracing::instrument(skip_all)]
pub async fn assign_wave_to_handpicks<'e, E>(executor: E, wave_id: CreatorWaveId) -> Result<i64>
where
    E: sqlx::Executor<'e, Database = Postgres>,
{
    let result = sqlx::query(
        r#"
        UPDATE creator_waitlist
        SET wave_id = $1
        WHERE status = 'approved' AND selection_method = 'hand_picked' AND wave_id IS NULL
        "#,
    )
    .bind(wave_id)
    .execute(executor)
    .await?;

    Ok(result.rows_affected() as i64)
}

/// Grant creator access to multiple users in a single query (batch).
#[tracing::instrument(skip_all)]
pub async fn grant_creator_access_batch<'e, E>(executor: E, user_ids: &[UserId]) -> Result<()>
where
    E: sqlx::Executor<'e, Database = Postgres>,
{
    sqlx::query("UPDATE users SET can_create_projects = true WHERE id = ANY($1)")
        .bind(user_ids)
        .execute(executor)
        .await?;

    Ok(())
}

/// Count hand-picked entries not yet assigned to a wave
#[tracing::instrument(skip_all)]
pub async fn count_unassigned_handpicks<'e, E>(executor: E) -> Result<i64>
where
    E: sqlx::Executor<'e, Database = Postgres>,
{
    let count: (i64,) = sqlx::query_as(
        "SELECT COUNT(*) FROM creator_waitlist WHERE status = 'approved' AND selection_method = 'hand_picked' AND wave_id IS NULL",
    )
    .fetch_one(executor)
    .await?;

    Ok(count.0)
}