/*
* File: SmokedTrout_Database.sql
* 
*
*
* Author: Lam, Cheuk Yu
*/


/*
*  Assume a user account 'fsad' with password 'fsad2022' with permission
* to create  databases already exists. 
*/


/* *********************************************************
* Exercise 1. Create the Smoked Trout database
* 
************************************************************ */

-- The first time you login to execute this file with \i it may
-- be convenient to change the working directory.
DROP DATABASE IF EXISTS "SmokedTrout" ;
CREATE USER fsad PASSWORD 'fsad2022' CREATEDB;
GRANT pg_read_server_files TO fsad;
-- In PostgreSQL, folders are identified with '/'


-- 1) Create a database called SmokedTrout.
CREATE DATABASE "SmokedTrout" WITH OWNER fsad
ENCODING = 'UTF8'
CONNECTION LIMIT = -1;

-- 2) Connect to the database
\c "SmokedTrout" fsad





/* *********************************************************
* Exercise 2. Implement the given design in the Smoked Trout database
* 
************************************************************ */

-- 1) Create a new ENUM type called materialState for storing the raw material state
CREATE TYPE "materialState" AS ENUM (
    'Solid',
    'Liquid',
    'Gas',
    'Plasma'
);

-- 2) Create a new ENUM type called materialComposition for storing whether
-- a material is Fundamental or Composite.
CREATE TYPE "materialComposition" AS ENUM (
    'Fundamental',
    'Composite'
);

-- 3) Create the table TradingRoute with the corresponding attributes.
CREATE TABLE "TradingRoute" (
    "MonitoringKey" INTEGER NOT NULL,
    "FleetSize" INTEGER,
    "OperatingCompany" VARCHAR(50),
    "LastYearRevenue"  REAL,
    PRIMARY KEY ("MonitoringKey")
);

-- 4) Create the table Planet with the corresponding attributes.
CREATE TABLE "Planet" (
    "PlanetID" INTEGER NOT NULL,
    "StarSystem" VARCHAR(50),
    "Name" VARCHAR(50),
    "Population" INTEGER,
    PRIMARY KEY ("PlanetID")
);

-- 5) Create the table SpaceStation with the corresponding attributes.
CREATE TABLE "SpaceStation" (
    "StationID" INTEGER NOT NULL,
    "PlanetID" INTEGER,
    "Name" VARCHAR(20),
    "Longitude" VARCHAR(20),
    "Latitude" VARCHAR(20),
    PRIMARY KEY ("StationID")
);

ALTER TABLE "SpaceStation"
    ADD FOREIGN KEY ("PlanetID") 
    REFERENCES "Planet"("PlanetID")
    ON UPDATE CASCADE
    ON DELETE CASCADE
    NOT VALID;

-- 6) Create the parent table Product with the corresponding attributes.
CREATE TABLE "Product" (
    "ProductID" INTEGER NOT NULL,
    "Name" VARCHAR(20),
    "VolumePerTon"  REAL,
    "ValuePerTon"  REAL,
    PRIMARY KEY ("ProductID")
);

-- 7) Create the child table RawMaterial with the corresponding attributes.
CREATE TABLE "RawMaterial" (
    "ProductID" INTEGER,
    "Name" VARCHAR(20),
    "FundamentalOrComposite" "materialComposition",
    "VolumePerTon"  REAL,
    "ValuePerTon"  REAL,
    "State" "materialState",
    PRIMARY KEY ("ProductID")
)
INHERITS ("Product");

-- 8) Create the child table ManufacturedGood. 
CREATE TABLE "ManufacturedGood" (
    "ProductID" INTEGER,
    "Name" VARCHAR(20),
    "VolumePerTon"  REAL,
    "ValuePerTon"  REAL,
    PRIMARY KEY ("ProductID")
)
INHERITS ("Product");

-- 9) Create the table MadeOf with the corresponding attributes.
CREATE TABLE "MadeOf" (
    "ManufacturedGoodID" INTEGER,
    "ProductID" INTEGER
);

