huntapi/Controllers/HuntsController.cs

using HuntingAppApi.Models;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Npgsql;

namespace HuntingAppApi.Controllers;

[ApiController]
[Authorize]
[Route("api/hunts")]
public sealed class HuntsController(NpgsqlDataSource dataSource) : ControllerBase
{
    [HttpGet("area/{huntAreaId:guid}")]
    public async Task<ActionResult<List<HuntDto>>> GetByArea(Guid huntAreaId, CancellationToken ct)
    {
        var result = new List<HuntDto>();
        await using var conn = await dataSource.OpenConnectionAsync(ct);
        await using var cmd = new NpgsqlCommand("""
            SELECT id, hunt_area_id, title, description, starts_at, ends_at, status
            FROM hunt
            WHERE hunt_area_id = @hunt_area_id
            ORDER BY starts_at DESC NULLS LAST, created_at DESC
        """, conn);
        cmd.Parameters.AddWithValue("hunt_area_id", huntAreaId);
        await using var r = await cmd.ExecuteReaderAsync(ct);
        while (await r.ReadAsync(ct))
        {
            result.Add(new HuntDto
            {
                Id = r.GetGuid(0), HuntAreaId = r.GetGuid(1), Title = r.GetString(2),
                Description = r.IsDBNull(3) ? null : r.GetString(3),
                StartsAt = r.IsDBNull(4) ? null : r.GetFieldValue<DateTimeOffset>(4),
                EndsAt = r.IsDBNull(5) ? null : r.GetFieldValue<DateTimeOffset>(5),
                Status = r.GetString(6)
            });
        }
        return Ok(result);
    }

    [HttpPost]
    public async Task<ActionResult<ApiId>> Create([FromBody] CreateHuntRequest request, CancellationToken ct)
    {
        var id = Guid.NewGuid();
        await using var conn = await dataSource.OpenConnectionAsync(ct);
        await using var cmd = new NpgsqlCommand("""
            INSERT INTO hunt (id, hunt_area_id, title, description, starts_at, ends_at)
            VALUES (@id, @hunt_area_id, @title, @description, @starts_at, @ends_at)
        """, conn);
        cmd.Parameters.AddWithValue("id", id);
        cmd.Parameters.AddWithValue("hunt_area_id", request.HuntAreaId);
        cmd.Parameters.AddWithValue("title", request.Title);
        cmd.Parameters.AddWithValue("description", (object?)request.Description ?? DBNull.Value);
        cmd.Parameters.AddWithValue("starts_at", (object?)request.StartsAt ?? DBNull.Value);
        cmd.Parameters.AddWithValue("ends_at", (object?)request.EndsAt ?? DBNull.Value);
        await cmd.ExecuteNonQueryAsync(ct);
        return Ok(new ApiId(id));
    }

    [HttpPatch("{huntId:guid}/status/{status}")]
    public async Task<IActionResult> SetStatus(Guid huntId, string status, CancellationToken ct)
    {
        await using var conn = await dataSource.OpenConnectionAsync(ct);
        await using var cmd = new NpgsqlCommand("UPDATE hunt SET status = @status WHERE id = @id", conn);
        cmd.Parameters.AddWithValue("id", huntId);
        cmd.Parameters.AddWithValue("status", status);
        return await cmd.ExecuteNonQueryAsync(ct) == 1 ? NoContent() : NotFound();
    }

    [HttpPost("{huntId:guid}/participants")]
    public async Task<ActionResult<ApiId>> AddParticipant(Guid huntId, [FromBody] JoinHuntRequest request, CancellationToken ct)
    {
        var id = Guid.NewGuid();
        await using var conn = await dataSource.OpenConnectionAsync(ct);
        await using var cmd = new NpgsqlCommand("""
            INSERT INTO hunt_participant (id, hunt_id, user_id, role, status)
            VALUES (@id, @hunt_id, @user_id, @role, 'accepted')
            ON CONFLICT (hunt_id, user_id) DO UPDATE SET role = EXCLUDED.role
            RETURNING id
        """, conn);
        cmd.Parameters.AddWithValue("id", id);
        cmd.Parameters.AddWithValue("hunt_id", huntId);
        cmd.Parameters.AddWithValue("user_id", request.UserId);
        cmd.Parameters.AddWithValue("role", request.Role);
        var returned = (Guid)(await cmd.ExecuteScalarAsync(ct))!;
        return Ok(new ApiId(returned));
    }

    [HttpPost("{huntId:guid}/assignments")]
    public async Task<ActionResult<ApiId>> Assign(Guid huntId, [FromBody] AssignParticipantRequest request, CancellationToken ct)
    {
        var id = Guid.NewGuid();
        await using var conn = await dataSource.OpenConnectionAsync(ct);
        await using var cmd = new NpgsqlCommand("""
            INSERT INTO hunt_assignment (id, hunt_id, participant_id, map_point_id, note)
            VALUES (@id, @hunt_id, @participant_id, @map_point_id, @note)
            ON CONFLICT (hunt_id, participant_id)
            DO UPDATE SET map_point_id = EXCLUDED.map_point_id, note = EXCLUDED.note, assigned_at = now()
            RETURNING id
        """, conn);
        cmd.Parameters.AddWithValue("id", id);
        cmd.Parameters.AddWithValue("hunt_id", huntId);
        cmd.Parameters.AddWithValue("participant_id", request.ParticipantId);
        cmd.Parameters.AddWithValue("map_point_id", request.MapPointId);
        cmd.Parameters.AddWithValue("note", (object?)request.Note ?? DBNull.Value);
        var returned = (Guid)(await cmd.ExecuteScalarAsync(ct))!;
        return Ok(new ApiId(returned));
    }

    [HttpGet("{huntId:guid}/participant-locations")]
    public async Task<ActionResult<List<ParticipantLocationDto>>> GetParticipantLocations(Guid huntId, CancellationToken ct)
    {
        var result = new List<ParticipantLocationDto>();
        await using var conn = await dataSource.OpenConnectionAsync(ct);
        await using var cmd = new NpgsqlCommand("""
            SELECT hp.id, u.id, u.display_name, u.color,
                   ST_Y(l.geom), ST_X(l.geom), l.accuracy_m, l.battery_percent, l.recorded_at
            FROM hunt_participant hp
            JOIN app_user u ON u.id = hp.user_id
            LEFT JOIN user_current_location l ON l.participant_id = hp.id
            WHERE hp.hunt_id = @hunt_id
            ORDER BY u.display_name
        """, conn);
        cmd.Parameters.AddWithValue("hunt_id", huntId);
        await using var r = await cmd.ExecuteReaderAsync(ct);
        while (await r.ReadAsync(ct))
        {
            result.Add(new ParticipantLocationDto
            {
                ParticipantId = r.GetGuid(0), UserId = r.GetGuid(1), DisplayName = r.GetString(2),
                Color = r.IsDBNull(3) ? null : r.GetString(3),
                Latitude = r.IsDBNull(4) ? null : r.GetDouble(4), Longitude = r.IsDBNull(5) ? null : r.GetDouble(5),
                AccuracyM = r.IsDBNull(6) ? null : r.GetDecimal(6),
                BatteryPercent = r.IsDBNull(7) ? null : r.GetInt32(7),
                RecordedAt = r.IsDBNull(8) ? null : r.GetFieldValue<DateTimeOffset>(8)
            });
        }
        return Ok(result);
    }
}