/*
  05_fix_erp_add_sync_columns_from_webpos.sql
  Tujuan:
  - Menyamakan jumlah kolom tabel sinkron antara WEB_POS dan ERP
    TANPA remove kolom apa pun.
  - Sumber acuan kolom tambahan diambil dari DB WEB_POS (beta_sb_pos).

  Target eksekusi:
  - Jalankan di SERVER ERP (192.168.5.10), DB: run_sumber_boga_modul.
  - Sifat additive-only (ADD COLUMN IF NOT EXISTS).
*/

USE `run_sumber_boga_modul`;

SELECT
    NOW() AS run_at,
    DATABASE() AS active_db,
    @@hostname AS server_host,
    @@version AS mariadb_version;

/* ==========================================================
   A) Tabel: produk
   WEB_POS punya 3 kolom tambahan:
   - harga_jual2 int(11) NULL
   - diskon_persen2 decimal(5,2) NULL
   - harga_diskon2 decimal(5,2) NULL
   ========================================================== */
ALTER TABLE `produk`
    ADD COLUMN IF NOT EXISTS `harga_jual2` int(11) NULL;

ALTER TABLE `produk`
    ADD COLUMN IF NOT EXISTS `diskon_persen2` decimal(5,2) NULL;

ALTER TABLE `produk`
    ADD COLUMN IF NOT EXISTS `harga_diskon2` decimal(5,2) NULL;

/* ==========================================================
   B) Tabel: per_cabang
   WEB_POS punya 2 kolom tambahan:
   - gudang_id int(11) NOT NULL DEFAULT 0
   - gudang_nama varchar(255) NOT NULL (di WEB_POS NO/NULL default ambigu),
     dipilih aman: NOT NULL DEFAULT '' agar tidak gagal pada row existing.
   ========================================================== */
ALTER TABLE `per_cabang`
    ADD COLUMN IF NOT EXISTS `gudang_id` int(11) NOT NULL DEFAULT 0;

ALTER TABLE `per_cabang`
    ADD COLUMN IF NOT EXISTS `gudang_nama` varchar(255) NOT NULL DEFAULT '';

/* ==========================================================
   C) Tabel: per_employee
   WEB_POS punya 1 kolom tambahan:
   - gudangID int(11) NOT NULL DEFAULT 0
   ========================================================== */
ALTER TABLE `per_employee`
    ADD COLUMN IF NOT EXISTS `gudangID` int(11) NOT NULL DEFAULT 0;

/* ==========================================================
   D) Verifikasi hasil
   ========================================================== */
SELECT
    TABLE_NAME,
    COUNT(*) AS col_count
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('produk','per_cabang','per_employee')
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME;

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    IFNULL(CAST(COLUMN_DEFAULT AS CHAR), 'NULL') AS COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND (
    (TABLE_NAME='produk' AND COLUMN_NAME IN ('harga_jual2','diskon_persen2','harga_diskon2'))
    OR (TABLE_NAME='per_cabang' AND COLUMN_NAME IN ('gudang_id','gudang_nama'))
    OR (TABLE_NAME='per_employee' AND COLUMN_NAME IN ('gudangID'))
  )
ORDER BY TABLE_NAME, ORDINAL_POSITION;

