-- ============================================================
-- Skeelera - Migration baseline v1
-- Generee depuis l historique timestamp? pour une installation neuve.
-- Les migrations timestamp?es restent conservees comme archive logique.
-- ============================================================

-- >>> BEGIN 20260418000001_init.sql
-- ============================================================
-- 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);
-- <<< END 20260418000001_init.sql

-- >>> BEGIN 20260418000002_fix_trigger.sql
-- Correction du trigger : ON CONFLICT DO NOTHING pour éviter l'échec si le profil existe déjà
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')
  on conflict (id) do nothing;
  return new;
end;
$$ language plpgsql security definer set search_path = public;
-- <<< END 20260418000002_fix_trigger.sql

-- >>> BEGIN 20260421000001_dev_sessions.sql
CREATE TABLE dev_sessions (
  id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  slug         TEXT        NOT NULL UNIQUE,
  key_hash     TEXT        NOT NULL,
  access_token TEXT,
  label        TEXT        NOT NULL DEFAULT 'Session dev',
  expires_at   TIMESTAMPTZ NOT NULL,
  created_by   TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_dev_sessions_slug        ON dev_sessions (slug);
CREATE INDEX idx_dev_sessions_token       ON dev_sessions (access_token) WHERE access_token IS NOT NULL;
CREATE INDEX idx_dev_sessions_expires_at  ON dev_sessions (expires_at);
-- <<< END 20260421000001_dev_sessions.sql

-- >>> BEGIN 20260421000002_dev_schema_helper.sql
CREATE OR REPLACE FUNCTION dev_schema_info()
RETURNS json
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT json_agg(t ORDER BY t.table_name)
  FROM (
    SELECT
      cols.table_name,
      json_agg(
        json_build_object(
          'name',     cols.column_name,
          'type',     cols.data_type,
          'nullable', (cols.is_nullable = 'YES'),
          'default',  cols.column_default
        )
        ORDER BY cols.ordinal_position
      ) AS columns
    FROM information_schema.columns AS cols
    JOIN information_schema.tables  AS tbls
      ON  tbls.table_name   = cols.table_name
      AND tbls.table_schema = cols.table_schema
    WHERE cols.table_schema = 'public'
      AND tbls.table_type   = 'BASE TABLE'
    GROUP BY cols.table_name
  ) t
$$;
-- <<< END 20260421000002_dev_schema_helper.sql

-- >>> BEGIN 20260421000003_schema_v2.sql
-- ============================================================
-- Skeelera — Schéma v2 (remplacement complet)
-- ============================================================

-- ─── Nettoyage de l'ancien schéma ───────────────────────────

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
DO $$
BEGIN
  IF to_regclass('public.avis') IS NOT NULL THEN
    DROP TRIGGER IF EXISTS on_avis_change ON avis;
  END IF;
END $$;
DROP FUNCTION IF EXISTS handle_new_user() CASCADE;
DROP FUNCTION IF EXISTS update_provider_rating() CASCADE;

DROP TABLE IF EXISTS reservation_enfants    CASCADE;
DROP TABLE IF EXISTS votes                  CASCADE;
DROP TABLE IF EXISTS enfants                CASCADE;
DROP TABLE IF EXISTS tuteurs                CASCADE;
DROP TABLE IF EXISTS creneaux               CASCADE;
DROP TABLE IF EXISTS agendas                CASCADE;
DROP TABLE IF EXISTS vitrines               CASCADE;
DROP TABLE IF EXISTS prestations            CASCADE;
DROP TABLE IF EXISTS prestataires           CASCADE;
DROP TABLE IF EXISTS abonnements            CASCADE;
DROP TABLE IF EXISTS entreprises            CASCADE;
DROP TABLE IF EXISTS utilisateurs           CASCADE;

DROP TABLE IF EXISTS signalements          CASCADE;
DROP TABLE IF EXISTS avis                  CASCADE;
DROP TABLE IF EXISTS subscriptions         CASCADE;
DROP TABLE IF EXISTS reservations          CASCADE;
DROP TABLE IF EXISTS services              CASCADE;
DROP TABLE IF EXISTS providers             CASCADE;
DROP TABLE IF EXISTS profiles              CASCADE;

DROP TYPE IF EXISTS role_utilisateur       CASCADE;
DROP TYPE IF EXISTS statut_compte          CASCADE;
DROP TYPE IF EXISTS statut_provider        CASCADE;
DROP TYPE IF EXISTS type_service           CASCADE;
DROP TYPE IF EXISTS statut_reservation     CASCADE;
DROP TYPE IF EXISTS statut_abonnement      CASCADE;
DROP TYPE IF EXISTS plan_abonnement        CASCADE;
DROP TYPE IF EXISTS statut_avis            CASCADE;
DROP TYPE IF EXISTS statut_signalement     CASCADE;
DROP TYPE IF EXISTS format_prestation_enum CASCADE;
DROP TYPE IF EXISTS type_prestation_enum   CASCADE;
DROP TYPE IF EXISTS statut_reservation_enum CASCADE;
DROP TYPE IF EXISTS statut_prestation_enum CASCADE;
DROP TYPE IF EXISTS statut_utilisateur_enum CASCADE;
DROP TYPE IF EXISTS role_enum              CASCADE;
DROP TYPE IF EXISTS duree_abonnement_enum  CASCADE;

-- ─── Nouveaux types énumérés ─────────────────────────────────

CREATE TYPE role_enum                 AS ENUM ('client', 'prestataire', 'admin', 'moderateur');
CREATE TYPE statut_utilisateur_enum   AS ENUM ('actif', 'suspendu', 'supprime', 'en_moderation');
CREATE TYPE statut_prestation_enum    AS ENUM ('active', 'inactive');
CREATE TYPE statut_reservation_enum   AS ENUM ('en_attente', 'confirmee', 'refusee', 'annulee_client', 'annulee_prestataire', 'effectuee');
CREATE TYPE type_prestation_enum      AS ENUM ('seance', 'cours_atelier', 'activite', 'evenement');
CREATE TYPE format_prestation_enum    AS ENUM ('domicile_client', 'domicile_prestataire', 'exterieur_public', 'salon_etablissement');
CREATE TYPE duree_abonnement_enum     AS ENUM ('jour', 'semaine', 'mois', 'annee');

-- ─── UTILISATEURS ────────────────────────────────────────────

CREATE TABLE utilisateurs (
  id               UUID        PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email            TEXT        NOT NULL,
  first_name       TEXT,
  last_name        TEXT,
  numero_tel       TEXT,
  roles            role_enum[] NOT NULL DEFAULT '{client}',
  photo            TEXT,
  statut           statut_utilisateur_enum NOT NULL DEFAULT 'actif',
  email_verifie    BOOLEAN     NOT NULL DEFAULT false,
  contrat_accepte  BOOLEAN     NOT NULL DEFAULT false,
  google_sub       TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── ENTREPRISES ─────────────────────────────────────────────

CREATE TABLE entreprises (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  nom         TEXT        NOT NULL,
  adresse     TEXT,
  code_postal TEXT,
  ville       TEXT,
  secteur     TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── ABONNEMENTS (catalogue des plans) ───────────────────────

CREATE TABLE abonnements (
  id                  UUID               PRIMARY KEY DEFAULT gen_random_uuid(),
  id_stripe           TEXT,
  nom                 TEXT               NOT NULL,
  prix                NUMERIC(10,2)      NOT NULL DEFAULT 0,
  recommande          BOOLEAN            NOT NULL DEFAULT false,
  avec_commission     BOOLEAN            NOT NULL DEFAULT false,
  percent_commission  NUMERIC(5,2)       NOT NULL DEFAULT 0,
  limite_reservations INTEGER,
  duree               duree_abonnement_enum NOT NULL DEFAULT 'mois',
  include             TEXT[],
  exclude             TEXT[],
  actif               BOOLEAN            NOT NULL DEFAULT true,
  data                JSONB,
  created_at          TIMESTAMPTZ        NOT NULL DEFAULT now()
);

-- ─── PRESTATAIRES ────────────────────────────────────────────

CREATE TABLE prestataires (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  utilisateur_id  UUID        REFERENCES utilisateurs(id) ON DELETE SET NULL,
  entreprise_id   UUID        REFERENCES entreprises(id) ON DELETE SET NULL,
  abonnement_id   UUID        REFERENCES abonnements(id) ON DELETE SET NULL,
  vitrine_id      UUID,       -- FK ajouté après création de vitrines
  description     TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── PRESTATIONS ─────────────────────────────────────────────

CREATE TABLE prestations (
  id              UUID                     PRIMARY KEY DEFAULT gen_random_uuid(),
  prestataire_id  UUID                     NOT NULL REFERENCES prestataires(id) ON DELETE CASCADE,
  titre           TEXT                     NOT NULL,
  type            type_prestation_enum     NOT NULL,
  description     TEXT,
  format          format_prestation_enum,
  duree_minutes   INTEGER                  NOT NULL,
  prix            NUMERIC(10,2)            NOT NULL,
  statut          statut_prestation_enum   NOT NULL DEFAULT 'active',
  created_at      TIMESTAMPTZ              NOT NULL DEFAULT now()
);

-- ─── AGENDAS ─────────────────────────────────────────────────

CREATE TABLE agendas (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  prestataire_id  UUID        NOT NULL REFERENCES prestataires(id) ON DELETE CASCADE,
  name            TEXT        NOT NULL,
  actif           BOOLEAN     NOT NULL DEFAULT true,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── CRENEAUX ────────────────────────────────────────────────

CREATE TABLE creneaux (
  id                              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  agenda_id                       UUID        NOT NULL REFERENCES agendas(id) ON DELETE CASCADE,
  name                            TEXT,
  number_semaine                  INTEGER,
  slots_semaine                   JSONB,
  indisponibilites_reservation    JSONB,
  indisponibilites_manuelles      JSONB,
  created_at                      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── VITRINES ────────────────────────────────────────────────

CREATE TABLE vitrines (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  prestataire_id  UUID        NOT NULL UNIQUE REFERENCES prestataires(id) ON DELETE CASCADE,
  photo           TEXT,
  type_entreprise TEXT,
  lieu            TEXT,
  ouverture       JSONB,
  nom_equipe      TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Résolution de la référence circulaire prestataires <-> vitrines
ALTER TABLE prestataires
  ADD CONSTRAINT fk_prestataires_vitrine
  FOREIGN KEY (vitrine_id) REFERENCES vitrines(id) ON DELETE SET NULL;

-- ─── TUTEURS ─────────────────────────────────────────────────

CREATE TABLE tuteurs (
  id                        UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  nom                       TEXT        NOT NULL,
  prenom                    TEXT        NOT NULL,
  genre                     TEXT,
  adresse                   TEXT,
  contact_urgence_nom       TEXT,
  contact_urgence_telephone TEXT,
  lien_avec_enfant          TEXT,
  telephone                 TEXT,
  contrat_accepte           BOOLEAN     NOT NULL DEFAULT false,
  created_at                TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── ENFANTS ─────────────────────────────────────────────────

CREATE TABLE enfants (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  nom             TEXT        NOT NULL,
  prenom          TEXT        NOT NULL,
  genre           TEXT,
  date_naissance  DATE,
  utilisateur_id  UUID        NOT NULL REFERENCES utilisateurs(id) ON DELETE CASCADE,
  tuteur_id       UUID        REFERENCES tuteurs(id) ON DELETE SET NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── RESERVATIONS ────────────────────────────────────────────

CREATE TABLE reservations (
  id                      UUID                      PRIMARY KEY DEFAULT gen_random_uuid(),
  utilisateur_id          UUID                      NOT NULL REFERENCES utilisateurs(id) ON DELETE CASCADE,
  prestataire_id          UUID                      NOT NULL REFERENCES prestataires(id) ON DELETE CASCADE,
  prestation_id           UUID                      NOT NULL REFERENCES prestations(id) ON DELETE CASCADE,
  date_reservation        TIMESTAMPTZ               NOT NULL,
  statut                  statut_reservation_enum   NOT NULL DEFAULT 'en_attente',
  validation_client       BOOLEAN                   NOT NULL DEFAULT false,
  validation_prestataire  BOOLEAN                   NOT NULL DEFAULT false,
  prix_final              NUMERIC(10,2),
  date_creation           TIMESTAMPTZ               NOT NULL DEFAULT now()
);

-- ─── RESERVATION_ENFANTS ─────────────────────────────────────

CREATE TABLE reservation_enfants (
  id                      UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  reservation_id          UUID        NOT NULL REFERENCES reservations(id) ON DELETE CASCADE,
  enfant_id               UUID        NOT NULL REFERENCES enfants(id) ON DELETE CASCADE,
  autorisation_parentale  BOOLEAN     NOT NULL DEFAULT false,
  commentaire             TEXT,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (reservation_id, enfant_id)
);

-- ─── VOTES ───────────────────────────────────────────────────

CREATE TABLE votes (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  utilisateur_id  UUID        NOT NULL REFERENCES utilisateurs(id) ON DELETE CASCADE,
  vitrine_id      UUID        NOT NULL REFERENCES vitrines(id) ON DELETE CASCADE,
  note            SMALLINT    NOT NULL CHECK (note BETWEEN 1 AND 5),
  commentaire     TEXT,
  publie          BOOLEAN     NOT NULL DEFAULT false,
  date_creation   TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (utilisateur_id, vitrine_id)
);

-- ─── Trigger : création automatique de l'utilisateur ─────────

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO utilisateurs (id, email, statut, roles)
  VALUES (NEW.id, NEW.email, 'actif', '{client}');
  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();

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

CREATE INDEX ON utilisateurs (email);
CREATE INDEX ON prestataires (utilisateur_id);
CREATE INDEX ON prestataires (entreprise_id);
CREATE INDEX ON prestations (prestataire_id, statut);
CREATE INDEX ON agendas (prestataire_id, actif);
CREATE INDEX ON creneaux (agenda_id);
CREATE INDEX ON reservations (utilisateur_id, statut);
CREATE INDEX ON reservations (prestataire_id, date_reservation);
CREATE INDEX ON reservation_enfants (reservation_id);
CREATE INDEX ON vitrines (prestataire_id);
CREATE INDEX ON votes (vitrine_id, publie);
CREATE INDEX ON abonnements (actif);
CREATE INDEX ON enfants (utilisateur_id);
-- <<< END 20260421000003_schema_v2.sql

-- >>> BEGIN 20260421000004_patch_trigger_entreprises.sql
-- ── Patch 1 : trigger handle_new_user avec cast explicite ────────────────────
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO utilisateurs (id, email, statut, roles)
  VALUES (
    NEW.id,
    NEW.email,
    'actif'::statut_utilisateur_enum,
    ARRAY['client'::role_enum]
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ── Patch 2 : colonnes manquantes sur entreprises ─────────────────────────────
ALTER TABLE entreprises
  ADD COLUMN IF NOT EXISTS siret             TEXT,
  ADD COLUMN IF NOT EXISTS statut_juridique  TEXT;
-- <<< END 20260421000004_patch_trigger_entreprises.sql

-- >>> BEGIN 20260421000005_abonnements_stripe_catalog.sql
ALTER TABLE abonnements
  ADD COLUMN IF NOT EXISTS stripe_product_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_price_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_lookup_key TEXT,
  ADD COLUMN IF NOT EXISTS stripe_currency TEXT NOT NULL DEFAULT 'eur',
  ADD COLUMN IF NOT EXISTS stripe_interval TEXT,
  ADD COLUMN IF NOT EXISTS stripe_interval_count INTEGER NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS stripe_active BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS stripe_product_data JSONB,
  ADD COLUMN IF NOT EXISTS stripe_price_data JSONB,
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT now();

UPDATE abonnements
SET stripe_price_id = COALESCE(stripe_price_id, id_stripe);

CREATE UNIQUE INDEX IF NOT EXISTS abonnements_stripe_product_id_idx
  ON abonnements (stripe_product_id)
  WHERE stripe_product_id IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS abonnements_stripe_price_id_idx
  ON abonnements (stripe_price_id)
  WHERE stripe_price_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS abonnements_stripe_lookup_key_idx
  ON abonnements (stripe_lookup_key)
  WHERE stripe_lookup_key IS NOT NULL;
-- <<< END 20260421000005_abonnements_stripe_catalog.sql

-- >>> BEGIN 20260421000006_fix_handle_new_user_search_path.sql
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO utilisateurs (id, email, statut, roles)
  VALUES (
    NEW.id,
    NEW.email,
    'actif'::statut_utilisateur_enum,
    ARRAY['client'::role_enum]
  )
  ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;
-- <<< END 20260421000006_fix_handle_new_user_search_path.sql

-- >>> BEGIN 20260421000007_prestataire_demandes.sql
CREATE TABLE IF NOT EXISTS prestataire_demandes (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  first_name          TEXT NOT NULL,
  last_name           TEXT NOT NULL,
  email               TEXT NOT NULL,
  password_hash       TEXT NOT NULL,
  numero_tel          TEXT NOT NULL,
  adresse             TEXT NOT NULL,
  date_naissance      DATE,
  sector              TEXT NOT NULL,
  lieu                TEXT NOT NULL,
  intervient_mineurs  BOOLEAN NOT NULL DEFAULT false,
  statut_juridique    TEXT NOT NULL,
  entreprise_nom      TEXT NOT NULL,
  siret               TEXT NOT NULL,
  assurance_confirmed BOOLEAN NOT NULL DEFAULT false,
  experience          TEXT NOT NULL,
  certif_1            BOOLEAN NOT NULL DEFAULT false,
  certif_2            BOOLEAN NOT NULL DEFAULT false,
  certif_3            BOOLEAN NOT NULL DEFAULT false,
  signature           TEXT NOT NULL,
  documents           JSONB NOT NULL DEFAULT '{}'::jsonb,
  status              TEXT NOT NULL DEFAULT 'pending',
  review_note         TEXT,
  reviewed_at         TIMESTAMPTZ,
  reviewed_by         UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  approved_user_id    UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  approved_provider_id UUID REFERENCES prestataires(id) ON DELETE SET NULL,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT prestataire_demandes_email_status_check CHECK (status IN ('pending', 'approved', 'rejected'))
);

CREATE INDEX IF NOT EXISTS prestataire_demandes_status_idx ON prestataire_demandes (status, created_at DESC);
CREATE INDEX IF NOT EXISTS prestataire_demandes_email_idx ON prestataire_demandes (email);
-- <<< END 20260421000007_prestataire_demandes.sql

-- >>> BEGIN 20260422000008_drop_prestataires_vitrine_id.sql
DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.table_constraints
    WHERE table_name = 'prestataires'
      AND constraint_name = 'fk_prestataires_vitrine'
  ) THEN
    ALTER TABLE prestataires DROP CONSTRAINT fk_prestataires_vitrine;
  END IF;
END $$;

ALTER TABLE prestataires
  DROP COLUMN IF EXISTS vitrine_id;
-- <<< END 20260422000008_drop_prestataires_vitrine_id.sql

-- >>> BEGIN 20260422000009_link_agendas_to_prestations.sql
ALTER TABLE agendas
ADD COLUMN IF NOT EXISTS prestation_id UUID REFERENCES prestations(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS agendas_prestation_id_idx ON agendas (prestation_id);
-- <<< END 20260422000009_link_agendas_to_prestations.sql

-- >>> BEGIN 20260424000010_address_cache_and_coordinates.sql
ALTER TABLE entreprises
  ADD COLUMN IF NOT EXISTS latitude DOUBLE PRECISION,
  ADD COLUMN IF NOT EXISTS longitude DOUBLE PRECISION;

ALTER TABLE prestataire_demandes
  ADD COLUMN IF NOT EXISTS code_postal TEXT,
  ADD COLUMN IF NOT EXISTS ville TEXT,
  ADD COLUMN IF NOT EXISTS latitude DOUBLE PRECISION,
  ADD COLUMN IF NOT EXISTS longitude DOUBLE PRECISION;

CREATE TABLE IF NOT EXISTS adresse_cache_requetes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  query_key TEXT NOT NULL UNIQUE,
  query_text TEXT NOT NULL,
  code_postal TEXT,
  ville TEXT,
  limit_value INTEGER NOT NULL DEFAULT 8,
  response_json JSONB NOT NULL DEFAULT '[]'::jsonb,
  source TEXT NOT NULL DEFAULT 'ign_geoplateforme',
  last_fetched_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS adresses_reference (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source TEXT NOT NULL DEFAULT 'ign_geoplateforme',
  source_id TEXT NOT NULL,
  label TEXT NOT NULL,
  adresse TEXT,
  code_postal TEXT,
  ville TEXT,
  code_insee TEXT,
  rue TEXT,
  numero TEXT,
  contexte TEXT,
  latitude DOUBLE PRECISION,
  longitude DOUBLE PRECISION,
  search_text TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT adresses_reference_source_unique UNIQUE (source, source_id)
);

CREATE INDEX IF NOT EXISTS adresse_cache_requetes_fetched_idx
  ON adresse_cache_requetes (last_fetched_at DESC);

CREATE INDEX IF NOT EXISTS adresses_reference_code_postal_idx
  ON adresses_reference (code_postal);

CREATE INDEX IF NOT EXISTS adresses_reference_ville_idx
  ON adresses_reference (ville);
-- <<< END 20260424000010_address_cache_and_coordinates.sql

-- >>> BEGIN 20260424000011_route_cache.sql
CREATE TABLE IF NOT EXISTS itineraire_cache_requetes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  route_key TEXT NOT NULL UNIQUE,
  mode TEXT NOT NULL DEFAULT 'car',
  start_latitude DOUBLE PRECISION NOT NULL,
  start_longitude DOUBLE PRECISION NOT NULL,
  end_latitude DOUBLE PRECISION NOT NULL,
  end_longitude DOUBLE PRECISION NOT NULL,
  response_json JSONB NOT NULL DEFAULT '{}'::jsonb,
  source TEXT NOT NULL DEFAULT 'ign_geoplateforme',
  last_fetched_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS itineraire_cache_requetes_fetched_idx
  ON itineraire_cache_requetes (last_fetched_at DESC);
-- <<< END 20260424000011_route_cache.sql

-- >>> BEGIN 20260424000012_provider_request_plan_payment.sql
ALTER TABLE prestataire_demandes
  ADD COLUMN IF NOT EXISTS plan_id UUID REFERENCES abonnements(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS stripe_customer_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_payment_method_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_setup_intent_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_setup_session_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_subscription_id TEXT,
  ADD COLUMN IF NOT EXISTS payment_setup_status TEXT NOT NULL DEFAULT 'not_required',
  ADD COLUMN IF NOT EXISTS payment_setup_confirmed_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS payment_charge_status TEXT NOT NULL DEFAULT 'not_required';

ALTER TABLE prestataire_demandes
  DROP CONSTRAINT IF EXISTS prestataire_demandes_payment_setup_status_check;

ALTER TABLE prestataire_demandes
  ADD CONSTRAINT prestataire_demandes_payment_setup_status_check
  CHECK (payment_setup_status IN ('not_required', 'pending', 'authorized', 'cancelled', 'failed'));

ALTER TABLE prestataire_demandes
  DROP CONSTRAINT IF EXISTS prestataire_demandes_payment_charge_status_check;

ALTER TABLE prestataire_demandes
  ADD CONSTRAINT prestataire_demandes_payment_charge_status_check
  CHECK (payment_charge_status IN ('not_required', 'pending_approval', 'succeeded', 'failed'));

CREATE INDEX IF NOT EXISTS prestataire_demandes_plan_id_idx
  ON prestataire_demandes (plan_id);
-- <<< END 20260424000012_provider_request_plan_payment.sql

-- >>> BEGIN 20260425000013_votes_contracts.sql
ALTER TABLE votes
  ADD COLUMN IF NOT EXISTS filter_flagged BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS filter_matches TEXT[] DEFAULT '{}'::text[],
  ADD COLUMN IF NOT EXISTS provider_response TEXT,
  ADD COLUMN IF NOT EXISTS provider_flag_reason TEXT,
  ADD COLUMN IF NOT EXISTS provider_flagged_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS moderation_note TEXT,
  ADD COLUMN IF NOT EXISTS moderated_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS moderated_by UUID REFERENCES utilisateurs(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS votes_filter_flagged_idx ON votes (filter_flagged, date_creation DESC);
CREATE INDEX IF NOT EXISTS votes_provider_flagged_idx ON votes (provider_flagged_at DESC);

CREATE TABLE IF NOT EXISTS contrats (
  id                     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  utilisateur_id         UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  prestataire_id         UUID REFERENCES prestataires(id) ON DELETE SET NULL,
  prestataire_demande_id UUID REFERENCES prestataire_demandes(id) ON DELETE SET NULL,
  titre                  TEXT NOT NULL,
  type_contrat           TEXT NOT NULL DEFAULT 'partenariat',
  statut                 TEXT NOT NULL DEFAULT 'draft',
  fichier_url            TEXT,
  notes                  TEXT,
  signature_name         TEXT,
  signed_at              TIMESTAMPTZ,
  created_by             UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  updated_by             UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  created_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT contrats_statut_check CHECK (statut IN ('draft', 'sent', 'signed', 'archived'))
);

CREATE INDEX IF NOT EXISTS contrats_prestataire_idx ON contrats (prestataire_id, created_at DESC);
CREATE INDEX IF NOT EXISTS contrats_utilisateur_idx ON contrats (utilisateur_id, created_at DESC);
CREATE INDEX IF NOT EXISTS contrats_statut_idx ON contrats (statut, created_at DESC);
-- <<< END 20260425000013_votes_contracts.sql

-- >>> BEGIN 20260425000014_admin_security.sql
-- Colonnes sécurité compte admin : TOTP 2FA + clé de sauvegarde
ALTER TABLE utilisateurs
  ADD COLUMN IF NOT EXISTS totp_secret     TEXT,
  ADD COLUMN IF NOT EXISTS backup_key_hash TEXT;
-- <<< END 20260425000014_admin_security.sql

-- >>> BEGIN 20260425000014_signalements_contact.sql
-- Signalements (rapports d'abus) -------------------------------------------
CREATE TABLE IF NOT EXISTS signalements (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type         TEXT NOT NULL CHECK (type IN ('vitrine', 'avis', 'reservation', 'autre')),
  cible_id     UUID NOT NULL,
  raison       TEXT NOT NULL CHECK (raison IN ('contenu_inapproprie', 'arnaque', 'comportement_abusif', 'informations_fausses', 'autre')),
  description  TEXT,
  signale_par  UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  statut       TEXT NOT NULL DEFAULT 'nouveau' CHECK (statut IN ('nouveau', 'en_cours', 'resolu', 'rejete')),
  note_admin   TEXT,
  traite_par   UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  traite_le    TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_signalements_statut ON signalements(statut);
CREATE INDEX IF NOT EXISTS idx_signalements_type   ON signalements(type);

-- Messages de contact (support/SAV) ----------------------------------------
CREATE TABLE IF NOT EXISTS messages_contact (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  nom          TEXT NOT NULL,
  email        TEXT NOT NULL,
  sujet        TEXT NOT NULL CHECK (sujet IN ('bug', 'question', 'reclamation', 'partenariat', 'autre')),
  message      TEXT NOT NULL,
  statut       TEXT NOT NULL DEFAULT 'nouveau' CHECK (statut IN ('nouveau', 'traite', 'ferme')),
  note_admin   TEXT,
  traite_par   UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  traite_le    TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_messages_contact_statut ON messages_contact(statut);
-- <<< END 20260425000014_signalements_contact.sql

-- >>> BEGIN 20260425000014_vote_comment_visibility.sql
ALTER TABLE votes
  ADD COLUMN IF NOT EXISTS commentaire_publie BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS commentaire_valide_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS commentaire_valide_by UUID REFERENCES utilisateurs(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS votes_commentaire_publie_idx ON votes (commentaire_publie, date_creation DESC);
-- <<< END 20260425000014_vote_comment_visibility.sql

-- >>> BEGIN 20260425000015_vitrine_comment_publication_mode.sql
ALTER TABLE vitrines
  ADD COLUMN IF NOT EXISTS commentaire_publication_mode TEXT NOT NULL DEFAULT 'manual',
  ADD CONSTRAINT vitrines_commentaire_publication_mode_check
    CHECK (commentaire_publication_mode IN ('manual', 'auto'));

UPDATE vitrines
SET commentaire_publication_mode = 'manual'
WHERE commentaire_publication_mode IS NULL OR commentaire_publication_mode NOT IN ('manual', 'auto');
-- <<< END 20260425000015_vitrine_comment_publication_mode.sql

-- >>> BEGIN 20260425000016_subscription_stripe_fields.sql
-- Stocke l'ID abonnement Stripe et la date de début pour la gestion des résiliations
ALTER TABLE prestataires
  ADD COLUMN IF NOT EXISTS stripe_subscription_id TEXT,
  ADD COLUMN IF NOT EXISTS abonnement_started_at TIMESTAMPTZ;
-- <<< END 20260425000016_subscription_stripe_fields.sql

-- Droits d'acces pour le role service_role (lecture/ecriture applicative)
grant usage on schema public to service_role;
grant select, insert, update, delete on all tables in schema public to service_role;
grant select, insert, update, delete on table public.utilisateurs to service_role;
grant usage, select on all sequences in schema public to service_role;

alter default privileges in schema public
  grant select, insert, update, delete on tables to service_role;

alter default privileges in schema public
  grant usage, select on sequences to service_role;