-- 考勤定位字段迁移。旧规则保持 location_required=0，先配置地点再启用范围限制。
DELIMITER $$
DROP PROCEDURE IF EXISTS xgdl_hrm_attendance_add_column_20261024$$
CREATE PROCEDURE xgdl_hrm_attendance_add_column_20261024(
    IN p_table_name VARCHAR(64), IN p_column_name VARCHAR(64), IN p_ddl_sql VARCHAR(1000))
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
                   WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table_name AND COLUMN_NAME = p_column_name) THEN
        SET @hrm_location_ddl = p_ddl_sql;
        PREPARE hrm_location_stmt FROM @hrm_location_ddl;
        EXECUTE hrm_location_stmt;
        DEALLOCATE PREPARE hrm_location_stmt;
    END IF;
END$$
DELIMITER ;

CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','location_name',
    'ALTER TABLE hrm_attendance_rule ADD COLUMN location_name varchar(100) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','location_address',
    'ALTER TABLE hrm_attendance_rule ADD COLUMN location_address varchar(255) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','longitude',
    'ALTER TABLE hrm_attendance_rule ADD COLUMN longitude decimal(10,7) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','latitude',
    'ALTER TABLE hrm_attendance_rule ADD COLUMN latitude decimal(10,7) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','radius_meters',
    'ALTER TABLE hrm_attendance_rule ADD COLUMN radius_meters int DEFAULT 10');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','location_required',
    'ALTER TABLE hrm_attendance_rule ADD COLUMN location_required bit(1) NOT NULL DEFAULT b''0''');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','coordinate_system',
    'ALTER TABLE hrm_attendance_rule ADD COLUMN coordinate_system varchar(20) DEFAULT ''WGS84''');

CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_in_longitude',
    'ALTER TABLE hrm_attendance_record ADD COLUMN clock_in_longitude decimal(10,7) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_in_latitude',
    'ALTER TABLE hrm_attendance_record ADD COLUMN clock_in_latitude decimal(10,7) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_in_accuracy',
    'ALTER TABLE hrm_attendance_record ADD COLUMN clock_in_accuracy decimal(8,2) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_in_distance',
    'ALTER TABLE hrm_attendance_record ADD COLUMN clock_in_distance decimal(10,2) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_in_address',
    'ALTER TABLE hrm_attendance_record ADD COLUMN clock_in_address varchar(255) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_out_longitude',
    'ALTER TABLE hrm_attendance_record ADD COLUMN clock_out_longitude decimal(10,7) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_out_latitude',
    'ALTER TABLE hrm_attendance_record ADD COLUMN clock_out_latitude decimal(10,7) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_out_accuracy',
    'ALTER TABLE hrm_attendance_record ADD COLUMN clock_out_accuracy decimal(8,2) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_out_distance',
    'ALTER TABLE hrm_attendance_record ADD COLUMN clock_out_distance decimal(10,2) NULL');
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_out_address',
    'ALTER TABLE hrm_attendance_record ADD COLUMN clock_out_address varchar(255) NULL');

DROP PROCEDURE xgdl_hrm_attendance_add_column_20261024;

-- 对齐考勤规则与打卡按钮权限码，确保 Web 和 APP 普通角色可以授权使用。
UPDATE `system_menu` SET `permission` = 'hrm:attendance-rule:query' WHERE `id` = 60131;
UPDATE `system_menu` SET `permission` = 'hrm:attendance-rule:create' WHERE `id` = 60132;
UPDATE `system_menu` SET `permission` = 'hrm:attendance-rule:update' WHERE `id` = 60133;
UPDATE `system_menu` SET `permission` = 'hrm:attendance-rule:delete' WHERE `id` = 60134;
UPDATE `system_menu` SET `permission` = 'hrm:attendance-rule:export' WHERE `id` = 60135;

INSERT INTO `system_menu`
(`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`,
 `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`,
 `updater`, `update_time`, `deleted`)
VALUES
(60114, '定位打卡', 'hrm:attendance-record:clock', 3, 4, 6011, '', '', '', '',
 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0')
ON DUPLICATE KEY UPDATE
`name` = VALUES(`name`), `permission` = VALUES(`permission`), `status` = 0, `deleted` = b'0';

-- 已拥有“考勤记录”菜单的角色自动获得定位打卡按钮权限。
INSERT INTO `system_role_menu`
(`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `tenant_id`)
SELECT DISTINCT parent_role.`role_id`, 60114, '1', NOW(), '1', NOW(), b'0', parent_role.`tenant_id`
FROM `system_role_menu` parent_role
WHERE parent_role.`menu_id` = 6011 AND parent_role.`deleted` = b'0'
  AND NOT EXISTS (
      SELECT 1 FROM `system_role_menu` current_grant
      WHERE current_grant.`role_id` = parent_role.`role_id`
        AND current_grant.`menu_id` = 60114
        AND current_grant.`tenant_id` = parent_role.`tenant_id`
        AND current_grant.`deleted` = b'0'
  );