-- mig 110 — add 'other' as a sixth paliad.projects.type value
--
-- m/paliad#51 (t-paliad-221): the type chip filter on /projects used to
-- treat unclassified projects as a synthetic "Empty" bucket. We replace
-- that with a real 'other' type so every row carries a meaningful label
-- and the filter UI stops needing a NULL/Empty shim.
--
-- Defensive backfill: NOT NULL + the original IN-list CHECK already
-- forbid NULL rows, but we coerce any stray rows just in case a future
-- migration ever relaxed the constraint. As of 2026-05-20 production
-- carries zero rows that would change here (live query confirmed).
--
-- The Go-side source of truth lives in
-- internal/services/project_service.go (ProjectType constants +
-- isValidProjectType); this migration keeps the DB in sync.

SELECT set_config(
    'paliad.audit_reason',
    'mig 110: add ''other'' to projects.type CHECK + backfill NULLs (m/paliad#51)',
    true);

-- Backfill first so the new CHECK never rejects a pre-existing row.
UPDATE paliad.projects
   SET type = 'other'
 WHERE type IS NULL;

ALTER TABLE paliad.projects
    DROP CONSTRAINT IF EXISTS projects_type_check;
ALTER TABLE paliad.projects
    ADD CONSTRAINT projects_type_check
    CHECK (type IN (
        'client', 'litigation', 'patent', 'case', 'project', 'other'
    ));
