-- ============================================
-- SCHEMA POUR SYSTEME DE COURS SQL
-- ============================================
-- Ce script crée les tables pour gérer les cours SQL en ligne

-- Supprimer les tables si elles existent
BEGIN
   FOR c IN (SELECT table_name FROM user_tables WHERE table_name IN ('COURS_EXERCICES', 'COURS_EXEMPLES', 'COURS_LECONS', 'COURS_MODULES')) LOOP
      EXECUTE IMMEDIATE ('DROP TABLE ' || c.table_name || ' CASCADE CONSTRAINTS');
   END LOOP;
END;
/

-- Supprimer les séquences si elles existent
BEGIN
   FOR s IN (SELECT sequence_name FROM user_sequences WHERE sequence_name IN ('SEQ_COURS_MODULE', 'SEQ_COURS_LECON', 'SEQ_COURS_EXEMPLE', 'SEQ_COURS_EXERCICE')) LOOP
      EXECUTE IMMEDIATE ('DROP SEQUENCE ' || s.sequence_name);
   END LOOP;
END;
/

-- ============================================
-- TABLE COURS_MODULES
-- ============================================
CREATE TABLE COURS_MODULES (
    module_id NUMBER(4) PRIMARY KEY,
    numero_module NUMBER(2) NOT NULL UNIQUE,
    titre VARCHAR2(200) NOT NULL,
    description VARCHAR2(1000),
    ordre_affichage NUMBER(2) NOT NULL,
    actif CHAR(1) DEFAULT 'O' CHECK (actif IN ('O', 'N')),
    date_creation DATE DEFAULT SYSDATE,
    date_modification DATE DEFAULT SYSDATE
);

-- ============================================
-- TABLE COURS_LECONS
-- ============================================
CREATE TABLE COURS_LECONS (
    lecon_id NUMBER(6) PRIMARY KEY,
    module_id NUMBER(4) NOT NULL,
    slug VARCHAR2(100) NOT NULL UNIQUE,
    titre VARCHAR2(200) NOT NULL,
    sous_titre VARCHAR2(300),
    contenu_html CLOB NOT NULL,
    objectifs CLOB,
    pre_requis CLOB,
    ordre_affichage NUMBER(3) NOT NULL,
    duree_minutes NUMBER(3),
    difficulte VARCHAR2(20) DEFAULT 'DEBUTANT' CHECK (difficulte IN ('DEBUTANT', 'INTERMEDIAIRE', 'AVANCE')),
    actif CHAR(1) DEFAULT 'O' CHECK (actif IN ('O', 'N')),
    date_creation DATE DEFAULT SYSDATE,
    date_modification DATE DEFAULT SYSDATE,
    CONSTRAINT fk_lecon_module FOREIGN KEY (module_id) REFERENCES COURS_MODULES(module_id) ON DELETE CASCADE,
    CONSTRAINT uk_module_slug UNIQUE (module_id, slug)
);

-- ============================================
-- TABLE COURS_EXEMPLES
-- ============================================
CREATE TABLE COURS_EXEMPLES (
    exemple_id NUMBER(8) PRIMARY KEY,
    lecon_id NUMBER(6) NOT NULL,
    titre VARCHAR2(200),
    code_sql CLOB NOT NULL,
    resultat_attendu CLOB,
    explication CLOB,
    ordre_affichage NUMBER(3) NOT NULL,
    CONSTRAINT fk_exemple_lecon FOREIGN KEY (lecon_id) REFERENCES COURS_LECONS(lecon_id) ON DELETE CASCADE
);

-- ============================================
-- TABLE COURS_EXERCICES
-- ============================================
CREATE TABLE COURS_EXERCICES (
    exercice_id NUMBER(8) PRIMARY KEY,
    lecon_id NUMBER(6) NOT NULL,
    titre VARCHAR2(200) NOT NULL,
    enonce CLOB NOT NULL,
    solution_sql CLOB,
    indice CLOB,
    difficulte VARCHAR2(20) DEFAULT 'FACILE' CHECK (difficulte IN ('FACILE', 'MOYEN', 'DIFFICILE')),
    ordre_affichage NUMBER(3) NOT NULL,
    CONSTRAINT fk_exercice_lecon FOREIGN KEY (lecon_id) REFERENCES COURS_LECONS(lecon_id) ON DELETE CASCADE
);

