-- =============================================================================
-- 02-access-control-schema.sql
-- Migration: AccessControl1780900000000
-- Requires: 00b-cpanel-helpers.sql (cp_safe_exec)
-- cPanel safe. Safe to run multiple times.
-- =============================================================================

CREATE TABLE IF NOT EXISTS `access_roles` (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `owner_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(191) NOT NULL,
  `slug` varchar(191) NOT NULL,
  `description` varchar(191) NOT NULL DEFAULT 'NT/APL',
  `is_system` tinyint NOT NULL DEFAULT 0,
  `hierarchy` int NOT NULL DEFAULT 0,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE INDEX `IDX_access_roles_owner_slug` (`owner_id`, `slug`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `role_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `permission_key` varchar(191) NOT NULL,
  `permission_value` varchar(191) NOT NULL,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE INDEX `IDX_role_permissions_role_key` (`role_id`, `permission_key`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CALL cp_safe_exec('ALTER TABLE `access_roles` ADD CONSTRAINT `FK_access_roles_owner` FOREIGN KEY (`owner_id`) REFERENCES `owner`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION');

CALL cp_safe_exec('ALTER TABLE `role_permissions` ADD CONSTRAINT `FK_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `access_roles`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION');

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

SELECT 'access_roles' AS tbl, COUNT(*) AS row_count FROM access_roles
UNION ALL
SELECT 'role_permissions', COUNT(*) FROM role_permissions;
