CREATE DATABASE IF NOT EXISTS nautiloc;
USE nautiloc;

CREATE TABLE IF NOT EXISTS types_bateaux (
    id_type_bateau INT AUTO_INCREMENT PRIMARY KEY,
    libelle VARCHAR(120) NOT NULL
);

CREATE TABLE IF NOT EXISTS ports (
    id_port INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(120) NOT NULL,
    ville VARCHAR(120) NOT NULL,
    description TEXT NULL
);

CREATE TABLE IF NOT EXISTS formules (
    id_formule INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(40) NOT NULL UNIQUE,
    libelle VARCHAR(120) NOT NULL,
    duree_heures INT NOT NULL,
    masque_creneau TINYINT UNSIGNED NOT NULL,
    ordre INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS options (
    id_option INT AUTO_INCREMENT PRIMARY KEY,
    libelle VARCHAR(120) NOT NULL,
    description TEXT NULL,
    prix DECIMAL(10,2) NOT NULL
);

CREATE TABLE IF NOT EXISTS admins (
    id_admin INT AUTO_INCREMENT PRIMARY KEY,
    login VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    nom VARCHAR(120) NOT NULL
);

CREATE TABLE IF NOT EXISTS bateaux (
    id_bateau INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(120) NOT NULL,
    slogan VARCHAR(190) NULL,
    description TEXT NULL,
    photo_url VARCHAR(255) NULL,
    capacite INT NOT NULL,
    permis_requis TINYINT(1) NOT NULL DEFAULT 0,
    familial TINYINT(1) NOT NULL DEFAULT 0,
    skipper_disponible TINYINT(1) NOT NULL DEFAULT 0,
    id_type_bateau INT NOT NULL,
    id_port INT NOT NULL,
    CONSTRAINT fk_bateaux_type FOREIGN KEY (id_type_bateau)
        REFERENCES types_bateaux (id_type_bateau)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_bateaux_port FOREIGN KEY (id_port)
        REFERENCES ports (id_port)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS clients (
    id_client INT AUTO_INCREMENT PRIMARY KEY,
    prenom VARCHAR(120) NOT NULL,
    nom VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    telephone VARCHAR(40) NULL,
    password_hash VARCHAR(255) NOT NULL,
    blackliste TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tarifs (
    id_bateau INT NOT NULL,
    id_formule INT NOT NULL,
    prix DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id_bateau, id_formule),
    CONSTRAINT fk_tarifs_bateau FOREIGN KEY (id_bateau)
        REFERENCES bateaux (id_bateau)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_tarifs_formule FOREIGN KEY (id_formule)
        REFERENCES formules (id_formule)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS reservations (
    id_reservation INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(40) NOT NULL UNIQUE,
    id_client INT NOT NULL,
    id_bateau INT NOT NULL,
    id_formule INT NOT NULL,
    date_depart DATE NOT NULL,
    nb_personnes INT NOT NULL,
    prix_base DECIMAL(10,2) NOT NULL,
    statut ENUM('en_attente', 'confirmee', 'annulee', 'remboursee') NOT NULL DEFAULT 'en_attente',
    note_admin TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_reservations_client FOREIGN KEY (id_client)
        REFERENCES clients (id_client)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_reservations_bateau FOREIGN KEY (id_bateau)
        REFERENCES bateaux (id_bateau)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_reservations_formule FOREIGN KEY (id_formule)
        REFERENCES formules (id_formule)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS reservations_options (
    id_reservation INT NOT NULL,
    id_option INT NOT NULL,
    prix_unitaire DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id_reservation, id_option),
    CONSTRAINT fk_res_opt_reservation FOREIGN KEY (id_reservation)
        REFERENCES reservations (id_reservation)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_res_opt_option FOREIGN KEY (id_option)
        REFERENCES options (id_option)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS remboursements (
    id_remboursement INT AUTO_INCREMENT PRIMARY KEY,
    id_reservation INT NOT NULL,
    montant DECIMAL(10,2) NOT NULL,
    motif VARCHAR(190) NOT NULL,
    note_admin TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_remboursements_reservation FOREIGN KEY (id_reservation)
        REFERENCES reservations (id_reservation)
        ON DELETE CASCADE ON UPDATE CASCADE
);