database.sql

--
-- Domaine représentant les pays que les utilisateurs peuvent renseigner comme adresse de domicile.
--
CREATE DOMAIN pays
    AS TEXT
    CHECK (
        value IN (
            'France'
        )
    );


--
-- Table représentant l'adresse des utilisateurs.
--
CREATE TABLE adresse (
    id SERIAL NOT NULL PRIMARY KEY, -- SERIAL <=> AUTOINCREMENT sur PostgreSQL
    pays pays NOT NULL,
    code_postal TEXT NOT NULL,
    rue TEXT NOT NULL,
    numero TEXT NOT NULL,
    complement TEXT
);

INSERT INTO adresse VALUES
    (DEFAULT, 'France', '93390', 'rue de Strasbourg', '46', NULL),
    (DEFAULT, 'France', '21000', 'rue des lieutenants Thomazo', '109', NULL),
    (DEFAULT, 'France', '57050', 'rue St Ferréol', '144b', NULL),
    (DEFAULT, 'France', '88630', 'rue de la Basilique', '2', NULL),
    (DEFAULT, 'France', '91600', 'rue du Président Roosevelt', '127', 'App. 7');


--
-- Table représentant les utilisateurs.
--
CREATE TABLE utilisateur (
    id SERIAL NOT NULL PRIMARY KEY, -- SERIAL <=> AUTOINCREMENT sur PostgreSQL
    nom TEXT NOT NULL,
    prenom TEXT NOT NULL,
    mail TEXT NOT NULL UNIQUE,
    tel TEXT NOT NULL,
    adresse INTEGER NOT NULL REFERENCES adresse(id)
);

INSERT INTO utilisateur VALUES
    (DEFAULT, 'Ferrau', 'Huppé', 'ferrau.huppe@yahoo.fr', '01.11.92.28', 1),
    (DEFAULT, 'Blanchefle', 'Robitaille', 'banchefle-robitaille@gmail.com', '03 36 16 24', 2),
    (DEFAULT, 'Ferrau', 'Patrice', 'patate482@yahoo.fr', '01.11.92.28', 1),
    (DEFAULT, 'Gradasso', 'Bilodeau', 'gradasso@google.com', '+33 3 35 61 28', 3),
    (DEFAULT, 'd''Arc', 'Jeanne', 'jeannedarc@vosges.fr', '0329069586', 4),
    (DEFAULT, 'Romée', 'Isabelle', 'isabelleromee@vosges.fr', '0329069586', 4),
    (DEFAULT, 'd''Arc', 'Jacques', 'jacquesdarc@vosges.fr', '03 29 06 95 86', 4),
    (DEFAULT, 'd''Arc', 'Catherine', 'catherine@vosges.fr', '0329 0695 86', 4),
    (DEFAULT, 'de Chateaub', 'Royce', 'royce@dechateaub.co.uk', '014932645', 5);


--
-- Table contenant les catégories disponibles pour les articles.
--
CREATE TABLE categorie (
    id INTEGER NOT NULL PRIMARY KEY,
    nom TEXT NOT NULL
);

INSERT INTO categorie VALUES
    (1, 'Smartphone'),
    (2, 'PC'),
    (3, 'Tablette'),
    (4, 'Audio'),
    (5, 'Caméra');


--
-- Table contenant tous les articles, même ceux achetés.
--
CREATE TABLE article (
    reference TEXT NOT NULL PRIMARY KEY,
    categorie INTEGER NOT NULL REFERENCES categorie(id),
    vendeur INTEGER NOT NULL REFERENCES utilisateur(id),
    prix REAL NOT NULL CHECK ( prix >= 0 AND prix <> 'NaN' AND prix <> 'Infinity' ),
    description TEXT,
    marque TEXT NOT NULL,
    serie TEXT NOT NULL,
    modele TEXT NOT NULL
);