-- ============================================
-- SEQUENCES
-- ============================================
CREATE SEQUENCE SEQ_COURS_MODULE START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_COURS_LECON START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_COURS_EXEMPLE START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_COURS_EXERCICE START WITH 1 INCREMENT BY 1;

-- ============================================
-- INDEX pour performances
-- ============================================
CREATE INDEX idx_lecon_module ON COURS_LECONS(module_id);
CREATE INDEX idx_lecon_slug ON COURS_LECONS(slug);
CREATE INDEX idx_exemple_lecon ON COURS_EXEMPLES(lecon_id);
CREATE INDEX idx_exercice_lecon ON COURS_EXERCICES(lecon_id);

-- ============================================
-- DONNEES INITIALES - MODULES
-- ============================================
INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 1, 'Fondamentaux SELECT', 'Apprendre les bases de la sélection de données avec SELECT', 1);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 2, 'Filtrage et Tri', 'Maîtriser les clauses WHERE et ORDER BY pour filtrer et trier les données', 2);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 3, 'Fonctions SQL', 'Découvrir et utiliser les fonctions SQL pour manipuler les données', 3);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 4, 'Agrégation', 'Utiliser GROUP BY et les fonctions d''agrégation', 4);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 5, 'Jointures', 'Comprendre et utiliser les jointures entre tables', 5);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 6, 'Sous-requêtes', 'Maîtriser les sous-requêtes dans différentes clauses', 6);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 7, 'Opérateurs Ensemblistes', 'Utiliser UNION, INTERSECT et MINUS', 7);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 8, 'DML', 'Manipuler les données avec INSERT, UPDATE, DELETE', 8);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 9, 'DDL', 'Créer et modifier des structures de tables', 9);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 10, 'Vues', 'Créer et gérer des vues SQL', 10);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 11, 'Transactions', 'Comprendre les transactions et le contrôle de concurrence', 11);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 12, 'Window Functions', 'Utiliser les fonctions analytiques avancées', 12);

INSERT INTO COURS_MODULES (module_id, numero_module, titre, description, ordre_affichage)
VALUES (SEQ_COURS_MODULE.NEXTVAL, 13, 'Sujets Avancés', 'Approfondir les concepts SQL avancés', 13);

-- ============================================
-- DONNEES INITIALES - LECONS MODULE 1
-- ============================================
INSERT INTO COURS_LECONS (lecon_id, module_id, slug, titre, sous_titre, contenu_html, objectifs, ordre_affichage, duree_minutes, difficulte)
VALUES (
    SEQ_COURS_LECON.NEXTVAL,
    1,
    'select-basics',
    'SELECT de Base',
    'Comprendre la syntaxe fondamentale du SELECT',
    '<h2>Introduction au SELECT</h2>
    <p>La commande <code>SELECT</code> est l''instruction la plus utilisée en SQL. Elle permet d''interroger une base de données et de récupérer des données.</p>

    <h3>Syntaxe de base</h3>
    <pre><code>SELECT colonne1, colonne2, ...
FROM nom_table;</code></pre>

    <h3>Sélectionner toutes les colonnes</h3>
    <p>Pour sélectionner toutes les colonnes d''une table, utilisez l''astérisque (*):</p>
    <pre><code>SELECT * FROM EMPLOYES;</code></pre>

    <h3>Sélectionner des colonnes spécifiques</h3>
    <p>Pour sélectionner uniquement certaines colonnes:</p>
    <pre><code>SELECT nom, prenom, email FROM EMPLOYES;</code></pre>

    <div class="alert alert-info">
        <strong>Bonne pratique :</strong> Évitez d''utiliser SELECT * en production. Sélectionnez uniquement les colonnes dont vous avez besoin pour optimiser les performances.
    </div>',
    '<ul>
        <li>Comprendre la syntaxe SELECT de base</li>
        <li>Sélectionner toutes les colonnes avec *</li>
        <li>Sélectionner des colonnes spécifiques</li>
        <li>Connaître les bonnes pratiques</li>
    </ul>',
    1,
    15,
    'DEBUTANT'
);

