53 lines
1.9 KiB
SQL
53 lines
1.9 KiB
SQL
-- Telegram-style posts feed (separate from legacy resources)
|
|
|
|
CREATE TABLE IF NOT EXISTS posts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
category_id INT NOT NULL REFERENCES categories(id),
|
|
language TEXT NOT NULL DEFAULT 'zh',
|
|
text_zh TEXT,
|
|
text_en TEXT,
|
|
text_ja TEXT,
|
|
text_ko TEXT,
|
|
text_vi TEXT,
|
|
text_id TEXT,
|
|
text_ms TEXT,
|
|
is_public BOOLEAN NOT NULL DEFAULT TRUE,
|
|
is_recommended BOOLEAN NOT NULL DEFAULT FALSE,
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
published_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
view_count INT NOT NULL DEFAULT 0,
|
|
download_count INT NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_posts_category ON posts(category_id);
|
|
CREATE INDEX IF NOT EXISTS idx_posts_status_public ON posts(status, is_public);
|
|
CREATE INDEX IF NOT EXISTS idx_posts_published ON posts(published_at DESC NULLS LAST, id DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_posts_recommended ON posts(is_recommended) WHERE is_recommended = TRUE;
|
|
|
|
CREATE TABLE IF NOT EXISTS post_attachments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
kind TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
mime TEXT NOT NULL DEFAULT 'application/octet-stream',
|
|
filename TEXT NOT NULL DEFAULT '',
|
|
size_bytes BIGINT NOT NULL DEFAULT 0,
|
|
width INT,
|
|
height INT,
|
|
duration_sec INT,
|
|
poster_url TEXT,
|
|
thumbnail_url TEXT,
|
|
sort_order INT NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_post_attachments_post ON post_attachments(post_id, sort_order ASC);
|
|
|
|
CREATE TABLE IF NOT EXISTS post_tags (
|
|
post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
tag_id INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (post_id, tag_id)
|
|
);
|