--Phase 2: Sql Queries to be optimized

-- Query One:

---- Description :  Select facilities pairs that report the same pollutants`
---- input : NOTHING

-- Draft for the query, rewrite it in any way you want.

SELECT FROM facility FirstTable, facility SecondTable
where FirstTable.ID < SecondTable.ID and NOT EXISTS ( SELECT * from form_entries FirstForm 
where FirstForm.FACILITY_ID = FirstTable.ID  and NOT EXISTS
( Select * from form_enteries SecondForm where SecondForm.FACILITY_ID = SecondTable.ID 
AND SecondTable.POLLUTANT_ID==FirstTable.POLLUTANT_ID);


-- Query Two:

---- Description :  Select for each facility its ID & Name and Pollutant Name the it produces, its Unit & value
--input: entry_year, facility_ID, PID 

-- Draft for the query, rewrite it in any way you want.

SELECT x.ID ,x.NAME as facility_name ,z.NAME as pollutant_name ,z.UNIT as pollutant_unit , w.POLLUTANT_VALUE as pollutant_value 
from facility x , pollutants z, form_enteries w
where x.SECTOR_ID IN (SELECT  y.SECTOR_ID SID from sectors_pollutants y WHERE y.POLLUTANT_ID=PID)
AND z.ID=PID AND (w.POLLUTANT_ID=PID AND w.FACILITY_ID= x.ID AND w.YEAR_OF_ENTRY=entry_year);

-- Query Third:

-- input: FACILITYID,year_entry
-- Important: Make sure before excuting the command that the user have privilage to add in form_entries table, you might use a trigger or make a complex query

-- Draft for the query, rewrite it in any way you want.

INSERT INTO form_enteries (POLLUTANT_ID, POLLUTANT_VALUE,FACILITY_ID,YEAR_OF_ENTRY,DELAY_PERIOD,SOURCE_OF_DATA, PRODUCTION_LINE)
 SELECT  sectors_pollutants.POLLUTANT_ID ,'999',FACILITYID,year_entry,0,'ONLINE',0
                FROM sectors_pollutants 
                WHERE NOT EXISTS (SELECT DISTINCT X.POLLUTANT_ID FROM form_enteries X where X.FACILITY_ID= FACILITYID and X.YEAR_OF_ENTRY= year_entry AND sectors_pollutants.POLLUTANT_ID=X.POLLUTANT_ID)
               AND sectors_pollutants.SECTOR_ID IN (SELECT facility.SECTOR_ID from facility where facility.ID = FACILITYID)
;

-- You can make your one query with approval before Next Sunday (min to have 3 different tables).