-- +goose Up

-- THE WRAP RATE, AND THE END OF CALLING A SPREAD A MARGIN.
--
-- Migration 0086 wrote the rule down and nothing ever implemented it. Its comment on
-- proposal_staffing_links says it plainly: "pay_rate and bill_rate are the ends; burden_pct is what makes
-- the middle real", and "burden_pct being NULL is the state that clause A of the gate is about: it means the
-- line is UNBURDENED and no margin may be reported for it at all."
--
-- Meanwhile the recruiting side computes bill minus pay, calls the result margin_pct, and publishes it on the
-- assignment screen, in the placements export, and behind an access control added on 30 July 2026. It is not
-- a margin. It is a SPREAD, which is the gross difference between the two rates before the employer has paid
-- for anything: payroll taxes, workers compensation, unemployment insurance, health cover, paid leave,
-- retirement, general and administrative, and overhead.
--
-- THE SIZE OF THE ERROR, on ordinary numbers. Pay 110, bill 170, a wrap of 35 percent.
--   spread  = 170 - 110                    = 60.00 per hour, 35.29 percent of bill
--   cost    = 110 * 1.35                   = 148.50 per hour
--   margin  = 170 - 148.50                 = 21.50 per hour, 12.65 percent of bill
-- The product currently reports 35.29 where the answer is 12.65. A staffing firm deciding whether to bid a
-- rate is being shown nearly three times the money it will actually keep.
--
-- THIS EXTENDS 0086 RATHER THAN BUILDING BESIDE IT. Same column name, same numeric type, same fraction
-- semantics, same NULL rule. A second money vocabulary would be the MAG and TAG collision again, in the one
-- part of the product where two names for one number is a financial reporting problem rather than an
-- inconvenience.
--
-- STORED AS A FRACTION, never as a whole number of percent, matching offers.fee_pct which is already stored
-- 0 to 1. A 35 percent wrap is 0.35.
--
-- NOTHING IS BACKFILLED AND NO DEFAULT IS INVENTED. Every burden column arrives NULL and stays NULL until a
-- human sets it. Guessing an industry average wrap would produce a margin figure that looks authoritative and
-- is fiction, which is worse than the honest refusal to report one. An organization sees spreads until it
-- tells the product what its wrap actually is.

-- The per record burden. On the offer as well as the placement, because the rate is negotiated at the offer
-- and a recruiter needs to see what a proposed rate actually earns BEFORE it is extended, not after it is
-- signed. An offer that looked profitable at the spread and loses money at cost is exactly the decision this
-- column exists to inform.
--
-- The upper bound is deliberate and is not present on 0086's column. It catches the one data entry error that
-- would otherwise pass silently in the wrong direction: typing 35 to mean 35 percent. A genuine wrap can
-- exceed 100 percent of direct labour once general and administrative and fee are layered on, so the bound is
-- 5 rather than 1, which admits every real rate and refuses the typo.
ALTER TABLE offers
    ADD COLUMN burden_pct numeric(9,6),
    ADD CONSTRAINT ck_offers_burden_range
        CHECK (burden_pct IS NULL OR (burden_pct >= 0 AND burden_pct <= 5));

ALTER TABLE placements
    ADD COLUMN burden_pct numeric(9,6),
    ADD CONSTRAINT ck_placements_burden_range
        CHECK (burden_pct IS NULL OR (burden_pct >= 0 AND burden_pct <= 5));

-- The same bound applied to 0086's column, so the two tables holding the same concept cannot disagree about
-- what is a valid rate. This is safe to tighten now precisely because nothing has ever written the column:
-- no Go code reads or writes burden_pct anywhere in the tree, so every existing row is NULL and the
-- constraint validates instantly.
ALTER TABLE proposal_staffing_links
    ADD CONSTRAINT ck_staffing_burden_range
        CHECK (burden_pct IS NULL OR (burden_pct <= 5));

-- The organization's standard wrap, used when a record does not carry its own.
--
-- ONE DEFAULT IS NOT THE WHOLE TRUTH AND THE PRODUCT MUST NOT PRETEND OTHERWISE. A real firm's wrap varies by
-- client contract, by labour category, and over time as insurance renews. This is the figure a firm quotes
-- when asked "what is your wrap", it is right for most lines, and the per record column above is how the
-- exceptions get told. Per client and per labour category wrap tables are a later piece of work and are
-- recorded as a known limitation rather than implied to exist.
--
-- NULL is the shipped state and means the firm has not said. Every organization therefore starts by seeing
-- spreads and no margins, which is the accurate description of what the product knows about them on day one.
ALTER TABLE org_settings
    ADD COLUMN default_burden_pct numeric(9,6),
    ADD CONSTRAINT ck_org_settings_burden_range
        CHECK (default_burden_pct IS NULL OR (default_burden_pct >= 0 AND default_burden_pct <= 5));

-- +goose Down
ALTER TABLE org_settings DROP CONSTRAINT IF EXISTS ck_org_settings_burden_range;
ALTER TABLE org_settings DROP COLUMN IF EXISTS default_burden_pct;
ALTER TABLE proposal_staffing_links DROP CONSTRAINT IF EXISTS ck_staffing_burden_range;
ALTER TABLE placements DROP CONSTRAINT IF EXISTS ck_placements_burden_range;
ALTER TABLE placements DROP COLUMN IF EXISTS burden_pct;
ALTER TABLE offers DROP CONSTRAINT IF EXISTS ck_offers_burden_range;
ALTER TABLE offers DROP COLUMN IF EXISTS burden_pct;