-- 1. Create staff_roles table
CREATE TABLE IF NOT EXISTS `staff_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `permissions` text NOT NULL, -- JSON like ["all"] or ["users", "tournaments"]
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Add Super Admin Role
INSERT IGNORE INTO `staff_roles` (`id`, `title`, `permissions`) VALUES
(1, 'Super Admin', '["all"]');

-- 3. Upgrade admins table
ALTER TABLE `admins` 
ADD COLUMN `name` VARCHAR(100) DEFAULT 'Admin User' AFTER `id`,
ADD COLUMN `email` VARCHAR(100) DEFAULT 'admin@example.com' AFTER `name`,
ADD COLUMN `role_id` INT(11) DEFAULT 1 AFTER `pin`;

-- 4. Update existing admin to have Super Admin role
UPDATE `admins` SET `role_id` = 1, `email` = 'admin@example.com' WHERE `id` = 1;
