"""One transaction owns both the idempotency record and its data changes."""
from __future__ import annotations

from datetime import datetime
from pathlib import Path
import uuid
import sqlite3

from sqlalchemy import DateTime, ForeignKey, Integer, String, create_engine, event, select, text
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
from sqlalchemy.pool import QueuePool
from sqlalchemy.pool.base import ConnectionPoolEntry

from .contracts import ConflictError, ImportInput, ImportResult


class Base(DeclarativeBase):
    pass


class Operation(Base):
    __tablename__ = "operations"
    owner: Mapped[str] = mapped_column(String(64), primary_key=True)
    key: Mapped[str] = mapped_column(String(64), primary_key=True)
    fingerprint: Mapped[str] = mapped_column(String(64))
    operation_id: Mapped[str] = mapped_column(String(36), unique=True)
    count: Mapped[int] = mapped_column(Integer)


class Record(Base):
    __tablename__ = "records"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    owner: Mapped[str] = mapped_column(String(64))
    operation_id: Mapped[str] = mapped_column(ForeignKey("operations.operation_id"))
    source: Mapped[str] = mapped_column(String(64))
    timestamp: Mapped[datetime] = mapped_column(DateTime)
    level: Mapped[str] = mapped_column(String(8))
    message: Mapped[str] = mapped_column(String(512))


class SqlStore:
    def __init__(self, database: Path) -> None:
        # No user-controlled URL, file path or SQL is accepted by the API.
        self.engine = create_engine("sqlite:///" + str(database.resolve()),
            connect_args={"check_same_thread": False, "timeout": 0.5},
            poolclass=QueuePool, pool_size=2, max_overflow=0, pool_timeout=0.5)

        @event.listens_for(self.engine, "connect")
        def enable_constraints(connection: sqlite3.Connection, entry: ConnectionPoolEntry) -> None:
            cursor = connection.cursor()
            try:
                cursor.execute("PRAGMA foreign_keys=ON")
            finally:
                cursor.close()

        self.migrate()

    def migrate(self) -> None:
        # Run once before serving, not concurrently from every worker.
        # SQLite legacy driver transaction mode does not make this DDL chain atomic.
        # Each step is replayable; user_version advances only after its schema step.
        with self.engine.begin() as connection:
            version = connection.exec_driver_sql("PRAGMA user_version").scalar_one()
            if version not in (0, 1, 2):
                raise RuntimeError(f"unsupported schema version: {version}")
            if version == 0:
                Base.metadata.create_all(connection)
                connection.exec_driver_sql("PRAGMA user_version=1")
            if version < 2:
                connection.execute(text(
                    "CREATE INDEX IF NOT EXISTS ix_records_owner_id ON records(owner,id)"))
                connection.exec_driver_sql("PRAGMA user_version=2")

    @staticmethod
    def replay(operation: Operation, fingerprint: str) -> ImportResult:
        if operation.fingerprint != fingerprint:
            raise ConflictError("operation key conflicts with existing payload")
        return ImportResult(operation_id=operation.operation_id, count=operation.count)

    def ingest(self, owner: str, key: str, payload: ImportInput) -> ImportResult:
        fingerprint = payload.fingerprint()
        try:
            with Session(self.engine) as session, session.begin():
                existing = session.get(Operation, (owner, key))
                if existing is not None:
                    return self.replay(existing, fingerprint)
                identifier = str(uuid.uuid4())
                operation = Operation(owner=owner, key=key, fingerprint=fingerprint,
                                      operation_id=identifier, count=len(payload.records))
                session.add(operation)
                session.flush()  # Unique constraint arbitrates concurrent attempts.
                for record in payload.records:
                    # SQLite stores the UTC value without tzinfo; serialize as UTC on read.
                    session.add(Record(owner=owner, operation_id=identifier,
                        source=record.source, timestamp=record.timestamp.replace(tzinfo=None),
                        level=record.level, message=record.message))
                result = ImportResult(operation_id=identifier, count=len(payload.records))
            # The context has committed before a result is returned.
            return result
        except IntegrityError:
            # The failed transaction was rolled back before opening another session.
            with Session(self.engine) as session:
                existing = session.get(Operation, (owner, key))
                if existing is None:
                    raise
                return self.replay(existing, fingerprint)

    def records(self, owner: str, after: int, limit: int) -> list[dict[str, object]]:
        with Session(self.engine) as session:
            rows = session.scalars(select(Record).where(Record.owner == owner, Record.id > after)
                                   .order_by(Record.id).limit(limit)).all()
            return [{"id": row.id, "source": row.source,
                     "timestamp": row.timestamp.isoformat() + "Z",
                     "level": row.level, "message": row.message} for row in rows]

    def close(self) -> None:
        self.engine.dispose()
