triqura-ecd/supabase/migrations/20260720000004_create_intake_treatment_advice_domain.sql

-- ============================================================================
-- INTAKE / TREATMENT ADVICE DOMAIN
-- ============================================================================
-- Created: 2026-07-20
-- Source: docs/datamodel/deelmodellen/model-intake-behandeladvies.md (5
--   entiteiten), docs/datamodel/feitenmodellen/feitenmodel-intake-behandeladvies.md,
--   besluiten B31-B34.
--
-- clinical_intake_assessment hangs off clinical_care_episode (not off
-- referral_case) — it starts at some point after the acceptance decision,
-- possibly after a wait-list period (status = 'gepland' covers that).
-- ============================================================================

-- ============================================================================
-- clinical_intake_assessment
-- ============================================================================
CREATE TABLE clinical_intake_assessment (
  id                         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinical_care_episode_id   UUID NOT NULL REFERENCES clinical_care_episode(id) ON DELETE RESTRICT,
  initiation_reason          TEXT NOT NULL,        -- waardelijst intake_initiation_reason
  department                 TEXT NOT NULL,        -- waardelijst department
  status                     TEXT NOT NULL DEFAULT 'gepland', -- waardelijst intake_status
  planned_at                 TIMESTAMPTZ NOT NULL,
  started_at                 TIMESTAMPTZ,
  ended_at                   TIMESTAMPTZ,
  abort_reason               TEXT,                 -- waardelijst intake_abort_reason

  created_at                 TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                 TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at                 TIMESTAMPTZ,

  CONSTRAINT clinical_intake_assessment_status_check CHECK (status IN ('gepland', 'bezig', 'afgerond', 'afgebroken')),
  CONSTRAINT clinical_intake_assessment_started_required CHECK (
    status NOT IN ('bezig', 'afgerond', 'afgebroken') OR started_at IS NOT NULL
  ),
  CONSTRAINT clinical_intake_assessment_ended_required CHECK (
    status NOT IN ('afgerond', 'afgebroken') OR ended_at IS NOT NULL
  ),
  CONSTRAINT clinical_intake_assessment_abort_reason_required CHECK (
    status <> 'afgebroken' OR abort_reason IS NOT NULL
  )
);

