joined_name citext not null,

	longitude decimal,
	latitude decimal,

	longitude decimal(10, 7) check (longitude > -180 and longitude <= 180),
	latitude decimal(9, 7) check (latitude between -90 and 90),

	extension citext,

	prepayment boolean not null default true--right now, this means the dumb af "give me credit card number" over phone, or etransfer, but eventually will be moneris

	prepayment boolean not null default true,--right now, this means the dumb af "give me credit card number" over phone, or etransfer, but eventually will be moneris
	open int4range[] check (cardinality(open) > 0 and array_ndims(open) = 1),
	check (
		address is null and address2 is null and city is null and postal_code is null and longitude is null and latitude is null or
		address is not null and address2 is not null and city is not null and postal_code is not null and longitude is not null and latitude is not null and (country = 'canada' and postal_code ~* '^([a-z]\d){3}$' or country != 'canada')
	)

create unique index upi_store_3 on store (name, region, country) where address is null and address2 is null and city is null and postal_code is null;
create unique index upi_store_6 on store (name, address, city, region, country, postal_code) where address is not null and address2 is null and city is not null and postal_code is not null;
create unique index upi_store_7 on store (name, address, address2, city, region, country, postal_code) where address is not null and address2 is not null and city is not null and postal_code is not null;

create or replace function idk() returns trigger as $$ begin
	new.open = (select range_agg(x, true, true) from unnest(new.open) as t(x));
	if cardinality(new.open) = 0 then
		new.open = null;
	end if;
	return new;
end $$ language plpgsql volatile;
--drop trigger if exists idk_trigger on store;
create trigger idk_trigger before insert or update on store for each row execute function idk();
create unique index upi_store_3 on store (joined_name, region, country) where address is null and address2 is null and city is null and postal_code is null;
create unique index upi_store_7 on store (joined_name, address, address2, city, region, country, postal_code) where address is not null and address2 is not null and city is not null and postal_code is not null;

drop trigger if exists remove_duplicates_in_array_trigger on store_tax;

--drop trigger if exists remove_duplicates_in_array_trigger on store_tax;

create table store_time (
	store_time_id serial primary key,
	store_id integer not null references store on delete cascade,
	open int4range not null check (not isempty(open)),--number of minutes in a week. possible to use int2range instead but need to be user defined. lazy and edge cases... input should be [,) in case someone puts as end and start for two separate entries, won't collide https://stackoverflow.com/a/27971136
	--when editing: https://stackoverflow.com/a/18146482 beware of canonical form! or force?
	exclude using gist (store_id with =, open with &&)
);

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

			name = _name and

			joined_name = _name and

			joined_name,

			_name,

	times json,

	open integer[][2],

		(
			select json_agg(coalesce(array_agg, '{}') order by generate_series)
			from (
				select
					coalesce(wow, lower(open) / 1440) as x,
					array_agg(array[lower(open), upper(open)] order by case
						when wow is not null then 2147483647
						else lower(open)
					end asc)
				from (
					select
						case when lower(open) = 0 then (
							select lower(open) / 1440
							from store_time
							where
								store_time.store_id = store.store_id and
								upper(open) = 10080
						) end as wow,
						open
					from store_time
					where store_time.store_id = store.store_id
				) as t
				group by x
			) as y full join (
				select generate_series
				from generate_series(0,6)
			) as ayy on y.x = ayy.generate_series
		),

		(select array_agg(array[lower(x), upper(x)]) from unnest(open) as t(x)),

	min_price_g decimal(5, 2),

	min_price_g text,

	min_price_g decimal(5, 2),

	min_price_g text,

	min_price_g decimal(5, 2),

	min_price_g text,

drop trigger if exists valid_line_item_as_of_now_trigger on line_item;

--drop trigger if exists valid_line_item_as_of_now_trigger on line_item;

drop trigger if exists decrement_stock_trigger on cart;

