--
--
-- Two stored procedures and a trigger from a MACY'S database. Tables include:
-- People, Address, Employees, EmployeePosition, Customers, ItemsBought, 
-- Products, Brands, Shipping, Suppliers, Jewelry, Clothing, and Shoes
--
-- by Carlie Maxwell in 2016
-- 

-- Take a date and return products sold on that day.
CREATE OR REPLACE FUNCTION datesOfProducts(date, refcursor) RETURNS refcursor AS 
$$
DECLARE
  dateOfProducts date :=$1;
  resultset refcursor :=$2;
BEGIN
  open resultset FOR
      SELECT itemsBought.dateSold, 
             itemsBought.product_id, 
             products.productName,  
             products.color, 
             products.costUSD
      FROM ItemsBought, 
           Products
      WHERE itemsBought.product_id = products.product_id
        and dateOfProducts = itemsBought.dateSold;
  RETURN resultset;
 END
$$
language plpgsql;

-- Test it.
SELECT datesOfProducts('2016-01-14' , 'results');
FETCH ALL FROM results;



-- Take a people id and check when their product will come in.
CREATE OR REPLACE FUNCTION peopleProductArrivalDate(INT, refcursor) RETURNS refcursor AS 
$$
DECLARE
  person INT          :=$1;
  resultset refcursor :=$2;
BEGIN
  OPEN resultset FOR
     SELECT  people.people_id,  
             people.fName,  
             people.lName, 
             products.productName, 
             shipping.expectedArrivalDate 
     FROM people, 
          customers, 
          itemsBought, 
          products, 
          shipping
     WHERE people.people_id = customers.people_id
       AND customers.people_id = itemsBought.people_id
       AND itemsBought.product_id = products.product_id
       AND products.product_id = shipping.product_id
       AND person = people.people_id;
  RETURN resultset;
 END
$$
language plpgsql;

-- Test it.
SELECT peopleProductArrivalDate(101 , 'results');
FETCH ALL FROM results;



-- Update Jewelry table Stored Procedure
CREATE OR REPLACE FUNCTION addToJewelry()
RETURNS TRIGGER AS
$$
BEGIN 
   IF NEW.color = 'sterling silver' THEN
      INSERT INTO jewelry(product_id) 
      VALUES (NEW.product_id);
   END IF;
   RETURN NEW;
END;
$$
language plpgsql;

-- Update Jewelry table Trigger
CREATE TRIGGER addToJewelry
AFTER INSERT ON Products
FOR EACH ROW 
EXECUTE PROCEDURE addToJewelry();

-- Test them.
insert into products (product_id, costUSD, color, productName);
values(314, 132.40, 'sterling silver', 'tiffany necklace');