-- Migration: Configure PKWT Notification Additional Recipients via CompanySetting
-- Description: Script untuk mengkonfigurasi email recipients tambahan untuk PKWT notifications per company
-- Date: 2026-02-06
-- Usage: Update COMPANY_ID dan email addresses sesuai kebutuhan

-- ============================================
-- CARA PENGGUNAAN:
-- ============================================
-- 1. Ganti COMPANY_ID dengan ID company yang ingin dikonfigurasi
-- 2. Ganti email addresses di JSON array sesuai kebutuhan
-- 3. Jalankan script ini untuk setiap company yang ingin dikonfigurasi
-- 4. Untuk update, script akan otomatis update jika setting sudah ada
-- ============================================

-- Contoh: Konfigurasi untuk Company ID 1
-- Ganti COMPANY_ID dan email addresses sesuai kebutuhan Anda

SET @company_id = 8; -- GANTI DENGAN COMPANY ID YANG INGIN DIKONFIGURASI
SET @additional_recipients = '["hr@sapharma.co.id", "training.development@sapharma.co.id", "it@sapharma.co.id", "recruitment2@sapharma.co.id"]'; -- GANTI DENGAN EMAIL ADDRESSES YANG DIINGINKAN
SET @modified_by = 1; -- User ID yang melakukan konfigurasi
SET @owner = 1; -- Owner ID
SET @setting_key = 'pkwt_notification_additional_recipients';

-- Insert or Update CompanySetting using ON DUPLICATE KEY UPDATE
-- Note: Requires unique constraint on (company_id, key) or primary key
-- If unique constraint doesn't exist, this will insert new record each time
-- Alternative: Use REPLACE INTO or manual UPDATE/INSERT with WHERE clause

-- Method 1: Using INSERT ... ON DUPLICATE KEY UPDATE (Recommended if unique constraint exists)
INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
VALUES (
    @company_id,
    @setting_key,
    @additional_recipients,
    NOW(),
    NOW(),
    @modified_by,
    @owner
)
ON DUPLICATE KEY UPDATE
    value = @additional_recipients,
    modified = NOW(),
    modified_by = @modified_by;

-- Method 2: Using REPLACE INTO (Alternative - will delete and re-insert if exists)
-- REPLACE INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
-- VALUES (
--     @company_id,
--     @setting_key,
--     @additional_recipients,
--     NOW(),
--     NOW(),
--     @modified_by,
--     @owner
-- );

-- Method 3: Manual UPDATE then INSERT (Alternative - works without unique constraint)
-- UPDATE companysettings 
-- SET 
--     value = @additional_recipients,
--     modified = NOW(),
--     modified_by = @modified_by
-- WHERE company_id = @company_id 
-- AND BINARY `key` = BINARY @setting_key;
-- 
-- -- If no rows were updated, insert new record
-- INSERT INTO companysettings (company_id, `key`, value, creation, modified, modified_by, owner)
-- SELECT @company_id, @setting_key, @additional_recipients, NOW(), NOW(), @modified_by, @owner
-- WHERE NOT EXISTS (
--     SELECT 1 FROM companysettings 
--     WHERE company_id = @company_id 
--     AND BINARY `key` = BINARY @setting_key
-- );

-- Verify the setting was created/updated
-- Using BINARY to avoid collation mismatch issues (utf8mb4_general_ci vs utf8mb4_unicode_ci)
-- BINARY ensures exact match without collation comparison, which is safe for key lookups
SELECT 
    id,
    company_id,
    `key`,
    value,
    creation,
    modified,
    modified_by,
    owner
FROM companysettings
WHERE company_id = @company_id
AND BINARY `key` = BINARY @setting_key;

-- ============================================
-- CATATAN PENTING:
-- ============================================
-- 1. Format value harus JSON array yang valid, contoh: ["email1@company.com", "email2@company.com"]
-- 2. Email addresses akan digunakan sebagai additional recipients untuk semua PKWT notifications di company tersebut
-- 3. Email employee tetap akan dikirim sebagai primary recipient
-- 4. Additional recipients akan ditambahkan ke semua PKWT notifications untuk company tersebut
-- 5. Untuk menghapus additional recipients, set value menjadi "[]" (empty array)
-- 6. Jika Method 1 gagal karena tidak ada unique constraint, gunakan Method 3 (Manual UPDATE then INSERT)
-- ============================================

-- Contoh untuk multiple companies (uncomment dan sesuaikan jika perlu):
/*
-- Company ID 1
SET @company_id = 1;
SET @additional_recipients = '["hr1@company.com", "admin1@company.com"]';
-- ... (jalankan INSERT/UPDATE logic di atas)

-- Company ID 2
SET @company_id = 2;
SET @additional_recipients = '["hr2@company.com", "admin2@company.com"]';
-- ... (jalankan INSERT/UPDATE logic di atas)
*/