-- 10) Create the table Batch with the corresponding attributes.
CREATE TABLE "Batch" (
    "BatchID" INTEGER NOT NULL,
    "ProductID" INTEGER,
    "ExtractionOrManufacturingDate" date,
    "OriginalFrom" INTEGER,
    PRIMARY KEY ("BatchID")
);

ALTER TABLE "Batch"
    ADD FOREIGN KEY ("OriginalFrom")
    REFERENCES "Planet"("PlanetID")
    ON UPDATE CASCADE
    ON DELETE CASCADE
    NOT VALID;

-- 11) Create the table Sells with the corresponding attributes.
CREATE TABLE "Sells" (
    "BatchID" INTEGER,
    "StationID" INTEGER
);

ALTER TABLE "Sells"
    ADD FOREIGN KEY ("BatchID")
    REFERENCES "Batch"("BatchID")
    ON UPDATE CASCADE 
    ON DELETE CASCADE 
    NOT VALID;

ALTER TABLE "Sells"
    ADD FOREIGN KEY ("StationID")
    REFERENCES "SpaceStation"("StationID")
    ON UPDATE CASCADE 
    ON DELETE CASCADE 
    NOT VALID;
-- 12)  Create the table Buys with the corresponding attributes.
CREATE TABLE "Buys" (
    "BatchID" INTEGER,
    "StationID" INTEGER
);

ALTER TABLE "Buys"
    ADD FOREIGN KEY ("BatchID") 
    REFERENCES "Batch"("BatchID") 
    ON UPDATE CASCADE 
    ON DELETE CASCADE 
    NOT VALID;

ALTER TABLE "Buys"
    ADD FOREIGN KEY ("StationID")
    REFERENCES "SpaceStation"("StationID")
    ON UPDATE CASCADE 
    ON DELETE CASCADE 
    NOT VALID;

-- 13)  Create the table CallsAt with the corresponding attributes.
CREATE TABLE "CallsAt" (
    "MonitoringKey" INTEGER,
    "StationID" INTEGER,
    "VisitOrder" INTEGER
);

ALTER TABLE "CallsAt"
    ADD FOREIGN KEY ("MonitoringKey") 
    REFERENCES "TradingRoute"("MonitoringKey") 
    ON UPDATE CASCADE 
    ON DELETE CASCADE 
    NOT VALID;

ALTER TABLE "CallsAt"
    ADD FOREIGN KEY ("StationID") 
    REFERENCES "SpaceStation"("StationID") 
    ON UPDATE CASCADE 
    ON DELETE CASCADE 
    NOT VALID;

-- 14)  Create the table Distance with the corresponding attributes.
CREATE TABLE "Distance" (
    "PlanetOrigin" INTEGER,
    "PlanetDestination" INTEGER,
    "AvgDistance"  REAL
);

ALTER TABLE "Distance"
    ADD FOREIGN KEY ("PlanetDestination")
    REFERENCES "Planet"("PlanetID")
    ON UPDATE CASCADE 
    ON DELETE CASCADE 
    NOT VALID;

ALTER TABLE "Distance"
    ADD FOREIGN KEY ("PlanetOrigin") 
    REFERENCES "Planet"("PlanetID") 
    ON UPDATE CASCADE 
    ON DELETE CASCADE 
    NOT VALID;

/* *********************************************************
* Exercise 3. Populate the Smoked Trout database
* 
************************************************************ */
/* *********************************************************
* NOTE: The copy statement is NOT standard SQL.
* The copy statement does NOT permit on-the-fly renaming columns,
* hence, whenever necessary, we:
* 1) Create a dummy table with the column name as in the file
* 2) Copy from the file to the dummy table
* 3) Copy from the dummy table to the real table
* 4) Drop the dummy table (This is done further below, as I keep
*    the dummy table also to imporrt the other columns)
************************************************************ */



