-- ================================================================
-- 车辆类型路径编码重构脚本（简化版）
-- 只管理三种车辆类型：无人车、特勤车、普通车
-- 版本：1.0
-- 创建时间：2025-07-13
-- ================================================================

-- 1. 备份现有数据
CREATE TABLE sys_vehicle_type_backup AS SELECT * FROM sys_vehicle_type;
CREATE TABLE sys_vehicle_info_backup AS SELECT * FROM sys_vehicle_info;

-- 2. 创建新的车辆类型表结构
DROP TABLE IF EXISTS "public"."sys_vehicle_type_new" CASCADE;

CREATE TABLE "public"."sys_vehicle_type_new" (
    "id" BIGSERIAL NOT NULL,                        -- 代理键（技术主键）
    "type_code" VARCHAR(30) NOT NULL UNIQUE,        -- 路径编码（业务主键）如：UV.PT
    "type_name" VARCHAR(100) NOT NULL,              -- 类型名称
    "display_name_cn" VARCHAR(100) NOT NULL,        -- 中文显示名称
    "display_name_en" VARCHAR(100),                 -- 英文显示名称
    "path_level" INTEGER NOT NULL,                  -- 路径层级 (1,2)
    "parent_code" VARCHAR(30),                      -- 父路径编码
    "full_path" VARCHAR(200) NOT NULL,              -- 完整路径描述
    "is_leaf" BOOLEAN DEFAULT false,                -- 是否叶子节点
    "sort_order" INTEGER DEFAULT 0,                 -- 排序序号
    "enabled" BOOLEAN DEFAULT true,                 -- 是否启用
    "description" VARCHAR(500),                     -- 描述信息
    "attributes" JSONB,                             -- 扩展属性（速度限制、权限等）
    "create_by" VARCHAR(64),                        -- 创建者
    "create_time" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    "update_by" VARCHAR(64),                        -- 更新者
    "update_time" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, -- 更新时间
    "version" BIGINT DEFAULT 0,                     -- 乐观锁版本号
    CONSTRAINT "sys_vehicle_type_new_pkey" PRIMARY KEY ("id"),
    CONSTRAINT "sys_vehicle_type_new_code_unique" UNIQUE ("type_code")
);

-- 表和字段注释
COMMENT ON TABLE "public"."sys_vehicle_type_new" IS '车辆类型表（路径编码模式）';
COMMENT ON COLUMN "public"."sys_vehicle_type_new"."id" IS '代理主键ID';
COMMENT ON COLUMN "public"."sys_vehicle_type_new"."type_code" IS '路径编码（如UV.PT表示无人车.巡逻车）';
COMMENT ON COLUMN "public"."sys_vehicle_type_new"."type_name" IS '类型名称';
COMMENT ON COLUMN "public"."sys_vehicle_type_new"."display_name_cn" IS '中文显示名称';
COMMENT ON COLUMN "public"."sys_vehicle_type_new"."display_name_en" IS '英文显示名称';
COMMENT ON COLUMN "public"."sys_vehicle_type_new"."path_level" IS '路径层级（1=一级分类，2=二级分类）';
COMMENT ON COLUMN "public"."sys_vehicle_type_new"."parent_code" IS '父路径编码';
COMMENT ON COLUMN "public"."sys_vehicle_type_new"."full_path" IS '完整路径描述';
COMMENT ON COLUMN "public"."sys_vehicle_type_new"."is_leaf" IS '是否叶子节点';
COMMENT ON COLUMN "public"."sys_vehicle_type_new"."attributes" IS '扩展属性JSON（权限、速度限制等）';

-- 创建索引
CREATE INDEX "idx_sys_vehicle_type_new_type_code" ON "public"."sys_vehicle_type_new" ("type_code");
CREATE INDEX "idx_sys_vehicle_type_new_parent_code" ON "public"."sys_vehicle_type_new" ("parent_code");
CREATE INDEX "idx_sys_vehicle_type_new_path_level" ON "public"."sys_vehicle_type_new" ("path_level");
CREATE INDEX "idx_sys_vehicle_type_new_enabled" ON "public"."sys_vehicle_type_new" ("enabled");
CREATE INDEX "idx_sys_vehicle_type_new_sort_order" ON "public"."sys_vehicle_type_new" ("sort_order");

-- 3. 插入标准的路径编码车辆类型数据

