-- ============================================
-- 无人机指挥中心数据库初始化脚本
-- 包含用户认证、威胁区域管理、路径规划等核心功能
-- ============================================

-- 创建数据库
CREATE DATABASE IF NOT EXISTS command_center CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE command_center;

-- 设置字符集
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;
SET character_set_connection=utf8mb4;

-- ============================================
-- 1. 用户认证系统
-- ============================================

-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin', 'operator') NOT NULL DEFAULT 'operator',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ============================================
-- 2. 威胁区域管理系统
-- ============================================

-- 删除现有威胁区表（如果存在）以确保结构正确
DROP TABLE IF EXISTS threat_zones;

-- 创建威胁区表
CREATE TABLE threat_zones (
  id INT PRIMARY KEY AUTO_INCREMENT,
  type ENUM('radar', 'missile', 'aircraft', 'ground', 'weather') NOT NULL,
  level ENUM('low', 'medium', 'high', 'critical') NOT NULL,
  description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  geometry_type ENUM('circle', 'polygon', 'rectangle') NOT NULL,
  geometry_data JSON NOT NULL COMMENT '存储几何形状数据',
  time_start TIMESTAMP NULL COMMENT '威胁区开始时间',
  time_end TIMESTAMP NULL COMMENT '威胁区结束时间',
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_type (type),
  INDEX idx_level (level),
  INDEX idx_status (status),
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ============================================
-- 3. 路径规划系统
-- ============================================

-- 删除现有路径规划表（如果存在）以确保结构正确
DROP TABLE IF EXISTS path_plans;

-- 创建路径规划表（增强版本，支持完整的路径保存和历史查看）
CREATE TABLE path_plans (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) DEFAULT NULL COMMENT '路径规划名称',
  description TEXT DEFAULT NULL COMMENT '路径描述信息',
  start_latitude DECIMAL(10, 7) NOT NULL COMMENT '起点纬度',
  start_longitude DECIMAL(10, 7) NOT NULL COMMENT '起点经度', 
  end_latitude DECIMAL(10, 7) NOT NULL COMMENT '终点纬度',
  end_longitude DECIMAL(10, 7) NOT NULL COMMENT '终点经度',
  path_points JSON NOT NULL COMMENT '路径点数据，包含lng, lat, altitude',
  smoothed_path_points JSON DEFAULT NULL COMMENT '平滑后的路径点数据',
  original_path_points JSON DEFAULT NULL COMMENT '原始A*算法路径点（未平滑）',
  algorithm VARCHAR(20) NOT NULL DEFAULT 'astar' COMMENT '使用的规划算法',
  distance DECIMAL(10, 2) DEFAULT NULL COMMENT '路径总距离（米）',
  estimated_time INT DEFAULT NULL COMMENT '预计时间（分钟）',
  flight_altitude INT DEFAULT 100 COMMENT '飞行高度（米）',
  flight_speed DECIMAL(5, 2) DEFAULT 10.00 COMMENT '飞行速度（米/秒）',
  waypoint_count INT DEFAULT 0 COMMENT '路径点数量',
  threat_zones_avoided JSON DEFAULT NULL COMMENT '避开的威胁区ID列表',
  threat_zones_passed JSON DEFAULT NULL COMMENT '穿过的威胁区ID列表',
  target_order JSON DEFAULT NULL COMMENT '目标点访问顺序',
  planning_options JSON DEFAULT NULL COMMENT '规划时的选项参数',
  execution_result JSON DEFAULT NULL COMMENT '执行结果信息',
  status ENUM('planned', 'executing', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'planned',
  visibility ENUM('public', 'private') NOT NULL DEFAULT 'public' COMMENT '路径可见性',
  color VARCHAR(7) DEFAULT '#FF5722' COMMENT '路径显示颜色',
  created_by INT DEFAULT NULL COMMENT '创建者ID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  executed_at TIMESTAMP NULL COMMENT '开始执行时间',
  completed_at TIMESTAMP NULL COMMENT '完成时间',
  INDEX idx_status (status),
  INDEX idx_algorithm (algorithm),
  INDEX idx_created_at (created_at),
  INDEX idx_created_by (created_by),
  INDEX idx_visibility (visibility),
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='路径规划表（增强版）';

-- ============================================
-- 3.1 多对多路径规划系统
-- ============================================

-- 删除现有多对多路径规划表（如果存在）以确保结构正确
DROP TABLE IF EXISTS multi_to_multi_plans;

-- 创建多对多路径规划表
CREATE TABLE multi_to_multi_plans (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) DEFAULT NULL COMMENT '多对多规划名称',
  description TEXT DEFAULT NULL COMMENT '规划描述信息',
  planning_mode ENUM('task_assignment', 'collaborative') NOT NULL DEFAULT 'task_assignment' COMMENT '规划模式：任务分配或协同规划',
  
  -- 起点和目标点信息
  start_points JSON NOT NULL COMMENT '起点数据数组，格式：[{id, lat, lng, altitude, name}]',
  target_points JSON NOT NULL COMMENT '目标点数据数组，格式：[{id, lat, lng, altitude, name, priority}]',
  start_points_count INT NOT NULL DEFAULT 0 COMMENT '起点数量',
  target_points_count INT NOT NULL DEFAULT 0 COMMENT '目标点数量',
  
  -- 分配和路径信息
  assignment_type ENUM('one_to_one', 'many_to_few', 'few_to_many') NOT NULL COMMENT '分配类型',
  assignment_strategy ENUM('greedy', 'hungarian', 'optimal') NOT NULL DEFAULT 'greedy' COMMENT '分配策略',
  assignments JSON NOT NULL COMMENT '分配结果，格式：[{startPointId, targetPointIds, paths}]',
  
  -- 算法参数
  algorithm VARCHAR(20) NOT NULL DEFAULT 'astar' COMMENT '路径规划算法',
  flight_altitude INT DEFAULT 100 COMMENT '飞行高度（米）',
  flight_speed DECIMAL(5, 2) DEFAULT 10.00 COMMENT '飞行速度（米/秒）',
  max_flight_time INT DEFAULT 30 COMMENT '最大飞行时间（分钟）',
  safe_distance DECIMAL(8, 2) DEFAULT 50.00 COMMENT '安全距离（米）',
  
  -- 统计信息
  total_distance DECIMAL(12, 2) DEFAULT NULL COMMENT '总距离（米）',
  total_time INT DEFAULT NULL COMMENT '总时间（分钟）',
  max_time INT DEFAULT NULL COMMENT '最大单机时间（分钟）',
  min_time INT DEFAULT NULL COMMENT '最小单机时间（分钟）',
  avg_time DECIMAL(8, 2) DEFAULT NULL COMMENT '平均单机时间（分钟）',
  threat_level_stats JSON DEFAULT NULL COMMENT '威胁等级统计，格式：{low: count, medium: count, high: count}',
  
  -- 威胁区域处理
  threat_zones_avoided JSON DEFAULT NULL COMMENT '避开的威胁区ID列表',
  threat_zones_passed JSON DEFAULT NULL COMMENT '穿过的威胁区ID列表',
  
  -- 规划选项和结果
  planning_options JSON DEFAULT NULL COMMENT '规划选项参数',
  execution_result JSON DEFAULT NULL COMMENT '执行结果信息',
  optimization_metrics JSON DEFAULT NULL COMMENT '优化指标结果',
  
  -- 状态管理
  status ENUM('planned', 'executing', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'planned',
  visibility ENUM('public', 'private') NOT NULL DEFAULT 'public' COMMENT '方案可见性',
  
  -- 元数据
  created_by INT DEFAULT NULL COMMENT '创建者ID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  executed_at TIMESTAMP NULL COMMENT '开始执行时间',
  completed_at TIMESTAMP NULL COMMENT '完成时间',
  
  -- 索引
  INDEX idx_planning_mode (planning_mode),
  INDEX idx_assignment_type (assignment_type),
  INDEX idx_algorithm (algorithm),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at),
  INDEX idx_created_by (created_by),
  INDEX idx_visibility (visibility),
  INDEX idx_start_points_count (start_points_count),
  INDEX idx_target_points_count (target_points_count),
  
  -- 外键约束
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='多对多路径规划表';

-- ============================================
-- 4. 操作日志系统
-- ============================================

-- 操作日志表
CREATE TABLE IF NOT EXISTS operation_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  operation_type ENUM('create_threat_zone', 'delete_threat_zone', 'update_threat_zone', 'plan_path', 'plan_multi_to_multi', 'execute_path', 'save_scheme', 'delete_scheme', 'load_scheme') NOT NULL COMMENT '操作类型',
  operation_name VARCHAR(100) NOT NULL COMMENT '操作名称',
  operation_description TEXT COMMENT '操作描述',
  user_id INT DEFAULT NULL COMMENT '操作用户ID',
  user_name VARCHAR(50) DEFAULT NULL COMMENT '操作用户名',
  ip_address VARCHAR(45) DEFAULT NULL COMMENT 'IP地址',
  user_agent TEXT DEFAULT NULL COMMENT '用户代理',
  request_data JSON DEFAULT NULL COMMENT '请求数据',
  response_data JSON DEFAULT NULL COMMENT '响应数据',
  operation_result ENUM('success', 'failed', 'partial') NOT NULL DEFAULT 'success' COMMENT '操作结果',
  error_message TEXT DEFAULT NULL COMMENT '错误信息',
  execution_time_ms INT DEFAULT NULL COMMENT '执行时间(毫秒)',
  affected_resources JSON DEFAULT NULL COMMENT '影响的资源',
  operation_context JSON DEFAULT NULL COMMENT '操作上下文',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  
  INDEX idx_operation_type (operation_type),
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at),
  INDEX idx_operation_result (operation_result)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';

-- ============================================
-- 5. 插入初始数据
-- ============================================

-- 插入默认管理员用户
INSERT INTO users (username, password, role)
VALUES ('admin', '$2a$10$X7UrH5QxX5QxX5QxX5QxX.5QxX5QxX5QxX5QxX5QxX5QxX5QxX5Qx', 'admin')
ON DUPLICATE KEY UPDATE username = username;

-- 插入示例路径规划数据（增强版本）
INSERT INTO path_plans (
  name, description, start_latitude, start_longitude, end_latitude, end_longitude, 
  path_points, smoothed_path_points, original_path_points, algorithm, distance, 
  estimated_time, flight_altitude, flight_speed, waypoint_count, threat_zones_avoided, 
  planning_options, status, visibility, color, created_by
) VALUES
(
  '长沙市区巡逻路径', 
  'A*算法规划路径，距离1.20km，避开2个威胁区',
  28.194090, 112.982279, 28.200000, 112.990000,
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.982279, 'lat', 28.194090, 'altitude', 100),
    JSON_OBJECT('lng', 112.985000, 'lat', 28.197000, 'altitude', 100),
    JSON_OBJECT('lng', 112.990000, 'lat', 28.200000, 'altitude', 100)
  ),
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.982279, 'lat', 28.194090, 'altitude', 100),
    JSON_OBJECT('lng', 112.985000, 'lat', 28.197000, 'altitude', 100),
    JSON_OBJECT('lng', 112.990000, 'lat', 28.200000, 'altitude', 100)
  ),
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.982279, 'lat', 28.194090, 'altitude', 100),
    JSON_OBJECT('lng', 112.984000, 'lat', 28.195500, 'altitude', 100),
    JSON_OBJECT('lng', 112.985000, 'lat', 28.197000, 'altitude', 100),
    JSON_OBJECT('lng', 112.987000, 'lat', 28.198500, 'altitude', 100),
    JSON_OBJECT('lng', 112.990000, 'lat', 28.200000, 'altitude', 100)
  ),
  'astar', 1200.50, 8, 100, 10.0, 3,
  JSON_ARRAY(1, 2),
  JSON_OBJECT('gridSize', 0.001, 'maxIterations', 8000, 'smoothed', true),
  'completed', 'public', '#FF5722', 1
),
(
  '湘江沿岸监控路径',
  'A*算法规划路径，距离1.80km，避开2个威胁区',
  28.196000, 112.985000, 28.205000, 112.995000,
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.985000, 'lat', 28.196000, 'altitude', 120),
    JSON_OBJECT('lng', 112.988000, 'lat', 28.199000, 'altitude', 120),
    JSON_OBJECT('lng', 112.992000, 'lat', 28.202000, 'altitude', 120),
    JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 120)
  ),
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.985000, 'lat', 28.196000, 'altitude', 120),
    JSON_OBJECT('lng', 112.988000, 'lat', 28.199000, 'altitude', 120),
    JSON_OBJECT('lng', 112.992000, 'lat', 28.202000, 'altitude', 120),
    JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 120)
  ),
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.985000, 'lat', 28.196000, 'altitude', 120),
    JSON_OBJECT('lng', 112.986500, 'lat', 28.197500, 'altitude', 120),
    JSON_OBJECT('lng', 112.988000, 'lat', 28.199000, 'altitude', 120),
    JSON_OBJECT('lng', 112.990000, 'lat', 28.200500, 'altitude', 120),
    JSON_OBJECT('lng', 112.992000, 'lat', 28.202000, 'altitude', 120),
    JSON_OBJECT('lng', 112.993500, 'lat', 28.203500, 'altitude', 120),
    JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 120)
  ),
  'astar', 1800.75, 12, 120, 10.0, 4,
  JSON_ARRAY(1, 3),
  JSON_OBJECT('gridSize', 0.001, 'maxIterations', 8000, 'smoothed', true),
  'executing', 'public', '#2196F3', 1
),
(
  '橘子洲头勘察路径',
  '直线规划路径，距离0.80km，无威胁区域',
  28.180000, 112.970000, 28.185000, 112.975000,
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.970000, 'lat', 28.180000, 'altitude', 50),
    JSON_OBJECT('lng', 112.972000, 'lat', 28.182000, 'altitude', 60),
    JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 50)
  ),
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.970000, 'lat', 28.180000, 'altitude', 50),
    JSON_OBJECT('lng', 112.972000, 'lat', 28.182000, 'altitude', 60),
    JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 50)
  ),
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.970000, 'lat', 28.180000, 'altitude', 50),
    JSON_OBJECT('lng', 112.972000, 'lat', 28.182000, 'altitude', 60),
    JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 50)
  ),
  'straight', 800.25, 5, 50, 8.0, 3,
  JSON_ARRAY(),
  JSON_OBJECT('gridSize', 0.001, 'maxIterations', 0, 'smoothed', false),
  'planned', 'public', '#4CAF50', 1
),
(
  '高速避障路径测试',
  'A*算法规划路径，距离2.15km，避开4个威胁区',
  28.175000, 112.965000, 28.210000, 113.000000,
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.965000, 'lat', 28.175000, 'altitude', 150),
    JSON_OBJECT('lng', 112.970000, 'lat', 28.178000, 'altitude', 150),
    JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 150),
    JSON_OBJECT('lng', 112.985000, 'lat', 28.195000, 'altitude', 150),
    JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 150),
    JSON_OBJECT('lng', 113.000000, 'lat', 28.210000, 'altitude', 150)
  ),
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.965000, 'lat', 28.175000, 'altitude', 150),
    JSON_OBJECT('lng', 112.970000, 'lat', 28.178000, 'altitude', 150),
    JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 150),
    JSON_OBJECT('lng', 112.985000, 'lat', 28.195000, 'altitude', 150),
    JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 150),
    JSON_OBJECT('lng', 113.000000, 'lat', 28.210000, 'altitude', 150)
  ),
  JSON_ARRAY(
    JSON_OBJECT('lng', 112.965000, 'lat', 28.175000, 'altitude', 150),
    JSON_OBJECT('lng', 112.967000, 'lat', 28.176000, 'altitude', 150),
    JSON_OBJECT('lng', 112.970000, 'lat', 28.178000, 'altitude', 150),
    JSON_OBJECT('lng', 112.972000, 'lat', 28.180000, 'altitude', 150),
    JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 150),
    JSON_OBJECT('lng', 112.980000, 'lat', 28.190000, 'altitude', 150),
    JSON_OBJECT('lng', 112.985000, 'lat', 28.195000, 'altitude', 150),
    JSON_OBJECT('lng', 112.990000, 'lat', 28.200000, 'altitude', 150),
    JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 150),
    JSON_OBJECT('lng', 112.998000, 'lat', 28.208000, 'altitude', 150),
    JSON_OBJECT('lng', 113.000000, 'lat', 28.210000, 'altitude', 150)
  ),
  'astar', 2150.80, 15, 150, 12.0, 6,
  JSON_ARRAY(1, 2, 3, 4),
  JSON_OBJECT('gridSize', 0.0005, 'maxIterations', 10000, 'smoothed', true),
  'completed', 'public', '#FF9800', 1
);

