update_alaska_csv_fields.sql

-- Alaska Quarterly CSV Export Fields
-- Adds required fields for generating Alaska quarterly contribution report CSV

-- 1. Add Alaska Employer Account Number to company_settings
ALTER TABLE `company_settings`
  ADD COLUMN `alaska_employer_account` VARCHAR(8) DEFAULT NULL COMMENT 'Alaska Employer Account Number (max 8 chars)';

-- 2. Add Alaska-specific fields to employee table
ALTER TABLE `employee`
  ADD COLUMN `middle_initial` VARCHAR(1) DEFAULT NULL COMMENT 'Employee middle initial (optional)',
  ADD COLUMN `occupation_code` VARCHAR(10) DEFAULT NULL COMMENT 'SOC occupation code (e.g., 35-3021 for food preparation workers)',
  ADD COLUMN `geographic_code` VARCHAR(2) DEFAULT NULL COMMENT 'Alaska geographic area code (2 digits)';

-- 3. Add reportable wages tracking to history table (if not already present)
-- This helps track quarterly wages for Alaska reporting
ALTER TABLE `history`
  ADD COLUMN `reportable_wages` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Total reportable wages for this pay period',
  ADD COLUMN `pay_period_start` DATE NULL COMMENT 'Start date of pay period',
  ADD COLUMN `pay_period_end` DATE NULL COMMENT 'End date of pay period';

-- 4. Initialize default values
-- Set default occupation code for existing employees (can be updated later)
-- Common SOC codes for food service:
-- 35-1011 = Chefs and Head Cooks
-- 35-2014 = Cooks, Restaurant
-- 35-3021 = Combined Food Preparation and Serving Workers
-- 35-3031 = Waiters and Waitresses
-- 35-3011 = Bartenders
-- 53-3033 = Light Truck or Delivery Services Drivers
-- 11-9051 = Food Service Managers

UPDATE `employee` SET `occupation_code` = '35-1011' WHERE `position` = 'Chef' AND `occupation_code` IS NULL;
UPDATE `employee` SET `occupation_code` = '35-1011' WHERE `position` = 'Sous Chef' AND `occupation_code` IS NULL;
UPDATE `employee` SET `occupation_code` = '35-2014' WHERE `position` = 'Line Cook' AND `occupation_code` IS NULL;
UPDATE `employee` SET `occupation_code` = '35-3031' WHERE `position` = 'Server' AND `occupation_code` IS NULL;
UPDATE `employee` SET `occupation_code` = '35-3011' WHERE `position` = 'Bartender' AND `occupation_code` IS NULL;
UPDATE `employee` SET `occupation_code` = '53-3033' WHERE `position` = 'Delivery Driver' AND `occupation_code` IS NULL;
UPDATE `employee` SET `occupation_code` = '11-9051' WHERE `position` = 'Catering Manager' AND `occupation_code` IS NULL;
UPDATE `employee` SET `occupation_code` = '11-9051' WHERE `position` = 'Kitchen Manager' AND `occupation_code` IS NULL;
UPDATE `employee` SET `occupation_code` = '11-1021' WHERE `position` = 'General Manager' AND `occupation_code` IS NULL;

-- Set default geographic code (44 = Anchorage, you can update based on actual location)
UPDATE `employee` SET `geographic_code` = '44' WHERE `geographic_code` IS NULL;

-- Insert or update company settings with placeholder Alaska employer account number
INSERT INTO `company_settings` (`id`, `alaska_employer_account`)
VALUES (1, NULL)
ON DUPLICATE KEY UPDATE
  `alaska_employer_account` = COALESCE(`alaska_employer_account`, NULL);