INSERT INTO COURS_LECONS (lecon_id, module_id, slug, titre, sous_titre, contenu_html, ordre_affichage, duree_minutes, difficulte)
VALUES (
    SEQ_COURS_LECON.NEXTVAL,
    1,
    'alias',
    'Alias de Colonnes',
    'Renommer les colonnes dans les résultats',
    '<h2>Les Alias de Colonnes</h2>
    <p>Les alias permettent de renommer temporairement les colonnes dans les résultats d''une requête.</p>

    <h3>Syntaxe</h3>
    <pre><code>SELECT colonne AS alias_nom
FROM nom_table;</code></pre>

    <h3>Exemples</h3>
    <pre><code>SELECT nom AS "Nom de famille",
       prenom AS "Prénom",
       salaire AS "Salaire mensuel"
FROM EMPLOYES;</code></pre>

    <div class="alert alert-warning">
        <strong>Note :</strong> Le mot-clé AS est optionnel en SQL, mais il est recommandé pour la lisibilité.
    </div>',
    2,
    10,
    'DEBUTANT'
);

INSERT INTO COURS_LECONS (lecon_id, module_id, slug, titre, sous_titre, contenu_html, ordre_affichage, duree_minutes, difficulte)
VALUES (
    SEQ_COURS_LECON.NEXTVAL,
    1,
    'distinct',
    'DISTINCT',
    'Éliminer les doublons dans les résultats',
    '<h2>La clause DISTINCT</h2>
    <p>DISTINCT permet d''éliminer les lignes en double dans les résultats d''une requête.</p>

    <h3>Syntaxe</h3>
    <pre><code>SELECT DISTINCT colonne
FROM nom_table;</code></pre>

    <h3>Exemple</h3>
    <pre><code>-- Obtenir la liste des différents postes
SELECT DISTINCT poste
FROM EMPLOYES;</code></pre>

    <h3>DISTINCT sur plusieurs colonnes</h3>
    <pre><code>SELECT DISTINCT dept_id, poste
FROM EMPLOYES;</code></pre>',
    3,
    12,
    'DEBUTANT'
);

-- ============================================
-- EXEMPLES POUR SELECT BASICS
-- ============================================
INSERT INTO COURS_EXEMPLES (exemple_id, lecon_id, titre, code_sql, resultat_attendu, explication, ordre_affichage)
VALUES (
    SEQ_COURS_EXEMPLE.NEXTVAL,
    1,
    'Sélectionner tous les employés',
    'SELECT * FROM EMPLOYES;',
    'Toutes les colonnes et toutes les lignes de la table EMPLOYES',
    'Cette requête récupère l''intégralité des données de la table EMPLOYES',
    1
);

INSERT INTO COURS_EXEMPLES (exemple_id, lecon_id, titre, code_sql, resultat_attendu, explication, ordre_affichage)
VALUES (
    SEQ_COURS_EXEMPLE.NEXTVAL,
    1,
    'Sélectionner nom et email',
    'SELECT nom, email FROM EMPLOYES;',
    'Uniquement les colonnes nom et email',
    'Récupère seulement les informations de nom et d''email des employés',
    2
);

-- ============================================
-- EXERCICES POUR SELECT BASICS
-- ============================================
INSERT INTO COURS_EXERCICES (exercice_id, lecon_id, titre, enonce, solution_sql, indice, difficulte, ordre_affichage)
VALUES (
    SEQ_COURS_EXERCICE.NEXTVAL,
    1,
    'Exercice 1 : Lister les départements',
    'Écrivez une requête pour afficher tous les départements (toutes les colonnes).',
    'SELECT * FROM DEPARTEMENTS;',
    'Utilisez SELECT * FROM ...',
    'FACILE',
    1
);

INSERT INTO COURS_EXERCICES (exercice_id, lecon_id, titre, enonce, solution_sql, indice, difficulte, ordre_affichage)
VALUES (
    SEQ_COURS_EXERCICE.NEXTVAL,
    1,
    'Exercice 2 : Nom et salaire des employés',
    'Affichez uniquement le nom, le prénom et le salaire de tous les employés.',
    'SELECT nom, prenom, salaire FROM EMPLOYES;',
    'Sélectionnez les 3 colonnes spécifiques',
    'FACILE',
    2
);

COMMIT;

-- ============================================
-- VERIFICATION
-- ============================================
SELECT 'MODULES: ' || COUNT(*) AS info FROM COURS_MODULES
UNION ALL
SELECT 'LECONS: ' || COUNT(*) FROM COURS_LECONS
UNION ALL
SELECT 'EXEMPLES: ' || COUNT(*) FROM COURS_EXEMPLES
UNION ALL
SELECT 'EXERCICES: ' || COUNT(*) FROM COURS_EXERCICES;

PROMPT
PROMPT ================================================
PROMPT Schema des cours SQL créé avec succès !
PROMPT ================================================