InvoiceDao.sql.stg

group InvoiceDao;

getInvoicesByAccount() ::= <<
  SELECT id, account_id, invoice_date, target_date, currency, amount_paid, last_payment_attempt
  FROM invoices
  WHERE account_id = :accountId
  ORDER BY invoice_date ASC;
>>

getInvoicesBySubscription() ::= <<
  SELECT i.id, i.account_id, i.invoice_date, i.target_date, i.currency, i.amount_paid, i.last_payment_attempt
  FROM invoices i
  INNER JOIN invoice_items ii ON i.id = ii.invoice_id
  WHERE ii.subscription_id = :subscriptionId;
>>

getInvoicesForPayment() ::= <<
  SELECT i.id
  FROM invoices i
  INNER JOIN invoice_items ii ON i.id = ii.invoice_id
  WHERE DATEDIFF(:targetDate, i.last_payment_attempt) >= :numberOfDays OR (i.last_payment_attempt IS NULL)
  GROUP BY i.id, i.amount_paid
  HAVING SUM(ii.amount) > (i.amount_paid);
>>

getById() ::= <<
  SELECT id, account_id, invoice_date, target_date, currency, amount_paid, last_payment_attempt
  FROM invoices
  WHERE id = :id;
>>

save() ::= <<
  INSERT INTO invoices(id, account_id, invoice_date, target_date, currency, amount_paid, last_payment_attempt)
  VALUES (:id, :accountId, :invoiceDate, :targetDate, :currency, :amountPaid, :lastPaymentAttempt)
  ON DUPLICATE KEY UPDATE
    invoice_date = :invoiceDate, target_date = :targetDate, currency = :currency,
    amount_paid = :amountPaid, last_payment_attempt = :lastPaymentAttempt;
>>

notifySuccessfulPayment() ::= <<
  UPDATE invoices
  SET amount_paid = amount_paid + :paymentAmount, last_payment_attempt = :paymentDate
  WHERE id = :id;
>>

notifyFailedPayment() ::= <<
  UPDATE invoices
  SET last_payment_attempt = :paymentAttemptDate
  WHERE id = :id;
>>

test() ::= <<
  SELECT 1
  FROM invoices;
>>
;