-- ============================================
-- SCHEMA DE BASE DE DONNEES POUR COURS PL/SQL
-- ============================================
-- Ce script crée toutes les tables nécessaires aux exercices PL/SQL

-- Désactiver les contraintes temporairement
BEGIN
   FOR c IN (SELECT table_name FROM user_tables WHERE table_name IN ('AUDIT_LOG', 'COMMANDES', 'DETAILS_COMMANDE', 'PRODUITS', 'CATEGORIES', 'CLIENTS', 'EMPLOYES', 'DEPARTEMENTS', 'PROJETS', 'AFFECTATIONS')) LOOP
      EXECUTE IMMEDIATE ('DROP TABLE ' || c.table_name || ' CASCADE CONSTRAINTS');
   END LOOP;
END;
/

-- ============================================
-- TABLE DEPARTEMENTS
-- ============================================
CREATE TABLE DEPARTEMENTS (
    dept_id NUMBER(4) PRIMARY KEY,
    dept_nom VARCHAR2(50) NOT NULL,
    localisation VARCHAR2(50),
    budget NUMBER(12,2) DEFAULT 0,
    date_creation DATE DEFAULT SYSDATE
);

-- ============================================
-- TABLE EMPLOYES
-- ============================================
CREATE TABLE EMPLOYES (
    emp_id NUMBER(6) PRIMARY KEY,
    nom VARCHAR2(50) NOT NULL,
    prenom VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    telephone VARCHAR2(20),
    date_embauche DATE DEFAULT SYSDATE,
    poste VARCHAR2(50),
    salaire NUMBER(10,2),
    commission NUMBER(8,2),
    manager_id NUMBER(6),
    dept_id NUMBER(4),
    actif CHAR(1) DEFAULT 'O' CHECK (actif IN ('O', 'N')),
    CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES EMPLOYES(emp_id),
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES DEPARTEMENTS(dept_id)
);

-- ============================================
-- TABLE PROJETS
-- ============================================
CREATE TABLE PROJETS (
    projet_id NUMBER(6) PRIMARY KEY,
    nom_projet VARCHAR2(100) NOT NULL,
    description VARCHAR2(500),
    date_debut DATE,
    date_fin DATE,
    budget NUMBER(12,2),
    statut VARCHAR2(20) DEFAULT 'EN_COURS' CHECK (statut IN ('PLANIFIE', 'EN_COURS', 'TERMINE', 'ANNULE')),
    dept_id NUMBER(4),
    CONSTRAINT fk_projet_dept FOREIGN KEY (dept_id) REFERENCES DEPARTEMENTS(dept_id)
);

-- ============================================
-- TABLE AFFECTATIONS (relation N-N entre EMPLOYES et PROJETS)
-- ============================================
CREATE TABLE AFFECTATIONS (
    affectation_id NUMBER(8) PRIMARY KEY,
    emp_id NUMBER(6) NOT NULL,
    projet_id NUMBER(6) NOT NULL,
    date_debut DATE DEFAULT SYSDATE,
    date_fin DATE,
    role VARCHAR2(50),
    heures_prevues NUMBER(6,2),
    heures_reelles NUMBER(6,2) DEFAULT 0,
    CONSTRAINT fk_affect_emp FOREIGN KEY (emp_id) REFERENCES EMPLOYES(emp_id),
    CONSTRAINT fk_affect_projet FOREIGN KEY (projet_id) REFERENCES PROJETS(projet_id),
    CONSTRAINT uk_emp_projet UNIQUE (emp_id, projet_id)
);

-- ============================================
-- TABLE CATEGORIES (pour produits)
-- ============================================
CREATE TABLE CATEGORIES (
    cat_id NUMBER(4) PRIMARY KEY,
    nom_categorie VARCHAR2(50) NOT NULL,
    description VARCHAR2(200)
);

-- ============================================
-- TABLE PRODUITS
-- ============================================
CREATE TABLE PRODUITS (
    prod_id NUMBER(6) PRIMARY KEY,
    nom_produit VARCHAR2(100) NOT NULL,
    description VARCHAR2(500),
    prix_unitaire NUMBER(10,2) NOT NULL,
    stock_disponible NUMBER(8) DEFAULT 0,
    stock_minimum NUMBER(8) DEFAULT 10,
    cat_id NUMBER(4),
    actif CHAR(1) DEFAULT 'O' CHECK (actif IN ('O', 'N')),
    date_creation DATE DEFAULT SYSDATE,
    CONSTRAINT fk_prod_cat FOREIGN KEY (cat_id) REFERENCES CATEGORIES(cat_id),
    CONSTRAINT chk_prix CHECK (prix_unitaire > 0)
);

