-- =============================================================================
-- 12-attendance-request-permissions.sql
-- Migration: AttendanceRequestPermissions1781020000000
-- Adds attendance request permissions to existing roles.
-- Run AFTER access_roles + role_permissions are seeded (steps 02-05).
-- Only inserts missing permission rows; does not delete data.
-- Safe to run multiple times.
-- =============================================================================

-- Member role
INSERT INTO role_permissions (id, role_id, permission_key, permission_value)
SELECT UUID(), ar.id, 'attendance.request.create', 'own'
FROM access_roles ar
WHERE ar.slug = 'member'
  AND NOT EXISTS (
    SELECT 1
    FROM role_permissions rp
    WHERE rp.role_id = ar.id
      AND rp.permission_key = 'attendance.request.create'
  );

INSERT INTO role_permissions (id, role_id, permission_key, permission_value)
SELECT UUID(), ar.id, 'attendance.request.view', 'own'
FROM access_roles ar
WHERE ar.slug = 'member'
  AND NOT EXISTS (
    SELECT 1
    FROM role_permissions rp
    WHERE rp.role_id = ar.id
      AND rp.permission_key = 'attendance.request.view'
  );

INSERT INTO role_permissions (id, role_id, permission_key, permission_value)
SELECT UUID(), ar.id, 'attendance.request.approve', 'false'
FROM access_roles ar
WHERE ar.slug = 'member'
  AND NOT EXISTS (
    SELECT 1
    FROM role_permissions rp
    WHERE rp.role_id = ar.id
      AND rp.permission_key = 'attendance.request.approve'
  );

-- Admin + owner roles
INSERT INTO role_permissions (id, role_id, permission_key, permission_value)
SELECT UUID(), ar.id, perm.permission_key, perm.permission_value
FROM access_roles ar
INNER JOIN (
  SELECT 'attendance.request.create' AS permission_key, 'all' AS permission_value
  UNION ALL SELECT 'attendance.request.view', 'all'
  UNION ALL SELECT 'attendance.request.approve', 'true'
) perm
WHERE ar.slug IN ('admin', 'owner')
  AND NOT EXISTS (
    SELECT 1
    FROM role_permissions rp
    WHERE rp.role_id = ar.id
      AND rp.permission_key = perm.permission_key
  );

INSERT INTO migrations (timestamp, name)
SELECT 1781020000000, 'AttendanceRequestPermissions1781020000000'
WHERE NOT EXISTS (
  SELECT 1 FROM migrations WHERE name = 'AttendanceRequestPermissions1781020000000'
);

SELECT ar.slug, rp.permission_key, rp.permission_value
FROM role_permissions rp
INNER JOIN access_roles ar ON ar.id = rp.role_id
WHERE rp.permission_key IN (
  'attendance.request.create',
  'attendance.request.view',
  'attendance.request.approve'
)
ORDER BY ar.slug, rp.permission_key;
