-- 考勤打卡记录表新增外勤/定位/照片/补卡字段
-- 执行库：lqerp_dev / lqerp
-- 说明：用于真实打卡、外勤打卡、地图查看和后台补卡

SET @db_name = DATABASE();

-- 1. 上下班打卡类型
SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchInType') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchInType` int DEFAULT NULL COMMENT '上班打卡类型（1正常上班，2外勤）' AFTER `F_PunchOutTime`;",
  "SELECT 'lq_attendance_record.F_PunchInType 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchOutType') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchOutType` int DEFAULT NULL COMMENT '下班打卡类型（1正常上班，2外勤）' AFTER `F_PunchInType`;",
  "SELECT 'lq_attendance_record.F_PunchOutType 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2. 上班定位、地址、照片
SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchInLongitude') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchInLongitude` decimal(18,6) DEFAULT NULL COMMENT '上班打卡经度' AFTER `F_PunchOutType`;",
  "SELECT 'lq_attendance_record.F_PunchInLongitude 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchInLatitude') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchInLatitude` decimal(18,6) DEFAULT NULL COMMENT '上班打卡纬度' AFTER `F_PunchInLongitude`;",
  "SELECT 'lq_attendance_record.F_PunchInLatitude 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchInAddress') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchInAddress` varchar(500) DEFAULT NULL COMMENT '上班打卡地址' AFTER `F_PunchInLatitude`;",
  "SELECT 'lq_attendance_record.F_PunchInAddress 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchInPhotoUrl') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchInPhotoUrl` varchar(500) DEFAULT NULL COMMENT '上班打卡照片地址' AFTER `F_PunchInAddress`;",
  "SELECT 'lq_attendance_record.F_PunchInPhotoUrl 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3. 下班定位、地址、照片
SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchOutLongitude') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchOutLongitude` decimal(18,6) DEFAULT NULL COMMENT '下班打卡经度' AFTER `F_PunchInPhotoUrl`;",
  "SELECT 'lq_attendance_record.F_PunchOutLongitude 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchOutLatitude') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchOutLatitude` decimal(18,6) DEFAULT NULL COMMENT '下班打卡纬度' AFTER `F_PunchOutLongitude`;",
  "SELECT 'lq_attendance_record.F_PunchOutLatitude 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchOutAddress') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchOutAddress` varchar(500) DEFAULT NULL COMMENT '下班打卡地址' AFTER `F_PunchOutLatitude`;",
  "SELECT 'lq_attendance_record.F_PunchOutAddress 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchOutPhotoUrl') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchOutPhotoUrl` varchar(500) DEFAULT NULL COMMENT '下班打卡照片地址' AFTER `F_PunchOutAddress`;",
  "SELECT 'lq_attendance_record.F_PunchOutPhotoUrl 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4. 围栏校验结果
SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_IsPunchInFenceValid') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_IsPunchInFenceValid` int DEFAULT NULL COMMENT '上班打卡是否通过围栏校验（1通过0未通过）' AFTER `F_PunchOutPhotoUrl`;",
  "SELECT 'lq_attendance_record.F_IsPunchInFenceValid 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_IsPunchOutFenceValid') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_IsPunchOutFenceValid` int DEFAULT NULL COMMENT '下班打卡是否通过围栏校验（1通过0未通过）' AFTER `F_IsPunchInFenceValid`;",
  "SELECT 'lq_attendance_record.F_IsPunchOutFenceValid 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5. 早退分钟
SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_EarlyLeaveMinutes') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_EarlyLeaveMinutes` int NOT NULL DEFAULT 0 COMMENT '早退分钟数' AFTER `F_LateMinutes`;",
  "SELECT 'lq_attendance_record.F_EarlyLeaveMinutes 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 6. 补卡信息
SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_SupplementRemark') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_SupplementRemark` varchar(500) DEFAULT NULL COMMENT '补卡备注' AFTER `F_Remark`;",
  "SELECT 'lq_attendance_record.F_SupplementRemark 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_SupplementOperatorId') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_SupplementOperatorId` varchar(50) DEFAULT NULL COMMENT '补卡操作人ID' AFTER `F_SupplementRemark`;",
  "SELECT 'lq_attendance_record.F_SupplementOperatorId 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_SupplementOperatorName') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_SupplementOperatorName` varchar(100) DEFAULT NULL COMMENT '补卡操作人姓名' AFTER `F_SupplementOperatorId`;",
  "SELECT 'lq_attendance_record.F_SupplementOperatorName 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_SupplementTime') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_SupplementTime` datetime DEFAULT NULL COMMENT '补卡操作时间' AFTER `F_SupplementOperatorName`;",
  "SELECT 'lq_attendance_record.F_SupplementTime 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_SupplementWorkflowId') = 0,
  "ALTER TABLE `lq_attendance_record` ADD COLUMN `F_SupplementWorkflowId` varchar(50) DEFAULT NULL COMMENT '关联补卡流程ID' AFTER `F_SupplementTime`;",
  "SELECT 'lq_attendance_record.F_SupplementWorkflowId 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;