DatabaseProgramming: console_2.sql

-- ============================================================
-- Trigger1: trg_schedule_next_vaccination_appointment
--
-- Use case: when a vaccination treatment's 'date_next' attribute
--   is recorded (the date the pet is due for its next dose), the
--   clinic automatically books a follow-up: a new 'appointment'
--   and a linked 'examination' (actual scheduled slot, for date_next).
-- ============================================================

CREATE OR REPLACE FUNCTION fn_schedule_next_vaccination_appointment()
RETURNS TRIGGER AS $$
DECLARE
    v_attribute_name     varchar(255);
    v_treatment_type     varchar(255);
    v_examination_id     int4;
    v_pet_id             int4;
    v_owner_id           int4;
    v_owner_phone        varchar(255);
    v_employee_id        int4;
    v_date_next          date;
    v_new_appointment_id int4;
BEGIN
    -- only act on the 'date_next' attribute
    SELECT ta.name
    INTO v_attribute_name
    FROM treatment_attribute ta
    WHERE ta.id = NEW.treatment_attribute_id;

    IF v_attribute_name IS DISTINCT FROM 'date_next' THEN
        RETURN NEW;
    END IF;

    -- confirm treatment is a vaccination, save its examination_id
    SELECT tt.name, t.examination_id
    INTO v_treatment_type, v_examination_id
    FROM treatment t
    JOIN treatment_type tt ON tt.id = t.treatment_type_id
    WHERE t.id = NEW.treatment_id;

    IF v_treatment_type IS DISTINCT FROM 'vaccination' THEN
        RETURN NEW;
    END IF;

    -- value is stored as text (EAV pattern), cast to date
    v_date_next := NEW.value::date;

    -- pet/owner/employee context from the current examination -> appointment
    SELECT a.pet_id, a.owner_id, o.phone, e.employee_id
    INTO v_pet_id, v_owner_id, v_owner_phone, v_employee_id
    FROM examination e
    JOIN appointment a ON a.id = e.appointment_id
    JOIN owner o ON o.id = a.owner_id
    WHERE e.id = v_examination_id;

    -- new appointment (request): dated today
    INSERT INTO appointment (date_appointment, reason, phone, owner_id, pet_id)
    VALUES (CURRENT_DATE, 'Auto-scheduled: next vaccine dose', v_owner_phone, v_owner_id, v_pet_id)
    RETURNING id INTO v_new_appointment_id;

    -- new examination (scheduled slot): dated for date_next
    INSERT INTO examination (date_examination, status, description, appointment_id, employee_id, examination_room_id)
    VALUES (v_date_next, 'scheduled', 'Follow-up vaccination dose', v_new_appointment_id, v_employee_id, NULL);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_schedule_next_vaccination_appointment
AFTER INSERT ON treatment_attribute_value
FOR EACH ROW
EXECUTE FUNCTION fn_schedule_next_vaccination_appointment();

SELECT id, name, data_type, treatment_type_id
FROM treatment_attribute
WHERE name = 'date_next';

SELECT t.id, t.date_treatment, e.id AS examination_id, e.employee_id
FROM treatment t
JOIN examination e ON e.id = t.examination_id
WHERE t.treatment_type_id = 2
LIMIT 5;

SELECT count(*) FROM appointment WHERE pet_id = (
    SELECT a.pet_id FROM appointment a
    JOIN examination e ON e.appointment_id = a.id
    JOIN treatment t ON t.examination_id = e.id
    WHERE t.id = 1255
);

INSERT INTO treatment_attribute_value (treatment_id, treatment_attribute_id, value)
VALUES (1255, 12, '2026-12-01');

SELECT * FROM appointment
WHERE reason = 'Auto-scheduled: next vaccine dose'
ORDER BY id DESC LIMIT 1;

SELECT * FROM examination
WHERE description = 'Follow-up vaccination dose'
ORDER BY id DESC LIMIT 1;

-- non-date_next attribute id, e.g. adverse_reaction (13), to test guard
INSERT INTO treatment_attribute_value (treatment_id, treatment_attribute_id, value)
VALUES (1568, 13, 'false');



