SQL création de la DB

-- Table des utilisateurs
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'enterprise', 'admin')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des entreprises
CREATE TABLE enterprises (
  id SERIAL PRIMARY KEY,
  user_id INTEGER UNIQUE NOT NULL REFERENCES users(id),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  logo_url VARCHAR(255),
  website_url VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des annonces
CREATE TABLE adverts (
  id SERIAL PRIMARY KEY,
  enterprise_id INTEGER NOT NULL REFERENCES enterprises(id),
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  location VARCHAR(255) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  salary DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des candidatures
CREATE TABLE applications (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  advert_id INTEGER NOT NULL REFERENCES adverts(id),
  status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'accepted', 'rejected')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des messages du chat
CREATE TABLE chat_messages (
  id SERIAL PRIMARY KEY,
  sender_id INTEGER NOT NULL REFERENCES users(id),
  recipient_id INTEGER NOT NULL REFERENCES users(id),
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des plannings
CREATE TABLE schedules (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  advert_id INTEGER NOT NULL REFERENCES adverts(id),
  start_time TIMESTAMP NOT NULL,
  end_time TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des notifications
CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  content TEXT NOT NULL,
  is_read BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_info (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    postal_code TEXT,
    city TEXT,
    address TEXT
);

-- Données pour la table users
INSERT INTO users (username, email, password_hash, role) VALUES
  ('john_doe', '[email protected]', '$2a$10$xKLBw9gqGMo4R.Rt/DNJCOU5Z5O5O5O5O5O5O5O5O5O5', 'user'),
  ('jane_smith', '[email protected]', '$2a$10$4m8I6eYJY1q1q1q1q1q1q1tIeNwM6Q6Q6Q6Q6Q6Q6Q6', 'user'),
  ('acme_inc', '[email protected]', '$2a$10$SgzfuE91DJgGgGgGgGgGgGI7EIj50505050505050505', 'enterprise'),
  ('admin', '[email protected]', '$2a$10$jzjzjzjzjzjzjzjzjzjzjzZFZpVp1p1p1p1p1p1p1p1', 'admin');

-- Données pour la table enterprises
INSERT INTO enterprises (user_id, name, description, logo_url, website_url) VALUES
  (3, 'Acme Inc.', 'Lorem ipsum dolor sit amet', 'https://example.com/acme-logo.png', 'https://acme.com');

-- Données pour la table adverts
INSERT INTO adverts (enterprise_id, title, description, location, start_date, end_date, salary) VALUES
  (1, 'Développeur Web', 'Nous recherchons un développeur web expérimenté', 'Paris', '2023-07-01', '2023-12-31', 3500.00),
  (1, 'Designer UX/UI', 'Rejoignez notre équipe de design pour créer des expériences utilisateur exceptionnelles', 'Lyon', '2023-08-15', '2024-02-15', 4000.00);

-- Données pour la table applications
INSERT INTO applications (user_id, advert_id, status) VALUES
  (1, 1, 'pending'),
  (2, 1, 'accepted'),
  (1, 2, 'rejected');

-- Données pour la table chat_messages
INSERT INTO chat_messages (sender_id, recipient_id, content) VALUES
  (1, 3, 'Bonjour, je suis intéressé par le poste de développeur web'),
  (3, 1, 'Merci pour votre intérêt, pouvez-vous nous en dire plus sur votre expérience ?'),
  (2, 3, 'J''ai postulé pour le poste de développeur web, quand puis-je espérer une réponse ?');

-- Données pour la table schedules
INSERT INTO schedules (user_id, advert_id, start_time, end_time) VALUES
  (2, 1, '2023-07-01 09:00:00', '2023-07-01 17:00:00'),
  (2, 1, '2023-07-02 09:00:00', '2023-07-02 17:00:00'),
  (2, 1, '2023-07-03 09:00:00', '2023-07-03 17:00:00');

-- Données pour la table notifications
INSERT INTO notifications (user_id, content) VALUES
  (1, 'Votre candidature pour le poste de développeur web a été reçue'),
  (2, 'Félicitations, votre candidature pour le poste de développeur web a été acceptée !'),
  (1, 'Malheureusement, votre candidature pour le poste de designer UX/UI a été rejetée');

ALTER TABLE users
ADD COLUMN enterprise_id INTEGER REFERENCES enterprises(id);

UPDATE users
SET enterprise_id = CASE
    WHEN id = 3 THEN 1
    ELSE NULL
END;