-- +goose Up
-- Workforce Command Center data: contract value on opportunities, and labor-category + quantity on
-- requirements (a requirement is a labor category needing N people on an opportunity). Additive.

ALTER TABLE opportunities ADD COLUMN IF NOT EXISTS value numeric(14,2);

ALTER TABLE requirements ADD COLUMN IF NOT EXISTS labor_category text;
ALTER TABLE requirements ADD COLUMN IF NOT EXISTS quantity int NOT NULL DEFAULT 1 CHECK (quantity >= 1);

-- index to roll requirements up to an opportunity efficiently (org-leading, per tenancy rule)
CREATE INDEX IF NOT EXISTS idx_requirements_opp_lcat ON requirements (organization_id, opportunity_id, labor_category)
    WHERE deleted_at IS NULL;

-- +goose Down
DROP INDEX IF EXISTS idx_requirements_opp_lcat;
ALTER TABLE requirements DROP COLUMN IF EXISTS quantity;
ALTER TABLE requirements DROP COLUMN IF EXISTS labor_category;
ALTER TABLE opportunities DROP COLUMN IF EXISTS value;