/*
ICYLAB OS v9.0.4
Reversa auditada de pagos y recuperación del flujo factura -> cotización -> oportunidad.

Ejecutar una vez desde phpMyAdmin sobre icylab_app, antes de subir los archivos PHP/JS.
La migración no anula pagos ni cambia saldos por sí sola.
*/

SET NAMES utf8mb4;
SET @db_name := DATABASE();
SET @ready := IF(
  @db_name='icylab_app' AND EXISTS (
    SELECT 1 FROM information_schema.tables
    WHERE table_schema=DATABASE() AND table_name='billing_payments'
  ),
  1,
  0
);

SELECT
  '00_PRECHECK' AS section,
  @db_name AS database_name,
  (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='billing_payments') AS payments_table,
  (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='billing_payments' AND column_name='is_voided') AS existing_void_column;

SET @sql := IF(
  @ready=1 AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema=DATABASE() AND table_name='billing_payments' AND column_name='is_voided'
  ),
  'ALTER TABLE billing_payments ADD COLUMN is_voided TINYINT(1) NOT NULL DEFAULT 0 AFTER notes',
  'SELECT ''SKIP is_voided'' AS migration_message'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  @ready=1 AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema=DATABASE() AND table_name='billing_payments' AND column_name='voided_at'
  ),
  'ALTER TABLE billing_payments ADD COLUMN voided_at DATETIME NULL AFTER is_voided',
  'SELECT ''SKIP voided_at'' AS migration_message'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  @ready=1 AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema=DATABASE() AND table_name='billing_payments' AND column_name='voided_by'
  ),
  'ALTER TABLE billing_payments ADD COLUMN voided_by INT NULL AFTER voided_at',
  'SELECT ''SKIP voided_by'' AS migration_message'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  @ready=1 AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema=DATABASE() AND table_name='billing_payments' AND column_name='void_reason'
  ),
  'ALTER TABLE billing_payments ADD COLUMN void_reason VARCHAR(500) NULL AFTER voided_by',
  'SELECT ''SKIP void_reason'' AS migration_message'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  @ready=1 AND NOT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema=DATABASE() AND table_name='billing_payments' AND index_name='idx_payment_active_collection'
  ),
  'CREATE INDEX idx_payment_active_collection ON billing_payments (collection_id,direction,is_voided,payment_date)',
  'SELECT ''SKIP idx_payment_active_collection'' AS migration_message'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  @ready=1,
  'CREATE TABLE IF NOT EXISTS billing_payment_audit_logs (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, payment_id BIGINT UNSIGNED NOT NULL, action_key VARCHAR(40) NOT NULL, before_json LONGTEXT NULL, after_json LONGTEXT NULL, actor_user_id INT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_payment_audit_payment (payment_id,created_at), KEY idx_payment_audit_actor (actor_user_id,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci',
  'SELECT ''BLOCKED: select icylab_app before running this migration'' AS migration_message'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @payments_already_voided := NULL;
SET @sql := IF(
  @ready=1 AND EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema=DATABASE() AND table_name='billing_payments' AND column_name='is_voided'
  ),
  'SELECT COUNT(*) INTO @payments_already_voided FROM billing_payments WHERE COALESCE(is_voided,0)=1',
  'SELECT NULL INTO @payments_already_voided'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT
  '01_RESULT' AS section,
  CASE
    WHEN @db_name<>'icylab_app' THEN 'BLOCKED: wrong database'
    WHEN @ready<>1 THEN 'BLOCKED: billing_payments table missing'
    WHEN (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='billing_payments' AND column_name IN ('is_voided','voided_at','voided_by','void_reason'))<>4 THEN 'BLOCKED: payment reversal columns missing'
    WHEN (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='billing_payment_audit_logs')<>1 THEN 'BLOCKED: audit table missing'
    ELSE 'PASS: ICYLAB OS v9.0.4 payment reversal installed'
  END AS result,
  @payments_already_voided AS payments_already_voided;
