create table public.parsed_talents
(
    id             serial
        primary key,
    name_zh        varchar(100)                          not null,
    name_en        varchar(100),
    title_zh       varchar(100),
    title_en       varchar(100),
    mobile         varchar(50),
    phone          varchar(50),
    email          varchar(100),
    hotel_zh       varchar(200),
    hotel_en       varchar(200),
    address_zh     text,
    address_en     text,
    postal_code_zh varchar(20),
    postal_code_en varchar(20),
    brand_zh       varchar(100),
    brand_en       varchar(100),
    affiliation_zh varchar(200),
    affiliation_en varchar(200),
    image_path     varchar(255),
    career_path    jsonb,
    brand_group    varchar(200),
    created_at     timestamp   default CURRENT_TIMESTAMP not null,
    updated_at     timestamp,
    updated_by     varchar(50),
    status         varchar(20) default 'active'::character varying,
    birthday       date,
    residence      text,
    age            integer,
    native_place   text,
    origin_source  json,
    talent_profile text,
    task_id varchar(50),
    task_type varchar(20)
);

comment on table public.parsed_talents is '名片信息表';

comment on column public.parsed_talents.id is '自增主键';

comment on column public.parsed_talents.name_zh is '中文姓名';

comment on column public.parsed_talents.name_en is '英文姓名';

comment on column public.parsed_talents.title_zh is '中文头衔';

comment on column public.parsed_talents.title_en is '英文头衔';

comment on column public.parsed_talents.mobile is '手机号码';

comment on column public.parsed_talents.phone is '固定电话';

comment on column public.parsed_talents.email is '电子邮箱';

comment on column public.parsed_talents.hotel_zh is '中文酒店名称';

comment on column public.parsed_talents.hotel_en is '英文酒店名称';

comment on column public.parsed_talents.address_zh is '中文详细地址';

comment on column public.parsed_talents.address_en is '英文详细地址';

comment on column public.parsed_talents.postal_code_zh is '中文邮政编码';

comment on column public.parsed_talents.postal_code_en is '英文邮政编码';

comment on column public.parsed_talents.brand_zh is '中文品牌名称';

comment on column public.parsed_talents.brand_en is '英文品牌名称';

comment on column public.parsed_talents.affiliation_zh is '中文隶属关系';

comment on column public.parsed_talents.affiliation_en is '英文隶属关系';

comment on column public.parsed_talents.image_path is 'MinIO中的图片路径';

comment on column public.parsed_talents.career_path is '职业轨迹，JSON格式';

comment on column public.parsed_talents.brand_group is '品牌组合';

comment on column public.parsed_talents.created_at is '创建时间';

comment on column public.parsed_talents.updated_at is '修改时间';

comment on column public.parsed_talents.updated_by is '修改人';

comment on column public.parsed_talents.status is '状态';

comment on column public.parsed_talents.birthday is '生日';

comment on column public.parsed_talents.residence is '居住地';

comment on column public.parsed_talents.age is '年龄字段 - 存储人员年龄信息，取值范围1-150';

comment on column public.parsed_talents.native_place is '籍贯字段 - 存储人员籍贯或出生地信息';

comment on column public.parsed_talents.origin_source is '原始资料记录字段 - 采用JSON格式保存原始资料信息，包括数据来源、MinIO路径等';

comment on column public.parsed_talents.talent_profile is '人才的情况简介';

comment on column public.parsed_talents.task_id is '解析任务ID';

comment on column public.parsed_talents.task_type is '解析任务类型';

alter table public.parsed_talents
    owner to postgres;

create index idx_parsed_talents_name_zh
    on public.parsed_talents (name_zh);

create index idx_parsed_talents_name_en
    on public.parsed_talents (name_en);

create index idx_parsed_talents_hotel_zh
    on public.parsed_talents (hotel_zh);

create index idx_parsed_talents_created_at
    on public.parsed_talents (created_at);

create index idx_parsed_talents_status
    on public.parsed_talents (status);