-- ==========================================================================
-- 001_initial_schema.sql
-- ==========================================================================
-- ExoSynk v0.2 schema: profiles + labs + RLS
-- Paste into Supabase Dashboard → SQL Editor → Run.
-- Idempotent: safe to run again.

-- ============================================================================
-- PROFILES
-- ============================================================================
create table if not exists public.profiles (
  id            uuid primary key references auth.users(id) on delete cascade,
  username      text unique,
  display_name  text,
  avatar_url    text,
  bio           text,
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now()
);

create index if not exists profiles_username_idx on public.profiles(username);

-- Auto-create a profile row when a new user signs up.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, display_name)
  values (new.id, split_part(new.email, '@', 1))
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- ============================================================================
-- LABS
-- ============================================================================
create table if not exists public.labs (
  id          uuid primary key default gen_random_uuid(),
  slug        text unique not null,
  owner_id    uuid not null references auth.users(id) on delete cascade,
  name        text not null default 'Untitled lab',
  description text default '',
  circuit     jsonb not null default '{"parts":[],"wires":[]}'::jsonb,
  visibility  text not null default 'private' check (visibility in ('private','public')),
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now()
);

create index if not exists labs_owner_id_idx on public.labs(owner_id);
create index if not exists labs_slug_idx     on public.labs(slug);
create index if not exists labs_updated_idx  on public.labs(updated_at desc);

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

drop trigger if exists labs_updated_at on public.labs;
create trigger labs_updated_at
  before update on public.labs
  for each row execute procedure public.set_updated_at();

drop trigger if exists profiles_updated_at on public.profiles;
create trigger profiles_updated_at
  before update on public.profiles
  for each row execute procedure public.set_updated_at();

-- ============================================================================
-- RLS
-- ============================================================================
alter table public.profiles enable row level security;
alter table public.labs     enable row level security;

-- Profiles: anyone can read (for public attribution), owner can update own.
drop policy if exists "profiles: read all"             on public.profiles;
drop policy if exists "profiles: owner can update"     on public.profiles;
drop policy if exists "profiles: owner can insert own" on public.profiles;

create policy "profiles: read all"
  on public.profiles for select
  using (true);

create policy "profiles: owner can update"
  on public.profiles for update
  using (auth.uid() = id)
  with check (auth.uid() = id);

create policy "profiles: owner can insert own"
  on public.profiles for insert
  with check (auth.uid() = id);

-- Labs: owner full control; anyone can read public labs.
drop policy if exists "labs: owner full"     on public.labs;
drop policy if exists "labs: read public"    on public.labs;

create policy "labs: owner full"
  on public.labs for all
  using (auth.uid() = owner_id)
  with check (auth.uid() = owner_id);

create policy "labs: read public"
  on public.labs for select
  using (visibility = 'public');

-- ============================================================================
-- Backfill profiles for users that already signed up before this migration.
-- ============================================================================
insert into public.profiles (id, display_name)
select u.id, split_part(u.email, '@', 1)
from auth.users u
left join public.profiles p on p.id = u.id
where p.id is null
on conflict (id) do nothing;


-- ==========================================================================
-- 002_add_code_to_labs.sql
-- ==========================================================================
-- v0.3: each lab now carries a code sketch alongside its circuit.
-- Paste into Supabase Dashboard → SQL Editor → Run.
-- Idempotent.

alter table public.labs
  add column if not exists code text not null default '';


-- ==========================================================================
-- 003_community.sql
-- ==========================================================================
-- ExoSynk v0.5: community features.
-- Paste into Supabase Dashboard → SQL Editor → Run. Idempotent.

-- Lab metrics + lineage.
alter table public.labs
  add column if not exists views       int  not null default 0,
  add column if not exists fork_count  int  not null default 0,
  add column if not exists forked_from uuid references public.labs(id) on delete set null;

-- Speeds up the /explore listing (public labs by most-recent update).
create index if not exists labs_public_recent_idx
  on public.labs (updated_at desc)
  where visibility = 'public';

-- Atomic view-count increment. Safe to call anonymously (security definer),
-- but only touches public labs thanks to the where clause.
create or replace function public.increment_lab_views(p_slug text)
returns void
language sql
security definer
set search_path = public
as $$
  update public.labs
     set views = views + 1
   where slug = p_slug
     and visibility = 'public';
$$;

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

-- Atomic fork-count bump (called when someone forks a public lab).
create or replace function public.increment_lab_fork_count(p_id uuid)
returns void
language sql
security definer
set search_path = public
as $$
  update public.labs
     set fork_count = fork_count + 1
   where id = p_id
     and visibility = 'public';
$$;

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


-- ==========================================================================
-- 004_readme_and_follows.sql
-- ==========================================================================
-- ExoSynk v0.5.1: lab README + follow graph + comments.
-- Paste into Supabase Dashboard → SQL Editor → Run. Idempotent.

-- =============================================================================
-- README on labs
-- =============================================================================
alter table public.labs
  add column if not exists readme text not null default '';

-- =============================================================================
-- Follow graph
-- =============================================================================
create table if not exists public.follows (
  follower_id uuid not null references auth.users(id) on delete cascade,
  followed_id uuid not null references auth.users(id) on delete cascade,
  created_at  timestamptz not null default now(),
  primary key (follower_id, followed_id),
  check (follower_id <> followed_id)
);

create index if not exists follows_followed_idx on public.follows(followed_id);
create index if not exists follows_follower_idx on public.follows(follower_id);

alter table public.follows enable row level security;

-- Anyone can read follow edges (so counts + follower lists work for logged-out users).
drop policy if exists "follows: read all" on public.follows;
create policy "follows: read all"
  on public.follows for select using (true);

-- You can only follow / unfollow as yourself.
drop policy if exists "follows: insert self" on public.follows;
create policy "follows: insert self"
  on public.follows for insert  
  with check (auth.uid() = follower_id);

drop policy if exists "follows: delete self" on public.follows;
create policy "follows: delete self"
  on public.follows for delete
  using (auth.uid() = follower_id);

-- =============================================================================
-- Comments on public labs
-- =============================================================================
create table if not exists public.comments (
  id         uuid primary key default gen_random_uuid(),
  lab_id     uuid not null references public.labs(id) on delete cascade,
  author_id  uuid not null references auth.users(id) on delete cascade,
  body       text not null check (length(trim(body)) > 0 and length(body) <= 2000),
  created_at timestamptz not null default now()
);

create index if not exists comments_lab_idx on public.comments(lab_id, created_at desc);

alter table public.comments enable row level security;

-- Anyone can read comments on a public lab (or on a lab they own).
drop policy if exists "comments: read public or own" on public.comments;
create policy "comments: read public or own"
  on public.comments for select
  using (
    exists (select 1 from public.labs l where l.id = lab_id and l.visibility = 'public')
    or exists (select 1 from public.labs l where l.id = lab_id and l.owner_id = auth.uid())
  );

-- Anyone signed in can post a comment on a public lab, as themselves.
drop policy if exists "comments: auth can post on public" on public.comments;
create policy "comments: auth can post on public"
  on public.comments for insert
  with check (
    auth.uid() = author_id
    and exists (select 1 from public.labs l where l.id = lab_id and l.visibility = 'public')
  );

-- Authors can delete their own comments; lab owners can delete any comment on their lab.
drop policy if exists "comments: author delete own" on public.comments;
create policy "comments: author delete own"
  on public.comments for delete
  using (
    auth.uid() = author_id
    or exists (select 1 from public.labs l where l.id = lab_id and l.owner_id = auth.uid())
  );


-- ==========================================================================
-- 005_votes.sql
-- ==========================================================================
-- ExoSynk v0.5.2: upvotes for labs + up/down votes on comments.
-- Paste into Supabase Dashboard → SQL Editor → Run. Idempotent.

