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

-- name: UpsertSamOpportunity :exec
INSERT INTO sam_opportunities (
    notice_id, title, solicitation_number, department, full_parent_path, sub_agency,
    naics_code, psc_code, set_aside_code, set_aside, notice_type, base_type,
    posted_date, response_deadline, active, pop_city, pop_state, pop_zip,
    ui_link, description_url, resource_links, poc, raw, source, synced_at
) VALUES (
    $1, $2, $3, $4, $5, $6,
    $7, $8, $9, $10, $11, $12,
    $13, $14, $15, $16, $17, $18,
    $19, $20, $21, $22, $23, $24, now()
)
ON CONFLICT (notice_id) DO UPDATE SET
    title               = EXCLUDED.title,
    solicitation_number = EXCLUDED.solicitation_number,
    department          = EXCLUDED.department,
    full_parent_path    = EXCLUDED.full_parent_path,
    sub_agency          = EXCLUDED.sub_agency,
    naics_code          = EXCLUDED.naics_code,
    psc_code            = EXCLUDED.psc_code,
    set_aside_code      = EXCLUDED.set_aside_code,
    set_aside           = EXCLUDED.set_aside,
    notice_type         = EXCLUDED.notice_type,
    base_type           = EXCLUDED.base_type,
    posted_date         = EXCLUDED.posted_date,
    response_deadline   = EXCLUDED.response_deadline,
    active              = EXCLUDED.active,
    pop_city            = EXCLUDED.pop_city,
    pop_state           = EXCLUDED.pop_state,
    pop_zip             = EXCLUDED.pop_zip,
    ui_link             = EXCLUDED.ui_link,
    description_url     = EXCLUDED.description_url,
    resource_links      = EXCLUDED.resource_links,
    poc                 = EXCLUDED.poc,
    raw                 = EXCLUDED.raw,
    source              = EXCLUDED.source,
    synced_at           = now();

-- name: GetSamOpportunity :one
SELECT * FROM sam_opportunities WHERE notice_id = $1;

-- name: SamSyncMeta :one
SELECT COUNT(*)::bigint AS total, MAX(synced_at) AS last_synced FROM sam_opportunities;

-- name: ListSamOpportunities :many
-- All filters are optional (NULL array / NULL scalar = "no filter"). active_only defaults true at the
-- handler. Soonest-closing first, then most-recently-posted.
-- Text equality filters are case-insensitive (upper() both sides; the service upper-cases the params)
-- so casing drift from SAM's path text can't silently zero out results [review COR-3]. The title search
-- is a literal substring (position()), NOT a LIKE pattern, so q's %/_/\ are matched literally and a
-- trailing backslash can't 500 the request [review COR-1]. Past-deadline / null-deadline notices sort
-- to the bottom so the feed leads with what's still open [review COR-2].
SELECT * FROM sam_opportunities
WHERE (sqlc.narg('naics')::text[]        IS NULL OR naics_code            = ANY(sqlc.narg('naics')::text[]))
  AND (sqlc.narg('set_asides')::text[]   IS NULL OR upper(set_aside_code) = ANY(sqlc.narg('set_asides')::text[]))
  AND (sqlc.narg('states')::text[]       IS NULL OR upper(pop_state)      = ANY(sqlc.narg('states')::text[]))
  AND (sqlc.narg('departments')::text[]  IS NULL OR upper(department)     = ANY(sqlc.narg('departments')::text[]))
  AND (sqlc.narg('sub_agencies')::text[] IS NULL OR upper(sub_agency)     = ANY(sqlc.narg('sub_agencies')::text[]))
  AND (sqlc.narg('q')::text              IS NULL OR position(lower(sqlc.narg('q')::text) in lower(title)) > 0)
  AND (NOT sqlc.arg('active_only')::boolean OR active = true)
ORDER BY (response_deadline IS NULL OR response_deadline < now()), response_deadline ASC, posted_date DESC NULLS LAST, notice_id
LIMIT sqlc.arg('lim')::int;