-- ============================================================
-- Trigger2: trg_prevent_double_booking
--
-- Use case: before inserting or updating an examination, check
--   if the assigned employee is already scheduled in the same
--   examination room on the same date. If a conflict exists,
--   the insert/update is blocked with a descriptive error.
--   This prevents two examinations being assigned to the same
--   employee and room at the same time.
--
-- Returns: raises an exception if a conflict is detected,
--   otherwise allows the operation to proceed (RETURN NEW).
-- ============================================================

CREATE OR REPLACE FUNCTION fn_prevent_double_booking()
RETURNS TRIGGER AS $$
DECLARE
    v_conflict_id   int4;
    v_employee_name text;
    v_room_number   varchar(255);
BEGIN
    -- only check when employee and room are both assigned
    IF NEW.employee_id IS NULL OR NEW.examination_room_id IS NULL THEN
        RETURN NEW;
    END IF;

    -- skip cancelled examinations
    IF NEW.status = 'cancelled' THEN
        RETURN NEW;
    END IF;

    -- look for any existing non-cancelled examination
    -- on the same date, same employee, same room
    -- excluding the current row on UPDATE (NEW.id)
    SELECT e.id
    INTO v_conflict_id
    FROM examination e
    WHERE e.employee_id        = NEW.employee_id
      AND e.examination_room_id = NEW.examination_room_id
      AND e.date_examination    = NEW.date_examination
      AND e.status             != 'cancelled'
      AND e.id                 IS DISTINCT FROM NEW.id  -- exclude self on UPDATE
    LIMIT 1;

    IF v_conflict_id IS NOT NULL THEN
        -- create error message
        SELECT emp.first_name || ' ' || emp.last_name
        INTO v_employee_name
        FROM employee emp
        WHERE emp.id = NEW.employee_id;

        SELECT er.room_number
        INTO v_room_number
        FROM examination_room er
        WHERE er.id = NEW.examination_room_id;

        RAISE EXCEPTION
            'Double booking conflict: % is already assigned to room % on %. '
            'Conflicting examination id: %.',
            v_employee_name,
            v_room_number,
            NEW.date_examination,
            v_conflict_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_double_booking
    BEFORE INSERT OR UPDATE
    ON examination
    FOR EACH ROW
EXECUTE FUNCTION fn_prevent_double_booking();


-- ============================================================
-- TEST
-- ============================================================

-- existing examination
SELECT
    e.id,
    e.date_examination,
    e.employee_id,
    e.examination_room_id,
    e.status
FROM examination e
WHERE e.status != 'cancelled'
  AND e.employee_id IS NOT NULL
  AND e.examination_room_id IS NOT NULL
ORDER BY e.id
LIMIT 5;

-- any appointment that has no examination yet
SELECT a.id
FROM appointment a
LEFT JOIN examination e ON e.appointment_id = a.id
WHERE e.id IS NULL;

-- conflicting insert (should fail)
INSERT INTO examination (date_examination, status, description, appointment_id, employee_id, examination_room_id)
VALUES (
    '2023-09-01',          -- same date as existing examination
    'scheduled',
    'Test insert — should be blocked by trigger',
    809019,             -- a free appointment
    5,             -- same employee as existing examination
    7            -- same room as existing examination
);
-- ERROR: Double booking conflict: BIANKA BARRIE is already assigned to room 7 on 2023-09-01. Conflicting examination id: 694.


-- different employee or a different date (no conflict, should pass)
INSERT INTO examination (date_examination, status, description, appointment_id, employee_id, examination_room_id)
VALUES (
    '2023-09-01',          -- same date
    'scheduled',
    'Test insert — should SUCCEED (different employee)',
    809019,
    1,       -- different employee, no conflict
    7
);


-- ============================================================
-- Trigger3: trg_cancel_examinations_on_pet_deactivation
--
-- Use case: when a pet's 'is_active' flag transitions from true
--   to false (e.g. pet passed away, owner surrendered it, pet
--   moved clinics), automatically cancel every future 'scheduled'
--   examination for that pet, since it no longer makes sense to
--   hold a room/employee slot for an inactive pet.
--
-- Scope: only examinations that are still 'scheduled' and whose
--   date_examination is today or later are cancelled. Completed
--   or already-cancelled examinations, and anything in the past,
--   are left untouched (historical record integrity).
-- ============================================================

CREATE OR REPLACE FUNCTION fn_cancel_examinations_on_pet_deactivation()
RETURNS TRIGGER AS $$
DECLARE
    v_cancelled_count int4;
