breakpilot-compliance/ai-compliance-sdk/migrations/006_workshop_schema.sql

-- ============================================================================
-- Migration 006: Workshop Session Schema
-- Collaborative Compliance Workshop Sessions
-- ============================================================================

-- Workshop sessions table
CREATE TABLE IF NOT EXISTS workshop_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    namespace_id UUID REFERENCES compliance_namespaces(id) ON DELETE SET NULL,

    -- Session info
    title VARCHAR(255) NOT NULL,
    description TEXT,
    session_type VARCHAR(50) NOT NULL, -- 'ucca', 'dsfa', 'custom'
    status VARCHAR(50) DEFAULT 'DRAFT', -- DRAFT, SCHEDULED, ACTIVE, PAUSED, COMPLETED, CANCELLED

    -- Wizard configuration
    wizard_schema VARCHAR(100), -- Reference to wizard schema version
    current_step INT DEFAULT 1,
    total_steps INT DEFAULT 10,

    -- Links to other entities
    assessment_id UUID REFERENCES ucca_assessments(id) ON DELETE SET NULL,
    roadmap_id UUID REFERENCES roadmaps(id) ON DELETE SET NULL,
    portfolio_id UUID, -- Will reference portfolio table when created

    -- Scheduling
    scheduled_start TIMESTAMPTZ,
    scheduled_end TIMESTAMPTZ,
    actual_start TIMESTAMPTZ,
    actual_end TIMESTAMPTZ,

    -- Access control
    join_code VARCHAR(10) NOT NULL UNIQUE,
    require_auth BOOLEAN DEFAULT FALSE,
    allow_anonymous BOOLEAN DEFAULT TRUE,

    -- Settings (JSONB)
    settings JSONB DEFAULT '{}',

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Workshop participants table
CREATE TABLE IF NOT EXISTS workshop_participants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES workshop_sessions(id) ON DELETE CASCADE,
    user_id UUID, -- Null for anonymous participants

    -- Info
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    role VARCHAR(50) DEFAULT 'STAKEHOLDER', -- FACILITATOR, EXPERT, STAKEHOLDER, OBSERVER
    department VARCHAR(255),

    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    last_active_at TIMESTAMPTZ,
    joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    left_at TIMESTAMPTZ,

    -- Permissions
    can_edit BOOLEAN DEFAULT TRUE,
    can_comment BOOLEAN DEFAULT TRUE,
    can_approve BOOLEAN DEFAULT FALSE
);

-- Workshop step progress table
CREATE TABLE IF NOT EXISTS workshop_step_progress (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES workshop_sessions(id) ON DELETE CASCADE,
    step_number INT NOT NULL,

    -- Status
    status VARCHAR(50) DEFAULT 'pending', -- pending, in_progress, completed, skipped
    progress INT DEFAULT 0, -- 0-100

    -- Timestamps
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,

    -- Notes
    notes TEXT,

    UNIQUE(session_id, step_number)
);

-- Workshop responses table
CREATE TABLE IF NOT EXISTS workshop_responses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES workshop_sessions(id) ON DELETE CASCADE,
    participant_id UUID NOT NULL REFERENCES workshop_participants(id) ON DELETE CASCADE,

    -- Question reference
    step_number INT NOT NULL,
    field_id VARCHAR(100) NOT NULL,

    -- Response data
    value JSONB, -- Can be any JSON type
    value_type VARCHAR(50), -- string, boolean, array, number, object

    -- Status
    status VARCHAR(50) DEFAULT 'SUBMITTED', -- PENDING, DRAFT, SUBMITTED, REVIEWED

    -- Review
    reviewed_by UUID,
    reviewed_at TIMESTAMPTZ,
    review_notes TEXT,

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Unique constraint per participant per field
    UNIQUE(session_id, participant_id, field_id)
);