-- =============================================================================
-- Lab upvotes  (star-style: one per user, no downvote)
-- =============================================================================
create table if not exists public.lab_votes (
  user_id    uuid not null references auth.users(id) on delete cascade,
  lab_id     uuid not null references public.labs(id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (user_id, lab_id)
);

create index if not exists lab_votes_lab_idx on public.lab_votes(lab_id);

alter table public.lab_votes enable row level security;

drop policy if exists "lab_votes: read all" on public.lab_votes;
create policy "lab_votes: read all"
  on public.lab_votes for select using (true);

drop policy if exists "lab_votes: insert self on public" on public.lab_votes;
create policy "lab_votes: insert self on public"
  on public.lab_votes for insert
  with check (
    auth.uid() = user_id
    and exists (select 1 from public.labs l where l.id = lab_id and l.visibility = 'public')
  );

drop policy if exists "lab_votes: delete self" on public.lab_votes;
create policy "lab_votes: delete self"
  on public.lab_votes for delete
  using (auth.uid() = user_id);

-- Denormalized count + trigger for O(1) reads on /explore, cards, hero.
alter table public.labs
  add column if not exists upvote_count int not null default 0;

create or replace function public.sync_lab_upvote_count()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if tg_op = 'INSERT' then
    update public.labs set upvote_count = upvote_count + 1 where id = new.lab_id;
  elsif tg_op = 'DELETE' then
    update public.labs set upvote_count = greatest(0, upvote_count - 1) where id = old.lab_id;
  end if;
  return null;
end;
$$;

drop trigger if exists lab_votes_count on public.lab_votes;
create trigger lab_votes_count
  after insert or delete on public.lab_votes
  for each row execute procedure public.sync_lab_upvote_count();

-- Backfill in case any votes existed before the column / trigger were added.
update public.labs l
   set upvote_count = coalesce((select count(*) from public.lab_votes v where v.lab_id = l.id), 0);

-- =============================================================================
-- Comment votes  (+1 up, -1 down; at most one per user per comment)
-- =============================================================================
create table if not exists public.comment_votes (
  user_id    uuid not null references auth.users(id) on delete cascade,
  comment_id uuid not null references public.comments(id) on delete cascade,
  value      smallint not null check (value in (-1, 1)),
  created_at timestamptz not null default now(),
  primary key (user_id, comment_id)
);

create index if not exists comment_votes_comment_idx on public.comment_votes(comment_id);

alter table public.comment_votes enable row level security;

drop policy if exists "comment_votes: read all" on public.comment_votes;
create policy "comment_votes: read all"
  on public.comment_votes for select using (true);

drop policy if exists "comment_votes: insert self" on public.comment_votes;
create policy "comment_votes: insert self"
  on public.comment_votes for insert
  with check (auth.uid() = user_id);

drop policy if exists "comment_votes: update self" on public.comment_votes;
create policy "comment_votes: update self"
  on public.comment_votes for update
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

drop policy if exists "comment_votes: delete self" on public.comment_votes;
create policy "comment_votes: delete self"
  on public.comment_votes for delete
  using (auth.uid() = user_id);


-- ==========================================================================
-- 006_friendly_urls.sql
-- ==========================================================================
-- ExoSynk v0.5.3: GitHub-style URLs  /<handle>/<project>
-- Paste into Supabase Dashboard → SQL Editor → Run. Idempotent.

-- =============================================================================
-- labs.project_slug  — unique *per owner*, derived from name
-- =============================================================================
alter table public.labs
  add column if not exists project_slug text;

-- Backfill existing rows from their name. ^[^a-z0-9]+$ -> hyphens, lowercased,
-- trimmed to 64 chars. Empty result falls back to 'untitled'.
update public.labs
   set project_slug = case
     when coalesce(trim(name), '') = '' then 'untitled'
     else
       nullif(
         trim(
           both '-' from
           regexp_replace(
             lower(regexp_replace(name, '[^a-zA-Z0-9]+', '-', 'g')),
             '-+', '-', 'g'
           )
         ),
         ''
       )
   end
 where project_slug is null;

update public.labs set project_slug = 'untitled' where project_slug is null;

-- Truncate anything longer than 64 chars.
update public.labs set project_slug = left(project_slug, 64)
 where length(project_slug) > 64;

-- Resolve any (owner_id, project_slug) collisions by appending -<random>.
-- This is a one-shot fixup — normal collisions during inserts/renames are
-- handled app-side by retrying with -2 / -3 suffixes.
do $$
declare
  dup record;
begin
  for dup in
    select id, owner_id, project_slug
      from (
        select id, owner_id, project_slug,
               row_number() over (partition by owner_id, project_slug order by created_at) as rn
          from public.labs
      ) sub
      where rn > 1
  loop
    update public.labs
       set project_slug = left(dup.project_slug, 56) || '-' || substr(md5(random()::text), 1, 6)
     where id = dup.id;
  end loop;
end;
$$;

-- Now lock it in.
alter table public.labs
  alter column project_slug set not null;

alter table public.labs
  drop constraint if exists labs_owner_project_slug_unique;
alter table public.labs
  add constraint labs_owner_project_slug_unique unique (owner_id, project_slug);

create index if not exists labs_owner_project_slug_idx
  on public.labs (owner_id, project_slug);

-- =============================================================================
-- profiles.username — backfill missing, keep unique
-- =============================================================================
-- For every existing profile without a username, synthesize one from the
-- user's email prefix, deduped with numeric suffixes (user, user2, user3, …).
do $$
declare
  p_row record;
  base text;
  candidate text;
  n int;
begin
  for p_row in
    select p.id, au.email
      from public.profiles p
      join auth.users au on au.id = p.id
     where p.username is null
  loop
    base := lower(regexp_replace(split_part(p_row.email, '@', 1), '[^a-z0-9_]', '', 'g'));
    if base = '' or length(base) < 2 then base := 'user'; end if;
    base := left(base, 28);
    candidate := base;
    n := 1;
    while exists (select 1 from public.profiles where username = candidate) loop
      n := n + 1;
      candidate := left(base, 28) || n::text;
    end loop;
    update public.profiles set username = candidate where id = p_row.id;
  end loop;
end;
$$;

-- Make sure the auto-create trigger from migration 001 also picks a username
-- going forward, not just display_name.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  base text;
  candidate text;
  n int;
begin
  base := lower(regexp_replace(split_part(new.email, '@', 1), '[^a-z0-9_]', '', 'g'));
  if base = '' or length(base) < 2 then base := 'user'; end if;
  base := left(base, 28);
  candidate := base;
  n := 1;
  while exists (select 1 from public.profiles where username = candidate) loop
    n := n + 1;
    candidate := left(base, 28) || n::text;
  end loop;

  insert into public.profiles (id, display_name, username)
  values (new.id, split_part(new.email, '@', 1), candidate)
  on conflict (id) do nothing;
  return new;
end;
$$;


-- ==========================================================================
-- 007_messages.sql
-- ==========================================================================
-- ExoSynk v0.6.0: direct messages + groups.
-- DMs default to "followed-only" gate. First message from a non-followed sender
-- lands in the recipient's Requests bucket. Recipient can Accept, Block, or
-- simply ignore. Block is a global user-level edge enforced at INSERT time.
--
-- Paste into Supabase Dashboard → SQL Editor → Run. Idempotent.

-- =============================================================================
-- Per-user messaging preference
-- =============================================================================
alter table public.profiles
  add column if not exists allow_messages_from text not null default 'followed'
  check (allow_messages_from in ('everyone', 'followed', 'nobody'));

-- =============================================================================
-- Blocks — a global edge: blocker refuses messages from blocked.
-- =============================================================================
create table if not exists public.user_blocks (
  blocker_id uuid not null references auth.users(id) on delete cascade,
  blocked_id uuid not null references auth.users(id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (blocker_id, blocked_id),
  check (blocker_id <> blocked_id)
);

create index if not exists user_blocks_blocked_idx on public.user_blocks(blocked_id);

alter table public.user_blocks enable row level security;

drop policy if exists "user_blocks: blocker manages own" on public.user_blocks;
create policy "user_blocks: blocker manages own"
  on public.user_blocks for all
  using (blocker_id = auth.uid())
  with check (blocker_id = auth.uid());

-- =============================================================================
-- Conversations
-- =============================================================================
create table if not exists public.conversations (
  id         uuid primary key default gen_random_uuid(),
  kind       text not null check (kind in ('dm', 'group')),
  name       text,  -- group name; null for DMs
  created_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists conversations_updated_idx on public.conversations(updated_at desc);

alter table public.conversations enable row level security;

create table if not exists public.conversation_members (
  conversation_id uuid not null references public.conversations(id) on delete cascade,
  user_id         uuid not null references auth.users(id) on delete cascade,
  role            text not null default 'member' check (role in ('admin', 'member')),
  accepted        boolean not null default true,   -- false = DM request awaiting Accept
  last_read_at    timestamptz,
  joined_at       timestamptz not null default now(),
  primary key (conversation_id, user_id)
);

create index if not exists conv_members_user_idx on public.conversation_members(user_id);

alter table public.conversation_members enable row level security;

-- =============================================================================
-- Messages
-- =============================================================================
create table if not exists public.messages (
  id              uuid primary key default gen_random_uuid(),
  conversation_id uuid not null references public.conversations(id) on delete cascade,
  author_id       uuid references auth.users(id) on delete set null,
  body            text not null check (length(trim(body)) > 0 and length(body) <= 4000),
  created_at      timestamptz not null default now()
);

create index if not exists messages_conv_idx on public.messages(conversation_id, created_at desc);

alter table public.messages enable row level security;

-- =============================================================================
-- Helper: is this user a member of this conversation?
-- Used by RLS policies to avoid recursive self-joins on conversation_members.
-- =============================================================================
create or replace function public.is_conv_member(p_conv uuid, p_uid uuid)
returns boolean
language sql
security definer
stable
as $func$
  select exists (
    select 1 from public.conversation_members
    where conversation_id = p_conv and user_id = p_uid
  );
$func$;

-- =============================================================================
-- RLS policies
-- =============================================================================

-- conversations: visible if viewer is a member.
drop policy if exists "conv: read own" on public.conversations;
create policy "conv: read own"
  on public.conversations for select
  using (public.is_conv_member(id, auth.uid()));

-- Group admins can update their conversation (rename). DMs are immutable.
drop policy if exists "conv: admin updates group" on public.conversations;
create policy "conv: admin updates group"
  on public.conversations for update
  using (
    kind = 'group' and exists (
      select 1 from public.conversation_members cm
      where cm.conversation_id = id and cm.user_id = auth.uid() and cm.role = 'admin'
    )
  );

-- conversation_members: viewer can read members of convs they belong to.
drop policy if exists "conv_members: read own convs" on public.conversation_members;
create policy "conv_members: read own convs"
  on public.conversation_members for select
  using (public.is_conv_member(conversation_id, auth.uid()));

-- Self-update (accept / mark read / leave).
drop policy if exists "conv_members: update own" on public.conversation_members;
create policy "conv_members: update own"
  on public.conversation_members for update
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

drop policy if exists "conv_members: delete own" on public.conversation_members;
create policy "conv_members: delete own"
  on public.conversation_members for delete
  using (user_id = auth.uid());

-- messages: readable to any member of the conversation.
drop policy if exists "messages: read own convs" on public.messages;
create policy "messages: read own convs"
  on public.messages for select
  using (public.is_conv_member(conversation_id, auth.uid()));

-- messages: author must be self, must be a member, must not be blocked by any
-- other member of the conversation.
drop policy if exists "messages: author inserts" on public.messages;
create policy "messages: author inserts"
  on public.messages for insert
  with check (
    author_id = auth.uid()
    and public.is_conv_member(conversation_id, auth.uid())
    and not exists (
      select 1
      from public.conversation_members cm
      join public.user_blocks ub on ub.blocker_id = cm.user_id
      where cm.conversation_id = messages.conversation_id
        and cm.user_id <> auth.uid()
        and ub.blocked_id = auth.uid()
    )
  );

-- Authors can delete their own messages.
drop policy if exists "messages: author deletes" on public.messages;
create policy "messages: author deletes"
  on public.messages for delete
  using (author_id = auth.uid());

-- =============================================================================
-- Trigger: bump conversation.updated_at on every new message so inbox sorts
-- correctly by "latest activity".
-- =============================================================================
create or replace function public.bump_conversation_updated_at()
returns trigger
language plpgsql
as $func$
begin
  update public.conversations
    set updated_at = new.created_at
    where id = new.conversation_id;
  return new;
end;
$func$;

drop trigger if exists messages_bump_conv on public.messages;
create trigger messages_bump_conv
  after insert on public.messages
  for each row execute function public.bump_conversation_updated_at();

-- =============================================================================
-- RPC: start_dm — open (or reuse) a DM with a target user.
-- Returns the conversation id. Applies the recipient's privacy gate:
--   - 'everyone'  → auto-accepted on both sides.
--   - 'followed'  → auto-accepted only if target already follows caller;
--                   otherwise recipient side is accepted=false (request).
--   - 'nobody'    → rejected unless target follows caller.
-- Blocks short-circuit: if target has blocked caller, call raises.
-- =============================================================================
-- Implementation note: we use named dollar-quoting ($func$) and assignment
-- expressions (v := (select ...)) instead of the bare `select ... into var`
-- form. Some Supabase Postgres configs misinterpret the latter as a SQL-level
-- CREATE-TABLE `SELECT INTO`, which throws 42P01 "relation <var> does not exist".
create or replace function public.start_dm(p_target uuid)
returns uuid
language plpgsql
security definer
as $func$
declare
  v_caller uuid;
  v_conv   uuid;
  v_setting text;
  v_target_follows_caller boolean;
  v_target_accepted boolean;
begin
  v_caller := auth.uid();

  if v_caller is null then
    raise exception 'not signed in';
  end if;
  if v_caller = p_target then
    raise exception 'cannot DM yourself';
  end if;

  if exists (
    select 1 from public.user_blocks
    where blocker_id = p_target and blocked_id = v_caller
  ) then
    raise exception 'target has blocked you';
  end if;

  v_setting := coalesce(
    (select p.allow_messages_from from public.profiles p where p.id = p_target),
    'followed'
  );

  v_target_follows_caller := exists (
    select 1 from public.follows
    where follower_id = p_target and followed_id = v_caller
  );

  if v_setting = 'nobody' and not v_target_follows_caller then
    raise exception 'recipient does not accept messages';
  end if;

  v_target_accepted := (v_setting = 'everyone') or v_target_follows_caller;

  v_conv := (
    select c.id
    from public.conversations c
    where c.kind = 'dm'
      and exists (
        select 1 from public.conversation_members m
        where m.conversation_id = c.id and m.user_id = v_caller
      )
      and exists (
        select 1 from public.conversation_members m
        where m.conversation_id = c.id and m.user_id = p_target
      )
    limit 1
  );

  if v_conv is not null then
    return v_conv;
  end if;

  insert into public.conversations (kind, created_by)
    values ('dm', v_caller)
    returning id into v_conv;

  insert into public.conversation_members (conversation_id, user_id, accepted)
  values
    (v_conv, v_caller, true),
    (v_conv, p_target, v_target_accepted);

  return v_conv;
end;
$func$;

-- =============================================================================
-- RPC: create_group — start a group conversation with an initial member list.
-- Caller becomes admin. Member list must include caller.
-- =============================================================================
create or replace function public.create_group(p_name text, p_members uuid[])
returns uuid
language plpgsql
security definer
as $func$
declare
  v_caller uuid;
  v_conv   uuid;
  v_uid    uuid;
begin
  v_caller := auth.uid();
  if v_caller is null then raise exception 'not signed in'; end if;
  if coalesce(length(trim(p_name)), 0) = 0 then raise exception 'group name required'; end if;
  if array_length(p_members, 1) is null then raise exception 'need at least one member'; end if;

  insert into public.conversations (kind, name, created_by)
    values ('group', trim(p_name), v_caller)
    returning id into v_conv;

  insert into public.conversation_members (conversation_id, user_id, role, accepted)
    values (v_conv, v_caller, 'admin', true);

  foreach v_uid in array p_members loop
    if v_uid <> v_caller then
      insert into public.conversation_members (conversation_id, user_id, role, accepted)
        values (v_conv, v_uid, 'member', true)
        on conflict (conversation_id, user_id) do nothing;
    end if;
  end loop;

  return v_conv;
end;
$func$;

-- =============================================================================
-- RPC: unread count per conversation member — helpful for inbox badges.
-- =============================================================================
create or replace function public.conv_unread_count(p_conv uuid)
returns integer
language sql
stable
as $func$
  select count(*)::int
  from public.messages m
  join public.conversation_members cm
    on cm.conversation_id = m.conversation_id and cm.user_id = auth.uid()
  where m.conversation_id = p_conv
    and m.author_id <> auth.uid()
    and (cm.last_read_at is null or m.created_at > cm.last_read_at);
$func$;


-- ==========================================================================
-- 008_verified.sql
-- ==========================================================================
-- ExoSynk v0.6.0a: verified badge on profiles.
-- Founder-only flag for now. Flipped manually below; no self-serve flow yet.
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.profiles
  add column if not exists verified boolean not null default false;

-- Flag the founder.
update public.profiles
  set verified = true
  where lower(username) = 'yashpatil';


-- ==========================================================================
-- 009_profile_readme.sql
-- ==========================================================================
-- ExoSynk v0.6.0e: GitHub-style profile README.
-- Each user gets a markdown document that renders on their public profile.
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.profiles
  add column if not exists profile_readme text not null default ''
  check (length(profile_readme) <= 20000);


-- ==========================================================================
-- 010_notifications.sql
-- ==========================================================================
-- ExoSynk v0.6.1a: notifications.
-- Triggers fire on follow / comment / lab-upvote / fork. Messages have their
-- own unread badge on the inbox so we skip per-message notifications to avoid
-- double-counting the same event.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

create table if not exists public.notifications (
  id                      uuid primary key default gen_random_uuid(),
  user_id                 uuid not null references auth.users(id) on delete cascade,   -- recipient
  actor_id                uuid references auth.users(id) on delete set null,            -- who triggered it
  kind                    text not null check (kind in ('follow', 'comment', 'upvote_lab', 'fork')),
  target_lab_id           uuid references public.labs(id) on delete cascade,
  target_comment_id       uuid references public.comments(id) on delete cascade,
  preview                 text,
  created_at              timestamptz not null default now(),
  read_at                 timestamptz
);

create index if not exists notif_user_time_idx   on public.notifications(user_id, created_at desc);
create index if not exists notif_user_unread_idx on public.notifications(user_id) where read_at is null;

alter table public.notifications enable row level security;

drop policy if exists "notif: self read" on public.notifications;
create policy "notif: self read"
  on public.notifications for select using (user_id = auth.uid());

drop policy if exists "notif: self update" on public.notifications;
create policy "notif: self update"
  on public.notifications for update
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

drop policy if exists "notif: self delete" on public.notifications;
create policy "notif: self delete"
  on public.notifications for delete using (user_id = auth.uid());

-- ---- triggers --------------------------------------------------------------

create or replace function public.notify_follow()
returns trigger language plpgsql security definer as $func$
begin
  insert into public.notifications (user_id, actor_id, kind)
    values (new.followed_id, new.follower_id, 'follow');
  return new;
end;
$func$;

drop trigger if exists follows_notify on public.follows;
create trigger follows_notify after insert on public.follows
  for each row execute function public.notify_follow();

create or replace function public.notify_comment()
returns trigger language plpgsql security definer as $func$
declare v_owner uuid;
begin
  v_owner := (select owner_id from public.labs where id = new.lab_id);
  if v_owner is null or v_owner = new.author_id then return new; end if;
  insert into public.notifications (user_id, actor_id, kind, target_lab_id, target_comment_id, preview)
    values (v_owner, new.author_id, 'comment', new.lab_id, new.id, left(new.body, 140));
  return new;
end;
$func$;

drop trigger if exists comments_notify on public.comments;
create trigger comments_notify after insert on public.comments
  for each row execute function public.notify_comment();

create or replace function public.notify_lab_upvote()
returns trigger language plpgsql security definer as $func$
declare v_owner uuid;
begin
  v_owner := (select owner_id from public.labs where id = new.lab_id);
  if v_owner is null or v_owner = new.user_id then return new; end if;
  insert into public.notifications (user_id, actor_id, kind, target_lab_id)
    values (v_owner, new.user_id, 'upvote_lab', new.lab_id);
  return new;
end;
$func$;

drop trigger if exists lab_votes_notify on public.lab_votes;
create trigger lab_votes_notify after insert on public.lab_votes
  for each row execute function public.notify_lab_upvote();

create or replace function public.notify_fork()
returns trigger language plpgsql security definer as $func$
declare v_source_owner uuid;
begin
  if new.forked_from is null then return new; end if;
  v_source_owner := (select owner_id from public.labs where id = new.forked_from);
  if v_source_owner is null or v_source_owner = new.owner_id then return new; end if;
  insert into public.notifications (user_id, actor_id, kind, target_lab_id)
    values (v_source_owner, new.owner_id, 'fork', new.id);
  return new;
end;
$func$;

drop trigger if exists labs_notify_fork on public.labs;
create trigger labs_notify_fork after insert on public.labs
  for each row execute function public.notify_fork();


-- ==========================================================================
-- 011_avatars.sql
-- ==========================================================================
-- ExoSynk v0.6.1e: avatar uploads (Supabase Storage).
-- Creates the 'avatars' bucket (public-read) and the RLS policies for
-- self-write. Users upload to  <uid>/<timestamp>.<ext>  so their filenames
-- never collide and old versions get garbage-collected by app logic.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

insert into storage.buckets (id, name, public)
  values ('avatars', 'avatars', true)
  on conflict (id) do nothing;

-- Public read on everything in this bucket.
drop policy if exists "avatars: public read" on storage.objects;
create policy "avatars: public read"
  on storage.objects for select
  using (bucket_id = 'avatars');

-- Users can upload inside a folder named after their UID.
drop policy if exists "avatars: self upload" on storage.objects;
create policy "avatars: self upload"
  on storage.objects for insert
  with check (
    bucket_id = 'avatars'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- Users can update or replace their own objects.
drop policy if exists "avatars: self update" on storage.objects;
create policy "avatars: self update"
  on storage.objects for update
  using (
    bucket_id = 'avatars'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- Users can delete their own objects.
drop policy if exists "avatars: self delete" on storage.objects;
create policy "avatars: self delete"
  on storage.objects for delete
  using (
    bucket_id = 'avatars'
    and auth.uid()::text = (storage.foldername(name))[1]
  );


-- ==========================================================================
-- 012_tutorials.sql
-- ==========================================================================
-- ExoSynk v0.7.0: ExoTutor — text + image tutorials and research write-ups.
-- Creators write long-form markdown; readers follow along to build their own.
-- Paste into Supabase SQL Editor → Run. Idempotent.
--
-- URL shape: /learn/<handle>/<slug>
-- (kept under /learn namespace to avoid collision with the /<handle>/<project> lab URLs)

create table if not exists public.tutorials (
  id            uuid primary key default gen_random_uuid(),
  author_id     uuid not null references auth.users(id) on delete cascade,
  slug          text not null,
  title         text not null check (length(trim(title)) between 1 and 200),
  description   text check (length(description) <= 500),
  content       text not null default '' check (length(content) <= 200000),
  cover_url     text,
  visibility    text not null default 'draft' check (visibility in ('public','private','draft')),
  linked_lab_id uuid references public.labs(id) on delete set null,
  views         int not null default 0,
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now(),
  published_at  timestamptz,
  unique (author_id, slug)
);

create index if not exists tutorials_author_time_idx
  on public.tutorials(author_id, updated_at desc);
create index if not exists tutorials_public_recent_idx
  on public.tutorials(updated_at desc) where visibility = 'public';

alter table public.tutorials enable row level security;

-- Read: public posts visible to everyone; draft/private only to author.
drop policy if exists "tutorials: read public or own" on public.tutorials;
create policy "tutorials: read public or own"
  on public.tutorials for select
  using (visibility = 'public' or author_id = auth.uid());

drop policy if exists "tutorials: author insert" on public.tutorials;
create policy "tutorials: author insert"
  on public.tutorials for insert
  with check (author_id = auth.uid());

drop policy if exists "tutorials: author update" on public.tutorials;
create policy "tutorials: author update"
  on public.tutorials for update
  using (author_id = auth.uid())
  with check (author_id = auth.uid());

drop policy if exists "tutorials: author delete" on public.tutorials;
create policy "tutorials: author delete"
  on public.tutorials for delete
  using (author_id = auth.uid());

-- Atomic view counter. Silent-fails if policies block (best effort).
create or replace function public.increment_tutorial_views(p_id uuid)
returns void
language sql
security definer
as $func$
  update public.tutorials set views = views + 1 where id = p_id;
$func$;

-- Keep updated_at in sync on every update.
create or replace function public.tutorials_touch_updated_at()
returns trigger language plpgsql as $func$
begin
  new.updated_at := now();
  return new;
end;
$func$;

drop trigger if exists tutorials_touch on public.tutorials;
create trigger tutorials_touch before update on public.tutorials
  for each row execute function public.tutorials_touch_updated_at();

-- =============================================================================
-- Cover image storage bucket.
-- =============================================================================
insert into storage.buckets (id, name, public)
  values ('tutorial-covers', 'tutorial-covers', true)
  on conflict (id) do nothing;

drop policy if exists "tutorial-covers: public read" on storage.objects;
create policy "tutorial-covers: public read"
  on storage.objects for select
  using (bucket_id = 'tutorial-covers');

drop policy if exists "tutorial-covers: self upload" on storage.objects;
create policy "tutorial-covers: self upload"
  on storage.objects for insert
  with check (
    bucket_id = 'tutorial-covers'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists "tutorial-covers: self update" on storage.objects;
create policy "tutorial-covers: self update"
  on storage.objects for update
  using (
    bucket_id = 'tutorial-covers'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists "tutorial-covers: self delete" on storage.objects;
create policy "tutorial-covers: self delete"
  on storage.objects for delete
  using (
    bucket_id = 'tutorial-covers'
    and auth.uid()::text = (storage.foldername(name))[1]
  );


-- ==========================================================================
-- 013_tutorial_social.sql
-- ==========================================================================
-- ExoSynk v0.7.1b: stars + comments on tutorials, Instagram-style author
-- heart on comments. Mirrors the v0.5.2 labs pattern but scoped to
-- tutorials so they can evolve independently.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

-- =============================================================================
-- Stars (upvotes) on tutorials
-- =============================================================================
alter table public.tutorials
  add column if not exists upvote_count int not null default 0;

create table if not exists public.tutorial_votes (
  tutorial_id uuid not null references public.tutorials(id) on delete cascade,
  user_id     uuid not null references auth.users(id) on delete cascade,
  created_at  timestamptz not null default now(),
  primary key (tutorial_id, user_id)
);

create index if not exists tutorial_votes_user_idx on public.tutorial_votes(user_id);

alter table public.tutorial_votes enable row level security;

drop policy if exists "tv: read all" on public.tutorial_votes;
create policy "tv: read all"
  on public.tutorial_votes for select using (true);

drop policy if exists "tv: self insert on public" on public.tutorial_votes;
create policy "tv: self insert on public"
  on public.tutorial_votes for insert
  with check (
    user_id = auth.uid()
    and exists (
      select 1 from public.tutorials t
      where t.id = tutorial_id and t.visibility = 'public'
    )
  );

drop policy if exists "tv: self delete" on public.tutorial_votes;
create policy "tv: self delete"
  on public.tutorial_votes for delete
  using (user_id = auth.uid());

create or replace function public.sync_tutorial_upvote_count()
returns trigger language plpgsql security definer as $func$
begin
  if tg_op = 'INSERT' then
    update public.tutorials set upvote_count = upvote_count + 1
      where id = new.tutorial_id;
    return new;
  elsif tg_op = 'DELETE' then
    update public.tutorials set upvote_count = greatest(upvote_count - 1, 0)
      where id = old.tutorial_id;
    return old;
  end if;
  return null;
end;
$func$;

drop trigger if exists tv_sync_insert on public.tutorial_votes;
create trigger tv_sync_insert after insert on public.tutorial_votes
  for each row execute function public.sync_tutorial_upvote_count();

drop trigger if exists tv_sync_delete on public.tutorial_votes;
create trigger tv_sync_delete after delete on public.tutorial_votes
  for each row execute function public.sync_tutorial_upvote_count();

-- =============================================================================
-- Comments on tutorials
-- =============================================================================
create table if not exists public.tutorial_comments (
  id             uuid primary key default gen_random_uuid(),
  tutorial_id    uuid not null references public.tutorials(id) on delete cascade,
  author_id      uuid not null references auth.users(id) on delete cascade,
  body           text not null check (length(trim(body)) > 0 and length(body) <= 2000),
  author_hearted boolean not null default false,
  created_at     timestamptz not null default now()
);

create index if not exists tutorial_comments_time_idx
  on public.tutorial_comments(tutorial_id, created_at desc);

alter table public.tutorial_comments enable row level security;

drop policy if exists "tc: read" on public.tutorial_comments;
create policy "tc: read"
  on public.tutorial_comments for select
  using (
    exists (
      select 1 from public.tutorials t
      where t.id = tutorial_id
        and (t.visibility = 'public' or t.author_id = auth.uid())
    )
  );

drop policy if exists "tc: self post on public" on public.tutorial_comments;
create policy "tc: self post on public"
  on public.tutorial_comments for insert
  with check (
    author_id = auth.uid()
    and exists (
      select 1 from public.tutorials t
      where t.id = tutorial_id and t.visibility = 'public'
    )
  );

-- Delete: comment author OR tutorial author (moderation).
drop policy if exists "tc: author or tutor owner delete" on public.tutorial_comments;
create policy "tc: author or tutor owner delete"
  on public.tutorial_comments for delete
  using (
    author_id = auth.uid()
    or exists (
      select 1 from public.tutorials t
      where t.id = tutorial_id and t.author_id = auth.uid()
    )
  );

-- NOTE on author_hearted: we don't expose UPDATE via RLS to end users. The
-- only legitimate writer of that column is the tutorial's author, toggling
-- the heart — we handle that via the set_tutorial_comment_heart RPC below,
-- which verifies ownership before touching the row.

-- =============================================================================
-- Votes on tutorial comments
-- =============================================================================
create table if not exists public.tutorial_comment_votes (
  comment_id uuid not null references public.tutorial_comments(id) on delete cascade,
  user_id    uuid not null references auth.users(id) on delete cascade,
  value      smallint not null check (value in (-1, 1)),
  created_at timestamptz not null default now(),
  primary key (comment_id, user_id)
);

create index if not exists tcv_user_idx on public.tutorial_comment_votes(user_id);

alter table public.tutorial_comment_votes enable row level security;

drop policy if exists "tcv: read all" on public.tutorial_comment_votes;
create policy "tcv: read all"
  on public.tutorial_comment_votes for select using (true);

drop policy if exists "tcv: self manage" on public.tutorial_comment_votes;
create policy "tcv: self manage"
  on public.tutorial_comment_votes for all
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

-- =============================================================================
-- RPC: tutorial author toggles heart on a comment.
-- Security definer so it can bypass RLS UPDATE restriction on the column;
-- verifies the caller owns the tutorial before touching the row.
-- =============================================================================
-- Implementation note: uses the assignment form (v := (select ...)) instead
-- of `select ... into v from ...`. Some Supabase Postgres configs misparse
-- the latter as a SQL-level CREATE-TABLE SELECT INTO, which throws 42P01
-- "relation <var> does not exist".
create or replace function public.set_tutorial_comment_heart(
  p_comment_id uuid,
  p_hearted boolean
)
returns void
language plpgsql
security definer
as $func$
declare
  v_caller uuid;
  v_tutorial_author uuid;
  v_tutorial_id uuid;
begin
  v_caller := auth.uid();
  if v_caller is null then raise exception 'not signed in'; end if;

  v_tutorial_id := (
    select tc.tutorial_id from public.tutorial_comments tc where tc.id = p_comment_id
  );
  if v_tutorial_id is null then raise exception 'comment not found'; end if;

  v_tutorial_author := (
    select t.author_id from public.tutorials t where t.id = v_tutorial_id
  );
  if v_tutorial_author is null or v_tutorial_author <> v_caller then
    raise exception 'only the tutorial author can heart a comment';
  end if;

  update public.tutorial_comments
    set author_hearted = p_hearted
    where id = p_comment_id;  
end;
$func$;


-- ==========================================================================
-- 014_verified_tier.sql
-- ==========================================================================
-- ExoSynk v0.8.1a: two-tier verified badge system.
-- 'founder'  → black scalloped seal  (Yash, single-holder)
-- 'official' → red scalloped seal    (the ExoSynk company account + future employees / partners)
--
-- The existing boolean `verified` column stays for back-compat with any
-- cached clients, but verified_tier is the new source of truth.
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.profiles
  add column if not exists verified_tier text
  check (verified_tier in ('founder', 'official'));

-- Backfill: every existing verified=true profile becomes a founder.
update public.profiles
  set verified_tier = 'founder'
  where verified = true and verified_tier is null;

-- Flag the ExoSynk company account (must exist with username='exosynk').
-- No-op if the row doesn't exist yet — re-run after signup.
update public.profiles
  set verified_tier = 'official',
      verified = true
  where lower(username) = 'exosynk';

-- Useful index for queries that filter by tier.
create index if not exists profiles_verified_tier_idx
  on public.profiles(verified_tier) where verified_tier is not null;


-- ==========================================================================
-- 015_exosynk_seed.sql
-- ==========================================================================
-- ExoSynk v0.8.1b: seed content for the @exosynk official account.
-- Runs AFTER the exosynk@gmail.com user has signed up and set
-- username='exosynk' via the normal profile flow.
--
-- Paste into Supabase SQL Editor → Run. Idempotent via ON CONFLICT.
-- Safe to re-run: updates the profile, only creates tutorials + labs that
-- don't already exist (keyed on exosynk's author_id + slug).

do $$
declare
  v_uid uuid;
begin
  select id into v_uid from public.profiles where lower(username) = 'exosynk';
  if v_uid is null then
    raise notice 'No profile with username=exosynk found — create the account and set the username first.';
    return;
  end if;

  -- ---------- 1. Flesh out the profile ----------
  update public.profiles
    set
      display_name = 'ExoSynk',
      bio = 'The Virtual Electronics Lab. Design circuits in your browser, wire them up, run real physics — then (later) order your build as actual hardware.',
      allow_messages_from = 'everyone',
      verified = true,
      verified_tier = 'official',
      profile_readme = $md$
# ExoSynk — The Virtual Electronics Lab

Hi. We're **ExoSynk** — a cloud-based lab where anyone can design real
electronics in their browser. No simulator downloads, no dead components,
no cables on the floor.

## What you can do here

- **Design circuits** in a real schematic editor — drag parts, route wires, snap to grid.
- **Run honest simulation** (Modified Nodal Analysis, not a toy) — LEDs glow at the right current, resistors respect Ohm, components can burn out.
- **Write Arduino code** next to your circuit — `digitalWrite(13, HIGH)` actually lights the LED.
- **Share with a link** — public labs live at `/<your-handle>/<project>`, forkable by anyone.
- **Learn from others** — read tutorials in `/learn`, star the ones that taught you something, comment to ask.

## Who we're for

Students, makers, hobbyists, professional engineers prototyping at their day job, hardware startups, educators, researchers. If you design electronics, this is for you.

## The roadmap

- ✅ 2D lab, accounts, community, messaging, tutorials — shipped.
- 🚧 Part rotation + wire-to-wire junctions — just shipped (v0.8).
- 🔜 3D lab, then **order your design as a parts kit or pre-built product** (v1.x).

Follow this account for announcements. DMs are open.

_Built by [@yashpatil](/yashpatil) · Based in India, shipping globally._
$md$,
      updated_at = now()
    where id = v_uid;

  -- ---------- 2. Seed tutorials ----------
  -- Each insert is keyed on (author_id, slug) which is unique. Re-runs no-op.
  insert into public.tutorials
    (author_id, slug, title, description, content, visibility, published_at)
  values
    (
      v_uid,
      'your-first-circuit-blink-an-led',
      'Your first circuit: blink an LED',
      'A 9V battery, a resistor, and an LED. Why the resistor matters, how to read current, and how to fix it when the LED goes dark.',
      $md$
# Your first circuit: blink an LED

Welcome to ExoSynk. This is the classic "hello world" of electronics —
and it's three components.

## What you need

- **9V battery** — the source
- **330Ω resistor** — the current limiter
- **LED** — the output

That's it. The companion lab is [@exosynk/blink](/exosynk/blink).

## Why the resistor?

An LED is not a regular bulb. It has a **forward voltage** (usually around
2V for red LEDs) — below that voltage it's off, above it the current rises
fast, and above its max current (~20 mA for standard through-hole LEDs)
it burns out.

Connecting a 9V battery straight to an LED would push something like
**1 amp** through it. It would glow for about 0.2 seconds, then go dark
forever.

The resistor "eats" the extra voltage. Ohm's law:

```
I = (V_battery − V_led) / R
I = (9V − 2V) / 330Ω
I = 21 mA
```

Right in the happy range. LED glows brightly, nothing burns.

## Try it

1. Open [@exosynk/blink](/exosynk/blink)
2. Click **Fork** in the top-right — now it's your lab
3. Change the battery to 3V. Does the LED still light? (It might not —
   3V − 2V = 1V across a 330Ω resistor is only 3 mA, which is barely
   visible.)
4. Change the resistor to 100Ω with the 9V battery. Watch the LED go
   orange, then 💥 `BURNT` — we've exceeded max current.

## What you learned

- LEDs need current-limiting resistors.
- Ohm's law picks the resistor value.
- In ExoSynk the simulation is honest — burn-outs are real.

Next up: [voltage dividers](/learn/exosynk/voltage-dividers-how-two-resistors-split-a-signal).
$md$,
      'public',
      now()
    ),
    (
      v_uid,
      'voltage-dividers-how-two-resistors-split-a-signal',
      'Voltage dividers: how two resistors split a signal',
      'Two resistors in a row, a tap in the middle, and suddenly you can shrink any voltage down to the one your circuit actually needs. The one formula to memorize.',
      $md$
# Voltage dividers

Given a 9V battery and a sensor that wants **3V**, how do you get there?

You could buy a "3V regulator". Or you could put two resistors in a row,
tap the middle, and do math.

## The setup

```
9V ──[ R1 ]──┬──[ R2 ]── GND
             │
           V_out
```

The output voltage at the tap is:

```
V_out = V_in × R2 / (R1 + R2)
```

For 9V → 3V, pick R1 = 2 × R2. So R1 = 10kΩ, R2 = 5kΩ works. Or 20kΩ +
10kΩ. The **ratio** is what matters, not the absolute values.

## Why big resistors?

In the lab above you could also use R1 = 2Ω, R2 = 1Ω. Math checks out —
3V still appears at the tap. But those resistors would draw **3 amps**
continuously, heating up and wasting power.

Rule of thumb for low-power dividers: use kilohms or higher.

## Companion lab

[@exosynk/voltage-divider-demo](/exosynk/voltage-divider-demo) has this
exact circuit. Click any wire or pin to see the voltage / current readout.
Try:

- Change R1 from 10k to 20k. V_out should drop to 1.8V. Verify with the
  multimeter.
- Swap both to 100Ω. Circuit still works, but now it burns 90 mW
  continuously — check the battery drain estimate.

## When dividers break

Voltage dividers assume almost no current flows out of the tap. The
moment you connect a "load" that draws real current, the math gets
messier (you've added a third resistor in parallel). That's why most
real designs use op-amp buffers or dedicated regulators — but dividers
are everywhere in low-power signal work.

## Next

A resistor in series with a capacitor — an RC circuit — has a more
interesting story. That's [here](/learn/exosynk/rc-circuits-capacitors-remember).
$md$,
      'public',
      now()
    ),
    (
      v_uid,
      'rc-circuits-capacitors-remember',
      'RC circuits: capacitors remember',
      'A resistor and a capacitor. Close the switch and the capacitor charges — but not instantly. The curve is exactly what the oscilloscope shows.',
      $md$
# RC circuits: capacitors remember

A capacitor stores charge. The moment you connect it to a voltage source
through a resistor, it starts to fill up — and the fill-up curve has a
specific, beautiful shape.

## The circuit

```
V ──[ R ]──┬──[ C ]── GND
           │
         V_cap
```

Close the switch. V_cap starts at 0V and climbs toward V. The time
constant is:

```
τ = R × C
```

- τ (tau) is in **seconds** when R is in ohms and C is in farads.
- After `1τ` the cap is at 63% of the target voltage.
- After `3τ` it's at 95%. We call this "basically full".
- After `5τ` it's at 99%.

## The math

```
V_cap(t) = V × (1 − e^(−t/τ))
```

The exponential. Same curve as population growth, cooling coffee,
radioactive decay (running in reverse). Once you see it in one place,
you see it everywhere.

## Try it

[@exosynk/rc-lowpass](/exosynk/rc-lowpass) has a 9V battery, 10kΩ
resistor, and a 100μF capacitor.

- τ = 10,000 × 0.0001 = **1 second**
- Open the oscilloscope (shortcut: press O or use the toolbar)
- Probe V_cap
- Press the simulate button. You'll see the exponential rise exactly
  as drawn above — ExoSynk's transient sim is real math, not a look-up.

## Why this matters

Every button on your keyboard has an RC debounce somewhere. Every audio
filter has RC-shaped cutoff. Every Arduino analog pin takes ~100 μs to
stabilize after a read because of an RC at the input.

Once you're comfortable reading an RC curve, a huge chunk of "real"
electronics becomes legible.
$md$,
      'public',
      now()
    )
  on conflict (author_id, slug) do update
    set title = excluded.title,
        description = excluded.description,
        content = excluded.content,
        visibility = excluded.visibility,
        updated_at = now();

  -- ---------- 3. Seed public labs ----------
  -- Circuits are small, valid, and simulate. Users who fork them can
  -- rearrange — positions are deliberately simple, not pixel-perfect.
  insert into public.labs
    (owner_id, slug, project_slug, name, description, visibility, circuit, code, readme)
  values
    (
      v_uid,
      substr(md5(random()::text), 1, 8),
      'blink',
      'Blink',
      'The hello-world circuit: 9V battery, 330Ω resistor, LED, ground. Fork it and try a 3V battery, or a 100Ω resistor to watch the LED burn out.',
      'public',
      '{
        "parts": [
          {"id":"bat1","kind":"battery","x":4,"y":6,"props":{"voltage":9}},
          {"id":"r1","kind":"resistor","x":11,"y":7,"props":{"resistance":330}},
          {"id":"led1","kind":"led","x":17,"y":6,"props":{"vf":2.0,"maxCurrent":0.025}},
          {"id":"gnd1","kind":"ground","x":9,"y":11,"props":{}}
        ],
        "wires": [
          {"id":"w1","from":{"partId":"bat1","pinId":"pos"},"to":{"partId":"r1","pinId":"a"}},
          {"id":"w2","from":{"partId":"r1","pinId":"b"},"to":{"partId":"led1","pinId":"anode"}},
          {"id":"w3","from":{"partId":"led1","pinId":"cathode"},"to":{"partId":"gnd1","pinId":"gnd"}},
          {"id":"w4","from":{"partId":"bat1","pinId":"neg"},"to":{"partId":"gnd1","pinId":"gnd"}}
        ],
        "junctions": []
      }'::jsonb,
      '',
      $md$# Blink

The classic three-component circuit. Tutorial: [/learn/exosynk/your-first-circuit-blink-an-led](/learn/exosynk/your-first-circuit-blink-an-led)

## Try this

1. Swap the resistor for **100Ω**. The LED will burn out.
2. Swap the battery for **3V**. The LED will barely glow.
3. Add a **switch** in series — now you've got a flashlight.
$md$
    ),
    (
      v_uid,
      substr(md5(random()::text), 1, 8),
      'voltage-divider-demo',
      'Voltage divider demo',
      'Two 10kΩ resistors in series across a 9V battery. The tap in the middle sits at 4.5V. Try different R1/R2 ratios.',
      'public',
      '{
        "parts": [
          {"id":"bat1","kind":"battery","x":4,"y":6,"props":{"voltage":9}},
          {"id":"r1","kind":"resistor","x":11,"y":7,"props":{"resistance":10000}},
          {"id":"r2","kind":"resistor","x":17,"y":7,"props":{"resistance":10000}},
          {"id":"gnd1","kind":"ground","x":9,"y":11,"props":{}}
        ],
        "wires": [
          {"id":"w1","from":{"partId":"bat1","pinId":"pos"},"to":{"partId":"r1","pinId":"a"}},
          {"id":"w2","from":{"partId":"r1","pinId":"b"},"to":{"partId":"r2","pinId":"a"}},
          {"id":"w3","from":{"partId":"r2","pinId":"b"},"to":{"partId":"gnd1","pinId":"gnd"}},
          {"id":"w4","from":{"partId":"bat1","pinId":"neg"},"to":{"partId":"gnd1","pinId":"gnd"}}
        ],
        "junctions": []
      }'::jsonb,
      '',
      $md$# Voltage divider demo

Tap sits at V_out = V × R2 / (R1 + R2). With equal resistors, V_out is
half the input — 4.5V. Tutorial: [/learn/exosynk/voltage-dividers-how-two-resistors-split-a-signal](/learn/exosynk/voltage-dividers-how-two-resistors-split-a-signal)

Hover over the junction between R1 and R2 to see the live voltage.
$md$
    ),
    (
      v_uid,
      substr(md5(random()::text), 1, 8),
      'rc-lowpass',
      'RC lowpass — capacitor charging curve',
      '9V battery, 10kΩ resistor, 100μF capacitor. Time constant τ = 1 second. Open the oscilloscope on V_cap to see the exponential rise.',
      'public',
      '{
        "parts": [
          {"id":"bat1","kind":"battery","x":4,"y":6,"props":{"voltage":9}},
          {"id":"r1","kind":"resistor","x":11,"y":7,"props":{"resistance":10000}},
          {"id":"c1","kind":"capacitor","x":17,"y":7,"props":{"capacitance":100}},
          {"id":"gnd1","kind":"ground","x":9,"y":11,"props":{}}
        ],
        "wires": [
          {"id":"w1","from":{"partId":"bat1","pinId":"pos"},"to":{"partId":"r1","pinId":"a"}},
          {"id":"w2","from":{"partId":"r1","pinId":"b"},"to":{"partId":"c1","pinId":"a"}},
          {"id":"w3","from":{"partId":"c1","pinId":"b"},"to":{"partId":"gnd1","pinId":"gnd"}},
          {"id":"w4","from":{"partId":"bat1","pinId":"neg"},"to":{"partId":"gnd1","pinId":"gnd"}}
        ],
        "junctions": []
      }'::jsonb,
      '',
      $md$# RC lowpass

Classic RC circuit. The capacitor charges through the resistor, following
`V(t) = V × (1 − e^(−t/τ))` where τ = R × C = 10kΩ × 100μF = **1 second**.

Tutorial: [/learn/exosynk/rc-circuits-capacitors-remember](/learn/exosynk/rc-circuits-capacitors-remember)

Open the oscilloscope, probe the cap's positive terminal, and hit
simulate — you'll see the exponential rise in real time.
$md$
    )
  on conflict (owner_id, project_slug) do update
    set name = excluded.name,
        description = excluded.description,
        visibility = excluded.visibility,
        circuit = excluded.circuit,
        readme = excluded.readme,
        updated_at = now();

  raise notice 'ExoSynk official account seeded: 3 tutorials + 3 public labs.';
end $$;


-- ==========================================================================
-- 016_exosynk_complex_labs.sql
-- ==========================================================================
-- ExoSynk v0.8.1d: three more complex public labs for the @exosynk account.
-- Layered on top of migration 015's starter trio (blink / voltage divider /
-- RC lowpass). These use the richer parts library — RGB LED with PWM,
-- 7-segment display driven by Arduino, and a proper 5-part analog circuit
-- that switches an LED based on ambient light with no microcontroller.
--
-- Paste into Supabase SQL Editor → Run. Idempotent via ON CONFLICT.

do $$
declare
  v_uid uuid;
begin
  select id into v_uid from public.profiles where lower(username) = 'exosynk';
  if v_uid is null then
    raise notice 'No profile with username=exosynk found. Create the account first.';
    return;
  end if;

  insert into public.labs
    (owner_id, slug, project_slug, name, description, visibility, circuit, code, readme)
  values
    -- ------------------------------------------------------------------
    -- 1. RGB color fader — Arduino PWM on three channels
    -- ------------------------------------------------------------------
    (
      v_uid,
      substr(md5(random()::text || 'rgb'), 1, 8),
      'rgb-color-fader',
      'RGB color fader (Arduino PWM)',
      'Arduino drives a common-cathode RGB LED through three 330Ω resistors. Code cycles R, G, B via analogWrite — classic PWM color mixing.',
      'public',
      '{
        "parts": [
          {"id":"ard1","kind":"arduino","x":3,"y":3,"props":{"v_5v":5}},
          {"id":"r_r","kind":"resistor","x":18,"y":5,"props":{"resistance":330}},
          {"id":"r_g","kind":"resistor","x":18,"y":7,"props":{"resistance":330}},
          {"id":"r_b","kind":"resistor","x":18,"y":9,"props":{"resistance":330}},
          {"id":"rgb1","kind":"rgbled","x":24,"y":5,"props":{"vf_r":1.8,"vf_g":2.2,"vf_b":3.0,"maxCurrent":0.02}},
          {"id":"gnd1","kind":"ground","x":27,"y":10,"props":{}}
        ],
        "wires": [
          {"id":"w1","from":{"partId":"ard1","pinId":"d11"},"to":{"partId":"r_r","pinId":"a"}},
          {"id":"w2","from":{"partId":"ard1","pinId":"d10"},"to":{"partId":"r_g","pinId":"a"}},
          {"id":"w3","from":{"partId":"ard1","pinId":"d9"},"to":{"partId":"r_b","pinId":"a"}},
          {"id":"w4","from":{"partId":"r_r","pinId":"b"},"to":{"partId":"rgb1","pinId":"r"}},
          {"id":"w5","from":{"partId":"r_g","pinId":"b"},"to":{"partId":"rgb1","pinId":"g"}},
          {"id":"w6","from":{"partId":"r_b","pinId":"b"},"to":{"partId":"rgb1","pinId":"b"}},
          {"id":"w7","from":{"partId":"rgb1","pinId":"gnd"},"to":{"partId":"gnd1","pinId":"gnd"}}
        ],
        "junctions": []
      }'::jsonb,
      $code$// RGB color fader — walk the hue wheel via PWM on pins 9, 10, 11.
// Each pin drives one color through a 330-ohm current-limiting resistor.

const int R_PIN = 11;
const int G_PIN = 10;
const int B_PIN = 9;

void setup() {
  pinMode(R_PIN, OUTPUT);
  pinMode(G_PIN, OUTPUT);
  pinMode(B_PIN, OUTPUT);
}

// A simple six-step hue cycle. Each step 256 frames, 5 ms per frame =
// ~7.7 s per full rotation.
uint8_t step = 0;
uint8_t t = 0;

void loop() {
  uint8_t r = 0, g = 0, b = 0;
  switch (step) {
    case 0: r = 255;      g = t;        b = 0;        break; // red → yellow
    case 1: r = 255 - t;  g = 255;      b = 0;        break; // yellow → green
    case 2: r = 0;        g = 255;      b = t;        break; // green → cyan
    case 3: r = 0;        g = 255 - t;  b = 255;      break; // cyan → blue
    case 4: r = t;        g = 0;        b = 255;      break; // blue → magenta
    case 5: r = 255;      g = 0;        b = 255 - t;  break; // magenta → red
  }
  analogWrite(R_PIN, r);
  analogWrite(G_PIN, g);
  analogWrite(B_PIN, b);
  delay(5);
  if (++t == 0) step = (step + 1) % 6;
}
$code$,
      $md$# RGB color fader

Three PWM pins → three 330Ω resistors → three anodes of an RGB LED →
common cathode → ground. That's it.

## What you see

Press ▶ **Simulate**. The LED walks the hue wheel — red, yellow, green,
cyan, blue, magenta, back to red. One full loop is about 7.7 seconds.

## The trick: PWM

Arduino's `analogWrite(pin, 0-255)` doesn't actually output an analog
voltage — it outputs a square wave that's on for `n/255` of the time.
The LED flickers faster than your eye can see, and you perceive the
average brightness.

- `analogWrite(pin, 255)` → 100% on, full brightness
- `analogWrite(pin, 128)` → 50% duty, half brightness
- `analogWrite(pin, 0)` → off

Mix three channels and you get 256³ ≈ 16.7 million colors, same as any
monitor.

## Try this

- Change each `255` to `64`. LED gets much dimmer but the cycle still runs.
- Change `delay(5)` to `delay(50)`. Cycle slows 10×.
- Hardcode `analogWrite(11, 200); analogWrite(10, 40); analogWrite(9, 150);`
  inside `setup()` and remove the `loop` body. You've just picked a single
  color and held it — purple-ish.

## The resistor values

With Arduino at 5 V and the R-channel Vf ≈ 1.8 V:
`I = (5 - 1.8) / 330 ≈ 9.7 mA`. Comfortably under the 20 mA max.
Each color has a different Vf so the three channels brighten at slightly
different rates — that's normal for RGB LEDs and the eye adjusts.
$md$
    ),

    -- ------------------------------------------------------------------
    -- 2. 7-segment counter — Arduino drives all seven segments
    -- ------------------------------------------------------------------
    (
      v_uid,
      substr(md5(random()::text || 'seg'), 1, 8),
      'seven-segment-counter',
      '7-segment counter (0-9 on Arduino)',
      'Seven Arduino digital pins drive a common-cathode 7-segment display. A shared 330Ω resistor limits current to ground. Code counts 0→9 in a loop.',
      'public',
      '{
        "parts": [
          {"id":"ard1","kind":"arduino","x":3,"y":3,"props":{"v_5v":5}},
          {"id":"seg1","kind":"sevenseg","x":6,"y":12,"props":{"vf":2.0,"maxCurrent":0.02}},
          {"id":"r_k","kind":"resistor","x":14,"y":18,"props":{"resistance":330}},
          {"id":"gnd1","kind":"ground","x":19,"y":19,"props":{}}
        ],
        "wires": [
          {"id":"w_a","from":{"partId":"ard1","pinId":"d2"},"to":{"partId":"seg1","pinId":"a"}},
          {"id":"w_b","from":{"partId":"ard1","pinId":"d3"},"to":{"partId":"seg1","pinId":"b"}},
          {"id":"w_c","from":{"partId":"ard1","pinId":"d4"},"to":{"partId":"seg1","pinId":"c"}},
          {"id":"w_d","from":{"partId":"ard1","pinId":"d5"},"to":{"partId":"seg1","pinId":"d"}},
          {"id":"w_e","from":{"partId":"ard1","pinId":"d6"},"to":{"partId":"seg1","pinId":"e"}},
          {"id":"w_f","from":{"partId":"ard1","pinId":"d7"},"to":{"partId":"seg1","pinId":"f"}},
          {"id":"w_g","from":{"partId":"ard1","pinId":"d8"},"to":{"partId":"seg1","pinId":"g"}},
          {"id":"w_k","from":{"partId":"seg1","pinId":"gnd"},"to":{"partId":"r_k","pinId":"a"}},
          {"id":"w_k2","from":{"partId":"r_k","pinId":"b"},"to":{"partId":"gnd1","pinId":"gnd"}}
        ],
        "junctions": []
      }'::jsonb,
      $code$// 7-segment counter — count 0..9 on a common-cathode display.
// Segments:
//      aaaa
//     f    b
//     f    b
//      gggg
//     e    c
//     e    c
//      dddd
//
// Wired: d2→a, d3→b, d4→c, d5→d, d6→e, d7→f, d8→g

const uint8_t DIGITS[10] = {
  0b0111111, // 0: a b c d e f      (no g)
  0b0000110, // 1: b c
  0b1011011, // 2: a b   d e   g
  0b1001111, // 3: a b c d     g
  0b1100110, // 4:   b c     f g
  0b1101101, // 5: a   c d   f g
  0b1111101, // 6: a   c d e f g
  0b0000111, // 7: a b c
  0b1111111, // 8: all
  0b1101111, // 9: a b c d   f g
};

void setup() {
  for (int p = 2; p <= 8; p++) pinMode(p, OUTPUT);
}

void writeDigit(uint8_t d) {
  uint8_t bits = DIGITS[d];
  for (int i = 0; i < 7; i++) {
    digitalWrite(2 + i, (bits >> i) & 1 ? HIGH : LOW);
  }
}

uint8_t n = 0;
void loop() {
  writeDigit(n);
  n = (n + 1) % 10;
  delay(600);
}
$code$,
      $md$# 7-segment counter

Seven Arduino pins, one segment per pin, common cathode tied to ground
through a 330Ω resistor. The code counts **0 → 9** at one digit every
600 ms.

## Segment mapping

A 7-segment display has seven LEDs arranged like this:

```
 aaaa
f    b
f    b
 gggg
e    c
e    c
 dddd
```

Each segment is wired to an Arduino pin (`a → d2`, `b → d3`, ...,
`g → d8`). The digit table in the code encodes which segments to light
for each number — `3` lights a, b, c, d, g but leaves e and f dark.

## One resistor or seven?

Real-world builds use **one resistor per segment** (7 total) so every
segment has the same brightness even when different counts of segments
are lit. This lab uses a **single shared resistor** on the cathode line
for schematic simplicity — you'll see a subtle brightness difference
between digits that light more segments (like 8) vs fewer (like 1).
Fork this and split out seven individual resistors if you want it right.

## Try this

- Change `delay(600)` to `100` — fast counter, barely readable.
- Flip the order: `n = (n + 9) % 10` counts **down** 9 → 0.
- Replace the digit table to draw letters: `b`, `c`, `d`, `E`, `F`, `A`.
  Standard hex pattern — handy for a debug display.

## Common anode vs common cathode

This lab uses a **common-cathode** 7-seg (all cathodes tied to GND; a pin
is ON when driven HIGH). Common-anode versions flip it: common pin goes
to 5 V and a segment lights when the Arduino pin is **LOW**. Invert the
writeDigit logic if you're adapting to a common-anode display.
$md$
    ),

    -- ------------------------------------------------------------------
    -- 3. Dark-activated night light — pure analog, no microcontroller
    -- ------------------------------------------------------------------
    (
      v_uid,
      substr(md5(random()::text || 'night'), 1, 8),
      'dark-activated-night-light',
      'Dark-activated night light (LDR + NPN switch)',
      'Classic analog circuit — no Arduino. An LDR forms a voltage divider that drives an NPN base. LED turns on only when ambient light drops. Four parts, one transistor, zero code.',
      'public',
      '{
        "parts": [
          {"id":"bat1","kind":"battery","x":3,"y":4,"props":{"voltage":9}},
          {"id":"r_led","kind":"resistor","x":10,"y":5,"props":{"resistance":330}},
          {"id":"led1","kind":"led","x":15,"y":4,"props":{"vf":2.0,"maxCurrent":0.025}},
          {"id":"npn1","kind":"npn","x":19,"y":4,"props":{"vbeOn":0.7,"beta":100}},
          {"id":"r_fixed","kind":"resistor","x":10,"y":9,"props":{"resistance":10000}},
          {"id":"ldr1","kind":"ldr","x":15,"y":9,"props":{"lightLevel":0.2,"rDark":1000000,"rBright":100}},
          {"id":"gnd1","kind":"ground","x":21,"y":12,"props":{}}
        ],
        "wires": [
          {"id":"w1","from":{"partId":"bat1","pinId":"pos"},"to":{"partId":"r_led","pinId":"a"}},
          {"id":"w2","from":{"partId":"r_led","pinId":"b"},"to":{"partId":"led1","pinId":"anode"}},
          {"id":"w3","from":{"partId":"led1","pinId":"cathode"},"to":{"partId":"npn1","pinId":"c"}},
          {"id":"w4","from":{"partId":"npn1","pinId":"e"},"to":{"partId":"gnd1","pinId":"gnd"}},
          {"id":"w5","from":{"partId":"bat1","pinId":"pos"},"to":{"partId":"r_fixed","pinId":"a"}},
          {"id":"w6","from":{"partId":"r_fixed","pinId":"b"},"to":{"partId":"ldr1","pinId":"a"}},
          {"id":"w7","from":{"partId":"r_fixed","pinId":"b"},"to":{"partId":"npn1","pinId":"b"}},
          {"id":"w8","from":{"partId":"ldr1","pinId":"b"},"to":{"partId":"gnd1","pinId":"gnd"}},
          {"id":"w9","from":{"partId":"bat1","pinId":"neg"},"to":{"partId":"gnd1","pinId":"gnd"}}
        ],
        "junctions": []
      }'::jsonb,
      '',
      $md$# Dark-activated night light

No Arduino. No code. Just transistor switching and a voltage divider
that reads the ambient light.

## The idea

An LDR (photoresistor) changes resistance with light:

| Condition | Resistance |
|-----------|------------|
| Pitch dark | ~1 MΩ |
| Indoors dim | ~50 kΩ |
| Bright room | ~1 kΩ |
| Direct sun | ~100 Ω |

Combine it with a fixed 10 kΩ resistor as a voltage divider, with the
tap going to an NPN transistor's base:

```
9V ──[ 10kΩ ]──┬──[ LDR ]── GND
               │
           base of NPN
```

**Dark** → LDR is huge → most of 9 V drops across the LDR → base sees
≈ 9 V (well above the 0.7 V turn-on) → transistor turns ON → LED lights.

**Bright** → LDR is tiny → almost all of 9 V drops across the 10 kΩ →
base sees ≈ 0 V → transistor OFF → LED dark.

## Why the transistor?

You could wire the LED straight across the LDR+R divider, but the
current through a voltage divider is tiny (microamps). A dim LED at
best. The NPN acts as a **current amplifier** — a tiny base current
controls a much larger collector current that actually lights the LED.
Gain β ≈ 100 for a typical NPN, so 50 µA at the base drives 5 mA at
the LED.

## Try this

This circuit has an **LDR `lightLevel` property**. Click the LDR and
drag the slider:

- `lightLevel = 0.0` (pitch dark) → LED full brightness
- `lightLevel = 0.5` (moderate) → LED dim
- `lightLevel = 1.0` (direct sun) → LED off

Also try:

- Swap the 10 kΩ fixed resistor for **1 kΩ**. Now the threshold shifts —
  the LED stays off until it's very dark. That's how you tune sensitivity
  in a real build.
- Reverse the divider (LDR on top, fixed resistor on bottom). Now it
  becomes a **light-activated** switch — useful for turning something
  on when a beam is broken.

## Why this is everywhere

Automatic streetlights, garden path lights, that "turn off when I leave
the room" LED under your desk — most of them are this exact circuit,
sometimes with a different transistor or with a 555 for hysteresis.
If you can read this one, you can read most of them.
$md$
    )
  on conflict (owner_id, project_slug) do update
    set name = excluded.name,
        description = excluded.description,
        visibility = excluded.visibility,
        circuit = excluded.circuit,
        code = excluded.code,
        readme = excluded.readme,
        updated_at = now();

  raise notice 'Seeded 3 complex labs: rgb-color-fader, seven-segment-counter, dark-activated-night-light.';
