CREATE DATABASE hotel;
USE hotel;

CREATE TABLE employees(
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
title VARCHAR(50) NOT NULL,
notes VARCHAR(50) NOT NULL,
CONSTRAINT pk_employees_id PRIMARY KEY employees(id)
);

INSERT INTO employees(first_name,last_name,title,notes)
VALUES ('Ivan','Ivanov','Boss','sample notes'),
	   ('Kiro','Cekov','Doorman','hardworking'),
	   ('Marti','Angelov','master shef','cooking notes'),
       ('Qsen','Swift','Recepcionist','notes');

Select * FROM employees;

CREATE TABLE  customers(
account_number INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
phone_nubmer VARCHAR(10) NOT NULL,
emergency_name VARCHAR(50),
emergency_number VARCHAR(10),
notes VARCHAR(50)
);

INSERT INTO customers(first_name,last_name,phone_nubmer)
VALUES  ('Angel','Angelov','089456734'),
		('Tanq','Angelova','089325671');

SELECT * FROM customers;

INSERT INTO customers(first_name,last_name,phone_nubmer,emergency_name,emergency_number)
VALUES ('Dani','Danailov','07856432','Danna','0293325');

CREATE TABLE room_status(
room_status INT NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
notes VARCHAR(50)
);

INSERT INTO room_status(notes) VALUES('For Clean'),('Occupied'),('Free');
Select * From room_status;

CREATE TABLE room_types(
room_type INT NOT NULL AUTO_INCREMENT UNIQUE,
notes VARCHAR(50),
CONSTRAINT pk_room_types PRIMARY KEY room_types(room_type)
);

INSERT INTO room_types(notes) VALUES('Apartament'),('Small'),('Medium'),('VIP room');
SELECT * FROM room_types;

CREATE TABLE bed_types(
bed_type INT NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
notes VARCHAR(50)
);

INSERT INTO bed_types(notes) VALUES('Double'),('Single'),('Water-filled');
SELECT * FROM bed_types;

 CREATE TABLE rooms(
room_number INT NOT NULL AUTO_INCREMENT,
room_type INT NOT NULL,
bed_type INT NOT NULL,
rate VARCHAR(50) NOT NULL,
room_status INT NOT NULL,
notes VARCHAR(50),
CONSTRAINT pk_rooms_id PRIMARY KEY rooms(room_number),
CONSTRAINT fk_room_type_id FOREIGN KEY rooms(room_type) REFERENCES room_types(room_type),
CONSTRAINT fk_bed_type_id FOREIGN KEY rooms(bed_type) REFERENCES bed_types(bed_type),
CONSTRAINT fk_room_status_id FOREIGN KEY rooms(room_status) REFERENCES
 room_status(room_status)
);

INSERT INTO rooms(rate,notes,room_type,bed_type,room_status)
VALUES();-- ????