"""add balance and transaction tables

Revision ID: b380d6b8f171
Revises: 2a40df941760
Create Date: 2026-04-27 10:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = 'b380d6b8f171'
down_revision = '2a40df941760'
branch_labels = None
depends_on = None

def upgrade():
    # 1. Tambahkan kolom balance ke tabel users jika belum ada
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    if 'balance' not in [c['name'] for c in inspector.get_columns('users')]:
        with op.batch_alter_table('users', recreate='always') as batch_op:
            batch_op.add_column(sa.Column('balance', sa.Integer(), nullable=False, server_default='0'))

    # 2. Buat tabel service_rates
    op.create_table('service_rates',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('key', sa.String(), nullable=False),
        sa.Column('value', sa.Integer(), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('key')
    )

    # 3. Buat tabel transactions (tanpa foreign key untuk SQLite compatibility)
    op.create_table('transactions',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('amount', sa.Integer(), nullable=False),
        sa.Column('service_type', sa.Enum('topup', 'transcription', 'ba', name='service_type_enum'), nullable=False),
        sa.Column('task_id', sa.Integer(), nullable=True),
        sa.Column('ba_id', sa.Integer(), nullable=True),
        sa.Column('description', sa.String(), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint('id')
    )

    # 4. Tambahkan kolom billed_amount ke berita_acara jika belum ada
    if 'billed_amount' not in [c['name'] for c in inspector.get_columns('berita_acara')]:
        with op.batch_alter_table('berita_acara', recreate='always') as batch_op:
            batch_op.add_column(sa.Column('billed_amount', sa.Integer(), nullable=True))

    # 5. Hapus foreign key jika ada (opsional) dan tambahkan index
    op.create_index(op.f('ix_transactions_user_id'), 'transactions', ['user_id'], unique=False)
    op.create_index(op.f('ix_transactions_task_id'), 'transactions', ['task_id'], unique=False)
    op.create_index(op.f('ix_transactions_ba_id'), 'transactions', ['ba_id'], unique=False)

def downgrade():
    # Hapus index
    op.drop_index(op.f('ix_transactions_ba_id'), table_name='transactions')
    op.drop_index(op.f('ix_transactions_task_id'), table_name='transactions')
    op.drop_index(op.f('ix_transactions_user_id'), table_name='transactions')

    # Hapus kolom
    with op.batch_alter_table('berita_acara', recreate='always') as batch_op:
        batch_op.drop_column('billed_amount')

    op.drop_table('transactions')
    op.drop_table('service_rates')

    with op.batch_alter_table('users', recreate='always') as batch_op:
        batch_op.drop_column('balance')