AdPlatform-Server/Gateway/Controllers/DemographicsController.cs

using Gateway.Data;
using Gateway.Security;
using Gateway.Services;
using Microsoft.AspNetCore.Mvc;
using System.Text.Json;

namespace Gateway.Controllers;

/// <summary>
/// Census demographic data endpoints for the campaign wizard.
///
/// GET  /api/demographics/{zcta}   — fetch census data from DB, forward to
///                                   Intelligence container for derived
///                                   recommendations. Falls back to raw census
///                                   data if Intelligence is unreachable.
/// POST /api/demographics/list     — multiple ZCTAs (raw data only)
/// POST /api/demographics/search   — find ZCTAs by criteria (raw data only)
/// </summary>
[ApiController]
[Route("api/demographics")]
public sealed class DemographicsController : ControllerBase
{
    private readonly SqlService _sql;
    private readonly AuthorizationGuard _guard;
    private readonly IntelligenceApiClient _intelligence;
    private readonly ILogger<DemographicsController> _log;

    public DemographicsController(
        SqlService sql,
        AuthorizationGuard guard,
        IntelligenceApiClient intelligence,
        ILogger<DemographicsController> log)
    {
        _sql          = sql;
        _guard        = guard;
        _intelligence = intelligence;
        _log          = log;
    }

    /// <summary>
    /// Fetch raw census data for a ZCTA, then forward to Intelligence container
    /// for derived audience recommendations (age chips, income tiers, insights).
    /// Falls back to raw census data only if Intelligence is unreachable.
    /// </summary>
    [HttpGet("{zcta}")]
    public async Task<IActionResult> Get(string zcta, CancellationToken ct)
    {
        var (ok, err) = _guard.RequireAuth();
        if (!ok) return Unauthorized(new { ok = false, error = err });

        if (string.IsNullOrWhiteSpace(zcta) || zcta.Length != 5 || !zcta.All(char.IsDigit))
            return BadRequest(new { ok = false, error = "Valid 5-digit ZIP code is required" });

        try
        {
            // 1. Fetch raw census data from DB
            var censusJson = await _sql.ExecProcAsync(
                SqlNames.Procs.Demographics, "get",
                JsonSerializer.Serialize(new { zcta }),
                ct: ct);

            if (string.IsNullOrWhiteSpace(censusJson))
                return NotFound(new { ok = false, error = "ZIP code not found" });

            using var doc  = JsonDocument.Parse(censusJson);
            var censusRoot = doc.RootElement;

            if (censusRoot.TryGetProperty("ok", out var okProp) && !okProp.GetBoolean())
                return NotFound(new { ok = false, error = "ZIP code not found" });

            // 2. Forward to Intelligence container for market analysis derivation
            var analysis = await _intelligence.GetDemographicAnalysisAsync(zcta, censusRoot, ct);

            if (analysis != null)
            {
                _log.LogInformation("[Demographics] Analysis by Intelligence container | ZCTA={Zcta}", zcta);
                return Content(analysis, "application/json");
            }

            // 3. Fallback: return raw census data
            _log.LogInformation("[Demographics] Intelligence unavailable — raw census | ZCTA={Zcta}", zcta);
            return Content(censusJson, "application/json");
        }
        catch (Exception ex)
        {
            _log.LogError(ex, "Demographics get error for ZCTA {Zcta}", zcta);
            return StatusCode(500, new { ok = false, error = "Demographics service error" });
        }
    }

    /// <summary>Get demographics for multiple ZCTAs (raw census data).</summary>
    [HttpPost("list")]
    public async Task<IActionResult> List([FromBody] ZctaListRequest? request, CancellationToken ct)
    {
        var (ok, err) = _guard.RequireAuth();
        if (!ok) return Unauthorized(new { ok = false, error = err });

        if (request?.Zctas == null || request.Zctas.Length == 0)
            return BadRequest(new { ok = false, error = "zctas array is required" });

        try
        {
            var rqst = JsonSerializer.Serialize(new
            {
                zctas    = request.Zctas,
                page     = request.Page ?? 1,
                pageSize = request.PageSize ?? 50
            });

            var resp = await _sql.ExecProcAsync(SqlNames.Procs.Demographics, "list", rqst, ct: ct);
            if (string.IsNullOrWhiteSpace(resp))
                return StatusCode(500, new { ok = false, error = "Demographics service unavailable" });

            return Content(resp, "application/json");
        }
        catch (Exception ex)
        {
            _log.LogError(ex, "Demographics list error");
            return StatusCode(500, new { ok = false, error = "Demographics service error" });
        }
    }

    /// <summary>Search ZCTAs by demographic criteria (raw census data).</summary>
    [HttpPost("search")]
    public async Task<IActionResult> Search([FromBody] DemographicSearchRequest? request, CancellationToken ct)
    {
        var (ok, err) = _guard.RequireAuth();
        if (!ok) return Unauthorized(new { ok = false, error = err });

        try
        {
            var rqst = JsonSerializer.Serialize(new
            {
                zctaPrefix      = request?.ZctaPrefix,
                minIncome       = request?.MinIncome,
                maxIncome       = request?.MaxIncome,
                minPopulation   = request?.MinPopulation,
                minBachelorPct  = request?.MinBachelorPct,
                minAge25to34Pct = request?.MinAge25to34Pct,
                minHomeValue    = request?.MinHomeValue,
                page            = request?.Page ?? 1,
                pageSize        = request?.PageSize ?? 50
            });

            var resp = await _sql.ExecProcAsync(SqlNames.Procs.Demographics, "search", rqst, ct: ct);
            if (string.IsNullOrWhiteSpace(resp))
                return StatusCode(500, new { ok = false, error = "Demographics service unavailable" });

            return Content(resp, "application/json");
        }
        catch (Exception ex)
        {
            _log.LogError(ex, "Demographics search error");
            return StatusCode(500, new { ok = false, error = "Demographics service error" });
        }
    }
}

// ── DTOs ──

public sealed class ZctaListRequest
{
    public string[]? Zctas { get; set; }
    public int? Page { get; set; }
    public int? PageSize { get; set; }
}

public sealed class DemographicSearchRequest
{
    public string? ZctaPrefix { get; set; }
    public int? MinIncome { get; set; }
    public int? MaxIncome { get; set; }
    public int? MinPopulation { get; set; }
    public decimal? MinBachelorPct { get; set; }
    public decimal? MinAge25to34Pct { get; set; }
    public int? MinHomeValue { get; set; }
    public int? Page { get; set; }
    public int? PageSize { get; set; }
}