-- 九泓化工工资表结构扩展
-- 1. 员工档案补充「标准项」：标准工资、岗位工资、加班工资标准（元/小时）、安全环保奖标准、绩效标准
-- 2. 薪酬核算（工资表）补充图片要求的全部列
-- 说明：MySQL 8.0 不支持 ADD COLUMN IF NOT EXISTS，这里用存储过程逐列判断后执行

DROP PROCEDURE IF EXISTS add_column_if_missing;
DELIMITER $$
CREATE PROCEDURE add_column_if_missing(IN tbl VARCHAR(64), IN col VARCHAR(64), IN ddl TEXT)
BEGIN
    IF (SELECT COUNT(*) FROM information_schema.columns
        WHERE table_schema = DATABASE() AND table_name = tbl AND column_name = col) = 0 THEN
        SET @sql = ddl;
        PREPARE st FROM @sql;
        EXECUTE st;
        DEALLOCATE PREPARE st;
    END IF;
END$$
DELIMITER ;

-- ---------- 员工档案：标准项（标准值由员工档案维护） ----------
CALL add_column_if_missing('hrm_employee', 'standard_salary',
    'ALTER TABLE hrm_employee ADD COLUMN standard_salary DECIMAL(12,2) NULL COMMENT ''标准工资'' AFTER base_salary');
CALL add_column_if_missing('hrm_employee', 'post_salary',
    'ALTER TABLE hrm_employee ADD COLUMN post_salary DECIMAL(12,2) NULL COMMENT ''岗位工资'' AFTER standard_salary');
CALL add_column_if_missing('hrm_employee', 'overtime_hourly_rate',
    'ALTER TABLE hrm_employee ADD COLUMN overtime_hourly_rate DECIMAL(12,2) NULL COMMENT ''加班工资标准（元/小时）'' AFTER post_salary');
CALL add_column_if_missing('hrm_employee', 'safety_award_standard',
    'ALTER TABLE hrm_employee ADD COLUMN safety_award_standard DECIMAL(12,2) NULL COMMENT ''安全环保奖标准'' AFTER overtime_hourly_rate');
CALL add_column_if_missing('hrm_employee', 'performance_standard',
    'ALTER TABLE hrm_employee ADD COLUMN performance_standard DECIMAL(12,2) NULL COMMENT ''绩效标准'' AFTER safety_award_standard');

-- ---------- 薪酬核算（工资表） ----------
CALL add_column_if_missing('hrm_salary_calculation', 'standard_salary',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN standard_salary DECIMAL(12,2) NULL COMMENT ''标准工资'' AFTER dept_id');
CALL add_column_if_missing('hrm_salary_calculation', 'post_salary',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN post_salary DECIMAL(12,2) NULL COMMENT ''岗位工资'' AFTER standard_salary');
CALL add_column_if_missing('hrm_salary_calculation', 'overtime_hourly_rate',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN overtime_hourly_rate DECIMAL(12,2) NULL COMMENT ''加班工资标准（元/小时）'' AFTER post_salary');
CALL add_column_if_missing('hrm_salary_calculation', 'safety_award_standard',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN safety_award_standard DECIMAL(12,2) NULL COMMENT ''安全环保奖标准'' AFTER overtime_hourly_rate');
CALL add_column_if_missing('hrm_salary_calculation', 'performance_standard',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN performance_standard DECIMAL(12,2) NULL COMMENT ''绩效标准'' AFTER safety_award_standard');
CALL add_column_if_missing('hrm_salary_calculation', 'paid_attendance_days',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN paid_attendance_days DECIMAL(6,2) NULL COMMENT ''计薪出勤（天）'' AFTER work_days');
CALL add_column_if_missing('hrm_salary_calculation', 'standard_work_hours',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN standard_work_hours DECIMAL(8,2) NULL COMMENT ''标准出勤时长（小时）'' AFTER paid_attendance_days');
CALL add_column_if_missing('hrm_salary_calculation', 'legal_holiday_hours',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN legal_holiday_hours DECIMAL(8,2) NULL COMMENT ''法定假日时长（小时）'' AFTER overtime_hours');
CALL add_column_if_missing('hrm_salary_calculation', 'standard_attendance_pay',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN standard_attendance_pay DECIMAL(12,2) NULL COMMENT ''标准出勤工资'' AFTER legal_holiday_hours');
CALL add_column_if_missing('hrm_salary_calculation', 'legal_holiday_pay',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN legal_holiday_pay DECIMAL(12,2) NULL COMMENT ''法定假工资'' AFTER overtime_pay');
CALL add_column_if_missing('hrm_salary_calculation', 'safety_award',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN safety_award DECIMAL(12,2) NULL COMMENT ''安全环保奖'' AFTER legal_holiday_pay');
CALL add_column_if_missing('hrm_salary_calculation', 'floating_performance_pay',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN floating_performance_pay DECIMAL(12,2) NULL COMMENT ''浮动绩效工资'' AFTER safety_award');
CALL add_column_if_missing('hrm_salary_calculation', 'payable_salary',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN payable_salary DECIMAL(12,2) NULL COMMENT ''应付工资'' AFTER floating_performance_pay');
CALL add_column_if_missing('hrm_salary_calculation', 'seniority_pay',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN seniority_pay DECIMAL(12,2) NULL COMMENT ''工龄工资'' AFTER payable_salary');
CALL add_column_if_missing('hrm_salary_calculation', 'night_shift_allowance',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN night_shift_allowance DECIMAL(12,2) NULL COMMENT ''夜班补助'' AFTER seniority_pay');
CALL add_column_if_missing('hrm_salary_calculation', 'education_allowance',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN education_allowance DECIMAL(12,2) NULL COMMENT ''学历补贴'' AFTER night_shift_allowance');
CALL add_column_if_missing('hrm_salary_calculation', 'health_check_allowance',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN health_check_allowance DECIMAL(12,2) NULL COMMENT ''体检'' AFTER education_allowance');
CALL add_column_if_missing('hrm_salary_calculation', 'excellent_employee_award',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN excellent_employee_award DECIMAL(12,2) NULL COMMENT ''优秀员工奖'' AFTER health_check_allowance');
CALL add_column_if_missing('hrm_salary_calculation', 'other_subsidy',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN other_subsidy DECIMAL(12,2) NULL COMMENT ''其他补助'' AFTER excellent_employee_award');
CALL add_column_if_missing('hrm_salary_calculation', 'dept_performance',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN dept_performance DECIMAL(12,2) NULL COMMENT ''部门绩效'' AFTER other_subsidy');
CALL add_column_if_missing('hrm_salary_calculation', 'addition_total',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN addition_total DECIMAL(12,2) NULL COMMENT ''加项合计'' AFTER dept_performance');
CALL add_column_if_missing('hrm_salary_calculation', 'missed_punch_deduction',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN missed_punch_deduction DECIMAL(12,2) NULL COMMENT ''漏打卡'' AFTER addition_total');
CALL add_column_if_missing('hrm_salary_calculation', 'late_early_deduction',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN late_early_deduction DECIMAL(12,2) NULL COMMENT ''迟到早退'' AFTER missed_punch_deduction');
CALL add_column_if_missing('hrm_salary_calculation', 'deduction_total',
    'ALTER TABLE hrm_salary_calculation ADD COLUMN deduction_total DECIMAL(12,2) NULL COMMENT ''扣款合计'' AFTER other_deduction');

DROP PROCEDURE IF EXISTS add_column_if_missing;