/*
  03_preflight_web_pos_local.sql
  Tujuan:
  - Read-only check DB local WEB_POS (XAMPP) sebelum UAT integrasi ke ERP.
  - Fokus memastikan antrean upload file dari WEB_POS tidak tersumbat.
*/

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;

-- 1) Cek tabel wajib WEB_POS local.
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'
) req
LEFT JOIN information_schema.TABLES t
    ON t.TABLE_SCHEMA = @db_name
   AND t.TABLE_NAME = req.table_name;

-- 2) Cek kolom transaksi_pos_create_file_log yang dipakai WEB_POS NonRest.
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, 'time_create'
    UNION ALL SELECT 5, 'lastid'
    UNION ALL SELECT 6, 'file_name'
    UNION ALL SELECT 7, 'file_path'
    UNION ALL SELECT 8, 'row'
    UNION ALL SELECT 9, 'file_size'
    UNION ALL SELECT 10, 'status'
    UNION ALL SELECT 11, 'time_sent'
) 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 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 local file upload.
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,
         MAX(time_create) AS newest_time_create
       FROM transaksi_pos_create_file_log
      WHERE status = 0',
    'SELECT NULL AS pending_status_0, NULL AS oldest_time_create, NULL AS newest_time_create'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5) Deteksi potensi duplikasi antrean per mesin+tabel+lastid.
SET @sql := IF(
    @has_tpcfl = 1,
    'SELECT
         id_machine,
         db_table,
         lastid,
         COUNT(*) AS duplicate_count
       FROM transaksi_pos_create_file_log
      WHERE status = 0
      GROUP BY id_machine, db_table, lastid
      HAVING COUNT(*) > 1
      ORDER BY duplicate_count DESC, id_machine, db_table
      LIMIT 50',
    'SELECT ''transaksi_pos_create_file_log missing'' AS note'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

