-- 删除触发器
DROP TRIGGER IF EXISTS trigger_transport_routes_updated_at ON transport_routes;

-- 删除函数
DROP FUNCTION IF EXISTS update_timestamp_column();

-- 删除表（按依赖顺序）
DROP TABLE IF EXISTS object_route_assignments;
DROP TABLE IF EXISTS conflict_alert_logs;
DROP TABLE IF EXISTS transport_routes;

-- ============================================
-- 基于路径的冲突检测系统数据库表
-- 创建时间: 2025-01-17
-- 版本: 1.0
-- ============================================

-- 启用PostGIS扩展（如果尚未启用）
CREATE EXTENSION IF NOT EXISTS postgis;

-- ============================================
-- 1. 路径定义表 (transport_routes)
-- ============================================
CREATE TABLE IF NOT EXISTS transport_routes (
    id BIGSERIAL PRIMARY KEY,
    
    -- 路径基本信息
    route_name VARCHAR(100) NOT NULL,
    route_type VARCHAR(20) NOT NULL CHECK (route_type IN ('AIRCRAFT', 'UNMANNED_VEHICLE', 'SPECIAL_VEHICLE', 'NORMAL_VEHICLE')),
    
    -- 路径描述
    description VARCHAR(500),
    
    -- 空间路径 (LineString)
    route_geometry GEOMETRY(LINESTRING, 4326) NOT NULL,
    
    -- 路径属性
    max_speed_kph DOUBLE PRECISION CHECK (max_speed_kph > 0),
    typical_speed_kph DOUBLE PRECISION CHECK (typical_speed_kph > 0),
    
    -- 使用条件
    active_time_start TIME,
    active_time_end TIME,
    
    -- 状态控制
    status VARCHAR(20) DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'INACTIVE', 'MAINTENANCE')),
    is_bidirectional BOOLEAN DEFAULT false,
    
    -- 审计字段
    created_by VARCHAR(50),
    updated_by VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 路径表索引
CREATE INDEX idx_transport_routes_type ON transport_routes(route_type);
CREATE INDEX idx_transport_routes_status ON transport_routes(status);
CREATE INDEX idx_transport_routes_geometry_gist ON transport_routes USING GIST(route_geometry);

-- ============================================
-- 4. 实时对象路径分配表 (object_route_assignments)
-- ============================================
CREATE TABLE IF NOT EXISTS object_route_assignments (
    id BIGSERIAL PRIMARY KEY,
    
    -- 对象信息
    object_type VARCHAR(20) NOT NULL CHECK (object_type IN ('UNMANNED_VEHICLE', 'AIRCRAFT', 'SPECIAL_VEHICLE', 'NORMAL_VEHICLE')),
    object_name VARCHAR(100) NOT NULL, -- 车牌号或航班号
    
    -- 路径分配
    assigned_route_id BIGINT NOT NULL,
    
    -- 时间信息
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- 外键约束
    FOREIGN KEY (assigned_route_id) REFERENCES transport_routes(id)
);

-- 对象路径分配表索引
CREATE INDEX idx_object_route_assignments_object_type ON object_route_assignments(object_type);
CREATE INDEX idx_object_route_assignments_route ON object_route_assignments(assigned_route_id);
CREATE INDEX idx_object_route_assignments_assigned_at ON object_route_assignments(assigned_at DESC);

