-- ============================================================================
-- REFERRAL / INSTROOM DOMAIN
-- ============================================================================
-- Created: 2026-07-20
-- Source: docs/datamodel/deelmodellen/model-instroom.md (20 entiteiten),
--   docs/datamodel/feitenmodellen/feitenmodel-instroom.md, besluiten B21-B30.
--
-- Design principles carried over from the model documents:
-- - referral_case has NO status column — the current state is derived from
--   the event timeline (signal_case_assignment/screening_activity/
--   case_information_request/care_acceptance_decision/case_withdrawal/
--   case_consolidation). See model-instroom.md §5. Application code (or a
--   read-model view, per the UX review) computes "actuele stand", not this
--   schema.
-- - "vervangt"-relations (care_acceptance_decision, professional_referral,
--   municipal_care_assignment) are enforced unique-if-set (partial unique
--   index) so the chain can never fork.
-- - `medewerker`/beslisser references point at the existing `practitioners`
--   table (already used by the prototype screening/intake schema).
-- - `decision_authority_id` on care_acceptance_decision is left nullable
--   for now: the target table for decision authority does not exist yet
--   (bevoegdheidsronde, B16/B33). The model calls it "verplicht"; enforcing
--   NOT NULL today would make every insert impossible. Tighten once that
--   ronde delivers a real authority table.
-- ============================================================================

-- ============================================================================
-- referral_request
-- ============================================================================
CREATE TABLE referral_request (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id             UUID REFERENCES person(id) ON DELETE RESTRICT,
  person_identified_at  TIMESTAMPTZ,
  person_identified_by  UUID REFERENCES practitioners(id),
  initiated_at          TIMESTAMPTZ NOT NULL,
  initiator_type        TEXT NOT NULL,             -- waardelijst initiator_type
  referrer_id           UUID REFERENCES referrer(id),
  requested_scope       TEXT NOT NULL,
  presenting_need_text  TEXT NOT NULL,

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

  CONSTRAINT referral_request_person_identification CHECK (
    (person_id IS NULL AND person_identified_at IS NULL AND person_identified_by IS NULL)
    OR (person_id IS NOT NULL AND person_identified_at IS NOT NULL AND person_identified_by IS NOT NULL)
  )
);

COMMENT ON TABLE referral_request IS 'model-instroom.md §2.1. person_id optioneel: crisisaanmelding met onbekende identiteit (B26) — geen placeholder-persoon, de koppeling is een eigen feit.';

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

