create extension if not exists pgcrypto;

create or replace function public.together_ready_touch_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create table if not exists public.together_ready_member_visits (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users (id) on delete cascade,
  title text not null default 'Dad time',
  starts_at timestamptz not null,
  ends_at timestamptz,
  timezone text,
  pickup_time text,
  dropoff_time text,
  visit_pattern text,
  status text not null default 'upcoming'
    check (status in ('upcoming', 'active', 'completed', 'cancelled')),
  source text not null default 'checklist',
  snapshot jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists together_ready_member_visits_user_start_idx
  on public.together_ready_member_visits (user_id, starts_at)
  where status in ('upcoming', 'active');

create index if not exists together_ready_member_visits_status_idx
  on public.together_ready_member_visits (status);

drop trigger if exists together_ready_member_visits_touch_updated_at
  on public.together_ready_member_visits;

create trigger together_ready_member_visits_touch_updated_at
before update on public.together_ready_member_visits
for each row
execute function public.together_ready_touch_updated_at();

create table if not exists public.together_ready_visit_checklist_items (
  id uuid primary key default gen_random_uuid(),
  visit_id uuid not null references public.together_ready_member_visits (id) on delete cascade,
  user_id uuid not null references auth.users (id) on delete cascade,
  item_type text not null
    check (
      item_type in (
        'grocery',
        'activity_supply',
        'photo_printing',
        'scrapbook_supply',
        'clothing',
        'packing',
        'comfort',
        'printable',
        'meal_plan',
        'weather',
        'reminder',
        'activity_prep',
        'custom'
      )
    ),
  label text not null,
  status text not null default 'needs_attention'
    check (status in ('needs_attention', 'completed', 'skipped')),
  due_window text not null default 'before_pickup'
    check (due_window in ('now', 'day_before', 'before_pickup', 'during_visit', 'after_visit', 'anytime')),
  completed_at timestamptz,
  sort_order integer not null default 0,
  source text not null default 'checklist',
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists together_ready_visit_checklist_items_visit_status_idx
  on public.together_ready_visit_checklist_items (visit_id, status, sort_order);

create index if not exists together_ready_visit_checklist_items_user_type_idx
  on public.together_ready_visit_checklist_items (user_id, item_type, status);

create index if not exists together_ready_visit_checklist_items_due_window_idx
  on public.together_ready_visit_checklist_items (visit_id, due_window)
  where status = 'needs_attention';

drop trigger if exists together_ready_visit_checklist_items_touch_updated_at
  on public.together_ready_visit_checklist_items;

create trigger together_ready_visit_checklist_items_touch_updated_at
before update on public.together_ready_visit_checklist_items
for each row
execute function public.together_ready_touch_updated_at();

create or replace view public.together_ready_next_visit_dashboard
with (security_invoker = true)
as
with next_visits as (
  select
    visits.*,
    row_number() over (
      partition by visits.user_id
      order by
        case when visits.status = 'active' then 0 else 1 end,
        visits.starts_at asc
    ) as visit_rank
  from public.together_ready_member_visits visits
  where visits.status = 'active'
    or (visits.status = 'upcoming' and visits.starts_at >= now())
)
select
  visits.user_id,
  visits.id as visit_id,
  visits.title,
  visits.starts_at,
  visits.ends_at,
  visits.timezone,
  visits.pickup_time,
  visits.dropoff_time,
  visits.status as visit_status,
  count(items.id) filter (where items.status = 'needs_attention') as open_item_count,
  count(items.id) filter (where items.status = 'completed') as completed_item_count,
  jsonb_agg(
    jsonb_build_object(
      'id', items.id,
      'item_type', items.item_type,
      'label', items.label,
      'status', items.status,
      'due_window', items.due_window,
      'completed_at', items.completed_at,
      'sort_order', items.sort_order,
      'metadata', items.metadata
    )
    order by items.sort_order, items.created_at
  ) filter (where items.id is not null) as checklist_items
from next_visits visits
left join public.together_ready_visit_checklist_items items
  on items.visit_id = visits.id
where visits.visit_rank = 1
group by visits.user_id, visits.id, visits.title, visits.starts_at, visits.ends_at, visits.timezone, visits.pickup_time, visits.dropoff_time, visits.status;

alter table public.together_ready_member_visits enable row level security;
alter table public.together_ready_visit_checklist_items enable row level security;

grant select, insert, update, delete on public.together_ready_member_visits to authenticated;
grant select, insert, update, delete on public.together_ready_visit_checklist_items to authenticated;
grant select on public.together_ready_next_visit_dashboard to authenticated;

grant select, insert, update, delete on public.together_ready_member_visits to service_role;
grant select, insert, update, delete on public.together_ready_visit_checklist_items to service_role;
grant select on public.together_ready_next_visit_dashboard to service_role;

drop policy if exists "members manage their own Together Ready visits"
  on public.together_ready_member_visits;

create policy "members manage their own Together Ready visits"
  on public.together_ready_member_visits
  for all
  to authenticated
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

drop policy if exists "members manage their own Together Ready visit checklist items"
  on public.together_ready_visit_checklist_items;

create policy "members manage their own Together Ready visit checklist items"
  on public.together_ready_visit_checklist_items
  for all
  to authenticated
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

drop policy if exists "service role manages Together Ready visits"
  on public.together_ready_member_visits;

create policy "service role manages Together Ready visits"
  on public.together_ready_member_visits
  for all
  to service_role
  using (true)
  with check (true);

drop policy if exists "service role manages Together Ready visit checklist items"
  on public.together_ready_visit_checklist_items;

create policy "service role manages Together Ready visit checklist items"
  on public.together_ready_visit_checklist_items
  for all
  to service_role
  using (true)
  with check (true);

comment on table public.together_ready_member_visits is
  'Paid-user parenting-time visits used by the member dashboard.';

comment on table public.together_ready_visit_checklist_items is
  'Persistent checklist item status for each paid-user visit. Drives what still needs attention before pickup.';