-- 一级分类（三种车辆类型）
INSERT INTO "public"."sys_vehicle_type_new" 
("type_code", "type_name", "display_name_cn", "display_name_en", "path_level", "parent_code", "full_path", "is_leaf", "sort_order", "enabled", "description", "attributes") VALUES
('UV', 'UNMANNED_VEHICLE', '无人车', 'Unmanned Vehicle', 1, NULL, '无人车', false, 1, true, '自动驾驶无人车辆', '{"category": "UNMANNED", "autonomous": true, "maxSpeed": 30, "requiresPermission": true}'),
('SP', 'SPECIAL_VEHICLE', '特勤车', 'Special Vehicle', 1, NULL, '特勤车', false, 2, true, '具有特殊权限的车辆', '{"category": "SPECIAL", "hasSpecialPrivileges": true, "priority": "HIGH", "accessAllAreas": true}'),
('NM', 'NORMAL_VEHICLE', '普通车', 'Normal Vehicle', 1, NULL, '普通车', false, 3, true, '一般机场运营车辆', '{"category": "NORMAL", "priority": "NORMAL", "maxSpeed": 40}');

-- 二级分类 - 无人车具体类型
INSERT INTO "public"."sys_vehicle_type_new" 
("type_code", "type_name", "display_name_cn", "display_name_en", "path_level", "parent_code", "full_path", "is_leaf", "sort_order", "enabled", "description", "attributes") VALUES
('UV.PT', 'PATROL_VEHICLE', '巡逻车', 'Patrol Vehicle', 2, 'UV', '无人车/巡逻车', true, 1, true, '自主巡逻无人车', '{"maxSpeed": 25, "patrolRange": 5000, "sensors": ["CAMERA", "RADAR"]}'),
('UV.DL', 'DELIVERY_VEHICLE', '配送车', 'Delivery Vehicle', 2, 'UV', '无人车/配送车', true, 2, true, '自主配送无人车', '{"maxSpeed": 30, "cargoCapacity": 500}'),
('UV.IN', 'INSPECTION_VEHICLE', '检查车', 'Inspection Vehicle', 2, 'UV', '无人车/检查车', true, 3, true, '自主检查无人车', '{"maxSpeed": 20, "sensors": ["CAMERA", "LIDAR", "THERMAL"]}'),
('UV.SH', 'SHUTTLE_VEHICLE', '接驳车', 'Shuttle Vehicle', 2, 'UV', '无人车/接驳车', true, 4, true, '无人接驳车', '{"maxSpeed": 35, "passengerCapacity": 8}'),
('UV.BD', 'BIRD_DETERRENT', '驱鸟车', 'Bird Deterrent Vehicle', 2, 'UV', '无人车/驱鸟车', true, 5, true, '自动驱鸟无人车', '{"maxSpeed": 25, "soundSystem": true}');

-- 二级分类 - 特勤车具体类型
INSERT INTO "public"."sys_vehicle_type_new" 
("type_code", "type_name", "display_name_cn", "display_name_en", "path_level", "parent_code", "full_path", "is_leaf", "sort_order", "enabled", "description", "attributes") VALUES
('SP.FT', 'FIRE_TRUCK', '消防车', 'Fire Truck', 2, 'SP', '特勤车/消防车', true, 1, true, '机场消防车', '{"emergencyVehicle": true, "priority": "CRITICAL", "accessAllAreas": true, "maxSpeed": 60}'),
('SP.PC', 'POLICE_CAR', '警车', 'Police Car', 2, 'SP', '特勤车/警车', true, 2, true, '机场警车', '{"emergencyVehicle": true, "priority": "HIGH", "accessAllAreas": true, "maxSpeed": 80}'),
('SP.AM', 'AMBULANCE', '救护车', 'Ambulance', 2, 'SP', '特勤车/救护车', true, 3, true, '机场救护车', '{"emergencyVehicle": true, "priority": "CRITICAL", "maxSpeed": 70}'),
('SP.FM', 'FOLLOW_ME_CAR', '引导车', 'Follow Me Car', 2, 'SP', '特勤车/引导车', true, 4, true, '飞机引导车', '{"priority": "HIGH", "followMode": true, "maxSpeed": 50}'),
('SP.SC', 'SECURITY_CAR', '安保车', 'Security Car', 2, 'SP', '特勤车/安保车', true, 5, true, '机场安保车辆', '{"priority": "HIGH", "patrolVehicle": true, "maxSpeed": 60}');

