/*
  02_fix_erp_integration_minimal.sql
  Tujuan:
  - Menyiapkan minimum schema ERP agar UAT integrasi WEB_POS + PyPOS tidak gagal karena mismatch tabel/kolom.
  - Non-destruktif: hanya CREATE TABLE IF NOT EXISTS / ADD COLUMN / ADD INDEX jika belum ada.
  - Tidak ada DROP COLUMN, tidak ada rename kolom.
*/

SET @db_name := DATABASE();

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

-- =====================================================================
-- A) Pastikan tabel transaksi_pos_create_file_log kompatibel endpoint lama + baru
-- =====================================================================
CREATE TABLE IF NOT EXISTS `transaksi_pos_create_file_log` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `id_machine` varchar(200) DEFAULT NULL,
    `db_table` varchar(300) DEFAULT NULL,
    `time_create` datetime DEFAULT NULL,
    `lastid` varchar(300) DEFAULT NULL,
    `file_name` text,
    `row` int(11) DEFAULT NULL,
    `file_size` int(11) DEFAULT NULL,
    `actual_file_size` int(11) DEFAULT NULL,
    `checksum` varchar(64) DEFAULT NULL,
    `version` varchar(16) DEFAULT NULL,
    `file_path` text,
    `status` int(11) DEFAULT 0,
    `time_sent` datetime DEFAULT NULL,
    `time_delivery` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'id_machine'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `id_machine` varchar(200) DEFAULT NULL', 'SELECT ''skip add id_machine'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'db_table'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `db_table` varchar(300) DEFAULT NULL', 'SELECT ''skip add db_table'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'time_create'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `time_create` datetime DEFAULT NULL', 'SELECT ''skip add time_create'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'lastid'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `lastid` varchar(300) DEFAULT NULL', 'SELECT ''skip add lastid'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'file_name'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `file_name` text', 'SELECT ''skip add file_name'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'row'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `row` int(11) DEFAULT NULL', 'SELECT ''skip add row'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'file_size'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `file_size` int(11) DEFAULT NULL', 'SELECT ''skip add file_size'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'file_path'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `file_path` text', 'SELECT ''skip add file_path'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'status'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `status` int(11) DEFAULT 0', 'SELECT ''skip add status'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'time_sent'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `time_sent` datetime DEFAULT NULL', 'SELECT ''skip add time_sent'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'time_delivery'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `time_delivery` datetime DEFAULT NULL', 'SELECT ''skip add time_delivery'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'actual_file_size'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `actual_file_size` int(11) DEFAULT NULL', 'SELECT ''skip add actual_file_size'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'checksum'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `checksum` varchar(64) DEFAULT NULL', 'SELECT ''skip add checksum'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND COLUMN_NAME = 'version'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD COLUMN `version` varchar(16) DEFAULT NULL', 'SELECT ''skip add version'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND INDEX_NAME = 'idx_tpcfl_machine_status'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD INDEX `idx_tpcfl_machine_status` (`id_machine`, `status`)', 'SELECT ''skip add idx_tpcfl_machine_status'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
      AND INDEX_NAME = 'idx_tpcfl_time_delivery'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `transaksi_pos_create_file_log` ADD INDEX `idx_tpcfl_time_delivery` (`time_delivery`)', 'SELECT ''skip add idx_tpcfl_time_delivery'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================================
