CREATE OR REPLACE FUNCTION update_reports_clean(fromtime timestamp with time zone, fortime interval DEFAULT '01:00:00'::interval, checkdata boolean DEFAULT true, analyze_it boolean DEFAULT true) RETURNS boolean
    LANGUAGE plpgsql
    SET client_min_messages TO 'ERROR'
    AS $_$
declare rc_part TEXT;
    rui_part TEXT;
    newfortime INTERVAL;
begin
-- this function creates a reports_clean fact table and all associated dimensions
-- intended to be run hourly for a target time three hours ago or so
-- eventually to be replaced by code for the processors to run

-- Version: 7
-- now includes support for rapid betas

-- accepts a timestamptz, so be careful that the calling script is sending
-- something appropriate

-- since we do allow dynamic timestamps, check if we split over a week
-- boundary.  if so, call self recursively for the first half of the period

IF ( week_begins_utc(fromtime) <>
    week_begins_utc( fromtime + fortime - interval '1 second' ) ) THEN
    PERFORM update_reports_clean( fromtime,
        ( week_begins_utc( fromtime + fortime ) - fromtime ), checkdata );
    newfortime := ( fromtime + fortime ) - week_begins_utc( fromtime + fortime );
    fromtime := week_begins_utc( fromtime + fortime );
    fortime := newfortime;
END IF;

-- prevent calling for a period of more than one day

IF fortime > INTERVAL '1 day' THEN
    RAISE NOTICE 'you may not execute this function on more than one day of data';
    RETURN FALSE;
END IF;

-- create a temporary table from the hour of reports you want to
-- process.  generally this will be from 3-4 hours ago to
-- avoid missing reports

-- RULE: replace NULL reason, address, flash_version, os_name with "Unknown"
-- RULE: replace NULL signature, url with ''
-- pre-cleaning: replace NULL product, version with ''
-- RULE: extract number of cores from cpu_info
-- RULE: convert all reference list TEXT values to CITEXT except Signature

create temporary table new_reports
on commit drop
as select uuid,
    date_processed,
    client_crash_date,
    uptime,
    install_age,
    build,
    COALESCE(signature, '')::text as signature,
    COALESCE(reason, 'Unknown')::citext as reason,
    COALESCE(address, 'Unknown')::citext as address,
    COALESCE(flash_version, 'Unknown')::citext as flash_version,
    COALESCE(product, '')::citext as product,
    COALESCE(version, '')::citext as version,
    COALESCE(os_name, 'Unknown')::citext as os_name,
    os_version::citext as os_version,
    coalesce(process_type, 'Browser') as process_type,
    COALESCE(url2domain(url),'') as domain,
    email, user_comments, url, app_notes,
    release_channel, hangid as hang_id,
    cpu_name as architecture,
    get_cores(cpu_info) as cores,
    exploitability
from reports
where date_processed >= fromtime and date_processed < ( fromtime + fortime )
    and completed_datetime is not null;

-- check for no data

PERFORM 1 FROM new_reports
LIMIT 1;
IF NOT FOUND THEN
    IF checkdata THEN
        RAISE NOTICE 'no report data found for period %',fromtime;
        DROP TABLE new_reports;
        RETURN FALSE;
    ELSE
        DROP TABLE new_reports;
        RETURN TRUE;
    END IF;
END IF;

create index new_reports_uuid on new_reports(uuid);
create index new_reports_signature on new_reports(signature);
create index new_reports_address on new_reports(address);
create index new_reports_domain on new_reports(domain);
create index new_reports_reason on new_reports(reason);
analyze new_reports;

-- trim reports_bad to 2 days of data
DELETE FROM reports_bad
WHERE date_processed < ( now() - interval '2 days' );

-- delete any reports which were already processed
delete from new_reports
using reports_clean
where new_reports.uuid = reports_clean.uuid
and reports_clean.date_processed between ( fromtime - interval '1 day' )
and ( fromtime + fortime + interval '1 day' );

