-- ================================================================
-- 电子围栏功能数据库表结构更新脚本
-- 用于支持无人车电子围栏准入检测功能
-- 版本：1.0
-- 创建时间：2025-07-12
-- ================================================================

-- 1. 更新 airport_areas 表结构
-- 先删除旧表（如果存在）
DROP TABLE IF EXISTS "public"."airport_areas" CASCADE;

-- 创建新的机场区域表，匹配Java实体类AirportArea
CREATE TABLE "public"."airport_areas" (
    "id" BIGSERIAL NOT NULL,
    "area_id" varchar(50) NOT NULL UNIQUE,
    "name" varchar(100) NOT NULL,
    "type" varchar(30) NOT NULL,
    "boundary" geometry(POLYGON, 4326) NOT NULL,
    "description" varchar(500),
    "active_time" timestamptz,
    "expiry_time" timestamptz,
    "enabled" boolean DEFAULT true,
    "priority" int4 DEFAULT 1,
    "area_sqm" float8,
    "functional_category" varchar(50),
    "parent_area_id" varchar(50),
    "created_at" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
    "version" bigint DEFAULT 0,
    CONSTRAINT "airport_areas_pkey" PRIMARY KEY ("id")
);

-- 表注释
COMMENT ON TABLE "public"."airport_areas" IS '机场区域信息表 - 支持电子围栏功能';
COMMENT ON COLUMN "public"."airport_areas"."id" IS '主键ID';
COMMENT ON COLUMN "public"."airport_areas"."area_id" IS '区域标识符';
COMMENT ON COLUMN "public"."airport_areas"."name" IS '区域名称';
COMMENT ON COLUMN "public"."airport_areas"."type" IS '区域类型（RUNWAY、TAXIWAY、APRON等）';
COMMENT ON COLUMN "public"."airport_areas"."boundary" IS '区域几何边界（PostGIS多边形）';
COMMENT ON COLUMN "public"."airport_areas"."description" IS '区域描述';
COMMENT ON COLUMN "public"."airport_areas"."active_time" IS '生效时间（用于临时区域）';
COMMENT ON COLUMN "public"."airport_areas"."expiry_time" IS '失效时间（用于临时区域）';
COMMENT ON COLUMN "public"."airport_areas"."enabled" IS '是否启用';
COMMENT ON COLUMN "public"."airport_areas"."priority" IS '优先级（数值越高优先级越高）';
COMMENT ON COLUMN "public"."airport_areas"."area_sqm" IS '区域面积（平方米）';
COMMENT ON COLUMN "public"."airport_areas"."functional_category" IS '功能分类';
COMMENT ON COLUMN "public"."airport_areas"."parent_area_id" IS '父区域ID';
COMMENT ON COLUMN "public"."airport_areas"."version" IS '数据版本号（用于乐观锁）';

-- 创建索引
CREATE INDEX "idx_airport_areas_area_id" ON "public"."airport_areas" ("area_id");
CREATE INDEX "idx_airport_areas_type" ON "public"."airport_areas" ("type");
CREATE INDEX "idx_airport_areas_enabled" ON "public"."airport_areas" ("enabled");
CREATE INDEX "idx_airport_areas_functional_category" ON "public"."airport_areas" ("functional_category");
CREATE INDEX "idx_airport_areas_parent_area_id" ON "public"."airport_areas" ("parent_area_id");
CREATE INDEX "idx_airport_areas_geom" ON "public"."airport_areas" USING GIST ("boundary");

-- 2. 创建空间规则表
DROP TABLE IF EXISTS "public"."spatial_rules" CASCADE;
DROP TABLE IF EXISTS "public"."spatial_rule_vehicle_types" CASCADE;

CREATE TABLE "public"."spatial_rules" (
    "rule_id" varchar(50) NOT NULL,
    "rule_name" varchar(200) NOT NULL,
    "description" text,
    "rule_category" varchar(50) NOT NULL,
    "status" varchar(20) NOT NULL,
    "priority" int4 NOT NULL,
    "spatial_object_type" varchar(30) NOT NULL,
    "spatial_object_id" varchar(50),
    "rule_parameters" jsonb,
    "alert_level" varchar(20) NOT NULL,
    "alert_message" varchar(500),
    "effective_start_time" timestamp(6),
    "effective_end_time" timestamp(6),
    "daily_start_time" time,
    "daily_end_time" time,
    "weekday_pattern" int4,
    "time_patterns" jsonb,
    "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "created_by" varchar(100),
    "updated_by" varchar(100),
    "version" bigint DEFAULT 0,
    "custom_geometry" geometry,
    CONSTRAINT "spatial_rules_pkey" PRIMARY KEY ("rule_id")
);