-- ============================================
-- 6. 数据验证和显示
-- ============================================

-- 验证数据插入
SELECT '=== 数据库初始化完成 ===' as status;
SELECT 'Users count:' as info, COUNT(*) as count FROM users;
SELECT 'Threat zones count:' as info, COUNT(*) as count FROM threat_zones;
SELECT 'Path plans count:' as info, COUNT(*) as count FROM path_plans;
SELECT 'Multi-to-multi plans count:' as info, COUNT(*) as count FROM multi_to_multi_plans;
SELECT 'Operation logs count:' as info, COUNT(*) as count FROM operation_logs;

-- 显示威胁区域统计
SELECT 
  level as '威胁级别',
  COUNT(*) as '数量',
  GROUP_CONCAT(type SEPARATOR ', ') as '类型'
FROM threat_zones 
WHERE status = 'active'
GROUP BY level 
ORDER BY FIELD(level, 'low', 'medium', 'high', 'critical');

-- 显示路径规划统计
SELECT 
  algorithm as '算法',
  status as '状态',
  COUNT(*) as '数量',
  ROUND(AVG(distance), 2) as '平均距离(米)'
FROM path_plans 
GROUP BY algorithm, status
ORDER BY algorithm, status;

-- ============================================
-- 7. 无人机信息表
-- ============================================

