-- +goose Up
-- BLS OEWS regional wage baseline cache for the Galapagos occupations in Hawaii and New Mexico.
-- Like the other reference catalogs this is GLOBAL public data, no organization_id, outside Row Level
-- Security. The app role reads only; the weekly rates sync writes as the migrator role. This baseline
-- sits beside any awarded rate context, labeled as a regional wage baseline, current year.

CREATE TABLE bls_wages (
    series_id        text PRIMARY KEY,             -- OEWS series id (upsert key)
    occupation_code  text NOT NULL DEFAULT '',     -- SOC, for example 151212
    occupation_title text NOT NULL DEFAULT '',
    role             text NOT NULL DEFAULT '',     -- the Galapagos role this occupation maps to
    area             text NOT NULL DEFAULT '',     -- HI or NM
    area_name        text NOT NULL DEFAULT '',     -- Hawaii or New Mexico
    metric           text NOT NULL DEFAULT '',     -- annual_mean or annual_median
    period_year      text NOT NULL DEFAULT '',     -- for example 2025
    value            numeric(14,2),                -- the wage in dollars
    raw              jsonb NOT NULL,
    synced_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_bls_occ_area ON bls_wages (occupation_code, area);

GRANT SELECT ON bls_wages TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS bls_wages;