-- ====================================================================
-- 角色字段清理脚本 - Role Field Cleanup
-- ====================================================================
-- 目的：确保角色信息只存储在 ak_users.role 字段中
-- 清理 ml_user_profiles 表中可能存在的重复 role 字段
-- 兼容性：Supabase + PostgreSQL 14+
-- ====================================================================

\echo '开始角色字段清理...'

BEGIN;

-- ====================================================================
-- 1. 检查并清理 ml_user_profiles 中的 role 字段
-- ====================================================================

-- 检查是否存在重复的 role 字段
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.columns 
               WHERE table_name = 'ml_user_profiles' 
               AND column_name = 'role') THEN
        
        RAISE NOTICE '发现 ml_user_profiles 表中存在 role 字段，开始清理...';
        
        -- 如果 ak_users.role 字段为空，从 ml_user_profiles.role 迁移数据
        UPDATE public.ak_users 
        SET role = COALESCE(ak_users.role, p.role),
            updated_at = CURRENT_TIMESTAMP
        FROM public.ml_user_profiles p
        WHERE ak_users.id = p.user_id
        AND (ak_users.role IS NULL OR ak_users.role = '');
        
        -- 删除相关约束
        ALTER TABLE public.ml_user_profiles DROP CONSTRAINT IF EXISTS chk_ml_user_role;
        
        -- 删除相关索引
        DROP INDEX IF EXISTS idx_ml_user_profiles_role;
        
        -- 删除 role 字段
        ALTER TABLE public.ml_user_profiles DROP COLUMN IF EXISTS role;
        
        RAISE NOTICE '已删除 ml_user_profiles 表中的 role 字段';
    ELSE
        RAISE NOTICE 'ml_user_profiles 表中不存在 role 字段，无需清理';
    END IF;
END $$;

-- ====================================================================
-- 2. 更新相关函数
-- ====================================================================

-- 更新商家验证函数
CREATE OR REPLACE FUNCTION public.is_verified_merchant(user_uuid UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    result BOOLEAN := FALSE;
BEGIN
    SELECT (u.role = 'merchant' AND p.verification_status = 1) INTO result
    FROM public.ml_user_profiles p
    JOIN public.ak_users u ON p.user_id = u.id
    WHERE p.user_id = user_uuid;
    
    RETURN COALESCE(result, FALSE);
END;
$$;

-- 获取用户角色函数
CREATE OR REPLACE FUNCTION public.get_user_role(user_uuid UUID)
RETURNS TEXT
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    user_role TEXT;
BEGIN
    SELECT role INTO user_role
    FROM public.ak_users
    WHERE id = user_uuid;
    
    RETURN COALESCE(user_role, 'customer');
END;
$$;

-- 检查用户权限函数
CREATE OR REPLACE FUNCTION public.check_user_permission(user_uuid UUID, required_roles TEXT[])
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    user_role TEXT;
BEGIN
    SELECT role INTO user_role
    FROM public.ak_users
    WHERE id = user_uuid;
    
    RETURN user_role = ANY(required_roles);
END;
$$;

-- ====================================================================
-- 3. 更新视图
-- ====================================================================

-- 更新用户信息视图
CREATE OR REPLACE VIEW public.ml_users_view AS
SELECT 
    u.id,
    u.email,
    u.username,
    u.phone,
    u.avatar_url,
    u.status as user_status,
    u.gender,
    u.birthday,
    u.bio,
    u.created_at as user_created_at,
    u.updated_at as user_updated_at,
    u.role,
    p.status,
    p.real_name,
    p.credit_score,
    p.verification_status,
    p.created_at as profile_created_at,
    p.updated_at as profile_updated_at,
    CASE 
        WHEN u.role = 'customer' THEN '消费者'
        WHEN u.role = 'merchant' THEN '商家'
        WHEN u.role = 'delivery' THEN '配送员'
        WHEN u.role = 'service' THEN '客服'
        WHEN u.role = 'admin' THEN '管理员'
        ELSE '未知'
    END as role_name
FROM public.ak_users u
LEFT JOIN public.ml_user_profiles p ON u.id = p.user_id;

-- 创建角色统计视图
CREATE OR REPLACE VIEW public.vw_role_statistics AS
SELECT 
    role,
    COUNT(*) as user_count,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() as percentage
FROM public.ak_users
WHERE role IS NOT NULL
GROUP BY role
ORDER BY user_count DESC;

-- ====================================================================
-- 4. 确保数据一致性
-- ====================================================================

-- 确保所有用户都有角色
UPDATE public.ak_users 
SET role = 'customer'
WHERE role IS NULL OR role = '';

-- 确保角色字段有约束
DO $$
BEGIN
    -- 检查约束是否存在
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'chk_ak_users_role') THEN
        ALTER TABLE public.ak_users 
        ADD CONSTRAINT chk_ak_users_role 
        CHECK (role IN ('customer', 'merchant', 'delivery', 'service', 'admin'));
        RAISE NOTICE '已添加 ak_users.role 字段约束';
    END IF;
END $$;

-- 创建角色字段索引（如果不存在）
CREATE INDEX IF NOT EXISTS idx_ak_users_role ON public.ak_users(role);

COMMIT;

\echo '角色字段清理完成！'

-- ====================================================================
-- 验证结果
-- ====================================================================

-- 检查角色分布
SELECT '角色分布统计:' as info;
SELECT * FROM public.vw_role_statistics;

-- 检查是否还有重复字段
SELECT '字段检查:' as info;
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.columns 
                     WHERE table_name = 'ml_user_profiles' 
                     AND column_name = 'role') 
        THEN '❌ ml_user_profiles.role 字段仍然存在'
        ELSE '✅ ml_user_profiles.role 字段已清理'
    END as ml_user_profiles_check,
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.columns 
                     WHERE table_name = 'ak_users' 
                     AND column_name = 'role') 
        THEN '✅ ak_users.role 字段存在'
        ELSE '❌ ak_users.role 字段不存在'
    END as ak_users_check;

SELECT '角色字段统一完成！角色信息统一存储在 ak_users.role 字段中。' as result;