-- Create red team notifications table
CREATE TABLE red_team_notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
    red_team_task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    related_task_id UUID REFERENCES tasks(id) ON DELETE SET NULL,

    message TEXT NOT NULL,
    severity VARCHAR(20) NOT NULL DEFAULT 'medium',
    file_path TEXT,
    context TEXT,

    -- Delivery status
    delivered BOOLEAN NOT NULL DEFAULT FALSE,
    delivered_at TIMESTAMPTZ,
    acknowledged BOOLEAN NOT NULL DEFAULT FALSE,
    acknowledged_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_red_team_notifications_contract_id ON red_team_notifications(contract_id);
CREATE INDEX idx_red_team_notifications_red_team_task_id ON red_team_notifications(red_team_task_id);
CREATE INDEX idx_red_team_notifications_created_at ON red_team_notifications(created_at DESC);

COMMENT ON TABLE red_team_notifications IS 'Audit log of notifications sent from red team tasks to supervisors';