QueryOptimization: views.sql

-- ============================================================
-- View 1: daily_clinic_schedule
--
-- Primary filter: date_appointment and employee_id
-- ============================================================

CREATE OR REPLACE VIEW daily_clinic_schedule AS
SELECT
    a.id                                     AS appointment_id,
    a.date_appointment,
    a.reason,
    o.id                                     AS owner_id,
    o.first_name || ' ' || o.last_name       AS owner_name,
    o.phone                                  AS owner_phone,
    p.id                                     AS pet_id,
    p.name                                   AS pet_name,
    p.type                                   AS pet_type,
    e.id                                     AS examination_id,
    e.status                                 AS examination_status,
    emp.id                                   AS employee_id,
    emp.first_name || ' ' || emp.last_name   AS employee_name,
    rl.name                                  AS employee_role,
    er.id                                    AS room_id,
    er.room_number,
    er.type                                  AS room_type
FROM appointment a
JOIN owner o ON o.id = a.owner_id
JOIN pet p ON p.id = a.pet_id
LEFT JOIN examination e ON e.appointment_id = a.id
LEFT JOIN employee emp ON emp.id = e.employee_id
LEFT JOIN role rl ON rl.id = emp.role_id
LEFT JOIN examination_room er ON er.id = e.examination_room_id;

SELECT * FROM daily_clinic_schedule WHERE date_appointment = '2025-07-07';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM daily_clinic_schedule WHERE date_appointment = '2025-07-07';

SELECT * FROM daily_clinic_schedule
WHERE employee_id = 3 AND date_appointment = '2025-07-07';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM daily_clinic_schedule
WHERE employee_id = 3 AND date_appointment = '2025-07-07';

-- INSERT into appointment
INSERT INTO appointment (date_appointment, reason, phone, owner_id, pet_id)
VALUES ('2025-07-07', 'Routine checkup', '+389 71 271 919', 1, 385);
-- UPDATE on appointment
UPDATE appointment
SET reason = 'Follow-up visit'
WHERE date_appointment = '2025-07-07' AND owner_id = 1;

-- INSERT into examination
INSERT INTO examination (date_examination, status, appointment_id, employee_id, examination_room_id)
VALUES ('2025-07-10', 'scheduled', 850801, 3, 5);
-- UPDATE on examination
UPDATE examination
SET status = 'completed'
WHERE employee_id = 3 AND date_examination = '2025-07-10';

-- INDEXES
-- CREATE INDEX idx_examination_employee on examination (employee_id);
DROP INDEX IF EXISTS idx_examination_employee;
CREATE INDEX idx_appointment_date on appointment (date_appointment);
CREATE INDEX idx_examination_appointment ON examination (appointment_id);

-- INSERT into appointment
INSERT INTO appointment (date_appointment, reason, phone, owner_id, pet_id)
VALUES ('2025-07-08', 'Vaccination check', '+389 77 979 331', 2, 230);
-- UPDATE on appointment
UPDATE appointment
SET reason = 'Vaccination follow-up visit'
WHERE date_appointment = '2025-07-08' AND owner_id = 2;

-- INSERT into examination
INSERT INTO examination (date_examination, status, appointment_id, employee_id, examination_room_id)
VALUES ('2025-07-15', 'scheduled', 850802, 3, 5);
-- UPDATE on examination
UPDATE examination
SET status = 'completed'
WHERE employee_id = 3 AND date_examination = '2025-07-15';


-- ============================================================
-- View 2: pet_medical_history
--
-- Primary filter: pet_id (also usable by owner_id, date_examination)
-- Use case: vet or assistant pulls up a pet's full medical timeline
--   during a visit: appointments, examinations, and every treatment
--   (consultation/vaccination/prescription/operation) with its
--   type-specific attributes collapsed into one JSON blob, since
--   each treatment_type has a different attribute set (EAV pattern).
-- ============================================================

