-- Founder Dashboard v1.
--
-- Adds the schema, RLS, RPCs, and seed data to power /admin-dashboard.
-- The page itself reads through the admin RLS policy already defined in
-- 20260425170000_wave4b_admin_rls.sql (is_admin() helper, role='admin'
-- on public.users). Service-role writes from netlify functions bypass
-- RLS the same way reconcile.js / onboarding-drip.js already do.
--
-- Apply by pasting into Supabase SQL Editor.
--
-- This migration is idempotent: ALTER ... ADD COLUMN IF NOT EXISTS,
-- CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE on functions, DROP
-- POLICY IF EXISTS before each policy. Safe to re-run.

-- ============================================================
-- 1. customers: attribution + churn columns.
-- ============================================================
alter table public.customers
  add column if not exists acquisition_source text,
  add column if not exists utm_source         text,
  add column if not exists utm_medium         text,
  add column if not exists utm_campaign       text,
  add column if not exists utm_content        text,
  add column if not exists fbclid             text,
  add column if not exists churned_at         timestamptz;

comment on column public.customers.acquisition_source is
  'Marketing channel attribution. Coarse bucket: facebook_ad, organic, '
  'cold_email, referral, direct, unknown. Populated by stripe-webhook.js '
  'from session.client_reference_id / metadata at checkout.session.completed.';
comment on column public.customers.churned_at is
  'Set once on customer.subscription.deleted by stripe-webhook.js. Used '
  'to compute churned MRR by date. Null = not churned.';

-- ============================================================
-- 2. daily_snapshots: ad spend + manual-entry input metrics by day.
-- One row per UTC date. Stripe-derived numbers (active_subs, mrr_cents)
-- are computed live in get_dashboard_metrics() since reconcile.js
-- already keeps customers.status accurate.
-- ============================================================
create table if not exists public.daily_snapshots (
  date              date primary key,
  ad_spend_cents    int  not null default 0,
  ad_impressions   int  not null default 0,
  ad_clicks        int  not null default 0,
  active_creatives int  not null default 0,
  ig_posts         int  not null default 0,
  fb_posts         int  not null default 0,
  ig_views         int  not null default 0,
  fb_views         int  not null default 0,
  dms_received     int  not null default 0,
  dms_answered     int  not null default 0,
  cc_balance_cents int  not null default 0,
  computed_at      timestamptz not null default now()
);

-- ============================================================
-- 3. daily_actions: 5-checkbox commitment row, one per day. Drives
-- the streak. Single-user table — Jake is the only writer/reader.
-- ============================================================
create table if not exists public.daily_actions (
  date                  date primary key,
  ad_creative_uploaded  boolean not null default false,
  ig_post              boolean not null default false,
  fb_post              boolean not null default false,
  dms_zeroed           boolean not null default false,
  customer_touch       boolean not null default false,  -- 5th: 1 call/text/check-in
  notes                text,
  updated_at           timestamptz not null default now()
);

-- ============================================================
-- 4. streaks: rolling counter. Single row keyed by type='daily'.
-- freezes_available is reset to 1 on the first day of each month
-- by resolve_streak().
-- ============================================================
create table if not exists public.streaks (
  type               text primary key check (type in ('daily','weekly')),
  current_count      int  not null default 0,
  longest_count      int  not null default 0,
  last_hit_date      date,
  freezes_available  int  not null default 1,
  freeze_month       text,                          -- 'YYYY-MM' for monthly reset
  updated_at         timestamptz not null default now()
);

insert into public.streaks (type) values ('daily')
on conflict (type) do nothing;

-- ============================================================
-- 5. actions: action board. CRUD from the dashboard.
-- ============================================================
create table if not exists public.actions (
  id                    uuid primary key default gen_random_uuid(),
  title                 text not null,
  hypothesis            text,
  est_hours             int,
  target_metric         text,
  status                text not null default 'idea'
                          check (status in ('idea','in_progress','shipped','measuring','killed')),
  date_created          timestamptz not null default now(),
  date_shipped          timestamptz,
  result_notes          text,
  result_metric_delta   numeric
);

create index if not exists actions_status_idx on public.actions(status);

-- ============================================================
-- 6. dashboard_meta: singleton key-value table for one-shot flags
-- (e.g. action seed completion). Keyed so we can add more later.
-- ============================================================
create table if not exists public.dashboard_meta (
  key   text primary key,
  value jsonb not null default '{}'::jsonb
);

-- ============================================================
-- 7. Enable RLS + admin policies on every new table.
-- ============================================================
alter table public.daily_snapshots enable row level security;
alter table public.daily_actions   enable row level security;
alter table public.streaks         enable row level security;
alter table public.actions         enable row level security;
alter table public.dashboard_meta  enable row level security;

