"""Add {{payment_url}} to the seeded contract_signed email template body.

INDL-29 (Contract Payment Link) generates a payment URL at issue time and
passes it into the contract_signed email context, but the template body
never referenced {{payment_url}} — so the link was silently dropped and
never reached the purchaser. Fixed at the source
(email_template_service.py:SYSTEM_TEMPLATES); this migration brings
already-seeded, still-unmodified tenant rows in line.

Only updates rows whose body still exactly matches the original seeded
default — a tenant that has customized their contract_signed template via
Settings is left untouched.

NOTE: migrations are self-contained/frozen-in-time — this file keeps its own
copy of the old/new body text. It intentionally mirrors the change made to
src/apps/settings/services/email_template_service.py:SYSTEM_TEMPLATES.

Revision ID: 0072
Revises: 0071
"""
import json

import sqlalchemy as sa
from alembic import op

revision = "0072"
down_revision = "0071"
branch_labels = None
depends_on = None

OLD_BODY = (
    "Dear {{purchaser_name}},\n\n"
    "Thank you for choosing {{cemetery_name}}. This confirms your "
    "{{contract_type}} contract ({{contract_number}}) for {{plot_id}} "
    "in {{section}} has been signed.\n\n"
    "Contract total: {{contract_total}}\n"
    "Balance due: {{balance_due}}\n\n"
    "If you have any questions, contact us at {{cemetery_email}} or "
    "{{cemetery_phone}}.\n\n"
    "Sincerely,\n{{cemetery_name}}"
)

NEW_BODY = (
    "Dear {{purchaser_name}},\n\n"
    "Thank you for choosing {{cemetery_name}}. This confirms your "
    "{{contract_type}} contract ({{contract_number}}) for {{plot_id}} "
    "in {{section}} has been signed.\n\n"
    "Contract total: {{contract_total}}\n"
    "Balance due: {{balance_due}}\n\n"
    "To complete your payment online, visit:\n{{payment_url}}\n\n"
    "If you have any questions, contact us at {{cemetery_email}} or "
    "{{cemetery_phone}}.\n\n"
    "Sincerely,\n{{cemetery_name}}"
)

NEW_MERGE_FIELDS = [
    "purchaser_name", "contract_type", "plot_id", "section",
    "contract_number", "contract_total", "balance_due", "payment_url",
    "cemetery_name", "cemetery_email", "cemetery_phone", "current_date",
]


def upgrade() -> None:
    conn = op.get_bind()
    conn.execute(
        sa.text(
            """
            UPDATE email_templates
            SET body = :new_body,
                merge_fields = CAST(:merge_fields AS JSONB),
                updated_at = now()
            WHERE template_key = 'contract_signed'
              AND is_system = true
              AND body = :old_body
            """
        ),
        {
            "new_body": NEW_BODY,
            "old_body": OLD_BODY,
            "merge_fields": json.dumps(NEW_MERGE_FIELDS),
        },
    )


def downgrade() -> None:
    """Best-effort revert: only rows still exactly on the new body."""
    conn = op.get_bind()
    old_merge_fields = [f for f in NEW_MERGE_FIELDS if f != "payment_url"]
    conn.execute(
        sa.text(
            """
            UPDATE email_templates
            SET body = :old_body,
                merge_fields = CAST(:merge_fields AS JSONB),
                updated_at = now()
            WHERE template_key = 'contract_signed'
              AND is_system = true
              AND body = :new_body
            """
        ),
        {
            "old_body": OLD_BODY,
            "new_body": NEW_BODY,
            "merge_fields": json.dumps(old_merge_fields),
        },
    )