end $$;


-- ==========================================================================
-- 017_avatars_hotfix.sql
-- ==========================================================================
-- ExoSynk v0.8.1e: avatars storage hotfix.
-- Re-runs the storage bucket + RLS policies from migration 011 cleanly,
-- drops and recreates every policy so there's no mismatched leftover from
-- earlier partial runs. Safe to re-run.
--
-- Paste into Supabase SQL Editor → Run.

-- 1. Ensure the bucket exists and is public-readable.
insert into storage.buckets (id, name, public)
  values ('avatars', 'avatars', true)
  on conflict (id) do update set public = true;

-- 2. Drop any old avatar policies to avoid name-mismatch limbo.
drop policy if exists "avatars: public read"   on storage.objects;
drop policy if exists "avatars: self upload"   on storage.objects;
drop policy if exists "avatars: self write"    on storage.objects;
drop policy if exists "avatars: self update"   on storage.objects;
drop policy if exists "avatars: self delete"   on storage.objects;

-- 3. Install fresh policies.
-- Public read on everything in this bucket (needed for <img src> to resolve).
create policy "avatars: public read"
  on storage.objects for select
  using (bucket_id = 'avatars');

-- Users can upload to a folder named after their UID.
create policy "avatars: self upload"
  on storage.objects for insert
  with check (
    bucket_id = 'avatars'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- Users can update / replace their own objects (for the upsert:true path
-- when uploading a new avatar that overwrites the old one).
create policy "avatars: self update"
  on storage.objects for update
  using (
    bucket_id = 'avatars'
    and auth.uid()::text = (storage.foldername(name))[1]
  )
  with check (
    bucket_id = 'avatars'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- Users can delete their own avatar.
create policy "avatars: self delete"
  on storage.objects for delete
  using (
    bucket_id = 'avatars'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- 4. Quick diagnostic: show bucket status + policy count.
do $$
declare
  v_bucket record;
  v_policy_count int;
begin
  select * into v_bucket from storage.buckets where id = 'avatars';
  select count(*) into v_policy_count from pg_policies
    where schemaname = 'storage'
      and tablename = 'objects'
      and policyname like 'avatars:%';

  raise notice 'avatars bucket: id=%, public=%, policies=%',
    v_bucket.id, v_bucket.public, v_policy_count;

  if v_bucket.id is null then
    raise exception 'avatars bucket not created — check Storage settings in the dashboard';
  end if;
  if v_policy_count < 4 then
    raise exception 'expected 4 avatar policies, found %', v_policy_count;
  end if;
end $$;


-- ==========================================================================
-- 018_3d_model.sql
-- ==========================================================================
-- ExoSynk v0.9.0a: 3D enclosure designer — model storage.
-- One lab holds a 2D circuit (circuit jsonb) + an optional 3D enclosure
-- design (model_3d jsonb). Same row, same visibility rules, same fork/share.
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.labs
  add column if not exists model_3d jsonb;

-- Cheap existence filter — future /explore could surface labs that have a
-- 3D design attached. Partial index so nulls don't bloat it.
create index if not exists labs_has_model_3d_idx
  on public.labs((model_3d is not null)) where model_3d is not null;

comment on column public.labs.model_3d is
  'v0.9 enclosure design. Shape { enclosure: Primitive[], parts: Placed3DPart[], camera?: {...} }. Null until the user opens the 3D designer for this lab.';


-- ==========================================================================
-- 019_store.sql
-- ==========================================================================
-- ExoSynk v0.12.0: Store module — 3D model sharing marketplace.
-- First tab live: Models. Shopping + Parts coming soon (UI stubs).
-- Every model is FREE for now; a price_cents column is reserved so we
-- can flip to paid listings later without a schema change.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

-- =============================================================================
-- store_models: one row per shared 3D model
-- =============================================================================
create table if not exists public.store_models (
  id           uuid primary key default gen_random_uuid(),
  author_id    uuid not null references auth.users(id) on delete cascade,
  title        text not null check (length(trim(title)) between 1 and 120),
  description  text check (length(description) <= 2000),
  readme       text not null default '' check (length(readme) <= 30000),
  stl_url      text not null,
  cover_url    text,
  tags         text[] not null default '{}',
  -- NULL = free. When we enable paid listings, price_cents becomes positive
  -- and we add a Stripe / Razorpay payment flow separately.
  price_cents  int,
  license      text not null default 'CC-BY-4.0'
               check (license in ('CC0', 'CC-BY-4.0', 'CC-BY-SA-4.0', 'MIT', 'ALL-RIGHTS-RESERVED')),
  downloads    int not null default 0,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now()
);

create index if not exists store_models_recent_idx on public.store_models(created_at desc);
create index if not exists store_models_author_idx on public.store_models(author_id);

alter table public.store_models enable row level security;

drop policy if exists "store_models: read all" on public.store_models;
create policy "store_models: read all"
  on public.store_models for select using (true);

drop policy if exists "store_models: author writes" on public.store_models;
create policy "store_models: author writes"
  on public.store_models for insert
  with check (author_id = auth.uid());

drop policy if exists "store_models: author updates" on public.store_models;
create policy "store_models: author updates"
  on public.store_models for update
  using (author_id = auth.uid())
  with check (author_id = auth.uid());

drop policy if exists "store_models: author deletes" on public.store_models;
create policy "store_models: author deletes"
  on public.store_models for delete
  using (author_id = auth.uid());

-- =============================================================================
-- RPC: increment the download counter. Non-author-only so creators can't
-- inflate their own numbers. Runs security-definer so it bypasses the
-- author-only UPDATE policy.
-- =============================================================================
create or replace function public.increment_store_model_downloads(p_id uuid)
returns void
language plpgsql
security definer
as $func$
declare
  v_caller uuid;
  v_author uuid;
begin
  v_caller := auth.uid();
  v_author := (select author_id from public.store_models where id = p_id);
  if v_author is null then return; end if;
  if v_author = v_caller then return; end if; -- skip self-downloads
  update public.store_models set downloads = downloads + 1 where id = p_id;
end;
$func$;

-- =============================================================================
-- Storage buckets for STL + cover images
-- =============================================================================
insert into storage.buckets (id, name, public)
  values ('store-models', 'store-models', true)
  on conflict (id) do update set public = true;

insert into storage.buckets (id, name, public)
  values ('store-covers', 'store-covers', true)
  on conflict (id) do update set public = true;

-- STL bucket policies — anyone can read (downloads), authors write to their
-- own folder (<uid>/<file>.stl).
drop policy if exists "store-models: public read" on storage.objects;
create policy "store-models: public read"
  on storage.objects for select
  using (bucket_id = 'store-models');

drop policy if exists "store-models: self upload" on storage.objects;
create policy "store-models: self upload"
  on storage.objects for insert
  with check (
    bucket_id = 'store-models'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists "store-models: self delete" on storage.objects;
create policy "store-models: self delete"
  on storage.objects for delete
  using (
    bucket_id = 'store-models'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- Cover-image bucket policies — same pattern.
drop policy if exists "store-covers: public read" on storage.objects;
create policy "store-covers: public read"
  on storage.objects for select
  using (bucket_id = 'store-covers');

drop policy if exists "store-covers: self upload" on storage.objects;
create policy "store-covers: self upload"
  on storage.objects for insert
  with check (
    bucket_id = 'store-covers'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists "store-covers: self update" on storage.objects;
create policy "store-covers: self update"
  on storage.objects for update
  using (
    bucket_id = 'store-covers'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists "store-covers: self delete" on storage.objects;
create policy "store-covers: self delete"
  on storage.objects for delete
  using (
    bucket_id = 'store-covers'
    and auth.uid()::text = (storage.foldername(name))[1]
  );


-- ==========================================================================
-- 020_owned_models.sql
-- ==========================================================================
-- ExoSynk v0.12.1: personal owned-models library (GitHub-style forks).
--
-- Clicking Get on a Store model creates a full editable copy in the user's
-- own library. Owners can rename, rewrite the README, or delete their copy
-- without affecting the source. If the source store row is deleted later,
-- the owned copies survive because they hold their own stl_url reference.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

create table if not exists public.user_owned_models (
  id               uuid primary key default gen_random_uuid(),
  owner_id         uuid not null references auth.users(id) on delete cascade,
  -- Optional lineage: where this fork came from. Kept as a soft link
  -- (set null on delete) so deleting the source doesn't cascade-delete
  -- every fork — this is exactly how GitHub handles fork lineage.
  source_model_id  uuid references public.store_models(id) on delete set null,
  -- Editable metadata copied from source at fork time.
  title            text not null check (length(trim(title)) between 1 and 120),
  description      text check (length(description) <= 2000),
  readme           text not null default '' check (length(readme) <= 30000),
  stl_url          text not null,
  cover_url        text,
  -- Cached bounding box so the My Models palette can show size without
  -- re-fetching the STL every render. Populated client-side once at
  -- fork time from the parsed geometry.
  size_w_mm        real,
  size_h_mm        real,
  size_d_mm        real,
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now(),
  -- Prevent duplicate forks of the same source model. If the user wants
  -- a second copy, they can clone their own fork later.
  unique (owner_id, source_model_id)
);

create index if not exists owned_models_owner_idx on public.user_owned_models(owner_id, updated_at desc);

alter table public.user_owned_models enable row level security;

-- Public-read so other users can see what's in someone's Models section on
-- their profile. Private models land as a later feature if asked for.
drop policy if exists "owned_models: read all" on public.user_owned_models;
create policy "owned_models: read all"
  on public.user_owned_models for select using (true);

drop policy if exists "owned_models: owner writes" on public.user_owned_models;
create policy "owned_models: owner writes"
  on public.user_owned_models for insert
  with check (owner_id = auth.uid());

drop policy if exists "owned_models: owner updates" on public.user_owned_models;
create policy "owned_models: owner updates"
  on public.user_owned_models for update
  using (owner_id = auth.uid())
  with check (owner_id = auth.uid());

drop policy if exists "owned_models: owner deletes" on public.user_owned_models;
create policy "owned_models: owner deletes"
  on public.user_owned_models for delete
  using (owner_id = auth.uid());

-- Helper RPC: one-click fork. Security definer so a successful call can
-- both insert into user_owned_models AND bump the source model's download
-- counter atomically. Returns the new fork id (or the existing fork's id
-- if the user already owned it).
create or replace function public.fork_store_model(p_source uuid)
returns uuid
language plpgsql
security definer
as $func$
declare
  v_caller  uuid;
  v_src     public.store_models%rowtype;
  v_existing uuid;
  v_new     uuid;
begin
  v_caller := auth.uid();
  if v_caller is null then
    raise exception 'not signed in';
  end if;

  v_existing := (
    select id from public.user_owned_models
    where owner_id = v_caller and source_model_id = p_source
  );
  if v_existing is not null then
    return v_existing;
  end if;

  select * into v_src from public.store_models where id = p_source;
  if v_src.id is null then
    raise exception 'source model not found';
  end if;

  insert into public.user_owned_models (
    owner_id, source_model_id, title, description, readme,
    stl_url, cover_url
  ) values (
    v_caller, p_source, v_src.title, v_src.description, v_src.readme,
    v_src.stl_url, v_src.cover_url
  ) returning id into v_new;

  -- Skip self-download counting the same way the existing RPC does.
  if v_src.author_id <> v_caller then
    update public.store_models
      set downloads = downloads + 1
      where id = p_source;
  end if;

  return v_new;
end;
$func$;


-- ==========================================================================
-- 021_store_publish.sql
-- ==========================================================================
-- ExoSynk v0.12.2: Store redesign — publish from lab, stars, comments.
--
-- Store listings are now REFERENCES to labs, not standalone uploads. A lab
-- owner flips publish_2d / publish_3d in their Lab Settings → Store section
-- and the lab's live circuit / model_3d becomes the store item. "Get" forks
-- the current lab snapshot into user_owned_models — no STL bucket dance.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

-- =============================================================================
-- store_models: extend with lab link, gallery, tutorial, publish flags
-- =============================================================================
alter table public.store_models
  add column if not exists lab_id uuid references public.labs(id) on delete cascade,
  add column if not exists publish_2d boolean not null default false,
  add column if not exists publish_3d boolean not null default false,
  add column if not exists gallery_urls text[] not null default '{}',
  add column if not exists linked_tutorial_id uuid references public.tutorials(id) on delete set null,
  -- Self-contained snapshots captured every time the listing is saved.
  -- The detail page's 3D viewer and the fork RPC read these, not the live
  -- lab — editing the lab doesn't retroactively change existing forks.
  add column if not exists model_3d jsonb,
  add column if not exists circuit  jsonb;

-- One lab = one listing. A lab owner can't accidentally create two rows.
create unique index if not exists store_models_lab_uniq
  on public.store_models(lab_id)
  where lab_id is not null;

-- Legacy STL-uploaded rows have stl_url not null; new rows link lab_id and
-- leave stl_url as empty string. Drop the NOT NULL so new rows don't need it.
alter table public.store_models alter column stl_url drop not null;

-- =============================================================================
-- store_model_stars — per-user star (like GitHub stars).
--   * one star per (user, model)
--   * counter lives on store_models.star_count; kept accurate by triggers
-- =============================================================================
alter table public.store_models
  add column if not exists star_count int not null default 0;

create table if not exists public.store_model_stars (
  store_model_id  uuid not null references public.store_models(id) on delete cascade,
  user_id         uuid not null references auth.users(id) on delete cascade,
  created_at      timestamptz not null default now(),
  primary key (store_model_id, user_id)
);

create index if not exists store_model_stars_user_idx
  on public.store_model_stars(user_id, created_at desc);

alter table public.store_model_stars enable row level security;

drop policy if exists "store_stars: read all" on public.store_model_stars;
create policy "store_stars: read all"
  on public.store_model_stars for select using (true);

drop policy if exists "store_stars: self star" on public.store_model_stars;
create policy "store_stars: self star"
  on public.store_model_stars for insert
  with check (user_id = auth.uid());

drop policy if exists "store_stars: self unstar" on public.store_model_stars;
create policy "store_stars: self unstar"
  on public.store_model_stars for delete
  using (user_id = auth.uid());

-- Trigger: keep star_count on store_models in sync.
create or replace function public.store_stars_bump()
returns trigger language plpgsql as $func$
begin
  if tg_op = 'INSERT' then
    update public.store_models set star_count = star_count + 1
      where id = new.store_model_id;
    return new;
  elsif tg_op = 'DELETE' then
    update public.store_models set star_count = greatest(0, star_count - 1)
      where id = old.store_model_id;
    return old;
  end if;
  return null;
end;
$func$;

drop trigger if exists store_stars_bump_ins on public.store_model_stars;
create trigger store_stars_bump_ins after insert on public.store_model_stars
  for each row execute function public.store_stars_bump();

drop trigger if exists store_stars_bump_del on public.store_model_stars;
create trigger store_stars_bump_del after delete on public.store_model_stars
  for each row execute function public.store_stars_bump();

-- =============================================================================
-- store_model_comments — reviews on a listing.
-- =============================================================================
create table if not exists public.store_model_comments (
  id              uuid primary key default gen_random_uuid(),
  store_model_id  uuid not null references public.store_models(id) on delete cascade,
  author_id       uuid not null references auth.users(id) on delete cascade,
  body            text not null check (length(trim(body)) between 1 and 4000),
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now()
);

create index if not exists store_comments_model_idx
  on public.store_model_comments(store_model_id, created_at desc);

alter table public.store_model_comments enable row level security;

drop policy if exists "store_comments: read all" on public.store_model_comments;
create policy "store_comments: read all"
  on public.store_model_comments for select using (true);

drop policy if exists "store_comments: self insert" on public.store_model_comments;
create policy "store_comments: self insert"
  on public.store_model_comments for insert
  with check (author_id = auth.uid());

drop policy if exists "store_comments: self update" on public.store_model_comments;
create policy "store_comments: self update"
  on public.store_model_comments for update
  using (author_id = auth.uid())
  with check (author_id = auth.uid());

drop policy if exists "store_comments: self delete" on public.store_model_comments;
create policy "store_comments: self delete"
  on public.store_model_comments for delete
  using (author_id = auth.uid());

-- =============================================================================
-- user_owned_models — snapshot from lab instead of STL copy
-- =============================================================================
alter table public.user_owned_models
  add column if not exists source_lab_id uuid references public.labs(id) on delete set null,
  add column if not exists model_3d  jsonb,
  add column if not exists circuit   jsonb;

-- STL url no longer required — forks carry live JSON from the source lab.
alter table public.user_owned_models alter column stl_url drop not null;
alter table public.user_owned_models alter column stl_url set default '';

-- =============================================================================
-- fork_store_model rewrite — snapshots lab state into the user's library.
-- =============================================================================
create or replace function public.fork_store_model(p_source uuid)
returns uuid
language plpgsql
security definer
as $func$
declare
  v_caller   uuid;
  v_src      public.store_models%rowtype;
  v_existing uuid;
  v_new      uuid;
begin
  v_caller := auth.uid();
  if v_caller is null then
    raise exception 'not signed in';
  end if;

  v_existing := (
    select id from public.user_owned_models
    where owner_id = v_caller and source_model_id = p_source
  );
  if v_existing is not null then
    return v_existing;
  end if;

  select * into v_src from public.store_models where id = p_source;
  if v_src.id is null then
    raise exception 'source model not found';
  end if;

  insert into public.user_owned_models (
    owner_id, source_model_id, source_lab_id,
    title, description, readme,
    stl_url, cover_url,
    model_3d, circuit
  ) values (
    v_caller, p_source, v_src.lab_id,
    v_src.title, v_src.description, v_src.readme,
    coalesce(v_src.stl_url, ''), v_src.cover_url,
    case when v_src.publish_3d then v_src.model_3d else null end,
    case when v_src.publish_2d then v_src.circuit  else null end
  ) returning id into v_new;

  if v_src.author_id <> v_caller then
    update public.store_models
      set downloads = downloads + 1
      where id = p_source;
  end if;

  return v_new;
end;
$func$;

-- =============================================================================
-- RPC: toggle star on a store model (idempotent). Returns true if now starred.
-- =============================================================================
create or replace function public.toggle_store_model_star(p_id uuid)
returns boolean
language plpgsql
security definer
as $func$
declare
  v_caller uuid;
  v_existed boolean;
begin
  v_caller := auth.uid();
  if v_caller is null then raise exception 'not signed in'; end if;

  v_existed := exists(
    select 1 from public.store_model_stars
    where store_model_id = p_id and user_id = v_caller
  );

  if v_existed then
    delete from public.store_model_stars
      where store_model_id = p_id and user_id = v_caller;
    return false;
  end if;

  insert into public.store_model_stars (store_model_id, user_id)
    values (p_id, v_caller)
    on conflict do nothing;
  return true;
end;
$func$;

-- =============================================================================
-- View: per-user total stars received across their store listings.
-- Used on the profile page ("stars earned across all your models").
-- =============================================================================
create or replace view public.user_total_store_stars as
  select m.author_id as user_id, coalesce(sum(m.star_count), 0)::int as total_stars
  from public.store_models m
  group by m.author_id;


-- ==========================================================================
-- 022_profile_pro.sql
-- ==========================================================================
-- ExoSynk v0.12.3: professional profile — pins, analytics views, contribution graph.
--
-- Adds:
--   • profile_pins: up to 5 pinned items per kind (lab, tutorial, store_model)
--     that show as highlights on the public profile.
--   • user_stars_received view: total stars earned, split by source.
--   • user_comment_votes_received view: up / down votes on the user's
--     comments (lab + tutorial). Labs, tutorials, and store listings
--     themselves are star-only, so downvotes only live on comments.
--   • user_activity_days view: (user, day, contributions) aggregated from
--     every user-authored timestamp so we can draw a GitHub-style heatmap
--     without a separate event log.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

-- =============================================================================
-- profile_pins — up to 5 of each kind
-- =============================================================================
create table if not exists public.profile_pins (
  user_id    uuid not null references auth.users(id) on delete cascade,
  kind       text not null check (kind in ('lab', 'tutorial', 'store_model')),
  target_id  uuid not null,
  position   smallint not null check (position between 1 and 5),
  created_at timestamptz not null default now(),
  primary key (user_id, kind, position),
  unique (user_id, kind, target_id)
);

create index if not exists profile_pins_user_idx on public.profile_pins(user_id, kind, position);

alter table public.profile_pins enable row level security;

drop policy if exists "pins: read all" on public.profile_pins;
create policy "pins: read all"
  on public.profile_pins for select using (true);

drop policy if exists "pins: self insert" on public.profile_pins;
create policy "pins: self insert"
  on public.profile_pins for insert
  with check (user_id = auth.uid());

drop policy if exists "pins: self update" on public.profile_pins;
create policy "pins: self update"
  on public.profile_pins for update
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

drop policy if exists "pins: self delete" on public.profile_pins;
create policy "pins: self delete"
  on public.profile_pins for delete
  using (user_id = auth.uid());

-- =============================================================================
-- user_stars_received — total stars earned, split by source.
-- Labs + tutorials + store listings are all star-only (no downvote column),
-- so this is pure cumulative popularity.
-- =============================================================================
create or replace view public.user_stars_received as
select
  user_id,
  sum(store_stars)::int    as store_stars,
  sum(tutorial_stars)::int as tutorial_stars,
  sum(lab_stars)::int      as lab_stars,
  (sum(store_stars) + sum(tutorial_stars) + sum(lab_stars))::int as total_stars
from (
  select author_id as user_id,
         coalesce(sum(star_count), 0) as store_stars,
         0::bigint                    as tutorial_stars,
         0::bigint                    as lab_stars
    from public.store_models
    group by author_id
  union all
  select author_id as user_id,
         0::bigint,
         coalesce(sum(upvote_count), 0),
         0::bigint
    from public.tutorials
    group by author_id
  union all
  select owner_id as user_id,
         0::bigint,
         0::bigint,
         coalesce(sum(upvote_count), 0)
    from public.labs
    group by owner_id
) src
group by user_id;

-- =============================================================================
-- user_comment_votes_received — up / downvotes across the user's comments.
-- Covers lab comments (comments table) and tutorial comments. Store
-- listing comments aren't voteable.
-- =============================================================================
create or replace view public.user_comment_votes_received as
select user_id, sum(ups)::int as upvotes, sum(downs)::int as downvotes
from (
  -- Lab comments
  select c.author_id as user_id,
         coalesce(sum(case when cv.value =  1 then 1 else 0 end), 0) as ups,
         coalesce(sum(case when cv.value = -1 then 1 else 0 end), 0) as downs
    from public.comments c
    left join public.comment_votes cv on cv.comment_id = c.id
    group by c.author_id
  union all
  -- Tutorial comments
  select tc.author_id as user_id,
         coalesce(sum(case when tcv.value =  1 then 1 else 0 end), 0) as ups,
         coalesce(sum(case when tcv.value = -1 then 1 else 0 end), 0) as downs
    from public.tutorial_comments tc
    left join public.tutorial_comment_votes tcv on tcv.comment_id = tc.id
    group by tc.author_id
) src
group by user_id;

-- =============================================================================
-- user_activity_days — one row per (user, day) with a contribution count.
-- Feeds the GitHub-style heatmap. Sources every user-authored event we
-- already have timestamps for.
-- =============================================================================
create or replace view public.user_activity_days as
with events as (
  -- Content touches (create OR update — last-touched day)
  select owner_id  as user_id, date(updated_at) as day from public.labs
  union all
  select author_id, date(updated_at)                   from public.tutorials
  union all
  select author_id, date(updated_at)                   from public.store_models
  -- Comments authored
  union all
  select author_id, date(created_at)                   from public.comments
  union all
  select author_id, date(created_at)                   from public.tutorial_comments
  union all
  select author_id, date(created_at)                   from public.store_model_comments
  -- Stars given (low-effort but still engagement)
  union all
  select user_id,   date(created_at)                   from public.lab_votes
  union all
  select user_id,   date(created_at)                   from public.tutorial_votes
  union all
  select user_id,   date(created_at)                   from public.store_model_stars
)
select user_id, day, count(*)::int as contributions
from events
where user_id is not null and day is not null
group by user_id, day;


-- ==========================================================================
-- 023_signal.sql
-- ==========================================================================
-- ExoSynk v0.12.4: Signal — the social feed.
--
-- A Twitter-ish surface for builders: short posts (≤1000 chars), optional
-- image, optional attachment linking a lab / tutorial / store listing that
-- auto-embeds as a rich card on the post. One-level replies (no deep
-- threads). Like / unlike.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

-- =============================================================================
-- posts — top-level feed items
-- =============================================================================
create table if not exists public.posts (
  id           uuid primary key default gen_random_uuid(),
  author_id    uuid not null references auth.users(id) on delete cascade,
  body         text not null check (length(trim(body)) between 1 and 1000),
  image_url    text,
  -- Optional attachment. Exactly one of lab/tutorial/store when set.
  attach_kind  text check (attach_kind in ('lab', 'tutorial', 'store_model')),
  attach_id    uuid,
  like_count   int  not null default 0,
  reply_count  int  not null default 0,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now(),
  constraint posts_attach_consistent check (
    (attach_kind is null and attach_id is null) or
    (attach_kind is not null and attach_id is not null)
  )
);

create index if not exists posts_feed_idx on public.posts(created_at desc);
create index if not exists posts_author_idx on public.posts(author_id, created_at desc);

alter table public.posts enable row level security;

drop policy if exists "posts: read all" on public.posts;
create policy "posts: read all"
  on public.posts for select using (true);

drop policy if exists "posts: self insert" on public.posts;
create policy "posts: self insert"
  on public.posts for insert
  with check (author_id = auth.uid());

drop policy if exists "posts: self update" on public.posts;
create policy "posts: self update"
  on public.posts for update
  using (author_id = auth.uid())
  with check (author_id = auth.uid());

drop policy if exists "posts: self delete" on public.posts;
create policy "posts: self delete"
  on public.posts for delete
  using (author_id = auth.uid());

-- =============================================================================
-- post_likes — one per (user, post)
-- =============================================================================
create table if not exists public.post_likes (
  post_id    uuid not null references public.posts(id) on delete cascade,
  user_id    uuid not null references auth.users(id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (post_id, user_id)
);

create index if not exists post_likes_user_idx on public.post_likes(user_id, created_at desc);

alter table public.post_likes enable row level security;

drop policy if exists "post_likes: read all" on public.post_likes;
create policy "post_likes: read all"
  on public.post_likes for select using (true);

drop policy if exists "post_likes: self insert" on public.post_likes;
create policy "post_likes: self insert"
  on public.post_likes for insert
  with check (user_id = auth.uid());

drop policy if exists "post_likes: self delete" on public.post_likes;
create policy "post_likes: self delete"
  on public.post_likes for delete
  using (user_id = auth.uid());

-- Trigger: keep posts.like_count in sync.
create or replace function public.post_likes_bump()
returns trigger language plpgsql as $func$
begin
  if tg_op = 'INSERT' then
    update public.posts set like_count = like_count + 1 where id = new.post_id;
    return new;
  elsif tg_op = 'DELETE' then
    update public.posts set like_count = greatest(0, like_count - 1) where id = old.post_id;
    return old;
  end if;
  return null;
end;
$func$;

drop trigger if exists post_likes_bump_ins on public.post_likes;
create trigger post_likes_bump_ins after insert on public.post_likes
  for each row execute function public.post_likes_bump();

drop trigger if exists post_likes_bump_del on public.post_likes;
create trigger post_likes_bump_del after delete on public.post_likes
  for each row execute function public.post_likes_bump();

-- =============================================================================
-- post_replies — one-level replies
-- =============================================================================
create table if not exists public.post_replies (
  id         uuid primary key default gen_random_uuid(),
  post_id    uuid not null references public.posts(id) on delete cascade,
  author_id  uuid not null references auth.users(id) on delete cascade,
  body       text not null check (length(trim(body)) between 1 and 1000),
  created_at timestamptz not null default now()
);

create index if not exists post_replies_post_idx on public.post_replies(post_id, created_at asc);

alter table public.post_replies enable row level security;

drop policy if exists "post_replies: read all" on public.post_replies;
create policy "post_replies: read all"
  on public.post_replies for select using (true);

drop policy if exists "post_replies: self insert" on public.post_replies;
create policy "post_replies: self insert"
  on public.post_replies for insert
  with check (author_id = auth.uid());

drop policy if exists "post_replies: self delete" on public.post_replies;
create policy "post_replies: self delete"
  on public.post_replies for delete
  using (author_id = auth.uid());

-- Trigger: keep posts.reply_count in sync.
create or replace function public.post_replies_bump()
returns trigger language plpgsql as $func$
begin
  if tg_op = 'INSERT' then
    update public.posts set reply_count = reply_count + 1 where id = new.post_id;
    return new;
  elsif tg_op = 'DELETE' then
    update public.posts set reply_count = greatest(0, reply_count - 1) where id = old.post_id;
    return old;
  end if;
  return null;
end;
$func$;

drop trigger if exists post_replies_bump_ins on public.post_replies;
create trigger post_replies_bump_ins after insert on public.post_replies
  for each row execute function public.post_replies_bump();

drop trigger if exists post_replies_bump_del on public.post_replies;
create trigger post_replies_bump_del after delete on public.post_replies
  for each row execute function public.post_replies_bump();

-- =============================================================================
-- Bucket + storage policies for post images
-- =============================================================================
insert into storage.buckets (id, name, public)
  values ('post-images', 'post-images', true)
  on conflict (id) do update set public = true;

drop policy if exists "post-images: public read" on storage.objects;
create policy "post-images: public read"
  on storage.objects for select
  using (bucket_id = 'post-images');

drop policy if exists "post-images: self upload" on storage.objects;
create policy "post-images: self upload"
  on storage.objects for insert
  with check (
    bucket_id = 'post-images'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists "post-images: self delete" on storage.objects;
create policy "post-images: self delete"
  on storage.objects for delete
  using (
    bucket_id = 'post-images'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- =============================================================================
-- Tie Signal posts into the contribution graph.
-- Re-create user_activity_days to include post + reply + like events.
-- =============================================================================
create or replace view public.user_activity_days as
with events as (
  select owner_id  as user_id, date(updated_at) as day from public.labs
  union all
  select author_id, date(updated_at)                   from public.tutorials
  union all
  select author_id, date(updated_at)                   from public.store_models
  union all
  select author_id, date(created_at)                   from public.comments
  union all
  select author_id, date(created_at)                   from public.tutorial_comments
  union all
  select author_id, date(created_at)                   from public.store_model_comments
  union all
  select user_id,   date(created_at)                   from public.lab_votes
  union all
  select user_id,   date(created_at)                   from public.tutorial_votes
  union all
  select user_id,   date(created_at)                   from public.store_model_stars
  union all
  -- Signal
  select author_id, date(created_at)                   from public.posts
  union all
  select author_id, date(created_at)                   from public.post_replies
  union all
  select user_id,   date(created_at)                   from public.post_likes
)
select user_id, day, count(*)::int as contributions
from events
where user_id is not null and day is not null
group by user_id, day;


-- ==========================================================================
-- 024_mention_codes.sql
-- ==========================================================================
-- ExoSynk v0.12.5: universal mention short codes (MCODEs).
--
-- Adds a 5-char Crockford-base-32 short_code column to:
--   • store_models   — for @S/<code> mentions
--   • posts          — for @N/<code> mentions (N = siNal)
-- Generator RPC mints a code at insert time via trigger, with retry on
-- collision. Codes stay immutable for the life of the row — they ARE the
-- mention target. Existing rows are back-filled on migration run.
--
-- Labs and tutorials keep using slug-based mentions (@P/handle/slug,
-- @L/handle/slug) — they already have memorable addresses, no need for
-- a second namespace.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.store_models add column if not exists short_code text;
alter table public.posts         add column if not exists short_code text;

-- =============================================================================
-- Code generator. Crockford base-32 minus the ambiguous letters (I/L/O/U).
-- 5 chars × 28 glyphs = ~17M addresses — plenty for a long while, and the
-- codes read cleanly out loud ("A3F2K"). Retries on collision up to 5 times
-- and then widens to 6 chars if we're truly unlucky.
-- =============================================================================
create or replace function public.mint_mention_code(p_table regclass, p_length int default 5)
returns text
language plpgsql
as $func$
declare
  v_alphabet constant text := 'ABCDEFGHJKMNPQRSTVWXYZ23456789';
  v_code text;
  v_exists boolean;
  v_attempt int := 0;
  v_len int := p_length;
begin
  loop
    v_attempt := v_attempt + 1;
    v_code := '';
    for i in 1..v_len loop
      v_code := v_code || substr(v_alphabet, (floor(random() * length(v_alphabet))::int) + 1, 1);
    end loop;
    execute format('select exists(select 1 from %s where short_code = $1)', p_table)
      using v_code into v_exists;
    if not v_exists then return v_code; end if;
    if v_attempt >= 5 then v_len := v_len + 1; v_attempt := 0; end if;
  end loop;
end;
$func$;

-- Trigger helpers — one per table so we can use the right regclass.
create or replace function public.store_models_set_code()
returns trigger language plpgsql as $func$
begin
  if new.short_code is null then
    new.short_code := public.mint_mention_code('public.store_models'::regclass, 5);
  end if;
  return new;
end;
$func$;

create or replace function public.posts_set_code()
returns trigger language plpgsql as $func$
begin
  if new.short_code is null then
    new.short_code := public.mint_mention_code('public.posts'::regclass, 5);
  end if;
  return new;
end;
$func$;

drop trigger if exists store_models_set_code_bi on public.store_models;
create trigger store_models_set_code_bi
  before insert on public.store_models
  for each row execute function public.store_models_set_code();

drop trigger if exists posts_set_code_bi on public.posts;
create trigger posts_set_code_bi
  before insert on public.posts
  for each row execute function public.posts_set_code();

-- Back-fill existing rows.
update public.store_models
  set short_code = public.mint_mention_code('public.store_models'::regclass, 5)
  where short_code is null;

update public.posts
  set short_code = public.mint_mention_code('public.posts'::regclass, 5)
  where short_code is null;

-- Enforce the invariants once everything has a value.
alter table public.store_models alter column short_code set not null;
alter table public.posts         alter column short_code set not null;

create unique index if not exists store_models_code_uniq on public.store_models(short_code);
create unique index if not exists posts_code_uniq        on public.posts(short_code);


-- ==========================================================================
-- 025_post_embed_toggle.sql
-- ==========================================================================
-- ExoSynk v0.12.6b: per-post toggle for inline mention embeds.
--
-- Default ON — when a post body mentions a project / tutorial / store /
-- signal, the rich card renders under the body. Authors who want to
-- mention something inline without the visual clutter can flip it OFF
-- in the composer; the mention still auto-links and shows a hover card,
-- just no big embed below the text.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.posts
  add column if not exists show_embeds boolean not null default true;


-- ==========================================================================
-- 026_verified_tiers.sql
-- ==========================================================================
-- ExoSynk v0.12.7b: expand verified_tier to 4 tiers.
--
-- Tier meaning:
--   founder  — reserved for Yash Patil, the platform founder (black tick)
--   official — ExoSynk itself and any ExoSynk-affiliated account       (red tick)
--   creator  — verified independent creators / influencers              (blue tick)
--   business — verified businesses, sponsors, partners                  (gold tick)
--
-- Only founder + official are issued today. creator + business are
-- reserved so assigning them later doesn't require another migration.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.profiles
  drop constraint if exists profiles_verified_tier_check;

alter table public.profiles
  add constraint profiles_verified_tier_check
  check (verified_tier is null or verified_tier in ('founder', 'official', 'creator', 'business'));


-- ==========================================================================
-- 027_exosynk_v0_12_9_content.sql
-- ==========================================================================
-- ExoSynk v0.12.9a: real content from the @exosynk account showcasing the
-- new MCUs + servo + motor-spin features shipped in v0.12.7–v0.12.9.
--
-- Creates four concrete artifacts, all authored by the @exosynk account:
--   1) A public lab: ESP32 + servo + LED sweep demo using the new parts.
--   2) A tutorial in /learn/exosynk/… that walks through the build pin
--      by pin and explains why each part is needed.
--   3) A Store listing published from the lab so visitors can Get a
--      free forkable copy.
--   4) A Signal post announcing the v0.12.9 release and @-linking the
--      tutorial + store listing so the feed gets rich embeds.
--
-- Paste into Supabase SQL Editor → Run. Idempotent on re-run: uses slug
-- + body matches to skip items that already exist.

do $$
declare
  v_uid       uuid;
  v_lab_id    uuid;
  v_tut_id    uuid;
  v_store_id  uuid;
  v_lab_slug  text;
begin
  select id into v_uid from public.profiles where lower(username) = 'exosynk';
  if v_uid is null then
    raise notice 'No @exosynk profile. Sign up and set username first, then re-run.';
    return;
  end if;

  -- ---------- 1. Lab: ESP32 servo sweep demo ----------
  -- project_slug is unique per owner, so we use that as the idempotency key.
  if not exists (
    select 1 from public.labs
    where owner_id = v_uid and project_slug = 'esp32-servo-sweep'
  ) then
    v_lab_slug := substr(md5(random()::text || 'esp32svs'), 1, 8);
    insert into public.labs
      (owner_id, slug, project_slug, name, description, visibility, circuit, code, readme)
    values
      (
        v_uid,
        v_lab_slug,
        'esp32-servo-sweep',
        'ESP32 servo sweep with status LED',
        'A 5-part starter project using the new ESP32 DevKit + hobby servo. GPIO18 drives servo signal, GPIO23 lights a status LED while the code is running.',
        'public',
        '{
          "parts": [
            {"id":"esp1","kind":"esp32","x":3,"y":3,"props":{"v_3v3":3.3,"v_d18":0,"v_d23":3.3}},
            {"id":"srv1","kind":"servo","x":19,"y":3,"props":{"position":90,"idleMA":15}},
            {"id":"r1","kind":"resistor","x":5,"y":11,"props":{"resistance":330}},
            {"id":"led1","kind":"led","x":11,"y":10,"props":{"vf":2.0,"maxCurrent":0.025}},
            {"id":"gnd1","kind":"ground","x":14,"y":13,"props":{}}
          ],
          "wires": [
            {"id":"w1","from":{"partId":"esp1","pinId":"3v3"},"to":{"partId":"srv1","pinId":"vcc"}},
            {"id":"w2","from":{"partId":"esp1","pinId":"d18"},"to":{"partId":"srv1","pinId":"sig"}},
            {"id":"w3","from":{"partId":"esp1","pinId":"gnd"},"to":{"partId":"srv1","pinId":"gnd"}},
            {"id":"w4","from":{"partId":"esp1","pinId":"d23"},"to":{"partId":"r1","pinId":"a"}},
            {"id":"w5","from":{"partId":"r1","pinId":"b"},"to":{"partId":"led1","pinId":"anode"}},
            {"id":"w6","from":{"partId":"led1","pinId":"cathode"},"to":{"partId":"gnd1","pinId":"gnd"}}
          ],
          "junctions": []
        }'::jsonb,
        $code$// ESP32 servo sweep with status LED.
// Signal pin drives a hobby servo from 0 → 180 → 0; status LED stays on
// while the loop is running. Works on any ESP32 DevKit — the Arduino
// IDE's Servo.h library ships with ESP32 support since Arduino-ESP32 2.x.

#include <ESP32Servo.h>

const int SIG_PIN = 18;  // Servo signal
const int LED_PIN = 23;  // Status LED through a 330 Ω resistor

Servo servo;

void setup() {
  pinMode(LED_PIN, OUTPUT);
  digitalWrite(LED_PIN, HIGH);   // status LED on as soon as we boot
  servo.attach(SIG_PIN);
}

void loop() {
  for (int pos = 0; pos <= 180; pos++) {
    servo.write(pos);
    delay(8);
  }
  for (int pos = 180; pos >= 0; pos--) {
    servo.write(pos);
    delay(8);
  }
}
$code$,
        $md$# ESP32 servo sweep — 5 parts, 20 lines of code

This is a classic "hello world" for the ESP32: drive a hobby servo back
and forth, with a little status LED that confirms the board is alive.

## Bill of materials

- 1 × **ESP32 DevKit** (or any ESP32 board with GPIO18 + GPIO23 broken out)
- 1 × **Hobby servo** (SG90 or similar — anything that runs on 3.3–6V)
- 1 × **330 Ω resistor**
- 1 × **LED** (any color)
- 1 × **Ground** rail

## Wiring

- `ESP32 3V3` → Servo `V+`
- `ESP32 GND` → Servo `GND` → Ground rail
- `ESP32 GPIO18` → Servo signal
- `ESP32 GPIO23` → 330 Ω resistor → LED anode → Ground

## How it runs

Flash the code, power the board, and the servo will sweep 0 → 180 → 0
over roughly three seconds while the status LED stays lit. If the LED
stays dark, GPIO23 isn't writing high — check the wire order.

## Try it

Hit Simulate in the lab. The status LED turns on because GPIO23 is set
to 3.3V in the initial props. Drag the servo position in the inspector
and watch the arm move.
$md$
      )
    returning id into v_lab_id;
  else
    select id into v_lab_id from public.labs
      where owner_id = v_uid and project_slug = 'esp32-servo-sweep';
  end if;

  -- ---------- 2. Tutorial: walk through the build ----------
  if not exists (
    select 1 from public.tutorials
    where author_id = v_uid and slug = 'esp32-servo-sweep'
  ) then
    insert into public.tutorials
      (author_id, slug, title, description, content, visibility, published_at)
    values
      (
        v_uid,
        'esp32-servo-sweep',
        'Your first ESP32 build: a servo that sweeps',
        'Five parts and twenty lines of code. How the ESP32 talks to a hobby servo, why the status LED lives on GPIO23, and how to verify every wire in the sim before you touch hardware.',
        $md$
# Your first ESP32 build: a servo that sweeps

If you just shipped an Arduino LED project and want to try something
with an actual *moving part* — this is the next step.

We'll wire an ESP32 DevKit to a hobby servo, add a status LED that
turns on while the code is running, and write a 20-line sketch that
sweeps the servo back and forth. Total part count: **five**.

## What's new about the ESP32

The ESP32 is a 3.3V microcontroller with WiFi and Bluetooth baked in.
For this project, you can treat it exactly like an Arduino — the
Arduino IDE supports ESP32 as a board target, and libraries like
`ESP32Servo` give you the same `servo.write(angle)` API you'd use on
an Uno.

Two things to remember:

1. **Power rail is 3.3V**, not 5V. Your LED current-limiting math
   doesn't change much — a 330 Ω resistor still keeps a 2V LED
   under its 25 mA limit — but servos typically want 5V, so run them
   from external supply if the servo strains or stalls.
2. **Pin labels are GPIO numbers**. GPIO18 and GPIO23 are common
   choices because they're easy to reach on every ESP32 board and
   they're not used by boot-strap or JTAG.

## Wiring

Drop these five parts onto the canvas:

- **ESP32 DevKit** — from Microcontrollers
- **Servo** — from Actuators
- **330 Ω resistor** — from Passives
- **LED** — from Diodes & LEDs
- **Ground** — from Power

Then wire them:

| From                 | To                     |
| -------------------- | ---------------------- |
| `ESP32 · 3V3`        | `Servo · V+`           |
| `ESP32 · GND`        | `Servo · GND`          |
| `ESP32 · GPIO18`     | `Servo · S` (signal)   |
| `ESP32 · GPIO23`     | `Resistor · a`         |
| `Resistor · b`       | `LED · anode`          |
| `LED · cathode`      | `Ground`               |

That's it. Hit Simulate and the LED should glow — GPIO23 is seeded to
3.3V in the lab so the sim reads "LED on" without needing the code to
run. Drag the servo's `position` property in the inspector and watch
the arm rotate.

## The code

```cpp
#include <ESP32Servo.h>

const int SIG_PIN = 18;
const int LED_PIN = 23;

Servo servo;

void setup() {
  pinMode(LED_PIN, OUTPUT);
  digitalWrite(LED_PIN, HIGH);
  servo.attach(SIG_PIN);
}

void loop() {
  for (int pos = 0; pos <= 180; pos++) {
    servo.write(pos);
    delay(8);
  }
  for (int pos = 180; pos >= 0; pos--) {
    servo.write(pos);
    delay(8);
  }
}
```

## What to build next

- Swap the servo for a **DC motor + relay** — the new motor symbol
  actually spins in the sim, and a relay lets the ESP32 reverse
  direction without melting itself.
- Swap the ESP32 for a **Pi Pico** — the sketch is nearly identical;
  the pin labels change to `GP0`, `GP1`, … but the wiring pattern is
  the same.
- Add a **photoresistor** (LDR) to GPIO A0 and make the servo position
  follow the room's brightness. All the parts are in the palette.

Happy building.
$md$,
        'public',
        now()
      )
    returning id into v_tut_id;
  else
    select id into v_tut_id from public.tutorials
      where author_id = v_uid and slug = 'esp32-servo-sweep';
  end if;

  -- ---------- 3. Store listing (publish the lab) ----------
  if not exists (
    select 1 from public.store_models where lab_id = v_lab_id
  ) then
    insert into public.store_models
      (author_id, lab_id, publish_2d, publish_3d, title, description, readme, tags, license,
       linked_tutorial_id, circuit, model_3d, stl_url)
    values
      (
        v_uid,
        v_lab_id,
        true,   -- 2D circuit published
        false,  -- no 3D enclosure on this demo
        'ESP32 servo sweep with status LED',
        'Starter project with the new ESP32 + servo parts. Five components, Get a free forkable copy, drop it into your own lab.',
        $md$# ESP32 servo sweep — starter listing

This is a forkable copy of the ESP32 servo demo covered in the
[tutorial](/learn/exosynk/esp32-servo-sweep).

## What you get

A lab-backed store listing. Click **Get For Free** to land a copy in
your personal library at `/models/mine`; from there, open it in the
2D lab to edit, run the sim, or drop it into another project.

## What's inside

- 1 ESP32 DevKit
- 1 Hobby servo
- 1 330 Ω resistor
- 1 LED
- 1 Ground rail

Wires are already routed; `GPIO23` is seeded to 3.3V so the LED lights
up the moment you open the sim.

## License

CC BY 4.0 — remix freely, keep the attribution.
$md$,
        array['esp32', 'servo', 'starter', 'mcu'],
        'CC-BY-4.0',
        v_tut_id,
        -- snapshot the lab's circuit at publish time
        (select circuit from public.labs where id = v_lab_id),
        null,
        null
      )
    returning id into v_store_id;
  else
    select id into v_store_id from public.store_models where lab_id = v_lab_id;
  end if;

  -- ---------- 4. Signal post: announcement ----------
  -- Dedupe on body prefix so re-runs don't spam the feed.
  if not exists (
    select 1 from public.posts
    where author_id = v_uid and body like '%v0.12.9 is live%'
  ) then
    insert into public.posts (author_id, body, show_embeds)
    values (
      v_uid,
      'v0.12.9 is live. Three new parts in the 2D lab:' || chr(10) ||
      chr(10) ||
      '• ESP32 DevKit — full GPIO/analog/3V3' || chr(10) ||
      '• Raspberry Pi Pico — GP0..GP15 + VBUS + 3V3' || chr(10) ||
      '• Hobby servo — rotating arm follows the position prop 0..180°' || chr(10) ||
      chr(10) ||
      'Plus: the DC motor actually spins in the sim now (direction follows current polarity, speed scales with amps).' || chr(10) ||
      chr(10) ||
      'Built a demo that uses all of it — ESP32 driving a servo with a status LED. Five parts, twenty lines of code. Full walkthrough: @L/exosynk/esp32-servo-sweep' || chr(10) ||
      chr(10) ||
      'Or just Get a free forkable copy: @P/exosynk/esp32-servo-sweep',
      true
    );
  end if;

  raise notice 'Seeded: lab=%, tutorial=%, store=%', v_lab_id, v_tut_id, v_store_id;
end
$$;


-- ==========================================================================
-- 028_admin_panel.sql
-- ==========================================================================
-- ExoSynk v0.13.0: Phase 1 admin panel.
--
-- Adds an admin_role column on profiles so the founder + trusted team
-- members can moderate without opening the Supabase SQL editor for
-- routine tasks. Three tiers:
--   superadmin — can grant/revoke admin roles, can do everything
--   admin      — full moderation powers, can't create new admins
--   moderator  — content moderation only, no user-level actions
--
-- Also seeds the @yashpatil account as the first superadmin. Future
-- superadmins are added by Yash via the admin UI (not SQL).
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.profiles
  add column if not exists admin_role text
  check (admin_role is null or admin_role in ('superadmin', 'admin', 'moderator'));

create index if not exists profiles_admin_idx
  on public.profiles(admin_role)
  where admin_role is not null;

-- RLS: admins can SELECT every profile (they already could — profiles
-- are public-read). The important gates are on WRITE paths: the admin
-- UI performs updates via server actions that use the service role,
-- bypassing RLS. No client-side admin writes.

-- Seed Yash as the first superadmin.
update public.profiles
  set admin_role = 'superadmin'
  where lower(username) = 'yashpatil'
    and admin_role is null;

-- Verify assignment for sanity.
do $$
declare v_count int;
begin
  select count(*) into v_count from public.profiles where admin_role is not null;
  raise notice 'admin role holders: %', v_count;
end
$$;


-- ==========================================================================
-- 029_moderation_audit.sql
-- ==========================================================================
-- ExoSynk v0.13.1: immutable admin audit log.
--
-- Every admin action — grant/revoke verification, promote/demote admin
-- roles, moderate content (v0.17+) — writes a row to moderation_actions.
-- The table is INSERT-only: even superadmins cannot UPDATE or DELETE.
-- This is legal/audit cover; future you needs to be able to show the
-- full history if a creator / regulator / court asks.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

create table if not exists public.moderation_actions (
  id          uuid primary key default gen_random_uuid(),
  actor_id    uuid not null references auth.users(id) on delete set null,
  target_kind text not null check (target_kind in (
    'user', 'lab', 'tutorial', 'store_model', 'post',
    'comment', 'reply', 'tutorial_comment'
  )),
  target_id   uuid not null,
  action      text not null check (action in (
    'set_verified_tier', 'set_admin_role',
    'warn', 'suspend', 'unsuspend', 'ban',
    'hide', 'unhide', 'delete', 'mark_safe',
    'dismiss_report'
  )),
  -- Free-form context: old/new values, reason, notes. JSONB so shape
  -- can evolve without migrations. 
  details     jsonb not null default '{}'::jsonb,
  created_at  timestamptz not null default now()
);

create index if not exists moderation_actions_actor_idx
  on public.moderation_actions(actor_id, created_at desc);

create index if not exists moderation_actions_target_idx
  on public.moderation_actions(target_kind, target_id, created_at desc);

alter table public.moderation_actions enable row level security;

-- Admins can SELECT everything. Nobody can UPDATE or DELETE — not even
-- superadmins. The only way to add a row is via server-side inserts
-- after requireAdmin() passes (service role bypasses RLS).
drop policy if exists "mod_log: admins read" on public.moderation_actions;
create policy "mod_log: admins read"
  on public.moderation_actions for select
  using (
    exists(
      select 1 from public.profiles
      where id = auth.uid() and admin_role is not null
    )
  );

-- Admins can insert audit rows, but only with their own actor_id. This
-- keeps the "who did what" accountability honest — nobody can forge an
-- entry under someone else's name. The append-only invariant is
-- enforced by the UPDATE/DELETE triggers below, not by blocking inserts.
drop policy if exists "mod_log: admin writes self-actor" on public.moderation_actions;
create policy "mod_log: admin writes self-actor"
  on public.moderation_actions for insert
  with check (
    actor_id = auth.uid()
    and exists(
      select 1 from public.profiles
      where id = auth.uid() and admin_role is not null
    )
  );

-- Hard-block UPDATE and DELETE at the Postgres level (redundant with
-- RLS, but defense in depth). Even a rogue server-role caller can't
-- modify history without dropping this trigger.
create or replace function public.block_moderation_mutation()
returns trigger language plpgsql as $func$
begin
  raise exception 'moderation_actions is append-only';
end;
$func$;

drop trigger if exists moderation_actions_no_update on public.moderation_actions;
create trigger moderation_actions_no_update
  before update on public.moderation_actions
  for each row execute function public.block_moderation_mutation();

drop trigger if exists moderation_actions_no_delete on public.moderation_actions;
create trigger moderation_actions_no_delete
  before delete on public.moderation_actions
  for each row execute function public.block_moderation_mutation();


-- ==========================================================================
-- 030_moderation_teeth.sql
-- ==========================================================================
-- ExoSynk v0.13.2: moderation teeth.
--
-- Three additions:
--   1) Custom verified label per profile. Black badge is no longer tied
--      to the word "Founder" — every co-founder / shareholder / partner
--      can wear the black tick with their own title.
--   2) Ban fields on profiles (banned_until + banned_reason). Banned
--      users are blocked from creating content by server-side checks.
--   3) banned_words table. Admins curate a list; server-side filters
--      reject any new post / tutorial / comment body that contains one.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

-- =============================================================================
-- 1. Custom badge label + ban fields
-- =============================================================================
alter table public.profiles
  add column if not exists verified_label  text,
  add column if not exists banned_until    timestamptz,
  add column if not exists banned_reason   text;

create index if not exists profiles_banned_idx
  on public.profiles(banned_until)
  where banned_until is not null;

-- =============================================================================
-- 2. banned_words — global content blocklist
-- =============================================================================
create table if not exists public.banned_words (
  id          uuid primary key default gen_random_uuid(),
  word        text not null,
  added_by    uuid references auth.users(id) on delete set null,
  severity    text not null default 'block' check (severity in ('warn', 'block')),
  created_at  timestamptz not null default now()
);

create unique index if not exists banned_words_word_uniq
  on public.banned_words(lower(word));

alter table public.banned_words enable row level security;

-- Anyone can read the blocklist (needed so client-side validators can
-- warn before submit). Only admins can write.
drop policy if exists "banned_words: read all" on public.banned_words;
create policy "banned_words: read all"
  on public.banned_words for select using (true);

drop policy if exists "banned_words: admin writes" on public.banned_words;
create policy "banned_words: admin writes"
  on public.banned_words for insert
  with check (
    exists(
      select 1 from public.profiles
      where id = auth.uid() and admin_role is not null
    )
  );

drop policy if exists "banned_words: admin deletes" on public.banned_words;
create policy "banned_words: admin deletes"
  on public.banned_words for delete
  using (
    exists(
      select 1 from public.profiles
      where id = auth.uid() and admin_role is not null
    )
  );

-- =============================================================================
-- 3. Extend moderation_actions check constraint to cover the new actions.
-- =============================================================================
alter table public.moderation_actions
  drop constraint if exists moderation_actions_action_check;

alter table public.moderation_actions
  add constraint moderation_actions_action_check check (action in (
    'set_verified_tier', 'set_verified_label', 'set_admin_role',
    'warn', 'suspend', 'unsuspend', 'ban', 'unban',
    'hide', 'unhide', 'delete', 'mark_safe', 'dismiss_report',
    'add_banned_word', 'remove_banned_word'
  ));

-- Also extend target_kind to allow "banned_word" audit entries.
alter table public.moderation_actions
  drop constraint if exists moderation_actions_target_kind_check;

alter table public.moderation_actions
  add constraint moderation_actions_target_kind_check check (target_kind in (
    'user', 'lab', 'tutorial', 'store_model', 'post',
    'comment', 'reply', 'tutorial_comment', 'banned_word'
  ));


-- ==========================================================================
-- 031_admin_profile_update_rls.sql
-- ==========================================================================
-- ExoSynk v0.13.2a: let admins update any profile.
--
-- Context: the existing profiles UPDATE policy is "users can update their
-- own profile" (auth.uid() = id). Admin actions like setVerifiedTier,
-- setAdminRole, setVerifiedLabel, banUser, unbanUser update OTHER users'
-- profiles. The session client runs under the admin's JWT, so RLS
-- evaluated auth.uid() = <target user> and silently filtered the write —
-- no error, 0 rows affected, nothing changed.
--
-- This migration adds a parallel UPDATE policy: if the caller's profile
-- row has admin_role IS NOT NULL, they can update any profile. The
-- self-edit policy stays in place so normal users keep editing their
-- own profile as before — PostgreSQL evaluates RLS policies with OR,
-- so a row passes if EITHER policy permits.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

drop policy if exists "profiles: admin writes" on public.profiles;

create policy "profiles: admin writes"
  on public.profiles for update
  using (
    exists(
      select 1 from public.profiles p
      where p.id = auth.uid() and p.admin_role is not null
    )
  )
  with check (
    exists(
      select 1 from public.profiles p
      where p.id = auth.uid() and p.admin_role is not null
    )
  );

-- Also allow admins to DELETE profiles (for full "remove user" in a
-- future admin flow). Self-delete is not supported by Supabase today,
-- and we don't need ordinary users to have that power anyway.
drop policy if exists "profiles: admin deletes" on public.profiles;

create policy "profiles: admin deletes"
  on public.profiles for delete
  using (
    exists(
      select 1 from public.profiles p
      where p.id = auth.uid() and p.admin_role is not null
    )
  );


-- ==========================================================================
-- 032_reports.sql
-- ==========================================================================
-- ExoSynk v0.13.3: user-initiated reports.
--
-- Any signed-in user can report content or a profile; reports land in a
-- queue at /admin/reports where moderators triage them.
--
-- Status lifecycle:
--   open       → freshly filed, awaiting review
--   actioned   → moderator took action (content deleted, author banned, etc.)
--   dismissed  → moderator reviewed and decided no action needed
--
-- A single (reporter, target_kind, target_id) pair is unique — reporting
-- the same thing twice just updates the description / reason instead of
-- flooding the queue.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

create table if not exists public.reports (
  id           uuid primary key default gen_random_uuid(),
  reporter_id  uuid not null references auth.users(id) on delete cascade,
  target_kind  text not null check (target_kind in (
    'user', 'post', 'reply', 'lab', 'tutorial',
    'store_model', 'comment', 'tutorial_comment', 'store_comment'
  )),
  target_id    uuid not null,
  reason       text not null check (reason in (
    'spam', 'harassment', 'nsfw', 'copyright', 'scam', 'other'
  )),
  description  text check (length(description) <= 2000),
  status       text not null default 'open'
               check (status in ('open', 'actioned', 'dismissed')),
  reviewed_by  uuid references auth.users(id) on delete set null,
  reviewed_at  timestamptz,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now(),
  unique (reporter_id, target_kind, target_id)
);

create index if not exists reports_open_idx
  on public.reports(created_at desc)
  where status = 'open';

create index if not exists reports_target_idx
  on public.reports(target_kind, target_id, created_at desc);

alter table public.reports enable row level security;

-- Reporters can see their own reports. Admins can see everything.
drop policy if exists "reports: reporter reads self" on public.reports;
create policy "reports: reporter reads self"
  on public.reports for select
  using (reporter_id = auth.uid());

drop policy if exists "reports: admin reads all" on public.reports;
create policy "reports: admin reads all"
  on public.reports for select
  using (
    exists(
      select 1 from public.profiles
      where id = auth.uid() and admin_role is not null
    )
  );

-- Anyone signed in can create a report; dedupe constraint prevents spam.
drop policy if exists "reports: self insert" on public.reports;
create policy "reports: self insert"
  on public.reports for insert
  with check (reporter_id = auth.uid());

-- Reporters can update their own (open) reports — e.g. add to the
-- description. Admins can update anything (to change status).
drop policy if exists "reports: reporter updates own open" on public.reports;
create policy "reports: reporter updates own open"
  on public.reports for update
  using (reporter_id = auth.uid() and status = 'open')
  with check (reporter_id = auth.uid() and status = 'open');

drop policy if exists "reports: admin updates" on public.reports;
create policy "reports: admin updates"
  on public.reports for update
  using (
    exists(
      select 1 from public.profiles
      where id = auth.uid() and admin_role is not null
    )
  );

-- Extend moderation_actions check constraints to cover the new action
-- types we'll log when admins triage reports.
alter table public.moderation_actions
  drop constraint if exists moderation_actions_action_check;

alter table public.moderation_actions
  add constraint moderation_actions_action_check check (action in (
    'set_verified_tier', 'set_verified_label', 'set_admin_role',
    'warn', 'suspend', 'unsuspend', 'ban', 'unban',
    'hide', 'unhide', 'delete', 'mark_safe',
    'dismiss_report', 'action_report',
    'add_banned_word', 'remove_banned_word'
  ));

alter table public.moderation_actions
  drop constraint if exists moderation_actions_target_kind_check;

alter table public.moderation_actions
  add constraint moderation_actions_target_kind_check check (target_kind in (
    'user', 'lab', 'tutorial', 'store_model', 'post',
    'comment', 'reply', 'tutorial_comment', 'store_comment',
    'banned_word', 'report'
  ));


-- ==========================================================================
-- 033_store_module_ports.sql
-- ==========================================================================
-- ExoSynk v0.14.0: port declarations on store listings.
--
-- A store listing backed by a 2D circuit can declare an external port
-- list — the pins that other builders are meant to connect to when they
-- compose this module into their own circuit. v0.14.1 will add the
-- `submodule` part kind that consumes these ports; this migration is
-- the foundation.
--
-- Shape (store_models.ports JSONB):
--   [
--     { "id": "vcc",     "label": "V+",  "kind": "power",  "part_id": "bat1", "pin_id": "pos" },
--     { "id": "gnd",     "label": "GND", "kind": "ground", "part_id": "bat1", "pin_id": "neg" },
--     { "id": "out",     "label": "OUT", "kind": "signal", "part_id": "led1", "pin_id": "cathode" }
--   ]
--
-- - id       — stable slug, unique per listing (used in future submodule
--              prop mapping + URL fragments).
-- - label    — display text shown on the module's outside pin.
-- - kind     — visual hint: "power" / "ground" / "signal" / "digital" /
--              "analog". Drives pin coloring on the submodule render.
-- - part_id / pin_id — which pin in the inner circuit this external
--              port maps to. Stored as strings (same IDs as circuit.parts).
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.store_models
  add column if not exists ports jsonb not null default '[]'::jsonb;

-- Quick sanity query: how many listings have declared ports?
-- select id, title, jsonb_array_length(ports) as n
--   from public.store_models where jsonb_array_length(ports) > 0;


-- ==========================================================================
-- 034_creator_economy.sql
-- ==========================================================================
-- ExoSynk v0.15.0: creator economy groundwork.
--
-- No actual charging yet (v0.20 wires Razorpay/Stripe). This migration
-- lays the schema so listings can declare:
--   • price_cents + currency — what they'd cost if paid today
--   • royalty_splits        — who gets paid when this sells
--   • is_paid_wanted        — author opted into paid tier (intent)
--
-- Also adds a creator Pro-tier signup intent table so we can measure
-- willingness-to-pay before turning on real billing.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

-- =============================================================================
-- store_models: pricing + royalty splits
-- =============================================================================
-- price_cents was nullable already (NULL = free). Keep that; add currency.
alter table public.store_models
  add column if not exists currency text not null default 'INR'
    check (currency in ('INR', 'USD', 'EUR', 'GBP')),
  -- When true, author intends to charge as soon as payments go live.
  -- Until then the listing still says "Free" publicly; this flag just
  -- surfaces on their earnings page.
  add column if not exists is_paid_wanted boolean not null default false,
  -- Royalty splits. Shape: [{ recipient_id: uuid, bps: int, note: text }]
  -- bps = basis points (1/100 of a percent). Sum across all splits must
  -- be ≤ 10000 (100%) minus platform fee. Unenforced at DB layer; app
  -- does validation before write.
  add column if not exists royalty_splits jsonb not null default '[]'::jsonb;

-- =============================================================================
-- pro_signups: willingness-to-pay measurement
-- =============================================================================
-- A user clicks "Notify me when Pro launches" — we record the click +
-- optional email (if not signed in) + timestamp. Pure intent capture,
-- no charging, no obligation.
create table if not exists public.pro_signups (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid references auth.users(id) on delete cascade,
  email           text,
  desired_tier    text not null default 'pro' check (desired_tier in ('pro', 'studio')),
  price_hint      int,  -- cents, what they'd pay if asked "what's fair?"
  source          text, -- which page/banner they clicked from
  created_at      timestamptz not null default now(),
  -- Dedupe by user_id (signed-in) OR email (anon).
  unique (user_id, desired_tier)
);

create unique index if not exists pro_signups_email_uniq
  on public.pro_signups(lower(email), desired_tier)
  where email is not null;

create index if not exists pro_signups_recent_idx
  on public.pro_signups(created_at desc);

alter table public.pro_signups enable row level security;

drop policy if exists "pro_signups: self read" on public.pro_signups;
create policy "pro_signups: self read"
  on public.pro_signups for select
  using (user_id = auth.uid());

drop policy if exists "pro_signups: admin read" on public.pro_signups;
create policy "pro_signups: admin read"
  on public.pro_signups for select
  using (
    exists(select 1 from public.profiles where id = auth.uid() and admin_role is not null)
  );

drop policy if exists "pro_signups: anon insert" on public.pro_signups;
create policy "pro_signups: anon insert"
  on public.pro_signups for insert
  with check (
    -- Either you're signing in yourself, or you're an anon giving email
    (user_id = auth.uid())
    or (user_id is null and email is not null)
  );


-- ==========================================================================
-- 035_comment_threading.sql
-- ==========================================================================
-- v0.21 — comment threading (Reddit-style replies)
--
-- Adds a parent_id self-reference to every comment table so a comment
-- can reply to another comment. parent_id is nullable — a null value
-- means the comment is a top-level thread, otherwise it's a reply.
--
-- Also adds a lightweight index per table so nested-tree lookups stay
-- cheap on big threads.

-- Lab comments.
alter table public.comments
  add column if not exists parent_id uuid
    references public.comments(id) on delete cascade;
create index if not exists comments_parent_idx on public.comments(parent_id);
create index if not exists comments_lab_created_idx on public.comments(lab_id, created_at);

-- Tutorial comments.
alter table public.tutorial_comments
  add column if not exists parent_id uuid
    references public.tutorial_comments(id) on delete cascade;
create index if not exists tutorial_comments_parent_idx on public.tutorial_comments(parent_id);
create index if not exists tutorial_comments_tutorial_created_idx on public.tutorial_comments(tutorial_id, created_at);

-- Store comments. (Table is store_model_comments, not store_comments.)
alter table public.store_model_comments
  add column if not exists parent_id uuid
    references public.store_model_comments(id) on delete cascade;
create index if not exists store_model_comments_parent_idx on public.store_model_comments(parent_id);
create index if not exists store_model_comments_model_created_idx on public.store_model_comments(store_model_id, created_at);


-- ==========================================================================
-- 036_post_reply_threading.sql
-- ==========================================================================
-- v0.22 — Signal (post_replies) threading
--
-- Mirrors migration 035 for labs/tutorials/store comments: adds a
-- nullable self-reference parent_id so a reply can reply to another
-- reply. Null = top-level reply to the post itself.

alter table public.post_replies
  add column if not exists parent_id uuid
    references public.post_replies(id) on delete cascade;

create index if not exists post_replies_parent_idx on public.post_replies(parent_id);
create index if not exists post_replies_post_created_idx on public.post_replies(post_id, created_at);


-- ==========================================================================
-- 037_profile_social_links.sql
-- ==========================================================================
-- v0.23 — Profile social links
--
-- Users can now point at their other profiles from ExoSynk. Stored as
-- discrete columns (not jsonb) so we can index, search, and display
-- each link without parsing.
--
-- Every field is nullable. URL validation happens at write time on the
-- server (see updateMyProfile); the DB only enforces length + scheme
-- prefix as a cheap safety net.

alter table public.profiles
  add column if not exists github_url text,
  add column if not exists twitter_url text,
  add column if not exists linkedin_url text,
  add column if not exists youtube_url text,
  add column if not exists website_url text,
  add column if not exists location text;

-- Cheap schema-level guardrails — block obviously wrong data.
-- Application layer does proper URL parsing + host allow-listing.
alter table public.profiles
  drop constraint if exists profiles_github_url_check,
  drop constraint if exists profiles_twitter_url_check,
  drop constraint if exists profiles_linkedin_url_check,
  drop constraint if exists profiles_youtube_url_check,
  drop constraint if exists profiles_website_url_check;

alter table public.profiles
  add constraint profiles_github_url_check
    check (github_url is null or (length(github_url) between 8 and 256 and github_url ~ '^https?://')),
  add constraint profiles_twitter_url_check
    check (twitter_url is null or (length(twitter_url) between 8 and 256 and twitter_url ~ '^https?://')),
  add constraint profiles_linkedin_url_check
    check (linkedin_url is null or (length(linkedin_url) between 8 and 256 and linkedin_url ~ '^https?://')),
  add constraint profiles_youtube_url_check
    check (youtube_url is null or (length(youtube_url) between 8 and 256 and youtube_url ~ '^https?://')),
  add constraint profiles_website_url_check
    check (website_url is null or (length(website_url) between 8 and 256 and website_url ~ '^https?://'));


-- ==========================================================================
-- 038_coin_wallet.sql
-- ==========================================================================
-- v0.24 — Coin wallet + fiat edges (Roblox Robux / Twitch Bits model)
--
-- Design:
--  - 1 Coin has a fixed INTERNAL base value of 10 USD cents ($0.10).
--    Accounting is always in Coins (int). No decimals anywhere.
--  - BETA launch mode: every new user auto-receives a 100-Coin
--    welcome gift so they can spend / review the economy. Real
--    Razorpay/Stripe flows are scaffolded but locked behind
--    "coming soon" until the business registration + merchant
--    account are live.
--  - Users will buy Coins in FIXED PACKS priced in local currency
--    (₹99 = 100 Coins, ₹499 = 550 Coins with a bonus, etc). The pack
--    defines the exchange rate for that purchase.
--  - Creators will withdraw Coins → local fiat at a SEPARATE withdraw
--    rate per currency from coin_rates. The difference between buy
--    and withdraw rates = platform spread = revenue on the money loop.
--  - Actual fiat movement is ALWAYS handled by a licensed payout
--    partner — Razorpay Route (India) or Stripe Connect (global).
--    ExoSynk stores only references to the partner's accounts and
--    webhook-confirmed transaction IDs.
--
-- Tables shipped here:
--   wallets             — per-user Coin balance
--   coin_ledger         — append-only log of every Coin motion
--   coin_rates          — withdraw rate per currency (updatable)
--   coin_packs          — fixed Coin packs (coming-soon in UI)
--   coin_purchases      — one row per buy, locks rate + ref
--   payout_accounts     — creator's connected Razorpay/Stripe account
--   payout_requests     — creator's request to cash out

-- ------------------- wallets -------------------

create table if not exists public.wallets (
  user_id uuid primary key references auth.users(id) on delete cascade,
  balance_coins bigint not null default 0 check (balance_coins >= 0),
  lifetime_earned_coins bigint not null default 0 check (lifetime_earned_coins >= 0),
  lifetime_spent_coins  bigint not null default 0 check (lifetime_spent_coins  >= 0),
  /** Amount currently held against an open payout request. balance_coins
   *  excludes this; effective available = balance_coins. Held coins are
   *  debited on payout success / re-credited on rejection. */
  held_coins bigint not null default 0 check (held_coins >= 0),
  updated_at timestamptz not null default now()
);

alter table public.wallets enable row level security;

drop policy if exists "wallets: self read" on public.wallets;
create policy "wallets: self read"
  on public.wallets for select using (auth.uid() = user_id);

drop policy if exists "wallets: admin read" on public.wallets;
create policy "wallets: admin read"
  on public.wallets for select using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- ------------------- coin_ledger -------------------

-- Every Coin motion. Append-only; balance_after is cached for quick
-- auditing. Mutation happens only through credit_coins / debit_coins
-- RPCs below (SECURITY DEFINER).

create table if not exists public.coin_ledger (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  amount_coins bigint not null,
  reason text not null check (reason in (
    'welcome_gift',      -- 100 Coins on first profile creation (beta)
    'admin_gift',        -- founder / admin manually credits a user
    'purchase_credit',
    'spend_store',
    'spend_tip',
    'spend_tutorial',
    'earn_store',
    'earn_tip',
    'earn_tutorial',
    'admin_adjustment',
    'payout_hold',
    'payout_released',
    'payout_refunded'
  )),
  balance_after bigint not null check (balance_after >= 0),
  /** Machine-readable extra context: {purchase_id, source_user_id,
   *  store_model_id, payout_request_id, payment_ref, ...} */
  meta jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists coin_ledger_user_idx on public.coin_ledger(user_id, created_at desc);

alter table public.coin_ledger enable row level security;

drop policy if exists "coin_ledger: self read" on public.coin_ledger;
create policy "coin_ledger: self read"
  on public.coin_ledger for select using (auth.uid() = user_id);

drop policy if exists "coin_ledger: admin read" on public.coin_ledger;
create policy "coin_ledger: admin read"
  on public.coin_ledger for select using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- ------------------- credit / debit RPCs -------------------

create or replace function public.credit_coins(
  p_user_id uuid,
  p_amount  bigint,
  p_reason  text,
  p_meta    jsonb default '{}'::jsonb
)
returns bigint
language plpgsql
security definer
set search_path = public
as $$
declare
  v_balance bigint;
begin
  if p_amount <= 0 then
    raise exception 'Credit amount must be positive (got %)', p_amount;
  end if;
  insert into public.wallets (user_id, balance_coins, lifetime_earned_coins)
    values (p_user_id, p_amount, p_amount)
  on conflict (user_id) do update set
    balance_coins = public.wallets.balance_coins + excluded.balance_coins,
    lifetime_earned_coins = public.wallets.lifetime_earned_coins + excluded.lifetime_earned_coins,
    updated_at = now()
  returning balance_coins into v_balance;

  insert into public.coin_ledger (user_id, amount_coins, reason, balance_after, meta)
    values (p_user_id, p_amount, p_reason, v_balance, p_meta);
  return v_balance;
end
$$;

create or replace function public.debit_coins(
  p_user_id uuid,
  p_amount  bigint,
  p_reason  text,
  p_meta    jsonb default '{}'::jsonb
)
returns bigint
language plpgsql
security definer
set search_path = public
as $$
declare
  v_balance bigint;
begin
  if p_amount <= 0 then
    raise exception 'Debit amount must be positive (got %)', p_amount;
  end if;
  update public.wallets
    set balance_coins = balance_coins - p_amount,
        lifetime_spent_coins = lifetime_spent_coins + p_amount,
        updated_at = now()
    where user_id = p_user_id and balance_coins >= p_amount
    returning balance_coins into v_balance;

  if v_balance is null then
    raise exception 'Insufficient Coin balance' using errcode = 'check_violation';
  end if;

  insert into public.coin_ledger (user_id, amount_coins, reason, balance_after, meta)
    values (p_user_id, -p_amount, p_reason, v_balance, p_meta);
  return v_balance;
end
$$;

-- ------------------- coin_rates -------------------

-- Per-currency buy (platform's sell rate to user) and withdraw (rate
-- we pay creators). Buy rate sanity-checked against the pack rate at
-- insert time; withdraw rate is the one authoritative cash-out rate
-- for new payout requests. Admins update this weekly or when FX
-- moves. History lives in coin_rates_history (auto, via trigger).

create table if not exists public.coin_rates (
  currency text primary key check (currency in ('INR','USD','EUR','GBP')),
  buy_minor_per_coin  int not null check (buy_minor_per_coin  > 0),
  withdraw_minor_per_coin int not null check (withdraw_minor_per_coin > 0),
  updated_at timestamptz not null default now(),
  updated_by uuid references auth.users(id)
);

-- Initial baseline rates at 1 Coin = $0.10 on buy, $0.08 on withdraw
-- (20% spread — tune later). paise for INR, cents for others.
-- ₹8.50 per Coin buy ≈ $0.10 at ~85 INR/USD; ₹6.80 per Coin withdraw.
insert into public.coin_rates (currency, buy_minor_per_coin, withdraw_minor_per_coin) values
  ('INR', 850, 680),
  ('USD',  10,   8),
  ('EUR',  10,   8),
  ('GBP',   8,   7)
on conflict (currency) do nothing;

alter table public.coin_rates enable row level security;

drop policy if exists "coin_rates: public read" on public.coin_rates;
create policy "coin_rates: public read"
  on public.coin_rates for select using (true);

drop policy if exists "coin_rates: admin write" on public.coin_rates;
create policy "coin_rates: admin write"
  on public.coin_rates for all using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  )) with check (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- ------------------- coin_packs -------------------

-- Fixed Coin packs users can buy. Each pack has a price in local-
-- currency minor units and a Coin quantity (including bonus). The
-- pack table drives the /wallet buy UI and the checkout page.

create table if not exists public.coin_packs (
  id text primary key,
  currency text not null check (currency in ('INR','USD','EUR','GBP')),
  price_minor int not null check (price_minor > 0),
  coins int not null check (coins > 0),
  bonus_coins int not null default 0 check (bonus_coins >= 0),
  is_active boolean not null default true,
  display_order int not null default 0,
  note text,
  created_at timestamptz not null default now()
);

-- Seed a starter set. Bonus coins reward larger purchases and are the
-- visible "value" to users. These prices map to ~10 Coins = $1 base
-- but bake in processor fees + a small margin for the platform.
insert into public.coin_packs (id, currency, price_minor, coins, bonus_coins, display_order, note) values
  ('inr_99_starter',   'INR',   9900,  100,   0, 1, 'Starter pack'),
  ('inr_499_saver',    'INR',  49900,  500,  50, 2, '10% bonus'),
  ('inr_999_pro',      'INR',  99900, 1000, 150, 3, '15% bonus'),
  ('inr_2499_studio',  'INR', 249900, 2500, 500, 4, '20% bonus'),
  ('usd_1_starter',    'USD',    100,   10,   0, 1, 'Starter pack'),
  ('usd_5_saver',      'USD',    500,   50,   5, 2, '10% bonus'),
  ('usd_10_pro',       'USD',   1000,  100,  15, 3, '15% bonus'),
  ('usd_25_studio',    'USD',   2500,  250,  50, 4, '20% bonus')
on conflict (id) do nothing;

alter table public.coin_packs enable row level security;

drop policy if exists "coin_packs: public read" on public.coin_packs;
create policy "coin_packs: public read"
  on public.coin_packs for select using (is_active = true);

drop policy if exists "coin_packs: admin all" on public.coin_packs;
create policy "coin_packs: admin all"
  on public.coin_packs for all using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  )) with check (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- ------------------- coin_purchases -------------------

-- One row per purchase. Created when checkout initiates, status
-- flipped by the payment partner's webhook. Coins are credited on
-- status='paid' via credit_coins RPC. rate_minor_per_coin is locked
-- here so history is immutable even if coin_rates changes later.

create table if not exists public.coin_purchases (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  pack_id text not null references public.coin_packs(id),
  currency text not null,
  price_minor int not null,
  coins_granted int not null,
  rate_minor_per_coin int not null,
  provider text not null check (provider in ('razorpay','stripe','test_stub')),
  provider_ref text,
  status text not null default 'pending' check (status in ('pending','paid','failed','refunded')),
  paid_at timestamptz,
  created_at timestamptz not null default now()
);

create index if not exists coin_purchases_user_idx on public.coin_purchases(user_id, created_at desc);
create index if not exists coin_purchases_provider_ref_idx on public.coin_purchases(provider, provider_ref);

alter table public.coin_purchases enable row level security;

drop policy if exists "coin_purchases: self read" on public.coin_purchases;
create policy "coin_purchases: self read"
  on public.coin_purchases for select using (auth.uid() = user_id);

drop policy if exists "coin_purchases: admin read" on public.coin_purchases;
create policy "coin_purchases: admin read"
  on public.coin_purchases for select using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- Writes come from server actions + webhooks (server runs with service
-- role key, bypasses RLS).

-- ------------------- payout_accounts -------------------

-- A creator's connected Razorpay Route / Stripe Connect account id.
-- The real KYC + bank details live on the payment partner. We only
-- store the reference so payouts.create() knows where to send.

create table if not exists public.payout_accounts (
  user_id uuid primary key references auth.users(id) on delete cascade,
  provider text not null check (provider in ('razorpay_route','stripe_connect')),
  external_account_id text not null,
  country text not null check (length(country) = 2),
  status text not null default 'pending' check (status in ('pending','active','restricted','disabled')),
  /** When payouts are blocked — missing info, failed KYC, etc. */
  last_requirement text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table public.payout_accounts enable row level security;

drop policy if exists "payout_accounts: self read" on public.payout_accounts;
create policy "payout_accounts: self read"
  on public.payout_accounts for select using (auth.uid() = user_id);

drop policy if exists "payout_accounts: admin all" on public.payout_accounts;
create policy "payout_accounts: admin all"
  on public.payout_accounts for all using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  )) with check (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- ------------------- payout_requests -------------------

create table if not exists public.payout_requests (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  coins_requested bigint not null check (coins_requested >= 100),
  target_currency text not null check (target_currency in ('INR','USD','EUR','GBP')),
  /** Rate in effect when the request was filed. Locked for history;
   *  admin / webhook uses this to compute the fiat amount. */
  rate_minor_per_coin int not null check (rate_minor_per_coin > 0),
  target_amount_minor int not null check (target_amount_minor >= 0),
  provider text check (provider in ('razorpay_route','stripe_connect','manual')),
  provider_payout_id text,
  status text not null default 'pending' check (status in ('pending','approved','processing','paid','rejected','cancelled')),
  admin_note text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists payout_requests_user_idx on public.payout_requests(user_id, created_at desc);
create index if not exists payout_requests_status_idx on public.payout_requests(status, created_at);

alter table public.payout_requests enable row level security;

drop policy if exists "payout_requests: self read" on public.payout_requests;
create policy "payout_requests: self read"
  on public.payout_requests for select using (auth.uid() = user_id);

drop policy if exists "payout_requests: self insert" on public.payout_requests;
create policy "payout_requests: self insert"
  on public.payout_requests for insert
  with check (auth.uid() = user_id and status = 'pending');

drop policy if exists "payout_requests: self cancel" on public.payout_requests;
create policy "payout_requests: self cancel"
  on public.payout_requests for update
  using (auth.uid() = user_id and status = 'pending')
  with check (auth.uid() = user_id and status in ('pending','cancelled'));

drop policy if exists "payout_requests: admin read" on public.payout_requests;
create policy "payout_requests: admin read"
  on public.payout_requests for select using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

drop policy if exists "payout_requests: admin update" on public.payout_requests;
create policy "payout_requests: admin update"
  on public.payout_requests for update using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- ------------------- welcome gift (beta) -------------------
--
-- Every profile gets a 100-Coin welcome gift so users can spend +
-- exercise the economy while real payments aren't live yet. Fires
-- once per profile via AFTER INSERT. Backfill runs once for existing
-- profiles at migration time.

create or replace function public.grant_welcome_coins()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  perform public.credit_coins(
    new.id,
    100,
    'welcome_gift',
    jsonb_build_object('source', 'profile_insert_trigger')
  );
  return new;
exception when others then
  -- Don't break profile insertion if the grant fails — log and move on.
  raise warning 'welcome gift skipped for %: %', new.id, sqlerrm;
  return new;
end
$$;

drop trigger if exists profiles_welcome_coins on public.profiles;
create trigger profiles_welcome_coins
  after insert on public.profiles
  for each row execute function public.grant_welcome_coins();

-- Backfill: every existing profile that doesn't yet have a wallet row
-- gets the same 100-Coin welcome gift. Idempotent — the ledger check
-- prevents double-gifting if this migration re-runs.
do $$
declare
  v_profile record;
begin
  for v_profile in
    select p.id
    from public.profiles p
    where not exists (
      select 1 from public.coin_ledger l
      where l.user_id = p.id and l.reason = 'welcome_gift'
    )
  loop
    begin
      perform public.credit_coins(v_profile.id, 100, 'welcome_gift',
        jsonb_build_object('source', 'backfill_migration_038'));
    exception when others then
      raise warning 'backfill welcome gift skipped for %: %', v_profile.id, sqlerrm;
    end;
  end loop;
end
$$;

-- ------------------- admin gifting -------------------
--
-- Founder / admin can gift Coins to any user via this RPC. Every
-- gift lands in coin_ledger with reason='admin_gift' and the admin's
-- user id baked into meta so everything stays tracked.

create or replace function public.admin_gift_coins(
  p_user_id uuid,
  p_amount  bigint,
  p_note    text default null
)
returns bigint
language plpgsql
security definer
set search_path = public
as $$
declare
  v_is_admin boolean;
  v_balance  bigint;
begin
  select exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ) into v_is_admin;

  if not v_is_admin then
    raise exception 'Only admins can gift Coins';
  end if;
  if p_amount <= 0 then
    raise exception 'Gift amount must be positive (got %)', p_amount;
  end if;
  if p_amount > 1000000 then
    raise exception 'Gift cap is 1,000,000 Coins per transaction';
  end if;

  select public.credit_coins(
    p_user_id,
    p_amount,
    'admin_gift',
    jsonb_build_object(
      'admin_id', auth.uid(),
      'note',     p_note
    )
  ) into v_balance;

  return v_balance;
end
$$;


-- ==========================================================================
-- 039_coin_economy.sql
-- ==========================================================================
-- v0.25 — Full Coin economy: paid listings, paid tutorials,
-- entitlements, platform-fee split, subscriptions, lab time budgets.
--
-- 1. Every sellable thing (store_model, tutorial) gets a price in Coins.
-- 2. purchase_coin_transactions records each buy, recipient, fee.
-- 3. entitlements track who bought what (so re-visit is free).
-- 4. subscriptions table holds monthly Coin-burn tiers.
-- 5. lab_usage_days track per-user per-day lab minutes for enforcement.

-- ---------- 1. paid listings ----------

alter table public.store_models
  add column if not exists price_coins int
    check (price_coins is null or (price_coins >= 0 and price_coins <= 100000)),
  add column if not exists total_sales bigint not null default 0,
  -- v0.25 attribution — creator can declare that a % of sales goes
  -- upstream to another creator whose paid module they built on.
  add column if not exists attribution_user_id uuid references auth.users(id) on delete set null,
  add column if not exists attribution_bps int not null default 0
    check (attribution_bps >= 0 and attribution_bps <= 5000);

alter table public.tutorials
  add column if not exists price_coins int
    check (price_coins is null or (price_coins >= 0 and price_coins <= 100000)),
  add column if not exists total_sales bigint not null default 0;

-- ---------- 2. on-platform purchases ----------
-- One row per Coin-funded purchase of a Store model or tutorial. Ties
-- together the buyer, seller, the thing bought, the platform fee, and
-- the ledger entries created by the purchase RPC.

create table if not exists public.coin_purchases_onplatform (
  id uuid primary key default gen_random_uuid(),
  buyer_id uuid not null references auth.users(id) on delete cascade,
  seller_id uuid not null references auth.users(id) on delete cascade,
  subject_kind text not null check (subject_kind in ('store_model','tutorial','tip')),
  subject_id uuid, -- null for tips; set for store/tutorial
  price_coins int not null check (price_coins >= 0),
  platform_fee_coins int not null check (platform_fee_coins >= 0),
  seller_net_coins int not null check (seller_net_coins >= 0),
  created_at timestamptz not null default now()
);

create index if not exists coin_purchases_onplatform_buyer_idx on public.coin_purchases_onplatform(buyer_id, created_at desc);
create index if not exists coin_purchases_onplatform_seller_idx on public.coin_purchases_onplatform(seller_id, created_at desc);
create index if not exists coin_purchases_onplatform_subject_idx on public.coin_purchases_onplatform(subject_kind, subject_id);

alter table public.coin_purchases_onplatform enable row level security;

drop policy if exists "coin_purchases_onplatform: self read" on public.coin_purchases_onplatform;
create policy "coin_purchases_onplatform: self read"
  on public.coin_purchases_onplatform for select
  using (auth.uid() = buyer_id or auth.uid() = seller_id);

drop policy if exists "coin_purchases_onplatform: admin read" on public.coin_purchases_onplatform;
create policy "coin_purchases_onplatform: admin read"
  on public.coin_purchases_onplatform for select
  using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- ---------- 3. entitlements ----------
-- After a successful purchase we grant the buyer an entitlement row.
-- Owned lookups join against this so re-visits are free. Scope one
-- row per (user, subject). Free items are NOT tracked here — only
-- paid purchases create entitlements.

create table if not exists public.entitlements (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  subject_kind text not null check (subject_kind in ('store_model','tutorial')),
  subject_id uuid not null,
  purchase_id uuid references public.coin_purchases_onplatform(id) on delete set null,
  granted_at timestamptz not null default now(),
  unique (user_id, subject_kind, subject_id)
);

create index if not exists entitlements_user_idx on public.entitlements(user_id, subject_kind);

alter table public.entitlements enable row level security;

drop policy if exists "entitlements: self read" on public.entitlements;
create policy "entitlements: self read"
  on public.entitlements for select
  using (auth.uid() = user_id);

-- ---------- 4. purchase RPC (atomic buy) ----------
-- Runs inside one transaction:
--   a. Checks buyer has enough Coins.
--   b. Debits buyer; credits seller net; records platform fee as
--      a system-owned delta (we keep it implicit — platform fee is
--      simply the amount NOT credited to the seller, stays in the
--      global Coin total and tracked separately in the purchase row).
--   c. Inserts coin_purchases_onplatform row.
--   d. Inserts entitlements row.
--   e. Bumps total_sales on the subject.
-- 10% platform fee hard-coded; change via PLATFORM_FEE_BPS.

create or replace function public.purchase_coin_subject(
  p_subject_kind text,
  p_subject_id   uuid,
  p_seller_id    uuid,
  p_price_coins  int
)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_buyer_id uuid := auth.uid();
  v_fee_bps int := 1000; -- 10% platform
  v_fee_coins int;
  v_attr_user uuid;
  v_attr_bps int := 0;
  v_attr_coins int := 0;
  v_seller_coins int;
  v_purchase_id uuid;
  v_existing uuid;
begin
  if v_buyer_id is null then
    raise exception 'Not signed in';
  end if;
  if v_buyer_id = p_seller_id then
    raise exception 'You can''t buy your own listing';
  end if;
  if p_subject_kind not in ('store_model','tutorial') then
    raise exception 'Unsupported subject kind: %', p_subject_kind;
  end if;
  if p_price_coins is null or p_price_coins <= 0 then
    raise exception 'Price must be > 0';
  end if;

  -- Already owned? Return existing purchase id (idempotent).
  select ent.purchase_id into v_existing
    from public.entitlements ent
    where ent.user_id = v_buyer_id
      and ent.subject_kind = p_subject_kind
      and ent.subject_id = p_subject_id;
  if v_existing is not null then
    return v_existing;
  end if;

  -- Attribution kickback (store_model only). If the listing declares
  -- an attribution_user_id + bps, a slice goes to the upstream creator.
  if p_subject_kind = 'store_model' then
    select attribution_user_id, attribution_bps
      into v_attr_user, v_attr_bps
      from public.store_models where id = p_subject_id;
    if v_attr_user is null or v_attr_user = p_seller_id or v_attr_user = v_buyer_id then
      v_attr_bps := 0;
      v_attr_user := null;
    end if;
  end if;

  v_fee_coins := floor((p_price_coins * v_fee_bps) / 10000.0);
  v_attr_coins := floor((p_price_coins * v_attr_bps) / 10000.0);
  v_seller_coins := p_price_coins - v_fee_coins - v_attr_coins;
  if v_seller_coins < 0 then
    raise exception 'Split math produced a negative seller share';
  end if;

  -- Debit buyer once.
  perform public.debit_coins(
    v_buyer_id,
    p_price_coins,
    case p_subject_kind when 'store_model' then 'spend_store' else 'spend_tutorial' end,
    jsonb_build_object(
      'subject_id', p_subject_id,
      'seller_id', p_seller_id,
      'platform_fee_coins', v_fee_coins,
      'attribution_user_id', v_attr_user,
      'attribution_coins', v_attr_coins
    )
  );

  -- Credit seller net.
  perform public.credit_coins(
    p_seller_id,
    v_seller_coins,
    case p_subject_kind when 'store_model' then 'earn_store' else 'earn_tutorial' end,
    jsonb_build_object(
      'subject_id', p_subject_id,
      'buyer_id', v_buyer_id,
      'platform_fee_coins', v_fee_coins,
      'attribution_coins', v_attr_coins,
      'attribution_user_id', v_attr_user
    )
  );

  -- Credit upstream attribution creator if any.
  if v_attr_user is not null and v_attr_coins > 0 then
    perform public.credit_coins(
      v_attr_user,
      v_attr_coins,
      'earn_store',
      jsonb_build_object(
        'subject_id', p_subject_id,
        'buyer_id', v_buyer_id,
        'origin_seller_id', p_seller_id,
        'attribution_kickback', true
      )
    );
  end if;

  insert into public.coin_purchases_onplatform
    (buyer_id, seller_id, subject_kind, subject_id, price_coins, platform_fee_coins, seller_net_coins)
    values (v_buyer_id, p_seller_id, p_subject_kind, p_subject_id, p_price_coins, v_fee_coins + v_attr_coins, v_seller_coins)
    returning id into v_purchase_id;

  insert into public.entitlements (user_id, subject_kind, subject_id, purchase_id)
    values (v_buyer_id, p_subject_kind, p_subject_id, v_purchase_id);

  if p_subject_kind = 'store_model' then
    update public.store_models set total_sales = total_sales + 1 where id = p_subject_id;
  else
    update public.tutorials set total_sales = total_sales + 1 where id = p_subject_id;
  end if;

  return v_purchase_id;
end
$$;

-- ---------- 5. tips ----------
-- Quick tip flow — no entitlement, just a transfer. Uses the same
-- ledger pattern.

create or replace function public.tip_user(
  p_recipient_id uuid,
  p_amount_coins int,
  p_note text default null
)
returns bigint
language plpgsql
security definer
set search_path = public
as $$
declare
  v_sender uuid := auth.uid();
begin
  if v_sender is null then raise exception 'Not signed in'; end if;
  if v_sender = p_recipient_id then raise exception 'You can''t tip yourself'; end if;
  if p_amount_coins is null or p_amount_coins <= 0 then raise exception 'Tip must be > 0'; end if;
  if p_amount_coins > 100000 then raise exception 'Single tip cap is 100,000 Coins'; end if;

  perform public.debit_coins(v_sender, p_amount_coins, 'spend_tip',
    jsonb_build_object('recipient_id', p_recipient_id, 'note', p_note));
  perform public.credit_coins(p_recipient_id, p_amount_coins, 'earn_tip',
    jsonb_build_object('sender_id', v_sender, 'note', p_note));

  insert into public.coin_purchases_onplatform
    (buyer_id, seller_id, subject_kind, price_coins, platform_fee_coins, seller_net_coins)
    values (v_sender, p_recipient_id, 'tip', p_amount_coins, 0, p_amount_coins);

  return p_amount_coins;
end
$$;

-- ---------- 6. subscriptions ----------

create table if not exists public.subscription_tiers (
  id text primary key,
  name text not null,
  description text,
  monthly_price_coins int not null check (monthly_price_coins >= 0),
  lab_hours_per_month int not null check (lab_hours_per_month > 0),
  -- Also gates "how many labs you can have" and "how much storage".
  max_labs int not null check (max_labs > 0),
  storage_gb int not null check (storage_gb > 0),
  highlight boolean not null default false,
  display_order int not null default 0,
  is_active boolean not null default true
);

insert into public.subscription_tiers (id, name, description, monthly_price_coins, lab_hours_per_month, max_labs, storage_gb, highlight, display_order) values
  ('free',   'Free',   'Great for students and casual tinkering.',          0,  20,   5,  1, false, 1),
  ('pro',    'Pro',    'Serious creators shipping real designs.',         500, 200,  50, 10, true,  2),
  ('studio', 'Studio', 'Teams and hardware startups.',                   2000, 2000, 500, 100, false, 3)
on conflict (id) do nothing;

alter table public.subscription_tiers enable row level security;

drop policy if exists "subscription_tiers: public read" on public.subscription_tiers;
create policy "subscription_tiers: public read"
  on public.subscription_tiers for select using (is_active);

-- User's currently active subscription. One row per user (or none =
-- free tier). period_end is when we attempt to auto-renew (debit
-- Coins, bump period). No renewal happens automatically in this
-- migration; a scheduled job calls renew_subscription() periodically.

