nxtgauge-backend-rust/crates/db/migrations/20260419235959_external_role_module_tables_early.up.sql

-- 20260420000003_external_role_modules.up.sql was renamed to .up.sql.skip
-- (its version slot was already claimed by ...seed.sql), which meant this
-- schema was never actually created by any active migration -- only the
-- seed data that depends on it (persona_types, modules, module_actions,
-- role_module_widgets) runs. Any fresh bootstrap hits "relation ... does
-- not exist" at that seed step. This is the .skip file's content, applied
-- one migration earlier under its own version so the seed step succeeds.
-- Verified: purely additive (CREATE TABLE/INDEX IF NOT EXISTS, ADD COLUMN
-- IF NOT EXISTS throughout), safe no-op on any environment that already
-- has this schema by other means.

-- ============================================
-- ADD COLUMNS TO ROLES for external role settings
-- ============================================
ALTER TABLE roles ADD COLUMN IF NOT EXISTS persona_type varchar(50);
ALTER TABLE roles ADD COLUMN IF NOT EXISTS onboarding_schema_key varchar(100);
ALTER TABLE roles ADD COLUMN IF NOT EXISTS verification_required boolean DEFAULT true;
ALTER TABLE roles ADD COLUMN IF NOT EXISTS switch_services_enabled boolean DEFAULT false;
ALTER TABLE roles ADD COLUMN IF NOT EXISTS is_publicly_discoverable boolean DEFAULT true;
ALTER TABLE roles ADD COLUMN IF NOT EXISTS external_role_description text;
ALTER TABLE roles ADD COLUMN IF NOT EXISTS sort_order integer DEFAULT 0;

-- ============================================
-- persona_types (categories for external roles)
-- ============================================
CREATE TABLE IF NOT EXISTS persona_types (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    code varchar(50) UNIQUE NOT NULL,
    name varchar(100) NOT NULL,
    description text,
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT NOW(),
    updated_at timestamptz DEFAULT NOW()
);

-- ============================================
-- modules (module registry)
-- ============================================
CREATE TABLE IF NOT EXISTS modules (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    module_key varchar(50) UNIQUE NOT NULL,
    module_name varchar(100) NOT NULL,
    category varchar(50), -- core/content/marketplace/work/financial
    description text,
    backend_domain varchar(100),
    default_route varchar(255),
    default_sidebar_label varchar(100),
    icon_key varchar(50),
    is_core boolean DEFAULT false,
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT NOW(),
    updated_at timestamptz DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_modules_category ON modules(category);
CREATE INDEX IF NOT EXISTS idx_modules_active ON modules(is_active);

-- ============================================
-- role_module_access (module visibility per role)
-- ============================================
CREATE TABLE IF NOT EXISTS role_module_access (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    module_id uuid NOT NULL REFERENCES modules(id) ON DELETE CASCADE,
    is_enabled boolean DEFAULT true,
    is_sidebar_visible boolean DEFAULT true,
    sidebar_label_override varchar(100),
    route_override varchar(255),
    sort_order integer DEFAULT 0,
    created_at timestamptz DEFAULT NOW(),
    UNIQUE(role_id, module_id)
);

CREATE INDEX IF NOT EXISTS idx_role_module_access_role ON role_module_access(role_id);
CREATE INDEX IF NOT EXISTS idx_role_module_access_module ON role_module_access(module_id);

-- ============================================
-- module_actions (CRUD actions per module)
-- ============================================
CREATE TABLE IF NOT EXISTS module_actions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    module_id uuid NOT NULL REFERENCES modules(id) ON DELETE CASCADE,
    action_key varchar(50) NOT NULL,
    action_name varchar(100) NOT NULL,
    description text,
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT NOW(),
    UNIQUE(module_id, action_key)
);

CREATE INDEX IF NOT EXISTS idx_module_actions_module ON module_actions(module_id);

-- ============================================
-- role_module_permissions (permissions per module per role)
-- ============================================
CREATE TABLE IF NOT EXISTS role_module_permissions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    module_id uuid NOT NULL REFERENCES modules(id) ON DELETE CASCADE,
    can_view boolean DEFAULT false,
    can_list boolean DEFAULT false,
    can_create boolean DEFAULT false,
    can_update boolean DEFAULT false,
    can_delete boolean DEFAULT false,
    extra_actions_json jsonb DEFAULT '{}',
    created_at timestamptz DEFAULT NOW(),
    UNIQUE(role_id, module_id)
);

CREATE INDEX IF NOT EXISTS idx_role_module_permissions_role ON role_module_permissions(role_id);
CREATE INDEX IF NOT EXISTS idx_role_module_permissions_module ON role_module_permissions(module_id);

-- ============================================
-- role_module_widgets (widgets per module per role)
-- ============================================
CREATE TABLE IF NOT EXISTS role_module_widgets (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    module_id uuid NOT NULL REFERENCES modules(id) ON DELETE CASCADE,
    widget_key varchar(50),
    is_enabled boolean DEFAULT true,
    sort_order integer DEFAULT 0,
    created_at timestamptz DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_role_module_widgets_role ON role_module_widgets(role_id);
CREATE INDEX IF NOT EXISTS idx_role_module_widgets_module ON role_module_widgets(module_id);

-- ============================================
-- module_variants (role-specific module variants)
-- ============================================
CREATE TABLE IF NOT EXISTS module_variants (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    module_id uuid NOT NULL REFERENCES modules(id) ON DELETE CASCADE,
    variant_key varchar(50) NOT NULL,
    variant_name varchar(100) NOT NULL,
    role_code varchar(50), -- target role (e.g., PHOTOGRAPHER, TUTOR)
    persona_type varchar(50), -- target persona (e.g., PROFESSIONAL)
    schema_key varchar(100),
    ui_template_key varchar(100),
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT NOW(),
    updated_at timestamptz DEFAULT NOW(),
    UNIQUE(module_id, variant_key)
);

CREATE INDEX IF NOT EXISTS idx_module_variants_module ON module_variants(module_id);
CREATE INDEX IF NOT EXISTS idx_module_variants_role ON module_variants(role_code);

-- ============================================
-- role_module_variant_mapping (which variants a role uses)
-- ============================================
CREATE TABLE IF NOT EXISTS role_module_variant_mapping (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    module_id uuid NOT NULL REFERENCES modules(id) ON DELETE CASCADE,
    module_variant_id uuid NOT NULL REFERENCES module_variants(id) ON DELETE CASCADE,
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT NOW(),
    UNIQUE(role_id, module_id, module_variant_id)
);

CREATE INDEX IF NOT EXISTS idx_role_module_variant_mapping_role ON role_module_variant_mapping(role_id);
CREATE INDEX IF NOT EXISTS idx_role_module_variant_mapping_module ON role_module_variant_mapping(module_id);