-- 001_grafo_nucleo: entidades del grafo de conocimiento + aristas + soporte
-- Idempotente: CREATE TABLE IF NOT EXISTS en todo el fichero.

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(100) NOT NULL,
  rol ENUM('paciente','profesional','editor','revisor_medico','admin') NOT NULL DEFAULT 'paciente',
  nombre VARCHAR(120) NULL,
  verificado_email TINYINT(1) NOT NULL DEFAULT 0,
  token_verificacion VARCHAR(64) NULL,
  consent JSON NULL,
  intentos_login TINYINT NOT NULL DEFAULT 0,
  bloqueado_hasta DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fabricantes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  es_distribuidor TINYINT(1) NOT NULL DEFAULT 0,
  pais VARCHAR(60) NULL,
  web VARCHAR(300) NULL,
  email_contacto VARCHAR(190) NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_fab (estado_editorial, es_demo, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patologias (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  articulacion ENUM('cadera','rodilla','hombro','columna','tobillo','codo','atm') NOT NULL,
  cie10 VARCHAR(10) NULL,
  sintomas JSON NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_pat (articulacion, estado_editorial, es_demo, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS procedimientos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  articulacion ENUM('cadera','rodilla','hombro','columna','tobillo','codo','atm') NOT NULL,
  duracion_ingreso_dias VARCHAR(20) NULL,
  anestesia JSON NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_proc (articulacion, estado_editorial, es_demo, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tipos_protesis (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  articulacion ENUM('cadera','rodilla','hombro','columna','tobillo','codo','atm') NOT NULL,
  eje_clasificacion VARCHAR(60) NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_tipo (articulacion, estado_editorial, es_demo, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS productos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sistema VARCHAR(200) NULL,
  fabricante_id BIGINT UNSIGNED NULL,
  udi_di VARCHAR(64) NULL,
  emdn VARCHAR(32) NULL,
  clase_riesgo ENUM('IIa','IIb','III') NULL,
  anio_lanzamiento SMALLINT NULL,
  ciclo_vida ENUM('activo','descatalogado','retirado') NOT NULL DEFAULT 'activo',
  articulacion ENUM('cadera','rodilla','hombro','columna','tobillo','codo','atm') NOT NULL,
  indicacion ENUM('primaria','revision','ambas') NOT NULL DEFAULT 'primaria',
  componentes JSON NULL,
  fijacion ENUM('cementada','no_cementada','hibrida','na') NULL,
  par_friccion VARCHAR(60) NULL,
  constriccion VARCHAR(60) NULL,
  materiales JSON NULL,
  atributos JSON NULL,
  contiene_niquel ENUM('si','no','sin_datos') NOT NULL DEFAULT 'sin_datos',
  compat_rm ENUM('condicional_1_5T','condicional_3T','condicional_ambas','no_segura','sin_datos') NOT NULL DEFAULT 'sin_datos',
  compat_rm_detalle TEXT NULL,
  aeropuerto_nota TEXT NULL,
  profilaxis_dental ENUM('recomendada','no_necesaria','segun_caso','sin_datos') NOT NULL DEFAULT 'sin_datos',
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_facetas (articulacion, indicacion, fijacion, ciclo_vida, estado_editorial, es_demo, deleted_at),
  CONSTRAINT fk_prod_fab FOREIGN KEY (fabricante_id) REFERENCES fabricantes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cirujanos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre_completo VARCHAR(160) NULL,
  num_colegiado VARCHAR(20) NULL,
  especialidad ENUM('traumatologia','neurocirugia','maxilofacial') NOT NULL DEFAULT 'traumatologia',
  subespecialidades JSON NULL,
  provincia VARCHAR(60) NULL,
  ciudad VARCHAR(80) NULL,
  acepta_privados TINYINT(1) NOT NULL DEFAULT 1,
  agenda_activada TINYINT(1) NOT NULL DEFAULT 0,
  verificado TINYINT(1) NOT NULL DEFAULT 0,
  user_id BIGINT UNSIGNED NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_cir (provincia, especialidad, estado_editorial, es_demo, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS centros (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tipo ENUM('publico','privado','mixto') NOT NULL DEFAULT 'privado',
  provincia VARCHAR(60) NULL,
  ciudad VARCHAR(80) NULL,
  direccion VARCHAR(300) NULL,
  web VARCHAR(300) NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_cen (provincia, tipo, estado_editorial, es_demo, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS aseguradoras (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tipo ENUM('salud','mutua_laboral','mutualismo_admin') NOT NULL DEFAULT 'salud',
  web VARCHAR(300) NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_aseg (tipo, estado_editorial, es_demo, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS polizas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  aseguradora_id BIGINT UNSIGNED NOT NULL,
  nombre_comercial VARCHAR(160) NULL,
  ambito VARCHAR(120) NULL,
  anio_condiciones SMALLINT NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_pol (aseguradora_id, estado_editorial, es_demo, deleted_at),
  CONSTRAINT fk_pol_aseg FOREIGN KEY (aseguradora_id) REFERENCES aseguradoras(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS servicios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  categoria ENUM('fisioterapia','imagen','ortopedia','telerehabilitacion') NOT NULL,
  provincia VARCHAR(60) NULL,
  ciudad VARCHAR(80) NULL,
  comision_pct DECIMAL(4,2) NOT NULL DEFAULT 0,
  user_id BIGINT UNSIGNED NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_srv (categoria, provincia, estado_editorial, es_demo, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tecnologias (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  categoria ENUM('robot','navegacion','planificacion_3d','psi','otro') NOT NULL DEFAULT 'otro',
  fabricante_id BIGINT UNSIGNED NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_tec (categoria, estado_editorial, es_demo, deleted_at),
  CONSTRAINT fk_tec_fab FOREIGN KEY (fabricante_id) REFERENCES fabricantes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS alertas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  origen ENUM('aemps','fabricante','otro') NOT NULL DEFAULT 'aemps',
  nivel ENUM('informativa','importante','urgente') NOT NULL DEFAULT 'importante',
  fecha_publicacion DATE NULL,
  url_oficial VARCHAR(400) NULL,
  estado ENUM('abierta','cerrada') NOT NULL DEFAULT 'abierta',
  slug VARCHAR(160) NOT NULL UNIQUE,
  nombre VARCHAR(200) NOT NULL,
  estado_editorial ENUM('borrador','revision','publicado','archivado') NOT NULL DEFAULT 'borrador',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  resumen_corto TEXT NULL,
  contenido_paciente MEDIUMTEXT NULL,
  contenido_pro MEDIUMTEXT NULL,
  faq JSON NULL,
  meta_title VARCHAR(70) NULL,
  meta_description VARCHAR(160) NULL,
  revisado_por BIGINT UNSIGNED NULL,
  fecha_revision DATE NULL,
  fuentes JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_ale (estado, nivel, estado_editorial, es_demo, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS evidencias (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tipo_fuente ENUM('registro_nacional','odep','estudio','guia_clinica','pmcf_interno') NOT NULL,
  fuente_nombre VARCHAR(120) NOT NULL,
  anio SMALLINT NULL,
  seguimiento_anios TINYINT NULL,
  metrica VARCHAR(60) NULL,
  valor VARCHAR(60) NULL,
  cohorte_n INT NULL,
  url VARCHAR(500) NULL,
  detalle TEXT NULL,
  estado_verificacion ENUM('pendiente','verificada') NOT NULL DEFAULT 'pendiente',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_evi (tipo_fuente, estado_verificacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS graph_edges (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_type VARCHAR(32) NOT NULL,
  from_id BIGINT UNSIGNED NOT NULL,
  rel VARCHAR(48) NOT NULL,
  to_type VARCHAR(32) NOT NULL,
  to_id BIGINT UNSIGNED NOT NULL,
  attrs JSON NULL,
  origen ENUM('editorial','reclamado','flujo') NOT NULL DEFAULT 'editorial',
  verificada TINYINT(1) NOT NULL DEFAULT 0,
  estado ENUM('activa','inactiva') NOT NULL DEFAULT 'activa',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_edge (from_type, from_id, rel, to_type, to_id),
  KEY idx_from (from_type, from_id, rel, estado),
  KEY idx_to (to_type, to_id, rel, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_implants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  producto_id BIGINT UNSIGNED NULL,
  producto_texto_libre VARCHAR(200) NULL,
  articulacion ENUM('cadera','rodilla','hombro','columna','tobillo','codo','atm') NOT NULL,
  lateralidad ENUM('izquierda','derecha','bilateral','na') NOT NULL DEFAULT 'na',
  fecha_intervencion DATE NULL,
  centro_texto VARCHAR(200) NULL,
  lote VARCHAR(60) NULL,
  origen ENUM('manual','flujo') NOT NULL DEFAULT 'manual',
  notif_alertas TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_ui (user_id), KEY idx_ui_prod (producto_id),
  CONSTRAINT fk_ui_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS claim_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type VARCHAR(32) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  mensaje TEXT NULL,
  documento_adjunto VARCHAR(300) NULL,
  estado ENUM('pendiente','aprobada','rechazada') NOT NULL DEFAULT 'pendiente',
  revisado_por BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cr (estado, entity_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS newsletter_subs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  articulaciones JSON NULL,
  fase ENUM('informandome','pre_quirurgico','post_quirurgico','portador') NULL,
  token_confirmacion VARCHAR(64) NULL,
  token_expira DATETIME NULL,
  confirmado_at DATETIME NULL,
  baja_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS redirects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_path VARCHAR(300) NOT NULL UNIQUE,
  to_path VARCHAR(300) NOT NULL,
  code SMALLINT NOT NULL DEFAULT 301,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pageviews (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  path VARCHAR(300) NOT NULL,
  ref_domain VARCHAR(190) NULL,
  ua_class ENUM('desktop','mobile','bot') NOT NULL DEFAULT 'desktop',
  entity_type VARCHAR(32) NULL,
  entity_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pv (path(120), created_at),
  KEY idx_pv_ent (entity_type, entity_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type VARCHAR(32) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  filename VARCHAR(200) NOT NULL,
  alt VARCHAR(200) NULL,
  variants JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_media (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  entity_type VARCHAR(32) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  action VARCHAR(40) NOT NULL,
  diff JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit (entity_type, entity_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;