-- 1. 首先为admin_chat_messages中存在但admin_profiles中不存在的用户创建档案
INSERT INTO public.admin_profiles (user_id, is_active, login_count)
SELECT DISTINCT acm.user_id, true, 0
FROM public.admin_chat_messages acm
WHERE NOT EXISTS (
  SELECT 1 FROM public.admin_profiles ap WHERE ap.user_id = acm.user_id
);

-- 2. 为admin_profiles添加对话统计字段
ALTER TABLE public.admin_profiles
ADD COLUMN IF NOT EXISTS total_conversations integer DEFAULT 0,
ADD COLUMN IF NOT EXISTS total_messages integer DEFAULT 0,
ADD COLUMN IF NOT EXISTS last_conversation_at timestamp with time zone;

-- 3. 更新现有的统计数据
UPDATE public.admin_profiles ap
SET 
  total_messages = (
    SELECT COUNT(*) 
    FROM public.admin_chat_messages acm 
    WHERE acm.user_id = ap.user_id
  ),
  last_conversation_at = (
    SELECT MAX(created_at) 
    FROM public.admin_chat_messages acm 
    WHERE acm.user_id = ap.user_id
  );

-- 4. 创建管理员对话统计视图
CREATE OR REPLACE VIEW public.admin_conversation_stats AS
SELECT 
  ap.user_id,
  ap.department,
  ap.position,
  ap.total_messages,
  ap.last_conversation_at,
  ap.is_active,
  p.display_name,
  p.avatar_url
FROM public.admin_profiles ap
LEFT JOIN public.profiles p ON p.user_id = ap.user_id;

-- 5. 创建更新统计的函数
CREATE OR REPLACE FUNCTION public.update_admin_conversation_stats()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  UPDATE public.admin_profiles
  SET 
    total_messages = total_messages + 1,
    last_conversation_at = NEW.created_at,
    updated_at = now()
  WHERE user_id = NEW.user_id;
  
  RETURN NEW;
END;
$$;

-- 6. 创建触发器自动更新统计
DROP TRIGGER IF EXISTS update_admin_stats_on_message ON public.admin_chat_messages;
CREATE TRIGGER update_admin_stats_on_message
AFTER INSERT ON public.admin_chat_messages
FOR EACH ROW
EXECUTE FUNCTION public.update_admin_conversation_stats();

-- 7. 添加索引提升查询性能
CREATE INDEX IF NOT EXISTS idx_admin_chat_messages_user_id 
ON public.admin_chat_messages(user_id);

CREATE INDEX IF NOT EXISTS idx_admin_chat_messages_created_at 
ON public.admin_chat_messages(created_at DESC);

-- 8. 创建获取管理员对话历史的函数
CREATE OR REPLACE FUNCTION public.get_admin_conversation_history(
  admin_user_id uuid,
  limit_count integer DEFAULT 50
)
RETURNS TABLE (
  message_id uuid,
  content text,
  role text,
  created_at timestamp with time zone,
  actions jsonb,
  admin_name text,
  admin_department text
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  RETURN QUERY
  SELECT 
    acm.id,
    acm.content,
    acm.role,
    acm.created_at,
    acm.actions,
    p.display_name,
    ap.department
  FROM public.admin_chat_messages acm
  LEFT JOIN public.admin_profiles ap ON ap.user_id = acm.user_id
  LEFT JOIN public.profiles p ON p.user_id = acm.user_id
  WHERE acm.user_id = admin_user_id
  ORDER BY acm.created_at DESC
  LIMIT limit_count;
END;
$$;

-- 9. 为视图添加RLS策略
ALTER VIEW public.admin_conversation_stats SET (security_invoker = on);

-- 10. 添加注释
COMMENT ON COLUMN public.admin_profiles.total_messages IS '管理员发送的总消息数';
COMMENT ON COLUMN public.admin_profiles.last_conversation_at IS '最后一次对话时间';
COMMENT ON FUNCTION public.get_admin_conversation_history IS '获取管理员的对话历史记录';