CREATE DATABASE IF NOT EXISTS vinyls_app
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE vinyls_app;

-- ========================
-- TABLE : UTILISATEURS
-- ========================
CREATE TABLE UTILISATEURS (
    user_id INT AUTO_INCREMENT,
    user_email VARCHAR(255) NOT NULL,
    user_mot_de_passe VARCHAR(255) NOT NULL,
    user_nom_utilisateur VARCHAR(100) NOT NULL,
    user_date_inscription DATETIME DEFAULT CURRENT_TIMESTAMP,
    user_email_verifie BOOLEAN DEFAULT FALSE,
    user_est_admin BOOLEAN DEFAULT FALSE,
    user_note_moyenne DECIMAL(3,2) DEFAULT 0.00,

    CONSTRAINT pk_utilisateurs PRIMARY KEY (user_id),
    CONSTRAINT uq_utilisateurs_email UNIQUE (user_email)
) ENGINE=InnoDB;

-- ========================
-- TABLE : VINYLS
-- ========================
CREATE TABLE VINYLS (
    vinyl_id INT AUTO_INCREMENT,
    vinyl_id_proprietaire INT NOT NULL,
    vinyl_titre VARCHAR(255) NOT NULL,
    vinyl_artiste VARCHAR(255) NOT NULL,
    vinyl_genre VARCHAR(100),
    vinyl_description TEXT,
    vinyl_etat ENUM ('Neuf', 'Très Bon', 'Bon', 'Correct', 'Mauvais') DEFAULT 'Bon',
    vinyl_disponible_au_pret BOOLEAN DEFAULT TRUE,
    vinyl_date_ajout DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_vinyls PRIMARY KEY (vinyl_id),
    CONSTRAINT fk_vinyls_proprietaire 
        FOREIGN KEY (vinyl_id_proprietaire) 
        REFERENCES UTILISATEURS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_vinyls_proprietaire ON VINYLS(vinyl_id_proprietaire);
CREATE INDEX idx_vinyls_genre ON VINYLS(vinyl_genre);

-- ========================
-- TABLE : PRETS
-- ========================
CREATE TABLE PRETS (
    loan_id INT AUTO_INCREMENT,
    loan_id_vinyle INT NOT NULL,
    loan_id_emprunteur INT NOT NULL,
    lieu_demande VARCHAR(255),
    loan_statut ENUM ('Demandé', 'Accepté', 'Refusé', 'En cours', 'Rendu', 'Annulé') DEFAULT 'Demandé',
    loan_date_demande DATETIME DEFAULT CURRENT_TIMESTAMP,
    loan_date_debut_pret DATETIME,
    loan_date_retour_prevue DATE,
    loan_date_retour_effective DATETIME,
    loan_instructions_retour TEXT,

    CONSTRAINT pk_prets PRIMARY KEY (loan_id),

    CONSTRAINT fk_prets_vinyle
        FOREIGN KEY (loan_id_vinyle)
        REFERENCES VINYLS(vinyl_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_prets_emprunteur
        FOREIGN KEY (loan_id_emprunteur)
        REFERENCES UTILISATEURS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_prets_vinyle ON PRETS(loan_id_vinyle);
CREATE INDEX idx_prets_emprunteur ON PRETS(loan_id_emprunteur);

-- ========================
-- TABLE : NOTATIONS
-- ========================
CREATE TABLE NOTATIONS (
    rating_id INT AUTO_INCREMENT,
    rating_id_pret INT NOT NULL,
    rating_id_auteur INT NOT NULL,
    rating_id_cible INT NOT NULL,
    rating_note INT NOT NULL,
    rating_commentaire TEXT,
    rating_date_notation DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_notations PRIMARY KEY (rating_id),

    CONSTRAINT fk_notations_pret
        FOREIGN KEY (rating_id_pret)
        REFERENCES PRETS(loan_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_notations_auteur
        FOREIGN KEY (rating_id_auteur)
        REFERENCES UTILISATEURS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_notations_cible
        FOREIGN KEY (rating_id_cible)
        REFERENCES UTILISATEURS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT chk_rating_note CHECK (rating_note BETWEEN 1 AND 5)
) ENGINE=InnoDB;

CREATE INDEX idx_notations_auteur ON NOTATIONS(rating_id_auteur);
CREATE INDEX idx_notations_cible ON NOTATIONS(rating_id_cible);

-- ========================
-- TABLE : PHOTOS_VINYLS
-- ========================
CREATE TABLE PHOTOS_VINYLS (
    photo_id INT AUTO_INCREMENT,
    photo_id_vinyle INT NOT NULL,
    photo_url VARCHAR(255) NOT NULL,
    photo_ordre INT DEFAULT 1,

    CONSTRAINT pk_photos_vinyls PRIMARY KEY (photo_id),

    CONSTRAINT fk_photos_vinyle
        FOREIGN KEY (photo_id_vinyle)
        REFERENCES VINYLS(vinyl_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_photos_vinyle ON PHOTOS_VINYLS(photo_id_vinyle);

