//! Report queries: create, list, resolve, and count user-submitted reports.

use sqlx::PgPool;
use uuid::Uuid;

use super::id_types::*;
use super::models::*;
use super::enums::*;
use crate::error::Result;

/// Get reports for the admin queue, joined with reporter, target, and owner info.
///
/// Uses two sub-queries (one for projects, one for items) to resolve target details,
/// then UNIONs and filters. This avoids N+1 queries.
#[tracing::instrument(skip_all)]
pub async fn get_admin_reports(
    pool: &PgPool,
    status_filter: Option<&str>,
    limit: i64,
    offset: i64,
) -> Result<Vec<DbAdminReportRow>> {
    let rows = sqlx::query_as::<_, DbAdminReportRow>(
        r#"
        SELECT
            r.id,
            u.username AS reporter_username,
            r.target_type,
            COALESCE(
                CASE WHEN r.target_type = 'project' THEN p.title END,
                CASE WHEN r.target_type = 'item' THEN i.title END,
                '(deleted)'
            ) AS target_title,
            COALESCE(
                CASE WHEN r.target_type = 'project' THEN p.slug::TEXT END,
                CASE WHEN r.target_type = 'item' THEN r.target_id::TEXT END,
                ''
            ) AS target_slug_or_id,
            COALESCE(
                CASE WHEN r.target_type = 'project' THEN pu.username END,
                CASE WHEN r.target_type = 'item' THEN iu.username END,
                '(unknown)'
            ) AS target_owner,
            r.report_type,
            r.reason,
            r.status,
            r.admin_notes,
            r.created_at,
            r.resolved_at
        FROM reports r
        JOIN users u ON u.id = r.reporter_user_id
        LEFT JOIN projects p ON r.target_type = 'project' AND p.id = r.target_id
        LEFT JOIN users pu ON p.user_id = pu.id
        LEFT JOIN items i ON r.target_type = 'item' AND i.id = r.target_id
        LEFT JOIN projects ip ON i.project_id = ip.id
        LEFT JOIN users iu ON ip.user_id = iu.id
        WHERE ($1::TEXT IS NULL OR r.status = $1)
        ORDER BY r.created_at DESC
        LIMIT $2 OFFSET $3
        "#,
    )
    .bind(status_filter)
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Get aggregate report stats.
#[tracing::instrument(skip_all)]
pub async fn get_report_stats(pool: &PgPool) -> Result<DbReportStats> {
    let stats = sqlx::query_as::<_, DbReportStats>(
        r#"
        SELECT
            COUNT(*) FILTER (WHERE status = 'open') AS open,
            COUNT(*) FILTER (WHERE status = 'resolved') AS resolved,
            COUNT(*) FILTER (WHERE status = 'dismissed') AS dismissed
        FROM reports
        "#,
    )
    .fetch_one(pool)
    .await?;

    Ok(stats)
}

/// Resolve or dismiss a report.
#[tracing::instrument(skip_all)]
pub async fn resolve_report(
    pool: &PgPool,
    id: ReportId,
    status: ReportStatus,
    admin_notes: &str,
    resolved_by: UserId,
) -> Result<()> {
    sqlx::query(
        r#"
        UPDATE reports
        SET status = $2, admin_notes = $3, resolved_by = $4, resolved_at = NOW()
        WHERE id = $1
        "#,
    )
    .bind(id)
    .bind(status)
    .bind(admin_notes)
    .bind(resolved_by)
    .execute(pool)
    .await?;

    Ok(())
}

/// Create a report only if the reporter is under the daily cap — atomically.
///
/// Returns `Ok(None)` when the reporter already has `>= max_per_day` reports in
/// the trailing 24h. The count and the insert run in one transaction under a
/// per-reporter `pg_advisory_xact_lock`, so a single user's concurrent submits
/// can't both observe `count = max-1` and both insert (the count-then-insert
/// TOCTOU the previous two-call sequence had). The lock is keyed on the
/// reporter id, so contention is scoped to one user's own concurrent requests —
/// it never blocks unrelated traffic — and it auto-releases at commit/rollback.
#[tracing::instrument(skip_all)]
pub async fn create_report_within_daily_limit(
    pool: &PgPool,
    reporter_id: UserId,
    target_type: ReportTargetType,
    target_id: Uuid,
    report_type: ReportType,
    reason: &str,
    max_per_day: i64,
) -> Result<Option<DbReport>> {
    let mut tx = pool.begin().await?;

    sqlx::query("SELECT pg_advisory_xact_lock(hashtextextended($1::text, 0))")
        .bind(reporter_id)
        .execute(&mut *tx)
        .await?;

    let recent: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM reports WHERE reporter_user_id = $1 AND created_at > NOW() - INTERVAL '24 hours'",
    )
    .bind(reporter_id)
    .fetch_one(&mut *tx)
    .await?;

    if recent >= max_per_day {
        // Drop rolls the (empty) tx back and releases the advisory lock.
        return Ok(None);
    }

    let report = sqlx::query_as::<_, DbReport>(
        r#"
        INSERT INTO reports (reporter_user_id, target_type, target_id, report_type, reason)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING id, reporter_user_id, target_type, target_id, report_type, reason,
                  status, admin_notes, resolved_by, created_at, resolved_at
        "#,
    )
    .bind(reporter_id)
    .bind(target_type)
    .bind(target_id)
    .bind(report_type)
    .bind(reason)
    .fetch_one(&mut *tx)
    .await?;

    tx.commit().await?;
    Ok(Some(report))
}