-- 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),
    status INTEGER DEFAULT 1,
    role_id BIGINT,
    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
);

-- 用户角色关联表（支持多对多关系）
CREATE TABLE IF NOT EXISTS user_role (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(50),
    CONSTRAINT fk_user_role_user FOREIGN KEY (user_id) REFERENCES sys_user(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_role_role FOREIGN KEY (role_id) REFERENCES sys_role(id) ON DELETE CASCADE,
    CONSTRAINT uk_user_role UNIQUE (user_id, role_id)
);

-- ============================================
-- 权限相关表
-- ============================================

-- 权限表
CREATE TABLE IF NOT EXISTS sys_permission (
    id BIGSERIAL PRIMARY KEY,
    permission_name VARCHAR(100) NOT NULL,
    permission_code VARCHAR(100) NOT NULL UNIQUE,
    resource VARCHAR(200) NOT NULL,
    action VARCHAR(50) NOT NULL,
    description VARCHAR(500),
    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_permission (
    id BIGSERIAL PRIMARY KEY,
    role_id BIGINT NOT NULL,
    permission_id BIGINT NOT NULL,
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES sys_role(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES sys_permission(id) ON DELETE CASCADE,
    UNIQUE (role_id, permission_id)
);

-- ============================================
-- 菜单相关表
-- ============================================

-- 菜单表
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 audit_log (
    id BIGSERIAL PRIMARY KEY,
    entity_type VARCHAR(100) NOT NULL,
    entity_id BIGINT,
    operation_type VARCHAR(20) NOT NULL,
    operator VARCHAR(100),
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    before_data JSONB,
    after_data JSONB,
    changed_fields TEXT[],
    ip_address VARCHAR(50),
    user_agent TEXT,
    description 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 audit_log_archive (
    id BIGSERIAL PRIMARY KEY,
    entity_type VARCHAR(100) NOT NULL,
    entity_id BIGINT,
    operation_type VARCHAR(20) NOT NULL,
    operator VARCHAR(100),
    operation_time TIMESTAMP,
    before_data JSONB,
    after_data JSONB,
    changed_fields TEXT[],
    ip_address VARCHAR(50),
    user_agent TEXT,
    description TEXT,
    created_at TIMESTAMP,
    archived_at TIMESTAMP DEFAULT CURRENT_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相关表
-- ============================================

-- 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_user IS '系统用户表';
COMMENT ON TABLE sys_role IS '系统角色表';
COMMENT ON TABLE user_role IS '用户角色关联表';
COMMENT ON TABLE sys_permission IS '系统权限表';
COMMENT ON TABLE sys_role_permission IS '角色权限关联表';
COMMENT ON TABLE sys_menu IS '系统菜单表';
COMMENT ON TABLE sys_dict_type IS '字典类型表';
COMMENT ON TABLE sys_dict_data IS '字典数据表';
COMMENT ON TABLE sys_dictionary IS '通用字典表';
COMMENT ON TABLE sys_config IS '系统配置表';
COMMENT ON TABLE sys_login_log IS '登录日志表';
COMMENT ON TABLE sys_exception_log IS '异常日志表';
COMMENT ON TABLE operation_log IS '操作日志表';
COMMENT ON TABLE audit_log IS '审计日志表';
COMMENT ON TABLE audit_log_archive IS '审计日志归档表';
COMMENT ON TABLE sys_notice IS '系统公告表';
COMMENT ON TABLE sys_user_message IS '用户消息表';
COMMENT ON TABLE sys_file IS '文件管理表';
COMMENT ON TABLE oauth2_client IS 'OAuth2客户端表';

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 audit_log IS '审计日志表';
COMMENT ON COLUMN audit_log.id IS '主键ID';
COMMENT ON COLUMN audit_log.entity_type IS '实体类型（如User, Role等）';
COMMENT ON COLUMN audit_log.entity_id IS '实体ID';
COMMENT ON COLUMN audit_log.operation_type IS '操作类型（CREATE, UPDATE, DELETE）';
COMMENT ON COLUMN audit_log.operator IS '操作人';
COMMENT ON COLUMN audit_log.operation_time IS '操作时间';
COMMENT ON COLUMN audit_log.before_data IS '变更前数据（JSON格式）';
COMMENT ON COLUMN audit_log.after_data IS '变更后数据（JSON格式）';
COMMENT ON COLUMN audit_log.changed_fields IS '变更字段列表';
COMMENT ON COLUMN audit_log.ip_address IS 'IP地址';
COMMENT ON COLUMN audit_log.description IS '操作描述';
COMMENT ON COLUMN audit_log.created_at IS '记录创建时间';

COMMENT ON TABLE audit_log_archive IS '审计日志归档表';
COMMENT ON COLUMN audit_log_archive.id IS '主键ID';
COMMENT ON COLUMN audit_log_archive.entity_type IS '实体类型（如User, Role等）';
COMMENT ON COLUMN audit_log_archive.entity_id IS '实体ID';
COMMENT ON COLUMN audit_log_archive.operation_type IS '操作类型（CREATE, UPDATE, DELETE）';
COMMENT ON COLUMN audit_log_archive.operator IS '操作人';
COMMENT ON COLUMN audit_log_archive.operation_time IS '操作时间';
COMMENT ON COLUMN audit_log_archive.before_data IS '变更前数据（JSON格式）';
COMMENT ON COLUMN audit_log_archive.after_data IS '变更后数据（JSON格式）';
COMMENT ON COLUMN audit_log_archive.changed_fields IS '变更字段列表';
COMMENT ON COLUMN audit_log_archive.ip_address IS 'IP地址';
COMMENT ON COLUMN audit_log_archive.user_agent IS '用户代理';
COMMENT ON COLUMN audit_log_archive.description IS '操作描述';
COMMENT ON COLUMN audit_log_archive.created_at IS '记录创建时间';
COMMENT ON COLUMN audit_log_archive.archived_at IS '归档时间';