-- 1) Unzip all the data files in a subfolder called data from where you have your code file 
-- NO CODE GOES HERE. THIS STEP IS JUST LEFT HERE TO KEEP CONSISTENCY WITH THE ASSIGNMENT STATEMENT

-- 2) Populate the table TradingRoute with the data in the file TradeRoutes.csv.
CREATE TABLE Dummy (
"MonitoringKey" SERIAL ,
"FleetSize" INTEGER,
"OperatingCompany" VARCHAR (40) ,
"LastYearRevenue" REAL NOT NULL);


\copy Dummy FROM './data/TradeRoutes.csv' WITH (FORMAT CSV, HEADER);

INSERT INTO "TradingRoute" ("MonitoringKey", "OperatingCompany", 
"FleetSize", "LastYearRevenue")
SELECT "MonitoringKey", "OperatingCompany", 
"FleetSize", "LastYearRevenue" FROM Dummy;

DROP TABLE Dummy;

-- 3) Populate the table Planet with the data in the file Planets.csv.
CREATE TABLE Dummy("PlanetID" SERIAL, 
"StarSystem" VARCHAR(20), "Planet" VARCHAR(20), "Population_inMillions_" INTEGER);

\copy Dummy FROM './data/Planets.csv' WITH (FORMAT CSV, HEADER);

INSERT INTO "Planet" ("PlanetID", "StarSystem", "Name", "Population")
SELECT "PlanetID", "StarSystem", "Planet", "Population_inMillions_" FROM Dummy;

DROP TABLE Dummy;

-- 4) Populate the table SpaceStation with the data in the file SpaceStations.csv.
CREATE TABLE Dummy ("StationID" SERIAL, "PlanetID" INTEGER, 
"SpaceStations" VARCHAR(20), "Longitude" VARCHAR(20), "Latitude" VARCHAR(20));

\copy Dummy from './data/SpaceStations.csv' WITH (FORMAT CSV , HEADER);

INSERT INTO "SpaceStation" ("StationID", "PlanetID", "Name", "Longitude", "Latitude")
SELECT "StationID", "PlanetID", "SpaceStations", "Longitude", "Latitude" FROM Dummy;

DROP TABLE Dummy;

-- 5) Populate the tables RawMaterial and Product with the data in the file Products_Raw.csv. 
CREATE TABLE Dummy("ProductID" SERIAL, "Product" VARCHAR(20), "Composite" BOOLEAN, 
"VolumePerTon" REAL, "ValuePerTon" REAL, State "materialState");

\copy Dummy FROM './data/Products_Raw.csv' WITH (FORMAT CSV , HEADER);

ALTER TABLE Dummy 
ALTER COLUMN "Composite" TYPE "materialComposition" USING (
CASE "Composite"
WHEN TRUE THEN 'Composite'::"materialComposition"
WHEN FALSE THEN 'Fundamental'::"materialComposition"
END
);

INSERT INTO "RawMaterial" ("ProductID", "Name", "FundamentalOrComposite", 
"VolumePerTon", "ValuePerTon", "State")
SELECT "ProductID", "Product", "Composite", "VolumePerTon", "ValuePerTon", State 
FROM Dummy;

DROP TABLE Dummy;

-- 6) Populate the tables ManufacturedGood and Product with the data in the file  Products_Manufactured.csv.
CREATE TABLE Dummy ("ProductID" SERIAL, "Product" VARCHAR (20), 
"VolumePerTon" REAL, "ValuePerTon" REAL);

\copy Dummy FROM './data/Products_Manufactured.csv' WITH (FORMAT CSV , HEADER);

INSERT INTO "ManufacturedGood" ("ProductID", "Name", "VolumePerTon", 
"ValuePerTon")
SELECT "ProductID", "Product", "VolumePerTon", "ValuePerTon" FROM Dummy;

DROP TABLE Dummy;

-- 7) Populate the table MadeOf with the data in the file MadeOf.csv.
CREATE TABLE Dummy ("ManufacturedGoodID" INTEGER, "ProductID" INTEGER);

