"""fc3h: backup_run table

Revision ID: 0017
Revises: 0016
Create Date: 2026-05-24

Additive. New table records every backup/restore attempt with artifact
metadata. Lifecycle tracking lives in task_run from FC-3i; this is
artifact-only (paths, sizes, tag, restore lineage).
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "0017"
down_revision: Union[str, None] = "0016"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "backup_run",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("kind", sa.String(length=16), nullable=False),
        sa.Column(
            "status", sa.String(length=16), nullable=False,
            server_default="pending",
        ),
        sa.Column("tag", sa.String(length=64), nullable=True),
        sa.Column("triggered_by", sa.String(length=32), nullable=False),
        sa.Column("started_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("finished_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("sql_path", sa.Text(), nullable=True),
        sa.Column("tar_path", sa.Text(), nullable=True),
        sa.Column("size_bytes", sa.BigInteger(), nullable=True),
        sa.Column("error", sa.Text(), nullable=True),
        sa.Column(
            "manifest", sa.JSON(), nullable=False, server_default="{}",
        ),
        sa.Column(
            "restored_from_id", sa.Integer(),
            sa.ForeignKey("backup_run.id", ondelete="SET NULL"),
            nullable=True,
        ),
    )

    # Single-column indexes (matches Mapped[...].index=True).
    op.create_index("ix_backup_run_kind", "backup_run", ["kind"])
    op.create_index("ix_backup_run_status", "backup_run", ["status"])
    op.create_index("ix_backup_run_tag", "backup_run", ["tag"])
    op.create_index("ix_backup_run_started_at", "backup_run", ["started_at"])
    op.create_index("ix_backup_run_finished_at", "backup_run", ["finished_at"])

    # Composite indexes for dashboard query patterns.
    op.create_index(
        "ix_backup_run_kind_started",
        "backup_run", ["kind", sa.text("started_at DESC")],
    )
    op.create_index(
        "ix_backup_run_status_finished",
        "backup_run", ["status", sa.text("finished_at DESC")],
    )
    # Partial index: only tagged rows participate in retention-exempt query.
    op.create_index(
        "ix_backup_run_tag_partial",
        "backup_run", ["tag"],
        postgresql_where=sa.text("tag IS NOT NULL"),
    )


def downgrade() -> None:
    op.drop_index("ix_backup_run_tag_partial", table_name="backup_run")
    op.drop_index("ix_backup_run_status_finished", table_name="backup_run")
    op.drop_index("ix_backup_run_kind_started", table_name="backup_run")
    op.drop_index("ix_backup_run_finished_at", table_name="backup_run")
    op.drop_index("ix_backup_run_started_at", table_name="backup_run")
    op.drop_index("ix_backup_run_tag", table_name="backup_run")
    op.drop_index("ix_backup_run_status", table_name="backup_run")
    op.drop_index("ix_backup_run_kind", table_name="backup_run")
    op.drop_table("backup_run")