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_Details 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;
DROP FUNCTION IF EXISTS GetShippedWinesReport(INT, INT);
DROP FUNCTION IF EXISTS GetCustomerShipmentsReport(INT, INT);


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

create table public.Wine(
		Wine_Id serial primary key,		
		Wine_Name varchar(50) unique not null CHECK (Wine_Name <> ''),
		Base_Price double precision not null CHECK (Base_Price <> 0),
		Year_Produced date not null,
		Wine_Type_Id integer not null CHECK (Wine_Type_Id <> 0),
		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 CHECK (Customer_Type_Name <> ''),
	    Customer_Type_Description varchar(200) not null CHECK (Customer_Type_Description <> '')
);

create table public.Address(
	    Address_Id serial primary key,
	    Street varchar(70) not null CHECK (Street <> ''),
	    City varchar(50) not null CHECK (City <> ''),
		Building_Number integer not null CHECK (Building_Number <> 0),
	    Postcode varchar(20) not null CHECK (Postcode <> '')
);

create table public.Customer(
	    Customer_Id serial primary key,
	    Customer_Name varchar(50) unique not null CHECK (Customer_Name <> ''),
	    Customer_Email varchar(70) not null CHECK (Customer_Email <> ''),
	    Customer_Phone_Number varchar(100) not null CHECK (Customer_Phone_Number <> ''),
	    Customer_Type_Id integer not null CHECK (Customer_Type_Id <> 0),
	    Address_Id integer not null CHECK (Address_Id <> 0),
		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 CHECK (Vehicle_Type_Name <> '')
);

create table public.Vehicle_Details(
	    Vehicle_Details_Id serial primary key,
	    Vehicle_Type_Id integer not null CHECK (Vehicle_Type_Id <> 0),
	    Make varchar(50) not null CHECK (Make <> ''),
	    Model varchar(50) not null CHECK (Model <> ''),
	    Capacity integer not null CHECK (Capacity <> 0),
		foreign key (Vehicle_Type_Id) references public.Vehicle_Type (Vehicle_Type_Id)
);

create table public.Vehicle(
	    Vehicle_Id serial primary key,
	    Vehicle_Details_Id integer not null CHECK (Vehicle_Details_Id <> 0),
	    Registration varchar(50) unique not null CHECK (Registration <> ''),
		foreign key (Vehicle_Details_Id) references public.Vehicle_Details (Vehicle_Details_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 CHECK (Expense_Type_Name <> ''),
	    Expense_Type_Description varchar(200) not null CHECK (Expense_Type_Description <> '')
);

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

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

create table public.Employee_Drives_Vehicle(
	    Employee_Id integer not null CHECK (Employee_Id <> 0),
	    Vehicle_Id integer not null CHECK (Vehicle_Id <> 0),
	    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 CHECK (Employee_Id <> 0),
	    Payment_Id integer not null CHECK (Payment_Id <> 0),
	    Vehicle_Id integer not null CHECK (Vehicle_Id <> 0),
	    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 CHECK (Expense_Type_Id <> 0),
	    Shipment_Id integer not null CHECK (Shipment_Id <> 0),
		Amount integer not null CHECK (Amount <> 0),
	    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 CHECK (Shipment_Id <> 0),
	    Customer_Id  integer not null CHECK (Customer_Id <> 0),
	    Wine_Id  integer not null CHECK (Wine_Id <> 0),
	    Wine_Base_Price double precision not null CHECK (Wine_Base_Price <> 0),
	    Wine_Agreed_Price double precision not null CHECK (Wine_Agreed_Price <> 0),    
	    Wine_Quantity integer not null CHECK (Wine_Quantity <> 0),
	    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)
);

CREATE FUNCTION GetShippedWinesReport(month INT, year INT)
RETURNS TABLE(
    WineName varchar(50),
    WineTypeName varchar(50),
    WineRegion varchar(50),
    YearProduced date,
    TotalWineBasePrice double precision,
    TotalWineAgreedPrice double precision,
    TotalWineQuantity bigint,
    TotalWineProfit double precision,
    PercentageDifference double precision
) 
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        w.wine_name as WineName,
        wt.wine_type_name AS WineTypeName, 
        wt.wine_type_region AS WineRegion, 
        w.year_produced AS YearProduced,
        SUM(sl.wine_base_price * sl.wine_quantity) AS TotalWineBasePrice, 
        SUM(sl.wine_agreed_price * sl.wine_quantity) AS TotalWineAgreedPrice,
        SUM(sl.wine_quantity) AS TotalWineQuantity,
        SUM((sl.wine_agreed_price * sl.wine_quantity) - (sl.wine_base_price * sl.wine_quantity)) AS TotalWineProfit,
		(SUM((sl.wine_agreed_price * sl.wine_quantity) - (sl.wine_base_price * sl.wine_quantity)) / SUM(sl.wine_base_price * sl.wine_quantity)) * 100 as PercentageDifference
    FROM shipment_load sl
    JOIN wine w ON w.wine_id = sl.wine_id
    JOIN wine_type wt ON wt.wine_type_id = w.wine_type_id
    JOIN shipment s ON sl.shipment_id = s.shipment_id
    WHERE EXTRACT(MONTH FROM s.shipment_date) = month
        AND EXTRACT(YEAR FROM s.shipment_date) = year
    GROUP BY
        w.wine_id,
        w.wine_name,
        wt.wine_type_name,
        wt.wine_type_region,
        w.year_produced
	order by SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) desc;
END;
$$;

CREATE FUNCTION GetCustomerShipmentsReport(month INT, year INT)
RETURNS TABLE(
    CustomerName varchar(50),
    CustomerTypeName varchar(50),
    TotalWineQuantity bigint,
    TotalWineProfit double precision,
    TotalWineBasePrice double precision,
    TotalWineAgreedPrice double precision,
    PercentageDifference double precision
) 
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
       c.customer_name as CustomerName,
       ct.customer_type_name as CustomerTypeName,
       SUM(wine_quantity) AS TotalWineQuantity,
       SUM(wine_base_price * wine_quantity) AS TotalWineBasePrice,
       SUM(wine_agreed_price * wine_quantity) AS TotalWineAgreedPrice,
       SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) as TotalWineProfit,
       (SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) / SUM(wine_base_price * wine_quantity)) * 100 as PercentageDifference
FROM shipment_load sl
join customer c on c.customer_id = sl.customer_id
join customer_type ct on c.customer_type_id = ct.customer_type_id
JOIN shipment s ON sl.shipment_id = s.shipment_id
WHERE EXTRACT(MONTH FROM s.shipment_date) = month AND EXTRACT(YEAR FROM s.shipment_date) = year
GROUP BY c.customer_name, ct.customer_type_name
order by SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) desc;
END;
$$;