\copy Dummy FROM './data/MadeOf.csv' WITH (FORMAT CSV , HEADER);

INSERT INTO "MadeOf" ("ManufacturedGoodID", "ProductID")
SELECT "ManufacturedGoodID", "ProductID" FROM Dummy;

DROP TABLE Dummy;

-- 8) Populate the table Batch with the data in the file Batches.csv.
CREATE TABLE Dummy("BatchID" SERIAL, "ProductID" INTEGER, 
"ExtractionOrManufacturingDate" DATE, "OriginalFrom" INT);

\copy Dummy FROM './data/Batches.csv' WITH (FORMAT CSV , HEADER);

INSERT INTO "Batch" ("BatchID", "ProductID", "ExtractionOrManufacturingDate", 
"OriginalFrom")
SELECT "BatchID", "ProductID", "ExtractionOrManufacturingDate", "OriginalFrom" 
FROM Dummy;

DROP TABLE Dummy;
-- 9) Populate the table Sells with the data in the file Sells.csv.
CREATE TABLE Dummy("BatchID" INTEGER, "StationID" INT);

\copy Dummy FROM './data/Sells.csv' WITH (FORMAT CSV , HEADER);

INSERT INTO "Sells" ("BatchID", "StationID")
SELECT "BatchID", "StationID" FROM Dummy;

DROP TABLE Dummy;

-- 10) Populate the table Buys with the data in the file Buys.csv.
CREATE TABLE Dummy("BatchID" INTEGER, "StationID" INT);

\copy Dummy FROM './data/Buys.csv' WITH (FORMAT CSV , HEADER);

INSERT INTO "Buys" ("BatchID", "StationID")
SELECT "BatchID", "StationID" FROM Dummy;

DROP TABLE Dummy;

-- 11) Populate the table CallsAt with the data in the file CallsAt.csv.
CREATE TABLE Dummy("MonitoringKey" INTEGER, "StationID" INTEGER, "VisitOrder" INT);

\copy Dummy FROM './data/CallsAt.csv' WITH (FORMAT CSV , HEADER);

INSERT INTO "CallsAt" ("MonitoringKey", "StationID", "VisitOrder") 
SELECT "MonitoringKey", "StationID", "VisitOrder" FROM Dummy;

DROP TABLE Dummy;

-- 12) Populate the table Distance with the data in the file PlanetDistances.csv.
CREATE TABLE Dummy("PlanetOrigin" INTEGER, "PlanetDestination" INTEGER, "Distance" REAL);

\copy Dummy FROM './data/PlanetDistances.csv' WITH (FORMAT CSV, HEADER);

INSERT INTO "Distance" ("PlanetOrigin", "PlanetDestination", "AvgDistance")
SELECT "PlanetOrigin", "PlanetDestination", "Distance" FROM Dummy;

DROP TABLE Dummy;

/* *********************************************************
* Exercise 4. Query the database
* 
************************************************************ */

-- 4.1 Report last year taxes per company

-- 1) Add an attribute Taxes to table TradingRoute
ALTER TABLE "TradingRoute" 
ADD COLUMN "Taxes" numeric;

-- 2) Set the derived attribute taxes as 12% of LastYearRevenue
UPDATE "TradingRoute" 
SET "Taxes" = "LastYearRevenue"*12/100;

-- 3) Report the operating company and the sum of its taxes group by company.
SELECT "OperatingCompany",SUM(TRUNC("Taxes", 4)) AS "Total taxes"
FROM "TradingRoute" 
GROUP BY "OperatingCompany";


-- 4.2 What's the longest trading route in parsecs?

-- 1) Create a dummy table RouteLength to store the trading route and their lengths.
CREATE TABLE "RouteLength" ("RouteMonitoringKey" INTEGER,"RouteTotalDistance" NUMERIC);

