-- ============================================================
-- MIGRATION FIX - pmse_perawatan
-- Mengatasi semua mismatch kolom antara DB lama & kode baru
-- ============================================================
USE pmse_perawatan;
SET @db = DATABASE();

-- ══════════════════════════════════════════════════════════════
-- 1. data_pegawai: Sinkronkan kolom nama & kolom baru
--    Schema lama (schema.sql) → Nama_Pegawai, Role
--    Kode baru (masterdata.js) → Nama, Jabatan_Kategori, Satker_Perawatan
--    auth.js sudah handle keduanya tapi INSERT/UPDATE via masterdata akan gagal
-- ══════════════════════════════════════════════════════════════

-- 1a. Tambah alias kolom Nama (kode baru pakai Nama, bukan Nama_Pegawai)
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_pegawai' AND COLUMN_NAME='Nama')=0,
  'ALTER TABLE data_pegawai ADD COLUMN Nama VARCHAR(100) NULL AFTER NIK_Pegawai',
  'SELECT "Nama ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1b. Sync Nama ← Nama_Pegawai jika ada
UPDATE data_pegawai SET Nama = Nama_Pegawai
WHERE (Nama IS NULL OR Nama='') AND (Nama_Pegawai IS NOT NULL AND Nama_Pegawai != '');

-- 1c. Tambah Jabatan_Kategori (kode baru) — sync dari Role jika ada
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_pegawai' AND COLUMN_NAME='Jabatan_Kategori')=0,
  'ALTER TABLE data_pegawai ADD COLUMN Jabatan_Kategori VARCHAR(100) NULL',
  'SELECT "Jabatan_Kategori ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Sync Jabatan_Kategori dari Role (Planner → Planner, approver → Approver, dll)
UPDATE data_pegawai
SET Jabatan_Kategori = CASE
  WHEN LOWER(Role) = 'planner'         THEN 'Planner'
  WHEN LOWER(Role) = 'approver'        THEN 'Approver'
  WHEN LOWER(Role) = 'admin'           THEN 'Admin'
  WHEN LOWER(Role) = 'supervisor'      THEN 'Supervisor'
  WHEN LOWER(Role) = 'manager'         THEN 'Manager'
  WHEN LOWER(Role) = 'general manager' THEN 'General Manager'
  WHEN LOWER(Role) = 'monitoring'      THEN 'Monitoring'
  ELSE Role
END
WHERE (Jabatan_Kategori IS NULL OR Jabatan_Kategori='') AND Role IS NOT NULL;

-- 1d. Tambah Satker_Perawatan jika belum ada
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_pegawai' AND COLUMN_NAME='Satker_Perawatan')=0,
  'ALTER TABLE data_pegawai ADD COLUMN Satker_Perawatan VARCHAR(100) NULL',
  'SELECT "Satker_Perawatan ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1e. Tambah NIK_Pegawai jika belum ada (misal tabel lama pakai NIK saja)
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_pegawai' AND COLUMN_NAME='NIK_Pegawai')=0,
  'ALTER TABLE data_pegawai ADD COLUMN NIK_Pegawai VARCHAR(30) NULL UNIQUE',
  'SELECT "NIK_Pegawai ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Sync NIK_Pegawai ← NIK jika kosong
UPDATE data_pegawai SET NIK_Pegawai = NIK
WHERE (NIK_Pegawai IS NULL OR NIK_Pegawai='') AND NIK IS NOT NULL AND NIK != '';

-- 1f. Tambah is_first_login jika belum ada (auth.js butuh ini)
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_pegawai' AND COLUMN_NAME='is_first_login')=0,
  'ALTER TABLE data_pegawai ADD COLUMN is_first_login TINYINT(1) NOT NULL DEFAULT 1',
  'SELECT "is_first_login ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1g. Tambah is_active jika belum ada
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_pegawai' AND COLUMN_NAME='is_active')=0,
  'ALTER TABLE data_pegawai ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1',
  'SELECT "is_active ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ══════════════════════════════════════════════════════════════
-- 2. data_operator: Sinkronkan skema
--    migration_full.sql buat tabel dengan: id INT AUTO_INCREMENT PK, Nama, NIK, NIK_Operator, Divisi, Satker_Perawatan
--    masterdata.js (auto-create) buat dengan: NIK_Operator PK, Nama_Operator, Tipe_Unit, No_HP, Status
--    → tambah kolom yang kurang di tabel lama
-- ══════════════════════════════════════════════════════════════

