-- =============================================================================
-- 05-permission-scope-updates.sql
-- Migrations: UpgradeLegacyScopeValues, OwnerScopeValuesAll, OwnerScopeUseAllMembers
-- Run AFTER access_roles + role_permissions are seeded.
-- Only updates permission_value rows; does not delete data.
-- Safe to run multiple times (idempotent for target states).
-- =============================================================================

-- UpgradeLegacyScopeValues1780920000000
UPDATE role_permissions rp
INNER JOIN access_roles ar ON ar.id = rp.role_id
SET rp.permission_value = 'own_and_all'
WHERE ar.slug IN ('owner', 'admin')
  AND rp.permission_value = 'all'
  AND rp.permission_key IN (
    'dashboard.view',
    'members.view',
    'members.update',
    'members.delete',
    'attendance.view',
    'leave.view',
    'leave.create',
    'leave.update',
    'leave.delete',
    'reports.view',
    'calendar.view',
    'holidays.view',
    'holidays.create',
    'holidays.update',
    'holidays.delete',
    'notice.view',
    'notice.create',
    'notice.update',
    'notice.delete'
  );

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

-- OwnerScopeValuesAll1780930000000
UPDATE role_permissions rp
INNER JOIN access_roles ar ON ar.id = rp.role_id
SET rp.permission_value = 'all'
WHERE ar.slug = 'owner'
  AND rp.permission_value IN ('own_and_all', 'own')
  AND rp.permission_key IN (
    'dashboard.view',
    'members.view',
    'members.update',
    'members.delete',
    'attendance.view',
    'leave.view',
    'leave.create',
    'leave.update',
    'leave.delete',
    'reports.view',
    'calendar.view',
    'holidays.view',
    'holidays.create',
    'holidays.update',
    'holidays.delete',
    'notice.view',
    'notice.create',
    'notice.update',
    'notice.delete'
  );

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

-- OwnerScopeUseAllMembers1780940000000
UPDATE role_permissions rp
INNER JOIN access_roles ar ON ar.id = rp.role_id
SET rp.permission_value = 'all'
WHERE ar.slug = 'owner'
  AND rp.permission_value = 'own_and_all'
  AND rp.permission_key IN (
    'dashboard.view',
    'members.view',
    'members.update',
    'members.delete',
    'attendance.view',
    'leave.view',
    'leave.create',
    'leave.update',
    'leave.delete',
    'reports.view',
    'calendar.view',
    'holidays.view',
    'holidays.create',
    'holidays.update',
    'holidays.delete',
    'notice.view',
    'notice.create',
    'notice.update',
    'notice.delete'
  );

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

SELECT name FROM migrations WHERE timestamp >= 1780900000000 ORDER BY timestamp;
