-- ============================================================
-- Additional named roles requested by the client.
-- Idempotent — safe to re-run. Run from phpMyAdmin SQL tab,
-- or `mysql -u root medmecs_mis < sql/seed-additional-roles.sql`.
-- ============================================================
USE medmecs_mis;

INSERT INTO `roles` (`code`,`name`,`description`,`is_system`,`scope`,`created_at`,`updated_at`) VALUES
  ('manager',    'Manager',    'Manages users + uploads + downloads on assigned reports',    1,'internal',NOW(),NOW()),
  ('supervisor', 'Supervisor', 'Reviews uploads and approves data revisions',                1,'internal',NOW(),NOW()),
  ('user',       'User',       'Standard data-entry user — view + upload + append on assigned reports', 1,'internal',NOW(),NOW())
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`description`=VALUES(`description`),`updated_at`=NOW();

-- Manager: view + upload + download + replace + access view + internal/client user management
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'client.view','process.view','process.manage',
      'user.client.view','user.client.manage',
      'user.internal.view','user.internal.manage',
      'report.definition.view','report.view','report.view_detail',
      'report.upload','report.replace_all','report.download',
      'report.access.view','audit.view'
    )
  WHERE r.code = 'manager';

-- Supervisor: view + upload + download + restore (review snapshots) — no replace/delete
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'client.view','process.view',
      'user.internal.view','user.client.view',
      'report.definition.view','report.view','report.view_detail',
      'report.upload','report.download','report.restore',
      'audit.view'
    )
  WHERE r.code = 'supervisor';

-- User: minimal upload/download set
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'report.view','report.view_detail','report.upload','report.download'
    )
  WHERE r.code = 'user';
