"""Build the Pearl Islands extract. Python standard library only; SQLite opened read-only."""
from pathlib import Path
import collections
import csv
import hashlib
import itertools
import json
import sqlite3

HERE = Path(__file__).resolve().parent
ROOT = HERE.parents[1]
DATABASE = ROOT.parent / 'Gamebot/gamebot_lite/data/gamebot.sqlite'
OUT = ROOT / 'static/survivor/pearl-islands'


def pair_stats(councils, a, b):
    history = []
    same = shared = 0
    for council in councils:
        votes = {v['voter_id']: v['target_id'] for v in council['votes'] if v['eligible']}
        agreement = None
        if a in votes and b in votes:
            shared += 1
            agreement = votes[a] == votes[b]
            same += agreement
        history.append({'council': council['id'], 'same': same, 'shared': shared,
                        'agreement': agreement})
    return {'same': same, 'shared': shared, 'history': history}


def extract(database=DATABASE):
    with sqlite3.connect(f'file:{database}?mode=ro', uri=True) as con:
        con.row_factory = sqlite3.Row
        rows = [dict(r) for r in con.execute((HERE / 'votes.sql').read_text())]
        snapshot = con.execute("SELECT max(ingested_at) FROM vote_history WHERE version_season='US07'").fetchone()[0]
    if len(rows) != 104:
        raise ValueError('Snapshot changed: review Pearl Islands records before publication.')
    seen = set()
    players = {}
    groups = collections.defaultdict(list)
    for r in rows:
        key = (r['council'], r['round'], r['voter_id'])
        if key in seen:
            raise ValueError(f'Duplicate ballot key {key}')
        seen.add(key)
        if r['round'] != 1 or r['nullified']:
            raise ValueError('Review new revote or nullified ballot before publication.')
        players.setdefault(r['voter_id'], {'id': r['voter_id'], 'name': r['voter'], 'tribe': r['tribe']})
        groups[r['council']].append(r)
    councils = []
    for number, votes in sorted(groups.items()):
        first = votes[0]
        for field in ['episode', 'day', 'tribe', 'departure']:
            assert len({v[field] for v in votes}) == 1, (number, field)
        quit_event = any(v['event'] == 'Quit' for v in votes)
        for v in votes:
            v['eligible'] = bool(v['target_id']) and not quit_event
            v['exclusion'] = 'Quit event; not counted as ballots' if quit_event else v['event'] if not v['target_id'] else None
            assert not v['target_id'] or v['target_id'] in players
        councils.append({'id': number, 'episode': first['episode'], 'day': first['day'],
                         'tribe': first['tribe'], 'merged': first['tribe_status'] == 'Merged',
                         'departure': first['departure'], 'quit': quit_event, 'votes': votes})
    ordered = sorted(players.values(), key=lambda p: (p['tribe'], p['name']))
    pairs = {f"{a['id']}:{b['id']}": pair_stats(councils, a['id'], b['id'])
             for a, b in itertools.combinations(ordered, 2)}
    assert len(councils) == 16 and len(players) == 16
    assert sum(v['eligible'] for c in councils for v in c['votes']) == 96
    return {'season': 'Pearl Islands', 'snapshot': snapshot,
            'source_sha256': hashlib.sha256(json.dumps(rows, sort_keys=True).encode()).hexdigest(),
            'players': ordered, 'councils': councils, 'pairs': pairs,
            'counts': {'records': len(rows), 'ballots': 96, 'councils': 16, 'players': 16}}


def main():
    data = extract()
    OUT.mkdir(parents=True, exist_ok=True)
    (OUT / 'data.json').write_text(json.dumps(data, ensure_ascii=False, separators=(',', ':'))+'\n')
    rows = [v for c in data['councils'] for v in c['votes']]
    with (OUT / 'votes.csv').open('w', newline='') as f:
        writer = csv.DictWriter(f, fieldnames=list(rows[0])); writer.writeheader(); writer.writerows(rows)
    for name in ['build.py', 'votes.sql', 'test_analysis.py']:
        (OUT / name.replace('build.py', 'build_extract.py')).write_text((HERE / name).read_text())
    print(f"Pearl Islands: {len(rows)} source records, 96 eligible ballots, 120 player pairs.")

if __name__ == '__main__':
    main()