-- ===================================================================
-- 业务项目内容初始化脚本
-- 基于 ak_contents 表，使用 content_type='business_item' 来区分业务项目
-- ===================================================================

-- 首先确保 ak_contents 表中有 content_type 字段
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'ak_contents' 
        AND column_name = 'content_type'
    ) THEN
        ALTER TABLE public.ak_contents 
        ADD COLUMN content_type VARCHAR(50) DEFAULT 'article';
        
        -- 创建索引以提高查询性能
        CREATE INDEX idx_ak_contents_content_type ON public.ak_contents(content_type);
        
        RAISE NOTICE '已为 ak_contents 表添加 content_type 字段';
    ELSE
        RAISE NOTICE 'content_type 字段已存在';
    END IF;
END
$$;

-- 确保有业务相关的分类
INSERT INTO public.ak_content_categories (name_key, parent_id, level, ai_keywords, sort_order, is_active) VALUES
('business.ai', NULL, 0, ARRAY['人工智能', 'AI', '机器学习', 'artificial intelligence'], 1, true),
('business.bigdata', NULL, 0, ARRAY['大数据', 'big data', '数据分析', 'analytics'], 2, true),
('business.iot', NULL, 0, ARRAY['物联网', 'IoT', '智能设备', 'smart devices'], 3, true),
('business.cloud', NULL, 0, ARRAY['云计算', 'cloud computing', '云服务', 'cloud services'], 4, true),
('business.blockchain', NULL, 0, ARRAY['区块链', 'blockchain', '数字货币', 'cryptocurrency'], 5, true),
('business.mobile', NULL, 0, ARRAY['移动开发', 'mobile development', 'APP开发', 'mobile app'], 6, true),
('business.web', NULL, 0, ARRAY['前端开发', 'web development', '网站建设', 'website'], 7, true),
('business.database', NULL, 0, ARRAY['数据库', 'database', 'SQL', '数据管理'], 8, true)
ON CONFLICT (name_key) DO NOTHING;

-- 获取分类ID以便插入内容
DO $$
DECLARE
    ai_category_id UUID;
    bigdata_category_id UUID;
    iot_category_id UUID;
    cloud_category_id UUID;
    blockchain_category_id UUID;
    mobile_category_id UUID;
    web_category_id UUID;
    database_category_id UUID;
BEGIN
    -- 获取分类ID
    SELECT id INTO ai_category_id FROM public.ak_content_categories WHERE name_key = 'business.ai';
    SELECT id INTO bigdata_category_id FROM public.ak_content_categories WHERE name_key = 'business.bigdata';
    SELECT id INTO iot_category_id FROM public.ak_content_categories WHERE name_key = 'business.iot';
    SELECT id INTO cloud_category_id FROM public.ak_content_categories WHERE name_key = 'business.cloud';
    SELECT id INTO blockchain_category_id FROM public.ak_content_categories WHERE name_key = 'business.blockchain';
    SELECT id INTO mobile_category_id FROM public.ak_content_categories WHERE name_key = 'business.mobile';
    SELECT id INTO web_category_id FROM public.ak_content_categories WHERE name_key = 'business.web';
    SELECT id INTO database_category_id FROM public.ak_content_categories WHERE name_key = 'business.database';

    -- 插入业务项目内容
    INSERT INTO public.ak_contents (
        title, 
        content_summary, 
        content_type, 
        category_id, 
        original_language, 
        status, 
        featured_image_url,
        view_count,
        published_at,
        tags,
        quality_score
    ) VALUES
    (
        '人工智能解决方案',
        '为企业提供全方位的AI技术支持和解决方案，助力数字化转型升级。我们的AI服务包括机器学习模型开发、自然语言处理、计算机视觉等核心技术。',
        'business_item',
        ai_category_id,
        'zh-CN',
        'published',
        'https://picsum.photos/400/300?random=1',
        1285,
        NOW() - INTERVAL '5 days',
        ARRAY['人工智能', 'AI', '机器学习', '企业服务'],
        0.95
    ),
    (
        '大数据分析平台',
        '专业的数据分析工具，帮助企业洞察商业价值，制定精准决策。支持实时数据处理、可视化报表、智能预测分析等功能。',
        'business_item',
        bigdata_category_id,
        'zh-CN',
        'published',
        'https://picsum.photos/400/300?random=2',
        892,
        NOW() - INTERVAL '8 days',
        ARRAY['大数据', '数据分析', '商业智能', 'BI'],
        0.92
    ),
    (
        '物联网系统集成',
        '完整的IoT解决方案，实现设备互联和智能管理。提供硬件接入、数据采集、远程控制、智能分析等一站式服务。',
        'business_item',
        iot_category_id,
        'zh-CN',
        'published',
        'https://picsum.photos/400/300?random=3',
        756,
        NOW() - INTERVAL '10 days',
        ARRAY['物联网', 'IoT', '智能设备', '系统集成'],
        0.89
    ),
    (
        '云计算服务',
        '稳定可靠的云端基础设施，为业务提供强大的技术支撑。包括云服务器、云存储、CDN加速、负载均衡等核心服务。',
        'business_item',
        cloud_category_id,
        'zh-CN',
        'published',
        'https://picsum.photos/400/300?random=4',
        643,
        NOW() - INTERVAL '12 days',
        ARRAY['云计算', '云服务', '基础设施', 'IaaS'],
        0.91
    ),
    (
        '区块链技术',
        '安全可信的区块链解决方案，保障数据安全与透明。提供智能合约开发、数字资产管理、去中心化应用等服务。',
        'business_item',
        blockchain_category_id,
        'zh-CN',
        'published',
        'https://picsum.photos/400/300?random=5',
        521,
        NOW() - INTERVAL '15 days',
        ARRAY['区块链', '智能合约', '数字资产', 'DApp'],
        0.87
    ),
    (
        '移动应用开发',
        '专业的移动端应用开发服务，覆盖iOS和Android平台。提供原生开发、混合开发、跨平台开发等多种解决方案。',
        'business_item',
        mobile_category_id,
        'zh-CN',
        'published',
        'https://picsum.photos/400/300?random=6',
        467,
        NOW() - INTERVAL '18 days',
        ARRAY['移动开发', 'APP', 'iOS', 'Android'],
        0.93
    ),
    (
        'Web前端设计',
        '现代化的前端界面设计，提供优秀的用户体验。采用最新的前端技术栈，确保响应式设计和跨浏览器兼容性。',
        'business_item',
        web_category_id,
        'zh-CN',
        'published',
        'https://picsum.photos/400/300?random=7',
        389,
        NOW() - INTERVAL '20 days',
        ARRAY['前端开发', 'UI设计', '响应式', '用户体验'],
        0.90
    ),
    (
        '数据库优化',
        '高性能数据库设计与优化，提升系统运行效率。包括数据库架构设计、性能调优、数据迁移、备份恢复等专业服务。',
        'business_item',
        database_category_id,
        'zh-CN',
        'published',
        'https://picsum.photos/400/300?random=8',
        324,
        NOW() - INTERVAL '22 days',
        ARRAY['数据库', '性能优化', 'SQL', '数据架构'],
        0.88
    );

    RAISE NOTICE '业务项目内容初始化完成！';
    RAISE NOTICE '已插入 8 个业务项目到 ak_contents 表中';
    RAISE NOTICE '内容类型设置为: business_item';
