CREATE TABLE IF NOT EXISTS `permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `permission_slug` varchar(50) NOT NULL UNIQUE,
  `description` varchar(100) NOT NULL,
  `category` varchar(50) DEFAULT 'General',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role` varchar(20) NOT NULL,
  `permission_slug` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_perm` (`role`, `permission_slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Insert Base Permissions
INSERT IGNORE INTO `permissions` (`permission_slug`, `description`, `category`) VALUES
('manage_products', 'Create, Edit, and Delete Products', 'Inventory'),
('manage_orders', 'View and Process Orders', 'Sales'),
('manage_users', 'Manage Customer Accounts', 'Users'),
('manage_staff', 'Manage Staff & Admin Accounts', 'Users'),
('manage_settings', 'Configure Global Site Settings', 'System'),
('manage_shipping_overrides', 'Set Product-Specific Shipping Fees', 'Inventory'),
('view_reports', 'View Sales and Revenue Reports', 'System');

-- Grant All to Admin
INSERT IGNORE INTO `role_permissions` (`role`, `permission_slug`) 
SELECT 'admin', permission_slug FROM permissions;

-- Grant Defaults to Staff
INSERT IGNORE INTO `role_permissions` (`role`, `permission_slug`) VALUES 
('staff', 'manage_products'),
('staff', 'manage_orders'),
('staff', 'manage_shipping_overrides'),
('staff', 'view_reports');

-- Grant Defaults to Sales
INSERT IGNORE INTO `role_permissions` (`role`, `permission_slug`) VALUES 
('sales', 'manage_orders'),
('sales', 'view_reports');
