DataOps-platform/database/create_wechat_users.sql

-- 微信用户表DDL脚本
-- 用于存储微信注册登录用户信息

create table public.wechat_users
(
    id              serial
        primary key,
    openid          varchar(255) not null unique,
    phone_number    varchar(20),
    id_card_number  varchar(18),
    login_status    boolean default false not null,
    login_time      timestamp with time zone,
    user_status     varchar(20) default 'active' not null,
    created_at      timestamp with time zone default current_timestamp not null,
    updated_at      timestamp with time zone default current_timestamp not null
);

comment on table public.wechat_users is '微信用户信息表';

comment on column public.wechat_users.id is '主键ID';

comment on column public.wechat_users.openid is '微信用户openid，唯一标识';

comment on column public.wechat_users.phone_number is '用户手机号码';

comment on column public.wechat_users.id_card_number is '用户身份证号码';

comment on column public.wechat_users.login_status is '当前登录状态，true表示已登录，false表示未登录';

comment on column public.wechat_users.login_time is '最后登录时间';

comment on column public.wechat_users.user_status is '用户账户状态：active-活跃，inactive-非活跃，suspended-暂停，deleted-已删除';

comment on column public.wechat_users.created_at is '账户创建时间';

comment on column public.wechat_users.updated_at is '信息更新时间';

-- 创建索引以提高查询性能
create index idx_wechat_users_openid on public.wechat_users(openid);
create index idx_wechat_users_phone_number on public.wechat_users(phone_number);
create index idx_wechat_users_login_status on public.wechat_users(login_status);
create index idx_wechat_users_user_status on public.wechat_users(user_status);

-- 创建更新时间触发器函数
create or replace function update_updated_at_column()
returns trigger as $$
begin
    new.updated_at = current_timestamp;
    return new;
end;
$$ language plpgsql;

-- 为表添加更新时间触发器
create trigger update_wechat_users_updated_at
    before update on public.wechat_users
    for each row
    execute function update_updated_at_column();