"""Build deterministic research projections from the official SPSS snapshot and Natural Earth land."""
from pathlib import Path
from hashlib import sha256
import gzip
import json
import sqlite3
import sys

import geopandas as gpd
import pandas as pd
import pyogrio
import pyreadstat
import shapely

ROOT = Path(__file__).resolve().parents[1]
DATA = ROOT / 'data'
EXPECTED_SHA = '39585586cb7b17cdd38cd20a1ac77fea109f7855f5ae76df19a0708a65e5bc21'
SOURCE = 'https://legacy.slavevoyages.org/documents/download/tastdb-exp-2020.sav'
NUMERIC = (
    'VOYAGEID YEARAM YEARAF DATEDEPC MAJBYIMP MAJBYIMP1 MJSELIMP MJSELIMP1 '
    'MAJBUYPT MAJSELPT MJBYPTIMP MJSLPTIMP PORTDEP NATINIMP NATIONAL FATE '
    'TSLAVESD SLAARRIV SLADVOY SLAXIMP SLAMIMP'
).split()
TEXT = ['SHIPNAME', 'CAPTAINA']
SOURCE_FIELDS = ['SOURCE'+letter for letter in 'ABCDEFGHIJKLMNOPQR']
LABEL_FIELDS = (
    'MAJBYIMP MAJBYIMP1 MJSELIMP MJSELIMP1 MAJBUYPT MAJSELPT MJBYPTIMP MJSLPTIMP '
    'PORTDEP NATINIMP NATIONAL FATE'
).split()
FIELD_LABELS = {
    'VOYAGEID': '航次编号',
    'YEARAM': '抵达年份（项目推定字段）',
    'MAJBYIMP': '主要登船区（项目推定字段）',
    'MJSELIMP1': '主要抵达大区（项目推定字段）',
    'NATINIMP': '船只旗籍分组（项目推定字段）',
    'SLAXIMP': '总登船人数（项目推定字段）',
    'SLAMIMP': '总下船人数（项目推定字段）',
    'TSLAVESD': '离开最后一个交易港时的在船人数',
    'SLAARRIV': '到达首个下船港时的人数',
    'SLADVOY': '文献记载的跨洋途中死亡人数',
}

def numeric(value):
    if pd.isna(value):
        return None
    if int(value) != value:
        raise ValueError(f'Expected integer: {value!r}')
    return int(value)

def text(value):
    return None if pd.isna(value) else (str(value).strip() or None)

def export_database(sav, db):
    digest = sha256(sav.read_bytes()).hexdigest()
    if digest != EXPECTED_SHA:
        raise ValueError(f'Unexpected snapshot SHA-256: {digest}')
    _, source_meta = pyreadstat.read_sav(sav, metadataonly=True)
    fields = NUMERIC+TEXT+SOURCE_FIELDS
    frame, meta = pyreadstat.read_sav(sav, usecols=fields, disable_datetime_conversion=True)
    assert len(frame) == meta.number_rows and frame.VOYAGEID.is_unique
    frame = frame[fields].sort_values('VOYAGEID')
    columns = ['VOYAGEID INTEGER PRIMARY KEY'] + [f'{k} INTEGER' for k in NUMERIC[1:]] + [f'{k} TEXT' for k in TEXT]
    with sqlite3.connect(db) as connection:
        connection.execute('PRAGMA foreign_keys=ON')
        connection.executescript(f'''
        CREATE TABLE voyages ({",".join(columns)});
        CREATE TABLE sources (voyage_id INTEGER REFERENCES voyages(VOYAGEID), field TEXT, citation TEXT NOT NULL,
          PRIMARY KEY(voyage_id,field)) WITHOUT ROWID;
        CREATE TABLE labels (field TEXT, code INTEGER, label TEXT NOT NULL, PRIMARY KEY(field,code)) WITHOUT ROWID;
        CREATE TABLE metadata (key TEXT PRIMARY KEY, value TEXT NOT NULL) WITHOUT ROWID;
        CREATE INDEX voyages_year ON voyages(YEARAM);
        CREATE INDEX voyages_region ON voyages(MAJBYIMP,MJSELIMP1,YEARAM);
        CREATE INDEX voyages_flag ON voyages(NATINIMP,YEARAM);
        ''')
        voyages, sources = [], []
        cut = len(NUMERIC)
        for row in frame.itertuples(index=False,name=None):
            values = [numeric(v) for v in row[:cut]] + [text(v) for v in row[cut:cut+len(TEXT)]]
            voyages.append(values)
            sources.extend((values[0],field,content) for field,value in zip(SOURCE_FIELDS,row[cut+len(TEXT):])
                           if (content := text(value)) is not None)
        placeholders = ','.join('?' for _ in columns)
        connection.executemany(f'INSERT INTO voyages VALUES ({placeholders})',voyages)
        connection.executemany('INSERT INTO sources VALUES (?,?,?)',sources)
        labels = [(field,numeric(code),label.strip()) for field in LABEL_FIELDS
                  for code,label in sorted(meta.variable_value_labels[field].items()) if label.strip()]
        connection.executemany('INSERT INTO labels VALUES (?,?,?)',labels)
        connection.executemany('INSERT INTO metadata VALUES (?,?)',[
            ('source',SOURCE),('source_sha256',digest),
            ('source_columns',str(source_meta.number_columns)),
            ('field_labels',json.dumps(FIELD_LABELS,ensure_ascii=False)),
        ])
        connection.commit()
        assert connection.execute('PRAGMA integrity_check').fetchone()[0] == 'ok'
        summary = connection.execute('''
        SELECT COUNT(*), MIN(YEARAM), MAX(YEARAM), COUNT(SLAXIMP), SUM(SLAXIMP), COUNT(SLAMIMP), SUM(SLAMIMP),
               COUNT(TSLAVESD), COUNT(SLAARRIV), COUNT(SLADVOY),
               SUM(TSLAVESD IS NOT NULL AND SLAARRIV IS NOT NULL)
        FROM voyages
        ''').fetchone()
        headings = 'count first last embark_known embark_total land_known land_total documented_embark documented_landing documented_deaths documented_pair'.split()
        result = dict(zip(headings,summary))
        result.update(source=SOURCE,sha256=digest,sourceColumns=source_meta.number_columns,selectedColumns=len(fields),sourceRefs=len(sources),
                      gdal=pyogrio.__gdal_version_string__,readstat=pyreadstat.__version__,sqlite=sqlite3.sqlite_version)
    return result

