-- Nucleo core tables
-- Ejecutar en la BD principal. No usa foreign keys.

DROP TABLE IF EXISTS options;

CREATE TABLE `options` (
  `option_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del registro',
  `option_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'Nombre del registro',
  `option_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Valor de option value',
  `autoload` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'yes' COMMENT 'Valor de autoload',
  PRIMARY KEY (`option_id`) USING BTREE,
  UNIQUE KEY `uk_options_name_autoload` (`option_name`, `autoload`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS roles;

CREATE TABLE `roles` (
  `rol_id` int NOT NULL AUTO_INCREMENT,
  `rol_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `rol_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `rol_parent_id` int DEFAULT NULL,
  `rol_redirection_url` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `rol_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `rol_state` int DEFAULT '0',
  PRIMARY KEY (`rol_id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS roles_entities;

CREATE TABLE `roles_entities` (
  `rol_ent_rol_id` int NOT NULL,
  `rol_ent_ent_id` int NOT NULL,
  `rol_ent_state` int DEFAULT NULL,
  PRIMARY KEY (`rol_ent_rol_id`, `rol_ent_ent_id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS users;

CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del registro',
  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Nombre del registro',
  `user_lastname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Valor de user lastname',
  `user_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Correo electrónico',
  `user_password` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Valor de user password',
  `user_img` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Valor de user img',
  `user_username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Valor de user username',
  `user_user_id` int NOT NULL DEFAULT '0' COMMENT 'Usuario que guarda',
  `user_level` int NOT NULL DEFAULT '0' COMMENT 'Valor de user level',
  `user_state` int DEFAULT '0' COMMENT 'Estado del registro',
  PRIMARY KEY (`user_id`) USING BTREE,
  KEY `idx_user_email` (`user_email`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS users_roles;

CREATE TABLE `users_roles` (
  `user_rol_user_id` int NOT NULL COMMENT 'ID del usuario',
  `user_rol_rol_id` int NOT NULL COMMENT 'ID de rol rol',
  `user_rol_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `user_rol_order` int NOT NULL DEFAULT '0' COMMENT 'Orden de visualización',
  PRIMARY KEY (`user_rol_user_id`, `user_rol_rol_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS users_tokens;

CREATE TABLE `users_tokens` (
  `user_tk_user_id` bigint NOT NULL COMMENT 'ID del usuario',
  `user_tk_type` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Tipo de registro',
  `user_tk_token` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Token de autenticación',
  `user_tk_expires_in` datetime DEFAULT NULL COMMENT 'Valor de user tk expires in',
  `user_tk_date` datetime DEFAULT NULL COMMENT 'Fecha del registro',
  `user_tk_dates_browser` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Valor de user tk dates browser',
  PRIMARY KEY (`user_tk_type`, `user_tk_token`) USING BTREE,
  KEY `idx_user_tk_user_type` (`user_tk_user_id`, `user_tk_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS users_groups;

CREATE TABLE `users_groups` (
  `user_group_user_id` int NOT NULL COMMENT 'ID del usuario',
  `user_group_group_id` int NOT NULL COMMENT 'ID de group group',
  `user_group_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `user_group_order` int NOT NULL DEFAULT '0' COMMENT 'Orden de visualización',
  PRIMARY KEY (`user_group_user_id`, `user_group_group_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS users_tabs;

CREATE TABLE `users_tabs` (
  `user_tab_user_id` int NOT NULL COMMENT 'ID del usuario',
  `user_tab_sys_id` int NOT NULL COMMENT 'ID de tab sys',
  `user_tab_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `user_tab_sys_active` int NOT NULL COMMENT 'Valor de user tab sys active',
  `user_tab_start_mod_id` int DEFAULT '0' COMMENT 'ID de tab start mod',
  `user_tab_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Datos adicionales en formato JSON',
  `user_tab_order` int NOT NULL COMMENT 'Orden de visualización',
  PRIMARY KEY (`user_tab_user_id`, `user_tab_sys_id`, `user_tab_ent_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS groups;

CREATE TABLE `groups` (
  `group_id` int NOT NULL AUTO_INCREMENT,
  `group_ent_id` int DEFAULT '1' COMMENT 'ID de la entidad/empresa',
  `group_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nombre del grupo',
  `group_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Descripción del grupo',
  `group_state` tinyint NOT NULL DEFAULT '1' COMMENT '0=Inactivo, 1=Activo',
  `group_created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `group_updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de actualización',
  PRIMARY KEY (`group_id`) USING BTREE,
  KEY `idx_group_ent_id` (`group_ent_id`) USING BTREE,
  KEY `idx_group_state` (`group_state`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Grupos de usuarios y archivos';

DROP TABLE IF EXISTS groups_users;

CREATE TABLE `groups_users` (
  `group_user_group_id` int NOT NULL COMMENT 'ID del grupo',
  `group_user_user_id` int NOT NULL COMMENT 'ID del usuario',
  `group_user_order` int NOT NULL DEFAULT '0' COMMENT 'Orden de visualización',
  KEY `idx_gu_group_id` (`group_user_group_id`) USING BTREE,
  KEY `idx_gu_user_id` (`group_user_user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Asignación de usuarios a grupos';

DROP TABLE IF EXISTS groups_files;

CREATE TABLE `groups_files` (
  `group_file_group_id` int NOT NULL COMMENT 'ID del grupo',
  `group_file_file_id` int NOT NULL COMMENT 'ID del archivo',
  `group_file_order` int NOT NULL DEFAULT '0' COMMENT 'Orden del archivo en el grupo',
  KEY `idx_gf_group_id` (`group_file_group_id`) USING BTREE,
  KEY `idx_gf_file_id` (`group_file_file_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Asignación de archivos a grupos';

DROP TABLE IF EXISTS sites;

CREATE TABLE `sites` (
  `site_id` int NOT NULL AUTO_INCREMENT COMMENT 'ID único del sitio',
  `site_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Nombre descriptivo del sitio',
  `site_path_web` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'URL completa del sitio',
  `site_path_host` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Ruta física del servidor',
  `site_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Título del sitio',
  `site_head` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Código HTML personalizado para head',
  `site_head_path_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'URL del archivo head externo',
  `site_default` int NOT NULL DEFAULT '0' COMMENT 'Indica si es el sitio por defecto',
  `site_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del sitio',
  `site_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Última actualización del sitio',
  `site_ent_id` int NOT NULL DEFAULT '0' COMMENT 'ID de la entidad propietaria',
  `site_state` int NOT NULL DEFAULT '0' COMMENT 'Estado del sitio',
  PRIMARY KEY (`site_id`) USING BTREE,
  KEY `idx_state` (`site_state`) USING BTREE,
  KEY `idx_ent_id` (`site_ent_id`) USING BTREE,
  KEY `idx_path_web` (`site_path_web`) USING BTREE,
  KEY `idx_path_host` (`site_path_host`) USING BTREE,
  KEY `idx_ent_state` (`site_ent_id`, `site_state`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS sites_entities;

CREATE TABLE `sites_entities` (
  `site_ent_site_id` int NOT NULL COMMENT 'ID del sitio',
  `site_ent_ent_id` int NOT NULL COMMENT 'ID de la entidad asociada',
  `site_ent_order` int NOT NULL DEFAULT '0' COMMENT 'Orden de la entidad en el sitio',
  PRIMARY KEY (`site_ent_site_id`, `site_ent_ent_id`) USING BTREE,
  KEY `idx_site_id` (`site_ent_site_id`) USING BTREE,
  KEY `idx_ent_id` (`site_ent_ent_id`) USING BTREE,
  KEY `idx_order` (`site_ent_site_id`, `site_ent_order`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS roles_sites;

CREATE TABLE `roles_sites` (
  `rol_site_rol_id` int NOT NULL COMMENT 'ID del rol',
  `rol_site_site_id` int NOT NULL COMMENT 'ID del sitio',
  `rol_site_order` int NOT NULL DEFAULT '0' COMMENT 'Orden del rol en el sitio',
  PRIMARY KEY (`rol_site_rol_id`, `rol_site_site_id`) USING BTREE,
  KEY `idx_site_id` (`rol_site_site_id`) USING BTREE,
  KEY `idx_order` (`rol_site_site_id`, `rol_site_order`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS sites_users;

CREATE TABLE `sites_users` (
  `site_user_site_id` int NOT NULL COMMENT 'ID del sitio',
  `site_user_user_id` int NOT NULL COMMENT 'ID del usuario',
  `site_user_rol_id` int NOT NULL COMMENT 'ID del rol asignado al usuario en el sitio',
  `site_user_order` int NOT NULL DEFAULT '0' COMMENT 'Orden del usuario en el sitio',
  PRIMARY KEY (`site_user_site_id`, `site_user_user_id`) USING BTREE,
  KEY `idx_user_id` (`site_user_user_id`) USING BTREE,
  KEY `idx_rol_id` (`site_user_rol_id`) USING BTREE,
  KEY `idx_order` (`site_user_site_id`, `site_user_order`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `users`
  MODIFY `user_password` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Valor de user password';

UPDATE `options` SET `option_value`='{{_PATH_WEB}}assets/img/logo.svg', `autoload`='yes' WHERE `option_name`='path_logo' AND `autoload`='yes';
INSERT INTO `options` (`option_name`, `option_value`, `autoload`)
SELECT 'path_logo', '{{_PATH_WEB}}assets/img/logo.svg', 'yes'
WHERE NOT EXISTS (SELECT 1 FROM `options` WHERE `option_name`='path_logo' AND `autoload`='yes');

UPDATE `options` SET `option_value`='Nucleo v1.0', `autoload`='yes' WHERE `option_name`='footer' AND `autoload`='yes';
INSERT INTO `options` (`option_name`, `option_value`, `autoload`)
SELECT 'footer', 'Nucleo v1.0', 'yes'
WHERE NOT EXISTS (SELECT 1 FROM `options` WHERE `option_name`='footer' AND `autoload`='yes');

UPDATE `options` SET `option_value`='assets/img/logo.svg', `autoload`='yes' WHERE `option_name`='dashboard_brand' AND `autoload`='yes';
INSERT INTO `options` (`option_name`, `option_value`, `autoload`)
SELECT 'dashboard_brand', 'assets/img/logo.svg', 'yes'
WHERE NOT EXISTS (SELECT 1 FROM `options` WHERE `option_name`='dashboard_brand' AND `autoload`='yes');

UPDATE `options` SET `option_value`='eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiI3MjM0OTIzODczNDkiLCJuYW1lIjoibnVjbGVvIiwiaWF0Ijo5MzI0MjQzNDl9.jIsiHIdOGa-KHCJ2mUhLLKqlvTYVLolUyrp1HM6E7Cs-gedeon', `autoload`='yes' WHERE `option_name`='bearer_token' AND `autoload`='yes';
INSERT INTO `options` (`option_name`, `option_value`, `autoload`)
SELECT 'bearer_token', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiI3MjM0OTIzODczNDkiLCJuYW1lIjoibnVjbGVvIiwiaWF0Ijo5MzI0MjQzNDl9.jIsiHIdOGa-KHCJ2mUhLLKqlvTYVLolUyrp1HM6E7Cs-gedeon', 'yes'
WHERE NOT EXISTS (SELECT 1 FROM `options` WHERE `option_name`='bearer_token' AND `autoload`='yes');

UPDATE `options` SET `option_value`='eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9-gedeon', `autoload`='yes' WHERE `option_name`='client_id' AND `autoload`='yes';
INSERT INTO `options` (`option_name`, `option_value`, `autoload`)
SELECT 'client_id', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9-gedeon', 'yes'
WHERE NOT EXISTS (SELECT 1 FROM `options` WHERE `option_name`='client_id' AND `autoload`='yes');

UPDATE `options` SET `option_value`='eyJzdWIiOiI3MjM0OTIzODczNDkiLCJuYW1lIjoibnVjbGVvIiwiaWF0Ijo5MzI0MjQzNDl9-gedeon', `autoload`='yes' WHERE `option_name`='client_secret' AND `autoload`='yes';
INSERT INTO `options` (`option_name`, `option_value`, `autoload`)
SELECT 'client_secret', 'eyJzdWIiOiI3MjM0OTIzODczNDkiLCJuYW1lIjoibnVjbGVvIiwiaWF0Ijo5MzI0MjQzNDl9-gedeon', 'yes'
WHERE NOT EXISTS (SELECT 1 FROM `options` WHERE `option_name`='client_secret' AND `autoload`='yes');

INSERT INTO `roles` (`rol_id`, `rol_name`, `rol_description`, `rol_parent_id`, `rol_redirection_url`, `rol_json`, `rol_state`) VALUES
  (1, 'Administrador General', 'Acceso total a todos los sistemas y módulos del sistema', 0, '', '{}', 1)
ON DUPLICATE KEY UPDATE
  `rol_name` = VALUES(`rol_name`),
  `rol_description` = VALUES(`rol_description`),
  `rol_parent_id` = VALUES(`rol_parent_id`),
  `rol_redirection_url` = VALUES(`rol_redirection_url`),
  `rol_json` = VALUES(`rol_json`),
  `rol_state` = VALUES(`rol_state`);

INSERT INTO `roles_entities` (`rol_ent_rol_id`, `rol_ent_ent_id`, `rol_ent_state`) VALUES
  (1, 1, 1)
ON DUPLICATE KEY UPDATE
  `rol_ent_state` = VALUES(`rol_ent_state`);