/*
    FlyByNite Data Warehouse Script.
*/

DELETE FROM SALES_FACT_DOS;

DROP TABLE SALES_FACT_DOS;

DROP TABLE CUSTOMER_DOS;

DROP TABLE PRODUCT_DOS;

DROP TABLE COUNTRY_DOS;

DROP TABLE TIMEDIMENSION_DOS;

DROP DIMENSION CUSTOMERDIMENSION_DOS;

DROP MATERIALIZED VIEW MV_SALESSINCE2013_DOS;

CREATE TABLE COUNTRY_DOS(COUNTRYID NUMERIC(10) NOT NULL,
		     COUNTRYNAME VARCHAR2(40) NOT NULL,
 		     REGION VARCHAR2(11) NOT NULL,
		     CONSTRAINT COUNTRY_PK_DOS PRIMARY KEY(COUNTRYID));

CREATE TABLE CUSTOMER_DOS(MOBILEPHONENUMBER NUMERIC(10) NOT NULL,
                      FIRSTNAME VARCHAR2(40) NOT NULL,
                      LASTNAME VARCHAR2(40) NOT NULL,
		      EMAILADDRESS VARCHAR(40) NOT NULL,
                      HOUSEADDRESS VARCHAR2(40) NOT NULL,
                      STREETADDRESS VARCHAR2(50) NOT NULL,
                      CITY VARCHAR2(30) NOT NULL,
                      COUNTY_STATE VARCHAR2(40) NOT NULL,
                      ZIPCODE VARCHAR2(20),
                      COUNTRYID NUMERIC(10) NOT NULL,
		      PASSPORT VARCHAR2(10) NOT NULL,
		      CONSTRAINT CUSTOMER_PK_DOS PRIMARY KEY(EMAILADDRESS),
		      CONSTRAINT COUNTRY_FK1_DOS FOREIGN KEY(COUNTRYID)
                      REFERENCES COUNTRY_DOS(COUNTRYID));

CREATE TABLE PRODUCT_DOS(PRODUCTID NUMERIC(10) NOT NULL,
                     PRODUCTNAME VARCHAR2(255) NOT NULL,
                     PRODUCTUNITCOST DECIMAL(10,2) NOT NULL,
                     CONSTRAINT PRODUCT_PK_DOS PRIMARY KEY(PRODUCTID));

CREATE TABLE TIMEDIMENSION_DOS(TIMEID NUMERIC(10) NOT NULL,
                           TIMEDAY VARCHAR2(10) NOT NULL,
                           TIMEMONTH VARCHAR2(9) NOT NULL,
                           TIMEQUARTER NUMERIC(1) NOT NULL,
                           TIMEYEAR NUMERIC(4) NOT NULL,
                           TIMEDAYOFWEEK VARCHAR2(9) NOT NULL,
                           CONSTRAINT TIME_PK_DOS PRIMARY KEY(TIMEID));

CREATE TABLE SALES_FACT_DOS(SALESID NUMERIC(10) NOT NULL,
                        SALESDATE DATE NOT NULL,
                        SALESUNITS NUMERIC(10) NOT NULL,
                        EUROSALESAMOUNT DECIMAL(10,2) NOT NULL,
                        TIMEID NUMERIC(10) NOT NULL,
                        PRODUCTID NUMERIC(10) NOT NULL,
                        COUNTRYID NUMERIC(10) NOT NULL,
                        CUSTOMERID VARCHAR2(10) NOT NULL,
                        CONSTRAINT SALES_PK_DOS PRIMARY KEY(SALESID),
                        CONSTRAINT TIME_FK_DOS FOREIGN KEY(TIMEID)
                        REFERENCES TIMEDIMENSION_DOS(TIMEID),
                        CONSTRAINT PRODUCT_FK_DOS FOREIGN KEY(PRODUCTID)
                        REFERENCES PRODUCT_DOS(PRODUCTID),
 			CONSTRAINT COUNTRY_FK2_DOS FOREIGN KEY(COUNTRYID)
                        REFERENCES COUNTRY_DOS(COUNTRYID),
                        CONSTRAINT CUSTOMER_FK_DOS FOREIGN KEY(CUSTOMERID)
                        REFERENCES CUSTOMER_DOS(EMAILADDRESS));

CREATE DIMENSION CUSTOMERDIMENSION_DOS
 LEVEL CUSTOMERID   IS CUSTOMER_DOS.MOBILEPHONENUMBER
 LEVEL HOUSEADDRESS IS CUSTOMER_DOS.HOUSEADDRESS
 LEVEL STREETADDRESS IS CUSTOMER_DOS.STREETADDRESS
 LEVEL CITY IS CUSTOMER_DOS.CITY
 LEVEL COUNTY_STATE IS CUSTOMER_DOS.COUNTY_STATE
 LEVEL COUNTRY IS COUNTRY_DOS.COUNTRYID
 LEVEL REGION IS COUNTRY_DOS.REGION
 HIERARCHY ADDRESSROLLUP (
   HOUSEADDRESS  CHILD OF
   STREETADDRESS CHILD OF
   CITY   CHILD OF
   COUNTY_STATE CHILD OF
   COUNTRY CHILD OF
   REGION
  JOIN KEY CUSTOMER_DOS.COUNTRYID REFERENCES COUNTRY)
  ATTRIBUTE CUSTOMERID DETERMINES
   (CUSTOMER_DOS.FIRSTNAME, CUSTOMER_DOS.LASTNAME)
 ATTRIBUTE COUNTRY DETERMINES (COUNTRY_DOS.COUNTRYNAME);


