--AJAY SINGH
--Write a stored procedure to populate the star schema database with the transaction data in the provided database.

CREATE PROCEDURE A10P3

AS
BEGIN

--STEP 0

ALTER TABLE PILOTDIM
DROP CONSTRAINT PK_PILOTDIM

ALTER TABLE AIRCRAFTDIM
DROP CONSTRAINT PK_AIRCRAFTDIM

ALTER TABLE TIMEDIM
DROP CONSTRAINT PK_TIMEDIM

ALTER TABLE CHARTERFACT
DROP CONSTRAINT PK_CHARTERFACT,
CONSTRAINT FK_CHARTERFACT_PILOTDIM,
CONSTRAINT FK_CHARTERFACT_AIRCRAFTDIM,
CONSTRAINT FK_CHARTERFACT_TIMEDIM
--TRUNCATE DATA FROM DATA WAREHOUSE
TRUNCATE TABLE PILOTDIM
TRUNCATE TABLE AIRCRAFTDIM
TRUNCATE TABLE TIMEDIM
TRUNCATE TABLE CHARTERFACT
TRUNCATE TABLE STAGING

--ADD CONSTRAINTS TO DATA WAREHOUSE
ALTER TABLE PILOTDIM
ADD CONSTRAINT PK_PILOT_ID PRIMARY KEY (PILOT_ID)

ALTER TABLE AIRCRAFTDIM
ADD CONSTRAINT PK_AIRCRAFTDIM PRIMARY KEY (AIRCRAFT_ID)

ALTER TABLE TIMEDIM
ADD CONSTRAINT PK_TIME_ID PRIMARY KEY (TIME_ID)

ALTER TABLE CHARTERFACT
ADD CONSTRAINT PK_CHARTERFACT PRIMARY KEY (PILOT_ID, AIRCRAFT_ID, TIME_ID),
CONSTRAINT FK_CHARTERFACT_PILOTDIM FOREIGN KEY (PILOT_ID) REFERENCES PILOTDIM,
CONSTRAINT FK_CHARTERFACT_AIRCRAFTDIM FOREIGN KEY (AIRCRAFT_ID) REFERENCES AIRCRAFTDIM,
CONSTRAINT FK_CHARTERFACT_TIMEDIM FOREIGN KEY (TIME_ID) REFERENCES TIMEDIM

--END
--STEP 1
--POPULATE FROM PRODUCTION DATABASE
INSERT INTO PILOTDIM (EMP_NUM, EMP_LNAME, EMP_FNAME)
SELECT P.EMP_NUM, E.EMP_LNAME, E.EMP_FNAME
FROM PILOT P INNER JOIN EMPLOYEE E ON P.EMP_NUM = E.EMP_NUM

INSERT INTO AIRCRAFTDIM(AC_NUM, MOD_CODE)
SELECT AC_NUMBER, MOD_CODE
FROM AIRCRAFT

INSERT INTO TIMEDIM (CHAR_DATE, CHAR_MONTH, CHAR_YEAR)
SELECT CHAR_DATE, MONTH(CHAR_DATE), YEAR(CHAR_DATE)
FROM CHARTER

INSERT INTO STAGING ( EMP_NUM, CHAR_DATE, AC_NUM, CHAR_FUEL_GALLONS, CHAR_DISTANCE, CHAR_REVENUE)
SELECT DISTINCT P.EMP_NUM, A.AC_NUMBER, C.CHAR_DATE, C.CHAR_FUEL_GALLONS, C.CHAR_DISTANCE, (M.MOD_CHG_MILE*C.CHAR_FUEL_GALLONS) AS CHAR_REVENUE
FROM CHARTER C INNER JOIN PILOT P ON P.EMP_NUM = C.CHAR_PILOT
				INNER JOIN AIRCRAFT A ON A.AC_NUMBER = C.AC_NUMBER
				INNER JOIN MODEL M ON M.MOD_CODE = A.MOD_CODE


--STEP 2.2
UPDATE STAGING
SET PILOT_ID =P.EMP_NUM
FROM STAGING S INNER JOIN PILOT P ON
S.EMP_NUM = P.EMP_NUM

UPDATE STAGING
SET AIRCRAFT_ID = AD.AIRCRAFT_ID
FROM STAGING S INNER JOIN AIRCRAFTDIM AD ON S.AC_NUM = AD.AC_NUM

UPDATE STAGING
SET TIME_ID = TD.TIME_ID
FROM STAGING S INNER JOIN TIMEDIM TD ON S.CHAR_DATE = TD.CHAR_DATE

INSERT INTO CHARTERFACT (PILOT_ID, TIME_ID, AIRCRAFT_ID, CHAR_FUEL_GALLONS, CHAR_DISTANCE, CHAR_REVENUE)
SELECT DISTINCT PILOT_ID, TIME_ID, AIRCRAFT_ID, CHAR_FUEL_GALLONS, CHAR_DISTANCE, CHAR_REVENUE
FROM STAGING

END 
GO