sql/designer-init-pg.sql

-- ----------------------------
-- 设计师档案表
-- ----------------------------
drop table if exists designer_work;
drop table if exists designer_category;
drop table if exists designer_profile;

create table designer_profile (
    profile_id   bigserial,
    user_id      bigint,
    designer_name varchar(100) default '',
    designer_slug varchar(100) not null,
    slogan       varchar(500) default '',
    bio          text,
    avatar_url   varchar(500) default '',
    banner_url   varchar(500) default '',
    contact_email varchar(100) default '',
    contact_phone varchar(20)  default '',
    social_instagram varchar(200) default '',
    social_weibo varchar(200) default '',
    social_wechat varchar(100) default '',
    social_xiaohongshu varchar(200) default '',
    social_douyin varchar(200) default '',
    social_instagram_qr_url varchar(500) default '',
    social_weibo_qr_url varchar(500) default '',
    social_wechat_qr_url varchar(500) default '',
    social_xiaohongshu_qr_url varchar(500) default '',
    social_douyin_qr_url varchar(500) default '',
    website      varchar(200) default '',
    is_default   char(1)      default '0',
    status       char(1)      default '0',
    create_by    varchar(64)  default '',
    create_time  timestamp(0),
    update_by    varchar(64)  default '',
    update_time  timestamp(0),
    primary key (profile_id),
    constraint uk_designer_profile_slug unique (designer_slug)
);
comment on table  designer_profile is '设计师档案表';
comment on column designer_profile.profile_id   is '档案ID';
comment on column designer_profile.user_id      is '关联用户ID';
comment on column designer_profile.designer_name is '设计师名称';
comment on column designer_profile.designer_slug is '设计师访问标识';
comment on column designer_profile.slogan       is '个性签名/标语';
comment on column designer_profile.is_default   is '是否默认设计师（0否 1是）';
comment on column designer_profile.bio          is '个人简介';
comment on column designer_profile.avatar_url   is '头像URL';
comment on column designer_profile.banner_url   is '主页横幅URL';
comment on column designer_profile.status       is '状态（0正常 1停用）';

-- ----------------------------
-- 作品分类表
-- ----------------------------
create table designer_category (
    category_id   bigserial,
    profile_id    bigint       not null,
    category_name varchar(50)  not null,
    category_code varchar(50)  not null,
    description   varchar(500) default '',
    cover_url     varchar(500) default '',
    sort_order    int4         default 0,
    status        char(1)      default '0',
    create_by     varchar(64)  default '',
    create_time   timestamp(0),
    update_by     varchar(64)  default '',
    update_time   timestamp(0),
    primary key (category_id)
);
comment on table  designer_category is '作品分类表';
comment on column designer_category.category_id   is '分类ID';
comment on column designer_category.profile_id    is '所属设计师ID';
comment on column designer_category.category_name is '分类名称';
comment on column designer_category.category_code is '分类编码';
comment on column designer_category.sort_order    is '排序';
comment on column designer_category.status        is '状态（0正常 1停用）';

-- ----------------------------
-- 设计作品表
-- ----------------------------
create table designer_work (
    work_id         bigserial,
    profile_id      bigint       not null,
    category_id     bigint,
    title           varchar(200) not null,
    description     text,
    cover_url       varchar(500) default '',
    images          text,
    collection_name varchar(100) default '',
    year            int4,
    season          varchar(20)  default '',
    tags            varchar(500) default '',
    sort_order      int4         default 0,
    is_featured     char(1)      default '0',
    status          char(1)      default '0',
    view_count      int4         default 0,
    create_by       varchar(64)  default '',
    create_time     timestamp(0),
    update_by       varchar(64)  default '',
    update_time     timestamp(0),
    primary key (work_id)
);
comment on table  designer_work is '设计作品表';
comment on column designer_work.work_id         is '作品ID';
comment on column designer_work.profile_id      is '所属设计师ID';
comment on column designer_work.category_id     is '分类ID';
comment on column designer_work.title           is '作品标题';
comment on column designer_work.description     is '作品描述';
comment on column designer_work.cover_url       is '封面图URL';
comment on column designer_work.images          is '图片列表JSON';
comment on column designer_work.collection_name is '系列名称';
comment on column designer_work.year            is '年份';
comment on column designer_work.season          is '季节';
comment on column designer_work.tags            is '标签';
comment on column designer_work.is_featured     is '是否推荐（0否 1是）';
comment on column designer_work.status          is '状态（0发布 1草稿）';
comment on column designer_work.view_count      is '浏览量';

