-- 20260502115405_share_votes.sql
--
-- Per-user vote tracking for the /said share gallery.
--
-- Replaces the broken counter-bump RPC (vote_card) with a proper relational
-- design: one row per (share_card, user) pair, RLS-enforced ownership,
-- aggregate columns on share_cards kept in sync via trigger. Voters can
-- change direction or undo at any time. One vote per user per card.
--
-- This migration is idempotent: re-running it is a no-op.

-- ---------------------------------------------------------------------------
-- Table
-- ---------------------------------------------------------------------------

create table if not exists public.share_votes (
  share_id   text not null references public.share_cards(id) on delete cascade,
  user_id    uuid not null references auth.users(id)         on delete cascade,
  direction  text not null check (direction in ('up','down')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (share_id, user_id)
);

create index if not exists share_votes_share_id_idx on public.share_votes (share_id);
create index if not exists share_votes_user_id_idx  on public.share_votes (user_id);

-- ---------------------------------------------------------------------------
-- Row Level Security
-- ---------------------------------------------------------------------------

alter table public.share_votes enable row level security;

drop policy if exists "users can read all votes"          on public.share_votes;
drop policy if exists "users can insert their own votes"  on public.share_votes;
drop policy if exists "users can update their own votes"  on public.share_votes;
drop policy if exists "users can delete their own votes"  on public.share_votes;

create policy "users can read all votes" on public.share_votes
  for select using (true);

create policy "users can insert their own votes" on public.share_votes
  for insert with check (auth.uid() = user_id);

create policy "users can update their own votes" on public.share_votes
  for update using (auth.uid() = user_id);

create policy "users can delete their own votes" on public.share_votes
  for delete using (auth.uid() = user_id);

-- ---------------------------------------------------------------------------
-- Aggregate sync trigger
--
-- After every insert / update / delete on share_votes, recompute the three
-- aggregate columns on the parent share_cards row. SECURITY DEFINER is
-- required because the calling user only has RLS access to their own vote
-- but the trigger needs to update share_cards.upvotes/downvotes/score.
-- ---------------------------------------------------------------------------

create or replace function public.recalc_share_card_score()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  s_id text;
begin
  s_id := coalesce(NEW.share_id, OLD.share_id);
  update public.share_cards
     set upvotes   = (select count(*) from public.share_votes
                       where share_id = s_id and direction = 'up'),
         downvotes = (select count(*) from public.share_votes
                       where share_id = s_id and direction = 'down'),
         score     = (select count(*) filter (where direction = 'up')
                           - count(*) filter (where direction = 'down')
                       from public.share_votes where share_id = s_id)
   where id = s_id;
  return null;
end;
$$;

drop trigger if exists share_votes_recalc on public.share_votes;
create trigger share_votes_recalc
  after insert or update or delete on public.share_votes
  for each row execute function public.recalc_share_card_score();

-- ---------------------------------------------------------------------------
-- updated_at touch trigger (only on UPDATE)
-- ---------------------------------------------------------------------------

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

drop trigger if exists share_votes_touch on public.share_votes;
create trigger share_votes_touch
  before update on public.share_votes
  for each row execute function public.touch_share_votes_updated_at();