-- 签到记录表
CREATE TABLE IF NOT EXISTS `t_checkin_record` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `checkin_type` VARCHAR(50) NOT NULL COMMENT '签到类型：inventory_tally-理货盘点签到，delivery_replenish-补货确认签到',
    `user_id` BIGINT NOT NULL COMMENT '签到用户ID',
    `user_name` VARCHAR(100) DEFAULT NULL COMMENT '签到用户名',
    `user_no` VARCHAR(50) DEFAULT NULL COMMENT '签到用户工号',
    `shop_id` BIGINT DEFAULT NULL COMMENT '关联门店ID',
    `shop_name` VARCHAR(200) DEFAULT NULL COMMENT '关联门店名称',
    `device_id` VARCHAR(50) DEFAULT NULL COMMENT '关联设备ID',
    `device_name` VARCHAR(200) DEFAULT NULL COMMENT '关联设备名称',
    `latitude` DECIMAL(10, 7) DEFAULT NULL COMMENT '签到纬度',
    `longitude` DECIMAL(10, 7) DEFAULT NULL COMMENT '签到经度',
    `address` VARCHAR(500) DEFAULT NULL COMMENT '签到地址',
    `accuracy` INT DEFAULT NULL COMMENT '定位精度（米）',
    `photos` TEXT DEFAULT NULL COMMENT '签到照片链接（JSON数组，存储OSS链接）',
    `remark` VARCHAR(500) DEFAULT NULL COMMENT '备注信息',
    `status` VARCHAR(20) DEFAULT 'synced' COMMENT '签到状态：pending-待同步，synced-已同步，failed-同步失败',
    `offline_id` VARCHAR(100) DEFAULT NULL COMMENT '离线ID（离线签到时生成）',
    `synced_at` DATETIME DEFAULT NULL COMMENT '同步时间',
    `checkin_time` DATETIME NOT NULL COMMENT '签到时间',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_checkin_type` (`checkin_type`),
    INDEX `idx_shop_id` (`shop_id`),
    INDEX `idx_device_id` (`device_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_checkin_time` (`checkin_time`),
    INDEX `idx_offline_id` (`offline_id`),
    INDEX `idx_user_type_time` (`user_id`, `checkin_type`, `checkin_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到记录表';

-- 签到照片表（可选，用于更细粒度的照片管理）
CREATE TABLE IF NOT EXISTS `t_checkin_photo` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `checkin_id` BIGINT NOT NULL COMMENT '签到记录ID',
    `photo_url` VARCHAR(500) NOT NULL COMMENT '照片OSS链接',
    `watermark_url` VARCHAR(500) DEFAULT NULL COMMENT '水印照片OSS链接',
    `timestamp` BIGINT DEFAULT NULL COMMENT '拍摄时间戳',
    `latitude` DECIMAL(10, 7) DEFAULT NULL COMMENT '拍摄纬度',
    `longitude` DECIMAL(10, 7) DEFAULT NULL COMMENT '拍摄经度',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    INDEX `idx_checkin_id` (`checkin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到照片表';

-- 插入测试数据
INSERT INTO `t_checkin_record` (`checkin_type`, `user_id`, `user_name`, `user_no`, `shop_id`, `shop_name`, `device_id`, `device_name`, `latitude`, `longitude`, `address`, `accuracy`, `photos`, `remark`, `status`, `checkin_time`, `create_time`) VALUES
('inventory_tally', 1, '张三', 'EMP001', 1, '张江科技园店', 'DEVICE001', '1号充电桩', 31.2304, 121.4737, '上海市浦东新区张江高科技园区', 65, '[{"id":"photo_001","path":"https://oss.example.com/photos/001.jpg","watermarkPath":"https://oss.example.com/photos/001_wm.jpg","timestamp":1709088000}]', '库存盘点正常，无异常', 'synced', NOW() - INTERVAL 2 HOUR, NOW() - INTERVAL 2 HOUR),
('delivery_replenish', 2, '李四', 'EMP002', 2, '陆家嘴金融城店', 'DEVICE002', '2号充电桩', 31.2356, 121.4998, '上海市浦东新区陆家嘴金融城', 50, '[{"id":"photo_002","path":"https://oss.example.com/photos/002.jpg","watermarkPath":"https://oss.example.com/photos/002_wm.jpg","timestamp":1709091000}]', '补货完成，已补充矿泉水10瓶', 'synced', NOW() - INTERVAL 1 HOUR, NOW() - INTERVAL 1 HOUR),
('inventory_tally', 1, '张三', 'EMP001', 3, '金桥开发区店', NULL, NULL, 31.2456, 121.5234, '上海市浦东新区金桥开发区', 70, '[{"id":"photo_003","path":"https://oss.example.com/photos/003.jpg","watermarkPath":"https://oss.example.com/photos/003_wm.jpg","timestamp":1709073600}]', '发现部分商品临期，需要处理', 'synced', NOW() - INTERVAL 1 DAY, NOW() - INTERVAL 1 DAY),
('delivery_replenish', 3, '王五', 'EMP003', 1, '张江科技园店', 'DEVICE003', '3号充电桩', 31.2304, 121.4737, '上海市浦东新区张江高科技园区', 55, '[{"id":"photo_004","path":"https://oss.example.com/photos/004.jpg","watermarkPath":"https://oss.example.com/photos/004_wm.jpg","timestamp":1708987200}]', '', 'synced', NOW() - INTERVAL 2 DAY, NOW() - INTERVAL 2 DAY),
('inventory_tally', 2, '李四', 'EMP002', 2, '陆家嘴金融城店', NULL, NULL, 31.2356, 121.4998, '上海市浦东新区陆家嘴金融城', 60, '[{"id":"photo_005","path":"https://oss.example.com/photos/005.jpg","watermarkPath":"https://oss.example.com/photos/005_wm.jpg","timestamp":1708900800}]', '盘点完毕，库存充足', 'pending', NOW() - INTERVAL 3 DAY, NOW() - INTERVAL 3 DAY);

-- 添加签到相关权限
INSERT INTO `t_permission` (`name`, `code`, `type`, `parent_id`, `sort`, `status`, `create_time`) VALUES
('签到管理', 'checkin', 'menu', 0, 6, 1, NOW()),
('签到查看', 'checkin:read', 'button', (SELECT id FROM (SELECT id FROM `t_permission` WHERE `code` = 'checkin') t), 1, 1, NOW()),
('签到提交', 'checkin:create', 'button', (SELECT id FROM (SELECT id FROM `t_permission` WHERE `code` = 'checkin') t), 2, 1, NOW());
