CREATE OR REPLACE FUNCTION update_exploitability(updateday date, checkdata boolean DEFAULT true, check_period interval DEFAULT '01:00:00'::interval) RETURNS boolean
    LANGUAGE plpgsql
    SET client_min_messages TO 'ERROR'
    AS $$
BEGIN
-- Populate a daily matview which reports on the exploitability of Top Crashers each day
-- depends on tcbs and signatures

-- check if we've been run
IF checkdata THEN
	PERFORM 1 FROM exploitability_reports WHERE report_date = updateday LIMIT 1;
	IF FOUND THEN
		RAISE INFO 'exploitability_reports has already been run for %.',updateday;
	END IF;
END IF;

-- check if reports_clean is complete
IF NOT reports_clean_done(updateday, check_period) THEN
    IF checkdata THEN
        RAISE NOTICE 'Reports_clean has not been updated to the end of %',updateday;
        RETURN FALSE;
    ELSE
        RETURN FALSE;
    END IF;
END IF;

-- check if tcbs is updated
PERFORM 1
FROM tcbs
WHERE tcbs.report_date = updateday
LIMIT 1;

IF NOT FOUND THEN
	IF checkdata THEN
		RAISE NOTICE 'TCBS has not been updated to the end of %',updateday;
        RETURN FALSE;
	ELSE
		RAISE NOTICE 'TCBS has not been updated, skipping.';
		RETURN TRUE;
	END IF;
END IF;


INSERT INTO exploitability_reports (
    signature_id
    , product_version_id
    , product_name
    , version_string
    , signature
    , report_date
    , null_count
    , none_count
    , low_count
    , medium_count
    , high_count
)
select s.signature_id,
    pv.product_version_id,
    pv.product_name,
    pv.version_string,
    s.signature,
    r.date_processed::date,
    sum(CASE WHEN r.exploitability is NULL THEN 1 ELSE 0 END) as count_null,
    sum(CASE WHEN r.exploitability = 'none' THEN 1 ELSE 0 END) as count_none,
    sum(CASE WHEN r.exploitability = 'low' THEN 1 ELSE 0 END) as count_low,
    sum(CASE WHEN r.exploitability = 'medium' THEN 1 ELSE 0 END) as count_medium,
    sum(CASE WHEN r.exploitability = 'high' THEN 1 ELSE 0 END) as count_high
FROM
    signatures s JOIN reports_clean r ON r.signature_id = s.signature_id
JOIN product_versions pv ON pv.product_version_id = r.product_version_id
WHERE s.signature_id IN (select signature_id from tcbs where utc_day_is(tcbs.report_date, updateday))
    AND utc_day_is(r.date_processed, updateday)
GROUP BY s.signature_id, s.signature, r.date_processed::date, pv.product_version_id;

RETURN TRUE;
END; $$;