-- RULE: strip leading "0.0.0 Linux" from Linux version strings
UPDATE new_reports
SET os_version = regexp_replace(os_version, $x$[0\.]+\s+Linux\s+$x$, '')
WHERE os_version LIKE '%0.0.0%'
    AND os_name ILIKE 'Linux%';

-- insert signatures into signature list
insert into signatures ( signature, first_report, first_build )
select newsigs.* from (
    select signature::citext as signature,
        min(date_processed) as first_report,
        min(build_numeric(build)) as first_build
    from new_reports
    group by signature::citext ) as newsigs
left join signatures
    on newsigs.signature = signatures.signature
where signatures.signature IS NULL;

-- insert oses into os list

PERFORM update_os_versions_new_reports();

-- insert reasons into reason list

PERFORM update_lookup_new_reports('reason');

-- insert addresses into address list

PERFORM update_lookup_new_reports('address');

-- insert flash_versions into flash version list

PERFORM update_lookup_new_reports('flash_version');

-- insert domains into the domain list

PERFORM update_lookup_new_reports('domain');

-- do not update reports_duplicates
-- this procedure assumes that it has already been run
-- later reports_duplicates will become a callable function from this function
-- maybe

-- create empty reports_clean_buffer
create temporary table reports_clean_buffer
(
uuid text not null primary key,
date_processed timestamptz not null,
client_crash_date timestamptz,
product_version_id int,
build numeric,
signature_id int,
install_age interval,
uptime interval,
reason_id int,
address_id int,
os_name citext,
os_version_id int,
major_version int,
minor_version int,
hang_id text,
flash_version_id int,
process_type citext,
release_channel citext,
duplicate_of text,
domain_id int,
architecture citext,
cores int,
exploitability text
) on commit drop ;

-- populate the new buffer with uuid, date_processed,
-- client_crash_date, build, install_time, uptime,
-- hang_id, duplicate_of, reason, address, flash_version,
-- release_channel

-- RULE: convert install_age, uptime to INTERVAL
-- RULE: convert reason, address, flash_version, URL domain to lookup list ID
-- RULE: add possible duplicate UUID link
-- RULE: convert release_channel to canonical release_channel based on
--  channel match list

INSERT INTO reports_clean_buffer

WITH build_type_matches AS (
    select enumlabel as build_type,
        CASE WHEN enumlabel = 'nightly' THEN 'nightly%'
            ELSE enumlabel
            END
        as match_string
    from pg_catalog.pg_enum WHERE enumtypid = 'build_type'::regtype
)

SELECT new_reports.uuid,
    new_reports.date_processed,
    client_crash_date,
    0,
    build_numeric(build),
    signatures.signature_id,
    install_age * interval '1 second',
    uptime * interval '1 second',
    reasons.reason_id,
    addresses.address_id,
    NULL, NULL, 0, 0,
    hang_id,
    flash_versions.flash_version_id,
    process_type,
    build_type_matches.build_type,
    reports_duplicates.duplicate_of,
    domains.domain_id,
    architecture,
    cores,
    exploitability
FROM new_reports
LEFT OUTER JOIN build_type_matches ON new_reports.release_channel ILIKE build_type_matches.match_string
LEFT OUTER JOIN signatures ON new_reports.signature = signatures.signature
LEFT OUTER JOIN reasons ON new_reports.reason = reasons.reason
LEFT OUTER JOIN addresses ON new_reports.address = addresses.address
LEFT OUTER JOIN flash_versions ON new_reports.flash_version = flash_versions.flash_version
LEFT OUTER JOIN reports_duplicates ON new_reports.uuid = reports_duplicates.uuid
    AND reports_duplicates.date_processed BETWEEN (fromtime - interval '1 day') AND (fromtime + interval '1 day' )
LEFT OUTER JOIN domains ON new_reports.domain = domains.domain
ORDER BY new_reports.uuid;

ANALYZE reports_clean_buffer;

