boogerlad/mj - Change XBHLXAKNBJNWSXZMHVKDMDVI7CUF5MN22FFS3XQIOLFDJHDCLTVAC

constraint for store times to be minutes within a week store_open_exception table for things like holidays commonize store name sanitization and geocoding/timezone/osrm hints rebrand summit cannabis dc properly update create_store to work with commonized functions, new times table remove duplicated `require`s

Created by boogerlad on August 10, 2020

XBHLXAKNBJNWSXZMHVKDMDVI7CUF5MN22FFS3XQIOLFDJHDCLTVAC

Dependencies

In channels

main

Change contents

Insertion in initialize.sql at line 45 [7.17]

[5.894]

[6.146]

create or replace function minute_in_range_week(int4range[]) returns boolean as $$
	select every(wow is not null and wow <@ int4range(0, 10080)) from unnest($1) as t(wow);
$$ language sql immutable strict;

Replacement in initialize.sql at line 73 [7.17]

B:BD[8.355] → [2.0:50]

	open int4range[] check (array_ndims(open) = 1),

[8.355]

[8.431]

	open int4range[] check (array_ndims(open) = 1 and minute_in_range_week(open)),

Insertion in initialize.sql at line 105 [7.17]

[9.1405]

[10.5750]

create table store_open_exception (
	store_open_exception_id serial primary key,
	store_id integer not null references store on delete cascade,
	during tsrange not null,
	open int4range[] check (array_ndims(open) = 1 and minute_in_range_week(open))
);

Insertion in initialize.sql at line 268 [7.17]

[10.8751]

[10.8846]

	periods.add_system_time_period('store_open_exception'),
	periods.add_system_versioning('store_open_exception'),

Insertion in initialize.sql at line 484 [7.17]
[11.733]
[11.733]
```
	_extension citext = null,
```
Insertion in initialize.sql at line 521 [7.17]
[11.1250]
[11.1250]
```
			extension,
```
Insertion in initialize.sql at line 542 [7.17]
[11.1472]
[11.1472]
```
			_extension,
```

Insertion in package-lock.json at line 403 [13.145]

[12.3350]

    "csv-parse": {
      "version": "4.12.0",
      "resolved": "https://registry.npmjs.org/csv-parse/-/csv-parse-4.12.0.tgz",
      "integrity": "sha512-wPQl3H79vWLPI8cgKFcQXl0NBgYYEqVnT1i6/So7OjMpsI540oD7p93r3w6fDSyPvwkTepG05F69/7AViX2lXg=="
    },

Replacement in package.json at line 16 [13.29376]
B:BD[9.19056] → [8.2009:2038]
```
    "csv-parse": "^4.10.1",
```
[9.19056]
[13.29598]
```
    "csv-parse": "^4.12.0",
```

Replacement in scrape/common.js at line 10 [14.4897]

B:BD[14.5322] → [9.19258:19306]

∅:D[9.19306] → [14.5352:5354]

B:BD[14.5352] → [14.5352:5354]

const postgres = new (require('pg').Client)();

[14.5322]

[14.5354]

const pg = require('pg');
const postgres = new pg.Client();
const googleMapsClient = new (require("@googlemaps/google-maps-services-js").Client)({});
const gay = /^Friendly Stranger/i;
const gay2 = /^stash\s+(and|&)\s+co/i;
const gay8 = /^fire\s+(and|&)\s+flower/i;
const gay3 = /^tokyo\s+smoke/i;
const gay4 = /^spiritleaf/i;
const gay5 = /^CANNACO/i;
const gay6 = /^one\s+plant/i;
const gay7 = /^sessions/i;

Insertion in scrape/common.js at line 22 [14.4897]