INSERT INTO COUNTRY_DOS VALUES(1,'Ireland','EU');
INSERT INTO COUNTRY_DOS VALUES(2,'England','EU');
INSERT INTO COUNTRY_DOS VALUES(3,'France','EU');
INSERT INTO COUNTRY_DOS VALUES(4,'Spain','EU');
INSERT INTO COUNTRY_DOS VALUES(5,'Germany','EU');
INSERT INTO COUNTRY_DOS VALUES(6,'UK','EU');


INSERT INTO PRODUCT_DOS VALUES(1,'Flights within a country', 25.00);
INSERT INTO PRODUCT_DOS VALUES(2,'Flights Throughout EU' ,70.00);
INSERT INTO PRODUCT_DOS VALUES(3,'Late Booking EU' ,50.00);
INSERT INTO PRODUCT_DOS VALUES(4,'Late Booking INT' ,150.00);
INSERT INTO PRODUCT_DOS VALUES(5,'Budjet Hotel' ,45.00);
INSERT INTO PRODUCT_DOS VALUES(6,'Mid Range Hotel', 60.00);
INSERT INTO PRODUCT_DOS VALUES(7,'Premium Hotel' ,100.00);
INSERT INTO PRODUCT_DOS VALUES(8,'Overweight KG' ,15.00);
INSERT INTO PRODUCT_DOS VALUES(9,'Boarding Card Pen', 40.00);


--CUSTOMER PURCHASING A DOMESTIC FLIGHT FROM IRELAND

INSERT INTO CUSTOMER_DOS VALUES('0877785411', 'Diarmaid', 'O Sullivan', 'diarmaidosullivan@mycit.ie','Ambleside', 'SCR', 'Limerick', 'Limerick', '12554', '1', 'P225654');
INSERT INTO CUSTOMER_DOS VALUES('0877556474', 'Laura', 'Butler', 'laurabutler@gmail.com','Downside', 'Ralla Road', 'Paris', 'Paris', '12555', '3', 'P256487');
INSERT INTO CUSTOMER_DOS VALUES('0851256698', 'James', 'Green', 'jamesgreen@mycit.ie','Rockville', 'Chico Street', 'Valencia', 'Valencia', '34554', '004', 'P226474');
INSERT INTO CUSTOMER_DOS VALUES('0836654214', 'Marcus', 'Finucan', 'marucsfinucan@mycit.ie','25', 'Amber Road', 'Berlin', 'Berlin', '67554', '5', 'P233644');
INSERT INTO CUSTOMER_DOS VALUES('0836652312', 'John', 'O Shea', 'johnoshea@mycit.ie','Nashville Terrace', 'NCR', 'Limerick', 'Limerick', '99554', '1', 'P015654');
INSERT INTO CUSTOMER_DOS VALUES('0862584213', 'David', 'O Leary', 'davidoleary@gmail.com','Broke Yard', 'N50', 'Barcelona', 'Barcelona', '13364', '4', 'P225111');
INSERT INTO CUSTOMER_DOS VALUES('0869089523', 'JJ', 'O Neil', 'jjoneil@mycit.ie','44', 'Mount Terrace', 'Dortmund', 'Dortmund', '12666', '5', 'P227774');
INSERT INTO CUSTOMER_DOS VALUES('0877744569', 'Roisin', 'Murphy', 'roisinmurphy@gmail.com','122', 'Raheen', 'Dublin', 'Dublin', '33554', '1', 'P885654');

SET SERVEROUTPUT ON FORMAT WRAPPED;
EXECUTE DBMS_DIMENSION.DESCRIBE_DIMENSION('CUSTOMERDIMENSION_DOS');

--DATACUBE

SELECT COUNTY_STATE, TIMEMONTH,
               SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
 FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
 WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.MOBILEPHONENUMBER
   AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
   AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
   AND (COUNTRYNAME = 'USA'
        OR COUNTRYNAME = 'Canada')
        AND TIMEYEAR = 2015
 GROUP BY CUBE (COUNTY_STATE, TIMEMONTH);

--ROLLUP

SELECT TIMEMONTH, TIMEYEAR,
               SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
 FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
 WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
   AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
   AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
   AND (COUNTRYNAME = 'Ireland'
        OR COUNTRYNAME = 'UK')
      AND TIMEYEAR BETWEEN 2015 AND 2016
 GROUP BY ROLLUP (TIMEMONTH, TIMEYEAR);

--GROUPING SETS

SELECT ZIPCODE, TIMEMONTH,
       SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
 FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
 WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
   AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
   AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
   AND (COUNTRYNAME = 'USA'
       OR COUNTRYNAME = 'Canada')
  AND TIMEYEAR = 2016
 GROUP BY GROUPING SETS((ZIPCODE, TIMEMONTH),
         ZIPCODE, TIMEMONTH, ());

--MATERIALSED VIEW

CREATE MATERIALIZED VIEW MV_SALESSINCE2013_DOS
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT ZIPCODE, TIMEYEAR,
               SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMREGION
 FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
 WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
   AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
   AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
   AND TIMEYEAR > 2013
 GROUP BY REGION, TIMEYEAR;

SELECT * FROM MV_SALESSINCE2013_DOS WHERE TIMEYEAR = 2016 AND COUNTRYNAME = 'Europe';