-- MAY SAFE BOX: upgrade module permissions to Create / Read / Update / Delete
-- Safe to import more than once. Existing broad access grants are copied to
-- all corresponding CRUD permissions so current users keep their access.
-- No application tables or columns are changed.

START TRANSACTION;

INSERT IGNORE INTO `permissions` (`name`, `guard_name`, `created_at`, `updated_at`) VALUES
('read_dashboard', 'web', NOW(), NOW()),
('create_customers', 'web', NOW(), NOW()),
('read_customers', 'web', NOW(), NOW()),
('update_customers', 'web', NOW(), NOW()),
('delete_customers', 'web', NOW(), NOW()),
('create_safe_boxes', 'web', NOW(), NOW()),
('read_safe_boxes', 'web', NOW(), NOW()),
('update_safe_boxes', 'web', NOW(), NOW()),
('delete_safe_boxes', 'web', NOW(), NOW()),
('create_appointments', 'web', NOW(), NOW()),
('read_appointments', 'web', NOW(), NOW()),
('update_appointments', 'web', NOW(), NOW()),
('delete_appointments', 'web', NOW(), NOW()),
('create_payments', 'web', NOW(), NOW()),
('read_payments', 'web', NOW(), NOW()),
('update_payments', 'web', NOW(), NOW()),
('delete_payments', 'web', NOW(), NOW()),
('create_reminders', 'web', NOW(), NOW()),
('read_reminders', 'web', NOW(), NOW()),
('update_reminders', 'web', NOW(), NOW()),
('delete_reminders', 'web', NOW(), NOW()),
('create_holidays', 'web', NOW(), NOW()),
('read_holidays', 'web', NOW(), NOW()),
('update_holidays', 'web', NOW(), NOW()),
('delete_holidays', 'web', NOW(), NOW()),
('create_logs', 'web', NOW(), NOW()),
('read_logs', 'web', NOW(), NOW()),
('update_logs', 'web', NOW(), NOW()),
('delete_logs', 'web', NOW(), NOW());

-- Copy role assignments from each legacy access permission to its CRUD permissions.
INSERT IGNORE INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT new_permission.id, existing_assignment.role_id
FROM `role_has_permissions` existing_assignment
INNER JOIN `permissions` legacy_permission
    ON legacy_permission.id = existing_assignment.permission_id
   AND legacy_permission.guard_name = 'web'
INNER JOIN (
    SELECT 'access_dashboard' legacy_name, 'read_dashboard' new_name
    UNION ALL SELECT 'access_customers', 'create_customers'
    UNION ALL SELECT 'access_customers', 'read_customers'
    UNION ALL SELECT 'access_customers', 'update_customers'
    UNION ALL SELECT 'access_customers', 'delete_customers'
    UNION ALL SELECT 'access_safe_boxes', 'create_safe_boxes'
    UNION ALL SELECT 'access_safe_boxes', 'read_safe_boxes'
    UNION ALL SELECT 'access_safe_boxes', 'update_safe_boxes'
    UNION ALL SELECT 'access_safe_boxes', 'delete_safe_boxes'
    UNION ALL SELECT 'access_appointments', 'create_appointments'
    UNION ALL SELECT 'access_appointments', 'read_appointments'
    UNION ALL SELECT 'access_appointments', 'update_appointments'
    UNION ALL SELECT 'access_appointments', 'delete_appointments'
    UNION ALL SELECT 'access_payments', 'create_payments'
    UNION ALL SELECT 'access_payments', 'read_payments'
    UNION ALL SELECT 'access_payments', 'update_payments'
    UNION ALL SELECT 'access_payments', 'delete_payments'
    UNION ALL SELECT 'access_reminders', 'create_reminders'
    UNION ALL SELECT 'access_reminders', 'read_reminders'
    UNION ALL SELECT 'access_reminders', 'update_reminders'
    UNION ALL SELECT 'access_reminders', 'delete_reminders'
    UNION ALL SELECT 'access_holidays', 'create_holidays'
    UNION ALL SELECT 'access_holidays', 'read_holidays'
    UNION ALL SELECT 'access_holidays', 'update_holidays'
    UNION ALL SELECT 'access_holidays', 'delete_holidays'
    UNION ALL SELECT 'access_logs', 'create_logs'
    UNION ALL SELECT 'access_logs', 'read_logs'
    UNION ALL SELECT 'access_logs', 'update_logs'
    UNION ALL SELECT 'access_logs', 'delete_logs'
) permission_map ON permission_map.legacy_name = legacy_permission.name
INNER JOIN `permissions` new_permission
    ON new_permission.name = permission_map.new_name
   AND new_permission.guard_name = 'web';

