DataOps-platform/database/create_calendar_records.sql

-- 日历内容记录表DDL脚本
-- 用于存储用户的日历内容记录信息

create table public.calendar_records
(
    id              serial
        primary key,
    openid          varchar(255) not null,
    month_key       varchar(7)   not null,
    calendar_content jsonb       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.calendar_records is '日历内容记录表';

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

comment on column public.calendar_records.openid is '微信用户openid';

comment on column public.calendar_records.month_key is '月份标识，格式为YYYY-MM';

comment on column public.calendar_records.calendar_content is '日历内容，JSON数组格式';

comment on column public.calendar_records.created_at is '记录创建时间';

comment on column public.calendar_records.updated_at is '记录更新时间';

-- 创建索引以提高查询性能
create index idx_calendar_records_openid on public.calendar_records(openid);
create index idx_calendar_records_month_key on public.calendar_records(month_key);
create index idx_calendar_records_openid_month on public.calendar_records(openid, month_key);
create index idx_calendar_records_created_at on public.calendar_records(created_at);
create index idx_calendar_records_updated_at on public.calendar_records(updated_at);

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

-- 创建触发器
create trigger trigger_update_calendar_records_updated_at
    before update on public.calendar_records
    for each row
    execute function update_calendar_records_updated_at();

-- 创建唯一约束（一个用户在同一个月份只能有一条记录）
create unique index idx_calendar_records_openid_month_unique 
    on public.calendar_records(openid, month_key);

-- 添加约束检查月份格式
alter table public.calendar_records 
add constraint chk_calendar_records_month_format 
check (month_key ~ '^\d{4}-\d{2}$');

-- 添加约束检查openid格式（微信openid通常是28位字符串）
alter table public.calendar_records 
add constraint chk_calendar_records_openid_format 
check (length(openid) = 28 and openid ~ '^[a-zA-Z0-9_-]+$');

-- 添加约束检查JSON内容不为空
alter table public.calendar_records 
add constraint chk_calendar_records_content_not_empty 
check (jsonb_array_length(calendar_content) >= 0);