-- populate product_version

    -- RULE: populate releases/aurora/nightlies based on matching product name
    -- and version with release_version

UPDATE reports_clean_buffer
SET product_version_id = product_versions.product_version_id
FROM product_versions, new_reports
WHERE reports_clean_buffer.uuid = new_reports.uuid
    AND new_reports.product = product_versions.product_name
    AND new_reports.version = product_versions.release_version
    AND reports_clean_buffer.release_channel = product_versions.build_type
    AND reports_clean_buffer.release_channel <> 'beta';

    -- RULE: populate betas based on matching product_name, version with
    -- release_version, and build number.

UPDATE reports_clean_buffer
SET product_version_id = product_versions.product_version_id
FROM product_versions JOIN product_version_builds USING (product_version_id), new_reports
WHERE reports_clean_buffer.uuid = new_reports.uuid
    AND new_reports.product = product_versions.product_name
    AND new_reports.version = product_versions.release_version
    AND reports_clean_buffer.release_channel = product_versions.build_type
    AND reports_clean_buffer.build = product_version_builds.build_id
    AND reports_clean_buffer.release_channel = 'beta';

-- populate os_name and os_version

-- RULE: set os_name based on name matching strings

UPDATE reports_clean_buffer SET os_name = os_name_matches.os_name
FROM new_reports, os_name_matches
WHERE reports_clean_buffer.uuid = new_reports.uuid
    AND new_reports.os_name ILIKE os_name_matches.match_string;

-- RULE: if os_name isn't recognized, set major and minor versions to 0.
UPDATE reports_clean_buffer SET os_name = 'Unknown',
    major_version = 0, minor_version = 0
WHERE os_name IS NULL OR os_name NOT IN ( SELECT os_name FROM os_names );

-- RULE: set minor_version based on parsing the os_version string
-- for a second decimal between 0 and 1000 if os_name is not Unknown
UPDATE reports_clean_buffer
SET minor_version = substring(os_version from $x$^\d+\.(\d+)$x$)::int
FROM new_reports
WHERE new_reports.uuid = reports_clean_buffer.uuid
    and os_version ~ $x$^\d+$x$
    and substring(os_version from $x$^(\d+)$x$)::numeric < 1000
    and substring(os_version from $x$^\d+\.(\d+)$x$)::numeric < 1000
    and reports_clean_buffer.os_name <> 'Unknown';

-- RULE: set major_version based on parsing the os_vesion string
-- for a number between 0 and 1000, but there's no minor version
UPDATE reports_clean_buffer
SET major_version = substring(os_version from $x$^(\d+)$x$)::int
FROM new_reports
WHERE new_reports.uuid = reports_clean_buffer.uuid
    AND os_version ~ $x$^\d+$x$
        and substring(os_version from $x$^(\d+)$x$)::numeric < 1000
        and reports_clean_buffer.major_version = 0
        and reports_clean_buffer.os_name <> 'Unknown';

UPDATE reports_clean_buffer
SET os_version_id = os_versions.os_version_id
FROM os_versions
WHERE reports_clean_buffer.os_name = os_versions.os_name
    AND reports_clean_buffer.major_version = os_versions.major_version
    AND reports_clean_buffer.minor_version = os_versions.minor_version;

-- Filter B2G reports using update_channel_map

