x1bczG5n

1CREATE TABLE IF NOT EXISTS `employees`(
2    `id` INT AUTO_INCREMENT PRIMARY KEY,
3    `first_name` VARCHAR(50),
4    `last_name` VARCHAR(50) NOT NULL,
5    `title` VARCHAR(50),
6    `notes` TEXT
7);  
8 
9INSERT INTO `employees`(`last_name`)
10    VALUES ('Petrov'),
11        ('Ivanov'),
12        ('Dimitrov');       
13 
14CREATE TABLE IF NOT EXISTS `customers`(
15    `account_number` VARCHAR(50) UNIQUE,
16    `first_name` VARCHAR(50),
17    `last_name` VARCHAR(50) NOT NULL,
18    `phone_number` VARCHAR(50),
19    `emergency_name` VARCHAR(50),
20    `emergency_number` VARCHAR(50),
21    `notes` TEXT
22);
23 
24INSERT INTO `customers`(`account_number`,`last_name`)
25    VALUES ('122','Petrov'),
26        ('123412','Ivanov'),
27        ('1212341','Dimitrov'); 
28 
29CREATE TABLE IF NOT EXISTS `room_status`(
30    `room_status`VARCHAR(50)  PRIMARY KEY NOT NULL,
31    `notes` TEXT
32);
33 
34INSERT INTO `room_status`(`room_status`)
35    VALUES ('ready'),
36        ('empty'),
37        ('occupied');   
38 
39CREATE TABLE IF NOT EXISTS `room_types`(
40    `room_type` VARCHAR(50) PRIMARY KEY  NOT NULL,
41    `notes` TEXT
42);
43 
44INSERT INTO `room_types`(`room_type`)
45    VALUES ('single'),
46        ('double'),
47        ('appartment'); 
48 
49 
50CREATE TABLE IF NOT EXISTS `bed_types`(
51    `bed_type` VARCHAR(50)  PRIMARY KEY  NOT NULL,
52    `notes` TEXT
53);
54 
55INSERT INTO `bed_types`(`bed_type`)
56    VALUES ('single'),
57        ('double'),
58        ('king');
59 
60CREATE TABLE IF NOT EXISTS `rooms`(
61    `room_number` INT(4) NOT NULL PRIMARY KEY,
62    `room_type` VARCHAR(50) NOT NULL,
63    `bed_type` VARCHAR(50) NOT NULL,
64    `rate` DECIMAL NOT NULL,
65    `room_status` VARCHAR(50) NOT NULL, 
66    `notes` TEXT
67);
68 
69INSERT INTO `rooms`(`room_number`,`room_type`,`bed_type`,`rate`,`room_status`)
70    VALUES (124,'dsfs','dsfs',124.21,'dsfs'),
71        (1214,'dsfs','dsfs',1231.21,'dsfdfsds'),
72        (1224,'dsfs','dsfs',112114.21,'dssdfsfssdffs');
73 
74CREATE TABLE IF NOT EXISTS `occupancies`(
75    `id` INT AUTO_INCREMENT PRIMARY KEY,
76    `employee_id` INT,
77    `date_occupied` DATETIME,
78    `account_number` VARCHAR(50),
79    `room_number` INT(4) NOT NULL,
80    `rate_applied` DECIMAL,
81    `phone_charge` DECIMAL,
82    `notes` TEXT
83);
84 
85INSERT INTO `occupancies`(`room_number`)
86    VALUES (2),
87        (3),
88        (3);
89    
90CREATE TABLE IF NOT EXISTS `payments`(
91    `id` INT AUTO_INCREMENT PRIMARY KEY,
92    `employee_id` INT,
93    `payment_date` DATETIME,
94    `account_number` VARCHAR(50) NOT NULL,
95    `first_date_occupied` DATETIME,
96    `last_date_occupied` DATETIME,
97    `total_days` INT(4),
98    `amount_charged` DECIMAL,
99    `tax_rate` DECIMAL,
100    `tax_amount` DECIMAL,
101    `payment_total` DECIMAL NOT NULL,
102    `notes` TEXT
103);  
104 
105INSERT INTO `payments`(`account_number`,`payment_total`)
106    VALUES ('1',3),
107        ('2',3),
108        ('1',2);