-- B) Tabel upload idempotency (untuk endpoint setUploadStream__GLG / compile status)
-- =====================================================================
CREATE TABLE IF NOT EXISTS `upload_idempotency_registry` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `idempotency_key` CHAR(64) NOT NULL,
    `machine_id` VARCHAR(128) NOT NULL DEFAULT '',
    `file_hash` CHAR(64) NOT NULL DEFAULT '',
    `file_seq` BIGINT(20) NOT NULL DEFAULT 0,
    `db_table` VARCHAR(128) DEFAULT NULL,
    `status` VARCHAR(32) NOT NULL DEFAULT 'RECEIVED',
    `file_name` VARCHAR(255) DEFAULT NULL,
    `file_path` VARCHAR(1024) DEFAULT NULL,
    `error_message` TEXT DEFAULT NULL,
    `response_json` MEDIUMTEXT DEFAULT NULL,
    `compiled_transaksi` TINYINT(1) NOT NULL DEFAULT 0,
    `compiled_data` TINYINT(1) NOT NULL DEFAULT 0,
    `compiled_registry` TINYINT(1) NOT NULL DEFAULT 0,
    `first_seen_at` DATETIME DEFAULT NULL,
    `last_seen_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_idempotency_key` (`idempotency_key`),
    KEY `idx_machine_status_seen` (`machine_id`, `status`, `last_seen_at`),
    KEY `idx_status_seen` (`status`, `last_seen_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME = 'db_table'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD COLUMN `db_table` VARCHAR(128) NULL', 'SELECT ''skip add upload.db_table'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME = 'file_name'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD COLUMN `file_name` VARCHAR(255) NULL', 'SELECT ''skip add upload.file_name'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME = 'file_path'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD COLUMN `file_path` VARCHAR(1024) NULL', 'SELECT ''skip add upload.file_path'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME = 'error_message'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD COLUMN `error_message` TEXT NULL', 'SELECT ''skip add upload.error_message'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME = 'response_json'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD COLUMN `response_json` MEDIUMTEXT NULL', 'SELECT ''skip add upload.response_json'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME = 'compiled_transaksi'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD COLUMN `compiled_transaksi` TINYINT(1) NOT NULL DEFAULT 0', 'SELECT ''skip add upload.compiled_transaksi'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME = 'compiled_data'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD COLUMN `compiled_data` TINYINT(1) NOT NULL DEFAULT 0', 'SELECT ''skip add upload.compiled_data'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME = 'compiled_registry'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD COLUMN `compiled_registry` TINYINT(1) NOT NULL DEFAULT 0', 'SELECT ''skip add upload.compiled_registry'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME = 'first_seen_at'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD COLUMN `first_seen_at` DATETIME NULL', 'SELECT ''skip add upload.first_seen_at'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND COLUMN_NAME = 'last_seen_at'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD COLUMN `last_seen_at` DATETIME NULL', 'SELECT ''skip add upload.last_seen_at'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND INDEX_NAME = 'uk_idempotency_key'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD UNIQUE KEY `uk_idempotency_key` (`idempotency_key`)', 'SELECT ''skip add upload.uk_idempotency_key'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND INDEX_NAME = 'idx_machine_status_seen'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD KEY `idx_machine_status_seen` (`machine_id`, `status`, `last_seen_at`)', 'SELECT ''skip add upload.idx_machine_status_seen'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_registry'
      AND INDEX_NAME = 'idx_status_seen'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `upload_idempotency_registry` ADD KEY `idx_status_seen` (`status`, `last_seen_at`)', 'SELECT ''skip add upload.idx_status_seen'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================================
-- C) Tabel settlement idempotency (untuk endpoint settlement PyPOS)
-- =====================================================================
CREATE TABLE IF NOT EXISTS `settlement_idempotency_registry` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `idempotency_key` VARCHAR(128) NOT NULL,
    `request_hash` CHAR(64) NOT NULL,
    `machine_id` VARCHAR(64) NOT NULL DEFAULT '',
    `date_settlement` VARCHAR(32) NOT NULL DEFAULT '',
    `id_penjualan_hash` CHAR(64) NOT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'PROCESSING',
    `settlement_id` VARCHAR(128) DEFAULT NULL,
    `trace_id` VARCHAR(128) DEFAULT NULL,
    `error_code` VARCHAR(64) DEFAULT NULL,
    `error_message` TEXT,
    `first_seen_at` DATETIME NOT NULL,
    `last_seen_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_idempotency_key` (`idempotency_key`),
    KEY `idx_tuple` (`machine_id`, `date_settlement`),
    KEY `idx_status` (`status`),
    KEY `idx_last_seen_at` (`last_seen_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'settlement_idempotency_registry'
      AND COLUMN_NAME = 'trace_id'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `settlement_idempotency_registry` ADD COLUMN `trace_id` VARCHAR(128) NULL', 'SELECT ''skip add settlement.trace_id'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'settlement_idempotency_registry'
      AND COLUMN_NAME = 'error_code'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `settlement_idempotency_registry` ADD COLUMN `error_code` VARCHAR(64) NULL', 'SELECT ''skip add settlement.error_code'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'settlement_idempotency_registry'
      AND COLUMN_NAME = 'error_message'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `settlement_idempotency_registry` ADD COLUMN `error_message` TEXT NULL', 'SELECT ''skip add settlement.error_message'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'settlement_idempotency_registry'
      AND INDEX_NAME = 'uk_idempotency_key'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `settlement_idempotency_registry` ADD UNIQUE KEY `uk_idempotency_key` (`idempotency_key`)', 'SELECT ''skip add settlement.uk_idempotency_key'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'settlement_idempotency_registry'
      AND INDEX_NAME = 'idx_tuple'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `settlement_idempotency_registry` ADD KEY `idx_tuple` (`machine_id`, `date_settlement`)', 'SELECT ''skip add settlement.idx_tuple'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'settlement_idempotency_registry'
      AND INDEX_NAME = 'idx_status'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `settlement_idempotency_registry` ADD KEY `idx_status` (`status`)', 'SELECT ''skip add settlement.idx_status'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'settlement_idempotency_registry'
      AND INDEX_NAME = 'idx_last_seen_at'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `settlement_idempotency_registry` ADD KEY `idx_last_seen_at` (`last_seen_at`)', 'SELECT ''skip add settlement.idx_last_seen_at'' AS note');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================================
-- D) Verifikasi ringkas setelah fix
-- =====================================================================
SELECT
    req.table_name,
    CASE WHEN t.TABLE_NAME IS NULL THEN 'MISSING' ELSE 'OK' END AS table_status
FROM (
    SELECT 'transaksi_pos_create_file_log' AS table_name
    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;

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';