INSERT INTO article VALUES
    ('abricot', 1, 1, 500, 'Un iPhone 13.', 'Apple', 'iPhone', 'iPhone 13'),
    ('banane', 1, 2, 620, 'iPhone 13, encore emballé.', 'Apple', 'iPhone', 'iPhone 13'),
    ('courgette', 1, 2, 15, 'Téléphone résistant aux chocs', 'Nokia', 'Nokia 105', 'Nokia 105'),
    ('datte', 2, 3, 4200, 'Écran 27 pouces, 8 coeurs, Intel Xeon W (3.2 GHz), Radeon Pro Vega 56, 1 To SDD', 'Apple', 'iMac', 'iMac Pro'),
    ('érable', 2, 4, 260, 'Beau comme un dindon sorti du four.', 'Lenovo', 'Lenovo', 'Lenovo L540'),
    ('figue', 3, 9, 700, 'Prix de Noël !', 'Apple', 'iPad', 'iPad Pro'),
    ('goyave', 4, 5, 830, 'Couleur: noyer', 'Focal', 'Aria', 'Aria 936'),
    ('haricot', 4, 6, 11490, NULL, 'Klipsch', 'Klipschorn', 'Klipschorn AK6'),
    ('icaque', 4, 1, 90, 'Haut-parleurs 5.1', 'Logitech', 'Logitech Z607', 'Logitech Z607'),
    ('jamalac', 5, 4, 400, '24.1 mégapixels', 'Canon', 'Canon EOS', 'Canon EOS 2000D');


--
-- Table contenant la référence des articles achetables
--
CREATE TABLE article_en_vente (
    article TEXT NOT NULL PRIMARY KEY REFERENCES article(reference) ON DELETE CASCADE
);

INSERT INTO article_en_vente (SELECT reference FROM article);


--
-- Vue équivalente à la table `article`, ne contenant que les articles présents dans la table `article_en_vente`.
--
CREATE VIEW articles_en_vente AS
    SELECT article.reference, article.categorie, article.vendeur, article.prix, article.description, article.marque, article.serie, article.modele
    FROM article
    WHERE (
        article.reference IN (
            SELECT article_en_vente.article
            FROM article_en_vente
        )
    );


--
-- Trigger: Après l'ajout d'un tuple dans la table `article`, le met directement en vente dans la table `article_en_vente`.
--
CREATE FUNCTION sync_articles_en_vente()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO article_en_vente VALUES (NEW.reference);

    RETURN NULL;
END;
$$;

CREATE TRIGGER sync_articles_en_vente_trigger
    AFTER INSERT
    ON article
    FOR EACH ROW
    EXECUTE FUNCTION sync_articles_en_vente();


--
-- Table représentant l'achat d'un article.
--
CREATE TABLE achat (
    acheteur INTEGER NOT NULL REFERENCES utilisateur(id),
    article TEXT NOT NULL PRIMARY KEY REFERENCES article(reference)
);


--
-- Table représentant la vente d'un article.
--
CREATE TABLE vente (
    vendeur INTEGER NOT NULL REFERENCES utilisateur(id),
    article TEXT NOT NULL PRIMARY KEY REFERENCES article(reference)
);


--
-- Trigger: Après l'ajout ou la suppresion d'un tuple dans la table `achat`, répercute le changement dans `vente`.
--
CREATE FUNCTION sync_achat_vente()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO vente (
            SELECT article.vendeur, article.reference FROM article
            WHERE article.reference = NEW.article -- jointure pour récupérer le vendeur à partir de la référence de l'article
        );

    ELSEIF TG_OP = 'DELETE' THEN
        DELETE FROM vente
        WHERE vente.article = OLD.article;

    ELSE
        RAISE EXCEPTION 'entered unreachable code: TG_OP = %', TG_OP;

    END IF;

    RETURN NULL;
END;
$$;

CREATE TRIGGER sync_achat_vente_trigger
    AFTER INSERT OR DELETE
    ON achat
    FOR EACH ROW
    EXECUTE FUNCTION sync_achat_vente();

