SET NAMES utf8mb4;
USE `intranet`;

CREATE TABLE IF NOT EXISTS `intranet_user_preferences` (
  `username` varchar(100) NOT NULL,
  `theme_mode` varchar(10) NOT NULL DEFAULT 'light',
  `menu_layout` varchar(20) NOT NULL DEFAULT 'top',
  `session_timeout_minutes` int unsigned NOT NULL DEFAULT 30,
  `home_frequent_limit` int unsigned NOT NULL DEFAULT 10,
  `home_frequent_app_bg` varchar(7) NOT NULL DEFAULT '#eaf8f0',
  `home_frequent_link_bg` varchar(7) NOT NULL DEFAULT '#eef5ff',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

ALTER TABLE `intranet_user_preferences`
  ADD COLUMN IF NOT EXISTS `session_timeout_minutes` int unsigned NOT NULL DEFAULT 30 AFTER `theme_mode`;

ALTER TABLE `intranet_user_preferences`
  ADD COLUMN IF NOT EXISTS `menu_layout` varchar(20) NOT NULL DEFAULT 'top' AFTER `theme_mode`;

ALTER TABLE `intranet_user_preferences`
  ADD COLUMN IF NOT EXISTS `home_frequent_limit` int unsigned NOT NULL DEFAULT 10 AFTER `session_timeout_minutes`;

ALTER TABLE `intranet_user_preferences`
  ADD COLUMN IF NOT EXISTS `home_frequent_app_bg` varchar(7) NOT NULL DEFAULT '#eaf8f0' AFTER `home_frequent_limit`;

ALTER TABLE `intranet_user_preferences`
  ADD COLUMN IF NOT EXISTS `home_frequent_link_bg` varchar(7) NOT NULL DEFAULT '#eef5ff' AFTER `home_frequent_app_bg`;

ALTER TABLE `intranet_user_preferences`
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;

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
  'preferences',
  'Preferencias',
  'base/preferences/index.php',
  'Generales',
  'Preferencias personales de interfaz',
  '<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"><circle cx="12" cy="12" r="3"></circle><path d="M19.4 15a1.65 1.65 0 0 0 .33 1.82l.06.06a2 2 0 1 1-2.83 2.83l-.06-.06a1.65 1.65 0 0 0-1.82-.33 1.65 1.65 0 0 0-1 1.51V21a2 2 0 1 1-4 0v-.09a1.65 1.65 0 0 0-1-1.51 1.65 1.65 0 0 0-1.82.33l-.06.06a2 2 0 1 1-2.83-2.83l.06-.06a1.65 1.65 0 0 0 .33-1.82 1.65 1.65 0 0 0-1.51-1H3a2 2 0 1 1 0-4h.09a1.65 1.65 0 0 0 1.51-1 1.65 1.65 0 0 0-.33-1.82l-.06-.06a2 2 0 1 1 2.83-2.83l.06.06a1.65 1.65 0 0 0 1.82.33h0A1.65 1.65 0 0 0 10 3.09V3a2 2 0 1 1 4 0v.09a1.65 1.65 0 0 0 1 1.51h0a1.65 1.65 0 0 0 1.82-.33l.06-.06a2 2 0 1 1 2.83 2.83l-.06.06a1.65 1.65 0 0 0-.33 1.82v0A1.65 1.65 0 0 0 20.91 10H21a2 2 0 1 1 0 4h-.09a1.65 1.65 0 0 0-1.51 1z"></path></svg>',
  NULL,
  0,
  '["app_users"]',
  99,
  1,
  NOW(),
  NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM `intranet_apps` WHERE `app_id` = 'preferences'
);
