"""Add reviews table (Trust System Phase 2: reviews & ratings).

One rating (1-5) + optional comment per participant per completed transaction.
Anchored to completed_transactions so a review can only exist for a real,
finalised sale. Integrity is enforced by the database: unique(transaction_id,
reviewer_id), a rating range check, and a reviewer<>reviewee check.

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

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    op.create_table(
        "reviews",
        sa.Column("public_id", sa.CHAR(length=36), nullable=False),
        sa.Column("transaction_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("reviewer_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("reviewee_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("rating", sa.SmallInteger(), nullable=False),
        sa.Column("comment", sa.String(length=1000), nullable=True),
        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(["transaction_id"], ["completed_transactions.id"], name="fk_reviews_transaction_id"),
        sa.ForeignKeyConstraint(["reviewer_id"], ["users.id"], name="fk_reviews_reviewer_id"),
        sa.ForeignKeyConstraint(["reviewee_id"], ["users.id"], name="fk_reviews_reviewee_id"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("public_id", name="uq_reviews_public_id"),
        sa.UniqueConstraint("transaction_id", "reviewer_id", name="uq_reviews_transaction_reviewer"),
        sa.CheckConstraint("rating >= 1 AND rating <= 5", name="ck_reviews_rating_range"),
        sa.CheckConstraint("reviewer_id <> reviewee_id", name="ck_reviews_distinct_users"),
    )
    with op.batch_alter_table("reviews", schema=None) as batch_op:
        batch_op.create_index("ix_reviews_reviewee_id", ["reviewee_id"], unique=False)
        batch_op.create_index("ix_reviews_transaction_id", ["transaction_id"], unique=False)


def downgrade():
    op.drop_table("reviews")