CREATE OR REPLACE VIEW pet_medical_history AS
SELECT
    p.id                                    AS pet_id,
    p.name                                  AS pet_name,
    p.type                                  AS pet_type,
    p.breed,
    p.age,
    o.id                                    AS owner_id,
    o.first_name || ' ' || o.last_name      AS owner_name,
    o.phone                                 AS owner_phone,
    a.id                                    AS appointment_id,
    a.date_appointment,
    a.reason                                AS appointment_reason,
    e.id                                    AS examination_id,
    e.date_examination,
    e.status                                AS examination_status,
    e.description                           AS examination_notes,
    emp.id                                  AS employee_id,
    emp.first_name || ' ' || emp.last_name  AS attending_employee,
    r.name                                  AS employee_role,
    t.id                                    AS treatment_id,
    t.date_treatment,
    tt.name                                 AS treatment_type,
    t.notes                                 AS treatment_notes,
    (
        SELECT json_object_agg(ta.name, tav.value)
        FROM treatment_attribute_value tav
        JOIN treatment_attribute ta ON ta.id = tav.treatment_attribute_id
        WHERE tav.treatment_id = t.id
    )                                        AS treatment_details
FROM pet p
JOIN owner o ON o.id = p.owner_id
LEFT JOIN appointment a ON a.pet_id = p.id
LEFT JOIN examination e ON e.appointment_id = a.id
LEFT JOIN employee emp ON emp.id = e.employee_id
LEFT JOIN role r ON r.id = emp.role_id
LEFT JOIN treatment t ON t.examination_id = e.id
LEFT JOIN treatment_type tt ON tt.id = t.treatment_type_id;

SELECT * FROM pet_medical_history WHERE pet_id = 9956;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pet_medical_history WHERE pet_id = 9956;

SELECT id, treatment_type_id, examination_id FROM treatment LIMIT 5;

-- INSERT into treatment, treatment_attribute_value
INSERT INTO treatment (examination_id, treatment_type_id, date_treatment, notes)
VALUES (1726, 2, CURRENT_DATE, 'Follow-up dosage adjustment');
-- SELECT max(id) FROM treatment

INSERT INTO treatment_attribute_value (treatment_id, treatment_attribute_id, value)
VALUES (577416, 13, 'false');

-- UPDATE treatment, treatment_attribute_value

-- SELECT tav.treatment_id, tav.value
-- FROM treatment_attribute_value tav
-- JOIN treatment t ON t.id = tav.treatment_id
-- WHERE t.treatment_type_id = 4
--   AND tav.treatment_attribute_id = 23
-- LIMIT 5;

UPDATE treatment
SET notes = 'Updated post-op notes'
WHERE id = 1577;

UPDATE treatment_attribute_value
SET value = '122'
WHERE treatment_id = 1577 AND treatment_attribute_id = 23;

CREATE INDEX idx_appointment_pet ON appointment (pet_id);
-- CREATE INDEX idx_treatment_attribute_value_treatment ON treatment_attribute_value (treatment_id);
DROP INDEX IF EXISTS idx_treatment_attribute_value_treatment;

-- INSERT into treatment, treatment_attribute_value
INSERT INTO treatment (examination_id, treatment_type_id, date_treatment, notes)
VALUES (1726, 2, CURRENT_DATE, 'Second follow-up dosage adjustment');
-- SELECT max(id) FROM treatment;

INSERT INTO treatment_attribute_value (treatment_id, treatment_attribute_id, value)
VALUES (577417, 13, 'false');

-- UPDATE treatment, treatment_attribute_value
UPDATE treatment
SET notes = 'Updated dosage per second follow-up'
WHERE id = 1570;

UPDATE treatment_attribute_value
SET value = 'true'
WHERE treatment_id = 1571 AND treatment_attribute_id = 13;


-- ============================================================
-- View 3: active_prescriptions_for_pet
--
-- Primary filter: pet_id (also usable by medicine_id)
-- Use case: pet_id - vet checks what a pet is currently prescribed
--   before adding a new treatment, to catch interactions or repeat
--   prescriptions. medicine_id - clinic checks which currently
--   active patients are on a given medicine, during a
--   medication recall or an interaction check. Filters to
--   prescriptions where today falls within date_start/date_end.
-- ============================================================

