"""Add completed_transactions table + 'completed' offer status (Trust System Phase 1).

Records the authoritative buyer/seller/listing/offer of every finalised sale so
future Trust-System features (reviews, ratings, reputation, badges) have a
trustworthy foundation. Also widens the ``offers.status`` ENUM on MySQL to accept
the terminal ``completed`` value (SQLite stores enums as VARCHAR, so no change is
needed there).

Revision ID: d9f2a4c6b8e0
Revises: f4a6c8b1d3e7
Create Date: 2026-07-04

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = "d9f2a4c6b8e0"
down_revision = "f4a6c8b1d3e7"
branch_labels = None
depends_on = None

_OFFER_STATUS_NEW = "ENUM('pending','accepted','rejected','countered','withdrawn','expired','completed')"
_OFFER_STATUS_OLD = "ENUM('pending','accepted','rejected','countered','withdrawn','expired')"


def upgrade():
    op.create_table(
        "completed_transactions",
        sa.Column("public_id", sa.CHAR(length=36), nullable=False),
        sa.Column("listing_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("offer_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("buyer_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("seller_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("completed_at", sa.DateTime(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), autoincrement=True, nullable=False),
        sa.ForeignKeyConstraint(["listing_id"], ["listings.id"], name="fk_completed_transactions_listing_id"),
        sa.ForeignKeyConstraint(["offer_id"], ["offers.id"], name="fk_completed_transactions_offer_id"),
        sa.ForeignKeyConstraint(["buyer_id"], ["users.id"], name="fk_completed_transactions_buyer_id"),
        sa.ForeignKeyConstraint(["seller_id"], ["users.id"], name="fk_completed_transactions_seller_id"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("public_id", name="uq_completed_transactions_public_id"),
        sa.UniqueConstraint("listing_id", name="uq_completed_transactions_listing_id"),
        sa.UniqueConstraint("offer_id", name="uq_completed_transactions_offer_id"),
    )
    with op.batch_alter_table("completed_transactions", schema=None) as batch_op:
        batch_op.create_index("ix_completed_transactions_buyer_id", ["buyer_id"], unique=False)
        batch_op.create_index("ix_completed_transactions_seller_id", ["seller_id"], unique=False)
        batch_op.create_index("ix_completed_transactions_completed_at", ["completed_at"], unique=False)

    bind = op.get_bind()
    if bind.dialect.name == "mysql":
        op.execute(f"ALTER TABLE offers MODIFY COLUMN status {_OFFER_STATUS_NEW} NOT NULL")


def downgrade():
    bind = op.get_bind()
    if bind.dialect.name == "mysql":
        # Move any completed offers back to a valid legacy value before narrowing.
        op.execute("UPDATE offers SET status='accepted' WHERE status='completed'")
        op.execute(f"ALTER TABLE offers MODIFY COLUMN status {_OFFER_STATUS_OLD} NOT NULL")

    op.drop_table("completed_transactions")