-- mig 112 — t-paliad-222 / m/paliad#47 — Client Role rework.
--
-- Widens paliad.projects.our_side CHECK to seven sub-role values and
-- drops the legacy 'court' / 'both' entries. The DB column name stays
-- as 'our_side' (UI label changes only — see design doc §2.2 Q1).
--
-- New allowed sub-roles, grouped at display time:
--   Active (we initiate)  : claimant, applicant, appellant
--   Reactive (we defend)  : defendant, respondent
--   Third Party / Other   : third_party, other
--   NULL                  : unknown / not set
--
-- Backfill: any rows still on 'court' / 'both' fall back to NULL.
-- Verified 2026-05-20: all 12 production rows are NULL, so this is
-- a no-op on prod; the UPDATE runs defensively for staging / test
-- fixtures that may carry the legacy values.
--
-- Idempotent so re-runs against a partially-applied state stay safe.

BEGIN;

-- 1. Backfill any 'court' / 'both' rows to NULL.
UPDATE paliad.projects
   SET our_side = NULL
 WHERE our_side IN ('court', 'both');

-- 2. Swap the CHECK constraint for the widened sub-role set.
ALTER TABLE paliad.projects
    DROP CONSTRAINT IF EXISTS projects_our_side_check;

ALTER TABLE paliad.projects
    ADD CONSTRAINT projects_our_side_check
    CHECK (our_side IS NULL OR our_side IN (
        'claimant', 'defendant',
        'applicant', 'appellant',
        'respondent',
        'third_party', 'other'
    ));

COMMENT ON COLUMN paliad.projects.our_side IS
    'Which side the firm represents on this case project (renamed in '
    'the UI to "Client Role" / "Mandantenrolle" — t-paliad-222 / '
    'm/paliad#47). Allowed sub-roles, grouped at display time: Active '
    '(claimant, applicant, appellant); Reactive (defendant, '
    'respondent); Third Party / Other (third_party, other). NULL = '
    'unknown. The form hides the field on non-case project types. '
    'Drives the Fristenrechner Determinator perspective chip — Active '
    'group → claimant-perspective, Reactive → defendant-perspective, '
    'Third Party / Other → null (chip free-pick).';

COMMIT;
