"""Учебная загрузка полных версий бронирований, Python 3 + SQLite.

python pipeline.py raw/bookings_versions.csv work/versions.sqlite
Входные суммы — целые евроценты, updated_at — UTC с точностью до секунды.
Пакет отклоняется целиком при ошибке; старая версия не заменяет новую.
"""
import argparse
import csv
from datetime import datetime
from pathlib import Path
import sqlite3

FIELDS = ("booking_id", "user_id", "room_id", "day", "status", "minutes",
          "charged_cents", "refund_cents", "updated_at")
NUMBERS = ("booking_id", "minutes", "charged_cents", "refund_cents")
SCHEMA = """
CREATE TABLE IF NOT EXISTS bookings (
 booking_id INTEGER PRIMARY KEY,
 user_id TEXT NOT NULL, room_id TEXT NOT NULL,
 day TEXT NOT NULL, status TEXT NOT NULL, minutes INTEGER NOT NULL,
 charged_cents INTEGER NOT NULL, refund_cents INTEGER NOT NULL,
 updated_at TEXT NOT NULL,
 CHECK (charged_cents >= 0 AND refund_cents >= 0
        AND refund_cents <= charged_cents));
"""
UPSERT = """
INSERT INTO bookings VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(booking_id) DO UPDATE SET
 user_id=excluded.user_id, room_id=excluded.room_id,
 day=excluded.day, status=excluded.status, minutes=excluded.minutes,
 charged_cents=excluded.charged_cents, refund_cents=excluded.refund_cents,
 updated_at=excluded.updated_at
WHERE excluded.updated_at > bookings.updated_at
"""


def read_batch(path):
    with Path(path).open(encoding="utf-8", newline="") as source:
        reader = csv.DictReader(source)
        if tuple(reader.fieldnames or ()) != FIELDS:
            raise ValueError("Ожидаются поля контракта в заданном порядке")
        return list(reader)


def validate(rows):
    checked = []
    seen = set()
    for line, original in enumerate(rows, 2):
        if set(original) != set(FIELDS):
            raise ValueError(f"Строка {line}: неверный набор полей")
        row = dict(original)
        if any(value is None or str(value).strip() == "" for value in row.values()):
            raise ValueError(f"Строка {line}: пропуск значения")
        for key in NUMBERS:
            row[key] = int(row[key])
        if row["booking_id"] <= 0 or row["booking_id"] in seen:
            raise ValueError(f"Строка {line}: неверный или повторный booking_id")
        seen.add(row["booking_id"])
        if row["room_id"] not in ("A", "B"):
            raise ValueError(f"Строка {line}: неизвестная комната")
        if row["status"] not in ("completed", "cancelled") or row["minutes"] <= 0:
            raise ValueError(f"Строка {line}: статус или длительность")
        if not 0 <= row["refund_cents"] <= row["charged_cents"]:
            raise ValueError(f"Строка {line}: неверные суммы")
        for key, fmt in (("day", "%Y-%m-%d"),
                         ("updated_at", "%Y-%m-%dT%H:%M:%SZ")):
            parsed = datetime.strptime(row[key], fmt)
            if parsed.strftime(fmt) != row[key]:
                raise ValueError(f"Строка {line}: неверный формат {key}")
        checked.append(tuple(row[key] for key in FIELDS))
    if not checked:
        raise ValueError("Пустой пакет: требуется явное правило источника")
    return checked


def load(rows, database):
    checked = validate(rows)
    db = sqlite3.connect(database)
    try:
        db.execute(SCHEMA)
        db.commit()
        with db:
            for row in checked:
                existing = db.execute(
                    "SELECT * FROM bookings WHERE booking_id=?", (row[0],)
                ).fetchone()
                if existing and existing[-1] == row[-1] and existing != row:
                    raise ValueError(f"Конфликт содержимого версии booking_id={row[0]}")
                db.execute(UPSERT, row)
        return db.execute(
            "SELECT COUNT(*), SUM(charged_cents-refund_cents) FROM bookings"
        ).fetchone()
    finally:
        db.close()


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("source")
    parser.add_argument("database")
    args = parser.parse_args()
    count, net = load(read_batch(args.source), args.database)
    print(f"Строк в базе: {count}; net_cents: {net}")