cowork_db.sql

-- Création de la table 'users'
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Création de la table 'spaces'
CREATE TABLE spaces (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    capacity INT NOT NULL,
    price_per_day DECIMAL(10, 2) NOT NULL,
    amenities VARCHAR(255) NOT NULL,
    location VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Création de la table 'bookings'
CREATE TABLE bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    space_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (space_id) REFERENCES spaces(id)
);

-- Création de la table 'reviews'
CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    space_id INT NOT NULL,
    rating INT NOT NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (space_id) REFERENCES spaces(id)
);

-- Création de la table 'invoices'
CREATE TABLE invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    status ENUM('paid', 'pending', 'overdue') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
);

-- Création de la table 'admin'
CREATE TABLE admin (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE admin
ADD user_id INT,
CONSTRAINT FK_admin_user FOREIGN KEY (user_id) REFERENCES [user](id);


-- Insertion dans la table 'users'
INSERT INTO users (name, email, password, role)
VALUES  ('John Doe', 'johndoe@example.com', 'password123', 'user'),
        ('Jane Smith', 'janesmith@example.com', 'password456', 'user');

-- Insertion dans la table 'spaces'
INSERT INTO spaces (name, description, capacity, price_per_day, amenities, location)
VALUES  ('Espace 1', 'Description de l\'espace 1', 10, 50.00, 'Wifi, Café, Imprimante', 'Emplacement 1'),
        ('Espace 2', 'Description de l\'espace 2', 8, 40.00, 'Wifi, Salle de réunion', 'Emplacement 2');

-- Insertion dans la table 'admin'
INSERT INTO admin (name, email, password)
VALUES ('Admin Name', 'admin@example.com', 'adminpassword123');