"""Add hiring fee, thumbs rating, and portfolio tables

Revision ID: db47ac1fe2a1
Revises: e1588d3f77c7
Create Date: 2026-02-03

"""
from alembic import op
import sqlalchemy as sa

revision = 'db47ac1fe2a1'
down_revision = 'e1588d3f77c7'
branch_labels = None
depends_on = None


def upgrade():
    # Part A: Add hiring fee fields to contracts
    op.add_column('contracts', sa.Column('hiring_fee_paid', sa.Boolean(), nullable=True, server_default='false'))
    op.add_column('contracts', sa.Column('hiring_fee_amount', sa.Numeric(10, 2), nullable=True, server_default='50.00'))

    # Part C: Add thumbs up/down fields to ratings
    op.add_column('ratings', sa.Column('is_positive', sa.Boolean(), nullable=True))
    op.add_column('ratings', sa.Column('rating_version', sa.Integer(), nullable=True, server_default='1'))

    # Make rating scores nullable for thumbs-only ratings
    op.alter_column('ratings', 'overall_score', existing_type=sa.Float(), nullable=True)
    op.alter_column('ratings', 'quality_score', existing_type=sa.Float(), nullable=True)

    # Part E: Create portfolio_items table
    op.create_table('portfolio_items',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=False),
        sa.Column('worker_id', sa.Integer(), nullable=False),
        sa.Column('title', sa.String(length=200), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('image_url', sa.String(length=500), nullable=False),
        sa.Column('trade_category', sa.String(length=100), nullable=True),
        sa.Column('contract_id', sa.Integer(), nullable=True),
        sa.Column('display_order', sa.Integer(), nullable=True, server_default='0'),
        sa.ForeignKeyConstraint(['worker_id'], ['users.id'], ),
        sa.ForeignKeyConstraint(['contract_id'], ['contracts.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_portfolio_worker', 'portfolio_items', ['worker_id'])


def downgrade():
    # Drop portfolio_items
    op.drop_index('idx_portfolio_worker', table_name='portfolio_items')
    op.drop_table('portfolio_items')

    # Remove thumbs fields from ratings
    op.drop_column('ratings', 'rating_version')
    op.drop_column('ratings', 'is_positive')
    op.alter_column('ratings', 'overall_score', existing_type=sa.Float(), nullable=False)

    # Remove hiring fee fields from contracts
    op.drop_column('contracts', 'hiring_fee_amount')
    op.drop_column('contracts', 'hiring_fee_paid')