050_populate_new_schema_metadata.sql

-- ==============================================================================
-- |   This refreshes static metadata on new and existing databases.
-- |   Nothing should be here that can't run on an existing database.
-- ==============================================================================

SET SEARCH_PATH TO {SCHEMA_NAME};

-- Drop constraints that prohibit record deletion
ALTER TABLE test_templates DROP CONSTRAINT test_templates_test_types_test_type_fk;
ALTER TABLE test_results DROP CONSTRAINT test_results_test_types_test_type_fk;
ALTER TABLE cat_test_conditions DROP CONSTRAINT cat_test_conditions_cat_tests_test_type_fk;

TRUNCATE TABLE profile_anomaly_types;

TRUNCATE TABLE test_types;

TRUNCATE TABLE generation_sets;

INSERT INTO generation_sets (generation_set, test_type)
VALUES  ('Monitor', 'Recency'),
        ('Monitor', 'Row_Ct'),
        ('Monitor', 'Row_Ct_Pct'),
        ('Monitor', 'Daily_Record_Ct'),
        ('Monitor', 'Monthly_Rec_Ct'),
        ('Monitor', 'Weekly_Rec_Ct'),
        ('Monitor', 'Table_Freshness'),
        ('Monitor', 'Schema_Drift');


TRUNCATE TABLE test_templates;

TRUNCATE TABLE cat_test_conditions;

TRUNCATE TABLE target_data_lookups;

TRUNCATE TABLE variant_codings;

