"""Add {{plot_details}} to the seeded proposal_sent/proposal_resent email templates.

The new Plot Selection card on the New Proposal page lets staff attach a
specific vacant plot to a proposal, but the proposal_sent/proposal_resent
email templates never referenced a merge field for it — so a purchaser
receiving the quote email would see everything except which plot it was for.
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 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.

Two "old body" variants are handled because tenants were seeded at different
points in this template's history: the original 0066 seed used a
"--- QUOTE {{quote_number}} ---" heading, which was later hand-edited in
email_template_service.py to "Your Quote — {{quote_number}}" without a
migration bringing already-seeded rows along — so live tenants may be on
either variant. Both map to their own NEW_BODY (same {{plot_details}} line
inserted, heading style preserved).

Revision ID: 0076
Revises: 0075
"""
import json

import sqlalchemy as sa
from alembic import op

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

TEMPLATE_KEYS = ("proposal_sent", "proposal_resent")

MERGE_FIELDS = [
    "cover_note", "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",
]

# Variant A — original 0066 seed
OLD_BODY_A = (
    "{{cover_note}}\n\n"
    "--- 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."
)

# Variant B — current email_template_service.py seed (post hand-edit)
OLD_BODY_B = (
    "{{cover_note}}\n\n"
    "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."
)

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),
                },
            )