SET NAMES utf8mb4;
USE `intranet`;

CREATE TABLE IF NOT EXISTS `intranet_roles` (
  `role_key` varchar(100) NOT NULL,
  `label` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `position` int NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`role_key`),
  KEY `idx_intranet_roles_enabled_position` (`is_enabled`, `position`, `role_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

CREATE TABLE IF NOT EXISTS `intranet_app_groups` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `group_name` varchar(100) NOT NULL,
  `icon_svg` mediumtext NULL,
  `position` int unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_group_name` (`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

CREATE TABLE IF NOT EXISTS `intranet_catalog_groups` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `group_type` varchar(32) NOT NULL,
  `group_name` varchar(255) NOT NULL,
  `icon_svg` mediumtext NULL,
  `position` int unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_group_type_name` (`group_type`, `group_name`),
  KEY `idx_group_type_position` (`group_type`, `position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

INSERT INTO `intranet_roles` (`role_key`, `label`, `description`, `is_enabled`, `is_system`, `position`) VALUES
('administradores', 'Administradores', 'Acceso completo a administracion y mantenimiento de la intranet.', 1, 1, 10)
ON DUPLICATE KEY UPDATE
  `label` = IF(TRIM(`label`) = '', VALUES(`label`), `label`),
  `description` = IF(`description` IS NULL OR TRIM(`description`) = '', VALUES(`description`), `description`),
  `is_system` = GREATEST(`is_system`, VALUES(`is_system`)),
  `position` = IF(`position` = 0, VALUES(`position`), `position`);

INSERT IGNORE INTO `intranet_catalog_groups` (`group_type`, `group_name`, `icon_svg`, `position`)
SELECT 'apps',
       'Administración',
       NULL,
       COALESCE(MAX(`position`), 0) + 1
  FROM `intranet_catalog_groups`
 WHERE `group_type` = 'apps';

INSERT IGNORE INTO `intranet_app_groups` (`group_name`, `icon_svg`, `position`)
SELECT 'Administración',
       NULL,
       COALESCE(MAX(`position`), 0) + 1
  FROM `intranet_app_groups`;

INSERT INTO `intranet_apps` (`app_id`, `name`, `url`, `group_name`, `description`, `icon`, `image`, `new_tab`, `roles_json`, `position`, `is_enabled`, `created_at`, `updated_at`)
SELECT 'roles',
       'Gestion de roles',
       'base/roles/index.php',
       'Administración',
       'Gestion centralizada de roles de la intranet',
       '<svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round"><path d="M16 21v-2a4 4 0 0 0-4-4H6a4 4 0 0 0-4 4v2"/><circle cx="9" cy="7" r="4"/><path d="M22 21v-2a4 4 0 0 0-3-3.87"/><path d="M16 3.13a4 4 0 0 1 0 7.75"/><path d="M12 11l2 2 4-4"/></svg>',
       NULL,
       0,
       '["administradores"]',
       p.`next_position`,
       1,
       NOW(),
       NOW()
  FROM (
       SELECT COALESCE(MAX(`position`), 0) + 1 AS `next_position`
         FROM `intranet_apps`
        WHERE `group_name` = 'Administración'
       ) p
 WHERE NOT EXISTS (SELECT 1 FROM `intranet_apps` WHERE `app_id` = 'roles');
