-- Roles & Permissions feature — run this AFTER etailorz_admin_seed.sql
-- (needs shops and users tables to already exist)

-- ============================================
-- 1. PERMISSIONS (master list)
-- ============================================
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(255) NOT NULL,
  `label` VARCHAR(255) NOT NULL,
  `module` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permissions_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 2. ROLES (shop-scoped, created by shop owners)
-- ============================================
CREATE TABLE IF NOT EXISTS `roles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_shop_id_name_unique` (`shop_id`, `name`),
  CONSTRAINT `roles_shop_id_foreign` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 3. PERMISSION_ROLE (pivot)
-- ============================================
CREATE TABLE IF NOT EXISTS `permission_role` (
  `role_id` BIGINT UNSIGNED NOT NULL,
  `permission_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  CONSTRAINT `permission_role_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `permission_role_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 4. ADD role_id TO users
-- ============================================
ALTER TABLE `users`
  ADD COLUMN `role_id` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `shop_id`,
  ADD CONSTRAINT `users_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL;

-- ============================================
-- 5. SEED PERMISSIONS
-- ============================================
INSERT INTO `permissions` (`key`, `label`, `module`, `created_at`, `updated_at`) VALUES
('orders.view', 'View orders', 'Orders', NOW(), NOW()),
('orders.create', 'Create orders', 'Orders', NOW(), NOW()),
('orders.edit', 'Edit orders', 'Orders', NOW(), NOW()),
('orders.delete', 'Delete orders', 'Orders', NOW(), NOW()),
('orders.change_stage', 'Move order stage (cutting/stitching/etc.)', 'Orders', NOW(), NOW()),
('measurements.view', 'View measurements', 'Measurements', NOW(), NOW()),
('measurements.edit', 'Add/edit measurements', 'Measurements', NOW(), NOW()),
('customers.view', 'View customers', 'Customers', NOW(), NOW()),
('customers.create', 'Add customers', 'Customers', NOW(), NOW()),
('customers.edit', 'Edit customers', 'Customers', NOW(), NOW()),
('payments.view', 'View payments', 'Payments', NOW(), NOW()),
('payments.collect', 'Collect / record payments', 'Payments', NOW(), NOW()),
('products.view', 'View products & addons', 'Products', NOW(), NOW()),
('products.manage', 'Add/edit products & addons', 'Products', NOW(), NOW()),
('staff.view', 'View staff list', 'Staff', NOW(), NOW()),
('staff.manage', 'Add/edit staff & assign roles', 'Staff', NOW(), NOW());