"""Email Templates — align schema with INDL-32 PRD + seed 8 system templates

Revision ID: 0054
Revises: 0053
Create Date: 2026-07-02

Alters the `email_templates` table (originally created by migration 0012 with
an ad-hoc `trigger`/`is_enabled`/`variables` shape) to match the INDL-32 PRD
data model: adds `template_key`, `is_system`, `description`, `status`
(with CHECK constraint), `merge_fields`, and `deleted_at`; drops the old
`trigger`/`is_enabled`/`variables` columns; adds a partial unique index on
(tenant_id, template_key); and seeds the 8 system templates for every
existing tenant in `accounts`.

All bound values are passed via SQLAlchemy `text()` bindparams — never
f-string interpolated — to avoid SQL injection in the migration itself.
"""
from alembic import op
import sqlalchemy as sa

revision = "0054"
down_revision = "0053"
branch_labels = None
depends_on = None


# keep in sync with src/apps/settings/services/email_template_service.py:SYSTEM_TEMPLATES
# (this migration intentionally keeps its own frozen copy rather than importing from src/apps —
# no other migration in this repo imports application code, since historical migrations must stay
# stable even as application code evolves; update both places if the seed set changes)
_GLOBAL_MERGE_FIELDS = ["cemetery_name", "cemetery_email", "cemetery_phone", "current_date"]

