"""invite cash account isolation + compare reward tracking (🅱-1)

Revision ID: invite_cash_compare_reward
Revises: feedback_review_fields
Create Date: 2026-06-23 00:00:00.000000

邀请功能 v2 账户隔离 + 比价发奖追踪:
  - coin_account 加 invite_cash_balance_cents(邀请奖励金独立余额,与金币兑换的 cash 物理隔离)
  - withdraw_order 加 source(标记提现扣哪个账户,退款退回对应账户;旧单默认 coin_cash)
  - invite_relation 加比价发奖追踪三列(好友比价多次只发一次)
  - 新增 invite_cash_transaction 表(邀请奖励金独立流水账本)

加列均 NOT NULL + server_default,存量行自动填默认值,安全。
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'invite_cash_compare_reward'
down_revision: Union[str, Sequence[str], None] = 'feedback_review_fields'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # 1. 邀请奖励金独立余额(与金币兑换的 cash_balance_cents 物理隔离;红线:两本账不可累加)
    op.add_column(
        'coin_account',
        sa.Column('invite_cash_balance_cents', sa.Integer(), nullable=False, server_default='0'),
    )
    # 2. 提现单标记账户来源:coin_cash / invite_cash,退款退回对应账户(旧单默认 coin_cash)
    op.add_column(
        'withdraw_order',
        sa.Column('source', sa.String(length=16), nullable=False, server_default='coin_cash'),
    )
    # 3. 邀请关系加比价发奖追踪(好友"下载+登录+比价一次"→ 给邀请人发奖,只发一次)
    op.add_column(
        'invite_relation',
        sa.Column('compare_reward_granted', sa.Boolean(), nullable=False, server_default=sa.false()),
    )
    op.add_column(
        'invite_relation',
        sa.Column('compare_reward_cents', sa.Integer(), nullable=False, server_default='0'),
    )
    op.add_column(
        'invite_relation',
        sa.Column('compare_rewarded_at', sa.DateTime(timezone=True), nullable=True),
    )
    # 4. 邀请奖励金独立流水表(结构同 cash_transaction;balance_after 记 invite_cash_balance_cents)
    op.create_table(
        'invite_cash_transaction',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('amount_cents', sa.Integer(), nullable=False),
        sa.Column('balance_after_cents', sa.Integer(), nullable=False),
        sa.Column('biz_type', sa.String(length=32), nullable=False),
        sa.Column('ref_id', sa.String(length=64), nullable=True),
        sa.Column('remark', sa.String(length=128), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.ForeignKeyConstraint(['user_id'], ['user.id']),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_invite_cash_transaction_user_id', 'invite_cash_transaction', ['user_id'])
    op.create_index('ix_invite_cash_transaction_created_at', 'invite_cash_transaction', ['created_at'])
    # 提现退款幂等:一个提现单只退一次(partial unique，对齐 cash_transaction 的 withdraw_refund 去重)
    op.create_index(
        'ux_invite_cash_txn_refund_ref',
        'invite_cash_transaction',
        ['ref_id'],
        unique=True,
        sqlite_where=sa.text("biz_type = 'invite_withdraw_refund' AND ref_id IS NOT NULL"),
        postgresql_where=sa.text("biz_type = 'invite_withdraw_refund' AND ref_id IS NOT NULL"),
    )


def downgrade() -> None:
    op.drop_index('ux_invite_cash_txn_refund_ref', table_name='invite_cash_transaction')
    op.drop_index('ix_invite_cash_transaction_created_at', table_name='invite_cash_transaction')
    op.drop_index('ix_invite_cash_transaction_user_id', table_name='invite_cash_transaction')
    op.drop_table('invite_cash_transaction')
    op.drop_column('invite_relation', 'compare_rewarded_at')
    op.drop_column('invite_relation', 'compare_reward_cents')
    op.drop_column('invite_relation', 'compare_reward_granted')
    op.drop_column('withdraw_order', 'source')
    op.drop_column('coin_account', 'invite_cash_balance_cents')
