-- ============================================
-- CORRECTIONS ET AJOUTS POUR LES EXERCICES
-- ============================================
-- Ce script ajoute les tables et données manquantes pour les exercices SQL

-- ============================================
-- 1. CRÉER LA TABLE PAYS
-- ============================================
BEGIN
   FOR c IN (SELECT table_name FROM user_tables WHERE table_name = 'PAYS') LOOP
      EXECUTE IMMEDIATE ('DROP TABLE ' || c.table_name || ' CASCADE CONSTRAINTS');
   END LOOP;
END;
/

CREATE TABLE PAYS (
    pays_code CHAR(2) PRIMARY KEY,
    nom_pays VARCHAR2(100) NOT NULL,
    capitale VARCHAR2(100),
    population NUMBER(12),
    continent VARCHAR2(50),
    superficie NUMBER(12),
    date_creation DATE DEFAULT SYSDATE
);

-- ============================================
-- 2. CRÉER LA TABLE LOCALISATIONS
-- ============================================
BEGIN
   FOR c IN (SELECT table_name FROM user_tables WHERE table_name = 'LOCALISATIONS') LOOP
      EXECUTE IMMEDIATE ('DROP TABLE ' || c.table_name || ' CASCADE CONSTRAINTS');
   END LOOP;
END;
/

CREATE TABLE LOCALISATIONS (
    localisation_id NUMBER(6) PRIMARY KEY,
    adresse_rue VARCHAR2(200),
    ville VARCHAR2(100) NOT NULL,
    code_postal VARCHAR2(20),
    region VARCHAR2(100),
    pays_code CHAR(2),
    CONSTRAINT fk_loc_pays FOREIGN KEY (pays_code) REFERENCES PAYS(pays_code)
);

-- Séquence pour LOCALISATIONS
BEGIN
   FOR s IN (SELECT sequence_name FROM user_sequences WHERE sequence_name = 'SEQ_LOCALISATION') LOOP
      EXECUTE IMMEDIATE ('DROP SEQUENCE ' || s.sequence_name);
   END LOOP;
END;
/

CREATE SEQUENCE seq_localisation START WITH 1 INCREMENT BY 1;

-- ============================================
-- 3. AJOUTER DES DONNÉES DANS PAYS
-- ============================================
INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('FR', 'France', 'Paris', 67000000, 'Europe', 643801);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('DE', 'Allemagne', 'Berlin', 83000000, 'Europe', 357022);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('ES', 'Espagne', 'Madrid', 47000000, 'Europe', 505990);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('IT', 'Italie', 'Rome', 60000000, 'Europe', 301340);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('GB', 'Royaume-Uni', 'Londres', 67000000, 'Europe', 243610);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('US', 'États-Unis', 'Washington', 331000000, 'Amérique du Nord', 9833517);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('CA', 'Canada', 'Ottawa', 38000000, 'Amérique du Nord', 9984670);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('JP', 'Japon', 'Tokyo', 126000000, 'Asie', 377975);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('CN', 'Chine', 'Pékin', 1400000000, 'Asie', 9596961);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('BR', 'Brésil', 'Brasília', 212000000, 'Amérique du Sud', 8515767);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('MX', 'Mexique', 'Mexico', 128000000, 'Amérique du Nord', 1964375);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('IN', 'Inde', 'New Delhi', 1380000000, 'Asie', 3287263);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('AU', 'Australie', 'Canberra', 25000000, 'Océanie', 7692024);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('PT', 'Portugal', 'Lisbonne', 10000000, 'Europe', 92090);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('NL', 'Pays-Bas', 'Amsterdam', 17000000, 'Europe', 41543);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('BE', 'Belgique', 'Bruxelles', 11500000, 'Europe', 30528);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('CH', 'Suisse', 'Berne', 8600000, 'Europe', 41285);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('SE', 'Suède', 'Stockholm', 10000000, 'Europe', 450295);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('NO', 'Norvège', 'Oslo', 5400000, 'Europe', 323802);

INSERT INTO PAYS (pays_code, nom_pays, capitale, population, continent, superficie)
VALUES ('PL', 'Pologne', 'Varsovie', 38000000, 'Europe', 312696);

-- ============================================
-- 4. AJOUTER DES DONNÉES DANS LOCALISATIONS
-- ============================================
INSERT INTO LOCALISATIONS (localisation_id, adresse_rue, ville, code_postal, region, pays_code)
VALUES (seq_localisation.NEXTVAL, '1200 5th Avenue', 'Seattle', '98101', 'Washington', 'US');

INSERT INTO LOCALISATIONS (localisation_id, adresse_rue, ville, code_postal, region, pays_code)
VALUES (seq_localisation.NEXTVAL, '10 Avenue des Champs-Élysées', 'Paris', '75008', 'Île-de-France', 'FR');

