"""Import datawebot saved product results into SQLite. Python 3.10+, standard library only.
Usage: python price-history.py history.sqlite first.json later.json
Prints comparable consecutive observations as CSV; imports are idempotent.
"""
import csv
import json
import sqlite3
import sys
from decimal import Decimal, InvalidOperation
from contextlib import closing


def import_result(db, result):
    insert = """INSERT INTO observations VALUES (?,?,?,?,?,?,?,?,?,?,?)
    ON CONFLICT(storefront,product_id,variant_id,retrieved_at) DO NOTHING"""
    for product in result["products"]:
        for variant in product["variants"]:
            price_record = variant.get("price")
            price = price_record.get("amount") if price_record else None
            currency = price_record.get("currency") if price_record else "unknown"
            if price is not None:
                try:
                    amount = Decimal(str(price))
                except InvalidOperation as exc:
                    raise ValueError("Invalid source price") from exc
                if not amount.is_finite() or amount < 0:
                    raise ValueError("Invalid source price")
                price = str(price)
            db.execute(insert, (
                product["provenance"]["storefront_id"], str(product["id"]), str(variant["id"]),
                product["provenance"]["retrieved_at"], currency, price,
                None if variant.get("available") is None else int(variant["available"]),
                product["url"], json.dumps(variant.get("options", []), sort_keys=True),
                str(result.get("completeness", "unknown")), json.dumps(result.get("warnings", []) + product.get("warnings", [])),
            ))


def main():
    if len(sys.argv) < 3:
        raise SystemExit(__doc__)
    with closing(sqlite3.connect(sys.argv[1])) as db, db:
        db.execute("""CREATE TABLE IF NOT EXISTS observations (
            storefront TEXT NOT NULL, product_id TEXT NOT NULL, variant_id TEXT NOT NULL,
            retrieved_at TEXT NOT NULL, currency TEXT NOT NULL, price TEXT,
            available INTEGER CHECK(available IN (0,1)), source_url TEXT NOT NULL,
            options_json TEXT NOT NULL, completeness TEXT NOT NULL, warnings_json TEXT NOT NULL,
            PRIMARY KEY(storefront,product_id,variant_id,retrieved_at)
        )""")
        for path in sys.argv[2:]:
            with open(path, encoding="utf-8") as handle:
                import_result(db, json.load(handle))
        rows = db.execute("""SELECT storefront,product_id,variant_id,retrieved_at,currency,price,
            options_json,source_url FROM observations ORDER BY storefront,product_id,variant_id,retrieved_at""")
        writer = csv.writer(sys.stdout, lineterminator="\n")
        writer.writerow(["storefront", "variant_id", "previous_at", "observed_at", "currency", "previous_price", "price", "change", "source_url"])
        previous = {}
        for store, product, variant, time, currency, price, options, url in rows:
            key = (store, product, variant, currency, options)
            prior = previous.get(key)
            if prior and price is not None and prior[1] is not None:
                writer.writerow([store, variant, prior[0], time, currency, prior[1], price, str(Decimal(price) - Decimal(prior[1])), url])
            previous[key] = (time, price)


if __name__ == "__main__":
    main()