---

-- USERS
CREATE TABLE user (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    login VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','guide','user') NOT NULL DEFAULT 'user'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- DIFFICULTY
CREATE TABLE difficulty (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- FORMAT (тип тура)
CREATE TABLE tour_format (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- TOUR
CREATE TABLE tour (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    guide_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    country VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    date_start DATETIME NOT NULL,
    date_end DATETIME NOT NULL,
    days_count INT UNSIGNED NOT NULL,
    image VARCHAR(255),
    price DECIMAL(10,2) NOT NULL,
    max_member INT UNSIGNED NOT NULL,
    difficulty_id INT UNSIGNED NOT NULL,
    format_id INT UNSIGNED NOT NULL,
    min_age INT UNSIGNED,
    max_age INT UNSIGNED,
    is_unlimited TINYINT(1) DEFAULT 0,

    FOREIGN KEY (guide_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (difficulty_id) REFERENCES difficulty(id) ON DELETE CASCADE,
    FOREIGN KEY (format_id) REFERENCES tour_format(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- APPLICATION (заявки)
CREATE TABLE application (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    tour_id INT UNSIGNED NOT NULL,
    status ENUM('new','approved','rejected') DEFAULT 'new',

    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- REVIEW (отзывы)
CREATE TABLE review (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tour_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;