-- USASpending awards cache. Global public catalog (no organization_id, outside RLS): reads run off
-- the pool, writes come from the daily sync (cmd/awards-sync) as the migrator role.

-- name: UpsertAward :exec
INSERT INTO usaspending_awards (
    generated_internal_id, award_id, recipient_name, recipient_uei, recipient_id, award_amount,
    awarding_agency, awarding_sub_agency, pop_start_date, pop_end_date, naics_code, naics_description,
    description, is_galapagos, raw, source, synced_at
) VALUES (
    $1, $2, $3, $4, $5, $6,
    $7, $8, $9, $10, $11, $12,
    $13, $14, $15, $16, now()
)
ON CONFLICT (generated_internal_id) DO UPDATE SET
    award_id            = EXCLUDED.award_id,
    recipient_name      = EXCLUDED.recipient_name,
    recipient_uei       = EXCLUDED.recipient_uei,
    recipient_id        = EXCLUDED.recipient_id,
    award_amount        = EXCLUDED.award_amount,
    awarding_agency     = EXCLUDED.awarding_agency,
    awarding_sub_agency = EXCLUDED.awarding_sub_agency,
    pop_start_date      = EXCLUDED.pop_start_date,
    pop_end_date        = EXCLUDED.pop_end_date,
    naics_code          = EXCLUDED.naics_code,
    naics_description   = EXCLUDED.naics_description,
    description         = EXCLUDED.description,
    is_galapagos        = EXCLUDED.is_galapagos OR usaspending_awards.is_galapagos,
    raw                 = EXCLUDED.raw,
    source              = EXCLUDED.source,
    synced_at           = now();

-- name: ListAwardsByNaics :many
-- Incumbents and similar awards for a NAICS, largest first (excludes Galapagos own awards).
SELECT * FROM usaspending_awards
WHERE naics_code = ANY(sqlc.arg('naics')::text[])
  AND NOT is_galapagos
ORDER BY award_amount DESC NULLS LAST, generated_internal_id
LIMIT sqlc.arg('lim')::int;

-- name: ListGalapagosAwards :many
-- Galapagos own past performance, largest first.
SELECT * FROM usaspending_awards
WHERE is_galapagos
ORDER BY award_amount DESC NULLS LAST, generated_internal_id
LIMIT sqlc.arg('lim')::int;

-- name: AwardsSyncMeta :one
SELECT COUNT(*)::bigint AS total,
       COUNT(*) FILTER (WHERE is_galapagos)::bigint AS galapagos,
       MAX(synced_at) AS last_synced
FROM usaspending_awards;