Hotel Database

create table employees(
id INT PRIMARY KEY AUTO_INCREMENT,
  first_name varchar(50) not null,
  last_name varchar(50) not NULL,
  title varchar(50),
  notes TEXT
);
insert into employees(id,first_name, last_name)values(1,'Pesho', 'Peshov'),(2,'Ivan','Ivanov'),(3,'Vanio', 'Vaniov');

create table customers (
   account_number INT PRIMARY KEY AUTO_INCREMENT,
   first_name varchar(50) not null,
    last_name varchar(50) NOT NULL,
    phone_number varchar(10),
    emergency_name varchar(50),
    emergency_number varchar(10),
    notes TEXT
);
insert into customers(account_number,first_name, last_name)values(1,'Pesho', 'Peshov'),(2,'Ivan','Ivanov'),(3,'Vanio', 'Vaniov');

create table room_status (
   room_status INT PRIMARY KEY AUTO_INCREMENT,
   notes TEXT
);
insert into room_status(room_status)values(1),(2),(3);

create table room_types(
   room_type INT PRIMARY KEY AUTO_INCREMENT,
   notes TEXT
);
insert into room_types(room_type)values (1), (2), (3);

create table bed_types(
  bed_type INT PRIMARY KEY AUTO_INCREMENT,
  notes TEXT
);
insert into bed_types(bed_type)values(1),(2),(3);

create table rooms(
   room_number INT PRIMARY KEY AUTO_INCREMENT,
   room_type INT,
   bed_type INT,
   rate DOUBLE,
   room_status INT,
   notes TEXT
);
insert into rooms(room_number)values(1),(2),(3);

create table payments(
   id INT PRIMARY KEY AUTO_INCREMENT,
   employee_id INT NOT NULL,
   payment_date DATE,
   account_number INT,
   first_date_occupied DATETIME,
   last_date_occupied DATETIME,
   total_days INT,
   amount_charged DOUBLE,
   tax_rate DOUBLE,
   tax_amount DOUBLE,
   payment_total DOUBLE,
   notes TEXT
);
insert into payments(id,employee_id)values(1,1),(2,2),(3,3);

create table occupancies(
  id INT PRIMARY KEY AUTO_INCREMENT,
  employee_id int NOT NULL,
  date_occupied DATE,
  account_number INT,
  room_number INT,
  rate_applied DOUBLE,
  phone_charge varchar(10),
  notes TEXT
);
insert into occupancies(id,employee_id, notes)values(1,1,'sasas'),(2,2,'sasas'),(3,3,'sasasa');