ALTER TABLE `{{prefix}}backup_logs`
  MODIFY `status` ENUM('created','uploaded','failed','deleted','restored') NOT NULL DEFAULT 'created';

CREATE TABLE IF NOT EXISTS `{{prefix}}system_uploads` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `category` VARCHAR(100) NOT NULL,
  `title` VARCHAR(190) NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `stored_name` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(120) NOT NULL,
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `description` TEXT NULL,
  `uploaded_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_system_upload_category` (`category`,`created_at`),
  KEY `idx_system_upload_branch` (`branch_id`,`created_at`),
  CONSTRAINT `{{prefix}}fk_system_upload_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_system_upload_user` FOREIGN KEY (`uploaded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}roles` (`code`,`name`,`description`,`created_at`) VALUES
('staff','General Staff','General patient-service support without clinical, finance, or system administration authority',NOW())
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`description`=VALUES(`description`);

UPDATE `{{prefix}}roles` SET `name`='Superuser',`description`='Protected system ownership, licensing, updates, backups, security, and organization-wide administration' WHERE `code`='super_admin';
UPDATE `{{prefix}}roles` SET `name`='Administrator',`description`='Clinic operations, staff, schedules, services, inventory oversight, and reports' WHERE `code`='clinic_admin';

INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('uploads.manage','Manage clinic files and uploads','Administration','Upload, view, and download protected clinic files')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`module_group`=VALUES(`module_group`),`description`=VALUES(`description`);

INSERT INTO `{{prefix}}role_permissions` (`role_id`,`permission_id`,`allowed`)
SELECT r.id,p.id,1 FROM `{{prefix}}roles` r JOIN `{{prefix}}permissions` p
WHERE r.code='clinic_admin' AND p.code='uploads.manage'
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

INSERT INTO `{{prefix}}role_permissions` (`role_id`,`permission_id`,`allowed`)
SELECT r.id,p.id,1 FROM `{{prefix}}roles` r JOIN `{{prefix}}permissions` p
WHERE r.code='staff' AND p.code IN ('patients.view','appointments.manage','queue.manage','communications.manage')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);