-- 创建车辆类型关联表
CREATE TABLE "public"."spatial_rule_vehicle_types" (
    "rule_id" varchar(50) NOT NULL,
    "vehicle_type" varchar(30) NOT NULL,
    CONSTRAINT "spatial_rule_vehicle_types_pkey" PRIMARY KEY ("rule_id", "vehicle_type"),
    CONSTRAINT "fk_spatial_rule_vehicle_types_rule" FOREIGN KEY ("rule_id") REFERENCES "public"."spatial_rules" ("rule_id") ON DELETE CASCADE
);

-- 表注释
COMMENT ON TABLE "public"."spatial_rules" IS '空间安全规则表';
COMMENT ON TABLE "public"."spatial_rule_vehicle_types" IS '空间规则允许的车辆类型表';

-- 创建索引
CREATE INDEX "idx_spatial_rules_status" ON "public"."spatial_rules" ("status");
CREATE INDEX "idx_spatial_rules_category" ON "public"."spatial_rules" ("rule_category");
CREATE INDEX "idx_spatial_rules_object_type" ON "public"."spatial_rules" ("spatial_object_type");
CREATE INDEX "idx_spatial_rules_priority" ON "public"."spatial_rules" ("priority");
CREATE INDEX "idx_spatial_rules_object_id" ON "public"."spatial_rules" ("spatial_object_id");

-- 3. 插入测试电子围栏区域
-- 基于route.md中两辆无人车的运行路线创建测试区域

-- 无人车A测试区域 (基于路线: 经度120.083084,纬度36.369696 到 经度120.084637,纬度36.365617)
INSERT INTO "public"."airport_areas" (
    "area_id", "name", "type", "boundary", "description", "enabled", "priority", 
    "functional_category"
) VALUES (
    'UV_TEST_AREA_A',
    '无人车A测试区域',
    'UNMANNED_VEHICLE_ZONE',
    ST_GeomFromText('POLYGON((120.082584 36.370196, 120.085137 36.370196, 120.085137 36.365117, 120.082584 36.365117, 120.082584 36.370196))', 4326),
    '无人车A运行测试区域，包含起点和终点的矩形区域',
    true,
    5,
    'UNMANNED_VEHICLE_OPERATIONS'
);

-- 无人车B测试区域 (基于路线: 经度120.086965,纬度36.368599 到 经度120.086263,纬度36.370484)
INSERT INTO "public"."airport_areas" (
    "area_id", "name", "type", "boundary", "description", "enabled", "priority",
    "functional_category"
) VALUES (
    'UV_TEST_AREA_B',
    '无人车B测试区域',
    'UNMANNED_VEHICLE_ZONE',
    ST_GeomFromText('POLYGON((120.085765 36.371484, 120.087465 36.371484, 120.087465 36.368099, 120.085765 36.368099, 120.085765 36.371484))', 4326),
    '无人车B运行测试区域，包含起点和终点的矩形区域',
    true,
    5,
    'UNMANNED_VEHICLE_OPERATIONS'
);

-- 共同测试区域 (两辆无人车路径可能相交的区域)
INSERT INTO "public"."airport_areas" (
    "area_id", "name", "type", "boundary", "description", "enabled", "priority",
    "functional_category"
) VALUES (
    'UV_INTERSECTION_ZONE',
    '无人车交汇测试区域',
    'INTERSECTION_ZONE',
    ST_GeomFromText('POLYGON((120.083000 36.371000, 120.087000 36.371000, 120.087000 36.365000, 120.083000 36.365000, 120.083000 36.371000))', 4326),
    '无人车路径可能相交的大型测试区域，用于测试电子围栏功能',
    true,
    10,
    'UNMANNED_VEHICLE_OPERATIONS'
);

-- 4. 插入测试规则
-- 无人车A测试区域的准入控制规则
INSERT INTO "public"."spatial_rules" (
    "rule_id", "rule_name", "description", "rule_category", "status", "priority",
    "spatial_object_type", "spatial_object_id", "alert_level", "alert_message",
    "rule_parameters", "created_by"
) VALUES (
    'GEOFENCE_UV_AREA_A_ACCESS',
    '无人车A区域准入控制',
    '限制无人车A测试区域的访问权限',
    'ACCESS_CONTROL',
    'ACTIVE',
    1,
    'AREA',
    'UV_TEST_AREA_A',
    'WARNING',
    '进入无人车A测试区域需要授权',
    '{"accessControl":{"requiresPermission":true,"allowedOperationModes":["AUTONOMOUS","MANUAL"]},"physicalLimits":{"maxSpeed":20.0},"safetyRequirements":{"minBatteryLevel":30}}',
    'system'
);