DROP TABLE IF EXISTS drones;
CREATE TABLE drones (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL COMMENT '无人机名称',
  type VARCHAR(100) NOT NULL COMMENT '无人机类型/型号',
  serial_number VARCHAR(100) NOT NULL UNIQUE COMMENT '无人机序列号',
  app_key VARCHAR(255) NOT NULL COMMENT 'DJI App Key',
  app_secret VARCHAR(255) NOT NULL COMMENT 'DJI App Secret',
  description TEXT DEFAULT NULL COMMENT '备注/描述',
  latitude DECIMAL(10,7) DEFAULT NULL COMMENT '当前纬度',
  longitude DECIMAL(10,7) DEFAULT NULL COMMENT '当前经度',
  battery INT DEFAULT NULL COMMENT '当前电量百分比',
  status ENUM('connected','disconnected','flying','paused') NOT NULL DEFAULT 'disconnected' COMMENT '无人机状态',
  last_heartbeat_at TIMESTAMP NULL COMMENT '最后一次收到消息时间',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='无人机信息表';

-- ============================================
-- 8. 无人机历史轨迹表
-- ============================================
CREATE TABLE IF NOT EXISTS drone_tracks (
  id INT PRIMARY KEY AUTO_INCREMENT,
  drone_id INT NOT NULL,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (drone_id) REFERENCES drones(id) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='无人机历史轨迹点';

-- ============================================
-- 初始化完成
-- ============================================ 

-- ============================================
-- 初始化完成
-- ============================================