-- Tambah Nama_Operator jika belum ada (masterdata.js pakai Nama_Operator, tabel lama pakai Nama)
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_operator' AND COLUMN_NAME='Nama_Operator')=0,
  'ALTER TABLE data_operator ADD COLUMN Nama_Operator VARCHAR(100) NULL',
  'SELECT "Nama_Operator ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Sync Nama_Operator ← Nama
UPDATE data_operator SET Nama_Operator = Nama
WHERE (Nama_Operator IS NULL OR Nama_Operator='') AND (Nama IS NOT NULL AND Nama != '');

-- Tambah Tipe_Unit jika belum ada
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_operator' AND COLUMN_NAME='Tipe_Unit')=0,
  'ALTER TABLE data_operator ADD COLUMN Tipe_Unit VARCHAR(100) NULL',
  'SELECT "Tipe_Unit ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Tambah No_HP jika belum ada
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_operator' AND COLUMN_NAME='No_HP')=0,
  'ALTER TABLE data_operator ADD COLUMN No_HP VARCHAR(20) NULL',
  'SELECT "No_HP ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Tambah Status jika belum ada
SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_operator' AND COLUMN_NAME='Status')=0,
  "ALTER TABLE data_operator ADD COLUMN Status VARCHAR(20) NULL DEFAULT 'Aktif'",
  'SELECT "Status ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ══════════════════════════════════════════════════════════════
-- 3. data_cc: Sinkronkan kolom Elemen & Deskripsi
--    schema.sql punya: Cost_Center, Keterangan
--    master.js query:  SELECT Cost_Center, Deskripsi AS Keterangan dan SELECT DISTINCT Elemen
--    → tambah Elemen & Deskripsi (alias Keterangan)
-- ══════════════════════════════════════════════════════════════

SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_cc' AND COLUMN_NAME='Elemen')=0,
  'ALTER TABLE data_cc ADD COLUMN Elemen VARCHAR(100) NULL',
  'SELECT "Elemen ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='data_cc' AND COLUMN_NAME='Deskripsi')=0,
  'ALTER TABLE data_cc ADD COLUMN Deskripsi VARCHAR(255) NULL',
  'SELECT "Deskripsi ok"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Sync Deskripsi ← Keterangan jika ada
UPDATE data_cc SET Deskripsi = Keterangan WHERE (Deskripsi IS NULL OR Deskripsi='') AND Keterangan IS NOT NULL;

-- ══════════════════════════════════════════════════════════════
-- 4. data_unit: Perbesar Satker_Perawatan (30→50) sudah di migration_full
-- ══════════════════════════════════════════════════════════════

-- ══════════════════════════════════════════════════════════════
-- 5. maintenance_record & progress_perbaikan: sudah di migration_full.sql
-- ══════════════════════════════════════════════════════════════

-- ══════════════════════════════════════════════════════════════
-- 6. Tabel-tabel baru yang dibutuhkan (sudah di migration_full tapi pastikan ada)
-- ══════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS master_suku_cadang (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  kode       VARCHAR(100) NULL, nama VARCHAR(255) NULL, brand VARCHAR(100) NULL,
  satuan     VARCHAR(50)  NULL, stock_msf DECIMAL(10,2) NULL DEFAULT 0,
  stock_tal  DECIMAL(10,2) NULL DEFAULT 0, keterangan TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS master_vendor (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  kode_vendor VARCHAR(50) NULL, nama_vendor VARCHAR(255) NULL, alamat TEXT NULL,
  no_telp     VARCHAR(50) NULL, email VARCHAR(100) NULL, pic VARCHAR(100) NULL,
  no_telp_pic VARCHAR(50) NULL, keterangan TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS master_kontrak (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  no_kontrak    VARCHAR(100) NULL, nama_vendor VARCHAR(255) NULL,
  nilai_kontrak VARCHAR(100) NULL, tgl_mulai DATE NULL, tgl_selesai DATE NULL,
  status        VARCHAR(50) NULL DEFAULT 'Draft', keterangan TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS data_delay (
  id        INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Kategori  VARCHAR(150) NOT NULL, Urutan INT NULL DEFAULT 99,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT 'Migration Fix selesai!' AS status;

SELECT 
  'data_pegawai' AS tabel,
  COUNT(*) AS jumlah_baris
FROM data_pegawai
UNION ALL SELECT 'data_operator', COUNT(*) FROM data_operator
UNION ALL SELECT 'data_cc', COUNT(*) FROM data_cc
UNION ALL SELECT 'master_suku_cadang', COUNT(*) FROM master_suku_cadang
UNION ALL SELECT 'master_kontrak', COUNT(*) FROM master_kontrak;