-- Helper: define ALL-permissions admin policies. is_admin() is the
-- same gate used in 20260425170000_wave4b_admin_rls.sql.
do $$
declare
  t text;
  tables text[] := array['daily_snapshots','daily_actions','streaks','actions','dashboard_meta'];
  cmd text;
begin
  foreach t in array tables loop
    foreach cmd in array array['select','insert','update','delete'] loop
      execute format('drop policy if exists %I on public.%I',
        format('%s admin %s', t, cmd), t);
      if cmd = 'insert' then
        -- INSERT takes WITH CHECK only.
        execute format(
          'create policy %I on public.%I for %s to authenticated with check (public.is_admin())',
          format('%s admin %s', t, cmd), t, cmd);
      elsif cmd = 'update' then
        -- UPDATE takes both USING (visibility) and WITH CHECK (post-write).
        execute format(
          'create policy %I on public.%I for %s to authenticated using (public.is_admin()) with check (public.is_admin())',
          format('%s admin %s', t, cmd), t, cmd);
      else
        -- SELECT and DELETE take USING only.
        execute format(
          'create policy %I on public.%I for %s to authenticated using (public.is_admin())',
          format('%s admin %s', t, cmd), t, cmd);
      end if;
    end loop;
  end loop;
end $$;

-- Service-role grants. Per the supabase-grants-on-drop-create memory:
-- always re-issue grants on new tables; auto-grants don't fire from
-- SQL Editor.
grant select, insert, update, delete on
  public.daily_snapshots,
  public.daily_actions,
  public.streaks,
  public.actions,
  public.dashboard_meta
to service_role;

-- ============================================================
-- 8. Pre-seed the action board on first run.
-- Items pulled from dashboard-plan.md §5.
-- ============================================================
do $$
begin
  if not exists (select 1 from public.dashboard_meta where key = 'actions_seeded') then
    insert into public.actions (title, hypothesis, est_hours, target_metric, status) values
      ('Postcard mailer with per-company QR + custom landing pages',
       'Will improve cold-channel CAC by ~30% via untapped offline channel',
       6, 'CAC', 'idea'),
      ('AI appointment-setter agent post-estimator (Bland.ai or Vapi)',
       'Auto-call homeowners within 60s of estimator submit; lifts trial->paid by reducing drop-off',
       8, 'trial_conversion', 'idea'),
      ('ManyChat flows fully built out',
       'Cuts DM response time to under 5 minutes; reclaims missed leads',
       4, 'response_time', 'idea'),
      ('Reddit ads test',
       'Diversify off Meta; test r/HomeImprovement and contractor subs',
       3, 'CAC', 'idea'),
      ('YouTube Shorts repurpose pipeline',
       'Repurpose IG Reels to YT Shorts for organic discovery surface',
       4, 'organic_trials', 'idea'),
      ('Upsell websites to existing customers',
       'New revenue line from already-warm fence pros; raises ARPU',
       12, 'LTV', 'idea'),
      ('Automated content generation pipeline for customers',
       'White-label content as new product line; expansion MRR',
       16, 'LTV', 'idea'),
      ('Lead-magnet free version of estimator (limited usage)',
       'Top-of-funnel signup engine; converts limited-tier to paid over time',
       10, 'trial_signups', 'idea');
    insert into public.dashboard_meta (key, value) values ('actions_seeded', jsonb_build_object('at', now()));
  end if;
end $$;

-- ============================================================
-- 9. RPCs.
-- ============================================================

-- get_dashboard_metrics(): single roll-up call for hero + funnel +
-- channels + churn list + 30d MRR series. Computes live from
-- customers + estimates + daily_snapshots.
--
-- ARPU is hard-coded to $149/mo (the only paid plan). When yearly
-- buyers are common this should split, but for v1 the dashboard rounds.
create or replace function public.get_dashboard_metrics()
returns jsonb
language plpgsql
security definer
set search_path = public
stable
as $$
declare
  v_now             timestamptz := now();
  v_arpu_cents      int := 14900;
  v_active_subs     int;
  v_active_trials   int;
  v_paid_14d        int;     -- paying customers acquired in last 14d (CAC denom)
  v_spend_14d_cents bigint;
  v_cac_cents       int;
  v_payback_months  numeric;
  v_new_paid_7d     int;
  v_churned_7d      int;
  v_net_mrr_7d_cents int;
  v_trials_30d      int;
  v_paid_30d        int;
  v_trial_paid_pct  numeric;
  v_channels        jsonb;
  v_churn_list      jsonb;
  v_mrr_series      jsonb;