WITH relevant_crashes AS (
    SELECT rc.uuid,
    json_object_field_text(rc.raw_crash, 'Android_Manufacturer') as android_manufacturer,
    json_object_field_text(rc.raw_crash, 'Android_Model') as android_model,
    json_object_field_text(rc.raw_crash, 'Android_Version') as android_version,
    json_object_field_text(rc.raw_crash, 'B2G_OS_Version') as b2g_os_version,
    json_object_field_text(rc.raw_crash, 'Version') AS version_string,
    rcl.build,
    rc.raw_crash
    FROM reports AS rcl
    JOIN raw_crashes AS rc ON rcl.uuid::uuid = rc.uuid
    WHERE
    product = 'B2G'
    AND rcl.date_processed
        BETWEEN (fromtime - interval '1 day') AND (fromtime + interval '1 day' )
    AND rc.date_processed
        BETWEEN (fromtime - interval '1 day') AND (fromtime + interval '1 day' )
),
rewrite_plan AS (
    SELECT json_object_field_text(ucm.rewrite, 'rewrite_build_type_to') as release_channel,
    json_array_elements(ucm.rewrite->'BuildID') AS buildid,
    json_object_field_text(ucm.rewrite, 'Android_Manufacturer') as android_manufacturer,
    json_object_field_text(ucm.rewrite, 'Android_Model') as android_model,
    json_object_field_text(ucm.rewrite, 'Android_Version') as android_version,
    json_object_field_text(ucm.rewrite, 'B2G_OS_Version') as b2g_os_version,
    product_versions.product_version_id
    FROM product_productid_map ppm
    JOIN update_channel_map ucm USING (productid)
    JOIN product_versions ON ppm.product_name = product_versions.product_name
    WHERE product_versions.version_string = trim(both '"' FROM (ucm.rewrite->'Version')::text)
    AND product_versions.product_name = 'B2G'
)

UPDATE reports_clean_buffer
SET release_channel = rp.release_channel,
    product_version_id = rp.product_version_id
FROM relevant_crashes AS rc
JOIN rewrite_plan AS rp ON
    rc.build::text = trim(both '"' from rp.buildid::text)
WHERE rp.android_manufacturer = rc.android_manufacturer
AND rp.android_model = rc.android_model
AND rp.android_version = rc.android_version
AND rp.b2g_os_version = rc.b2g_os_version
AND rc.uuid = reports_clean_buffer.uuid::uuid;

-- copy to reports_bad and delete bad reports
-- RULE: currently we purge reports which have any of the following
-- missing or invalid: product_version, release_channel, os_name

INSERT INTO reports_bad ( uuid, date_processed )
SELECT uuid, date_processed
FROM reports_clean_buffer
WHERE product_version_id = 0
    OR release_channel IS NULL
    OR signature_id IS NULL;

DELETE FROM reports_clean_buffer
WHERE product_version_id = 0
    OR release_channel IS NULL
    OR signature_id IS NULL;

-- check if the right reports_clean partition exists, or create it

rc_part := reports_clean_weekly_partition(fromtime, 'reports_clean');

-- check if the right reports_user_info partition exists, or create it

rui_part := reports_clean_weekly_partition(fromtime, 'reports_user_info');

-- copy to reports_clean

EXECUTE 'INSERT INTO ' || rc_part || '
    (uuid, date_processed, client_crash_date, product_version_id,
     build, signature_id, install_age, uptime,
     reason_id, address_id, os_name, os_version_id,
     hang_id, flash_version_id, process_type, release_channel,
     duplicate_of, domain_id, architecture, cores, exploitability,
     build_type)
SELECT uuid, date_processed, client_crash_date, product_version_id,
        build, signature_id, install_age, uptime,
        reason_id, address_id, os_name, os_version_id,
        hang_id, flash_version_id, process_type, release_channel,
        duplicate_of, domain_id, architecture, cores, exploitability,
        lower(release_channel)::build_type
FROM reports_clean_buffer;';

IF analyze_it THEN
    EXECUTE 'ANALYZE ' || rc_part;
END IF;

-- copy to reports_user_info

EXECUTE 'INSERT INTO ' || rui_part || $$
    ( uuid, date_processed, email, user_comments, url, app_notes )
SELECT new_reports.uuid, new_reports.date_processed,
        email, user_comments, url, app_notes
FROM new_reports JOIN reports_clean_buffer USING ( uuid )
WHERE email <> '' OR user_comments <> ''
    OR url <> '' OR app_notes <> '';$$;

EXECUTE 'ANALYZE ' || rui_part;

-- exit
DROP TABLE new_reports;
DROP TABLE reports_clean_buffer;
RETURN TRUE;

END;
$_$;


