"""agent system: tiers, user_agents, agent_customers, agent_settlements, affiliate_clicks, order settlement fields, member commission_rate, user invite fields

Revision ID: 031_agent_system
Revises: 030_user_membership_groups
Create Date: 2026-06-15
"""

from alembic import op
import sqlalchemy as sa

revision = "031_agent_system"
down_revision = "030_user_membership_groups"
branch_labels = None
depends_on = ("029_ticket_indexes",)


def _column_names(bind: sa.engine.Connection, table_name: str) -> set[str]:
    return {column["name"] for column in sa.inspect(bind).get_columns(table_name)}


def _index_names(bind: sa.engine.Connection, table_name: str) -> set[str]:
    return {index["name"] for index in sa.inspect(bind).get_indexes(table_name)}


def _table_names(bind: sa.engine.Connection) -> set[str]:
    return set(sa.inspect(bind).get_table_names())


def upgrade() -> None:
    bind = op.get_bind()
    tables = _table_names(bind)

    # ── agent_tiers ──
    if "agent_tiers" not in tables:
        op.create_table(
            "agent_tiers",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("name", sa.String(50), nullable=False, comment="档位名称，如 云享伙伴"),
            sa.Column("code", sa.String(32), nullable=False, comment="档位标识，如 yunxiang"),
            sa.Column("cost_rate", sa.Integer(), nullable=False, comment="代理成本折扣，万分比，如 7500=75折"),
            sa.Column("min_member_level", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="申请所需最低会员等级"),
            sa.Column("min_total_spend", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="申请所需最低累计有效消费，单位分"),
            sa.Column("max_coupon_discount_rate", sa.Integer(), nullable=False, server_default=sa.text("5000"), comment="优惠券最大折扣力度，万分比"),
            sa.Column("max_monthly_coupon_count", sa.Integer(), nullable=False, server_default=sa.text("10"), comment="每月最多发券数量"),
            sa.Column("sort_order", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="排序权重"),
            sa.Column("enabled", sa.Boolean(), nullable=False, server_default=sa.text("1"), comment="是否启用"),
            sa.Column("description", sa.String(500), nullable=True, comment="档位说明"),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("code"),
        )
        op.create_index("ix_agent_tiers_code", "agent_tiers", ["code"])

    # ── user_agents ──
    if "user_agents" not in tables:
        op.create_table(
            "user_agents",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("user_id", sa.BigInteger(), nullable=False, comment="用户ID"),
            sa.Column("agent_tier_id", sa.Integer(), nullable=True, comment="当前代理档位ID"),
            sa.Column("status", sa.String(16), nullable=False, server_default=sa.text("'pending'"), comment="代理状态"),
            sa.Column("invite_code", sa.String(32), nullable=True, comment="代理邀请码"),
            sa.Column("total_sales", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="累计销售额，单位分"),
            sa.Column("total_profit", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="累计代理收益，单位分"),
            sa.Column("total_subsidy", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="累计代理补贴，单位分"),
            sa.Column("approved_at", sa.DateTime(timezone=True), nullable=True, comment="审核通过时间"),
            sa.Column("approved_by", sa.BigInteger(), nullable=True, comment="审核管理员ID"),
            sa.Column("reject_reason", sa.Text(), nullable=True, comment="拒绝原因"),
            sa.Column("remark", sa.Text(), nullable=True, comment="备注"),
            sa.PrimaryKeyConstraint("id"),
            sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["agent_tier_id"], ["agent_tiers.id"], ondelete="SET NULL"),
        )
        op.create_index("ix_user_agents_user_id", "user_agents", ["user_id"], unique=True)
        op.create_index("ix_user_agents_agent_tier_id", "user_agents", ["agent_tier_id"])
        op.create_index("ix_user_agents_status", "user_agents", ["status"])
        op.create_index("ix_user_agents_invite_code", "user_agents", ["invite_code"], unique=True)

    # ── agent_customers ──
    if "agent_customers" not in tables:
        op.create_table(
            "agent_customers",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("agent_user_id", sa.BigInteger(), nullable=False, comment="代理用户ID"),
            sa.Column("customer_user_id", sa.BigInteger(), nullable=False, comment="客户用户ID"),
            sa.Column("bind_source", sa.String(32), nullable=False, server_default=sa.text("'invite_code'"), comment="绑定来源"),
            sa.Column("bound_at", sa.DateTime(timezone=True), nullable=False, comment="绑定时间"),
            sa.Column("first_order_id", sa.Integer(), nullable=True, comment="首单订单ID"),
            sa.Column("status", sa.String(16), nullable=False, server_default=sa.text("'active'"), comment="绑定状态"),
            sa.PrimaryKeyConstraint("id"),
            sa.ForeignKeyConstraint(["agent_user_id"], ["users.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["customer_user_id"], ["users.id"], ondelete="CASCADE"),
        )
        op.create_index("ix_agent_customers_agent_user_id", "agent_customers", ["agent_user_id"])
        op.create_index("ix_agent_customers_customer_user_id", "agent_customers", ["customer_user_id"], unique=True)

    # ── agent_settlements ──
    if "agent_settlements" not in tables:
        op.create_table(
            "agent_settlements",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("order_id", sa.Integer(), nullable=False, comment="关联订单ID"),
            sa.Column("order_no", sa.String(64), nullable=False, comment="订单号快照"),
            sa.Column("settlement_type", sa.String(16), nullable=False, comment="结算类型"),
            sa.Column("user_id", sa.BigInteger(), nullable=False, comment="收益归属用户ID"),
            sa.Column("customer_user_id", sa.BigInteger(), nullable=True, comment="客户用户ID"),
            sa.Column("agent_tier_id", sa.Integer(), nullable=True, comment="代理档位ID快照"),
            sa.Column("standard_amount", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="订单标准金额，单位分"),
            sa.Column("customer_pay_amount", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="客户实付金额，单位分"),
            sa.Column("agent_cost_rate", sa.Integer(), nullable=True, comment="代理成本折扣率，万分比"),
            sa.Column("agent_cost_amount", sa.Integer(), nullable=True, comment="代理成本金额，单位分"),
            sa.Column("delta_amount", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="结算差额"),
            sa.Column("commission_rate", sa.Integer(), nullable=True, comment="会员返佣比例，万分比"),
            sa.Column("status", sa.String(24), nullable=False, server_default=sa.text("'pending'"), comment="状态"),
            sa.Column("settled_at", sa.DateTime(timezone=True), nullable=True, comment="结算时间"),
            sa.Column("remark", sa.Text(), nullable=True, comment="备注"),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index("ix_agent_settlements_order_id", "agent_settlements", ["order_id"])
        op.create_index("ix_agent_settlements_settlement_type", "agent_settlements", ["settlement_type"])
        op.create_index("ix_agent_settlements_user_id", "agent_settlements", ["user_id"])
        op.create_index("ix_agent_settlements_status", "agent_settlements", ["status"])

    # ── affiliate_clicks ──
    if "affiliate_clicks" not in tables:
        op.create_table(
            "affiliate_clicks",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("aff_code", sa.String(64), nullable=False, comment="AFF标识码/邀请码"),
            sa.Column("agent_id", sa.BigInteger(), nullable=True, comment="关联代理用户ID"),
            sa.Column("landing_url", sa.Text(), nullable=True, comment="落地页URL"),
            sa.Column("ip", sa.String(64), nullable=True, comment="访问IP"),
            sa.Column("user_agent", sa.Text(), nullable=True, comment="浏览器User-Agent"),
            sa.Column("referer", sa.Text(), nullable=True, comment="HTTP Referer"),
            sa.Column("utm_source", sa.String(128), nullable=True),
            sa.Column("utm_medium", sa.String(128), nullable=True),
            sa.Column("utm_campaign", sa.String(128), nullable=True),
            sa.Column("utm_content", sa.String(128), nullable=True),
            sa.Column("utm_term", sa.String(128), nullable=True),
            sa.Column("registered_user_id", sa.BigInteger(), nullable=True, comment="由此点击注册的用户ID"),
            sa.Column("converted_at", sa.DateTime(timezone=True), nullable=True, comment="转化时间"),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index("ix_affiliate_clicks_aff_code", "affiliate_clicks", ["aff_code"])
        op.create_index("ix_affiliate_clicks_agent_id", "affiliate_clicks", ["agent_id"])

    # ── orders: add settlement fields ──
    if "orders" in tables:
        cols = _column_names(bind, "orders")
        for col_name, col_type, col_comment in [
            ("agent_id", sa.BigInteger(), "关联代理用户ID"),
            ("agent_tier_id", sa.Integer(), "代理档位ID快照"),
            ("agent_cost_rate", sa.Integer(), "代理成本折扣率，万分比"),
            ("agent_cost_amount", sa.Integer(), "代理成本金额，单位分"),
            ("agent_delta_amount", sa.Integer(), "代理差额：正=收益，负=补贴"),
            ("member_commission_rate", sa.Integer(), "会员返佣比例快照，万分比"),
            ("member_commission_amount", sa.Integer(), "会员返佣金额快照，单位分"),
            ("settlement_status", sa.String(24), "结算状态"),
            ("aff_code", sa.String(64), "归因AFF代码"),
        ]:
            if col_name not in cols:
                op.add_column("orders", sa.Column(col_name, col_type, nullable=True, comment=col_comment))

        idxs = _index_names(bind, "orders")
        if "ix_orders_agent_id" not in idxs:
            op.create_index("ix_orders_agent_id", "orders", ["agent_id"])
        if "ix_orders_settlement_status" not in idxs:
            op.create_index("ix_orders_settlement_status", "orders", ["settlement_status"])

    # ── member_levels: add commission_rate ──
    if "member_levels" in tables:
        cols = _column_names(bind, "member_levels")
        if "commission_rate" not in cols:
            op.add_column("member_levels", sa.Column("commission_rate", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="会员返佣比例，万分比"))

    # ── users: add invite_code, bound_agent_id ──
    if "users" in tables:
        cols = _column_names(bind, "users")
        if "invite_code" not in cols:
            op.add_column("users", sa.Column("invite_code", sa.String(32), nullable=True, comment="用户自己的邀请码"))
        if "bound_agent_id" not in cols:
            op.add_column("users", sa.Column("bound_agent_id", sa.BigInteger(), nullable=True, comment="绑定的代理用户ID"))

        idxs = _index_names(bind, "users")
        if "ix_users_invite_code" not in idxs:
            op.create_index("ix_users_invite_code", "users", ["invite_code"], unique=True)
        if "ix_users_bound_agent_id" not in idxs:
            op.create_index("ix_users_bound_agent_id", "users", ["bound_agent_id"])

    # ── seed default agent tiers ──
    existing_count = bind.execute(sa.text("SELECT COUNT(*) FROM agent_tiers")).scalar()
    if existing_count == 0:
        bind.execute(
            sa.text(
                """
                INSERT INTO agent_tiers (name, code, cost_rate, min_member_level, min_total_spend, sort_order, enabled, description)
                VALUES
                ('云享伙伴', 'yunxiang', 7500, 3, 0, 1, 1, '云享伙伴：代理成本75折，具备发券和推广权限'),
                ('云策伙伴', 'yunce',   6000, 4, 0, 2, 1, '云策伙伴：代理成本60折，更多优惠券额度'),
                ('云领伙伴', 'yunling', 6500, 5, 0, 3, 1, '云领伙伴：代理成本65折，最高结算额度')
                """
            )
        )

    # ── seed default commission rates for existing member levels ──
    levels = bind.execute(sa.text("SELECT id, level FROM member_levels WHERE commission_rate = 0")).fetchall()
    rate_map = {0: 100, 1: 500, 2: 1000, 3: 1500, 4: 2000, 5: 2500}
    for row in levels:
        rid = row[0]
        lv = row[1]
        rate = rate_map.get(lv, 0)
        if rate > 0:
            bind.execute(sa.text("UPDATE member_levels SET commission_rate = :rate WHERE id = :id"), {"rate": rate, "id": rid})


def downgrade() -> None:
    bind = op.get_bind()
    tables = _table_names(bind)

    # Remove added columns from users
    if "users" in tables:
        idxs = _index_names(bind, "users")
        if "ix_users_bound_agent_id" in idxs:
            op.drop_index("ix_users_bound_agent_id", table_name="users")
        if "ix_users_invite_code" in idxs:
            op.drop_index("ix_users_invite_code", table_name="users")
        cols = _column_names(bind, "users")
        if "bound_agent_id" in cols:
            op.drop_column("users", "bound_agent_id")
        if "invite_code" in cols:
            op.drop_column("users", "invite_code")

    # Remove commission_rate from member_levels
    if "member_levels" in tables:
        cols = _column_names(bind, "member_levels")
        if "commission_rate" in cols:
            op.drop_column("member_levels", "commission_rate")

    # Remove settlement fields from orders
    if "orders" in tables:
        idxs = _index_names(bind, "orders")
        if "ix_orders_settlement_status" in idxs:
            op.drop_index("ix_orders_settlement_status", table_name="orders")
        if "ix_orders_agent_id" in idxs:
            op.drop_index("ix_orders_agent_id", table_name="orders")
        cols = _column_names(bind, "orders")
        for col_name in reversed([
            "aff_code", "settlement_status", "member_commission_amount", "member_commission_rate",
            "agent_delta_amount", "agent_cost_amount", "agent_cost_rate", "agent_tier_id", "agent_id",
        ]):
            if col_name in cols:
                op.drop_column("orders", col_name)

    # Drop new tables
    for table_name in ["affiliate_clicks", "agent_settlements", "agent_customers", "user_agents", "agent_tiers"]:
        if table_name in tables:
            op.drop_table(table_name)
