-- Table pour stocker les questions d'entretien
-- Créée pour le système de notes d'entretien

-- Suppression de la table si elle existe déjà
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE INTERVIEW_QUESTIONS CASCADE CONSTRAINTS';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

-- Suppression de la séquence si elle existe
BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE INTERVIEW_QUESTIONS_SEQ';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -2289 THEN
         RAISE;
      END IF;
END;
/

-- Création de la table INTERVIEW_QUESTIONS
CREATE TABLE INTERVIEW_QUESTIONS (
    ID NUMBER PRIMARY KEY,
    QUESTION_TEXT VARCHAR2(1000) NOT NULL,
    IS_COMPLETED NUMBER(1) DEFAULT 0 CHECK (IS_COMPLETED IN (0, 1)),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    SORT_ORDER NUMBER DEFAULT 0
);

-- Création de la séquence pour les ID
CREATE SEQUENCE INTERVIEW_QUESTIONS_SEQ
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Trigger pour auto-incrémenter l'ID
CREATE OR REPLACE TRIGGER INTERVIEW_QUESTIONS_TRG
BEFORE INSERT ON INTERVIEW_QUESTIONS
FOR EACH ROW
BEGIN
    IF :NEW.ID IS NULL THEN
        SELECT INTERVIEW_QUESTIONS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
    END IF;
    :NEW.CREATED_AT := CURRENT_TIMESTAMP;
    :NEW.UPDATED_AT := CURRENT_TIMESTAMP;
END;
/

-- Trigger pour mettre à jour UPDATED_AT
CREATE OR REPLACE TRIGGER INTERVIEW_QUESTIONS_UPD_TRG
BEFORE UPDATE ON INTERVIEW_QUESTIONS
FOR EACH ROW
BEGIN
    :NEW.UPDATED_AT := CURRENT_TIMESTAMP;
END;
/

-- Table pour stocker les notes personnelles
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE INTERVIEW_NOTES CASCADE CONSTRAINTS';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

CREATE TABLE INTERVIEW_NOTES (
    ID NUMBER PRIMARY KEY,
    NOTES_CONTENT CLOB,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insérer une ligne par défaut pour les notes
INSERT INTO INTERVIEW_NOTES (ID, NOTES_CONTENT) VALUES (1, '');

-- Trigger pour mettre à jour UPDATED_AT des notes
CREATE OR REPLACE TRIGGER INTERVIEW_NOTES_UPD_TRG
BEFORE UPDATE ON INTERVIEW_NOTES
FOR EACH ROW
BEGIN
    :NEW.UPDATED_AT := CURRENT_TIMESTAMP;
END;
/

-- Insertion de questions d'exemple
INSERT INTO INTERVIEW_QUESTIONS (QUESTION_TEXT, IS_COMPLETED, SORT_ORDER)
VALUES ('Qu''est-ce qu''une clé primaire en SQL ?', 0, 1);

INSERT INTO INTERVIEW_QUESTIONS (QUESTION_TEXT, IS_COMPLETED, SORT_ORDER)
VALUES ('Quelle est la différence entre INNER JOIN et LEFT JOIN ?', 0, 2);

INSERT INTO INTERVIEW_QUESTIONS (QUESTION_TEXT, IS_COMPLETED, SORT_ORDER)
VALUES ('Comment optimiser une requête SQL lente ?', 0, 3);

INSERT INTO INTERVIEW_QUESTIONS (QUESTION_TEXT, IS_COMPLETED, SORT_ORDER)
VALUES ('Qu''est-ce qu''une transaction et comment fonctionne COMMIT et ROLLBACK ?', 0, 4);

INSERT INTO INTERVIEW_QUESTIONS (QUESTION_TEXT, IS_COMPLETED, SORT_ORDER)
VALUES ('Expliquez la normalisation de base de données et ses formes normales', 0, 5);

COMMIT;

-- Vérification
SELECT 'Questions créées: ' || COUNT(*) as RESULTAT FROM INTERVIEW_QUESTIONS;