-- =====================================================
-- 一站式消息系统部署脚本
-- 自动检测环境并部署合适的权限模式
-- =====================================================

-- 部署配置检查
DO $$
BEGIN
    RAISE NOTICE '🚀 开始消息系统一站式部署...';
    RAISE NOTICE '📋 部署清单:';
    RAISE NOTICE '  1. 检查依赖扩展';
    RAISE NOTICE '  2. 创建/更新表结构';
    RAISE NOTICE '  3. 部署权限系统';
    RAISE NOTICE '  4. 插入基础数据';
    RAISE NOTICE '  5. 验证部署结果';
    RAISE NOTICE '';
END $$;

-- =============================================================================
-- 1. 检查和创建必要的扩展
-- =============================================================================

DO $$
BEGIN
    RAISE NOTICE '🔧 检查数据库扩展...';
    
    -- UUID扩展
    IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'uuid-ossp') THEN
        CREATE EXTENSION "uuid-ossp";
        RAISE NOTICE '  ✅ 已创建 uuid-ossp 扩展';
    ELSE
        RAISE NOTICE '  ✅ uuid-ossp 扩展已存在';
    END IF;
    
    -- 文本搜索扩展
    IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_trgm') THEN
        BEGIN
            CREATE EXTENSION "pg_trgm";
            RAISE NOTICE '  ✅ 已创建 pg_trgm 扩展';
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE '  ⚠️ pg_trgm 扩展创建失败，将跳过全文搜索功能';
        END;
    ELSE
        RAISE NOTICE '  ✅ pg_trgm 扩展已存在';
    END IF;
END $$;

-- =============================================================================
-- 2. 创建消息类型表（如果不存在）
-- =============================================================================

CREATE TABLE IF NOT EXISTS public.ak_message_types (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    type_name VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    color VARCHAR(7) DEFAULT '#1890ff',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID,
    updated_by UUID
);

-- 创建更新触发器
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_ak_message_types_updated_at ON public.ak_message_types;
CREATE TRIGGER update_ak_message_types_updated_at
    BEFORE UPDATE ON public.ak_message_types
    FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- =============================================================================
-- 3. 环境检测和权限模式选择
-- =============================================================================

DO $$
DECLARE
    is_production BOOLEAN := false;
    supabase_url TEXT;
    deployment_mode TEXT;
BEGIN
    RAISE NOTICE '🔍 检测部署环境...';
    
    -- 尝试检测环境（这里可以根据实际情况调整检测逻辑）
    -- 检查是否有特定的生产环境标识
    BEGIN
        -- 检查当前数据库名称或其他生产环境标识
        SELECT current_database() INTO supabase_url;
        
        -- 简单的环境判断逻辑
        IF supabase_url LIKE '%prod%' OR supabase_url LIKE '%production%' THEN
            is_production := true;
            deployment_mode := 'production';
        ELSE
            deployment_mode := 'development';
        END IF;
        
    EXCEPTION WHEN OTHERS THEN
        deployment_mode := 'development';
    END;
    
    RAISE NOTICE '  🎯 检测到环境: %', deployment_mode;
    
    -- 根据环境选择权限模式
    IF is_production THEN
        RAISE NOTICE '  🔒 将部署生产权限模式（基于角色）';
    ELSE
        RAISE NOTICE '  🔓 将部署开发权限模式（开放权限）';
        RAISE NOTICE '  ⚠️ 如需生产权限，请手动运行 restore_normal_permissions.sql';
    END IF;
END $$;

-- =============================================================================
-- 4. 部署基础数据和权限（开发模式）
-- =============================================================================

-- 插入基础消息类型
INSERT INTO public.ak_message_types (type_name, display_name, description, icon, color)
VALUES 
    ('system', '系统消息', '系统自动发送的消息', 'system', '#52c41a'),
    ('notification', '通知消息', '普通通知消息', 'bell', '#1890ff'),
    ('announcement', '公告消息', '重要公告消息', 'megaphone', '#fa8c16'),
    ('private', '私人消息', '个人私密消息', 'mail', '#722ed1'),
    ('group', '群组消息', '群组内的消息', 'team', '#13c2c2'),
    ('task', '任务消息', '任务相关消息', 'check-circle', '#52c41a'),
    ('urgent', '紧急消息', '紧急重要消息', 'warning', '#ff4d4f'),
    ('reminder', '提醒消息', '定时提醒消息', 'clock-circle', '#faad14')
