update_sick_days.sql

-- Migration to add sick days and accrual tracking
-- For Food Service Vendor Payroll System

-- Add sick days tracking columns to employee table
ALTER TABLE `employee`
ADD COLUMN `sick_days_balance` DECIMAL(5,2) DEFAULT 0.00 AFTER `employee_no`,
ADD COLUMN `sick_days_accrual_rate` DECIMAL(4,2) DEFAULT 0.83 COMMENT 'Days per month (10 days/year = 0.83/month)' AFTER `sick_days_balance`,
ADD COLUMN `last_accrual_date` DATE DEFAULT NULL AFTER `sick_days_accrual_rate`;

-- Add sick days used to history table
ALTER TABLE `history`
ADD COLUMN `sick_days_used` DECIMAL(4,2) DEFAULT 0.00 AFTER `exception`;

-- Update existing employees with initial sick days balance
UPDATE `employee` SET
  `sick_days_balance` = 10.00,
  `sick_days_accrual_rate` = 0.83,
  `last_accrual_date` = CURDATE();

-- Update history records to have 0 sick days used
UPDATE `history` SET `sick_days_used` = 0.00;