-- mig 106 — add 'madrid' to firm office CHECK constraints
--
-- m's ask 2026-05-20 09:42: add Madrid as an HLC office, alongside the
-- existing seven (munich, duesseldorf, hamburg, amsterdam, london,
-- paris, milan). Two active CHECK constraints to extend:
--   - paliad.users.office          (mig 002)
--   - paliad.partner_units.office  (mig 018; renamed mig 024 + mig 027)
--
-- The Go-side source of truth lives in internal/offices/offices.go;
-- this migration keeps the DB in sync.
--
-- Long-term, the admin area will let firms manage their own office
-- list (separate issue) — but for now the list is hard-coded here
-- + offices.go.
--
-- Non-blocking: extending a CHECK constraint is a metadata-only change
-- on a small enum-style column.

SELECT set_config(
    'paliad.audit_reason',
    'mig 106: add madrid to firm office CHECK on users + partner_units',
    true);

ALTER TABLE paliad.users
    DROP CONSTRAINT IF EXISTS users_office_check;
ALTER TABLE paliad.users
    ADD CONSTRAINT users_office_check
    CHECK (office IN (
        'munich', 'duesseldorf', 'hamburg',
        'amsterdam', 'london', 'paris', 'milan',
        'madrid'
    ));

ALTER TABLE paliad.partner_units
    DROP CONSTRAINT IF EXISTS partner_units_office_check;
ALTER TABLE paliad.partner_units
    ADD CONSTRAINT partner_units_office_check
    CHECK (office IN (
        'munich', 'duesseldorf', 'hamburg',
        'amsterdam', 'london', 'paris', 'milan',
        'madrid'
    ));
