-- ACC Community Supabase setup
-- Run this in the Supabase SQL editor after creating the project.

create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text,
  display_name text,
  location text,
  interests text[] default '{}',
  bio text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create table if not exists public.member_posts (
  id uuid primary key default gen_random_uuid(),
  author_id uuid not null references public.profiles(id) on delete cascade,
  topic text not null,
  title text not null,
  body text not null,
  status text not null default 'pending' check (status in ('pending','approved','hidden')),
  created_at timestamptz default now()
);

create table if not exists public.member_actions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles(id) on delete cascade,
  action_type text not null,
  details text,
  created_at timestamptz default now()
);

create table if not exists public.member_companion_progress (
  user_id uuid primary key references auth.users(id) on delete cascade,
  progress jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

do $$
begin
  if to_regclass('public.member_tracker_progress') is not null then
    execute '
      insert into public.member_companion_progress (user_id, progress, updated_at)
      select user_id, progress, coalesce(updated_at, now())
      from public.member_tracker_progress
      on conflict (user_id) do update
      set progress = excluded.progress,
          updated_at = greatest(public.member_companion_progress.updated_at, excluded.updated_at)
    ';
  end if;
end;
$$;

create table if not exists public.forum_topics (
  id uuid primary key default gen_random_uuid(),
  author_id uuid not null references public.profiles(id) on delete cascade,
  category text not null default 'General',
  title text not null,
  body text not null,
  status text not null default 'open' check (status in ('open','closed','hidden')),
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create table if not exists public.forum_replies (
  id uuid primary key default gen_random_uuid(),
  topic_id uuid not null references public.forum_topics(id) on delete cascade,
  author_id uuid not null references public.profiles(id) on delete cascade,
  body text not null,
  status text not null default 'visible' check (status in ('visible','hidden')),
  created_at timestamptz default now()
);

create table if not exists public.chat_groups (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references public.profiles(id) on delete cascade,
  name text not null,
  description text,
  join_code text not null unique,
  created_at timestamptz default now()
);

create table if not exists public.chat_members (
  group_id uuid not null references public.chat_groups(id) on delete cascade,
  user_id uuid not null references public.profiles(id) on delete cascade,
  role text not null default 'member' check (role in ('owner','member')),
  created_at timestamptz default now(),
  primary key (group_id,user_id)
);

create table if not exists public.chat_messages (
  id uuid primary key default gen_random_uuid(),
  group_id uuid not null references public.chat_groups(id) on delete cascade,
  author_id uuid not null references public.profiles(id) on delete cascade,
  body text not null,
  created_at timestamptz default now()
);

alter table public.profiles enable row level security;
alter table public.member_posts enable row level security;
alter table public.member_actions enable row level security;
alter table public.member_companion_progress enable row level security;
alter table public.forum_topics enable row level security;
alter table public.forum_replies enable row level security;
alter table public.chat_groups enable row level security;
alter table public.chat_members enable row level security;
alter table public.chat_messages enable row level security;

drop policy if exists "Profiles are viewable by signed-in members" on public.profiles;
create policy "Profiles are viewable by signed-in members"
on public.profiles for select
to authenticated
using (true);

drop policy if exists "Members create their own profile" on public.profiles;
create policy "Members create their own profile"
on public.profiles for insert
to authenticated
with check (auth.uid() = id);

drop policy if exists "Members update their own profile" on public.profiles;
create policy "Members update their own profile"
on public.profiles for update
to authenticated
using (auth.uid() = id)
with check (auth.uid() = id);

drop policy if exists "Members can read approved or own posts" on public.member_posts;
create policy "Members can read approved or own posts"
on public.member_posts for select
to authenticated
using (status = 'approved' or auth.uid() = author_id);

drop policy if exists "Members can create their own posts" on public.member_posts;
create policy "Members can create their own posts"
on public.member_posts for insert
to authenticated
with check (auth.uid() = author_id);

drop policy if exists "Members can edit their pending posts" on public.member_posts;
create policy "Members can edit their pending posts"
on public.member_posts for update
to authenticated
using (auth.uid() = author_id and status = 'pending')
with check (auth.uid() = author_id);

drop policy if exists "Members can read their own actions" on public.member_actions;
create policy "Members can read their own actions"
on public.member_actions for select
to authenticated
using (auth.uid() = user_id);

drop policy if exists "Members can save their own actions" on public.member_actions;
create policy "Members can save their own actions"
on public.member_actions for insert
to authenticated
with check (auth.uid() = user_id);

grant select, insert, update, delete on public.member_companion_progress to authenticated;

drop policy if exists "Members can read their own companion progress" on public.member_companion_progress;
create policy "Members can read their own companion progress"
on public.member_companion_progress for select
to authenticated
using (auth.uid() = user_id);

drop policy if exists "Members can save their own companion progress" on public.member_companion_progress;
create policy "Members can save their own companion progress"
on public.member_companion_progress for insert
to authenticated
with check (auth.uid() = user_id);

drop policy if exists "Members can update their own companion progress" on public.member_companion_progress;
create policy "Members can update their own companion progress"
on public.member_companion_progress for update
to authenticated
using (auth.uid() = user_id)
with check (auth.uid() = user_id);

drop policy if exists "Members can delete their own companion progress" on public.member_companion_progress;
create policy "Members can delete their own companion progress"
on public.member_companion_progress for delete
to authenticated
using (auth.uid() = user_id);

drop policy if exists "Members can read open forum topics" on public.forum_topics;
create policy "Members can read open forum topics"
on public.forum_topics for select
to authenticated
using (status = 'open' or auth.uid() = author_id);

drop policy if exists "Members can create forum topics" on public.forum_topics;
create policy "Members can create forum topics"
on public.forum_topics for insert
to authenticated
with check (auth.uid() = author_id);

drop policy if exists "Members can edit their own forum topics" on public.forum_topics;
create policy "Members can edit their own forum topics"
on public.forum_topics for update
to authenticated
using (auth.uid() = author_id)
with check (auth.uid() = author_id);

drop policy if exists "Members can read visible forum replies" on public.forum_replies;
create policy "Members can read visible forum replies"
on public.forum_replies for select
to authenticated
using (
  status = 'visible'
  and exists (
    select 1 from public.forum_topics
    where public.forum_topics.id = forum_replies.topic_id
    and public.forum_topics.status = 'open'
  )
);

drop policy if exists "Members can create forum replies" on public.forum_replies;
create policy "Members can create forum replies"
on public.forum_replies for insert
to authenticated
with check (auth.uid() = author_id);

drop policy if exists "Members can create chat groups" on public.chat_groups;
create policy "Members can create chat groups"
on public.chat_groups for insert
to authenticated
with check (auth.uid() = owner_id);

drop policy if exists "Members can find groups by invite code" on public.chat_groups;
drop policy if exists "Members can read their chat groups" on public.chat_groups;
create policy "Members can read their chat groups"
on public.chat_groups for select
to authenticated
using (
  auth.uid() = owner_id
  or exists (
    select 1 from public.chat_members
    where public.chat_members.group_id = chat_groups.id
    and public.chat_members.user_id = auth.uid()
  )
);

drop policy if exists "Members can read their group memberships" on public.chat_members;
create policy "Members can read their group memberships"
on public.chat_members for select
to authenticated
using (auth.uid() = user_id);

drop policy if exists "Members can join groups as themselves" on public.chat_members;
create policy "Members can join groups as themselves"
on public.chat_members for insert
to authenticated
with check (auth.uid() = user_id);

create or replace function public.join_chat_group_by_code(invite_code text)
returns table(group_id uuid, group_name text)
language plpgsql
security definer
set search_path = public
as $$
declare
  found_group public.chat_groups%rowtype;
begin
  select *
  into found_group
  from public.chat_groups
  where upper(join_code) = upper(trim(invite_code))
  limit 1;

  if not found then
    return;
  end if;

  insert into public.chat_members (group_id, user_id, role)
  values (found_group.id, auth.uid(), 'member')
  on conflict (group_id, user_id) do nothing;

  return query select found_group.id, found_group.name;
end;
$$;

grant execute on function public.join_chat_group_by_code(text) to authenticated;

drop policy if exists "Members can read messages in their groups" on public.chat_messages;
create policy "Members can read messages in their groups"
on public.chat_messages for select
to authenticated
using (
  exists (
    select 1 from public.chat_members
    where public.chat_members.group_id = chat_messages.group_id
    and public.chat_members.user_id = auth.uid()
  )
);

drop policy if exists "Members can send messages to their groups" on public.chat_messages;
create policy "Members can send messages to their groups"
on public.chat_messages for insert
to authenticated
with check (
  auth.uid() = author_id
  and exists (
    select 1 from public.chat_members
    where public.chat_members.group_id = chat_messages.group_id
    and public.chat_members.user_id = auth.uid()
  )
);
