/*
  06_monitoring_integration_healthcheck.sql
  Tujuan:
  - Health check operasional integrasi WEB_POS + PyPOS + ERP.
  - Read-only, aman dijalankan berkala (harian / sebelum deploy).
*/

SET @db_name := DATABASE();

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

-- ============================================================
-- A) Upload log legacy (WEB_POS -> ERP)
-- ============================================================
SET @has_upload_log := (
    SELECT COUNT(*)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'transaksi_pos_create_file_log'
);

SET @sql := IF(
    @has_upload_log = 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 missing'' AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    @has_upload_log = 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;

-- ============================================================
-- B) Upload idempotency (PyPOS upload stream)
-- ============================================================
SET @has_upload_registry := (
    SELECT COUNT(*)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'upload_idempotency_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 missing'' AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    @has_upload_registry = 1,
    'SELECT
         SUM(CASE WHEN status = ''FAILED'' THEN 1 ELSE 0 END) AS failed_total,
         SUM(CASE WHEN status = ''FAILED'' AND last_seen_at >= (NOW() - INTERVAL 1 DAY) THEN 1 ELSE 0 END) AS failed_last_24h
       FROM upload_idempotency_registry',
    'SELECT NULL AS failed_total, NULL AS failed_last_24h'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    @has_upload_registry = 1,
    'SELECT
         machine_id,
         status,
         COUNT(*) AS total_rows
       FROM upload_idempotency_registry
      WHERE last_seen_at >= (NOW() - INTERVAL 1 DAY)
      GROUP BY machine_id, status
      ORDER BY total_rows DESC, machine_id
      LIMIT 100',
    'SELECT ''upload_idempotency_registry missing'' AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- C) Settlement idempotency (PyPOS settlement)
-- ============================================================
SET @has_settle_registry := (
    SELECT COUNT(*)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'settlement_idempotency_registry'
);

SET @sql := IF(
    @has_settle_registry = 1,
    'SELECT status, COUNT(*) AS total_rows
       FROM settlement_idempotency_registry
      GROUP BY status
      ORDER BY status',
    'SELECT ''settlement_idempotency_registry missing'' AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    @has_settle_registry = 1,
    'SELECT
         SUM(CASE WHEN status = ''FAILED'' THEN 1 ELSE 0 END) AS failed_total,
         SUM(CASE WHEN status = ''FAILED'' AND last_seen_at >= (NOW() - INTERVAL 1 DAY) THEN 1 ELSE 0 END) AS failed_last_24h,
         SUM(CASE WHEN status = ''PROCESSING'' AND last_seen_at < (NOW() - INTERVAL 10 MINUTE) THEN 1 ELSE 0 END) AS stale_processing_over_10m
       FROM settlement_idempotency_registry',
    'SELECT NULL AS failed_total, NULL AS failed_last_24h, NULL AS stale_processing_over_10m'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    @has_settle_registry = 1,
    'SELECT
         machine_id,
         date_settlement,
         COUNT(*) AS total_rows
       FROM settlement_idempotency_registry
      WHERE status IN (''FAILED'', ''PROCESSING'')
        AND last_seen_at >= (NOW() - INTERVAL 3 DAY)
      GROUP BY machine_id, date_settlement
      ORDER BY total_rows DESC, machine_id
      LIMIT 100',
    'SELECT ''settlement_idempotency_registry missing'' AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