CREATE OR REPLACE VIEW active_prescriptions_for_pet AS
SELECT
    p.id                 AS pet_id,
    p.name                AS pet_name,
    o.id                 AS owner_id,
    o.first_name || ' ' || o.last_name AS owner_name,
    pr.id                 AS prescription_id,
    pr.date_start,
    pr.date_end,
    pr.description        AS prescription_notes,
    m.id                  AS medicine_id,
    m.name                AS medicine_name,
    pm.dosage,
    pm.num_days
FROM pet p
JOIN appointment a ON a.pet_id = p.id
JOIN owner o ON o.id = p.owner_id
JOIN examination e ON e.appointment_id = a.id
JOIN prescription pr ON pr.examination_id = e.id
JOIN prescription_medicine pm ON pm.prescription_id = pr.id
JOIN medicine m ON m.id = pm.medicine_id
WHERE pr.date_start <= CURRENT_DATE AND pr.date_end >= CURRENT_DATE;

-- 1. By pet (idx_appointment_pet + PK on prescription_medicine)
SELECT * FROM active_prescriptions_for_pet WHERE pet_id = 3;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM active_prescriptions_for_pet WHERE pet_id = 3;

-- 2. By medicine (seq scan)
SELECT * FROM active_prescriptions_for_pet WHERE medicine_id = 47;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM active_prescriptions_for_pet WHERE medicine_id = 47;

-- INSERT into prescription, prescription_medicine
INSERT INTO prescription (examination_id, date_start, date_end, description)
VALUES (699, CURRENT_DATE, CURRENT_DATE + 10, 'Antibiotic course, 10 days');
-- SELECT max(id) FROM prescription;

INSERT INTO prescription_medicine (prescription_id, medicine_id, dosage, num_days)
VALUES (432525, 1, 250, 10);

-- UPDATE on prescription, prescription_medicine
UPDATE prescription
SET date_end = date_end + 5
WHERE id = 328;

UPDATE prescription_medicine
SET dosage = 300
WHERE prescription_id = 327 AND medicine_id = 8;

-- CREATE INDEX idx_prescription_active_dates ON prescription (date_end, date_start);
DROP INDEX IF EXISTS idx_prescription_active_dates;

-- INSERT into prescription, prescription_medicine
INSERT INTO prescription (examination_id, date_start, date_end, description)
VALUES (700, CURRENT_DATE, CURRENT_DATE + 7, 'Follow-up course');
-- SELECT max(id) FROM prescription;

INSERT INTO prescription_medicine (prescription_id, medicine_id, dosage, num_days)
VALUES (432526, 2, 200, 7);

-- UPDATE on prescription, prescription_medicine
UPDATE prescription
SET date_end = date_end + 3
WHERE id = 329;

UPDATE prescription_medicine
SET dosage = 150
WHERE prescription_id = 329 AND medicine_id = 6756;

-- ============================================================
-- View 4: employee_performance_summary
--
-- Primary filter: employee_id (also usable by date_treatment range)
-- Use case: management pulls a per-employee workload record.
--   Every examination they conducted, every treatment tied to it,
--   and what that treatment was billed at in order to review caseload
--   and revenue contribution per vet/assistant.
-- ============================================================

-- DROP VIEW IF EXISTS employee_performance_summary;

CREATE MATERIALIZED VIEW employee_performance_summary AS
SELECT
    emp.id                                   AS employee_id,
    emp.first_name || ' ' || emp.last_name   AS employee_name,
    rl.name                                  AS employee_role,
    e.id                                      AS examination_id,
    e.date_examination,
    e.status                                  AS examination_status,
    t.id                                       AS treatment_id,
    tt.name                                    AS treatment_type,
    t.date_treatment,
    ii.price                                   AS billed_price,
    ii.invoice_id
FROM employee emp
         JOIN role rl ON rl.id = emp.role_id
         LEFT JOIN examination e ON e.employee_id = emp.id
         LEFT JOIN treatment t ON t.examination_id = e.id
         LEFT JOIN treatment_type tt ON tt.id = t.treatment_type_id
         LEFT JOIN invoice_item ii ON ii.treatment_id = t.id;

