-- ==============================================================================
-- 体系推演底层数据架构 (支持多租户、三维度索引、三元组、全维属性约束)
-- 兼容: 达梦数据库 (Dameng)
-- ==============================================================================

-- ---------------------------------------------------------
-- 第一步：创建上下文环境变量 (达梦标准语法：必须绑定包)
-- ---------------------------------------------------------

-- 1.1 创建管理包头
CREATE OR REPLACE PACKAGE PKG_SYSTEM_CTX AS
    PROCEDURE SET_USER_ID(P_ID VARCHAR(50));
END;
/

-- 1.2 创建管理包体
CREATE OR REPLACE PACKAGE BODY PKG_SYSTEM_CTX AS
    PROCEDURE SET_USER_ID(P_ID VARCHAR(50)) AS
    BEGIN
        DBMS_SESSION.SET_CONTEXT('SYSTEM_CTX', 'USER_ID', P_ID);
    END;
END;
/

-- 1.3 创建上下文对象 (注意：达梦不能加 OR REPLACE)
-- 如果提示“对象已存在”忽略即可，说明已经建好了
CREATE CONTEXT SYSTEM_CTX USING PKG_SYSTEM_CTX;


-- ---------------------------------------------------------
-- 第二步：创建基础属性字典表 (无外键依赖，最先创建)
-- ---------------------------------------------------------

-- 1. 实体详情属性表 (存储单兵级物理参数、图片与新闻)
CREATE TABLE ENTITY_ATTR_DETAIL (
    attr_id VARCHAR(50) PRIMARY KEY,
    longitude DECIMAL(15, 6),       -- 经度
    latitude DECIMAL(15, 6),        -- 纬度
    speed DECIMAL(10, 2),           -- 速度
    entity_value DECIMAL(10, 2),    -- 价值
    image_url VARCHAR(500),         -- 图片 (路径或URL)
    news_content CLOB,              -- 新闻 (文本或HTML)
    extra_json CLOB                 -- 备用扩展属性 (JSON)
);

-- 2. 体系整体属性表 (存储整个体系的评估属性)
CREATE TABLE SYSTEM_ATTR_DETAIL (
    sys_attr_id VARCHAR(50) PRIMARY KEY,
    effectiveness_score DECIMAL(10, 4), -- 体系效能
    extra_props CLOB                    -- 其他体系属性 (JSON)
);


-- ---------------------------------------------------------
-- 第三步：创建核心体系索引与三元组数据表 (带有强约束)
-- ---------------------------------------------------------

-- 3. 体系数据版本总表 (所有数据的根索引：用户+战略任务+时间)
CREATE TABLE SYSTEM_VERSION_MASTER (
    version_id VARCHAR(50) PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,          -- 索引维度1：所属用户
    strat_task_id VARCHAR(50) NOT NULL,    -- 索引维度2：战略任务ID
    sys_date DATE NOT NULL,                -- 索引维度3：体系对应日期 (年月日)
    save_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 上传保存时间戳
    
    -- 体系类型强约束
    sys_type VARCHAR(50) CHECK (sys_type IN (
        '常规体系', '关系推理后的体系', '形式化后的体系', '历史体系', 
        '当前体系', '预测体系', '指控体系', '重构体系'
    )),
    -- 体系任务强约束
    system_task VARCHAR(50) CHECK (system_task IN (
        '综合防御任务', '火力打击任务', '后勤保障任务', '医疗救援任务', '紧急疏散任务'
    )),
    -- 国家/地区约束
    country_region VARCHAR(10) CHECK (country_region IN ('美', '日', '韩', '印', '台')),
    
    sys_attr_id VARCHAR(50) REFERENCES SYSTEM_ATTR_DETAIL(sys_attr_id)
);

-- 建立三维联合索引，极大提升查询检索效率
CREATE INDEX idx_sys_version_lookup ON SYSTEM_VERSION_MASTER(user_id, strat_task_id, sys_date);


-- 4. 实体节点表 (三元组之头/尾节点)
CREATE TABLE SYSTEM_ENTITY (
    entity_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    entity_name VARCHAR(100) NOT NULL,
    
    -- 实体角色强约束 (7种)
    entity_role VARCHAR(20) CHECK (entity_role IN (
        '信息获取', '信息传输', '指挥控制', '信息对抗', '协同打击', '部署平台', '综合保障'
    )),
    -- 实体国家/地区强约束
    entity_country VARCHAR(10) CHECK (entity_country IN ('美', '日', '韩', '印', '台')),
    -- 实体可能执行的任务
    entity_task VARCHAR(50) CHECK (entity_task IN (
        '综合防御任务', '火力打击任务', '后勤保障任务', '医疗救援任务', '紧急疏散任务'
    )),
    -- 指控类型 (OODA，仅在指控体系中有效，允许为空)
    c2_type VARCHAR(10) CHECK (c2_type IN ('Observe', 'Orient', 'Decide', 'Act', NULL)),
    
    -- 实体可能存在的关系 (存贮格式由后端决定，如 "情报保障,指挥控制")
    possible_relations VARCHAR(255), 
    
    attr_id VARCHAR(50) REFERENCES ENTITY_ATTR_DETAIL(attr_id)
);


-- 5. 实体连线关系表 (三元组之边)
CREATE TABLE SYSTEM_RELATION (
    rel_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    head_entity_id VARCHAR(50) REFERENCES SYSTEM_ENTITY(entity_id),
    tail_entity_id VARCHAR(50) REFERENCES SYSTEM_ENTITY(entity_id),
    
    -- 关系类型强约束 (5种选其一)
    rel_type VARCHAR(20) CHECK (rel_type IN (
        '情报保障', '指挥控制', '状态反馈', '平台部署', '协同作战'
    ))
);


