-- ============================================================ -- CareBill seed data — run AFTER schema.sql -- Default admin: admin@caresoft.co.in / Caresoft@123 (change on first login) -- ============================================================ SET NAMES utf8mb4; -- ---------- users ---------- INSERT INTO users (name,email,phone,password_hash,role,status,must_reset) VALUES ('Administrator','admin@caresoft.co.in','02228115575','$2y$10$umwgwAnm4w6oTKjNj06meOyIfxNyHXkdfGV12LsV0SymPGzb.peGa','admin','active',1), ('Accounts Cell','accounts@caresoft.co.in','02228115573','$2y$10$umwgwAnm4w6oTKjNj06meOyIfxNyHXkdfGV12LsV0SymPGzb.peGa','user','active',1); -- ---------- settings ---------- INSERT INTO settings (k,v) VALUES ('org_name','Caresoft Systems Private Limited'), ('app_url','https://billing.caresoft.co.in'), ('fy_start_month','4'), ('mail_driver','ses'), -- ses | smtp (Postal) | log ('ses_region','ap-south-1'), ('ses_key',''),('ses_secret',''), ('ses_from_name','Caresoft Accounts'), ('ses_from_email','accounts@caresoft.co.in'), ('ses_daily_cap','800'), -- hard cap; protects against runaway sends ('smtp_host',''),('smtp_port','587'),('smtp_user',''),('smtp_pass',''),('smtp_secure','tls'), ('wa_driver','cloud'), -- cloud = WhatsApp Cloud API ('wa_phone_id',''),('wa_token',''),('wa_waba_id',''), ('call_driver','none'), -- exotel | knowlarity | none ('call_api_key',''),('call_api_token',''),('call_sid',''),('call_caller_id',''), ('send_window_start','09:30'), ('send_window_end','18:30'), ('send_on_sunday','0'), ('escalation_email','prassant@caresoft.co.in'), ('support_email','support@caresoft.co.in'), ('management_email','prassant@caresoft.co.in'), ('mis_digest_day','1'), ('ccav_merchant_id',''), ('ccav_access_code',''), ('ccav_working_key',''), ('ccav_mode','test'), ('doc_number_format','{prefix}/{yy}/{mm}/{n4}'), ('approvals_enabled','1'), ('approve_writeoff_above','1000'), ('approve_discount_above','5000'), ('approve_invoice_above','500000'), ('approve_credit_notes','1'), ('approve_bank_changes','1'), ('tds_chase_enabled','1'), ('tds_section','194J'), ('revenue_enabled','1'), ('revenue_basis','period'), ('backup_enabled','0'), ('backup_keep','14'), ('wa_verify_token',''), ('wa_app_secret',''), ('wa_hourly_cap','60'), ('wa_optout_words','STOP,UNSUBSCRIBE,OPT OUT,OPTOUT,BAND KARO,ROKO'), ('wa_optin_words','START,YES,SUBSCRIBE,HAAN'), ('deliver_warmup_start',''), ('deliver_domain_hourly','30'), ('deliver_soft_limit','4'), ('mailhook_key',''), ('twofa_required_admins','0'), ('login_lock_attempts','6'), ('login_lock_minutes','15'), ('pay_enabled','0'), ('pay_provider','ccavenue'), ('pay_methods','ccavenue,razorpay'), ('sms_driver','log'), ('sms_sender_id',''), ('sms_entity_id',''), ('sms_api_key',''), ('sms_api_url','https://control.msg91.com/api/v5/flow'), ('retention_message_months','24'), ('retention_login_months','12'), ('retention_events_months','12'), ('retention_upload_days','90'), ('retention_financial_years','8'), ('tally_url',''), ('tally_company',''), ('tally_sync_enabled','0'), ('pay_key_id',''), ('pay_key_secret',''), ('pay_webhook_secret',''), ('pay_link_days','15'), ('pay_min_amount','100'), ('pay_base_url','https://api.razorpay.com/v1'), ('base_currency','INR'), ('fx_warn_days','7'), ('lut_number',''), ('lut_valid_till',''), ('portal_enabled','1'), ('einv_enabled','0'), ('einv_driver','manual'), ('einv_gstin',''), ('einv_base_url','https://einv-apisandbox.nic.in'), ('einv_username',''), ('einv_password',''), ('einv_client_id',''), ('einv_client_secret',''), ('einv_public_key',''), ('einv_gsp_url',''), ('einv_gsp_cancel_url',''), ('einv_gsp_headers',''), ('einv_30day_rule','0'), ('einv_token_cache',''), ('portal_url',''), ('settle_tolerance','10'), -- <= Rs.10 balance auto-closes as round-off ('company_logo','assets/caresoft-logo.png'); -- ---------- brands ---------- INSERT INTO brands (code,name,legal_name,tagline,address,city,state,state_code,pincode,phone,email,website,gstin,pan,cin,msme_no, accreditation,bank_name,bank_ac,bank_ifsc,bank_branch,jurisdiction,invoice_prefix,proforma_prefix,cn_prefix, payment_terms_days,reply_to,footer_note,terms_html,exclusions_html) VALUES ('CSPL','Caresoft HIS','CARESOFT SYSTEMS PRIVATE LIMITED', 'Hospital Software * Healthcare Marketing * Supply Chain', '311, 2nd Floor, Mahesh Industrial Estate, Silver Park, Mira Road East','Thane','Maharashtra','27','401107', '022-28115575 / 28115573 / 28130557 / 28130558','info@caresoft.co.in','http://www.caresoft.co.in', '27AAKCC5044M1ZB','AAKCC5044M','U72900MH2022PTC387875','UDYAM-MH-33-0240256', 'A CMMI Maturity Level 5 Company | ISO 9001:2015 | ISO 27001:2013 | ISO 20000-1:2018 | ISO 17799:2005', 'HDFC BANK LTD','59202424242402','HDFC0006199','Mira Road Silver Park Kashimira','Thane', 'CSPL','CSPF','CSCN',45,'accounts@caresoft.co.in', 'Certified that the particulars given above are true & correct', '
  1. Payment terms: 100% advance unless otherwise agreed in writing.
  2. As per Section 15 of the MSME Act 2006, payment should clear within 45 days.
  3. Interest @18% p.a. is chargeable on amounts outstanding beyond the due date.
  4. Subscription covers the period stated above only. Services are liable to be suspended if renewal is not received within 30 days of expiry.
  5. Cheques / NEFT to be drawn in favour of Caresoft Systems Private Limited.
  6. GST will be charged as applicable on the date of invoice. Please share GSTIN before payment; ITC is not claimable on a proforma invoice.
  7. TDS, if deducted, must be supported by Form 16A within the same quarter.
  8. All disputes subject to Thane jurisdiction.
', '
1Customisations - As per requirements
2Rate Revisions - INR 3000 + GST Per Tpa
3Third Party Integrations SMS - INR 25000 + GST
4Third Party Integrations WhatsApp - INR 25000 + GST
5SMS Charges - 1 Lac @ 20k+GST, 50k @ 12.5k+GST, 25k @ 7.5k+GST, 10k @ 3.5k+GST
6Database Maintenance BACKUP PLAN - 30 GB: 12K + GST/year; 50 GB: 18K + GST/year; 100 GB: 30K + GST/year
7Application Maintenance Charges (Online) +1 Follow up - INR 10K + GST
8Same Sequence Maintenance for OPD and IPD - 6k+GST / record (up to 5 entries; deletion of existing records only)
9Client Server Maintenance (Server Shifting) - INR 10K + GST
10Graphics Work - Logo design, Header design, Doctor signature design - As per requirements
11Consultant Visit Charge - INR 3K + GST
'); -- Additional brand shells (fill in GSTIN/entity as applicable; each keeps its own numbering + items) INSERT INTO brands (code,name,legal_name,city,state,state_code,payment_terms_days,invoice_prefix,proforma_prefix,cn_prefix,email,status) VALUES ('DIPD','Digital IPD','CARESOFT SYSTEMS PRIVATE LIMITED','Thane','Maharashtra','27',45,'DIPD','DIPF','DICN','accounts@caresoft.co.in','active'), ('DOPD','Digital OPD','CARESOFT SYSTEMS PRIVATE LIMITED','Thane','Maharashtra','27',45,'DOPD','DOPF','DOCN','accounts@caresoft.co.in','active'), ('CLOUD','Caresoft Cloud','CARESOFT SYSTEMS PRIVATE LIMITED','Thane','Maharashtra','27',30,'CLD','CLPF','CLCN','accounts@caresoft.co.in','active'), ('MEDC','Medicircle','MEDICIRCLE MEDIA PRIVATE LIMITED','Thane','Maharashtra','27',30,'MC','MCPF','MCCN','accounts@medicircle.in','active'); -- ---------- billing item master (brand-wise) ---------- INSERT INTO billing_items (brand_id,code,name,description,hsn_sac,default_rate,gst_rate,default_cycle,is_recurring,revenue_head,tally_ledger) VALUES (1,'AMC','Annual Maintenance Charges - Caresoft HIS','ANNUAL MAINTENANCE CHARGES FOR CARESOFT HIS (HOSPITAL INFORMATION SYSTEM)\nFOR THE PERIOD FROM {period_from} TO {period_to}','998314',0,18,'yearly',1,'AMC','AMC Income'), (1,'SUB','Subscription Charges - Caresoft HIS','SUBSCRIPTION CHARGES FOR CARESOFT HIS (HOSPITAL INFORMATION SYSTEM)\nFOR THE PERIOD FROM {period_from} TO {period_to}','998314',30000,18,'quarterly',1,'Subscription','Subscription Income'), (1,'LIC','Software Licence - Caresoft HIS','SUPPLY OF CARESOFT HIS SOFTWARE LICENCE AS PER PO {po_no}','998314',0,18,'one_time',0,'Licence','Software Sales'), (1,'ENH','Enhancement / Customisation','CUSTOMISATION AND ENHANCEMENT CHARGES AS PER APPROVED SCOPE','998314',0,18,'one_time',0,'Enhancement','Development Income'), (1,'UPG','Version Upgradation','UPGRADATION OF CARESOFT HIS TO LATEST VERSION','998314',0,18,'one_time',0,'Upgradation','Development Income'), (1,'BKP30','Database Backup Plan - 30 GB','DATABASE MAINTENANCE BACKUP PLAN - 30 GB FOR THE PERIOD {period_from} TO {period_to}','998315',12000,18,'yearly',1,'Backup','Backup Income'), (1,'BKP50','Database Backup Plan - 50 GB','DATABASE MAINTENANCE BACKUP PLAN - 50 GB FOR THE PERIOD {period_from} TO {period_to}','998315',18000,18,'yearly',1,'Backup','Backup Income'), (1,'BKP100','Database Backup Plan - 100 GB','DATABASE MAINTENANCE BACKUP PLAN - 100 GB FOR THE PERIOD {period_from} TO {period_to}','998315',30000,18,'yearly',1,'Backup','Backup Income'), (1,'WAINT','WhatsApp Integration (one time)','THIRD PARTY INTEGRATION - WHATSAPP','998314',25000,18,'one_time',0,'Integration','Integration Income'), (1,'SMSINT','SMS Integration (one time)','THIRD PARTY INTEGRATION - SMS','998314',25000,18,'one_time',0,'Integration','Integration Income'), (1,'WACR','WhatsApp Credits','WHATSAPP CREDITS - {qty} MESSAGES','998314',4000,18,'one_time',0,'WhatsApp','WhatsApp Income'), (1,'SMS10','SMS Credits - 10,000','SMS CREDITS - 10,000','998314',3500,18,'one_time',0,'SMS','SMS Income'), (1,'SMS25','SMS Credits - 25,000','SMS CREDITS - 25,000','998314',7500,18,'one_time',0,'SMS','SMS Income'), (1,'SMS50','SMS Credits - 50,000','SMS CREDITS - 50,000','998314',12500,18,'one_time',0,'SMS','SMS Income'), (1,'SMS100','SMS Credits - 1,00,000','SMS CREDITS - 1,00,000','998314',20000,18,'one_time',0,'SMS','SMS Income'), (1,'RATEREV','Rate Revision / Data Updation','RATE REVISION AND MASTER DATA UPDATION - PER TPA','998314',3000,18,'one_time',0,'Data Updation','Service Income'), (1,'APPMNT','Application Maintenance (Online) + 1 Follow up','APPLICATION MAINTENANCE CHARGES (ONLINE) WITH ONE FOLLOW UP','998314',10000,18,'one_time',0,'Service','Service Income'), (1,'SEQ','Same Sequence Maintenance (OPD/IPD)','SAME SEQUENCE MAINTENANCE FOR OPD AND IPD - PER RECORD','998314',6000,18,'one_time',0,'Service','Service Income'), (1,'SRVSHIFT','Client Server Maintenance / Server Shifting','CLIENT SERVER MAINTENANCE - SERVER SHIFTING','998314',10000,18,'one_time',0,'Service','Service Income'), (1,'VISIT','Consultant Visit Charge','CONSULTANT VISIT CHARGE','998314',3000,18,'one_time',0,'Service','Service Income'), (1,'TRAIN','Onsite Training','ONSITE TRAINING - PER DAY','998314',5000,18,'one_time',0,'Service','Service Income'), (4,'CLDQ','Cloud Server Rental','CLOUD SERVER HOSTING CHARGES FOR THE PERIOD {period_from} TO {period_to}','998315',0,18,'quarterly',1,'Cloud','Cloud Rental Income'), (4,'CLDY','Cloud Server Rental (Yearly)','CLOUD SERVER HOSTING CHARGES FOR THE PERIOD {period_from} TO {period_to}','998315',0,18,'yearly',1,'Cloud','Cloud Rental Income'), (4,'CLDSSL','SSL Certificate','SSL CERTIFICATE RENEWAL FOR THE PERIOD {period_from} TO {period_to}','998315',0,18,'yearly',1,'Cloud','Cloud Rental Income'), (2,'DIPDSUB','Digital IPD Subscription','DIGITAL IPD SUBSCRIPTION - {qty} BEDS FOR THE PERIOD {period_from} TO {period_to}','998314',0,18,'yearly',1,'Digital IPD','Subscription Income'), (3,'DOPDSUB','Digital OPD Subscription','DIGITAL OPD SUBSCRIPTION FOR THE PERIOD {period_from} TO {period_to}','998314',0,18,'yearly',1,'Digital OPD','Subscription Income'), (5,'MCCAMP','Medicircle Campaign','MEDIA CAMPAIGN AS PER APPROVED PLAN','998361',0,18,'one_time',0,'Media','Media Income'); -- ---------- templates ---------- -- Placeholders: {client_name} {contact_name} {brand_name} {invoice_no} {invoice_date} {due_date} -- {period_from} {period_to} {amount} {balance} {days_overdue} {service_list} {invoice_table} -- {statement_table} {pay_link} {bank_line} {sender_name} {sender_designation} {org_phone} INSERT INTO templates (brand_id,code,channel,name,subject,body) VALUES (NULL,'INT_BILL_DUE','email','Internal - bills to be generated', 'Bill generation due in {days} days - {count} service(s)', '

Hi Team,

The following services fall due for invoicing. Raise and issue the proforma today so the client receives it before the period starts.

{service_list}

Nothing moves to the next stage until the invoice is issued.

— CareBill

'), (NULL,'INV_ISSUE','email','Invoice / proforma issued to client', '{brand_name} | Invoice {invoice_no} for the period {period_from} to {period_to}', '

Dear {contact_name},

Please find attached invoice {invoice_no} dated {invoice_date} towards {service_names} for the period {period_from} to {period_to}.

{invoice_table}

Amount payable: Rs. {amount}  |  Due date: {due_date}

Kindly release the payment on or before the due date so that services continue without interruption.

{bank_line}

For any clarification on this invoice, reply to this mail or call us on {org_phone}.

Regards,
{sender_name}
{sender_designation}
{brand_name}

'), (NULL,'DUE_SOON','email','Payment due shortly', 'Reminder | Invoice {invoice_no} due on {due_date}', '

Dear {contact_name},

This is a gentle reminder that invoice {invoice_no} for Rs. {balance} is due on {due_date}.

{invoice_table}

If the payment is already processed, please share the UTR / cheque details so we can mark it settled.

{pay_button} {bank_line}

Regards,
{sender_name}
{brand_name} Accounts

'), (NULL,'DUE_TODAY','email','Payment due today', 'Due today | Invoice {invoice_no} - Rs. {balance}', '

Dear {contact_name},

Invoice {invoice_no} for Rs. {balance} is due today, {due_date}.

{invoice_table}{pay_button} {bank_line}

Regards,
{sender_name}
{brand_name} Accounts

'), (NULL,'REM_1','email','Reminder 1 - 7 days overdue', 'Payment overdue | Invoice {invoice_no} - Rs. {balance}', '

Dear {contact_name},

Invoice {invoice_no} dated {invoice_date} for Rs. {balance} was due on {due_date} and is now {days_overdue} days overdue.

{invoice_table}

Please confirm the payment date, or tell us what is pending at your end so we can close it.

{pay_button} {bank_line}

Regards,
{sender_name}
{brand_name} Accounts

'), (NULL,'REM_2','email','Reminder 2 - 15 days overdue', 'Second reminder | Invoice {invoice_no} overdue {days_overdue} days', '

Dear {contact_name},

Despite our earlier reminder, invoice {invoice_no} for Rs. {balance} remains unpaid, now {days_overdue} days past the due date of {due_date}.

{invoice_table}

The billed period is {period_from} to {period_to} — a period during which the service has already been delivered and supported by our team.

Please release the payment this week, or share a written commitment date from your accounts department.

{pay_button} {bank_line}

Regards,
{sender_name}
{brand_name} Accounts

'), (NULL,'REM_3','email','Reminder 3 - 30 days overdue', 'Third reminder | Rs. {balance} outstanding against {client_name}', '

Dear {contact_name},

The following amount remains outstanding against your account:

{statement_table}

Total outstanding: Rs. {total_outstanding}, the oldest item pending {days_overdue} days.

This is our third written reminder. Kindly treat it as urgent — unresolved dues are escalated to management review and may affect service continuity.

{bank_line}

Regards,
{sender_name}
{brand_name} Accounts

'), (NULL,'ESCALATION','email','Escalation to client management', 'Escalation | Outstanding Rs. {total_outstanding} - {client_name}', '

Dear Sir / Madam,

We are writing to you directly as repeated reminders to the accounts team have not resulted in payment.

{statement_table}

Total outstanding: Rs. {total_outstanding}  |  oldest item {days_overdue} days overdue.

Our engineering and support teams have continued to serve the hospital through this entire period. Records of the version updates and support calls delivered are available on request.

We request settlement within 7 days. If any part of the amount is disputed, please write to us with the specific item so it can be resolved on merit rather than held against the full bill.

{bank_line}

Regards,
{sender_name}
{brand_name}

'), (NULL,'SUSPEND_WARN','email','Service suspension notice', 'Important | Service continuity for {client_name} - Rs. {total_outstanding} pending', '

Dear {contact_name},

Outstanding of Rs. {total_outstanding} against your account is now {days_overdue} days old.

{statement_table}

As per the subscription terms, services are liable to be suspended where renewal dues remain unpaid. We do not wish to take this step in a hospital environment, and would far rather agree a payment plan with you this week.

Please call us on {org_phone} today.

Regards,
{sender_name}
{brand_name}

'), (NULL,'SHORT_PAY','email','Short payment query', 'Short payment received against {invoice_no}', '

Dear {contact_name},

Thank you for the payment of Rs. {amount_received} received on {pay_date} against invoice {invoice_no} of Rs. {amount}.

A balance of Rs. {balance} remains open.

If this is a TDS deduction, please share the challan / Form 16A reference so we can adjust it and close the invoice. If it is a short payment for any other reason, do let us know the reason.

Regards,
{sender_name}
{brand_name} Accounts

'), (NULL,'PAY_ACK','email','Payment received - acknowledgement', 'Payment received with thanks - {client_name}', '

Dear {contact_name},

We acknowledge receipt of Rs. {amount_received} on {pay_date}, adjusted as below:

{invoice_table}

Thank you for the prompt settlement.

Regards,
{sender_name}
{brand_name} Accounts

'), (NULL,'RENEWAL','email','Renewal notice before period end', 'Renewal due | {service_names} for {client_name} expires on {period_to}', '

Dear {contact_name},

Your {service_names} is valid up to {period_to}.

The renewal for {next_period_from} to {next_period_to} is Rs. {amount} (inclusive of GST).

Please confirm the renewal so we can raise the invoice and keep your services continuous with no interruption to hospital operations.

Regards,
{sender_name}
{brand_name}

'), (NULL,'SOA','email','Statement of account', 'Statement of account - {client_name} as on {today}', '

Dear {contact_name},

Statement of account as on {today}:

{statement_table}

Total outstanding: Rs. {total_outstanding}

Please confirm the balance, or mark any entry you are unable to reconcile so we can sort it out.

Regards,
{sender_name}
{brand_name} Accounts

'), (NULL,'INT_ESCALATE','email','Internal - escalate to support & management', 'Internal | {client_name} - Rs. {total_outstanding} overdue {days_overdue} days', '

{client_name} has Rs. {total_outstanding} outstanding, oldest item {days_overdue} days overdue. {reminder_count} reminders sent, no payment received.

{statement_table}

Support and management to review before any further service commitment.

'), (NULL,'WA_REMINDER','whatsapp','WhatsApp payment reminder',NULL, 'Dear {contact_name}, invoice {invoice_no} of Rs. {balance} for {brand_name} was due on {due_date} and is pending. Kindly arrange payment or share the UTR if already paid. - Caresoft Accounts'), (NULL,'WA_RENEWAL','whatsapp','WhatsApp renewal reminder',NULL, 'Dear {contact_name}, your {service_names} expires on {period_to}. Renewal amount Rs. {amount}. Please confirm so we keep your services running without interruption. - Caresoft'), (NULL,'CALL_SCRIPT','call_script','Collection call script',NULL, '1. Confirm you are speaking to the person who releases payments.\n2. State: invoice {invoice_no}, Rs. {balance}, due {due_date}, now {days_overdue} days overdue.\n3. Ask one question: what is the date the payment will be released?\n4. If a dispute is raised, capture the exact item disputed - do not argue on the call.\n5. Record the commitment date in the system before ending the call.'); -- ---------- reminder rules (the dunning ladder) ---------- INSERT INTO reminder_rules (brand_id,code,name,trigger_type,offset_days,channel,template_code,audience,repeat_every_days,max_repeats,stage_no,active) VALUES (NULL,'GEN15','Bill generation alert - 15 days before','pre_generation',-15,'email','INT_BILL_DUE','internal_accounts',0,1,0,1), (NULL,'GEN07','Bill generation alert - 7 days before (unbilled)','pre_generation',-7,'email','INT_BILL_DUE','internal_accounts',0,1,0,1), (NULL,'GEN00','Bill not raised - due today','pre_generation',0,'email','INT_BILL_DUE','management',0,1,0,1), (NULL,'DUE07','Payment due in 7 days','pre_due',-7,'email','DUE_SOON','client',0,1,1,1), (NULL,'DUE00','Payment due today','on_due',0,'email','DUE_TODAY','client',0,1,2,1), (NULL,'OD07','Reminder 1 - 7 days overdue','post_due',7,'email','REM_1','client',0,1,3,1), (NULL,'OD07W','WhatsApp nudge - 10 days overdue','post_due',10,'whatsapp','WA_REMINDER','client',0,1,3,1), (NULL,'OD15','Reminder 2 - 15 days overdue','post_due',15,'email','REM_2','client',0,1,4,1), (NULL,'OD30','Reminder 3 - 30 days overdue','post_due',30,'email','REM_3','client',0,1,5,1), (NULL,'OD30C','Collection call - 30 days overdue','post_due',30,'call','CALL_SCRIPT','collector',7,4,5,1), (NULL,'OD45','Escalation to client management','post_due',45,'email','ESCALATION','client',0,1,6,1), (NULL,'OD45I','Internal escalation - support & management','post_due',45,'email','INT_ESCALATE','management',0,1,6,1), (NULL,'OD60','Service suspension notice','post_due',60,'email','SUSPEND_WARN','client',15,3,7,1), (NULL,'REN45','Renewal notice - 45 days before expiry','renewal',-45,'email','RENEWAL','client',0,1,0,1), (NULL,'REN15','Renewal follow-up - 15 days before expiry','renewal',-15,'whatsapp','WA_RENEWAL','client',0,1,0,1), (NULL,'EXP30','Contract expiry alert - internal','contract_expiry',-30,'email','INT_BILL_DUE','internal_accounts',0,1,0,1), (NULL,'SOAM','Monthly statement of account','statement',1,'email','SOA','client',0,1,0,0); -- ---------- portal templates ---------- INSERT INTO templates (code,name,channel,subject,body) VALUES ('INT_ADVICE','Internal: a client says they have paid','email', 'Payment advised: {client_name} - Rs. {amount_received}', '

{client_name} has told us through the portal that they have paid.

AmountRs. {amount_received}
Paid on{pay_date}
Reference{reference}

Do not treat this as received until it is seen in the bank statement. It is waiting under From clients.

'), ('INT_QUERY','Internal: a client has raised a query','email', '{query_type} from {client_name}', '

{client_name} has raised a {query_type} through the portal.

{subject}

{body}

A dispute pauses reminders on that invoice until somebody answers it. It is waiting under From clients.

'), ('QUERY_REPLY','Reply to a client query','email', 'Re: {subject}', '

Dear {contact_name},

About the query you raised with us:

{subject}

{reply}

You can see this in your billing portal along with your invoices and statement.

Regards,
{sender_name}
{brand_name}

'), ('PORTAL_INVITE','Invitation to the billing portal','email', 'Your billing account with {brand_name}', '

Dear {contact_name},

You can now see {client_name}''s invoices, statement of account and outstanding balance online, whenever you need them.

Open your billing account

There is no password. Enter this email address and we send a six digit code that works once.

You can also tell us about a payment you have made, which is the quickest way to have it matched to the right invoice, and raise a query if a bill looks wrong.

Regards,
{sender_name}
{brand_name}

'); -- ---------- TDS certificate chaser ---------- INSERT INTO templates (code,name,channel,subject,body) VALUES ('TDS_CERT','Request for Form 16A','email', 'Form 16A pending - {tds_quarter} - {client_name}', '

Dear {contact_name},

Our records show TDS of Rs. {tds_amount} deducted by {client_name} for {tds_quarter}, against which we have not yet received Form 16A.

The certificate was due on {tds_due}. Until it is issued and the deduction reflects in our Form 26AS, we are unable to claim this credit — so the amount sits deducted from our invoice and unavailable to us.

Could you please have your accounts team issue the certificate, or confirm the challan details if the return is already filed?

Our PAN is available on every invoice, and we will gladly share any detail your team needs.

Regards,
{sender_name}
{brand_name} Accounts

');