-- ============================================
-- TABLE CLIENTS
-- ============================================
CREATE TABLE CLIENTS (
    client_id NUMBER(8) PRIMARY KEY,
    nom VARCHAR2(50) NOT NULL,
    prenom VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    telephone VARCHAR2(20),
    adresse VARCHAR2(200),
    ville VARCHAR2(50),
    code_postal VARCHAR2(10),
    pays VARCHAR2(50) DEFAULT 'France',
    date_inscription DATE DEFAULT SYSDATE,
    actif CHAR(1) DEFAULT 'O' CHECK (actif IN ('O', 'N'))
);

-- ============================================
-- TABLE COMMANDES
-- ============================================
CREATE TABLE COMMANDES (
    commande_id NUMBER(10) PRIMARY KEY,
    client_id NUMBER(8) NOT NULL,
    date_commande DATE DEFAULT SYSDATE,
    date_livraison DATE,
    statut VARCHAR2(20) DEFAULT 'EN_ATTENTE' CHECK (statut IN ('EN_ATTENTE', 'VALIDEE', 'EXPEDIE', 'LIVREE', 'ANNULEE')),
    montant_total NUMBER(12,2) DEFAULT 0,
    mode_paiement VARCHAR2(30),
    adresse_livraison VARCHAR2(200),
    CONSTRAINT fk_cmd_client FOREIGN KEY (client_id) REFERENCES CLIENTS(client_id)
);

-- ============================================
-- TABLE DETAILS_COMMANDE
-- ============================================
CREATE TABLE DETAILS_COMMANDE (
    detail_id NUMBER(12) PRIMARY KEY,
    commande_id NUMBER(10) NOT NULL,
    prod_id NUMBER(6) NOT NULL,
    quantite NUMBER(6) NOT NULL,
    prix_unitaire NUMBER(10,2) NOT NULL,
    reduction NUMBER(5,2) DEFAULT 0,
    montant_ligne NUMBER(12,2),
    CONSTRAINT fk_detail_cmd FOREIGN KEY (commande_id) REFERENCES COMMANDES(commande_id),
    CONSTRAINT fk_detail_prod FOREIGN KEY (prod_id) REFERENCES PRODUITS(prod_id),
    CONSTRAINT chk_quantite CHECK (quantite > 0)
);

-- ============================================
-- TABLE AUDIT_LOG (pour tracking des modifications)
-- ============================================
CREATE TABLE AUDIT_LOG (
    log_id NUMBER(12) PRIMARY KEY,
    table_name VARCHAR2(50) NOT NULL,
    operation VARCHAR2(10) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    record_id NUMBER(12),
    ancien_valeur VARCHAR2(4000),
    nouvelle_valeur VARCHAR2(4000),
    utilisateur VARCHAR2(50) DEFAULT USER,
    date_operation DATE DEFAULT SYSDATE,
    ip_address VARCHAR2(50)
);

-- ============================================
-- SEQUENCES pour les clés primaires
-- ============================================
CREATE SEQUENCE seq_dept START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_emp START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_projet START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_affectation START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_categorie START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_produit START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_client START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_commande START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_detail START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_audit START WITH 1 INCREMENT BY 1;

-- ============================================
-- DONNEES DE TEST
-- ============================================

-- Départements
INSERT INTO DEPARTEMENTS VALUES (seq_dept.NEXTVAL, 'Informatique', 'Paris', 500000, SYSDATE);
INSERT INTO DEPARTEMENTS VALUES (seq_dept.NEXTVAL, 'Ressources Humaines', 'Lyon', 300000, SYSDATE);
INSERT INTO DEPARTEMENTS VALUES (seq_dept.NEXTVAL, 'Commercial', 'Marseille', 450000, SYSDATE);
INSERT INTO DEPARTEMENTS VALUES (seq_dept.NEXTVAL, 'Finance', 'Paris', 400000, SYSDATE);
INSERT INTO DEPARTEMENTS VALUES (seq_dept.NEXTVAL, 'Marketing', 'Bordeaux', 350000, SYSDATE);