[14.5374]

	sanitize_name: function (n) {
		if(n === 'Cannabis Boutique Inc.') {
			n = 'Cannabis Boutique';
		} else if(n === 'MARY JANE RIGS & CANNABIS') {
			n = 'Mary Jane Rigs & Cannabis';
		} else if(n === 'HELLO CANNABIS SSM') {
			n = 'HELLO CANNABIS'
		} else if(gay2.test(n)) {
			n = 'Stash & Co.';
		} else if(gay3.test(n)) {
			n = 'Tokyo Smoke';
		} else if(gay4.test(n)) {
			n = 'SPIRITLEAF';
		} else if(gay5.test(n)) {
			n = 'CANNACO';
		} else if(gay6.test(n)) {
			n = 'One Plant';
		} else if(gay7.test(n)) {
			n = 'Sessions';
		} else if(gay8.test(n)) {
			n = 'Fire & Flower';
		} else if(n.toLowerCase().trim() === 'rainbow thunder bay cannabis accessories') {
			n = 'Rainbow';
		} else if(n !== 'Made In Cannabis' && n !== 'Mary Jane on Penny Lane' && n !== 'This Is Cannabis' && n !== 'BURNSIDE BUDS.CA' && n !== 'The Cannabis Guys') {
			n = n.replace(/\./g, '');
			let first = n.split(/\s+/);
			//if(first.length > 2) {
				[first, ...n] = first;
				n = n.join(' ');
				//todo: replace if not prefixed by "of", "the", etc. what are those words called?
				n = n.split('-')[0].split(/\bat\b/i)[0].split(/\bon\b/i)[0].replace(/\b(sale(s?)|shop|retail|boutique|market|premium|cannabis|store(s?)|corp(orat(ed|ion))?|inc(orporat(ed|ion))?|co(mpany)?|supply|retailer(s?)|limited|ltd|calgary|bridgeland|downtown|kensington|barrhaven|recreational|dispensary)\b|\(.+\)/gi,'').trim();
				n = (first + ' ' + n).trim().replace(/\s+/g, ' ');
				if(n === '420') {
					//console.log('dumbass');
					n = 'FOUR20';
				} else if(n === 'HOBO') {
					n = 'Hobo';
				} else if(n === 'NUMO') {
					n = 'Numo';
				} else if(gay.test(n)) {
					n = "Friendly Stranger";
				}
			//}
		}
		if(n.toUpperCase() === n) {//todo: check how many are title cased, all caps, all lower.
			//console.log(`don't yell at me :( ${n}`);
		}
		return n;
	},
	g: async function (address) {
		let addr = {address: '', address2: ''};
		let location = (await googleMapsClient.geocode({params: {address, key: process.env.GAPI}})).data;
		if(location.status === 'OK') {
			let wow = 0;
			for(let j = 0; j < location.results[0].address_components.length; ++j) {
				if(location.results[0].address_components[j].types.includes('administrative_area_level_1')) {
					addr.region = location.results[0].address_components[j].long_name;
					++wow;
				} else if(location.results[0].address_components[j].types.includes('country')) {
					addr.country = location.results[0].address_components[j].long_name;
					++wow;
				} else if(location.results[0].address_components[j].types.includes('locality')) {
					addr.city = location.results[0].address_components[j].long_name;
					++wow;
				} else if(location.results[0].address_components[j].types.includes('postal_code')) {
					addr.postal_code = location.results[0].address_components[j].long_name.replace(/\s+/, '');
					++wow;
				} else if(location.results[0].address_components[j].types.includes('route')) {
					addr.address += ' ' + location.results[0].address_components[j].long_name;
					++wow;
				} else if(location.results[0].address_components[j].types.includes('street_number')) {
					addr.address += location.results[0].address_components[j].long_name;
					++wow;
				} else if(location.results[0].address_components[j].types.includes('subpremise')) {
					addr.address2 = location.results[0].address_components[j].long_name;
				}
			}
			if(wow < 6) {
				console.log('hardcode me', address, location);
				throw('unparseable address');
			}
			location = location.results[0].geometry.location;
			let [tz, foot, car] = await Promise.all([
				googleMapsClient.timezone({params: {location, timestamp: 0, key: process.env.GAPI}}),
				request(`http://127.0.0.1:5000/nearest/v1/fuck/${location.lng},${location.lat}`).then(x => JSON.parse(x).waypoints[0].hint),
				request(`http://127.0.0.1:5001/nearest/v1/fuck/${location.lng},${location.lat}`).then(x => JSON.parse(x).waypoints[0].hint)
			]);
			if(tz.data.status === 'OK') {
				return [addr, location, tz.data.timeZoneId, foot, car];
			} else {
				throw tz;
			}
		} else {
			throw location;
		}
	},

