AdminGit
36b0c00cbc
-> Refonte du système de login -> Récupération du MDP -> Refonte imports d'horaires + Modale de gestion d'horaires
1045 lines
52 KiB
PL/PgSQL
1045 lines
52 KiB
PL/PgSQL
-- ============================================================================
|
|
-- FERROVIA PANEL — SCHÉMA SQL COMPLET
|
|
-- Généré le 2026-08-25 par analyse exhaustive de l'application
|
|
-- ============================================================================
|
|
-- Ce fichier contient TOUT le SQL nécessaire pour recréer la base de données.
|
|
-- Il est conçu pour être exécuté sur une instance Supabase (self-hosted ou cloud).
|
|
-- Ordre d'exécution : Extensions → Types → Fonctions → Tables → Séquences →
|
|
-- Contraintes → Index → Triggers → RLS → Grants
|
|
-- ============================================================================
|
|
|
|
-- ============================================================================
|
|
-- 1. EXTENSIONS
|
|
-- ============================================================================
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- ============================================================================
|
|
-- 2. TYPES ENUM
|
|
-- ============================================================================
|
|
DO $$ BEGIN
|
|
CREATE TYPE "public"."service_type" AS ENUM ('TER','TGV','Intercités','Fret');
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE "public"."station_type" AS ENUM ('interurbaine','ville');
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE "public"."transport_type" AS ENUM ('bus','tram','metro','tram-train','train');
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ============================================================================
|
|
-- 3. FONCTIONS UTILITAIRES (triggers)
|
|
-- ============================================================================
|
|
CREATE OR REPLACE FUNCTION "public"."handle_updated_at"() RETURNS trigger
|
|
LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$;
|
|
|
|
CREATE OR REPLACE FUNCTION "public"."set_updated_at"() RETURNS trigger
|
|
LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;
|
|
|
|
CREATE OR REPLACE FUNCTION "public"."update_updated_at_column"() RETURNS trigger
|
|
LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$;
|
|
|
|
CREATE OR REPLACE FUNCTION "public"."update_travaux_updated_at"() RETURNS trigger
|
|
LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;
|
|
|
|
CREATE OR REPLACE FUNCTION "public"."update_utilisateurs_updated_at"() RETURNS trigger
|
|
LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$;
|
|
|
|
-- ============================================================================
|
|
-- 4. FONCTION D'AUDIT
|
|
-- ============================================================================
|
|
CREATE OR REPLACE FUNCTION "public"."log_audit"() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER AS $$
|
|
DECLARE
|
|
v_changed_by_text text;
|
|
v_changed_by uuid;
|
|
BEGIN
|
|
v_changed_by_text := current_setting('jwt.claims.user_id', true);
|
|
IF v_changed_by_text IS NOT NULL AND v_changed_by_text <> '' THEN
|
|
BEGIN v_changed_by := v_changed_by_text::uuid;
|
|
EXCEPTION WHEN others THEN v_changed_by := NULL; END;
|
|
ELSE v_changed_by := NULL;
|
|
END IF;
|
|
IF (TG_OP = 'INSERT') THEN
|
|
INSERT INTO public.audit_logs(table_name, operation, record_id, changed_by, new_data, changed_at)
|
|
VALUES (TG_TABLE_NAME, 'INSERT', NEW.id, v_changed_by, row_to_json(NEW)::jsonb, now());
|
|
RETURN NEW;
|
|
ELSIF (TG_OP = 'UPDATE') THEN
|
|
INSERT INTO public.audit_logs(table_name, operation, record_id, changed_by, old_data, new_data, changed_at)
|
|
VALUES (TG_TABLE_NAME, 'UPDATE', NEW.id, v_changed_by, row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb, now());
|
|
RETURN NEW;
|
|
ELSIF (TG_OP = 'DELETE') THEN
|
|
INSERT INTO public.audit_logs(table_name, operation, record_id, changed_by, old_data, changed_at)
|
|
VALUES (TG_TABLE_NAME, 'DELETE', OLD.id, v_changed_by, row_to_json(OLD)::jsonb, now());
|
|
RETURN OLD;
|
|
END IF;
|
|
RETURN NULL;
|
|
END; $$;
|
|
|
|
-- ============================================================================
|
|
-- 5. FONCTION STUB GTFS
|
|
-- ============================================================================
|
|
CREATE OR REPLACE FUNCTION "public"."import_gtfs_rpc"(payload json) RETURNS json
|
|
LANGUAGE plpgsql AS $$
|
|
BEGIN RETURN json_build_object('status', 'ok', 'created', 0, 'warnings', json_build_array());
|
|
EXCEPTION WHEN OTHERS THEN RETURN json_build_object('status', 'error', 'message', SQLERRM);
|
|
END; $$;
|
|
|
|
-- ============================================================================
|
|
-- 6. TABLES (ordonnées par dépendances)
|
|
-- ============================================================================
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.01 audit_logs
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."audit_logs" (
|
|
"id" bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
"table_name" text NOT NULL,
|
|
"operation" text NOT NULL,
|
|
"record_id" uuid,
|
|
"changed_by" uuid,
|
|
"old_data" jsonb,
|
|
"new_data" jsonb,
|
|
"changed_at" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.02 utilisateurs
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."utilisateurs" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"email" varchar(255) NOT NULL UNIQUE,
|
|
"nom" varchar(100) NOT NULL,
|
|
"prenom" varchar(100) NOT NULL,
|
|
"role" varchar(50) NOT NULL DEFAULT 'utilisateur',
|
|
"actif" boolean NOT NULL DEFAULT true,
|
|
"password_hash" varchar(255),
|
|
"photo_url" text,
|
|
"region_admin" boolean DEFAULT false,
|
|
"region_id" varchar(100),
|
|
"region_nom" varchar(255),
|
|
"permissions" jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now()
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "utilisateurs_email_lower_idx" ON "public"."utilisateurs" (lower(email::text));
|
|
CREATE INDEX IF NOT EXISTS "idx_utilisateurs_email" ON "public"."utilisateurs" ("email");
|
|
CREATE INDEX IF NOT EXISTS "idx_utilisateurs_role" ON "public"."utilisateurs" ("role");
|
|
CREATE INDEX IF NOT EXISTS "idx_utilisateurs_actif" ON "public"."utilisateurs" ("actif");
|
|
CREATE INDEX IF NOT EXISTS "idx_utilisateurs_region_id" ON "public"."utilisateurs" ("region_id");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.03 gares
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."gares" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"nom" text NOT NULL,
|
|
"type" varchar(50) NOT NULL DEFAULT 'ville',
|
|
"services" "public"."service_type"[] NOT NULL DEFAULT '{}'::service_type[],
|
|
"informations" jsonb DEFAULT '{}'::jsonb,
|
|
"quais_hall" jsonb DEFAULT '[]'::jsonb,
|
|
"created_at" timestamptz NOT NULL DEFAULT now(),
|
|
"updated_at" timestamptz NOT NULL DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.04 gare_quais
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."gare_quais" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"gare_id" uuid NOT NULL REFERENCES "public"."gares"("id") ON DELETE CASCADE,
|
|
"nom" text NOT NULL,
|
|
"distance_metres" integer NOT NULL DEFAULT 0,
|
|
"created_at" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_gare_quais_gare_id" ON "public"."gare_quais" ("gare_id");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.05 gare_transports
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."gare_transports" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"gare_id" uuid NOT NULL REFERENCES "public"."gares"("id") ON DELETE CASCADE,
|
|
"type" "public"."transport_type" NOT NULL,
|
|
"ligne" text,
|
|
"created_at" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_gare_transports_gare_id" ON "public"."gare_transports" ("gare_id");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.06 types_train
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."types_train" (
|
|
"id" bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
"nom" varchar(255) NOT NULL,
|
|
"code" varchar(50),
|
|
"description" text,
|
|
"logo_url" text,
|
|
"logo_color_url" text,
|
|
"couleur" varchar(20) DEFAULT '#0074d9',
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_types_train_code" ON "public"."types_train" ("code");
|
|
CREATE INDEX IF NOT EXISTS "idx_types_train_deleted_at" ON "public"."types_train" ("deleted_at");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.07 materiel-roulant
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."materiel-roulant" (
|
|
"id" bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
"nom" text,
|
|
"nom_technique" text,
|
|
"capacite" integer,
|
|
"image_url" text,
|
|
"type_train" text,
|
|
"numero_serie" text UNIQUE,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz
|
|
);
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.08 services_annuels
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."services_annuels" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"nom" varchar(255) NOT NULL,
|
|
"code" varchar(50),
|
|
"date_debut" date NOT NULL,
|
|
"date_fin" date NOT NULL,
|
|
"description" text,
|
|
"actif" boolean DEFAULT true,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_services_annuels_actif" ON "public"."services_annuels" ("actif") WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS "idx_services_annuels_dates" ON "public"."services_annuels" ("date_debut","date_fin");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.09 lignes
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."lignes" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"nom" varchar(255) NOT NULL,
|
|
"code" varchar(50),
|
|
"gare_depart_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"gare_arrivee_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"actif" boolean DEFAULT true,
|
|
"fh_url" text,
|
|
"region" varchar(100),
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_lignes_actif" ON "public"."lignes" ("actif") WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS "idx_lignes_fh_url" ON "public"."lignes" ("fh_url");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.10 lignes_gares
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."lignes_gares" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"ligne_id" uuid REFERENCES "public"."lignes"("id") ON DELETE CASCADE,
|
|
"gare_id" uuid REFERENCES "public"."gares"("id") ON DELETE CASCADE,
|
|
"ordre" integer NOT NULL,
|
|
"desserte" varchar(20) NOT NULL CHECK (desserte IN ('tous','partielle')),
|
|
"created_at" timestamptz DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_lignes_gares_ligne" ON "public"."lignes_gares" ("ligne_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_lignes_gares_ordre" ON "public"."lignes_gares" ("ligne_id","ordre");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.11 horaires (Table unique et consolidée)
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."horaires" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"numero_train" varchar(50) NOT NULL,
|
|
"nom_train" varchar(255),
|
|
"code_mission" varchar(50),
|
|
"sens" varchar(20) DEFAULT 'aller',
|
|
"type_train" varchar(100),
|
|
"type_train_id" bigint REFERENCES "public"."types_train"("id") ON DELETE SET NULL,
|
|
"transporteur" varchar(100) DEFAULT 'SNCF Voyageurs',
|
|
"gare_depart_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"gare_depart_nom" varchar(255) NOT NULL,
|
|
"heure_depart" time,
|
|
"gare_arrivee_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"gare_arrivee_nom" varchar(255) NOT NULL,
|
|
"heure_arrivee" time,
|
|
"gares_desservies" jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
"attributions_quais" jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
"circule_lundi" boolean DEFAULT true,
|
|
"circule_mardi" boolean DEFAULT true,
|
|
"circule_mercredi" boolean DEFAULT true,
|
|
"circule_jeudi" boolean DEFAULT true,
|
|
"circule_vendredi" boolean DEFAULT true,
|
|
"circule_samedi" boolean DEFAULT true,
|
|
"circule_dimanche" boolean DEFAULT true,
|
|
"circule_jours_feries" boolean DEFAULT false,
|
|
"circule_dimanches_feries" boolean DEFAULT false,
|
|
"date_debut" timestamptz,
|
|
"date_fin" timestamptz,
|
|
"jours_personnalises" jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
"jours_non_circulation" jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
"calendrier_circulation" jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
"periode_validite" jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
"materiel_roulant_id" bigint REFERENCES "public"."materiel-roulant"("id") ON DELETE SET NULL,
|
|
"composition_train" jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
"ligne_id" uuid REFERENCES "public"."lignes"("id") ON DELETE SET NULL,
|
|
"ligne_nom" varchar(255),
|
|
"region_id" varchar(100),
|
|
"region" varchar(255),
|
|
"service_annuel_id" uuid REFERENCES "public"."services_annuels"("id") ON DELETE SET NULL,
|
|
"service_annuel_nom" varchar(255),
|
|
"service_type" varchar(50) DEFAULT 'TER',
|
|
"est_substitution" boolean NOT NULL DEFAULT false,
|
|
"motif_substitution" varchar(255),
|
|
"substitution_disponible" boolean NOT NULL DEFAULT false,
|
|
"horaire_id" uuid REFERENCES "public"."horaires"("id") ON DELETE SET NULL,
|
|
"substitutions_associees" jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
"sillon_id" text,
|
|
"tranche" varchar(50),
|
|
"gestionnaire_infrastructure" varchar(100) DEFAULT 'SNCF Réseau',
|
|
"vitesse_max_kmh" integer,
|
|
"statut" varchar(50) NOT NULL DEFAULT 'a_l_heure',
|
|
"retard_minutes" integer NOT NULL DEFAULT 0,
|
|
"retard_motif" text,
|
|
"informations_voyageurs" text,
|
|
"actif" boolean NOT NULL DEFAULT true,
|
|
"metadata" jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
"notes" text,
|
|
"created_at" timestamptz NOT NULL DEFAULT now(),
|
|
"updated_at" timestamptz NOT NULL DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
|
|
-- Index haute performance B-Tree et GIN
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_numero_train" ON "public"."horaires" ("numero_train");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_gare_depart" ON "public"."horaires" ("gare_depart_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_gare_arrivee" ON "public"."horaires" ("gare_arrivee_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_departs" ON "public"."horaires" ("gare_depart_id", "heure_depart");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_arrivees" ON "public"."horaires" ("gare_arrivee_id", "heure_arrivee");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_actif" ON "public"."horaires" ("actif") WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_type_train" ON "public"."horaires" ("type_train_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_materiel" ON "public"."horaires" ("materiel_roulant_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_ligne" ON "public"."horaires" ("ligne_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_service_annuel" ON "public"."horaires" ("service_annuel_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_substitution" ON "public"."horaires" ("est_substitution");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_region_id" ON "public"."horaires" ("region_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_dates" ON "public"."horaires" ("date_debut", "date_fin");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_statut" ON "public"."horaires" ("statut");
|
|
|
|
-- Index GIN JSONB pour recherche et filtrage ultra-rapide sans jointure
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_gares_desservies_gin" ON "public"."horaires" USING gin ("gares_desservies" jsonb_path_ops);
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_attributions_quais_gin" ON "public"."horaires" USING gin ("attributions_quais");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_calendrier_gin" ON "public"."horaires" USING gin ("calendrier_circulation" jsonb_path_ops);
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_metadata_gin" ON "public"."horaires" USING gin ("metadata");
|
|
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.12 horaires_gares_desservies
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."horaires_gares_desservies" (
|
|
"id" bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
"horaire_id" uuid REFERENCES "public"."horaires"("id") ON DELETE CASCADE,
|
|
"gare_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"gare_nom" varchar(255) NOT NULL,
|
|
"heure_arrivee" time,
|
|
"heure_depart" time,
|
|
"ordre" integer NOT NULL DEFAULT 0,
|
|
"quai" varchar(50),
|
|
"quai_id" uuid REFERENCES "public"."gare_quais"("id"),
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_gares_desservies_horaire" ON "public"."horaires_gares_desservies" ("horaire_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_gares_desservies_ordre" ON "public"."horaires_gares_desservies" ("horaire_id","ordre");
|
|
CREATE INDEX IF NOT EXISTS "idx_hgd_gare_id" ON "public"."horaires_gares_desservies" ("gare_id");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.13 horaires_substitutions (utilisé par l'API mais absent du dump)
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."horaires_substitutions" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"horaire_id" uuid REFERENCES "public"."horaires"("id") ON DELETE SET NULL,
|
|
"region_id" varchar(100),
|
|
"region" varchar(255),
|
|
"numero_train" varchar(50),
|
|
"type_train" varchar(100),
|
|
"type_train_id" bigint,
|
|
"gare_depart_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"gare_depart_nom" varchar(255),
|
|
"heure_depart" time,
|
|
"gare_arrivee_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"gare_arrivee_nom" varchar(255),
|
|
"heure_arrivee" time,
|
|
"date_debut" timestamptz,
|
|
"date_fin" timestamptz,
|
|
"circule_lundi" boolean DEFAULT true,
|
|
"circule_mardi" boolean DEFAULT true,
|
|
"circule_mercredi" boolean DEFAULT true,
|
|
"circule_jeudi" boolean DEFAULT true,
|
|
"circule_vendredi" boolean DEFAULT true,
|
|
"circule_samedi" boolean DEFAULT true,
|
|
"circule_dimanche" boolean DEFAULT true,
|
|
"circule_jours_feries" boolean DEFAULT false,
|
|
"jours_personnalises" jsonb DEFAULT '[]'::jsonb,
|
|
"jours_non_circulation" jsonb DEFAULT '[]'::jsonb,
|
|
"materiel_roulant_id" bigint,
|
|
"ligne_id" uuid REFERENCES "public"."lignes"("id") ON DELETE SET NULL,
|
|
"ligne_nom" varchar(255),
|
|
"service_annuel_id" uuid REFERENCES "public"."services_annuels"("id") ON DELETE SET NULL,
|
|
"service_annuel_nom" varchar(255),
|
|
"est_substitution" boolean DEFAULT true,
|
|
"motif_substitution" varchar(255),
|
|
"gares_desservies" jsonb DEFAULT '[]'::jsonb,
|
|
"composition_train" jsonb DEFAULT '[]'::jsonb,
|
|
"actif" boolean DEFAULT true,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_substitutions_region" ON "public"."horaires_substitutions" ("region_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_substitutions_horaire" ON "public"."horaires_substitutions" ("horaire_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_horaires_substitutions_actif" ON "public"."horaires_substitutions" ("actif") WHERE deleted_at IS NULL;
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.14 perturbations
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."perturbations" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"horaire_id" uuid NOT NULL REFERENCES "public"."horaires"("id") ON DELETE CASCADE,
|
|
"type_perturbation" varchar(50) NOT NULL CHECK (type_perturbation IN ('retard','suppression','modification_parcours')),
|
|
"duree_retard_minutes" integer,
|
|
"cause" varchar(100) NOT NULL CHECK (cause IN ('incident_technique','accident_personne','conditions_meteorologiques','travaux','mouvement_social','intervention_police','affluence','malveillance','autre')),
|
|
"cause_details" text,
|
|
"sillon_substitution_id" uuid REFERENCES "public"."horaires"("id") ON DELETE SET NULL,
|
|
"date_debut" timestamptz NOT NULL DEFAULT now(),
|
|
"date_fin" timestamptz,
|
|
"actif" boolean DEFAULT true,
|
|
"notes" text,
|
|
"region_id" varchar(100),
|
|
"created_by" uuid,
|
|
"perturbations_arrets" jsonb,
|
|
"jours_perturbation" jsonb,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_perturbations_horaire" ON "public"."perturbations" ("horaire_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_perturbations_type" ON "public"."perturbations" ("type_perturbation");
|
|
CREATE INDEX IF NOT EXISTS "idx_perturbations_actif" ON "public"."perturbations" ("actif") WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS "idx_perturbations_dates" ON "public"."perturbations" ("date_debut","date_fin");
|
|
CREATE INDEX IF NOT EXISTS "idx_perturbations_sillon_substitution" ON "public"."perturbations" ("sillon_substitution_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_perturbations_region_id" ON "public"."perturbations" ("region_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_perturbations_created_by" ON "public"."perturbations" ("created_by");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.15 perturbations_modifications_parcours
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."perturbations_modifications_parcours" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"perturbation_id" uuid NOT NULL REFERENCES "public"."perturbations"("id") ON DELETE CASCADE,
|
|
"type_modification" varchar(20) NOT NULL CHECK (type_modification IN ('gare_supprimee','gare_ajoutee','horaire_modifie')),
|
|
"gare_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"gare_nom" varchar(255) NOT NULL,
|
|
"nouvelle_heure_arrivee" time,
|
|
"nouvelle_heure_depart" time,
|
|
"ordre" integer,
|
|
"created_at" timestamptz DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_modif_parcours_perturbation" ON "public"."perturbations_modifications_parcours" ("perturbation_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_modif_parcours_gare" ON "public"."perturbations_modifications_parcours" ("gare_id");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.16 info_trafic
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."info_trafic" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"titre" varchar(255) NOT NULL,
|
|
"description" text,
|
|
"type" varchar(50) NOT NULL CHECK (type IN ('perturbation','travaux','greve','meteo','autre')),
|
|
"severite" varchar(20) NOT NULL CHECK (severite IN ('info','warning','critical')),
|
|
"ligne" varchar(100),
|
|
"gare_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"date_debut" timestamptz NOT NULL DEFAULT now(),
|
|
"date_fin" timestamptz,
|
|
"actif" boolean DEFAULT true,
|
|
"region_id" varchar(100),
|
|
"created_by" uuid,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_info_trafic_actif" ON "public"."info_trafic" ("actif") WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS "idx_info_trafic_dates" ON "public"."info_trafic" ("date_debut","date_fin");
|
|
CREATE INDEX IF NOT EXISTS "idx_info_trafic_region_id" ON "public"."info_trafic" ("region_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_info_trafic_created_by" ON "public"."info_trafic" ("created_by");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.17 evenements
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."evenements" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"titre" varchar(255) NOT NULL,
|
|
"description" text,
|
|
"lieu" varchar(255),
|
|
"gare_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"date_debut" timestamptz NOT NULL,
|
|
"date_fin" timestamptz,
|
|
"image_url" varchar(500),
|
|
"lien_externe" varchar(500),
|
|
"actif" boolean DEFAULT true,
|
|
"visible_site" boolean DEFAULT true,
|
|
"region_id" varchar(100),
|
|
"created_by" uuid,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_evenements_actif" ON "public"."evenements" ("actif") WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS "idx_evenements_dates" ON "public"."evenements" ("date_debut","date_fin");
|
|
CREATE INDEX IF NOT EXISTS "idx_evenements_region_id" ON "public"."evenements" ("region_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_evenements_created_by" ON "public"."evenements" ("created_by");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.18 actualites
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."actualites" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"titre" varchar(255) NOT NULL,
|
|
"contenu" text NOT NULL,
|
|
"resume" varchar(500),
|
|
"categorie" varchar(50) CHECK (categorie IN ('general','service','promotion','partenariat','autre')),
|
|
"image_url" varchar(500),
|
|
"image_couverture" text,
|
|
"date_publication" timestamptz NOT NULL DEFAULT now(),
|
|
"auteur" varchar(100),
|
|
"actif" boolean DEFAULT true,
|
|
"mise_en_avant" boolean DEFAULT false,
|
|
"region_id" varchar(100),
|
|
"created_by" uuid,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_actualites_actif" ON "public"."actualites" ("actif") WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS "idx_actualites_publication" ON "public"."actualites" ("date_publication");
|
|
CREATE INDEX IF NOT EXISTS "idx_actualites_region_id" ON "public"."actualites" ("region_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_actualites_created_by" ON "public"."actualites" ("created_by");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.19 travaux
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."travaux" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"titre" text NOT NULL,
|
|
"description" text,
|
|
"date_debut" date NOT NULL,
|
|
"date_fin" date NOT NULL,
|
|
"jours_travaux" text[] NOT NULL,
|
|
"heure_debut" time NOT NULL,
|
|
"heure_fin" time NOT NULL,
|
|
"actif" boolean DEFAULT true,
|
|
"ligne_id" uuid REFERENCES "public"."lignes"("id") ON DELETE SET NULL,
|
|
"ligne_nom" text,
|
|
"region_id" varchar(100),
|
|
"created_by" uuid REFERENCES "public"."utilisateurs"("id"),
|
|
"substitutions_attribuees" jsonb DEFAULT '[]'::jsonb,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz,
|
|
CONSTRAINT "valid_dates" CHECK (date_fin >= date_debut),
|
|
CONSTRAINT "valid_hours" CHECK (heure_fin > heure_debut)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_travaux_dates" ON "public"."travaux" ("date_debut","date_fin");
|
|
CREATE INDEX IF NOT EXISTS "idx_travaux_actif" ON "public"."travaux" ("actif");
|
|
CREATE INDEX IF NOT EXISTS "idx_travaux_deleted" ON "public"."travaux" ("deleted_at");
|
|
CREATE INDEX IF NOT EXISTS "idx_travaux_ligne_id" ON "public"."travaux" ("ligne_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_travaux_region_id" ON "public"."travaux" ("region_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_travaux_created_by" ON "public"."travaux" ("created_by");
|
|
CREATE INDEX IF NOT EXISTS "idx_travaux_substitutions_attribuees_gin" ON "public"."travaux" USING gin ("substitutions_attribuees");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.20 travaux_substitutions
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."travaux_substitutions" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"travaux_id" uuid NOT NULL REFERENCES "public"."travaux"("id") ON DELETE CASCADE,
|
|
"horaire_id" uuid NOT NULL REFERENCES "public"."horaires"("id") ON DELETE CASCADE,
|
|
"ordre" integer DEFAULT 0,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
UNIQUE ("travaux_id","horaire_id")
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_travaux_substitutions_travaux" ON "public"."travaux_substitutions" ("travaux_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_travaux_substitutions_horaire" ON "public"."travaux_substitutions" ("horaire_id");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.21 tarifs
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."tarifs" (
|
|
"id" integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
"nom" varchar(255) NOT NULL,
|
|
"type" varchar(50) NOT NULL,
|
|
"description" text,
|
|
"prix_minimum" numeric(10,2),
|
|
"prix_par_km" numeric(10,3),
|
|
"age_maximum" integer,
|
|
"regions" text[] NOT NULL,
|
|
"date_debut" date,
|
|
"date_fin" date,
|
|
"ordre" integer NOT NULL DEFAULT 0,
|
|
"page_dediee" boolean DEFAULT false,
|
|
"infos" jsonb DEFAULT '{}'::jsonb,
|
|
"details" jsonb,
|
|
"created_at" timestamp DEFAULT now(),
|
|
"updated_at" timestamp DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_tarifs_ordre" ON "public"."tarifs" ("ordre");
|
|
CREATE INDEX IF NOT EXISTS "idx_tarifs_regions" ON "public"."tarifs" USING gin ("regions");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.22 parametres
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."parametres" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"cle" varchar(100) NOT NULL UNIQUE,
|
|
"valeur" jsonb,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now()
|
|
);
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.23 menus_regionaux
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."menus_regionaux" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"nom" varchar(200) NOT NULL,
|
|
"description" text,
|
|
"actif" boolean DEFAULT false,
|
|
"structure" jsonb DEFAULT '[]'::jsonb,
|
|
"region_id" varchar(64),
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_menus_regionaux_region_id" ON "public"."menus_regionaux" ("region_id");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.24 pages (CMS)
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."pages" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"titre" text NOT NULL,
|
|
"slug" text,
|
|
"contenu" jsonb DEFAULT '[]'::jsonb,
|
|
"statut" text DEFAULT 'brouillon',
|
|
"region_id" varchar(255),
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.25 page_personnalisation
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."page_personnalisation" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"page" text NOT NULL UNIQUE,
|
|
"config" jsonb DEFAULT '{}'::jsonb,
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now()
|
|
);
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.26 info_gares
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."info_gares" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"titre" text,
|
|
"description" text,
|
|
"debut_affichage" timestamptz,
|
|
"fin_affichage" timestamptz,
|
|
"gare_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"ligne_id" uuid REFERENCES "public"."lignes"("id") ON DELETE SET NULL,
|
|
"gare_ids" uuid[] DEFAULT '{}'::uuid[],
|
|
"ligne_ids" uuid[] DEFAULT '{}'::uuid[],
|
|
"type" text DEFAULT 'Information',
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.27 regles_quais
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."regles_quais" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"gare_id" uuid NOT NULL REFERENCES "public"."gares"("id") ON DELETE CASCADE,
|
|
"ligne_id" uuid REFERENCES "public"."lignes"("id") ON DELETE SET NULL,
|
|
"type_regle" text,
|
|
"quais_concernes" jsonb DEFAULT '[]'::jsonb,
|
|
"created_at" timestamptz DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_regles_quais_gare_id" ON "public"."regles_quais" ("gare_id");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.28 messagerie_threads
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."messagerie_threads" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"created_at" timestamptz NOT NULL DEFAULT now(),
|
|
"updated_at" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.29 messagerie_participants
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."messagerie_participants" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"thread_id" uuid NOT NULL REFERENCES "public"."messagerie_threads"("id") ON DELETE CASCADE,
|
|
"user_id" uuid NOT NULL REFERENCES "public"."utilisateurs"("id") ON DELETE CASCADE,
|
|
"created_at" timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE ("thread_id","user_id")
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_messagerie_participants_user_id" ON "public"."messagerie_participants" ("user_id");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.30 messagerie_messages
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."messagerie_messages" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"thread_id" uuid NOT NULL REFERENCES "public"."messagerie_threads"("id") ON DELETE CASCADE,
|
|
"sender_id" uuid NOT NULL REFERENCES "public"."utilisateurs"("id") ON DELETE CASCADE,
|
|
"contenu" text NOT NULL,
|
|
"created_at" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_messagerie_messages_thread_id" ON "public"."messagerie_messages" ("thread_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_messagerie_messages_created_at" ON "public"."messagerie_messages" ("created_at" DESC);
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.31 profils_utilisateurs (app mobile / client)
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."profils_utilisateurs" (
|
|
"id" uuid NOT NULL PRIMARY KEY,
|
|
"email" text NOT NULL,
|
|
"prenom" text,
|
|
"nom" text,
|
|
"date_naissance" date,
|
|
"telephone" text,
|
|
"carte_reduction" text,
|
|
"gares_favorites" text[] DEFAULT '{}'::text[],
|
|
"lignes_favorites" text[] DEFAULT '{}'::text[],
|
|
"cartes_bancaires" jsonb DEFAULT '[]'::jsonb,
|
|
"mode_paiement_favori" text,
|
|
"commandes" jsonb DEFAULT '[]'::jsonb,
|
|
"panier" jsonb DEFAULT '[]'::jsonb,
|
|
"trajets_favoris" text[] DEFAULT '{}'::text[],
|
|
"created_at" timestamptz DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_profils_email" ON "public"."profils_utilisateurs" ("email");
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.32 banque_mots (annonces sonores)
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."banque_mots" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"label" text,
|
|
"nom_fichier" text,
|
|
"storage_path" text,
|
|
"categorie" text DEFAULT 'general',
|
|
"format" text DEFAULT 'mp3',
|
|
"taille_bytes" bigint,
|
|
"description" text,
|
|
"duree_ms" integer,
|
|
"actif" boolean NOT NULL DEFAULT true,
|
|
"created_by" uuid,
|
|
"created_at" timestamptz NOT NULL DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_banque_mots_categorie" ON "public"."banque_mots" ("categorie");
|
|
CREATE INDEX IF NOT EXISTS "idx_banque_mots_label" ON "public"."banque_mots" ("label");
|
|
CREATE INDEX IF NOT EXISTS "idx_banque_mots_actif" ON "public"."banque_mots" ("actif") WHERE deleted_at IS NULL;
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.33 annonces_templates
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."annonces_templates" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"nom" text NOT NULL,
|
|
"type_annonce" text NOT NULL,
|
|
"description" text,
|
|
"sequence" jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
"variables" jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
"tts_texte" text,
|
|
"actif" boolean NOT NULL DEFAULT true,
|
|
"created_by" uuid,
|
|
"created_at" timestamptz NOT NULL DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
CREATE INDEX IF NOT EXISTS "idx_templates_type" ON "public"."annonces_templates" ("type_annonce");
|
|
CREATE INDEX IF NOT EXISTS "idx_templates_actif" ON "public"."annonces_templates" ("actif") WHERE deleted_at IS NULL;
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.34 annonces_enregistrees
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."annonces_enregistrees" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"nom" text,
|
|
"type_annonce" text,
|
|
"template_id" uuid REFERENCES "public"."annonces_templates"("id"),
|
|
"storage_path" text,
|
|
"nom_fichier" text,
|
|
"variables_values" jsonb DEFAULT '{}'::jsonb,
|
|
"mode_generation" text DEFAULT 'tts',
|
|
"duree_ms" integer,
|
|
"taille_bytes" bigint,
|
|
"gare_id" uuid REFERENCES "public"."gares"("id"),
|
|
"diffuse" boolean DEFAULT false,
|
|
"date_diffusion" timestamptz,
|
|
"notes" text,
|
|
"created_by" uuid,
|
|
"created_at" timestamptz NOT NULL DEFAULT now(),
|
|
"updated_at" timestamptz DEFAULT now(),
|
|
"deleted_at" timestamptz
|
|
);
|
|
|
|
-- -----------------------------------------------
|
|
-- 6.35 annonces_diffusions
|
|
-- -----------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS "public"."annonces_diffusions" (
|
|
"id" uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
|
|
"annonce_id" uuid REFERENCES "public"."annonces_enregistrees"("id") ON DELETE SET NULL,
|
|
"gare_id" uuid REFERENCES "public"."gares"("id") ON DELETE SET NULL,
|
|
"audio_path" text,
|
|
"tts_texte" text,
|
|
"created_at" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- 7. TRIGGERS
|
|
-- ============================================================================
|
|
DROP TRIGGER IF EXISTS "tr_utilisateurs_set_updated_at" ON "public"."utilisateurs";
|
|
CREATE TRIGGER "tr_utilisateurs_set_updated_at" BEFORE UPDATE ON "public"."utilisateurs"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."set_updated_at"();
|
|
|
|
DROP TRIGGER IF EXISTS "tr_utilisateurs_audit" ON "public"."utilisateurs";
|
|
CREATE TRIGGER "tr_utilisateurs_audit" AFTER INSERT OR DELETE OR UPDATE ON "public"."utilisateurs"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."log_audit"();
|
|
|
|
DROP TRIGGER IF EXISTS "tr_gares_set_updated_at" ON "public"."gares";
|
|
CREATE TRIGGER "tr_gares_set_updated_at" BEFORE UPDATE ON "public"."gares"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."set_updated_at"();
|
|
|
|
DROP TRIGGER IF EXISTS "tr_gares_audit" ON "public"."gares";
|
|
CREATE TRIGGER "tr_gares_audit" AFTER INSERT OR DELETE OR UPDATE ON "public"."gares"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."log_audit"();
|
|
|
|
DROP TRIGGER IF EXISTS "update_horaires_updated_at" ON "public"."horaires";
|
|
CREATE TRIGGER "update_horaires_updated_at" BEFORE UPDATE ON "public"."horaires"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"();
|
|
|
|
DROP TRIGGER IF EXISTS "update_horaires_gares_desservies_updated_at" ON "public"."horaires_gares_desservies";
|
|
CREATE TRIGGER "update_horaires_gares_desservies_updated_at" BEFORE UPDATE ON "public"."horaires_gares_desservies"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"();
|
|
|
|
DROP TRIGGER IF EXISTS "update_perturbations_updated_at" ON "public"."perturbations";
|
|
CREATE TRIGGER "update_perturbations_updated_at" BEFORE UPDATE ON "public"."perturbations"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"();
|
|
|
|
DROP TRIGGER IF EXISTS "update_info_trafic_updated_at" ON "public"."info_trafic";
|
|
CREATE TRIGGER "update_info_trafic_updated_at" BEFORE UPDATE ON "public"."info_trafic"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"();
|
|
|
|
DROP TRIGGER IF EXISTS "update_evenements_updated_at" ON "public"."evenements";
|
|
CREATE TRIGGER "update_evenements_updated_at" BEFORE UPDATE ON "public"."evenements"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"();
|
|
|
|
DROP TRIGGER IF EXISTS "update_actualites_updated_at" ON "public"."actualites";
|
|
CREATE TRIGGER "update_actualites_updated_at" BEFORE UPDATE ON "public"."actualites"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"();
|
|
|
|
DROP TRIGGER IF EXISTS "update_lignes_updated_at" ON "public"."lignes";
|
|
CREATE TRIGGER "update_lignes_updated_at" BEFORE UPDATE ON "public"."lignes"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"();
|
|
|
|
DROP TRIGGER IF EXISTS "update_services_annuels_updated_at" ON "public"."services_annuels";
|
|
CREATE TRIGGER "update_services_annuels_updated_at" BEFORE UPDATE ON "public"."services_annuels"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"();
|
|
|
|
DROP TRIGGER IF EXISTS "trigger_update_travaux_updated_at" ON "public"."travaux";
|
|
CREATE TRIGGER "trigger_update_travaux_updated_at" BEFORE UPDATE ON "public"."travaux"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."update_travaux_updated_at"();
|
|
|
|
DROP TRIGGER IF EXISTS "set_profils_updated_at" ON "public"."profils_utilisateurs";
|
|
CREATE TRIGGER "set_profils_updated_at" BEFORE UPDATE ON "public"."profils_utilisateurs"
|
|
FOR EACH ROW EXECUTE FUNCTION "public"."handle_updated_at"();
|
|
|
|
-- ============================================================================
|
|
-- 8. FONCTIONS RPC MÉTIER
|
|
-- ============================================================================
|
|
|
|
-- Authentification
|
|
CREATE OR REPLACE FUNCTION "public"."authenticate_user"(p_email text, p_password text)
|
|
RETURNS TABLE(id uuid, email text, nom text, prenom text, name text, role text, region_admin boolean, region_id text)
|
|
LANGUAGE plpgsql STABLE SECURITY DEFINER AS $$
|
|
DECLARE
|
|
has_deleted_at boolean;
|
|
sql text;
|
|
BEGIN
|
|
SELECT EXISTS(SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema='public' AND table_name='utilisateurs' AND column_name='deleted_at')
|
|
INTO has_deleted_at;
|
|
sql := 'SELECT u.id::uuid, u.email::text, u.nom::text, u.prenom::text, '
|
|
|| '(COALESCE(u.nom,'''') || '' '' || COALESCE(u.prenom,''''))::text as name, '
|
|
|| 'u.role::text, '
|
|
|| 'COALESCE(u.region_admin, false)::boolean as region_admin, '
|
|
|| 'u.region_id::text '
|
|
|| 'FROM public.utilisateurs u '
|
|
|| 'WHERE lower(u.email) = lower($1) '
|
|
|| 'AND u.password_hash IS NOT NULL '
|
|
|| 'AND u.password_hash = crypt($2, u.password_hash) '
|
|
|| 'AND coalesce(u.actif, true) = true';
|
|
IF has_deleted_at THEN sql := sql || ' AND u.deleted_at IS NULL'; END IF;
|
|
RETURN QUERY EXECUTE sql USING p_email, p_password;
|
|
END; $$;
|
|
|
|
-- Création utilisateur avec mot de passe
|
|
CREATE OR REPLACE FUNCTION "public"."create_user_with_password"(
|
|
p_email text, p_nom text, p_prenom text,
|
|
p_role text DEFAULT 'utilisateur', p_actif boolean DEFAULT true,
|
|
p_password text DEFAULT NULL, p_photo_url text DEFAULT NULL,
|
|
p_region_admin boolean DEFAULT false, p_region_id text DEFAULT NULL
|
|
) RETURNS TABLE(id uuid, email text, nom text, prenom text, role text, actif boolean, photo_url text, region_admin boolean, region_id text)
|
|
LANGUAGE plpgsql SECURITY DEFINER AS $$
|
|
DECLARE new_id uuid;
|
|
BEGIN
|
|
INSERT INTO public.utilisateurs (email, nom, prenom, role, actif, password_hash, photo_url, region_admin, region_id, created_at)
|
|
VALUES (lower(p_email), p_nom, p_prenom, p_role, p_actif,
|
|
CASE WHEN p_password IS NOT NULL AND p_password != '' THEN crypt(p_password, gen_salt('bf')) ELSE NULL END,
|
|
p_photo_url, p_region_admin, p_region_id, NOW())
|
|
RETURNING utilisateurs.id INTO new_id;
|
|
RETURN QUERY SELECT u.id, u.email::text, u.nom::text, u.prenom::text, u.role::text,
|
|
u.actif, u.photo_url::text, u.region_admin, u.region_id::text
|
|
FROM public.utilisateurs u WHERE u.id = new_id;
|
|
END; $$;
|
|
|
|
-- Mise à jour mot de passe
|
|
CREATE OR REPLACE FUNCTION "public"."update_user_password"(p_user_id uuid, p_password text) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER AS $$
|
|
BEGIN
|
|
IF p_password IS NULL OR p_password = '' THEN RAISE EXCEPTION 'Le mot de passe ne peut pas être vide'; END IF;
|
|
UPDATE public.utilisateurs SET password_hash = crypt(p_password, gen_salt('bf')), updated_at = NOW() WHERE id = p_user_id;
|
|
IF NOT FOUND THEN RAISE EXCEPTION 'Utilisateur non trouvé'; END IF;
|
|
END; $$;
|
|
|
|
-- Détails d'une gare
|
|
CREATE OR REPLACE FUNCTION "public"."get_gare_details"(p_gare_id uuid)
|
|
RETURNS TABLE(id uuid, nom text, type varchar(50), services "public"."service_type"[], quais jsonb, transports jsonb, created_at timestamptz, updated_at timestamptz)
|
|
LANGUAGE plpgsql STABLE SECURITY DEFINER AS $$
|
|
BEGIN
|
|
RETURN QUERY SELECT g.id, g.nom, g.type, g.services,
|
|
COALESCE((SELECT jsonb_agg(jsonb_build_object('id',q.id,'nom',q.nom,'distance_metres',q.distance_metres))
|
|
FROM public.gare_quais q WHERE q.gare_id = g.id), '[]'::jsonb) as quais,
|
|
COALESCE((SELECT jsonb_agg(jsonb_build_object('id',t.id,'type',t.type,'ligne',t.ligne))
|
|
FROM public.gare_transports t WHERE t.gare_id = g.id), '[]'::jsonb) as transports,
|
|
g.created_at, g.updated_at
|
|
FROM public.gares g WHERE g.id = p_gare_id AND g.deleted_at IS NULL;
|
|
END; $$;
|
|
|
|
-- ============================================================================
|
|
-- 9. ROW LEVEL SECURITY (RLS) + POLICIES
|
|
-- ============================================================================
|
|
|
|
-- menus_regionaux
|
|
ALTER TABLE "public"."menus_regionaux" ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY "menus_select" ON "public"."menus_regionaux" FOR SELECT USING (true);
|
|
CREATE POLICY "menus_insert" ON "public"."menus_regionaux" FOR INSERT WITH CHECK (true);
|
|
CREATE POLICY "menus_update" ON "public"."menus_regionaux" FOR UPDATE USING (true);
|
|
CREATE POLICY "menus_delete" ON "public"."menus_regionaux" FOR DELETE USING (true);
|
|
|
|
-- parametres
|
|
ALTER TABLE "public"."parametres" ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY "parametres_select_all" ON "public"."parametres" FOR SELECT USING (true);
|
|
CREATE POLICY "parametres_insert_all" ON "public"."parametres" FOR INSERT WITH CHECK (true);
|
|
CREATE POLICY "parametres_update_all" ON "public"."parametres" FOR UPDATE USING (true);
|
|
CREATE POLICY "parametres_delete_all" ON "public"."parametres" FOR DELETE USING (true);
|
|
|
|
-- messagerie_threads
|
|
ALTER TABLE "public"."messagerie_threads" ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY "messagerie_threads_insert" ON "public"."messagerie_threads" FOR INSERT TO "anon" WITH CHECK (true);
|
|
CREATE POLICY "messagerie_threads_select" ON "public"."messagerie_threads" FOR SELECT TO "anon"
|
|
USING (EXISTS (SELECT 1 FROM "public"."messagerie_participants" p WHERE p.thread_id = messagerie_threads.id));
|
|
CREATE POLICY "messagerie_threads_update" ON "public"."messagerie_threads" FOR UPDATE TO "anon"
|
|
USING (EXISTS (SELECT 1 FROM "public"."messagerie_participants" p WHERE p.thread_id = messagerie_threads.id)) WITH CHECK (true);
|
|
|
|
-- messagerie_participants
|
|
ALTER TABLE "public"."messagerie_participants" ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY "messagerie_participants_insert" ON "public"."messagerie_participants" FOR INSERT TO "anon" WITH CHECK (true);
|
|
CREATE POLICY "messagerie_participants_select" ON "public"."messagerie_participants" FOR SELECT TO "anon" USING (true);
|
|
|
|
-- messagerie_messages
|
|
ALTER TABLE "public"."messagerie_messages" ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY "messagerie_messages_insert" ON "public"."messagerie_messages" FOR INSERT TO "anon"
|
|
WITH CHECK (EXISTS (SELECT 1 FROM "public"."messagerie_participants" p WHERE p.thread_id = messagerie_messages.thread_id AND p.user_id = messagerie_messages.sender_id));
|
|
CREATE POLICY "messagerie_messages_select" ON "public"."messagerie_messages" FOR SELECT TO "anon"
|
|
USING (EXISTS (SELECT 1 FROM "public"."messagerie_participants" p WHERE p.thread_id = messagerie_messages.thread_id));
|
|
|
|
-- profils_utilisateurs
|
|
ALTER TABLE "public"."profils_utilisateurs" ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY "Lecture profil propre" ON "public"."profils_utilisateurs" FOR SELECT USING (auth.uid() = id);
|
|
CREATE POLICY "Insertion profil propre" ON "public"."profils_utilisateurs" FOR INSERT WITH CHECK (auth.uid() = id);
|
|
CREATE POLICY "Mise à jour profil propre" ON "public"."profils_utilisateurs" FOR UPDATE USING (auth.uid() = id) WITH CHECK (auth.uid() = id);
|
|
CREATE POLICY "Suppression profil propre" ON "public"."profils_utilisateurs" FOR DELETE USING (auth.uid() = id);
|
|
|
|
-- ============================================================================
|
|
-- 10. GRANTS — Accès complet pour anon / authenticated / service_role
|
|
-- ============================================================================
|
|
GRANT USAGE ON SCHEMA "public" TO "postgres", "anon", "authenticated", "service_role";
|
|
|
|
-- Tables
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "public" TO "anon", "authenticated", "service_role";
|
|
|
|
-- Séquences
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA "public" TO "anon", "authenticated", "service_role";
|
|
|
|
-- Fonctions
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "public" TO "anon", "authenticated", "service_role";
|
|
|
|
-- Droits par défaut pour les futurs objets
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "anon", "authenticated", "service_role";
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT USAGE, SELECT ON SEQUENCES TO "anon", "authenticated", "service_role";
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT EXECUTE ON FUNCTIONS TO "anon", "authenticated", "service_role";
|
|
|
|
-- ============================================================================
|
|
-- 11. DONNÉES INITIALES — Utilisateur administrateur
|
|
-- ============================================================================
|
|
INSERT INTO "public"."utilisateurs" (email, nom, prenom, password_hash, role, actif, region_admin)
|
|
VALUES (
|
|
'admin@ferrovia.fr',
|
|
'Admin',
|
|
'Ferrovia',
|
|
crypt('password', gen_salt('bf')),
|
|
'admin',
|
|
true,
|
|
true
|
|
) ON CONFLICT (email) DO UPDATE SET
|
|
password_hash = EXCLUDED.password_hash,
|
|
role = EXCLUDED.role,
|
|
actif = EXCLUDED.actif,
|
|
region_admin = EXCLUDED.region_admin,
|
|
updated_at = NOW();
|
|
|
|
-- ============================================================================
|
|
-- FIN DU SCHÉMA — 35 tables, 3 enums, 12 fonctions, 14 triggers, 1 admin
|
|
-- ============================================================================
|