INSERT INTO variant_codings (value_type, check_values)
VALUES  ('measure', 'meter|m|metre'),
        ('measure', 'centimeter|cm|centimetre'),
        ('measure', 'millimeter|mm|millimetre'),
        ('measure', 'kilometer|km|kilometre'),
        ('measure', 'inches|inch|in|"'),
        ('measure', 'foot|ft|feet|'''),
        ('measure', 'yard|yd'),
        ('measure', 'mile|mi|miles'),
        ('measure', 'kilogram|kgs|kg'),
        ('measure', 'gram|g'),
        ('measure', 'milligram|mgs|mg'),
        ('measure', 'pound|lb|lbs|pounds'),
        ('measure', 'ounce|oz'),
        ('measure', 'liter|l|litre|liters|litres'),
        ('measure', 'milliliter|ml|millilitre'),
        ('measure', 'cubic meter|m^3|m³|cubic metre'),
        ('measure', 'cubic centimeter|cm^3|cm³|cubic centimetre'),
        ('measure', 'gallon|gal|gallons'),
        ('measure', 'quart|qt'),
        ('measure', 'pint|pt'),
        ('measure', 'cup|cups'),
        ('measure', 'percent|pct|%'),
        ('med_dose', 'fluid ounce|fl oz|fluid ounces'),
        ('med_dose', 'tablet|tab|tabs'),
        ('med_dose', 'capsule|cap|caps'),
        ('med_dose', 'once daily|daily|qd'),
        ('med_dose', 'twice daily|bid'),
        ('med_dose', 'three times daily|tid'),
        ('med_dose', 'four times daily|qid'),
        ('med_dose', 'as needed|prn'),
        ('med_dose', 'before meals|ac'),
        ('med_dose', 'after meals|pc'),
        ('med_dose', 'at bedtime|hs'),
        ('med_dose', 'intravenous|iv'),
        ('med_dose', 'subcutaneous|sc|sq'),
        ('med_dose', 'intramuscular|im'),
        ('med_dose', 'oral|po'),
        ('med_dose', 'per rectum|pr'),
        ('med_dose', 'drops|gtt|gtts'),

        ('med_tx', 'treatment|trx|tx'),
        ('med_tx', 'new patients|new patient|new pt|nrx'),
        ('med_tx', 'patient|pat|pt|px'),
        ('med_tx', 'prescription|rx'),
        ('med_tx', 'hcp|md|dr'),

        ('inv_uom', 'each|ea'),
        ('inv_uom', 'piece|pc|pieces|pcs'),
        ('inv_uom', 'set|sets'),
        ('inv_uom', 'pack|pk|pks'),
        ('inv_uom', 'box|bx|boxes'),
        ('inv_uom', 'case|cases'),
        ('inv_uom', 'bottle|btl|bottles|btls'),
        ('inv_uom', 'dozen|dz'),
        ('inv_uom', 'pair|pr|pairs'),
        ('inv_uom', 'batch|lot|lots'),
        ('inv_uom', 'bundle|bundles'),
        ('inv_uom', 'units|unit|each|ea'),
        ('inv_uom', 'carton|ctn|cartons'),
        ('inv_uom', 'case|cs|ca'),
        ('inv_uom', 'bag|bg|bags'),
        ('status', 'positive|pos|p'),
        ('status', 'negative|neg|n'),
        ('status', 'complete|completed|comp|cmp|c'),
        ('status', 'incomplete|incomp|inc|i'),
        ('status', 'active|act|a'),
        ('status', 'inactive|inact|in|ia|i'),
        ('status', 'enabled|en'),
        ('status', 'disabled|dis'),
        ('status', 'open|opn|o'),
        ('status', 'closed|cls|c'),
        ('status', 'terminated|cancellation|cancelled|cancel|canc|cc'),
        ('status', 'verified|confirmed|conf|cnf|cf'),
        ('status', 'unconfirmed|unconf|ucf'),
        ('status', 'not available|unavailable|n/a|na|unknown|unkn|unk|un'),
        ('status', 'processed|proc|pr'),
        ('status', 'unprocessed|unproc|upr'),
        ('status', 'approved|accepted|accept|appr|ap'),
        ('status', 'unapproved|unappr|uap'),
        ('status', 'rejected|reject|rej|rj|declined'),
        ('status', 'received|recv|rcvd|rcv'),
        ('status', 'on hold|hold|held|paused|pause'),
        ('status', 'not received|nrecv|nrc'),
        ('status', 'shipped|dispatched|despatched|filled|sent|shp|s'),
        ('status', 'not shipped|nshp|ns|unshipped'),
        ('status', 'past due|overdue|late'),
        ('status', 'true|yes|y'),
        ('status', 'true|yes|t'),
        ('status', 'false|no|n'),
        ('status', 'false|no|f'),
        ('status', 'pending|pend|pnd'),
        ('status', 'in process|in progress|active'),
        ('status', 'retain|keep'),
        ('status', 'remove|drop|delete|del'),
        ('status', 'low|lo|l'),
        ('status', 'medium|moderate|med|m'),
        ('status', 'high|hi|h'),
        ('status', 'same|sm'),
        ('status', 'average|mean|avg'),
        ('status', 'decreased|decrease|decr|down|dn'),
        ('status', 'increased|increase|incr|up'),
        ('status', 'qualification|qual|q'),
        ('status', 'qualified|qual|q'),
        ('status', 'failed|fail|f'),
        ('status', 'passed|pass|p|success'),
        ('status', 'deferred|defer|delayed|delay'),
        ('status', 'resolved|fixed|fx'),
        ('crm', 'email|eml|em'),
        ('crm', 'direct mail|mail|dm'),
        ('crm', 'account|acct|act'),
        ('crm', 'clinical|clinic|clin|c'),
        ('crm', 'hospitals|hospital|hosp|hos'),
        ('crm', 'private practice|practice|prac|clinical|clinic'),
        ('crm', 'pharmacy|pharm|phar|rx'),
        ('crm', 'community|comm|com'),
        ('crm', 'academic|educational|ed'),
        ('crm', 'government|govt|gov|gvt|federal|fed'),
        ('demog', 'male|m'),
        ('demog', 'female|f'),
        ('demog', 'single|s'),
        ('demog', 'married|m'),
        ('demog', 'widowed|w'),
        ('demog', 'separated|sep'),
        ('demog', 'divorced|dvcd|div'),
        ('demog', 'partnered|prt'),
        ('demog', 'cohabiting|coh'),
        ('demog', 'engaged|eng'),
        ('demog', 'living|alive|lv'),
        ('demog', 'deceased|dead|dec|dcd'),
        ('demog', 'retired|ret|rt'),
        ('demog', 'employed|emp'),
        ('demog', 'unemployed|unemp'),
        ('demog', 'student|stu|std|st'),
        ('demog', 'child|ch'),
        ('demog', 'adult|ad'),
        ('demog', 'senior|sr'),
        ('demog', 'veteran|vet'),
        ('demog', 'homeowner|homeown'),
        ('demog', 'renter|rnt'),
        ('demog', 'urban|urb'),
        ('demog', 'suburban|sub'),
        ('demog', 'rural|rur'),
        ('demog', 'cellular|cell|mobile|mob'),
        ('chron', 'monday|mon|m'),
        ('chron', 'tuesday|tue|tu'),
        ('chron', 'wednesday|wed|w'),
        ('chron', 'thursday|thu|th'),
        ('chron', 'friday|fri|f'),
        ('chron', 'saturday|sat|sa'),
        ('chron', 'sunday|sun|su'),
        ('chron', 'january|jan|01'),
        ('chron', 'february|feb|02'),
        ('chron', 'march|mar|03'),
        ('chron', 'april|apr|04'),
        ('chron', 'may|05'),
        ('chron', 'june|jun|06'),
        ('chron', 'july|jul|07'),
        ('chron', 'august|aug|08'),
        ('chron', 'september|sept|sep|09'),
        ('chron', 'october|oct|10'),
        ('chron', 'november|nov|11'),
        ('chron', 'december|dec|12'),
        ('chron', 'week-ending|week|wk|w'),
        ('chron', 'month-ending|month-end|month|mo|m'),
        ('chron', 'quarter|quarter-ending|quarter-end|qtr|q'),
        ('chron', 'year|yr|fy|y'),
        ('chron', 'year-to-date|ytd'),
        ('currency', 'us dollars|dollars|usd|us|$'),
        ('currency', 'euro|eur|€'),
        ('currency', 'pound|pounds|gbp|£'),
        ('currency', 'yen|jpy|¥'),
        ('currency', 'yuan|cny|元'),
        ('country', 'united states of america|united states|u.s.a.|u.s.|usa|us'),
        ('country', 'united kingdom|great britain|england|britain|uk|gb|gbr'),
        ('country', 'canada|ca|can'),
        ('country', 'mexico|méxico|mx'),
        ('country', 'australia|au|aus'),
        ('country', 'germany|de|deu'),
        ('country', 'france|fr|fra'),
        ('country', 'italy|it|ita'),
        ('country', 'japan|jp|jpn'),
        ('country', 'china|cn|chn'),
        ('country', 'india|in|ind'),
         ('hr','full-time|ft'),
         ('hr','part-time|pt'),
         ('hr','contract|contractor'),
         ('hr','temporary|temp|tmp'),
         ('hr','intern|internship'),
         ('hr','permanent|perm'),
         ('hr','non-binary|nb'),
         ('hr','active|employed|working'),
         ('hr','inactive|unemployed|not working'),
         ('hr','leave of absence|leave|loa'),
         ('hr','maternity leave|mat leave|mat'),
         ('hr','paternity leave|pat leave|pat'),
         ('hr','sick leave|sick|illness'),
         ('hr','vacation|vac|pto'),
         ('hr','remote|work from home|home|wfh'),
         ('hr','on-site|office based|wfo'),
         ('hr','resigned|quit|left'),
         ('hr','terminated|fired'),
         ('hr','promotion|promoted'),
         ('hr','transfer|transferred|xfer'),
         ('hr','performance review|perf review|pr'),
         ('hr','training|education|ed'),
         ('hr','salary increase|increase|raise'),
         ('hr','bonus|bon|incentive|incent'),
         ('hr','employee referral|referral'),
         ('hr','exit interview|exit'),
         ('office','corporate|corp|co'),
         ('office','headquarters|hq|head office'),
         ('office','branch office|branch|local office'),
         ('office','regional office|region office|regional hub|regionalregion'),
         ('office','sales office|sales|field office'),
         ('office','distribution center|distribution hub|distribution|dist'),
         ('office','manufacturing plant|factory|manufacturing facility|manufacturing|mfg'),
         ('office','research and development|r&d|innovation center'),
         ('office','customer service center|customer support center|service center'),
         ('office','logistics center|logistics|shipping center|shipping'),
         ('office','data center|data hub|it center'),
         ('office','administrative office|admin office|administration|admin'),
         ('office','call center|contact center|customer call center'),
         ('office','warehouse|storage facility|storage|fulfillment center|fulfillment'),
         ('office','retail store|store|retail outlet|retail'),
         ('office','outlet store|outlet|clearance center|clearance'),
         ('office','training center|training facility|learning center'),
         ('office','legal office|legal department|legal|compliance office|compliance'),
         ('office','finance department|finance office|accounting|finance'),
         ('office','human resources|hr'),
         ('office','marketing department|marketing|mktg|sales and marketing|s&m'),
         ('office','operations center|operations|ops center|ops'),
         ('office','executive office|executive suite|c-suite'),
         ('finance','fiscal year|fy'),
         ('finance','forecast|fcast|fore|for'),
         ('finance','actuals|actual|act'),
         ('finance','estimated|estimates|estimate|est'),
         ('finance','credit|cred|cr|c'),
         ('finance','debit|deb|db|d'),
         ('cust_service','new inquiry|ni|new question|nq|new ticket'),
         ('cust_service','open ticket|ot|unresolved|ur'),
         ('cust_service','pending review|pr|awaiting response|ar'),
         ('cust_service','resolved|res|closed|cl'),
         ('cust_service','escalated|esc|high priority|hp'),
         ('cust_service','customer feedback|cf|feedback|fb'),
         ('cust_service','complaint|comp|issue reported|ir'),
         ('cust_service','refund request|rr|refund required|rf'),
         ('cust_service','exchange request|er|exchange required|exr'),
         ('cust_service','return initiated|ri|return started|rs'),
         ('cust_service','follow-up required|follow-up needed|follow-up|follow up|f/u|fu'),
         ('cust_service','customer satisfaction|csat|satisfaction level|sl'),
         ('cust_service','service level agreement|sla|sla compliance|slac'),
         ('cust_service','first response time|frt|initial response time|irt'),
         ('cust_service','average handle time|aht|average resolution time|art'),
         ('cust_service','customer retention|cr|retention rate|rr'),
         ('cust_service','customer churn|churn rate|churn|ch'),
         ('cust_service','net promoter score|nps|promoter score|ps'),
         ('cust_service','case reopened|cr|reopened ticket|rt'),
         ('cust_service','technical support|ts|tech help|th'),
         ('cust_service','billing inquiry|bi|billing question|bq'),
         ('cust_service','product inquiry|pi|product question|pq'),
         ('cust_service','service feedback|sf|service review|sr'),
         ('cust_service','live chat|instant messaging|im'),
         ('cust_service','imessage|text message|text|sms'),
         ('cust_service','email support|es|email inquiry|ei'),
         ('cust_service','phone support|ps|call center|cc'),
         ('cust_service','social media support|sms|social inquiry|social|si'),
         ('cust_service','self-service|help center|ss'),
         ('cust_service','knowledge base|kb|faq|frequently asked questions'),
         ('cust_service','ticket closed without action|tcwa|closed no action|cna'),
         ('pharma','phase 1|phase i'),
         ('pharma','phase 2|phase ii'),
         ('pharma','phase 3|phase iii|late phase'),
         ('pharma','phase 4|phase iv|post-marketing surveillance'),
         ('pharma','clinical trial|clinical study|research|trial'),
         ('pharma','preclinical|pre-clinical|non-clinical studies'),
         ('pharma','in vitro studies|ivs|laboratory studies|lab studies'),
         ('pharma','in vivo studies|animal studies|animal model studies'),
         ('pharma','regulatory submission|rs|submission to regulatory'),
         ('pharma','regulatory approval|ra|approved by regulatory|approval|approved'),
         ('pharma','marketed|commercialized|launched'),
         ('pharma','under review|review by regulatory'),
         ('pharma','rejected by regulatory|regulatory rejection|rejected'),
         ('pharma','drug discovery|dd|early research'),
         ('pharma','formulation development|fd|drug formulation'),
         ('pharma','toxicology studies|tox studies|toxicological assessment|toxo'),
         ('pharma','bioavailability study|ba study|absorption study'),
         ('pharma','bioequivalence study|be study|equivalence study'),
         ('pharma','pharmacokinetics|pk|pharmacokinetic studies'),
         ('pharma','pharmacodynamics|pd|pharmacodynamic studies'),
         ('pharma','clinical development plan|cdp|development strategy'),
         ('pharma','investigational new drug|ind|ind application'),
         ('pharma','new drug application|nda|drug registration'),
         ('pharma','biologics license application|bla|biologics application'),
         ('pharma','orphan drug designation|odd|orphan status|orphan drug'),
         ('pharma','breakthrough therapy designation|btd|expedited development'),
         ('pharma','fast track designation|ftd|accelerated review'),
         ('pharma','priority review|pr|priority assessment'),
         ('pharma','tentative approval|ta|conditional approval'),
         ('pharma','off-label use|off-label|olu|unapproved use|unapproved');