"""Add a closing "Thank you," signature line to the seeded proposal_sent/
proposal_resent email templates.

The templates ended abruptly after the non-binding-quote disclaimer with no
sign-off, which read as unfinished/unprofessional. 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 one of the known post-0076
defaults (with {{plot_details}}) — a tenant that has customized either
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: 0077
Revises: 0076
"""
import json

import sqlalchemy as sa
from alembic import op

revision = "0077"
down_revision = "0076"
branch_labels = None
depends_on = None

TEMPLATE_KEYS = ("proposal_sent", "proposal_resent")

MERGE_FIELDS = [
    "cover_note", "plot_details", "quote_number", "line_items", "subtotal",
    "tax_amount", "total_amount", "expiry_note",
]
NEW_MERGE_FIELDS = [
    "cover_note", "plot_details", "quote_number", "line_items", "subtotal",
    "tax_amount", "total_amount", "expiry_note", "cemetery_name",
]

# Variant A — 0066 heading style ("--- QUOTE ... ---"), post-0076 plot_details
OLD_BODY_A = (
    "{{cover_note}}\n\n"
    "{{plot_details}}"
    "--- QUOTE {{quote_number}} ---\n"
    "{{line_items}}\n\n"
    "Subtotal:   {{subtotal}}\n"
    "Tax:        {{tax_amount}}\n"
    "Total (CAD): {{total_amount}}\n\n"
    "{{expiry_note}}\n\n"
    "This is a non-binding price quote. Reply to this email with any questions."
)
NEW_BODY_A = (
    "{{cover_note}}\n\n"
    "{{plot_details}}"
    "--- QUOTE {{quote_number}} ---\n"
    "{{line_items}}\n\n"
    "Subtotal:   {{subtotal}}\n"
    "Tax:        {{tax_amount}}\n"
    "Total (CAD): {{total_amount}}\n\n"
    "{{expiry_note}}\n\n"
    "This is a non-binding price quote. Reply to this email with any questions.\n\n"
    "Thank you,\n"
    "{{cemetery_name}}"
)

# Variant B — current email_template_service.py heading style ("Your Quote —"), post-0076 plot_details
OLD_BODY_B = (
    "{{cover_note}}\n\n"
    "{{plot_details}}"
    "Your Quote — {{quote_number}}\n"
    "{{line_items}}\n\n"
    "Subtotal:   {{subtotal}}\n"
    "Tax:        {{tax_amount}}\n"
    "Total (CAD): {{total_amount}}\n\n"
    "{{expiry_note}}\n\n"
    "This is a non-binding price quote. Reply to this email with any questions."
)
NEW_BODY_B = (
    "{{cover_note}}\n\n"
    "{{plot_details}}"
    "Your Quote — {{quote_number}}\n"
    "{{line_items}}\n\n"
    "Subtotal:   {{subtotal}}\n"
    "Tax:        {{tax_amount}}\n"
    "Total (CAD): {{total_amount}}\n\n"
    "{{expiry_note}}\n\n"
    "This is a non-binding price quote. Reply to this email with any questions.\n\n"
    "Thank you,\n"
    "{{cemetery_name}}"
)

VARIANTS = [(OLD_BODY_A, NEW_BODY_A), (OLD_BODY_B, NEW_BODY_B)]


def upgrade() -> None:
    conn = op.get_bind()
    for key in TEMPLATE_KEYS:
        for old_body, new_body in VARIANTS:
            conn.execute(
                sa.text(
                    """
                    UPDATE email_templates
                    SET body = :new_body,
                        merge_fields = CAST(:merge_fields AS JSONB),
                        updated_at = now()
                    WHERE template_key = :template_key
                      AND is_system = true
                      AND body = :old_body
                    """
                ),
                {
                    "template_key": key,
                    "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 one of the new bodies."""
    conn = op.get_bind()
    for key in TEMPLATE_KEYS:
        for old_body, new_body in VARIANTS:
            conn.execute(
                sa.text(
                    """
                    UPDATE email_templates
                    SET body = :old_body,
                        merge_fields = CAST(:merge_fields AS JSONB),
                        updated_at = now()
                    WHERE template_key = :template_key
                      AND is_system = true
                      AND body = :new_body
                    """
                ),
                {
                    "template_key": key,
                    "old_body": old_body,
                    "new_body": new_body,
                    "merge_fields": json.dumps(MERGE_FIELDS),
                },
            )