"""add notification_templates and hpp_retry_tokens tables

Revision ID: f2a3b4c5d6e7
Revises: e1f2a3b4c5d6
Create Date: 2026-03-10 00:01:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy import inspect as sa_inspect


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


def upgrade() -> None:
    conn = op.get_bind()
    existing_tables = sa_inspect(conn).get_table_names()

    # ── 1. notification_templates ────────────────────────────────────────────
    # Guard: table may already exist if created by a parallel branch.
    if 'notification_templates' not in existing_tables:
        op.create_table(
            'notification_templates',
            sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
            sa.Column('merchant_id', sa.Integer(), nullable=True),
            sa.Column('template_key', sa.String(length=100), nullable=False),
            sa.Column('channel', sa.String(length=10), nullable=False),
            sa.Column('subject', sa.String(length=255), nullable=True),
            sa.Column('body_html', sa.Text(), nullable=True),
            sa.Column('body_text', sa.Text(), nullable=True),
            sa.Column('is_active', sa.Boolean(), nullable=False, server_default=sa.text('true')),
            sa.Column(
                'created_at',
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
            sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
            sa.ForeignKeyConstraint(
                ['merchant_id'], ['merchants.id'],
                name='fk_notification_templates_merchant_id',
            ),
            sa.PrimaryKeyConstraint('id'),
            sa.UniqueConstraint(
                'merchant_id', 'template_key', 'channel',
                name='uq_notification_template_merchant_key_channel',
            ),
        )
        op.create_index(
            op.f('ix_notification_templates_id'),
            'notification_templates',
            ['id'],
            unique=False,
        )
        op.create_index(
            'ix_notification_templates_key_channel',
            'notification_templates',
            ['template_key', 'channel'],
            unique=False,
        )
        op.create_index(
            'ix_notification_templates_merchant_key_channel',
            'notification_templates',
            ['merchant_id', 'template_key', 'channel'],
            unique=False,
        )

        # ── 2. Seed default notification templates ───────────────────────────
        op.execute(
            """
            INSERT INTO notification_templates
                (merchant_id, template_key, channel, subject, body_html, body_text, is_active, created_at)
            VALUES
            (
                NULL,
                'hpp_payment_request',
                'email',
                '{{merchant_name}} has sent you a payment request for {{currency}}{{amount}}',
                '<h1>Payment Request from {{merchant_name}}</h1><p>Amount due: {{currency}}{{amount}}</p><p>Due date: {{due_date}}</p><p><a href="{{hpp_link}}">Pay Now</a></p>',
                'You have a payment request from {{merchant_name}} for {{currency}}{{amount}} due on {{due_date}}. Pay here: {{hpp_link}}',
                true,
                NOW()
            ),
            (
                NULL,
                'hpp_payment_request',
                'sms',
                NULL,
                NULL,
                '{{merchant_name}} has sent you a payment request for {{currency}}{{amount}}. Pay here: {{hpp_link}}',
                true,
                NOW()
            ),
            (
                NULL,
                'payment_receipt',
                'email',
                'Payment receipt from {{merchant_name}}',
                '<h1>Payment Received</h1><p>Thank you for your payment of {{currency}}{{amount}} to {{merchant_name}}.</p><p>Transaction ID: {{transaction_id}}</p>',
                'Thank you for your payment of {{currency}}{{amount}} to {{merchant_name}}. Transaction ID: {{transaction_id}}',
                true,
                NOW()
            ),
            (
                NULL,
                'payment_receipt',
                'sms',
                NULL,
                NULL,
                'Payment of {{currency}}{{amount}} to {{merchant_name}} confirmed. Transaction ID: {{transaction_id}}',
                true,
                NOW()
            ),
            (
                NULL,
                'customer_verify',
                'email',
                'Verify your email address',
                '<h1>Email Verification</h1><p>Click the link below to verify your email address.</p><p><a href="{{verify_link}}">Verify Email</a></p>',
                'Please verify your email address by visiting: {{verify_link}}',
                true,
                NOW()
            ),
            (
                NULL,
                'payment_failed_retry',
                'email',
                'Action required: Payment of {{currency}}{{amount}} failed',
                '<h1>Payment Failed</h1><p>Your scheduled payment of {{currency}}{{amount}} to {{merchant_name}} failed on {{due_date}}.</p><p><a href="{{retry_link}}">Update Payment Method &amp; Retry</a></p>',
                'Your payment of {{currency}}{{amount}} to {{merchant_name}} failed. Update your payment method and retry: {{retry_link}}',
                true,
                NOW()
            ),
            (
                NULL,
                'payment_failed_retry',
                'sms',
                NULL,
                NULL,
                'Your payment of {{currency}}{{amount}} to {{merchant_name}} failed. Retry here: {{retry_link}}',
                true,
                NOW()
            ),
            (
                NULL,
                'customer_account_created',
                'email',
                'Welcome — your account has been created',
                '<h1>Account Created</h1><p>Hello {{customer_name}}, your account with {{merchant_name}} has been created. Verify your email: <a href="{{verify_link}}">Verify Email</a></p>',
                'Hello {{customer_name}}, your account with {{merchant_name}} has been created. Verify your email: {{verify_link}}',
                true,
                NOW()
            ),
            (
                NULL,
                'merchant_alert',
                'email',
                'Payment Received: Invoice #{{invoice_ref}}',
                '<h1>Payment Received</h1><p>Hello {{merchant_name}},</p><p>Your customer {{customer_name}} has successfully paid invoice #{{invoice_ref}} for ${{amount}}.</p><p>Transaction ID: {{transaction_id}}</p><p>You can view the transaction details in your HubWallet portal.</p><p>— HubWallet</p>',
                'Hello {{merchant_name}}, your customer {{customer_name}} has successfully paid invoice #{{invoice_ref}} for ${{amount}}. Transaction ID: {{transaction_id}}. — HubWallet',
                true,
                NOW()
            )
            """
        )

    # ── 3. hpp_retry_tokens ──────────────────────────────────────────────────
    # Guard: table may already exist if created by a parallel branch.
    if 'hpp_retry_tokens' not in existing_tables:
        op.create_table(
            'hpp_retry_tokens',
            sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
            sa.Column('transaction_id', sa.Integer(), nullable=False),
            sa.Column('payment_request_id', sa.Integer(), nullable=False),
            sa.Column('token', sa.String(length=255), nullable=False),
            sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
            sa.Column('used_at', sa.DateTime(timezone=True), nullable=True),
            sa.Column('attempt_count', sa.Integer(), nullable=False, server_default=sa.text('0')),
            sa.Column(
                'created_at',
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.ForeignKeyConstraint(
                ['transaction_id'], ['transactions.id'],
                name='fk_hpp_retry_tokens_transaction_id',
            ),
            sa.ForeignKeyConstraint(
                ['payment_request_id'], ['payment_requests.id'],
                name='fk_hpp_retry_tokens_payment_request_id',
            ),
            sa.PrimaryKeyConstraint('id'),
        )
        op.create_index(op.f('ix_hpp_retry_tokens_id'), 'hpp_retry_tokens', ['id'], unique=False)
        op.create_index(
            'ix_hpp_retry_tokens_token',
            'hpp_retry_tokens',
            ['token'],
            unique=True,
        )
        op.create_index(
            'ix_hpp_retry_tokens_transaction_id',
            'hpp_retry_tokens',
            ['transaction_id'],
            unique=False,
        )


def downgrade() -> None:
    conn = op.get_bind()
    existing_tables = sa_inspect(conn).get_table_names()

    if 'hpp_retry_tokens' in existing_tables:
        op.drop_index('ix_hpp_retry_tokens_transaction_id', table_name='hpp_retry_tokens')
        op.drop_index('ix_hpp_retry_tokens_token', table_name='hpp_retry_tokens')
        op.drop_index(op.f('ix_hpp_retry_tokens_id'), table_name='hpp_retry_tokens')
        op.drop_table('hpp_retry_tokens')

    if 'notification_templates' in existing_tables:
        op.drop_index(
            'ix_notification_templates_merchant_key_channel',
            table_name='notification_templates',
        )
        op.drop_index(
            'ix_notification_templates_key_channel',
            table_name='notification_templates',
        )
        op.drop_index(op.f('ix_notification_templates_id'), table_name='notification_templates')
        op.drop_table('notification_templates')