SIT/database/schema.sql

-- 字段表
CREATE TABLE IF NOT EXISTS fields (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name TEXT NOT NULL UNIQUE,
    domain TEXT NOT NULL,
    sub_domains TEXT,
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    range_min TEXT,
    range_max TEXT,
    default_value TEXT,
    unit TEXT,
    description TEXT,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 字段索引
CREATE INDEX IF NOT EXISTS idx_fields_domain ON fields(domain);
CREATE INDEX IF NOT EXISTS idx_fields_name ON fields(name);
CREATE INDEX IF NOT EXISTS idx_fields_type ON fields(type);
CREATE INDEX IF NOT EXISTS idx_fields_full_name ON fields(full_name);

-- 消息表
CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name TEXT NOT NULL UNIQUE,
    system_name TEXT NOT NULL,
    message_type TEXT NOT NULL,
    version TEXT NOT NULL,
    description TEXT,
    protocol TEXT NOT NULL,
    serialization TEXT NOT NULL,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 消息索引
CREATE INDEX IF NOT EXISTS idx_messages_system_name ON messages(system_name);
CREATE INDEX IF NOT EXISTS idx_messages_type ON messages(message_type);
CREATE INDEX IF NOT EXISTS idx_messages_version ON messages(version);

-- 消息字段关联表
CREATE TABLE IF NOT EXISTS message_fields (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id INTEGER NOT NULL,
    field_id INTEGER NOT NULL,
    field_order INTEGER NOT NULL,
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    FOREIGN KEY (field_id) REFERENCES fields(id) ON DELETE CASCADE,
    UNIQUE(message_id, field_id)
);

-- 消息字段关联索引
CREATE INDEX IF NOT EXISTS idx_message_fields_message ON message_fields(message_id);
CREATE INDEX IF NOT EXISTS idx_message_fields_field ON message_fields(field_id);

-- 映射模型表
CREATE TABLE IF NOT EXISTS mapping_models (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    mapping_type TEXT NOT NULL,
    source_field_ids TEXT NOT NULL,
    target_field_id INTEGER NOT NULL,
    operator TEXT,
    operand TEXT,
    language TEXT,
    code TEXT,
    description TEXT,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (target_field_id) REFERENCES fields(id) ON DELETE CASCADE
);

-- 映射模型索引
CREATE INDEX IF NOT EXISTS idx_mapping_models_type ON mapping_models(mapping_type);
CREATE INDEX IF NOT EXISTS idx_mapping_models_target ON mapping_models(target_field_id);

-- 映射图节点表
CREATE TABLE IF NOT EXISTS mapping_graph_nodes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    field_id INTEGER NOT NULL UNIQUE,
    x_position REAL DEFAULT 0,
    y_position REAL DEFAULT 0,
    FOREIGN KEY (field_id) REFERENCES fields(id) ON DELETE CASCADE
);

-- 映射图边表
CREATE TABLE IF NOT EXISTS mapping_graph_edges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_field_id INTEGER NOT NULL,
    target_field_id INTEGER NOT NULL,
    mapping_id INTEGER NOT NULL,
    weight REAL DEFAULT 1.0,
    FOREIGN KEY (source_field_id) REFERENCES fields(id) ON DELETE CASCADE,
    FOREIGN KEY (target_field_id) REFERENCES fields(id) ON DELETE CASCADE,
    FOREIGN KEY (mapping_id) REFERENCES mapping_models(id) ON DELETE CASCADE,
    UNIQUE(source_field_id, target_field_id, mapping_id)
);

-- 映射图边索引
CREATE INDEX IF NOT EXISTS idx_graph_edges_source ON mapping_graph_edges(source_field_id);
CREATE INDEX IF NOT EXISTS idx_graph_edges_target ON mapping_graph_edges(target_field_id);

-- 复合映射表
CREATE TABLE IF NOT EXISTS composite_mappings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 复合映射关联表
CREATE TABLE IF NOT EXISTS composite_mapping_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    composite_id INTEGER NOT NULL,
    mapping_id INTEGER NOT NULL,
    item_order INTEGER NOT NULL,
    FOREIGN KEY (composite_id) REFERENCES composite_mappings(id) ON DELETE CASCADE,
    FOREIGN KEY (mapping_id) REFERENCES mapping_models(id) ON DELETE CASCADE
);

-- 触发器：更新字段的updated_time
CREATE TRIGGER IF NOT EXISTS update_fields_timestamp
AFTER UPDATE ON fields
BEGIN
    UPDATE fields SET updated_time = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- 触发器：更新消息的updated_time
CREATE TRIGGER IF NOT EXISTS update_messages_timestamp
AFTER UPDATE ON messages
BEGIN
    UPDATE messages SET updated_time = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- 触发器：更新映射模型的updated_time
CREATE TRIGGER IF NOT EXISTS update_mapping_models_timestamp
AFTER UPDATE ON mapping_models
BEGIN
    UPDATE mapping_models SET updated_time = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;