Replacement in scrape/common.js at line 144 [14.4897]
B:BD[14.5866] → [14.5866:5888]
```
	postgres: postgres,
```
[14.5866]
[14.5888]
```
	pg,
	postgres,
```
File move: summit.js → dc.js
BF:BFD[14.4884] → [15.2476575:2476586]
[14.4884]
[15.2476587]

Replacement in scrape/dc.js at line 1 [15.2476587]

B:BD[15.2476587] → [15.2476588:2476751]

const request = require('request-promise-native');
require('dotenv').config({path: '../.env'});
const { Client } = require('pg');
let postgres = new Client();

[15.2476587]

[15.2476751]

Object.entries(require('./common.js')).forEach(([name, exported]) => global[name] = exported);

Deletion in scrape/dc.js at line 41 [15.2476587]

B:BD[15.2478051] → [15.2478051:2478428]

let hints = Promise.all([
	request('http://127.0.0.1:5000/nearest/v1/fuck/-120.2209884,55.7501513').then(x => JSON.parse(x).waypoints[0].hint),
	request('http://127.0.0.1:5001/nearest/v1/fuck/-120.2209884,55.7501513').then(x => JSON.parse(x).waypoints[0].hint),
	'https://images.squarespace-cdn.com/content/5d2516d4eafe0d00016d4f52/1563851764355-Z79A7Y9YMODUHQ06GDR2'
]);

Replacement in scrape/dc.js at line 43 [15.2476587]

B:BD[15.2478501] → [15.2478501:2478584]

B:BD[15.2478584] → [4.0:34]

∅:D[4.34] → [15.2478599:2478680]

B:BD[15.2478599] → [15.2478599:2478680]

	.then(async () => postgres.query(
		`with storeid as (
			select ios_store_id(
				'Dawson Creek Cannabis Co.',
				'british columbia',
				'canada',
				'11000 8th Street',
				'Unit 19',

[15.2478501]

[15.2478680]

	.then(async () => {
		let r = await g("UNIT 19 - 11000 8 St, Dawson Creek, V1G 4K6, british columbia, canada");
		return postgres.query(
			`select ios_store_id(

Deletion in scrape/dc.js at line 48 [15.2476587]
B:BD[15.2478701] → [15.2478701:2478716]
B:BD[15.2478716] → [4.35:78]
∅:D[4.78] → [15.2478756:2478792]
B:BD[15.2478756] → [15.2478756:2478792]
```
				'V1G4K6',
				'https://www.dawsoncreekcannabis.co',
				-120.2209884,
				55.7501513,
```

Replacement in scrape/dc.js at line 50 [15.2476587]

B:BD[15.2478810] → [15.2478810:2478839]

				'America/Dawson_Creek',

[15.2478810]

[15.2478839]

				$3,
				$4,
				$5,
				$6,
				'https://www.dawsoncreekcannabis.co',
				$7,
				$8,
				$9,
				$10,
				$11,

Insertion in scrape/dc.js at line 61 [15.2476587]
[15.2478858]
[15.2478858]
```
				null,
```

Replacement in scrape/dc.js at line 63 [15.2476587]

B:BD[15.2478868] → [15.2478868:2479068]

			)
		),
		ins as (
			insert into store_image (store_id, url)
			select ios_store_id, $3 from storeid
			on conflict do nothing
		)
		select ios_store_id from storeid`,
		await hints
	));

[15.2478868]

[15.2479068]

			)`,
			[r[0].region, r[0].country, r[0].address, r[0].address2, r[0].city, r[0].postal_code, r[1].lng, r[1].lat, r[3], r[4], r[2]]
		);
	});

Replacement in scrape/dc.js at line 81 [15.2476587]

