DROP TABLE alimente CASCADE CONSTRAINTS;
DROP TABLE fournit CASCADE CONSTRAINTS;
DROP TABLE contient CASCADE CONSTRAINTS;
DROP TABLE possede CASCADE CONSTRAINTS;
DROP TABLE stock CASCADE CONSTRAINTS;
DROP TABLE recommandation CASCADE CONSTRAINTS;
DROP TABLE historique_de_vente CASCADE CONSTRAINTS;
DROP TABLE facture CASCADE CONSTRAINTS;
DROP TABLE commande CASCADE CONSTRAINTS;
DROP TABLE fournisseur CASCADE CONSTRAINTS;
DROP TABLE produit CASCADE CONSTRAINTS;
DROP TABLE client CASCADE CONSTRAINTS;
DROP TABLE magasin CASCADE CONSTRAINTS;
DROP TABLE commercial CASCADE CONSTRAINTS;




CREATE TABLE commercial (
  id_commercant NUMBER(10) PRIMARY KEY,
  nom_commercant VARCHAR(100) NOT NULL CHECK (LENGTH(nom_commercant) >= 2),
  prenom_commercant VARCHAR(100) NOT NULL CHECK (LENGTH(prenom_commercant) >= 2),
  email_commercant VARCHAR(150) NOT NULL UNIQUE
    CHECK (email_commercant LIKE '%_@__%.__%'),
  telephone_commercant VARCHAR(15) NOT NULL
    CHECK (telephone_commercant NOT LIKE '%[^0-9]%' AND LENGTH(telephone_commercant) BETWEEN 8 AND 15)
);

CREATE TABLE magasin (
  id_magasin NUMBER(10) PRIMARY KEY,
  nom_magasin VARCHAR(100) NOT NULL CHECK (LENGTH(nom_magasin) >= 2),
  localisation VARCHAR(100) NOT NULL CHECK (LENGTH(localisation) >= 2)
);

CREATE TABLE client (
  id_client NUMBER(10) PRIMARY KEY,
  nom_client VARCHAR(150) NOT NULL CHECK (LENGTH(nom_client) >= 2),
  adresse_client VARCHAR(255) NOT NULL CHECK (LENGTH(adresse_client) >= 5),
  email_client VARCHAR(150) NOT NULL UNIQUE
    CHECK (email_client LIKE '%_@__%.__%'),
  telephone_client VARCHAR(15) NOT NULL
    CHECK (telephone_client NOT LIKE '%[^0-9]%' AND LENGTH(telephone_client) BETWEEN 8 AND 15)
);

CREATE TABLE produit (
  id_produit NUMBER(10) PRIMARY KEY,
  nom_produit VARCHAR(120) NOT NULL CHECK (LENGTH(nom_produit) >= 2),
  reference_interne VARCHAR(50) NOT NULL UNIQUE CHECK (LENGTH(reference_interne) >= 3),
  prix_unitaire NUMBER(10,2) NOT NULL CHECK (prix_unitaire >= 0),
  description VARCHAR(255)
);

CREATE TABLE fournisseur (
  id_fournisseur NUMBER(10) PRIMARY KEY,
  nom_fournisseur VARCHAR(120) NOT NULL CHECK (LENGTH(nom_fournisseur) >= 2),
  adresse_fournisseur VARCHAR(255) NOT NULL CHECK (LENGTH(adresse_fournisseur) >= 5),
  tel_fournisseur VARCHAR(15) NOT NULL
    CHECK (tel_fournisseur NOT LIKE '%[^0-9]%' AND LENGTH(tel_fournisseur) BETWEEN 8 AND 15),
  mail_fournisseur VARCHAR(150) NOT NULL UNIQUE
    CHECK (mail_fournisseur LIKE '%_@__%.__%')
);

CREATE TABLE commande (
  id_commande NUMBER(10) PRIMARY KEY,
  date_commande DATE NOT NULL,
  date_livraison DATE,
  etat_commande VARCHAR(20) NOT NULL
    CHECK (etat_commande IN ('en cours','livree','annulee')),
  CHECK (date_livraison IS NULL OR date_livraison >= date_commande),
  id_client NUMBER(10) NOT NULL,
  id_commercant NUMBER(10) NOT NULL,
  FOREIGN KEY (id_client) REFERENCES client(id_client),
  FOREIGN KEY (id_commercant) REFERENCES commercial(id_commercant)
);


