"""Add derived memory curation lifecycle fields."""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "20260806_0012"
down_revision: str | Sequence[str] | None = "20260806_0011"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    with op.batch_alter_table("memory_references") as batch:
        batch.add_column(
            sa.Column(
                "curation_status",
                sa.String(length=32),
                nullable=False,
                server_default="pending",
            )
        )
        batch.add_column(sa.Column("curated_revision", sa.Integer(), nullable=True))
        batch.add_column(sa.Column("curated_at", sa.DateTime(timezone=True), nullable=True))
        batch.add_column(
            sa.Column("covered_by_json", sa.Text(), nullable=False, server_default="[]")
        )
        batch.add_column(
            sa.Column("superseded_by_json", sa.Text(), nullable=False, server_default="[]")
        )
        batch.add_column(sa.Column("latest_curation_job_id", sa.String(length=36), nullable=True))
        batch.create_index("ix_memory_references_curation_status", ["curation_status"])

    op.execute(
        """
        UPDATE curated_documents
        SET status = 'superseded'
        WHERE status = 'active'
          AND id NOT IN (
            SELECT id
            FROM (
              SELECT
                id,
                ROW_NUMBER() OVER (
                  PARTITION BY
                    scope,
                    COALESCE(project_id, ''),
                    COALESCE(usage_profile_id, ''),
                    document_type
                  ORDER BY updated_at DESC, revision DESC, id DESC
                ) AS position
              FROM curated_documents
              WHERE status = 'active'
            ) ranked
            WHERE position = 1
          )
        """
    )
    with op.batch_alter_table("curated_documents") as batch:
        batch.drop_constraint("uq_curated_document", type_="unique")
    op.execute(
        """
        CREATE UNIQUE INDEX uq_active_curated_document
        ON curated_documents (
          scope,
          COALESCE(project_id, ''),
          COALESCE(usage_profile_id, ''),
          document_type
        )
        WHERE status = 'active'
        """
    )


def downgrade() -> None:
    op.drop_index("uq_active_curated_document", table_name="curated_documents")
    with op.batch_alter_table("curated_documents") as batch:
        batch.create_unique_constraint(
            "uq_curated_document",
            ["scope", "project_id", "usage_profile_id", "document_type"],
        )
    with op.batch_alter_table("memory_references") as batch:
        batch.drop_index("ix_memory_references_curation_status")
        batch.drop_column("latest_curation_job_id")
        batch.drop_column("superseded_by_json")
        batch.drop_column("covered_by_json")
        batch.drop_column("curated_at")
        batch.drop_column("curated_revision")
        batch.drop_column("curation_status")