-- ============================================
-- 统一数据库迁移脚本
-- 将CollisionAvoidanceSystem的空间数据表合并到QAUP-Management数据库
-- 创建时间: 2025-01-15
-- 版本: 1.0.0
-- ============================================

-- ============================================
-- 第一步：启用PostGIS扩展
-- ============================================
-- 启用PostGIS扩展（如果尚未启用）
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- 验证PostGIS版本
SELECT PostGIS_Version();

-- ============================================
-- 第二步：创建更新时间戳函数（如果不存在）
-- ============================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- 第三步：创建空间数据核心表
-- ============================================

-- 1. 车辆位置表 (vehicle_locations) - 规范化版本
-- 只存储位置相关数据，车辆基础信息通过关联sys_vehicle_info表获取
CREATE TABLE IF NOT EXISTS vehicle_locations (
    id BIGSERIAL PRIMARY KEY,
    
    -- 车辆ID（关联sys_vehicle_info.vehicle_id）
    -- 注意：存储数字ID，不是车牌号
    vehicle_id BIGINT NOT NULL,
    
    -- PostGIS空间字段 (WGS84坐标系)
    location GEOMETRY(POINT, 4326) NOT NULL,
    
    -- 位置相关属性
    altitude DOUBLE PRECISION,
    heading DOUBLE PRECISION CHECK (heading >= 0 AND heading < 360),
    speed DOUBLE PRECISION CHECK (speed >= 0),
    
    -- 时间戳
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 数据质量指标
    data_quality VARCHAR(20),
    
    -- 审计字段
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 车辆位置表索引
CREATE INDEX IF NOT EXISTS idx_vehicle_locations_vehicle_id ON vehicle_locations(vehicle_id);
CREATE INDEX IF NOT EXISTS idx_vehicle_locations_timestamp ON vehicle_locations(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_vehicle_locations_location_gist ON vehicle_locations USING GIST(location);
CREATE INDEX IF NOT EXISTS idx_vehicle_locations_compound ON vehicle_locations(vehicle_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_vehicle_locations_data_quality ON vehicle_locations(data_quality);

-- 2. 机场区域表 (airport_areas)
CREATE TABLE IF NOT EXISTS airport_areas (
    id BIGSERIAL PRIMARY KEY,
    area_id VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(30) NOT NULL,
    description VARCHAR(500),
    
    -- PostGIS空间字段
    boundary GEOMETRY(POLYGON, 4326) NOT NULL,
    
    -- 速度和限制
    speed_limit_kph DOUBLE PRECISION,
    restricted BOOLEAN DEFAULT false,
    allowed_vehicle_types JSONB,
    allowed_aircraft_types JSONB,
    max_height DOUBLE PRECISION,
    max_weight DOUBLE PRECISION,
    
    -- 时间控制
    active_time TIMESTAMP WITH TIME ZONE,
    expiry_time TIMESTAMP WITH TIME ZONE,
    
    -- 区域属性
    enabled BOOLEAN NOT NULL DEFAULT true,
    priority INTEGER NOT NULL DEFAULT 1 CHECK (priority >= 1),
    
    -- 版本控制
    version BIGINT NOT NULL DEFAULT 1,
    
    -- 审计字段
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 机场区域表索引
CREATE INDEX IF NOT EXISTS idx_airport_areas_area_id ON airport_areas(area_id);
CREATE INDEX IF NOT EXISTS idx_airport_areas_type ON airport_areas(type);
CREATE INDEX IF NOT EXISTS idx_airport_areas_enabled ON airport_areas(enabled);
CREATE INDEX IF NOT EXISTS idx_airport_areas_boundary_gist ON airport_areas USING GIST(boundary);
CREATE INDEX IF NOT EXISTS idx_airport_areas_allowed_vehicles_gin ON airport_areas USING GIN(allowed_vehicle_types);

-- 3. 车辆轨迹表 (vehicle_trajectories)
CREATE TABLE IF NOT EXISTS vehicle_trajectories (
    id BIGSERIAL PRIMARY KEY,
    vehicle_id BIGINT NOT NULL,
    trajectory_date DATE NOT NULL,
    
    -- PostGIS轨迹线
    trajectory_line GEOMETRY(LINESTRING, 4326),
    
    -- 统计信息
    total_distance DOUBLE PRECISION,
    max_speed DOUBLE PRECISION,
    avg_speed DOUBLE PRECISION,
    duration_seconds INTEGER,
    point_count INTEGER,
    
    -- 时间范围
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    
    -- 审计字段
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 车辆轨迹表索引
CREATE INDEX IF NOT EXISTS idx_vehicle_trajectories_vehicle_id ON vehicle_trajectories(vehicle_id);
CREATE INDEX IF NOT EXISTS idx_vehicle_trajectories_date ON vehicle_trajectories(trajectory_date DESC);
CREATE INDEX IF NOT EXISTS idx_vehicle_trajectories_line_gist ON vehicle_trajectories USING GIST(trajectory_line);

-- ============================================
-- 第四步：创建规则引擎表
-- ============================================

-- 1. 空间规则表 (spatial_rules)
CREATE TABLE IF NOT EXISTS spatial_rules (
    id BIGSERIAL PRIMARY KEY,
    rule_id VARCHAR(50) NOT NULL UNIQUE,
    rule_name VARCHAR(100) NOT NULL,
    
    -- 规则类型分类
    rule_category VARCHAR(30) NOT NULL CHECK (rule_category IN (
        'ACCESS_CONTROL', 'SPEED_LIMIT', 'VEHICLE_RESTRICTION', 'HEIGHT_LIMIT',
        'WEIGHT_LIMIT', 'TIME_RESTRICTION', 'PRIORITY_CONTROL', 'SAFETY_ZONE', 'CUSTOM'
    )),
    
    rule_type VARCHAR(50) NOT NULL,
    
    -- 空间对象关联
    spatial_object_type VARCHAR(30) NOT NULL CHECK (spatial_object_type IN (
        'AREA', 'ROAD', 'INTERSECTION', 'CUSTOM_GEOMETRY'
    )),
    spatial_object_id VARCHAR(50),
    custom_geometry GEOMETRY(POLYGON, 4326),
    
    -- 规则参数
    rule_parameters JSONB NOT NULL DEFAULT '{}',
    applicable_vehicle_types JSONB DEFAULT '[]',
    applicable_aircraft_types JSONB DEFAULT '[]',
    
    -- 告警配置
    alert_level VARCHAR(20) NOT NULL DEFAULT 'WARNING' CHECK (alert_level IN (
        'INFO', 'WARNING', 'CRITICAL', 'EMERGENCY'
    )),
    alert_message VARCHAR(500),
    response_actions JSONB DEFAULT '[]',
    
    -- 规则状态
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' CHECK (status IN (
        'ACTIVE', 'INACTIVE', 'SUSPENDED', 'EXPIRED'
    )),
    
    -- 时间控制
    effective_from TIMESTAMP WITH TIME ZONE,
    effective_until TIMESTAMP WITH TIME ZONE,
    time_patterns JSONB DEFAULT '[]',
    
    -- 优先级
    priority INTEGER NOT NULL DEFAULT 1 CHECK (priority >= 1),
    version BIGINT NOT NULL DEFAULT 1,
    
    -- 审计字段
    created_by VARCHAR(50),
    updated_by VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 空间规则表索引
CREATE INDEX IF NOT EXISTS idx_spatial_rules_rule_id ON spatial_rules(rule_id);
CREATE INDEX IF NOT EXISTS idx_spatial_rules_category ON spatial_rules(rule_category);
CREATE INDEX IF NOT EXISTS idx_spatial_rules_status ON spatial_rules(status);
CREATE INDEX IF NOT EXISTS idx_spatial_rules_custom_geometry_gist ON spatial_rules USING GIST(custom_geometry);
CREATE INDEX IF NOT EXISTS idx_spatial_rules_parameters_gin ON spatial_rules USING GIN(rule_parameters);

-- 2. 规则违反事件表 (rule_violation_events)
CREATE TABLE IF NOT EXISTS rule_violation_events (
    id BIGSERIAL PRIMARY KEY,
    event_id VARCHAR(50) NOT NULL UNIQUE,
    
    -- 关联规则
    rule_id VARCHAR(50) NOT NULL,
    
    -- 违规主体
    subject_type VARCHAR(20) NOT NULL CHECK (subject_type IN ('VEHICLE', 'AIRCRAFT')),
    subject_id VARCHAR(50) NOT NULL,
    
    -- 违规详情
    violation_type VARCHAR(50) NOT NULL,
    violation_details JSONB NOT NULL DEFAULT '{}',
    
    -- 位置信息
    location GEOMETRY(POINT, 4326) NOT NULL,
    spatial_context VARCHAR(100),
    
    -- 违规数据
    actual_value JSONB,
    threshold_value JSONB,
    
    -- 严重程度
    severity VARCHAR(20) NOT NULL CHECK (severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
    
    -- 响应状态
    response_status VARCHAR(20) NOT NULL DEFAULT 'PENDING' CHECK (response_status IN (
        'PENDING', 'ACKNOWLEDGED', 'IN_PROGRESS', 'RESOLVED', 'IGNORED'
    )),
    
    -- 响应动作记录
    response_actions_taken JSONB DEFAULT '[]',
    response_details JSONB DEFAULT '{}',
    
    -- 时间信息
    detected_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    acknowledged_at TIMESTAMP WITH TIME ZONE,
    resolved_at TIMESTAMP WITH TIME ZONE,
    
    -- 审计字段
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 规则违反事件表索引
CREATE INDEX IF NOT EXISTS idx_rule_violations_rule_id ON rule_violation_events(rule_id);
CREATE INDEX IF NOT EXISTS idx_rule_violations_subject ON rule_violation_events(subject_type, subject_id);
CREATE INDEX IF NOT EXISTS idx_rule_violations_severity ON rule_violation_events(severity);
CREATE INDEX IF NOT EXISTS idx_rule_violations_status ON rule_violation_events(response_status);
CREATE INDEX IF NOT EXISTS idx_rule_violations_detected_at ON rule_violation_events(detected_at DESC);
CREATE INDEX IF NOT EXISTS idx_rule_violations_location_gist ON rule_violation_events USING GIST(location);

-- ============================================
-- 第五步：创建业务关联视图
-- ============================================

-- 车辆完整信息视图（基础信息 + 最新位置）- 规范化版本
CREATE OR REPLACE VIEW vehicle_complete_info AS
SELECT 
    vi.vehicle_id,
    vi.license_plate,
    vi.vin_number,
    vi.type_id,
    vt.type_name,
    vi.brand,
    vi.owning_unit,
    vi.contact_person,
    vi.phone_number,
    vi.image_url,
    vl.location,
    vl.altitude,
    vl.heading,
    vl.speed,
    vl.timestamp as last_location_time,
    vl.data_quality,
    vi.create_time,
    vi.update_time
FROM sys_vehicle_info vi
LEFT JOIN sys_vehicle_type vt ON vi.type_id = vt.type_id
LEFT JOIN LATERAL (
    SELECT * FROM vehicle_locations 
    WHERE vehicle_id = vi.vehicle_id 
    ORDER BY timestamp DESC 
    LIMIT 1
) vl ON true;

-- 车辆实时状态统计视图 - 规范化版本
CREATE OR REPLACE VIEW vehicle_status_summary AS
SELECT 
    COUNT(*) as total_vehicles,
    COUNT(CASE WHEN last_location_time > NOW() - INTERVAL '5 minutes' THEN 1 END) as active_vehicles,
    COUNT(CASE WHEN last_location_time > NOW() - INTERVAL '30 minutes' AND last_location_time <= NOW() - INTERVAL '5 minutes' THEN 1 END) as inactive_vehicles,
    COUNT(CASE WHEN last_location_time IS NULL OR last_location_time <= NOW() - INTERVAL '30 minutes' THEN 1 END) as offline_vehicles,
    AVG(speed) as avg_speed,
    MAX(speed) as max_speed
FROM vehicle_complete_info;

-- ============================================
-- 第六步：创建触发器
-- ============================================

-- 为新表创建自动更新时间戳的触发器
CREATE TRIGGER trigger_vehicle_locations_updated_at
    BEFORE UPDATE ON vehicle_locations
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER trigger_airport_areas_updated_at
    BEFORE UPDATE ON airport_areas
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER trigger_spatial_rules_updated_at
    BEFORE UPDATE ON spatial_rules
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================
-- 第七步：添加表注释
-- ============================================

-- 添加表和视图注释
COMMENT ON TABLE vehicle_locations IS '车辆位置信息表（规范化版本）- 只存储位置相关数据，车辆基础信息通过关联sys_vehicle_info表获取';
COMMENT ON COLUMN vehicle_locations.vehicle_id IS '车辆ID，关联sys_vehicle_info.vehicle_id（数字主键）';
COMMENT ON VIEW vehicle_complete_info IS '车辆完整信息视图 - 包含基础信息和最新位置，通过vehicle_id关联';

-- ============================================
-- 第八步：添加外键约束（可选）
-- ============================================

-- 外键约束：确保vehicle_id在sys_vehicle_info表中存在
-- 注意：只有在确保数据完整性的情况下才启用
-- ALTER TABLE vehicle_locations 
-- ADD CONSTRAINT fk_vehicle_locations_vehicle_id 
-- FOREIGN KEY (vehicle_id) REFERENCES sys_vehicle_info(vehicle_id) 
-- ON DELETE CASCADE ON UPDATE CASCADE;

-- ============================================
-- 第八步：插入示例配置数据
-- ============================================

-- 插入默认机场区域（示例）
INSERT INTO airport_areas (area_id, name, type, description, boundary, enabled) VALUES 
('RUNWAY_01', '跑道01区域', 'RUNWAY', '主跑道区域', 
 ST_GeomFromText('POLYGON((116.3 39.9, 116.31 39.9, 116.31 39.91, 116.3 39.91, 116.3 39.9))', 4326), 
 true)
ON CONFLICT (area_id) DO NOTHING;

-- 插入默认安全规则（示例）
INSERT INTO spatial_rules (rule_id, rule_name, rule_category, rule_type, spatial_object_type, spatial_object_id, 
                          rule_parameters, alert_level, status) VALUES 
('SPEED_LIMIT_RUNWAY', '跑道限速规则', 'SPEED_LIMIT', 'MAX_SPEED', 'AREA', 'RUNWAY_01',
 '{"max_speed_kph": 30}', 'WARNING', 'ACTIVE')
ON CONFLICT (rule_id) DO NOTHING;

-- ============================================
-- 验证脚本执行结果
-- ============================================

-- 检查PostGIS扩展
SELECT name, default_version, installed_version 
FROM pg_available_extensions 
WHERE name IN ('postgis', 'postgis_topology');

-- 检查新创建的表
SELECT schemaname, tablename, tableowner 
FROM pg_tables 
WHERE tablename IN (
    'vehicle_locations', 'airport_areas', 'vehicle_trajectories', 
    'spatial_rules', 'rule_violation_events'
)
ORDER BY tablename;

-- 检查空间索引
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes 
WHERE indexname LIKE '%gist%' 
AND tablename IN ('vehicle_locations', 'airport_areas', 'vehicle_trajectories', 'spatial_rules', 'rule_violation_events')
ORDER BY tablename, indexname;

-- 检查视图
SELECT schemaname, viewname, viewowner
FROM pg_views 
WHERE viewname IN ('vehicle_complete_info', 'vehicle_status_summary')
ORDER BY viewname;

-- ============================================
-- 迁移完成提示
-- ============================================

DO $$
BEGIN
    RAISE NOTICE '==============================================';
    RAISE NOTICE '数据库迁移完成！';
    RAISE NOTICE '已成功合并CollisionAvoidanceSystem的空间数据表到QAUP-Management数据库';
    RAISE NOTICE '新增表：vehicle_locations, airport_areas, vehicle_trajectories, spatial_rules, rule_violation_events';
    RAISE NOTICE '新增视图：vehicle_complete_info, vehicle_status_summary';
    RAISE NOTICE '已启用PostGIS扩展支持空间数据操作';
    RAISE NOTICE '==============================================';
END $$;