-- Sprint 24F: conciliación, reintentos, alertas y operación financiera Owner.
-- No conecta pasarelas, no ejecuta cargos ni reembolsos reales.

CREATE TABLE IF NOT EXISTS billing_pago_intentos (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 id_transaccion BIGINT UNSIGNED NOT NULL,
 accion ENUM('reintento','conciliacion','recuperacion','ajuste','reembolso_registro') NOT NULL,
 resultado ENUM('programado','simulado','aplicado','omitido','error') NOT NULL DEFAULT 'programado',
 mensaje_seguro VARCHAR(255) NULL,
 idempotency_key VARCHAR(190) NOT NULL,
 programado_en DATETIME NULL,
 ejecutado_en DATETIME NULL,
 creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_bpi_idempotency (idempotency_key),
 KEY ix_bpi_transaction_date (id_transaccion,creado_en),
 CONSTRAINT fk_bpi_transaction FOREIGN KEY (id_transaccion) REFERENCES billing_transacciones(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_conciliaciones (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 id_proveedor BIGINT UNSIGNED NOT NULL,
 entorno ENUM('sandbox','produccion') NOT NULL DEFAULT 'sandbox',
 periodo CHAR(7) NOT NULL,
 huella CHAR(64) NOT NULL,
 idempotency_key CHAR(64) NOT NULL,
 estado ENUM('pendiente','conciliada','con_diferencias','revisando','cerrada','error') NOT NULL DEFAULT 'pendiente',
 total_local DECIMAL(14,2) NOT NULL DEFAULT 0,
 total_proveedor DECIMAL(14,2) NOT NULL DEFAULT 0,
 diferencia DECIMAL(14,2) NOT NULL DEFAULT 0,
 detalle_json LONGTEXT NULL,
 creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 conciliado_en DATETIME NULL,
 UNIQUE KEY uq_bc_idempotency (idempotency_key),
 UNIQUE KEY uq_bc_provider_env_period (id_proveedor,entorno,periodo),
 CONSTRAINT fk_bc_provider FOREIGN KEY (id_proveedor) REFERENCES billing_proveedores(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_alertas_financieras (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 id_empresa BIGINT UNSIGNED NULL,
 codigo VARCHAR(80) NOT NULL,
 severidad ENUM('info','advertencia','critica') NOT NULL DEFAULT 'advertencia',
 mensaje VARCHAR(255) NOT NULL,
 huella CHAR(64) NOT NULL,
 estado ENUM('abierta','revisando','cerrada') NOT NULL DEFAULT 'abierta',
 creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 cerrado_en DATETIME NULL,
 UNIQUE KEY uq_baf_huella (huella),
 KEY ix_baf_state_severity (estado,severidad),
 CONSTRAINT fk_baf_company FOREIGN KEY (id_empresa) REFERENCES org_empresas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_periodos_gracia_eventos (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 id_suscripcion BIGINT UNSIGNED NOT NULL,
 evento ENUM('inicio','extension','recuperacion','vencimiento') NOT NULL,
 origen VARCHAR(60) NOT NULL DEFAULT 'sistema',
 nota_segura VARCHAR(255) NULL,
 idempotency_key VARCHAR(190) NOT NULL,
 creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_bpge_idempotency (idempotency_key),
 KEY ix_bpge_subscription_date (id_suscripcion,creado_en),
 CONSTRAINT fk_bpge_subscription FOREIGN KEY (id_suscripcion) REFERENCES billing_suscripciones(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO org_permisos(codigo,modulo,nombre,descripcion)
SELECT 'billing.financial_operations.manage','billing','Operar conciliación financiera',
'Owner-only: conciliaciones, reintentos sandbox, alertas, periodos de gracia y recuperación.'
WHERE NOT EXISTS(SELECT 1 FROM org_permisos WHERE codigo='billing.financial_operations.manage');

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.financial_operations.manage'
WHERE r.codigo IN ('owner_plataforma','owner','platform_owner','superadmin','admin_plataforma');