Погледи

---

1) Поглед што дава детален overview за клиент:

Основни податоци
Вкупни нарачки и сума на потрошено
Број на успешни/неуспешни плаќања
Лојалност (поени од clubcard)
Проверка дали клиентот е верифициран
Дали има регистрирани алергии
Дали има активни рецепти

CREATE OR REPLACE VIEW synergymed.v_client_full_activity_overview AS
WITH order_summary AS (
    SELECT co.client_id,
           COUNT(co.id) AS total_orders,
           COALESCE(SUM(co.total_price), 0) AS total_spent
    FROM synergymed.clientorder co
    GROUP BY co.client_id
),
payment_summary AS (
    SELECT p.client_id,
           COUNT(*) FILTER (WHERE p.status = 'завршено') AS successful_payments,
           COUNT(*) FILTER (WHERE p.status = 'неуспешно') AS failed_payments,
           MAX(p.payment_date) AS last_payment_date
    FROM synergymed.payment p
    GROUP BY p.client_id
),
allergy_clients AS (
    SELECT DISTINCT hp.client_id
    FROM synergymed.healthprofile hp
    JOIN synergymed.allergicreaction_healthprofile_medicine ahm 
         ON ahm.health_profile_id = hp.id
),
prescription_clients AS (
    SELECT DISTINCT pr.client_id
    FROM synergymed.prescription pr
    WHERE pr.valid_to >= CURRENT_DATE
)
SELECT 
    u.id AS user_id,
    u.first_name || ' ' || u.last_name AS client_name,
    u.e_mail,
    c.is_verified,
    COALESCE(cc.points, 0) AS loyalty_points,

    COALESCE(order_summary.total_orders, 0) AS total_orders,
    COALESCE(order_summary.total_spent, 0) AS total_spent,

    COALESCE(payment_summary.successful_payments, 0) AS successful_payments,
    COALESCE(payment_summary.failed_payments, 0) AS failed_payments,
    payment_summary.last_payment_date,

    (allergy_clients.client_id IS NOT NULL) AS has_allergies,
    (prescription_clients.client_id IS NOT NULL) AS has_active_prescriptions

FROM synergymed.client c
JOIN synergymed.users u ON u.id = c.user_id
LEFT JOIN synergymed.clubcard cc ON cc.user_id = c.user_id
LEFT JOIN order_summary ON order_summary.client_id = c.user_id
LEFT JOIN payment_summary ON payment_summary.client_id = c.user_id
LEFT JOIN allergy_clients ON allergy_clients.client_id = c.user_id
LEFT JOIN prescription_clients ON prescription_clients.client_id = c.user_id;

---

2) Поглед за движењето на лековите низ целиот supply-chain:

Кој дистрибутер испорачува
Од лек и колку
На која аптека
Статус на нарачката
Очекуван датум на пристигнување
Последна залиха во магацин

CREATE OR REPLACE VIEW synergymed.v_supply_chain_overview AS
SELECT 
    so.id AS supply_order_id,
    d.company_id AS distributor_id,
    cd.company_name AS distributor_name,
    ph.company_id AS pharmacy_id,
    cp.company_name AS pharmacy_name,
    bm.id AS branded_medicine_id,
    bm.name AS branded_medicine_name,
    sobm.quantity AS ordered_quantity,
    so.status AS order_status,
    so.order_date,
    so.expected_arrival_date,
    
    COALESCE(ibm.quantity, 0) AS last_known_inventory
FROM synergymed.supplyorder so
JOIN synergymed.distributor d ON d.company_id = so.distributor_id
JOIN synergymed.company cd ON cd.id = d.company_id
JOIN synergymed.pharmacy ph ON ph.company_id = so.pharmacy_id
JOIN synergymed.company cp ON cp.id = ph.company_id
JOIN synergymed.supplyorder_brandedmedicine sobm ON sobm.supply_order_id = so.id
JOIN synergymed.brandedmedicine bm ON bm.id = sobm.branded_medicine_id
LEFT JOIN synergymed.inventory i ON i.facility_id IN (
    SELECT f.id FROM synergymed.facility f WHERE f.company_id = ph.company_id
)
LEFT JOIN synergymed.inventory_brandedmedicine ibm 
       ON ibm.inventory_id = i.id AND ibm.branded_medicine_id = bm.id;

---

3) Поглед што ги прикажува залихите на сите лекови во секоја аптека, со датум на последна промена.

CREATE OR REPLACE VIEW synergymed.v_inventory_status AS
SELECT 
    ph.company_id AS pharmacy_id,
    phc.branded_medicine_id,
    bm.name AS branded_name,
    ibm.quantity,
    ibm.last_changed
FROM synergymed.inventory_brandedmedicine ibm
JOIN synergymed.inventory i ON ibm.inventory_id = i.id
JOIN synergymed.facility f ON i.facility_id = f.id
JOIN synergymed.pharmacy ph ON ph.company_id = f.company_id
JOIN synergymed.pharmacy_catalog phc ON phc.pharmacy_id = ph.company_id
JOIN synergymed.brandedmedicine bm ON bm.id = phc.branded_medicine_id;

---

4) Поглед што дава детална историја на нарачки за секој клиент, со информации за статус, цена и датум.

CREATE OR REPLACE VIEW synergymed.v_client_order_history AS
SELECT 
    co.id AS order_id,
    u.first_name || ' ' || u.last_name AS client_name,
    co.order_date,
    co.expected_arrival_date,
    co.status,
    co.total_price,
    pm.method_name AS payment_method
FROM synergymed.clientorder co
JOIN synergymed.client c ON co.client_id = c.user_id
JOIN synergymed.users u ON c.user_id = u.id
JOIN synergymed.payment p ON co.payment_id = p.id
JOIN synergymed.paymentmethod pm ON p.payment_method_id = pm.id;