-- GeneaCase Sprint 24A
-- Catálogo de planes, límites, capacidades, trial profesional y add-ons.
-- Idempotente. No conecta pasarelas ni realiza cargos.

INSERT INTO billing_planes
(codigo,nombre,segmento,moneda,precio_mensual,precio_anual,estado,caracteristicas_json)
VALUES
('starter','Esencial','b2c','USD',0.00,0.00,'activo',
 JSON_OBJECT('audiencia','b2c_inicial','trial_days',0,'portal_familiar',false,'informes_sin_marca',false)),
('family','Legado','b2c','USD',12.00,115.00,'activo',
 JSON_OBJECT('audiencia','familias','trial_days',0,'portal_familiar',true,'informes_sin_marca',true)),
('pro','Profesional','b2b','USD',49.00,470.00,'activo',
 JSON_OBJECT('audiencia','profesionales','trial_days',14,'portal_familiar',true,'marca_profesional',true)),
('studio','Estudio','institution','USD',99.00,950.00,'activo',
 JSON_OBJECT('audiencia','equipos','trial_days',14,'precio_desde',true,'marca_blanca',true))
ON DUPLICATE KEY UPDATE
 nombre=VALUES(nombre),
 segmento=VALUES(segmento),
 moneda=VALUES(moneda),
 precio_mensual=VALUES(precio_mensual),
 precio_anual=VALUES(precio_anual),
 estado='activo',
 caracteristicas_json=VALUES(caracteristicas_json);

UPDATE billing_planes
SET estado='retirado'
WHERE codigo='legal';

-- Límites principales.
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'users',1,NULL,0 FROM billing_planes WHERE codigo='starter'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),valor_texto=NULL,ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'cases',1,NULL,0 FROM billing_planes WHERE codigo='starter'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),valor_texto=NULL,ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'storage_gb',0.48828125,'500 MB',0 FROM billing_planes WHERE codigo='starter'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),valor_texto=VALUES(valor_texto),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'portal_clients',0,NULL,0 FROM billing_planes WHERE codigo='starter'
ON DUPLICATE KEY UPDATE valor_decimal=0,valor_texto=NULL,ilimitado=0;

INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'users',1,NULL,0 FROM billing_planes WHERE codigo='family'
ON DUPLICATE KEY UPDATE valor_decimal=1,valor_texto=NULL,ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'cases',5,NULL,0 FROM billing_planes WHERE codigo='family'
ON DUPLICATE KEY UPDATE valor_decimal=5,valor_texto=NULL,ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'storage_gb',5,NULL,0 FROM billing_planes WHERE codigo='family'
ON DUPLICATE KEY UPDATE valor_decimal=5,valor_texto=NULL,ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'portal_clients',5,NULL,0 FROM billing_planes WHERE codigo='family'
ON DUPLICATE KEY UPDATE valor_decimal=5,valor_texto=NULL,ilimitado=0;

INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'users',1,NULL,0 FROM billing_planes WHERE codigo='pro'
ON DUPLICATE KEY UPDATE valor_decimal=1,valor_texto=NULL,ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'cases',25,NULL,0 FROM billing_planes WHERE codigo='pro'
ON DUPLICATE KEY UPDATE valor_decimal=25,valor_texto=NULL,ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'storage_gb',20,NULL,0 FROM billing_planes WHERE codigo='pro'
ON DUPLICATE KEY UPDATE valor_decimal=20,valor_texto=NULL,ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'portal_clients',25,NULL,0 FROM billing_planes WHERE codigo='pro'
ON DUPLICATE KEY UPDATE valor_decimal=25,valor_texto=NULL,ilimitado=0;

INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'users',5,'3-5 incluidos',0 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=5,valor_texto=VALUES(valor_texto),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'cases',75,'Configurable hasta 150',0 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=75,valor_texto=VALUES(valor_texto),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'storage_gb',75,'Configurable',0 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=75,valor_texto=VALUES(valor_texto),ilimitado=0;
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT id,'portal_clients',75,'Configurable',0 FROM billing_planes WHERE codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=75,valor_texto=VALUES(valor_texto),ilimitado=0;

-- Capacidades por plan: 1=habilitada, 0=bloqueada.
INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT p.id,c.codigo,c.valor,NULL,0
FROM billing_planes p
JOIN (
 SELECT 'feature_timeline_basic' codigo,1 valor UNION ALL
 SELECT 'feature_map_basic',1 UNION ALL
 SELECT 'feature_evidence_hypotheses',0 UNION ALL
 SELECT 'feature_pdf_clean',0 UNION ALL
 SELECT 'feature_family_portal',0 UNION ALL
 SELECT 'feature_professional_branding',0 UNION ALL
 SELECT 'feature_white_label',0
) c
WHERE p.codigo='starter'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),valor_texto=NULL,ilimitado=0;

INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT p.id,c.codigo,c.valor,NULL,0
FROM billing_planes p
JOIN (
 SELECT 'feature_timeline_basic' codigo,1 valor UNION ALL
 SELECT 'feature_map_basic',1 UNION ALL
 SELECT 'feature_evidence_hypotheses',1 UNION ALL
 SELECT 'feature_pdf_clean',1 UNION ALL
 SELECT 'feature_family_portal',1 UNION ALL
 SELECT 'feature_professional_branding',0 UNION ALL
 SELECT 'feature_white_label',0
) c
WHERE p.codigo='family'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),valor_texto=NULL,ilimitado=0;

INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT p.id,c.codigo,c.valor,NULL,0
FROM billing_planes p
JOIN (
 SELECT 'feature_timeline_basic' codigo,1 valor UNION ALL
 SELECT 'feature_map_basic',1 UNION ALL
 SELECT 'feature_evidence_hypotheses',1 UNION ALL
 SELECT 'feature_pdf_clean',1 UNION ALL
 SELECT 'feature_family_portal',1 UNION ALL
 SELECT 'feature_professional_branding',1 UNION ALL
 SELECT 'feature_white_label',0
) c
WHERE p.codigo='pro'
ON DUPLICATE KEY UPDATE valor_decimal=VALUES(valor_decimal),valor_texto=NULL,ilimitado=0;

INSERT INTO billing_plan_limites(id_plan,codigo,valor_decimal,valor_texto,ilimitado)
SELECT p.id,c.codigo,1,NULL,0
FROM billing_planes p
JOIN (
 SELECT 'feature_timeline_basic' codigo UNION ALL
 SELECT 'feature_map_basic' UNION ALL
 SELECT 'feature_evidence_hypotheses' UNION ALL
 SELECT 'feature_pdf_clean' UNION ALL
 SELECT 'feature_family_portal' UNION ALL
 SELECT 'feature_professional_branding' UNION ALL
 SELECT 'feature_white_label'
) c
WHERE p.codigo='studio'
ON DUPLICATE KEY UPDATE valor_decimal=1,valor_texto=NULL,ilimitado=0;

CREATE TABLE IF NOT EXISTS billing_addons_catalogo (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo VARCHAR(80) NOT NULL UNIQUE,
  nombre VARCHAR(140) NOT NULL,
  metrica_codigo VARCHAR(100) NOT NULL,
  cantidad DECIMAL(18,4) NOT NULL,
  moneda CHAR(3) NOT NULL DEFAULT 'USD',
  precio_mensual DECIMAL(12,2) NOT NULL DEFAULT 0,
  precio_anual DECIMAL(12,2) NOT NULL DEFAULT 0,
  estado ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  metadata_json JSON NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_empresa_addons (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_addon BIGINT UNSIGNED NOT NULL,
  cantidad_bloques INT UNSIGNED NOT NULL DEFAULT 1,
  estado ENUM('pendiente','activo','cancelado','vencido') NOT NULL DEFAULT 'pendiente',
  periodo_inicio DATETIME NULL,
  periodo_fin DATETIME NULL,
  metadata_json JSON NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id) ON DELETE CASCADE,
  FOREIGN KEY (id_addon) REFERENCES billing_addons_catalogo(id),
  UNIQUE KEY uq_empresa_addon_estado (id_empresa,id_addon,estado),
  INDEX idx_empresa_addon_activo (id_empresa,estado,periodo_fin)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO billing_addons_catalogo
(codigo,nombre,metrica_codigo,cantidad,moneda,precio_mensual,precio_anual,estado,metadata_json)
VALUES
('cases_5','Bloque de 5 casos activos','cases',5,'USD',10.00,96.00,'activo',JSON_OBJECT('aplica_a',JSON_ARRAY('family','pro','studio'))),
('storage_5gb','5 GB adicionales','storage_gb',5,'USD',5.00,48.00,'activo',JSON_OBJECT('aplica_a',JSON_ARRAY('family','pro','studio'))),
('portal_5','5 portales adicionales','portal_clients',5,'USD',8.00,77.00,'activo',JSON_OBJECT('aplica_a',JSON_ARRAY('pro','studio'))),
('user_1','Usuario interno adicional','users',1,'USD',12.00,115.00,'activo',JSON_OBJECT('aplica_a',JSON_ARRAY('pro','studio')))
ON DUPLICATE KEY UPDATE
 nombre=VALUES(nombre),metrica_codigo=VALUES(metrica_codigo),cantidad=VALUES(cantidad),
 moneda=VALUES(moneda),precio_mensual=VALUES(precio_mensual),
 precio_anual=VALUES(precio_anual),estado='activo',metadata_json=VALUES(metadata_json);

INSERT INTO org_permisos(codigo,modulo,nombre,descripcion)
VALUES
('billing.limits.view','billing','Ver límites SaaS','Consulta consumo, límites, trial y capacidades del plan.'),
('billing.addons.manage','billing','Gestionar add-ons','Administra ampliaciones de casos, almacenamiento, portales y usuarios.')
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
JOIN org_permisos p ON p.codigo='billing.limits.view'
WHERE r.codigo IN ('owner_plataforma','admin_empresa','investigador','colaborador','revisor','investigador_local');

INSERT IGNORE INTO org_roles_permisos(id_rol,id_permiso)
SELECT r.id,p.id
FROM org_roles r
JOIN org_permisos p ON p.codigo='billing.addons.manage'
WHERE r.codigo IN ('owner_plataforma','admin_empresa');

-- El plan gratuito queda activo sin fecha de expiración comercial.
UPDATE billing_suscripciones s
JOIN billing_planes p ON p.id=s.id_plan
SET s.estado='activa',
    s.trial_fin=NULL,
    s.periodo_fin=GREATEST(s.periodo_fin,DATE_ADD(NOW(),INTERVAL 10 YEAR)),
    s.actualizado_en=NOW()
WHERE p.codigo='starter' AND s.estado='trial';

-- El trial profesional se normaliza a 14 días solo si aún está en prueba.
UPDATE billing_suscripciones s
JOIN billing_planes p ON p.id=s.id_plan
SET s.trial_fin=LEAST(COALESCE(s.trial_fin,DATE_ADD(s.periodo_inicio,INTERVAL 14 DAY)),DATE_ADD(s.periodo_inicio,INTERVAL 14 DAY)),
    s.periodo_fin=LEAST(s.periodo_fin,DATE_ADD(s.periodo_inicio,INTERVAL 14 DAY)),
    s.actualizado_en=NOW()
WHERE p.codigo IN ('pro','studio') AND s.estado='trial';