BEGIN
    -- only act on an active -> inactive transition
    IF NOT (OLD.is_active = true AND NEW.is_active = false) THEN
        RETURN NEW;
    END IF;

    WITH cancelled AS (
        UPDATE examination e
        SET status = 'cancelled'
        FROM appointment a
        WHERE e.appointment_id = a.id
          AND a.pet_id = NEW.id
          AND e.status = 'scheduled'
          AND e.date_examination >= CURRENT_DATE
        RETURNING e.id
    )
    SELECT count(*) INTO v_cancelled_count FROM cancelled;

    IF v_cancelled_count > 0 THEN
        RAISE NOTICE 'Pet id % deactivated: % future scheduled examination(s) cancelled.',
            NEW.id, v_cancelled_count;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_cancel_examinations_on_pet_deactivation
    AFTER UPDATE OF is_active ON pet
    FOR EACH ROW
    WHEN (OLD.is_active = true AND NEW.is_active = false)
EXECUTE FUNCTION fn_cancel_examinations_on_pet_deactivation();


-- ============================================================
-- TEST
-- ============================================================

-- find a pet that currently has a future 'scheduled' examination
SELECT
    p.id   AS pet_id,
    p.name,
    p.is_active,
    e.id   AS examination_id,
    e.date_examination,
    e.status
FROM pet p
JOIN appointment a ON a.pet_id = p.id
JOIN examination e ON e.appointment_id = a.id
WHERE p.is_active = true
  AND e.status = 'scheduled'
  AND e.date_examination >= CURRENT_DATE
ORDER BY p.id
LIMIT 5;

-- deactivate that pet
UPDATE pet
SET is_active = false
WHERE id = 1211;

-- examination(s) should now be 'cancelled'
SELECT id, date_examination, status
FROM examination
WHERE appointment_id IN (
    SELECT id FROM appointment WHERE pet_id = 1211
) and date_examination >= CURRENT_DATE
ORDER BY date_examination;

-- past examinations for the same pet remain untouched
SELECT id, date_examination, status
FROM examination
WHERE appointment_id IN (
    SELECT id FROM appointment WHERE pet_id = 1211
)
  AND date_examination < CURRENT_DATE;

-- no-op check: toggling is_active from false -> true (or true -> true)
-- does NOT fire the trigger
UPDATE pet SET is_active = true WHERE id = 1211;


-- ============================================================
-- Trigger4: trg_validate_coupon_on_invoice
--
-- Use case: before an invoice carrying a coupon is inserted,
--   verify the coupon is actually usable:
--     - is_active = true
--     - date_invoice falls within [valid_from, valid_to]
--     - usage_count < usage_limit (still has redemptions left)
--     - NEW.total >= coupon.min_total
--   If any check fails, the insert is rejected. If all checks
--   pass, usage_count is incremented in the same operation.
--
-- Note: NEW.total at INSERT time is the pre-discount
--   subtotal, not the final discounted amount, this is what
--   min_total is meant to gate. Callers (sp_generate_invoice)
--   insert with total = subtotal, let this trigger validate and
--   redeem the coupon, then issue a separate UPDATE afterwards
--   to apply the discount and set the real total. That UPDATE
--   does not re-fire this trigger (BEFORE INSERT only),
--   validation/redemption is a one-time event tied
--   to the insert
-- ============================================================

CREATE OR REPLACE FUNCTION fn_validate_coupon_on_invoice()
RETURNS TRIGGER AS $$
DECLARE
    v_coupon coupon%ROWTYPE;
BEGIN
    IF NEW.coupon_id IS NULL THEN
        RETURN NEW;
    END IF;

    SELECT *
    INTO v_coupon
    FROM coupon
    WHERE id = NEW.coupon_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Coupon id % does not exist.', NEW.coupon_id;
    END IF;

    IF v_coupon.is_active = false THEN
        RAISE EXCEPTION 'Coupon % is not active.', v_coupon.code;
    END IF;

    IF NEW.date_invoice < v_coupon.valid_from OR NEW.date_invoice > v_coupon.valid_to THEN
        RAISE EXCEPTION
            'Coupon % is not valid on %. Valid window: % to %.',
            v_coupon.code, NEW.date_invoice, v_coupon.valid_from, v_coupon.valid_to;
    END IF;

    IF v_coupon.usage_count >= v_coupon.usage_limit THEN
        RAISE EXCEPTION
            'Coupon % has reached its usage limit (% / %).',
            v_coupon.code, v_coupon.usage_count, v_coupon.usage_limit;
    END IF;

    IF NEW.total < COALESCE(v_coupon.min_total, 0) THEN
        RAISE EXCEPTION
            'Invoice subtotal % is below coupon % minimum spend of %.',
            NEW.total, v_coupon.code, v_coupon.min_total;
    END IF;

    UPDATE coupon
    SET usage_count = usage_count + 1
    WHERE id = v_coupon.id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_coupon_on_invoice
    BEFORE INSERT ON invoice
    FOR EACH ROW
    WHEN (NEW.coupon_id IS NOT NULL)
