imitsys/sqlite.sql

-- 删除设备初装表
DROP TABLE IF EXISTS device_init;

-- 重新创建设备初装表
CREATE TABLE IF NOT EXISTS device_init
(
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    sys_type              TEXT,
    content              TEXT,
    secret               TEXT,
    algorithm            TEXT,
    self_inspection_code TEXT,
    device_name          TEXT,
    init_number          TEXT,
    version_number       TEXT,
    local_num            TEXT,
    firmware_version     TEXT,
    device_status        TEXT,
    file_name            TEXT,
    data_installed       TEXT,
    create_time          TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time          TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除参数设置表
DROP TABLE IF EXISTS parameter_set;

-- 重新创建参数设置表
CREATE TABLE IF NOT EXISTS parameter_set
(
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    ip_address  TEXT,
    create_time TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除密码机初装表
DROP TABLE IF EXISTS secret_init;

-- 重新创建密码机初装表
CREATE TABLE IF NOT EXISTS secret_init
(
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    password_init      TEXT,
    algorithm_param    TEXT,
    param_code         TEXT,
    file_name          TEXT,
    secret_data_status TEXT,
    secret_rain        TEXT,
    secret_kill        TEXT,
    mmj_kill           TEXT,
    create_time        TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time        TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除秘钥体格式列表表
DROP TABLE IF EXISTS secret_format;

-- 重新创建秘钥体格式列表表
CREATE TABLE IF NOT EXISTS secret_format
(
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name     TEXT,
    carrier_name     TEXT,
    carrier_number   TEXT,
    manager_filename TEXT,
    system_name      TEXT,
    shstem_id        TEXT,
    publisher        TEXT,
    publisher_type   TEXT,
    current_status   TEXT,
    carrier_type     TEXT,
    create_time      TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time      TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除秘钥体申请列表表
DROP TABLE IF EXISTS secret_ask;

-- 重新创建秘钥体申请列表表
CREATE TABLE IF NOT EXISTS secret_ask
(
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    apply_new_old  TEXT,
    ask_type       TEXT,
    product_name   TEXT,
    product_num    TEXT,
    apply_num      TEXT,
    apply_time     DATETIME,
    apply_result   TEXT,
    carrier_model  TEXT,
    current_status TEXT,
    carrier_type   TEXT,
    create_time    TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time    TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除秘钥体导入表
DROP TABLE IF EXISTS secret_import;

-- 重新创建秘钥体导入表
CREATE TABLE IF NOT EXISTS secret_import
(
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    device_mark_id   TEXT DEFAULT NULL,
    product_name   TEXT,
    product_num    TEXT,
    product_code   TEXT,
    device_name    TEXT,
    carrier_type   TEXT,
    carrier_model  TEXT,
    ip             TEXT,
    sys_type       TEXT,
    apply_num      TEXT,
    apply_model    TEXT,
    number         INTEGER,
    source         TEXT,
    current_status TEXT,
    manager_model_id   TEXT,
    device_model   TEXT,
    is_clean       INTEGER,
    import_time    DATETIME,
    key_type       TEXT,
    dist_type      TEXT,
    usage_reserve_area TEXT,
    key_type_name   TEXT,
    assembly_situation    TEXT,
    management_flag   TEXT,
    enter_date     TEXT,
    set_number     TEXT,
    install_method   TEXT,
    net             TEXT,
    content          TEXT,
    active_date    DATETIME DEFAULT CURRENT_TIMESTAMP,
    create_time    TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time    TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除秘钥体接收单表
DROP TABLE IF EXISTS secret_body_acceptance;

-- 重新创建秘钥体接收单表
CREATE TABLE IF NOT EXISTS secret_body_acceptance
(
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name    TEXT,
    product_num     TEXT,
    source          TEXT,
    operator        TEXT,
    import_time     DATETIME,
    is_interface    TEXT,
    send_unit       TEXT,
    password_type   TEXT,
    number_carriers TEXT,
    code_carriers   TEXT,
    apply_model     TEXT,
    number          INTEGER,
    notes           TEXT,
    create_time     TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time     TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除上下级配发列表表
DROP TABLE IF EXISTS secret_issue_subordinate;

-- 重新创建上下级配发列表表
CREATE TABLE IF NOT EXISTS secret_issue_subordinate
(
    id                       INTEGER PRIMARY KEY AUTOINCREMENT,
    issue_type               TEXT,
    product_name             TEXT,
    product_num              TEXT,
    carrier_type             TEXT,
    apply_model              TEXT,
    dist_manager_name        TEXT,
    dist_manager_identifer   TEXT,
    issue_num                INTEGER,
    issue_status             TEXT,
    del_notes                TEXT,
    sub_operate_time         DATETIME,
    apply_manager_sys_name   TEXT,
    apply_manager_model_name TEXT,
    quantity                 TEXT,
    quantity_time            DATETIME,
    dist_code                TEXT,
    apply_code               TEXT,
    create_time              TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time              TEXT DEFAULT CURRENT_TIMESTAMP
);


-- 删除向邮箱配发表
DROP TABLE IF EXISTS secret_issue_mailbox;

-- 重新创建向邮箱配发表
CREATE TABLE IF NOT EXISTS secret_issue_mailbox
(
    id                       INTEGER PRIMARY KEY AUTOINCREMENT,
    key_dist_platform_name   TEXT,
    key_dist_platform_entity TEXT,
    product_name             TEXT,
    product_code             TEXT,
    apply_type               TEXT,
    apply_model              TEXT,
    total                    INTEGER,
    download_num             INTEGER,
    empower                  TEXT,
    priority                 TEXT,
    over_time                DATETIME,
    notification_flag        TEXT,
    delete_flag              TEXT,
    create_time              TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time              TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除向专用密码管理系统配发表
DROP TABLE IF EXISTS secret_issue_private;

-- 重新创建向专用密码管理系统配发表
CREATE TABLE IF NOT EXISTS secret_issue_private
(
    id                     INTEGER PRIMARY KEY AUTOINCREMENT,
    duty_name              TEXT,
    duty_area_identify     TEXT,
    affiliated_network     TEXT,
    ip_address             TEXT,
    product_name           TEXT,
    product_code           TEXT,
    issue_manager_sys_name TEXT,
    operate_time           TEXT,
    sync_status            TEXT,
    create_time            TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time            TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除密钥体配发三联单1表
DROP TABLE IF EXISTS secret_issue_three_one;

-- 重新创建密钥体配发三联单1表
CREATE TABLE IF NOT EXISTS secret_issue_three_one
(
    id                     INTEGER PRIMARY KEY AUTOINCREMENT,
    duty_name              TEXT,
    duty_area_identify     TEXT,
    affiliated_network     TEXT,
    ip_address             TEXT,
    product_name           TEXT,
    product_code           TEXT,
    issue_manager_sys_name TEXT,
    create_time            TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time            TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除密钥体配发三联单2表
DROP TABLE IF EXISTS secret_issue_three_two;

-- 重新创建密钥体配发三联单2表
CREATE TABLE IF NOT EXISTS secret_issue_three_two
(
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    duty_name          TEXT,
    duty_area_identify TEXT,
    affiliated_network TEXT,
    ip_address         TEXT,
    product_name       TEXT,
    product_code       TEXT,
    notes              TEXT,
    create_time        TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time        TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除明文载体包封表
DROP TABLE IF EXISTS clear_text_key_body;

-- 重新创建明文载体包封表
CREATE TABLE IF NOT EXISTS clear_text_key_body
(
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name     TEXT,
    product_code     TEXT,
    carrier_code     TEXT,
    carrier_type     TEXT,
    carrier_model    TEXT,
    file_path        TEXT,
    carrier_position TEXT,
    create_time      TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time      TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 删除秘文载体包封表
DROP TABLE IF EXISTS encrypted_key_body;

-- 重新创建秘文载体包封表
CREATE TABLE IF NOT EXISTS encrypted_key_body
(
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    carrier_type        TEXT,
    carrier_model       TEXT,
    file_path           TEXT,
    product_name        TEXT,
    product_code        TEXT,
    originator_identify TEXT,
    receiver_identify   TEXT,
    create_time         TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time         TEXT DEFAULT CURRENT_TIMESTAMP
);


-- 管理系统管理盘数据维护
DROP TABLE IF EXISTS manage_sys_disk_data_maintenance;

CREATE TABLE IF NOT EXISTS manage_sys_disk_data_maintenance
(
    id                     INTEGER PRIMARY KEY AUTOINCREMENT,
    management_disk_number TEXT,
    carrier_model          TEXT,
    file_path              TEXT,
    product_name           TEXT,
    product_code           TEXT,
    create_time            TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time            TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 统型完善普密设备密钥互通关系配置
DROP TABLE IF EXISTS relation_setting;

CREATE TABLE IF NOT EXISTS relation_setting
(
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    device_name      TEXT,
    manager_model_id TEXT,
    device_model     TEXT,
    type             INTEGER,
    create_time      TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time      TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 管理密钥装配
DROP TABLE IF EXISTS manager_secret_setting;

CREATE TABLE IF NOT EXISTS manager_secret_setting
(
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    device_name        TEXT,
    manager_model_id   TEXT,
    device_model       TEXT,
    product_name       TEXT,
    product_code       TEXT,
    usage_reserve_area INTEGER,
    dist_type          TEXT,
    install_method     TEXT,
    start_date         TEXT,
    set_number         TEXT,
    carrier_type       TEXT,
    enter_date         DATETIME,
    management_flag    TEXT,
    assembly_situation TEXT,
    create_time        TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time        TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 用户密钥装配
DROP TABLE IF EXISTS user_relation_setting;

CREATE TABLE IF NOT EXISTS user_relation_setting
(
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    device_name        TEXT,
    manager_model_id   TEXT,
    device_model       TEXT,
    product_name       TEXT,
    product_code       TEXT,
    usage_reserve_area INTEGER,
    dist_type          TEXT,
    install_method     TEXT,
    start_date         TEXT,
    set_number         TEXT,
    carrier_type       TEXT,
    enter_date         DATETIME,
    management_flag    TEXT,
    assembly_situation TEXT,
    create_time        TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time        TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 专用密钥装配
DROP TABLE IF EXISTS special_setting;

CREATE TABLE IF NOT EXISTS special_setting
(
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    device_name         TEXT,
    manager_model_id    TEXT,
    device_model        TEXT,
    product_name        TEXT,
    product_code        TEXT,
    usage_reserve_area  INTEGER,
    dist_type           TEXT,
    install_method      TEXT,
    start_date          TEXT,
    set_number          TEXT,
    carrier_type        TEXT,
    enter_date          DATETIME,
    management_flag     TEXT,
    assembly_situation  TEXT,
    is_donwload_mailbox TEXT,
    create_time         TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time         TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 密钥配发三联单
DROP TABLE IF EXISTS order_distribution;

CREATE TABLE IF NOT EXISTS order_distribution
(
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    device_name        TEXT,
    manager_model_id   TEXT,
    device_model       TEXT,
    file_type          TEXT,
    product_code       TEXT,
    usage_reserve_area INTEGER,
    dist_type          TEXT,
    create_time        TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time        TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 设备管理-执勤区配置-核心区信息
DROP TABLE IF EXISTS core_area_info;

CREATE TABLE IF NOT EXISTS core_area_info
(
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    unit_info         TEXT,
    manager_entity_id TEXT,
    ip                TEXT,
    unit_tel          TEXT,
    rank              INTEGER DEFAULT 1,
    create_time       TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time       TEXT    DEFAULT CURRENT_TIMESTAMP
);

-- 设备管理-执勤区配置-执勤区信息
DROP TABLE IF EXISTS duty_area_info;

CREATE TABLE IF NOT EXISTS duty_area_info
(
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    duty_area_name       TEXT,
    isolator_external_ip TEXT,
    home_network         TEXT,
    external_server_ip   TEXT,
    isolator_intranet_ip TEXT,
    default_conn         TEXT,
    gateway_id           TEXT,
    create_time          TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time          TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag             INTEGER DEFAULT 0
);

-- 设备管理-执勤区配置-数据同步信息
DROP TABLE IF EXISTS data_sync_info;

CREATE TABLE IF NOT EXISTS data_sync_info
(
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    duty_area_name TEXT,
    home_network   TEXT,
    ip             TEXT,
    result         TEXT,
    type           INTEGER DEFAULT 1,
    create_time    TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time    TEXT    DEFAULT CURRENT_TIMESTAMP
);


-- 导入管理系统盘表
DROP TABLE IF EXISTS manager_sys_disk_data_maintenance;

CREATE TABLE IF NOT EXISTS manager_sys_disk_data_maintenance
(
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    sys_type                  TEXT,
    management_disk_number    TEXT,
    carrier_model             TEXT,
    version                   INTEGER,
    data_length               INTEGER,
    content                   TEXT,
    file_name                 TEXT,
    create_time    TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time    TEXT    DEFAULT CURRENT_TIMESTAMP
);



-- 设备管理-远程维护-下级管理系统信息
DROP TABLE IF EXISTS uphold_sub_system;

CREATE TABLE IF NOT EXISTS uphold_sub_system
(
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    unit_name         TEXT,
    manager_entity_id TEXT,
    ip                TEXT,
    version_info      TEXT,
    select_time       TEXT DEFAULT CURRENT_TIMESTAMP,
    select_status     TEXT,
    create_time       TEXT DEFAULT CURRENT_TIMESTAMP,
    update_time       TEXT DEFAULT CURRENT_TIMESTAMP
);


-- 设备管理-报号维护-管理系统列表
DROP TABLE IF EXISTS uphold_manager_system;

CREATE TABLE IF NOT EXISTS uphold_manager_system
(
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    sys_name          TEXT,
    manager_entity_id TEXT,
    receive_number    TEXT,
    sent_number       TEXT,
    create_time       TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time       TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag          INTEGER DEFAULT 0
);

-- 算法管理_算法信息维护
DROP TABLE IF EXISTS uphold_algorithm_info;

CREATE TABLE IF NOT EXISTS uphold_algorithm_info
(
    id                         INTEGER PRIMARY KEY AUTOINCREMENT,
    algorithm_name             TEXT,
    algorithm_short_name       TEXT,
    algorithm_number           TEXT,
    pwd_type                   TEXT,
    algorithm_usage            TEXT,
    algorithm_style            TEXT,
    algorithm_model_short_name TEXT,
    args_short_name            TEXT,
    info_size                  TEXT,
    work_model                 TEXT,
    pwd_size                   INTEGER,
    use_flag                   INTEGER DEFAULT 0,
    create_time                TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time                TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag                   INTEGER DEFAULT 0
);

-- 算法管理_参数信息维护
DROP TABLE IF EXISTS uphold_args_info;

CREATE TABLE IF NOT EXISTS uphold_args_info
(
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    args_cn_name     TEXT,
    algorithm_id     INTEGER NOT NULL,
    args_short_name  TEXT,
    model_short_name TEXT,
    device_type      TEXT,
    args_type        TEXT,
    args_style       TEXT,
    create_time      TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time      TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag         INTEGER DEFAULT 0
);

-- 算法管理_算法配发
DROP TABLE IF EXISTS algorithm_distribute;

CREATE TABLE IF NOT EXISTS algorithm_distribute
(
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    algorithm_id      INTEGER NOT NULL,
    status            TEXT,
    distribute_result TEXT,
    distribute_type   INTEGER,
    target_system     TEXT,
    create_time       TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time       TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag          INTEGER DEFAULT 0
);

-- 算法管理_算法参数配发
DROP TABLE IF EXISTS algorithm_args_distribute;

CREATE TABLE IF NOT EXISTS algorithm_args_distribute
(
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    args_id           INTEGER NOT NULL,
    distribute_result TEXT,
    distribute_type   INTEGER,
    target_system     TEXT,
    create_time       TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time       TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag          INTEGER DEFAULT 0
);

-- 算法管理_算法配置
DROP TABLE IF EXISTS algorithm_config;

CREATE TABLE IF NOT EXISTS algorithm_config
(
    id                       INTEGER PRIMARY KEY AUTOINCREMENT,
    algorithm_id             INTEGER NOT NULL,
    device_id                INTEGER NOT NULL,
    cooperate_manager_number TEXT,
    create_time              TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time              TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag                 INTEGER DEFAULT 0
);

-- 算法管理_算法参数配置
DROP TABLE IF EXISTS algorithm_args_config;

CREATE TABLE IF NOT EXISTS algorithm_args_config
(
    id                       INTEGER PRIMARY KEY AUTOINCREMENT,
    args_id                  INTEGER NOT NULL,
    device_id                INTEGER NOT NULL,
    cooperate_manager_number TEXT,
    create_time              TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time              TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag                 INTEGER DEFAULT 0
);

-- 设备注册-设备信息
DROP TABLE IF EXISTS device_hard;

CREATE TABLE IF NOT EXISTS device_hard
(
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    sys_type          TEXT,
    level            INTEGER DEFAULT 1,
    unit_name        TEXT,
    device_name      TEXT,
    device_type      TEXT,
    version          TEXT,
    device_number    TEXT,
    device_entity_id TEXT,
    device_mark_id   TEXT,
    disk_number      TEXT,
    home_network     TEXT,
    contacts         TEXT,
    contacts_info    TEXT,
    ip               TEXT,
    telephone        TEXT,
    seat_name        TEXT,
    content          TEXT,
    photo_url        TEXT,
    conn_flag        INTEGER DEFAULT 0,
    cancel_flag      INTEGER DEFAULT 0,
    create_time      TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time      TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag         INTEGER DEFAULT 0
);

-- 设备注册-管理系统注册（上下级共用）
DROP TABLE IF EXISTS manager_system;

CREATE TABLE IF NOT EXISTS manager_system
(
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    sysType              TEXT,
    level                INTEGER DEFAULT 1,
    rank                 INTEGER,
    manager_sys_type     INTEGER DEFAULT 1,
    unit_name            TEXT,
    sys_name             TEXT,
    sys_type             INTEGER DEFAULT 1,
    sys_entity_id        TEXT,
    sys_mark_id          TEXT,
    isolator_external_ip TEXT,
    isolator_intranet_ip TEXT,
    manager_area         TEXT,
    telephone            TEXT,
    pwd_server_ip        TEXT,
    contacts             TEXT,
    contacts_info        TEXT,
    seat_name            TEXT,
    disk_number          TEXT,
    content              TEXT,
    photo_url            TEXT,
    create_time          TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time          TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag             INTEGER DEFAULT 0
);

-- 设备注册-注册信息管理-管理盘信息
DROP TABLE IF EXISTS manager_disk;

CREATE TABLE IF NOT EXISTS manager_disk
(
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    disk_number TEXT,
    status      INTEGER DEFAULT 1,
    create_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag    INTEGER DEFAULT 0
);

-- 设备注册-注册信息管理-管理盘文件
DROP TABLE IF EXISTS manager_disk_file;

CREATE TABLE IF NOT EXISTS manager_disk_file
(
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    disk_id     INTEGER NOT NULL,
    file_name   TEXT,
    file_size   INTEGER DEFAULT 0,
    create_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag    INTEGER DEFAULT 0
);

-- 设备注册-注册信息管理-设备照片维护
DROP TABLE IF EXISTS device_photo;

CREATE TABLE IF NOT EXISTS device_photo
(
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    device_type TEXT,
    photo_url   TEXT,
    create_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag    INTEGER DEFAULT 0
);

-- 应用系统
DROP TABLE IF EXISTS application_system;

CREATE TABLE IF NOT EXISTS application_system
(
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    system_name TEXT UNIQUE NOT NULL,
    create_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag    INTEGER DEFAULT 0
);

-- 设备注册-注册信息管理-应用系统配置
DROP TABLE IF EXISTS device_sys_config;

CREATE TABLE IF NOT EXISTS device_sys_config
(
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id   INTEGER NOT NULL,
    sys_id      INTEGER NOT NULL,
    create_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag    INTEGER DEFAULT 0
);

-- 设备注册-信息汇总-数据收集
DROP TABLE IF EXISTS data_collect;

CREATE TABLE IF NOT EXISTS data_collect
(
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    sys_name    TEXT,
    identify    TEXT,
    ip          TEXT,
    status      TEXT,
    create_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag    INTEGER DEFAULT 0
);

-- 设备数据
DROP TABLE IF EXISTS device_data;

CREATE TABLE IF NOT EXISTS device_data
(
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    unit_name        TEXT,
    device_name      TEXT,
    device_type      TEXT,
    device_entity_id TEXT,
    home_network     TEXT,
    create_time      TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time      TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag         INTEGER DEFAULT 0
);

-- 设备注册-单位位置
DROP TABLE IF EXISTS unit_address;

CREATE TABLE IF NOT EXISTS unit_address
(
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    sys_type    TEXT,
    unit_name   TEXT,
    parent_id   INTEGER,
    device_name TEXT,
    longitude   REAL,
    latitude    REAL,
    create_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT    DEFAULT CURRENT_TIMESTAMP,
    del_flag    INTEGER DEFAULT 0
);



DROP TABLE IF EXISTS remote_file_config;

CREATE TABLE IF NOT EXISTS remote_file_config (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   service_list TEXT,
   net_param TEXT DEFAULT NULL,
   safe_strategy TEXT DEFAULT NULL,
   manager_strategy TEXT DEFAULT NULL,
   vpn_strategy TEXT DEFAULT NULL,
   create_time TEXT    DEFAULT CURRENT_TIMESTAMP,
   update_time TEXT    DEFAULT CURRENT_TIMESTAMP
);