-- Employés (sans manager d'abord)
INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, dept_id)
VALUES (seq_emp.NEXTVAL, 'MARTIN', 'Sophie', 'sophie.martin@company.com', '0612345678', TO_DATE('2020-01-15', 'YYYY-MM-DD'), 'Directrice IT', 6500, NULL, 1);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, dept_id)
VALUES (seq_emp.NEXTVAL, 'DUBOIS', 'Marc', 'marc.dubois@company.com', '0623456789', TO_DATE('2019-03-20', 'YYYY-MM-DD'), 'Directeur RH', 5800, NULL, 2);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, dept_id)
VALUES (seq_emp.NEXTVAL, 'BERNARD', 'Julie', 'julie.bernard@company.com', '0634567890', TO_DATE('2018-06-10', 'YYYY-MM-DD'), 'Directrice Commercial', 6000, 500, 3);

-- Employés avec manager
INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'LEROY', 'Thomas', 'thomas.leroy@company.com', '0645678901', TO_DATE('2021-02-01', 'YYYY-MM-DD'), 'Développeur Senior', 4500, NULL, 1, 1);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'MOREAU', 'Emma', 'emma.moreau@company.com', '0656789012', TO_DATE('2022-04-15', 'YYYY-MM-DD'), 'Développeur Junior', 3200, NULL, 4, 1);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'SIMON', 'Lucas', 'lucas.simon@company.com', '0667890123', TO_DATE('2021-09-01', 'YYYY-MM-DD'), 'Commercial Senior', 3800, 800, 3, 3);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'LAURENT', 'Camille', 'camille.laurent@company.com', '0678901234', TO_DATE('2023-01-10', 'YYYY-MM-DD'), 'Commercial Junior', 2800, 400, 3, 3);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'PETIT', 'Antoine', 'antoine.petit@company.com', '0689012345', TO_DATE('2022-08-20', 'YYYY-MM-DD'), 'Chargé RH', 3000, NULL, 2, 2);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'ROUX', 'Léa', 'lea.roux@company.com', '0690123456', TO_DATE('2020-11-05', 'YYYY-MM-DD'), 'Comptable', 3500, NULL, NULL, 4);

INSERT INTO EMPLOYES (emp_id, nom, prenom, email, telephone, date_embauche, poste, salaire, commission, manager_id, dept_id)
VALUES (seq_emp.NEXTVAL, 'FOURNIER', 'Hugo', 'hugo.fournier@company.com', '0601234567', TO_DATE('2023-03-15', 'YYYY-MM-DD'), 'Stagiaire Dev', 1500, NULL, 4, 1);

-- Projets
INSERT INTO PROJETS VALUES (seq_projet.NEXTVAL, 'Refonte Site Web', 'Migration vers nouvelle plateforme', TO_DATE('2024-01-01', 'YYYY-MM-DD'), TO_DATE('2024-06-30', 'YYYY-MM-DD'), 150000, 'EN_COURS', 1);
INSERT INTO PROJETS VALUES (seq_projet.NEXTVAL, 'CRM Client', 'Mise en place nouveau CRM', TO_DATE('2024-02-01', 'YYYY-MM-DD'), TO_DATE('2024-08-31', 'YYYY-MM-DD'), 200000, 'EN_COURS', 3);
INSERT INTO PROJETS VALUES (seq_projet.NEXTVAL, 'Formation Interne', 'Programme de formation employés', TO_DATE('2024-01-15', 'YYYY-MM-DD'), TO_DATE('2024-12-31', 'YYYY-MM-DD'), 80000, 'PLANIFIE', 2);

-- Affectations
INSERT INTO AFFECTATIONS VALUES (seq_affectation.NEXTVAL, 4, 1, TO_DATE('2024-01-01', 'YYYY-MM-DD'), NULL, 'Chef de projet', 500, 320);
INSERT INTO AFFECTATIONS VALUES (seq_affectation.NEXTVAL, 5, 1, TO_DATE('2024-01-15', 'YYYY-MM-DD'), NULL, 'Développeur', 600, 450);
INSERT INTO AFFECTATIONS VALUES (seq_affectation.NEXTVAL, 10, 1, TO_DATE('2024-02-01', 'YYYY-MM-DD'), NULL, 'Développeur', 400, 280);
INSERT INTO AFFECTATIONS VALUES (seq_affectation.NEXTVAL, 6, 2, TO_DATE('2024-02-01', 'YYYY-MM-DD'), NULL, 'Responsable', 400, 200);