CREATE INDEX idx_clinical_intake_assessment_episode ON clinical_intake_assessment(clinical_care_episode_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_clinical_intake_assessment_status ON clinical_intake_assessment(status) WHERE deleted_at IS NULL;

COMMENT ON TABLE clinical_intake_assessment IS 'model-intake-behandeladvies.md §1.1. Start ná het acceptatiebesluit (B31), niet gelijktijdig met episode-ontstaan — planned_at dekt een eventuele intakewachtlijst-periode. "Maximaal één lopende intake per episode" is een aan/uit-zetbare bedrijfsregel, geen schemabeperking.';

ALTER TABLE clinical_intake_assessment ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON clinical_intake_assessment FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_clinical_intake_assessment_updated_at BEFORE UPDATE ON clinical_intake_assessment FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- intake_contact
-- ============================================================================
CREATE TABLE intake_contact (
  id                            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinical_intake_assessment_id UUID NOT NULL REFERENCES clinical_intake_assessment(id) ON DELETE CASCADE,
  occurred_at                   TIMESTAMPTZ NOT NULL,
  contact_type                  TEXT NOT NULL,      -- waardelijst intake_contact_type
  performed_by                  UUID NOT NULL REFERENCES practitioners(id),
  notes                         TEXT,
  planned_duration_minutes      INTEGER,

  created_at                    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                    TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at                    TIMESTAMPTZ,

  CONSTRAINT intake_contact_duration_non_negative CHECK (planned_duration_minutes IS NULL OR planned_duration_minutes >= 0)
);

CREATE INDEX idx_intake_contact_assessment ON intake_contact(clinical_intake_assessment_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE intake_contact IS 'model-intake-behandeladvies.md §1.2. Exclusief aan één clinical_intake_assessment; generalisatie naar een bredere consult-/afspraakstructuur is de agenda-ronde.';

ALTER TABLE intake_contact ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON intake_contact FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_intake_contact_updated_at BEFORE UPDATE ON intake_contact FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- child_safety_check
-- ============================================================================
CREATE TABLE child_safety_check (
  id                             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinical_intake_assessment_id  UUID NOT NULL REFERENCES clinical_intake_assessment(id) ON DELETE CASCADE,
  performed_at                   TIMESTAMPTZ NOT NULL,
  performed_by                   UUID NOT NULL REFERENCES practitioners(id),
  responsible_for_minors         BOOLEAN NOT NULL,
  minor_count                    INTEGER,
  minor_ages                     TEXT,             -- vrije tekst, bewust geen kindrecords (dataminimalisatie)
  safety_concern                 BOOLEAN NOT NULL,
  safety_concern_notes           TEXT,
  action_taken                   BOOLEAN NOT NULL,
  action_taken_notes             TEXT,
  pregnancy                      BOOLEAN NOT NULL,  -- vierde vlag, te verifiëren tegen KNMG-meldcode (B34)
  notes                          TEXT,

  created_at                     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                     TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at                     TIMESTAMPTZ,

  CONSTRAINT child_safety_check_safety_notes_required CHECK (NOT safety_concern OR safety_concern_notes IS NOT NULL),
  CONSTRAINT child_safety_check_action_notes_required CHECK (NOT action_taken OR action_taken_notes IS NOT NULL)
);

-- maximaal één per intake
CREATE UNIQUE INDEX idx_child_safety_check_assessment ON child_safety_check(clinical_intake_assessment_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE child_safety_check IS 'model-intake-behandeladvies.md §1.3 (B34). Wet verplichte meldcode / KNMG-kindcheck.';

ALTER TABLE child_safety_check ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON child_safety_check FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_child_safety_check_updated_at BEFORE UPDATE ON child_safety_check FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- intake_mdt_review (minimale, voorlopige hook — zie §6 aldaar)
-- ============================================================================
CREATE TABLE intake_mdt_review (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  occurred_at   TIMESTAMPTZ NOT NULL,
  participants  TEXT NOT NULL,           -- vrije tekst; structurering wacht op de MDO-ronde (B14-B15)
  notes         TEXT,

  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at    TIMESTAMPTZ
);

COMMENT ON TABLE intake_mdt_review IS 'model-intake-behandeladvies.md §1.4. Geen eigen FK naar clinical_intake_assessment — gekoppeld via treatment_advice.mdt_review_id, telt pas zodra hij aan een vastgesteld advies hangt. LKS 4.0: dwingend voor settings 3-8, optioneel setting 2, n.v.t. vrijgevestigden.';

ALTER TABLE intake_mdt_review ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON intake_mdt_review FOR ALL USING (auth.role() = 'authenticated');

-- ============================================================================
-- treatment_advice
-- ============================================================================
CREATE TABLE treatment_advice (
  id                             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinical_intake_assessment_id  UUID NOT NULL REFERENCES clinical_intake_assessment(id) ON DELETE RESTRICT,
  decided_at                     TIMESTAMPTZ NOT NULL,
  decided_by                     UUID NOT NULL REFERENCES practitioners(id), -- indicerende regiebehandelaar
  outcome                        TEXT NOT NULL,       -- waardelijst treatment_advice_outcome
  recommended_care_program       TEXT,                -- waardelijst care_program
  rationale                      TEXT,
  mdt_review_id                  UUID REFERENCES intake_mdt_review(id),
  replaces_advice_id             UUID REFERENCES treatment_advice(id),
  replacement_reason             TEXT,

  created_at                     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                     TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at                     TIMESTAMPTZ,

  CONSTRAINT treatment_advice_outcome_check CHECK (outcome IN ('in_zorg', 'terugverwijzing', 'doorverwijzing', 'extra_diagnostiek')),
  CONSTRAINT treatment_advice_care_program_required CHECK (
    outcome <> 'in_zorg' OR recommended_care_program IS NOT NULL
  ),
  CONSTRAINT treatment_advice_rationale_required CHECK (
    outcome = 'in_zorg' OR (rationale IS NOT NULL AND length(trim(rationale)) > 0)
  ),
  CONSTRAINT treatment_advice_replacement_reason_required CHECK (
    replaces_advice_id IS NULL OR (replacement_reason IS NOT NULL AND length(trim(replacement_reason)) > 0)
  ),
  CONSTRAINT treatment_advice_no_self_replace CHECK (replaces_advice_id <> id)
);

-- vervangt-keten kan nooit vertakken, zelfde patroon als care_acceptance_decision
CREATE UNIQUE INDEX idx_treatment_advice_replaces_unique ON treatment_advice(replaces_advice_id) WHERE replaces_advice_id IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_treatment_advice_assessment ON treatment_advice(clinical_intake_assessment_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE treatment_advice IS 'model-intake-behandeladvies.md §1.5 (B32-B33). Geen apart voorafgaand advies — dit object zelf geeft de vervolgrichting. doorverwijzing/terugverwijzing zijn LKS-genormeerd volgtijdelijk (via replaces_advice_id); extra_diagnostiek is praktijkgefundeerd, niet LKS-genormeerd.';

ALTER TABLE treatment_advice ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON treatment_advice FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_treatment_advice_updated_at BEFORE UPDATE ON treatment_advice FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Een afgeronde intake vereist minimaal één (niet noodzakelijk het laatst
-- vervangen) treatment_advice — afgedwongen op applicatieniveau, niet als
-- schema-constraint (zou een circulaire FK-afhankelijkheid vereisen).
COMMENT ON COLUMN clinical_intake_assessment.status IS 'gepland/bezig/afgerond/afgebroken. Bij afgerond hoort minimaal één treatment_advice (applicatieniveau, geen FK-constraint i.v.m. circulaire afhankelijkheid).';