begin
  if not is_admin() then
    raise exception 'admin only';
  end if;

  select count(*) into v_active_subs
    from customers
    where status = 'active';

  select count(*) into v_active_trials
    from customers
    where status = 'trial';

  select count(*) into v_paid_14d
    from customers
    where converted_at is not null
      and converted_at >= v_now - interval '14 days';

  select coalesce(sum(ad_spend_cents),0) into v_spend_14d_cents
    from daily_snapshots
    where date >= (v_now - interval '14 days')::date;

  v_cac_cents := case when v_paid_14d > 0
    then (v_spend_14d_cents / v_paid_14d)::int
    else null end;

  v_payback_months := case when v_cac_cents is not null and v_arpu_cents > 0
    then round((v_cac_cents::numeric / v_arpu_cents)::numeric, 2)
    else null end;

  select count(*) into v_new_paid_7d
    from customers
    where converted_at is not null
      and converted_at >= v_now - interval '7 days';

  select count(*) into v_churned_7d
    from customers
    where churned_at is not null
      and churned_at >= v_now - interval '7 days';

  v_net_mrr_7d_cents := (v_new_paid_7d - v_churned_7d) * v_arpu_cents;

  select count(*) into v_trials_30d
    from customers
    where created_at >= v_now - interval '30 days';

  select count(*) into v_paid_30d
    from customers
    where converted_at is not null
      and converted_at >= v_now - interval '30 days';

  v_trial_paid_pct := case when v_trials_30d > 0
    then round((v_paid_30d::numeric / v_trials_30d) * 100, 1)
    else null end;

  -- Channel breakdown for last 7 days. Groups by acquisition_source
  -- (null -> 'unknown'). Only counts trials that have converted as
  -- 'paid_7d' to compute CAC; spend allocation by channel is a v2
  -- nicety (today we lump all spend into facebook_ad if present).
  select coalesce(jsonb_agg(
    jsonb_build_object(
      'source', source,
      'trials_7d', trials_7d,
      'paid_7d', paid_7d
    )
    order by source
  ), '[]'::jsonb) into v_channels
  from (
    select coalesce(acquisition_source, 'unknown') as source,
      count(*) filter (where created_at >= v_now - interval '7 days') as trials_7d,
      count(*) filter (where converted_at >= v_now - interval '7 days') as paid_7d
    from customers
    group by coalesce(acquisition_source, 'unknown')
  ) s;

  -- Churn list, last 30 days.
  select coalesce(jsonb_agg(row order by row->>'churned_at' desc), '[]'::jsonb)
    into v_churn_list from (
    select jsonb_build_object(
      'id', id,
      'slug', slug,
      'business_name', business_name,
      'churned_at', churned_at,
      'health_note', health_note,
      'mrr_lost_cents', v_arpu_cents
    ) as row
    from customers
    where churned_at is not null
      and churned_at >= v_now - interval '30 days'
  ) s;

  -- MRR daily series, last 30 days. For each date, MRR = active_subs
  -- as-of that date (converted_at <= d AND (churned_at is null OR
  -- churned_at > d)) * ARPU.
  select jsonb_agg(jsonb_build_object('date', d, 'mrr_cents', mrr_cents) order by d)
    into v_mrr_series from (
    select
      d::date as d,
      v_arpu_cents * (
        select count(*)
        from customers c
        where c.converted_at is not null
          and c.converted_at <= d
          and (c.churned_at is null or c.churned_at > d)
      ) as mrr_cents
    from generate_series(v_now - interval '30 days', v_now, interval '1 day') as d
  ) s;

  return jsonb_build_object(
    'hero', jsonb_build_object(
      'cac_cents',         v_cac_cents,
      'cac_window_days',   14,
      'payback_months',    v_payback_months,
      'arpu_cents',        v_arpu_cents,
      'net_mrr_7d_cents',  v_net_mrr_7d_cents,
      'new_paid_7d',       v_new_paid_7d,
      'churned_7d',        v_churned_7d,
      'trial_paid_pct',    v_trial_paid_pct,
      'trials_30d',        v_trials_30d,
      'paid_30d',          v_paid_30d,
      'active_trials',     v_active_trials,
      'active_subs',       v_active_subs,
      'mrr_cents',         v_active_subs * v_arpu_cents
    ),
    'funnel', jsonb_build_object(
      'spend_14d_cents', v_spend_14d_cents,
      'paid_14d',        v_paid_14d,
      'active_trials',   v_active_trials,
      'active_subs',     v_active_subs
    ),
    'channels',   v_channels,
    'churn_list', coalesce(v_churn_list, '[]'::jsonb),
    'mrr_series', coalesce(v_mrr_series, '[]'::jsonb)
  );
