تفعيل جدول الإحصائيات في Supabase (خطوة واحدة)
SQL Setup
انسخ الكود التالي وشغّله في Supabase SQL Editor ثم اضغط "تحديث الآن":
create table if not exists public.site_events (
id bigserial primary key,
event_type text not null check (event_type in ('page_view', 'play_click')),
page_path text not null,
app_id text check (app_id in ('privacy', 'notify') or app_id is null),
placement text,
referrer_host text,
device_type text check (device_type in ('mobile', 'desktop') or device_type is null),
created_at timestamptz not null default now()
);
create index if not exists idx_site_events_type_created on public.site_events (event_type, created_at desc);
create index if not exists idx_site_events_page_path on public.site_events (page_path);
alter table public.site_events enable row level security;
drop policy if exists "Allow anon insert site_events" on public.site_events;
create policy "Allow anon insert site_events"
on public.site_events for insert to anon, authenticated
with check (event_type in ('page_view', 'play_click'));
create or replace function public.get_site_analytics_summary(days_back int default 30)
returns jsonb language sql security definer set search_path = public as $$
with window_events as (
select * from public.site_events
where created_at >= now() - make_interval(days => greatest(1, least(days_back, 365)))
),
totals as (
select
count(*) filter (where event_type = 'page_view') as total_visits,
count(*) filter (where event_type = 'page_view' and created_at >= date_trunc('day', now())) as today_visits,
count(*) filter (where event_type = 'play_click') as total_play_clicks,
count(*) filter (where event_type = 'play_click' and created_at >= date_trunc('day', now())) as today_play_clicks,
count(*) filter (where event_type = 'play_click' and app_id = 'privacy') as privacy_clicks,
count(*) filter (where event_type = 'play_click' and app_id = 'notify') as notify_clicks
from window_events
),
daily as (
select to_char(date_trunc('day', created_at), 'YYYY-MM-DD') as day,
count(*) filter (where event_type = 'page_view') as visits,
count(*) filter (where event_type = 'play_click') as clicks
from window_events group by 1 order by 1 asc
),
top_pages as (
select page_path,
count(*) filter (where event_type = 'page_view') as visits,
count(*) filter (where event_type = 'play_click') as clicks
from window_events group by page_path order by visits desc, clicks desc limit 20
),
by_placement as (
select coalesce(app_id, 'unknown') as app_id, coalesce(placement, 'unknown') as placement, count(*) as clicks
from window_events where event_type = 'play_click' group by 1, 2 order by clicks desc limit 20
),
by_referrer as (
select coalesce(nullif(referrer_host, ''), 'direct') as referrer_host, count(*) as visits
from window_events where event_type = 'page_view' group by 1 order by visits desc limit 10
),
by_device as (
select coalesce(device_type, 'desktop') as device_type, count(*) as visits
from window_events where event_type = 'page_view' group by 1 order by visits desc
)
select jsonb_build_object(
'totals', (select row_to_json(totals) from totals),
'daily', coalesce((select jsonb_agg(row_to_json(daily)) from daily), '[]'::jsonb),
'top_pages', coalesce((select jsonb_agg(row_to_json(top_pages)) from top_pages), '[]'::jsonb),
'by_placement', coalesce((select jsonb_agg(row_to_json(by_placement)) from by_placement), '[]'::jsonb),
'by_referrer', coalesce((select jsonb_agg(row_to_json(by_referrer)) from by_referrer), '[]'::jsonb),
'by_device', coalesce((select jsonb_agg(row_to_json(by_device)) from by_device), '[]'::jsonb)
);
$$;
grant execute on function public.get_site_analytics_summary(int) to anon, authenticated;