-- GeneaCase Sprint 11: planes B2C/B2B, límites, suscripciones y comercialización.
-- Migración aditiva. No procesa cobros reales ni almacena credenciales de pago.

ALTER TABLE billing_suscripciones
  ADD COLUMN IF NOT EXISTS ciclo_facturacion ENUM('mensual','anual') NOT NULL DEFAULT 'mensual' AFTER estado,
  ADD COLUMN IF NOT EXISTS cancelar_al_fin TINYINT(1) NOT NULL DEFAULT 0 AFTER trial_fin,
  ADD COLUMN IF NOT EXISTS proximo_plan_id BIGINT UNSIGNED NULL AFTER cancelar_al_fin,
  ADD COLUMN IF NOT EXISTS cambio_programado_en DATETIME NULL AFTER proximo_plan_id,
  ADD COLUMN IF NOT EXISTS gracia_hasta DATETIME NULL AFTER cambio_programado_en;

CREATE TABLE IF NOT EXISTS billing_suscripcion_eventos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_suscripcion BIGINT UNSIGNED NOT NULL,
  tipo ENUM('creada','trial_iniciado','plan_cambiado','checkout_creado','cancelacion_programada','cancelada','reactivada','renovada','pago_fallido','pago_recuperado','pausada','vencida','ajuste_manual') NOT NULL,
  plan_anterior_id BIGINT UNSIGNED NULL,
  plan_nuevo_id BIGINT UNSIGNED NULL,
  metadata_json JSON NULL,
  creado_por BIGINT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id) ON DELETE CASCADE,
  FOREIGN KEY (id_suscripcion) REFERENCES billing_suscripciones(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_anterior_id) REFERENCES billing_planes(id),
  FOREIGN KEY (plan_nuevo_id) REFERENCES billing_planes(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_billing_evento_empresa_fecha (id_empresa,creado_en),
  INDEX idx_billing_evento_suscripcion (id_suscripcion,creado_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_alertas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  codigo VARCHAR(100) NOT NULL,
  periodo CHAR(7) NOT NULL,
  nivel ENUM('informativa','advertencia','critica','bloqueo') NOT NULL DEFAULT 'informativa',
  mensaje VARCHAR(500) NOT NULL,
  valor_actual DECIMAL(18,4) NOT NULL DEFAULT 0,
  valor_limite DECIMAL(18,4) NOT NULL DEFAULT 0,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  resuelta_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id) ON DELETE CASCADE,
  UNIQUE KEY uq_billing_alerta_empresa_codigo_periodo (id_empresa,codigo,periodo),
  INDEX idx_billing_alerta_empresa_nivel (id_empresa,nivel,resuelta_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_checkout_sesiones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_suscripcion BIGINT UNSIGNED NOT NULL,
  id_plan BIGINT UNSIGNED NOT NULL,
  proveedor VARCHAR(60) NOT NULL,
  proveedor_sesion_id VARCHAR(190) NULL,
  ciclo ENUM('mensual','anual') NOT NULL DEFAULT 'mensual',
  estado ENUM('pendiente','completada','cancelada','vencida','fallida') NOT NULL DEFAULT 'pendiente',
  retorno_url VARCHAR(900) NULL,
  cancelacion_url VARCHAR(900) NULL,
  metadata_json JSON NULL,
  creado_por BIGINT UNSIGNED NOT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  vence_en DATETIME NOT NULL,
  completada_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id) ON DELETE CASCADE,
  FOREIGN KEY (id_suscripcion) REFERENCES billing_suscripciones(id) ON DELETE CASCADE,
  FOREIGN KEY (id_plan) REFERENCES billing_planes(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_checkout_empresa_estado (id_empresa,estado,vence_en),
  UNIQUE KEY uq_checkout_proveedor_sesion (proveedor,proveedor_sesion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO org_permisos (codigo,modulo,nombre,descripcion) VALUES
('billing.view','billing','Ver facturación','Consulta plan, consumo, alertas e historial comercial.')
ON DUPLICATE KEY UPDATE modulo=VALUES(modulo),nombre=VALUES(nombre),descripcion=VALUES(descripcion);

INSERT IGNORE INTO org_roles_permisos (id_rol,id_permiso)
SELECT r.id,p.id FROM org_roles r CROSS JOIN org_permisos p
WHERE r.codigo IN ('owner_plataforma','admin_empresa','investigador','colaborador','revisor','investigador_local')
  AND p.codigo='billing.view';

-- Límites comerciales normalizados. Los códigos son contratos internos del SaaS.
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'documents',1000,NULL,0 FROM billing_planes WHERE codigo='starter'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),valor_texto=VALUES(valor_texto),ilimitado=VALUES(ilimitado);
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'ocr_monthly',20,NULL,0 FROM billing_planes WHERE codigo='starter'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=VALUES(ilimitado);
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'ai_monthly',20,NULL,0 FROM billing_planes WHERE codigo='starter'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=VALUES(ilimitado);
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'reports_monthly',2,NULL,0 FROM billing_planes WHERE codigo='starter'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=VALUES(ilimitado);
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'portal_clients',1,NULL,0 FROM billing_planes WHERE codigo='starter'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=VALUES(ilimitado);

INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'documents',10000,NULL,0 FROM billing_planes WHERE codigo='family'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=VALUES(ilimitado);
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'ocr_monthly',200,NULL,0 FROM billing_planes WHERE codigo='family'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=VALUES(ilimitado);
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'ai_monthly',200,NULL,0 FROM billing_planes WHERE codigo='family'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=VALUES(ilimitado);
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'reports_monthly',20,NULL,0 FROM billing_planes WHERE codigo='family'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=VALUES(ilimitado);
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'portal_clients',10,NULL,0 FROM billing_planes WHERE codigo='family'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=VALUES(ilimitado);

INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'documents',NULL,NULL,1 FROM billing_planes WHERE codigo='pro'
ON DUPLICATE KEY UPDATE valor_decimal=NULL,ilimitado=1;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'ocr_monthly',2000,NULL,0 FROM billing_planes WHERE codigo='pro'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'ai_monthly',2000,NULL,0 FROM billing_planes WHERE codigo='pro'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'reports_monthly',NULL,NULL,1 FROM billing_planes WHERE codigo='pro'
ON DUPLICATE KEY UPDATE valor_decimal=NULL,ilimitado=1;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'portal_clients',100,NULL,0 FROM billing_planes WHERE codigo='pro'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;

-- Legal y Studio se completan aquí para instalaciones antiguas que solo tenían límites de Pro.
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'users',20,NULL,0 FROM billing_planes WHERE codigo='legal'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'cases',NULL,NULL,1 FROM billing_planes WHERE codigo='legal'
ON DUPLICATE KEY UPDATE valor_decimal=NULL,ilimitado=1;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'documents',NULL,NULL,1 FROM billing_planes WHERE codigo='legal'
ON DUPLICATE KEY UPDATE valor_decimal=NULL,ilimitado=1;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'storage_gb',250,NULL,0 FROM billing_planes WHERE codigo='legal'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'ocr_monthly',5000,NULL,0 FROM billing_planes WHERE codigo='legal'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'ai_monthly',5000,NULL,0 FROM billing_planes WHERE codigo='legal'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'reports_monthly',NULL,NULL,1 FROM billing_planes WHERE codigo='legal'
ON DUPLICATE KEY UPDATE valor_decimal=NULL,ilimitado=1;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'portal_clients',200,NULL,0 FROM billing_planes WHERE codigo='legal'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;

INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'users',NULL,NULL,1 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=NULL,ilimitado=1;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'cases',NULL,NULL,1 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=NULL,ilimitado=1;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'documents',NULL,NULL,1 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=NULL,ilimitado=1;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'storage_gb',1000,NULL,0 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'ocr_monthly',20000,NULL,0 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'ai_monthly',20000,NULL,0 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'reports_monthly',NULL,NULL,1 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=NULL,ilimitado=1;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'portal_clients',NULL,NULL,1 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=NULL,ilimitado=1;