"""Add user_reputations table (Trust System Phase 3: reputation cache).

A cached rollup of each user's trust signals (average rating, review count,
positive-review percentage, completed sales/purchases) so pages never run
AVG()/COUNT() over reviews/transactions on every request. One row per user;
refreshed only when a review or completed transaction is created.

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

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    op.create_table(
        "user_reputations",
        sa.Column("user_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("average_rating", sa.Numeric(precision=3, scale=2), server_default="0", nullable=False),
        sa.Column("review_count", sa.Integer(), server_default="0", nullable=False),
        sa.Column("completed_sales", sa.Integer(), server_default="0", nullable=False),
        sa.Column("completed_purchases", sa.Integer(), server_default="0", nullable=False),
        sa.Column("positive_review_percentage", sa.Numeric(precision=5, scale=2), server_default="0", nullable=False),
        sa.Column("average_response_minutes", sa.Integer(), 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(["user_id"], ["users.id"], name="fk_user_reputations_user_id"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("user_id", name="uq_user_reputations_user_id"),
    )
    with op.batch_alter_table("user_reputations", schema=None) as batch_op:
        batch_op.create_index("ix_user_reputations_user_id", ["user_id"], unique=False)


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