"""The backcountry-osm catalog row and import record as one SQL file (docs/backcountry/PLAN.md B2.2a): `register` runs it
on the PC's database and leaves a copy in data/export/ for the targeted ship (the VM has no geotools image, so its
Postgres container runs the same file with psql: docs/10 §4c).

    python3 -I apply_sql.py <layer.json> <version> <tier> <sha256> <scope> > backcountry-osm-apply.sql
"""
import json
import os
import re
import sys

HERE = os.path.dirname(os.path.abspath(__file__))
UPSERT = os.path.join(os.path.dirname(HERE), 'lib', 'upsert-layer.sql')
DATASET = 'backcountry-osm'
SOURCE_URL = 'https://download.geofabrik.de/north-america.html'


def literal(text):
    """A dollar-quoted SQL literal whose tag can't occur in the text."""
    tag = 'hg'
    while f'${tag}$' in text:
        tag += 'x'
    return f'${tag}${text}${tag}$'


def plain(text):
    return "'" + text.replace("'", "''") + "'"


def apply_sql(layer, version, tier, checksum, scope):
    if layer.get('key') != DATASET:
        raise ValueError(f'not the {DATASET} layer: {layer.get("key")!r}')
    if not re.fullmatch(r'OpenStreetMap \d{4}-\d{2}-\d{2}', version):
        raise ValueError(f'bad version: {version!r}')
    if tier not in ('clean', 'unverified'):
        raise ValueError(f'tier {tier!r} never ships')
    if not re.fullmatch(r'[0-9a-f]{64}', checksum):
        raise ValueError('checksum must be a sha256')
    with open(UPSERT, encoding='utf-8') as f:
        upsert = '\n'.join(line for line in f.read().splitlines() if not line.startswith('--'))
    upsert = upsert.replace(":'layer'", literal(json.dumps(layer, ensure_ascii=False)))
    return f"""-- backcountry-osm catalog row and import record ({version}), written by tools/geodata/backcountry-osm/register.
\\set ON_ERROR_STOP on
begin;
{upsert.strip()}
update geo.layers set source_version = {plain(version)}, updated_at = now() where key = '{DATASET}';
insert into geo.dataset_imports (id, dataset_key, version, source_url, license, attribution, feature_count, checksum, scope, tier, imported_at)
values (
  encode(set_bit(set_bit(overlay(uuid_send(gen_random_uuid()) placing
    substring(int8send(floor(extract(epoch from clock_timestamp()) * 1000)::bigint) from 3) from 1 for 6), 52, 1), 53, 1), 'hex')::uuid,
  '{DATASET}', {plain(version)}, '{SOURCE_URL}', 'ODbL 1.0', '© OpenStreetMap contributors', null, '{checksum}', {plain(scope)}, '{tier}', now());
commit;
"""


def main(argv):
    if len(argv) != 5:
        print(__doc__, file=sys.stderr)
        return 2
    with open(argv[0], encoding='utf-8') as f:
        layer = json.load(f)
    sys.stdout.write(apply_sql(layer, *argv[1:]))
    return 0


if __name__ == '__main__':
    sys.exit(main(sys.argv[1:]))