-- 无人车B测试区域的准入控制规则
INSERT INTO "public"."spatial_rules" (
    "rule_id", "rule_name", "description", "rule_category", "status", "priority",
    "spatial_object_type", "spatial_object_id", "alert_level", "alert_message",
    "rule_parameters", "created_by"
) VALUES (
    'GEOFENCE_UV_AREA_B_ACCESS',
    '无人车B区域准入控制',
    '限制无人车B测试区域的访问权限',
    'ACCESS_CONTROL',
    'ACTIVE',
    1,
    'AREA',
    'UV_TEST_AREA_B',
    'WARNING',
    '进入无人车B测试区域需要授权',
    '{"accessControl":{"requiresPermission":true,"allowedOperationModes":["AUTONOMOUS","MANUAL"]},"physicalLimits":{"maxSpeed":20.0},"safetyRequirements":{"minBatteryLevel":30}}',
    'system'
);

-- 交汇区域的严格控制规则
INSERT INTO "public"."spatial_rules" (
    "rule_id", "rule_name", "description", "rule_category", "status", "priority",
    "spatial_object_type", "spatial_object_id", "alert_level", "alert_message",
    "rule_parameters", "created_by"
) VALUES (
    'GEOFENCE_INTERSECTION_CRITICAL',
    '交汇区域严格控制',
    '交汇区域需要严格的安全控制',
    'ACCESS_CONTROL',
    'ACTIVE',
    10,
    'AREA',
    'UV_INTERSECTION_ZONE',
    'CRITICAL',
    '进入交汇区域需要最高级别授权',
    '{"accessControl":{"requiresPermission":true,"allowedOperationModes":["MANUAL"]},"physicalLimits":{"maxSpeed":15.0},"safetyRequirements":{"minBatteryLevel":50,"requiresOperatorPresence":true}}',
    'system'
);

-- 5. 插入规则允许的车辆类型
INSERT INTO "public"."spatial_rule_vehicle_types" ("rule_id", "vehicle_type") VALUES
('GEOFENCE_UV_AREA_A_ACCESS', 'UNMANNED_VEHICLE'),
('GEOFENCE_UV_AREA_B_ACCESS', 'UNMANNED_VEHICLE'),
('GEOFENCE_INTERSECTION_CRITICAL', 'UNMANNED_VEHICLE');

-- 6. 添加APRON（停机坪）区域和限速规则（兼容原有限速检测系统）
-- APRON区域
INSERT INTO "public"."airport_areas" (
    "area_id", "name", "type", "boundary", "description", "enabled", "priority",
    "functional_category"
) VALUES (
    'APRON_01',
    '停机坪区域01',
    'APRON',
    ST_GeomFromText('POLYGON((120.080000 36.372000, 120.088000 36.372000, 120.088000 36.366000, 120.080000 36.366000, 120.080000 36.372000))', 4326),
    '主要停机坪区域，覆盖无人车和航空器活动区域，包含route.md中所有测试路线',
    true,
    8,
    'AIRCRAFT_OPERATIONS'
);

-- 停机坪限速规则
INSERT INTO "public"."spatial_rules" (
    "rule_id", "rule_name", "description", "rule_category", "status", "priority",
    "spatial_object_type", "spatial_object_id", "alert_level", "alert_message",
    "rule_parameters", "created_by"
) VALUES (
    'SPEED_LIMIT_APRON',
    '停机坪限速规则',
    '停机坪区域内所有车辆的限速控制规则',
    'SPEED_LIMIT',
    'ACTIVE',
    1,
    'AREA',
    'APRON_01',
    'WARNING',
    '停机坪内超速行驶',
    '{"maxSpeed":15.0,"unit":"kmh","warningThreshold":0.8,"strictMode":true}',
    'system'
);

