-- The Forge Alpha — Initial Supabase schema
-- Version: 0.1.0
-- Purpose: shared family data, missions, school, verification, FC ledger, rewards, and alarms.
-- Safe design: Row Level Security is enabled on every public table.

create extension if not exists pgcrypto;

create schema if not exists app_private;
revoke all on schema app_private from public;
grant usage on schema app_private to authenticated;

-- ---------- CORE FAMILY TABLES ----------

create table if not exists public.families (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_by uuid not null references auth.users(id) on delete restrict default auth.uid(),
  created_at timestamptz not null default now()
);

create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  display_name text not null,
  default_role text not null default 'parent'
    check (default_role in ('parent', 'child')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.family_members (
  family_id uuid not null references public.families(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  member_role text not null check (member_role in ('parent', 'child')),
  display_name text not null,
  joined_at timestamptz not null default now(),
  primary key (family_id, user_id)
);

-- ---------- MISSION BUILDER ----------

create table if not exists public.mission_phases (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  name text not null,
  slug text not null,
  icon text,
  sort_order integer not null default 0,
  is_active boolean not null default true,
  default_start_time time,
  created_at timestamptz not null default now(),
  unique (family_id, slug)
);

create table if not exists public.mission_categories (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  phase_id uuid not null references public.mission_phases(id) on delete cascade,
  name text not null,
  icon text,
  sort_order integer not null default 0,
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists public.mission_actions (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  category_id uuid not null references public.mission_categories(id) on delete cascade,
  title text not null,
  icon text,
  instructions text,
  sort_order integer not null default 0,
  fc_reward integer not null default 0 check (fc_reward >= 0),
  verification_required boolean not null default false,
  verification_type text not null default 'trust'
    check (verification_type in ('trust', 'visual', 'quick', 'critical', 'outcome')),
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists public.mission_runs (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  phase_id uuid not null references public.mission_phases(id) on delete cascade,
  child_id uuid not null references auth.users(id) on delete cascade,
  run_date date not null default current_date,
  status text not null default 'active'
    check (status in ('active', 'logged_complete', 'verified_complete', 'closed')),
  started_at timestamptz not null default now(),
  logged_complete_at timestamptz,
  verified_complete_at timestamptz,
  unique (phase_id, child_id, run_date)
);

create table if not exists public.action_logs (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  run_id uuid not null references public.mission_runs(id) on delete cascade,
  action_id uuid not null references public.mission_actions(id) on delete cascade,
  child_id uuid not null references auth.users(id) on delete cascade,
  status text not null default 'logged'
    check (status in ('logged', 'pending_verification', 'verified', 'retry', 'undone')),
  completed_at timestamptz not null default now(),
  undone_at timestamptz,
  updated_at timestamptz not null default now(),
  unique (run_id, action_id)
);

create table if not exists public.verifications (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  action_log_id uuid not null unique references public.action_logs(id) on delete cascade,
  status text not null default 'pending'
    check (status in ('pending', 'verified', 'retry')),
  prompt_count integer not null default 0 check (prompt_count >= 0),
  verified_by uuid references auth.users(id) on delete set null,
  note text,
  requested_at timestamptz not null default now(),
  resolved_at timestamptz
);

-- ---------- FORGE CREDITS ----------

create table if not exists public.fc_ledger (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  child_id uuid not null references auth.users(id) on delete cascade,
  amount integer not null check (amount <> 0),
  reason text not null,
  source_type text not null default 'manual'
    check (source_type in (
      'mission', 'verification', 'bonus', 'reward_redemption',
      'school_adjustment', 'manual'
    )),
  source_id uuid,
  created_by uuid references auth.users(id) on delete set null default auth.uid(),
  created_at timestamptz not null default now()
);

-- ---------- SCHOOL ----------

create table if not exists public.school_classes (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  child_id uuid not null references auth.users(id) on delete cascade,
  name text not null,
  icon text,
  teacher_name text,
  sort_order integer not null default 0,
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists public.assignments (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  class_id uuid not null references public.school_classes(id) on delete cascade,
  child_id uuid not null references auth.users(id) on delete cascade,
  title text not null,
  description text,
  due_at timestamptz,
  status text not null default 'assigned'
    check (status in ('assigned', 'in_progress', 'completed', 'turned_in', 'missing')),
  work_in_proper_folder boolean not null default false,
  next_day_items_packed boolean not null default false,
  fc_value integer not null default 0 check (fc_value >= 0),
  created_by uuid references auth.users(id) on delete set null default auth.uid(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- ---------- REWARDS ----------

create table if not exists public.rewards (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  title text not null,
  description text,
  cost_fc integer not null check (cost_fc > 0),
  icon text,
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists public.reward_redemptions (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  reward_id uuid not null references public.rewards(id) on delete restrict,
  child_id uuid not null references auth.users(id) on delete cascade,
  cost_fc integer not null check (cost_fc > 0),
  status text not null default 'requested'
    check (status in ('requested', 'approved', 'fulfilled', 'declined')),
  requested_at timestamptz not null default now(),
  resolved_by uuid references auth.users(id) on delete set null,
  resolved_at timestamptz
);

-- ---------- ALARMS ----------

create table if not exists public.alarm_rules (
  id uuid primary key default gen_random_uuid(),
  family_id uuid not null references public.families(id) on delete cascade,
  child_id uuid not null references auth.users(id) on delete cascade,
  phase_id uuid references public.mission_phases(id) on delete set null,
  label text not null,
  alarm_time time not null,
  days_of_week smallint[] not null default array[1,2,3,4,5]::smallint[],
  challenge_type text not null default 'none'
    check (challenge_type in ('none', 'math', 'forge_question')),
  difficulty text not null default 'easy'
    check (difficulty in ('easy', 'medium', 'hard')),
  snooze_minutes integer not null default 5 check (snooze_minutes between 1 and 30),
  is_enabled boolean not null default true,
  created_at timestamptz not null default now()
);

-- ---------- PRIVATE SECURITY HELPERS ----------

create or replace function app_private.is_family_member(target_family_id uuid)
returns boolean
language sql
stable
security definer
set search_path = public, pg_temp
as $$
  select exists (
    select 1
    from public.family_members fm
    where fm.family_id = target_family_id
      and fm.user_id = auth.uid()
  );
$$;

create or replace function app_private.is_family_parent(target_family_id uuid)
returns boolean
language sql
stable
security definer
set search_path = public, pg_temp
as $$
  select exists (
    select 1
    from public.family_members fm
    where fm.family_id = target_family_id
      and fm.user_id = auth.uid()
      and fm.member_role = 'parent'
  );
$$;

grant execute on function app_private.is_family_member(uuid) to authenticated;
grant execute on function app_private.is_family_parent(uuid) to authenticated;

-- Creates the first family + parent membership after sign-up.
create or replace function public.bootstrap_family(
  family_name text,
  parent_display_name text
)
returns uuid
language plpgsql
security definer
set search_path = public, pg_temp
as $$
declare
  new_family_id uuid;
begin
  if auth.uid() is null then
    raise exception 'Authentication required';
  end if;

  insert into public.profiles (id, display_name, default_role)
  values (auth.uid(), parent_display_name, 'parent')
  on conflict (id) do update
    set display_name = excluded.display_name,
        default_role = 'parent',
        updated_at = now();

  insert into public.families (name, created_by)
  values (family_name, auth.uid())
  returning id into new_family_id;

  insert into public.family_members (
    family_id, user_id, member_role, display_name
  )
  values (
    new_family_id, auth.uid(), 'parent', parent_display_name
  );

  return new_family_id;
end;
$$;

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

-- ---------- ROW LEVEL SECURITY ----------

alter table public.families enable row level security;
alter table public.profiles enable row level security;
alter table public.family_members enable row level security;
alter table public.mission_phases enable row level security;
alter table public.mission_categories enable row level security;
alter table public.mission_actions enable row level security;
alter table public.mission_runs enable row level security;
alter table public.action_logs enable row level security;
alter table public.verifications enable row level security;
alter table public.fc_ledger enable row level security;
alter table public.school_classes enable row level security;
alter table public.assignments enable row level security;
alter table public.rewards enable row level security;
alter table public.reward_redemptions enable row level security;
alter table public.alarm_rules enable row level security;

-- Drop policies so this migration can be safely rerun.
do $$
declare
  r record;
begin
  for r in
    select schemaname, tablename, policyname
    from pg_policies
    where schemaname = 'public'
      and tablename in (
        'families','profiles','family_members','mission_phases',
        'mission_categories','mission_actions','mission_runs','action_logs',
        'verifications','fc_ledger','school_classes','assignments',
        'rewards','reward_redemptions','alarm_rules'
      )
  loop
    execute format(
      'drop policy if exists %I on %I.%I',
      r.policyname, r.schemaname, r.tablename
    );
  end loop;
end $$;

-- Families
create policy "families_select_member"
on public.families for select to authenticated
using (app_private.is_family_member(id));

create policy "families_insert_creator"
on public.families for insert to authenticated
with check (created_by = auth.uid());

create policy "families_update_parent"
on public.families for update to authenticated
using (app_private.is_family_parent(id))
with check (app_private.is_family_parent(id));

create policy "families_delete_parent"
on public.families for delete to authenticated
using (app_private.is_family_parent(id));

-- Profiles
create policy "profiles_select_self_or_family"
on public.profiles for select to authenticated
using (
  id = auth.uid()
  or exists (
    select 1
    from public.family_members me
    join public.family_members them
      on them.family_id = me.family_id
    where me.user_id = auth.uid()
      and them.user_id = profiles.id
  )
);

create policy "profiles_insert_self"
on public.profiles for insert to authenticated
with check (id = auth.uid());

create policy "profiles_update_self"
on public.profiles for update to authenticated
using (id = auth.uid())
with check (id = auth.uid());

-- Family members
create policy "family_members_select_member"
on public.family_members for select to authenticated
using (app_private.is_family_member(family_id));

create policy "family_members_insert_parent_or_creator"
on public.family_members for insert to authenticated
with check (
  app_private.is_family_parent(family_id)
  or (
    user_id = auth.uid()
    and exists (
      select 1 from public.families f
      where f.id = family_id and f.created_by = auth.uid()
    )
  )
);

create policy "family_members_update_parent"
on public.family_members for update to authenticated
using (app_private.is_family_parent(family_id))
with check (app_private.is_family_parent(family_id));

create policy "family_members_delete_parent"
on public.family_members for delete to authenticated
using (app_private.is_family_parent(family_id));

-- Reusable parent-managed tables
create policy "mission_phases_select_family"
on public.mission_phases for select to authenticated
using (app_private.is_family_member(family_id));
create policy "mission_phases_parent_all"
on public.mission_phases for all to authenticated
using (app_private.is_family_parent(family_id))
with check (app_private.is_family_parent(family_id));

create policy "mission_categories_select_family"
on public.mission_categories for select to authenticated
using (app_private.is_family_member(family_id));
create policy "mission_categories_parent_all"
on public.mission_categories for all to authenticated
using (app_private.is_family_parent(family_id))
with check (app_private.is_family_parent(family_id));

create policy "mission_actions_select_family"
on public.mission_actions for select to authenticated
using (app_private.is_family_member(family_id));
create policy "mission_actions_parent_all"
on public.mission_actions for all to authenticated
using (app_private.is_family_parent(family_id))
with check (app_private.is_family_parent(family_id));

-- Mission runs
create policy "mission_runs_select_family"
on public.mission_runs for select to authenticated
using (app_private.is_family_member(family_id));

create policy "mission_runs_insert_child_or_parent"
on public.mission_runs for insert to authenticated
with check (
  app_private.is_family_parent(family_id)
  or (app_private.is_family_member(family_id) and child_id = auth.uid())
);

create policy "mission_runs_update_child_or_parent"
on public.mission_runs for update to authenticated
using (
  app_private.is_family_parent(family_id)
  or (app_private.is_family_member(family_id) and child_id = auth.uid())
)
with check (
  app_private.is_family_parent(family_id)
  or (app_private.is_family_member(family_id) and child_id = auth.uid())
);

-- Action logs
create policy "action_logs_select_family"
on public.action_logs for select to authenticated
using (app_private.is_family_member(family_id));

create policy "action_logs_insert_child_or_parent"
on public.action_logs for insert to authenticated
with check (
  app_private.is_family_parent(family_id)
  or (app_private.is_family_member(family_id) and child_id = auth.uid())
);

create policy "action_logs_update_child_or_parent"
on public.action_logs for update to authenticated
using (
  app_private.is_family_parent(family_id)
  or (app_private.is_family_member(family_id) and child_id = auth.uid())
)
with check (
  app_private.is_family_parent(family_id)
  or (app_private.is_family_member(family_id) and child_id = auth.uid())
);

create policy "action_logs_delete_parent"
on public.action_logs for delete to authenticated
using (app_private.is_family_parent(family_id));

-- Verifications
create policy "verifications_select_family"
on public.verifications for select to authenticated
using (app_private.is_family_member(family_id));

create policy "verifications_parent_all"
on public.verifications for all to authenticated
using (app_private.is_family_parent(family_id))
with check (app_private.is_family_parent(family_id));

-- FC ledger: readable by family; writable only by parents.
create policy "fc_ledger_select_family"
on public.fc_ledger for select to authenticated
using (app_private.is_family_member(family_id));

create policy "fc_ledger_parent_insert"
on public.fc_ledger for insert to authenticated
with check (app_private.is_family_parent(family_id));

create policy "fc_ledger_parent_update"
on public.fc_ledger for update to authenticated
using (app_private.is_family_parent(family_id))
with check (app_private.is_family_parent(family_id));

create policy "fc_ledger_parent_delete"
on public.fc_ledger for delete to authenticated
using (app_private.is_family_parent(family_id));

-- School classes
create policy "school_classes_select_family"
on public.school_classes for select to authenticated
using (app_private.is_family_member(family_id));

create policy "school_classes_parent_all"
on public.school_classes for all to authenticated
using (app_private.is_family_parent(family_id))
with check (app_private.is_family_parent(family_id));

-- Assignments: child can update their own progress; parents manage all.
create policy "assignments_select_family"
on public.assignments for select to authenticated
using (app_private.is_family_member(family_id));

create policy "assignments_insert_parent"
on public.assignments for insert to authenticated
with check (app_private.is_family_parent(family_id));

create policy "assignments_update_child_or_parent"
on public.assignments for update to authenticated
using (
  app_private.is_family_parent(family_id)
  or (app_private.is_family_member(family_id) and child_id = auth.uid())
)
with check (
  app_private.is_family_parent(family_id)
  or (app_private.is_family_member(family_id) and child_id = auth.uid())
);

create policy "assignments_delete_parent"
on public.assignments for delete to authenticated
using (app_private.is_family_parent(family_id));

-- Rewards
create policy "rewards_select_family"
on public.rewards for select to authenticated
using (app_private.is_family_member(family_id));

create policy "rewards_parent_all"
on public.rewards for all to authenticated
using (app_private.is_family_parent(family_id))
with check (app_private.is_family_parent(family_id));

create policy "reward_redemptions_select_family"
on public.reward_redemptions for select to authenticated
using (app_private.is_family_member(family_id));

create policy "reward_redemptions_insert_child_or_parent"
on public.reward_redemptions for insert to authenticated
with check (
  app_private.is_family_parent(family_id)
  or (app_private.is_family_member(family_id) and child_id = auth.uid())
);

create policy "reward_redemptions_update_parent"
on public.reward_redemptions for update to authenticated
using (app_private.is_family_parent(family_id))
with check (app_private.is_family_parent(family_id));

-- Alarms: child can read; parent controls.
create policy "alarm_rules_select_family"
on public.alarm_rules for select to authenticated
using (app_private.is_family_member(family_id));

create policy "alarm_rules_parent_all"
on public.alarm_rules for all to authenticated
using (app_private.is_family_parent(family_id))
with check (app_private.is_family_parent(family_id));

-- ---------- INDEXES ----------

create index if not exists idx_family_members_user
  on public.family_members(user_id);

create index if not exists idx_mission_phases_family
  on public.mission_phases(family_id, sort_order);

create index if not exists idx_mission_categories_phase
  on public.mission_categories(phase_id, sort_order);

create index if not exists idx_mission_actions_category
  on public.mission_actions(category_id, sort_order);

create index if not exists idx_mission_runs_child_date
  on public.mission_runs(child_id, run_date desc);

create index if not exists idx_action_logs_run
  on public.action_logs(run_id);

create index if not exists idx_verifications_family_status
  on public.verifications(family_id, status, requested_at);

create index if not exists idx_fc_ledger_child_date
  on public.fc_ledger(child_id, created_at desc);

create index if not exists idx_assignments_child_due
  on public.assignments(child_id, due_at);

-- ---------- REALTIME ----------

do $$
begin
  if not exists (
    select 1 from pg_publication_tables
    where pubname = 'supabase_realtime'
      and schemaname = 'public'
      and tablename = 'action_logs'
  ) then
    alter publication supabase_realtime add table public.action_logs;
  end if;

  if not exists (
    select 1 from pg_publication_tables
    where pubname = 'supabase_realtime'
      and schemaname = 'public'
      and tablename = 'verifications'
  ) then
    alter publication supabase_realtime add table public.verifications;
  end if;

  if not exists (
    select 1 from pg_publication_tables
    where pubname = 'supabase_realtime'
      and schemaname = 'public'
      and tablename = 'fc_ledger'
  ) then
    alter publication supabase_realtime add table public.fc_ledger;
  end if;
end $$;

-- Completed successfully.