-- ===================================================================
-- ak_topics 多语言支持扩展
-- ===================================================================

-- 专题多语言翻译表
CREATE TABLE IF NOT EXISTS public.ak_topic_translations (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    topic_id uuid NOT NULL REFERENCES public.ak_topics(id) ON DELETE CASCADE,
    language_code VARCHAR(10) NOT NULL, -- 如 'zh-CN', 'en', 'ja', 'zh-TW'
    title VARCHAR(255) NOT NULL,
    description TEXT,
    meta_description TEXT,
    meta_keywords TEXT[],
    translation_method VARCHAR(32) DEFAULT 'ai', -- 'ai', 'human', 'hybrid'
    ai_provider VARCHAR(32), -- 'openai', 'google', 'baidu', 'custom'
    quality_score FLOAT,
    ai_confidence FLOAT,
    human_verified BOOLEAN DEFAULT false,
    human_verified_by uuid, -- 参考 ak_users 表
    human_verified_at TIMESTAMP WITH TIME ZONE,
    tokens_used INTEGER,
    processing_time_ms INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    UNIQUE(topic_id, language_code)
);

-- 专题翻译索引
CREATE INDEX IF NOT EXISTS idx_topic_translations_topic ON public.ak_topic_translations(topic_id);
CREATE INDEX IF NOT EXISTS idx_topic_translations_language ON public.ak_topic_translations(language_code);
CREATE INDEX IF NOT EXISTS idx_topic_translations_method ON public.ak_topic_translations(translation_method);
CREATE INDEX IF NOT EXISTS idx_topic_translations_quality ON public.ak_topic_translations(quality_score DESC);
CREATE INDEX IF NOT EXISTS idx_topic_translations_verified ON public.ak_topic_translations(human_verified);

COMMENT ON TABLE public.ak_topic_translations IS '专题多语言翻译表';
COMMENT ON COLUMN public.ak_topic_translations.language_code IS '语言代码，如zh-CN、en、ja、zh-TW';
COMMENT ON COLUMN public.ak_topic_translations.translation_method IS '翻译方式：ai=AI翻译，human=人工翻译，hybrid=混合翻译';
COMMENT ON COLUMN public.ak_topic_translations.quality_score IS '翻译质量评分(0-1)';

-- ===================================================================
-- ak_topic_contents 多语言支持扩展  
-- ===================================================================

-- 专题内容关联多语言翻译表
CREATE TABLE IF NOT EXISTS public.ak_topic_content_translations (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    topic_content_id uuid NOT NULL REFERENCES public.ak_topic_contents(id) ON DELETE CASCADE,
    language_code VARCHAR(10) NOT NULL,
    editor_note TEXT, -- 多语言编辑说明
    translation_method VARCHAR(32) DEFAULT 'ai',
    ai_provider VARCHAR(32),
    quality_score FLOAT,
    human_verified BOOLEAN DEFAULT false,
    human_verified_by uuid,
    human_verified_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    UNIQUE(topic_content_id, language_code)
);

-- 专题内容翻译索引
CREATE INDEX IF NOT EXISTS idx_topic_content_translations_content ON public.ak_topic_content_translations(topic_content_id);
CREATE INDEX IF NOT EXISTS idx_topic_content_translations_language ON public.ak_topic_content_translations(language_code);
CREATE INDEX IF NOT EXISTS idx_topic_content_translations_quality ON public.ak_topic_content_translations(quality_score DESC);

COMMENT ON TABLE public.ak_topic_content_translations IS '专题内容关联多语言翻译表';

-- ===================================================================
-- 多语言专题视图和函数
-- ===================================================================