-- Catégories
INSERT INTO CATEGORIES VALUES (seq_categorie.NEXTVAL, 'Électronique', 'Produits électroniques et informatiques');
INSERT INTO CATEGORIES VALUES (seq_categorie.NEXTVAL, 'Mobilier', 'Meubles de bureau');
INSERT INTO CATEGORIES VALUES (seq_categorie.NEXTVAL, 'Fournitures', 'Fournitures de bureau');
INSERT INTO CATEGORIES VALUES (seq_categorie.NEXTVAL, 'Logiciels', 'Licences et logiciels');

-- Produits
INSERT INTO PRODUITS VALUES (seq_produit.NEXTVAL, 'Ordinateur Portable HP', 'HP EliteBook 840 G8', 1200.00, 25, 5, 1, 'O', SYSDATE);
INSERT INTO PRODUITS VALUES (seq_produit.NEXTVAL, 'Souris Sans Fil', 'Logitech MX Master 3', 89.99, 150, 20, 1, 'O', SYSDATE);
INSERT INTO PRODUITS VALUES (seq_produit.NEXTVAL, 'Clavier Mécanique', 'Clavier RGB Gaming', 129.99, 80, 15, 1, 'O', SYSDATE);
INSERT INTO PRODUITS VALUES (seq_produit.NEXTVAL, 'Bureau Ajustable', 'Bureau électrique hauteur réglable', 599.00, 15, 3, 2, 'O', SYSDATE);
INSERT INTO PRODUITS VALUES (seq_produit.NEXTVAL, 'Chaise Ergonomique', 'Chaise de bureau premium', 399.00, 30, 5, 2, 'O', SYSDATE);
INSERT INTO PRODUITS VALUES (seq_produit.NEXTVAL, 'Ramette Papier A4', 'Papier 80g - 500 feuilles', 5.99, 500, 100, 3, 'O', SYSDATE);
INSERT INTO PRODUITS VALUES (seq_produit.NEXTVAL, 'Stylos Bleus', 'Pack de 10 stylos', 8.50, 200, 50, 3, 'O', SYSDATE);
INSERT INTO PRODUITS VALUES (seq_produit.NEXTVAL, 'Licence Office 365', 'Abonnement annuel', 149.00, 100, 10, 4, 'O', SYSDATE);
INSERT INTO PRODUITS VALUES (seq_produit.NEXTVAL, 'Antivirus Pro', 'Protection 5 postes', 79.99, 50, 10, 4, 'O', SYSDATE);

-- Clients
INSERT INTO CLIENTS VALUES (seq_client.NEXTVAL, 'DUPONT', 'Pierre', 'pierre.dupont@email.com', '0611111111', '10 Rue de la Paix', 'Paris', '75001', 'France', TO_DATE('2023-01-10', 'YYYY-MM-DD'), 'O');
INSERT INTO CLIENTS VALUES (seq_client.NEXTVAL, 'GARCIA', 'Marie', 'marie.garcia@email.com', '0622222222', '25 Avenue des Champs', 'Lyon', '69001', 'France', TO_DATE('2023-02-15', 'YYYY-MM-DD'), 'O');
INSERT INTO CLIENTS VALUES (seq_client.NEXTVAL, 'RODRIGUEZ', 'Jean', 'jean.rodriguez@email.com', '0633333333', '5 Boulevard du Soleil', 'Marseille', '13001', 'France', TO_DATE('2023-03-20', 'YYYY-MM-DD'), 'O');
INSERT INTO CLIENTS VALUES (seq_client.NEXTVAL, 'MARTINEZ', 'Sophie', 'sophie.martinez@email.com', '0644444444', '30 Rue Victor Hugo', 'Toulouse', '31000', 'France', TO_DATE('2023-04-05', 'YYYY-MM-DD'), 'O');
INSERT INTO CLIENTS VALUES (seq_client.NEXTVAL, 'THOMAS', 'Luc', 'luc.thomas@email.com', '0655555555', '15 Place de la Mairie', 'Nice', '06000', 'France', TO_DATE('2023-05-12', 'YYYY-MM-DD'), 'O');