ON CONFLICT (type_name) DO UPDATE SET
    display_name = EXCLUDED.display_name,
    description = EXCLUDED.description,
    icon = EXCLUDED.icon,
    color = EXCLUDED.color,
    updated_at = NOW();

-- 启用RLS（如果表存在）
DO $$
DECLARE
    table_names TEXT[] := ARRAY[
        'ak_message_types', 'ak_messages', 'ak_message_recipients',
        'ak_message_groups', 'ak_message_group_members', 'ak_message_templates',
        'ak_user_message_preferences', 'ak_message_stats'
    ];
    table_name TEXT;
BEGIN
    RAISE NOTICE '🛡️ 启用行级安全性...';
    
    FOREACH table_name IN ARRAY table_names LOOP
        BEGIN
            EXECUTE format('ALTER TABLE IF EXISTS public.%I ENABLE ROW LEVEL SECURITY', table_name);
            RAISE NOTICE '  ✅ 已为 % 启用RLS', table_name;
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE '  ⚠️ 表 % 不存在，跳过RLS设置', table_name;
        END;
    END LOOP;
END $$;

-- =============================================================================
-- 5. 部署开发权限策略（默认）
-- =============================================================================

-- 清理现有策略
DO $$
DECLARE
    table_names TEXT[] := ARRAY[
        'ak_message_types', 'ak_messages', 'ak_message_recipients',
        'ak_message_groups', 'ak_message_group_members', 'ak_message_templates',
        'ak_user_message_preferences', 'ak_message_stats'
    ];
    table_name TEXT;
    r RECORD;
