-- ====================================================================
-- 角色字段统一升级脚本 - Role Field Unification Upgrade
-- ====================================================================
-- 目的：将所有表的 user_type (INTEGER) 字段统一为 role (TEXT) 字段
-- 兼容性：Supabase + PostgreSQL 14+
-- 执行顺序：在现有数据库基础上执行
-- ====================================================================

BEGIN;

-- ====================================================================
-- 1. 统一 ml_user_profiles 表的角色字段
-- ====================================================================

-- 1.1 添加新的 role 字段
ALTER TABLE public.ml_user_profiles 
ADD COLUMN IF NOT EXISTS role TEXT DEFAULT 'customer';

-- 1.2 将现有 user_type 数据迁移到 role 字段
UPDATE public.ml_user_profiles 
SET role = CASE 
    WHEN user_type = 1 THEN 'customer'     -- 消费者
    WHEN user_type = 2 THEN 'merchant'     -- 商家
    WHEN user_type = 3 THEN 'delivery'     -- 配送员
    WHEN user_type = 4 THEN 'service'      -- 客服
    WHEN user_type = 5 THEN 'admin'        -- 管理员
    ELSE 'customer'
END
WHERE role IS NULL OR role = 'customer';

-- 1.3 设置 role 字段约束
ALTER TABLE public.ml_user_profiles 
ALTER COLUMN role SET NOT NULL;

ALTER TABLE public.ml_user_profiles 
ADD CONSTRAINT IF NOT EXISTS chk_ml_user_role 
CHECK (role IN ('customer', 'merchant', 'delivery', 'service', 'admin'));

-- 1.4 更新索引
DROP INDEX IF EXISTS idx_ml_user_profiles_type;
CREATE INDEX IF NOT EXISTS idx_ml_user_profiles_role ON public.ml_user_profiles(role);

-- 1.5 删除旧的 user_type 字段和约束（可选，建议在测试确认后执行）
-- ALTER TABLE public.ml_user_profiles DROP CONSTRAINT IF EXISTS chk_ml_user_type;
-- ALTER TABLE public.ml_user_profiles DROP COLUMN IF EXISTS user_type;

-- ====================================================================
-- 2. 更新相关函数中的字段引用
-- ====================================================================

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

-- 2.2 更新用户信息视图
CREATE OR REPLACE VIEW public.vw_user_info AS
SELECT 
    u.id as user_id,
    u.email,
    u.username,
    u.role as user_role,
    u.status as user_status,
    u.created_at as user_created_at,
    p.cid as profile_cid,
    p.role as profile_role,
    p.status as profile_status,
    p.real_name,
    p.avatar_url,
    p.phone,
    p.credit_score,
    p.verification_status,
    p.created_at as profile_created_at,
    CASE 
        WHEN p.role = 'customer' THEN '消费者'
        WHEN p.role = 'merchant' THEN '商家'
        WHEN p.role = 'delivery' THEN '配送员'
        WHEN p.role = 'service' THEN '客服'
        WHEN p.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;

-- ====================================================================
-- 3. 更新 RLS 策略中的角色检查
-- ====================================================================

-- 3.1 更新商品相关策略
DROP POLICY IF EXISTS "商家管理自己的商品" ON public.ml_products;
CREATE POLICY "商家管理自己的商品"
ON public.ml_products
FOR ALL
TO authenticated
USING (
    merchant_id = auth.uid()
    OR EXISTS (
        SELECT 1 FROM public.ml_user_profiles p 
        WHERE p.user_id = auth.uid() 
        AND p.role IN ('admin', 'service')
    )
);

-- 3.2 更新订单相关策略
DROP POLICY IF EXISTS "配送员查看分配的订单" ON public.ml_orders;
CREATE POLICY "配送员查看分配的订单"
ON public.ml_orders
FOR SELECT
TO authenticated
USING (
    delivery_id = auth.uid()
    OR EXISTS (
        SELECT 1 FROM public.ml_user_profiles p 
        WHERE p.user_id = auth.uid() 
        AND p.role IN ('admin', 'service')
    )
);

-- 3.3 更新用户资料策略
DROP POLICY IF EXISTS "用户管理自己的资料" ON public.ml_user_profiles;
CREATE POLICY "用户管理自己的资料"
ON public.ml_user_profiles
FOR ALL
TO authenticated
USING (
    user_id = auth.uid()
    OR EXISTS (
        SELECT 1 FROM public.ml_user_profiles p 
        WHERE p.user_id = auth.uid() 
        AND p.role IN ('admin', 'service')
    )
);

-- ====================================================================
-- 4. 更新字段注释
-- ====================================================================

COMMENT ON COLUMN public.ml_user_profiles.role IS '用户角色：customer消费者, merchant商家, delivery配送员, service客服, admin管理员';

-- ====================================================================
-- 5. 创建角色辅助函数
-- ====================================================================

-- 5.1 获取用户角色函数
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.ml_user_profiles
    WHERE user_id = user_uuid;
    
    RETURN COALESCE(user_role, 'customer');
END;
$$;

-- 5.2 检查用户权限函数
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.ml_user_profiles
    WHERE user_id = user_uuid;
    
    RETURN user_role = ANY(required_roles);
END;
$$;

-- 5.3 角色升级函数（将用户提升为商家等）
CREATE OR REPLACE FUNCTION public.upgrade_user_role(user_uuid UUID, new_role TEXT)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    -- 检查新角色是否有效
    IF new_role NOT IN ('customer', 'merchant', 'delivery', 'service', 'admin') THEN
        RAISE EXCEPTION '无效的角色类型: %', new_role;
    END IF;
    
    -- 更新用户角色
    UPDATE public.ml_user_profiles 
    SET role = new_role,
        updated_at = CURRENT_TIMESTAMP
    WHERE user_id = user_uuid;
    
    -- 同步更新 ak_users 表的 role 字段
    UPDATE public.ak_users 
    SET role = new_role,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = user_uuid;
    
    RETURN FOUND;
END;
$$;

-- ====================================================================
-- 6. 数据一致性检查
-- ====================================================================

-- 6.1 检查角色字段一致性
DO $$
DECLARE
    inconsistent_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO inconsistent_count
    FROM public.ak_users u
    JOIN public.ml_user_profiles p ON u.id = p.user_id
    WHERE u.role != p.role;
    
    IF inconsistent_count > 0 THEN
        RAISE NOTICE '发现 % 条记录的角色字段不一致，正在同步...', inconsistent_count;
        
        -- 以 ml_user_profiles.role 为准同步到 ak_users.role
        UPDATE public.ak_users 
        SET role = p.role,
            updated_at = CURRENT_TIMESTAMP
        FROM public.ml_user_profiles p
        WHERE ak_users.id = p.user_id
        AND ak_users.role != p.role;
        
        RAISE NOTICE '角色字段同步完成';
    ELSE
        RAISE NOTICE '角色字段一致性检查通过';
    END IF;
END;
$$;

-- ====================================================================
-- 7. 创建角色统计视图
-- ====================================================================

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.ml_user_profiles
GROUP BY role
ORDER BY user_count DESC;

COMMIT;

-- ====================================================================
-- 执行验证
-- ====================================================================

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

-- 检查索引
SELECT '索引检查:' as info;
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'ml_user_profiles' 
AND indexname LIKE '%role%';

-- 检查约束
SELECT '约束检查:' as info;
SELECT conname, pg_get_constraintdef(oid) as definition
FROM pg_constraint 
WHERE conrelid = 'public.ml_user_profiles'::regclass
AND conname LIKE '%role%';

SELECT '角色字段统一升级完成！' as result;