breakpilot-compliance/backend-compliance/migrations/012_notfallplan.sql

-- Migration 012: Notfallplan (Emergency Plan)
CREATE TABLE IF NOT EXISTS compliance_notfallplan_contacts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR(100) NOT NULL DEFAULT 'default',
    name TEXT NOT NULL,
    role VARCHAR(100),
    email VARCHAR(200),
    phone VARCHAR(50),
    is_primary BOOLEAN DEFAULT FALSE,
    available_24h BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS compliance_notfallplan_scenarios (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR(100) NOT NULL DEFAULT 'default',
    title TEXT NOT NULL,
    category VARCHAR(50),
    severity VARCHAR(20) DEFAULT 'medium',
    description TEXT,
    response_steps JSONB DEFAULT '[]',
    estimated_recovery_time INTEGER,
    last_tested TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS compliance_notfallplan_checklists (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR(100) NOT NULL DEFAULT 'default',
    scenario_id UUID REFERENCES compliance_notfallplan_scenarios(id) ON DELETE SET NULL,
    title TEXT NOT NULL,
    description TEXT,
    order_index INTEGER DEFAULT 0,
    is_required BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS compliance_notfallplan_exercises (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR(100) NOT NULL DEFAULT 'default',
    title TEXT NOT NULL,
    scenario_id UUID,
    exercise_type VARCHAR(50) DEFAULT 'tabletop',
    exercise_date TIMESTAMP,
    participants JSONB DEFAULT '[]',
    outcome VARCHAR(50),
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_notfallplan_contacts_tenant ON compliance_notfallplan_contacts(tenant_id);
CREATE INDEX IF NOT EXISTS idx_notfallplan_scenarios_tenant ON compliance_notfallplan_scenarios(tenant_id);