-- 多语言专题视图
CREATE OR REPLACE VIEW public.vw_multilingual_topics AS
SELECT 
    t.id,
    t.title as original_title,
    t.description as original_description,
    t.topic_type,
    t.status,
    t.cover_image_url,
    t.creator_id,
    t.content_count,
    t.view_count,
    t.like_count,
    t.share_count,
    t.comment_count,
    t.subscriber_count,
    t.priority_level,
    t.tags,
    t.created_at,
    t.updated_at,
    -- 聚合翻译信息
    COALESCE(
        json_agg(
            json_build_object(
                'language_code', tt.language_code,
                'title', tt.title,
                'description', tt.description,
                'meta_description', tt.meta_description,
                'meta_keywords', tt.meta_keywords,
                'quality_score', tt.quality_score,
                'translation_method', tt.translation_method,
                'human_verified', tt.human_verified
            )
        ) FILTER (WHERE tt.id IS NOT NULL),
        '[]'::json
    ) as translations
FROM public.ak_topics t
LEFT JOIN public.ak_topic_translations tt ON t.id = tt.topic_id
WHERE t.status IN ('active', 'featured')
GROUP BY t.id, t.title, t.description, t.topic_type, t.status, t.cover_image_url, 
         t.creator_id, t.content_count, t.view_count, t.like_count, t.share_count,
         t.comment_count, t.subscriber_count, t.priority_level, t.tags, 
         t.created_at, t.updated_at;

COMMENT ON VIEW public.vw_multilingual_topics IS '多语言专题聚合视图';

