-- Trigger configuration
-- to execute from cmd heroku pg:psql -a fieldsblaze -f "D:\Project File\Draw IO\Kipu\dbCodeExecute.sql"
-- for order

CREATE OR REPLACE FUNCTION before_insertUpdate_Order()
RETURNS trigger
LANGUAGE 'plpgsql'
AS
$$
DECLARE
objId char(18);
BEGIN
objId=(SELECT sfid FROM salesforce.account WHERE customer_id__c=NEW.customer_id__c LIMIT 1);
IF(objId IS NULL) THEN objId=(SELECT sfid FROM salesforce.account WHERE name='FB_Master Record' LIMIT 1);
END IF;
NEW.AccountId = objId;
RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER "order_Before" BEFORE INSERT OR UPDATE ON salesforce.order FOR EACH ROW EXECUTE FUNCTION public.before_insertUpdate_Order();

-- for contact

CREATE OR REPLACE FUNCTION before_insertUpdate_contact()
RETURNS trigger
LANGUAGE 'plpgsql'
AS
$$
DECLARE
objId char(18);
BEGIN
objId=(SELECT sfid FROM salesforce.account WHERE Customer_Id__c=NEW.External_Account_Id__c LIMIT 1);
IF(objId IS NULL) THEN objId=(SELECT sfid FROM salesforce.account WHERE name='FB_Master Record' LIMIT 1);
END IF;
NEW.AccountId = objId;
RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER "contact_Before" BEFORE INSERT OR UPDATE ON salesforce.contact FOR EACH ROW EXECUTE FUNCTION public.before_insertUpdate_Contact();

-- for OrderItem

CREATE OR REPLACE FUNCTION before_insertUpdate_orderItem()
RETURNS trigger
LANGUAGE 'plpgsql'
AS
$$
DECLARE
strId char(18);
strAccId char(255);
strPBid char(255);
peId char(18);
productId char(18);
BEGIN
strId=(SELECT sfid FROM salesforce.order WHERE order_id__c=NEW.order_id__c LIMIT 1);
-- IF(strId IS NULL) THEN strId=(SELECT sfid FROM salesforce.order WHERE name='FB_Master Record' LIMIT 1);
-- strPBid=(SELECT sfid FROM salesforce.pricebook2 WHERE name='FB_Master Record' LIMIT 1);
-- productId=(SELECT sfid FROM salesforce.product2 WHERE name='FB_Master Record' LIMIT 1);
-- peId=(SELECT sfid FROM salesforce.pricebookentry WHERE pricebook2id=strPBid AND product2id=productId LIMIT 1);
-- ELSE strAccId=(SELECT customer_id__c FROM salesforce.order WHERE order_id__c=NEW.order_id__c LIMIT 1);
-- strPBid= (SELECT price_book_id__c FROM salesforce.account WHERE customer_id__c=strAccId LIMIT 1);
-- productId=(SELECT sfid FROM salesforce.product2 WHERE productid__c=NEW.product_id__c LIMIT 1);
-- peId=(SELECT sfid FROM salesforce.pricebookentry WHERE price_book_id__c=strPBid AND product2id=productId LIMIT 1);
-- END IF;
strPBid= (SELECT price_book_id__c FROM salesforce.account WHERE customer_id__c=strAccId LIMIT 1);
productId=(SELECT sfid FROM salesforce.product2 WHERE productid__c=NEW.product_id__c LIMIT 1);
peId=(SELECT sfid FROM salesforce.pricebookentry WHERE price_book_id__c=strPBid AND product2id=productId LIMIT 1);

NEW.OrderId = strId;
NEW.unitprice=NEW.sale_price__c;
NEW.PricebookEntryId=peId;
NEW.product2id=productId;
RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER "orderItem_Before" BEFORE INSERT OR UPDATE ON salesforce.orderitem FOR EACH ROW EXECUTE FUNCTION public.before_insertUpdate_OrderItem();

-- --  For Product
-- CREATE OR REPLACE FUNCTION after_insert_product()
-- RETURNS trigger
-- LANGUAGE 'plpgsql'
-- AS
-- $$
-- DECLARE
-- productId char(18);
-- pricebookId char(18);

