module.exports = {
  // Insert a single billing item
  insertBill: `
      INSERT INTO bills (
        patient_id, session_id, inventory_id, item_name, quantity, unit_price, amount_price, item_type, rx_time, billed_by
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `,

  // Insert billing summary
  insertSummary: `
   INSERT INTO billing_summary (
  patient_id,
  session_id,
  rx_time,
  total_bill,
  vat_rate,
  discount_type,
  discount_rate,
  vat_amount,
  discount_amount,
  net_payable,
  status,
  total_paid,
  balance_remaining
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
 `,

  // Fetch bills for a session and rx_time
  getBillsBySessionAndTime: `
      SELECT * FROM bills 
      WHERE patient_id = ? AND session_id = ? AND rx_time = ?
      ORDER BY created_at ASC
    `,

  // Fetch ALL bills for a session (optional)
  getAllBillsBySession: `
      SELECT * FROM bills 
      WHERE patient_id = ? AND session_id = ?
      ORDER BY rx_time ASC, created_at ASC
    `,

  // Delete billing summary (for overwrite, if needed)
  deleteSummary: `
      DELETE FROM billing_summary 
      WHERE patient_id = ? AND session_id = ? AND rx_time = ?
    `,

  // Recalculate total for a rx_time
  getTotalByBillingTime: `
      SELECT SUM(amount_price) AS total 
      FROM bills 
      WHERE patient_id = ? AND session_id = ? AND rx_time = ?
    `,

  // Fetch billing summaries for a patient (optional)
  getBillingSummaries: `
      SELECT * FROM billing_summary 
      WHERE patient_id = ? 
      ORDER BY rx_time DESC
    `,
  getBillingSummariesPaginated: `
  SELECT *
  FROM billing_summary
  WHERE 1=1
    {{statusFilter}}
    {{dateFilter}}
    {{patientFilter}}
    {{sessionFilter}}
  ORDER BY rx_time DESC
  LIMIT ? OFFSET ?
`,
};
