drop table admin.users;
drop table file.metas;
drop table file.files_user;

-- admin 空间
CREATE SCHEMA IF NOT EXISTS admin;

create table admin.users
(
    user_id    uuid primary key,
    username   character varying(32),
    password   char(32),
    realname   character varying(16),
    setting    jsonb,
    created_at timestamptz NOT NULL,
    updated_at timestamptz NOT NULL

);

alter table only admin.users
    add constraint uk_users_name unique (username);

comment on column admin.users.realname is '真实姓名';
comment on column admin.users.setting is '用户设置[password_salt，session_salt，lock_time，reset_password]';

CREATE SCHEMA IF NOT EXISTS file;

create table file.metas
(
    sha1         varchar(64) primary key,
    size         int4         not null default 0,
    location     varchar(256) not null,
    content_type varchar(128) not null,
    updated_at   timestamptz  not null,
    created_at   timestamptz  not null
);
comment on column file.metas.sha1 is '文件哈希';
comment on column file.metas.size is '文件大小';
comment on column file.metas.location is '本地存放路径';
comment on column file.metas.content_type is '文件类型';


-- 用户文件存储
create table file.files_user
(
    fu_id      uuid primary key,
    meta_id    varchar(64) not null references file.metas (sha1), -- 设置外键
    user_id    uuid references admin.users (user_id),
    file_name  varchar(64) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null,
    deleted_at timestamptz default null
);
comment on table file.files_user is '用户文件表';
comment on column file.files_user.meta_id is '用户文件元数据ID';
comment on column file.files_user.file_name is '用户文件名称';
comment on column file.files_user.deleted_at is '用户删除时间';