create table if not exists public.user_subscriptions (
  user_id uuid primary key references auth.users(id) on delete cascade,
  tier_id text not null references public.subscription_tiers(id),
  status text not null default 'active' check (status in ('active','past_due','cancelled')),
  period_start timestamptz not null default now(),
  period_end   timestamptz not null default (now() + interval '30 days'),
  auto_renew boolean not null default true,
  last_renewed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table public.user_subscriptions enable row level security;

drop policy if exists "user_subscriptions: self read" on public.user_subscriptions;
create policy "user_subscriptions: self read"
  on public.user_subscriptions for select using (auth.uid() = user_id);

drop policy if exists "user_subscriptions: admin read" on public.user_subscriptions;
create policy "user_subscriptions: admin read"
  on public.user_subscriptions for select using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- Subscribe / upgrade. Changes tier immediately; if the new tier is
-- paid and the user is not already paying for it this period, we
-- debit the monthly price.
create or replace function public.subscribe_to_tier(p_tier_id text)
returns text
language plpgsql
security definer
set search_path = public
as $$
declare
  v_user uuid := auth.uid();
  v_tier record;
  v_existing record;
begin
  if v_user is null then raise exception 'Not signed in'; end if;
  select * into v_tier from public.subscription_tiers where id = p_tier_id and is_active;
  if v_tier is null then raise exception 'Unknown or inactive tier: %', p_tier_id; end if;

  select * into v_existing from public.user_subscriptions where user_id = v_user;

  if v_existing.tier_id = p_tier_id and v_existing.status = 'active' then
    -- Already on this tier — no-op.
    return 'noop';
  end if;

  if v_tier.monthly_price_coins > 0 then
    perform public.debit_coins(v_user, v_tier.monthly_price_coins, 'admin_adjustment',
      jsonb_build_object('kind', 'subscribe', 'tier', p_tier_id));
  end if;

  if v_existing.user_id is null then
    insert into public.user_subscriptions (user_id, tier_id, period_start, period_end)
      values (v_user, p_tier_id, now(), now() + interval '30 days');
  else
    update public.user_subscriptions set
      tier_id = p_tier_id,
      status = 'active',
      period_start = now(),
      period_end = now() + interval '30 days',
      last_renewed_at = now(),
      updated_at = now()
    where user_id = v_user;
  end if;

  return 'ok';
end
$$;

-- Cancel: mark the row cancelled so auto-renew stops; tier stays
-- active until period_end naturally expires.
create or replace function public.cancel_subscription()
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  v_user uuid := auth.uid();
begin
  if v_user is null then raise exception 'Not signed in'; end if;
  update public.user_subscriptions set
    auto_renew = false,
    status = 'cancelled',
    updated_at = now()
  where user_id = v_user;
end
$$;

-- ---------- 7. lab usage tracking ----------
-- Per-user per-day minute budget. Enforced at lab-load time by
-- reading the current month's total and comparing to the tier's
-- lab_hours_per_month. Lightweight — we're not tracking active
-- sessions, just cumulative minutes the user has accrued by
-- opening labs this month.

create table if not exists public.lab_usage_days (
  user_id uuid not null references auth.users(id) on delete cascade,
  usage_date date not null default (current_date at time zone 'UTC'),
  minutes int not null default 0 check (minutes >= 0),
  primary key (user_id, usage_date)
);

create index if not exists lab_usage_days_user_idx on public.lab_usage_days(user_id, usage_date desc);

alter table public.lab_usage_days enable row level security;

drop policy if exists "lab_usage_days: self read" on public.lab_usage_days;
create policy "lab_usage_days: self read"
  on public.lab_usage_days for select using (auth.uid() = user_id);

drop policy if exists "lab_usage_days: admin read" on public.lab_usage_days;
create policy "lab_usage_days: admin read"
  on public.lab_usage_days for select using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- Add minutes to today's bucket. Called from a server action fired
-- periodically while a lab is open. SECURITY DEFINER so users don't
-- need direct insert rights.
create or replace function public.add_lab_minutes(p_minutes int)
returns int
language plpgsql
security definer
set search_path = public
as $$
declare
  v_user uuid := auth.uid();
  v_today date := (now() at time zone 'UTC')::date;
  v_new_total int;
begin
  if v_user is null then raise exception 'Not signed in'; end if;
  if p_minutes is null or p_minutes <= 0 or p_minutes > 60 then
    raise exception 'Minutes must be between 1 and 60 per call';
  end if;
  insert into public.lab_usage_days (user_id, usage_date, minutes)
    values (v_user, v_today, p_minutes)
  on conflict (user_id, usage_date) do update set
    minutes = public.lab_usage_days.minutes + excluded.minutes
  returning minutes into v_new_total;
  return v_new_total;
end
$$;

-- Sum this month's minutes for a user — used by the lab-load gate.
create or replace function public.month_lab_minutes(p_user uuid)
returns int
language sql
security definer
set search_path = public
as $$
  select coalesce(sum(minutes), 0)::int
  from public.lab_usage_days
  where user_id = p_user
    and usage_date >= date_trunc('month', (now() at time zone 'UTC')::date);
$$;


-- ==========================================================================
-- 040_content_protection.sql
-- ==========================================================================
-- v0.26 — Content protection (balanced, not strict)
--
-- Goal: catch real copies of paid content, not false-positive honest
-- remixes. Three pieces:
--
-- 1. content_fingerprints — a cheap hash + token-set per paid item.
--    On publish we stash it. On future publishes of paid items we
--    compare and queue "possible copy" reviews for admin if similarity
--    crosses a high threshold (> 0.85 overlap).
--
-- 2. content_strikes — soft-strike system for confirmed copies. 3
--    confirmed strikes in 90 days = soft ban (banned_until set). Fewer
--    strikes = warning + content removal, user keeps their account.
--
-- 3. fingerprint_reviews — queue of auto-flagged items for a human
--    (admin) to confirm / dismiss. NEVER auto-bans or auto-removes —
--    the admin ack is the gate.

create table if not exists public.content_fingerprints (
  id uuid primary key default gen_random_uuid(),
  subject_kind text not null check (subject_kind in ('store_model','tutorial','lab')),
  subject_id uuid not null,
  owner_id uuid not null references auth.users(id) on delete cascade,
  /** 64-bit hash (stored as text) of the normalized content. */
  content_hash text not null,
  /** Space-separated sorted unique tokens, for jaccard / trigram
   *  similarity checks. Kept short — trimmed to 2000 chars. */
  token_bag text not null,
  /** Minimum token count to be eligible for comparison. Tiny
   *  snippets are false-positive prone, so we skip <20 tokens. */
  token_count int not null,
  created_at timestamptz not null default now(),
  unique (subject_kind, subject_id)
);

create index if not exists content_fingerprints_hash_idx on public.content_fingerprints(content_hash);
create index if not exists content_fingerprints_owner_idx on public.content_fingerprints(owner_id);

alter table public.content_fingerprints enable row level security;

drop policy if exists "content_fingerprints: public read" on public.content_fingerprints;
create policy "content_fingerprints: public read"
  on public.content_fingerprints for select using (true);

create table if not exists public.fingerprint_reviews (
  id uuid primary key default gen_random_uuid(),
  flagged_fingerprint_id uuid not null references public.content_fingerprints(id) on delete cascade,
  matched_fingerprint_id uuid not null references public.content_fingerprints(id) on delete cascade,
  similarity_pct int not null check (similarity_pct between 0 and 100),
  status text not null default 'open' check (status in ('open','dismissed','confirmed_copy')),
  admin_note text,
  created_at timestamptz not null default now(),
  resolved_at timestamptz,
  resolved_by uuid references auth.users(id)
);

create index if not exists fingerprint_reviews_status_idx on public.fingerprint_reviews(status, created_at);

alter table public.fingerprint_reviews enable row level security;

drop policy if exists "fingerprint_reviews: admin all" on public.fingerprint_reviews;
create policy "fingerprint_reviews: admin all"
  on public.fingerprint_reviews for all
  using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ))
  with check (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

create table if not exists public.content_strikes (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  review_id uuid references public.fingerprint_reviews(id) on delete set null,
  reason text not null,
  severity int not null default 1 check (severity between 1 and 3),
  admin_id uuid references auth.users(id),
  created_at timestamptz not null default now()
);

create index if not exists content_strikes_user_idx on public.content_strikes(user_id, created_at desc);

alter table public.content_strikes enable row level security;

drop policy if exists "content_strikes: self read" on public.content_strikes;
create policy "content_strikes: self read"
  on public.content_strikes for select using (auth.uid() = user_id);

drop policy if exists "content_strikes: admin all" on public.content_strikes;
create policy "content_strikes: admin all"
  on public.content_strikes for all
  using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ))
  with check (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- Finds fingerprint rows with token_bag overlap > threshold_pct,
-- excluding the subject's own row and rows owned by the same user
-- (users re-publishing their own similar work is legitimate).
create or replace function public.find_possible_copies(
  p_fingerprint_id uuid,
  p_threshold_pct int default 85
)
returns table (
  matched_id uuid,
  similarity_pct int
)
language plpgsql
stable
security definer
set search_path = public
as $$
declare
  v_subject record;
  v_my_tokens text[];
  v_my_count int;
begin
  select * into v_subject from public.content_fingerprints where id = p_fingerprint_id;
  if v_subject is null then return; end if;
  if v_subject.token_count < 20 then return; end if;

  v_my_tokens := string_to_array(v_subject.token_bag, ' ');
  v_my_count := array_length(v_my_tokens, 1);

  return query
    with others as (
      select id, owner_id, token_bag,
             string_to_array(token_bag, ' ') as toks
        from public.content_fingerprints
        where id <> p_fingerprint_id
          and owner_id <> v_subject.owner_id
          and token_count >= 20
    ),
    scored as (
      select o.id,
             -- Intersection / smaller-set size = "how much of the
             -- smaller piece appears in the larger". Feels correct
             -- for copy detection where the new thing might be a
             -- trimmed subset.
             round(
               100.0 *
               cardinality(array(select unnest(v_my_tokens) intersect select unnest(o.toks)))
               / least(v_my_count, array_length(o.toks, 1))
             ) as pct
        from others o
    )
    select s.id, s.pct::int
      from scored s
      where s.pct >= p_threshold_pct
      order by s.pct desc;
end
$$;


-- ==========================================================================
-- 041_ads_and_business.sql
-- ==========================================================================
-- v0.27 — Advertising + business verification
--
-- Coin-funded self-serve ads. Every campaign starts `pending_review`;
-- an admin must approve before impressions flow. Impressions + clicks
-- logged to ad_events for stats. Campaigns spend at a configurable
-- Coin cost-per-impression (CPM) until budget exhausted or paused.
--
-- Business accounts earn a yellow verification tick via a separate
-- request queue (business_verification_requests). The yellow tier
-- already exists in verified_tier from v0.13; this migration adds the
-- explicit request queue so admins can review proof-of-business docs.

-- ---------- ad_campaigns ----------

create table if not exists public.ad_campaigns (
  id uuid primary key default gen_random_uuid(),
  advertiser_id uuid not null references auth.users(id) on delete cascade,
  name text not null check (length(name) between 1 and 120),
  /** What's shown to viewers. */
  headline text not null check (length(headline) between 1 and 120),
  body text check (body is null or length(body) <= 400),
  /** Required — takes viewer away on click. Validated at action layer. */
  click_url text not null check (length(click_url) between 8 and 400 and click_url ~ '^https?://'),
  /** Optional image (banner / card creative). URL only — no upload flow in v0.27. */
  image_url text check (image_url is null or (length(image_url) between 8 and 400 and image_url ~ '^https?://')),
  /** Which on-platform slot this campaign targets. */
  placement text not null check (placement in ('signal', 'explore', 'store', 'learn')),
  /** Cost per mille (per 1000 impressions), in Coins. Default = 10 Coins / 1000 impressions = ~$0.10 CPM. */
  cpm_coins int not null default 10 check (cpm_coins between 1 and 10000),
  /** Upfront budget deducted from advertiser's wallet on activation. */
  budget_coins int not null check (budget_coins >= 10),
  /** Coins consumed so far (1 Coin per (1000/cpm_coins) impressions served). */
  spent_coins int not null default 0 check (spent_coins >= 0),
  /** Lifecycle. Admin flips pending_review → approved. Advertiser can
   *  pause/resume an approved campaign. exhausted = budget used up. */
  status text not null default 'pending_review'
    check (status in ('pending_review','approved','paused','rejected','exhausted','ended')),
  admin_note text,
  /** Impression tally — denormalized counter for quick dashboards. */
  impressions bigint not null default 0 check (impressions >= 0),
  clicks bigint not null default 0 check (clicks >= 0),
  created_at timestamptz not null default now(),
  approved_at timestamptz,
  approved_by uuid references auth.users(id),
  ended_at timestamptz
);

create index if not exists ad_campaigns_placement_idx
  on public.ad_campaigns(placement, status);
create index if not exists ad_campaigns_advertiser_idx
  on public.ad_campaigns(advertiser_id, created_at desc);

alter table public.ad_campaigns enable row level security;

drop policy if exists "ad_campaigns: self read" on public.ad_campaigns;
create policy "ad_campaigns: self read"
  on public.ad_campaigns for select
  using (auth.uid() = advertiser_id);

drop policy if exists "ad_campaigns: public read approved" on public.ad_campaigns;
create policy "ad_campaigns: public read approved"
  on public.ad_campaigns for select
  using (status in ('approved','paused','exhausted','ended'));

drop policy if exists "ad_campaigns: self insert" on public.ad_campaigns;
create policy "ad_campaigns: self insert"
  on public.ad_campaigns for insert
  with check (auth.uid() = advertiser_id and status = 'pending_review');

drop policy if exists "ad_campaigns: self pause" on public.ad_campaigns;
create policy "ad_campaigns: self pause"
  on public.ad_campaigns for update
  using (auth.uid() = advertiser_id and status in ('approved','paused'))
  with check (auth.uid() = advertiser_id and status in ('approved','paused'));

drop policy if exists "ad_campaigns: admin all" on public.ad_campaigns;
create policy "ad_campaigns: admin all"
  on public.ad_campaigns for all
  using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ))
  with check (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- ---------- ad_events ----------

create table if not exists public.ad_events (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.ad_campaigns(id) on delete cascade,
  event_type text not null check (event_type in ('impression','click')),
  viewer_id uuid references auth.users(id) on delete set null,
  /** Anti-double-count: one impression per viewer per campaign per day
   *  is enforced at the insert layer (unique constraint below covers
   *  the common case). */
  day date not null default (now() at time zone 'UTC')::date,
  created_at timestamptz not null default now()
);

create index if not exists ad_events_campaign_idx on public.ad_events(campaign_id, created_at desc);

-- Unique (campaign, viewer, day, event_type) — cheapest way to prevent
-- a logged-in viewer from racking up impressions on refresh. Anonymous
-- viewers share viewer_id=null; that row is still useful stats-wise
-- but not anti-fraud.
create unique index if not exists ad_events_unique_daily_impression
  on public.ad_events(campaign_id, viewer_id, day, event_type)
  where viewer_id is not null;

alter table public.ad_events enable row level security;

drop policy if exists "ad_events: admin read" on public.ad_events;
create policy "ad_events: admin read"
  on public.ad_events for select
  using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

drop policy if exists "ad_events: campaign owner read" on public.ad_events;
create policy "ad_events: campaign owner read"
  on public.ad_events for select
  using (exists (
    select 1 from public.ad_campaigns c
    where c.id = ad_events.campaign_id and c.advertiser_id = auth.uid()
  ));

-- Insert policy is loose — any authenticated session can register an
-- impression / click. Integrity comes from the unique index above and
-- server-side rate limiting in the action.
drop policy if exists "ad_events: public insert" on public.ad_events;
create policy "ad_events: public insert"
  on public.ad_events for insert
  with check (auth.role() = 'authenticated');

-- ---------- business_verification_requests ----------

create table if not exists public.business_verification_requests (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  legal_name text not null check (length(legal_name) between 2 and 200),
  country text not null check (length(country) = 2),
  /** e.g. "Samsung Electronics India Pvt Ltd — corporate brand page". */
  business_description text not null check (length(business_description) between 10 and 2000),
  /** Public-facing proof: corporate domain + a LinkedIn / Crunchbase
   *  URL admins can use to sanity-check. */
  proof_url text not null check (length(proof_url) between 8 and 400 and proof_url ~ '^https?://'),
  /** Contact email at the business domain — useful for verification. */
  contact_email text not null check (length(contact_email) between 5 and 200),
  status text not null default 'pending'
    check (status in ('pending','approved','rejected')),
  admin_note text,
  created_at timestamptz not null default now(),
  resolved_at timestamptz,
  resolved_by uuid references auth.users(id)
);

create index if not exists business_verification_requests_status_idx
  on public.business_verification_requests(status, created_at);

alter table public.business_verification_requests enable row level security;

drop policy if exists "business_verification: self read" on public.business_verification_requests;
create policy "business_verification: self read"
  on public.business_verification_requests for select
  using (auth.uid() = user_id);

drop policy if exists "business_verification: self insert" on public.business_verification_requests;
create policy "business_verification: self insert"
  on public.business_verification_requests for insert
  with check (auth.uid() = user_id and status = 'pending');

drop policy if exists "business_verification: admin all" on public.business_verification_requests;
create policy "business_verification: admin all"
  on public.business_verification_requests for all
  using (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ))
  with check (exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ));