SELECT * FROM employee_performance_summary WHERE employee_id = 5;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM employee_performance_summary WHERE employee_id = 5;

SELECT * FROM employee_performance_summary
WHERE employee_id = 5 AND date_treatment >= '2025-01-01' AND date_treatment < '2026-01-01';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM employee_performance_summary
                           WHERE employee_id = 5 AND date_treatment >= '2025-01-01' AND date_treatment < '2026-01-01';

--INSERT into examination
INSERT INTO examination(date_examination, status, description, appointment_id, employee_id, examination_room_id)
VALUES ('2026-08-01','completed','Regular check-up', 720800,6,3);

--INSERT into treatment
INSERT INTO treatment(date_treatment, notes, treatment_type_id, examination_id)
VALUES ('2026-08-01','Follow-up plan agreed',2,715);

--INSERT into invoice
INSERT INTO invoice(date_invoice, total, coupon_id, owner_id)
VALUES ('2026-08-01',56.33,398,79);

--INSERT into invoice_item
INSERT INTO invoice_item(num_item, invoice_id, price,quantity, type, shop_item_id, treatment_id)
VALUES (1,24972,67.87,1,'treatment',null,223433);

--UPDATE on treatment
UPDATE treatment
SET notes = 'Updated: discussed follow-up diet plan'
WHERE id = 5778001;

CREATE INDEX idx_treatment_examination ON treatment (examination_id);
-- CREATE INDEX idx_treatment_date ON treatment (date_treatment);
-- CREATE INDEX idx_invoice_item_treatment ON invoice_item (treatment_id);
DROP INDEX IF EXISTS idx_treatment_date;
DROP INDEX IF EXISTS idx_invoice_item_treatment;

-- INSERT into examination (second follow-up)
INSERT INTO examination(date_examination, status, description, appointment_id, employee_id, examination_room_id)
VALUES ('2026-08-01','completed','Follow-up check-up', 720800,6,3);

--INSERT into treatment (second follow-up)
INSERT INTO treatment(date_treatment, notes, treatment_type_id, examination_id)
VALUES ('2026-08-01','Follow-up plan discussed',2,715);

UPDATE treatment
SET notes = 'Updated after second follow-up'
WHERE id = 5778002;

-- ============================================================
-- View 5: invoice_billing_details
--
-- Primary filter: invoice_id (also works by owner_id)
-- Use case: front-desk/billing staff pull up everything on an
--   invoice — owner, coupon applied, every line item (shop item
--   or treatment), and payments received, to answer the question "what does
--   this owner still owe" or reprint a receipt.
-- ============================================================

CREATE OR REPLACE VIEW invoice_billing_details AS
SELECT
    i.id                                    AS invoice_id,
    i.date_invoice,
    i.total                                  AS invoice_total,
    o.id                                      AS owner_id,
    o.first_name || ' ' || o.last_name        AS owner_name,
    o.phone                                   AS owner_phone,
    c.code                                     AS coupon_code,
    c.type                                     AS coupon_type,
    c.value                                    AS coupon_value,
    ii.num_item,
    ii.type                                    AS line_type,
    ii.price                                   AS line_price,
    ii.quantity,
    si.name                                    AS shop_item_name,
    tr.id                                      AS treatment_id,
    tt.name                                    AS treatment_type,
    p.date_payment,
    p.amount                                   AS amount_paid,
    p.method                                    AS payment_method
FROM invoice i
         JOIN owner o ON o.id = i.owner_id
         LEFT JOIN coupon c ON c.id = i.coupon_id
         LEFT JOIN invoice_item ii ON ii.invoice_id = i.id
         LEFT JOIN shop_item si ON si.id = ii.shop_item_id
         LEFT JOIN treatment tr ON tr.id = ii.treatment_id
         LEFT JOIN treatment_type tt ON tt.id = tr.treatment_type_id
         LEFT JOIN payment p ON p.invoice_id = i.id;