INSERT INTO LOCALISATIONS (localisation_id, adresse_rue, ville, code_postal, region, pays_code)
VALUES (seq_localisation.NEXTVAL, '25 Rue de la République', 'Lyon', '69002', 'Auvergne-Rhône-Alpes', 'FR');

INSERT INTO LOCALISATIONS (localisation_id, adresse_rue, ville, code_postal, region, pays_code)
VALUES (seq_localisation.NEXTVAL, '50 Broadway', 'New York', '10004', 'New York', 'US');

INSERT INTO LOCALISATIONS (localisation_id, adresse_rue, ville, code_postal, region, pays_code)
VALUES (seq_localisation.NEXTVAL, '100 King Street', 'Londres', 'EC2V 8AS', 'Greater London', 'GB');

INSERT INTO LOCALISATIONS (localisation_id, adresse_rue, ville, code_postal, region, pays_code)
VALUES (seq_localisation.NEXTVAL, '30 Unter den Linden', 'Berlin', '10117', 'Berlin', 'DE');

INSERT INTO LOCALISATIONS (localisation_id, adresse_rue, ville, code_postal, region, pays_code)
VALUES (seq_localisation.NEXTVAL, '15 Via del Corso', 'Rome', '00186', 'Lazio', 'IT');

INSERT INTO LOCALISATIONS (localisation_id, adresse_rue, ville, code_postal, region, pays_code)
VALUES (seq_localisation.NEXTVAL, '20 Gran Vía', 'Madrid', '28013', 'Madrid', 'ES');

INSERT INTO LOCALISATIONS (localisation_id, adresse_rue, ville, code_postal, region, pays_code)
VALUES (seq_localisation.NEXTVAL, '5 Shibuya Crossing', 'Tokyo', '150-0002', 'Tokyo', 'JP');

INSERT INTO LOCALISATIONS (localisation_id, adresse_rue, ville, code_postal, region, pays_code)
VALUES (seq_localisation.NEXTVAL, '88 Bay Street', 'Toronto', 'M5J 2Z2', 'Ontario', 'CA');

-- ============================================
-- 5. AJOUTER DES DÉPARTEMENTS AVEC IDS SPÉCIFIQUES
-- ============================================
-- Département IT avec ID 60
INSERT INTO DEPARTEMENTS (dept_id, dept_nom, localisation, budget, date_creation)
VALUES (60, 'IT', 'Seattle', 750000, SYSDATE);

-- Département avec ID 90
INSERT INTO DEPARTEMENTS (dept_id, dept_nom, localisation, budget, date_creation)
VALUES (90, 'Direction Générale', 'Paris', 1000000, SYSDATE);

-- ============================================
-- 6. AJOUTER DES EMPLOYÉS DANS CES DÉPARTEMENTS
-- ============================================
-- Manager du département IT (ID 60)
INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'JOHNSON', 'David', 'david.johnson@company.com', '0701234567', TO_DATE('2019-05-01', 'YYYY-MM-DD'), 'Manager IT', 9000, NULL, NULL, 60);

-- Employés du département IT (ID 60)
INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'WILLIAMS', 'Sarah', 'sarah.williams@company.com', '0702345678', TO_DATE('2020-03-15', 'YYYY-MM-DD'), 'Développeur Full Stack', 5500, NULL, 11, 60);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'BROWN', 'Michael', 'michael.brown@company.com', '0703456789', TO_DATE('2021-01-20', 'YYYY-MM-DD'), 'DevOps Engineer', 5200, NULL, 11, 60);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'DAVIS', 'Jennifer', 'jennifer.davis@company.com', '0704567890', TO_DATE('2022-06-10', 'YYYY-MM-DD'), 'Data Analyst', 4800, NULL, 11, 60);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'MILLER', 'Robert', 'robert.miller@company.com', '0705678901', TO_DATE('2023-02-15', 'YYYY-MM-DD'), 'Développeur Backend', 4500, NULL, 11, 60);

-- Employés du département Direction Générale (ID 90)
INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'WILSON', 'Patricia', 'patricia.wilson@company.com', '0706789012', TO_DATE('2018-01-10', 'YYYY-MM-DD'), 'PDG', 15000, NULL, NULL, 90);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'MOORE', 'Christopher', 'christopher.moore@company.com', '0707890123', TO_DATE('2019-03-20', 'YYYY-MM-DD'), 'Directeur Financier', 12000, NULL, 16, 90);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'TAYLOR', 'Linda', 'linda.taylor@company.com', '0708901234', TO_DATE('2019-06-15', 'YYYY-MM-DD'), 'Directrice Stratégie', 11000, NULL, 16, 90);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'ANDERSON', 'James', 'james.anderson@company.com', '0709012345', TO_DATE('2020-02-01', 'YYYY-MM-DD'), 'Directeur Opérations', 10500, NULL, 16, 90);

