/*
  MSIN - Acces evenement v2 (phase 2)
  Multi-kits par compte/evenement + permissions a l'unite + granularite mobile.

  Executer APRES :
    MSIN-eve-acces-v2-phase1-tables.sql
    MSIN-eve-acces-v2-phase1-seed.sql
    MSIN-eve-acces-v2-bib-qr-seed.sql (si deja applique)

  Safe en prod pilote : n'enleve aucun acces existant.
*/

SET NAMES utf8mb4;


/* ------------------------------------------------------------------
   1. Niveau permission : page | action | api | report
   ------------------------------------------------------------------ */
/* Ajout idempotent : n'ajoute la colonne que si elle n'existe pas deja
   (evite l'erreur 1060 - Duplicate column name si le script est relance). */
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'inscriptions_eve_permissions'
    AND COLUMN_NAME = 'perm_scope'
);
SET @ddl := IF(@col_exists = 0,
  'ALTER TABLE `inscriptions_eve_permissions` ADD COLUMN `perm_scope` enum(''page'',''action'',''api'',''report'') NOT NULL DEFAULT ''api'' AFTER `perm_group`',
  'DO 0');
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `inscriptions_eve_permissions` SET `perm_scope` = 'api'
  WHERE `perm_group` = 'api';

UPDATE `inscriptions_eve_permissions` SET `perm_scope` = 'report'
  WHERE `perm_group` = 'reports';

UPDATE `inscriptions_eve_permissions` SET `perm_scope` = 'page'
  WHERE `perm_key` IN (
    'inscriptions.view', 'inscriptions_gestion.view',
    'dossards.view', 'epreuves.view', 'rabais.view', 'team.view'
  );

UPDATE `inscriptions_eve_permissions` SET `perm_scope` = 'action'
  WHERE `perm_key` IN (
    'inscriptions.edit', 'dossards.manage', 'epreuves.edit_qte',
    'rabais.manage', 'emails.send', 'registrations.scan',
    'team.invite', 'team.manage', 'tools.qr_test'
  );


/* ------------------------------------------------------------------
   2. Multi-kits : plusieurs roles par compte x evenement
   ------------------------------------------------------------------ */
/* Retrait idempotent de l'ancien index unique (evite 1091 si deja retire). */
SET @idx_old := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'inscriptions_eve_acces'
    AND INDEX_NAME = 'uk_com_eve'
);
SET @ddl := IF(@idx_old > 0,
  'ALTER TABLE `inscriptions_eve_acces` DROP INDEX `uk_com_eve`',
  'DO 0');
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

/* Ajout idempotent du nouvel index unique (evite 1061 si deja present). */
SET @idx_new := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'inscriptions_eve_acces'
    AND INDEX_NAME = 'uk_com_eve_role'
);
SET @ddl := IF(@idx_new = 0,
  'ALTER TABLE `inscriptions_eve_acces` ADD UNIQUE KEY `uk_com_eve_role` (`com_id`, `eve_id`, `role_id`)',
  'DO 0');
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


/* ------------------------------------------------------------------
   3. Permissions additionnelles a l'unite (hors kit)
   ------------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS `inscriptions_eve_acces_extra` (
  `ae_id`           int(10) unsigned NOT NULL AUTO_INCREMENT,
  `com_id`          int(10) unsigned NOT NULL,
  `eve_id`          int(10) unsigned NOT NULL,
  `perm_key`        varchar(64)      NOT NULL,
  `ae_statut`       enum('actif','revoke') NOT NULL DEFAULT 'actif',
  `ae_granted_by`   int(10) unsigned DEFAULT NULL,
  `ae_note`         varchar(255) DEFAULT NULL,
  `ae_created_at`   datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ae_updated_at`   datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `ae_revoked_at`   datetime DEFAULT NULL,
  PRIMARY KEY (`ae_id`),
  UNIQUE KEY `uk_com_eve_perm` (`com_id`, `eve_id`, `perm_key`),
  KEY `idx_ae_eve_actif` (`eve_id`, `ae_statut`),
  KEY `idx_ae_com_actif` (`com_id`, `ae_statut`),
  CONSTRAINT `fk_ae_perm`
    FOREIGN KEY (`perm_key`) REFERENCES `inscriptions_eve_permissions` (`perm_key`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Grants permission unitaire v2 (complement des kits)';


/* ------------------------------------------------------------------
   4. Permissions actions mobile (niveau fonction dans la fiche)
   ------------------------------------------------------------------ */
INSERT INTO `inscriptions_eve_permissions`
  (`perm_key`, `perm_group`, `perm_scope`, `perm_label_fr`, `perm_label_en`,
   `perm_description_fr`, `perm_description_en`, `perm_phase`, `perm_actif`, `perm_tri`)
VALUES
  ('inscriptions_gestion.statut_edit', 'inscriptions', 'action',
   'Gestion inscription : statut', 'Registration mgmt: status',
   'Modifier le statut course sur la fiche', NULL, 2, 1, 131),
  ('inscriptions_gestion.bib_edit', 'inscriptions', 'action',
   'Gestion inscription : no dossard', 'Registration mgmt: bib number',
   'Modifier le numero de dossard sur la fiche', NULL, 2, 1, 132),
  ('inscriptions_gestion.bib_remis', 'inscriptions', 'action',
   'Gestion inscription : dossard remis', 'Registration mgmt: bib handed out',
   'Marquer dossard remis sur la fiche', NULL, 2, 1, 133),
  ('inscriptions_gestion.edit', 'inscriptions', 'action',
   'Gestion inscription : modifier', 'Registration mgmt: edit',
   'Bouton modifier inscription sur la fiche', NULL, 2, 1, 134),
  ('inscriptions_gestion.cancel', 'inscriptions', 'action',
   'Gestion inscription : annuler', 'Registration mgmt: cancel',
   'Annuler une inscription depuis la fiche', NULL, 2, 1, 135),
  ('inscriptions_gestion.restore', 'inscriptions', 'action',
   'Gestion inscription : retablir', 'Registration mgmt: restore',
   'Retablir une inscription annulee depuis la fiche', NULL, 2, 1, 136),
  ('inscriptions_gestion.refund', 'inscriptions', 'action',
   'Gestion inscription : remboursement', 'Registration mgmt: refund',
   'Effectuer un remboursement PayPal depuis la fiche de gestion', NULL, 2, 1, 137)