-- ---------------------------------------------------------
-- 第四步：创建 9 大体系计算分析结果表 (全指向 version_id)
-- ---------------------------------------------------------

CREATE TABLE RES_TARGET_RECOGNITION (
    res_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    entity_id VARCHAR(50) REFERENCES SYSTEM_ENTITY(entity_id),
    confidence_score DECIMAL(5, 4),
    metrics_json CLOB
);

CREATE TABLE RES_NODE_VALUE (
    res_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    entity_id VARCHAR(50) REFERENCES SYSTEM_ENTITY(entity_id),
    value_result_json CLOB
);

CREATE TABLE RES_REL_REASONING (
    res_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    reasoning_data CLOB
);

CREATE TABLE RES_FORMAL_ANALYSIS (
    res_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    formal_data CLOB
);

CREATE TABLE RES_SYS_ANALYSIS (
    res_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    analysis_data CLOB
);

CREATE TABLE RES_SYS_EVO_PREDICT (
    res_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    predict_data CLOB
);

CREATE TABLE RES_SYS_EFFECTIVENESS (
    res_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    effectiveness_data CLOB
);

CREATE TABLE RES_LINK_COG (
    res_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    link_cog_data CLOB
);

CREATE TABLE RES_NODE_COG (
    res_id VARCHAR(50) PRIMARY KEY,
    version_id VARCHAR(50) REFERENCES SYSTEM_VERSION_MASTER(version_id) ON DELETE CASCADE,
    node_cog_data CLOB
);


-- ---------------------------------------------------------
-- 第五步：当前体系专用覆盖表
-- ---------------------------------------------------------

-- 用于单独保存“当前体系”的快照，逻辑上同用户、同任务只有唯一一份
CREATE TABLE CURRENT_SYSTEM_SNAPSHOT (
    snapshot_id VARCHAR(50) PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    strat_task_id VARCHAR(50) NOT NULL,
    sys_date DATE NOT NULL,
    save_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    full_system_json CLOB, 
    CONSTRAINT uq_current_sys UNIQUE(user_id, strat_task_id)
);


-- ---------------------------------------------------------
-- 第六步：建立上下文安全视图 (极重要：实现后端模块免传 user_id)
-- ---------------------------------------------------------

-- 主表与三元组视图
CREATE OR REPLACE VIEW V_MY_SYSTEM_VERSION AS
SELECT * FROM SYSTEM_VERSION_MASTER
WHERE user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

CREATE OR REPLACE VIEW V_MY_SYSTEM_ENTITY AS
SELECT e.* FROM SYSTEM_ENTITY e
JOIN SYSTEM_VERSION_MASTER v ON e.version_id = v.version_id
WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

CREATE OR REPLACE VIEW V_MY_SYSTEM_RELATION AS
SELECT r.* FROM SYSTEM_RELATION r
JOIN SYSTEM_VERSION_MASTER v ON r.version_id = v.version_id
WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

-- 9 大分析结果视图
CREATE OR REPLACE VIEW V_MY_RES_TARGET_RECOGNITION AS
SELECT r.* FROM RES_TARGET_RECOGNITION r JOIN SYSTEM_VERSION_MASTER v ON r.version_id = v.version_id WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

CREATE OR REPLACE VIEW V_MY_RES_NODE_VALUE AS
SELECT r.* FROM RES_NODE_VALUE r JOIN SYSTEM_VERSION_MASTER v ON r.version_id = v.version_id WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

CREATE OR REPLACE VIEW V_MY_RES_REL_REASONING AS
SELECT r.* FROM RES_REL_REASONING r JOIN SYSTEM_VERSION_MASTER v ON r.version_id = v.version_id WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

CREATE OR REPLACE VIEW V_MY_RES_FORMAL_ANALYSIS AS
SELECT r.* FROM RES_FORMAL_ANALYSIS r JOIN SYSTEM_VERSION_MASTER v ON r.version_id = v.version_id WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

CREATE OR REPLACE VIEW V_MY_RES_SYS_ANALYSIS AS
SELECT r.* FROM RES_SYS_ANALYSIS r JOIN SYSTEM_VERSION_MASTER v ON r.version_id = v.version_id WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

CREATE OR REPLACE VIEW V_MY_RES_SYS_EVO_PREDICT AS
SELECT r.* FROM RES_SYS_EVO_PREDICT r JOIN SYSTEM_VERSION_MASTER v ON r.version_id = v.version_id WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

CREATE OR REPLACE VIEW V_MY_RES_SYS_EFFECTIVENESS AS
SELECT r.* FROM RES_SYS_EFFECTIVENESS r JOIN SYSTEM_VERSION_MASTER v ON r.version_id = v.version_id WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

CREATE OR REPLACE VIEW V_MY_RES_LINK_COG AS
SELECT r.* FROM RES_LINK_COG r JOIN SYSTEM_VERSION_MASTER v ON r.version_id = v.version_id WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');

CREATE OR REPLACE VIEW V_MY_RES_NODE_COG AS
SELECT r.* FROM RES_NODE_COG r JOIN SYSTEM_VERSION_MASTER v ON r.version_id = v.version_id WHERE v.user_id = SYS_CONTEXT('SYSTEM_CTX', 'USER_ID');