-- 性能监控表
CREATE TABLE public.perf_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid,
  metric_type text NOT NULL, -- 'page_load' | 'api_call' | 'custom'
  metric_name text NOT NULL,
  duration_ms numeric NOT NULL,
  url text,
  context jsonb DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- 索引：按时间和类型查询
CREATE INDEX idx_perf_logs_created_at ON public.perf_logs(created_at DESC);
CREATE INDEX idx_perf_logs_type_name ON public.perf_logs(metric_type, metric_name);

-- 启用 RLS
ALTER TABLE public.perf_logs ENABLE ROW LEVEL SECURITY;

-- 任何人可写入（含匿名）
CREATE POLICY "anyone_can_insert_perf_logs"
ON public.perf_logs FOR INSERT
WITH CHECK (true);

-- 只有管理员可查看
CREATE POLICY "admins_can_view_perf_logs"
ON public.perf_logs FOR SELECT
USING (public.is_admin(auth.uid()));

-- 自动清理 7 天前的数据
CREATE OR REPLACE FUNCTION public.cleanup_old_perf_logs()
RETURNS trigger
LANGUAGE plpgsql
SET search_path = public
AS $$
BEGIN
  -- 概率性清理（1% 触发，避免每次插入都扫表）
  IF random() < 0.01 THEN
    DELETE FROM public.perf_logs WHERE created_at < now() - interval '7 days';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_cleanup_old_perf_logs
AFTER INSERT ON public.perf_logs
FOR EACH ROW
EXECUTE FUNCTION public.cleanup_old_perf_logs();