-- Commandes
INSERT INTO COMMANDES VALUES (seq_commande.NEXTVAL, 1, TO_DATE('2024-01-15', 'YYYY-MM-DD'), TO_DATE('2024-01-20', 'YYYY-MM-DD'), 'LIVREE', 0, 'CARTE', '10 Rue de la Paix, 75001 Paris');
INSERT INTO COMMANDES VALUES (seq_commande.NEXTVAL, 2, TO_DATE('2024-01-20', 'YYYY-MM-DD'), TO_DATE('2024-01-25', 'YYYY-MM-DD'), 'LIVREE', 0, 'VIREMENT', '25 Avenue des Champs, 69001 Lyon');
INSERT INTO COMMANDES VALUES (seq_commande.NEXTVAL, 1, TO_DATE('2024-02-01', 'YYYY-MM-DD'), NULL, 'VALIDEE', 0, 'CARTE', '10 Rue de la Paix, 75001 Paris');
INSERT INTO COMMANDES VALUES (seq_commande.NEXTVAL, 3, TO_DATE('2024-02-05', 'YYYY-MM-DD'), NULL, 'EN_ATTENTE', 0, 'CHEQUE', '5 Boulevard du Soleil, 13001 Marseille');
INSERT INTO COMMANDES VALUES (seq_commande.NEXTVAL, 4, TO_DATE('2024-02-10', 'YYYY-MM-DD'), NULL, 'VALIDEE', 0, 'CARTE', '30 Rue Victor Hugo, 31000 Toulouse');

-- Détails de commande
INSERT INTO DETAILS_COMMANDE VALUES (seq_detail.NEXTVAL, 1, 1, 2, 1200.00, 5.00, 0);
INSERT INTO DETAILS_COMMANDE VALUES (seq_detail.NEXTVAL, 1, 2, 2, 89.99, 0, 0);

INSERT INTO DETAILS_COMMANDE VALUES (seq_detail.NEXTVAL, 2, 4, 1, 599.00, 0, 0);
INSERT INTO DETAILS_COMMANDE VALUES (seq_detail.NEXTVAL, 2, 5, 1, 399.00, 10.00, 0);

INSERT INTO DETAILS_COMMANDE VALUES (seq_detail.NEXTVAL, 3, 6, 10, 5.99, 0, 0);
INSERT INTO DETAILS_COMMANDE VALUES (seq_detail.NEXTVAL, 3, 7, 5, 8.50, 0, 0);

INSERT INTO DETAILS_COMMANDE VALUES (seq_detail.NEXTVAL, 4, 8, 3, 149.00, 0, 0);

INSERT INTO DETAILS_COMMANDE VALUES (seq_detail.NEXTVAL, 5, 1, 1, 1200.00, 0, 0);
INSERT INTO DETAILS_COMMANDE VALUES (seq_detail.NEXTVAL, 5, 3, 1, 129.99, 0, 0);
INSERT INTO DETAILS_COMMANDE VALUES (seq_detail.NEXTVAL, 5, 9, 1, 79.99, 0, 0);

COMMIT;

-- ============================================
-- VERIFICATION DES DONNEES
-- ============================================
SELECT 'DEPARTEMENTS: ' || COUNT(*) AS info FROM DEPARTEMENTS
UNION ALL
SELECT 'EMPLOYES: ' || COUNT(*) FROM EMPLOYES
UNION ALL
SELECT 'PROJETS: ' || COUNT(*) FROM PROJETS
UNION ALL
SELECT 'AFFECTATIONS: ' || COUNT(*) FROM AFFECTATIONS
UNION ALL
SELECT 'CATEGORIES: ' || COUNT(*) FROM CATEGORIES
UNION ALL
SELECT 'PRODUITS: ' || COUNT(*) FROM PRODUITS
UNION ALL
SELECT 'CLIENTS: ' || COUNT(*) FROM CLIENTS
UNION ALL
SELECT 'COMMANDES: ' || COUNT(*) FROM COMMANDES
UNION ALL
SELECT 'DETAILS_COMMANDE: ' || COUNT(*) FROM DETAILS_COMMANDE;

PROMPT
PROMPT ================================================
PROMPT Schema PL/SQL créé avec succès !
PROMPT ================================================
PROMPT Activez l'affichage avec: SET SERVEROUTPUT ON
PROMPT ================================================