-- ═══════════════════════════════════════════════════════════════
-- Seed: Roles and Permissions
-- Type: Data
-- Version: 2026-05-02
-- Purpose: Create default roles, permissions, and role-permission assignments
--
-- This seed:
-- - Creates default roles (user, admin, system_admin) with multi-tenant support
-- - Creates granular permissions following action:resource convention
-- - Assigns permissions to roles via role_permissions junction table
-- - Idempotent: uses ON CONFLICT to handle re-runs
--
-- Permission key convention: action:resource (e.g. read:user, manage:role)
--
-- Dependencies: 001-auth-schema.sql
-- ═══════════════════════════════════════════════════════════════

-- ─────────────────────────────────────────────────────────────
-- Section 1: Roles
-- ─────────────────────────────────────────────────────────────

INSERT INTO roles (name, description, tenant_id, is_system_role, scope, created_at, updated_at) VALUES
    (
        'user',
        'Standard user with basic permissions',
        NULL,
        TRUE,
        'global',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        'admin',
        'Administrator with elevated permissions',
        NULL,
        TRUE,
        'global',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    ),
    (
        'system_admin',
        'System administrator with full access',
        NULL,
        TRUE,
        'global',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    )
ON CONFLICT (tenant_id, name) DO UPDATE SET
    description = EXCLUDED.description,
    is_system_role = EXCLUDED.is_system_role,
    scope = EXCLUDED.scope,
    updated_at  = CURRENT_TIMESTAMP;

-- ─────────────────────────────────────────────────────────────
-- Section 2: Permissions (action:resource convention)
-- ─────────────────────────────────────────────────────────────

INSERT INTO permissions (key, resource, action, name, description, created_at, updated_at) VALUES
    -- Profile
    ('read:profile',      'profile',      'read',    'Read Profile',      'View own profile',             CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('update:profile',    'profile',      'update',  'Update Profile',    'Update own profile',           CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),

    -- User management
    ('read:user',         'user',         'read',    'Read User',         'View any user',                CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('create:user',       'user',         'create',  'Create User',       'Create new user',              CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('update:user',       'user',         'update',  'Update User',       'Update any user',              CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('delete:user',       'user',         'delete',  'Delete User',       'Delete user',                  CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('manage:user',       'user',         'manage',  'Manage User',       'Full access to users',         CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),

    -- Role management
    ('read:role',         'role',         'read',    'Read Role',         'View roles',                   CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('create:role',       'role',         'create',  'Create Role',       'Create roles',                 CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('update:role',       'role',         'update',  'Update Role',       'Update roles',                 CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('delete:role',       'role',         'delete',  'Delete Role',       'Delete roles',                 CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('manage:role',       'role',         'manage',  'Manage Role',       'Full access to roles',         CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),

    -- Permission management
    ('read:permission',   'permission',   'read',    'Read Permission',   'View permissions',             CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('manage:permission', 'permission',   'manage',  'Manage Permission', 'Full permission management',   CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),

    -- Notification
    ('read:notification',   'notification', 'read',    'Read Notification',   'View notifications',           CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('manage:notification', 'notification', 'manage',  'Manage Notification', 'Full notification access',     CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),

    -- File
    ('read:file',         'file',         'read',    'Read File',         'View/download files',          CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('create:file',       'file',         'create',  'Create File',       'Upload files',                 CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('delete:file',       'file',         'delete',  'Delete File',       'Delete files',                 CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('manage:file',       'file',         'manage',  'Manage File',       'Full file management',         CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),

    -- Session
    ('read:session',      'session',      'read',    'Read Session',      'View own sessions',            CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('manage:session',    'session',      'manage',  'Manage Session',    'Manage any session',           CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON CONFLICT (key) DO UPDATE SET
    name        = EXCLUDED.name,
    description = EXCLUDED.description,
    updated_at  = CURRENT_TIMESTAMP;

-- ─────────────────────────────────────────────────────────────
-- Section 3: Role-Permission Assignments
-- ─────────────────────────────────────────────────────────────

DO $$
DECLARE
    role_user_id       UUID;
    role_admin_id      UUID;
    role_sysadmin_id   UUID;
BEGIN
    SELECT id INTO role_user_id     FROM roles WHERE name = 'user';
    SELECT id INTO role_admin_id    FROM roles WHERE name = 'admin';
    SELECT id INTO role_sysadmin_id FROM roles WHERE name = 'system_admin';

    -- ── user: basic self-service access ──────────────────────
    INSERT INTO role_permissions (role_id, permission_id, created_at, updated_at)
    SELECT role_user_id, p.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
    FROM permissions p
    WHERE p.key IN (
        'read:profile',
        'update:profile',
        'read:notification',
        'read:file',
        'create:file',
        'read:session'
    )
    ON CONFLICT (role_id, permission_id) DO NOTHING;

    -- ── admin: user & content management, no structural changes ──
    INSERT INTO role_permissions (role_id, permission_id, created_at, updated_at)
    SELECT role_admin_id, p.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
    FROM permissions p
    WHERE p.key IN (
        'read:profile',
        'update:profile',
        'read:user',
        'create:user',
        'update:user',
        'delete:user',
        'read:role',
        'read:permission',
        'read:notification',
        'manage:notification',
        'read:file',
        'create:file',
        'delete:file',
        'read:session',
        'manage:session'
    )
    ON CONFLICT (role_id, permission_id) DO NOTHING;

    -- ── system_admin: all permissions ────────────────────────
    INSERT INTO role_permissions (role_id, permission_id, created_at, updated_at)
    SELECT role_sysadmin_id, p.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
    FROM permissions p
    ON CONFLICT (role_id, permission_id) DO NOTHING;
END $$;

-- ═══════════════════════════════════════════════════════════════
-- End of Seed
-- ═══════════════════════════════════════════════════════════════
-- Role permission summary:
--   user        → read:profile, update:profile, read:notification,
--                 read:file, create:file, read:session
--   admin       → all user perms + read/write users, read roles & permissions,
--                 manage notifications, delete files, manage sessions
--   system_admin → ALL permissions (full system access)