SYSTEM_TEMPLATES = [
    {
        "template_key": "contract_signed",
        "name": "Contract signed — confirmation",
        "description": "Sent to purchaser when a contract is signed",
        "status": "active",
        "subject": "Your contract with {{cemetery_name}} is confirmed",
        "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}}"
        ),
        "merge_fields": [
            "purchaser_name", "contract_type", "plot_id", "section",
            "contract_number", "contract_total", "balance_due",
        ],
    },
    {
        "template_key": "invoice_payment_received",
        "name": "Invoice — payment received",
        "description": "Receipt to purchaser",
        "status": "active",
        "subject": "Payment received — invoice {{invoice_number}}",
        "body": (
            "Dear {{purchaser_name}},\n\n"
            "We have received your payment of {{amount_paid}} on {{payment_date}} "
            "for invoice {{invoice_number}}.\n\n"
            "Invoice total: {{invoice_total}}\n"
            "Remaining balance: {{balance_due}}\n\n"
            "Thank you,\n{{cemetery_name}}"
        ),
        "merge_fields": [
            "purchaser_name", "invoice_number", "amount_paid", "payment_date",
            "invoice_total", "balance_due",
        ],
    },
    {
        "template_key": "invoice_overdue_reminder",
        "name": "Invoice overdue reminder",
        "description": "7 / 14 / 30 days overdue",
        "status": "active",
        "subject": "Reminder: invoice {{invoice_number}} is {{days_overdue}} days overdue",
        "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}}"
        ),
        "merge_fields": [
            "purchaser_name", "invoice_number", "invoice_total", "amount_due",
            "due_date", "days_overdue",
        ],
    },
    {
        "template_key": "service_scheduled_family",
        "name": "Service scheduled — family notice",
        "description": "Interment / memorial confirmation",
        "status": "active",
        "subject": "Your {{service_type}} at {{cemetery_name}} is confirmed",
        "body": (
            "Dear {{family_name}},\n\n"
            "This confirms the {{service_type}} scheduled for {{service_date}} at "
            "{{service_time}}, at {{location}}.\n\n"
            "Officiant: {{officiant_name}}\n\n"
            "Please contact us at {{cemetery_email}} or {{cemetery_phone}} if you "
            "have any questions.\n\n"
            "With sympathy,\n{{cemetery_name}}"
        ),
        "merge_fields": [
            "family_name", "service_type", "service_date", "service_time",
            "location", "officiant_name",
        ],
    },
    {
        "template_key": "field_crew_briefing",
        "name": "Field crew briefing sheet",
        "description": "Sent to assigned field staff",
        "status": "active",
        "subject": "Briefing: {{service_type}} on {{service_date}}",
        "body": (
            "Team,\n\n"
            "You are assigned to a {{service_type}} on {{service_date}} at "
            "{{service_time}}.\n\n"
            "Location: {{plot_id}}, {{section}}\n"
            "Assigned staff: {{assigned_staff}}\n\n"
            "— {{cemetery_name}} Operations"
        ),
        "merge_fields": [
            "service_type", "service_date", "service_time", "plot_id",
            "section", "assigned_staff",
        ],
    },
    {
        "template_key": "tribute_moderated",
        "name": "Tribute approved / rejected",
        "description": "To memorial submitter",
        "status": "active",
        "subject": "Your tribute for {{memorial_name}} has been {{tribute_status}}",
        "body": (
            "Dear {{submitter_name}},\n\n"
            "Your submitted tribute for {{memorial_name}} has been {{tribute_status}}.\n\n"
            "{{rejection_reason}}\n\n"
            "Thank you for sharing your memories.\n\n"
            "{{cemetery_name}}"
        ),
        "merge_fields": [
            "submitter_name", "memorial_name", "tribute_status", "rejection_reason",
        ],
    },
    {
        "template_key": "deed_transferred",
        "name": "Deed transferred",
        "description": "To new + previous deed holder",
        "status": "inactive",
        "subject": "Deed transfer confirmation — {{plot_id}}",
        "body": (
            "This confirms the deed for {{plot_id}} in {{section}} was transferred "
            "from {{previous_holder_name}} to {{new_holder_name}} effective "
            "{{transfer_date}}.\n\n"
            "Questions? Contact us at {{cemetery_email}} or {{cemetery_phone}}.\n\n"
            "{{cemetery_name}}"
        ),
        "merge_fields": [
            "new_holder_name", "previous_holder_name", "plot_id", "section",
            "transfer_date",
        ],
    },
    {
        "template_key": "new_user_welcome",
        "name": "New user welcome",
        "description": "Temporary password + sign-in link",
        "status": "active",
        "subject": "Welcome to {{cemetery_name}} on INDELIS",
        "body": (
            "Hi {{user_name}},\n\n"
            "An account has been created for you on {{cemetery_name}}'s INDELIS "
            "workspace.\n\n"
            "Temporary password: {{temp_password}}\n"
            "Sign in here: {{login_url}}\n\n"
            "You will be asked to set a new password on first login.\n\n"
            "— {{cemetery_name}}"
        ),
        "merge_fields": ["user_name", "temp_password", "login_url"],
    },
]


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

    # ── Schema changes ───────────────────────────────────────────────────────
    conn.execute(sa.text(
        "ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS template_key VARCHAR(100) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS is_system BOOLEAN NOT NULL DEFAULT false"
    ))
    conn.execute(sa.text(
        "ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS description VARCHAR(255) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS status VARCHAR(20) NOT NULL DEFAULT 'active'"
    ))
    conn.execute(sa.text(
        "ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS merge_fields JSONB NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ NULL"
    ))

    conn.execute(sa.text(
        """
        DO $$
        BEGIN
            IF NOT EXISTS (
                SELECT 1 FROM pg_constraint WHERE conname = 'ck_email_templates_status'
            ) THEN
                ALTER TABLE email_templates
                    ADD CONSTRAINT ck_email_templates_status
                    CHECK (status IN ('active', 'inactive'));
            END IF;
        END $$;
        """
    ))

    # ── Backfill status / merge_fields from the old columns ─────────────────
    conn.execute(sa.text(
        "UPDATE email_templates SET status = CASE WHEN is_enabled THEN 'active' ELSE 'inactive' END"
    ))
    conn.execute(sa.text(
        "UPDATE email_templates SET merge_fields = variables WHERE variables IS NOT NULL"
    ))

    # ── Drop obsolete columns ─────────────────────────────────────────────────
    conn.execute(sa.text("ALTER TABLE email_templates DROP COLUMN IF EXISTS trigger"))
    conn.execute(sa.text("ALTER TABLE email_templates DROP COLUMN IF EXISTS is_enabled"))
    conn.execute(sa.text("ALTER TABLE email_templates DROP COLUMN IF EXISTS variables"))

    # ── Unique index: one system template per key per tenant ────────────────
    conn.execute(sa.text(
        """
        CREATE UNIQUE INDEX IF NOT EXISTS uq_email_templates_tenant_template_key
        ON email_templates (tenant_id, template_key)
        WHERE template_key IS NOT NULL
        """
    ))

    # ── Seed 8 system templates for every existing tenant ────────────────────
    account_ids = [row[0] for row in conn.execute(sa.text("SELECT id FROM accounts")).all()]

    # NOTE: :tenant_id/:template_key are each bound to two different SQL type
    # contexts (a plain SELECT-list value vs. a column comparison) — under the
    # psycopg3 driver this triggers "AmbiguousParameter: inconsistent types
    # deduced" unless every occurrence is explicitly cast to match the target
    # column type. Do not remove these casts even though they look redundant.
    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))
        )
        """
    )

    import json

    for tenant_id in account_ids:
        for tmpl in SYSTEM_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:
    """Best-effort reverse. Data loss on the seeded system templates and any
    values stored in the new columns is acceptable for a dev-only rollback."""
    conn = op.get_bind()

    # Remove seeded system template rows.
    conn.execute(sa.text("DELETE FROM email_templates WHERE is_system = true"))

    # Drop the new constraint / index / columns.
    conn.execute(sa.text(
        "DROP INDEX IF EXISTS uq_email_templates_tenant_template_key"
    ))
    conn.execute(sa.text(
        "ALTER TABLE email_templates DROP CONSTRAINT IF EXISTS ck_email_templates_status"
    ))
    conn.execute(sa.text("ALTER TABLE email_templates DROP COLUMN IF EXISTS template_key"))
    conn.execute(sa.text("ALTER TABLE email_templates DROP COLUMN IF EXISTS is_system"))
    conn.execute(sa.text("ALTER TABLE email_templates DROP COLUMN IF EXISTS description"))
    conn.execute(sa.text("ALTER TABLE email_templates DROP COLUMN IF EXISTS merge_fields"))
    conn.execute(sa.text("ALTER TABLE email_templates DROP COLUMN IF EXISTS deleted_at"))

    # Re-add the old columns (best effort — data not restored).
    conn.execute(sa.text(
        "ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS trigger VARCHAR(255) NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS is_enabled BOOLEAN NOT NULL DEFAULT true"
    ))
    conn.execute(sa.text(
        "ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS variables JSONB NULL"
    ))
    conn.execute(sa.text(
        "UPDATE email_templates SET is_enabled = (status = 'active')"
    ))

    conn.execute(sa.text("ALTER TABLE email_templates DROP COLUMN IF EXISTS status"))