-- Active: 1755472439040@@dev.wappcom.com@3306@nucleo_base
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 (ej: https://example.com/)',
  `site_path_host` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Ruta física del servidor (ej: /var/www/html/)',
  `site_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Título del sitio para SEO y navegador',
  `site_head` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Código HTML personalizado para la sección <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 (0=No, 1=Sí)',
  `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 del sitio',
  `site_state` int NOT NULL DEFAULT '0' COMMENT 'Estado del sitio (0=Inactivo, 1=Activo, 2=Mantenimiento, 3=Suspendido)',
  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 AUTO_INCREMENT=1;


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 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 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 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;

COMMIT;