queries.ts

export const queries = {
  createInvoice:
    'INSERT INTO "bonadocs"."invoices" (subscription_id, reference, amount, currency, status, date_created, due_date) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id;',
  createInvoicePayment:
    'INSERT INTO "bonadocs"."invoice_payments" (invoice_id, payment_channel, payment_reference, amount, currency, date_created, status) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id;',
  createInvoiceItem:
    'INSERT INTO "bonadocs"."invoice_items" (invoice_id, resource_id, quantity, amount, currency) VALUES ($1, $2, $3, $4, $5) RETURNING id;',
  updateInvoiceStatus: 'UPDATE "bonadocs"."invoices" SET status = $2 WHERE id = $1',
  getInvoiceById:
    'SELECT i.id, i.subscription_id, i.reference, i.amount, i.currency, i.status, i.date_created, i.due_date FROM "bonadocs"."invoices" i WHERE id = $1 LIMIT 1',
  getInvoiceBySubscriptionId:
    'SELECT i.id, i.subscription_id, i.reference, i.amount, i.currency, i.status, i.date_created, i.due_date FROM "bonadocs"."invoices" i WHERE subscription_id = $1 LIMIT 1',
  updateInvoicePaymentStatus: 'UPDATE "bonadocs"."invoice_payments" SET status = $2 WHERE id = $1',
  getInvoicePaymentById:
    'SELECT ip.id, ip.invoice_id, ip.payment_channel, ip.payment_reference, ip.amount, ip.currency, ip.date_created, ip.status FROM "bonadocs"."invoice_payments" ip WHERE id = $1 LIMIT 1',
  updateInvoicePayment:
    'UPDATE "bonadocs"."invoice_payments" SET payment_chaneel = $1, payment_reference = $2 WHERE id = $3',
  getUnpaidInvoicesWithCloseDueDate:
    'SELECT i.subscription_id, i.reference, i.amount, i.currency, i.status, i.date_created, i.due_date FROM "bonadocs"."invoices" i WHERE status = \'unpaid\' AND due_date BETWEEN $1 AND $2',
  getInvoicePaymentsByInvoiceIds:
    'SELECT ip.invoice_id, ip.payment_channel, ip.payment_reference, ip.amount, ip.currency, ip.date_created, ip.status FROM "bonadocs"."invoice_payments" ip WHERE invoice_id = ANY($1)',
  getAllPlans:
    'SELECT  p.id,  p.name, p.price, p.currency, p.duration, p.active, p.date_created FROM "bonadocs"."plans" AS p',
  getPlanById:
    'SELECT id, name, price, currency, duration, active, date_created, metadata FROM "bonadocs"."plans" WHERE id = $1 LIMIT 1',
  updateSubscriptionMetadata: 'UPDATE "bonadocs"."subscriptions" SET metadata = $2 WHERE id = $1',
  updateInvoicePaymentMetadata:
    'UPDATE "bonadocs"."invoice_payments" SET metadata = $2 WHERE id = $1',
  createProjectSubscription:
    'INSERT INTO "bonadocs"."subscriptions" (project_id, plan_id, metadata, status, date_expires) VALUES ($1, $2, $3, $4, $5) RETURNING id',
  updateSubscriptionStatus: 'UPDATE "bonadocs"."subscriptions" SET status = $2 WHERE id = $1',
  insertSubscriptionResources:
    'INSERT INTO "bonadocs"."subscription_resources" (subscription_id, resource_id, quantity, usage, overages_supported, overages_price, overages_bundle_count) VALUES ($1, $2, $3, $4, $5, $6, $7)',
  getPlanResources:
    'SELECT id, plan_id, resource_id, quantity, overages_supported, overages_price, overages_bundle_count FROM "bonadocs"."plan_resources" WHERE plan_id = $1',
  getProjectActiveSubscriptionId:
    'SELECT id FROM "bonadocs"."subscriptions" WHERE project_id = $1 AND status = $2 LIMIT 1',
  getProjectActiveSubscription:
    'SELECT id, project_id, plan_id, metadata, status, date_expires FROM "bonadocs"."subscriptions" WHERE project_id = $1 AND status = $2 LIMIT 1',
  getProjectSubscriptionByProjectIdAndPlanId:
    'SELECT id, project_id, plan_id, metadata, status, date_expires FROM "bonadocs"."subscriptions" WHERE project_id = $1 AND plan_id = $2 LIMIT 1',
  getProjectLastSubscription:
    'SELECT id, project_id, plan_id, metadata, status, date_expires FROM "bonadocs"."subscriptions" WHERE project_id = $1 ORDER BY date_created DESC LIMIT 1',
  getSubscriptionById:
    'SELECT id, project_id, plan_id, metadata, status, date_expires FROM "bonadocs"."subscriptions" WHERE id = $1 LIMIT 1',
  getInvoicePaymentByStatus:
    'SELECT ip.id, ip.invoice_id, ip.payment_channel, ip.payment_reference, ip.amount, ip.currency, ip.date_created, ip.status, ip.metadata FROM "bonadocs"."invoice_payments" ip where status = $1',
  getAlmostDueSubscriptions: `
      SELECT s.id,
             s.project_id,
             s.plan_id,
             s.status,
             s.date_created,
             s.date_expires,
      FROM "bonadocs"."subscriptions" s
        AND s.date_expires > $1
        AND s.date_expires < $2`,
  getDueSubscriptions: `
      SELECT s.id,
             s.project_id,
             s.plan_id,
             s.status,
             s.date_created,
             s.date_expires,
      FROM "bonadocs"."subscriptions" s
        AND s.date_expires > $1 AND s.status = $2`,
};