1030 lines
40 KiB
PL/PgSQL
1030 lines
40 KiB
PL/PgSQL
-- 视频系统数据库设计
|
||
-- 基于 ak_contents 扩展，支持视频内容、弹幕、评论、收藏、转发、点赞等功能
|
||
|
||
-- ================================
|
||
-- 重要说明：内容类型校验
|
||
-- ================================
|
||
-- 注意：由于 PostgreSQL 不支持在 CHECK 约束中使用子查询，
|
||
-- 以下表的内容类型校验需要在应用层实现：
|
||
--
|
||
-- 1. ak_video_danmakus: 应确保 content_id 对应的内容类型为 'video' 或 'audio'
|
||
-- 2. ak_video_play_records: 应确保 content_id 对应的内容类型为 'video' 或 'audio'
|
||
-- 3. ak_image_tags: 应确保 content_id 对应的内容类型为 'image'
|
||
-- 4. ak_image_view_records: 应确保 content_id 对应的内容类型为 'image'
|
||
--
|
||
-- 建议在应用层（如 Supabase RPC 函数）中添加这些校验逻辑
|
||
-- ================================
|
||
|
||
-- 1. 扩展 ak_contents 表，增加多媒体相关字段
|
||
ALTER TABLE ak_contents
|
||
ADD COLUMN IF NOT EXISTS content_type VARCHAR(20) DEFAULT 'article' CHECK (content_type IN ('article', 'video', 'audio', 'image')),
|
||
-- 视频相关字段
|
||
ADD COLUMN IF NOT EXISTS video_url TEXT,
|
||
ADD COLUMN IF NOT EXISTS video_duration INTEGER, -- 视频时长（秒）
|
||
ADD COLUMN IF NOT EXISTS video_poster TEXT, -- 视频封面
|
||
ADD COLUMN IF NOT EXISTS video_width INTEGER,
|
||
ADD COLUMN IF NOT EXISTS video_height INTEGER,
|
||
ADD COLUMN IF NOT EXISTS video_size BIGINT, -- 文件大小（字节）
|
||
ADD COLUMN IF NOT EXISTS video_format VARCHAR(10), -- mp4, webm, etc
|
||
ADD COLUMN IF NOT EXISTS video_quality VARCHAR(10), -- 720p, 1080p, 4k, etc
|
||
-- 音频相关字段
|
||
ADD COLUMN IF NOT EXISTS audio_url TEXT,
|
||
ADD COLUMN IF NOT EXISTS audio_duration INTEGER, -- 音频时长（秒）
|
||
ADD COLUMN IF NOT EXISTS audio_size BIGINT, -- 文件大小（字节）
|
||
ADD COLUMN IF NOT EXISTS audio_format VARCHAR(10), -- mp3, wav, flac, etc
|
||
ADD COLUMN IF NOT EXISTS audio_bitrate INTEGER, -- 比特率（kbps）
|
||
ADD COLUMN IF NOT EXISTS audio_sample_rate INTEGER, -- 采样率（Hz）
|
||
ADD COLUMN IF NOT EXISTS audio_cover TEXT, -- 音频封面图
|
||
-- 图片相关字段
|
||
ADD COLUMN IF NOT EXISTS image_url TEXT,
|
||
ADD COLUMN IF NOT EXISTS image_width INTEGER,
|
||
ADD COLUMN IF NOT EXISTS image_height INTEGER,
|
||
ADD COLUMN IF NOT EXISTS image_size BIGINT, -- 文件大小（字节）
|
||
ADD COLUMN IF NOT EXISTS image_format VARCHAR(10), -- jpg, png, webp, etc
|
||
ADD COLUMN IF NOT EXISTS image_quality VARCHAR(10), -- original, compressed, thumbnail
|
||
ADD COLUMN IF NOT EXISTS image_alt_text TEXT, -- 图片alt文本，用于无障碍访问
|
||
-- 图集字段
|
||
ADD COLUMN IF NOT EXISTS images JSONB, -- 图集数据，支持多图片存储
|
||
-- 通用字段
|
||
ADD COLUMN IF NOT EXISTS allow_danmu BOOLEAN DEFAULT true, -- 视频/音频是否允许弹幕
|
||
ADD COLUMN IF NOT EXISTS allow_download BOOLEAN DEFAULT false, -- 是否允许下载
|
||
ADD COLUMN IF NOT EXISTS media_metadata JSONB; -- 存储额外的媒体元数据
|
||
|
||
-- 2. 多媒体弹幕表（支持视频和音频）
|
||
CREATE TABLE IF NOT EXISTS ak_video_danmakus (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
content_id UUID REFERENCES ak_contents(id) ON DELETE CASCADE,
|
||
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
user_name VARCHAR(100) NOT NULL,
|
||
text TEXT NOT NULL,
|
||
time_point DECIMAL(10,3) NOT NULL, -- 时间点（秒，支持小数），适用于视频和音频
|
||
color VARCHAR(7) DEFAULT '#FFFFFF', -- 弹幕颜色
|
||
font_size INTEGER DEFAULT 25, -- 字体大小
|
||
position_type VARCHAR(10) DEFAULT 'scroll' CHECK (position_type IN ('scroll', 'top', 'bottom')),
|
||
speed INTEGER DEFAULT 1, -- 滚动速度
|
||
is_visible BOOLEAN DEFAULT true,
|
||
status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'hidden', 'deleted', 'reviewing')),
|
||
ip_address INET,
|
||
user_agent TEXT,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- 3. 用户行为表（点赞、收藏、转发等）
|
||
CREATE TABLE IF NOT EXISTS ak_user_interactions (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
content_id UUID REFERENCES ak_contents(id) ON DELETE CASCADE,
|
||
interaction_type VARCHAR(20) NOT NULL CHECK (interaction_type IN ('like', 'favorite', 'share', 'view', 'download')),
|
||
interaction_data JSONB, -- 额外数据，如分享到的平台、收藏夹ID等
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
UNIQUE(user_id, content_id, interaction_type) -- 防止重复操作
|
||
);
|
||
|
||
-- 4. 评论表（支持多级回复）
|
||
CREATE TABLE IF NOT EXISTS ak_content_comments (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
content_id UUID REFERENCES ak_contents(id) ON DELETE CASCADE,
|
||
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
user_name VARCHAR(100) NOT NULL,
|
||
parent_id UUID REFERENCES ak_content_comments(id) ON DELETE CASCADE, -- 父评论ID，NULL表示主评论
|
||
reply_to_user_id UUID REFERENCES auth.users(id), -- 回复的用户ID
|
||
reply_to_user_name VARCHAR(100), -- 回复的用户名
|
||
content TEXT NOT NULL,
|
||
like_count INTEGER DEFAULT 0,
|
||
reply_count INTEGER DEFAULT 0, -- 回复数量
|
||
status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'hidden', 'deleted', 'reviewing')),
|
||
is_pinned BOOLEAN DEFAULT false, -- 是否置顶
|
||
ip_address INET,
|
||
user_agent TEXT,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- 5. 评论点赞表
|
||
CREATE TABLE IF NOT EXISTS ak_comment_likes (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
comment_id UUID REFERENCES ak_content_comments(id) ON DELETE CASCADE,
|
||
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
UNIQUE(comment_id, user_id) -- 防止重复点赞
|
||
);
|
||
|
||
-- 6. 媒体播放记录表（支持视频、音频）
|
||
CREATE TABLE IF NOT EXISTS ak_video_play_records (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
content_id UUID REFERENCES ak_contents(id) ON DELETE CASCADE,
|
||
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
play_position DECIMAL(10,3) DEFAULT 0, -- 播放位置（秒）
|
||
play_duration DECIMAL(10,3) DEFAULT 0, -- 播放时长（秒）
|
||
play_percentage DECIMAL(5,2) DEFAULT 0, -- 播放进度百分比
|
||
is_completed BOOLEAN DEFAULT false, -- 是否播放完成
|
||
device_type VARCHAR(20), -- 设备类型
|
||
resolution VARCHAR(10), -- 播放分辨率（视频专用）
|
||
quality VARCHAR(10), -- 播放质量（音频/视频）
|
||
play_speed DECIMAL(3,2) DEFAULT 1.0, -- 播放速度
|
||
volume INTEGER DEFAULT 100, -- 音量（0-100）
|
||
media_type VARCHAR(10), -- 媒体类型：video, audio
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- 7. 弹幕举报表
|
||
CREATE TABLE IF NOT EXISTS ak_danmu_reports (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
danmu_id UUID REFERENCES ak_video_danmakus(id) ON DELETE CASCADE,
|
||
reporter_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
report_type VARCHAR(20) NOT NULL CHECK (report_type IN ('spam', 'inappropriate', 'harassment', 'advertising')),
|
||
report_reason TEXT,
|
||
status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processed', 'rejected')),
|
||
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- 8. 内容统计表（缓存统计数据）
|
||
CREATE TABLE IF NOT EXISTS ak_content_statistics (
|
||
content_id UUID PRIMARY KEY REFERENCES ak_contents(id) ON DELETE CASCADE,
|
||
view_count INTEGER DEFAULT 0,
|
||
like_count INTEGER DEFAULT 0,
|
||
favorite_count INTEGER DEFAULT 0,
|
||
share_count INTEGER DEFAULT 0,
|
||
comment_count INTEGER DEFAULT 0,
|
||
danmu_count INTEGER DEFAULT 0, -- 弹幕数量（视频/音频专用）
|
||
play_completion_rate DECIMAL(5,2) DEFAULT 0, -- 播放完成率（视频/音频专用）
|
||
average_play_duration DECIMAL(10,3) DEFAULT 0, -- 平均播放时长（视频/音频专用）
|
||
download_count INTEGER DEFAULT 0, -- 下载次数
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- 9. 图片标签表（用于图片分类和搜索）
|
||
CREATE TABLE IF NOT EXISTS ak_image_tags (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
content_id UUID REFERENCES ak_contents(id) ON DELETE CASCADE,
|
||
tag_name VARCHAR(50) NOT NULL,
|
||
tag_type VARCHAR(20) DEFAULT 'user' CHECK (tag_type IN ('user', 'auto', 'ai')), -- 标签来源：用户添加、自动识别、AI生成
|
||
confidence DECIMAL(3,2), -- 置信度（0-1），适用于AI标签
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
UNIQUE(content_id, tag_name)
|
||
);
|
||
|
||
-- 10. 图片浏览记录表
|
||
CREATE TABLE IF NOT EXISTS ak_image_view_records (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
content_id UUID REFERENCES ak_contents(id) ON DELETE CASCADE,
|
||
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
view_duration INTEGER DEFAULT 0, -- 浏览时长（秒）
|
||
zoom_level DECIMAL(4,2) DEFAULT 1.0, -- 缩放级别
|
||
device_type VARCHAR(20), -- 设备类型
|
||
screen_resolution VARCHAR(20), -- 屏幕分辨率
|
||
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- ================================
|
||
-- 索引优化
|
||
-- ================================
|
||
|
||
-- 内容表索引
|
||
CREATE INDEX IF NOT EXISTS idx_contents_type ON ak_contents(content_type);
|
||
CREATE INDEX IF NOT EXISTS idx_contents_type_published ON ak_contents(content_type, published_at DESC);
|
||
-- 图集索引（使用 GIN 索引支持 JSONB 查询）
|
||
CREATE INDEX IF NOT EXISTS idx_contents_images_gin ON ak_contents USING GIN (images);
|
||
|
||
-- 弹幕表索引
|
||
CREATE INDEX IF NOT EXISTS idx_danmakus_content_time ON ak_video_danmakus(content_id, time_point);
|
||
CREATE INDEX IF NOT EXISTS idx_danmakus_user ON ak_video_danmakus(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_danmakus_status ON ak_video_danmakus(status);
|
||
|
||
-- 用户交互表索引
|
||
CREATE INDEX IF NOT EXISTS idx_interactions_user_type ON ak_user_interactions(user_id, interaction_type);
|
||
CREATE INDEX IF NOT EXISTS idx_interactions_content_type ON ak_user_interactions(content_id, interaction_type);
|
||
|
||
-- 评论表索引
|
||
CREATE INDEX IF NOT EXISTS idx_comments_content ON ak_content_comments(content_id, status, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_comments_parent ON ak_content_comments(parent_id);
|
||
CREATE INDEX IF NOT EXISTS idx_comments_user ON ak_content_comments(user_id);
|
||
|
||
-- 播放记录表索引
|
||
CREATE INDEX IF NOT EXISTS idx_play_records_user_content ON ak_video_play_records(user_id, content_id);
|
||
CREATE INDEX IF NOT EXISTS idx_play_records_content_time ON ak_video_play_records(content_id, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_play_records_media_type ON ak_video_play_records(media_type);
|
||
|
||
-- 图片标签表索引
|
||
CREATE INDEX IF NOT EXISTS idx_image_tags_content ON ak_image_tags(content_id);
|
||
CREATE INDEX IF NOT EXISTS idx_image_tags_name ON ak_image_tags(tag_name);
|
||
CREATE INDEX IF NOT EXISTS idx_image_tags_type ON ak_image_tags(tag_type);
|
||
|
||
-- 图片浏览记录表索引
|
||
CREATE INDEX IF NOT EXISTS idx_image_views_user_content ON ak_image_view_records(user_id, content_id);
|
||
CREATE INDEX IF NOT EXISTS idx_image_views_content_time ON ak_image_view_records(content_id, created_at DESC);
|
||
|
||
-- ================================
|
||
-- 触发器和函数
|
||
-- ================================
|
||
|
||
-- 更新评论数量的触发器
|
||
CREATE OR REPLACE FUNCTION update_comment_counts()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
IF TG_OP = 'INSERT' THEN
|
||
-- 更新内容统计
|
||
INSERT INTO ak_content_statistics (content_id, comment_count)
|
||
VALUES (NEW.content_id, 1)
|
||
ON CONFLICT (content_id)
|
||
DO UPDATE SET
|
||
comment_count = ak_content_statistics.comment_count + 1,
|
||
updated_at = NOW();
|
||
|
||
-- 更新父评论回复数
|
||
IF NEW.parent_id IS NOT NULL THEN
|
||
UPDATE ak_content_comments
|
||
SET reply_count = reply_count + 1
|
||
WHERE id = NEW.parent_id;
|
||
END IF;
|
||
|
||
ELSIF TG_OP = 'DELETE' THEN
|
||
-- 更新内容统计
|
||
UPDATE ak_content_statistics
|
||
SET comment_count = GREATEST(0, comment_count - 1),
|
||
updated_at = NOW()
|
||
WHERE content_id = OLD.content_id;
|
||
|
||
-- 更新父评论回复数
|
||
IF OLD.parent_id IS NOT NULL THEN
|
||
UPDATE ak_content_comments
|
||
SET reply_count = GREATEST(0, reply_count - 1)
|
||
WHERE id = OLD.parent_id;
|
||
END IF;
|
||
END IF;
|
||
|
||
RETURN NULL;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE TRIGGER tr_comment_counts
|
||
AFTER INSERT OR DELETE ON ak_content_comments
|
||
FOR EACH ROW EXECUTE FUNCTION update_comment_counts();
|
||
|
||
-- 更新弹幕数量的触发器
|
||
CREATE OR REPLACE FUNCTION update_danmu_counts()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
IF TG_OP = 'INSERT' THEN
|
||
INSERT INTO ak_content_statistics (content_id, danmu_count)
|
||
VALUES (NEW.content_id, 1)
|
||
ON CONFLICT (content_id)
|
||
DO UPDATE SET
|
||
danmu_count = ak_content_statistics.danmu_count + 1,
|
||
updated_at = NOW();
|
||
ELSIF TG_OP = 'DELETE' THEN
|
||
UPDATE ak_content_statistics
|
||
SET danmu_count = GREATEST(0, danmu_count - 1),
|
||
updated_at = NOW()
|
||
WHERE content_id = OLD.content_id;
|
||
END IF;
|
||
|
||
RETURN NULL;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE TRIGGER tr_danmu_counts
|
||
AFTER INSERT OR DELETE ON ak_video_danmakus
|
||
FOR EACH ROW EXECUTE FUNCTION update_danmu_counts();
|
||
|
||
-- 更新用户交互统计的触发器
|
||
CREATE OR REPLACE FUNCTION update_interaction_stats()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
IF TG_OP = 'INSERT' THEN
|
||
INSERT INTO ak_content_statistics (content_id)
|
||
VALUES (NEW.content_id)
|
||
ON CONFLICT (content_id) DO NOTHING;
|
||
|
||
UPDATE ak_content_statistics
|
||
SET
|
||
view_count = CASE WHEN NEW.interaction_type = 'view' THEN view_count + 1 ELSE view_count END,
|
||
like_count = CASE WHEN NEW.interaction_type = 'like' THEN like_count + 1 ELSE like_count END,
|
||
favorite_count = CASE WHEN NEW.interaction_type = 'favorite' THEN favorite_count + 1 ELSE favorite_count END,
|
||
share_count = CASE WHEN NEW.interaction_type = 'share' THEN share_count + 1 ELSE share_count END,
|
||
updated_at = NOW()
|
||
WHERE content_id = NEW.content_id;
|
||
|
||
ELSIF TG_OP = 'DELETE' THEN
|
||
UPDATE ak_content_statistics
|
||
SET
|
||
view_count = CASE WHEN OLD.interaction_type = 'view' THEN GREATEST(0, view_count - 1) ELSE view_count END,
|
||
like_count = CASE WHEN OLD.interaction_type = 'like' THEN GREATEST(0, like_count - 1) ELSE like_count END,
|
||
favorite_count = CASE WHEN OLD.interaction_type = 'favorite' THEN GREATEST(0, favorite_count - 1) ELSE favorite_count END,
|
||
share_count = CASE WHEN OLD.interaction_type = 'share' THEN GREATEST(0, share_count - 1) ELSE share_count END,
|
||
updated_at = NOW()
|
||
WHERE content_id = OLD.content_id;
|
||
END IF;
|
||
|
||
RETURN NULL;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE TRIGGER tr_interaction_stats
|
||
AFTER INSERT OR DELETE ON ak_user_interactions
|
||
FOR EACH ROW EXECUTE FUNCTION update_interaction_stats();
|
||
|
||
-- ================================
|
||
-- RLS 安全策略
|
||
-- ================================
|
||
|
||
-- 启用 RLS
|
||
ALTER TABLE ak_video_danmakus ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE ak_user_interactions ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE ak_content_comments ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE ak_comment_likes ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE ak_video_play_records ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE ak_danmu_reports ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE ak_image_tags ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE ak_image_view_records ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- 弹幕策略
|
||
CREATE POLICY "Public can view danmakus" ON ak_video_danmakus
|
||
FOR SELECT USING (status = 'active' AND is_visible = true);
|
||
|
||
CREATE POLICY "Users can insert own danmakus" ON ak_video_danmakus
|
||
FOR INSERT WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can update own danmakus" ON ak_video_danmakus
|
||
FOR UPDATE USING (auth.uid() = user_id);
|
||
|
||
-- 用户交互策略
|
||
CREATE POLICY "Users can view own interactions" ON ak_user_interactions
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can insert own interactions" ON ak_user_interactions
|
||
FOR INSERT WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can delete own interactions" ON ak_user_interactions
|
||
FOR DELETE USING (auth.uid() = user_id);
|
||
|
||
-- 评论策略
|
||
CREATE POLICY "Public can view active comments" ON ak_content_comments
|
||
FOR SELECT USING (status = 'active');
|
||
|
||
CREATE POLICY "Users can insert own comments" ON ak_content_comments
|
||
FOR INSERT WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can update own comments" ON ak_content_comments
|
||
FOR UPDATE USING (auth.uid() = user_id);
|
||
|
||
-- 播放记录策略
|
||
CREATE POLICY "Users can view own play records" ON ak_video_play_records
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can insert own play records" ON ak_video_play_records
|
||
FOR INSERT WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can update own play records" ON ak_video_play_records
|
||
FOR UPDATE USING (auth.uid() = user_id);
|
||
|
||
-- 图片标签策略
|
||
CREATE POLICY "Public can view image tags" ON ak_image_tags
|
||
FOR SELECT USING (true);
|
||
|
||
CREATE POLICY "Users can add image tags" ON ak_image_tags
|
||
FOR INSERT WITH CHECK (true); -- 允许所有认证用户添加标签
|
||
|
||
-- 图片浏览记录策略
|
||
CREATE POLICY "Users can view own image records" ON ak_image_view_records
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can insert own image records" ON ak_image_view_records
|
||
FOR INSERT WITH CHECK (auth.uid() = user_id);
|
||
|
||
-- ================================
|
||
-- 查询视图
|
||
-- ================================
|
||
|
||
-- 多媒体内容详情视图
|
||
CREATE OR REPLACE VIEW vw_media_content_detail AS
|
||
SELECT
|
||
c.id,
|
||
c.content_type,
|
||
-- 视频字段
|
||
c.video_url,
|
||
c.video_duration,
|
||
c.video_poster,
|
||
c.video_width,
|
||
c.video_height,
|
||
c.video_size,
|
||
c.video_format,
|
||
c.video_quality,
|
||
-- 音频字段
|
||
c.audio_url,
|
||
c.audio_duration,
|
||
c.audio_size,
|
||
c.audio_format,
|
||
c.audio_bitrate,
|
||
c.audio_sample_rate,
|
||
c.audio_cover,
|
||
-- 图片字段
|
||
c.image_url,
|
||
c.image_width,
|
||
c.image_height,
|
||
c.image_size,
|
||
c.image_format,
|
||
c.image_quality,
|
||
c.image_alt_text,
|
||
c.images,
|
||
-- 通用字段
|
||
c.allow_danmu,
|
||
c.allow_download,
|
||
c.media_metadata,
|
||
-- 统计字段
|
||
COALESCE(s.view_count, 0) as view_count,
|
||
COALESCE(s.like_count, 0) as like_count,
|
||
COALESCE(s.favorite_count, 0) as favorite_count,
|
||
COALESCE(s.share_count, 0) as share_count,
|
||
COALESCE(s.comment_count, 0) as comment_count,
|
||
COALESCE(s.danmu_count, 0) as danmu_count,
|
||
COALESCE(s.play_completion_rate, 0) as play_completion_rate,
|
||
COALESCE(s.average_play_duration, 0) as average_play_duration,
|
||
COALESCE(s.download_count, 0) as download_count,
|
||
-- 检查当前用户是否已点赞、收藏
|
||
EXISTS(SELECT 1 FROM ak_user_interactions ui WHERE ui.content_id = c.id AND ui.user_id = auth.uid() AND ui.interaction_type = 'like') as is_liked,
|
||
EXISTS(SELECT 1 FROM ak_user_interactions ui WHERE ui.content_id = c.id AND ui.user_id = auth.uid() AND ui.interaction_type = 'favorite') as is_favorited
|
||
FROM ak_contents c
|
||
LEFT JOIN ak_content_statistics s ON c.id = s.content_id;
|
||
|
||
-- 视频内容详情视图（向后兼容）
|
||
CREATE OR REPLACE VIEW vw_video_content_detail AS
|
||
SELECT * FROM vw_media_content_detail
|
||
WHERE content_type = 'video';
|
||
|
||
-- 音频内容详情视图
|
||
CREATE OR REPLACE VIEW vw_audio_content_detail AS
|
||
SELECT * FROM vw_media_content_detail
|
||
WHERE content_type = 'audio';
|
||
|
||
-- 图片内容详情视图
|
||
CREATE OR REPLACE VIEW vw_image_content_detail AS
|
||
SELECT
|
||
c.id,
|
||
c.content_type,
|
||
c.image_url,
|
||
c.image_width,
|
||
c.image_height,
|
||
c.image_size,
|
||
c.image_format,
|
||
c.image_quality,
|
||
c.image_alt_text,
|
||
c.images,
|
||
c.allow_download,
|
||
c.media_metadata,
|
||
COALESCE(s.view_count, 0) as view_count,
|
||
COALESCE(s.like_count, 0) as like_count,
|
||
COALESCE(s.favorite_count, 0) as favorite_count,
|
||
COALESCE(s.share_count, 0) as share_count,
|
||
COALESCE(s.comment_count, 0) as comment_count,
|
||
COALESCE(s.download_count, 0) as download_count,
|
||
-- 图片标签
|
||
ARRAY_AGG(DISTINCT it.tag_name) FILTER (WHERE it.tag_name IS NOT NULL) as tags,
|
||
-- 检查当前用户是否已点赞、收藏
|
||
EXISTS(SELECT 1 FROM ak_user_interactions ui WHERE ui.content_id = c.id AND ui.user_id = auth.uid() AND ui.interaction_type = 'like') as is_liked,
|
||
EXISTS(SELECT 1 FROM ak_user_interactions ui WHERE ui.content_id = c.id AND ui.user_id = auth.uid() AND ui.interaction_type = 'favorite') as is_favorited
|
||
FROM ak_contents c
|
||
LEFT JOIN ak_content_statistics s ON c.id = s.content_id
|
||
LEFT JOIN ak_image_tags it ON c.id = it.content_id
|
||
WHERE c.content_type = 'image'
|
||
GROUP BY c.id, c.content_type, c.image_url, c.image_width, c.image_height, c.image_size, c.image_format, c.image_quality, c.image_alt_text, c.images, c.allow_download, c.media_metadata, s.view_count, s.like_count, s.favorite_count, s.share_count, s.comment_count, s.download_count;
|
||
|
||
-- 弹幕列表视图（支持视频和音频）
|
||
CREATE OR REPLACE VIEW vw_video_danmakus AS
|
||
SELECT
|
||
d.id,
|
||
d.content_id,
|
||
d.user_id,
|
||
d.user_name,
|
||
d.text,
|
||
d.time_point,
|
||
d.color,
|
||
d.font_size,
|
||
d.position_type,
|
||
d.speed,
|
||
d.is_visible,
|
||
d.status,
|
||
d.created_at,
|
||
d.updated_at,
|
||
c.content_type,
|
||
-- 显示用户敏感信息（仅对自己的弹幕）
|
||
CASE WHEN d.user_id = auth.uid() THEN d.ip_address ELSE NULL END as ip_address,
|
||
CASE WHEN d.user_id = auth.uid() THEN d.user_agent ELSE NULL END as user_agent
|
||
FROM ak_video_danmakus d
|
||
JOIN ak_contents c ON d.content_id = c.id
|
||
WHERE d.status = 'active' AND d.is_visible = true
|
||
ORDER BY d.time_point ASC;
|
||
|
||
-- 评论树形视图
|
||
CREATE OR REPLACE VIEW vw_content_comments AS
|
||
WITH RECURSIVE comment_tree AS (
|
||
-- 主评论
|
||
SELECT
|
||
c.*,
|
||
0 as level,
|
||
ARRAY[c.created_at] as sort_path,
|
||
EXISTS(SELECT 1 FROM ak_comment_likes cl WHERE cl.comment_id = c.id AND cl.user_id = auth.uid()) as is_liked_by_user
|
||
FROM ak_content_comments c
|
||
WHERE c.parent_id IS NULL AND c.status = 'active'
|
||
|
||
UNION ALL
|
||
|
||
-- 子评论
|
||
SELECT
|
||
c.*,
|
||
ct.level + 1,
|
||
ct.sort_path || c.created_at,
|
||
EXISTS(SELECT 1 FROM ak_comment_likes cl WHERE cl.comment_id = c.id AND cl.user_id = auth.uid()) as is_liked_by_user
|
||
FROM ak_content_comments c
|
||
JOIN comment_tree ct ON c.parent_id = ct.id
|
||
WHERE c.status = 'active' AND ct.level < 3 -- 限制评论层级
|
||
)
|
||
SELECT * FROM comment_tree ORDER BY sort_path;
|
||
|
||
-- 热门标签视图（图片专用）
|
||
CREATE OR REPLACE VIEW vw_popular_image_tags AS
|
||
SELECT
|
||
tag_name,
|
||
COUNT(*) as usage_count,
|
||
tag_type,
|
||
AVG(confidence) FILTER (WHERE confidence IS NOT NULL) as avg_confidence
|
||
FROM ak_image_tags
|
||
GROUP BY tag_name, tag_type
|
||
ORDER BY usage_count DESC;
|
||
|
||
-- 用户媒体消费统计视图
|
||
CREATE OR REPLACE VIEW vw_user_media_stats AS
|
||
SELECT
|
||
ui.user_id,
|
||
COUNT(CASE WHEN c.content_type = 'video' THEN 1 END) as videos_watched,
|
||
COUNT(CASE WHEN c.content_type = 'audio' THEN 1 END) as audios_listened,
|
||
COUNT(CASE WHEN c.content_type = 'image' THEN 1 END) as images_viewed,
|
||
SUM(CASE WHEN c.content_type IN ('video', 'audio') THEN vpr.play_duration ELSE 0 END) as total_play_time,
|
||
AVG(CASE WHEN c.content_type IN ('video', 'audio') THEN vpr.play_percentage ELSE NULL END) as avg_completion_rate
|
||
FROM ak_user_interactions ui
|
||
LEFT JOIN ak_contents c ON ui.content_id = c.id
|
||
LEFT JOIN ak_video_play_records vpr ON ui.content_id = vpr.content_id AND ui.user_id = vpr.user_id
|
||
WHERE ui.interaction_type = 'view'
|
||
GROUP BY ui.user_id;
|
||
|
||
-- ================================
|
||
-- 兼容性检查和视图优化
|
||
-- ================================
|
||
|
||
-- 创建一个更安全的视图，只使用我们确定存在的列
|
||
-- 如果您的 ak_contents 表有更多列（如 title, content, excerpt 等），
|
||
-- 可以在部署后手动添加到视图中
|
||
|
||
-- 检查表结构的辅助函数
|
||
CREATE OR REPLACE FUNCTION check_column_exists(table_name TEXT, column_name TEXT)
|
||
RETURNS BOOLEAN
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
BEGIN
|
||
RETURN EXISTS (
|
||
SELECT 1
|
||
FROM information_schema.columns
|
||
WHERE table_name = lower(table_name)
|
||
AND column_name = lower(column_name)
|
||
AND table_schema = current_schema()
|
||
);
|
||
END;
|
||
$$;
|
||
|
||
-- ================================
|
||
-- 示例 RPC 函数：带内容类型校验的数据插入
|
||
-- ================================
|
||
|
||
-- 插入弹幕（带内容类型校验）
|
||
CREATE OR REPLACE FUNCTION insert_danmu_with_validation(
|
||
p_content_id UUID,
|
||
p_content TEXT,
|
||
p_time_position DECIMAL,
|
||
p_color VARCHAR DEFAULT '#FFFFFF',
|
||
p_font_size INTEGER DEFAULT 16,
|
||
p_position_type VARCHAR DEFAULT 'scroll'
|
||
)
|
||
RETURNS UUID
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
AS $$
|
||
DECLARE
|
||
v_content_type VARCHAR(20);
|
||
v_danmu_id UUID;
|
||
BEGIN
|
||
-- 检查内容类型
|
||
SELECT content_type INTO v_content_type
|
||
FROM ak_contents
|
||
WHERE id = p_content_id;
|
||
|
||
IF v_content_type IS NULL THEN
|
||
RAISE EXCEPTION '内容不存在';
|
||
END IF;
|
||
|
||
IF v_content_type NOT IN ('video', 'audio') THEN
|
||
RAISE EXCEPTION '只有视频和音频内容支持弹幕功能';
|
||
END IF;
|
||
|
||
-- 插入弹幕
|
||
INSERT INTO ak_video_danmakus (
|
||
content_id, user_id, user_name, text, time_point,
|
||
color, font_size, position_type
|
||
) VALUES (
|
||
p_content_id, auth.uid(),
|
||
COALESCE((SELECT email FROM auth.users WHERE id = auth.uid()), 'Anonymous'),
|
||
p_content, p_time_position,
|
||
p_color, p_font_size, p_position_type
|
||
) RETURNING id INTO v_danmu_id;
|
||
|
||
RETURN v_danmu_id;
|
||
END;
|
||
$$;
|
||
|
||
-- 插入图片标签（带内容类型校验）
|
||
CREATE OR REPLACE FUNCTION insert_image_tag_with_validation(
|
||
p_content_id UUID,
|
||
p_tag_name VARCHAR,
|
||
p_tag_type VARCHAR DEFAULT 'user',
|
||
p_confidence DECIMAL DEFAULT NULL
|
||
)
|
||
RETURNS UUID
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
AS $$
|
||
DECLARE
|
||
v_content_type VARCHAR(20);
|
||
v_tag_id UUID;
|
||
BEGIN
|
||
-- 检查内容类型
|
||
SELECT content_type INTO v_content_type
|
||
FROM ak_contents
|
||
WHERE id = p_content_id;
|
||
|
||
IF v_content_type IS NULL THEN
|
||
RAISE EXCEPTION '内容不存在';
|
||
END IF;
|
||
|
||
IF v_content_type != 'image' THEN
|
||
RAISE EXCEPTION '只有图片内容支持标签功能';
|
||
END IF;
|
||
|
||
-- 插入标签
|
||
INSERT INTO ak_image_tags (
|
||
content_id, tag_name, tag_type, confidence
|
||
) VALUES (
|
||
p_content_id, p_tag_name, p_tag_type, p_confidence
|
||
) RETURNING id INTO v_tag_id;
|
||
|
||
RETURN v_tag_id;
|
||
END;
|
||
$$;
|
||
|
||
-- 记录播放进度（带内容类型校验）
|
||
CREATE OR REPLACE FUNCTION record_play_progress_with_validation(
|
||
p_content_id UUID,
|
||
p_play_position DECIMAL,
|
||
p_play_duration DECIMAL DEFAULT 0,
|
||
p_device_type VARCHAR DEFAULT NULL,
|
||
p_resolution VARCHAR DEFAULT NULL,
|
||
p_quality VARCHAR DEFAULT NULL
|
||
)
|
||
RETURNS UUID
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
AS $$
|
||
DECLARE
|
||
v_content_type VARCHAR(20);
|
||
v_record_id UUID;
|
||
v_play_percentage DECIMAL(5,2);
|
||
v_total_duration DECIMAL(10,3);
|
||
BEGIN
|
||
-- 检查内容类型和获取时长
|
||
SELECT
|
||
content_type,
|
||
CASE
|
||
WHEN content_type = 'video' THEN video_duration
|
||
WHEN content_type = 'audio' THEN audio_duration
|
||
ELSE NULL
|
||
END
|
||
INTO v_content_type, v_total_duration
|
||
FROM ak_contents
|
||
WHERE id = p_content_id;
|
||
|
||
IF v_content_type IS NULL THEN
|
||
RAISE EXCEPTION '内容不存在';
|
||
END IF;
|
||
|
||
IF v_content_type NOT IN ('video', 'audio') THEN
|
||
RAISE EXCEPTION '只有视频和音频内容支持播放记录';
|
||
END IF;
|
||
|
||
-- 计算播放百分比
|
||
IF v_total_duration IS NOT NULL AND v_total_duration > 0 THEN
|
||
v_play_percentage = (p_play_position / v_total_duration) * 100;
|
||
v_play_percentage = LEAST(v_play_percentage, 100); -- 限制最大100%
|
||
ELSE
|
||
v_play_percentage = 0;
|
||
END IF;
|
||
|
||
-- 插入或更新播放记录
|
||
INSERT INTO ak_video_play_records (
|
||
content_id, user_id, play_position, play_duration,
|
||
play_percentage, is_completed, device_type, resolution, quality, media_type
|
||
) VALUES (
|
||
p_content_id, auth.uid(), p_play_position, p_play_duration,
|
||
v_play_percentage, (v_play_percentage >= 95), p_device_type,
|
||
p_resolution, p_quality, v_content_type
|
||
)
|
||
ON CONFLICT (content_id, user_id)
|
||
DO UPDATE SET
|
||
play_position = EXCLUDED.play_position,
|
||
play_duration = EXCLUDED.play_duration,
|
||
play_percentage = EXCLUDED.play_percentage,
|
||
is_completed = EXCLUDED.is_completed,
|
||
updated_at = NOW()
|
||
RETURNING id INTO v_record_id;
|
||
|
||
RETURN v_record_id;
|
||
END;
|
||
$$;
|
||
|
||
-- ================================
|
||
-- 模拟数据插入
|
||
-- ================================
|
||
|
||
-- 首先插入5个多媒体内容到 ak_contents 表
|
||
INSERT INTO ak_contents (
|
||
id, title, content, summary, status, content_type, original_language, quality_score,
|
||
video_url, video_duration, video_poster, video_width, video_height,
|
||
video_size, video_format, video_quality, audio_url, audio_duration, audio_size,
|
||
audio_format, audio_bitrate, audio_sample_rate, audio_cover, image_url, image_width,
|
||
image_height, image_size, image_format, image_quality, image_alt_text, images,
|
||
allow_danmu, allow_download, media_metadata, published_at, updated_at
|
||
) VALUES
|
||
-- 视频内容1
|
||
(
|
||
gen_random_uuid(),
|
||
'星际征途：未来科幻大片',
|
||
'一部震撼人心的科幻电影，讲述了人类在星际时代的冒险故事。导演张导演运用先进的特效技术，为观众呈现了一个令人惊叹的未来世界。这部影片不仅具有视觉冲击力，更在情感层面触动人心，是一部值得反复观看的科幻佳作。',
|
||
'震撼人心的科幻电影，展现未来世界的冒险故事',
|
||
'published',
|
||
'video',
|
||
'zh-CN',
|
||
8.5,
|
||
'https://example.com/videos/sample-video-1.mp4', 1800,
|
||
'https://example.com/posters/video-1-poster.jpg', 1920, 1080,
|
||
524288000, 'mp4', '1080p',
|
||
NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
||
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
||
true, false,
|
||
'{"director": "张导演", "genre": "科幻", "rating": 8.5, "year": 2024, "subtitle_languages": ["zh-CN", "en-US"], "tags": ["科幻", "冒险", "未来", "特效"]}',
|
||
NOW() - INTERVAL '2 days', NOW()
|
||
),
|
||
-- 视频内容2
|
||
(
|
||
gen_random_uuid(),
|
||
'血战江湖：经典动作片',
|
||
'李导演执导的动作大片，以精彩的打斗场面和紧张的剧情著称。影片采用实拍与特效相结合的方式，为观众带来视觉盛宴。每一个动作场面都经过精心设计，展现了中国功夫的精髓，是动作片爱好者不可错过的经典之作。',
|
||
'李导演执导的动作大片，精彩打斗场面引人入胜',
|
||
'published',
|
||
'video',
|
||
'zh-CN',
|
||
9.2,
|
||
'https://example.com/videos/sample-video-2.mp4', 3600,
|
||
'https://example.com/posters/video-2-poster.jpg', 1920, 1080,
|
||
1048576000, 'mp4', '1080p',
|
||
NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
||
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
||
true, true,
|
||
'{"director": "李导演", "genre": "动作", "rating": 9.2, "year": 2024, "subtitle_languages": ["zh-CN", "en-US", "ja-JP"], "tags": ["动作", "功夫", "江湖", "经典"]}',
|
||
NOW() - INTERVAL '1 day', NOW()
|
||
),
|
||
-- 音频内容1
|
||
(
|
||
gen_random_uuid(),
|
||
'夜空中最亮的星 - 王歌手',
|
||
'王歌手的最新单曲，旋律优美动听，歌词深情感人。这首歌曲融合了流行与民谣元素，展现了歌手成熟的音乐风格。歌曲以夜空为背景，表达了对理想和爱情的追求，是一首充满正能量的励志歌曲。',
|
||
'王歌手最新单曲，旋律优美歌词深情',
|
||
'published',
|
||
'audio',
|
||
'zh-CN',
|
||
8.8,
|
||
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
||
'https://example.com/audios/sample-audio-1.mp3', 240,
|
||
10485760, 'mp3', 320, 44100,
|
||
'https://example.com/covers/audio-1-cover.jpg',
|
||
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
||
true, false,
|
||
'{"artist": "王歌手", "album": "热门单曲", "genre": "流行", "year": 2024, "lyrics": true, "tags": ["流行", "励志", "爱情", "民谣"]}',
|
||
NOW() - INTERVAL '3 days', NOW()
|
||
),
|
||
-- 图片内容1
|
||
(
|
||
gen_random_uuid(),
|
||
'杭州西湖美景摄影作品',
|
||
'陈摄影师使用Canon EOS R5相机拍摄的西湖美景。照片完美捕捉了西湖的宁静与美丽，展现了中国古典园林的魅力。作品运用了经典的构图技巧，光影效果处理精美，是风景摄影的典型代表作品。',
|
||
'陈摄影师拍摄的西湖美景，展现古典园林魅力',
|
||
'published',
|
||
'image',
|
||
'zh-CN',
|
||
9.0,
|
||
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
||
NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
||
'https://example.com/images/sample-image-1.jpg', 2048, 1536,
|
||
2097152, 'jpg', 'original', '美丽的风景照片',
|
||
NULL,
|
||
false, true,
|
||
'{"photographer": "陈摄影师", "location": "杭州西湖", "camera": "Canon EOS R5", "taken_at": "2024-06-15", "tags": ["风景", "西湖", "摄影", "古典"]}',
|
||
NOW() - INTERVAL '5 days', NOW()
|
||
),
|
||
-- 图集内容1
|
||
(
|
||
gen_random_uuid(),
|
||
'云南旅行摄影图集',
|
||
'李摄影师云南之行的精彩摄影作品集。从海边日落到山顶风光，再到城市夜景，每一张照片都记录了云南的美丽瞬间。这个图集展现了云南的多样性，从自然风光到人文景观，是一次完整的视觉旅行体验。',
|
||
'李摄影师云南旅行摄影作品，记录美丽瞬间',
|
||
'published',
|
||
'image',
|
||
'zh-CN',
|
||
8.7,
|
||
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
||
NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
||
'https://example.com/images/gallery-1-cover.jpg', 1920, 1080,
|
||
1572864, 'jpg', 'original', '旅游图集封面',
|
||
'[
|
||
{
|
||
"url": "https://example.com/images/gallery-1-1.jpg",
|
||
"width": 1920, "height": 1080, "size": 1572864,
|
||
"alt": "海边日落", "order": 1, "description": "云南大理洱海日落美景"
|
||
},
|
||
{
|
||
"url": "https://example.com/images/gallery-1-2.jpg",
|
||
"width": 1920, "height": 1080, "size": 1310720,
|
||
"alt": "山顶风光", "order": 2, "description": "玉龙雪山山顶壮丽风光"
|
||
},
|
||
{
|
||
"url": "https://example.com/images/gallery-1-3.jpg",
|
||
"width": 1920, "height": 1080, "size": 1835008,
|
||
"alt": "城市夜景", "order": 3, "description": "昆明市区夜景璀璨灯火"
|
||
}
|
||
]'::jsonb,
|
||
false, true,
|
||
'{"photographer": "李摄影师", "trip": "云南旅行", "total_images": 3, "created_date": "2024-06-20", "tags": ["旅行", "云南", "图集", "风光"]}',
|
||
NOW() - INTERVAL '4 days', NOW()
|
||
);
|
||
|
||
-- 获取插入的内容ID（用于后续插入统计数据）
|
||
WITH inserted_contents AS (
|
||
SELECT id, content_type FROM ak_contents
|
||
WHERE created_at >= NOW() - INTERVAL '1 hour'
|
||
ORDER BY created_at DESC
|
||
LIMIT 5
|
||
)
|
||
-- 为这些内容初始化统计数据
|
||
INSERT INTO ak_content_statistics (
|
||
content_id, view_count, like_count, favorite_count, share_count,
|
||
comment_count, danmu_count, play_completion_rate, average_play_duration, download_count
|
||
)
|
||
SELECT
|
||
id,
|
||
FLOOR(RANDOM() * 1000 + 100)::INTEGER, -- 100-1099 views
|
||
FLOOR(RANDOM() * 200 + 10)::INTEGER, -- 10-209 likes
|
||
FLOOR(RANDOM() * 50 + 5)::INTEGER, -- 5-54 favorites
|
||
FLOOR(RANDOM() * 30 + 2)::INTEGER, -- 2-31 shares
|
||
FLOOR(RANDOM() * 20 + 1)::INTEGER, -- 1-20 comments
|
||
CASE WHEN content_type IN ('video', 'audio') THEN FLOOR(RANDOM() * 100 + 10)::INTEGER ELSE 0 END, -- 弹幕数
|
||
CASE WHEN content_type IN ('video', 'audio') THEN ROUND((RANDOM() * 30 + 70)::NUMERIC, 2) ELSE 0 END, -- 70-100% 完成率
|
||
CASE WHEN content_type IN ('video', 'audio') THEN ROUND((RANDOM() * 300 + 60)::NUMERIC, 3) ELSE 0 END, -- 平均播放时长
|
||
CASE WHEN content_type = 'image' THEN FLOOR(RANDOM() * 20 + 1)::INTEGER ELSE 0 END -- 下载次数
|
||
FROM inserted_contents;
|
||
|
||
-- 为图片内容添加标签
|
||
WITH image_contents AS (
|
||
SELECT id FROM ak_contents
|
||
WHERE content_type = 'image'
|
||
AND created_at >= NOW() - INTERVAL '1 hour'
|
||
LIMIT 2
|
||
),
|
||
image_with_row AS (
|
||
SELECT id, ROW_NUMBER() OVER (ORDER BY id) as rn FROM image_contents
|
||
),
|
||
image_tags AS (
|
||
SELECT
|
||
id,
|
||
CASE
|
||
WHEN rn = 1 THEN ARRAY['风景', '西湖', '自然', '摄影']
|
||
WHEN rn = 2 THEN ARRAY['旅游', '图集', '云南', '风光', '摄影']
|
||
END as tag_array
|
||
FROM image_with_row
|
||
)
|
||
INSERT INTO ak_image_tags (content_id, tag_name, tag_type, confidence)
|
||
SELECT
|
||
id,
|
||
tag_name,
|
||
'user',
|
||
NULL
|
||
FROM image_tags
|
||
CROSS JOIN LATERAL unnest(tag_array) AS tag_name;
|
||
|
||
-- 添加一些示例弹幕（仅为视频和音频内容）
|
||
WITH media_contents AS (
|
||
SELECT id, content_type FROM ak_contents
|
||
WHERE content_type IN ('video', 'audio')
|
||
AND created_at >= NOW() - INTERVAL '1 hour'
|
||
LIMIT 3
|
||
)
|
||
INSERT INTO ak_video_danmakus (
|
||
content_id, user_id, user_name, text, time_point, color, font_size, position_type, status
|
||
)
|
||
SELECT
|
||
id,
|
||
auth.uid(),
|
||
'演示用户',
|
||
CASE content_type
|
||
WHEN 'video' THEN
|
||
(ARRAY['太精彩了!', '画质真棒', '这个特效牛', '导演厉害', '期待续集'])[FLOOR(RANDOM() * 5 + 1)]
|
||
WHEN 'audio' THEN
|
||
(ARRAY['好听!', '单曲循环', '歌手唱功不错', '这首歌很有感觉', '音质很棒'])[FLOOR(RANDOM() * 5 + 1)]
|
||
END,
|
||
ROUND((RANDOM() * 300)::NUMERIC, 3), -- 随机时间点
|
||
(ARRAY['#FFFFFF', '#FF6B6B', '#4ECDC4', '#45B7D1', '#96CEB4'])[FLOOR(RANDOM() * 5 + 1)], -- 随机颜色
|
||
(ARRAY[16, 18, 20, 22, 24])[FLOOR(RANDOM() * 5 + 1)], -- 随机字体大小
|
||
(ARRAY['scroll', 'top', 'bottom'])[FLOOR(RANDOM() * 3 + 1)], -- 随机位置
|
||
'active'
|
||
FROM media_contents,
|
||
generate_series(1, 3) -- 每个媒体内容生成3条弹幕
|
||
WHERE id IS NOT NULL;
|
||
|
||
-- 添加一些示例评论
|
||
WITH all_contents AS (
|
||
SELECT id FROM ak_contents
|
||
WHERE created_at >= NOW() - INTERVAL '1 hour'
|
||
LIMIT 5
|
||
)
|
||
INSERT INTO ak_content_comments (
|
||
content_id, user_id, user_name, content, status
|
||
)
|
||
SELECT
|
||
id,
|
||
auth.uid(),
|
||
'评论用户',
|
||
(ARRAY[
|
||
'这个内容质量很高，值得推荐！',
|
||
'制作精良，很有意思。',
|
||
'感谢分享，学到了很多。',
|
||
'希望能看到更多这样的作品。',
|
||
'内容很棒，期待更新！'
|
||
])[FLOOR(RANDOM() * 5 + 1)],
|
||
'active'
|
||
FROM all_contents,
|
||
generate_series(1, 2) -- 每个内容生成2条评论
|
||
WHERE id IS NOT NULL;
|
||
|
||
-- 添加示例用户交互（点赞、收藏等）
|
||
WITH all_contents AS (
|
||
SELECT id FROM ak_contents
|
||
WHERE created_at >= NOW() - INTERVAL '1 hour'
|
||
LIMIT 5
|
||
)
|
||
INSERT INTO ak_user_interactions (
|
||
user_id, content_id, interaction_type, interaction_data
|
||
)
|
||
SELECT
|
||
auth.uid(),
|
||
id,
|
||
interaction_type,
|
||
CASE interaction_type
|
||
WHEN 'share' THEN jsonb_build_object('platform', 'weibo', 'shared_at', NOW())
|
||
WHEN 'favorite' THEN jsonb_build_object('folder', '我的收藏', 'added_at', NOW())
|
||
ELSE '{}'::jsonb
|
||
END
|
||
FROM all_contents
|
||
CROSS JOIN (
|
||
VALUES ('like'), ('favorite'), ('view'), ('share')
|
||
) AS interactions(interaction_type)
|
||
WHERE id IS NOT NULL;
|
||
|
||
-- 模拟数据插入完成
|
||
-- 已成功插入5个多媒体内容（2个视频、1个音频、2个图片/图集）
|
||
-- 包含完整的统计数据、标签、弹幕、评论和用户交互记录
|
||
|
||
-- 允许所有用户（包括 anon）插入播放记录
|
||
CREATE POLICY "Anon can insert play records" ON ak_video_play_records
|
||
FOR INSERT WITH CHECK (true);
|
||
|
||
-- 应用策略
|
||
ALTER TABLE ak_video_play_records ENABLE ROW LEVEL SECURITY;
|
||
-- 认证用户只能插入自己的记录
|
||
CREATE POLICY "Users can insert own play records" ON ak_video_play_records
|
||
FOR INSERT WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Anon can insert play records" ON ak_video_play_records
|
||
FOR INSERT WITH CHECK (
|
||
(auth.role() = 'anon' AND user_id = '00000000-0000-0000-0000-000000000000')
|
||
OR (auth.role() = 'anon' AND user_id IS NULL)
|
||
);
|
||
|
||
|