53Z9ksPe

1CREATE TABLE IF NOT EXISTS `categories`(
2    `id` INT AUTO_INCREMENT PRIMARY KEY,
3    `category` VARCHAR(50) NOT NULL,
4    `daily_rate` DECIMAL,
5    `weekly_rate` DECIMAL,
6    `monthly_rate` DECIMAL, 
7    `weekend_rate` DECIMAL
8);
9 
10INSERT INTO `categories` (`category`)
11    VALUES ('Van'),
12        ('Sedan'),
13        ('4x4');
14        
15CREATE TABLE IF NOT EXISTS `cars`(
16    `id` INT AUTO_INCREMENT PRIMARY KEY,
17    `plate_number` VARCHAR(50) NOT NULL UNIQUE,
18    `make` VARCHAR(50),
19    `model` VARCHAR(50),
20    `car_year` INT(4),  
21    `category_id` INT,
22    `doors` INT(1),
23    `picture` LONGBLOB,
24    `car_condition` VARCHAR(50) NOT NULL,
25    `available` ENUM ('true','false') NOT NULL
26);
27 
28 
29INSERT INTO `cars` (`plate_number`,`category_id`,`car_condition`,`available`)
30    VALUES ('Van123',1,'fsd', 'true'),
31        ('Sedan123',2,'dsffds', 'false'),
32        ('4x4',1,'fsdsfsd', 'false');
33        
34CREATE TABLE IF NOT EXISTS `employees`(
35    `id` INT AUTO_INCREMENT PRIMARY KEY,
36    `first_name` VARCHAR(50),
37    `last_name` VARCHAR(50) NOT NULL,
38    `title` VARCHAR(50),
39    `notes` TEXT
40);              
41 
42INSERT INTO `employees`(`last_name`)
43    VALUES ('Ivan'),
44        ('Petkan'),
45        ('Pesho');
46        
47CREATE TABLE IF NOT EXISTS `customers`(
48    `id` INT AUTO_INCREMENT PRIMARY KEY,
49    `dirver_licence_number` VARCHAR(50) UNIQUE,
50    `full_name` VARCHAR(50) NOT NULL,
51    `address` VARCHAR(50),
52    `city` VARCHAR(50),
53    `zip_code` INT,
54    `notes` TEXT
55);
56 
57INSERT INTO `customers`(`full_name`)
58    VALUES ('P.P'),
59        ('D.D'),
60        ('A.A');
61        
62CREATE TABLE IF NOT EXISTS `rental_orders`(
63    `id` INT AUTO_INCREMENT PRIMARY KEY,
64    `employee_id` INT NOT NULL,
65    `customer_id` INT NOT NULL,                 
66    `car_id` INT NOT NULL,
67    `car_condition` VARCHAR(50) NOT NULL,
68    `tank_level` DECIMAL,
69    `kilometrage_start` INT(7),
70    `kilometrage_end` INT(7),
71    `total_kilometrage` INT(7),
72    `start_date` DATETIME,
73    `end_date` DATETIME,
74    `total_days` INT(4),
75    `rate_applied` DECIMAL,
76    `tax_rate` DECIMAL,
77    `order_status` VARCHAR(50),
78    `notes` TEXT
79);
80 
81    
82INSERT INTO `rental_orders` (`employee_id`,`customer_id`,`car_id`, `car_condition`)
83    VALUES (1,1,1, 'fsd'),
84        (2,2,2, 'dsffds'),
85        (3,3,3, 'fsdsfsd');