"""Add a {{bank_details_block}} paragraph to the seeded invoice_overdue_reminder
email template (INDL-XX Bank Details Settings) — mirrors 0079's change to
contract_signed, so overdue-invoice reminders also carry Payment Details +
Bank Details, not just the amount owed.

Only updates rows whose body still exactly matches the known seeded default
from migration 0054 — a tenant that has customized their
invoice_overdue_reminder template via Settings is left untouched, same guard
as 0072/0077/0079.

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 that must
also be made to
src/apps/settings/services/email_template_service.py:SYSTEM_TEMPLATES.

Revision ID: 0080
Revises: 0079
"""
import json

import sqlalchemy as sa
from alembic import op

revision = "0080"
down_revision = "0079"
branch_labels = None
depends_on = None

OLD_BODY = (
    "Dear {{purchaser_name}},\n\n"
    "Our records show invoice {{invoice_number}} (total {{invoice_total}}) "
    "was due on {{due_date}} and is now {{days_overdue}} days overdue.\n\n"
    "Amount due: {{amount_due}}\n\n"
    "Please arrange payment at your earliest convenience. Contact us at "
    "{{cemetery_email}} or {{cemetery_phone}} with any questions.\n\n"
    "Regards,\n{{cemetery_name}}"
)
NEW_BODY = (
    "Dear {{purchaser_name}},\n\n"
    "Our records show invoice {{invoice_number}} (total {{invoice_total}}) "
    "was due on {{due_date}} and is now {{days_overdue}} days overdue.\n\n"
    "Amount due: {{amount_due}}\n\n"
    "{{bank_details_block}}\n\n"
    "Please arrange payment at your earliest convenience. Contact us at "
    "{{cemetery_email}} or {{cemetery_phone}} with any questions.\n\n"
    "Regards,\n{{cemetery_name}}"
)
OLD_MERGE_FIELDS = [
    "purchaser_name", "invoice_number", "invoice_total", "amount_due",
    "due_date", "days_overdue",
    "cemetery_name", "cemetery_email", "cemetery_phone", "current_date",
]
NEW_MERGE_FIELDS = [
    "purchaser_name", "invoice_number", "invoice_total", "amount_due",
    "due_date", "days_overdue", "bank_details_block",
    "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 = 'invoice_overdue_reminder'
              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()
    conn.execute(
        sa.text(
            """
            UPDATE email_templates
            SET body = :old_body,
                merge_fields = CAST(:merge_fields AS JSONB),
                updated_at = now()
            WHERE template_key = 'invoice_overdue_reminder'
              AND is_system = true
              AND body = :new_body
            """
        ),
        {
            "old_body": OLD_BODY,
            "new_body": NEW_BODY,
            "merge_fields": json.dumps(OLD_MERGE_FIELDS),
        },
    )