END
$$;

-- 创建业务项目专用视图，方便查询和管理
CREATE OR REPLACE VIEW public.vw_business_items AS
SELECT 
    c.id,
    c.title,
    c.content_summary as description,
    c.featured_image_url as image_url,
    cc.name_key as category_key,
    c.published_at,
    c.view_count,
    c.status,
    c.tags,
    c.quality_score,
    c.created_at,
    c.updated_at
FROM public.ak_contents c
LEFT JOIN public.ak_content_categories cc ON c.category_id = cc.id
WHERE c.content_type = 'business_item'
ORDER BY c.published_at DESC;

COMMENT ON VIEW public.vw_business_items IS '业务项目视图，基于ak_contents表的business_item类型内容';

-- 创建获取业务项目的函数
CREATE OR REPLACE FUNCTION public.get_business_items(
    p_limit INTEGER DEFAULT 20,
    p_offset INTEGER DEFAULT 0,
    p_language VARCHAR(10) DEFAULT 'zh-CN'
)
RETURNS TABLE (
    id UUID,
    title TEXT,
    description TEXT,
    image_url TEXT,
    category_name TEXT,
    publish_time TEXT,
    view_count INTEGER,
    tags TEXT[]
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        c.id,
        c.title,
        c.content_summary as description,
        c.featured_image_url as image_url,
        COALESCE(cc.name_key, '未分类') as category_name,
        TO_CHAR(c.published_at, 'YYYY-MM-DD') as publish_time,
        c.view_count,
        c.tags
    FROM public.ak_contents c
    LEFT JOIN public.ak_content_categories cc ON c.category_id = cc.id
    WHERE c.content_type = 'business_item'
        AND c.status = 'published'
        AND c.original_language = p_language
    ORDER BY c.published_at DESC
    LIMIT p_limit
    OFFSET p_offset;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION public.get_business_items IS '获取业务项目列表的函数';

-- 输出完成信息
DO $$
BEGIN
    RAISE NOTICE '=================================';
    RAISE NOTICE '业务项目数据初始化完成！';
    RAISE NOTICE '- 已创建业务相关分类';
    RAISE NOTICE '- 已插入示例业务项目数据';
    RAISE NOTICE '- 已创建业务项目视图';
    RAISE NOTICE '- 已创建查询函数';
    RAISE NOTICE '现在可以在 comindex.uvue 页面中使用 ak_contents 表获取业务项目了';
    RAISE NOTICE '=================================';
END
$$;