"""Persist reviewed chunks, version snapshots and atomic publication history."""

import sqlalchemy as sa
from alembic import op

revision = "0005_chunk_indexes"
down_revision = "0004_auth_sessions"
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        "index_versions",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("status", sa.String(20), nullable=False),
        sa.Column("config", sa.JSON(), nullable=False),
        sa.Column("corpus_fingerprint", sa.String(64), nullable=False),
        sa.Column("catalog_revision", sa.Integer(), nullable=False),
        sa.Column("item_count", sa.Integer(), nullable=False),
        sa.Column("chunk_count", sa.Integer(), nullable=False),
        sa.Column("created_by", sa.Integer(), sa.ForeignKey("users.id"), nullable=False),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
        sa.Column("published_at", sa.DateTime(timezone=True)),
        sa.CheckConstraint(
            "status IN ('ready', 'published', 'superseded')", name="ck_index_version_status"
        ),
    )
    op.create_table(
        "index_state",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("active_version_id", sa.String(36), sa.ForeignKey("index_versions.id")),
        sa.Column("generation", sa.Integer(), nullable=False),
        sa.Column("catalog_revision", sa.Integer(), nullable=False),
        sa.CheckConstraint("id = 1", name="ck_index_state_singleton"),
    )
    op.execute(
        sa.text("INSERT INTO index_state (id, generation, catalog_revision) VALUES (1, 0, 0)")
    )
    op.create_table(
        "chunks",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column(
            "index_version_id", sa.String(36), sa.ForeignKey("index_versions.id"), nullable=False
        ),
        sa.Column(
            "knowledge_id", sa.Integer(), sa.ForeignKey("knowledge_items.id"), nullable=False
        ),
        sa.Column("ordinal", sa.Integer(), nullable=False),
        sa.Column("start_offset", sa.Integer(), nullable=False),
        sa.Column("end_offset", sa.Integer(), nullable=False),
        sa.Column("text", sa.Text(), nullable=False),
        sa.Column("text_sha256", sa.String(64), nullable=False),
        sa.Column("provenance", sa.JSON(), nullable=False),
        sa.UniqueConstraint(
            "index_version_id", "knowledge_id", "ordinal", name="uq_chunk_position"
        ),
        sa.CheckConstraint(
            "start_offset >= 0 AND end_offset > start_offset", name="ck_chunk_offsets"
        ),
    )
    op.create_index("ix_chunks_index_version_id", "chunks", ["index_version_id"])
    op.create_table(
        "index_publications",
        sa.Column("generation", sa.Integer(), primary_key=True),
        sa.Column("previous_version_id", sa.String(36), sa.ForeignKey("index_versions.id")),
        sa.Column("version_id", sa.String(36), sa.ForeignKey("index_versions.id"), nullable=False),
        sa.Column("actor_id", sa.Integer(), sa.ForeignKey("users.id"), nullable=False),
        sa.Column(
            "published_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
    )


def downgrade():
    op.drop_table("index_publications")
    op.drop_table("chunks")
    op.drop_table("index_state")
    op.drop_table("index_versions")