-- ============================================
-- 5. 预警/告警记录表 (conflict_alert_logs)
-- ============================================
CREATE TABLE IF NOT EXISTS conflict_alert_logs (
    id BIGSERIAL PRIMARY KEY,
    
    -- 告警信息
    alert_type VARCHAR(20) NOT NULL CHECK (alert_type IN ('CONFLICT_WARNING', 'CONFLICT_ALERT')),
    alert_level VARCHAR(20) NOT NULL CHECK (alert_level IN ('WARNING', 'CRITICAL', 'EMERGENCY')),
    alert_message TEXT NOT NULL,
    
    -- 距离信息
    object1_distance DOUBLE PRECISION, -- 对象1距离冲突点距离
    object2_distance DOUBLE PRECISION, -- 对象2距离冲突点距离
    minimum_distance DOUBLE PRECISION, -- 两对象之间最小距离
    
    -- 时间信息
    alert_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 告警记录表索引
CREATE INDEX idx_conflict_alerts_type ON conflict_alert_logs(alert_type);
CREATE INDEX idx_conflict_alerts_level ON conflict_alert_logs(alert_level);
CREATE INDEX idx_conflict_alerts_time ON conflict_alert_logs(alert_time DESC);

-- ============================================
-- 6. 创建触发器自动更新时间戳
-- ============================================
CREATE OR REPLACE FUNCTION update_timestamp_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为相关表创建触发器
CREATE TRIGGER trigger_transport_routes_updated_at
    BEFORE UPDATE ON transport_routes
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp_column();

-- ============================================
-- 7. 添加表注释
-- ============================================
COMMENT ON TABLE transport_routes IS '交通路径定义表 - 存储航空器和无人车的预定路径';
COMMENT ON TABLE object_route_assignments IS '实时对象路径分配表 - 跟踪对象当前使用的路径';
COMMENT ON TABLE conflict_alert_logs IS '冲突预警告警记录表 - 记录发送的所有预警和告警消息';

-- ============================================
-- 8. 插入示例数据
-- ============================================

-- 示例路径数据 (transport_routes)
INSERT INTO transport_routes (route_name, route_type, description, route_geometry, max_speed_kph, typical_speed_kph) VALUES 
('航班CA1234路径', 'AIRCRAFT', 'CA1234航班的预定路径', 
 ST_GeomFromText('LINESTRING(120.086263 36.370484, 120.080996 36.369105)', 4326), 
 50, 50),
('航班MU5123路径', 'AIRCRAFT', 'MU5123航班的预定路径',
 ST_GeomFromText('LINESTRING(120.088076 36.374179, 120.077971 36.371503)', 4326),
 50, 50),
('特勤车鲁B123路径', 'SPECIAL_VEHICLE', '鲁B123特勤车的预定路径',
 ST_GeomFromText('LINESTRING(120.080801 36.366626, 120.083899 36.367403)', 4326),
 30, 30),
('普通车鲁B234路径', 'UNMANNED_VEHICLE', '鲁B234普通车的预定路径',
 ST_GeomFromText('LINESTRING(120.087259 36.368299, 120.083899 36.367403)', 4326),
 30, 30),
('无人车鲁B567路径', 'UNMANNED_VEHICLE', '鲁B567无人车的预定路径',
 ST_GeomFromText('LINESTRING(120.083084 36.369696, 120.084637 36.365617)', 4326),
 25, 25),
('无人车鲁B579路径', 'UNMANNED_VEHICLE', '鲁B579无人车的预定路径',
 ST_GeomFromText('LINESTRING(120.086965 36.368599, 120.086263 36.370484)', 4326),
 25, 25);

-- 示例路径分配数据 (object_route_assignments)
INSERT INTO object_route_assignments (object_type, object_name, assigned_route_id) VALUES
('AIRCRAFT', 'CA1234', (SELECT id FROM transport_routes WHERE route_name = '航班CA1234路径')),
('AIRCRAFT', 'MU5123', (SELECT id FROM transport_routes WHERE route_name = '航班MU5123路径')),
('SPECIAL_VEHICLE', '鲁B123', (SELECT id FROM transport_routes WHERE route_name = '特勤车鲁B123路径')),
('NORMAL_VEHICLE', '鲁B234', (SELECT id FROM transport_routes WHERE route_name = '普通车鲁B234路径')),
('UNMANNED_VEHICLE', '鲁B567', (SELECT id FROM transport_routes WHERE route_name = '无人车鲁B567路径')),
('UNMANNED_VEHICLE', '鲁B579', (SELECT id FROM transport_routes WHERE route_name = '无人车鲁B579路径'));