-- Migration 016: Notfallplan Incidents and Melde-Templates
-- Extends Notfallplan module with incident register and template management

CREATE TABLE IF NOT EXISTS compliance_notfallplan_incidents (
    id                        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id                 TEXT NOT NULL DEFAULT 'default',
    title                     TEXT NOT NULL,
    description               TEXT,
    detected_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    detected_by               TEXT,
    status                    TEXT NOT NULL DEFAULT 'detected',
    -- detected | classified | assessed | reported | not_reportable | closed
    severity                  TEXT NOT NULL DEFAULT 'medium',
    -- low | medium | high | critical
    affected_data_categories  JSONB DEFAULT '[]'::jsonb,
    estimated_affected_persons INTEGER DEFAULT 0,
    measures                  JSONB DEFAULT '[]'::jsonb,
    art34_required            BOOLEAN DEFAULT FALSE,
    art34_justification       TEXT,
    reported_to_authority_at  TIMESTAMPTZ,
    notified_affected_at      TIMESTAMPTZ,
    closed_at                 TIMESTAMPTZ,
    closed_by                 TEXT,
    lessons_learned           TEXT,
    created_at                TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at                TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_incidents_tenant ON compliance_notfallplan_incidents(tenant_id);
CREATE INDEX IF NOT EXISTS idx_incidents_status ON compliance_notfallplan_incidents(status);

CREATE TABLE IF NOT EXISTS compliance_notfallplan_templates (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   TEXT NOT NULL DEFAULT 'default',
    type        TEXT NOT NULL DEFAULT 'art33',
    -- art33 | art34
    title       TEXT NOT NULL,
    content     TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_templates_tenant ON compliance_notfallplan_templates(tenant_id);
