-- Migration: Clock In/Out Reminder Notification Configuration
-- Description: Konfigurasi scheduler untuk Clock In/Out reminder notifications per company
-- Date: 2026-02-06
-- Safe: Yes (menambahkan settings baru, tidak merusak data existing)

-- ============================================================================
-- KONFIGURASI CLOCK REMINDER SCHEDULER PER COMPANY
-- ============================================================================
-- 
-- Notification Types:
-- 1. clock_in_reminder - Pengingat clock in sebelum jam masuk
-- 2. clock_out_reminder - Pengingat clock out sebelum jam pulang
-- 3. missed_clock_in_alert - Alert jika belum clock in setelah jam masuk
-- 4. late_arrival_alert - Alert ke supervisor jika karyawan terlambat
-- 5. overtime_warning - Peringatan jika mendekati overtime
--
-- Setting Keys (gunakan prefix "clock_reminder_"):
-- 1. clock_reminder_enabled: boolean - Enable/disable semua clock reminders
-- 2. clock_in_reminder_enabled: boolean - Enable/disable clock in reminder
-- 3. clock_in_reminder_minutes_before: number - Menit sebelum shift start (default: 30)
-- 4. clock_in_reminder_days: JSON array - Hari kerja [0,1,2,3,4,5,6] (0=Minggu)
-- 5. clock_out_reminder_enabled: boolean - Enable/disable clock out reminder
-- 6. clock_out_reminder_minutes_before: number - Menit sebelum shift end (default: 30)
-- 7. clock_out_reminder_days: JSON array - Hari kerja
-- 8. missed_clock_in_enabled: boolean - Enable/disable missed clock in alert
-- 9. missed_clock_in_minutes_after: number - Menit setelah shift start (default: 60)
-- 10. late_arrival_enabled: boolean - Enable/disable late arrival alert
-- 11. late_arrival_threshold_minutes: number - Batas keterlambatan menit (default: 15)
-- 12. overtime_warning_enabled: boolean - Enable/disable overtime warning
-- 13. overtime_warning_hours_before: number - Jam sebelum shift end (default: 2)
-- 14. notify_employee: boolean - Kirim notifikasi ke karyawan
-- 15. notify_supervisor: boolean - Kirim notifikasi ke supervisor
-- 16. notify_hr: boolean - Kirim notifikasi ke HR
-- 17. send_email: boolean - Kirim via email juga
-- 18. send_system_notification: boolean - Kirim via sistem notifikasi
--
-- ============================================================================
-- CONTOH KONFIGURASI (GANTI company_id SESUAI)
-- ============================================================================

SET @company_id = 8; -- GANTI DENGAN COMPANY ID YANG INGIN DIKONFIGURASI
SET @modified_by = 1; -- User ID yang melakukan konfigurasi
SET @owner = 1; -- Owner ID

-- ============================================================================
-- Method: Using INSERT ... ON DUPLICATE KEY UPDATE (Recommended)
-- ============================================================================

-- 1. Enable/Disable Semua Clock Reminders
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'clock_reminder_enabled', 'true', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'true', modified = NOW(), modified_by = @modified_by;

-- 2. Clock In Reminder Settings
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'clock_in_reminder_enabled', 'true', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'true', modified = NOW(), modified_by = @modified_by;

INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'clock_in_reminder_minutes_before', '30', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = '30', modified = NOW(), modified_by = @modified_by;

-- Days: 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday (Workdays)
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'clock_in_reminder_days', '[1,2,3,4,5]', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = '[1,2,3,4,5]', modified = NOW(), modified_by = @modified_by;

-- 3. Clock Out Reminder Settings
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'clock_out_reminder_enabled', 'true', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'true', modified = NOW(), modified_by = @modified_by;

INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'clock_out_reminder_minutes_before', '30', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = '30', modified = NOW(), modified_by = @modified_by;

INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'clock_out_reminder_days', '[1,2,3,4,5]', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = '[1,2,3,4,5]', modified = NOW(), modified_by = @modified_by;

-- 4. Missed Clock In Alert Settings
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'missed_clock_in_enabled', 'true', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'true', modified = NOW(), modified_by = @modified_by;

INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'missed_clock_in_minutes_after', '60', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = '60', modified = NOW(), modified_by = @modified_by;

-- 5. Late Arrival Alert Settings
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'late_arrival_enabled', 'true', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'true', modified = NOW(), modified_by = @modified_by;

INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'late_arrival_threshold_minutes', '15', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = '15', modified = NOW(), modified_by = @modified_by;

-- 6. Overtime Warning Settings
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'overtime_warning_enabled', 'false', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'false', modified = NOW(), modified_by = @modified_by;

INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'overtime_warning_hours_before', '2', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = '2', modified = NOW(), modified_by = @modified_by;

-- 7. Recipient Settings
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'notify_employee', 'true', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'true', modified = NOW(), modified_by = @modified_by;

INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'notify_supervisor', 'true', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'true', modified = NOW(), modified_by = @modified_by;

INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'notify_hr', 'false', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'false', modified = NOW(), modified_by = @modified_by;

-- 8. Channel Settings
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'send_email', 'false', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'false', modified = NOW(), modified_by = @modified_by;

INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'send_system_notification', 'true', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'true', modified = NOW(), modified_by = @modified_by;

-- ============================================================================
-- VERIFIKASI KONFIGURASI
-- ============================================================================

SELECT 
    id,
    company_id,
    `key`,
    value,
    creation,
    modified
FROM companysettings
WHERE company_id = @company_id
AND `key` LIKE 'clock_%'
ORDER BY `key`;

-- ============================================================================
-- CONTOH KONFIGURASI LAINNYA
-- ============================================================================

-- Contoh 1: Disable semua clock reminders untuk company tertentu
/*
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'clock_reminder_enabled', 'false', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'false', modified = NOW(), modified_by = @modified_by;
*/

-- Contoh 2: Enable overtime warning untuk company tertentu
/*
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'overtime_warning_enabled', 'true', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'true', modified = NOW(), modified_by = @modified_by;
*/

-- Contoh 3: Enable email notification juga
/*
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'send_email', 'true', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = 'true', modified = NOW(), modified_by = @modified_by;
*/

-- Contoh 4: Include Saturday (6) dalam reminder days
/*
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (@company_id, 'clock_in_reminder_days', '[1,2,3,4,5,6]', NOW(), NOW(), @modified_by, @owner)
ON DUPLICATE KEY UPDATE value = '[1,2,3,4,5,6]', modified = NOW(), modified_by = @modified_by;
*/

-- ============================================================================
-- DEFAULT VALUES (JIKA TIDAK ADA KONFIGURASI)
-- ============================================================================
--
-- clock_reminder_enabled: true
-- clock_in_reminder_enabled: true
-- clock_in_reminder_minutes_before: 30
-- clock_in_reminder_days: [1,2,3,4,5] (Weekdays only)
-- clock_out_reminder_enabled: true
-- clock_out_reminder_minutes_before: 30
-- clock_out_reminder_days: [1,2,3,4,5]
-- missed_clock_in_enabled: true
-- missed_clock_in_minutes_after: 60
-- late_arrival_enabled: true
-- late_arrival_threshold_minutes: 15
-- overtime_warning_enabled: false
-- overtime_warning_hours_before: 2
-- notify_employee: true
-- notify_supervisor: true
-- notify_hr: false
-- send_email: false
-- send_system_notification: true
--
-- ============================================================================