-- ----------------------------
-- 初始化默认设计师档案（关联admin用户 user_id=1）
-- ----------------------------
insert into designer_profile values(1, 1, 'NANA', 'nana', 'Where Art Meets Fashion', '独立时尚设计师，专注于将艺术灵感融入日常穿搭，擅长服装、包袋、鞋履及配饰设计。每一件作品都是对美学与工艺的极致追求。', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '0', 'admin', current_timestamp, '', null);
alter sequence designer_profile_profile_id_seq restart 100;

-- ----------------------------
-- 初始化默认分类
-- ----------------------------
insert into designer_category values(1, 1, '服装', 'clothing', '原创服装设计', '', 1, '0', 'admin', current_timestamp, '', null);
insert into designer_category values(2, 1, '包袋', 'bags', '手工包袋设计', '', 2, '0', 'admin', current_timestamp, '', null);
insert into designer_category values(3, 1, '鞋履', 'shoes', '独立鞋履设计', '', 3, '0', 'admin', current_timestamp, '', null);
insert into designer_category values(4, 1, '配饰', 'accessories', '挂饰与配饰设计', '', 4, '0', 'admin', current_timestamp, '', null);
alter sequence designer_category_category_id_seq restart 100;

-- ----------------------------
-- 初始化菜单
-- ----------------------------
insert into sys_menu values(2100, '设计师管理', 0, 6, 'designer', null, null, '', '1', '0', 'M', '0', '0', '', 'education', 'admin', current_timestamp, '', null, '设计师管理目录');
insert into sys_menu values(2110, '作品管理', 2100, 1, 'work', 'designer/work/index', null, '', '1', '0', 'C', '0', '0', 'designer:work:list', 'documentation', 'admin', current_timestamp, '', null, '作品管理菜单');
insert into sys_menu values(2111, '作品查询', 2110, 1, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:work:query', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2112, '作品新增', 2110, 2, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:work:add', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2113, '作品修改', 2110, 3, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:work:edit', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2114, '作品删除', 2110, 4, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:work:remove', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2120, '分类管理', 2100, 2, 'category', 'designer/category/index', null, '', '1', '0', 'C', '0', '0', 'designer:category:list', 'tree-table', 'admin', current_timestamp, '', null, '分类管理菜单');
insert into sys_menu values(2121, '分类查询', 2120, 1, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:category:query', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2122, '分类新增', 2120, 2, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:category:add', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2123, '分类修改', 2120, 3, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:category:edit', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2124, '分类删除', 2120, 4, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:category:remove', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2130, '设计师档案', 2100, 3, 'profile', 'designer/profile/index', null, '', '1', '0', 'C', '0', '0', 'designer:profile:list', 'peoples', 'admin', current_timestamp, '', null, '设计师档案菜单');
insert into sys_menu values(2131, '档案查询', 2130, 1, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:profile:query', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2132, '档案修改', 2130, 2, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:profile:edit', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2133, '设计师入驻', 2130, 3, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:profile:add', '#', 'admin', current_timestamp, '', null, '');
insert into sys_menu values(2134, '设计师删除', 2130, 4, '', '', null, '', '1', '0', 'F', '0', '0', 'designer:profile:remove', '#', 'admin', current_timestamp, '', null, '');

-- 新增设计师角色，仅保留设计师管理菜单
insert into sys_role values(3, '设计师', 'designer', 3, 5, 1, 1, '0', '0', 'admin', current_timestamp, '', null, '设计师注册默认角色');

-- 为admin角色分配设计师管理菜单权限
insert into sys_role_menu values(1, 2100);
insert into sys_role_menu values(1, 2110);
insert into sys_role_menu values(1, 2111);
insert into sys_role_menu values(1, 2112);
insert into sys_role_menu values(1, 2113);
insert into sys_role_menu values(1, 2114);
insert into sys_role_menu values(1, 2120);
insert into sys_role_menu values(1, 2121);
insert into sys_role_menu values(1, 2122);
insert into sys_role_menu values(1, 2123);
insert into sys_role_menu values(1, 2124);
insert into sys_role_menu values(1, 2130);
insert into sys_role_menu values(1, 2131);
insert into sys_role_menu values(1, 2132);
insert into sys_role_menu values(1, 2133);
insert into sys_role_menu values(1, 2134);

-- 为设计师角色分配设计师后台可见菜单权限
insert into sys_role_menu values(3, 2100);
insert into sys_role_menu values(3, 2110);
insert into sys_role_menu values(3, 2111);
insert into sys_role_menu values(3, 2112);
insert into sys_role_menu values(3, 2113);
insert into sys_role_menu values(3, 2114);
insert into sys_role_menu values(3, 2120);
insert into sys_role_menu values(3, 2121);
insert into sys_role_menu values(3, 2122);
insert into sys_role_menu values(3, 2123);
insert into sys_role_menu values(3, 2124);
insert into sys_role_menu values(3, 2130);
insert into sys_role_menu values(3, 2131);
insert into sys_role_menu values(3, 2132);