-- BEGIN
-- productId=(SELECT sfid FROM salesforce.product2 WHERE name='FB_Master Record' LIMIT 1);
-- pricebookId=(SELECT sfid FROM salesforce.pricebook2 WHERE name='FB_Master Record' LIMIT 1);
-- INSERT INTO salesforce.pricebookentry(name,unitprice,pricebook2id, product2id)VALUES (NEW.name,0,pricebookId,productId);
-- RETURN NEW;
-- END;
-- $$;

-- CREATE OR REPLACE TRIGGER "product_After" AFTER INSERT ON salesforce.product2 FOR EACH ROW EXECUTE FUNCTION public.after_insert_Product();

-- for priceBook Entry

CREATE OR REPLACE FUNCTION before_insertUpdate_priceBookEntry()
RETURNS trigger
LANGUAGE 'plpgsql'
AS
$$

DECLARE
productId char(18);
pricebookId char(18);
BEGIN
productId=(SELECT sfid FROM salesforce.product2 WHERE ProductId__c=NEW.Product_Id__c LIMIT 1);
IF(productId IS NULL) THEN productId=(SELECT sfid FROM salesforce.product2 WHERE name='FB_Master Record' LIMIT 1);
END IF;
pricebookId=(SELECT sfid FROM salesforce.pricebook2 WHERE External_Price_Book_Id__c=NEW.Price_Book_Id__c LIMIT 1);
IF(pricebookId IS NULL) THEN pricebookId=(SELECT sfid FROM salesforce.pricebook2 WHERE name='FB_Master Record' LIMIT 1);
END IF;
NEW.Product2Id = productId;
NEW.Pricebook2Id = pricebookId;
RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER "priceBookEntry_Before" BEFORE INSERT OR UPDATE ON salesforce.pricebookentry FOR EACH ROW EXECUTE FUNCTION public.before_insertUpdate_PriceBookEntry();

-- Get country name from app
CREATE OR REPLACE FUNCTION getCountry (Pid INT)
    RETURNS  CHAR(120)
	LANGUAGE 'plpgsql'
AS
$$
DECLARE
BEGIN
    RETURN (SELECT country_name FROM public.tbl_countries WHERE id=Pid);
  END;
  $$;

-- Get attendance dateTime for Salesforce name from app
CREATE OR REPLACE FUNCTION attendanceDt(charDate CHAR(100),charTime CHAR(100))
RETURNS CHAR(120)
LANGUAGE 'plpgsql'
AS
$$
DECLARE
convertedDateTime CHAR(120);
tempDateTime CHAR(100);
BEGIN
tempDateTime=(charDate + charTime);
 convertedDateTime = CONVERT_TZ(tempDateTime, 'Asia/Kolkata', 'UTC');
  RETURN convertedDateTime;
END;
$$;

-- Get DateTime in standard UTC format.
CREATE OR REPLACE FUNCTION sfDateTime(currentDateTime CHAR(120))
RETURNS CHAR(120)
LANGUAGE 'plpgsql'
AS
$$
DECLARE
convertedDateTime CHAR(120);
BEGIN
convertedDateTime = CONVERT_TZ(currentDateTime, 'Asia/Kolkata', 'UTC');
  RETURN convertedDateTime;
END;
$$;



-- Insert First Record in salesforce--------------------------------

-- -- Account Table:
-- INSERT INTO salesforce.account(name,description)
-- 	VALUES ('FB_Master Record','This is Account record for App.');


-- -- Order Table:
-- INSERT INTO salesforce.order(name)
-- 	VALUES ();

-- -- Order Item Table:
-- INSERT INTO salesforce.orderitem(name)
-- 	VALUES ();

-- -- Pricebook2 Table:

-- INSERT INTO salesforce.pricebook2(name)
-- 	VALUES ();

-- -- Pricebook Entry Table:
-- INSERT INTO salesforce.pricebookentry(name,unitprice)
-- 	VALUES ('FB_Master Record',0);

-- -- Product2 Table:

-- INSERT INTO salesforce.product2(name)
-- 	VALUES ();

-- -- Contact Table:

-- INSERT INTO salesforce.contact(name)
-- 	VALUES (name);