//! 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;

/// Create a new report.
#[tracing::instrument(skip_all)]
pub async fn create_report(
    pool: &PgPool,
    reporter_id: UserId,
    target_type: ReportTargetType,
    target_id: Uuid,
    report_type: ReportType,
    reason: &str,
) -> Result<DbReport> {
    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(pool)
    .await?;

    Ok(report)
}

/// 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(())
}

/// Count reports from a specific user in the last 24 hours (rate limiting).
#[tracing::instrument(skip_all)]
pub async fn count_recent_reports_by_user(pool: &PgPool, user_id: UserId) -> Result<i64> {
    let row: (i64,) = sqlx::query_as(
        "SELECT COUNT(*) FROM reports WHERE reporter_user_id = $1 AND created_at > NOW() - INTERVAL '24 hours'",
    )
    .bind(user_id)
    .fetch_one(pool)
    .await?;

    Ok(row.0)
}