-- ============================================================
-- OAuth 登录数据库迁移：auth_oauth_accounts
-- 执行环境：在已有 EchoChat 数据库基础上增量升级
-- 全部语句使用 IF NOT EXISTS / 幂等形式，可重复执行
-- ============================================================

CREATE TABLE IF NOT EXISTS auth_oauth_accounts (
    id            BIGSERIAL PRIMARY KEY,
    user_id       BIGINT        NOT NULL REFERENCES auth_users(id),
    provider      VARCHAR(20)   NOT NULL,
    open_id       VARCHAR(128)  NOT NULL,
    union_id      VARCHAR(128)  NOT NULL DEFAULT '',
    nickname      VARCHAR(100)  NOT NULL DEFAULT '',
    avatar        VARCHAR(500)  NOT NULL DEFAULT '',
    access_token  VARCHAR(1024) NOT NULL DEFAULT '',
    refresh_token VARCHAR(1024) NOT NULL DEFAULT '',
    expires_at    TIMESTAMP(0)  DEFAULT NULL,
    created_at    TIMESTAMP(0)  NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMP(0)  NOT NULL DEFAULT NOW(),
    UNIQUE (provider, open_id)
);

COMMENT ON TABLE  auth_oauth_accounts               IS '第三方登录账号绑定表';
COMMENT ON COLUMN auth_oauth_accounts.user_id       IS '关联本地用户 ID';
COMMENT ON COLUMN auth_oauth_accounts.provider      IS '第三方平台标识：wechat / qq';
COMMENT ON COLUMN auth_oauth_accounts.open_id       IS '第三方平台用户 OpenID';
COMMENT ON COLUMN auth_oauth_accounts.union_id      IS '第三方平台 UnionID';
COMMENT ON COLUMN auth_oauth_accounts.nickname      IS '第三方平台昵称快照';
COMMENT ON COLUMN auth_oauth_accounts.avatar        IS '第三方平台头像快照';
COMMENT ON COLUMN auth_oauth_accounts.access_token  IS '第三方平台 access_token';
COMMENT ON COLUMN auth_oauth_accounts.refresh_token IS '第三方平台 refresh_token';
COMMENT ON COLUMN auth_oauth_accounts.expires_at    IS '第三方平台 token 过期时间';

CREATE INDEX IF NOT EXISTS idx_auth_oauth_accounts_user ON auth_oauth_accounts (user_id);
CREATE INDEX IF NOT EXISTS idx_auth_oauth_accounts_union ON auth_oauth_accounts (union_id);