"""tsys003 - Assign TSYS MerchantProviderConfig to all existing merchants

Revision ID: tsys003_assign_tsys_config
Revises: dev001_add_req_resp_body
Create Date: 2026-05-06
"""
from typing import Union, Sequence
import sqlalchemy as sa
from alembic import op

revision: str = 'tsys003_assign_tsys_config'
down_revision: Union[str, Sequence[str], None] = 'dev001_add_req_resp_body'
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()
    row = conn.execute(
        sa.text("SELECT id FROM payment_providers WHERE slug = 'tsys' AND deleted_at IS NULL LIMIT 1")
    ).fetchone()
    if row is None:
        return
    tsys_provider_id = row[0]
    conn.execute(sa.text("""
        INSERT INTO merchant_provider_configs
            (merchant_id, provider_id, is_active, onboarding_status, created_at)
        SELECT m.id, :tsys_id, true, 'not_started', NOW()
        FROM merchants m
        WHERE m.deleted_at IS NULL
          AND NOT EXISTS (
              SELECT 1 FROM merchant_provider_configs mpc
              WHERE mpc.merchant_id = m.id AND mpc.provider_id = :tsys_id AND mpc.deleted_at IS NULL
          )
    """), {"tsys_id": tsys_provider_id})


def downgrade() -> None:
    conn = op.get_bind()
    row = conn.execute(sa.text("SELECT id FROM payment_providers WHERE slug = 'tsys' LIMIT 1")).fetchone()
    if row is None:
        return
    tsys_provider_id = row[0]
    conn.execute(sa.text("""
        DELETE FROM merchant_provider_configs
        WHERE provider_id = :tsys_id AND onboarding_status = 'not_started'
          AND id NOT IN (SELECT DISTINCT merchant_provider_config_id FROM merchant_provider_credentials)
    """), {"tsys_id": tsys_provider_id})