"""dev001 - Add Developer Portal tables

Creates:
  - merchant_api_keys (HWDEV-101)
  - merchant_webhook_endpoints (HWDEV-101)
  - merchant_webhook_deliveries (HWDEV-101)
  - api_request_logs (HWDEV-101)

Revision ID: dev001_developer_portal
Revises: chk005_make_payer_id_nullable
Create Date: 2026-04-06
"""
from typing import Union, Sequence

import sqlalchemy as sa
from alembic import op

revision: str = 'dev001_developer_portal'
down_revision: Union[str, Sequence[str], None] = 'chk005_make_payer_id_nullable'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── merchant_api_keys ─────────────────────────────────────────────────────
    op.create_table(
        'merchant_api_keys',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('merchant_id', sa.Integer(), nullable=False),
        sa.Column('display_name', sa.String(length=100), nullable=False),
        sa.Column('key_id', sa.String(length=20), nullable=False),
        sa.Column('key_hash', sa.String(length=128), nullable=False),
        sa.Column('key_salt', sa.String(length=64), nullable=False),
        sa.Column('key_prefix', sa.String(length=12), nullable=False),
        sa.Column('environment', sa.String(length=10), nullable=False, server_default='live'),
        sa.Column('scopes', sa.JSON(), nullable=False),
        sa.Column('allowed_ips', sa.JSON(), nullable=True),
        sa.Column('last_used_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('expires_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('revoked_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('created_by_user_id', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(['merchant_id'], ['merchants.id'], name='fk_api_keys_merchant_id'),
        sa.ForeignKeyConstraint(['created_by_user_id'], ['users.id'], name='fk_api_keys_created_by_user_id'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('key_id', name='uq_api_keys_key_id'),
    )
    op.create_index('ix_merchant_api_keys_merchant_id', 'merchant_api_keys', ['merchant_id'])
    op.create_index('ix_merchant_api_keys_key_id', 'merchant_api_keys', ['key_id'])

    # ── merchant_webhook_endpoints ────────────────────────────────────────────
    op.create_table(
        'merchant_webhook_endpoints',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('merchant_id', sa.Integer(), nullable=False),
        sa.Column('display_name', sa.String(length=100), nullable=False),
        sa.Column('url', sa.String(length=500), nullable=False),
        sa.Column('events', sa.JSON(), nullable=False),
        sa.Column('signing_secret_encrypted', sa.String(length=512), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column('last_triggered_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('failure_count', sa.Integer(), nullable=False, server_default='0'),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.ForeignKeyConstraint(['merchant_id'], ['merchants.id'], name='fk_webhook_endpoints_merchant_id'),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_merchant_webhook_endpoints_merchant_id', 'merchant_webhook_endpoints', ['merchant_id'])

    # ── merchant_webhook_deliveries ───────────────────────────────────────────
    op.create_table(
        'merchant_webhook_deliveries',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('webhook_endpoint_id', sa.Integer(), nullable=False),
        sa.Column('merchant_id', sa.Integer(), nullable=False),
        sa.Column('event_type', sa.String(length=100), nullable=False),
        sa.Column('event_id', sa.String(length=128), nullable=False),
        sa.Column('payload', sa.JSON(), nullable=False),
        sa.Column('response_status', sa.Integer(), nullable=True),
        sa.Column('response_body', sa.String(length=1000), nullable=True),
        sa.Column('attempt_count', sa.Integer(), nullable=False, server_default='1'),
        sa.Column('delivered_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('failed_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.ForeignKeyConstraint(
            ['webhook_endpoint_id'], ['merchant_webhook_endpoints.id'],
            name='fk_webhook_deliveries_endpoint_id',
            ondelete='CASCADE',
        ),
        sa.ForeignKeyConstraint(['merchant_id'], ['merchants.id'], name='fk_webhook_deliveries_merchant_id'),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_merchant_webhook_deliveries_endpoint_id', 'merchant_webhook_deliveries', ['webhook_endpoint_id'])
    op.create_index('ix_merchant_webhook_deliveries_merchant_id', 'merchant_webhook_deliveries', ['merchant_id'])

    # ── api_request_logs ──────────────────────────────────────────────────────
    op.create_table(
        'api_request_logs',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('api_key_id', sa.Integer(), nullable=False),
        sa.Column('merchant_id', sa.Integer(), nullable=False),
        sa.Column('method', sa.String(length=10), nullable=False),
        sa.Column('path', sa.String(length=500), nullable=False),
        sa.Column('query_params', sa.JSON(), nullable=True),
        sa.Column('status_code', sa.Integer(), nullable=False),
        sa.Column('duration_ms', sa.Integer(), nullable=False),
        sa.Column('ip_address', sa.String(length=45), nullable=False),
        sa.Column('request_id', sa.String(length=128), nullable=False),
        sa.Column('error_message', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.ForeignKeyConstraint(['api_key_id'], ['merchant_api_keys.id'], name='fk_api_request_logs_api_key_id'),
        sa.ForeignKeyConstraint(['merchant_id'], ['merchants.id'], name='fk_api_request_logs_merchant_id'),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_api_request_logs_api_key_id', 'api_request_logs', ['api_key_id'])
    op.create_index('ix_api_request_logs_merchant_id', 'api_request_logs', ['merchant_id'])
    op.create_index('ix_api_request_logs_created_at', 'api_request_logs', ['created_at'])


def downgrade() -> None:
    op.drop_index('ix_api_request_logs_created_at', table_name='api_request_logs')
    op.drop_index('ix_api_request_logs_merchant_id', table_name='api_request_logs')
    op.drop_index('ix_api_request_logs_api_key_id', table_name='api_request_logs')
    op.drop_table('api_request_logs')

    op.drop_index('ix_merchant_webhook_deliveries_merchant_id', table_name='merchant_webhook_deliveries')
    op.drop_index('ix_merchant_webhook_deliveries_endpoint_id', table_name='merchant_webhook_deliveries')
    op.drop_table('merchant_webhook_deliveries')

    op.drop_index('ix_merchant_webhook_endpoints_merchant_id', table_name='merchant_webhook_endpoints')
    op.drop_table('merchant_webhook_endpoints')

    op.drop_index('ix_merchant_api_keys_key_id', table_name='merchant_api_keys')
    op.drop_index('ix_merchant_api_keys_merchant_id', table_name='merchant_api_keys')
    op.drop_table('merchant_api_keys')