-- =============================================================================
-- Migration: Fix workactivities and workactivityplans fields
-- =============================================================================

-- 1. Remove activity_date and work_type from workactivities
-- Note: 'activity_date' was already migrated to 'workactivityitems' in a previous migration.
-- We use a safe check and drop.
SET @dbname = DATABASE();

SET @sqlstmt = (SELECT IF(
    (SELECT COUNT(*) 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE table_name = 'workactivities' 
     AND table_schema = @dbname 
     AND column_name = 'activity_date') > 0,
    'ALTER TABLE `workactivities` DROP COLUMN `activity_date`',
    'SELECT 1'
));
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sqlstmt = (SELECT IF(
    (SELECT COUNT(*) 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE table_name = 'workactivities' 
     AND table_schema = @dbname 
     AND column_name = 'work_type') > 0,
    'ALTER TABLE `workactivities` DROP COLUMN `work_type`',
    'SELECT 1'
));
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Assuming employee_id and company_id might be missing or already exist, we use IF NOT EXISTS if supported, 
-- but in strict MySQL 5.7+ we just run ALTER TABLE.

-- Note: employee_id and company_id were actually present in a previous plan migration, but we ensure they exist.
-- To prevent error if they already exist, we do it safely:
SET @dbname = DATABASE();

SET @sqlstmt = (SELECT IF(
    (SELECT COUNT(*) 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE table_name = 'workactivityplans' 
     AND table_schema = @dbname 
     AND column_name = 'work_type') > 0,
    'SELECT 1',
    'ALTER TABLE `workactivityplans` ADD `work_type` VARCHAR(255) NULL AFTER `period_year`'
));
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sqlstmt = (SELECT IF(
    (SELECT COUNT(*) 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE table_name = 'workactivityplans' 
     AND table_schema = @dbname 
     AND column_name = 'employee_id') > 0,
    'SELECT 1',
    'ALTER TABLE `workactivityplans` ADD `employee_id` INT(11) NULL'
));
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sqlstmt = (SELECT IF(
    (SELECT COUNT(*) 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE table_name = 'workactivityplans' 
     AND table_schema = @dbname 
     AND column_name = 'company_id') > 0,
    'SELECT 1',
    'ALTER TABLE `workactivityplans` ADD `company_id` INT(11) NULL'
));
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. Add work_type to workactivityitems
SET @sqlstmt = (SELECT IF(
    (SELECT COUNT(*) 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE table_name = 'workactivityitems' 
     AND table_schema = @dbname 
     AND column_name = 'work_type') > 0,
    'SELECT 1',
    'ALTER TABLE `workactivityitems` ADD `work_type` VARCHAR(255) NULL AFTER `activity_date`'
));
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;