-- BLS OEWS wage baseline cache. Global public catalog (no organization_id, outside RLS): reads run
-- off the pool, writes come from the weekly rates sync (cmd/rates-sync) as the migrator role.

-- name: UpsertBlsWage :exec
INSERT INTO bls_wages (
    series_id, occupation_code, occupation_title, role, area, area_name, metric, period_year, value, raw, synced_at
) VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, now()
)
ON CONFLICT (series_id) DO UPDATE SET
    occupation_code  = EXCLUDED.occupation_code,
    occupation_title = EXCLUDED.occupation_title,
    role             = EXCLUDED.role,
    area             = EXCLUDED.area,
    area_name        = EXCLUDED.area_name,
    metric           = EXCLUDED.metric,
    period_year      = EXCLUDED.period_year,
    value            = EXCLUDED.value,
    raw              = EXCLUDED.raw,
    synced_at        = now();

-- name: UpsertBlsWageByOcc :exec
-- National OEWS wage expansion upsert, keyed on the natural key (occupation_code, area, metric), the
-- UNIQUE added in migration 0035. Used by cmd/oews-national-sync for the area='US' national rows: a
-- second run updates the value in place and inserts nothing new. Idempotent.
INSERT INTO bls_wages (
    series_id, occupation_code, occupation_title, role, area, area_name, metric, period_year, value, raw, synced_at
) VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, now()
)
ON CONFLICT (occupation_code, area, metric) DO UPDATE SET
    series_id        = EXCLUDED.series_id,
    occupation_title = EXCLUDED.occupation_title,
    role             = EXCLUDED.role,
    area_name        = EXCLUDED.area_name,
    period_year      = EXCLUDED.period_year,
    value            = EXCLUDED.value,
    raw              = EXCLUDED.raw,
    synced_at        = now();

-- name: ListBlsWages :many
SELECT * FROM bls_wages ORDER BY occupation_code, area, metric;

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