Database_queries.txt

/*Create Database , you may remove next two lines if you want to insert into another database or already created a database for this project*/
CREATE DATABASE IF NOT EXISTS soft_travel;
USE soft_travel;
/* Database creation code ends, you may remove above two lines, if you do not want to create a new database */

CREATE TABLE divisions(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(10) NOT NULL UNIQUE
);

INSERT INTO divisions (name) VALUES ('Barisal'), ('Chittagong'), ('Dhaka'), ('Khulna'), ('Mymensingh'), ('Rajshahi'), ('Rangpur'), ('Sylhet');

CREATE TABLE spots(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(20) NOT NULL UNIQUE
);

INSERT INTO spots (name) VALUES ('Sajek'), ('Coxs Bazar'), ('Kuakata');

CREATE TABLE hotels(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    area varchar(10)
);

INSERT INTO hotels(name, area) VALUES ('Hotel Sea Cox', 'Coxs Bazar'), ('Hotel Prime Park Hotel', 'Coxs Bazar'), ('Hotel Agrabad', 'Chittagong'), ('The Avenue Hotel & Suites', 'Chittagong'), ('Foys Lake Resort', 'Chittagong'), ('Sikder Resort & Resort', 'Kuakata'), ('Padma Resort', 'Dhaka');

CREATE TABLE roles(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    role varchar(10) NOT NULL UNIQUE,
    permissions varchar(100) 
);

INSERT INTO roles(role, permissions) VALUES ('admin',''), ('manager', ''), ('user', '');

CREATE TABLE users(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    email varchar(100) NOT NULL UNIQUE,
    password varchar(32) NOT NULL,
    role varchar(10) DEFAULT 'user',

    CONSTRAINT FOREIGN KEY ufk (role) REFERENCES roles(role)
);

INSERT INTO users(email, password, role) VALUES ('admin@example.com', '12345', 'admin'), ('manager@example.com', '1234', 'manager'), ('user@example.com', '123', 'user');

CREATE TABLE settings(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title varchar(50) UNIQUE,
    info varchar(100)
);

INSERT INTO settings(title, info) VALUES ('site_name', 'Network Er Bahire'), ('titleSeparator', '|');



/* CREATE TABLE admin_menus(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(10) NOT NULL,
    link varchar(10) NOT NULL
);

INSERT INTO admin_menus(name, link) VALUES ('settings', 'settings');*/

CREATE TABLE `blog` (
  `id` int(10) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `area` varchar(255) DEFAULT NULL,
  `image` blob NOT NULL,
  `tittle` varchar(255) DEFAULT NULL,
  `description` mediumtext NOT NULL,
  `datetime` datetime NOT NULL,
  `userid` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;