-- Product Picker: schema + RLS policies
-- Applies to a Supabase project via the SQL Editor (or `supabase db push`).

-- ---------- profiles ----------
create table profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  display_name text not null,
  created_at timestamptz not null default now()
);

alter table profiles enable row level security;

create policy "profiles are readable by authenticated users"
  on profiles for select
  to authenticated
  using (true);

create policy "users can update their own profile"
  on profiles for update
  to authenticated
  using (id = auth.uid());

-- Auto-create a profile row whenever a new auth user is created.
create function 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, coalesce(split_part(new.email, '@', 1), 'Associé'));
  return new;
end;
$$;

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

-- ---------- tags ----------
create table tags (
  id uuid primary key default gen_random_uuid(),
  name text not null unique,
  created_at timestamptz not null default now()
);

alter table tags enable row level security;

create policy "tags are readable by authenticated users"
  on tags for select
  to authenticated
  using (true);

create policy "authenticated users can create tags"
  on tags for insert
  to authenticated
  with check (true);

-- ---------- products ----------
create table products (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  description text,
  image_url text,
  price numeric(10, 2),
  currency text not null default 'MAD',
  source_url text,
  status text not null default 'a_discuter'
    check (status in ('a_discuter', 'garder', 'rejeter')),
  created_by uuid not null references profiles(id),
  status_updated_by uuid references profiles(id),
  status_updated_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table products enable row level security;

create policy "products are readable by authenticated users"
  on products for select
  to authenticated
  using (true);

create policy "authenticated users can create products"
  on products for insert
  to authenticated
  with check (created_by = auth.uid());

create policy "authenticated users can update products"
  on products for update
  to authenticated
  using (true);

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

create trigger products_set_updated_at
  before update on products
  for each row execute procedure set_updated_at();

-- ---------- product_tags ----------
create table product_tags (
  product_id uuid not null references products(id) on delete cascade,
  tag_id uuid not null references tags(id) on delete cascade,
  primary key (product_id, tag_id)
);

alter table product_tags enable row level security;

create policy "product_tags are readable by authenticated users"
  on product_tags for select
  to authenticated
  using (true);

create policy "authenticated users can link tags to products"
  on product_tags for insert
  to authenticated
  with check (true);

create policy "authenticated users can unlink tags from products"
  on product_tags for delete
  to authenticated
  using (true);

-- ---------- comments ----------
create table comments (
  id uuid primary key default gen_random_uuid(),
  product_id uuid not null references products(id) on delete cascade,
  author_id uuid not null references profiles(id),
  body text not null,
  created_at timestamptz not null default now()
);

alter table comments enable row level security;

create policy "comments are readable by authenticated users"
  on comments for select
  to authenticated
  using (true);

create policy "authenticated users can add comments"
  on comments for insert
  to authenticated
  with check (author_id = auth.uid());

create policy "authors can delete their own comments"
  on comments for delete
  to authenticated
  using (author_id = auth.uid());

-- ---------- votes ----------
create table votes (
  product_id uuid not null references products(id) on delete cascade,
  user_id uuid not null references profiles(id),
  value smallint not null check (value in (-1, 1)),
  created_at timestamptz not null default now(),
  primary key (product_id, user_id)
);

alter table votes enable row level security;

create policy "votes are readable by authenticated users"
  on votes for select
  to authenticated
  using (true);

create policy "users can cast their own vote"
  on votes for insert
  to authenticated
  with check (user_id = auth.uid());

create policy "users can change their own vote"
  on votes for update
  to authenticated
  using (user_id = auth.uid());

create policy "users can remove their own vote"
  on votes for delete
  to authenticated
  using (user_id = auth.uid());

-- ---------- storage: product images ----------
insert into storage.buckets (id, name, public)
values ('product-images', 'product-images', true)
on conflict (id) do nothing;

create policy "product images are publicly readable"
  on storage.objects for select
  using (bucket_id = 'product-images');

create policy "authenticated users can upload product images"
  on storage.objects for insert
  to authenticated
  with check (bucket_id = 'product-images');