-- =====================================================
-- dis核对 完整部署脚本
-- 数据库: urban_ops_agent
-- 包含: 表结构变更 + 菜单创建 + 角色授权
-- 执行方式: 在对应环境的数据库中执行此 SQL
-- =====================================================

-- ==========================================
-- Part 0: garden_gis_road 表结构变更
-- ==========================================
-- 新增多边形围栏边界坐标字段（MySQL 8.x 兼容写法）
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'garden_gis_road'
      AND COLUMN_NAME = 'gis_polygon_coords'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE garden_gis_road ADD COLUMN gis_polygon_coords LONGTEXT NOT NULL COMMENT ''多边形/矩形围栏边界坐标，格式：POLYGON((x1 y1, x2 y2, ..., xn yn, x1 y1)) 存储的是WGS84坐标系''',
    'SELECT ''COLUMN gis_polygon_coords already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ==========================================
-- Part 1: 菜单创建
-- ==========================================

-- 1. 新增「dis核对」子菜单（位于电子围栏下）
INSERT INTO system_menu
(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
('gis数据核对', '', 2, 1, 5698, 'disCheck', 'ep:view', 'fence/dis-check/index', 'DisCheck', 0, 1, 0, 1, '1', NOW(), '1', NOW(), 0);

-- 2. 获取上一步插入的菜单ID，用于后续按钮权限插入
SET @dis_check_menu_id = LAST_INSERT_ID();

-- 3. 新增「导出」按钮权限（挂在新菜单下）
INSERT INTO system_menu
(name, permission, type, sort, parent_id, path, icon, component, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES
('导出', 'garden:gis-road:export', 3, 0, @dis_check_menu_id, '', '', '', 0, 1, 0, 1, '1', NOW(), '1', NOW(), 0);

-- 4. 新增「查询」按钮权限
INSERT INTO system_menu
(name, permission, type, sort, parent_id, path, icon, component, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES
('查询', 'garden:gis-road:query', 3, 1, @dis_check_menu_id, '', '', '', 0, 1, 0, 1, '1', NOW(), '1', NOW(), 0);

-- 5. 给超级管理员(role_id=1)分配菜单权限
INSERT INTO system_role_menu (role_id, menu_id, creator, create_time, updater, update_time)
VALUES
(1, @dis_check_menu_id, '1', NOW(), '1', NOW());

INSERT INTO system_role_menu (role_id, menu_id, creator, create_time, updater, update_time)
SELECT 1, id, '1', NOW(), '1', NOW()
FROM system_menu
WHERE parent_id = @dis_check_menu_id AND deleted = 0;

-- =====================================================
-- 验证插入结果
-- =====================================================
SELECT id, name, type, parent_id, path, component, permission
FROM system_menu
WHERE parent_id = 5698 OR id = @dis_check_menu_id
ORDER BY parent_id, sort;

SELECT rm.role_id, rm.menu_id, m.name, m.permission
FROM system_role_menu rm
JOIN system_menu m ON rm.menu_id = m.id
WHERE rm.menu_id = @dis_check_menu_id
   OR rm.menu_id IN (SELECT id FROM system_menu WHERE parent_id = @dis_check_menu_id);