-- =====================================================
-- Excel columns (STATUS KARYAWAN, DIANGKAT KARYAWAN TETAP, PKWT BERAKHIR, Resign Date)
-- Mapping to table `employees` – verification and safe add if missing
--
-- Excel Column 29: STATUS KARYAWAN     → employees.employeestatus_id (FK, resolved via master)
-- Excel Column 30: DIANGKAT KARYAWAN TETAP (Tanggal) → employees.final_confirmation_date
-- Excel Column 31: PKWT BERAKHIR      → employees.contract_end_date
-- Excel Column 32: Resign Date        → employees.resignation_letter_date
--
-- These columns already exist in the employee model. This script only adds a column
-- if it does not exist (safe for multiple runs). Run in MySQL/phpMyAdmin.
-- =====================================================

-- Use your database name if needed: USE `your_sapps_db`;

-- Add final_confirmation_date if missing (tanggal diangkat karyawan tetap)
SET @db = DATABASE();
SET @tbl = 'employees';
SET @col = 'final_confirmation_date';
SET @q = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = @col);
SET @sql = IF(@q = 0,
  'ALTER TABLE `employees` ADD COLUMN `final_confirmation_date` DATE NULL DEFAULT NULL COMMENT ''Tanggal diangkat karyawan tetap'' AFTER `scheduled_confirmation_date`;',
  'SELECT ''Column final_confirmation_date already exists'' AS msg;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add contract_end_date if missing (PKWT berakhir)
SET @col = 'contract_end_date';
SET @q = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = @col);
SET @sql = IF(@q = 0,
  'ALTER TABLE `employees` ADD COLUMN `contract_end_date` DATE NULL DEFAULT NULL COMMENT ''PKWT berakhir'' AFTER `final_confirmation_date`;',
  'SELECT ''Column contract_end_date already exists'' AS msg;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add resignation_letter_date if missing (Resign Date)
SET @col = 'resignation_letter_date';
SET @q = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = @col);
SET @sql = IF(@q = 0,
  'ALTER TABLE `employees` ADD COLUMN `resignation_letter_date` DATE NULL DEFAULT NULL COMMENT ''Resign date'' AFTER `date_of_retirement`;',
  'SELECT ''Column resignation_letter_date already exists'' AS msg;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- employeestatus_id: already exists as FK. No ALTER needed.