-- WebcatEE-1.2.5
drop table if exists webcat_database_driver;
create table webcat_database_driver(
    id                        integer primary key autoincrement,
    driver_name               varchar(64)   not null,
    database_type             varchar(32)   not null,
    driver_class              varchar(128)  not null,
    version                   varchar(32)   not null,
    enabled                   int(10)       not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_driver_file;
create table webcat_driver_file(
    id                        integer primary key autoincrement,
    driver_id                 integer       not null,
    file_name                 varchar(64)   not null,
    file_path                 varchar(128)  not null,
    file_size                 integer       not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_database;
create table webcat_database(
    id                        integer primary key autoincrement,
    server_id                 integer default 0 not null,
    master_id                 integer default 0 not null,
    database_name             varchar(64)       not null,
    database_type             varchar(32)       not null,
    database_env              varchar(16)       not null,
    node_type                 varchar(16)       not null,
    host                      varchar(64)       not null,
    port                      int(10)           not null,
    driver_id                 integer           not null,
    driver_class              varchar(256)      not null,
    url                       varchar(512)      not null,
    user_name                 varchar(64)       not null,
    password                  varchar(512)      not null,
    database_catalog          varchar(64)       not null,
    database_schema           varchar(64)       not null,
    database_tablespace       varchar(64)       not null,
    table_query_sql           varchar(2048),
    connection_properties     varchar(1024)     not null,
    query_timeout             int(10)           not null,
    query_interval            int(10)           not null,
    allow_query_rows          int(10)           not null,
    readonly                  int(10)           not null,
    enable                    int(10)           not null,
    version                   int(10)           not null,
    create_time               datetime          not null,
    update_time               datetime          not null,
    unique (database_name)
);

drop table if exists webcat_database_owner;
create table webcat_database_owner(
    id                        integer primary key autoincrement,
    owner_id                  integer      not null,
    owner_name                varchar(64)  not null,
    database_id               integer      not null,
    database_name             varchar(128) not null,
    role_name                 varchar(32)  not null,
    full_table                int(10)      not null,
    expire_days               int(10)      not null,
    expire_time               datetime     not null,
    display_order             int(10)      not null,
    create_time               datetime     not null,
    update_time               datetime     not null,
    unique (owner_id, database_id)
);

drop table if exists webcat_user_table;
create table webcat_user_table(
    id                        integer primary key autoincrement,
    user_id                   integer      not null,
    user_name                 varchar(64)  not null,
    database_id               integer      not null,
    database_name             varchar(128) not null,
    table_name                varchar(32)  not null,
    expire_days               int(10)      not null,
    expire_time               datetime     not null,
    create_time               datetime     not null,
    update_time               datetime     not null,
    unique (user_id, database_id, table_name)
);

drop table if exists webcat_sensitive_field;
create table webcat_sensitive_field(
    id                        integer primary key autoincrement,
    database_id               integer      not null,
    database_name             varchar(128) not null,
    table_name                varchar(64)  not null,
    column_name               varchar(64)  not null,
    display_pattern           varchar(64)  not null,
    properties                varchar(512) not null,
    create_time               datetime     not null,
    update_time               datetime     not null,
    unique (database_id, table_name, column_name)
);

drop table if exists webcat_query_log;
create table webcat_query_log(
    id                        integer primary key autoincrement,
    user_id                   integer       not null,
    user_name                 varchar(64)   not null,
    database_id               integer       not null,
    database_name             varchar(128)  not null,
    database_type             varchar(32)   not null,
    database_env              varchar(128)  not null,
    node_name                 varchar(64)   not null,
    thread_name               varchar(64)   not null,
    remote_ip                 varchar(64)   not null,
    query_token               varchar(64)   not null,
    query_md5                 varchar(64)   not null,
    query_summary             varchar(1024) not null,
    query_sql                 mediumtext    not null,
    query_status              int(10)       not null,
    query_result              text                  ,
    affected_rows             int(10)       not null,
    elapsed_time              integer       not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);
create index idx_webcat_query_log_user_id on webcat_query_log(user_id);
create index idx_webcat_query_log_database_id on webcat_query_log(database_id);
create index idx_webcat_query_log_token on webcat_query_log(query_token);
create index idx_webcat_query_log_md5 on webcat_query_log(query_md5);

drop table if exists webcat_attachment;
create table webcat_attachment(
    id                        integer primary key autoincrement,
    user_id                   integer       not null,
    file_uuid                 varchar(64)   not null,
    file_name                 varchar(128)  not null,
    file_path                 varchar(128)  not null,
    file_size                 integer       not null,
    mime_type                 varchar(32)   not null,
    last_modified             integer       not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_data_file;
create table webcat_data_file(
    id                        integer primary key autoincrement,
    user_id                   integer       not null,
    order_id                  integer       not null,
    database_id               integer       not null,
    file_uuid                 varchar(64)   not null,
    file_name                 varchar(128)  not null,
    file_path                 varchar(128)  not null,
    file_size                 integer       not null,
    mime_type                 varchar(32)   not null,
    last_modified             integer       not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_data_text;
create table webcat_data_text(
    id                        integer primary key autoincrement,
    user_id                   integer       not null,
    order_id                  integer       not null,
    database_id               integer       not null,
    content_length            integer       not null,
    mime_type                 varchar(64)   not null,
    content                   text          not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_data_check;
create table webcat_data_check(
    id                        integer primary key autoincrement,
    user_id                   integer       not null,
    order_id                  integer       not null,
    database_id               integer       not null,
    data_id                   integer       not null,
    data_type                 varchar(32)   not null,
    mime_type                 varchar(64)   not null,
    order_name                varchar(128)  not null,
    order_type                int(10)       not null,
    check_token               varchar(64)   not null,
    check_level               varchar(16)   not null,
    status                    int(10)       not null,
    result                    text          not null,
    create_time               datetime      not null,
    update_time               datetime      not null,
    unique (data_id, data_type)
);
create index idx_webcat_sql_check_user_id on webcat_data_check(user_id);
create index idx_webcat_sql_check_order_id on webcat_data_check(order_id);
create index idx_webcat_sql_check_db_id on webcat_data_check(database_id);

drop table if exists webcat_order_execute;
create table webcat_order_execute(
    id                integer primary key autoincrement,
    user_id           integer       not null,
    order_id          integer       not null,
    database_id       integer       not null,
    order_name        varchar(128)  not null,
    order_type        int(10)       not null,
    data_id           integer       not null,
    data_type         varchar(32)   not null,
    schedule_type     int(10)       not null,
    schedule_time     datetime      not null,
    execute_token     varchar(64)   not null,
    log_path          varchar(128)  not null,
    status            int(10)       not null,
    result            text          not null,
    create_time       datetime      not null,
    update_time       datetime      not null
);
create index idx_webcat_order_execute_user_id on webcat_order_execute(user_id);
create index idx_webcat_order_execute_order_id on webcat_order_execute(order_id);
create index idx_webcat_order_execute_db_id on webcat_order_execute(database_id);

drop table if exists webcat_work_order;
create table webcat_work_order(
    id                integer primary key autoincrement,
    parent_id         integer                    not null,
    order_name        varchar(128)               not null,
    order_type        int(10)                    not null,
    user_id           integer                    not null,
    user_name         varchar(64)                not null,
    database_id       integer                    not null,
    database_name     varchar(64)                not null,
    database_env      varchar(32)                not null,
    order_data        text                       not null,
    audit_id          integer                    not null,
    audit_user_id     integer                    not null,
    audit_user_name   varchar(64)                not null,
    remarks           varchar(256)               not null,
    opinion           varchar(128)               not null,
    execute_status    int(10)                    not null,
    status            int(10)                    not null,
    create_time       datetime                   not null,
    update_time       datetime                   not null
);
create index idx_webcat_work_order_user_id on webcat_work_order (user_id);

drop table if exists webcat_database_order;
create table webcat_database_order(
    id                integer       not null,
    parent_id         integer       not null,
    order_name        varchar(128)  not null,
    user_id           integer       not null,
    user_name         varchar(64)   not null,
    database_id       integer       not null,
    database_name     varchar(64)   not null,
    database_env      varchar(32)   not null,
    user_role         varchar(32)   not null,
    expire_days       int(10)       not null,
    audit_id          integer       not null,
    audit_user_id     integer       not null,
    audit_user_name   varchar(64)   not null,
    remarks           varchar(256)  not null,
    opinion           varchar(128)  not null,
    execute_status    int(10)       not null,
    status            int(10)       not null,
    create_time       datetime      not null,
    update_time       datetime      not null
);

drop table if exists webcat_table_order;
create table webcat_table_order(
    id                integer       not null,
    parent_id         integer       not null,
    order_name        varchar(128)  not null,
    user_id           integer       not null,
    user_name         varchar(64)   not null,
    database_id       integer       not null,
    database_name     varchar(64)   not null,
    database_env      varchar(32)   not null,
    tables            text          not null,
    expire_days       int(10)       not null,
    audit_id          integer       not null,
    audit_user_id     integer       not null,
    audit_user_name   varchar(64)   not null,
    remarks           varchar(256)  not null,
    opinion           varchar(128)  not null,
    execute_status    int(10)       not null,
    status            int(10)       not null,
    create_time       datetime      not null,
    update_time       datetime      not null
);

drop table if exists webcat_base_change_order;
create table webcat_base_change_order(
    id                    integer       not null,
    parent_id             integer       not null,
    order_name            varchar(128)  not null,
    user_id               integer       not null,
    user_name             varchar(64)   not null,
    database_id           integer       not null,
    database_name         varchar(64)   not null,
    database_env          varchar(32)   not null,
    data_id               integer       not null,
    data_type             varchar(32)   not null,
    affected_rows         int(10)       not null,
    schedule_type         int(10)       not null,
    schedule_time         datetime      not null,
    audit_id              integer       not null,
    audit_user_id         integer       not null,
    audit_user_name       varchar(64)   not null,
    execute_id            bigint(10)    not null,
    execute_status        int(10)       not null,
    remarks               varchar(256)  not null,
    opinion               varchar(128)  not null,
    status                int(10)       not null,
    create_time           datetime      not null,
    update_time           datetime      not null
);

drop table if exists webcat_data_change_order;
create table webcat_data_change_order(
    id                    integer       not null,
    parent_id             integer       not null,
    order_name            varchar(128)  not null,
    user_id               integer       not null,
    user_name             varchar(64)   not null,
    database_id           integer       not null,
    database_name         varchar(64)   not null,
    database_env          varchar(32)   not null,
    data_id               integer       not null,
    data_type             varchar(32)   not null,
    mime_type             varchar(64)   not null,
    rollback_data_id      integer       not null,
    rollback_data_type    varchar(32)   not null,
    batch_insert          int(10)       not null,
    change_options        varchar(1024) not null,
    affected_rows         int(10)       not null,
    schedule_type         int(10)       not null,
    schedule_time         datetime      not null,
    audit_id              integer       not null,
    audit_user_id         integer       not null,
    audit_user_name       varchar(64)   not null,
    execute_id            bigint(10)    not null,
    execute_status        int(10)       not null,
    remarks               varchar(256)  not null,
    opinion               varchar(128)  not null,
    status                int(10)       not null,
    create_time           datetime      not null,
    update_time           datetime      not null
);

drop table if exists webcat_data_export_order;
create table webcat_data_export_order(
    id                    integer       not null,
    parent_id             integer       not null,
    order_name            varchar(128)  not null,
    user_id               integer       not null,
    user_name             varchar(64)   not null,
    database_id           integer       not null,
    database_name         varchar(64)   not null,
    database_env          varchar(32)   not null,
    data_id               integer       not null,
    data_type             varchar(32)   not null,
    affected_rows         int(10)       not null,
    schedule_type         int(10)       not null,
    schedule_time         datetime      not null,
    audit_id              integer       not null,
    audit_user_id         integer       not null,
    audit_user_name       varchar(64)   not null,
    execute_id            bigint(10)    not null,
    execute_status        int(10)       not null,
    export_path           varchar(128)  not null,
    remarks               varchar(256)  not null,
    opinion               varchar(128)  not null,
    status                int(10)       not null,
    create_time           datetime      not null,
    update_time           datetime      not null
);

drop table if exists webcat_pending_work;
create table webcat_pending_work(
    id                integer primary key autoincrement,
    user_id           integer                    not null,
    database_id       integer                    not null,
    database_name     varchar(64)                not null,
    database_env      varchar(32)                not null,
    order_id          integer                    not null,
    order_name        varchar(128)               not null,
    order_type        int(10)                    not null,
    apply_user_id     integer                    not null,
    apply_user_name   varchar(64)                not null,
    opinion           varchar(128)               not null,
    execute_status    int(10)                    not null,
    status            int(10)                    not null,
    create_time       datetime                   not null,
    update_time       datetime                   not null,
    unique (user_id, order_id)
);
create index idx_webcat_pending_work_user_id on webcat_pending_work (user_id);

drop table if exists webcat_order_log;
create table webcat_order_log(
    id                             integer primary key autoincrement,
    order_id                       bigint(20)           not null,
    operator_id                    bigint(20)           not null,
    operator_name                  varchar(64)          not null,
    operation_type                 varchar(32)          not null,
    data_object                    varchar(2048)        not null,
    create_time                    datetime             not null,
    update_time                    datetime             not null
);
create index idx_webcat_order_log_order_id on webcat_order_log (order_id);

drop table if exists webcat_order_job;
create table webcat_order_job(
    id                             integer primary key autoincrement,
    database_id                    integer              not null,
    order_id                       integer              not null,
    order_name                     varchar(128)         not null,
    order_type                     int(10)              not null,
    user_id                        bigint(20)           not null,
    user_name                      varchar(64)          not null,
    action_type                    int(10)              not null,
    properties                     varchar(255)         not null,
    status                         int(10)              not null,
    fire_time                      integer              not null,
    create_time                    datetime             not null,
    update_time                    datetime             not null,
    unique (order_id, action_type)
);

drop table if exists webcat_order_job_history;
create table webcat_order_job_history(
    id                             integer primary key autoincrement,
    database_id                    integer              not null,
    order_id                       integer              not null,
    order_name                     varchar(128)         not null,
    order_type                     int(10)              not null,
    user_id                        bigint(20)           not null,
    user_name                      varchar(64)          not null,
    action_type                    int(10)              not null,
    properties                     varchar(255)         not null,
    status                         int(10)              not null,
    fire_time                      integer              not null,
    create_time                    datetime             not null,
    update_time                    datetime             not null,
    unique (order_id, action_type)
);

drop table if exists webcat_execute_progress;
create table webcat_execute_progress(
    id                             integer primary key autoincrement,
    user_id                        integer              not null,
    database_id                    integer              not null,
    token                          varchar(64)          not null,
    status                         int(10)              not null,
    message                        varchar(256)         not null,
    create_time                    datetime             not null,
    update_time                    datetime             not null
);

drop table if exists webcat_user_notify;
create table webcat_user_notify(
    id                   integer primary key autoincrement,
    user_id              integer        not null,
    biz_id               integer        not null,
    biz_type             int(10)        not null,
    content              text           not null,
    readed               tinyint(1)     not null,
    deleted              tinyint(1)     not null,
    create_time          datetime       not null,
    update_time          datetime       not null
);
create index idx_webcat_user_notify_user_id on webcat_user_notify(user_id);

drop table if exists webcat_profile;
create table webcat_profile(
    id               integer primary key autoincrement,
    name             varchar(64)                not null,
    content          text                       not null,
    create_time      datetime                   not null,
    update_time      datetime                   not null,
    unique (name)
);

drop table if exists webcat_mail_template;
create table webcat_mail_template(
    id               integer primary key autoincrement,
    name             varchar(64)                not null,
    content          text                       not null,
    create_time      datetime                   not null,
    update_time      datetime                   not null,
    unique (name)
);

drop table if exists webcat_monitor;
create table webcat_monitor(
    id                        integer primary key autoincrement,
    monitor_name              varchar(64)                not null,
    monitor_type              varchar(64)                not null,
    monitor_config            text                       not null,
    agent_name                varchar(64)                not null,
    agent_url                 varchar(256)               not null,
    shard_name                varchar(64)                not null,
    table_name                varchar(64)                not null,
    enabled                   tinyint(3)                 not null,
    status                    tinyint(3)                 not null,
    create_time               datetime                   not null,
    update_time               datetime                   not null
);
create index idx_webcat_monitor_monitor_name on webcat_monitor (monitor_name);
create index idx_webcat_monitor_agent_name on webcat_monitor (agent_name);

drop table if exists webcat_mysql_status;
create table webcat_mysql_status(
    id                                            integer primary key autoincrement,
    server_id                                     integer       default '0' not null,
    host                                          varchar(64)   default ''  not null,
    port                                          int(10)       default 0   not null,
    tags                                          varchar(64)   default ''  not null,
    uptime                                        int(10)       default 0   not null,
    aborted_clients                               int(10)       default 0   not null,
    aborted_connects                              int(10)       default 0   not null,
    max_connections                               int(10)       default 0   not null,
    max_connect_errors                            int(10)       default 0   not null,
    open_files_limit                              int(10)       default 0   not null,
    open_files                                    int(10)       default 0   not null,
    table_open_cache                              int(10)       default 0   not null,
    open_tables                                   int(10)       default 0   not null,
    max_tmp_tables                                int(10)       default 0   not null,
    max_heap_table_size                           int(10)       default 0   not null,
    max_allowed_packet                            int(10)       default 0   not null,
    threads_connected                             int(10)       default 0   not null,
    threads_running                               int(10)       default 0   not null,
    threads_waits                                 int(10)       default 0   not null,
    threads_created                               int(10)       default 0   not null,
    threads_cached                                int(10)       default 0   not null,
    connections                                   int(10)       default 0   not null,
    connections_per_second                        int(10)       default 0   not null,
    bytes_received_per_second                     integer       default '0' not null,
    bytes_sent_per_second                         integer       default '0' not null,
    com_select_per_second                         int(10)       default 0   not null,
    com_insert_per_second                         int(10)       default 0   not null,
    com_update_per_second                         int(10)       default 0   not null,
    com_delete_per_second                         int(10)       default 0   not null,
    com_commit_per_second                         int(10)       default 0   not null,
    com_rollback_per_second                       int(10)       default 0   not null,
    questions_per_second                          int(10)       default 0   not null,
    queries_per_second                            int(10)       default 0   not null,
    transaction_per_second                        int(10)       default 0   not null,
    created_tmp_tables_per_second                 int(10)       default 0   not null,
    created_tmp_disk_tables_per_second            int(10)       default 0   not null,
    created_tmp_files_per_second                  int(10)       default 0   not null,
    table_locks_immediate_per_second              int(10)       default 0   not null,
    table_locks_waited_per_second                 int(10)       default 0   not null,
    key_buffer_size                               integer       default '0' not null,
    sort_buffer_size                              int(10)       default 0   not null,
    join_buffer_size                              int(10)       default 0   not null,
    key_blocks_used                               int(10)       default 0   not null,
    key_blocks_unused                             int(10)       default 0   not null,
    key_blocks_not_flushed                        int(10)       default 0   not null,
    key_read_requests_per_second                  int(10)       default 0   not null,
    key_reads_per_second                          int(10)       default 0   not null,
    key_write_requests_per_second                 int(10)       default 0   not null,
    key_writes_per_second                         int(10)       default 0   not null,
    innodb_buffer_pool_instances                  int(10)       default 0   not null,
    innodb_buffer_pool_size                       integer       default 0   not null,
    innodb_doublewrite                            char(10)      default '0' not null,
    innodb_file_per_table                         char(10)      default '0' not null,
    innodb_flush_log_at_trx_commit                tinyint(2)    default 0   not null,
    innodb_flush_method                           varchar(30)   default '0' not null,
    innodb_force_recovery                         tinyint(2)    default 0   not null,
    innodb_io_capacity                            int(10)       default 0   not null,
    innodb_read_io_threads                        tinyint(2)    default 0   not null,
    innodb_write_io_threads                       tinyint(2)    default 0   not null,
    innodb_buffer_pool_pages_total                int(10)       default 0   not null,
    innodb_buffer_pool_pages_data                 int(10)       default 0   not null,
    innodb_buffer_pool_pages_dirty                int(10)       default 0   not null,
    innodb_buffer_pool_pages_flushed              integer       default 0   not null,
    innodb_buffer_pool_pages_free                 int(10)       default 0   not null,
    innodb_buffer_pool_pages_misc                 int(10)       default 0   not null,
    innodb_page_size                              int(10)       default 0   not null,
    innodb_pages_created                          integer       default 0   not null,
    innodb_pages_read                             integer       default 0   not null,
    innodb_pages_written                          integer       default 0   not null,
    innodb_row_lock_current_waits                 int(10)       default 0   not null,
    innodb_buffer_pool_pages_flushed_per_second   int(10)       default 0   not null,
    innodb_buffer_pool_read_requests_per_second   int(10)       default 0   not null,
    innodb_buffer_pool_reads_per_second           int(10)       default 0   not null,
    innodb_buffer_pool_write_requests_per_second  int(10)       default 0   not null,
    innodb_rows_read_per_second                   int(10)       default 0   not null,
    innodb_rows_inserted_per_second               int(10)       default 0   not null,
    innodb_rows_updated_per_second                int(10)       default 0   not null,
    innodb_rows_deleted_per_second                int(10)       default 0   not null,
    innodb_version                                varchar(30)   default ''  not null,
    query_cache_hit                               int(10)       default 0   not null,
    thread_cache_hit                              int(10)       default 0   not null,
    key_buffer_read_rate                          int(10)       default 0   not null,
    key_buffer_write_rate                         int(10)       default 0   not null,
    key_blocks_used_rate                          int(10)       default 0   not null,
    created_tmp_disk_tables_rate                  int(10)       default 0   not null,
    connections_usage                             int(10)       default 0   not null,
    open_files_usage                              int(10)       default 0   not null,
    open_tables_usage                             int(10)       default 0   not null,
    version                                       varchar(32)   default ''  not null,
    create_time                                   integer       default 0   not null
);
create index idx_webcat_mysql_status_connections on webcat_mysql_status (threads_connected);
create index idx_webcat_mysql_status_active on webcat_mysql_status (threads_running);
create index idx_webcat_mysql_status_server_id on webcat_mysql_status (server_id);
create index idx_webcat_mysql_status_create_time on webcat_mysql_status (create_time);

drop table if exists webcat_user_query;
create table webcat_user_query(
    id                        integer primary key autoincrement,
    user_id                   integer       not null,
    user_name                 varchar(64)   not null,
    database_id               integer       not null,
    database_name             varchar(128)  not null,
    database_type             varchar(32)   not null,
    database_env              varchar(128)  not null,
    query_name                varchar(64)   not null,
    query_md5                 varchar(64)   not null,
    query_summary             varchar(1024) not null,
    query_sql                 mediumtext    not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);
create index idx_webcat_user_query_user on webcat_user_query(user_id);
create index idx_webcat_user_query_database on webcat_user_query (database_id);
create index idx_webcat_user_query_md5 on webcat_user_query (query_md5);

drop table if exists webcat_common_group;
create table webcat_common_group(
    id                        integer primary key autoincrement,
    group_name                varchar(64)   not null,
    database_type             varchar(32)   not null,
    sys_enabled               tinyint(3)    not null,
    dba_enabled               tinyint(3)    not null,
    app_enabled               tinyint(3)    not null,
    dev_enabled               tinyint(3)    not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);
create index idx_webcat_common_group_db_type on webcat_common_group (database_type);

drop table if exists webcat_common_sql;
create table webcat_common_sql(
    id                        integer primary key autoincrement,
    group_id                  integer       not null,
    group_name                varchar(64)   not null,
    query_name                varchar(64)   not null,
    database_type             varchar(32)   not null,
    query_summary             varchar(1024) not null,
    query_sql                 mediumtext    not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);
create index idx_webcat_common_sql_db_type on webcat_common_sql (database_type);

drop table if exists webcat_patch;
create table webcat_patch(
    id                        integer primary key autoincrement,
    patch_code                varchar(64)   not null,
    patch_name                varchar(64)   not null,
    description               varchar(1024) not null,
    version                   varchar(32)   not null,
    create_time               datetime      not null,
    update_time               datetime      not null,
    unique (patch_code)
);
create index idx_webcat_patch_name on webcat_patch (patch_code);

drop table if exists webcat_system_log;
create table webcat_system_log(
    id                             integer primary key autoincrement,
    local_ip                       varchar(64)         not null,
    thread_name                    varchar(64)         not null,
    level                          varchar(12)         not null,
    message                        varchar(1024)       not null,
    create_time                    datetime            not null,
    update_time                    datetime            not null
);

drop table if exists webcat_user_ip;
create table webcat_user_ip(
    id                             integer primary key autoincrement,
    user_id                        integer             not null,
    user_name                      varchar(64)         not null,
    nick_name                      varchar(64)         not null,
    pattern                        varchar(64)         not null,
    ip_mode                        tinyint(4)          not null,
    remarks                        varchar(256)        not null,
    create_time                    datetime            not null,
    update_time                    datetime            not null
);

drop table if exists webcat_biz_query;
create table webcat_biz_query(
    id                        integer primary key autoincrement,
    user_id                   integer       not null,
    user_name                 varchar(64)   not null,
    database_id               integer       not null,
    database_name             varchar(128)  not null,
    database_type             varchar(32)   not null,
    database_env              varchar(128)  not null,
    query_name                varchar(64)   not null,
    query_md5                 varchar(64)   not null,
    query_summary             varchar(1024) not null,
    query_sql                 mediumtext    not null,
    visibility                tinyint(4)    not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);
create index idx_webcat_biz_query_database on webcat_biz_query (database_id);
create index idx_webcat_biz_query_md5 on webcat_biz_query (query_md5);

-- @since 1.2.0
drop table if exists webcat_app_server;
create table webcat_app_server(
    id                        integer primary key autoincrement,
    app_name                  varchar(64)   not null,
    node_name                 varchar(64)   not null,
    host                      varchar(64)   not null,
    port                      integer       not null,
    url                       varchar(128)  not null,
    token                     varchar(64)   not null,
    start_time                datetime      not null,
    heartbeat                 datetime      not null,
    register_time             datetime      not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_app_server_log;
create table webcat_app_server_log(
    id                        integer primary key autoincrement,
    app_name                  varchar(64)   not null,
    node_name                 varchar(64)   not null,
    token                     varchar(64)   not null,
    start_time                datetime      not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_app_secret;
create table webcat_app_secret(
    id                        integer primary key autoincrement,
    app_name                  varchar(64)   not null,
    public_key                varchar(2048) not null,
    private_key               varchar(2048) not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_table_meta_data;
create table webcat_table_meta_data(
    id                        integer primary key autoincrement,
    database_id               bigint(20)    not null,
    database_name             varchar(64)   not null,
    database_type             varchar(32)   not null,
    database_env              varchar(16)   not null,
    meta_data_schema          varchar(16)   not null,
    meta_data_value           varchar(64)   not null,
    table_catalog             varchar(64)       null,
    table_schema              varchar(64)       null,
    table_domain              varchar(64)   not null,
    table_type                varchar(16)   not null,
    table_name                varchar(64)   not null,
    column_name               varchar(64)   not null,
    data_type                 int(10)       not null,
    type_name                 varchar(64)   not null,
    column_size               int(10)           null,
    buffer_length             int(10)           null,
    decimal_digits            int(10)           null,
    num_prec_radix            int(10)           null,
    nullable                  int(10)           null,
    remarks                   varchar(256)      null,
    column_def                varchar(64)       null,
    sql_data_type             int(10)           null,
    sql_datetime_sub          int(10)           null,
    char_octet_length         int(10)           null,
    ordinal_position          int(10)           null,
    is_nullable               varchar(16)       null,
    scope_catalog             varchar(64)       null,
    scope_schema              varchar(64)       null,
    scope_table               varchar(64)       null,
    source_data_type          int(10)           null,
    is_autoincrement          varchar(16)       null,
    is_generatedcolumn        varchar(16)       null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_database_group;
create table webcat_database_group(
    id                        integer primary key autoincrement,
    parent_id                 bigint(20)    not null,
    user_id                   bigint(20)    not null,
    group_name                varchar(64)   not null,
    display_order             integer       not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_user_group_database;
create table webcat_user_group_database(
    id                        integer primary key autoincrement,
    user_id                   bigint(20)    not null,
    group_id                  bigint(20)    not null,
    database_id               bigint(20)    not null,
    display_order             int(10)       not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_meta_data_build_task;
create table webcat_meta_data_build_task(
    id                        integer primary key autoincrement,
    user_id                   bigint(20)    not null,
    user_name                 varchar(64)   not null,
    database_id               bigint(20)    not null,
    database_name             varchar(64)   not null,
    database_type             varchar(32)   not null,
    database_env              varchar(16)   not null,
    node_name                 varchar(64)       null,
    build_time                datetime          null,
    elapsed_time              bigint(20)        null,
    status                    int(10)       not null,
    message                   text          not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_meta_data_build_log;
create table webcat_meta_data_build_log(
    id                        integer primary key autoincrement,
    task_id                   bigint(20)    not null,
    table_name                varchar(64)   not null,
    content                   text          not null,
    build_time                datetime      not null,
    create_time               datetime      not null,
    update_time               datetime      not null
);

drop table if exists webcat_job_lock;
create table webcat_job_lock(
    lock_name varchar(64) primary key
);
insert into webcat_job_lock(lock_name) values ('meta_data_build_lock');

drop table if exists webcat_ssh_tunnel;
create table webcat_ssh_tunnel(
    id                        integer primary key autoincrement,
    proxy_host                varchar(64)             not null,
    proxy_port                int(10)                 not null,
    remote_host               varchar(64)             not null,
    remote_port               int(10)                 not null,
    local_port                int(10)                 not null,
    auth_method               varchar(16)             not null,
    user_name                 varchar(64)             not null,
    password                  varchar(64)             not null,
    private_key               varchar(2048)           not null,
    passphrase                varchar(64)             not null,
    remarks                   varchar(256)            not null,
    enable                    int(10)                 not null,
    status                    int(10)                 not null,
    create_time               datetime                not null,
    update_time               datetime                not null
);

drop table if exists webcat_work_order_notify_log;
create table webcat_work_order_notify_log(
    id                        integer primary key autoincrement,
    order_id                  bigint(20)              not null,
    auditor_id                bigint(20)              not null,
    local_host                varchar(64)             not null,
    thread_name               varchar(64)             not null,
    channel_name              varchar(64)             not null,
    code                      int(10)                 not null,
    message                   varchar(128),
    result                    text,
    create_time               datetime                not null,
    update_time               datetime                not null
);

drop table if exists webcat_user_profile;
create table webcat_user_profile(
    id               integer primary key autoincrement,
    user_id          bigint(20)                 not null,
    name             varchar(128)               not null,
    content          text                       not null,
    create_time      datetime                   not null,
    update_time      datetime                   not null
);
