-- Novalon管理系统数据库初始化脚本
-- 版本: V1
-- 描述: 创建所有核心表结构
-- 用户表
CREATE TABLE IF NOT EXISTS sys_user (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    nickname VARCHAR(100),
    role_id BIGINT,
    status INTEGER DEFAULT 1,
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 角色表
CREATE TABLE IF NOT EXISTS sys_role (
    id BIGSERIAL PRIMARY KEY,
    role_name VARCHAR(100) NOT NULL,
    role_key VARCHAR(100) NOT NULL UNIQUE,
    role_sort INTEGER DEFAULT 0,
    status INTEGER DEFAULT 1,
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 菜单表（统一使用sys_menu表名）
CREATE TABLE IF NOT EXISTS sys_menu (
    id BIGSERIAL PRIMARY KEY,
    menu_name VARCHAR(50) NOT NULL,
    parent_id BIGINT DEFAULT 0,
    order_num INTEGER DEFAULT 0,
    menu_type VARCHAR(1) DEFAULT 'C',
    perms VARCHAR(100),
    component VARCHAR(200),
    status INTEGER DEFAULT 1,
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 字典类型表
CREATE TABLE IF NOT EXISTS sys_dict_type (
    id BIGSERIAL PRIMARY KEY,
    dict_name VARCHAR(100) NOT NULL,
    dict_type VARCHAR(100) NOT NULL UNIQUE,
    status VARCHAR(1) DEFAULT '0',
    remark VARCHAR(500),
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 字典数据表
CREATE TABLE IF NOT EXISTS sys_dict_data (
    id BIGSERIAL PRIMARY KEY,
    dict_sort INTEGER DEFAULT 0,
    dict_label VARCHAR(100) NOT NULL,
    dict_value VARCHAR(100) NOT NULL,
    dict_type VARCHAR(100) NOT NULL,
    css_class VARCHAR(100),
    list_class VARCHAR(100),
    is_default VARCHAR(1) DEFAULT 'N',
    status VARCHAR(1) DEFAULT '0',
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 字典表（通用字典）
CREATE TABLE IF NOT EXISTS sys_dictionary (
    id BIGSERIAL PRIMARY KEY,
    type VARCHAR(100) NOT NULL,
    code VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    value VARCHAR(500),
    remark VARCHAR(500),
    sort INTEGER DEFAULT 0,
    create_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 系统配置表
CREATE TABLE IF NOT EXISTS sys_config (
    id BIGSERIAL PRIMARY KEY,
    config_name VARCHAR(100) NOT NULL,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value VARCHAR(500) NOT NULL,
    config_type VARCHAR(1) DEFAULT 'N',
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 登录日志表
CREATE TABLE IF NOT EXISTS sys_login_log (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50),
    ip VARCHAR(50),
    location VARCHAR(255),
    browser VARCHAR(50),
    os VARCHAR(50),
    status VARCHAR(1),
    message VARCHAR(255),
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 异常日志表
CREATE TABLE IF NOT EXISTS sys_exception_log (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50),
    title VARCHAR(100),
    exception_name VARCHAR(100),
    method_name VARCHAR(255),
    method_params TEXT,
    exception_msg TEXT,
    exception_stack TEXT,
    ip VARCHAR(50),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 操作日志表
CREATE TABLE IF NOT EXISTS operation_log (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50),
    operation VARCHAR(100),
    method VARCHAR(200),
    params TEXT,
    result TEXT,
    ip VARCHAR(50),
    duration BIGINT,
    status VARCHAR(1) DEFAULT '0',
    error_msg TEXT,
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 系统公告表
CREATE TABLE IF NOT EXISTS sys_notice (
    id BIGSERIAL PRIMARY KEY,
    notice_title VARCHAR(50) NOT NULL,
    notice_type VARCHAR(1) NOT NULL,
    notice_content TEXT,
    status VARCHAR(1) DEFAULT '0',
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 用户消息表
CREATE TABLE IF NOT EXISTS sys_user_message (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    notice_id BIGINT,
    message_title VARCHAR(255),
    message_content TEXT,
    is_read VARCHAR(1) DEFAULT '0',
    read_time TIMESTAMP,
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 文件管理表
CREATE TABLE IF NOT EXISTS sys_file (
    id BIGSERIAL PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    file_type VARCHAR(100),
    file_extension VARCHAR(10),
    storage_type VARCHAR(50),
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- OAuth2客户端表
CREATE TABLE IF NOT EXISTS oauth2_client (
    id BIGSERIAL PRIMARY KEY,
    client_id VARCHAR(100) NOT NULL UNIQUE,
    client_secret VARCHAR(255) NOT NULL,
    client_name VARCHAR(100),
    web_server_redirect_uri VARCHAR(500),
    scope VARCHAR(500),
    authorized_grant_types VARCHAR(500),
    access_token_validity_seconds INTEGER,
    refresh_token_validity_seconds INTEGER,
    auto_approve VARCHAR(1) DEFAULT 'false',
    enabled VARCHAR(1) DEFAULT 'true',
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);
-- 表注释
COMMENT ON TABLE sys_exception_log IS '异常日志表';
COMMENT ON COLUMN sys_exception_log.id IS '主键ID';
COMMENT ON COLUMN sys_exception_log.username IS '操作用户';
COMMENT ON COLUMN sys_exception_log.title IS '异常标题';
COMMENT ON COLUMN sys_exception_log.exception_name IS '异常名称';
COMMENT ON COLUMN sys_exception_log.method_name IS '方法名称';
COMMENT ON COLUMN sys_exception_log.method_params IS '方法参数';
COMMENT ON COLUMN sys_exception_log.exception_msg IS '异常消息';
COMMENT ON COLUMN sys_exception_log.exception_stack IS '异常堆栈';
COMMENT ON COLUMN sys_exception_log.ip IS 'IP地址';
COMMENT ON COLUMN sys_exception_log.create_time IS '创建时间';
COMMENT ON TABLE sys_menu IS '系统菜单表';
COMMENT ON TABLE sys_login_log IS '登录日志表';