---

CREATE DATABASE IF NOT EXISTS guide_tours CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE guide_tours;

CREATE TABLE role (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO role (title) VALUES
('admin'),
('guide'),
('tourist');

CREATE TABLE user (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    login VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    FOREIGN KEY (role_id) REFERENCES role(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

INSERT INTO difficulty (title) VALUES
('легкий'),
('средний'),
('сложный');

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

INSERT INTO tour_format (title) VALUES
('экскурсия'),
('поход'),
('автобусный тур');

CREATE TABLE tour (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    guide_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    country VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    date_start DATE NOT NULL,
    date_end DATE NOT NULL,
    days_count INT UNSIGNED NOT NULL,
    image VARCHAR(255) DEFAULT NULL,
    price DECIMAL(10,2) NOT NULL,
    max_members INT UNSIGNED NOT NULL,
    difficulty_id INT UNSIGNED NOT NULL,
    format_id INT UNSIGNED NOT NULL,
    min_age INT UNSIGNED DEFAULT NULL,
    max_age INT UNSIGNED DEFAULT NULL,
    is_unlimited TINYINT(1) NOT NULL DEFAULT 0,
    FOREIGN KEY (guide_id) REFERENCES user(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (difficulty_id) REFERENCES difficulty(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (format_id) REFERENCES tour_format(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

INSERT INTO application_status (title, alias) VALUES
('Новая', 'new'),
('Подтверждена', 'approved'),
('Отклонена', 'rejected');

CREATE TABLE application (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tour_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    status_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tour_id) REFERENCES tour(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES user(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (status_id) REFERENCES application_status(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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 NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tour_id) REFERENCES tour(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES user(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;