"""INDL-54: extend email_templates seed catalog with 9 new trigger keys.

Seeds the 9 previously-hardcoded, unmapped trigger templates
(staff_invitation, staff_invitation_resend, contact_enquiry_reply,
lead_qualified, proposal_sent, proposal_resent, contract_pdf_confirmation,
service_reminder_24h, news_notification) for every existing tenant, bringing
the seeded system-template count from 8 to 17. New tenants get all 17 from
the provisioning seed flow (EmailTemplateService.seed_system_templates).

Idempotent: uses WHERE NOT EXISTS on (tenant_id, template_key), matching the
partial unique index created by 0054. Safe to re-run.

NOTE: migrations are self-contained / frozen-in-time — this file keeps its own
copy of the seed data. It intentionally mirrors the 9 new entries added to
src/apps/settings/services/email_template_service.py:SYSTEM_TEMPLATES; update
both places if the seed set changes.

Revision ID: 0066
Revises: 0065

NOTE: originally numbered 0065 with down_revision 0064. Renumbered to 0066
(down_revision 0065) to re-chain onto develop head after 0065 was
independently claimed by 0065_qr_codes_headstone_type.py, mirroring the
precedent set by commit 3ebf2e6 "chore(migrations): re-chain scheduling
migrations onto develop head".
"""
import json

import sqlalchemy as sa
from alembic import op

revision = "0066"
down_revision = "0065"
branch_labels = None
depends_on = None

_GLOBAL_MERGE_FIELDS = ["cemetery_name", "cemetery_email", "cemetery_phone", "current_date"]