CREATE TABLE facture (
  id_facture NUMBER(10) PRIMARY KEY,
  date_facture DATE NOT NULL,
  montant_total NUMBER(12,2) NOT NULL CHECK (montant_total >= 0),
  statut_facture VARCHAR(20) NOT NULL
    CHECK (statut_facture IN ('payee','en attente','annulee')),
  id_commande NUMBER(10) NOT NULL UNIQUE,
  FOREIGN KEY (id_commande) REFERENCES commande(id_commande)
);

CREATE TABLE historique_de_vente (
  id_vente NUMBER(10) PRIMARY KEY,
  id_commande NUMBER(10) NOT NULL,
  id_produit NUMBER(10) NOT NULL,
  id_client NUMBER(10) NOT NULL,
  quantite_vendue NUMBER(10) NOT NULL CHECK (quantite_vendue > 0),
  prix_vente NUMBER(10,2) NOT NULL CHECK (prix_vente >= 0),
  date_vente DATE NOT NULL,
  FOREIGN KEY (id_commande) REFERENCES commande(id_commande),
  FOREIGN KEY (id_produit) REFERENCES produit(id_produit),
  FOREIGN KEY (id_client) REFERENCES client(id_client)
);

CREATE TABLE recommandation (
  id_recommandation NUMBER(10) PRIMARY KEY,
  id_commercant NUMBER(10) NOT NULL,
  id_produit NUMBER(10) NOT NULL,
  id_client NUMBER(10) NOT NULL,
  score_recommandation NUMBER(1) NOT NULL
    CHECK (score_recommandation BETWEEN 1 AND 5),
  raison VARCHAR(255),
  FOREIGN KEY (id_commercant) REFERENCES commercial(id_commercant),
  FOREIGN KEY (id_produit) REFERENCES produit(id_produit),
  FOREIGN KEY (id_client) REFERENCES client(id_client)
);

CREATE TABLE stock (
  id_stock NUMBER(10) PRIMARY KEY,
  id_produit NUMBER(10) NOT NULL,
  quantite_disponible NUMBER(10) NOT NULL CHECK (quantite_disponible >= 0),
  seuil_alerte NUMBER(10) NOT NULL CHECK (seuil_alerte >= 0),
  numero_lot VARCHAR(30) NOT NULL CHECK (LENGTH(numero_lot) >= 3),
  UNIQUE (id_produit, numero_lot),
  FOREIGN KEY (id_produit) REFERENCES produit(id_produit)
);

CREATE TABLE possede (
  id_client NUMBER(10) NOT NULL,
  id_magasin NUMBER(10) NOT NULL,
  PRIMARY KEY (id_client, id_magasin),
  FOREIGN KEY (id_client) REFERENCES client(id_client),
  FOREIGN KEY (id_magasin) REFERENCES magasin(id_magasin)
);

CREATE TABLE contient (
  id_commande NUMBER(10) NOT NULL,
  id_produit NUMBER(10) NOT NULL,
  quantite_commandee NUMBER(10) NOT NULL CHECK (quantite_commandee > 0),
  PRIMARY KEY (id_commande, id_produit),
  FOREIGN KEY (id_commande) REFERENCES commande(id_commande),
  FOREIGN KEY (id_produit) REFERENCES produit(id_produit)
);

CREATE TABLE fournit (
  id_fournisseur NUMBER(10) NOT NULL,
  id_produit NUMBER(10) NOT NULL,
  PRIMARY KEY (id_fournisseur, id_produit),
  FOREIGN KEY (id_fournisseur) REFERENCES fournisseur(id_fournisseur),
  FOREIGN KEY (id_produit) REFERENCES produit(id_produit)
);

CREATE TABLE alimente (
  id_vente NUMBER(10) NOT NULL,
  id_recommandation NUMBER(10) NOT NULL,
  PRIMARY KEY (id_vente, id_recommandation),
  FOREIGN KEY (id_vente) REFERENCES historique_de_vente(id_vente),
  FOREIGN KEY (id_recommandation) REFERENCES recommandation(id_recommandation)
);
