water-management-system/db/postgresql/V1__base.sql

-- =============================================
-- 智慧水务管理系统 - PostgreSQL DDL
-- 版本: V1
-- 描述: 核心业务表建表脚本
-- =============================================

-- ==================== 系统管理 ====================

-- 部门表
CREATE TABLE IF NOT EXISTS sys_dept (
    id BIGSERIAL PRIMARY KEY,
    parent_id BIGINT,
    dept_name VARCHAR(100) NOT NULL,
    dept_type VARCHAR(20) NOT NULL DEFAULT 'water_company',  -- water_bureau/water_company/ops
    sort_order INT DEFAULT 0,
    leader VARCHAR(50),
    phone VARCHAR(20),
    status SMALLINT DEFAULT 1,
    deleted SMALLINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_dept IS '部门表';
COMMENT ON COLUMN sys_dept.dept_type IS '部门类型: water_bureau(水利局)/water_company(水务公司)/ops(运维单位)';

-- 用户表
CREATE TABLE IF NOT EXISTS sys_user (
    id BIGSERIAL PRIMARY KEY,
    dept_id BIGINT REFERENCES sys_dept(id),
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    real_name VARCHAR(50),
    nickname VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(100),
    avatar VARCHAR(500),
    gender SMALLINT DEFAULT 0,  -- 0:未知 1:男 2:女
    role_type VARCHAR(30) DEFAULT 'operator',  -- admin/leader/manager/operator/tech
    status SMALLINT DEFAULT 1,  -- 0:停用 1:启用
    login_ip VARCHAR(50),
    login_at TIMESTAMP,
    deleted SMALLINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_user IS '用户表';
COMMENT ON COLUMN sys_user.role_type IS '角色类型: admin/leader/manager/operator/tech';

-- 角色表
CREATE TABLE IF NOT EXISTS sys_role (
    id BIGSERIAL PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    role_key VARCHAR(50) UNIQUE NOT NULL,
    role_sort INT DEFAULT 0,
    data_scope VARCHAR(20) DEFAULT 'SELF',  -- ALL/DEPT/CUSTOM/SELF
    status SMALLINT DEFAULT 1,
    remark VARCHAR(500),
    deleted SMALLINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_role IS '角色表';
COMMENT ON COLUMN sys_role.role_key IS '角色标识: admin/supervisor/biz_manager/field_ops/tech_maintain';
COMMENT ON COLUMN sys_role.data_scope IS '数据权限范围: ALL/DEPT/CUSTOM/SELF';

-- 菜单表
CREATE TABLE IF NOT EXISTS sys_menu (
    id BIGSERIAL PRIMARY KEY,
    parent_id BIGINT DEFAULT 0,
    menu_name VARCHAR(50) NOT NULL,
    menu_type CHAR(1) DEFAULT 'M',  -- M:目录 C:菜单 F:按钮
    path VARCHAR(200),
    component VARCHAR(255),
    perms VARCHAR(100),
    icon VARCHAR(100),
    sort_order INT DEFAULT 0,
    visible SMALLINT DEFAULT 1,
    status SMALLINT DEFAULT 1,
    deleted SMALLINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_menu IS '菜单表';

-- 角色-菜单关联
CREATE TABLE IF NOT EXISTS sys_role_menu (
    role_id BIGINT NOT NULL REFERENCES sys_role(id),
    menu_id BIGINT NOT NULL REFERENCES sys_menu(id),
    PRIMARY KEY (role_id, menu_id)
);
COMMENT ON TABLE sys_role_menu IS '角色菜单关联表';

-- 用户-角色关联
CREATE TABLE IF NOT EXISTS sys_user_role (
    user_id BIGINT NOT NULL REFERENCES sys_user(id),
    role_id BIGINT NOT NULL REFERENCES sys_role(id),
    PRIMARY KEY (user_id, role_id)
);
COMMENT ON TABLE sys_user_role IS '用户角色关联表';

-- 操作日志表
CREATE TABLE IF NOT EXISTS sys_oper_log (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(50),
    module VARCHAR(50),
    operation VARCHAR(50),
    method VARCHAR(200),
    request_method VARCHAR(10),
    request_url VARCHAR(500),
    request_params TEXT,
    response_result TEXT,
    ip VARCHAR(50),
    location VARCHAR(100),
    duration BIGINT,
    status SMALLINT DEFAULT 1,
    error_msg TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_oper_log IS '操作日志表';

-- 登录日志表
CREATE TABLE IF NOT EXISTS sys_login_log (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50),
    ip VARCHAR(50),
    location VARCHAR(100),
    browser VARCHAR(100),
    os VARCHAR(100),
    status SMALLINT DEFAULT 1,  -- 0:失败 1:成功
    msg VARCHAR(500),
    login_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_login_log IS '登录日志表';

-- ==================== 数据字典 ====================

CREATE TABLE IF NOT EXISTS sys_dict_type (
    id BIGSERIAL PRIMARY KEY,
    dict_name VARCHAR(100) NOT NULL,
    dict_key VARCHAR(100) UNIQUE NOT NULL,
    status SMALLINT DEFAULT 1,
    remark VARCHAR(500),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_dict_type IS '字典类型表';

CREATE TABLE IF NOT EXISTS sys_dict_data (
    id BIGSERIAL PRIMARY KEY,
    dict_type_id BIGINT REFERENCES sys_dict_type(id),
    dict_label VARCHAR(100) NOT NULL,
    dict_value VARCHAR(100) NOT NULL,
    css_class VARCHAR(100),
    list_class VARCHAR(100),
    sort_order INT DEFAULT 0,
    status SMALLINT DEFAULT 1,
    remark VARCHAR(500),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_dict_data IS '字典数据表';

-- ==================== 通知方案 ====================

CREATE TABLE IF NOT EXISTS sys_notify_scheme (
    id BIGSERIAL PRIMARY KEY,
    scheme_name VARCHAR(100) NOT NULL,
    channels VARCHAR(200),  -- sms,app_push,websocket,wechat (逗号分隔)
    template_id VARCHAR(50),
    status SMALLINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_notify_scheme IS '通知方案表';

CREATE TABLE IF NOT EXISTS sys_notify_record (
    id BIGSERIAL PRIMARY KEY,
    scheme_id BIGINT REFERENCES sys_notify_scheme(id),
    target_user_id BIGINT,
    target_phone VARCHAR(20),
    channel VARCHAR(20),
    title VARCHAR(200),
    content TEXT,
    status VARCHAR(20) DEFAULT 'pending',  -- pending/sent/failed/read
    sent_at TIMESTAMP,
    read_at TIMESTAMP,
    error_msg VARCHAR(500),
    created_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_notify_record IS '通知记录表';

-- ==================== 文件管理 ====================

CREATE TABLE IF NOT EXISTS sys_file (
    id BIGSERIAL PRIMARY KEY,
    file_name VARCHAR(200) NOT NULL,
    original_name VARCHAR(200),
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    mime_type VARCHAR(100),
    file_ext VARCHAR(20),
    storage_type VARCHAR(20) DEFAULT 'minio',
    bucket VARCHAR(100) DEFAULT 'water-management',
    module VARCHAR(50),
    biz_id BIGINT,
    upload_by BIGINT,
    deleted SMALLINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE sys_file IS '文件管理表';