-- ---------- Campaign activation RPC ----------
--
-- Called by the advertiser after they hit "Submit campaign". Locks in
-- budget by debiting Coins from the advertiser's wallet. Status moves
-- from pending_review → approved is an admin action; this RPC only
-- creates the pending row and holds the budget. On admin rejection,
-- the held budget is refunded (separate RPC).

create or replace function public.activate_ad_campaign(
  p_name         text,
  p_headline     text,
  p_body         text,
  p_click_url    text,
  p_image_url    text,
  p_placement    text,
  p_cpm_coins    int,
  p_budget_coins int
)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_advertiser uuid := auth.uid();
  v_campaign_id uuid;
begin
  if v_advertiser is null then
    raise exception 'Not signed in';
  end if;
  if p_budget_coins < 10 then
    raise exception 'Minimum campaign budget is 10 Coins';
  end if;

  -- Debit budget up front — if the advertiser doesn't have the Coins
  -- this raises and nothing happens. No campaign row on insufficient
  -- funds.
  perform public.debit_coins(
    v_advertiser,
    p_budget_coins,
    'spend_tip',  -- reuse an existing reason; ledger meta carries context
    jsonb_build_object('kind', 'ad_campaign_budget', 'placement', p_placement)
  );

  insert into public.ad_campaigns
    (advertiser_id, name, headline, body, click_url, image_url,
     placement, cpm_coins, budget_coins, status)
  values
    (v_advertiser, p_name, p_headline, p_body, p_click_url, p_image_url,
     p_placement, p_cpm_coins, p_budget_coins, 'pending_review')
  returning id into v_campaign_id;

  return v_campaign_id;
