bessanemansour-bot/BESSANEPROSSERVICE-ECOLE

Plateforme educative pour l'enseignement à l'ecole elementaire au senegal # BESSANEPROSSERVICE-ECOLE Plateforme educative pour l'enseignement à l'ecole elementaire au senegal pour la gestion de l'ecole ```sql -- Script SQL pour créer la base de données de la plateforme éducative -- Utilise PostgreSQL -- Création de la base de données CREATE DATABASE plateforme_educative_senegal; \c plateforme_educative_senegal; -- Table school CREATE TABLE school ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, address TEXT, phone VARCHAR(20), email VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Table level CREATE TABLE level ( id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, -- CP, CE1, etc. school_id INTEGER REFERENCES school(id) ON DELETE CASCADE ); -- Table classroom CREATE TABLE classroom ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, level_id INTEGER REFERENCES level(id) ON DELETE CASCADE, school_id INTEGER REFERENCES school(id) ON DELETE CASCADE, teacher_id INTEGER, -- Référence à user.id, ajoutée après capacity INTEGER ); -- Table user CREATE TABLE "user" ( id SERIAL PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, role VARCHAR(50) NOT NULL CHECK (role IN ('admin', 'directeur', 'enseignant', 'parent', 'eleve')), phone VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Table student CREATE TABLE student ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES "user"(id) ON DELETE CASCADE, birth_date DATE, gender VARCHAR(10) CHECK (gender IN ('M', 'F')), admission_date DATE, status VARCHAR(20) DEFAULT 'active' ); -- Table parent CREATE TABLE parent ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES "user"(id) ON DELETE CASCADE, relationship VARCHAR(50), -- père, mère, tuteur student_id INTEGER REFERENCES student(id) ON DELETE CASCADE ); -- Table teacher CREATE TABLE teacher ( id SERIAL PRIMARY KEY, user_ …