-- =============================================
-- MIGRASI KEAMANAN v1
-- Jalankan setelah update kode backend
-- =============================================
USE perawatan;

-- 1. Pastikan kolom Password cukup panjang untuk bcrypt hash ($2b$12$ = 60 karakter)
ALTER TABLE data_pegawai MODIFY COLUMN Password VARCHAR(255) NOT NULL;

-- 2. Pastikan kolom is_active ada
ALTER TABLE data_pegawai ADD COLUMN IF NOT EXISTS is_active TINYINT(1) DEFAULT 1;
UPDATE data_pegawai SET is_active = 1 WHERE is_active IS NULL;

-- 3. Pastikan kolom Role ada
ALTER TABLE data_pegawai ADD COLUMN IF NOT EXISTS Role VARCHAR(30) DEFAULT 'planner';
UPDATE data_pegawai SET Role = 'planner'  WHERE Role IS NULL OR Role = '';
UPDATE data_pegawai SET Role = 'approver' WHERE jabatan_kategori IN ('Supervisor','Manager','General Manager');

-- 4. Tabel hirarki approval (menggantikan hardcode di source code)
CREATE TABLE IF NOT EXISTS data_approval_hirarki (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  NIK_Planner VARCHAR(30) NOT NULL UNIQUE COMMENT 'NIK pegawai planner',
  NIK_Apv1    VARCHAR(30) NOT NULL COMMENT 'NIK approver level 1',
  NIK_Apv2    VARCHAR(30) NOT NULL COMMENT 'NIK approver level 2',
  NIK_Apv3    VARCHAR(30) NOT NULL COMMENT 'NIK approver level 3',
  is_active   TINYINT(1)  DEFAULT 1,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Isi hirarki berdasarkan data yang sebelumnya hardcoded
INSERT IGNORE INTO data_approval_hirarki (NIK_Planner, NIK_Apv1, NIK_Apv2, NIK_Apv3) VALUES
('31833', '31385', '30662', '31196'),  -- NABILA RACHMATIKA
('31539', '31385', '30662', '31196'),  -- ANDIKA APRIANSYAH
('32158', '31385', '30662', '31196'),  -- ERWIN FIRMANSYAH
('31408', '31385', '30662', '31196'),  -- RIESANT AHMADI
('30899', '31385', '30662', '31196'),  -- ERWAN ABJATAR SIMANJUNTAK
('31877', '31385', '30909', '31196'),  -- SULIKIN
('31835', '31385', '30909', '31196'),  -- OKI TRIANTO WICAKSONO
('31733', '31385', '30909', '31196'),  -- RENI LESTARI MANSOBEN
('31120', '31385', '30909', '31196');  -- JULIAWANI

-- 5. Index tambahan untuk performa
CREATE INDEX IF NOT EXISTS idx_hirarki_planner ON data_approval_hirarki (NIK_Planner);

-- LANGKAH SELANJUTNYA (jalankan via Node.js, bukan SQL):
-- Setelah jalankan SQL ini, jalankan:
--   npm run hash-passwords
-- untuk mengenkripsi semua password plaintext dengan bcrypt