CREATE TABLE users
(
    "id"         serial PRIMARY KEY,
    "name"       text                                NOT NULL,
    "login"      text                                NULL,
    "password"   text                                NULL,
    "updated_at" timestamp DEFAULT NULL,
    "created_at" timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- 电子邮件
-- 关联电子邮件，可以设置多个
CREATE TABLE user_emails
(
    "address"          text PRIMARY KEY,
    "user_id"          integer                             NOT NULL,
    "primary"          boolean   DEFAULT FALSE,
    "verified_at"      timestamp DEFAULT NULL,
    "last_accessed_at" timestamp DEFAULT NULL,
    "created_at"       timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- 手机号码
-- 关联手机号码，可以设置多个
CREATE TABLE user_phones
(
    "number"           text PRIMARY KEY,
    "user_id"          integer                             NOT NULL,
    "primary"          boolean   DEFAULT FALSE,
    "last_accessed_at" timestamp DEFAULT NULL,
    "created_at"       timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- 关联账号
-- 第三方账户，即 OAuth2 账号
CREATE TABLE user_links
(
    "id"               serial PRIMARY KEY,
    "kind"             text                                NOT NULL,
    "open_id"          text                                NOT NULL,
    "user_id"          integer                             NOT NULL,
    "union_id"         text      DEFAULT NULL,
    "last_accessed_at" timestamp DEFAULT NULL,
    "created_at"       timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX ON user_links ("kind", "open_id");

-- 用户令牌
-- 用户登录后生成，用于刷新`AccessToken`
CREATE TABLE user_tokens
(
    "id"               serial PRIMARY KEY,
    "token"            text                                NOT NULL,
    "user_id"          integer                             NOT NULL,
    "display"          text                                NOT NULL,
    "last_accessed_at" timestamp DEFAULT NULL,
    "created_at"       timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX ON user_tokens ("token");