DROP DATABASE IF EXISTS class_harmony;
CREATE DATABASE class_harmony;

USE class_harmony;


CREATE TABLE users
(
    id                       INT AUTO_INCREMENT PRIMARY KEY,
    email                    VARCHAR(255) NOT NULL UNIQUE,
    password                 VARCHAR(255) NOT NULL,
    role                     ENUM ('user', 'admin')                  DEFAULT 'user',
    status                   ENUM ('active', 'inactive', 'disabled') DEFAULT 'inactive',
    validation_token         VARCHAR(255),
    validation_token_expires TIMESTAMP    NULL,
    reset_token              VARCHAR(255),
    reset_token_expires      TIMESTAMP    NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE teachers
(
    id                       INT AUTO_INCREMENT PRIMARY KEY,
    first_name               VARCHAR(100) NOT NULL,
    last_name                VARCHAR(100) NOT NULL,
    email                    VARCHAR(255) NOT NULL UNIQUE,
    password                 VARCHAR(255) NOT NULL,
    bio                      TEXT,
    photo                    VARCHAR(255),
    skills                   TEXT,
    music_types              VARCHAR(255),
    instruments              VARCHAR(255),
    location                 VARCHAR(255),
    average_rating           DECIMAL(3, 2)                           DEFAULT 0.00,
    total_reviews            INT                                     DEFAULT 0,
    status                   ENUM ('active', 'inactive', 'disabled') DEFAULT 'inactive',
    disable_reason           TEXT,
    validation_token         VARCHAR(255),
    validation_token_expires TIMESTAMP    NULL,
    reset_token              VARCHAR(255),
    reset_token_expires      TIMESTAMP    NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE students
(
    id                       INT AUTO_INCREMENT PRIMARY KEY,
    first_name               VARCHAR(100) NOT NULL,
    last_name                VARCHAR(100) NOT NULL,
    email                    VARCHAR(255) NOT NULL UNIQUE,
    password                 VARCHAR(255) NOT NULL,
    location                 VARCHAR(255),
    status                   ENUM ('active', 'inactive', 'disabled') DEFAULT 'inactive',
    disable_reason           TEXT,
    validation_token         VARCHAR(255),
    validation_token_expires TIMESTAMP    NULL,
    reset_token              VARCHAR(255),
    reset_token_expires      TIMESTAMP    NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE courses
(
    id            INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id    INT                                           NOT NULL,
    student_id    INT                                           NULL,
    title         VARCHAR(255)                                  NOT NULL,
    description   TEXT,
    instruments   VARCHAR(255),
    level         ENUM ('beginner', 'intermediate', 'advanced') NOT NULL,
    music_type    VARCHAR(100),
    location      VARCHAR(255),
    date          DATE                                          NOT NULL,
    start_time    TIME                                          NOT NULL,
    end_time      TIME                                          NOT NULL,
    duration      INT                                           NOT NULL,
    price         DECIMAL(10, 2)                                            DEFAULT 0.00,
    status        ENUM ('available', 'confirmed', 'cancelled', 'completed') DEFAULT 'available',
    cancel_reason TEXT,
    FOREIGN KEY (teacher_id) REFERENCES teachers (id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE SET NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE enrollments
(
    id            INT AUTO_INCREMENT PRIMARY KEY,
    course_id     INT NOT NULL,
    student_id    INT NOT NULL,
    status        ENUM ('pending', 'confirmed', 'rejected', 'cancelled') DEFAULT 'pending',
    cancel_reason TEXT,
    FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE reviews
(
    id         INT AUTO_INCREMENT PRIMARY KEY,
    course_id  INT     NOT NULL,
    teacher_id INT     NOT NULL,
    student_id INT     NOT NULL,
    rating     TINYINT NOT NULL,
    comment    TEXT,
    FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teachers (id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE,
    UNIQUE KEY (course_id, student_id),
    CHECK (rating >= 1 AND rating <= 5)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

-- Insert admin user (password: admin)
INSERT INTO users (email, password, role, status)
VALUES ('admin@classharmony.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active');

-- Insert 11 teachers with different instruments (password: prof)
INSERT INTO teachers (first_name, last_name, email, password, bio, skills, music_types, instruments, location, status)
VALUES
    ('Jean', 'Dupont', 'jean.dupont@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Professeur de piano avec 15 ans d\'expérience', 'Solfège, Théorie musicale', 'Classique, Jazz', 'Piano', 'Paris', 'active'),

    ('Marie', 'Martin', 'marie.martin@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Guitariste professionnelle et pédagogue', 'Composition, Improvisation', 'Rock, Blues', 'Guitare', 'Lyon', 'active'),

    ('Pierre', 'Bernard', 'pierre.bernard@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Batteur de jazz passionné', 'Rythme, Coordination', 'Jazz, Funk', 'Batterie', 'Marseille', 'active'),

    ('Sophie', 'Dubois', 'sophie.dubois@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Violoniste classique diplômée du conservatoire', 'Technique classique, Lecture à vue', 'Classique, Contemporain', 'Violon', 'Bordeaux', 'active'),

    ('Luc', 'Moreau', 'luc.moreau@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Bassiste avec expérience en studio', 'Groove, Harmonie', 'Funk, Pop', 'Basse', 'Toulouse', 'active'),

    ('Claire', 'Laurent', 'claire.laurent@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Coach vocale certifiée', 'Technique vocale, Respiration', 'Pop, Soul', 'Chant', 'Nantes', 'active'),

    ('Thomas', 'Simon', 'thomas.simon@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Saxophoniste de jazz et professeur', 'Improvisation, Phrasé', 'Jazz, Bebop', 'Saxophone', 'Strasbourg', 'active'),

    ('Julie', 'Michel', 'julie.michel@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Flûtiste et spécialiste en musique de chambre', 'Souffle, Articulation', 'Classique, Baroque', 'Flûte', 'Nice', 'active'),

    ('Marc', 'Lefebvre', 'marc.lefebvre@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Professeur de trompette et de cuivres', 'Embouchure, Sonorité', 'Jazz, Classique', 'Trompette', 'Lille', 'active'),

    ('Anne', 'Roux', 'anne.roux@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Violoncelliste et chef d\'orchestre', 'Interprétation, Direction', 'Classique, Romantique', 'Violoncelle', 'Montpellier', 'active'),

    ('David', 'Garnier', 'david.garnier@music.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
     'Clarinettiste et professeur au conservatoire', 'Technique, Répertoire', 'Classique, Klezmer', 'Clarinette', 'Rennes', 'active');