SELECT * FROM invoice_billing_details WHERE invoice_id = 500000;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM invoice_billing_details WHERE invoice_id = 500000;

SELECT * FROM invoice_billing_details WHERE owner_id = 45;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM invoice_billing_details WHERE owner_id = 45;

-- INSERT into invoice
INSERT INTO invoice (date_invoice, total, coupon_id, owner_id)
VALUES (CURRENT_DATE, 62.50, NULL, 8);

-- INSERT into invoice_item
INSERT INTO invoice_item (num_item, invoice_id, price,quantity, type, shop_item_id, treatment_id)
VALUES (1, 24984,62.50, 1, 'shop_item', 9,null);

-- INSERT into payment
INSERT INTO payment (date_payment, amount, method, invoice_id)
VALUES (CURRENT_DATE, 62.50, 'credit card', 24984);

-- UPDATE on invoice
UPDATE invoice
SET total = 58.00
WHERE id = 24984;

-- UPDATE on payment
UPDATE payment
SET amount = 58.00
WHERE invoice_id = 24984;

--Creating indexes
CREATE INDEX idx_invoice_owner ON invoice (owner_id);
CREATE INDEX idx_payment_invoice ON payment (invoice_id);
-- CREATE INDEX idx_invoice_item_invoice_id ON invoice_item (invoice_id, num_item);
DROP INDEX IF EXISTS idx_invoice_item_invoice_id;


-- INSERT into invoice (second invoice)
INSERT INTO invoice (date_invoice, total, coupon_id, owner_id)
VALUES (CURRENT_DATE, 69.50, NULL, 8);

-- INSERT into invoice_item (second invoice)
INSERT INTO invoice_item (num_item, invoice_id, price,quantity, type, shop_item_id, treatment_id)
VALUES (1, 24984,69.50, 1, 'shop_item', 9,null);

-- INSERT into payment (second invoice)
INSERT INTO payment (date_payment, amount, method, invoice_id)
VALUES (CURRENT_DATE, 50.00, 'credit card', 24984);

UPDATE invoice
SET total = 27.00
WHERE id = 24984;

-- ============================================================
-- View 6: low_stock_shop_items
--
-- Primary filter: category_name (also usable by a stock threshold)
-- Use case: shop manager checks what needs reordering. Any item
--   with fewer than 20 units left, grouped by category so they
--   can restock by supplier/category in one trip.
-- ============================================================

CREATE OR REPLACE VIEW low_stock_shop_items AS
SELECT
    si.id                AS shop_item_id,
    si.name               AS item_name,
    si.price,
    si.stock,
    sic.id                AS category_id,
    sic.name               AS category_name,
    parent.name             AS parent_category_name
FROM shop_item si
         JOIN shop_item_category sic ON sic.id = si.shop_item_category_id
         LEFT JOIN shop_item_category parent ON parent.id = sic.parent_id
WHERE si.stock < 20;

SELECT * FROM low_stock_shop_items WHERE category_name = 'Medicine';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM low_stock_shop_items WHERE category_name = 'Medicine';

SELECT * FROM low_stock_shop_items WHERE stock < 10;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM low_stock_shop_items WHERE stock < 10;

-- INSERT a new item that's already low on stock
INSERT INTO shop_item (name, price, stock, shop_item_category_id)
SELECT 'Emergency Saline Restock Pack', 12.99, 8, sic.id
FROM shop_item_category sic
WHERE sic.name = 'Medicine'
LIMIT 1;

-- UPDATE — simulate a sale reducing stock on an existing item
UPDATE shop_item
SET stock = stock - 5
WHERE id = (SELECT id FROM shop_item WHERE name = 'Emergency Saline Restock Pack');

-- second restock scenario
UPDATE shop_item
SET stock = stock - 3
WHERE id = (SELECT id FROM shop_item WHERE name = 'Emergency Saline Restock Pack');

INSERT INTO shop_item (name, price, stock, shop_item_category_id)
SELECT 'Sample Low-Stock Toy', 6.49, 4, sic.id
FROM shop_item_category sic
WHERE sic.name = 'Toys'
LIMIT 1;