car_rental

CREATE TABLE categories(
`id` INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
`category` VARCHAR(50) NOT NULL,
`daily_rate` DECIMAL(19,2),
 `weekly_rate`DECIMAL(19,2),
 `monthly_rate`DECIMAL(19,2),
 `weekend_rate`DECIMAL(19,2)
);

CREATE TABLE cars(
`id` INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
 `plate_number` INT UNIQUE NOT NULL,
 `make` VARCHAR(300),
 `model`VARCHAR(300),
 `car_year` YEAR,
 `category_id` INT,
 `doors`VARCHAR(300),
 `picture` BLOB,
 `car_condition` VARCHAR(300),
 `available` BOOL NOT NULL
);
CREATE TABLE employees(
`id` INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
 `first_name` VARCHAR(300) NOT NULL,
 `last_name` VARCHAR(300) NOT NULL,
 `title` VARCHAR(300),
 `notes` TEXT(300)
);

CREATE TABLE customers(
`id` INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
 `driver_licence_number` INT NOT NULL,
 `full_name` VARCHAR(300),
 `address`VARCHAR(300) ,
 `city` VARCHAR(300),
 `zip_code` INT,
 `notes` TEXT(300)
);
CREATE TABLE rental_orders(
`id` INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
 `employee_id` INT NOT NULL ,
 `customer_id` INT NOT NULL,
 `car_id` INT NOT NULL,
 `car_condition`VARCHAR(300) ,
 `tank_level` INT,
 `kilometrage_start` INT,
 `kilometrage_end`INT,
 `total_kilometrage`INT,
 `start_date` DATE,
 `end_date` DATE,
 `total_days` INT,
 `rate_applied` VARCHAR(300),
 `tax_rate` INT,
 `order_status` VARCHAR(300),
 `notes` TEXT(300)
);


INSERT INTO categories(`category`)
VALUES
('category'),
('category'),
('category')
;

INSERT INTO cars(`plate_number` ,`available`)
VALUES
(10, 1),
(11, 1),
(12, 1)
;

INSERT INTO employees(`first_name`,`last_name`)
VALUES
('fn','ln'),
('fn','ln'),
('fn','ln')
;

INSERT INTO customers(driver_licence_number)
VALUES
(10),
(11),
(12)
;

INSERT INTO rental_orders(`employee_id`,`customer_id`,`car_id`)
VALUES
(10,11,12),
(11,12,13),
(12,13,14)
;