/*
  01_preflight_erp_integration.sql
  Tujuan:
  - Read-only check kesiapan DB ERP sebelum UAT integrasi WEB_POS + PyPOS.
  - Tidak ada DDL/DML perubahan data.
*/

SET @db_name := DATABASE();

SELECT
    NOW() AS run_at,
    @db_name AS active_database,
    @@version AS mariadb_version,
    @@version_comment AS version_comment;

SELECT CURRENT_USER() AS current_user;
SHOW GRANTS FOR CURRENT_USER();

-- 1) Cek tabel inti integrasi.
SELECT
    req.table_name,
    CASE WHEN t.TABLE_NAME IS NULL THEN 'MISSING' ELSE 'OK' END AS table_status,
    IFNULL(t.ENGINE, '-') AS engine_name,
    IFNULL(t.TABLE_COLLATION, '-') AS table_collation
FROM (
    SELECT 'transaksi_pos_create_file_log' AS table_name
    UNION ALL SELECT 'transaksi_pos_lastid'
    UNION ALL SELECT 'upload_idempotency_registry'
    UNION ALL SELECT 'settlement_idempotency_registry'
) req
LEFT JOIN information_schema.TABLES t
    ON t.TABLE_SCHEMA = @db_name
   AND t.TABLE_NAME = req.table_name;

-- 2) Cek kolom tabel log upload yang dipakai endpoint lama + baru.
SELECT
    req.seq,
    req.column_name,
    CASE WHEN c.COLUMN_NAME IS NULL THEN 'MISSING' ELSE 'OK' END AS column_status,
    IFNULL(c.COLUMN_TYPE, '-') AS column_type,
    IFNULL(c.IS_NULLABLE, '-') AS is_nullable,
    IFNULL(CAST(c.COLUMN_DEFAULT AS CHAR), '-') AS column_default
FROM (
    SELECT 1 AS seq, 'id' AS column_name
    UNION ALL SELECT 2, 'id_machine'
    UNION ALL SELECT 3, 'db_table'
    UNION ALL SELECT 4, 'time_create'
    UNION ALL SELECT 5, 'lastid'
    UNION ALL SELECT 6, 'file_name'
    UNION ALL SELECT 7, 'row'
    UNION ALL SELECT 8, 'file_size'
    UNION ALL SELECT 9, 'file_path'
    UNION ALL SELECT 10, 'status'
    UNION ALL SELECT 11, 'time_sent'
    UNION ALL SELECT 12, 'time_delivery'
    UNION ALL SELECT 13, 'actual_file_size'
    UNION ALL SELECT 14, 'checksum'
    UNION ALL SELECT 15, 'version'
) req
LEFT JOIN information_schema.COLUMNS c
    ON c.TABLE_SCHEMA = @db_name
   AND c.TABLE_NAME = 'transaksi_pos_create_file_log'
   AND c.COLUMN_NAME = req.column_name
ORDER BY req.seq;

-- 3) Cek kolom tabel transaksi_pos_lastid.
SELECT
    req.seq,
    req.column_name,
    CASE WHEN c.COLUMN_NAME IS NULL THEN 'MISSING' ELSE 'OK' END AS column_status,
    IFNULL(c.COLUMN_TYPE, '-') AS column_type
FROM (
    SELECT 1 AS seq, 'id' AS column_name
    UNION ALL SELECT 2, 'id_machine'
    UNION ALL SELECT 3, 'db_table'
    UNION ALL SELECT 4, 'lastid'
) req
LEFT JOIN information_schema.COLUMNS c
    ON c.TABLE_SCHEMA = @db_name
   AND c.TABLE_NAME = 'transaksi_pos_lastid'
   AND c.COLUMN_NAME = req.column_name
ORDER BY req.seq;

-- 4) Cek antrean upload ke ERP.
SET @has_tpcfl := (
    SELECT COUNT(*)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
);

SET @sql := IF(
    @has_tpcfl = 1,
    'SELECT status, COUNT(*) AS total_rows
       FROM transaksi_pos_create_file_log
      GROUP BY status
      ORDER BY status',
    'SELECT ''transaksi_pos_create_file_log'' AS table_name, ''MISSING'' AS note'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    @has_tpcfl = 1,
    'SELECT
         COUNT(*) AS pending_status_0,
         MIN(time_create) AS oldest_time_create,
         MIN(time_delivery) AS oldest_time_delivery
       FROM transaksi_pos_create_file_log
      WHERE status = 0',
    'SELECT NULL AS pending_status_0, NULL AS oldest_time_create, NULL AS oldest_time_delivery'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5) Cek health upload idempotency registry.
SET @has_upload_registry := (
    SELECT COUNT(*)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
);

SET @has_upload_flags := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME IN ('compiled_transaksi', 'compiled_data', 'compiled_registry')
);

SET @sql := IF(
    @has_upload_registry = 1,
    'SELECT status, COUNT(*) AS total_rows
       FROM upload_idempotency_registry
      GROUP BY status
      ORDER BY status',
    'SELECT ''upload_idempotency_registry'' AS table_name, ''MISSING'' AS note'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    @has_upload_registry = 1 AND @has_upload_flags = 3,
    'SELECT
         SUM(CASE WHEN status = ''PROCESSED'' AND compiled_transaksi = 1 AND compiled_data = 1 AND compiled_registry = 1 THEN 1 ELSE 0 END) AS compiled_full_rows,
         SUM(CASE WHEN status = ''PROCESSED'' AND (compiled_transaksi = 0 OR compiled_data = 0 OR compiled_registry = 0) THEN 1 ELSE 0 END) AS processed_but_pending_compile_rows,
         SUM(CASE WHEN status = ''FAILED'' THEN 1 ELSE 0 END) AS failed_rows
       FROM upload_idempotency_registry',
    IF(
        @has_upload_registry = 1,
        'SELECT ''upload_idempotency_registry exists, but compile flag columns are incomplete'' AS note',
        'SELECT ''upload_idempotency_registry missing'' AS note'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 6) Cek health settlement idempotency registry.
SET @has_settlement_registry := (
    SELECT COUNT(*)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'settlement_idempotency_registry'
);

SET @has_settlement_status_col := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'settlement_idempotency_registry'
      AND COLUMN_NAME = 'status'
);

SET @sql := IF(
    @has_settlement_registry = 1 AND @has_settlement_status_col = 1,
    'SELECT status, COUNT(*) AS total_rows
       FROM settlement_idempotency_registry
      GROUP BY status
      ORDER BY status',
    IF(
        @has_settlement_registry = 1,
        'SELECT ''settlement_idempotency_registry exists, but status column is missing'' AS note',
        'SELECT ''settlement_idempotency_registry missing'' AS note'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 7) Quick check schema drift yang paling sering bikin upload gagal.
SELECT
    SUM(CASE WHEN COLUMN_NAME = 'time_delivery' THEN 1 ELSE 0 END) AS has_time_delivery,
    SUM(CASE WHEN COLUMN_NAME = 'actual_file_size' THEN 1 ELSE 0 END) AS has_actual_file_size,
    SUM(CASE WHEN COLUMN_NAME = 'checksum' THEN 1 ELSE 0 END) AS has_checksum,
    SUM(CASE WHEN COLUMN_NAME = 'version' THEN 1 ELSE 0 END) AS has_version
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'transaksi_pos_create_file_log';

