-- ============================================================
-- 河湖长制文档管理 - 数据库建表语句
-- 达梦数据库(Database) SQL Script
-- ============================================================

-- 目录表
CREATE TABLE DOC_DIRECTORY (
    ID BIGINT PRIMARY KEY IDENTITY(1,1),
    DIR_NAME VARCHAR2(200) NOT NULL COMMENT '目录名称',
    PARENT_ID BIGINT DEFAULT 0 COMMENT '父目录ID，0表示根目录',
    ORDER_NUM INT DEFAULT 0 COMMENT '排序号',
    STATUS CHAR(1) DEFAULT '0' COMMENT '状态：0启用，1禁用',
    REMARK VARCHAR2(500) COMMENT '备注',
    CREATE_BY VARCHAR2(64) COMMENT '创建人',
    CREATE_TIME DATETIME DEFAULT SYSDATE COMMENT '创建时间',
    UPDATE_BY VARCHAR2(64) COMMENT '更新人',
    UPDATE_TIME DATETIME DEFAULT SYSDATE COMMENT '更新时间'
);

-- 目录表索引
CREATE INDEX IDX_DOC_DIRECTORY_PARENT ON DOC_DIRECTORY(PARENT_ID);
CREATE INDEX IDX_DOC_DIRECTORY_STATUS ON DOC_DIRECTORY(STATUS);

-- 文件表
CREATE TABLE DOC_FILE (
    ID BIGINT PRIMARY KEY IDENTITY(1,1),
    DIR_ID BIGINT NOT NULL COMMENT '所属目录ID',
    FILE_NAME VARCHAR2(200) NOT NULL COMMENT '文件名称',
    FILE_PATH VARCHAR2(500) NOT NULL COMMENT '文件存储路径',
    FILE_SIZE BIGINT DEFAULT 0 COMMENT '文件大小(字节)',
    FILE_TYPE VARCHAR2(50) COMMENT '文件类型',
    FILE_EXT VARCHAR2(20) COMMENT '文件扩展名',
    UPLOAD_BY VARCHAR2(64) COMMENT '上传人',
    UPLOAD_TIME DATETIME DEFAULT SYSDATE COMMENT '上传时间',
    STATUS CHAR(1) DEFAULT '0' COMMENT '状态：0正常，1删除',
    REMARK VARCHAR2(500) COMMENT '备注',
    CREATE_BY VARCHAR2(64) COMMENT '创建人',
    CREATE_TIME DATETIME DEFAULT SYSDATE COMMENT '创建时间',
    UPDATE_BY VARCHAR2(64) COMMENT '更新人',
    UPDATE_TIME DATETIME DEFAULT SYSDATE COMMENT '更新时间'
);

-- 文件表索引
CREATE INDEX IDX_DOC_FILE_DIR ON DOC_FILE(DIR_ID);
CREATE INDEX IDX_DOC_FILE_STATUS ON DOC_FILE(STATUS);
CREATE INDEX IDX_DOC_FILE_UPLOAD_TIME ON DOC_FILE(UPLOAD_TIME);

-- 初始化数据 - 根目录
INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('河湖长制', 0, 1, '0', 'admin', SYSDATE);

-- 初始化数据 - 一级目录
INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('制度文件', 1, 1, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('工作方案', 1, 2, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('考核评估', 1, 3, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('巡河记录', 1, 4, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('问题台账', 1, 5, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('信息简报', 1, 6, '0', 'admin', SYSDATE);

-- 初始化数据 - 二级目录
INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('河长职责', 2, 1, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('湖长职责', 2, 2, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('会议制度', 2, 3, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('巡查制度', 2, 4, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('年度方案', 3, 1, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('专项方案', 3, 2, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('季度考核', 4, 1, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('年度考核', 4, 2, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('月度巡河', 5, 1, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('问题整改', 6, 1, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('督办记录', 6, 2, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('工作简报', 7, 1, '0', 'admin', SYSDATE);

INSERT INTO DOC_DIRECTORY (DIR_NAME, PARENT_ID, ORDER_NUM, STATUS, CREATE_BY, CREATE_TIME)
VALUES ('通报信息', 7, 2, '0', 'admin', SYSDATE);