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

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

use super::enums::{ReportStatus, ReportTargetType, ReportType};
use super::id_types::{ReportId, UserId};
use super::models::{DbAdminReportRow, DbReport, DbReportStats};
use crate::error::{AppError, 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(crate) 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,
            -- Custom-page source that rendered for this target, so moderators
            -- see what was published. Items inherit their parent project's CSS.
            CASE WHEN r.target_type = 'project' THEN p.custom_html
                 WHEN r.target_type = 'item' THEN ip.custom_html END AS target_custom_html,
            CASE WHEN r.target_type = 'project' THEN p.custom_css
                 WHEN r.target_type = 'item' THEN ip.custom_css END AS target_custom_css,
            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(crate) 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(crate) async fn resolve_report(
    pool: &PgPool,
    id: ReportId,
    status: ReportStatus,
    admin_notes: &str,
    resolved_by: crate::auth::AdminId,
) -> Result<()> {
    let 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.get())
    .execute(pool)
    .await?;

    // No matching row means the report id was stale (already resolved away, or
    // never existed). Surface it as NotFound so the admin sees a real failure
    // instead of a false "resolved", the UPDATE silently affecting zero rows
    // otherwise reports success.
    if result.rows_affected() == 0 {
        return Err(AppError::NotFound);
    }

    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(crate) 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);
    }

    // Per-(reporter, target) dedup: a reporter cannot stack a second still-open
    // report on the same target. Without this, one user could spend their whole
    // daily quota re-reporting one item and flood the moderation queue with
    // duplicates of a single complaint. Re-reporting is allowed once the prior
    // report is resolved (`resolved_at` set). Runs under the same per-reporter
    // advisory lock as the cap, so concurrent submits can't both slip past.
    let already_open: bool = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM reports \
         WHERE reporter_user_id = $1 AND target_type = $2 AND target_id = $3 \
           AND resolved_at IS NULL)",
    )
    .bind(reporter_id)
    .bind(target_type)
    .bind(target_id)
    .fetch_one(&mut *tx)
    .await?;
    if already_open {
        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))
}