= Functions, Procedures and Triggers =

== Triggers ==

=== 1. trg_schedule_next_vaccination_appointment ===

This trigger fires after a new row is inserted into `treatment_attribute_value`. It only acts when the inserted attribute is `date_next` on a treatment whose type is `vaccination` — every other attribute insert is ignored. When those conditions are met, the trigger reads the pet, owner and attending employee from the vaccination's original examination, and automatically books a follow-up visit: a new `appointment` (dated today, marked as auto-scheduled) and a linked `examination` with `status = 'scheduled'`, dated for the recorded `date_next`. This removes the need for staff to manually track and re-book every pet's next vaccine dose.

=== 2. trg_prevent_double_booking ===

This trigger fires before an `examination` row is inserted or updated. Its purpose is to stop the same employee from being assigned to two different examinations in the same room on the same date. Before allowing the insert/update, it checks for any other non-cancelled examination that shares the same employee, the same room and the same date (excluding the row being updated, so an update to an existing row doesn't conflict with itself). If a conflict is found, the operation is blocked and a descriptive error is raised naming the employee, the room and the id of the conflicting examination. Cancelled examinations are ignored by the check, since they no longer occupy the slot.

=== 3. trg_cancel_examinations_on_pet_deactivation ===

This trigger fires after a `pet` row is updated, and only runs its logic when `is_active` transitions from `true` to `false` (e.g. the pet has passed away, was surrendered, or moved to another clinic). When that transition happens, the trigger automatically cancels every future examination for that pet that is still in `scheduled` status and dated today or later — there is no longer a reason to hold a room/employee slot for an inactive pet. Completed examinations, already-cancelled examinations, and anything dated in the past are left untouched, so the pet's historical record stays intact. Toggling `is_active` back to `true`, or any update that doesn't change `is_active`, does not trigger this logic.

=== 4. trg_validate_coupon_on_invoice ===

This trigger fires before an `invoice` row is inserted, and only runs when the invoice carries a `coupon_id`. It verifies that the coupon is actually usable: it must be active, the invoice date must fall within the coupon's valid date range, its usage count must still be below its usage limit, and the invoice's amount must meet the coupon's minimum spend requirement. If any of these checks fail, the insert is rejected with a specific error explaining which condition wasn't met. If all checks pass, the coupon's usage count is incremented as part of the same operation, so validation and redemption happen atomically. The amount checked at insert time is expected to be the pre-discount subtotal — the invoice's final, discounted total is set afterwards in a separate update that does not re-trigger this check, since redemption is meant to happen once, at the moment the coupon is applied.

== Functions ==

=== 1. fn_get_owner_total_spent(p_owner_id) ===

This function returns the total amount an owner has paid across all of their invoices. It sums every `payment.amount` linked (via `invoice`) to the given owner, returning 0.00 if the owner has no payments on record. It is meant to be called wherever an owner's overall spend needs to be shown or evaluated, for example for loyalty tracking or account summaries, without duplicating the underlying join logic each time.

=== 2. fn_is_examination_room_available(p_room_id, p_date) ===

This function checks whether a given examination room is free on a given date. It returns `true` only if there is no examination in that room, on that date, with status `scheduled` or `completed` — cancelled examinations do not count as occupying the room. It is designed to be called from scheduling logic before assigning a room to a new examination, to check availability without repeating the underlying query everywhere it's needed.

== Procedures ==

=== 1. sp_generate_invoice(p_owner_id, p_coupon_code) ===

This procedure generates a single invoice covering every treatment belonging to an owner that has not yet been invoiced. It first resolves the given coupon code to a coupon, if one was provided: if the code doesn't correspond to any real coupon, this is not treated as an error — a notice is raised and the invoice proceeds without a discount. It then collects all uninvoiced treatments for the owner (a treatment counts as uninvoiced if no `invoice_item` row already references it) and computes their combined subtotal. The invoice is inserted with this subtotal as its total, which is the point at which `trg_validate_coupon_on_invoice` checks and redeems the coupon if one was supplied; if the coupon turns out to be invalid (expired, inactive, exhausted, or below its minimum spend), the whole procedure is rolled back and nothing is committed. One `invoice_item` line is then created per treatment. Finally, if a valid coupon was applied, the discount is calculated and the invoice's total is updated to the final, discounted amount. If the owner has no uninvoiced treatments at all, the procedure raises an error instead of creating an empty invoice.

=== 2. sp_process_payment(p_invoice_id, p_amount, p_method) ===

This procedure records a payment against an invoice, and is designed to be called repeatedly for the same invoice to support paying in multiple instalments over time. Each call first confirms the invoice exists, then calculates the invoice's remaining balance as its total minus everything already paid. The payment is rejected if the amount given is zero or negative, if the invoice is already fully paid, or if the amount would exceed what is still owed — overpayment is not allowed or clamped, since the system has no concept of a credit balance to absorb the difference. If the payment is valid, it is inserted and the procedure returns, through output parameters, the new payment's id, the invoice's remaining balance after this payment, and whether the invoice is now fully paid.

== Script ==
[https://develop.finki.ukim.mk/projects/pawcare/attachment/wiki/DatabaseProgramming/console_2.sql]