B:BD[15.2479720] → [15.2479720:2479818]

		//todo: other official government online stores, prescedence to be deterministic, canada only?

[15.2479720]

[15.2479818]

		//todo: other official government online stores
		//default concentrations from online stores with fixed prescedence. OCS seems to have the highest quality data

Insertion in scrape/dc.js at line 98 [15.2476587]

[15.2480137]

				address is null and address2 is null and city is null and postal_code is null and longitude is null and latitude is null and country = 'canada' and

Replacement in scrape/dc.js at line 100 [15.2476587]

B:BD[15.2480144] → [15.2480144:2480320]

					name = 'Ontario Cannabis Store' and region = 'ontario' and country = 'canada' or
					name = 'BC Cannabis Stores' and region = 'british columbia' and country = 'canada'

[15.2480144]

[15.2480320]

					joined_name = 'ocs' and region = 'ontario' or
					joined_name = 'bc' and region = 'british columbia' or
					joined_name = 'alberta' and region = 'alberta'

Replacement in scrape/dc.js at line 121 [15.2476587]
B:BD[15.2480843] → [4.79:86]
```
})();
```
[15.2480843]
```
})();
```

Deletion in scrape/stores.js at line 2 [8.2622]

B:BD[8.2719] → [8.2719:2810]

const googleMapsClient = new (require("@googlemaps/google-maps-services-js").Client)({});

Deletion in scrape/stores.js at line 3 [8.2622]

B:BD[8.2856] → [8.2856:3124]

const gay = /^Friendly Stranger/i;
const gay2 = /^stash\s+(and|&)\s+co/i;
const gay8 = /^fire\s+(and|&)\s+flower/i;
const gay3 = /^tokyo\s+smoke/i;
const gay4 = /^spiritleaf/i;
const gay5 = /^CANNACO/i;
const gay6 = /^one\s+plant/i;
const gay7 = /^sessions/i;

Deletion in scrape/stores.js at line 5 [8.2622]

B:BD[8.3211] → [8.3211:7318]

