Hotel

CREATE TABLE IF NOT EXISTS `employees`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(50),
    `last_name` VARCHAR(50) NOT NULL,
    `title` VARCHAR(50),
    `notes` TEXT
);

INSERT INTO `employees`(`last_name`)
    VALUES ('Petrov'),
        ('Ivanov'),
        ('Dimitrov');

CREATE TABLE IF NOT EXISTS `customers`(
    `account_number` VARCHAR(50) UNIQUE PRIMARY KEY,
    `first_name` VARCHAR(50),
    `last_name` VARCHAR(50) NOT NULL,
    `phone_number` VARCHAR(50),
    `emergency_name` VARCHAR(50),
    `emergency_number` VARCHAR(50),
    `notes` TEXT
);

INSERT INTO `customers`(`account_number`,`last_name`)
    VALUES ('122','Petrov'),
        ('123412','Ivanov'),
        ('1212341','Dimitrov');

CREATE TABLE IF NOT EXISTS `room_status`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `room_status` VARCHAR(50) NOT NULL,
    `notes` TEXT
);

INSERT INTO `room_status`(`room_status`)
    VALUES ('ready'),
        ('empty'),
        ('occupied');

CREATE TABLE IF NOT EXISTS `room_types`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `room_type` VARCHAR(50) NOT NULL,
    `notes` TEXT
);

INSERT INTO `room_types`(`room_type`)
    VALUES ('single'),
        ('double'),
        ('appartment');


CREATE TABLE IF NOT EXISTS `bed_types`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `bed_type` VARCHAR(50) NOT NULL,
    `notes` TEXT
);

INSERT INTO `bed_types`(`bed_type`)
    VALUES ('single'),
        ('double'),
        ('king');

CREATE TABLE IF NOT EXISTS `rooms`(
    `room_number` INT(4) NOT NULL PRIMARY KEY,
    `room_type` VARCHAR(50) NOT NULL,
    `bed_type` VARCHAR(50) NOT NULL,
    `rate` DECIMAL NOT NULL,
    `room_status` VARCHAR(50) NOT NULL,
    `notes` TEXT
);

INSERT INTO `rooms`(`room_number`,`room_type`,`bed_type`,`rate`,`room_status`)
    VALUES (124,'dsfs','dsfs',124.21,'dsfs'),
        (1214,'dsfs','dsfs',1231.21,'dsfdfsds'),
        (1224,'dsfs','dsfs',112114.21,'dssdfsfssdffs');

CREATE TABLE IF NOT EXISTS `occupancies`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT,
    `date_occupied` DATETIME,
    `account_number` VARCHAR(50),
    `room_number` INT(4) NOT NULL,
    `rate_applied` DECIMAL,
    `phone_charge` DECIMAL,
    `notes` TEXT
);

INSERT INTO `occupancies`(`room_number`)
    VALUES (2),
        (3),
        (3);

CREATE TABLE IF NOT EXISTS `payments`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT,
    `payment_date` DATETIME,
    `account_number` VARCHAR(50) NOT NULL,
    `first_date_occupied` DATETIME,
    `last_date_occupied` DATETIME,
    `total_days` INT(4),
    `amount_charged` DECIMAL,
    `tax_rate` DECIMAL,
    `tax_amount` DECIMAL,
    `payment_total` DECIMAL NOT NULL,
    `notes` TEXT
);

INSERT INTO `payments`(`account_number`,`payment_total`)
    VALUES ('1',3),
        ('2',3),
        ('1',2);