-- 门店正常打卡：围栏 + Wi-Fi 成对配置（SSID+BSSID）
-- 可重复执行。会删除已废弃列 F_AttendanceWifiSsids、F_AttendanceWifiBssids（数据请先迁移到 F_AttendanceWifiPairs）
-- 执行前：USE 你的库名;

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_patch_lq_mdxx_attendance_wifi`$$

CREATE PROCEDURE `sp_patch_lq_mdxx_attendance_wifi`()
BEGIN
  DECLARE dbname VARCHAR(64);
  SET dbname = DATABASE();

  -- 1) 基础列：围栏开关、Wi-Fi 开关
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceCheckFence'
  ) THEN
    ALTER TABLE `lq_mdxx`
      ADD COLUMN `F_AttendanceCheckFence` int NULL DEFAULT NULL
      COMMENT '正常打卡是否启用围栏校验：1是 0否；NULL 表示沿用旧逻辑(有围栏则校验)'
      AFTER `fence_polygons`;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceCheckWifi'
  ) THEN
    ALTER TABLE `lq_mdxx`
      ADD COLUMN `F_AttendanceCheckWifi` int NULL DEFAULT NULL
      COMMENT '正常打卡是否启用Wi-Fi校验：1是 0否；NULL 视为0'
      AFTER `F_AttendanceCheckFence`;
  END IF;

  -- 2) 删除废弃列（独立 SSID / BSSID JSON 列，已由 F_AttendanceWifiPairs 替代）
  IF EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceWifiBssids'
  ) THEN
    ALTER TABLE `lq_mdxx` DROP COLUMN `F_AttendanceWifiBssids`;
  END IF;

  IF EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceWifiSsids'
  ) THEN
    ALTER TABLE `lq_mdxx` DROP COLUMN `F_AttendanceWifiSsids`;
  END IF;

  -- 3) 成对配置列（加在 Wi-Fi 开关后；此时已不存在旧列）
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceWifiPairs'
  ) THEN
    ALTER TABLE `lq_mdxx`
      ADD COLUMN `F_AttendanceWifiPairs` varchar(4000) NULL DEFAULT NULL
      COMMENT 'Wi-Fi成对配置 JSON [{"ssid":"","bssid":""}]'
      AFTER `F_AttendanceCheckWifi`;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceWifiVerifyPair'
  ) THEN
    ALTER TABLE `lq_mdxx`
      ADD COLUMN `F_AttendanceWifiVerifyPair` int NULL DEFAULT 0
      COMMENT '1=校验SSID与BSSID为同一AP；0=SSID或BSSID命中任一行即可'
      AFTER `F_AttendanceWifiPairs`;
  END IF;
END$$

DELIMITER ;

CALL `sp_patch_lq_mdxx_attendance_wifi`();

DROP PROCEDURE IF EXISTS `sp_patch_lq_mdxx_attendance_wifi`;