F2: paws_care_views.sql

CREATE VIEW View_Low_Stock_Products AS
SELECT product_id,
       name,
       stock_quantity,
       reorder_level
FROM Product
WHERE stock_quantity <= reorder_level;


CREATE VIEW View_Treatment_Revenue_Summary AS
SELECT t.name                           AS treatment_name,
       COUNT(pt.performed_treatment_id) AS times_performed,
       SUM(t.price)                     AS total_revenue,
       ROUND(AVG(t.price), 2)           AS average_price
FROM Treatment t
         JOIN Performed_Treatment pt ON t.treatment_id = pt.treatment_id
GROUP BY t.name
ORDER BY total_revenue DESC;


CREATE VIEW View_Review_Stats AS
SELECT owner_id,
       ROUND(AVG(rating), 2) AS average_rating,
       COUNT(comment)        AS total_comments,
       AVG(LENGTH(comment))  AS avg_comment_length
FROM Review
GROUP BY owner_id;


CREATE VIEW View_Pet_Owners AS
SELECT p.pet_id,
       p.name                           AS pet_name,
       p.type_of_animal,
       p.breed,
       o.owner_id,
       pers.name || ' ' || pers.surname AS owner_full_name,
       pers.phone_number,
       opp.is_primary_owner
FROM Pet p
         JOIN Owner_Pet_Pair opp ON p.pet_id = opp.pet_id
         JOIN Owner o ON opp.owner_id = o.owner_id
         JOIN Person pers ON o.owner_id = pers.person_id;


CREATE VIEW View_Pet_Vaccination_Status AS
SELECT opp.owner_id,
       pet.pet_id,
       pet.name          AS pet_name,
       v_info.trade_name AS vaccine,
       vacc.date         AS last_dose_date,
       vacc.next_due_date,
       CASE
           WHEN vacc.next_due_date < CURRENT_DATE THEN 'OVERDUE'
           WHEN vacc.next_due_date <= CURRENT_DATE + INTERVAL '14 days' THEN 'Due Soon'
           ELSE 'Up to Date'
           END           AS status
FROM Vaccination vacc
         JOIN Performed_Treatment pt ON vacc.vaccination_id = pt.performed_treatment_id
         JOIN Medical_Record mr ON pt.medical_record_id = mr.medical_record_id
         JOIN Pet pet ON mr.pet_id = pet.pet_id
         JOIN Owner_Pet_Pair opp ON pet.pet_id = opp.pet_id
         JOIN Vaccination_Vaccine vv ON vacc.vaccination_id = vv.vaccination_id
         JOIN Vaccine v_info ON vv.vaccine_id = v_info.vaccine_id

WHERE opp.is_primary_owner = TRUE;


CREATE VIEW View_Next_Appointments AS
SELECT opp.owner_id,
       p.pet_id,
       p.name          AS pet_name,
       a.appointment_time,
       a.reason,
       a.status,
       er.room_type,
       vt.veterinarian_id,
       per_doc.name    AS vet_name,
       per_doc.surname AS vet_surname,
       vt.specialty
FROM Appointment a
         JOIN Pet p ON p.pet_id = a.pet_id
         JOIN Owner_Pet_Pair opp ON opp.pet_id = p.pet_id
         LEFT JOIN Exam_Room er ON er.exam_room_id = a.exam_room_id
         LEFT JOIN Attends at2 ON at2.app_id = a.appointment_id
         LEFT JOIN Veterinarian vt ON vt.veterinarian_id = at2.medical_prof_id
         LEFT JOIN Person per_doc ON per_doc.person_id = vt.veterinarian_id
WHERE a.appointment_time >= CURRENT_TIMESTAMP
  AND a.status = 'Scheduled'
ORDER BY a.appointment_time ASC;


CREATE VIEW View_Available_Veterinarians AS
SELECT v.veterinarian_id,
       per.name,
       per.surname,
       v.specialty,
       mp.shift_type,
       mp.department,
       v.license_number
FROM Veterinarian v
         JOIN Medical_Professional mp
              ON mp.medical_prof_id = v.veterinarian_id
         JOIN Person per
              ON per.person_id = v.veterinarian_id
WHERE (
    (mp.shift_type = 'Morning'
        AND EXTRACT(HOUR FROM NOW()) BETWEEN 6 AND 12)
        OR
    (mp.shift_type = 'Afternoon'
        AND EXTRACT(HOUR FROM NOW()) BETWEEN 12 AND 20)
        OR
    (mp.shift_type = 'Night'
        AND (
         EXTRACT(HOUR FROM NOW()) >= 20
             OR EXTRACT(HOUR FROM NOW()) < 6
         )
        )
        OR
    (mp.shift_type = 'Full-Day'
        AND EXTRACT(HOUR FROM NOW()) BETWEEN 8 AND 20)
        OR
    (mp.shift_type = 'On-Call')
    )
  AND NOT EXISTS (SELECT 1
                  FROM Attends a
                           JOIN Appointment ap
                                ON ap.appointment_id = a.app_id
                  WHERE a.medical_prof_id = v.veterinarian_id
                    AND ap.status = 'Scheduled'
                    AND ap.appointment_time < NOW() + INTERVAL '1 hour'
                    AND ap.appointment_time >= NOW());



