DROP DATABASE IF EXISTS korochki;
CREATE DATABASE korochki CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE korochki;
CREATE TABLE roles (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL UNIQUE) ENGINE=InnoDB;
CREATE TABLE payment_methods (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE) ENGINE=InnoDB;
CREATE TABLE application_statuses (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE) ENGINE=InnoDB;
CREATE TABLE applications (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, course_name VARCHAR(200) NOT NULL, start_date DATE NOT NULL, payment_method_id INT NOT NULL, status_id INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_app_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_app_payment FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_app_status FOREIGN KEY (status_id) REFERENCES application_statuses(id) ON UPDATE CASCADE ON DELETE RESTRICT) ENGINE=InnoDB;
CREATE TABLE reviews (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, text TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_reviews_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE=InnoDB;
INSERT INTO roles (name) VALUES ("user"), ("admin");
INSERT INTO payment_methods (name) VALUES ("Наличными"), ("Переводом по номеру телефона");
INSERT INTO application_statuses (name) VALUES ("Новая"), ("Идёт обучение"), ("Обучение завершено");
