CREATE DATABASE IF NOT EXISTS rencontre;
USE rencontre;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birthdate DATE,
    gender ENUM('homme', 'femme', 'non-binaire', 'autre'),
    sexual_orientation ENUM('hétérosexuel', 'homosexuel', 'bisexuel', 'autre'),
    location VARCHAR(255),
    profile_photo VARCHAR(255),
    bio TEXT,
    looking_for ENUM('amitié', 'amour', 'aventure', 'autre'),
    last_active TIMESTAMP,
    account_status ENUM('actif', 'suspendu', 'banni') DEFAULT 'actif'
);

CREATE TABLE interests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    interest_name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE user_interests (
    user_id INT,
    interest_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (interest_id) REFERENCES interests(id),
    UNIQUE (user_id, interest_id)
);

CREATE TABLE photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    photo_url VARCHAR(255) NOT NULL,
    caption VARCHAR(255),
    upload_date TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT,
    receiver_id INT,
    content TEXT NOT NULL,
    sent_date TIMESTAMP,
    read_date TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id),
    FOREIGN KEY (receiver_id) REFERENCES users(id)
);

CREATE TABLE likes (
    user_id INT,
    liked_user_id INT,
    like_date TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (liked_user_id) REFERENCES users(id),
    UNIQUE (user_id, liked_user_id)
);

CREATE TABLE matches (
    user_id INT,
    matched_user_id INT,
    match_date TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (matched_user_id) REFERENCES users(id),
    UNIQUE (user_id, matched_user_id)
);