module.exports = {
  // Insert a new payment
  insertPayment: `
    INSERT INTO payments (
      patient_id,
      rx_time,
      amount_paid,
      paid_for,
      bill_reference,
      payment_method,
      bank_name,
      payer_type,
      confirmed_by,
      rx_session
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `,

  // Fetch all payments (paginated version optional)
  getAllPayments: `
    SELECT * FROM payments
    ORDER BY date_time DESC
    LIMIT ? OFFSET ?
  `,

  // Fetch by patient ID
  getPaymentsByPatientId: `
    SELECT * FROM payments
    WHERE patient_id = ?
    ORDER BY date_time DESC
  `,

  // Get total paid for a specific bill
  getTotalPaidByBillReference: `
    SELECT SUM(amount_paid) as total_paid
    FROM payments
    WHERE bill_reference = ?
  `,

  // Check duplicate payment for same bill/session/time
  checkDuplicatePayment: `
    SELECT * FROM payments
    WHERE patient_id = ? AND rx_time = ? AND rx_session = ?
  `,

  // Summary: total amount by payer type
  getPaymentSummaryByPayerType: `
    SELECT payer_type, SUM(amount_paid) as total
    FROM payments
    GROUP BY payer_type
  `,

  // Search payments (flexible search)
  searchPayments: `
    SELECT * FROM payments
    WHERE 
      patient_id LIKE ? OR
      bill_reference LIKE ? OR
      payment_method LIKE ? OR
      bank_name LIKE ?
    ORDER BY date_time DESC
    LIMIT ? OFFSET ?
  `,
};