EXECUTE FUNCTION fn_validate_coupon_on_invoice();


-- ============================================================
-- Function1: fn_get_owner_total_spent
--
-- Returns the total amount paid by an owner across all invoices.
-- ============================================================

CREATE OR REPLACE FUNCTION fn_get_owner_total_spent(p_owner_id int4)
    RETURNS numeric(10, 2) AS
$$
DECLARE
    v_total_spend numeric(10, 2);
BEGIN
    SELECT COALESCE(SUM(p.amount), 0.00)
    INTO v_total_spend
    FROM payment p
    JOIN invoice i
        ON i.id = p.invoice_id
    WHERE i.owner_id = p_owner_id;

    RETURN v_total_spend;
END;
$$ LANGUAGE plpgsql;

-- Test
SELECT fn_get_owner_total_spent(1);


-- ============================================================
-- Function2: fn_is_examination_room_available
--
-- Checks whether an examination room is available on a given date.
-- Returns FALSE if there is a scheduled or completed examination.
-- ============================================================

CREATE OR REPLACE FUNCTION fn_is_examination_room_available(
    p_room_id int4,
    p_date date
)
    RETURNS boolean AS
$$
BEGIN
    RETURN NOT EXISTS (
        SELECT 1
        FROM examination e
        WHERE e.examination_room_id = p_room_id
          AND e.date_examination = p_date
          AND e.status IN ('scheduled', 'completed')
    );
END;
$$ LANGUAGE plpgsql;

-- Test
SELECT fn_is_examination_room_available(1, CURRENT_DATE);
SELECT fn_is_examination_room_available(4, '2026-09-14');
SELECT
    er.id AS room_id,
    CURRENT_DATE AS date
FROM examination_room er
WHERE fn_is_examination_room_available(er.id, CURRENT_DATE);



-- ============================================================
-- Procedure1: sp_generate_invoice
--
-- Collects every treatment linked (via examination -> appointment)
-- to p_owner_id that has not yet been invoiced (no invoice_item
-- row references it), creates one invoice, one invoice_item line
-- per treatment, optionally applies a coupon, and sets the final
-- discounted total.
--
-- Coupon handling:
--   - p_coupon_code is resolved to a coupon_id BEFORE the insert.
--   - If the code doesn't exist: NOT an error. A NOTICE is raised
--     and the invoice proceeds as if no coupon was given.
--   - If the code exists but is expired/inactive/exhausted/under
--     min_total: trg_validate_coupon_on_invoice raises an
--     EXCEPTION when we INSERT INTO invoice, and the whole
--     procedure (including any invoice_item work already done)
--     rolls back. Nothing is left half-committed.
-- ============================================================