-- ============================================================================
-- referral_submission
-- ============================================================================
CREATE TABLE referral_submission (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  received_at       TIMESTAMPTZ NOT NULL,
  channel           TEXT NOT NULL,                 -- waardelijst submission_channel
  source_reference  TEXT,                          -- vrije tekst of naam verwijzer; "onbekend" toegestaan
  recorded_by       UUID REFERENCES practitioners(id),
  raw_note          TEXT,

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

COMMENT ON TABLE referral_submission IS 'model-instroom.md §2.2. Nooit overschreven door een latere aanvulling — een aanvulling is een nieuwe submission.';

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

-- ============================================================================
-- submission_document
-- ============================================================================
CREATE TABLE submission_document (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  submission_id       UUID NOT NULL REFERENCES referral_submission(id) ON DELETE CASCADE,
  document_type       TEXT NOT NULL,                -- waardelijst document_type
  document_reference  UUID NOT NULL,                -- documentopslag

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

CREATE INDEX idx_submission_document_submission ON submission_document(submission_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE submission_document IS 'model-instroom.md §2.3.';

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

-- ============================================================================
-- submission_request_link
-- ============================================================================
CREATE TABLE submission_request_link (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  submission_id       UUID NOT NULL REFERENCES referral_submission(id) ON DELETE CASCADE,
  referral_request_id UUID NOT NULL REFERENCES referral_request(id) ON DELETE CASCADE,
  established_at      TIMESTAMPTZ NOT NULL,

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

  UNIQUE (submission_id, referral_request_id)
);

CREATE INDEX idx_submission_request_link_request ON submission_request_link(referral_request_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE submission_request_link IS 'model-instroom.md §2.4. "representeert" vs "vult_aan" is niet opgeslagen — af te leiden als vroegste established_at per referral_request_id.';

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

-- ============================================================================
-- submission_duplicate_assessment
-- ============================================================================
CREATE TABLE submission_duplicate_assessment (
  id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  submission_id               UUID NOT NULL REFERENCES referral_submission(id) ON DELETE CASCADE,
  duplicate_of_submission_id  UUID NOT NULL REFERENCES referral_submission(id) ON DELETE CASCADE,
  assessed_by                 UUID NOT NULL REFERENCES practitioners(id),
  assessed_at                 TIMESTAMPTZ NOT NULL,
  reason                      TEXT NOT NULL,

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

  CONSTRAINT submission_duplicate_not_self CHECK (submission_id <> duplicate_of_submission_id)
);

COMMENT ON TABLE submission_duplicate_assessment IS 'model-instroom.md §2.5. Niet-destructief: beide submissions blijven bestaan.';

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

-- ============================================================================
-- referral_case
-- ============================================================================
CREATE TABLE referral_case (
  id                        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id                 UUID REFERENCES person(id) ON DELETE RESTRICT,
  person_identified_at      TIMESTAMPTZ,
  person_identified_by      UUID REFERENCES practitioners(id),
  opened_at                 TIMESTAMPTZ NOT NULL,
  presenting_need_summary   TEXT NOT NULL,
  legal_framework           TEXT NOT NULL,          -- waardelijst wettelijk_kader
  zpm_referral_type         TEXT,                   -- waardelijst zpm_verwijstype
  gp_informed_at            DATE,

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

  CONSTRAINT referral_case_person_identification CHECK (
    (person_id IS NULL AND person_identified_at IS NULL AND person_identified_by IS NULL)
    OR (person_id IS NOT NULL AND person_identified_at IS NOT NULL AND person_identified_by IS NOT NULL)
  )
);

COMMENT ON TABLE referral_case IS 'model-instroom.md §2.6. Geen statusveld — zie §5 aldaar en de opmerking bovenaan dit bestand. legal_framework/zpm_referral_type/gp_informed_at hersteld van het oudere AANMELDING (B21-B27).';

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

-- Now that referral_case exists: wire the consent scope FK from the person/client migration
ALTER TABLE consent
  ADD CONSTRAINT consent_scope_referral_case_fk FOREIGN KEY (scope_referral_case_id) REFERENCES referral_case(id) ON DELETE SET NULL;

-- ============================================================================
-- submission_case_assignment
-- ============================================================================
CREATE TABLE submission_case_assignment (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  submission_id     UUID NOT NULL REFERENCES referral_submission(id) ON DELETE CASCADE,
  referral_case_id  UUID NOT NULL REFERENCES referral_case(id) ON DELETE CASCADE,
  assigned_at       TIMESTAMPTZ NOT NULL,
  assigned_by       UUID NOT NULL REFERENCES practitioners(id),
  reason            TEXT,

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

  UNIQUE (submission_id, referral_case_id)
);

CREATE INDEX idx_submission_case_assignment_case ON submission_case_assignment(referral_case_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE submission_case_assignment IS 'model-instroom.md §2.7 (hernoemd van signal_case_assignment). Toewijzing is een gebeurtenis, geen overschrijfbaar veld.';

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

-- ============================================================================
-- request_case_link
-- ============================================================================
CREATE TABLE request_case_link (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referral_request_id   UUID NOT NULL REFERENCES referral_request(id) ON DELETE CASCADE,
  referral_case_id      UUID NOT NULL REFERENCES referral_case(id) ON DELETE CASCADE,
  since_date            DATE NOT NULL,
  reason                TEXT,

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

  UNIQUE (referral_request_id, referral_case_id)
);

COMMENT ON TABLE request_case_link IS 'model-instroom.md §2.8. reason verplicht (applicatieniveau) zodra een request aan >1 case gekoppeld is.';

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

-- ============================================================================
-- case_consolidation
-- ============================================================================
CREATE TABLE case_consolidation (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  primary_case_id       UUID NOT NULL REFERENCES referral_case(id) ON DELETE RESTRICT,
  related_case_id       UUID NOT NULL REFERENCES referral_case(id) ON DELETE RESTRICT,
  overlap_assessed_at   TIMESTAMPTZ,
  designated_at         TIMESTAMPTZ NOT NULL,
  designated_by         UUID NOT NULL REFERENCES practitioners(id),
  reason                TEXT NOT NULL,

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

  CONSTRAINT case_consolidation_no_self CHECK (primary_case_id <> related_case_id)
);

-- een case kan hoogstens één keer de niet-leidende rol vervullen
CREATE UNIQUE INDEX idx_case_consolidation_related_unique ON case_consolidation(related_case_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE case_consolidation IS 'model-instroom.md §2.9 (hernoemd van access_case_consolidation). Geen speciale decision_authority vereist (administratieve correctie). Transitieve cykels niet uitgesloten op schemaniveau — applicatieverantwoordelijkheid.';

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

-- ============================================================================
-- screening_activity
-- ============================================================================
CREATE TABLE screening_activity (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referral_case_id  UUID NOT NULL REFERENCES referral_case(id) ON DELETE CASCADE,
  performed_at      TIMESTAMPTZ NOT NULL,
  activity_type     TEXT NOT NULL,          -- waardelijst screening_activity_type
  performed_by      UUID NOT NULL REFERENCES practitioners(id),
  notes             TEXT,
  funded            BOOLEAN NOT NULL DEFAULT false,

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

CREATE INDEX idx_screening_activity_case ON screening_activity(referral_case_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE screening_activity IS 'model-instroom.md §2.10. Directe koppeling aan referral_case — de aparte "screening"-groeperingsentiteit is vervallen (reviewronde 19 juli).';

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

-- ============================================================================
-- case_information_request
-- ============================================================================
CREATE TABLE case_information_request (
  id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referral_case_id            UUID NOT NULL REFERENCES referral_case(id) ON DELETE CASCADE,
  requested_at                TIMESTAMPTZ NOT NULL,
  requested_by                UUID NOT NULL REFERENCES practitioners(id),
  requested_information       TEXT NOT NULL,
  resolved_by_submission_id   UUID REFERENCES referral_submission(id),

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

CREATE UNIQUE INDEX idx_case_information_request_resolved_submission ON case_information_request(resolved_by_submission_id) WHERE resolved_by_submission_id IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_case_information_request_case ON case_information_request(referral_case_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE case_information_request IS 'model-instroom.md §2.11. Geen besluituitkomst en geen statusfase (B21/vraag 5) — een gedateerd gebeurtenisfeit; kan zich ook ná een eerder besluit voordoen tijdens heroverweging.';

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

-- ============================================================================
-- care_acceptance_decision
-- ============================================================================
CREATE TABLE care_acceptance_decision (
  id                     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referral_case_id       UUID NOT NULL REFERENCES referral_case(id) ON DELETE RESTRICT,
  decided_at             TIMESTAMPTZ NOT NULL,
  decided_by             UUID NOT NULL REFERENCES practitioners(id),
  decision_authority_id  UUID,                    -- ref decision_authority — tabel volgt uit bevoegdheidsronde (B16); tijdelijk geen FK/NOT NULL
  content_match          BOOLEAN NOT NULL,
  capacity_available     BOOLEAN,
  program_context        TEXT,                    -- waardelijst care_program of vrije tekst
  decision_outcome       TEXT NOT NULL,            -- waardelijst decision_outcome
  follow_up_route        TEXT NOT NULL,            -- waardelijst follow_up_route
  rationale              TEXT,
  replaces_decision_id   UUID REFERENCES care_acceptance_decision(id),
  replacement_reason     TEXT,

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

  CONSTRAINT care_acceptance_decision_outcome_check CHECK (decision_outcome IN ('geaccepteerd', 'afgewezen')),
  CONSTRAINT care_acceptance_decision_rationale_required CHECK (
    decision_outcome <> 'afgewezen' OR (rationale IS NOT NULL AND length(trim(rationale)) > 0)
  ),
  CONSTRAINT care_acceptance_decision_replacement_reason_required CHECK (
    replaces_decision_id IS NULL OR (replacement_reason IS NOT NULL AND length(trim(replacement_reason)) > 0)
  ),
  CONSTRAINT care_acceptance_decision_no_self_replace CHECK (replaces_decision_id <> id)
);

-- vervangt-keten kan nooit vertakken (B23 / reviewronde 19 juli, kritieke bevinding)
CREATE UNIQUE INDEX idx_care_acceptance_decision_replaces_unique ON care_acceptance_decision(replaces_decision_id) WHERE replaces_decision_id IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_care_acceptance_decision_case ON care_acceptance_decision(referral_case_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE care_acceptance_decision IS 'model-instroom.md §2.12. Geen apart voorafgaand screeningsadvies (B21). Concurrency bij gelijktijdige heroverwegingen (SELECT ... FOR UPDATE op de keten-tail) is applicatieverantwoordelijkheid.';

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

-- ============================================================================
-- case_withdrawal
-- ============================================================================
CREATE TABLE case_withdrawal (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referral_case_id  UUID NOT NULL REFERENCES referral_case(id) ON DELETE CASCADE,
  withdrawn_at      TIMESTAMPTZ NOT NULL,
  recorded_by       UUID NOT NULL REFERENCES practitioners(id),
  note              TEXT,

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

CREATE INDEX idx_case_withdrawal_case ON case_withdrawal(referral_case_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE case_withdrawal IS 'model-instroom.md §2.13. Geen beslisbevoegdheid vereist; kan op elk moment, ook ná een positief besluit (B24).';

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

-- ============================================================================
-- professional_referral
-- ============================================================================
CREATE TABLE professional_referral (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referral_request_id   UUID NOT NULL REFERENCES referral_request(id) ON DELETE RESTRICT,
  referrer_id           UUID NOT NULL REFERENCES referrer(id),
  referral_date         DATE NOT NULL,
  echelon               TEXT,                      -- waardelijst echelon
  suspected_condition   TEXT,
  is_readmission        BOOLEAN NOT NULL DEFAULT false,
  replaces_referral_id  UUID REFERENCES professional_referral(id),
  replacement_reason    TEXT,

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

  CONSTRAINT professional_referral_replacement_reason_required CHECK (
    replaces_referral_id IS NULL OR (replacement_reason IS NOT NULL AND length(trim(replacement_reason)) > 0)
  ),
  CONSTRAINT professional_referral_no_self_replace CHECK (replaces_referral_id <> id)
);

CREATE UNIQUE INDEX idx_professional_referral_replaces_unique ON professional_referral(replaces_referral_id) WHERE replaces_referral_id IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_professional_referral_request ON professional_referral(referral_request_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE professional_referral IS 'model-instroom.md §2.14. Correctie/aanvulling = nieuw, vervangend record, geen nieuw request (was open punt 1, opgelost).';

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

-- ============================================================================
-- municipal_care_assignment
-- ============================================================================
CREATE TABLE municipal_care_assignment (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referral_request_id   UUID NOT NULL REFERENCES referral_request(id) ON DELETE RESTRICT,
  legal_framework       TEXT NOT NULL,            -- waardelijst municipal_legal_framework
  municipality_code     TEXT NOT NULL,
  assignment_number     TEXT NOT NULL,
  product_category      TEXT NOT NULL,            -- waardelijst municipal_product_category
  product_code          TEXT NOT NULL,
  volume                NUMERIC NOT NULL,
  unit                  TEXT NOT NULL,            -- waardelijst municipal_product_unit
  frequency             TEXT NOT NULL,
  start_date            DATE NOT NULL,
  end_date              DATE,
  issued_at             TIMESTAMPTZ NOT NULL,
  replaces_assignment_id UUID REFERENCES municipal_care_assignment(id),
  replacement_reason    TEXT,

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

  CONSTRAINT municipal_care_assignment_replacement_reason_required CHECK (
    replaces_assignment_id IS NULL OR (replacement_reason IS NOT NULL AND length(trim(replacement_reason)) > 0)
  ),
  CONSTRAINT municipal_care_assignment_no_self_replace CHECK (replaces_assignment_id <> id)
);

CREATE UNIQUE INDEX idx_municipal_care_assignment_replaces_unique ON municipal_care_assignment(replaces_assignment_id) WHERE replaces_assignment_id IS NOT NULL AND deleted_at IS NULL;
CREATE UNIQUE INDEX idx_municipal_care_assignment_number ON municipal_care_assignment(municipality_code, assignment_number) WHERE deleted_at IS NULL;

COMMENT ON TABLE municipal_care_assignment IS 'model-instroom.md §2.15 (voorstel, juridisch onderzoek 19 juli). Naast, niet in plaats van professional_referral (Jeugdwet kent een eigen professionele verwijsroute).';

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

-- ============================================================================
-- legal_mandate
-- ============================================================================
CREATE TABLE legal_mandate (
  id                            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referral_case_id              UUID NOT NULL REFERENCES referral_case(id) ON DELETE RESTRICT,
  mandate_type                  TEXT NOT NULL,      -- waardelijst mandate_type
  decision_reference            TEXT NOT NULL,
  decided_by_role               TEXT NOT NULL,      -- waardelijst mandate_decider_role
  decided_at                    TIMESTAMPTZ NOT NULL,
  medical_statement_reference   UUID,               -- documentopslag
  effectuated_at                TIMESTAMPTZ NOT NULL,
  valid_from                    TIMESTAMPTZ NOT NULL,
  valid_until                   TIMESTAMPTZ,

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

  CONSTRAINT legal_mandate_type_check CHECK (mandate_type IN ('crisismaatregel', 'zorgmachtiging')),
  CONSTRAINT legal_mandate_medical_statement_required CHECK (
    mandate_type <> 'crisismaatregel' OR medical_statement_reference IS NOT NULL
  )
);

CREATE INDEX idx_legal_mandate_case ON legal_mandate(referral_case_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE legal_mandate IS 'model-instroom.md §2.16 (voorstel, juridisch onderzoek 19 juli). Geen vervangt-relatie: een crisismaatregel die overgaat in een zorgmachtiging is een nieuw mandaat.';

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

-- ============================================================================
-- crisis_encounter_note
-- ============================================================================
CREATE TABLE crisis_encounter_note (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referral_case_id  UUID NOT NULL REFERENCES referral_case(id) ON DELETE CASCADE,
  occurred_at       TIMESTAMPTZ NOT NULL,
  recorded_by       UUID NOT NULL REFERENCES practitioners(id),
  fact_type         TEXT NOT NULL,          -- waardelijst crisis_fact_type
  description       TEXT NOT NULL,
  structured_value  JSONB,

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

CREATE INDEX idx_crisis_encounter_note_case ON crisis_encounter_note(referral_case_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE crisis_encounter_note IS 'model-instroom.md §2.17 (voorstel). Werkt al zonder geïdentificeerde persoon. 14-dagen-identificatienudge hangt aan referral_request.person_identified_at IS NULL, geen constraint hier.';

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

-- ============================================================================
-- clinical_care_episode
-- ============================================================================
CREATE TABLE clinical_care_episode (
  id                                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  originating_decision_id               UUID REFERENCES care_acceptance_decision(id) ON DELETE RESTRICT,
  originating_mandate_id                UUID REFERENCES legal_mandate(id) ON DELETE RESTRICT,
  started_at                            TIMESTAMPTZ NOT NULL,
  ended_at                              TIMESTAMPTZ,
  end_reason                            TEXT,                -- waardelijst episode_end_reason
  possible_continuation_of_episode_id   UUID REFERENCES clinical_care_episode(id),

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

  CONSTRAINT clinical_care_episode_origin_xor CHECK (
    (originating_decision_id IS NOT NULL AND originating_mandate_id IS NULL)
    OR (originating_decision_id IS NULL AND originating_mandate_id IS NOT NULL)
  ),
  CONSTRAINT clinical_care_episode_end_reason_required CHECK (
    ended_at IS NULL OR end_reason IS NOT NULL
  ),
  CONSTRAINT clinical_care_episode_no_self_continuation CHECK (possible_continuation_of_episode_id <> id)
);

CREATE UNIQUE INDEX idx_clinical_care_episode_decision ON clinical_care_episode(originating_decision_id) WHERE originating_decision_id IS NOT NULL AND deleted_at IS NULL;
CREATE UNIQUE INDEX idx_clinical_care_episode_mandate ON clinical_care_episode(originating_mandate_id) WHERE originating_mandate_id IS NOT NULL AND deleted_at IS NULL;

COMMENT ON TABLE clinical_care_episode IS 'model-instroom.md §2.18 (alleen ontstaan/einde — volledig episodemodel is een ander deelgebied). XOR: ontstaat óf uit een acceptatiebesluit óf uit een mandaat. possible_continuation_of_episode_id is een zorginhoudelijke aanwijzing, GEEN NZa-trajectnummertoets.';

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

-- ============================================================================
-- case_urgency_assessment
-- ============================================================================
CREATE TABLE case_urgency_assessment (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referral_case_id  UUID NOT NULL REFERENCES referral_case(id) ON DELETE CASCADE,
  assessed_at       TIMESTAMPTZ NOT NULL,
  assessed_by       UUID NOT NULL REFERENCES practitioners(id),
  assessed_in_role  TEXT NOT NULL,          -- waardelijst urgency_assessor_role
  urgency_level     TEXT NOT NULL,          -- waardelijst urgency_level

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

CREATE INDEX idx_case_urgency_assessment_case ON case_urgency_assessment(referral_case_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE case_urgency_assessment IS 'model-instroom.md §2.19. Herhaalbaar, laatste assessed_at geldt; geen vervangt-relatie (lagere inzet dan een acceptatiebesluit).';

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

-- ============================================================================
-- episode_team_involvement (minimale, voorlopige hook — zie besluitenlog §10 punt 3)
-- ============================================================================
CREATE TABLE episode_team_involvement (
  id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinical_care_episode_id    UUID NOT NULL REFERENCES clinical_care_episode(id) ON DELETE CASCADE,
  team_reference              TEXT NOT NULL,     -- vrije tekst; geen Team-entiteit vóór de zorgteam-ronde
  involved_since               TIMESTAMPTZ NOT NULL,
  recorded_by                  UUID NOT NULL REFERENCES practitioners(id),

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

CREATE INDEX idx_episode_team_involvement_episode ON episode_team_involvement(clinical_care_episode_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE episode_team_involvement IS 'model-instroom.md §2.20. Bewust minimaal — geen rollen, geen bevoegdheid, geen individueel lidmaatschap. Wordt vervangen/geabsorbeerd door het volledige zorgteam-model (besluitenlog §10 punt 3).';

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