-- 二级分类 - 普通车具体类型
INSERT INTO "public"."sys_vehicle_type_new" 
("type_code", "type_name", "display_name_cn", "display_name_en", "path_level", "parent_code", "full_path", "is_leaf", "sort_order", "enabled", "description", "attributes") VALUES
('NM.BG', 'BAGGAGE_CART', '行李车', 'Baggage Cart', 2, 'NM', '普通车/行李车', true, 1, true, '行李运输车', '{"maxSpeed": 25, "cargoType": "BAGGAGE"}'),
('NM.CL', 'CLEANING_VEHICLE', '清洁车', 'Cleaning Vehicle', 2, 'NM', '普通车/清洁车', true, 2, true, '机场清洁车', '{"maxSpeed": 20, "equipmentType": "CLEANING"}'),
('NM.CT', 'CATERING_TRUCK', '餐车', 'Catering Truck', 2, 'NM', '普通车/餐车', true, 3, true, '航空餐饮车', '{"maxSpeed": 25, "cargoType": "CATERING"}'),
('NM.TG', 'TUG_VEHICLE', '拖车', 'Tug Vehicle', 2, 'NM', '普通车/拖车', true, 4, true, '飞机拖车', '{"maxSpeed": 15, "towingCapacity": true}'),
('NM.FL', 'FUEL_TRUCK', '加油车', 'Fuel Truck', 2, 'NM', '普通车/加油车', true, 5, true, '燃料补给车', '{"hazardousMaterial": true, "maxSpeed": 20, "fuelCapacity": true}'),
('NM.MT', 'MAINTENANCE_VEHICLE', '维护车', 'Maintenance Vehicle', 2, 'NM', '普通车/维护车', true, 6, true, '设备维护车', '{"maxSpeed": 30, "equipmentType": "MAINTENANCE"}'),
('NM.TR', 'TRANSPORT_TRUCK', '运输车', 'Transport Truck', 2, 'NM', '普通车/运输车', true, 7, true, '货物运输车', '{"cargoCapacity": 5000, "maxSpeed": 40}');

-- 4. 创建类型转换映射表（用于数据迁移）
CREATE TABLE vehicle_type_migration_mapping (
    old_type_id BIGINT,
    old_type_name VARCHAR(100),
    new_type_code VARCHAR(30),
    migration_notes VARCHAR(500)
);

-- 插入迁移映射数据（基于现有数据）
INSERT INTO vehicle_type_migration_mapping (old_type_id, old_type_name, new_type_code, migration_notes) VALUES
(5, '无人车', 'UV', '无人车一级分类映射'),
(6, '特勤车', 'SP', '特勤车一级分类映射'), 
(7, '普通车', 'NM', '普通车一级分类映射'),
(21, '测试车', 'NM', '测试车归类为普通车'),
(8, '消防车', 'SP.FT', '消防车具体类型映射'),
(9, '接驳车', 'UV.SH', '接驳车归类为无人车接驳车'),
(10, '驱鸟车', 'UV.BD', '驱鸟车归类为无人车驱鸟车'),
(11, '特勤车', 'SP.SC', '二级特勤车归类为安保车'),
(12, '运输车', 'NM.TR', '运输车归类为普通运输车'),
(17, '无人车新增测试2', 'UV.PT', '测试无人车归类为巡逻车'),
(22, '测试车1', 'NM.BG', '测试车1归类为行李车');

-- 5. 创建路径编码相关的辅助函数

-- 根据路径编码获取父级类型
CREATE OR REPLACE FUNCTION get_parent_type_by_code(type_code VARCHAR(30))
RETURNS VARCHAR(30) AS $$
DECLARE
    parent_code VARCHAR(30);
    dot_pos INTEGER;
BEGIN
    -- 找到点的位置
    dot_pos := POSITION('.' IN type_code);
    
    IF dot_pos > 0 THEN
        -- 提取父路径编码（点之前的部分）
        parent_code := LEFT(type_code, dot_pos - 1);
        RETURN parent_code;
    ELSE
        -- 一级分类没有父级
        RETURN NULL;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 根据路径编码获取所有子类型
