-- 非遗新闻采集 - 数据库初始化脚本
-- 库: yangyi / 模式: new_collection
-- 注意: 重跑会清空 news 表数据。

CREATE SCHEMA IF NOT EXISTS new_collection;

DROP TABLE IF EXISTS new_collection.news;

CREATE TABLE new_collection.news (
    id                  varchar(64) PRIMARY KEY,
    title               text,
    url                 text,
    publish_time        timestamp,
    channel_id          varchar(64),
    channel_name        varchar(128),
    editor              varchar(255),
    text_reporters      text,
    image_reporters     text,
    correspondents      text,
    content             text,
    content_fetched_at  timestamp,
    created_at          timestamp default now()
);

COMMENT ON TABLE new_collection.news IS '非遗新闻采集结果';

DROP TABLE IF EXISTS new_collection.channel;

CREATE TABLE new_collection.channel (
    id BIGSERIAL PRIMARY KEY ,
    channel_id   varchar(64) UNIQUE ,
    channel_name varchar(255)
);

COMMENT ON TABLE new_collection.channel IS '新闻频道';