update_additional_fields.sql

-- Additional Fields Update for Payroll System
-- Adds: Alaska unemployment rate, SSN, W-4 allowances, and color theme

-- Add Alaska Unemployment Rate to company_settings
ALTER TABLE company_settings
ADD COLUMN alaska_unemployment_rate DECIMAL(5,4) DEFAULT 0.0270 COMMENT 'Alaska SUTA rate (default 2.7%)';

-- Add SSN to employee table
ALTER TABLE employee
ADD COLUMN ssn VARCHAR(11) DEFAULT NULL COMMENT 'Social Security Number (XXX-XX-XXXX format)',
ADD COLUMN withholding_allowances INT DEFAULT 0 COMMENT 'W-4 withholding allowances (dependents)';

-- Add color theme to company_settings
ALTER TABLE company_settings
ADD COLUMN color_theme VARCHAR(50) DEFAULT 'dark_red' COMMENT 'Website color theme';

-- Update existing company_settings record with default unemployment rate if exists
UPDATE company_settings
SET alaska_unemployment_rate = 0.0270
WHERE alaska_unemployment_rate IS NULL OR alaska_unemployment_rate = 0;

-- Update existing employees with default allowances if null
UPDATE employee
SET withholding_allowances = 0
WHERE withholding_allowances IS NULL;