/*
ICYLAB Billing v6.15
MIGRACION DE IDEMPOTENCIA PARA PAGOS Y COBROS PRODUCTIVOS.

Ejecutar exclusivamente sobre icylab_app con FINANCE=false e ISSUE=false.
La migracion no crea pagos ni modifica saldos.
*/

SET @db_name := DATABASE();

SELECT
    '00_PRECHECK' AS section,
    @db_name AS database_name,
    (SELECT COUNT(*)
       FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'billing_payment_batches'
        AND COLUMN_NAME = 'idempotency_key') AS existing_column,
    (SELECT COUNT(*)
       FROM information_schema.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'billing_payment_batches'
        AND INDEX_NAME = 'uq_payment_batch_idempotency') AS existing_index;

SET @add_column_sql := IF(
    @db_name = 'icylab_app'
    AND NOT EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'billing_payment_batches'
          AND COLUMN_NAME = 'idempotency_key'
    ),
    'ALTER TABLE billing_payment_batches ADD COLUMN idempotency_key VARCHAR(64) NULL AFTER notes',
    'SELECT ''SKIP: idempotency_key already exists or wrong database'' AS migration_message'
);
PREPARE stmt_add_column FROM @add_column_sql;
EXECUTE stmt_add_column;
DEALLOCATE PREPARE stmt_add_column;

SET @duplicate_keys := (
    SELECT COUNT(*)
    FROM (
        SELECT environment, direction, idempotency_key
        FROM billing_payment_batches
        WHERE idempotency_key IS NOT NULL
          AND TRIM(idempotency_key) <> ''
        GROUP BY environment, direction, idempotency_key
        HAVING COUNT(*) > 1
    ) duplicate_groups
);

SET @add_index_sql := IF(
    @db_name = 'icylab_app'
    AND @duplicate_keys = 0
    AND NOT EXISTS (
        SELECT 1
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'billing_payment_batches'
          AND INDEX_NAME = 'uq_payment_batch_idempotency'
    ),
    'CREATE UNIQUE INDEX uq_payment_batch_idempotency ON billing_payment_batches (environment, direction, idempotency_key)',
    'SELECT ''SKIP: index already exists, duplicate keys found, or wrong database'' AS migration_message'
);
PREPARE stmt_add_index FROM @add_index_sql;
EXECUTE stmt_add_index;
DEALLOCATE PREPARE stmt_add_index;

SELECT
    '01_RESULT' AS section,
    (SELECT COUNT(*)
       FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'billing_payment_batches'
        AND COLUMN_NAME = 'idempotency_key') AS idempotency_column_rows,
    (SELECT COUNT(*)
       FROM information_schema.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'billing_payment_batches'
        AND INDEX_NAME = 'uq_payment_batch_idempotency'
        AND NON_UNIQUE = 0) AS unique_index_rows,
    @duplicate_keys AS duplicate_key_groups,
    CASE
        WHEN @db_name <> 'icylab_app'
            THEN 'BLOCKED: wrong database'
        WHEN (SELECT COUNT(*) FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'billing_payment_batches'
                AND COLUMN_NAME = 'idempotency_key') <> 1
            THEN 'BLOCKED: idempotency column missing'
        WHEN (SELECT COUNT(*) FROM information_schema.STATISTICS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'billing_payment_batches'
                AND INDEX_NAME = 'uq_payment_batch_idempotency'
                AND NON_UNIQUE = 0) < 1
            THEN 'BLOCKED: unique idempotency index missing'
        WHEN @duplicate_keys <> 0
            THEN 'BLOCKED: duplicate idempotency keys exist'
        ELSE 'PASS: payment idempotency schema installed'
    END AS result;