end
$$;

-- Admin-only: reject a pending campaign. Refunds the upfront budget
-- debit via credit_coins (admin_adjustment) so the advertiser is
-- made whole.
create or replace function public.reject_ad_campaign(
  p_campaign_id uuid,
  p_note        text default null
)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  v_is_admin boolean;
  v_campaign record;
begin
  select exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ) into v_is_admin;
  if not v_is_admin then raise exception 'Admin only'; end if;

  select * into v_campaign from public.ad_campaigns where id = p_campaign_id;
  if v_campaign is null then raise exception 'Campaign not found'; end if;
  if v_campaign.status <> 'pending_review' then
    raise exception 'Only pending campaigns can be rejected (got: %)', v_campaign.status;
  end if;

  perform public.credit_coins(
    v_campaign.advertiser_id,
    v_campaign.budget_coins,
    'admin_adjustment',
    jsonb_build_object('kind', 'ad_rejection_refund', 'campaign_id', p_campaign_id)
  );

  update public.ad_campaigns set
    status = 'rejected',
    admin_note = p_note,
    ended_at = now()
  where id = p_campaign_id;
end
$$;

-- Admin-only: approve a pending campaign. Budget was already held on
-- activate_ad_campaign; approval just flips status so the serve
-- rotation picks it up.
create or replace function public.approve_ad_campaign(
  p_campaign_id uuid
)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  v_is_admin boolean;
begin
  select exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ) into v_is_admin;
  if not v_is_admin then raise exception 'Admin only'; end if;
  update public.ad_campaigns set
    status = 'approved',
    approved_at = now(),
    approved_by = auth.uid()
  where id = p_campaign_id and status = 'pending_review';
end
$$;

-- ---------- Impression / click bump RPCs ----------
--
-- Cheap denormalized counters. 1 Coin of spend accrues every
-- (1000 / cpm_coins) impressions — so cpm_coins=10 = 1 Coin per 100
-- impressions. When spent_coins reaches budget_coins, campaign flips
-- to 'exhausted' and stops serving.

create or replace function public.bump_ad_impression(p_campaign_id uuid)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  v record;
  v_new_impressions bigint;
  v_accrued_spend int;
begin
  select * into v from public.ad_campaigns where id = p_campaign_id;
  if v is null or v.status <> 'approved' then return; end if;

  v_new_impressions := v.impressions + 1;
  -- Integer spend: for every (1000 / cpm_coins) impressions, accrue
  -- 1 Coin. cpm_coins is "per 1000", so spend = floor(n * cpm / 1000).
  v_accrued_spend := least(v.budget_coins, (v_new_impressions * v.cpm_coins) / 1000);

  update public.ad_campaigns set
    impressions = v_new_impressions,
    spent_coins = v_accrued_spend,
    status = case
      when v_accrued_spend >= v.budget_coins then 'exhausted'
      else status
    end,
    ended_at = case
      when v_accrued_spend >= v.budget_coins then now()
      else ended_at
    end
  where id = p_campaign_id;
end
$$;

create or replace function public.bump_ad_click(p_campaign_id uuid)
returns void
language plpgsql
security definer
set search_path = public
as $$
begin
  update public.ad_campaigns set clicks = clicks + 1
    where id = p_campaign_id;
end
$$;

-- Admin-only: approve a business verification request. Sets profile
-- verified = true + verified_tier = 'business' so the yellow tick lights
-- up everywhere the VerifiedBadge renders.
create or replace function public.approve_business_verification(
  p_request_id uuid
)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  v_is_admin boolean;
  v_req record;
begin
  select exists (
    select 1 from public.profiles
    where profiles.id = auth.uid() and profiles.admin_role is not null
  ) into v_is_admin;
  if not v_is_admin then raise exception 'Admin only'; end if;

  select * into v_req from public.business_verification_requests where id = p_request_id;
  if v_req is null then raise exception 'Request not found'; end if;
  if v_req.status <> 'pending' then
    raise exception 'Only pending requests can be approved';
  end if;

  update public.profiles set
    verified = true,
    verified_tier = 'business',
    verified_label = coalesce(verified_label, v_req.legal_name)
  where id = v_req.user_id;

  update public.business_verification_requests set
    status = 'approved',
    resolved_at = now(),
    resolved_by = auth.uid()
  where id = p_request_id;
end
$$;


-- ==========================================================================
-- 042_exosynk_clock.sql
-- ==========================================================================
-- ExoSynk v0.14.2a: public Arduino Uno clock showcase lab for @exosynk.
-- Four multiplexed 7-segment displays show 24-hour HH:MM time. Two analog
-- switches on A0/A1 bump the hour and minute. Idempotent via project_slug.

do $$
declare
  v_uid uuid;
