DROP DATABASE IF EXISTS waxshare;
CREATE OR REPLACE DATABASE waxshare
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE waxshare;

-- ========================
-- TABLE : UTILISATEURS
-- ========================
CREATE OR REPLACE TABLE users (
    id INT AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    pwd VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL UNIQUE,
    date_inscription DATETIME DEFAULT CURRENT_TIMESTAMP,
    token VARCHAR(255),
    email_verified BOOLEAN DEFAULT FALSE,
    is_admin BOOLEAN DEFAULT FALSE,
    note_moyenne DECIMAL(3,2) DEFAULT 0.00,
    suspended BOOLEAN DEFAULT FALSE,
    end_suspension DATE,
    number_of_warning INT,

    CONSTRAINT pk_user PRIMARY KEY (id),
    CONSTRAINT uq_user_email UNIQUE (email)
) ENGINE=InnoDB;

-- ========================
-- TABLE : VINYLS
-- ========================
CREATE OR REPLACE TABLE vinyls (
    id INT AUTO_INCREMENT,
    id_proprietaire INT NOT NULL,
    titre VARCHAR(255) NOT NULL,
    artiste VARCHAR(255) NOT NULL,
    genre VARCHAR(100),
    description TEXT,
    etat ENUM ('Neuf', 'Très Bon', 'Bon', 'Correct', 'Mauvais') DEFAULT 'Bon',
    disponible_au_pret BOOLEAN DEFAULT TRUE,
    date_ajout DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_vinyls PRIMARY KEY (id),
    CONSTRAINT fk_vinyls_proprietaire 
        FOREIGN KEY (id_proprietaire) 
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE OR REPLACE INDEX idx_vinyls_proprietaire ON vinyls(id_proprietaire);
CREATE OR REPLACE INDEX idx_vinyls_genre ON vinyls(genre);

-- ========================
-- TABLE : PRETS
-- ========================
CREATE OR REPLACE TABLE prets (
    id INT AUTO_INCREMENT,
    id_vinyle INT NOT NULL,
    id_emprunteur INT NOT NULL,
    lieu_demande VARCHAR(255),
    statut ENUM ('Demandé', 'Accepté', 'Refusé', 'En cours', 'Rendu', 'Annulé') DEFAULT 'Demandé',
    date_demande DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_debut_pret DATETIME,
    date_retour_prevue DATE,
    date_retour_effective DATETIME,
    instructions_retour TEXT,

    CONSTRAINT pk_prets PRIMARY KEY (id),

    CONSTRAINT fk_prets_vinyle
        FOREIGN KEY (id_vinyle)
        REFERENCES vinyls(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_prets_emprunteur
        FOREIGN KEY (id_emprunteur)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE OR REPLACE INDEX idx_prets_vinyle ON prets(id_vinyle);
CREATE OR REPLACE INDEX idx_prets_emprunteur ON prets(id_emprunteur);

-- ========================
-- TABLE : NOTATIONS
-- ========================
CREATE OR REPLACE TABLE notations (
    id INT AUTO_INCREMENT,
    id_pret INT NOT NULL,
    id_auteur INT NOT NULL,
    id_cible INT NOT NULL,
    note INT NOT NULL,
    commentaire TEXT,
    date_notation DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_notations PRIMARY KEY (id),

    CONSTRAINT fk_notations_pret
        FOREIGN KEY (id_pret)
        REFERENCES prets(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_notations_auteur
        FOREIGN KEY (id_auteur)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_notations_cible
        FOREIGN KEY (id_cible)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

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

CREATE OR REPLACE INDEX idx_notations_auteur ON notations(id_auteur);
CREATE OR REPLACE INDEX idx_notations_cible ON notations(id_cible);

-- ========================
-- TABLE : PHOTOS_VINYLS
-- ========================
CREATE OR REPLACE TABLE photos_vinyls (
    id INT AUTO_INCREMENT,
    id_vinyle INT NOT NULL,
    url VARCHAR(255) NOT NULL,
    ordre INT DEFAULT 1,

    CONSTRAINT pk_photos_vinyls PRIMARY KEY (id),

    CONSTRAINT fk_photos_vinyle
        FOREIGN KEY (id_vinyle)
        REFERENCES vinyls(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE OR REPLACE INDEX idx_photos_vinyle ON photos_vinyls(id_vinyle);
