/*
  04_compare_webpos_vs_erp_sync_tables.sql
  Tujuan:
  - Membandingkan kesesuaian tabel sinkron WEB_POS vs ERP pada level schema.
  - Fokus ke tabel list_update_table() yang dipakai jalur DataSync/doSync_autoV2.
  - Read-only, tidak mengubah data/schema.

  Cara pakai:
  1) Ubah @db_webpos dan @db_erp sesuai nama database Anda.
  2) Jalankan script ini di server MariaDB yang dapat mengakses kedua database tsb.
*/

SET @db_webpos_host := '192.168.5.14';
SET @db_webpos_user := 'admin';
SET @db_webpos_pass := 'mayanet619955';
SET @db_webpos := 'beta_sb_pos';

SET @db_erp_host    := '192.168.5.10';
SET @db_erp_user    := 'admin';
SET @db_erp_pass    := 'mayanet619955';
SET @db_erp    := 'run_sumber_boga_modul';

SELECT
    NOW() AS run_at,
    @db_webpos AS webpos_db,
    @db_erp AS erp_db,
    @@version AS mariadb_version;

DROP TEMPORARY TABLE IF EXISTS tmp_sync_tables;
CREATE TEMPORARY TABLE tmp_sync_tables (
    table_name VARCHAR(128) NOT NULL PRIMARY KEY
) ENGINE=Memory;

INSERT INTO tmp_sync_tables (table_name) VALUES
('produk'),
('price'),
('price_last_purchase'),
('price_per_area'),
('satuan_produk_relasi'),
('satuan'),
('diskon'),
('diskon_customer'),
('produk_folders'),
('per_cabang'),
('per_cabang_device'),
('per_customer_level'),
('per_customers'),
('bank'),
('company_profile'),
('setting_struk'),
('fifo_avg'),
('per_employee');

-- 1) Cek eksistensi tabel pada kedua DB.
SELECT
    s.table_name,
    CASE WHEN tw.TABLE_NAME IS NULL THEN 'MISSING' ELSE 'OK' END AS webpos_table_status,
    CASE WHEN te.TABLE_NAME IS NULL THEN 'MISSING' ELSE 'OK' END AS erp_table_status
FROM tmp_sync_tables s
LEFT JOIN information_schema.TABLES tw
    ON tw.TABLE_SCHEMA = @db_webpos
   AND tw.TABLE_NAME = s.table_name
LEFT JOIN information_schema.TABLES te
    ON te.TABLE_SCHEMA = @db_erp
   AND te.TABLE_NAME = s.table_name
ORDER BY s.table_name;

-- 2) Ringkasan jumlah kolom per tabel.
SELECT
    s.table_name,
    IFNULL(cw.col_count, 0) AS webpos_col_count,
    IFNULL(ce.col_count, 0) AS erp_col_count,
    CASE
        WHEN IFNULL(cw.col_count, 0) = 0 OR IFNULL(ce.col_count, 0) = 0 THEN 'TABLE_MISSING'
        WHEN IFNULL(cw.col_count, 0) = IFNULL(ce.col_count, 0) THEN 'MATCH'
        ELSE 'MISMATCH'
    END AS column_count_status
FROM tmp_sync_tables s
LEFT JOIN (
    SELECT TABLE_NAME, COUNT(*) AS col_count
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_webpos
    GROUP BY TABLE_NAME
) cw
    ON cw.TABLE_NAME = s.table_name
LEFT JOIN (
    SELECT TABLE_NAME, COUNT(*) AS col_count
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_erp
    GROUP BY TABLE_NAME
) ce
    ON ce.TABLE_NAME = s.table_name
ORDER BY s.table_name;

-- 3) Kolom yang ada di ERP tapi tidak ada di WEB_POS (akan menyebabkan mismatch).
SELECT
    'ERP_ONLY' AS diff_type,
    s.table_name,
    ce.COLUMN_NAME,
    ce.COLUMN_TYPE,
    ce.IS_NULLABLE,
    IFNULL(CAST(ce.COLUMN_DEFAULT AS CHAR), 'NULL') AS column_default
FROM tmp_sync_tables s
JOIN information_schema.COLUMNS ce
    ON ce.TABLE_SCHEMA = @db_erp
   AND ce.TABLE_NAME = s.table_name
LEFT JOIN information_schema.COLUMNS cw
    ON cw.TABLE_SCHEMA = @db_webpos
   AND cw.TABLE_NAME = ce.TABLE_NAME
   AND cw.COLUMN_NAME = ce.COLUMN_NAME
WHERE cw.COLUMN_NAME IS NULL
ORDER BY s.table_name, ce.ORDINAL_POSITION;

-- 4) Kolom yang ada di WEB_POS tapi tidak ada di ERP (juga memicu mismatch count).
SELECT
    'WEBPOS_ONLY' AS diff_type,
    s.table_name,
    cw.COLUMN_NAME,
    cw.COLUMN_TYPE,
    cw.IS_NULLABLE,
    IFNULL(CAST(cw.COLUMN_DEFAULT AS CHAR), 'NULL') AS column_default
FROM tmp_sync_tables s
JOIN information_schema.COLUMNS cw
    ON cw.TABLE_SCHEMA = @db_webpos
   AND cw.TABLE_NAME = s.table_name
LEFT JOIN information_schema.COLUMNS ce
    ON ce.TABLE_SCHEMA = @db_erp
   AND ce.TABLE_NAME = cw.TABLE_NAME
   AND ce.COLUMN_NAME = cw.COLUMN_NAME
WHERE ce.COLUMN_NAME IS NULL
ORDER BY s.table_name, cw.ORDINAL_POSITION;

-- 5) Cek kolom last_update di ERP (kritis untuk doSync_autoV2 query filter last_update > ...).
SELECT
    s.table_name,
    CASE WHEN c.COLUMN_NAME IS NULL THEN 'MISSING_LAST_UPDATE' ELSE 'OK' END AS erp_last_update_status
FROM tmp_sync_tables s
LEFT JOIN information_schema.COLUMNS c
    ON c.TABLE_SCHEMA = @db_erp
   AND c.TABLE_NAME = s.table_name
   AND c.COLUMN_NAME = 'last_update'
ORDER BY s.table_name;

-- 6) Info kolom last_update di WEB_POS (untuk efisiensi delta pull).
SELECT
    s.table_name,
    CASE WHEN c.COLUMN_NAME IS NULL THEN 'NO_LAST_UPDATE' ELSE 'OK' END AS webpos_last_update_status
FROM tmp_sync_tables s
LEFT JOIN information_schema.COLUMNS c
    ON c.TABLE_SCHEMA = @db_webpos
   AND c.TABLE_NAME = s.table_name
   AND c.COLUMN_NAME = 'last_update'
ORDER BY s.table_name;