begin
  select id into v_uid from public.profiles where lower(username) = 'exosynk';
  if v_uid is null then
    raise notice 'No profile with username=exosynk found. Create the account first.';
    return;
  end if;

  insert into public.labs
    (owner_id, slug, project_slug, name, description, visibility, circuit, code, readme)
  values
    (
      v_uid,
      substr(md5(random()::text || 'clock'), 1, 8),
      'arduino-7seg-clock',
      'Arduino 7-segment clock',
      'Arduino Uno multiplexing four common-cathode 7-segment displays into a 24-hour clock. Switches on A0 and A1 adjust the hour and minute.',
      'public',
      '{
        "parts": [
          {"id":"ard1","kind":"arduino","x":3,"y":3,"props":{"v_5v":5}},
          {"id":"r_a","kind":"resistor","x":14,"y":2,"props":{"resistance":330}},
          {"id":"r_b","kind":"resistor","x":14,"y":3,"props":{"resistance":330}},
          {"id":"r_c","kind":"resistor","x":14,"y":4,"props":{"resistance":330}},
          {"id":"r_d","kind":"resistor","x":14,"y":5,"props":{"resistance":330}},
          {"id":"r_e","kind":"resistor","x":14,"y":6,"props":{"resistance":330}},
          {"id":"r_f","kind":"resistor","x":14,"y":7,"props":{"resistance":330}},
          {"id":"r_g","kind":"resistor","x":14,"y":8,"props":{"resistance":330}},
          {"id":"seg1","kind":"sevenseg","x":22,"y":2,"props":{"vf":2.0,"maxCurrent":0.02}},
          {"id":"seg2","kind":"sevenseg","x":31,"y":2,"props":{"vf":2.0,"maxCurrent":0.02}},
          {"id":"seg3","kind":"sevenseg","x":40,"y":2,"props":{"vf":2.0,"maxCurrent":0.02}},
          {"id":"seg4","kind":"sevenseg","x":49,"y":2,"props":{"vf":2.0,"maxCurrent":0.02}},
          {"id":"rb1","kind":"resistor","x":27,"y":11,"props":{"resistance":1000}},
          {"id":"rb2","kind":"resistor","x":36,"y":11,"props":{"resistance":1000}},
          {"id":"rb3","kind":"resistor","x":45,"y":11,"props":{"resistance":1000}},
          {"id":"rb4","kind":"resistor","x":54,"y":11,"props":{"resistance":1000}},
          {"id":"q1","kind":"npn","x":30,"y":7,"props":{"vbeOn":0.7,"beta":100}},
          {"id":"q2","kind":"npn","x":39,"y":7,"props":{"vbeOn":0.7,"beta":100}},
          {"id":"q3","kind":"npn","x":48,"y":7,"props":{"vbeOn":0.7,"beta":100}},
          {"id":"q4","kind":"npn","x":57,"y":7,"props":{"vbeOn":0.7,"beta":100}},
          {"id":"pull_h","kind":"resistor","x":4,"y":12,"props":{"resistance":10000}},
          {"id":"pull_m","kind":"resistor","x":10,"y":12,"props":{"resistance":10000}},
          {"id":"sw_h","kind":"switch","x":4,"y":15,"props":{"closed":false}},
          {"id":"sw_m","kind":"switch","x":10,"y":15,"props":{"closed":false}},
          {"id":"gnd1","kind":"ground","x":66,"y":12,"props":{}}
        ],
        "wires": [
          {"id":"w5v_a","from":{"partId":"ard1","pinId":"d2"},"to":{"partId":"r_a","pinId":"a"}},
          {"id":"w5v_b","from":{"partId":"ard1","pinId":"d3"},"to":{"partId":"r_b","pinId":"a"}},
          {"id":"w5v_c","from":{"partId":"ard1","pinId":"d4"},"to":{"partId":"r_c","pinId":"a"}},
          {"id":"w5v_d","from":{"partId":"ard1","pinId":"d5"},"to":{"partId":"r_d","pinId":"a"}},
          {"id":"w5v_e","from":{"partId":"ard1","pinId":"d6"},"to":{"partId":"r_e","pinId":"a"}},
          {"id":"w5v_f","from":{"partId":"ard1","pinId":"d7"},"to":{"partId":"r_f","pinId":"a"}},
          {"id":"w5v_g","from":{"partId":"ard1","pinId":"d8"},"to":{"partId":"r_g","pinId":"a"}},

          {"id":"wa1","from":{"partId":"r_a","pinId":"b"},"to":{"partId":"seg1","pinId":"a"}},
          {"id":"wa2","from":{"partId":"r_a","pinId":"b"},"to":{"partId":"seg2","pinId":"a"}},
          {"id":"wa3","from":{"partId":"r_a","pinId":"b"},"to":{"partId":"seg3","pinId":"a"}},
          {"id":"wa4","from":{"partId":"r_a","pinId":"b"},"to":{"partId":"seg4","pinId":"a"}},
          {"id":"wb1","from":{"partId":"r_b","pinId":"b"},"to":{"partId":"seg1","pinId":"b"}},
          {"id":"wb2","from":{"partId":"r_b","pinId":"b"},"to":{"partId":"seg2","pinId":"b"}},
          {"id":"wb3","from":{"partId":"r_b","pinId":"b"},"to":{"partId":"seg3","pinId":"b"}},
          {"id":"wb4","from":{"partId":"r_b","pinId":"b"},"to":{"partId":"seg4","pinId":"b"}},
          {"id":"wc1","from":{"partId":"r_c","pinId":"b"},"to":{"partId":"seg1","pinId":"c"}},
          {"id":"wc2","from":{"partId":"r_c","pinId":"b"},"to":{"partId":"seg2","pinId":"c"}},
          {"id":"wc3","from":{"partId":"r_c","pinId":"b"},"to":{"partId":"seg3","pinId":"c"}},
          {"id":"wc4","from":{"partId":"r_c","pinId":"b"},"to":{"partId":"seg4","pinId":"c"}},
          {"id":"wd1","from":{"partId":"r_d","pinId":"b"},"to":{"partId":"seg1","pinId":"d"}},
          {"id":"wd2","from":{"partId":"r_d","pinId":"b"},"to":{"partId":"seg2","pinId":"d"}},
          {"id":"wd3","from":{"partId":"r_d","pinId":"b"},"to":{"partId":"seg3","pinId":"d"}},
          {"id":"wd4","from":{"partId":"r_d","pinId":"b"},"to":{"partId":"seg4","pinId":"d"}},
          {"id":"we1","from":{"partId":"r_e","pinId":"b"},"to":{"partId":"seg1","pinId":"e"}},
          {"id":"we2","from":{"partId":"r_e","pinId":"b"},"to":{"partId":"seg2","pinId":"e"}},
          {"id":"we3","from":{"partId":"r_e","pinId":"b"},"to":{"partId":"seg3","pinId":"e"}},
          {"id":"we4","from":{"partId":"r_e","pinId":"b"},"to":{"partId":"seg4","pinId":"e"}},
          {"id":"wf1","from":{"partId":"r_f","pinId":"b"},"to":{"partId":"seg1","pinId":"f"}},
          {"id":"wf2","from":{"partId":"r_f","pinId":"b"},"to":{"partId":"seg2","pinId":"f"}},
          {"id":"wf3","from":{"partId":"r_f","pinId":"b"},"to":{"partId":"seg3","pinId":"f"}},
          {"id":"wf4","from":{"partId":"r_f","pinId":"b"},"to":{"partId":"seg4","pinId":"f"}},
          {"id":"wg1","from":{"partId":"r_g","pinId":"b"},"to":{"partId":"seg1","pinId":"g"}},
          {"id":"wg2","from":{"partId":"r_g","pinId":"b"},"to":{"partId":"seg2","pinId":"g"}},
          {"id":"wg3","from":{"partId":"r_g","pinId":"b"},"to":{"partId":"seg3","pinId":"g"}},
          {"id":"wg4","from":{"partId":"r_g","pinId":"b"},"to":{"partId":"seg4","pinId":"g"}},

          {"id":"wdig1","from":{"partId":"ard1","pinId":"d9"},"to":{"partId":"rb1","pinId":"a"}},
          {"id":"wdig2","from":{"partId":"ard1","pinId":"d10"},"to":{"partId":"rb2","pinId":"a"}},
          {"id":"wdig3","from":{"partId":"ard1","pinId":"d11"},"to":{"partId":"rb3","pinId":"a"}},
          {"id":"wdig4","from":{"partId":"ard1","pinId":"d12"},"to":{"partId":"rb4","pinId":"a"}},
          {"id":"wbx1","from":{"partId":"rb1","pinId":"b"},"to":{"partId":"q1","pinId":"b"}},
          {"id":"wbx2","from":{"partId":"rb2","pinId":"b"},"to":{"partId":"q2","pinId":"b"}},
          {"id":"wbx3","from":{"partId":"rb3","pinId":"b"},"to":{"partId":"q3","pinId":"b"}},
          {"id":"wbx4","from":{"partId":"rb4","pinId":"b"},"to":{"partId":"q4","pinId":"b"}},
          {"id":"wcx1","from":{"partId":"seg1","pinId":"gnd"},"to":{"partId":"q1","pinId":"c"}},
          {"id":"wcx2","from":{"partId":"seg2","pinId":"gnd"},"to":{"partId":"q2","pinId":"c"}},
          {"id":"wcx3","from":{"partId":"seg3","pinId":"gnd"},"to":{"partId":"q3","pinId":"c"}},
          {"id":"wcx4","from":{"partId":"seg4","pinId":"gnd"},"to":{"partId":"q4","pinId":"c"}},
          {"id":"wex1","from":{"partId":"q1","pinId":"e"},"to":{"partId":"gnd1","pinId":"gnd"}},
          {"id":"wex2","from":{"partId":"q2","pinId":"e"},"to":{"partId":"gnd1","pinId":"gnd"}},
          {"id":"wex3","from":{"partId":"q3","pinId":"e"},"to":{"partId":"gnd1","pinId":"gnd"}},
          {"id":"wex4","from":{"partId":"q4","pinId":"e"},"to":{"partId":"gnd1","pinId":"gnd"}},

          {"id":"wh_pwr","from":{"partId":"ard1","pinId":"5v"},"to":{"partId":"pull_h","pinId":"a"}},
          {"id":"wh_sig","from":{"partId":"pull_h","pinId":"b"},"to":{"partId":"sw_h","pinId":"a"}},
          {"id":"wh_in","from":{"partId":"pull_h","pinId":"b"},"to":{"partId":"ard1","pinId":"a0"}},
          {"id":"wh_gnd","from":{"partId":"sw_h","pinId":"b"},"to":{"partId":"gnd1","pinId":"gnd"}},

          {"id":"wm_pwr","from":{"partId":"ard1","pinId":"5v"},"to":{"partId":"pull_m","pinId":"a"}},
          {"id":"wm_sig","from":{"partId":"pull_m","pinId":"b"},"to":{"partId":"sw_m","pinId":"a"}},
          {"id":"wm_in","from":{"partId":"pull_m","pinId":"b"},"to":{"partId":"ard1","pinId":"a1"}},
          {"id":"wm_gnd","from":{"partId":"sw_m","pinId":"b"},"to":{"partId":"gnd1","pinId":"gnd"}},

          {"id":"wgnd","from":{"partId":"ard1","pinId":"gnd"},"to":{"partId":"gnd1","pinId":"gnd"}}
        ],
        "junctions": []
      }'::jsonb,
      $code$// ExoSynk public project: Arduino Uno 24-hour clock.
// D2-D8 drive the shared segment bus for a 4-digit multiplexed display.
// D9-D12 switch the four digit commons through NPN transistors.
// A0 and A1 are manual adjust switches with 10k pull-ups.

const SEG_PINS = [2, 3, 4, 5, 6, 7, 8];
const DIGIT_PINS = [9, 10, 11, 12];
const HOUR_BTN = A0;
const MIN_BTN = A1;

const DIGITS = [
  0b0111111, // 0
  0b0000110, // 1
  0b1011011, // 2
  0b1001111, // 3
  0b1100110, // 4
  0b1101101, // 5
  0b1111101, // 6
  0b0000111, // 7
  0b1111111, // 8
  0b1101111, // 9
];

let hour = 12;
let minute = 34;
let scanIndex = 0;
let lastTickMs = 0;
let lastHourPressed = false;
let lastMinutePressed = false;
let lastAdjustMs = 0;

function setup() {
  for (const pin of SEG_PINS) pinMode(pin, OUTPUT);
  for (const pin of DIGIT_PINS) pinMode(pin, OUTPUT);
  for (const pin of SEG_PINS) digitalWrite(pin, LOW);
  for (const pin of DIGIT_PINS) digitalWrite(pin, LOW);

  Serial.begin(9600);
  Serial.println("ExoSynk Arduino clock ready.");
  Serial.println("A0 = +hour, A1 = +minute");

  lastTickMs = millis();
}

function allDigitsOff() {
  for (const pin of DIGIT_PINS) digitalWrite(pin, LOW);
}

function setSegments(mask) {
  for (let i = 0; i < SEG_PINS.length; i++) {
    const on = ((mask >> i) & 1) === 1;
    digitalWrite(SEG_PINS[i], on ? HIGH : LOW);
  }
}

function pressed(pin) {
  return analogRead(pin) < 200;
}

function tickClock(now) {
  while (now - lastTickMs >= 1000) {
    lastTickMs += 1000;
    minute += 1;
    if (minute >= 60) {
      minute = 0;
      hour = (hour + 1) % 24;
    }
  }
}

function handleButtons(now) {
  const hourPressed = pressed(HOUR_BTN);
  const minutePressed = pressed(MIN_BTN);

  if (hourPressed && !lastHourPressed && now - lastAdjustMs > 150) {
    hour = (hour + 1) % 24;
    lastTickMs = now;
    lastAdjustMs = now;
  }

  if (minutePressed && !lastMinutePressed && now - lastAdjustMs > 150) {
    minute += 1;
    if (minute >= 60) {
      minute = 0;
      hour = (hour + 1) % 24;
    }
    lastTickMs = now;
    lastAdjustMs = now;
  }

  lastHourPressed = hourPressed;
  lastMinutePressed = minutePressed;
}

function currentDigits() {
  const tensHour = hour < 10 ? -1 : Math.floor(hour / 10);
  return [
    tensHour,
    hour % 10,
    Math.floor(minute / 10),
    minute % 10,
  ];
}

async function loop() {
  const now = millis();
  tickClock(now);
  handleButtons(now);

  const digits = currentDigits();
  const digit = digits[scanIndex];

  allDigitsOff();
  setSegments(digit < 0 ? 0 : DIGITS[digit]);
  digitalWrite(DIGIT_PINS[scanIndex], HIGH);

  scanIndex = (scanIndex + 1) % DIGIT_PINS.length;
  await delay(2);
}
$code$,
      $md$# Arduino 7-segment clock

This public ExoSynk project is a real Arduino Uno clock, not a blank demo.
It uses four multiplexed common-cathode 7-segment displays to show 24-hour
time in `HH:MM` format.

## Wiring

- `D2`-`D8` drive the shared segment bus for `a` through `g`.
- `D9`-`D12` switch the four digit commons through NPN transistors.
- `A0` increments the hour through a toggle switch.
- `A1` increments the minute through a toggle switch.
- The switches use 10k pull-ups to `5V`, so the inputs stay stable.

## How it works

The sketch keeps time with `millis()`, updates the clock once per second,
and multiplexes one digit at a time so the Arduino only needs 11 output pins.
If you flip the hour or minute switch, the display nudges the time forward.

## What to try

1. Run the sketch and watch it count forward in 24-hour mode.
2. Flip the hour switch a few times and set a different time.
3. Flip the minute switch until the display rolls from `23:59` to `00:00`.
4. Fork the lab and turn it into an alarm clock, timer, or countdown display.

## Notes

This version intentionally uses a standard 7-segment layout and a normal
Arduino sketch, so it stays editable inside ExoSynk instead of being a fixed
mockup.
$md$
    )
  on conflict (owner_id, project_slug) do update
    set name = excluded.name,
        description = excluded.description,
        visibility = excluded.visibility,
        circuit = excluded.circuit,
        code = excluded.code,
        readme = excluded.readme,
        updated_at = now();

  raise notice 'Seeded public lab: arduino-7seg-clock for @exosynk.';
end $$;

-- ==========================================================================
-- 043_paid_store_model_access_guard.sql
-- ==========================================================================
-- v0.26.1 — Enforce paid store-model access on the server side.
--
-- Fixes a gap where a user could call fork_store_model() for a priced
-- listing without holding an entitlement, creating a "My Models" copy
-- before paying.
--
-- This migration:
-- 1) Guards fork_store_model() with entitlement checks for paid listings.
-- 2) Tightens user_owned_models insert policy so manual inserts cannot
--    bypass paid access rules.
-- 3) Removes already-created unpaid owned copies of paid listings.

-- -------------------------------------------------------------------
-- 1) fork_store_model guard
-- -------------------------------------------------------------------
create or replace function public.fork_store_model(p_source uuid)
returns uuid
language plpgsql
security definer
set search_path = public
as $func$
declare
  v_caller   uuid;
  v_src      public.store_models%rowtype;
  v_existing uuid;
  v_new      uuid;
  v_has_entitlement boolean := false;
begin
  v_caller := auth.uid();
  if v_caller is null then
    raise exception 'not signed in';
  end if;

  select * into v_src from public.store_models where id = p_source;
  if v_src.id is null then
    raise exception 'source model not found';
  end if;

  -- Paid listings require an entitlement unless viewer is the author.
  if coalesce(v_src.price_coins, 0) > 0 and v_src.author_id <> v_caller then
    select exists(
      select 1
      from public.entitlements ent
      where ent.user_id = v_caller
        and ent.subject_kind = 'store_model'
        and ent.subject_id = p_source
    ) into v_has_entitlement;

    if not v_has_entitlement then
      raise exception 'buy required for paid model';
    end if;
  end if;

  v_existing := (
    select id from public.user_owned_models
    where owner_id = v_caller and source_model_id = p_source
  );
  if v_existing is not null then
    return v_existing;
  end if;

  insert into public.user_owned_models (
    owner_id, source_model_id, source_lab_id,
    title, description, readme,
    stl_url, cover_url,
    model_3d, circuit
  ) values (
    v_caller, p_source, v_src.lab_id,
    v_src.title, v_src.description, v_src.readme,
    coalesce(v_src.stl_url, ''), v_src.cover_url,
    case when v_src.publish_3d then v_src.model_3d else null end,
    case when v_src.publish_2d then v_src.circuit  else null end
  ) returning id into v_new;

  if v_src.author_id <> v_caller then
    update public.store_models
      set downloads = downloads + 1
      where id = p_source;
  end if;

  return v_new;
end;
$func$;

-- -------------------------------------------------------------------
-- 2) Tighten insert policy on owned copies
-- -------------------------------------------------------------------
drop policy if exists "owned_models: owner writes" on public.user_owned_models;
create policy "owned_models: owner writes"
  on public.user_owned_models for insert
  with check (
    owner_id = auth.uid()
    and (
      source_model_id is null
      or exists (
        select 1
        from public.store_models sm
        where sm.id = source_model_id
          and (
            coalesce(sm.price_coins, 0) <= 0
            or sm.author_id = auth.uid()
            or exists (
              select 1
              from public.entitlements ent
              where ent.user_id = auth.uid()
                and ent.subject_kind = 'store_model'
                and ent.subject_id = source_model_id
            )
          )
      )
    )
  );

-- -------------------------------------------------------------------
-- 3) Cleanup existing unpaid owned copies of paid listings
-- -------------------------------------------------------------------
delete from public.user_owned_models uom
using public.store_models sm
where uom.source_model_id = sm.id
  and coalesce(sm.price_coins, 0) > 0
  and sm.author_id <> uom.owner_id
  and not exists (
    select 1
    from public.entitlements ent
    where ent.user_id = uom.owner_id
      and ent.subject_kind = 'store_model'
      and ent.subject_id = sm.id
  );


-- ==========================================================================
-- 044_tutorial_videos.sql
-- ==========================================================================
-- ExoSynk: ExoTutor video uploads.
-- A second storage bucket for short demo videos that creators embed inside
-- tutorial markdown. Storage is tight, so the client enforces a 5-minute
-- duration cap and ~50 MB file-size cap before upload. We also cap the
-- bucket per-object size at 60 MB as a server-side belt.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
  values (
    'tutorial-videos',
    'tutorial-videos',
    true,
    62914560, -- 60 MB
    array['video/mp4','video/webm','video/quicktime']
  )
  on conflict (id) do update
    set public = excluded.public,
        file_size_limit = excluded.file_size_limit,
        allowed_mime_types = excluded.allowed_mime_types;

drop policy if exists "tutorial-videos: public read" on storage.objects;
create policy "tutorial-videos: public read"
  on storage.objects for select
  using (bucket_id = 'tutorial-videos');

