drop table if exists public.Wine_Type cascade;
drop table if exists public.Wine cascade;
drop table if exists public.Address cascade;
drop table if exists public.Customer_Type cascade;
drop table if exists public.Customer cascade;
drop table if exists public.Vehicle_Type cascade;
drop table if exists public.Vehicle cascade;
drop table if exists public.Payment cascade;
drop table if exists public.Expense_Type cascade;
drop table if exists public.Warehouse cascade;
drop table if exists public.Employee cascade;
drop table if exists public.Employee_Drives_Vehicle cascade;
drop table if exists public.Shipment cascade;
drop table if exists public.Shipment_Has_Expense_Of_Expense_Type cascade;
drop table if exists public.Shipment_Load cascade;


create table public.Wine_Type(
	    Wine_Type_Id serial primary key,
	    Wine_Type_Name varchar(50) unique not null,
	    Wine_Type_Description varchar(200) not null,
	    Wine_Type_Region varchar(50) not null
);

create table public.Wine(
		Wine_Id serial primary key,		
		Wine_Name varchar(50) unique not null,
		Base_Price double precision not null,
		Year_Produced date not null,
		Wine_Type_Id integer not null,
		foreign key (Wine_Type_Id) references public.Wine_Type (Wine_Type_Id)	
);

create table public.Customer_Type(
	    Customer_Type_Id serial primary key,
	    Customer_Type_Name varchar(50) unique not null,
	    Customer_Type_Description varchar(200) not null
);

create table public.Address(
	    Address_Id serial primary key,
	    Street varchar(70) unique not null,
	    City varchar(50) not null,
	    Postcode varchar(20) not null
);

create table public.Customer(
	    Customer_Id serial primary key,
	    Customer_Name varchar(50) unique not null,
	    Customer_Email varchar(70) not null,
	    Customer_Phone_Number varchar(100) not null,
	    Customer_Type_Id integer not null,
	    Address_Id integer not null,
		foreign key (Customer_Type_Id) references public.Customer_Type (Customer_Type_Id),
		foreign key (Address_Id) references public.Address (Address_Id)
);

create table public.Vehicle_Type(
	    Vehicle_Type_Id serial primary key,
	    Vehicle_Type_Name varchar(50) unique not null
);

create table public.Vehicle(
	    Vehicle_Id serial primary key,
	    Vehicle_Type_Id integer not null,
	    Make varchar(50) not null,
	    Model varchar(50) not null,
	    Registration varchar(50) not null,
	    Capacity integer not null,
		foreign key (Vehicle_Type_Id) references public.Vehicle_Type (Vehicle_Type_Id)
);

create table public.Payment(
	    Payment_Id serial primary key,
	    Payment_Status integer not null
);

create table public.Expense_Type(
	    Expense_Type_Id serial primary key,
	    Expense_Type_Name varchar(50) unique not null,
	    Expense_Type_Description varchar(200) not null
);

create table public.Warehouse(
	    Warehouse_Id serial primary key,
	    Warehouse_Name varchar(50) unique not null,
	    Address_Id integer not null,    
	    foreign key (Address_Id) references public.Address (Address_Id)
);

create table public.Employee(
	    Employee_Id serial primary key,
	    Employee_Name varchar(50) not null,
	    Employee_Surname varchar(50) not null,
	    Warehouse_Id integer not null,    
	    foreign key (Warehouse_Id) references public.Warehouse (Warehouse_Id)
);

create table public.Employee_Drives_Vehicle(
	    Employee_Id integer not null,
	    Vehicle_Id integer not null,
	    primary key (Employee_Id, Vehicle_Id), -- Composite primary key
	    foreign key (Vehicle_Id) references public.Vehicle (Vehicle_Id),
	    foreign key (Employee_Id) references public.Employee (Employee_Id)
);

create table public.Shipment(
		Shipment_Id serial primary key,
		Employee_Id integer not null,
	    Payment_Id integer not null,
	    Vehicle_Id integer not null,
	    Shipment_Date date not null,
	    foreign key (Employee_Id) references public.Employee (Employee_Id),
	    foreign key (Payment_Id) references public.Payment (Payment_Id),
	    foreign key (Vehicle_Id) references public.Vehicle (Vehicle_Id)
);

create table public.Shipment_Has_Expense_Of_Expense_Type(
	    Expense_Type_Id integer not null,
	    Shipment_Id integer not null,
		Amount integer not null,
	    primary key (Expense_Type_Id, Shipment_Id), -- Composite primary key
	    foreign key (Expense_Type_Id) references public.Expense_Type (Expense_Type_Id),
	    foreign key (Shipment_Id) references public.Shipment (Shipment_Id)
);

create table public.Shipment_Load(
	    Shipment_Id  integer not null,
	    Customer_Id  integer not null,
	    Wine_Id  integer not null,
	    Wine_Base_Price double precision not null,
	    Wine_Agreed_Price double precision not null,    
	    Wine_Quantity integer not null,
	    primary key (Shipment_Id, Customer_Id, Wine_Id), -- Composite primary key
	    foreign key (Shipment_Id) references public.Shipment (Shipment_Id),
	    foreign key (Customer_Id) references public.Customer (Customer_Id),
	    foreign key (Wine_Id) references public.Wine (Wine_Id)
);