def export_geography(land_path, db):
    pyogrio.set_gdal_config_options({'OGR_CURRENT_DATE':'2020-01-01T00:00:00.000Z'})
    setup = json.loads((DATA/'regions.json').read_text())
    # Project uses WGS84; bbox contents follow Natural Earth's actual land geometry.
    land = pyogrio.read_dataframe(land_path).to_crs(4326)[['geometry']]
    frame = land.clip(shapely.box(-107,-38,50,65), keep_geom_type=True)
    pyogrio.write_dataframe(frame, DATA/'land.geojson', driver='GeoJSON',
                            layer_options={'COORDINATE_PRECISION':'4'})
    points = []
    for side in ('origin','destination'):
        for code,item in setup[side].items():
            if 'lon' in item:
                points.append({'side':side,'region_code':int(code),'name_zh':item['zh'],
                               'geometry':shapely.Point(item['lon'],item['lat'])})
    anchors = gpd.GeoDataFrame(points,crs=4326)
    package = DATA/'atlantic.gpkg'
    if package.exists():
        package.unlink()
    pyogrio.write_dataframe(frame,package,layer='natural_earth_land',driver='GPKG')
    pyogrio.write_dataframe(anchors,package,layer='regional_cartographic_anchors',driver='GPKG')
    lookup = {(row.side,row.region_code):row.geometry for row in anchors.itertuples()}
    query = '''SELECT MAJBYIMP, MJSELIMP1, COUNT(*), SUM(SLAXIMP), SUM(SLAMIMP)
               FROM voyages GROUP BY MAJBYIMP,MJSELIMP1'''
    with sqlite3.connect(db) as connection:
        links = []
        for origin,destination,voyages,embarked,disembarked in connection.execute(query):
            a,b=lookup.get(('origin',origin)),lookup.get(('destination',destination))
            if a is not None and b is not None:
                links.append(dict(origin_code=origin,destination_code=destination,
                                  voyages=voyages,embarked=embarked,disembarked=disembarked,
                                  geometry=shapely.LineString([a,b])))
    g_links = gpd.GeoDataFrame(links,crs=4326)
    pyogrio.write_dataframe(g_links,package,layer='regional_associations_not_tracks',driver='GPKG')
    inspected = pyogrio.read_dataframe(package,layer='regional_associations_not_tracks')
    assert int(inspected.voyages.sum()) == sum(row['voyages'] for row in links)
    return dict(mappedConnections=len(links),mappedVoyages=int(inspected.voyages.sum()))

def main():
    sav,land = map(Path,sys.argv[1:])
    temporary = DATA/'voyages.work.sqlite'
    if temporary.exists():
        temporary.unlink()
    result = export_database(sav,temporary)
    result.update(export_geography(land,temporary))
    with (DATA/'voyages.sqlite.gz').open('wb') as stream:
        with gzip.GzipFile(filename='',fileobj=stream,mode='wb',mtime=0) as compressed:
            compressed.write(temporary.read_bytes())
    temporary.unlink()
    (DATA/'summary.json').write_text(json.dumps(result,indent=2)+'\n')
    print(json.dumps(result,indent=2))

if __name__ == '__main__':
    main()
