Add type_id to the Alerts table

[schnuerle]

I'd like to add a type_id that references the alert_types table. Currently this table is unused. I also think the type and subtype columns should be removed from the alerts table.

-- modify database
ALTER TABLE waze.alerts ADD COLUMN IF NOT EXISTS type_id INTEGER;
CREATE INDEX CONCURRENTLY alerts_type_id_idx ON waze.alerts (type_id);

-- add data to new type_id field in alerts
UPDATE waze.alerts AS a 
SET type_id = t.id
FROM waze.alert_types AS t
WHERE a.type = t.type 
 and a.subtype = t.subtype 
 and a.type_id is null; 

[schnuerle]

This was meant to be in scope for the initial schema but was left out accidentally.

Type_id needs to be saved as part of the data ingestion process.

[schnuerle]

Working on this here: #41

[sunnywiz]

Because waze sends down subtypes as blanks, need to modify slightly:

UPDATE waze.alerts AS a 
SET type_id = t.id
FROM waze.alert_types AS t
WHERE a.type = t.type 
  and ((a.subtype = t.subtype) OR (a.subtype='' and t.subtype='NO_SUBTYPE'))
  and a.type_id is null;        

[schnuerle]

The issue with this is that Waze change the text values arbitrarily and therefore not all known types get matched so there are blank type_ids. A better solution is needed for all cases.

[sunnywiz]

Suggested approach to handle during ingestion:

• add replacedwithid to alert_types table, self reference to alert_types table, NULLable
• read in cache of type id's so we're not doing a bunch of db calls
• consider the row we're trying to ingest
• if TYPE/SUBTYPE doesn't exist, very carefully {check DB if it exists again, UPSERT a row, check if it exists again} (so it doesn't get added multiple times)
• now that TYPE/SUBTYPE is in the cache,
  ** if there is a replacewithid value consider using that instead (possible care to not be cyclic)
  ** else use the alert_type_id looked up

Then, seperately, a stored procedure:

• activated by humans
• input args are TYPE1 SUBTYPE1 TYPE2 SUBTYPE2 (not id's)
• used to merge one alert_type into another
• sets the former alert_type's replace_with_id to the id of the one that replaces it
• also updates existing data in the database

Over time, we can build up a library of "replace this type with that type" that we can add to the intialization scripts

As Waze changes what they send, the system adapts to that, and there's a manual cleanup of prior data that needs to happen.

The original (historical) type/subtype data in the alerts table stays the same, but the meaning via alert_type_id stays current.