-- 2) Create a view EnrichedCallsAt that brings together trading route, space stations and planets.
CREATE VIEW "EnrichedCallsAt" AS 
SELECT "CallsAt"."MonitoringKey", "CallsAt"."StationID", "SpaceStation"."PlanetID", "CallsAt"."VisitOrder"
FROM "CallsAt"
INNER JOIN "SpaceStation" ON "CallsAt"."StationID" = "SpaceStation"."StationID";

-- 3) Add the support to execute an anonymous code block as follows;
DO
$$
DECLARE

-- 4) Within the declare section, declare a variable of type real to store a route total distance.
"RouteDistance" real := 0.0; -- Trading route total distance

-- 5) Within the declare section, declare a variable of type real to store a hop partial distance.
"PartialDistance" real;

-- 6) Within the declare section, declare a variable of type record to iterate over routes.
"route" record;

-- 7) Within the declare section, declare a variable of type record to iterate over hops.
"hop" record;

-- 8) Within the declare section, declare a variable of type text to transiently build dynamic queries.
"query" text;

-- 9) Within the main body section, loop over routes in TradingRoutes
BEGIN
FOR route IN SELECT "MonitoringKey" FROM "TradingRoute"
LOOP

-- 10) Within the loop over routes, get all visited planets (in order) by this trading route.
query := 'CREATE VIEW "PortsOfCall" AS '
	||'SELECT "PlanetID" ,"VisitOrder"'
	||'FROM "EnrichedCallsAt"'
	||'WHERE "MonitoringKey" = ' || route."MonitoringKey"
	||'ORDER BY "VisitOrder"' ;

-- 11) Within the loop over routes, execute the dynamic view
EXECUTE query;

-- 12) Within the loop over routes, create a view Hops for storing the hops of that route. 
CREATE VIEW "Hops" AS
SELECT origin."PlanetID" AS origin, destination."PlanetID" AS destination
FROM "PortsOfCall" origin
INNER JOIN "PortsOfCall" destination ON destination."VisitOrder" = origin."VisitOrder"+1
ORDER BY destination."VisitOrder";

-- 13) Within the loop over routes, initialize the route total distance to 0.0.
"RouteDistance" := 0.0;

-- 14) Within the loop over routes, create an inner loop over the hops
FOR hop IN SELECT origin, destination FROM "Hops"
LOOP

-- 15) Within the loop over hops, get the partial distances of the hop. 
query := 'SELECT "AvgDistance" '
        || 'FROM "Distance" '
        || 'WHERE "Distance"."PlanetOrigin" = $1 '
        || 'AND "Distance"."PlanetDestination" = $2 ';

-- 16)  Within the loop over hops, execute the dynamic view and store the outcome INTO the hop partial distance.
EXECUTE query INTO "PartialDistance" USING hop.origin, hop.destination;

-- 17)  Within the loop over hops, accumulate the hop partial distance to the route total distance.
"RouteDistance" := "RouteDistance"+ "PartialDistance";

-- 18)  Go back to the routes loop and insert into the dummy table RouteLength the pair (RouteMonitoringKey,RouteTotalDistance).
END LOOP;
INSERT INTO "RouteLength" ("RouteMonitoringKey", "RouteTotalDistance") 
VALUES (route."MonitoringKey", "RouteDistance");

-- 19)  Within the loop over routes, drop the view for Hops (and cascade to delete dependent objects).
DROP VIEW "Hops" CASCADE;

-- 20)  Within the loop over routes, drop the view for PortsOfCall (and cascade to delete dependent objects).
DROP VIEW "PortsOfCall" CASCADE;
END LOOP;
END;
$$;

-- 21)  Finally, just report the longest route in the dummy table RouteLength.
SELECT "RouteMonitoringKey" AS "Longest route monitoringKey", 
"RouteTotalDistance" AS "Total length in parsecs" 
FROM "RouteLength" 
WHERE "RouteTotalDistance" = (
    SELECT MAX("RouteTotalDistance") 
    FROM "RouteLength"
    );


\c postgres postgres