DROP VIEW pc.opportunity_extract;
CREATE OR REPLACE VIEW pc.opportunity_extract AS
SELECT
    id,
    notif_source,
    NULLIF(notif_date,'')::DATE notif_date,
    notif_url::TEXT, --'<a href=' || notif_url || 'target="_blank">Link</a>' notif_url,
    notif_details,
    oppty_desc,
    oppty_source,
    NULLIF(oppty_date,'')::DATE oppty_date,
    oppty_url::TEXT, --'<a href=' || oppty_url || 'target="_blank">Link</a>' oppty_url,
    (oppty_details::jsonb->'data'->>'naics')::jsonb->0->'code'->>0 naics,
    (oppty_details::jsonb->'data'->>'placeOfPerformance')::jsonb->'state'->>'code' pop,
    (oppty_details::jsonb->'data'->>'solicitation')::jsonb->>'setAside' setaside,
    (oppty_details::jsonb->'data'->>'pointOfContact')::jsonb->0->>'fullName' primary_poc_name,
    (oppty_details::jsonb->'data'->>'pointOfContact')::jsonb->0->>'email' primary_poc_email,
    (oppty_details::jsonb->'data'->>'pointOfContact')::jsonb->1->>'fullName' secondary_poc_fullname,
    (oppty_details::jsonb->'data'->>'pointOfContact')::jsonb->1->>'email' secondary_poc_email,
FROM pc.opportunity o
        LEFT JOIN pc.naics n ON ( ((oppty_details::jsonb->'data'->>'naics')::jsonb->0->'code'->>0)::INTEGER = n.naics_code)
/*WHERE CASE WHEN match = 'MATCH' THEN 1
         WHEN n.naics_code IS NOT NULL THEN 1
         WHEN (oppty_details::jsonb->'data'->>'placeOfPerformance')::jsonb->'state'->>'code' IN ('NY', 'CT', 'MA', 'RI') THEN 1
        ELSE 0 END = 1*/
ORDER BY notif_date DESC

CREATE TABLE opportunity_report AS
SELECT * FROM pc.opportunity_extract;


INSERT INTO opportunity_report
SELECT 1, e.* FROM pc.opportunity_extract e LEFT JOIN pc.opportunity_report r USING (id)
WHERE r.id IS NULL

INSERT INTO match_type (match_type)
SELECT DISTINCT match FROM opportunity_report;

UPDATE opportunity_report o SET match_id = m.id
FROM match_type m
WHERE m.match_type = o.match_id

UPDATE opportunity_report SET match_id = 1 WHERE notif_date = '2019-12-22'

WITH comb AS (
    SELECT primary_poc_name AS name, lower(primary_poc_email) AS email
    FROM pc.opportunity_report
    UNION ALL
    SELECT secondary_poc_fullname, lower(secondary_poc_email)
    FROM pc.opportunity_report
) SELECT DISTINCT email FROM comb


WITH omit AS(
    SELECT oppty_details::jsonb->'opportunityId' o FROM pc.opportunity WHERE match = 'SKIP')
UPDATE pc.opportunity
SET match = 'SKIP'
WHERE match IS NULL AND oppty_details::jsonb->'opportunityId' IN (SELECT * FROM omit)


WITH omit AS(
    SELECT COALESCE(oppty_details::jsonb->'parent'->>'opportunityId' , oppty_details::jsonb->>'opportunityId') o, oppty_details::jsonb->>'modifiedDate' d FROM pc.opportunity WHERE NULLIF(match,'') IS NULL),
rec AS (SELECT o, COUNT(*) c, MAX(d) m FROM omit GROUP BY o HAVING COUNT(*) > 1)
UPDATE pc.opportunity o
SET --match = 'SKIP'
  match = CASE WHEN oppty_details::jsonb->>'modifiedDate' != rec.m THEN 'SKIP' END
-- SELECT *
FROM rec
WHERE COALESCE(oppty_details::jsonb->'parent'->>'opportunityId' , oppty_details::jsonb->>'opportunityId') = rec.o
    -- AND match IS NULL
    -- AND oppty_details::jsonb->>'modifiedDate' != rec.m

WITH omit AS(
    SELECT COALESCE(oppty_details::jsonb->'parent'->>'opportunityId' , oppty_details::jsonb->>'opportunityId') o, oppty_details::jsonb->>'status' d, match
    FROM pc.opportunity WHERE NULLIF(match,'') IS NULL),
rec AS (SELECT o, COUNT(*) c, MAX(d) m FROM omit GROUP BY o HAVING COUNT(*) > 1)
SELECT *
FROM pc.opportunity, rec
WHERE COALESCE(oppty_details::jsonb->'parent'->>'opportunityId' , oppty_details::jsonb->>'opportunityId') = rec.o
    --AND match IS NULL
   --AND oppty_details::jsonb->>'modifiedDate' != rec.m


UPDATE pc.opportunity
SET match = 'SKIP'
WHERE notif_source = 'Sources Sought' AND NULLIF(match, '') IS NULL






SELECT jsonb_pretty(oppty_details::jsonb) FROM pc.opportunity

SELECT oppty_details::jsonb->'description'->0->>'body' FROM pc.opportunity
WHERE oppty_details::jsonb->'description'->0->>'body' ~* 'hardware'

SELECT oppty_details::jsonb->'parent'->>'opportunityId'
FROM pc.opportunity
WHERE notif_details ~ 'Enterprise Administrative Support Services'