-- 获取指定语言的专题列表函数
CREATE OR REPLACE FUNCTION public.get_topics_by_language(
    p_language_code VARCHAR(10) DEFAULT 'zh-CN',
    p_topic_type VARCHAR(32) DEFAULT NULL,
    p_status VARCHAR(32) DEFAULT 'active',
    p_limit INTEGER DEFAULT 20,
    p_offset INTEGER DEFAULT 0
)
RETURNS TABLE (
    topic_id uuid,
    title VARCHAR(255),
    description TEXT,
    topic_type VARCHAR(32),
    status VARCHAR(32),
    cover_image_url TEXT,
    content_count INTEGER,
    view_count INTEGER,
    like_count INTEGER,
    share_count INTEGER,
    comment_count INTEGER,
    subscriber_count INTEGER,
    priority_level INTEGER,
    tags TEXT[],
    translation_quality FLOAT,
    created_at TIMESTAMP WITH TIME ZONE,
    updated_at TIMESTAMP WITH TIME ZONE
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        t.id,
        COALESCE(tt.title, t.title) as title,
        COALESCE(tt.description, t.description) as description,
        t.topic_type,
        t.status,
        t.cover_image_url,
        t.content_count,
        t.view_count,
        t.like_count,
        t.share_count,
        t.comment_count,
        t.subscriber_count,
        t.priority_level,
        t.tags,
        tt.quality_score as translation_quality,
        t.created_at,
        t.updated_at
    FROM public.ak_topics t
    LEFT JOIN public.ak_topic_translations tt ON t.id = tt.topic_id 
        AND tt.language_code = p_language_code
    WHERE (p_topic_type IS NULL OR t.topic_type = p_topic_type)
        AND (p_status IS NULL OR t.status = p_status)
    ORDER BY t.priority_level DESC, t.updated_at DESC
    LIMIT p_limit OFFSET p_offset;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION public.get_topics_by_language IS '获取指定语言的专题列表';

-- 获取专题详情（多语言）函数
CREATE OR REPLACE FUNCTION public.get_topic_detail(
    p_topic_id uuid,
    p_language_code VARCHAR(10) DEFAULT 'zh-CN'
)
RETURNS TABLE (
    topic_id uuid,
    title VARCHAR(255),
    description TEXT,
    meta_description TEXT,
    meta_keywords TEXT[],
    topic_type VARCHAR(32),
    status VARCHAR(32),
    cover_image_url TEXT,
    creator_id uuid,
    editor_id uuid,
    content_count INTEGER,
    view_count INTEGER,
    like_count INTEGER,
    share_count INTEGER,
    comment_count INTEGER,
    subscriber_count INTEGER,
    priority_level INTEGER,
    tags TEXT[],
    seo_slug VARCHAR(255),
    translation_quality FLOAT,
    is_translated BOOLEAN,
    created_at TIMESTAMP WITH TIME ZONE,
    updated_at TIMESTAMP WITH TIME ZONE,
    contents JSON
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        t.id,
        COALESCE(tt.title, t.title) as title,
        COALESCE(tt.description, t.description) as description,
        COALESCE(tt.meta_description, t.meta_description) as meta_description,
        COALESCE(tt.meta_keywords, t.meta_keywords) as meta_keywords,
        t.topic_type,
        t.status,
        t.cover_image_url,
        t.creator_id,
        t.editor_id,
        t.content_count,
        t.view_count,
        t.like_count,
        t.share_count,
        t.comment_count,
        t.subscriber_count,
        t.priority_level,
        t.tags,
        t.seo_slug,
        tt.quality_score as translation_quality,
        (tt.id IS NOT NULL) as is_translated,
        t.created_at,
        t.updated_at,
        -- 聚合该专题下的内容
        COALESCE(
            (SELECT json_agg(
                json_build_object(
                    'content_id', tc.content_id,
                    'display_order', tc.display_order,
                    'editor_note', COALESCE(tct.editor_note, tc.editor_note),
                    'is_featured', tc.is_featured,
                    'added_at', tc.added_at
                )
                ORDER BY tc.display_order, tc.added_at
            )
            FROM public.ak_topic_contents tc
            LEFT JOIN public.ak_topic_content_translations tct 
                ON tc.id = tct.topic_content_id AND tct.language_code = p_language_code
            WHERE tc.topic_id = t.id),
            '[]'::json
        ) as contents
    FROM public.ak_topics t
    LEFT JOIN public.ak_topic_translations tt ON t.id = tt.topic_id 
        AND tt.language_code = p_language_code
    WHERE t.id = p_topic_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION public.get_topic_detail IS '获取专题详情（多语言支持）';

-- ===================================================================
-- 初始化示例数据
-- ===================================================================

-- 创建示例专题（如果不存在）
INSERT INTO public.ak_topics (title, description, topic_type, status, priority_level, tags) 
VALUES 
('人工智能发展趋势', '探讨2024年人工智能技术的最新发展趋势和应用前景', 'analysis', 'active', 5, ARRAY['AI', '人工智能', '技术趋势']),
('科技创新专题', '汇聚最新的科技创新成果和突破性技术', 'series', 'featured', 8, ARRAY['科技', '创新', '技术']),
('数字化转型指南', '企业数字化转型的完整指导和最佳实践', 'guide', 'active', 6, ARRAY['数字化', '转型', '企业'])
ON CONFLICT DO NOTHING;

-- 为示例专题添加多语言翻译
INSERT INTO public.ak_topic_translations (topic_id, language_code, title, description, translation_method, quality_score)
SELECT 
    t.id,
    'en',
    CASE 
        WHEN t.title = '人工智能发展趋势' THEN 'AI Development Trends'
        WHEN t.title = '科技创新专题' THEN 'Technology Innovation Topics'
        WHEN t.title = '数字化转型指南' THEN 'Digital Transformation Guide'
        ELSE t.title
    END,
    CASE 
        WHEN t.description LIKE '%人工智能%' THEN 'Exploring the latest AI technology trends and application prospects in 2024'
        WHEN t.description LIKE '%科技创新%' THEN 'Gathering the latest technological innovations and breakthrough technologies'
        WHEN t.description LIKE '%数字化转型%' THEN 'Complete guidance and best practices for enterprise digital transformation'
        ELSE t.description
    END,
    'ai',
    0.85
FROM public.ak_topics t
WHERE t.title IN ('人工智能发展趋势', '科技创新专题', '数字化转型指南')
ON CONFLICT (topic_id, language_code) DO NOTHING;

-- 添加日文翻译
INSERT INTO public.ak_topic_translations (topic_id, language_code, title, description, translation_method, quality_score)
SELECT 
    t.id,
    'ja',
    CASE 
        WHEN t.title = '人工智能发展趋势' THEN 'AI発展トレンド'
        WHEN t.title = '科技创新专题' THEN 'テクノロジーイノベーション特集'
        WHEN t.title = '数字化转型指南' THEN 'デジタル変革ガイド'
        ELSE t.title
    END,
    CASE 
        WHEN t.description LIKE '%人工智能%' THEN '2024年のAI技術の最新発展トレンドと応用展望を探る'
        WHEN t.description LIKE '%科技创新%' THEN '最新の技術革新成果と画期的技術を集約'
        WHEN t.description LIKE '%数字化转型%' THEN '企業のデジタル変革の完全ガイドとベストプラクティス'
        ELSE t.description
    END,
    'ai',
    0.80
FROM public.ak_topics t
WHERE t.title IN ('人工智能发展趋势', '科技创新专题', '数字化转型指南')
ON CONFLICT (topic_id, language_code) DO NOTHING;

-- ===================================================================
-- 更新现有ak_languages表以支持专题翻译
-- ===================================================================

-- 为专题翻译启用RLS策略
ALTER TABLE public.ak_topic_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_topic_content_translations ENABLE ROW LEVEL SECURITY;

-- 专题翻译访问策略（所有人可读）
DROP POLICY IF EXISTS "topic_translations_select_policy" ON public.ak_topic_translations;
CREATE POLICY "topic_translations_select_policy" ON public.ak_topic_translations
    FOR SELECT USING (true);

-- 专题翻译插入策略（认证用户可插入）
DROP POLICY IF EXISTS "topic_translations_insert_policy" ON public.ak_topic_translations;
CREATE POLICY "topic_translations_insert_policy" ON public.ak_topic_translations
    FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);

-- 专题内容翻译访问策略
DROP POLICY IF EXISTS "topic_content_translations_select_policy" ON public.ak_topic_content_translations;
CREATE POLICY "topic_content_translations_select_policy" ON public.ak_topic_content_translations
    FOR SELECT USING (true);

DROP POLICY IF EXISTS "topic_content_translations_insert_policy" ON public.ak_topic_content_translations;
CREATE POLICY "topic_content_translations_insert_policy" ON public.ak_topic_content_translations
    FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);

