-- Add remunerations column to staff_salaries table
-- This migration adds support for storing individual remuneration items (JSON format)
-- instead of just basic_salary and allowances

ALTER TABLE `staff_salaries` 
ADD COLUMN `remunerations` JSON COMMENT 'Store remuneration items as JSON array: [{"item": "...", "amount": ...}, ...]' AFTER `salary_month`;

-- Update existing records to populate remunerations from basic_salary and allowances
-- This creates a backwards-compatible JSON structure
UPDATE `staff_salaries` 
SET `remunerations` = JSON_ARRAY(
    JSON_OBJECT('item', 'Basic Salary', 'amount', COALESCE(`basic_salary`, 0)),
    JSON_OBJECT('item', 'Allowances', 'amount', COALESCE(`allowances`, 0))
)
WHERE `remunerations` IS NULL;
