-- Novalon管理系统权限功能数据库迁移脚本
-- 版本: V4
-- 描述: 创建权限管理相关表结构

-- 权限表
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 roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES sys_permission(id) ON DELETE CASCADE,
    UNIQUE (role_id, permission_id)
);

-- 表注释
COMMENT ON TABLE sys_permission IS '系统权限表';
COMMENT ON COLUMN sys_permission.id IS '主键ID';
COMMENT ON COLUMN sys_permission.permission_name IS '权限名称';
COMMENT ON COLUMN sys_permission.permission_code IS '权限编码';
COMMENT ON COLUMN sys_permission.resource IS '资源路径';
COMMENT ON COLUMN sys_permission.action IS '操作类型';
COMMENT ON COLUMN sys_permission.description IS '权限描述';
COMMENT ON COLUMN sys_permission.status IS '状态：0-禁用，1-正常';
COMMENT ON COLUMN sys_permission.create_by IS '创建者';
COMMENT ON COLUMN sys_permission.update_by IS '更新者';
COMMENT ON COLUMN sys_permission.created_at IS '创建时间';
COMMENT ON COLUMN sys_permission.updated_at IS '更新时间';
COMMENT ON COLUMN sys_permission.deleted_at IS '删除时间';

COMMENT ON TABLE sys_role_permission IS '角色权限关联表';
COMMENT ON COLUMN sys_role_permission.id IS '主键ID';
COMMENT ON COLUMN sys_role_permission.role_id IS '角色ID';
COMMENT ON COLUMN sys_role_permission.permission_id IS '权限ID';
COMMENT ON COLUMN sys_role_permission.create_by IS '创建者';
COMMENT ON COLUMN sys_role_permission.update_by IS '更新者';
COMMENT ON COLUMN sys_role_permission.created_at IS '创建时间';
COMMENT ON COLUMN sys_role_permission.updated_at IS '更新时间';

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_permission_code ON sys_permission(permission_code);
CREATE INDEX IF NOT EXISTS idx_permission_resource ON sys_permission(resource);
CREATE INDEX IF NOT EXISTS idx_permission_status ON sys_permission(status);
CREATE INDEX IF NOT EXISTS idx_role_permission_role_id ON sys_role_permission(role_id);
CREATE INDEX IF NOT EXISTS idx_role_permission_permission_id ON sys_role_permission(permission_id);

-- 插入初始权限数据
INSERT INTO sys_permission (permission_name, permission_code, resource, action, description, status) VALUES
('用户查看', 'system:user:view', '/api/users', 'GET', '查看用户列表', 1),
('用户创建', 'system:user:create', '/api/users', 'POST', '创建用户', 1),
('用户编辑', 'system:user:edit', '/api/users', 'PUT', '编辑用户', 1),
('用户删除', 'system:user:delete', '/api/users', 'DELETE', '删除用户', 1),
('角色查看', 'system:role:view', '/api/roles', 'GET', '查看角色列表', 1),
('角色创建', 'system:role:create', '/api/roles', 'POST', '创建角色', 1),
('角色编辑', 'system:role:edit', '/api/roles', 'PUT', '编辑角色', 1),
('角色删除', 'system:role:delete', '/api/roles', 'DELETE', '删除角色', 1),
('角色分配权限', 'system:role:assign', '/api/roles/*/permissions', 'POST', '为角色分配权限', 1),
('权限查看', 'system:permission:view', '/api/permissions', 'GET', '查看权限列表', 1),
('权限创建', 'system:permission:create', '/api/permissions', 'POST', '创建权限', 1),
('权限编辑', 'system:permission:edit', '/api/permissions', 'PUT', '编辑权限', 1),
('权限删除', 'system:permission:delete', '/api/permissions', 'DELETE', '删除权限', 1),
('菜单查看', 'system:menu:view', '/api/menus', 'GET', '查看菜单列表', 1),
('菜单创建', 'system:menu:create', '/api/menus', 'POST', '创建菜单', 1),
('菜单编辑', 'system:menu:edit', '/api/menus', 'PUT', '编辑菜单', 1),
('菜单删除', 'system:menu:delete', '/api/menus', 'DELETE', '删除菜单', 1),
('字典查看', 'system:dict:view', '/api/dict', 'GET', '查看字典列表', 1),
('字典创建', 'system:dict:create', '/api/dict', 'POST', '创建字典', 1),
('字典编辑', 'system:dict:edit', '/api/dict', 'PUT', '编辑字典', 1),
('字典删除', 'system:dict:delete', '/api/dict', 'DELETE', '删除字典', 1),
('配置查看', 'system:config:view', '/api/config', 'GET', '查看系统配置', 1),
('配置创建', 'system:config:create', '/api/config', 'POST', '创建系统配置', 1),
('配置编辑', 'system:config:edit', '/api/config', 'PUT', '编辑系统配置', 1),
('配置删除', 'system:config:delete', '/api/config', 'DELETE', '删除系统配置', 1),
('日志查看', 'system:log:view', '/api/logs', 'GET', '查看日志', 1),
('文件上传', 'system:file:upload', '/api/files/upload', 'POST', '上传文件', 1),
('文件下载', 'system:file:download', '/api/files/download', 'GET', '下载文件', 1),
('文件删除', 'system:file:delete', '/api/files', 'DELETE', '删除文件', 1),
('公告查看', 'system:notice:view', '/api/notices', 'GET', '查看公告', 1),
('公告创建', 'system:notice:create', '/api/notices', 'POST', '创建公告', 1),
('公告编辑', 'system:notice:edit', '/api/notices', 'PUT', '编辑公告', 1),
('公告删除', 'system:notice:delete', '/api/notices', 'DELETE', '删除公告', 1);

-- 为管理员角色分配所有权限
INSERT INTO sys_role_permission (role_id, permission_id)
SELECT 1, id FROM sys_permission WHERE status = 1;