--
-- Effectue une recherche à partir des filtres passés en paramètre.
--
CREATE FUNCTION recherche(search_input TEXT DEFAULT NULL, categorie_filter INTEGER DEFAULT NULL, price_min REAL DEFAULT 0, price_max REAL DEFAULT 'Infinity', ordering INTEGER DEFAULT 0)
    RETURNS TABLE(reference TEXT, modele TEXT, prix REAL, nom_vendeur TEXT, prenom_vendeur TEXT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- résultat de la recherche désordonnée
    CREATE TEMPORARY TABLE unordered(reference text, modele text, prix real, nom_vendeur text, prenom_vendeur text);

    INSERT INTO unordered (
        SELECT article.reference, article.modele, article.prix, utilisateur.nom, utilisateur.prenom FROM articles_en_vente article, utilisateur
            WHERE article.vendeur = utilisateur.id -- jointure pour réunir les articles et le nom du vendeur sur la même ligne
            AND (
                -- filtre avec les mots à rechercher, si existants
                search_input IS NULL
                OR LOWER(article.description) LIKE LOWER(search_input)
                OR LOWER(article.marque) LIKE LOWER(search_input)
                OR LOWER(article.serie) LIKE LOWER(search_input)
                OR LOWER(article.modele) LIKE LOWER(search_input)
            )
            AND (
                categorie_filter IS NULL
                OR article.categorie = categorie_filter
            )
            AND (
                article.prix >= price_min
                AND article.prix <= price_max
            )
    );

    IF ordering = 0 THEN
        RETURN QUERY SELECT * FROM unordered;

    ELSEIF ordering = 1 THEN
        RETURN QUERY SELECT * FROM unordered ORDER BY prix ASC;

    ELSEIF ordering = 2 THEN
        RETURN QUERY SELECT * FROM unordered ORDER BY prix DESC;

    ELSE
        RAISE EXCEPTION 'unknow ordering %', ordering;

    END IF;

    DROP TABLE unordered;
END;
$$;


--
-- Renvoie l'historique d'achat de l'utilisateur `user_id`.
--
CREATE FUNCTION historique_achat(user_id INTEGER)
    RETURNS TABLE(reference TEXT, modele TEXT, prix REAL, nom_vendeur TEXT, prenom_vendeur TEXT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
        SELECT article.reference, article.modele, article.prix, utilisateur.nom, utilisateur.prenom FROM article, utilisateur
        WHERE article.vendeur = utilisateur.id -- jointure pour réunir les articles et le nom du vendeur sur la même ligne
        AND article.reference IN (
            SELECT article FROM achat
            WHERE achat.acheteur = user_id -- l'on ne prend que les articles qui ont étés achetés par `user_id`
        );
END;
$$;


--
-- Renvoie l'historique de vente de l'utilisateur `user_id`.
--
CREATE FUNCTION historique_vente(user_id INTEGER)
    RETURNS TABLE(reference TEXT, modele TEXT, prix REAL, nom_acheteur TEXT, prenom_acheteur TEXT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
        SELECT article.reference, article.modele, article.prix, utilisateur.nom, utilisateur.prenom FROM article, achat, utilisateur
            WHERE article.reference IN (
                SELECT vente.article FROM vente
                WHERE vente.vendeur = user_id -- l'on ne prend que les articles qui ont étés vendus par `user_id`
            )
            AND achat.article = article.reference -- jointure pour récupérer les details de l'achat depuis la table `article` à travers la table `achat`
            AND utilisateur.id = achat.acheteur; -- jointure pour réunir les articles et le nom de l'acheteur sur la même ligne
END;
$$;


--
-- Renvoie les articles actuellements vendus par l'utilisateur `user_id`.
--
CREATE FUNCTION articles_vendus_par(user_id INTEGER)
    RETURNS TABLE(reference TEXT, modele TEXT, prix REAL)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
        SELECT article.reference, article.modele, article.prix FROM articles_en_vente article
        WHERE article.vendeur = user_id;
END;
$$;