function sanitize_name(n) {
	if(n === 'Cannabis Boutique Inc.') {
		n = 'Cannabis Boutique';
	} else if(n === 'MARY JANE RIGS & CANNABIS') {
		n = 'Mary Jane Rigs & Cannabis';
	} else if(n === 'HELLO CANNABIS SSM') {
		n = 'HELLO CANNABIS'
	} else if(gay2.test(n)) {
		n = 'Stash & Co.';
	} else if(gay3.test(n)) {
		n = 'Tokyo Smoke';
	} else if(gay4.test(n)) {
		n = 'SPIRITLEAF';
	} else if(gay5.test(n)) {
		n = 'CANNACO';
	} else if(gay6.test(n)) {
		n = 'One Plant';
	} else if(gay7.test(n)) {
		n = 'Sessions';
	} else if(gay8.test(n)) {
		n = 'Fire & Flower';
	} else if(n.toLowerCase().trim() === 'rainbow thunder bay cannabis accessories') {
		n = 'Rainbow';
	} else if(n !== 'Made In Cannabis' && n !== 'Mary Jane on Penny Lane' && n !== 'This Is Cannabis' && n !== 'BURNSIDE BUDS.CA' && n !== 'The Cannabis Guys') {
		n = n.replace(/\./g, '');
		let first = n.split(/\s+/);
		//if(first.length > 2) {
			[first, ...n] = first;
			n = n.join(' ');
			//todo: replace if not prefixed by "of", "the", etc. what are those words called?
			n = n.split('-')[0].split(/\bat\b/i)[0].split(/\bon\b/i)[0].replace(/\b(sale(s?)|shop|retail|boutique|market|premium|cannabis|store(s?)|corp(orat(ed|ion))?|inc(orporat(ed|ion))?|co(mpany)?|supply|retailer(s?)|limited|ltd|calgary|bridgeland|downtown|kensington|barrhaven|recreational|dispensary)\b|\(.+\)/gi,'').trim();
			n = (first + ' ' + n).trim().replace(/\s+/g, ' ');
			if(n === '420') {
				//console.log('dumbass');
				n = 'FOUR20';
			} else if(n === 'HOBO') {
				n = 'Hobo';
			} else if(n === 'NUMO') {
				n = 'Numo';
			} else if(gay.test(n)) {
				n = "Friendly Stranger";
			}
		//}
	}
	if(n.toUpperCase() === n) {//todo: check how many are title cased, all caps, all lower.
		//console.log(`don't yell at me :( ${n}`);
	}
	return n;
}
async function g(address) {
	let addr = {address: '', address2: ''};
	let location = (await googleMapsClient.geocode({params: {address, key: process.env.GAPI}})).data;
	if(location.status === 'OK') {
		let wow = 0;
		for(let j = 0; j < location.results[0].address_components.length; ++j) {
			if(location.results[0].address_components[j].types.includes('administrative_area_level_1')) {
				addr.region = location.results[0].address_components[j].long_name;
				++wow;
			} else if(location.results[0].address_components[j].types.includes('country')) {
				addr.country = location.results[0].address_components[j].long_name;
				++wow;
			} else if(location.results[0].address_components[j].types.includes('locality')) {
				addr.city = location.results[0].address_components[j].long_name;
				++wow;
			} else if(location.results[0].address_components[j].types.includes('postal_code')) {
				addr.postal_code = location.results[0].address_components[j].long_name.replace(/\s+/, '');
				++wow;
			} else if(location.results[0].address_components[j].types.includes('route')) {
				addr.address += ' ' + location.results[0].address_components[j].long_name;
				++wow;
			} else if(location.results[0].address_components[j].types.includes('street_number')) {
				addr.address += location.results[0].address_components[j].long_name;
				++wow;
			} else if(location.results[0].address_components[j].types.includes('subpremise')) {
				addr.address2 = location.results[0].address_components[j].long_name;
			}
		}
		if(wow < 6) {
			console.log('hardcode me', address, location);
			throw('unparseable address');
		}
		location = location.results[0].geometry.location;
		let [tz, foot, car] = await Promise.all([
			googleMapsClient.timezone({params: {location, timestamp: 0, key: process.env.GAPI}}),
			request(`http://127.0.0.1:5000/nearest/v1/fuck/${location.lng},${location.lat}`).then(x => JSON.parse(x).waypoints[0].hint),
			request(`http://127.0.0.1:5001/nearest/v1/fuck/${location.lng},${location.lat}`).then(x => JSON.parse(x).waypoints[0].hint)
		]);
		if(tz.data.status === 'OK') {
			return [addr, location, tz.data.timeZoneId, foot, car];
		} else {
			throw tz;
		}
	} else {
		throw location;
	}
}

Replacement in server.js at line 8 [17.590]

B:BD[16.271] → [9.19382:19515]

const pool = new (require('pg').Pool)();
const googleMapsClient = new (require("@googlemaps/google-maps-services-js").Client)({});

[16.271]

[3.395]

const pool = new pg.Pool();

Replacement in server.js at line 812 [17.590]

