SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `cms_medios` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid_publico` CHAR(36) NOT NULL,
  `actor_tipo` ENUM('global','distribuidor','contador','negocio') NOT NULL DEFAULT 'global',
  `actor_id` BIGINT UNSIGNED NULL,
  `categoria` ENUM('logo','favicon','hero','galeria','seo','icono','archivo') NOT NULL DEFAULT 'galeria',
  `nombre_original` VARCHAR(255) NOT NULL,
  `nombre_guardado` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(120) NOT NULL,
  `extension` VARCHAR(15) NOT NULL,
  `tamano_bytes` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `ancho` INT UNSIGNED NULL,
  `alto` INT UNSIGNED NULL,
  `ruta_publica` VARCHAR(255) NOT NULL,
  `alt_text` VARCHAR(180) NULL,
  `titulo_media` VARCHAR(180) NULL,
  `estado` ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  `subido_por` BIGINT UNSIGNED NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cms_medios_uuid_publico` (`uuid_publico`),
  KEY `idx_cms_medios_actor` (`actor_tipo`, `actor_id`),
  KEY `idx_cms_medios_categoria` (`categoria`),
  KEY `idx_cms_medios_estado` (`estado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `branding_perfiles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `actor_tipo` ENUM('global','distribuidor','contador','negocio') NOT NULL DEFAULT 'global',
  `actor_id` BIGINT UNSIGNED NULL,
  `actor_scope_key` VARCHAR(80) GENERATED ALWAYS AS (CONCAT(`actor_tipo`, ':', IFNULL(CAST(`actor_id` AS CHAR), '0'))) STORED,
  `nombre_visible` VARCHAR(180) NOT NULL,
  `slogan` VARCHAR(255) NULL,
  `logo_media_id` BIGINT UNSIGNED NULL,
  `favicon_media_id` BIGINT UNSIGNED NULL,
  `portada_media_id` BIGINT UNSIGNED NULL,
  `color_primario` VARCHAR(20) NULL,
  `color_secundario` VARCHAR(20) NULL,
  `color_acento` VARCHAR(20) NULL,
  `texto_boton_primario` VARCHAR(80) NULL,
  `whatsapp` VARCHAR(30) NULL,
  `correo_publico` VARCHAR(150) NULL,
  `telefono_publico` VARCHAR(30) NULL,
  `direccion_corta` VARCHAR(255) NULL,
  `meta_titulo_global` VARCHAR(180) NULL,
  `meta_descripcion_global` VARCHAR(255) NULL,
  `meta_imagen_media_id` BIGINT UNSIGNED NULL,
  `estado` ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_branding_perfiles_scope` (`actor_scope_key`),
  KEY `idx_branding_perfiles_actor` (`actor_tipo`, `actor_id`),
  KEY `idx_branding_perfiles_estado` (`estado`),
  CONSTRAINT `fk_branding_logo_media` FOREIGN KEY (`logo_media_id`) REFERENCES `cms_medios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_branding_favicon_media` FOREIGN KEY (`favicon_media_id`) REFERENCES `cms_medios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_branding_portada_media` FOREIGN KEY (`portada_media_id`) REFERENCES `cms_medios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_branding_meta_media` FOREIGN KEY (`meta_imagen_media_id`) REFERENCES `cms_medios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dominios` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `actor_tipo` ENUM('global','distribuidor','contador','negocio') NOT NULL DEFAULT 'global',
  `actor_id` BIGINT UNSIGNED NULL,
  `host` VARCHAR(255) NOT NULL,
  `ruta_base` VARCHAR(255) NULL,
  `ssl_activo` TINYINT(1) NOT NULL DEFAULT 0,
  `principal` TINYINT(1) NOT NULL DEFAULT 1,
  `estado` ENUM('borrador','pendiente','activo','suspendido') NOT NULL DEFAULT 'borrador',
  `observacion` VARCHAR(255) NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dominios_host_ruta` (`host`, `ruta_base`),
  KEY `idx_dominios_actor` (`actor_tipo`, `actor_id`),
  KEY `idx_dominios_estado` (`estado`),
  KEY `idx_dominios_principal` (`principal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `landing_paginas` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid_publico` CHAR(36) NOT NULL,
  `actor_tipo` ENUM('global','distribuidor','contador','negocio') NOT NULL DEFAULT 'global',
  `actor_id` BIGINT UNSIGNED NULL,
  `tipo_pagina` ENUM('home','planes','plan_detalle','herramientas','contacto','soporte','canal','legal','custom') NOT NULL DEFAULT 'custom',
  `titulo_admin` VARCHAR(180) NOT NULL,
  `titulo_publico` VARCHAR(180) NOT NULL,
  `slug` VARCHAR(180) NOT NULL,
  `ruta_publica` VARCHAR(255) NOT NULL,
  `seo_title` VARCHAR(180) NULL,
  `seo_description` VARCHAR(255) NULL,
  `seo_keywords` VARCHAR(255) NULL,
  `seo_media_id` BIGINT UNSIGNED NULL,
  `resumen` TEXT NULL,
  `estado_publicacion` ENUM('borrador','programada','publicada','oculta','archivada') NOT NULL DEFAULT 'borrador',
  `mostrar_en_menu` TINYINT(1) NOT NULL DEFAULT 0,
  `mostrar_en_footer` TINYINT(1) NOT NULL DEFAULT 0,
  `orden_menu` INT NOT NULL DEFAULT 0,
  `plantilla` ENUM('home','contenido','planes','contacto','canal') NOT NULL DEFAULT 'contenido',
  `fecha_publicacion` DATETIME NULL,
  `creado_por` BIGINT UNSIGNED NULL,
  `actualizado_por` BIGINT UNSIGNED NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `actor_route_key` VARCHAR(450) GENERATED ALWAYS AS (CONCAT(`actor_tipo`, ':', IFNULL(CAST(`actor_id` AS CHAR), '0'), ':', `ruta_publica`)) STORED,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_landing_paginas_uuid_publico` (`uuid_publico`),
  UNIQUE KEY `uq_landing_paginas_actor_ruta` (`actor_route_key`),
  KEY `idx_landing_paginas_actor` (`actor_tipo`, `actor_id`),
  KEY `idx_landing_paginas_estado` (`estado_publicacion`),
  KEY `idx_landing_paginas_tipo` (`tipo_pagina`),
  KEY `idx_landing_paginas_slug` (`slug`),
  CONSTRAINT `fk_landing_paginas_seo_media` FOREIGN KEY (`seo_media_id`) REFERENCES `cms_medios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cms_bloques` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `pagina_id` BIGINT UNSIGNED NOT NULL,
  `parent_id` BIGINT UNSIGNED NULL,
  `clave` VARCHAR(120) NOT NULL,
  `tipo_bloque` ENUM('hero','beneficios','cards','texto','cta','faq','planes','testimonios','cifras','contacto','formulario','herramientas','html_controlado') NOT NULL DEFAULT 'texto',
  `titulo` VARCHAR(180) NULL,
  `subtitulo` VARCHAR(255) NULL,
  `contenido` LONGTEXT NULL,
  `config_json` LONGTEXT NULL,
  `media_id` BIGINT UNSIGNED NULL,
  `icono` VARCHAR(80) NULL,
  `link_url` VARCHAR(255) NULL,
  `link_texto` VARCHAR(80) NULL,
  `orden` INT NOT NULL DEFAULT 0,
  `estado` ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  `visible_desktop` TINYINT(1) NOT NULL DEFAULT 1,
  `visible_mobile` TINYINT(1) NOT NULL DEFAULT 1,
  `creado_por` BIGINT UNSIGNED NULL,
  `actualizado_por` BIGINT UNSIGNED NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cms_bloques_pagina_clave` (`pagina_id`, `clave`),
  KEY `idx_cms_bloques_parent` (`parent_id`),
  KEY `idx_cms_bloques_pagina_estado_orden` (`pagina_id`, `estado`, `orden`),
  CONSTRAINT `fk_cms_bloques_pagina` FOREIGN KEY (`pagina_id`) REFERENCES `landing_paginas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cms_bloques_parent` FOREIGN KEY (`parent_id`) REFERENCES `cms_bloques` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cms_bloques_media` FOREIGN KEY (`media_id`) REFERENCES `cms_medios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cms_formularios` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `actor_tipo` ENUM('global','distribuidor','contador','negocio') NOT NULL DEFAULT 'global',
  `actor_id` BIGINT UNSIGNED NULL,
  `nombre_interno` VARCHAR(120) NOT NULL,
  `slug` VARCHAR(120) NOT NULL,
  `titulo_publico` VARCHAR(180) NOT NULL,
  `descripcion_publica` VARCHAR(255) NULL,
  `destino_tipo` ENUM('lead','contacto','soporte') NOT NULL DEFAULT 'lead',
  `campos_json` LONGTEXT NOT NULL,
  `mensaje_exito` VARCHAR(255) NOT NULL,
  `enviar_correo` TINYINT(1) NOT NULL DEFAULT 0,
  `correo_destino` VARCHAR(150) NULL,
  `webhook_activo` TINYINT(1) NOT NULL DEFAULT 0,
  `estado` ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  `honeypot_activo` TINYINT(1) NOT NULL DEFAULT 1,
  `captcha_activo` TINYINT(1) NOT NULL DEFAULT 1,
  `creado_por` BIGINT UNSIGNED NULL,
  `actualizado_por` BIGINT UNSIGNED NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cms_formularios_slug` (`slug`),
  KEY `idx_cms_formularios_actor` (`actor_tipo`, `actor_id`),
  KEY `idx_cms_formularios_estado` (`estado`),
  KEY `idx_cms_formularios_destino` (`destino_tipo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `leads` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid_publico` CHAR(36) NOT NULL,
  `actor_tipo` ENUM('global','distribuidor','contador','negocio') NOT NULL DEFAULT 'global',
  `actor_id` BIGINT UNSIGNED NULL,
  `pagina_id` BIGINT UNSIGNED NULL,
  `formulario_id` BIGINT UNSIGNED NULL,
  `origen` ENUM('landing','planes','herramientas','contacto','canal','campana') NOT NULL DEFAULT 'landing',
  `nombre` VARCHAR(180) NOT NULL,
  `correo` VARCHAR(150) NULL,
  `telefono` VARCHAR(30) NULL,
  `empresa` VARCHAR(180) NULL,
  `tipo_interes` ENUM('general','plan','demo','distribuidor','contador','negocio') NOT NULL DEFAULT 'general',
  `mensaje` TEXT NULL,
  `datos_extra_json` LONGTEXT NULL,
  `estado` ENUM('nuevo','en_revision','contactado','cerrado','descartado') NOT NULL DEFAULT 'nuevo',
  `ip_hash` VARCHAR(128) NULL,
  `user_agent_hash` VARCHAR(128) NULL,
  `request_id` CHAR(36) NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_leads_uuid_publico` (`uuid_publico`),
  KEY `idx_leads_actor` (`actor_tipo`, `actor_id`),
  KEY `idx_leads_estado` (`estado`),
  KEY `idx_leads_origen` (`origen`),
  KEY `idx_leads_formulario` (`formulario_id`),
  KEY `idx_leads_pagina` (`pagina_id`),
  CONSTRAINT `fk_leads_pagina` FOREIGN KEY (`pagina_id`) REFERENCES `landing_paginas` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_leads_formulario` FOREIGN KEY (`formulario_id`) REFERENCES `cms_formularios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;