-- ============================================================
-- PT_TAT E-INVOICE FAILURE EMAIL TEMPLATE (Zepto)
-- ============================================================
-- Finance Manager notification sent when e-invoice (IRP) generation
-- fails for a TAT registration. Resolved at send time via access key
-- EINVOICE_ERROR_EMAIL_SEEKER for PT_TAT programs.
--
-- Merge field sources (read-only — no change to error storage):
--   Error_Code      -> hdb_registration_invoice_detail.error_full_json ->> 'code'
--   Error_Message   -> hdb_registration_invoice_detail.error_message
--   Invoice_Number  -> hdb_registration_invoice_detail.invoice_sequence_number
--   Invoice_Value   -> computed calculateTotalPaymentAmount (original_amount + gst_amount)
--   Invoice_Date    -> hdb_registration_payment_detail.payment_date (formatted DD/MM/YYYY)
--   Customer_GSTIN  -> hdb_registration_invoice_detail.gst_number
--   Seeker_Name     -> hdb_program_registration.full_name
--
-- Safe to re-run (idempotent via ON CONFLICT).
-- ============================================================

-- 1. TAT E-Invoice Error Email (e-invoice / IRP generation failure)
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'TAT_EINVOICE_ERROR_EMAIL',
    'TAT E-Invoice Error - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.d90f9f30-712e-11f1-a000-525400114fe6.19f02c8d8a3',
    '2d6f.2a60732b5ed84938.k1.90339fe0-712f-11f1-a000-525400114fe6.19f02cd88de',
    'invoice',
    'general',
    'EINVOICE_ERROR_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),
    '[
        {"keyName": "Error_Code", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "error_full_json->>''code''", "dataType": "string", "isNullable": true},
        {"keyName": "Error_Message", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "error_message", "dataType": "string", "isNullable": true},
        {"keyName": "Invoice_Number", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "invoice_sequence_number", "dataType": "string", "isNullable": true},
        {"keyName": "Invoice_Value", "sourceTable": "computed", "sourceColumn": "calculateTotalPaymentAmount", "dataType": "number", "isNullable": true},
        {"keyName": "Invoice_Date", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_date", "dataType": "date", "formatType": "short", "isNullable": true},
        {"keyName": "Customer_GSTIN", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "gst_number", "dataType": "string", "isNullable": true},
        {"keyName": "Seeker_Name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    13,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- ============================================================
-- VERIFICATION QUERY
-- ============================================================
-- SELECT template_key, template_id, sandbox_template_id, category, template_access_key, merge_field_map
-- FROM public.communication_templates_master
-- WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT')
--   AND template_access_key = 'EINVOICE_ERROR_EMAIL_SEEKER'
-- ORDER BY display_order;
