-- Sesuaikan nama tabel/kolom:
-- users(created_at, is_internal, is_test)
-- analytics_events(event_time, event_name, source, user_id)

create or replace view recon_signups_daily as
with db_signups as (
  select
    date_trunc('day', created_at)::date as day,
    count(*) as db_signups
  from users
  where created_at >= current_date - 30
    and coalesce(is_internal,false) = false
    and coalesce(is_test,false) = false
  group by 1
),
ga4_like as (
  select
    date_trunc('day', event_time)::date as day,
    count(*) as ga4_signups
  from analytics_events
  where event_name = 'signup_completed'
    and event_time >= now() - interval '30 days'
  group by 1
)
select
  coalesce(d.day, g.day) as day,
  coalesce(g.ga4_signups, 0) as ga4_signups,
  coalesce(d.db_signups, 0) as db_signups,
  (coalesce(g.ga4_signups, 0) - coalesce(d.db_signups, 0)) as diff,
  case
    when coalesce(d.db_signups, 0) = 0 then null
    else (coalesce(g.ga4_signups, 0) - coalesce(d.db_signups, 0))::numeric / d.db_signups
  end as pct_diff
from db_signups d
full outer join ga4_like g using (day)
order by day desc;

select
  date_trunc('day', event_time)::date as day,
  source,
  count(*) as cnt
from analytics_events
where event_name = 'signup_completed'
  and event_time >= now() - interval '14 days'
group by 1,2
order by 1 desc, 2;

select
  date_trunc('day', created_at)::date as day,
  signup_method,
  count(*) as db_signups
from users
where created_at >= now() - interval '14 days'
  and coalesce(is_internal,false) = false
  and coalesce(is_test,false) = false
group by 1,2
order by 1 desc, 2;

select
  (created_at at time zone 'UTC' at time zone 'Asia/Jakarta')::date as day_wib,
  count(*) as db_signups
from users
where created_at >= now() - interval '7 days'
group by 1
order by 1 desc;

select
  date_trunc('day', event_time)::date as day,
  count(distinct user_id) as users_fired
from analytics_events
where event_name = 'signup_completed'
  and event_time >= now() - interval '7 days'
group by 1
order by 1 desc;

with sample as (
  select user_id, created_at
  from users
  where created_at >= now() - interval '2 days'
    and coalesce(is_internal,false) = false
    and coalesce(is_test,false) = false
  order by created_at desc
  limit 50
)
select
  s.user_id,
  s.created_at as db_created_at,
  min(a.event_time) as first_analytics_event_time,
  count(*) as analytics_event_count
from sample s
left join analytics_events a
  on a.user_id = s.user_id
 and a.event_name = 'signup_completed'
 and a.event_time between s.created_at - interval '1 day' and s.created_at + interval '1 day'
group by 1,2
order by s.created_at desc;

select
  date_trunc('day', event_time)::date as day,
  count(*) as duplicate_groups
from (
  select
    event_name,
    user_id,
    event_time,
    coalesce(object_id,'-') as object_id,
    count(*) as c
  from analytics_events
  where event_time >= now() - interval '7 days'
    and user_id is not null
  group by 1,2,3,4
  having count(*) > 1
) d
group by 1
order by 1 desc;

select
  date_trunc('day', event_time)::date as day,
  source,
  count(*) as total,
  sum(case when user_id is null then 1 else 0 end) as missing_user_id
from analytics_events
where event_name = 'signup_completed'
  and event_time >= now() - interval '14 days'
group by 1,2
order by 1 desc, 2;

kpi_sources:
  - kpi: signup_completed
    official: product_db
    supporting: [ga4]
    note: "GA4 directional; DB official buat akun beneran"
  - kpi: activation_rate
    official: warehouse_events
    supporting: [ga4]
    note: "Business-critical -> server/warehouse validated"
  - kpi: landing_page_dropoff
    official: ga4
    supporting: []
    note: "Funnel web behavior -> GA4 paling pas"