CREATE VIEW View_Free_Upcoming_Slots AS
SELECT er.exam_room_id,
       er.room_type,
       ts.slot_time
FROM Exam_Room er
         CROSS JOIN generate_series(
        NOW(),
        NOW() + INTERVAL '24 hours',
        INTERVAL '30 minutes'
                    ) AS ts(slot_time)
WHERE er.is_available = TRUE

  AND NOT EXISTS (SELECT 1
                  FROM Appointment ap
                  WHERE ap.exam_room_id = er.exam_room_id
                    AND ap.status = 'Scheduled'
                    AND ap.appointment_time < ts.slot_time + INTERVAL '30 minutes'
                    AND ap.appointment_time + INTERVAL '30 minutes' > ts.slot_time)


  AND NOT EXISTS (SELECT 1
                  FROM Performed_Treatment pt
                  WHERE pt.exam_room_id = er.exam_room_id
                    AND pt.start_time < ts.slot_time + INTERVAL '30 minutes'
                    AND pt.end_time > ts.slot_time);


CREATE VIEW View_Owner_Pet_Treatments AS
SELECT o.owner_id,
       pers.name || ' ' || pers.surname AS owner_name,
       pet.pet_id,
       pet.name                         AS pet_name,
       pet.type_of_animal,
       t.name                           AS treatment_name,
       pt.start_time                    AS treatment_date,
       t.price
FROM Person pers
         JOIN Owner o ON pers.person_id = o.owner_id
         JOIN Owner_Pet_Pair opp ON o.owner_id = opp.owner_id
         JOIN Pet pet ON opp.pet_id = pet.pet_id
         JOIN Medical_Record mr ON pet.pet_id = mr.pet_id
         JOIN Performed_Treatment pt ON mr.medical_record_id = pt.medical_record_id
         JOIN Treatment t ON pt.treatment_id = t.treatment_id
ORDER BY pt.start_time DESC;


CREATE VIEW View_Stock_Health AS
SELECT pr.product_id,
       pr.name,
       pr.manufacturer,
       pr.stock_quantity,
       pr.reorder_level,
       pr.unit_price,
       pr.stock_quantity * pr.unit_price AS stock_value,
       CASE
           WHEN pr.stock_quantity = 0 THEN 'Out of Stock'
           WHEN pr.stock_quantity <= pr.reorder_level THEN 'Low Stock'
           WHEN pr.stock_quantity <= pr.reorder_level * 2 THEN 'Medium Stock'
           ELSE 'Well Stocked'
           END                           AS stock_status,
       CASE
           WHEN pr.is_prescription_required THEN 'Prescription'
           WHEN m.medication_id IS NOT NULL THEN 'Medication'
           WHEN pf.product_id IS NOT NULL THEN 'Pet Food'
           ELSE 'Other'
           END                           AS category,
       p.name || ' ' || p.surname        AS manager_name
FROM Product pr
         LEFT JOIN Medication m ON m.medication_id = pr.product_id
         LEFT JOIN Pet_Food pf ON pf.product_id = pr.product_id
         JOIN Inventory_Manager im ON im.inventory_manager_id = pr.inventory_manager_id
         JOIN Person p ON p.person_id = im.inventory_manager_id
ORDER BY stock_status, stock_value DESC;


CREATE VIEW View_Medical_Staff_Dashboard AS
SELECT mp.medical_prof_id,
       p.name || ' ' || p.surname AS full_name,
       mp.department,
       mp.shift_type,
       COALESCE(appt.todays_count, 0) AS todays_appointments,
       COALESCE(tr.active_treatments, 0) AS active_treatments,
       COALESCE(tr.total_treatments, 0) AS total_treatments_done
FROM Medical_Professional mp
JOIN Person p ON mp.medical_prof_id = p.person_id

LEFT JOIN (
    SELECT a_link.medical_prof_id,
           COUNT(a.appointment_id) AS todays_count
    FROM Appointment a
    JOIN Attends a_link ON a.appointment_id = a_link.app_id
    WHERE a.appointment_time::DATE = CURRENT_DATE
    AND a.status = 'Scheduled'
    GROUP BY a_link.medical_prof_id
) appt ON appt.medical_prof_id = mp.medical_prof_id

LEFT JOIN (
    SELECT medical_professional_id,
           COUNT(CASE WHEN end_time IS NULL THEN 1 END) AS active_treatments,
           COUNT(*) AS total_treatments
    FROM Performed_Treatment
    GROUP BY medical_professional_id
) tr ON tr.medical_professional_id = mp.medical_prof_id;



CREATE VIEW View_Owner_Financial_Status AS
SELECT o.owner_id,
       p.name || ' ' || p.surname AS owner_name,
       o.membership_type,
       o.balance,
       o.total_spent,
       CASE
           WHEN o.balance < 0 THEN 'In debt'
           WHEN o.balance = 0 THEN 'Settled'
           ELSE 'Positive'
           END                    AS balance_status,
       CASE
           WHEN o.membership_type = 'VIP' THEN 'Priority Scheduling + Discount'
           WHEN o.membership_type = 'Premium' THEN 'Treatment Discount'
           WHEN o.membership_type = 'Standard' THEN 'No Benefits'
           ELSE 'No Membership'
           END                    AS membership_benefits
FROM Owner o
         JOIN Person p ON o.owner_id = p.person_id;