end $$;

revoke all on function public.get_dashboard_metrics() from public;
grant execute on function public.get_dashboard_metrics() to authenticated;

-- get_dashboard_inputs(): daily_actions + streak + post counts for
-- the input panels and gamification heatmap.
create or replace function public.get_dashboard_inputs()
returns jsonb
language plpgsql
security definer
set search_path = public
stable
as $$
declare
  v_now           timestamptz := now();
  v_actions_30d   jsonb;
  v_snapshots_30d jsonb;
  v_streak        jsonb;
begin
  if not is_admin() then
    raise exception 'admin only';
  end if;

  select coalesce(jsonb_agg(row order by row->>'date' desc), '[]'::jsonb)
    into v_actions_30d from (
    select jsonb_build_object(
      'date', date,
      'ad_creative_uploaded', ad_creative_uploaded,
      'ig_post', ig_post,
      'fb_post', fb_post,
      'dms_zeroed', dms_zeroed,
      'customer_touch', customer_touch,
      'notes', notes
    ) as row
    from daily_actions
    where date >= (v_now - interval '30 days')::date
  ) s;

  select coalesce(jsonb_agg(row order by row->>'date' desc), '[]'::jsonb)
    into v_snapshots_30d from (
    select jsonb_build_object(
      'date', date,
      'ad_spend_cents', ad_spend_cents,
      'ad_impressions', ad_impressions,
      'ad_clicks', ad_clicks,
      'active_creatives', active_creatives,
      'ig_posts', ig_posts,
      'fb_posts', fb_posts,
      'dms_received', dms_received,
      'dms_answered', dms_answered
    ) as row
    from daily_snapshots
    where date >= (v_now - interval '30 days')::date
  ) s;

  select to_jsonb(s) into v_streak
    from streaks s
    where type = 'daily';

  return jsonb_build_object(
    'daily_actions_30d', v_actions_30d,
    'snapshots_30d',     v_snapshots_30d,
    'streak',            coalesce(v_streak, '{}'::jsonb)
  );
end $$;

revoke all on function public.get_dashboard_inputs() from public;
grant execute on function public.get_dashboard_inputs() to authenticated;

-- upsert_daily_action(): toggle one of the 5 fields on a given date.
-- Idempotent. Returns the resulting row.
create or replace function public.upsert_daily_action(
  p_date  date,
  p_field text,
  p_value boolean
)
returns daily_actions
language plpgsql
security definer
set search_path = public
as $$
declare
  v_row daily_actions;
begin
  if not is_admin() then
    raise exception 'admin only';
  end if;
  if p_field not in ('ad_creative_uploaded','ig_post','fb_post','dms_zeroed','customer_touch') then
    raise exception 'unknown field: %', p_field;
  end if;

  insert into daily_actions (date) values (p_date)
    on conflict (date) do nothing;

  execute format(
    'update daily_actions set %I = $1, updated_at = now() where date = $2 returning *',
    p_field
  ) using p_value, p_date into v_row;

  return v_row;
end $$;

revoke all on function public.upsert_daily_action(date, text, boolean) from public;
grant execute on function public.upsert_daily_action(date, text, boolean) to authenticated;

-- upsert_daily_snapshot(): manual entry for DMs / cc balance / post
-- counts that don't come from APIs. Set fields you want; null leaves
-- the existing value alone.
create or replace function public.upsert_daily_snapshot(
  p_date              date,
  p_ig_posts         int default null,
  p_fb_posts         int default null,
  p_dms_received     int default null,
  p_dms_answered     int default null,
  p_cc_balance_cents int default null
)
returns daily_snapshots
language plpgsql
security definer
set search_path = public
as $$
declare
  v_row daily_snapshots;
begin
  if not is_admin() then
    raise exception 'admin only';
  end if;

  insert into daily_snapshots (date) values (p_date)
    on conflict (date) do nothing;

  update daily_snapshots set
    ig_posts         = coalesce(p_ig_posts, ig_posts),
    fb_posts         = coalesce(p_fb_posts, fb_posts),
    dms_received     = coalesce(p_dms_received, dms_received),
    dms_answered     = coalesce(p_dms_answered, dms_answered),
    cc_balance_cents = coalesce(p_cc_balance_cents, cc_balance_cents),
    computed_at      = now()
    where date = p_date
    returning * into v_row;

  return v_row;
end $$;

