"""affiliate referrals

Revision ID: 000000000010
Revises: 000000000009
Create Date: 2026-02-13

Creates affiliates, referral_clicks, referral_attributions,
affiliate_commissions, affiliate_payouts, and app_affiliate_config tables
for the multi-level affiliate/referral system.
"""

from alembic import op

revision = "000000000010"
down_revision = "000000000009"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # -----------------------------------------------------------------------
    # affiliates table
    # -----------------------------------------------------------------------
    op.execute("""
        CREATE TABLE IF NOT EXISTS affiliates (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            affiliate_type VARCHAR(10) NOT NULL,
            user_id UUID REFERENCES users(id) ON DELETE CASCADE,
            agent_id UUID REFERENCES agents(id) ON DELETE CASCADE,
            slug VARCHAR(50) NOT NULL UNIQUE,
            referred_by_affiliate_id UUID REFERENCES affiliates(id) ON DELETE SET NULL,
            referral_depth INTEGER NOT NULL DEFAULT 0,
            is_active BOOLEAN NOT NULL DEFAULT true,
            payout_wallet_address VARCHAR(200),
            payout_chain VARCHAR(50),
            credit_balance_cents BIGINT NOT NULL DEFAULT 0,
            lifetime_earned_cents BIGINT NOT NULL DEFAULT 0,
            lifetime_paid_out_cents BIGINT NOT NULL DEFAULT 0,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            CONSTRAINT ck_affiliates_type CHECK (affiliate_type IN ('user', 'agent')),
            CONSTRAINT ck_affiliates_exactly_one_fk CHECK (
                (affiliate_type = 'user' AND user_id IS NOT NULL AND agent_id IS NULL)
                OR (affiliate_type = 'agent' AND agent_id IS NOT NULL AND user_id IS NULL)
            )
        );
    """)

    # One affiliate per user
    op.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS uq_affiliates_user
        ON affiliates (user_id)
        WHERE user_id IS NOT NULL;
    """)

    # One affiliate per agent
    op.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS uq_affiliates_agent
        ON affiliates (agent_id)
        WHERE agent_id IS NOT NULL;
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_affiliates_referred_by
        ON affiliates (referred_by_affiliate_id);
    """)

    # -----------------------------------------------------------------------
    # referral_clicks table
    # -----------------------------------------------------------------------
    op.execute("""
        CREATE TABLE IF NOT EXISTS referral_clicks (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            affiliate_id UUID NOT NULL REFERENCES affiliates(id) ON DELETE CASCADE,
            application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
            clicked_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            ip_hash VARCHAR(64) NOT NULL,
            user_agent VARCHAR(500),
            expires_at TIMESTAMPTZ NOT NULL,
            converted_at TIMESTAMPTZ,
            converted_entity_type VARCHAR(10),
            converted_entity_id UUID
        );
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_referral_clicks_affiliate
        ON referral_clicks (affiliate_id);
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_referral_clicks_app
        ON referral_clicks (application_id);
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_referral_clicks_ip_slug
        ON referral_clicks (ip_hash, affiliate_id);
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_referral_clicks_expires
        ON referral_clicks (expires_at)
        WHERE converted_at IS NULL;
    """)

    # -----------------------------------------------------------------------
    # referral_attributions table
    # -----------------------------------------------------------------------
    op.execute("""
        CREATE TABLE IF NOT EXISTS referral_attributions (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            referred_entity_type VARCHAR(10) NOT NULL,
            referred_entity_id UUID NOT NULL,
            affiliate_id UUID NOT NULL REFERENCES affiliates(id) ON DELETE CASCADE,
            referral_click_id UUID REFERENCES referral_clicks(id) ON DELETE SET NULL,
            application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
            attributed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            CONSTRAINT ck_referral_attributions_type CHECK (referred_entity_type IN ('user', 'agent')),
            CONSTRAINT uq_referral_attributions_entity UNIQUE (referred_entity_type, referred_entity_id)
        );
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_referral_attributions_affiliate
        ON referral_attributions (affiliate_id);
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_referral_attributions_entity
        ON referral_attributions (referred_entity_type, referred_entity_id);
    """)

    # -----------------------------------------------------------------------
    # affiliate_commissions table
    # -----------------------------------------------------------------------
    op.execute("""
        CREATE TABLE IF NOT EXISTS affiliate_commissions (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            affiliate_id UUID NOT NULL REFERENCES affiliates(id) ON DELETE CASCADE,
            source_entity_type VARCHAR(10) NOT NULL,
            source_entity_id UUID NOT NULL,
            payment_event_type VARCHAR(50) NOT NULL,
            payment_amount_cents BIGINT NOT NULL,
            commission_level INTEGER NOT NULL,
            commission_rate_bps INTEGER NOT NULL,
            commission_amount_cents BIGINT NOT NULL,
            application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
            credited_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            payment_reference VARCHAR(500) NOT NULL,
            CONSTRAINT ck_affiliate_commissions_level CHECK (commission_level IN (1, 2, 3)),
            CONSTRAINT uq_affiliate_commissions_idempotent UNIQUE (payment_reference, affiliate_id, commission_level)
        );
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_affiliate_commissions_affiliate
        ON affiliate_commissions (affiliate_id);
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_affiliate_commissions_app
        ON affiliate_commissions (application_id);
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_affiliate_commissions_credited_at
        ON affiliate_commissions (credited_at);
    """)

    # -----------------------------------------------------------------------
    # affiliate_payouts table
    # -----------------------------------------------------------------------
    op.execute("""
        CREATE TABLE IF NOT EXISTS affiliate_payouts (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            affiliate_id UUID NOT NULL REFERENCES affiliates(id) ON DELETE CASCADE,
            amount_cents BIGINT NOT NULL,
            payout_method VARCHAR(20) NOT NULL DEFAULT 'usdc_onchain',
            wallet_address VARCHAR(200) NOT NULL,
            chain VARCHAR(50) NOT NULL,
            tx_hash VARCHAR(200),
            status VARCHAR(20) NOT NULL DEFAULT 'requested',
            requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            approved_at TIMESTAMPTZ,
            completed_at TIMESTAMPTZ,
            approved_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
            denial_reason VARCHAR(500),
            failure_reason VARCHAR(500),
            CONSTRAINT ck_affiliate_payouts_status CHECK (
                status IN ('requested', 'approved', 'completed', 'denied', 'failed')
            )
        );
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_affiliate_payouts_affiliate
        ON affiliate_payouts (affiliate_id);
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_affiliate_payouts_status
        ON affiliate_payouts (status)
        WHERE status IN ('requested', 'approved');
    """)

    # -----------------------------------------------------------------------
    # app_affiliate_config table
    # -----------------------------------------------------------------------
    op.execute("""
        CREATE TABLE IF NOT EXISTS app_affiliate_config (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            application_id UUID NOT NULL UNIQUE REFERENCES applications(id) ON DELETE CASCADE,
            enabled BOOLEAN NOT NULL DEFAULT true,
            l1_rate_bps INTEGER NOT NULL DEFAULT 1000,
            l2_rate_bps INTEGER NOT NULL DEFAULT 500,
            l3_rate_bps INTEGER NOT NULL DEFAULT 200,
            attribution_window_days INTEGER NOT NULL DEFAULT 30,
            min_payout_cents BIGINT NOT NULL DEFAULT 2500,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            CONSTRAINT ck_app_affiliate_config_l1 CHECK (l1_rate_bps >= 0 AND l1_rate_bps <= 5000),
            CONSTRAINT ck_app_affiliate_config_l2 CHECK (l2_rate_bps >= 0 AND l2_rate_bps <= 5000),
            CONSTRAINT ck_app_affiliate_config_l3 CHECK (l3_rate_bps >= 0 AND l3_rate_bps <= 5000),
            CONSTRAINT ck_app_affiliate_config_total_rate CHECK (l1_rate_bps + l2_rate_bps + l3_rate_bps <= 5000)
        );
    """)


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS app_affiliate_config CASCADE;")
    op.execute("DROP TABLE IF EXISTS affiliate_payouts CASCADE;")
    op.execute("DROP TABLE IF EXISTS affiliate_commissions CASCADE;")
    op.execute("DROP TABLE IF EXISTS referral_attributions CASCADE;")
    op.execute("DROP TABLE IF EXISTS referral_clicks CASCADE;")
    op.execute("DROP TABLE IF EXISTS affiliates CASCADE;")