"""add_billing_and_subscription_system

Revision ID: 15e6777c1212
Revises: 48be491ecb7b
Create Date: 2026-11-18 08:36:54.870070

"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '15e6777c1212'
down_revision: Union[str, None] = '48be491ecb7b'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('coupons',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('code', sa.String(length=50), nullable=False),
    sa.Column('discount_type', sa.String(length=20), nullable=False),
    sa.Column('discount_value', sa.Integer(), nullable=False),
    sa.Column('currency', sa.String(length=10), nullable=False),
    sa.Column('min_order_amount', sa.Integer(), nullable=False),
    sa.Column('max_discount_amount', sa.Integer(), nullable=True),
    sa.Column('max_redemptions', sa.Integer(), nullable=True),
    sa.Column('times_redeemed', sa.Integer(), nullable=False),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('applicable_plans', sa.JSON(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_coupons_code'), 'coupons', ['code'], unique=True)
    op.create_table('payment_webhook_events',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('gateway_provider', sa.String(length=50), nullable=False),
    sa.Column('event_id', sa.String(length=100), nullable=False),
    sa.Column('event_type', sa.String(length=100), nullable=False),
    sa.Column('payload', sa.JSON(), nullable=False),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('error_message', sa.String(length=500), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('gateway_provider', 'event_id', name='uq_webhook_provider_event')
    )
    op.create_index(op.f('ix_payment_webhook_events_event_id'), 'payment_webhook_events', ['event_id'], unique=False)
    op.create_index(op.f('ix_payment_webhook_events_gateway_provider'), 'payment_webhook_events', ['gateway_provider'], unique=False)
    op.create_table('plans',
    sa.Column('id', sa.String(length=50), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=False),
    sa.Column('description', sa.String(length=500), nullable=False),
    sa.Column('price_amount', sa.Integer(), nullable=False),
    sa.Column('currency', sa.String(length=10), nullable=False),
    sa.Column('billing_interval', sa.String(length=20), nullable=False),
    sa.Column('trial_days', sa.Integer(), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('max_bots', sa.Integer(), nullable=False),
    sa.Column('max_broadcasts_per_month', sa.Integer(), nullable=False),
    sa.Column('max_custom_emojis', sa.Integer(), nullable=False),
    sa.Column('ai_enabled', sa.Boolean(), nullable=False),
    sa.Column('advanced_studio', sa.Boolean(), nullable=False),
    sa.Column('custom_domain', sa.Boolean(), nullable=False),
    sa.Column('priority_support', sa.Boolean(), nullable=False),
    sa.Column('webhook_rate_limit', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('payment_orders',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('order_number', sa.String(length=100), nullable=False),
    sa.Column('amount', sa.Integer(), nullable=False),
    sa.Column('currency', sa.String(length=10), nullable=False),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('provider', sa.String(length=50), nullable=False),
    sa.Column('gateway_order_id', sa.String(length=100), nullable=True),
    sa.Column('gateway_payment_id', sa.String(length=100), nullable=True),
    sa.Column('gateway_signature', sa.String(length=255), nullable=True),
    sa.Column('idempotency_key', sa.String(length=100), nullable=False),
    sa.Column('purpose', sa.String(length=50), nullable=False),
    sa.Column('plan_id', sa.String(length=50), nullable=True),
    sa.Column('metadata_json', sa.JSON(), nullable=True),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_payment_orders_gateway_order_id'), 'payment_orders', ['gateway_order_id'], unique=False)
    op.create_index(op.f('ix_payment_orders_gateway_payment_id'), 'payment_orders', ['gateway_payment_id'], unique=False)
    op.create_index(op.f('ix_payment_orders_idempotency_key'), 'payment_orders', ['idempotency_key'], unique=True)
    op.create_index(op.f('ix_payment_orders_order_number'), 'payment_orders', ['order_number'], unique=True)
    op.create_index(op.f('ix_payment_orders_status'), 'payment_orders', ['status'], unique=False)
    op.create_index(op.f('ix_payment_orders_tenant_id'), 'payment_orders', ['tenant_id'], unique=False)
    op.create_table('referrals',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('referrer_tenant_id', sa.UUID(), nullable=False),
    sa.Column('referred_tenant_id', sa.UUID(), nullable=False),
    sa.Column('referral_code', sa.String(length=50), nullable=False),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('reward_amount', sa.Integer(), nullable=False),
    sa.Column('rewarded_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['referred_tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['referrer_tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('referred_tenant_id')
    )
    op.create_index(op.f('ix_referrals_referral_code'), 'referrals', ['referral_code'], unique=False)
    op.create_index(op.f('ix_referrals_referrer_tenant_id'), 'referrals', ['referrer_tenant_id'], unique=False)
    op.create_table('subscriptions',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('plan_id', sa.String(length=50), nullable=False),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('current_period_start', sa.DateTime(timezone=True), nullable=False),
    sa.Column('current_period_end', sa.DateTime(timezone=True), nullable=False),
    sa.Column('cancel_at_period_end', sa.Boolean(), nullable=False),
    sa.Column('canceled_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('trial_start', sa.DateTime(timezone=True), nullable=True),
    sa.Column('trial_end', sa.DateTime(timezone=True), nullable=True),
    sa.Column('grace_period_ends_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('gateway_provider', sa.String(length=50), nullable=False),
    sa.Column('gateway_subscription_id', sa.String(length=100), nullable=True),
    sa.Column('gateway_customer_id', sa.String(length=100), nullable=True),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['plan_id'], ['plans.id'], ondelete='RESTRICT'),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_subscriptions_current_period_end'), 'subscriptions', ['current_period_end'], unique=False)
    op.create_index(op.f('ix_subscriptions_plan_id'), 'subscriptions', ['plan_id'], unique=False)
    op.create_index(op.f('ix_subscriptions_status'), 'subscriptions', ['status'], unique=False)
    op.create_index(op.f('ix_subscriptions_tenant_id'), 'subscriptions', ['tenant_id'], unique=False)
    op.create_table('tenant_usages',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('period_start', sa.DateTime(timezone=True), nullable=False),
    sa.Column('period_end', sa.DateTime(timezone=True), nullable=False),
    sa.Column('active_bots_count', sa.Integer(), nullable=False),
    sa.Column('broadcasts_sent_count', sa.Integer(), nullable=False),
    sa.Column('custom_emojis_count', sa.Integer(), nullable=False),
    sa.Column('ai_tokens_used', sa.Integer(), nullable=False),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_tenant_usages_tenant_id'), 'tenant_usages', ['tenant_id'], unique=False)
    op.create_table('wallets',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('balance', sa.Integer(), nullable=False),
    sa.Column('currency', sa.String(length=10), nullable=False),
    sa.Column('is_frozen', sa.Boolean(), nullable=False),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('tenant_id', name='uq_tenant_wallet')
    )
    op.create_index(op.f('ix_wallets_tenant_id'), 'wallets', ['tenant_id'], unique=False)
    op.create_table('coupon_redemptions',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('coupon_id', sa.UUID(), nullable=False),
    sa.Column('order_id', sa.UUID(), nullable=False),
    sa.Column('discount_applied', sa.Integer(), nullable=False),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['coupon_id'], ['coupons.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['order_id'], ['payment_orders.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_coupon_redemptions_coupon_id'), 'coupon_redemptions', ['coupon_id'], unique=False)
    op.create_index(op.f('ix_coupon_redemptions_order_id'), 'coupon_redemptions', ['order_id'], unique=False)
    op.create_index('ix_coupon_redemptions_tenant_id', 'coupon_redemptions', ['tenant_id'], unique=False)
    op.create_table('invoices',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('invoice_number', sa.String(length=100), nullable=False),
    sa.Column('subscription_id', sa.UUID(), nullable=True),
    sa.Column('order_id', sa.UUID(), nullable=True),
    sa.Column('amount_due', sa.Integer(), nullable=False),
    sa.Column('amount_paid', sa.Integer(), nullable=False),
    sa.Column('amount_remaining', sa.Integer(), nullable=False),
    sa.Column('currency', sa.String(length=10), nullable=False),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('due_date', sa.DateTime(timezone=True), nullable=False),
    sa.Column('paid_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('billing_email', sa.String(length=255), nullable=False),
    sa.Column('billing_name', sa.String(length=255), nullable=False),
    sa.Column('billing_address', sa.String(length=500), nullable=True),
    sa.Column('tax_id', sa.String(length=100), nullable=True),
    sa.Column('items', sa.JSON(), nullable=False),
    sa.Column('subtotal', sa.Integer(), nullable=False),
    sa.Column('tax_amount', sa.Integer(), nullable=False),
    sa.Column('discount_amount', sa.Integer(), nullable=False),
    sa.Column('pdf_url', sa.String(length=500), nullable=True),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['order_id'], ['payment_orders.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['subscription_id'], ['subscriptions.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_invoices_invoice_number'), 'invoices', ['invoice_number'], unique=True)
    op.create_index(op.f('ix_invoices_status'), 'invoices', ['status'], unique=False)
    op.create_index(op.f('ix_invoices_tenant_id'), 'invoices', ['tenant_id'], unique=False)
    op.create_table('wallet_transactions',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('wallet_id', sa.UUID(), nullable=False),
    sa.Column('transaction_type', sa.String(length=50), nullable=False),
    sa.Column('amount', sa.Integer(), nullable=False),
    sa.Column('balance_after', sa.Integer(), nullable=False),
    sa.Column('reference_type', sa.String(length=50), nullable=False),
    sa.Column('reference_id', sa.String(length=100), nullable=True),
    sa.Column('description', sa.String(length=500), nullable=False),
    sa.Column('checksum', sa.String(length=64), nullable=False),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['wallet_id'], ['wallets.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_wallet_transactions_tenant_id'), 'wallet_transactions', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_wallet_transactions_wallet_id'), 'wallet_transactions', ['wallet_id'], unique=False)
    op.create_table('refunds',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('order_id', sa.UUID(), nullable=False),
    sa.Column('invoice_id', sa.UUID(), nullable=True),
    sa.Column('amount', sa.Integer(), nullable=False),
    sa.Column('currency', sa.String(length=10), nullable=False),
    sa.Column('reason', sa.String(length=500), nullable=False),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('gateway_refund_id', sa.String(length=100), nullable=True),
    sa.Column('provider', sa.String(length=50), nullable=False),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['invoice_id'], ['invoices.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['order_id'], ['payment_orders.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_refunds_order_id'), 'refunds', ['order_id'], unique=False)
    op.create_index(op.f('ix_refunds_status'), 'refunds', ['status'], unique=False)
    op.create_index('ix_refunds_tenant_id', 'refunds', ['tenant_id'], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('ix_refunds_tenant_id', table_name='refunds')
    op.drop_index(op.f('ix_refunds_status'), table_name='refunds')
    op.drop_index(op.f('ix_refunds_order_id'), table_name='refunds')
    op.drop_table('refunds')
    op.drop_index(op.f('ix_wallet_transactions_wallet_id'), table_name='wallet_transactions')
    op.drop_index(op.f('ix_wallet_transactions_tenant_id'), table_name='wallet_transactions')
    op.drop_table('wallet_transactions')
    op.drop_index(op.f('ix_invoices_tenant_id'), table_name='invoices')
    op.drop_index(op.f('ix_invoices_status'), table_name='invoices')
    op.drop_index(op.f('ix_invoices_invoice_number'), table_name='invoices')
    op.drop_table('invoices')
    op.drop_index('ix_coupon_redemptions_tenant_id', table_name='coupon_redemptions')
    op.drop_index(op.f('ix_coupon_redemptions_order_id'), table_name='coupon_redemptions')
    op.drop_index(op.f('ix_coupon_redemptions_coupon_id'), table_name='coupon_redemptions')
    op.drop_table('coupon_redemptions')
    op.drop_index(op.f('ix_wallets_tenant_id'), table_name='wallets')
    op.drop_table('wallets')
    op.drop_index(op.f('ix_tenant_usages_tenant_id'), table_name='tenant_usages')
    op.drop_table('tenant_usages')
    op.drop_index(op.f('ix_subscriptions_tenant_id'), table_name='subscriptions')
    op.drop_index(op.f('ix_subscriptions_status'), table_name='subscriptions')
    op.drop_index(op.f('ix_subscriptions_plan_id'), table_name='subscriptions')
    op.drop_index(op.f('ix_subscriptions_current_period_end'), table_name='subscriptions')
    op.drop_table('subscriptions')
    op.drop_index(op.f('ix_referrals_referrer_tenant_id'), table_name='referrals')
    op.drop_index(op.f('ix_referrals_referral_code'), table_name='referrals')
    op.drop_table('referrals')
    op.drop_index(op.f('ix_payment_orders_tenant_id'), table_name='payment_orders')
    op.drop_index(op.f('ix_payment_orders_status'), table_name='payment_orders')
    op.drop_index(op.f('ix_payment_orders_order_number'), table_name='payment_orders')
    op.drop_index(op.f('ix_payment_orders_idempotency_key'), table_name='payment_orders')
    op.drop_index(op.f('ix_payment_orders_gateway_payment_id'), table_name='payment_orders')
    op.drop_index(op.f('ix_payment_orders_gateway_order_id'), table_name='payment_orders')
    op.drop_table('payment_orders')
    op.drop_table('plans')
    op.drop_index(op.f('ix_payment_webhook_events_gateway_provider'), table_name='payment_webhook_events')
    op.drop_index(op.f('ix_payment_webhook_events_event_id'), table_name='payment_webhook_events')
    op.drop_table('payment_webhook_events')
    op.drop_index(op.f('ix_coupons_code'), table_name='coupons')
    op.drop_table('coupons')
    # ### end Alembic commands ###