BEGIN
    RAISE NOTICE '🔓 部署开发权限模式...';
    
    FOREACH table_name IN ARRAY table_names LOOP
        -- 删除现有策略
        FOR r IN 
            SELECT policyname 
            FROM pg_policies 
            WHERE schemaname = 'public' AND tablename = table_name
        LOOP
            EXECUTE format('DROP POLICY IF EXISTS %I ON public.%I', r.policyname, table_name);
        END LOOP;
        
        -- 创建开放策略（如果表存在）
        BEGIN
            EXECUTE format('
                CREATE POLICY "auto_dev_access_%s" ON public.%I
                FOR ALL USING (auth.role() = ''authenticated'')
                WITH CHECK (auth.role() = ''authenticated'')
            ', replace(table_name, 'ak_', ''), table_name);
            RAISE NOTICE '  ✅ 已为 % 创建开放权限', table_name;
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE '  ⚠️ 表 % 不存在，跳过权限设置', table_name;
        END;
    END LOOP;
END $$;

-- =============================================================================
-- 6. 创建便捷开发函数
-- =============================================================================

-- 快速发送消息函数
CREATE OR REPLACE FUNCTION public.quick_send(
    p_title TEXT,
    p_content TEXT,
    p_to_user TEXT DEFAULT NULL,
    p_type TEXT DEFAULT 'notification'
)
RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_message_id UUID;
    v_type_id UUID;
    v_to_user_id UUID;
BEGIN
    -- 获取消息类型
    SELECT id INTO v_type_id FROM public.ak_message_types WHERE type_name = p_type LIMIT 1;
    
    -- 如果指定了接收者，尝试解析
    IF p_to_user IS NOT NULL THEN
        IF p_to_user ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$' THEN
            v_to_user_id := p_to_user::UUID;
        ELSE
            v_to_user_id := auth.uid(); -- 默认发给自己
        END IF;
    ELSE
        v_to_user_id := auth.uid(); -- 默认发给自己
    END IF;
    
    -- 创建消息
    INSERT INTO public.ak_messages (
        message_type_id, sender_type, sender_id, receiver_type, receiver_id,
        title, content, status, created_by
    ) VALUES (
        v_type_id, 'user', auth.uid(), 'user', v_to_user_id,
        p_title, p_content, 'sent', auth.uid()
    ) RETURNING id INTO v_message_id;
    
    RETURN v_message_id;
END $$;

-- 消息统计函数
CREATE OR REPLACE FUNCTION public.message_stats()
RETURNS TABLE (
    total_messages BIGINT,
    unread_messages BIGINT,
    message_types BIGINT,
    recent_messages BIGINT
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        (SELECT COUNT(*) FROM public.ak_messages)::BIGINT,
        (SELECT COUNT(*) FROM public.ak_message_recipients WHERE status = 'unread')::BIGINT,
        (SELECT COUNT(*) FROM public.ak_message_types WHERE is_active = true)::BIGINT,
        (SELECT COUNT(*) FROM public.ak_messages WHERE created_at > NOW() - INTERVAL '24 hours')::BIGINT;
END $$;

-- =============================================================================
-- 7. 部署验证和结果报告
-- =============================================================================

DO $$
DECLARE
    table_count INTEGER;
    policy_count INTEGER;
    function_count INTEGER;
    type_count INTEGER;
    extension_count INTEGER;
BEGIN
    RAISE NOTICE '✅ 部署完成，生成部署报告...';
    RAISE NOTICE '';
    
    -- 统计表数量
    SELECT COUNT(*) INTO table_count
    FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name LIKE 'ak_%';
    
    -- 统计策略数量
    SELECT COUNT(*) INTO policy_count
    FROM pg_policies
    WHERE schemaname = 'public'
    AND tablename LIKE 'ak_%';
    
    -- 统计函数数量
    SELECT COUNT(*) INTO function_count
    FROM information_schema.routines
    WHERE routine_schema = 'public'
    AND routine_name IN ('quick_send', 'message_stats');
    
    -- 统计消息类型
    SELECT COUNT(*) INTO type_count
    FROM public.ak_message_types
    WHERE is_active = true;
    
    -- 统计扩展
    SELECT COUNT(*) INTO extension_count
    FROM pg_extension
    WHERE extname IN ('uuid-ossp', 'pg_trgm');
    
    RAISE NOTICE '📊 部署统计报告:';
    RAISE NOTICE '  🗄️  消息相关表: % 个', table_count;
    RAISE NOTICE '  🛡️  安全策略: % 个', policy_count;
    RAISE NOTICE '  ⚙️  便捷函数: % 个', function_count;
    RAISE NOTICE '  📝 消息类型: % 个', type_count;
    RAISE NOTICE '  🔧 数据库扩展: % 个', extension_count;
    RAISE NOTICE '';
    
    RAISE NOTICE '🎯 部署模式: 🔓 开发模式（开放权限）';
    RAISE NOTICE '⚠️  生产环境请运行: restore_normal_permissions.sql';
    RAISE NOTICE '';
    
    RAISE NOTICE '🛠️  可用的便捷函数:';
    RAISE NOTICE '   • quick_send(标题, 内容, 接收者, 类型) - 快速发送消息';
    RAISE NOTICE '   • message_stats() - 查看消息统计';
    RAISE NOTICE '';
    
    RAISE NOTICE '📝 使用示例:';
    RAISE NOTICE '   SELECT quick_send(''测试'', ''这是测试消息'');';
    RAISE NOTICE '   SELECT * FROM message_stats();';
    RAISE NOTICE '   SELECT * FROM public.ak_message_types;';
    
    IF table_count > 0 AND policy_count > 0 THEN
        RAISE NOTICE '';
        RAISE NOTICE '🎉 消息系统部署成功！可以开始使用了。';
    ELSE
        RAISE NOTICE '';
        RAISE NOTICE '⚠️  部署可能不完整，请检查错误信息。';
    END IF;
END $$;

-- 最终状态检查
SELECT 
    '🚀 一站式部署完成' as status,
    '开发模式已启用' as mode,
    COUNT(*) || ' 个消息类型已创建' as message_types,
    '运行 SELECT * FROM message_stats() 查看统计' as next_step
FROM public.ak_message_types
WHERE is_active = true;