ON DUPLICATE KEY UPDATE
  `perm_group` = VALUES(`perm_group`),
  `perm_scope` = VALUES(`perm_scope`),
  `perm_label_fr` = VALUES(`perm_label_fr`),
  `perm_label_en` = VALUES(`perm_label_en`),
  `perm_description_fr` = VALUES(`perm_description_fr`),
  `perm_phase` = VALUES(`perm_phase`),
  `perm_actif` = VALUES(`perm_actif`),
  `perm_tri` = VALUES(`perm_tri`);


UPDATE `inscriptions_eve_permissions` SET `perm_scope` = 'page'
  WHERE `perm_key` = 'inscriptions_gestion.view';


/* ------------------------------------------------------------------
   5. Kits : gestion_ops complet, gestion_statut (exemple granulaire)
   ------------------------------------------------------------------ */
INSERT INTO `inscriptions_eve_roles`
  (`role_code`, `role_label_fr`, `role_label_en`, `role_description_fr`, `role_description_en`, `role_icone`, `role_systeme`, `role_actif`, `role_tri`)
VALUES
  ('gestion_statut', 'Gestion statuts seulement', 'Management status only',
   'Fiche : consulter et modifier les statuts (pas le dossard)',
   'Sheet: view and edit status only (not bib number)',
   'fa-flag-checkered', 1, 1, 25)
ON DUPLICATE KEY UPDATE
  `role_label_fr` = VALUES(`role_label_fr`),
  `role_label_en` = VALUES(`role_label_en`),
  `role_description_fr` = VALUES(`role_description_fr`),
  `role_description_en` = VALUES(`role_description_en`),
  `role_icone` = VALUES(`role_icone`),
  `role_actif` = VALUES(`role_actif`),
  `role_tri` = VALUES(`role_tri`);


DELETE erp FROM inscriptions_eve_role_permissions erp
INNER JOIN inscriptions_eve_roles r ON r.role_id = erp.role_id
WHERE r.role_code = 'owner';

INSERT INTO inscriptions_eve_role_permissions (role_id, perm_key)
SELECT r.role_id, p.perm_key
FROM inscriptions_eve_roles r
CROSS JOIN inscriptions_eve_permissions p
WHERE r.role_code = 'owner'
  AND p.perm_actif = 1;


DELETE erp FROM inscriptions_eve_role_permissions erp
INNER JOIN inscriptions_eve_roles r ON r.role_id = erp.role_id
WHERE r.role_code = 'gestion_ops';

INSERT INTO inscriptions_eve_role_permissions (role_id, perm_key)
SELECT r.role_id, p.perm_key
FROM inscriptions_eve_roles r
INNER JOIN inscriptions_eve_permissions p
  ON p.perm_key IN (
    'events.list', 'events.view', 'registrations.view', 'registrations.scan',
    'inscriptions_gestion.view',
    'inscriptions_gestion.statut_edit', 'inscriptions_gestion.bib_edit',
    'inscriptions_gestion.bib_remis', 'inscriptions_gestion.edit',
    'inscriptions_gestion.cancel', 'inscriptions_gestion.restore'
  )
 AND p.perm_actif = 1
WHERE r.role_code = 'gestion_ops';


DELETE erp FROM inscriptions_eve_role_permissions erp
INNER JOIN inscriptions_eve_roles r ON r.role_id = erp.role_id
WHERE r.role_code = 'gestion_readonly';

INSERT INTO inscriptions_eve_role_permissions (role_id, perm_key)
SELECT r.role_id, p.perm_key
FROM inscriptions_eve_roles r
INNER JOIN inscriptions_eve_permissions p
  ON p.perm_key IN (
    'events.list', 'events.view', 'registrations.view', 'inscriptions_gestion.view'
  )
 AND p.perm_actif = 1
WHERE r.role_code = 'gestion_readonly';


DELETE erp FROM inscriptions_eve_role_permissions erp
INNER JOIN inscriptions_eve_roles r ON r.role_id = erp.role_id
WHERE r.role_code = 'gestion_statut';

INSERT INTO inscriptions_eve_role_permissions (role_id, perm_key)
SELECT r.role_id, p.perm_key
FROM inscriptions_eve_roles r
INNER JOIN inscriptions_eve_permissions p
  ON p.perm_key IN (
    'events.list', 'events.view', 'registrations.view', 'inscriptions_gestion.view',
    'inscriptions_gestion.statut_edit', 'inscriptions_gestion.bib_remis'
  )
 AND p.perm_actif = 1
WHERE r.role_code = 'gestion_statut';


/* ------------------------------------------------------------------
   6. Plus de vues SQL.
   Les vues v_eve_acces_actif / v_eve_acces_permissions portaient un DEFINER
   non copiable par Navicat / la releve. La logique (union kits multiples +
   extras) est desormais en sous-requetes directement dans le code PHP
   (php/inc_fx_eve_acces.php et v3_ci4/app/Libraries/EventAccess.php).
   On supprime les vues si elles existent.
   ------------------------------------------------------------------ */
DROP VIEW IF EXISTS `v_eve_acces_permissions`;
DROP VIEW IF EXISTS `v_eve_acces_actif`;