#!/usr/bin/env python3
"""Record SockOdds prices in SQLite or backfill Pro history. Python 3.10+."""
import argparse
import hashlib
import json
import os
import re
import sqlite3
import sys
import tempfile
import time
from contextlib import closing
from datetime import date, datetime, timedelta, timezone
from pathlib import Path
from urllib.error import HTTPError, URLError
from urllib.parse import parse_qs, urlencode, urlsplit
from urllib.request import HTTPRedirectHandler, Request, build_opener

API = "https://api.sockodds.com/v1"
UTC = timezone.utc

SCHEMA = """
CREATE TABLE IF NOT EXISTS odds (
    source TEXT NOT NULL,
    event_id TEXT NOT NULL,
    market_id TEXT NOT NULL,
    outcome_id TEXT NOT NULL,
    bookmaker TEXT NOT NULL,
    observed_at TEXT NOT NULL,
    kind TEXT NOT NULL,
    decimal_odds REAL,
    available INTEGER NOT NULL,
    line REAL,
    PRIMARY KEY (source, event_id, market_id, outcome_id, bookmaker, observed_at, kind)
);
CREATE TABLE IF NOT EXISTS history_windows (
    event_id TEXT NOT NULL,
    starts_at TEXT NOT NULL,
    ends_at TEXT NOT NULL,
    PRIMARY KEY (event_id, starts_at, ends_at)
);
CREATE TABLE IF NOT EXISTS results (
    event_id TEXT NOT NULL,
    outcome_id TEXT NOT NULL,
    settled_at TEXT NOT NULL,
    verdict TEXT NOT NULL,
    dead_heat_factor REAL,
    PRIMARY KEY (event_id, outcome_id)
);
"""


class NoRedirects(HTTPRedirectHandler):
    def redirect_request(self, req, fp, code, msg, headers, newurl):
        return None


def utc(value):
    return value.astimezone(UTC).isoformat(timespec="microseconds").replace("+00:00", "Z")


def instant(value):
    try:
        parsed = datetime.fromisoformat(value.replace("Z", "+00:00"))
        if parsed.tzinfo is None:
            raise ValueError
        return parsed.astimezone(UTC)
    except ValueError as error:
        raise argparse.ArgumentTypeError("Use an ISO timestamp with a timezone.") from error


class SockOdds:
    def __init__(self, key):
        self.key = key
        self.opener = build_opener(NoRedirects())

    def open(self, url):
        request = Request(url, headers={"Authorization": "Bearer " + self.key})
        for attempt in range(3):
            try:
                return self.opener.open(request, timeout=30)
            except HTTPError as error:
                with error:
                    try:
                        title = json.load(error).get("title", "Request failed")
                    except (ValueError, AttributeError):
                        title = "Request failed"
                    delay = error.headers.get("Retry-After", "")
                    if (error.code == 429 and title == "Rate limit reached"
                            and delay.isdecimal() and attempt < 2):
                        time.sleep(int(delay))
                        continue
                    raise RuntimeError(f"SockOdds HTTP {error.code}: {title}") from None

    def pages(self, route, **filters):
        url = API + route + "?" + urlencode(filters)
        origin = urlsplit(url)
        visited = set()
        while url:
            if url in visited:
                raise RuntimeError("Pagination repeated a URL.")
            visited.add(url)
            with self.open(url) as response:
                page = json.load(response)
            yield page["data"]
            url = page["links"]["next"]
            if url:
                target = urlsplit(url)
                query = parse_qs(target.query, keep_blank_values=True)
                cursor = query.pop("cursor", [])
                if (target.scheme != origin.scheme or target.netloc != origin.netloc
                        or target.path != origin.path or target.fragment
                        or query != parse_qs(origin.query, keep_blank_values=True)
                        or len(cursor) != 1 or not cursor[0]):
                    raise RuntimeError("Unexpected pagination URL.")


def write_odds(db, rows):
    with db:
        db.executemany("""
            INSERT INTO odds VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT DO NOTHING
        """, rows)


def record(api, args):
    now = datetime.now(UTC)
    boundary = now + timedelta(hours=3)
    start, end = (now, boundary) if args.window == "near" else (boundary, now + timedelta(days=1))
    filters = dict(starts_after=utc(start), starts_before=utc(end), limit=25)
    if args.alternates:
        filters["alternates"] = "true"
    if args.league:
        filters["league"] = args.league
    with closing(sqlite3.connect(args.db)) as db:
        db.executescript(SCHEMA)
        count = 0
        for events in api.pages("/odds", **filters):
            rows = [
                ("poll", event["id"], market["id"], outcome["id"], price["bookmaker"],
                 utc(instant(price["observed_at"])), "snapshot", price["decimal_odds"],
                 price["available"], outcome.get("line"))
                for event in events for market in event["markets"]
                for outcome in market["outcomes"] for price in outcome["prices"]
            ]
            write_odds(db, rows)
            count += len(rows)
        print(f"{count} quotes read for {args.window}, including any already stored.")