COMMIT;

-- ============================================
-- 7. MISE À JOUR DES SÉQUENCES
-- ============================================
-- Mettre à jour la séquence dept pour éviter les conflits
DECLARE
    v_max_id NUMBER;
BEGIN
    SELECT NVL(MAX(dept_id), 0) INTO v_max_id FROM DEPARTEMENTS;
    EXECUTE IMMEDIATE 'DROP SEQUENCE seq_dept';
    EXECUTE IMMEDIATE 'CREATE SEQUENCE seq_dept START WITH ' || (v_max_id + 1) || ' INCREMENT BY 1';
END;
/

-- Mettre à jour la séquence emp pour éviter les conflits
DECLARE
    v_max_id NUMBER;
BEGIN
    SELECT NVL(MAX(emp_id), 0) INTO v_max_id FROM EMPLOYES;
    EXECUTE IMMEDIATE 'DROP SEQUENCE seq_emp';
    EXECUTE IMMEDIATE 'CREATE SEQUENCE seq_emp START WITH ' || (v_max_id + 1) || ' INCREMENT BY 1';
END;
/

-- ============================================
-- 8. VÉRIFICATION DES DONNÉES
-- ============================================
PROMPT
PROMPT ================================================
PROMPT VÉRIFICATION DES DONNÉES AJOUTÉES
PROMPT ================================================

SELECT 'PAYS: ' || COUNT(*) AS info FROM PAYS
UNION ALL
SELECT 'LOCALISATIONS: ' || COUNT(*) FROM LOCALISATIONS
UNION ALL
SELECT 'DEPARTEMENTS TOTAL: ' || COUNT(*) FROM DEPARTEMENTS
UNION ALL
SELECT 'Département ID 60 (IT): ' || COUNT(*) FROM DEPARTEMENTS WHERE dept_id = 60
UNION ALL
SELECT 'Département ID 90: ' || COUNT(*) FROM DEPARTEMENTS WHERE dept_id = 90
UNION ALL
SELECT 'EMPLOYES TOTAL: ' || COUNT(*) FROM EMPLOYES
UNION ALL
SELECT 'Employés dept 60: ' || COUNT(*) FROM EMPLOYES WHERE dept_id = 60
UNION ALL
SELECT 'Employés dept 90: ' || COUNT(*) FROM EMPLOYES WHERE dept_id = 90
UNION ALL
SELECT 'Employés salaire 9000: ' || COUNT(*) FROM EMPLOYES WHERE salaire = 9000;

PROMPT
PROMPT ================================================
PROMPT TEST DES REQUÊTES DES EXERCICES
PROMPT ================================================

PROMPT
PROMPT 1. DISTINCT departement_id (dept_id):
SELECT DISTINCT dept_id FROM EMPLOYES WHERE dept_id IS NOT NULL ORDER BY dept_id;

PROMPT
PROMPT 2. Employés du département 60:
SELECT nom, prenom, poste FROM EMPLOYES WHERE dept_id = 60;

PROMPT
PROMPT 3. Employés avec salaire 9000:
SELECT nom, prenom, salaire FROM EMPLOYES WHERE salaire = 9000;

PROMPT
PROMPT 4. Employés des départements 60 ou 90:
SELECT nom, prenom, dept_id FROM EMPLOYES WHERE dept_id = 60 OR dept_id = 90;

PROMPT
PROMPT 5. Données France:
SELECT nom_pays, capitale, population FROM PAYS WHERE pays_code = 'FR';

PROMPT
PROMPT 6. Pays dont le nom commence par M ou après:
SELECT nom_pays, capitale FROM PAYS WHERE nom_pays >= 'M' ORDER BY nom_pays;

PROMPT
PROMPT 7. Pays avec population > 1 million:
SELECT nom_pays, population FROM PAYS WHERE population > 1000000 ORDER BY population DESC;

PROMPT
PROMPT 8. Localisation à Seattle:
SELECT adresse_rue, code_postal FROM LOCALISATIONS WHERE ville = 'Seattle';

PROMPT
PROMPT 9. Département IT (manager_id peut être NULL si pas encore défini):
SELECT dept_id, dept_nom FROM DEPARTEMENTS WHERE dept_nom = 'IT';

PROMPT
PROMPT ================================================
PROMPT Script terminé avec succès !
PROMPT ================================================
PROMPT
PROMPT NOTE IMPORTANTE:
PROMPT Dans vos exercices SQL, utilisez 'dept_id' au lieu de 'departement_id'
PROMPT car la colonne s'appelle 'dept_id' dans la table EMPLOYES.
PROMPT
PROMPT Exemple: SELECT DISTINCT dept_id FROM EMPLOYES WHERE dept_id IS NOT NULL
PROMPT ================================================