B:BD[9.40938] → [9.40938:42121]

					if(notmissing(parameters, ['name', 'address', 'city', 'region', 'country', 'postal_code'], ws, request_ID)) {
						let params = [
							parameters.name,
							parameters.URL,
							parameters.address,
							parameters.address2 ? parameters.address2 : null,
							parameters.city,
							parameters.region,
							parameters.country,
							parameters.postal_code,
							Boolean(parameters.delivery),
							Boolean(parameters.pickup),
							Boolean(parameters.prepayment),
							parameters.phone ? parameters.phone : null
						];
						//I went through the effort of delaying gapi as long as I could and folding three inserts into one transaction, only for the unique constraint to goof me up. Likely won't happen often though, so accept the tradeoff.
						let q = 1;
						let query = '';
						if(Array.isArray(parameters.taxes) && parameters.taxes.length) {
							query += `, insert${++q} as (
								insert into store_tax (store_id, name, rate, types)
								values ${parameters.taxes.map(tax => `((select store_id from insert1), $${params.push(tax.name)}, $${params.push(tax.rate)}, $${params.push(tax.types)})`).join(', ')}
							)`;

[9.40938]

[9.42121]

					let addr, location, tz, foot, car;
					try {
						[addr, location, tz, foot, car] = await g(`${parameters.address}, ${parameters.address2}, ${parameters.city}, ${parameters.region}, ${parameters.country}, ${parameters.postal_code}`);
					} catch(e) {
						if(e.response) {
							console.error(e.response.data);
						} else {
							console.error(e);

Replacement in server.js at line 821 [17.590]

B:BD[9.42130] → [9.42130:42950]

∅:D[9.42950] → [18.5866:5905]

B:BD[18.5866] → [18.5866:5905]

B:BD[18.5905] → [9.42951:43152]

∅:D[9.43152] → [18.6100:6123]

∅:D[19.6616] → [18.6100:6123]

B:BD[18.6100] → [18.6100:6123]

						if(Array.isArray(parameters.times)) {
							let fuck = [];
							for(let i = 0; i < parameters.times.length; ++i) {
								try {
									let ts = parameters.times[i].time_start.split(':');
									ts = Number(parameters.times[i].day_start) * 1440 + Number(ts[0]) * 60 + Number(ts[1]);
									let te = parameters.times[i].time_end.split(':');
									te = Number(parameters.times[i].day_end) * 1440 + Number(te[0]) * 60 + Number(te[1]);
									if(te <= ts) {
										fuck.push(`(int4range($${params.push(ts)}, 10080)),(int4range(0, $${params.push(te)}))`);
										//oops possibly 0,0 empty range. nvm good job range_agg
									} else {
										fuck.push(`(int4range($${params.push(ts)}, $${params.push(te)}))`);
									}
								} catch(e) {}
							}
							if(fuck.length) {
								query += `, insert${++q} as (
									insert into store_time (store_id, open)
									select store_id, unnest from insert1 cross join (select unnest(range_agg(column1, true, true)) from (values ${fuck.join(',')}) as ex) as idc
								)`;
							}

[9.42130]

[9.43153]

						ws.send(JSON.stringify({
							response_ID: request_ID,
							data: 'geocode failure'
						}));
						break;
					}
					let fuck = [];
					if(Array.isArray(parameters.times)) {
						for(let i = 0; i < parameters.times.length; ++i) {
							try {
								let ts = parameters.times[i].time_start.split(':');
								ts = Number(parameters.times[i].day_start) * 1440 + Number(ts[0]) * 60 + Number(ts[1]);
								let te = parameters.times[i].time_end.split(':');
								te = Number(parameters.times[i].day_end) * 1440 + Number(te[0]) * 60 + Number(te[1]);
								if(ts < 0 || ts >= 10080 || te < 0 || te >= 10080) {
									continue;
								}
								if(te <= ts) {
									fuck.push([ts, 10080], [0, te]);
									//oops possibly 0,0 empty range. nvm good job range_agg
								} else {
									fuck.push([ts, te]);
								}
							} catch(e) {}

Replacement in server.js at line 846 [17.590]

B:BD[9.43162] → [9.43162:43801]

						if(Array.isArray(parameters.images) && parameters.images.length) {
							query += `, insert${++q} as (
								insert into store_image (store_id, URL)
								values ${parameters.images.map(image => `((select store_id from insert1), $${params.push(image)})`).join(', ')}
							)`;
						}
						/*
						two google maps api error handlers because their api is inconsistent as fuck
						missing api key will return 'REQUEST_DENIED', but 200, so (2)
						wrong parameters will return 'INVALID_REQUEST', so 400 (1) for geocode
						but 200 (2) for timezone!! 400 throws automatically, but 200 need to be thrown manually

[9.43162]

[9.43801]

					}
					let jn = sanitize_name(parameters.name)
					let params = [
						jn,
						jn,
						parameters.URL,
						addr.address,
						addr.address2,
						addr.city,
						addr.region,
						addr.country,
						addr.postal_code,
						location.lng,
						location.lat,
						foot,
						car,
						tz,
						parameters.phone ? parameters.phone : null,
						parameters.phone && parameters.extension ? parameters.extension : null,
						Boolean(parameters.delivery),
						Boolean(parameters.pickup),
						Boolean(parameters.prepayment),
						JSON.stringify(fuck)
					];
					let q = 1;
					let query = '';
					if(Array.isArray(parameters.taxes) && parameters.taxes.length) {
						query += `, insert${++q} as (
							insert into store_tax (store_id, name, rate, types)
							values ${parameters.taxes.map(tax => `((select store_id from insert1), $${params.push(tax.name)}, $${params.push(tax.rate)}, $${params.push(tax.types)})`).join(', ')}
						)`;
					}
					if(Array.isArray(parameters.images) && parameters.images.length) {
						query += `, insert${++q} as (
							insert into store_image (store_id, URL)
							values ${parameters.images.map(image => `((select store_id from insert1), $${params.push(image)})`).join(', ')}
						)`;
					}
					pool.query(
						`with insert1 as (
							insert into store (
								joined_name,
								name,
								url,
								address,
								address2,
								city,
								region,
								country,
								postal_code,
								longitude,
								latitude,
								osrm_hint_foot,
								osrm_hint_car,
								timezone,
								phone,
								extension,
								delivery,
								pickup,
								prepayment,
								open
							) select
								$1,
								$2,
								$3,
								$4,
								$5,
								$6,
								$7,
								$8,
								$9,
								$10,
								$11,
								$12,
								$13,
								$14,
								$15,
								$16,
								$17,
								$18,
								$19,
								(select range_agg(int4range((x->>0)::int, (x->>1)::int), true, true) from json_array_elements($20) as t(x))
							returning store_id
						)${query}
						select * from get_stores((select store_id from insert1))`,
						params
					).then(r => {
						ws.publish('store', JSON.stringify({
							what: 'store',
							how: 'add',
							data: r.rows[0]
						}));
						ws.send(JSON.stringify({
							response_ID: request_ID
						}));
					}).catch(e => {
						/* todo
						Class 23 https://www.postgresql.org/docs/current/errcodes-appendix.html with e.code
						e.table
						https://stackoverflow.com/a/4108266
						use implicit names in initialize.sql. then, can use e.constraint, remove e.table + _ from beginning, remove suffix to see which columns are affected
						e.detail is fine for 23505, but 23514 just complains about the row as a whole. how can we get the specific details? https://stackoverflow.com/a/47972916 for python...
						useful
						  code: '23505',
						  detail: 'Key (store, name)=(21, hst) already exists.',
						useless
						  [message]: 'new row for relation "store_tax" violates check constraint "store_tax_rate_check"',
						  name: 'error',
						  length: 266,
						  severity: 'ERROR',
						  code: '23514',
						  detail: 'Failing row contains (15, 28, hst, 2, 2020-02-11 20:20:54.923496-05, infinity).',
						what I want: rate > 0 and rate < 1
						2 > 0 and 2 < 1
						true and false
						false

Replacement in server.js at line 963 [17.590]

B:BD[9.43811] → [9.43811:44228]

∅:D[20.1230] → [18.7264:7274]

∅:D[9.44228] → [18.7264:7274]

B:BD[18.7264] → [18.7264:7274]

B:BD[18.7274] → [9.44229:44335]

∅:D[9.44335] → [18.7564:7574]

B:BD[18.7564] → [18.7564:7574]

B:BD[18.7574] → [9.44336:44411]

∅:D[18.8736] → [21.5582:5592]

∅:D[9.44411] → [21.5582:5592]

B:BD[21.5582] → [21.5582:5592]

B:BD[21.5592] → [9.44412:45157]

∅:D[18.9342] → [21.5756:5766]

∅:D[9.45157] → [21.5756:5766]

B:BD[21.5756] → [21.5756:5766]

B:BD[21.5766] → [9.45158:45495]

∅:D[18.11131] → [21.5867:5876]

∅:D[9.45495] → [21.5867:5876]

B:BD[21.5867] → [21.5867:5876]

B:BD[21.5876] → [9.45496:48004]

						let location;
						try {
							location = (await googleMapsClient.geocode({params: {address: `${parameters.address}, ${parameters.address2}, ${parameters.city}, ${parameters.region}, ${parameters.country}, ${parameters.postal_code}`, key: process.env.GAPI}})).data;
							if(location.status === 'OK') {
								location = location.results[0].geometry.location;
							} else {
								throw location;
							}
						} catch(e) {
							if(e.response) {
								console.error(e.response.data);//google maps api(1)
							}
							else if(e.status) {
								console.error(e);//google maps api(2)
							}
							ws.send(JSON.stringify({
								response_ID: request_ID,
								data: 'google geocode failure'
							}));//todo: status: 'ZERO_RESULTS' => "invalid address :("
							break;
						}
						let hints = Promise.all([
							request(`http://127.0.0.1:5000/nearest/v1/fuck/${location.lng},${location.lat}`).then(x => JSON.parse(x).waypoints[0].hint),
							request(`http://127.0.0.1:5001/nearest/v1/fuck/${location.lng},${location.lat}`).then(x => JSON.parse(x).waypoints[0].hint)
						]);
						let tz;
						try {
							tz = (await googleMapsClient.timezone({params: {location, timestamp: 0, key: process.env.GAPI}})).data;
							if(tz.status === 'OK') {
								tz = tz.timeZoneId;
							} else {
								throw tz;
							}
						} catch(e) {
							if(e.response) {
								console.error(e.response.data);//google maps api(1)
							}
							else if(e.status) {
								console.error(e);//google maps api(2)
							}
							ws.send(JSON.stringify({
								response_ID: request_ID,
								data: 'google timezone failure'
							}));
							break;
						}
						hints = await hints;
						//promise then catch is fine since this is the last thing to do this function. don't need to break;
						pool.query(
							`with insert1 as (
								insert into store (
									name,
									url,
									address,
									address2,
									city,
									region,
									country,
									postal_code,
									delivery,
									pickup,
									prepayment,
									phone,
									longitude,
									latitude,
									osrm_hint_foot,
									osrm_hint_car,
									timezone
								) values (
									$1,
									$2,
									$3,
									$4,
									$5,
									$6,
									$7,
									$8,
									$9,
									$10,
									$11,
									$12,
									$${params.push(location.lng)},
									$${params.push(location.lat)},
									$${params.push(hints[0])},
									$${params.push(hints[1])},
									$${params.push(tz)}
								)
								returning store_id
							)${query}
							select * from get_stores((select store_id from insert1))`,
							params
						).then(r => {
							ws.publish('store', JSON.stringify({
								what: 'store',
								how: 'add',
								data: r.rows[0]
							}));
							ws.send(JSON.stringify({
								response_ID: request_ID
							}));
						}).catch(e => {
							/* todo
							Class 23 https://www.postgresql.org/docs/current/errcodes-appendix.html with e.code
							e.table
							https://stackoverflow.com/a/4108266
							use implicit names in initialize.sql. then, can use e.constraint, remove e.table + _ from beginning, remove suffix to see which columns are affected
							e.detail is fine for 23505, but 23514 just complains about the row as a whole. how can we get the specific details? https://stackoverflow.com/a/47972916 for python...
							useful
							  code: '23505',
							  detail: 'Key (store, name)=(21, hst) already exists.',
							useless
							  [message]: 'new row for relation "store_tax" violates check constraint "store_tax_rate_check"',
							  name: 'error',
							  length: 266,
							  severity: 'ERROR',
							  code: '23514',
							  detail: 'Failing row contains (15, 28, hst, 2, 2020-02-11 20:20:54.923496-05, infinity).',
							what I want: rate > 0 and rate < 1
							2 > 0 and 2 < 1
							true and false
							false
							*/
							console.log(e);
							ws.send(JSON.stringify({
								response_ID: request_ID,
								data: 'db fuckup'
							}));
						});
					}

[9.43811]

[9.48004]

						console.log(e);
						ws.send(JSON.stringify({
							response_ID: request_ID,
							data: 'db fuckup'
						}));
					});