# The 9 new trigger templates (see module docstring).
NEW_TEMPLATES: list[dict] = [
    {
        "template_key": "staff_invitation",
        "name": "Staff invitation",
        "description": "Sent to a staff member invited to the tenant",
        "status": "active",
        "subject": "You've been invited to join {{organization_name}} on INDELIS",
        "body": (
            "Hi {{invitee_name}},\n\n"
            "{{invited_by_name}} has invited you to join {{organization_name}} on "
            "INDELIS as a {{role}}.\n\n"
            "Accept your invitation and set your password here:\n{{invite_url}}\n\n"
            "This link is valid for 7 days and can only be used once.\n\n"
            "— {{cemetery_name}}"
        ),
        "merge_fields": [
            "invitee_name", "invited_by_name", "organization_name", "role", "invite_url",
        ],
    },
    {
        "template_key": "staff_invitation_resend",
        "name": "Staff invitation — resent",
        "description": "Reminder resend of a staff invitation",
        "status": "active",
        "subject": "Reminder: your invitation to join {{organization_name}} on INDELIS",
        "body": (
            "Hi {{invitee_name}},\n\n"
            "This is a reminder that {{invited_by_name}} invited you to join "
            "{{organization_name}} on INDELIS as a {{role}}.\n\n"
            "Accept your invitation and set your password here:\n{{invite_url}}\n\n"
            "This link is valid for 7 days and can only be used once.\n\n"
            "— {{cemetery_name}}"
        ),
        "merge_fields": [
            "invitee_name", "invited_by_name", "organization_name", "role", "invite_url",
        ],
    },
    {
        "template_key": "contact_enquiry_reply",
        "name": "Contact enquiry reply",
        "description": "Site admin's reply to a public contact-form enquiry",
        "status": "active",
        "subject": "Re: your enquiry to {{cemetery_name}}",
        "body": (
            "Hi {{contact_name}},\n\n"
            "Thank you for reaching out. Here is a response from our team:\n\n"
            "{{reply_message}}\n\n"
            "If you have any further questions, please reply to this email.\n\n"
            "Warm regards,\n{{admin_name}}\n{{cemetery_name}}"
        ),
        "merge_fields": ["contact_name", "reply_message", "admin_name"],
    },
    {
        "template_key": "lead_qualified",
        "name": "Lead qualified — signup invitation",
        "description": "Sent when an enquiry is marked qualified, with a signup link",
        "status": "active",
        "subject": "You're approved — complete your INDELIS setup",
        "body": (
            "Hi {{contact_name}},\n\n"
            "Great news — we've reviewed your enquiry for {{organization}} and "
            "you're approved to join INDELIS!\n\n"
            "Complete your account setup here:\n{{signup_url}}\n\n"
            "Your information has been pre-filled to save you time.\n\n"
            "Warm regards,\nThe INDELIS Team"
        ),
        "merge_fields": ["contact_name", "organization", "signup_url"],
    },
    {
        "template_key": "proposal_sent",
        "name": "Sales proposal sent",
        "description": "Price proposal / quote sent to a prospect",
        "status": "active",
        "subject": "Your price proposal {{quote_number}} from {{cemetery_name}}",
        "body": (
            "{{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."
        ),
        "merge_fields": [
            "cover_note", "quote_number", "line_items", "subtotal",
            "tax_amount", "total_amount", "expiry_note",
        ],
    },
    {
        "template_key": "proposal_resent",
        "name": "Sales proposal — resent",
        "description": "Proposal re-sent to a prospect after renewal",
        "status": "active",
        "subject": "[Resent] Your price proposal {{quote_number}} from {{cemetery_name}}",
        "body": (
            "{{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."
        ),
        "merge_fields": [
            "cover_note", "quote_number", "line_items", "subtotal",
            "tax_amount", "total_amount", "expiry_note",
        ],
    },
    {
        "template_key": "contract_pdf_confirmation",
        "name": "Contract PDF confirmation",
        "description": "Confirmation sent once a signed contract PDF is generated",
        "status": "active",
        "subject": "Your contract {{contract_number}} is ready",
        "body": (
            "Dear {{purchaser_name}},\n\n"
            "Your signed Cemetery Plot Purchase Agreement (Contract "
            "#{{contract_number}}) has been processed and is ready.\n\n"
            "Please retain this confirmation for your records. A copy of your "
            "contract PDF is available through your INDELIS portal.\n\n"
            "If you have any questions, please contact the cemetery office.\n\n"
            "— {{cemetery_name}}"
        ),
        "merge_fields": ["purchaser_name", "contract_number"],
    },
    {
        "template_key": "service_reminder_24h",
        "name": "Service reminder (24h)",
        "description": "24-hour reminder before a scheduled service",
        "status": "active",
        "subject": "Reminder: service tomorrow for {{decedent_name}}",
        "body": (
            "Dear {{family_name}},\n\n"
            "This is a reminder that the following service is scheduled for "
            "tomorrow at {{cemetery_name}}:\n\n"
            "Service Type: {{service_type}}\n"
            "For:          {{decedent_name}}\n"
            "Date:         {{service_date}}\n"
            "Time:         {{service_time}}\n"
            "Location:     {{location}}\n\n"
            "If you need to make any changes, please contact us as soon as "
            "possible at {{cemetery_email}} or {{cemetery_phone}}.\n\n"
            "Warm regards,\n{{cemetery_name}}"
        ),
        "merge_fields": [
            "family_name", "service_type", "decedent_name", "service_date",
            "service_time", "location",
        ],
    },
    {
        "template_key": "news_notification",
        "name": "News post notification",
        "description": "Sent to subscribers when a news post is published",
        "status": "active",
        "subject": "New article: {{post_title}}",
        "body": (
            "{{post_title}}\n\n"
            "{{post_excerpt}}\n\n"
            "Read the full article: {{article_url}}\n\n"
            "---\n"
            "To unsubscribe from news notifications: {{unsubscribe_url}}"
        ),
        "merge_fields": [
            "post_title", "post_excerpt", "article_url", "unsubscribe_url",
        ],
    },
]


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

    account_ids = [row[0] for row in conn.execute(sa.text("SELECT id FROM accounts")).all()]

    # :tenant_id / :template_key are cast explicitly to defeat psycopg3
    # "AmbiguousParameter: inconsistent types deduced" (same rationale as 0054).
    insert_stmt = sa.text(
        """
        INSERT INTO email_templates
            (id, tenant_id, template_key, is_system, name, description,
             subject, body, status, merge_fields, created_at, updated_at)
        SELECT
            gen_random_uuid(), CAST(:tenant_id AS UUID), CAST(:template_key AS VARCHAR(100)),
            true, :name, :description,
            :subject, :body, :status, CAST(:merge_fields AS JSONB), now(), now()
        WHERE NOT EXISTS (
            SELECT 1 FROM email_templates
            WHERE tenant_id = CAST(:tenant_id AS UUID)
              AND template_key = CAST(:template_key AS VARCHAR(100))
        )
        """
    )

    for tenant_id in account_ids:
        for tmpl in NEW_TEMPLATES:
            conn.execute(
                insert_stmt,
                {
                    "tenant_id": tenant_id,
                    "template_key": tmpl["template_key"],
                    "name": tmpl["name"],
                    "description": tmpl["description"],
                    "subject": tmpl["subject"],
                    "body": tmpl["body"],
                    "status": tmpl["status"],
                    "merge_fields": json.dumps(tmpl["merge_fields"] + _GLOBAL_MERGE_FIELDS),
                },
            )


def downgrade() -> None:
    """Remove only the 9 rows this migration seeds (best-effort, dev-only)."""
    conn = op.get_bind()
    keys = tuple(t["template_key"] for t in NEW_TEMPLATES)
    conn.execute(
        sa.text(
            "DELETE FROM email_templates WHERE is_system = true "
            "AND template_key = ANY(:keys)"
        ),
        {"keys": list(keys)},
    )