-- Copy direct user/model assignments in the same way.
INSERT IGNORE INTO `model_has_permissions` (`permission_id`, `model_type`, `model_id`)
SELECT new_permission.id, existing_assignment.model_type, existing_assignment.model_id
FROM `model_has_permissions` existing_assignment
INNER JOIN `permissions` legacy_permission
    ON legacy_permission.id = existing_assignment.permission_id
   AND legacy_permission.guard_name = 'web'
INNER JOIN (
    SELECT 'access_dashboard' legacy_name, 'read_dashboard' new_name
    UNION ALL SELECT 'access_customers', 'create_customers'
    UNION ALL SELECT 'access_customers', 'read_customers'
    UNION ALL SELECT 'access_customers', 'update_customers'
    UNION ALL SELECT 'access_customers', 'delete_customers'
    UNION ALL SELECT 'access_safe_boxes', 'create_safe_boxes'
    UNION ALL SELECT 'access_safe_boxes', 'read_safe_boxes'
    UNION ALL SELECT 'access_safe_boxes', 'update_safe_boxes'
    UNION ALL SELECT 'access_safe_boxes', 'delete_safe_boxes'
    UNION ALL SELECT 'access_appointments', 'create_appointments'
    UNION ALL SELECT 'access_appointments', 'read_appointments'
    UNION ALL SELECT 'access_appointments', 'update_appointments'
    UNION ALL SELECT 'access_appointments', 'delete_appointments'
    UNION ALL SELECT 'access_payments', 'create_payments'
    UNION ALL SELECT 'access_payments', 'read_payments'
    UNION ALL SELECT 'access_payments', 'update_payments'
    UNION ALL SELECT 'access_payments', 'delete_payments'
    UNION ALL SELECT 'access_reminders', 'create_reminders'
    UNION ALL SELECT 'access_reminders', 'read_reminders'
    UNION ALL SELECT 'access_reminders', 'update_reminders'
    UNION ALL SELECT 'access_reminders', 'delete_reminders'
    UNION ALL SELECT 'access_holidays', 'create_holidays'
    UNION ALL SELECT 'access_holidays', 'read_holidays'
    UNION ALL SELECT 'access_holidays', 'update_holidays'
    UNION ALL SELECT 'access_holidays', 'delete_holidays'
    UNION ALL SELECT 'access_logs', 'create_logs'
    UNION ALL SELECT 'access_logs', 'read_logs'
    UNION ALL SELECT 'access_logs', 'update_logs'
    UNION ALL SELECT 'access_logs', 'delete_logs'
) permission_map ON permission_map.legacy_name = legacy_permission.name
INNER JOIN `permissions` new_permission
    ON new_permission.name = permission_map.new_name
   AND new_permission.guard_name = 'web';

-- Super Admin always receives every new CRUD permission.
INSERT IGNORE INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT permission.id, role.id
FROM `permissions` permission
INNER JOIN `roles` role
    ON role.name = 'Super Admin'
   AND role.guard_name = 'web'
WHERE permission.guard_name = 'web'
  AND permission.name IN (
    'read_dashboard',
    'create_customers', 'read_customers', 'update_customers', 'delete_customers',
    'create_safe_boxes', 'read_safe_boxes', 'update_safe_boxes', 'delete_safe_boxes',
    'create_appointments', 'read_appointments', 'update_appointments', 'delete_appointments',
    'create_payments', 'read_payments', 'update_payments', 'delete_payments',
    'create_reminders', 'read_reminders', 'update_reminders', 'delete_reminders',
    'create_holidays', 'read_holidays', 'update_holidays', 'delete_holidays',
    'create_logs', 'read_logs', 'update_logs', 'delete_logs'
  );

COMMIT;

-- After importing on live, clear the cached permission map:
-- php artisan permission:cache-reset