-- ============================================================
-- 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);