-- ===================================================================
-- 完成信息
-- ===================================================================

DO $$
BEGIN
    RAISE NOTICE '===================================================================';
    RAISE NOTICE 'ak_topics 多语言支持扩展完成！';
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '新增表：';
    RAISE NOTICE '- ak_topic_translations: 专题多语言翻译表';
    RAISE NOTICE '- ak_topic_content_translations: 专题内容关联多语言翻译表';
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '新增函数：';
    RAISE NOTICE '- get_topics_by_language(): 获取指定语言的专题列表';
    RAISE NOTICE '- get_topic_detail(): 获取专题详情（多语言支持）';
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '新增视图：';
    RAISE NOTICE '- vw_multilingual_topics: 多语言专题聚合视图';
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '特性支持：';
    RAISE NOTICE '- 专题标题和描述多语言翻译';
    RAISE NOTICE '- 专题内容编辑说明多语言支持';
    RAISE NOTICE '- AI和人工翻译质量评分';
    RAISE NOTICE '- SEO元数据多语言支持';
    RAISE NOTICE '- 翻译质量追踪和审核机制';
    RAISE NOTICE '- 与现有ak_content_translations架构一致';
    RAISE NOTICE '===================================================================';
END
$$;

ALTER TABLE public.ak_contents
ADD COLUMN cid BIGSERIAL UNIQUE;

-- 或更推荐（PostgreSQL 10+，标准写法）：
-- ALTER TABLE public.ak_contents
-- ADD COLUMN cid BIGINT GENERATED ALWAYS AS IDENTITY