-- Workshop comments table
CREATE TABLE IF NOT EXISTS workshop_comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES workshop_sessions(id) ON DELETE CASCADE,
    participant_id UUID NOT NULL REFERENCES workshop_participants(id) ON DELETE CASCADE,

    -- Target (one of these should be set)
    step_number INT,
    field_id VARCHAR(100),
    response_id UUID REFERENCES workshop_responses(id) ON DELETE CASCADE,

    -- Content
    text TEXT NOT NULL,
    is_resolved BOOLEAN DEFAULT FALSE,

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================================
-- Indexes
-- ============================================================================

-- Session indexes
CREATE INDEX IF NOT EXISTS idx_workshop_sessions_tenant ON workshop_sessions(tenant_id);
CREATE INDEX IF NOT EXISTS idx_workshop_sessions_status ON workshop_sessions(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_workshop_sessions_join_code ON workshop_sessions(join_code);
CREATE INDEX IF NOT EXISTS idx_workshop_sessions_assessment ON workshop_sessions(assessment_id) WHERE assessment_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_workshop_sessions_created_by ON workshop_sessions(created_by);

-- Participant indexes
CREATE INDEX IF NOT EXISTS idx_workshop_participants_session ON workshop_participants(session_id);
CREATE INDEX IF NOT EXISTS idx_workshop_participants_user ON workshop_participants(user_id) WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_workshop_participants_active ON workshop_participants(session_id, is_active);

-- Step progress indexes
CREATE INDEX IF NOT EXISTS idx_workshop_step_progress_session ON workshop_step_progress(session_id);

-- Response indexes
CREATE INDEX IF NOT EXISTS idx_workshop_responses_session ON workshop_responses(session_id);
CREATE INDEX IF NOT EXISTS idx_workshop_responses_participant ON workshop_responses(participant_id);
CREATE INDEX IF NOT EXISTS idx_workshop_responses_step ON workshop_responses(session_id, step_number);
CREATE INDEX IF NOT EXISTS idx_workshop_responses_field ON workshop_responses(session_id, field_id);

-- Comment indexes
CREATE INDEX IF NOT EXISTS idx_workshop_comments_session ON workshop_comments(session_id);
CREATE INDEX IF NOT EXISTS idx_workshop_comments_response ON workshop_comments(response_id) WHERE response_id IS NOT NULL;

-- ============================================================================
-- Triggers
-- ============================================================================

-- Reuse existing update_updated_at_column function

-- Sessions trigger
DROP TRIGGER IF EXISTS update_workshop_sessions_updated_at ON workshop_sessions;
CREATE TRIGGER update_workshop_sessions_updated_at
    BEFORE UPDATE ON workshop_sessions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Responses trigger
DROP TRIGGER IF EXISTS update_workshop_responses_updated_at ON workshop_responses;
CREATE TRIGGER update_workshop_responses_updated_at
    BEFORE UPDATE ON workshop_responses
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Comments trigger
DROP TRIGGER IF EXISTS update_workshop_comments_updated_at ON workshop_comments;
CREATE TRIGGER update_workshop_comments_updated_at
    BEFORE UPDATE ON workshop_comments
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- Comments
-- ============================================================================

COMMENT ON TABLE workshop_sessions IS 'Collaborative compliance workshop sessions';
COMMENT ON TABLE workshop_participants IS 'Participants in workshop sessions';
COMMENT ON TABLE workshop_step_progress IS 'Progress tracking for each wizard step';
COMMENT ON TABLE workshop_responses IS 'Participant responses to wizard questions';
COMMENT ON TABLE workshop_comments IS 'Comments and discussions on responses';

COMMENT ON COLUMN workshop_sessions.join_code IS 'Code for participants to join the session';
COMMENT ON COLUMN workshop_sessions.settings IS 'JSON settings (allow_back_navigation, require_all_responses, etc.)';
COMMENT ON COLUMN workshop_responses.value IS 'JSON response value (can be any type)';