CREATE OR REPLACE FUNCTION get_child_types_by_code(parent_code VARCHAR(30))
RETURNS TABLE(type_code VARCHAR(30), type_name VARCHAR(100), display_name_cn VARCHAR(100)) AS $$
BEGIN
    RETURN QUERY
    SELECT vt.type_code, vt.type_name, vt.display_name_cn
    FROM sys_vehicle_type_new vt
    WHERE vt.parent_code = parent_code
      AND vt.enabled = true
    ORDER BY vt.sort_order, vt.type_code;
END;
$$ LANGUAGE plpgsql;

-- 根据路径编码获取一级分类
CREATE OR REPLACE FUNCTION get_top_level_type(type_code VARCHAR(30))
RETURNS VARCHAR(30) AS $$
DECLARE
    dot_pos INTEGER;
BEGIN
    -- 找到点的位置
    dot_pos := POSITION('.' IN type_code);
    
    IF dot_pos > 0 THEN
        -- 返回点之前的部分（一级分类）
        RETURN LEFT(type_code, dot_pos - 1);
    ELSE
        -- 本身就是一级分类
        RETURN type_code;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 检查是否为特勤车辆（用于权限判断）
CREATE OR REPLACE FUNCTION is_special_vehicle(type_code VARCHAR(30))
RETURNS BOOLEAN AS $$
BEGIN
    RETURN get_top_level_type(type_code) = 'SP';
END;
$$ LANGUAGE plpgsql;

-- 检查是否为无人车（用于自动驾驶判断）
CREATE OR REPLACE FUNCTION is_unmanned_vehicle(type_code VARCHAR(30))
RETURNS BOOLEAN AS $$
BEGIN
    RETURN get_top_level_type(type_code) = 'UV';
END;
$$ LANGUAGE plpgsql;

-- 6. 创建视图用于兼容现有查询
CREATE OR REPLACE VIEW sys_vehicle_type_compatible AS
SELECT 
    id as type_id,
    CASE 
        WHEN parent_code IS NULL THEN NULL
        ELSE (SELECT id FROM sys_vehicle_type_new WHERE type_code = vt.parent_code)
    END as parent_id,
    display_name_cn as type_name,
    path_level as level,
    create_by,
    create_time,
    update_by,
    update_time,
    description as remark,
    type_code,
    full_path
FROM sys_vehicle_type_new vt
WHERE enabled = true;

-- 7. 创建Migration存储过程（仅显示迁移计划，不实际执行）
CREATE OR REPLACE FUNCTION show_migration_plan()
RETURNS TABLE(
    vehicle_id BIGINT,
    license_plate VARCHAR(50),
    old_type_id BIGINT,
    old_type_name VARCHAR(100),
    new_type_code VARCHAR(30),
    new_type_id BIGINT,
    new_display_name VARCHAR(100)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        vi.vehicle_id,
        vi.license_plate,
        vi.type_id as old_type_id,
        vt_old.type_name as old_type_name,
        vm.new_type_code,
        vtn.id as new_type_id,
        vtn.display_name_cn as new_display_name
    FROM sys_vehicle_info vi
    LEFT JOIN sys_vehicle_type vt_old ON vi.type_id = vt_old.type_id
    LEFT JOIN vehicle_type_migration_mapping vm ON vi.type_id = vm.old_type_id
    LEFT JOIN sys_vehicle_type_new vtn ON vm.new_type_code = vtn.type_code
    ORDER BY vi.vehicle_id;
END;
$$ LANGUAGE plpgsql;

-- 8. 验证数据完整性
SELECT 
    '新车辆类型表创建完成' as status,
    (SELECT COUNT(*) FROM sys_vehicle_type_new) as new_type_count,
    (SELECT COUNT(*) FROM sys_vehicle_type_new WHERE path_level = 1) as level_1_count,
    (SELECT COUNT(*) FROM sys_vehicle_type_new WHERE path_level = 2) as level_2_count,
    (SELECT COUNT(*) FROM vehicle_type_migration_mapping) as mapping_count;

-- 显示新的车辆类型层级结构
SELECT 
    path_level,
    type_code,
    display_name_cn,
    parent_code,
    is_leaf,
    sort_order,
    (attributes->>'maxSpeed')::INTEGER as max_speed_kmh,
    CASE 
        WHEN (attributes->>'emergencyVehicle')::BOOLEAN = true THEN '紧急车辆'
        WHEN (attributes->>'autonomous')::BOOLEAN = true THEN '自动驾驶'
        ELSE '普通车辆'
    END as vehicle_category
FROM sys_vehicle_type_new 
ORDER BY path_level, sort_order, type_code;