-- Branch-wise Role Permissions Table
-- Allows different permissions for same role in different branches

CREATE TABLE IF NOT EXISTS `site_role_permissions` (
  `site_role_permission_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int(11),
  PRIMARY KEY (`site_role_permission_id`),
  UNIQUE KEY `unique_site_role_permission` (`site_id`,`role_id`,`permission_id`),
  KEY `fk_site_role_permission_site` (`site_id`),
  KEY `fk_site_role_permission_role` (`role_id`),
  KEY `fk_site_role_permission_permission` (`permission_id`),
  KEY `fk_site_role_permission_created_by` (`created_by`),
  CONSTRAINT `fk_site_role_permission_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_site_role_permission_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_site_role_permission_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`permission_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_site_role_permission_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

