-- ============================================================
-- Skeelera — Migration initiale
-- ============================================================

-- ─── Types énumérés ─────────────────────────────────────────

create type role_utilisateur     as enum ('client', 'prestataire', 'admin');
create type statut_compte        as enum ('actif', 'suspendu', 'supprime', 'en_moderation');
create type statut_provider      as enum ('pending', 'active', 'suspended', 'deleted');
create type type_service         as enum ('seance', 'cours', 'activite', 'evenement');
create type statut_reservation   as enum ('pending', 'confirmed', 'cancelled', 'completed');
create type statut_abonnement    as enum ('actif', 'expire', 'resilie', 'suspendu');
create type plan_abonnement      as enum ('basique', 'essentiel', 'pro', 'pro_plus');
create type statut_avis          as enum ('publie', 'en_attente', 'supprime');
create type statut_signalement   as enum ('nouveau', 'traite', 'ferme');

-- ─── profiles ───────────────────────────────────────────────

create table profiles (
  id          uuid        primary key references auth.users(id) on delete cascade,
  email       text        not null,
  nom         text,
  prenom      text,
  telephone   text,
  role        role_utilisateur  not null default 'client',
  statut      statut_compte     not null default 'actif',
  photo_url   text,
  created_at  timestamptz not null default now()
);

-- Création automatique du profil à chaque nouvel utilisateur Auth
create or replace function handle_new_user()
returns trigger as $$
begin
  insert into profiles (id, email, role, statut)
  values (new.id, new.email, 'client', 'actif');
  return new;
end;
$$ language plpgsql security definer;

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

-- ─── providers ──────────────────────────────────────────────

create table providers (
  id                uuid        primary key default gen_random_uuid(),
  user_id           uuid        references profiles(id) on delete set null,
  name              text        not null,
  email             text        not null,
  telephone         text,
  sector            text,
  city              text,
  address           text,
  siret             text,
  statut_juridique  text,
  enterprise_name   text,
  lieu_prestation   text,
  format            text,
  travail_mineurs   boolean     not null default false,
  experience        text,
  description       text,
  rating            numeric(3,2) not null default 0,
  reviews_count     integer     not null default 0,
  price_range       text,
  photo_url         text,
  cover_url         text,
  status            statut_provider not null default 'pending',
  verified          boolean     not null default false,
  submitted_at      timestamptz,
  created_at        timestamptz not null default now()
);

-- ─── services ───────────────────────────────────────────────

create table services (
  id           uuid        primary key default gen_random_uuid(),
  provider_id  uuid        not null references providers(id) on delete cascade,
  title        text        not null,
  type         type_service not null,
  price        numeric(10,2) not null,
  duration_min integer     not null,
  description  text,
  format       text,
  active       boolean     not null default true,
  created_at   timestamptz not null default now()
);

-- ─── reservations ───────────────────────────────────────────

create table reservations (
  id            uuid        primary key default gen_random_uuid(),
  user_id       uuid        not null references profiles(id) on delete cascade,
  provider_id   uuid        not null references providers(id) on delete cascade,
  service_id    uuid        not null references services(id) on delete cascade,
  scheduled_at  timestamptz not null,
  notes         text,
  status        statut_reservation not null default 'pending',
  created_at    timestamptz not null default now()
);

-- ─── subscriptions ──────────────────────────────────────────

create table subscriptions (
  id                      uuid        primary key default gen_random_uuid(),
  user_id                 uuid        not null references profiles(id) on delete cascade,
  plan                    plan_abonnement not null,
  status                  statut_abonnement not null default 'actif',
  stripe_subscription_id  text,
  activated_at            timestamptz not null default now(),
  updated_at              timestamptz not null default now()
);

-- ─── avis ───────────────────────────────────────────────────

create table avis (
  id              uuid        primary key default gen_random_uuid(),
  user_id         uuid        not null references profiles(id) on delete cascade,
  provider_id     uuid        not null references providers(id) on delete cascade,
  reservation_id  uuid        references reservations(id) on delete set null,
  note            smallint    not null check (note between 1 and 5),
  commentaire     text,
  statut          statut_avis not null default 'en_attente',
  created_at      timestamptz not null default now(),
  unique (user_id, reservation_id)
);

-- Mise à jour automatique de la note moyenne du prestataire
create or replace function update_provider_rating()
returns trigger as $$
begin
  update providers
  set
    rating        = (select round(avg(note)::numeric, 2) from avis where provider_id = coalesce(new.provider_id, old.provider_id) and statut = 'publie'),
    reviews_count = (select count(*) from avis where provider_id = coalesce(new.provider_id, old.provider_id) and statut = 'publie')
  where id = coalesce(new.provider_id, old.provider_id);
  return coalesce(new, old);
end;
$$ language plpgsql security definer;

create trigger on_avis_change
  after insert or update or delete on avis
  for each row execute procedure update_provider_rating();

-- ─── signalements ───────────────────────────────────────────

create table signalements (
  id           uuid        primary key default gen_random_uuid(),
  user_id      uuid        not null references profiles(id) on delete cascade,
  target_type  text        not null check (target_type in ('provider', 'avis', 'reservation')),
  target_id    uuid        not null,
  motif        text        not null,
  statut       statut_signalement not null default 'nouveau',
  created_at   timestamptz not null default now()
);

-- ─── Index ──────────────────────────────────────────────────

create index on providers (status, sector, city);
create index on services (provider_id, active);
create index on reservations (user_id, status);
create index on reservations (provider_id, scheduled_at);
create index on avis (provider_id, statut);
create index on subscriptions (user_id, status);
create index on signalements (statut);