-- 停机坪限速规则的车辆类型（所有类型车辆都受限速规则约束）
INSERT INTO "public"."spatial_rule_vehicle_types" ("rule_id", "vehicle_type") VALUES
('SPEED_LIMIT_APRON', 'UNMANNED_VEHICLE'),
('SPEED_LIMIT_APRON', 'AIRCRAFT'),
('SPEED_LIMIT_APRON', 'SPECIAL_VEHICLE'),
('SPEED_LIMIT_APRON', 'AIRPORT_VEHICLE'),
('SPEED_LIMIT_APRON', 'NORMAL_VEHICLE');

-- 7. 添加滑行道区域和限速规则
INSERT INTO "public"."airport_areas" (
    "area_id", "name", "type", "boundary", "description", "enabled", "priority",
    "functional_category"
) VALUES (
    'TAXIWAY_ALPHA',
    '滑行道Alpha',
    'TAXIWAY',
    ST_GeomFromText('POLYGON((120.082000 36.370000, 120.086000 36.370000, 120.086000 36.368000, 120.082000 36.368000, 120.082000 36.370000))', 4326),
    '主要滑行道，连接停机坪和跑道',
    true,
    7,
    'AIRCRAFT_OPERATIONS'
);

-- 滑行道限速规则
INSERT INTO "public"."spatial_rules" (
    "rule_id", "rule_name", "description", "rule_category", "status", "priority",
    "spatial_object_type", "spatial_object_id", "alert_level", "alert_message",
    "rule_parameters", "created_by"
) VALUES (
    'SPEED_LIMIT_TAXIWAY',
    '滑行道限速规则',
    '滑行道区域内的限速控制',
    'SPEED_LIMIT',
    'ACTIVE',
    2,
    'AREA',
    'TAXIWAY_ALPHA',
    'WARNING',
    '滑行道内超速行驶',
    '{"maxSpeed":25.0,"unit":"kmh","warningThreshold":0.9}',
    'system'
);

-- 滑行道限速规则的车辆类型
INSERT INTO "public"."spatial_rule_vehicle_types" ("rule_id", "vehicle_type") VALUES
('SPEED_LIMIT_TAXIWAY', 'UNMANNED_VEHICLE'),
('SPEED_LIMIT_TAXIWAY', 'AIRCRAFT'),
('SPEED_LIMIT_TAXIWAY', 'SPECIAL_VEHICLE'),
('SPEED_LIMIT_TAXIWAY', 'AIRPORT_VEHICLE');

-- 8. 创建限速检测视图
CREATE OR REPLACE VIEW speed_limit_areas AS
SELECT 
    aa.area_id,
    aa.name as area_name,
    aa.type as area_type,
    aa.boundary,
    sr.rule_id,
    sr.rule_name,
    (sr.rule_parameters->>'maxSpeed')::float as max_speed_kmh,
    (sr.rule_parameters->>'warningThreshold')::float as warning_threshold,
    sr.alert_level,
    array_agg(DISTINCT vt.vehicle_type) as applicable_vehicle_types
FROM airport_areas aa
JOIN spatial_rules sr ON aa.area_id = sr.spatial_object_id
JOIN spatial_rule_vehicle_types vt ON sr.rule_id = vt.rule_id
WHERE sr.rule_category = 'SPEED_LIMIT' AND sr.status = 'ACTIVE' AND aa.enabled = true
GROUP BY aa.area_id, aa.name, aa.type, aa.boundary, sr.rule_id, sr.rule_name, 
         sr.rule_parameters, sr.alert_level;

-- 9. 更新序列值（如果需要）
SELECT setval('airport_areas_id_seq', (SELECT COALESCE(MAX(id), 1) FROM airport_areas));

-- 10. 验证数据插入
SELECT 
    area_id, 
    name, 
    type, 
    enabled, 
    priority,
    ST_AsText(boundary) as boundary_wkt,
    created_at
FROM airport_areas 
WHERE area_id IN ('UV_TEST_AREA_A', 'UV_TEST_AREA_B', 'UV_INTERSECTION_ZONE', 'APRON_01', 'TAXIWAY_ALPHA')
ORDER BY priority DESC;

SELECT 
    rule_id,
    rule_name,
    rule_category,
    status,
    spatial_object_id,
    alert_level,
    created_at
FROM spatial_rules
WHERE rule_id LIKE 'GEOFENCE_%' OR rule_id LIKE 'SPEED_LIMIT_%'
ORDER BY rule_category, rule_id;

-- 11. 验证限速检测视图
SELECT * FROM speed_limit_areas ORDER BY max_speed_kmh;

-- 完成
SELECT 'Database schema updated successfully for geofence functionality!' as status;