13-14

-- p_13
create table `categories`(
   id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
   category varchar(50) not null,
   daily_rate decimal(5,2),
   weekly_rate decimal(5,2),
   monthly_rate decimal(5,2),
   weekend_rate decimal(5,2)
);
insert into categories(id,category,daily_rate,weekly_rate,monthly_rate,weekend_rate)
values(1,'a',12.23,12.34,12.45,12.45),(2,'dasdads',12.23,12.34,12.45,12.45),(3,'a',12.23,12.34,12.45,12.45);

create table `cars`(
   id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
   plate_number int not null unique,
   make varchar(10) not null,
   model varchar(10),
   car_year datetime,
   category_id int,
   doors INT,
   picture blob,
   car_condition varchar(20),
   available bit
);
insert into `cars`(id,plate_number,make,model,car_year)
values(1,4,'dasd','dasdasd','1000-01-01 00:00:00'),(2,5,'dasd','dasdasd','1000-01-01 00:00:00'),(3,6,'dasd','dasdasd','1000-01-01 00:00:00');

create table `employees`(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    first_name varchar(50) not null,
    last_name varchar(50),
    title varchar(50),
    notes TEXT
);
insert into `employees`(first_name) values('dasdads'),('dasdads'),('dasdads');

create table`customers`(
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  driver_licence_number varchar(20),
  full_name varchar(50),
  address varchar(50),
  city varchar(50) not null,
  zip_code varchar(50),
  notes TEXT
);
insert into `customers`(city) values('dasdads'),('dasdads'),('dasdads');

create table`rental_orders`(
   id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
   employee_id int,
   customer_id int,
   car_id int,
   car_condition varchar(50),
   tank_level decimal (20,2),
   kilometrage_start int,
   kilometrage_end int,
   total_kilometrage int,
   start_date datetime,
   end_date datetime,
   total_days int,
   rate_applied varchar(50),
   tax_rate int,
   order_status VARCHAR(30),
   notes TEXT
);
insert into `rental_orders`(employee_id,customer_id,car_id) values(1,2,1),(2,2,1),(3,2,1);


-- p_14
create table employees(
   id INT,
  first_name varchar(50) not null,
  last_name varchar(50),
  title varchar(50),
  notes blob
  ,PRIMARY KEY (id)
);
insert into employees(id,first_name)values(1,'dasd'),(2,'dasd'),(3,'dasd');

create table customers (
   account_number int not null,
   first_name varchar(50) not null,
    last_name varchar(50),
    phone_number varchar(10),
    emergency_name varchar(50),
    emergency_number varchar(10),
    notes blob,
    primary key(account_number)
);
insert into customers(account_number,first_name)values(1,'dasd'),(2,'dasd'),(3,'dasd');
create table room_status (
   room_status int not null,
   notes blob,
   primary key(room_status)
);
insert into room_status(room_status)values(1),(2),(3);

create table room_types(
   room_type INT not null,
   notes blob not null,
   primary key(room_type)
);
insert into room_types(room_type,notes)values(1,'sasas'),(2,'sasas'),(3,'sasasa');

create table bed_types(
  bed_type INT not null,
  notes blob not null,
  primary key(bed_type)
);
insert into bed_types(bed_type,notes)values(1,'sasas'),(2,'sasas'),(3,'sasasa');

create table rooms(
   room_number int not null,
   room_type varchar(50),
   bed_type varchar(50),
   rate varchar(50),
   room_status varchar(50),
   notes blob not null,
   primary key(room_number)
);
insert into rooms(room_number,notes)values(1,'sasas'),(2,'sasas'),(3,'sasasa');

create table payments(
   id INT not null,
   employee_id int,
   payment_date datetime,
   account_number varchar(10),
   first_date_occupied datetime,
   last_date_occupied datetime,
   total_days INT,
   amount_charged double(10,2),
   tax_rate double(10,2),
   tax_amount double(10,2),
   payment_total double(10,2),
   notes blob not null,
   primary key(id)
);
insert into payments(id,notes)values(1,'sasas'),(2,'sasas'),(3,'sasasa');

create table occupancies(
  id int,
  employee_id int ,
  date_occupied datetime,
  account_number varchar(10),
  room_number int,
  rate_applied varchar(10),
  phone_charge varchar(10),
  notes blob not null,
  primary key(id)
);
insert into occupancies(id,notes)values(1,'sasas'),(2,'sasas'),(3,'sasasa');