revoke all on function public.upsert_daily_snapshot(date, int, int, int, int, int) from public;
grant execute on function public.upsert_daily_snapshot(date, int, int, int, int, int) to authenticated;

-- resolve_streak(): called on page load and by the snapshot cron.
-- Logic: "yesterday" = (current_date - 1 day). If yesterday's
-- daily_actions row had all 5 booleans true and last_hit_date < yesterday:
--   - increment current_count, update last_hit_date
-- Else if yesterday < last_hit_date by exactly 1 and a freeze is
-- available for the current month, consume it and keep the streak.
-- Else: reset current_count to 0 (but don't touch longest).
-- Always: refill freezes_available on month rollover.
create or replace function public.resolve_streak()
returns streaks
language plpgsql
security definer
set search_path = public
as $$
declare
  v_row             streaks;
  v_today           date := current_date;
  v_yesterday       date := current_date - 1;
  v_y_actions       daily_actions;
  v_all_hit         boolean;
  v_current_month   text := to_char(current_date, 'YYYY-MM');
begin
  -- Allow both admin (browser) and service_role (cron) callers. The
  -- cron's REST request authenticates as service_role, so auth.role()
  -- returns 'service_role' inside the function.
  if not (auth.role() = 'service_role' or is_admin()) then
    raise exception 'admin only';
  end if;

  select * into v_row from streaks where type = 'daily';
  if not found then
    insert into streaks (type) values ('daily') returning * into v_row;
  end if;

  -- Monthly freeze refill.
  if v_row.freeze_month is null or v_row.freeze_month <> v_current_month then
    update streaks
      set freezes_available = 1,
          freeze_month = v_current_month,
          updated_at = now()
      where type = 'daily'
      returning * into v_row;
  end if;

  -- If we already resolved for yesterday today, no-op.
  if v_row.last_hit_date is not null and v_row.last_hit_date >= v_yesterday then
    return v_row;
  end if;

  select * into v_y_actions from daily_actions where date = v_yesterday;
  v_all_hit := coalesce(v_y_actions.ad_creative_uploaded, false)
           and coalesce(v_y_actions.ig_post, false)
           and coalesce(v_y_actions.fb_post, false)
           and coalesce(v_y_actions.dms_zeroed, false)
           and coalesce(v_y_actions.customer_touch, false);

  if v_all_hit then
    update streaks
      set current_count = current_count + 1,
          longest_count = greatest(longest_count, current_count + 1),
          last_hit_date = v_yesterday,
          updated_at    = now()
      where type = 'daily'
      returning * into v_row;
  elsif v_row.freezes_available > 0 and v_row.last_hit_date is not null
        and v_row.last_hit_date = v_yesterday - 1 then
    -- Consume one freeze. Streak count stays; last_hit_date advances
    -- so we don't double-consume.
    update streaks
      set freezes_available = freezes_available - 1,
          last_hit_date     = v_yesterday,
          updated_at        = now()
      where type = 'daily'
      returning * into v_row;
  else
    update streaks
      set current_count = 0,
          last_hit_date = v_yesterday,
          updated_at    = now()
      where type = 'daily'
      returning * into v_row;
  end if;

  return v_row;
end $$;

revoke all on function public.resolve_streak() from public;
grant execute on function public.resolve_streak() to authenticated;

-- update_action(): partial update on actions row from the kanban.
create or replace function public.update_action(
  p_id     uuid,
  p_patch  jsonb
)
returns actions
language plpgsql
security definer
set search_path = public
as $$
declare
  v_row actions;
begin
  if not is_admin() then
    raise exception 'admin only';
  end if;

  update actions set
    title               = coalesce(p_patch->>'title', title),
    hypothesis          = coalesce(p_patch->>'hypothesis', hypothesis),
    est_hours           = coalesce((p_patch->>'est_hours')::int, est_hours),
    target_metric       = coalesce(p_patch->>'target_metric', target_metric),
    status              = coalesce(p_patch->>'status', status),
    date_shipped        = case
      when p_patch ? 'date_shipped'
        then nullif(p_patch->>'date_shipped','')::timestamptz
      when p_patch->>'status' = 'shipped' and date_shipped is null
        then now()
      else date_shipped end,
    result_notes        = coalesce(p_patch->>'result_notes', result_notes),
    result_metric_delta = coalesce((p_patch->>'result_metric_delta')::numeric, result_metric_delta)
    where id = p_id
    returning * into v_row;

  return v_row;
end $$;

revoke all on function public.update_action(uuid, jsonb) from public;
grant execute on function public.update_action(uuid, jsonb) to authenticated;