def backfill(api, args):
    with closing(sqlite3.connect(args.db)) as db:
        db.executescript(SCHEMA)
        start = args.start
        while start < args.end:
            end = min(start + timedelta(days=1), args.end)
            window = (args.event, utc(start), utc(end))
            if not args.refresh and db.execute(
                "SELECT 1 FROM history_windows WHERE (event_id, starts_at, ends_at) = (?, ?, ?)",
                window,
            ).fetchone():
                start = end
                continue
            for changes in api.pages("/odds/history", event_id=window[0], **{
                "from": window[1], "to": window[2], "limit": 1000,
            }):
                rows = [
                    ("history", row["event"], row["market"]["id"], row["outcome"]["id"],
                     row["bookmaker"], utc(instant(row["observed_at"])), row["kind"],
                     row["decimal_odds"], row["kind"] != "withdrawn", row.get("line"))
                    for row in changes
                ]
                write_odds(db, rows)
            with db:
                db.execute("INSERT INTO history_windows VALUES (?, ?, ?) ON CONFLICT DO NOTHING", window)
            print(f"Completed {window[1]} through {window[2]}.")
            start = end


def file_hash(file):
    digest = hashlib.sha256()
    for block in iter(lambda: file.read(1024 * 1024), b""):
        digest.update(block)
    return digest.hexdigest()


def archive(api, args):
    if not re.fullmatch(r"[a-z0-9_]+", args.league):
        raise ValueError("Use a catalogue league key.")
    directory = Path(args.output) / args.league
    directory.mkdir(parents=True, exist_ok=True)
    for files in api.pages("/history/files", league=args.league, limit=100):
        for entry in files:
            day = date.fromisoformat(entry["day"])
            if entry["league"]["key"] != args.league or not args.start <= day <= args.end:
                continue
            path = directory / f"{day}.parquet"
            if path.exists():
                with path.open("rb") as existing:
                    if file_hash(existing) == entry["sha256"]:
                        continue
            url = f"{API}/history/files/{args.league}/{day}"
            with tempfile.NamedTemporaryFile(dir=directory, delete=False) as temp:
                staged = Path(temp.name)
                try:
                    with api.open(url) as response:
                        for block in iter(lambda: response.read(1024 * 1024), b""):
                            temp.write(block)
                    temp.close()
                    with staged.open("rb") as downloaded:
                        if file_hash(downloaded) != entry["sha256"]:
                            raise RuntimeError(f"Checksum mismatch for {day}. Run again for a new listing.")
                    staged.replace(path)
                finally:
                    staged.unlink(missing_ok=True)
            print(f"Saved {path}")


def results(api, args):
    with closing(sqlite3.connect(args.db)) as db:
        db.executescript(SCHEMA)
        for events in api.pages("/results", event_id=args.event):
            for result in events:
                event = result["event"]["id"]
                settled_at = utc(instant(result["settled_at"]))
                with db:
                    db.execute("BEGIN IMMEDIATE")
                    # Each result contains the currently graded outcomes. Replace it as a unit.
                    newest = db.execute(
                        "SELECT MAX(settled_at) FROM results WHERE event_id = ?", (event,)
                    ).fetchone()[0]
                    if newest and newest > settled_at:
                        continue
                    db.execute("DELETE FROM results WHERE event_id = ?", (event,))
                    db.executemany("INSERT INTO results VALUES (?, ?, ?, ?, ?)", [
                        (event, row["id"], settled_at, row["verdict"], row.get("dead_heat_factor"))
                        for row in result["outcomes"]
                    ])
                print(f"Saved {len(result['outcomes'])} verdicts for {event}.")


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--db", default="sockodds.sqlite")
    modes = parser.add_subparsers(dest="mode", required=True)
    current = modes.add_parser("record", help="Record one window of current prices on Rookie or Pro.")
    current.add_argument("window", choices=("near", "later"))
    current.add_argument("--league", help="Omit to record across your plan's leagues.")
    current.add_argument("--alternates", action="store_true", help="Include alternate lines.")
    history = modes.add_parser("backfill", help="Store recorded price changes for one event on Pro.")
    history.add_argument("event")
    history.add_argument("start", type=instant)
    history.add_argument("end", type=instant)
    history.add_argument("--refresh", action="store_true", help="Reread completed windows for late rows.")
    files = modes.add_parser("archive", help="Download Pro Parquet files for one league.")
    files.add_argument("league")
    files.add_argument("start", type=date.fromisoformat, help="First UTC day, inclusive.")
    files.add_argument("end", type=date.fromisoformat, help="Last UTC day, inclusive.")
    files.add_argument("--output", default="sockodds-history")
    settled = modes.add_parser("results", help="Store graded outcomes for one event.")
    settled.add_argument("event")
    args = parser.parse_args()
    if args.mode == "backfill" and args.start >= args.end:
        parser.error("End must be after start.")
    if args.mode == "archive" and args.start > args.end:
        parser.error("End day must be on or after start day.")
    key = os.environ.get("SOCKODDS_API_KEY")
    if not key:
        parser.error("Set SOCKODDS_API_KEY in your server environment.")
    try:
        {"record": record, "backfill": backfill, "archive": archive, "results": results}[args.mode](
            SockOdds(key), args
        )
    except (RuntimeError, URLError, OSError, ValueError, KeyError, sqlite3.Error) as error:
        sys.exit(str(error))


if __name__ == "__main__":
    main()