drop policy if exists "tutorial-videos: self upload" on storage.objects;
create policy "tutorial-videos: self upload"
  on storage.objects for insert
  with check (
    bucket_id = 'tutorial-videos'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists "tutorial-videos: self update" on storage.objects;
create policy "tutorial-videos: self update"
  on storage.objects for update
  using (
    bucket_id = 'tutorial-videos'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists "tutorial-videos: self delete" on storage.objects;
create policy "tutorial-videos: self delete"
  on storage.objects for delete
  using (
    bucket_id = 'tutorial-videos'
    and auth.uid()::text = (storage.foldername(name))[1]
  );


-- ==========================================================================
-- 045_tutorial_reads.sql
-- ==========================================================================
-- ExoSynk: ExoTutor "mark as read" state per (reader, tutorial).
-- One row per reader+tutorial pair. We never expose other people's read state
-- — the SELECT policy restricts to the row owner.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

create table if not exists public.tutorial_reads (
  user_id     uuid not null references auth.users(id) on delete cascade,
  tutorial_id uuid not null references public.tutorials(id) on delete cascade,
  read_at     timestamptz not null default now(),
  primary key (user_id, tutorial_id)
);

create index if not exists tutorial_reads_tutorial_idx
  on public.tutorial_reads(tutorial_id);

alter table public.tutorial_reads enable row level security;

drop policy if exists "tutorial_reads: self select" on public.tutorial_reads;
create policy "tutorial_reads: self select"
  on public.tutorial_reads for select
  using (user_id = auth.uid());

drop policy if exists "tutorial_reads: self insert" on public.tutorial_reads;
create policy "tutorial_reads: self insert"
  on public.tutorial_reads for insert
  with check (user_id = auth.uid());

drop policy if exists "tutorial_reads: self delete" on public.tutorial_reads;
create policy "tutorial_reads: self delete"
  on public.tutorial_reads for delete
  using (user_id = auth.uid());


-- ==========================================================================
-- 046_tutorial_sections.sql
-- ==========================================================================
-- ExoSynk: ExoTutor structured sections.
-- Replaces the freeform ## markdown approach. Each tutorial is now an ordered
-- list of sections (text or video), with explicit short titles for the chip
-- nav and per-video metadata (title/description/timestamp).
--
-- Old `content` column is kept for backwards compatibility — when `sections`
-- is null/empty, the reader falls back to rendering `content` as before.
--
-- post_kind is the author's choice in the Create menu:
--   text  — text + images only, no videos or YouTube
--   video — text + images + videos + YouTube embeds allowed
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.tutorials
  add column if not exists post_kind text not null default 'text'
    check (post_kind in ('text','video'));

alter table public.tutorials
  add column if not exists sections jsonb not null default '[]'::jsonb;

create index if not exists tutorials_post_kind_idx
  on public.tutorials(post_kind);


-- ==========================================================================
-- 047_message_features.sql
-- ==========================================================================
-- ExoSynk: chat upgrades — reply, edit, soft-delete.
--
-- parent_id  → message this one is replying to (same conversation)
-- edited_at  → set whenever the author edits the body; null = never edited
-- deleted_at → soft-delete tombstone; author keeps row + the UI renders a
--              "Message deleted" placeholder so reply chains don't break
--
-- Also: ADD an UPDATE policy so authors can edit / soft-delete their own
-- rows. The existing INSERT + DELETE + SELECT policies stay intact.
--
-- Paste into Supabase SQL Editor → Run. Idempotent.

alter table public.messages
  add column if not exists parent_id  uuid references public.messages(id) on delete set null;

alter table public.messages
  add column if not exists edited_at  timestamptz;

alter table public.messages
  add column if not exists deleted_at timestamptz;

create index if not exists messages_parent_idx on public.messages(parent_id);

-- The existing body NOT NULL + length check would block a soft-delete that
-- wants to keep the row but clear the body. We relax the check to allow
-- empty body when deleted_at is set. (Re-create rather than alter — Postgres
-- doesn't support modifying CHECK in place.)
alter table public.messages drop constraint if exists messages_body_check;
alter table public.messages
  add constraint messages_body_check
    check (
      length(body) <= 4000
      and (deleted_at is not null or length(trim(body)) > 0)
    );

-- Author can edit their own message (body, edited_at, deleted_at). The
-- INSERT policy already pins author_id = auth.uid(); we just allow UPDATEs
-- by the same user. RLS still gates SELECT through is_conv_member, so
-- everyone in the convo sees the change.
drop policy if exists "messages: author updates" on public.messages;
create policy "messages: author updates"
  on public.messages for update
  using (author_id = auth.uid())
  with check (author_id = auth.uid());


-- ==========================================================================
-- 048_signal_repost_pulse.sql
-- ==========================================================================
-- ExoSynk v0.28: Signal v2 — reposts + Pulse (24h ephemeral stories).
--
-- Paste into Supabase Dashboard → SQL Editor → Run. Idempotent.
--
-- Adds:
--   * post_reposts        — retweet-style reshares, one per (user, post)
--   * posts.repost_count  — denormalised counter kept in sync by trigger
--   * pulses              — 24h ephemeral stories (image / text / project)
--   * pulse_views         — per-viewer seen tracking (drives the "seen" ring)
--   * pulse-images bucket — storage for image pulses

-- =============================================================================
-- Reposts
-- =============================================================================
alter table public.posts
  add column if not exists repost_count int not null default 0;

create table if not exists public.post_reposts (
  post_id    uuid not null references public.posts(id) on delete cascade,
  user_id    uuid not null references auth.users(id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (post_id, user_id)
);

create index if not exists post_reposts_user_idx on public.post_reposts(user_id, created_at desc);
create index if not exists post_reposts_post_idx on public.post_reposts(post_id);

alter table public.post_reposts enable row level security;

drop policy if exists "post_reposts: read all" on public.post_reposts;
create policy "post_reposts: read all"
  on public.post_reposts for select using (true);

drop policy if exists "post_reposts: self insert" on public.post_reposts;
create policy "post_reposts: self insert"
  on public.post_reposts for insert
  with check (user_id = auth.uid());

drop policy if exists "post_reposts: self delete" on public.post_reposts;
create policy "post_reposts: self delete"
  on public.post_reposts for delete
  using (user_id = auth.uid());

-- Trigger: keep posts.repost_count in sync.
create or replace function public.post_reposts_bump()
returns trigger language plpgsql as $func$
begin
  if tg_op = 'INSERT' then
    update public.posts set repost_count = repost_count + 1 where id = new.post_id;
    return new;
  elsif tg_op = 'DELETE' then
    update public.posts set repost_count = greatest(0, repost_count - 1) where id = old.post_id;
    return old;
  end if;
  return null;
end;
$func$;

drop trigger if exists post_reposts_bump_ins on public.post_reposts;
create trigger post_reposts_bump_ins after insert on public.post_reposts
  for each row execute function public.post_reposts_bump();

drop trigger if exists post_reposts_bump_del on public.post_reposts;
create trigger post_reposts_bump_del after delete on public.post_reposts
  for each row execute function public.post_reposts_bump();

-- =============================================================================
-- Pulse — 24h ephemeral stories
-- =============================================================================
-- A pulse is one of three kinds:
--   image   — an uploaded image (image_url required)
--   text    — a colored text card (body required, bg = colour token)
--   project — attaches a lab / tutorial / store listing (attach_* required)
-- Every pulse auto-expires 24h after creation; queries filter on expires_at.
create table if not exists public.pulses (
  id          uuid primary key default gen_random_uuid(),
  author_id   uuid not null references auth.users(id) on delete cascade,
  kind        text not null check (kind in ('image', 'text', 'project')),
  image_url   text,
  body        text check (body is null or length(body) <= 280),
  bg          text,
  attach_kind text check (attach_kind in ('lab', 'tutorial', 'store_model')),
  attach_id   uuid,
  created_at  timestamptz not null default now(),
  expires_at  timestamptz not null default (now() + interval '24 hours'),
  constraint pulses_image_ok   check (kind <> 'image'   or image_url is not null),
  constraint pulses_text_ok    check (kind <> 'text'    or (body is not null and length(trim(body)) > 0)),
  constraint pulses_project_ok check (kind <> 'project' or (attach_kind is not null and attach_id is not null))
);

create index if not exists pulses_active_idx on public.pulses(expires_at desc, created_at desc);
create index if not exists pulses_author_idx on public.pulses(author_id, created_at desc);

alter table public.pulses enable row level security;

drop policy if exists "pulses: read all" on public.pulses;
create policy "pulses: read all"
  on public.pulses for select using (true);

drop policy if exists "pulses: self insert" on public.pulses;
create policy "pulses: self insert"
  on public.pulses for insert
  with check (author_id = auth.uid());

drop policy if exists "pulses: self delete" on public.pulses;
create policy "pulses: self delete"
  on public.pulses for delete
  using (author_id = auth.uid());

-- pulse_views — one row per (pulse, viewer). Powers the seen/unseen ring
-- and lets an author see who watched their pulse.
create table if not exists public.pulse_views (
  pulse_id   uuid not null references public.pulses(id) on delete cascade,
  viewer_id  uuid not null references auth.users(id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (pulse_id, viewer_id)
);

create index if not exists pulse_views_viewer_idx on public.pulse_views(viewer_id);

alter table public.pulse_views enable row level security;

drop policy if exists "pulse_views: read all" on public.pulse_views;
create policy "pulse_views: read all"
  on public.pulse_views for select using (true);

drop policy if exists "pulse_views: self insert" on public.pulse_views;
create policy "pulse_views: self insert"
  on public.pulse_views for insert
  with check (viewer_id = auth.uid());

-- =============================================================================
-- Storage bucket for image pulses
-- =============================================================================
insert into storage.buckets (id, name, public)
  values ('pulse-images', 'pulse-images', true)
  on conflict (id) do update set public = true;

drop policy if exists "pulse-images: public read" on storage.objects;
create policy "pulse-images: public read"
  on storage.objects for select
  using (bucket_id = 'pulse-images');

drop policy if exists "pulse-images: self upload" on storage.objects;
create policy "pulse-images: self upload"
  on storage.objects for insert
  with check (
    bucket_id = 'pulse-images'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

drop policy if exists "pulse-images: self delete" on storage.objects;
create policy "pulse-images: self delete"
  on storage.objects for delete
  using (
    bucket_id = 'pulse-images'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- =============================================================================
-- Contribution graph — count reposts + pulses as activity.
-- =============================================================================
create or replace view public.user_activity_days as
with events as (
  select owner_id  as user_id, date(updated_at) as day from public.labs
  union all
  select author_id, date(updated_at)                   from public.tutorials
  union all
  select author_id, date(updated_at)                   from public.store_models
  union all
  select author_id, date(created_at)                   from public.comments
  union all
  select author_id, date(created_at)                   from public.tutorial_comments
  union all
  select author_id, date(created_at)                   from public.store_model_comments
  union all
  select user_id,   date(created_at)                   from public.lab_votes
  union all
  select user_id,   date(created_at)                   from public.tutorial_votes
  union all
  select user_id,   date(created_at)                   from public.store_model_stars
  union all
  select author_id, date(created_at)                   from public.posts
  union all
  select author_id, date(created_at)                   from public.post_replies
  union all
  select user_id,   date(created_at)                   from public.post_likes
  union all
  -- Signal v2
  select user_id,   date(created_at)                   from public.post_reposts
  union all
  select author_id, date(created_at)                   from public.pulses
)
select user_id, day, count(*)::int as contributions
from events
where user_id is not null and day is not null
group by user_id, day;


-- ==========================================================================
-- 049_project_hub.sql
-- ==========================================================================
-- ExoSynk: Project Hub (sub-project B).
-- Adds collaborators, content links, issues, and a discussion-group FK so the
-- per-lab page can become a tabbed hub.
-- Paste into Supabase SQL Editor -> Run. Idempotent.

-- ============================================================================
-- labs: discussion group FK
-- ============================================================================
alter table public.labs
  add column if not exists discussion_group_id uuid
    references public.conversations(id) on delete set null;

-- ============================================================================
-- lab_collaborators
-- ============================================================================
create table if not exists public.lab_collaborators (
  lab_id     uuid not null references public.labs(id) on delete cascade,
  user_id    uuid not null references auth.users(id) on delete cascade,
  role       text not null check (role in ('maintainer','contributor')),
  added_by   uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  primary key (lab_id, user_id)
);
create index if not exists lab_collaborators_user_idx on public.lab_collaborators(user_id);

-- Helper: can this user maintain this lab? (owner OR maintainer collaborator)
-- SECURITY DEFINER so RLS policies can call it without recursive policy checks.
create or replace function public.can_maintain_lab(p_lab uuid, p_uid uuid)
returns boolean
language sql
security definer
stable
as $func$
  select exists (select 1 from public.labs where id = p_lab and owner_id = p_uid)
      or exists (
        select 1 from public.lab_collaborators
        where lab_id = p_lab and user_id = p_uid and role = 'maintainer'
      );
$func$;

-- Helper: is this lab visible to this user? (public, or owner, or collaborator)
create or replace function public.can_see_lab(p_lab uuid, p_uid uuid)
returns boolean
language sql
security definer
stable
as $func$
  select exists (
    select 1 from public.labs l
    where l.id = p_lab
      and (
        l.visibility = 'public'
        or l.owner_id = p_uid
        or exists (
          select 1 from public.lab_collaborators c
          where c.lab_id = l.id and c.user_id = p_uid
        )
      )
  );
$func$;

-- ============================================================================
-- lab_links — tutorials / store listings / posts that mention a lab
-- ============================================================================
create table if not exists public.lab_links (
  id          uuid primary key default gen_random_uuid(),
  lab_id      uuid not null references public.labs(id) on delete cascade,
  kind        text not null check (kind in ('post','tutorial','store_model')),
  ref_id      uuid not null,
  created_by  uuid references auth.users(id) on delete set null,
  status      text not null default 'pending' check (status in ('pending','approved','hidden')),
  pinned      boolean not null default false,
  approved_by uuid references auth.users(id) on delete set null,
  approved_at timestamptz,
  created_at  timestamptz not null default now(),
  unique (lab_id, kind, ref_id)
);
create index if not exists lab_links_lab_kind_status_idx on public.lab_links(lab_id, kind, status);
create index if not exists lab_links_lab_pinned_idx on public.lab_links(lab_id, pinned);

-- ============================================================================
-- lab_link_endorsements — community endorsements for tutorial links
-- ============================================================================
create table if not exists public.lab_link_endorsements (
  lab_link_id uuid not null references public.lab_links(id) on delete cascade,
  user_id     uuid not null references auth.users(id) on delete cascade,
  created_at  timestamptz not null default now(),
  primary key (lab_link_id, user_id)
);

-- ============================================================================
-- lab_issues
-- ============================================================================
create table if not exists public.lab_issues (
  id         uuid primary key default gen_random_uuid(),
  lab_id     uuid not null references public.labs(id) on delete cascade,
  number     int not null,
  author_id  uuid references auth.users(id) on delete set null,
  title      text not null check (length(trim(title)) > 0 and length(title) <= 200),
  body       text not null default '' check (length(body) <= 20000),
  status     text not null default 'open' check (status in ('open','closed')),
  closed_by  uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (lab_id, number)
);
create index if not exists lab_issues_lab_idx on public.lab_issues(lab_id, status, number desc);

drop trigger if exists lab_issues_updated_at on public.lab_issues;
create trigger lab_issues_updated_at
  before update on public.lab_issues
  for each row execute procedure public.set_updated_at();

-- Atomic per-lab issue number allocation. Locks the lab row to serialize.
create or replace function public.next_lab_issue_number(p_lab uuid)
returns int
language plpgsql
security definer
as $func$
declare
  v_next int;
begin
  perform 1 from public.labs where id = p_lab for update;
  select coalesce(max(number), 0) + 1 into v_next
    from public.lab_issues where lab_id = p_lab;
  return v_next;
end;
$func$;

create table if not exists public.lab_issue_comments (
  id         uuid primary key default gen_random_uuid(),
  issue_id   uuid not null references public.lab_issues(id) on delete cascade,
  author_id  uuid references auth.users(id) on delete set null,
  body       text not null check (length(trim(body)) > 0 and length(body) <= 20000),
  created_at timestamptz not null default now()
);
create index if not exists lab_issue_comments_issue_idx on public.lab_issue_comments(issue_id, created_at);

create table if not exists public.lab_issue_label_map (
  issue_id uuid not null references public.lab_issues(id) on delete cascade,
  label    text not null check (label in ('bug','idea','question','help wanted')),
  primary key (issue_id, label)
);

-- ============================================================================
-- RLS
-- ============================================================================
alter table public.lab_collaborators    enable row level security;
alter table public.lab_links            enable row level security;
alter table public.lab_link_endorsements enable row level security;
alter table public.lab_issues           enable row level security;
alter table public.lab_issue_comments   enable row level security;
alter table public.lab_issue_label_map  enable row level security;

-- lab_collaborators: readable if the lab is visible; written by lab owner only.
drop policy if exists "lab_collab: read" on public.lab_collaborators;
create policy "lab_collab: read" on public.lab_collaborators for select
  using (public.can_see_lab(lab_id, auth.uid()));
drop policy if exists "lab_collab: owner writes" on public.lab_collaborators;
create policy "lab_collab: owner writes" on public.lab_collaborators for all
  using (exists (select 1 from public.labs where id = lab_id and owner_id = auth.uid()))
  with check (exists (select 1 from public.labs where id = lab_id and owner_id = auth.uid()));

-- lab_links: readable if lab visible; insert by any signed-in user;
-- status/pinned/approved updates by maintainers only; delete by maintainers.
drop policy if exists "lab_links: read" on public.lab_links;
create policy "lab_links: read" on public.lab_links for select
  using (public.can_see_lab(lab_id, auth.uid()));
drop policy if exists "lab_links: insert" on public.lab_links;
create policy "lab_links: insert" on public.lab_links for insert
  with check (auth.uid() is not null and created_by = auth.uid());
drop policy if exists "lab_links: maintainer update" on public.lab_links;
create policy "lab_links: maintainer update" on public.lab_links for update
  using (public.can_maintain_lab(lab_id, auth.uid()))
  with check (public.can_maintain_lab(lab_id, auth.uid()));
drop policy if exists "lab_links: maintainer delete" on public.lab_links;
create policy "lab_links: maintainer delete" on public.lab_links for delete
  using (public.can_maintain_lab(lab_id, auth.uid()));

-- lab_link_endorsements: readable by all; users manage their own rows.
drop policy if exists "lab_endorse: read" on public.lab_link_endorsements;
create policy "lab_endorse: read" on public.lab_link_endorsements for select using (true);
drop policy if exists "lab_endorse: self write" on public.lab_link_endorsements;
create policy "lab_endorse: self write" on public.lab_link_endorsements for all
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

-- lab_issues: readable if lab visible; insert by any signed-in user;
-- update by author or maintainer; delete by maintainer.
drop policy if exists "lab_issues: read" on public.lab_issues;
create policy "lab_issues: read" on public.lab_issues for select
  using (public.can_see_lab(lab_id, auth.uid()));
drop policy if exists "lab_issues: insert" on public.lab_issues;
create policy "lab_issues: insert" on public.lab_issues for insert
  with check (auth.uid() is not null and author_id = auth.uid());
drop policy if exists "lab_issues: update" on public.lab_issues;
create policy "lab_issues: update" on public.lab_issues for update
  using (author_id = auth.uid() or public.can_maintain_lab(lab_id, auth.uid()))
  with check (author_id = auth.uid() or public.can_maintain_lab(lab_id, auth.uid()));
drop policy if exists "lab_issues: delete" on public.lab_issues;
create policy "lab_issues: delete" on public.lab_issues for delete
  using (public.can_maintain_lab(lab_id, auth.uid()));

-- lab_issue_comments: readable if parent issue's lab is visible;
-- insert by any signed-in user; delete by comment author or maintainer.
drop policy if exists "lab_issue_comments: read" on public.lab_issue_comments;
create policy "lab_issue_comments: read" on public.lab_issue_comments for select
  using (exists (
    select 1 from public.lab_issues i
    where i.id = issue_id and public.can_see_lab(i.lab_id, auth.uid())
  ));
drop policy if exists "lab_issue_comments: insert" on public.lab_issue_comments;
create policy "lab_issue_comments: insert" on public.lab_issue_comments for insert
  with check (auth.uid() is not null and author_id = auth.uid());
drop policy if exists "lab_issue_comments: delete" on public.lab_issue_comments;
create policy "lab_issue_comments: delete" on public.lab_issue_comments for delete
  using (author_id = auth.uid() or exists (
    select 1 from public.lab_issues i
    where i.id = issue_id and public.can_maintain_lab(i.lab_id, auth.uid())
  ));

-- lab_issue_label_map: readable if parent issue's lab is visible;
-- written by maintainers only.
drop policy if exists "lab_labels: read" on public.lab_issue_label_map;
create policy "lab_labels: read" on public.lab_issue_label_map for select
  using (exists (
    select 1 from public.lab_issues i
    where i.id = issue_id and public.can_see_lab(i.lab_id, auth.uid())
  ));
drop policy if exists "lab_labels: maintainer write" on public.lab_issue_label_map;
create policy "lab_labels: maintainer write" on public.lab_issue_label_map for all
  using (exists (
    select 1 from public.lab_issues i
    where i.id = issue_id and public.can_maintain_lab(i.lab_id, auth.uid())
  ))
  with check (exists (
    select 1 from public.lab_issues i
    where i.id = issue_id and public.can_maintain_lab(i.lab_id, auth.uid())
  ));

-- ============================================================================
-- Backfill lab_links from existing mentions.
-- Scans posts.body for the @P/<handle>/<slug> token and links them to the
-- matching lab.
-- ============================================================================
do $backfill$
declare
  r record;
  v_lab uuid;
  v_handle text;
  v_slug text;
begin
  -- Posts -> approved
  for r in
    select p.id, p.author_id,
           (regexp_match(p.body, '@[Pp]/([A-Za-z0-9_-]+)/([A-Za-z0-9_-]+)'))[1] as h,
           (regexp_match(p.body, '@[Pp]/([A-Za-z0-9_-]+)/([A-Za-z0-9_-]+)'))[2] as s
    from public.posts p
    where p.body ~ '@[Pp]/[A-Za-z0-9_-]+/[A-Za-z0-9_-]+'
  loop
    v_handle := r.h; v_slug := r.s;
    select l.id into v_lab
      from public.labs l
      join public.profiles pr on pr.id = l.owner_id
      where pr.username = v_handle and l.project_slug = v_slug
      limit 1;
    if v_lab is not null then
      insert into public.lab_links (lab_id, kind, ref_id, created_by, status)
      values (v_lab, 'post', r.id, r.author_id, 'approved')
      on conflict (lab_id, kind, ref_id) do nothing;
    end if;
  end loop;
end
$backfill$;


-- ==========================================================================
-- 050_lab_group_server.sql
-- ==========================================================================
-- ExoSynk: Lab Group Server (sub-project B1).
-- Discord-style per-lab server: categories + text channels. A channel wraps a
-- conversations row of kind 'channel'; access is governed by lab_group_members.
-- Paste into Supabase SQL Editor -> Run. Idempotent.

-- ============================================================================
-- conversations.kind: allow 'channel'
-- ============================================================================
alter table public.conversations drop constraint if exists conversations_kind_check;
alter table public.conversations
  add constraint conversations_kind_check
  check (kind in ('dm','group','channel'));

-- ============================================================================
-- Tables
-- ============================================================================
create table if not exists public.lab_group_categories (
  id         uuid primary key default gen_random_uuid(),
  lab_id     uuid not null references public.labs(id) on delete cascade,
  name       text not null check (length(trim(name)) between 1 and 60),
  position   int  not null default 0,
  created_at timestamptz not null default now()
);
create index if not exists lab_group_categories_lab_idx
  on public.lab_group_categories(lab_id, position);

create table if not exists public.lab_group_channels (
  id              uuid primary key default gen_random_uuid(),
  lab_id          uuid not null references public.labs(id) on delete cascade,
  category_id     uuid not null references public.lab_group_categories(id) on delete cascade,
  conversation_id uuid not null unique references public.conversations(id) on delete cascade,
  name            text not null check (length(trim(name)) between 1 and 60),
  topic           text not null default '',
  position        int  not null default 0,
  created_at      timestamptz not null default now()
);
create index if not exists lab_group_channels_lab_idx on public.lab_group_channels(lab_id);
create index if not exists lab_group_channels_cat_idx on public.lab_group_channels(category_id, position);

create table if not exists public.lab_group_members (
  lab_id    uuid not null references public.labs(id) on delete cascade,
  user_id   uuid not null references auth.users(id) on delete cascade,
  role      text not null default 'member' check (role in ('owner','member')),
  joined_at timestamptz not null default now(),
  primary key (lab_id, user_id)
);
create index if not exists lab_group_members_user_idx on public.lab_group_members(user_id);

-- ============================================================================
-- Helper functions (SECURITY DEFINER so RLS policies can call them)
-- ============================================================================
create or replace function public.is_lab_group_member(p_lab uuid, p_uid uuid)
returns boolean language sql security definer stable as $func$
  select exists (
    select 1 from public.lab_group_members
    where lab_id = p_lab and user_id = p_uid
  );
$func$;

-- lab_id of the channel whose conversation = p_conv, or null.
create or replace function public.lab_channel_lab(p_conv uuid)
returns uuid language sql security definer stable as $func$
  select lab_id from public.lab_group_channels where conversation_id = p_conv;
$func$;

create or replace function public.can_read_lab_channel(p_conv uuid, p_uid uuid)
returns boolean language sql security definer stable as $func$
  select exists (
    select 1
    from public.lab_group_channels c
    join public.labs l on l.id = c.lab_id
    where c.conversation_id = p_conv
      and (l.visibility = 'public' or public.is_lab_group_member(l.id, p_uid))
  );
$func$;

create or replace function public.can_post_lab_channel(p_conv uuid, p_uid uuid)
returns boolean language sql security definer stable as $func$
  select public.is_lab_group_member(public.lab_channel_lab(p_conv), p_uid);
$func$;

-- ============================================================================
-- RPCs (SECURITY DEFINER) — own the conversation-creating writes atomically.
-- ============================================================================
-- Bootstrap a lab's group: General category + #general channel + owner member.
create or replace function public.create_lab_group(p_lab uuid)
returns void language plpgsql security definer as $func$
declare
  v_uid uuid := auth.uid();
  v_cat uuid;
  v_conv uuid;
begin
  if v_uid is null or not public.can_maintain_lab(p_lab, v_uid) then
    raise exception 'Not allowed to create this group.';
  end if;
  if exists (select 1 from public.lab_group_channels where lab_id = p_lab) then
    return; -- already bootstrapped
  end if;
  insert into public.lab_group_categories (lab_id, name, position)
    values (p_lab, 'General', 0) returning id into v_cat;
  insert into public.conversations (kind, name, created_by)
    values ('channel', 'general', v_uid) returning id into v_conv;
  insert into public.lab_group_channels (lab_id, category_id, conversation_id, name, position)
    values (p_lab, v_cat, v_conv, 'general', 0);
  insert into public.lab_group_members (lab_id, user_id, role)
    values (p_lab, v_uid, 'owner')
    on conflict (lab_id, user_id) do update set role = 'owner';
end;
$func$;

-- Create a channel inside a category.
create or replace function public.create_lab_channel(p_lab uuid, p_category uuid, p_name text)
returns uuid language plpgsql security definer as $func$
declare
  v_uid uuid := auth.uid();
  v_conv uuid;
  v_id uuid;
  v_pos int;
begin
  if v_uid is null or not public.can_maintain_lab(p_lab, v_uid) then
    raise exception 'Not allowed to manage this group.';
  end if;
  if not exists (select 1 from public.lab_group_categories where id = p_category and lab_id = p_lab) then
    raise exception 'Category not found.';
  end if;
  select coalesce(max(position), -1) + 1 into v_pos
    from public.lab_group_channels where category_id = p_category;
  insert into public.conversations (kind, name, created_by)
    values ('channel', left(trim(p_name), 60), v_uid) returning id into v_conv;
  insert into public.lab_group_channels (lab_id, category_id, conversation_id, name, position)
    values (p_lab, p_category, v_conv, left(trim(p_name), 60), v_pos)
    returning id into v_id;
  return v_id;
end;
$func$;

-- Delete a channel (and its conversation + messages via cascade).
create or replace function public.delete_lab_channel(p_channel uuid)
returns void language plpgsql security definer as $func$
declare
  v_uid uuid := auth.uid();
  v_lab uuid;
  v_conv uuid;
begin
  select lab_id, conversation_id into v_lab, v_conv
    from public.lab_group_channels where id = p_channel;
  if v_lab is null then return; end if;
  if v_uid is null or not public.can_maintain_lab(v_lab, v_uid) then
    raise exception 'Not allowed to manage this group.';
  end if;
  delete from public.conversations where id = v_conv; -- cascades channel + messages
end;
$func$;

-- ============================================================================
-- RLS
-- ============================================================================
alter table public.lab_group_categories enable row level security;
alter table public.lab_group_channels   enable row level security;
alter table public.lab_group_members    enable row level security;

drop policy if exists "lgc: read" on public.lab_group_categories;
create policy "lgc: read" on public.lab_group_categories for select
  using (public.can_see_lab(lab_id, auth.uid()));
drop policy if exists "lgc: maintainer write" on public.lab_group_categories;
create policy "lgc: maintainer write" on public.lab_group_categories for all
  using (public.can_maintain_lab(lab_id, auth.uid()))
  with check (public.can_maintain_lab(lab_id, auth.uid()));

drop policy if exists "lgch: read" on public.lab_group_channels;
create policy "lgch: read" on public.lab_group_channels for select
  using (public.can_see_lab(lab_id, auth.uid()));
drop policy if exists "lgch: maintainer write" on public.lab_group_channels;
create policy "lgch: maintainer write" on public.lab_group_channels for all
  using (public.can_maintain_lab(lab_id, auth.uid()))
  with check (public.can_maintain_lab(lab_id, auth.uid()));

drop policy if exists "lgm: read" on public.lab_group_members;
create policy "lgm: read" on public.lab_group_members for select
  using (public.can_see_lab(lab_id, auth.uid()));
drop policy if exists "lgm: self join" on public.lab_group_members;
create policy "lgm: self join" on public.lab_group_members for insert
  with check (user_id = auth.uid()
              and public.can_see_lab(lab_id, auth.uid()));
drop policy if exists "lgm: self leave" on public.lab_group_members;
create policy "lgm: self leave" on public.lab_group_members for delete
  using (user_id = auth.uid()
         or exists (select 1 from public.labs where id = lab_id and owner_id = auth.uid()));

-- Additive policies for channel conversations + messages.
drop policy if exists "conv: read channel" on public.conversations;
create policy "conv: read channel" on public.conversations for select
  using (kind = 'channel' and public.can_read_lab_channel(id, auth.uid()));

drop policy if exists "msg: read channel" on public.messages;
create policy "msg: read channel" on public.messages for select
  using (public.can_read_lab_channel(conversation_id, auth.uid()));

drop policy if exists "msg: post channel" on public.messages;
create policy "msg: post channel" on public.messages for insert
  with check (author_id = auth.uid()
              and public.can_post_lab_channel(conversation_id, auth.uid()));


-- ==========================================================================
-- 051_group_server_extensions.sql
-- ==========================================================================
-- ExoSynk: Group Server Extensions (sub-project B2).
-- Announcement + private channels, polls, unread reads.
-- Paste into Supabase SQL Editor -> Run. Idempotent.

-- ============================================================================
-- lab_group_channels: kind + privacy
-- ============================================================================
alter table public.lab_group_channels
  add column if not exists kind text not null default 'text';
alter table public.lab_group_channels drop constraint if exists lab_group_channels_kind_check;
alter table public.lab_group_channels
  add constraint lab_group_channels_kind_check check (kind in ('text','announcement'));
alter table public.lab_group_channels
  add column if not exists is_private boolean not null default false;

-- ============================================================================
-- New tables
-- ============================================================================
create table if not exists public.lab_group_channel_members (
  channel_id uuid not null references public.lab_group_channels(id) on delete cascade,
  user_id    uuid not null references auth.users(id) on delete cascade,
  added_by   uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  primary key (channel_id, user_id)
);
create index if not exists lab_group_channel_members_user_idx
  on public.lab_group_channel_members(user_id);

create table if not exists public.lab_group_polls (
  id         uuid primary key default gen_random_uuid(),
  message_id uuid not null unique references public.messages(id) on delete cascade,
  channel_id uuid not null references public.lab_group_channels(id) on delete cascade,
  question   text not null check (length(trim(question)) between 1 and 300),
  created_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now()
);
create index if not exists lab_group_polls_channel_idx on public.lab_group_polls(channel_id);

create table if not exists public.lab_group_poll_options (
  id       uuid primary key default gen_random_uuid(),
  poll_id  uuid not null references public.lab_group_polls(id) on delete cascade,
  label    text not null check (length(trim(label)) between 1 and 120),
  position int  not null default 0
);
create index if not exists lab_group_poll_options_poll_idx
  on public.lab_group_poll_options(poll_id, position);

create table if not exists public.lab_group_poll_votes (
  poll_id    uuid not null references public.lab_group_polls(id) on delete cascade,
  user_id    uuid not null references auth.users(id) on delete cascade,
  option_id  uuid not null references public.lab_group_poll_options(id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (poll_id, user_id)
);

create table if not exists public.lab_group_reads (
  channel_id   uuid not null references public.lab_group_channels(id) on delete cascade,
  user_id      uuid not null references auth.users(id) on delete cascade,
  last_read_at timestamptz not null default now(),
  primary key (channel_id, user_id)
);

-- ============================================================================
-- Channel-access helpers — replace B1 versions, now kind/privacy-aware.
-- ============================================================================
create or replace function public.can_read_lab_channel(p_conv uuid, p_uid uuid)
returns boolean language sql security definer stable as $func$
  select exists (
    select 1
    from public.lab_group_channels c
    join public.labs l on l.id = c.lab_id
    where c.conversation_id = p_conv
      and case when c.is_private then
            public.can_maintain_lab(c.lab_id, p_uid)
            or exists (select 1 from public.lab_group_channel_members m
                       where m.channel_id = c.id and m.user_id = p_uid)
          else
            l.visibility = 'public' or public.is_lab_group_member(c.lab_id, p_uid)
          end
  );
$func$;

create or replace function public.can_post_lab_channel(p_conv uuid, p_uid uuid)
returns boolean language sql security definer stable as $func$
  select exists (
    select 1
    from public.lab_group_channels c
    where c.conversation_id = p_conv
      and case when c.kind = 'announcement' then
            public.can_maintain_lab(c.lab_id, p_uid)
          else
            public.is_lab_group_member(c.lab_id, p_uid)
            and (not c.is_private
                 or public.can_maintain_lab(c.lab_id, p_uid)
                 or exists (select 1 from public.lab_group_channel_members m
                            where m.channel_id = c.id and m.user_id = p_uid))
          end
  );
$func$;

-- ============================================================================
-- create_lab_channel RPC — recreate with kind + is_private params.
-- ============================================================================
drop function if exists public.create_lab_channel(uuid, uuid, text);
create or replace function public.create_lab_channel(
  p_lab uuid, p_category uuid, p_name text,
  p_kind text default 'text', p_is_private boolean default false
)
returns uuid language plpgsql security definer as $func$
declare
  v_uid uuid := auth.uid();
  v_conv uuid;
  v_id uuid;
  v_pos int;
begin
  if v_uid is null or not public.can_maintain_lab(p_lab, v_uid) then
    raise exception 'Not allowed to manage this group.';
  end if;
  if not exists (select 1 from public.lab_group_categories where id = p_category and lab_id = p_lab) then
    raise exception 'Category not found.';
  end if;
  if p_kind not in ('text','announcement') then
    raise exception 'Invalid channel kind.';
  end if;
  select coalesce(max(position), -1) + 1 into v_pos
    from public.lab_group_channels where category_id = p_category;
  insert into public.conversations (kind, name, created_by)
    values ('channel', left(trim(p_name), 60), v_uid) returning id into v_conv;
  insert into public.lab_group_channels
    (lab_id, category_id, conversation_id, name, position, kind, is_private)
    values (p_lab, p_category, v_conv, left(trim(p_name), 60), v_pos, p_kind, p_is_private)
    returning id into v_id;
  if p_is_private then
    insert into public.lab_group_channel_members (channel_id, user_id, added_by)
      values (v_id, v_uid, v_uid) on conflict do nothing;
  end if;
  return v_id;
end;
$func$;

-- ============================================================================
-- create_lab_poll RPC — message + poll + options, atomically.
-- ============================================================================
create or replace function public.create_lab_poll(
  p_conv uuid, p_channel uuid, p_question text, p_options text[]
)
returns void language plpgsql security definer as $func$
declare
  v_uid uuid := auth.uid();
  v_msg uuid;
  v_poll uuid;
  v_opt text;
  v_i int := 0;
begin
  if v_uid is null or not public.can_post_lab_channel(p_conv, v_uid) then
    raise exception 'Not allowed to post in this channel.';
  end if;
  if array_length(p_options, 1) is null or array_length(p_options, 1) < 2 then
    raise exception 'A poll needs at least 2 options.';
  end if;
  insert into public.messages (conversation_id, author_id, body)
    values (p_conv, v_uid, left(trim(p_question), 4000)) returning id into v_msg;
  insert into public.lab_group_polls (message_id, channel_id, question, created_by)
    values (v_msg, p_channel, left(trim(p_question), 300), v_uid) returning id into v_poll;
  foreach v_opt in array p_options loop
    if length(trim(v_opt)) > 0 then
      insert into public.lab_group_poll_options (poll_id, label, position)
        values (v_poll, left(trim(v_opt), 120), v_i);
      v_i := v_i + 1;
    end if;
  end loop;
  if v_i < 2 then
    raise exception 'A poll needs at least 2 non-empty options.';
  end if;
end;
$func$;

-- ============================================================================
-- RLS
-- ============================================================================
alter table public.lab_group_channel_members enable row level security;
alter table public.lab_group_polls           enable row level security;
alter table public.lab_group_poll_options    enable row level security;
alter table public.lab_group_poll_votes      enable row level security;
alter table public.lab_group_reads           enable row level security;

-- lab_group_channels: tighten read so private channels hide from non-members.
drop policy if exists "lgch: read" on public.lab_group_channels;
create policy "lgch: read" on public.lab_group_channels for select
  using (
    public.can_see_lab(lab_id, auth.uid())
    and (
      not is_private
      or public.can_maintain_lab(lab_id, auth.uid())
      or exists (select 1 from public.lab_group_channel_members m
                 where m.channel_id = id and m.user_id = auth.uid())
    )
  );

-- channel members: readable when the channel is readable; written by maintainers.
drop policy if exists "lgcm: read" on public.lab_group_channel_members;
create policy "lgcm: read" on public.lab_group_channel_members for select
  using (exists (select 1 from public.lab_group_channels c
                 where c.id = channel_id
                   and public.can_read_lab_channel(c.conversation_id, auth.uid())));
drop policy if exists "lgcm: maintainer write" on public.lab_group_channel_members;
create policy "lgcm: maintainer write" on public.lab_group_channel_members for all
  using (exists (select 1 from public.lab_group_channels c
                 where c.id = channel_id and public.can_maintain_lab(c.lab_id, auth.uid())))
  with check (exists (select 1 from public.lab_group_channels c
                 where c.id = channel_id and public.can_maintain_lab(c.lab_id, auth.uid())));

-- polls: readable when the channel is readable; insert by a channel poster.
drop policy if exists "lgp: read" on public.lab_group_polls;
create policy "lgp: read" on public.lab_group_polls for select
  using (exists (select 1 from public.lab_group_channels c
                 where c.id = channel_id
                   and public.can_read_lab_channel(c.conversation_id, auth.uid())));
drop policy if exists "lgp: insert" on public.lab_group_polls;
create policy "lgp: insert" on public.lab_group_polls for insert
  with check (created_by = auth.uid()
              and exists (select 1 from public.lab_group_channels c
                          where c.id = channel_id
                            and public.can_post_lab_channel(c.conversation_id, auth.uid())));

-- poll options: readable / insertable with their parent poll.
drop policy if exists "lgpo: read" on public.lab_group_poll_options;
create policy "lgpo: read" on public.lab_group_poll_options for select
  using (exists (select 1 from public.lab_group_polls p
                 join public.lab_group_channels c on c.id = p.channel_id
                 where p.id = poll_id
                   and public.can_read_lab_channel(c.conversation_id, auth.uid())));
drop policy if exists "lgpo: insert" on public.lab_group_poll_options;
create policy "lgpo: insert" on public.lab_group_poll_options for insert
  with check (exists (select 1 from public.lab_group_polls p
                      join public.lab_group_channels c on c.id = p.channel_id
                      where p.id = poll_id
                        and public.can_post_lab_channel(c.conversation_id, auth.uid())));

-- poll votes: readable when poll readable; each user manages their own vote.
drop policy if exists "lgpv: read" on public.lab_group_poll_votes;
create policy "lgpv: read" on public.lab_group_poll_votes for select
  using (exists (select 1 from public.lab_group_polls p
                 join public.lab_group_channels c on c.id = p.channel_id
                 where p.id = poll_id
                   and public.can_read_lab_channel(c.conversation_id, auth.uid())));
-- Split from a former "for all" policy: a "for all" USING clause also governs
-- SELECT, which would let a removed member still read their vote row in a
-- private channel. SELECT now goes solely through "lgpv: read".
drop policy if exists "lgpv: self vote" on public.lab_group_poll_votes;
drop policy if exists "lgpv: insert" on public.lab_group_poll_votes;
create policy "lgpv: insert" on public.lab_group_poll_votes for insert
  with check (user_id = auth.uid()
              and exists (select 1 from public.lab_group_polls p
                          join public.lab_group_channels c on c.id = p.channel_id
                          where p.id = poll_id
                            and public.can_read_lab_channel(c.conversation_id, auth.uid())));
drop policy if exists "lgpv: update" on public.lab_group_poll_votes;
create policy "lgpv: update" on public.lab_group_poll_votes for update
  using (user_id = auth.uid())
  with check (user_id = auth.uid()
              and exists (select 1 from public.lab_group_polls p
                          join public.lab_group_channels c on c.id = p.channel_id
                          where p.id = poll_id
                            and public.can_read_lab_channel(c.conversation_id, auth.uid())));
drop policy if exists "lgpv: delete own" on public.lab_group_poll_votes;
create policy "lgpv: delete own" on public.lab_group_poll_votes for delete
  using (user_id = auth.uid());

-- reads: each user manages only their own rows.
drop policy if exists "lgr: self" on public.lab_group_reads;
create policy "lgr: self" on public.lab_group_reads for all
  using (user_id = auth.uid())
  with check (user_id = auth.uid());