CREATE OR REPLACE PROCEDURE sp_generate_invoice(
    p_owner_id    int4,
    p_coupon_code varchar(255) DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_invoice_id   int4;
    v_coupon       coupon%ROWTYPE;
    v_coupon_id    int4 := NULL;
    v_subtotal     numeric(10,2);
    v_final_total  numeric(10,2);
    v_line_count   int4;
BEGIN
    -- resolve coupon code -> id (silent fallback on miss)
    IF p_coupon_code IS NOT NULL THEN
        SELECT * INTO v_coupon FROM coupon WHERE code = p_coupon_code;

        IF NOT FOUND THEN
            RAISE NOTICE 'Coupon code "%" not found — generating invoice without a coupon.',
                p_coupon_code;
            v_coupon_id := NULL;
        ELSE
            v_coupon_id := v_coupon.id;
        END IF;
    END IF;

    -- confirm there's something to invoice, compute subtotal
    SELECT
        count(*),
        SUM(
            CASE tt.name
                WHEN 'prescription' THEN 35.00
                WHEN 'vaccination'  THEN 30.00
                WHEN 'consultation' THEN 40.00
                WHEN 'operation'    THEN 400.00
                ELSE 0.00
            END
        )
    INTO v_line_count, v_subtotal
    FROM treatment t
    JOIN treatment_type tt ON tt.id = t.treatment_type_id
    JOIN examination e     ON e.id = t.examination_id
    JOIN appointment a     ON a.id = e.appointment_id
    WHERE a.owner_id = p_owner_id
      AND NOT EXISTS (
          SELECT 1 FROM invoice_item ii
          WHERE ii.treatment_id = t.id AND ii.type = 'treatment'
      );

    IF v_line_count IS NULL OR v_line_count = 0 THEN
        RAISE EXCEPTION 'No uninvoiced treatments found for owner id %.', p_owner_id;
    END IF;

    -- insert invoice with the SUBTOTAL (pre-discount)
    --    trg_validate_coupon_on_invoice fires here:
    --      - validates active / not expired / under usage_limit
    --      - validates v_subtotal >= coupon.min_total
    --      - increments coupon.usage_count
    --      - RAISEs and rolls back the whole CALL if invalid
    INSERT INTO invoice (date_invoice, total, coupon_id, owner_id)
    VALUES (CURRENT_DATE, v_subtotal, v_coupon_id, p_owner_id)
    RETURNING id INTO v_invoice_id;

    -- one invoice_item line per uninvoiced treatment
    --    num_item is set by trg_generate_num_item
    INSERT INTO invoice_item (num_item, invoice_id, price, quantity, type, treatment_id)
    SELECT
        1,
        v_invoice_id,
        CASE tt.name
            WHEN 'prescription' THEN 35.00
            WHEN 'vaccination'  THEN 30.00
            WHEN 'consultation' THEN 40.00
            WHEN 'operation'    THEN 400.00
            ELSE 0.00
        END,
        1,
        'treatment',
        t.id
    FROM treatment t
    JOIN treatment_type tt ON tt.id = t.treatment_type_id
    JOIN examination e     ON e.id = t.examination_id
    JOIN appointment a     ON a.id = e.appointment_id
    WHERE a.owner_id = p_owner_id
      AND NOT EXISTS (
          SELECT 1 FROM invoice_item ii
          WHERE ii.treatment_id = t.id AND ii.type = 'treatment'
      );

    -- apply the coupon discount and update to the final total
    --    this UPDATE does NOT re-fire the coupon trigger (BEFORE INSERT only) — validation/redemption already done
    IF v_coupon_id IS NOT NULL THEN
        v_final_total := ROUND(
            CASE v_coupon.type
                WHEN 'fixed'      THEN GREATEST(v_subtotal - v_coupon.value, 0)
                WHEN 'percentage' THEN v_subtotal * (1 - v_coupon.value / 100)
            END,
            2
        );

        UPDATE invoice SET total = v_final_total WHERE id = v_invoice_id;
    END IF;

    RAISE NOTICE 'Invoice id % generated for owner id % — % line(s), subtotal %, final total %.',
        v_invoice_id, p_owner_id, v_line_count, v_subtotal, COALESCE(v_final_total, v_subtotal);
END;
$$;


-- ============================================================
-- TEST
-- ============================================================

-- owner with uninvoiced treatments
SELECT a.owner_id, count(*) AS uninvoiced
FROM treatment t
JOIN examination e ON e.id = t.examination_id
JOIN appointment a ON a.id = e.appointment_id
WHERE NOT EXISTS (
    SELECT 1 FROM invoice_item ii
    WHERE ii.treatment_id = t.id AND ii.type = 'treatment'
)
GROUP BY a.owner_id
ORDER BY uninvoiced DESC
LIMIT 5;

-- no coupon
CALL sp_generate_invoice(740, NULL);
-- Invoice id 775850 generated for owner id 1 — 1 line(s), subtotal 30.00, final total 30.00.

-- valid coupon
SELECT code FROM coupon
WHERE is_active = true AND usage_count < usage_limit;

-- ============================================================
-- TEST DATA — new uninvoiced treatments
-- Creates 15 treatments for random owners.
-- Uses existing completed examinations.
-- ============================================================

INSERT INTO treatment (
    date_treatment,
    notes,
    treatment_type_id,
    examination_id
)
SELECT
    e.date_examination + (floor(random() * 3))::int AS date_treatment,

    CASE tt.name
        WHEN 'prescription' THEN
            'TEST: Prescription created for invoice generation testing.'
        WHEN 'vaccination' THEN
            'TEST: Vaccination created for invoice generation testing.'
        WHEN 'consultation' THEN
            'TEST: Consultation created for invoice generation testing.'
        WHEN 'operation' THEN
            'TEST: Operation created for invoice generation testing.'
    END AS notes,

    tt.id AS treatment_type_id,
    e.id AS examination_id

FROM (
    -- 15 random completed examinations.
    SELECT e.id
    FROM examination e
    JOIN appointment a ON a.id = e.appointment_id
    WHERE e.status = 'completed'
      AND a.owner_id IS NOT NULL
    ORDER BY random()
    LIMIT 15
) selected_exams

JOIN examination e
    ON e.id = selected_exams.id

CROSS JOIN LATERAL (
    SELECT id, name
    FROM treatment_type
    ORDER BY random()
    LIMIT 1
) tt;

-- newly inserted, uninvoiced treatments:
SELECT
    t.id AS treatment_id,
    a.owner_id,
    tt.name AS treatment_type,
    t.date_treatment,
    e.id AS examination_id,
    t.notes
FROM treatment t
JOIN treatment_type tt
    ON tt.id = t.treatment_type_id
JOIN examination e
    ON e.id = t.examination_id
JOIN appointment a
    ON a.id = e.appointment_id
WHERE t.notes LIKE 'TEST:%'
ORDER BY a.owner_id, t.id;

CALL sp_generate_invoice(269, 'HAA-007');
-- Invoice id 775851 generated for owner id 209 — 1 line(s), subtotal 35.00, final total 19.70.

-- bad code: should NOTICE and still succeed without a coupon
CALL sp_generate_invoice(695, 'NOTAREALCODE');
-- Coupon code "NOTAREALCODE" not found — generating invoice without a coupon.
-- Invoice id 775852 generated for owner id 3687 — 1 line(s), subtotal 35.00, final total 35.00.

-- owner with nothing left to invoice: should raise
CALL sp_generate_invoice(1, NULL);


-- ============================================================
-- Procedure2: sp_process_payment
--
-- Records a payment against an invoice, supporting
-- multiple payments over time. Each call:
--   1. Validates the invoice exists.
--   2. Computes the current remaining balance:
--        invoice.total - SUM(existing payment.amount)
--   3. Rejects the call if:
--        - p_amount <= 0
--        - the invoice is already fully paid (balance = 0)
--        - p_amount would overpay the invoice (amount > balance)
--   4. Inserts the payment row.
--   5. Returns (via OUT params) the new remaining balance and
--      whether the invoice is now fully paid.
-- ============================================================

CREATE OR REPLACE PROCEDURE sp_process_payment(
    p_invoice_id        int4,
    p_amount            numeric(10,2),
    p_method            varchar(255),
    OUT p_payment_id        int4,
    OUT p_remaining_balance numeric(10,2),
    OUT p_fully_paid        boolean
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_invoice_total  numeric(10,2);
    v_paid_so_far    numeric(10,2);
    v_balance        numeric(10,2);
BEGIN
    -- validate the invoice exists, lock it against
    --    payments on the same invoice
    SELECT total
    INTO v_invoice_total
    FROM invoice
    WHERE id = p_invoice_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Invoice id % does not exist.', p_invoice_id;
    END IF;

    -- validate amount
    IF p_amount IS NULL OR p_amount <= 0 THEN
        RAISE EXCEPTION 'Payment amount must be greater than 0 (got %).', p_amount;
    END IF;

    -- compute remaining balance from prior payments
    SELECT COALESCE(SUM(amount), 0)
    INTO v_paid_so_far
    FROM payment
    WHERE invoice_id = p_invoice_id;

    v_balance := v_invoice_total - v_paid_so_far;

    IF v_balance <= 0 THEN
        RAISE EXCEPTION 'Invoice id % is already fully paid (total %, paid %).',
            p_invoice_id, v_invoice_total, v_paid_so_far;
    END IF;

    IF p_amount > v_balance THEN
        RAISE EXCEPTION
            'Payment of % exceeds remaining balance of % on invoice id % (total %, already paid %).',
            p_amount, v_balance, p_invoice_id, v_invoice_total, v_paid_so_far;
    END IF;

    -- record the payment
    INSERT INTO payment (date_payment, amount, method, invoice_id)
    VALUES (CURRENT_DATE, p_amount, p_method, p_invoice_id)
    RETURNING id INTO p_payment_id;

    -- report back the new state
    -- ------------------------------------------------------
    p_remaining_balance := v_balance - p_amount;
    p_fully_paid        := (p_remaining_balance = 0);

    IF p_fully_paid THEN
        RAISE NOTICE 'Payment id % recorded for invoice id % — invoice is now fully paid.',
            p_payment_id, p_invoice_id;
    ELSE
        RAISE NOTICE 'Payment id % recorded for invoice id % — % remaining.',
            p_payment_id, p_invoice_id, p_remaining_balance;
    END IF;
END;
$$;


-- ============================================================
-- TEST
-- ============================================================
-- Create 3 unpaid invoices
INSERT INTO invoice (date_invoice, total, coupon_id, owner_id)
VALUES
    (CURRENT_DATE, 100.00, NULL, NULL),
    (CURRENT_DATE, 150.00, NULL, NULL),
    (CURRENT_DATE, 250.00, NULL, NULL);

-- Give one of them a partial payment
INSERT INTO payment (date_payment, amount, method, invoice_id)
SELECT
    CURRENT_DATE,
    40.00,
    'cash',
    id
FROM invoice
ORDER BY id DESC
LIMIT 1;

-- invoice with a healthy total and see what's been paid so far
SELECT i.id, i.total, COALESCE(SUM(p.amount), 0) AS paid_so_far,
       i.total - COALESCE(SUM(p.amount), 0) AS balance
FROM invoice i
LEFT JOIN payment p ON p.invoice_id = i.id
GROUP BY i.id, i.total
HAVING i.total - COALESCE(SUM(p.amount), 0) > 0
ORDER BY i.id
LIMIT 5;
-- id,total,paid_so_far,balance
-- 775850,30.00,0,30

-- partial payment: pay half the balance
CALL sp_process_payment(
    775849,
    50,
    'cash',
    NULL, NULL, NULL   -- OUT params
);
-- p_payment_id,p_remaining_balance,p_fully_paid
-- 775849,15,false

-- check the OUT values via a DO block (psql doesn't surface OUT
-- params directly from CALL)
DO $$
DECLARE
    v_payment_id int4;
    v_balance    numeric(10,2);
    v_paid       boolean;
BEGIN
    CALL sp_process_payment(775849, 50, 'debit card',
                             v_payment_id, v_balance, v_paid);
    RAISE NOTICE 'payment_id=% balance=% fully_paid=%', v_payment_id, v_balance, v_paid;
END $$;
-- Payment id 775850 recorded for invoice id 775850 — invoice is now fully paid.
-- payment_id=775850 balance=0.00 fully_paid=t

-- verify total paid now matches invoice total
SELECT i.id, i.total, SUM(p.amount) AS total_paid
FROM invoice i
JOIN payment p ON p.invoice_id = i.id
WHERE i.id = 775849
GROUP BY i.id, i.total;
-- id,total,total_paid
-- 775850,30.00,30

-- overpayment attempt: should raise
CALL sp_process_payment(775850, 9999999.99, 'cash', NULL, NULL, NULL);
-- Invoice id 775850 is already fully paid (total 30.00, paid 30.00)

-- already fully paid: should raise
CALL sp_process_payment(775849, 1.00, 'cash', NULL, NULL, NULL);
--  Invoice id 775850 is already fully paid (total 30.00, paid 30.00).

-- zero / negative amount: should raise
CALL sp_process_payment(775849, 0, 'cash', NULL, NULL, NULL);
CALL sp_process_payment(775849, -5.00, 'cash', NULL, NULL, NULL);
-- Payment amount must be greater than 0 (got 0).

-- invalid method: should raise via existing CHECK constraint
CALL sp_process_payment(775850, 5.00, 'bitcoin', NULL, NULL, NULL);
-- ERROR: new row for relation "payment" violates check constraint "payment_method_check"

-- nonexistent invoice: should raise
CALL sp_process_payment(999999999, 5.00, 'cash', NULL, NULL, NULL);
-- Invoice id 999999999 does not exist.