-- 本级设备及上级设备基本信息表
DROP TABLE IF EXISTS certificate;

-- 本级设备及上级设备基本信息表
CREATE TABLE IF NOT EXISTS certificate
(
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    system_number     TEXT,
    device_name           TEXT,
    management_level            TEXT,
    device_number          TEXT,
    entity_identifier              TEXT,
    level           TEXT,
    create_time       TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time       TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 密码设备信息表
DROP TABLE IF EXISTS password_device_info;

-- 密码设备信息表
CREATE TABLE IF NOT EXISTS password_device_info
(
    id                         INTEGER PRIMARY KEY AUTOINCREMENT,
    sys_type                    TEXT,
    belong_crypto_system        TEXT,
    equipment_type             TEXT,
    password_entity_identifier      TEXT,
    password_device_serial_number  TEXT,
    password_device_number  TEXT,
    superior_equipment_number TEXT,
    equipment_user_unit       TEXT,
    equipment_deployment_location    TEXT,
    equipment_management_unit        TEXT,
    communication_address_type      TEXT,
    isolator_external_network_ip     TEXT,
    equipment_manufacturer         TEXT,
    device_aliases              TEXT,
    memo                    TEXT,
    registration_date       TEXT,
    create_time TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 离线管理导入文件
DROP TABLE  IF EXISTS offline_management;

-- 离线管理导入文件
CREATE TABLE IF NOT EXISTS offline_management
(
    id                INTEGER PRIMARY KEY,
    sys_type          TEXT,
    operating_mode    TEXT,
    filename          TEXT,
    content           TEXT
);






-- 预制密钥体表
DROP TABLE IF EXISTS receive_prefabricated_key_body;

-- 预制密钥体表
CREATE TABLE IF NOT EXISTS receive_prefabricated_key_body
(
    id          INTEGER PRIMARY KEY,
    sys_type                  TEXT,
    filename                 TEXT,
    prefabricated_key_code    TEXT,
    key_record_format         TEXT,
    key_number                TEXT,
    key_leader                TEXT,
    number_key_groups         TEXT,
    key_file_name             TEXT,
    key_length_per            TEXT,
    number_prefabricated_key_groups  TEXT,
    content      TEXT,
    create_time TEXT          DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT          DEFAULT CURRENT_TIMESTAMP
);


-- 秘钥装配表
DROP TABLE IF EXISTS key_assembly;

-- 秘钥装配表
CREATE TABLE IF NOT EXISTS key_assembly
    (
    id          INTEGER PRIMARY KEY,
    password_device_number     TEXT,
    device_type_name           TEXT,
    password_entity_identifier   TEXT,
    crypto_system               TEXT,
    superior_equipment_number   TEXT,
    equipment_user_unit         TEXT,
    create_time TEXT          DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT          DEFAULT CURRENT_TIMESTAMP
    );



-- 063设备 导入
DROP TABLE IF EXISTS device;

-- 063设备 导入
CREATE TABLE IF NOT EXISTS device
(
    id          INTEGER PRIMARY KEY,
    device_entity_identifier     TEXT,
    device_num                   TEXT,
    device_name                  TEXT,
    work_status                  TEXT,
    unit                         TEXT,
    log                          TEXT,
    create_time TEXT          DEFAULT CURRENT_TIMESTAMP,
    update_time TEXT          DEFAULT CURRENT_TIMESTAMP
);