-- Étape 3 — schéma complet : catégories, besoins, offres, conversations, messages,
-- storage, RLS, et la fonction accept_offer(). Appliqué via le MCP Supabase.

-- ============ Catégories ============
create table if not exists public.categories (
  id uuid primary key default gen_random_uuid(),
  name text not null unique,
  slug text not null unique,
  icon text,
  created_at timestamptz not null default now()
);

alter table public.categories enable row level security;

drop policy if exists "categories_select_all" on public.categories;
create policy "categories_select_all"
  on public.categories for select
  using (true);

insert into public.categories (name, slug) values
  ('Bricolage', 'bricolage'),
  ('Jardinage', 'jardinage'),
  ('Ménage', 'menage'),
  ('Déménagement', 'demenagement'),
  ('Informatique', 'informatique'),
  ('Cours particuliers', 'cours-particuliers'),
  ('Beauté & bien-être', 'beaute-bien-etre'),
  ('Événementiel', 'evenementiel'),
  ('Automobile', 'automobile'),
  ('Autre', 'autre')
on conflict (slug) do nothing;

-- ============ Besoins (needs) ============
create table if not exists public.needs (
  id uuid primary key default gen_random_uuid(),
  author_id uuid not null references public.profiles (id) on delete cascade,
  category_id uuid references public.categories (id),
  title text not null,
  description text not null,
  city text not null,
  region text,
  budget_min numeric,
  budget_max numeric,
  status text not null default 'open' check (status in ('open', 'closed', 'fulfilled')),
  accepted_offer_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.need_images (
  id uuid primary key default gen_random_uuid(),
  need_id uuid not null references public.needs (id) on delete cascade,
  storage_path text not null,
  position int not null default 0,
  created_at timestamptz not null default now()
);

-- ============ Offres ============
create table if not exists public.offers (
  id uuid primary key default gen_random_uuid(),
  need_id uuid not null references public.needs (id) on delete cascade,
  merchant_id uuid not null references public.profiles (id) on delete cascade,
  title text not null,
  description text not null,
  price numeric not null,
  status text not null default 'pending' check (status in ('pending', 'accepted', 'rejected', 'withdrawn')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (need_id, merchant_id)
);

create table if not exists public.offer_images (
  id uuid primary key default gen_random_uuid(),
  offer_id uuid not null references public.offers (id) on delete cascade,
  storage_path text not null,
  position int not null default 0
);

do $$
begin
  if not exists (
    select 1 from pg_constraint where conname = 'needs_accepted_offer_fk'
  ) then
    alter table public.needs
      add constraint needs_accepted_offer_fk
      foreign key (accepted_offer_id) references public.offers (id);
  end if;
end;
$$;

-- ============ Conversations & messages ============
create table if not exists public.conversations (
  id uuid primary key default gen_random_uuid(),
  need_id uuid not null references public.needs (id) on delete cascade,
  offer_id uuid not null unique references public.offers (id) on delete cascade,
  author_id uuid not null references public.profiles (id),
  merchant_id uuid not null references public.profiles (id),
  created_at timestamptz not null default now()
);

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,
  sender_id uuid not null references public.profiles (id),
  body text not null,
  created_at timestamptz not null default now()
);

-- ============ RLS ============
alter table public.needs enable row level security;
alter table public.need_images enable row level security;
alter table public.offers enable row level security;
alter table public.offer_images enable row level security;
alter table public.conversations enable row level security;
alter table public.messages enable row level security;

-- needs: lecture publique si open, ou toujours par l'auteur ; écriture par l'auteur uniquement
drop policy if exists "needs_select" on public.needs;
create policy "needs_select"
  on public.needs for select
  using (status = 'open' or auth.uid() = author_id);

drop policy if exists "needs_insert_own" on public.needs;
create policy "needs_insert_own"
  on public.needs for insert
  to authenticated
  with check (auth.uid() = author_id);

drop policy if exists "needs_update_own" on public.needs;
create policy "needs_update_own"
  on public.needs for update
  to authenticated
  using (auth.uid() = author_id)
  with check (auth.uid() = author_id);

-- need_images: miroir de la visibilité du besoin parent
drop policy if exists "need_images_select" on public.need_images;
create policy "need_images_select"
  on public.need_images for select
  using (
    exists (
      select 1 from public.needs n
      where n.id = need_id and (n.status = 'open' or auth.uid() = n.author_id)
    )
  );

drop policy if exists "need_images_insert_own" on public.need_images;
create policy "need_images_insert_own"
  on public.need_images for insert
  to authenticated
  with check (
    exists (select 1 from public.needs n where n.id = need_id and n.author_id = auth.uid())
  );

drop policy if exists "need_images_delete_own" on public.need_images;
create policy "need_images_delete_own"
  on public.need_images for delete
  to authenticated
  using (
    exists (select 1 from public.needs n where n.id = need_id and n.author_id = auth.uid())
  );

-- offers: visibles uniquement par le marchand auteur et l'auteur du besoin (jamais entre marchands concurrents)
drop policy if exists "offers_select" on public.offers;
create policy "offers_select"
  on public.offers for select
  to authenticated
  using (
    auth.uid() = merchant_id
    or exists (select 1 from public.needs n where n.id = need_id and n.author_id = auth.uid())
  );

drop policy if exists "offers_insert" on public.offers;
create policy "offers_insert"
  on public.offers for insert
  to authenticated
  with check (
    merchant_id = auth.uid()
    and exists (
      select 1 from public.needs n
      where n.id = need_id and n.status = 'open' and n.author_id <> auth.uid()
    )
  );

drop policy if exists "offers_update_own_pending" on public.offers;
create policy "offers_update_own_pending"
  on public.offers for update
  to authenticated
  using (auth.uid() = merchant_id and status = 'pending')
  with check (auth.uid() = merchant_id);

-- offer_images: miroir de la visibilité de l'offre parente
drop policy if exists "offer_images_select" on public.offer_images;
create policy "offer_images_select"
  on public.offer_images for select
  to authenticated
  using (
    exists (
      select 1 from public.offers o
      where o.id = offer_id
        and (
          o.merchant_id = auth.uid()
          or exists (select 1 from public.needs n where n.id = o.need_id and n.author_id = auth.uid())
        )
    )
  );

drop policy if exists "offer_images_insert_own" on public.offer_images;
create policy "offer_images_insert_own"
  on public.offer_images for insert
  to authenticated
  with check (
    exists (select 1 from public.offers o where o.id = offer_id and o.merchant_id = auth.uid())
  );

-- conversations: lecture par les deux participants ; aucune insertion côté client
drop policy if exists "conversations_select" on public.conversations;
create policy "conversations_select"
  on public.conversations for select
  to authenticated
  using (auth.uid() = author_id or auth.uid() = merchant_id);

-- messages: lecture/écriture réservées aux participants de la conversation
drop policy if exists "messages_select" on public.messages;
create policy "messages_select"
  on public.messages for select
  to authenticated
  using (
    exists (
      select 1 from public.conversations c
      where c.id = conversation_id and (auth.uid() = c.author_id or auth.uid() = c.merchant_id)
    )
  );

drop policy if exists "messages_insert" on public.messages;
create policy "messages_insert"
  on public.messages for insert
  to authenticated
  with check (
    sender_id = auth.uid()
    and exists (
      select 1 from public.conversations c
      where c.id = conversation_id and (auth.uid() = c.author_id or auth.uid() = c.merchant_id)
    )
  );

-- ============ Fonction accept_offer ============
create or replace function public.accept_offer(p_offer_id uuid)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_need_id uuid;
  v_author_id uuid;
  v_merchant_id uuid;
  v_conversation_id uuid;
begin
  select o.need_id, n.author_id, o.merchant_id
    into v_need_id, v_author_id, v_merchant_id
  from public.offers o
  join public.needs n on n.id = o.need_id
  where o.id = p_offer_id and o.status = 'pending' and n.status = 'open';

  if v_need_id is null then
    raise exception 'Offer not eligible for acceptance';
  end if;

  if auth.uid() <> v_author_id then
    raise exception 'Not authorized';
  end if;

  update public.offers set status = 'rejected', updated_at = now()
    where need_id = v_need_id and id <> p_offer_id and status = 'pending';

  update public.offers set status = 'accepted', updated_at = now()
    where id = p_offer_id;

  update public.needs set status = 'fulfilled', accepted_offer_id = p_offer_id, updated_at = now()
    where id = v_need_id;

  insert into public.conversations (need_id, offer_id, author_id, merchant_id)
    values (v_need_id, p_offer_id, v_author_id, v_merchant_id)
    returning id into v_conversation_id;

  return v_conversation_id;
end;
$$;

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

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

drop policy if exists "need_images_bucket_read" on storage.objects;
create policy "need_images_bucket_read"
  on storage.objects for select
  using (bucket_id = 'need-images');

-- Convention de chemin : {auth.uid()}/{needId}/{filename} — chacun ne peut
-- écrire que dans son propre dossier.
drop policy if exists "need_images_bucket_write" on storage.objects;
create policy "need_images_bucket_write"
  on storage.objects for insert
  to authenticated
  with check (
    bucket_id = 'need-images'
    and (storage.foldername(name))[1] = auth.uid()::text
  );

drop policy if exists "offer_images_bucket_read" on storage.objects;
create policy "offer_images_bucket_read"
  on storage.objects for select
  to authenticated
  using (bucket_id = 'offer-images');

-- Convention de chemin : {auth.uid()}/{offerId}/{filename}.
drop policy if exists "offer_images_bucket_write" on storage.objects;
create policy "offer_images_bucket_write"
  on storage.objects for insert
  to authenticated
  with check (
    bucket_id = 'offer-images'
    and (storage.foldername(name))[1] = auth.uid()::text
  );

-- ============ Realtime ============
alter publication supabase_realtime add table public.messages;
alter publication supabase_realtime add table public.conversations;