--drop trigger if exists decrement_stock_trigger on cart;

    "csv-parse": "^4.10.1",

}
async function mnmalism(page) {
	await page.setUserAgent((await page.evaluate('navigator.userAgent')).replace('Headless', ''));
	await page.setJavaScriptEnabled(false);
	await page.setRequestInterception(true);
	page.on('request', request => {
		if (request.resourceType() === 'document') {
			request.continue();
		} else {
			request.abort();
		}
	});
	return page;

	mnmalism: async function (page) {
		await page.setUserAgent((await page.evaluate('navigator.userAgent')).replace('Headless', ''));
		await page.setJavaScriptEnabled(false);
		await page.setRequestInterception(true);
		page.on('request', request => {
			if(request.resourceType() === 'document') {
				request.continue();
			} else {
				request.abort();
			}
		});
		return page;
	},

	let storeID = (await postgres.query(`select ios_store_id('Ontario Cannabis Store', 'ontario', 'canada', _url => $1, _delivery => true, _prepayment => true)`, [url])).rows[0].ios_store_id;

	let storeID = (await postgres.query(`select ios_store_id('OCS', 'ontario', 'canada', _url => $1, _delivery => true, _prepayment => true)`, [url])).rows[0].ios_store_id;

Object.entries(require('./common.js')).forEach(([name, exported]) => global[name] = exported);
const googleMapsClient = new (require("@googlemaps/google-maps-services-js").Client)({});
const parse = require('csv-parse/lib/sync');
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;
const pc = /^([a-z]\d){3}$/i;
const apm = /^(\d{1,2}):(\d{2})\s?(([ap])\.?m\.?)?$/i;
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;
	}
}
async function alberta(browser, page) {
	if(page === undefined) {
		page = await browser.newPage();
	}
	page = await mnmalism(page);
	await page.goto('https://aglc.ca/cannabis/retail-cannabis/cannabis-licensee-search');
	let ab = (await page.$eval('#cannabis-results > div > table > tbody', e => e.innerText)).split('\n');
	page.close();
	let names = [];
	let address = [];
	let address2 = [];
	let city = [];
	let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	let phone = [];
	for(let i = 0; i < ab.length; ++i) {
		let t = ab[i].split('\t');
		let r;
		try {
			r = await g(`${t[2]} ${t[0]} alberta ${t[3]} canada`);
		} catch(e) {
			console.error(e);
			console.log(t[1]);
			continue;
		}
		if(!pc.test(r[0].postal_code)) {
			t[3] = t[3].replace(/\s+/g, '');
			if(r[0].postal_code.length === 3 && t[3].length === 6) {
				r[0].postal_code = t[3];
			} else {
				console.error('postal_code goofup', r, t);
				continue;
			}
		}
		names.push(sanitize_name(t[1]));
		address.push(r[0].address);
		address2.push(r[0].address2);
		city.push(r[0].city);
		region.push(r[0].region);
		country.push(r[0].country);
		postal_code.push(r[0].postal_code);
		longitude.push(r[1].lng);
		latitude.push(r[1].lat);
		osrm_hint_foot.push(r[3]);
		osrm_hint_car.push(r[4]);
		timezone.push(r[2]);
		phone.push(t[4]);
	}try{
	console.log((await postgres.query(`insert into store (
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone,
		partner,
		delivery,
		pickup,
		prepayment,
		name
	) select i.*, false, false, false, false, i.joined_name from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::citext[],
		$7::citext[],
		$8::decimal[],
		$9::decimal[],
		$10::text[],
		$11::text[],
		$12::text[],
		$13::packed_phone_number[]
	) as i(
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone
	) where not exists (select 1 from store_history where
		store_history.joined_name = i.joined_name and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = i.region and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = i.joined_name and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = i.region and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		names,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone
	])).rowCount, 'alberta');} catch(e) {console.log(e); console.log('alberta failed')}
}
async function manitoba(browser, page) {
	if(page === undefined) {
		page = await browser.newPage();
	}
	page = await mnmalism(page);
	await page.goto('https://lgcamb.ca/cannabis/store-list/');
	let results = await page.$$eval('.e2272-10.x-text ul', x => {
		for(let i = x.length - 1; i >= 0; --i) {
			x[i] = x[i].textContent.trim();
			if(x[i].length === 0) {
				x.splice(i, 1);
			} else {
				x[i] = x[i].split('\n');
				for(let j = 0; j < x[i].length; ++j) {
					let idx = x[i][j].indexOf(",");
					x[i][j] = [x[i][j].substring(0, idx).trim(), x[i][j].substring(idx + 1).trim()];
				}
			}
		}
		return x;
	});
	let idk = await page.$$eval('.e2272-10.x-text strong', e => e.map(x => x.textContent.replace(':', '').trim()));
	page.close();
	if(results.length === idk.length) {
		let names = [];
		let address = [];
		let address2 = [];
		let city = [];
		let region = [];
		let country = [];
		let postal_code = [];
		let longitude = [];
		let latitude = [];
		let osrm_hint_foot = [];
		let osrm_hint_car = [];
		let timezone = [];
		for(let i = 0; i < idk.length; ++i) {
			for(let j = 0; j < results[i].length; ++j) {
				let lol = sanitize_name(results[i][j][0]);
				if(results[i][j][1] === 'Otineka Mall' && lol === 'Meta') {
					names.push(lol);
					address.push('Otineka Mall Highway 10 North');//gmaps ok
					address2.push('Unit 128');
					city.push('Opaskwayak');
					region.push('Manitoba');
					country.push('Canada');
					postal_code.push('R0B2J0');
					longitude.push('-101.261585');
					latitude.push('53.834096');
					osrm_hint_foot.push(await request(`http://127.0.0.1:5000/nearest/v1/fuck/-101.261585,53.834096`).then(x => JSON.parse(x).waypoints[0].hint));
					osrm_hint_car.push(await request(`http://127.0.0.1:5001/nearest/v1/fuck/-101.261585,53.834096`).then(x => JSON.parse(x).waypoints[0].hint));
					timezone.push('America/Winnipeg');
				} else {
					let r;
					try {
						r = await g(`${results[i][j][1]} ${idk[i]} manitoba canada`);
					} catch(e) {
						console.error(e);
						console.log(results[i][j][0]);
						continue;
					}
					if(!pc.test(r[0].postal_code)) {
						if(results[i][j][1] === '300A North Railway Street') {
							r[0].postal_code = 'R6M1S7';
						} else {
							console.error('postal_code goofup', r, results[i][j], idk[i]);
							continue;
						}
					}
					names.push(lol);
					address.push(r[0].address);
					address2.push(results[i][j][1] === '300A North Railway Street' ? 'A' : r[0].address2);
					city.push(r[0].city);
					region.push(r[0].region);
					country.push(r[0].country);
					postal_code.push(r[0].postal_code);
					longitude.push(r[1].lng);
					latitude.push(r[1].lat);
					osrm_hint_foot.push(r[3]);
					osrm_hint_car.push(r[4]);
					timezone.push(r[2]);
				}
			}
		}try{
		console.log((await postgres.query(`insert into store (
			joined_name,
			address,
			address2,
			city,
			region,
			country,
			postal_code,
			longitude,
			latitude,
			osrm_hint_foot,
			osrm_hint_car,
			timezone,
			partner,
			delivery,
			pickup,
			prepayment,
			name
		) select i.*, false, false, false, false, i.joined_name from unnest (
			$1::citext[],
			$2::citext[],
			$3::citext[],
			$4::citext[],
			$5::citext[],
			$6::citext[],
			$7::citext[],
			$8::decimal[],
			$9::decimal[],
			$10::text[],
			$11::text[],
			$12::text[]
		) as i(
			joined_name,
			address,
			address2,
			city,
			region,
			country,
			postal_code,
			longitude,
			latitude,
			osrm_hint_foot,
			osrm_hint_car,
			timezone
		) where not exists (select 1 from store_history where
			store_history.joined_name = i.joined_name and
			store_history.address = i.address and
			store_history.address2 is not distinct from i.address2 and
			store_history.city = i.city and
			store_history.region = i.region and
			store_history.country = i.country and
			store_history.postal_code = i.postal_code
		) or exists (select 1 from store where
			store.joined_name = i.joined_name and
			store.address = i.address and
			store.address2 is not distinct from i.address2 and
			store.city = i.city and
			store.region = i.region and
			store.country = i.country and
			store.postal_code = i.postal_code
		) on conflict (joined_name, address, address2, city, region, country, postal_code)
		where address is not null and address2 is not null and city is not null and postal_code is not null
		do update set longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
		where store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
			names,
			address,
			address2,
			city,
			region,
			country,
			postal_code,
			longitude,
			latitude,
			osrm_hint_foot,
			osrm_hint_car,
			timezone
		])).rowCount, 'manitoba');} catch(e) {console.log(e); console.log('manitoba failed')}
	} else {
		console.error("https://lgcamb.ca/cannabis/store-list/ city mismatch")
	}
}
async function saskatchewan(browser, page) {
	if(page === undefined) {
		page = await browser.newPage();
	}
	page = await mnmalism(page);
	await page.goto('https://www.slga.com/permits-and-licences/cannabis-permits/cannabis-retailing/cannabis-retailers-in-saskatchewan');
	let results = await page.$$eval('#content h1 + p', e => e.map(x => {
		let t = x.textContent.split('\n');
		if(t.length === 1) {
			while((x = x.nextElementSibling) && x.textContent.trim().toLowerCase() !== 'go to top') {
				t.push(x.textContent.trim());
			}
		}
		return t;
	}));
	page.close();
	let names = [];
	let address = [];
	let address2 = [];
	let city = [];
	let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	let url = [];
	for(let i = 0; i < results.length; ++i) {
		if(results[i][1] === '82B Battlefords Crossing') {
			names.push('Fire & Flower');
			address.push('Battleford Crossing');//googleable, but osm and gmaps no results. fallback to lng/lat
			address2.push('82B');
			city.push('Battleford');
			region.push('Saskatchewan');
			country.push('Canada');
			postal_code.push('S0M0E0');
			longitude.push('-108.3195177');//https://www.openstreetmap.org/node/6257183759
			latitude.push('52.7406683');
			osrm_hint_foot.push(await request(`http://127.0.0.1:5000/nearest/v1/fuck/-108.3195177,52.7406683`).then(x => JSON.parse(x).waypoints[0].hint));
			osrm_hint_car.push(await request(`http://127.0.0.1:5001/nearest/v1/fuck/-108.3195177,52.7406683`).then(x => JSON.parse(x).waypoints[0].hint));
			timezone.push('America/Regina');
			url.push(results[i][3]);
		} else if(results[i][0] === 'Vatic Cannabis Co.') {
			names.push('Vatic');
			address.push('Building # 1 – Unit #15 SE 6-18-18-Parcel B Plan #101924726');//gmaps ok
			address2.push('');
			city.push('Edenwold No. 158');
			region.push('Saskatchewan');
			country.push('Canada');
			postal_code.push('S0G3Z0');
			longitude.push('-104.459866');
			latitude.push('50.488856');
			osrm_hint_foot.push(await request(`http://127.0.0.1:5000/nearest/v1/fuck/-104.459866,50.488856`).then(x => JSON.parse(x).waypoints[0].hint));
			osrm_hint_car.push(await request(`http://127.0.0.1:5001/nearest/v1/fuck/-104.459866,50.488856`).then(x => JSON.parse(x).waypoints[0].hint));
			timezone.push('America/Regina');
			url.push(results[i][3]);
		} else {
			results[i][2] = results[i][2].replace(/\bsk\b/i, 'saskatchewan');
			if(results[i][2].indexOf('saskatchewan') === -1) {
				results[i][2] += 'saskatchewan';
			}
			let r;
			try {
				r = await g(`${results[i][1]} ${results[i][2]} canada`);
			} catch(e) {
				console.error(e);
				console.log(results[i][0]);
				continue;
			}
			if(!pc.test(r[0].postal_code)) {
				console.error('postal_code goofup', r, results[i]);
				continue;
			}
			names.push(sanitize_name(results[i][0]));
			address.push(r[0].address);
			address2.push(r[0].address2);
			city.push(r[0].city);
			region.push(r[0].region);
			country.push(r[0].country);
			postal_code.push(r[0].postal_code);
			longitude.push(r[1].lng);
			latitude.push(r[1].lat);
			osrm_hint_foot.push(r[3]);
			osrm_hint_car.push(r[4]);
			timezone.push(r[2]);
			url.push(results[i][3]);
		}
	}try {
	console.log((await postgres.query(`insert into store (
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		url,
		partner,
		delivery,
		pickup,
		prepayment,
		name
	) select i.*, false, false, false, false, i.joined_name from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::citext[],
		$7::citext[],
		$8::decimal[],
		$9::decimal[],
		$10::text[],
		$11::text[],
		$12::text[],
		$13::text[]
	) as i(
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		url
	) where not exists (select 1 from store_history where
		store_history.joined_name = i.joined_name and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = i.region and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = i.joined_name and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = i.region and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		names,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		url
	])).rowCount, 'saskatchewan');} catch(e) {console.log(e); console.log('saskatchewan failed')}
}
async function newbrunswick(browser, page) {
	if(page === undefined) {
		page = await browser.newPage();
	}
	page = await mnmalism(page);
	await page.goto('https://www.cannabis-nb.com/stores/');
	let results = await page.$$eval('.visible-lg tbody > tr', e => e.map(x => Array.from(x.querySelectorAll('td')).map((y, i) => {
		if(i === 2) {
			return y.textContent.trim().split('\n').map(yy => {
				//does not work monday 8am to wednesday 2pm, for example
				let temp = yy.trim().replace(/-|,/g, ' ').split(/\s+/);
				let d = 0;
				switch(temp[0]) {
					case 'Tue': d = 1440; break;
					case 'Wed': d = 2880; break;
					case 'Thu': d = 4320; break;
					case 'Fri': d = 5760; break;
					case 'Sat': d = 7200; break;
					case 'Sun': d = 8640; break; 
				}
				let out = [];
				for(let j = 2; j < temp.length; j += 2) {
					let beg = temp[j - 1].split(':').map(Number);
					beg = beg[0] * 60 + beg[1];
					let end = temp[j].split(':').map(Number);
					end = end[0] * 60 + end[1];
					let rtn = [d + beg, d + end];
					if(end < beg) {
						if(d === 8640) {
							out.push([rtn[0], 10080]);
							rtn[0] = 0;
							rtn[1] -= 8640;
						} else {
							rtn[1] += 1440;
						}
					}
					out.push(rtn);
				}
				return out;
			});
		} else {
			return y.textContent.trim();
		}
	})));
	page.close();
	let address = [];
	let address2 = [];
	let city = [];
	let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	let open = [];
	for(let i = 0; i < results.length; ++i) {
		if(results[i][1] === '16 Allee De La Cooperative, Richibucto, NB E4W 5V8, Canada') {
			address.push('16 Cooperative Street');
			address2.push('');
			city.push('Richibucto');
			country.push('Canada');
			postal_code.push('E4W3W7');
			longitude.push('-64.8737465');//https://www.openstreetmap.org/node/7062087062
			latitude.push('46.6731998');
			osrm_hint_foot.push(await request(`http://127.0.0.1:5000/nearest/v1/fuck/-64.8737465,46.6731998`).then(x => JSON.parse(x).waypoints[0].hint));
			osrm_hint_car.push(await request(`http://127.0.0.1:5001/nearest/v1/fuck/-64.8737465,46.6731998`).then(x => JSON.parse(x).waypoints[0].hint));
			timezone.push('America/Halifax');
		} else if(results[i][1] === '20 F. Tribe Road, Perth-Andover, NB E7H 3R6, Canada') {
			address.push('20 F. Tribe Road');
			address2.push('');
			city.push('Perth-Andover');
			country.push('Canada');
			postal_code.push('E7H3R6');
			longitude.push('-67.713059');//saw construction via street view
			latitude.push('46.745906');
			osrm_hint_foot.push(await request(`http://127.0.0.1:5000/nearest/v1/fuck/-67.713059,46.745906`).then(x => JSON.parse(x).waypoints[0].hint));
			osrm_hint_car.push(await request(`http://127.0.0.1:5001/nearest/v1/fuck/-67.713059,46.745906`).then(x => JSON.parse(x).waypoints[0].hint));
			timezone.push('America/Halifax');
		} else {
			let r;
			try {
				r = await g(results[i][1]);
			} catch(e) {
				console.error(e);
				console.log(results[i][0]);
				continue;
			}
			if(!pc.test(r[0].postal_code)) {
				console.error('postal_code goofup', r, results[i]);
				continue;
			}
			address.push(r[0].address);
			address2.push(r[0].address2);
			city.push(r[0].city);
			country.push(r[0].country);
			postal_code.push(r[0].postal_code);
			longitude.push(r[1].lng);
			latitude.push(r[1].lat);
			osrm_hint_foot.push(r[3]);
			osrm_hint_car.push(r[4]);
			timezone.push(r[2]);
		}
		region.push('New Brunswick');
		open.push(JSON.stringify(results[i][2].flat()));
	} try {
	console.log((await postgres.query(`insert into store (
		joined_name,
		name,
		url,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open,
		partner,
		delivery,
		pickup,
		prepayment
	) select
		'Cannabis NB',
		'Cannabis NB',
		'cannabis-nb.com',
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		(select array_agg(int4range((x->>0)::int, (x->>1)::int)) from json_array_elements(open) as t(x)),
		false,
		false,
		false,
		false
	from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::citext[],
		$7::decimal[],
		$8::decimal[],
		$9::text[],
		$10::text[],
		$11::text[],
		$12::json[]
	) as i(
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open
	) where not exists (select 1 from store_history where
		store_history.joined_name = 'Cannabis NB' and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = i.region and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = 'Cannabis NB' and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = i.region and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set open = excluded.open, longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.open is distinct from excluded.open and store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		address,//select rows where they haven't been previously deleted
		address2,//what is previously deleted?
		city,//present in history table and not present in current table
		region,//not deleted means not present in history table or present in current table
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open
	])).rowCount, 'newbrunswick');} catch(e) {console.log(e); console.log('newbrunswick failed')}
}
async function britishcolumbia() {
	//todo: https://justice.gov.bc.ca/cannabislicensing/api/establishments/map contains longitude & latitude, so google maps api unnecessary if address2 can be parsed and short forms (ave, rd) are dealt with
	let bc = await request('https://justice.gov.bc.ca/cannabislicensing/api/establishments/map-json').then(JSON.parse);
	let names = [];
	let address = [];
	let address2 = [];
	let city = [];
	let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	let phone = [];
	for(let i = 0; i < bc.length; ++i) {
		if(bc[i].Status === 'Open') {
			let r;
			try {
				r = await g(`${bc[i].Address} ${bc[i].City} british columbia ${bc[i].Postal} canada`);
			} catch(e) {
				console.error(e);
				console.log(bc[i].Name);
				continue;
			}
			if(!pc.test(r[0].postal_code)) {
				bc[i].Postal = bc[i].Postal.replace(/\s+/g, '');
				if(r[0].postal_code.length === 3 && bc[i].Postal.length === 6) {
					r[0].postal_code = bc[i].Postal
				} else {
					console.error('postal_code goofup', r, bc[i]);
					continue;
				}
			}
			names.push(sanitize_name(bc[i].Name));
			address.push(r[0].address);
			address2.push(r[0].address2);
			city.push(r[0].city);
			region.push(r[0].region);
			country.push(r[0].country);
			postal_code.push(r[0].postal_code);
			longitude.push(r[1].lng);
			latitude.push(r[1].lat);
			osrm_hint_foot.push(r[3]);
			osrm_hint_car.push(r[4]);
			timezone.push(r[2]);
			phone.push(bc[i].Phone);
		}
	} try{
	console.log((await postgres.query(`insert into store (
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone,
		partner,
		delivery,
		pickup,
		prepayment,
		name
	) select i.*, false, false, false, false, i.joined_name from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::citext[],
		$7::citext[],
		$8::decimal[],
		$9::decimal[],
		$10::text[],
		$11::text[],
		$12::text[],
		$13::packed_phone_number[]
	) as i(
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone
	) where not exists (select 1 from store_history where
		store_history.joined_name = i.joined_name and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = i.region and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = i.joined_name and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = i.region and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		names,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone
	])).rowCount, 'bc');} catch(e) {console.log(e); console.log('bc failed')}
}
async function newfoundland() {
	let nl = (await request('https://stores.boldapps.net/front-end/get_surrounding_stores.php?shop=nlc-production.myshopify.com&latitude=50&longitude=-59&limit=0').then(JSON.parse)).stores;
	let names = [];
	let address = [];
	let address2 = [];
	let city = [];
	let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	let phone = [];
	let extension = [];
	for(let i = 0; i < nl.length; ++i) {
		let r;
		try {
			r = await g(`${nl[i].address} ${nl[i].address2} ${nl[i].city} newfoundland ${nl[i].postal_zip} canada`);
		} catch(e) {
			console.error(e);
			console.log(nl[i].name);
			continue;
		}
		if(!pc.test(r[0].postal_code)) {
			r[0].postal_code = nl[i].postal_zip.replace(/\s+/, '');
			//https://www.zip-codes.com/canadian/postal-code.asp?postalcode=a0p+1c0 27 aspen road is closer to here
			//https://www.zip-codes.com/canadian/postal-code.asp?postalcode=a0p+1e0 than here
			//google geocode gives only a0p :(
			//https://www.google.com/maps/place/Corner+Brook,+NL+A2H+4C8 means provided postal code is correct (polygon contains tweed)
			//google geocode gives only a2h :(
			//google maps says tweed's official address is 62 Broadway, Corner Brook, NL A2H 6H4, but a2h6h4 totally wrong plaza
			//source says 62 Broadway Avenue Corner Brook, NL A2H 4C8, Canada. wrong address right postal code
		}
		names.push(sanitize_name(nl[i].name));
		address.push(r[0].address);
		address2.push(r[0].address2);
		city.push(r[0].city);
		region.push(r[0].region);
		country.push(r[0].country);
		postal_code.push(r[0].postal_code);
		longitude.push(r[1].lng);
		latitude.push(r[1].lat);
		osrm_hint_foot.push(r[3]);
		osrm_hint_car.push(r[4]);
		timezone.push(r[2]);
		phone.push(nl[i].phone.trim().replace(/(?<!^)\(.+\)/g, '').trim());
		extension.push(nl[i].phone.match(/ext (.+)\)/)?.[1]?.trim());
	} try {
	console.log((await postgres.query(`insert into store (
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone,
		extension,
		partner,
		delivery,
		pickup,
		prepayment,
		name
	) select i.*, false, false, false, false, i.joined_name from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::citext[],
		$7::citext[],
		$8::decimal[],
		$9::decimal[],
		$10::text[],
		$11::text[],
		$12::text[],
		$13::packed_phone_number[],
		$14::citext[]
	) as i(
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone,
		extension
	) where not exists (select 1 from store_history where
		store_history.joined_name = i.joined_name and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = i.region and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = i.joined_name and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = i.region and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		names,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone,
		extension
	])).rowCount, 'newfoundland'); } catch(e) {console.log(e); console.log('newfoundland failed')}
}
async function novascotia() {
	let x = await request('https://www.mynslc.com/skins/mynslc/scripts/locations.json').then(JSON.parse);
	let address = [];
	let address2 = [];
	let city = [];
	let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	let open = [];
	let phone = [];
	for(let i = 0; i < x.length; ++i) {
		for(let j = 0; j < x[i].features.length; ++j) {
			if(x[i].features[j].featureId === 'CP') {
				let r;
				try {
					r = await g(`${x[i].addressInfo.address1} ${x[i].addressInfo.address2 ?? ''} ${x[i].addressInfo.city} nova scotia ${x[i].addressInfo.postal} canada`);
				} catch(e) {
					console.error(e);
					console.log(x[i].name);
					continue;
				}
				if(!pc.test(r[0].postal_code)) {
					x[i].postal = x[i].postal.replace(/\s+/g, '');
					if(r[0].postal_code.length === 3 && x[i].postal.length === 6) {
						r[0].postal_code = x[i].postal
					} else {
						console.error('postal_code goofup', r, x[i]);
						continue;
					}
				}
				address.push(r[0].address);
				address2.push(r[0].address2);
				city.push(r[0].city);
				region.push(r[0].region);
				country.push(r[0].country);
				postal_code.push(r[0].postal_code);
				longitude.push(r[1].lng);
				latitude.push(r[1].lat);
				osrm_hint_foot.push(r[3]);
				osrm_hint_car.push(r[4]);
				timezone.push(r[2]);
				phone.push(x[i].phone);
				let tim = [];
				for(let k = 0; k < x[i].openingHours.length; ++k) {
					let d = 0;
					switch(x[i].openingHours[k].dayOfWeek) {
						case 'Tuesday': d = 1440; break;
						case 'Wednesday': d = 2880; break;
						case 'Thursday': d = 4320; break;
						case 'Friday': d = 5760; break;
						case 'Saturday': d = 7200; break;
						case 'Sunday': d = 8640; break; 
					}
					let beg = x[i].openingHours[k].opens.split(':').map(Number);
					beg = beg[0] * 60 + beg[1];
					let end = x[i].openingHours[k].closes.split(':').map(Number);
					end = end[0] * 60 + end[1];
					let rtn = [d + beg, d + end];
					if(end < beg) {
						if(d === 8640) {
							tim.push([rtn[0], 10080]);
							rtn[0] = 0;
							rtn[1] -= 8640;
						} else {
							rtn[1] += 1440;
						}
					}
					tim.push(rtn);
				}
				open.push(JSON.stringify(tim));
				break;
			}
		}
	} try {
	console.log((await postgres.query(`insert into store (
		joined_name,
		name,
		url,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open,
		phone,
		partner,
		delivery,
		pickup,
		prepayment
	) select
		'NSLC',
		'NSLC',
		'cannabis.mynslc.com',
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		(select array_agg(int4range((x->>0)::int, (x->>1)::int)) from json_array_elements(open) as t(x)),
		phone,
		false,
		false,
		false,
		false
	from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::citext[],
		$7::decimal[],
		$8::decimal[],
		$9::text[],
		$10::text[],
		$11::text[],
		$12::json[],
		$13::packed_phone_number[]
	) as i(
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open,
		phone
	) where not exists (select 1 from store_history where
		store_history.joined_name = 'NSLC' and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = i.region and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = 'NSLC' and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = i.region and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set open = excluded.open, longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.open is distinct from excluded.open and store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open,
		phone
	])).rowCount, 'nova scotia');} catch(e) {console.log(e); console.log('novascotia failed')}
}
async function pei(browser, page) {
	if(page === undefined) {
		page = await browser.newPage();
	}
	page = await mnmalism(page);
	await page.goto('https://peicannabiscorp.com/pages/contact');
	let results = await page.$$eval('.location', e => e.map(x => x.innerText.trim().split('\n')));
	page.close();
	let address = [];
	let address2 = [];
	let city = [];
	let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	let open = [];
	let phone = [];
	const dayOfWeek = /^(Mon|Tue|Wed|Thurs|Fri|Sat|Sun)/;
	for(let i = 0; i < results.length; ++i) {
		let r;
		let adr = '';
		let first = false;
		let second = true;
		let tim = [];
		for(let j = 0; j < results[i].length; ++j) {
			if(results[i][j]) {
				if(first) {
					if(second && results[i][j].startsWith('Phone:')) {
						phone.push(results[i][j].replace('Phone:', ''));
						second = false;
					} else if(!second && dayOfWeek.test(results[i][j])) {
						let idx = results[i][j].indexOf(":");
						let days = results[i][j].substring(0, idx).split('-');
						let hours = results[i][j].substring(idx + 1).split('-');
						let beg = hours[0].trim().match(apm);
						//12am -> 0
						//1am -> 1
						//11am -> 11
						//12pm -> 12
						//1pm -> 13
						//11pm -> 23
						beg = (Number(beg[1]) % 12 + (beg[4]?.toLowerCase() === 'p' ? 12 : 0)) * 60 + Number(beg[2]);
						let end = hours[1].trim().match(apm);
						end = (Number(end[1]) % 12 + (end[4]?.toLowerCase() === 'p' ? 12 : 0)) * 60 + Number(end[2]);
						switch(days[0]) {
							case 'Mon': days[0] = 0; break;
							case 'Tue': days[0] = 1; break;
							case 'Wed': days[0] = 2; break;
							case 'Thurs': days[0] = 3; break;
							case 'Fri': days[0] = 4; break;
							case 'Sat': days[0] = 5; break;
							case 'Sun': days[0] = 6; break;
						}
						if(days[1]) {
							switch(days[1]) {
								case 'Mon': days[1] = 0; break;
								case 'Tue': days[1] = 1; break;
								case 'Wed': days[1] = 2; break;
								case 'Thurs': days[1] = 3; break;
								case 'Fri': days[1] = 4; break;
								case 'Sat': days[1] = 5; break;
								case 'Sun': days[1] = 6; break;
							}
							//sat to tue
							//5 to 1
							//5,6,0,1
							//5 to 8
							//5,6,7,8
							if(days[1] < days[0]) {
								days[1] += 7;
							}
							for(let k = days[0]; k <= days[1]; ++k) {
								let d = k % 7 * 1440;
								let rtn = [d + beg, d + end];
								if(end < beg) {
									if(d === 8640) {
										tim.push([rtn[0], 10080]);
										rtn[0] = 0;
										rtn[1] -= 8640;
									} else {
										rtn[1] += 1440;
									}
								}
								tim.push(rtn);
							}
						} else {
							let d = days[0] * 1440;
							let rtn = [d + beg, d + end];
							if(end < beg) {
								if(d === 8640) {
									tim.push([rtn[0], 10080]);
									rtn[0] = 0;
									rtn[1] -= 8640;
								} else {
									rtn[1] += 1440;
								}
							}
							tim.push(rtn);
						}
					} else if(results[i][j] === 'Store Hours') {
						second = false;
					} else if(second) {
						adr += results[i][j];
					}
				} else {
					first = true;
				}
			}
		}
		try {
			r = await g(`${adr} canada`);
		} catch(e) {
			console.error(e);
			console.log(results[i]);
			continue;
		}
		if(!pc.test(r[0].postal_code)) {
			console.error('postal_code goofup', r, results[i]);
			continue;
		}
		address.push(r[0].address);
		address2.push(r[0].address2);
		city.push(r[0].city);
		region.push(r[0].region);
		country.push(r[0].country);
		postal_code.push(r[0].postal_code);
		longitude.push(r[1].lng);
		latitude.push(r[1].lat);
		osrm_hint_foot.push(r[3]);
		osrm_hint_car.push(r[4]);
		timezone.push(r[2]);
		open.push(JSON.stringify(tim));
	} try {
	console.log((await postgres.query(`insert into store (
		joined_name,
		name,
		url,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open,
		phone,
		partner,
		delivery,
		pickup,
		prepayment
	) select
		'PEI',
		'PEI',
		'peicannabiscorp.com',
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		(select array_agg(int4range((x->>0)::int, (x->>1)::int)) from json_array_elements(open) as t(x)),
		phone,
		false,
		false,
		false,
		false
	from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::citext[],
		$7::decimal[],
		$8::decimal[],
		$9::text[],
		$10::text[],
		$11::text[],
		$12::json[],
		$13::packed_phone_number[]
	) as i(
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open,
		phone
	) where not exists (select 1 from store_history where
		store_history.joined_name = 'PEI' and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = i.region and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = 'PEI' and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = i.region and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set open = excluded.open, longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.open is distinct from excluded.open and store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open,
		phone
	])).rowCount, 'pei');} catch(e) {console.log(e); console.log('pei failed')}
}
async function yukon(browser, page) {
	if(page === undefined) {
		page = await browser.newPage();
	}
	page = await mnmalism(page);
	await page.goto('https://cannabisyukon.org/store-locations');
	let n = (await page.$$eval('.component-store-location__title', x => x.map(y => y.textContent.trim()))).map(sanitize_name);
	let names = [];
	let address = [];
	let address2 = [];
	let city = [];
	let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	let phone = [];
	let url = [];
	let results = await page.$$eval('.component-store-location__address', x => x.map(y => Array.from(y.querySelectorAll('p')).map(z => z.textContent.trim())));
	page.close();
	for(let i = 0; i < results.length; ++i) {
		let r;
		try {
			r = await g(`${results[i][0].replace('\n', ',')},canada`);
		} catch(e) {
			console.error(e);
			console.log(names[i]);
			continue;
		}
		if(!pc.test(r[0].postal_code)) {
			console.error('postal_code goofup', r, names[i], results[i]);
			continue;
		}
		names.push(n[i]);
		address.push(r[0].address);
		address2.push(r[0].address2);
		city.push(r[0].city);
		region.push(r[0].region);
		country.push(r[0].country);
		postal_code.push(r[0].postal_code);
		longitude.push(r[1].lng);
		latitude.push(r[1].lat);
		osrm_hint_foot.push(r[3]);
		osrm_hint_car.push(r[4]);
		timezone.push(r[2]);
		let p;
		let u;
		for(let j = 0; j < results[i].length; ++j) {
			if(results[i][j].startsWith('http')) {
				u = results[i][j];
			} else if(results[i][j].replace(/\D/g, '').length >= 10) {
				p = results[i][j];
			}
			if(u && p) {
				break;
			}
		}
		phone.push(p);
		url.push(u);
	} try {
	console.log((await postgres.query(`insert into store (
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone,
		url,
		partner,
		delivery,
		pickup,
		prepayment,
		name
	) select i.*, false, false, false, false, i.joined_name from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::citext[],
		$7::citext[],
		$8::decimal[],
		$9::decimal[],
		$10::text[],
		$11::text[],
		$12::text[],
		$13::packed_phone_number[],
		$14::text[]
	) as i(
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone,
		url
	) where not exists (select 1 from store_history where
		store_history.joined_name = i.joined_name and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = i.region and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = i.joined_name and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = i.region and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		names,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone,
		url
	])).rowCount, 'yukon');} catch(e) {console.log(e); console.log('yukon failed')}
}
async function nwt(browser, page) {
	if(page === undefined) {
		page = await browser.newPage();
	}
	page = await mnmalism(page);
	await page.goto('https://www.ntlcc.ca/en/where-buy-cannabis');
	let names = [];
	let address = [];
	let address2 = [];
	let city = [];
	let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	let phone = [];
	let results = await page.$$eval('#content h3', x => {
		let out = [];
		for(let i = 0; i < x.length; ++i) {
			let stuff = x[i].textContent.trim();
			if(stuff === 'Norman Wells') {
				stuff = 'Norman Wells Liquor Agency Ltd';
			}
			let store = [stuff];
			x[i] = x[i].nextElementSibling;
			if(x[i]?.tagName === 'P') {
				store = store.concat(x[i].textContent.split('\n').map(y => y.trim()));
			}
			if(stuff === 'Norman Wells Liquor Agency Ltd' && store[1] === 'Franklin Ave') {
				store[1] = '15 Franklin Ave';
			}
			out.push(store);
		}
		return out;
	});
	page.close();
	for(let i = 0; i < results.length; ++i) {
		let r;
		let adr;
		let fun;
		if(results[i][0] === 'Hay River Liquor Retailers Ltd') {
			adr = '76 Capital Dr, Hay River, X0E 1G2';
			fun = '101';
		} else {
			adr = `${results[i][0]}, ${results[i][1]}`
		}
		try {
			r = await g(`${adr}, northwest territories, canada`);
		} catch(e) {
			console.error(e);
			console.log(results[i]);
			continue;
		}
		if(!pc.test(r[0].postal_code)) {
			if(r[0].address === '10021 100 Street') {
				r[0].postal_code = 'X0E0N0';
			} else {
				console.error('postal_code goofup', r, results[i]);
				continue;
			}
		}
		names.push(sanitize_name(results[i][0]));
		address.push(r[0].address);
		address2.push(fun ?? r[0].address2);
		city.push(r[0].city);
		region.push(r[0].region);
		country.push(r[0].country);
		postal_code.push(r[0].postal_code);
		longitude.push(r[1].lng);
		latitude.push(r[1].lat);
		osrm_hint_foot.push(r[3]);
		osrm_hint_car.push(r[4]);
		timezone.push(r[2]);
		phone.push(results[i][2]);
	} try{
	console.log((await postgres.query(`insert into store (
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone,
		partner,
		delivery,
		pickup,
		prepayment,
		name
	) select i.*, false, false, false, false, i.joined_name from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::citext[],
		$7::citext[],
		$8::decimal[],
		$9::decimal[],
		$10::text[],
		$11::text[],
		$12::text[],
		$13::packed_phone_number[]
	) as i(
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone
	) where not exists (select 1 from store_history where
		store_history.joined_name = i.joined_name and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = i.region and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = i.joined_name and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = i.region and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		names,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		phone
	])).rowCount, 'nwt');} catch(e) {console.log(e); console.log('nwt failed')}
}
async function ontario() {
	let on = parse(await request('https://www.agco.ca/sites/default/files/opendata/AGCOWebSiteCannabisMapData.csv'), {skip_empty_lines: true, from_line: 2});
	let names = [];
	let address = [];
	let address2 = [];
	let city = [];
	let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	for(let i = 0; i < on.length; ++i) {
		if(on[i][3] === 'Authorized to Open') {
			let r;
			try {
				r = await g(on[i][11]);
			} catch(e) {
				console.error(e);
				console.log(on[i][6]);
				continue;
			}
			if(!pc.test(r[0].postal_code)) {
				on[i][10] = on[i][10].replace(/\s+/g, '');
				if(r[0].postal_code.length === 3 && on[i][10].length === 6) {
					r[0].postal_code = on[i][10]
				} else {
					console.error('postal_code goofup', r, on[i]);
					continue;
				}
			}
			names.push(sanitize_name(on[i][6])
				.replace(new RegExp('(?<!^)' + (on[i][7] + ' (east|west|north|south)').split(/\s+/).map((n, idx) => {
					n = n.toLowerCase().replace(/\./g, '');
					if(n === 'st') {
						n = 'st(reet)?';
					} else if(n === 'rd') {
						n = 'r(oa)?d';
					} else if(n === 'ave') {
						n = 'ave(nue)?';
					} else if(n === 'dr') {
						n = 'dr(ive)?';
					}
					return `(\\b${n}\\b)${idx !== 1 ? '?' : ''}`;
				}).join('\\s?'), 'i'), '')
				.replace(new RegExp('(?<!^)\\b' + on[i][8] + '\\b', 'gi'), '')
			);
			address.push(r[0].address);
			address2.push(r[0].address2);
			city.push(r[0].city);
			region.push(r[0].region);
			country.push(r[0].country);
			postal_code.push(r[0].postal_code);
			longitude.push(r[1].lng);
			latitude.push(r[1].lat);
			osrm_hint_foot.push(r[3]);
			osrm_hint_car.push(r[4]);
			timezone.push(r[2]);
		}
	} try {
	console.log((await postgres.query(`insert into store (
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		partner,
		delivery,
		pickup,
		prepayment,
		name
	) select i.*, false, false, false, false, i.joined_name from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::citext[],
		$7::citext[],
		$8::decimal[],
		$9::decimal[],
		$10::text[],
		$11::text[],
		$12::text[]
	) as i(
		joined_name,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone
	) where not exists (select 1 from store_history where
		store_history.joined_name = i.joined_name and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = i.region and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = i.joined_name and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = i.region and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		names,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone
	])).rowCount, 'ontario'); } catch(e) {console.log(e); console.log('ontario failed')}
}
async function quebec() {
	let results = (await request({method: 'post', url: 'https://www.sqdc.ca/api/storelocator/stores', headers: {
		'X-Requested-With': 'XMLHttpRequest',
		'Accept-Language': 'en-CA',
		'Content-Type': 'application/json'
	}, body: '{"page":1,"pageSize":900}'}).then(JSON.parse)).Stores;
	let address = [];
	let address2 = [];
	let city = [];
	//let region = [];
	let country = [];
	let postal_code = [];
	let longitude = [];
	let latitude = [];
	let osrm_hint_foot = [];
	let osrm_hint_car = [];
	let timezone = [];
	let phone = [];
	let open = [];
	for(let i = 0; i < results.length; ++i) {
		try {
			r = await g(`${results[i].Address.Line1}${results[i].Address.Line2 ? ',' + results[i].Address.Line2 : ''}, ${results[i].Address.City}, ${results[i].Address.RegionName}, ${results[i].Address.PostalCode}, canada`);
		} catch(e) {
			console.error(e);
			console.log(results[i]);
			continue;
		}
		if(!pc.test(r[0].postal_code)) {
			results[i].Address.PostalCode = results[i].Address.PostalCode.replace(/\s+/g, '');
			if(r[0].postal_code.length === 3 && results[i].Address.PostalCode.length === 6) {
				r[0].postal_code = results[i].Address.PostalCode
			} else {
				console.error('postal_code goofup', r, results[i]);
				continue;
			}
		}
		address.push(r[0].address);
		address2.push(results[i].Address.Line1 === '110-1, rue St-Germain Ouest' ? 1 : r[0].address2);
		//google maps is clearly wrong: 1 Rue Saint Germain O #110, Rimouski, Quebec G5L 4B5. it's 100-1 in the picture
		city.push(r[0].city);
		//region.push(r[0].region);
		country.push(r[0].country);
		postal_code.push(r[0].postal_code);
		longitude.push(r[1].lng);
		latitude.push(r[1].lat);
		osrm_hint_foot.push(r[3]);
		osrm_hint_car.push(r[4]);
		timezone.push(r[2]);
		phone.push(results[i].PhoneNumber);
		//todo: wait for holiday and see what OpeningHourExceptions is
		let tim = [];
		for(let j = 0; j < results[i].Schedule.OpeningHours.length; ++j) {
			//todo: does IsOpenedAllDay: true imply OpeningTimes empty array? null?
			let d = 0;
			switch(results[i].Schedule.OpeningHours[j].LocalizedDay) {
				case 'Tuesday': d = 1440; break;
				case 'Wednesday': d = 2880; break;
				case 'Thursday': d = 4320; break;
				case 'Friday': d = 5760; break;
				case 'Saturday': d = 7200; break;
				case 'Sunday': d = 8640; break; 
			}
			for(let k = 0; k < results[i].Schedule.OpeningHours[j].OpeningTimes.length; ++k) {
				let beg = results[i].Schedule.OpeningHours[j].OpeningTimes[k].BeginTime.match(apm);
				beg = (Number(beg[1]) % 12 + (beg[4]?.toLowerCase() === 'p' ? 12 : 0)) * 60 + Number(beg[2]);
				let end = results[i].Schedule.OpeningHours[j].OpeningTimes[k].EndTime.match(apm);
				end = (Number(end[1]) % 12 + (end[4]?.toLowerCase() === 'p' ? 12 : 0)) * 60 + Number(end[2]);
				let rtn = [d + beg, d + end];
				if(end < beg) {
					if(d === 8640) {
						tim.push([rtn[0], 10080]);
						rtn[0] = 0;
						rtn[1] -= 8640;
					} else {
						rtn[1] += 1440;
					}
				}
				tim.push(rtn);
			}
		}
		open.push(JSON.stringify(tim));
	} try {
	console.log((await postgres.query(`insert into store (
		joined_name,
		name,
		url,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open,
		phone,
		partner,
		delivery,
		pickup,
		prepayment
	) select
		'SQDC',
		'SQDC',
		'sqdc.ca',
		address,
		address2,
		city,
		'Quebec',
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		(select array_agg(int4range((x->>0)::int, (x->>1)::int)) from json_array_elements(open) as t(x)),
		phone,
		false,
		false,
		false,
		false
	from unnest (
		$1::citext[],
		$2::citext[],
		$3::citext[],
		$4::citext[],
		$5::citext[],
		$6::decimal[],
		$7::decimal[],
		$8::text[],
		$9::text[],
		$10::text[],
		$11::json[],
		$12::packed_phone_number[]
	) as i(
		address,
		address2,
		city,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open,
		phone
	) where not exists (select 1 from store_history where
		store_history.joined_name = 'SQDC' and
		store_history.address = i.address and
		store_history.address2 is not distinct from i.address2 and
		store_history.city = i.city and
		store_history.region = 'Quebec' and
		store_history.country = i.country and
		store_history.postal_code = i.postal_code
	) or exists (select 1 from store where
		store.joined_name = 'SQDC' and
		store.address = i.address and
		store.address2 is not distinct from i.address2 and
		store.city = i.city and
		store.region = 'Quebec' and
		store.country = i.country and
		store.postal_code = i.postal_code
	) on conflict (joined_name, address, address2, city, region, country, postal_code)
	where address is not null and address2 is not null and city is not null and postal_code is not null
	do update set open = excluded.open, longitude = excluded.longitude, latitude = excluded.latitude, timezone = excluded.timezone
	where store.open is distinct from excluded.open and store.longitude != excluded.longitude and store.latitude != excluded.latitude and store.timezone != excluded.timezone`, [
		address,
		address2,
		city,
		//region,
		country,
		postal_code,
		longitude,
		latitude,
		osrm_hint_foot,
		osrm_hint_car,
		timezone,
		open,
		phone
	])).rowCount, 'quebec');} catch(e) {console.log(e); console.log('quebec failed')}
}
(async function() {
	const [browser,] = await Promise.all([
		puppeteer.launch({
			args: ["--no-sandbox"],
			headless: true
		}),
		postgres.connect().then(() => postgres.query('begin'))
	]);
	await Promise.all([
		alberta(browser, (await browser.pages())[0]),
		britishcolumbia(),
		manitoba(browser),
		saskatchewan(browser),
		novascotia(),
		newbrunswick(browser),
		newfoundland(),
		pei(browser),
		quebec(),
		yukon(browser),
		nwt(browser),
		ontario()
	]);
	await Promise.all([
		browser.close(),
		postgres.query('commit').then(() => postgres.end())
	]);
})();