Погледи

---

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

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

CREATE OR REPLACE VIEW synergymed.v_client_full_activity_overview AS
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,
    
    -- narachki
    COUNT(DISTINCT co.id) AS total_orders,
    COALESCE(SUM(co.total_price), 0) AS total_spent,
    
    -- plakjanja
    COUNT(DISTINCT p.id) FILTER (WHERE p.status = 'завршено') AS successful_payments,
    COUNT(DISTINCT p.id) FILTER (WHERE p.status = 'неуспешно') AS failed_payments,
    MAX(p.payment_date) AS last_payment_date,
    
    -- alergii
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM synergymed.healthprofile hp
            JOIN synergymed.allergicreaction_healthprofile_medicine ahm ON ahm.health_profile_id = hp.id
            WHERE hp.client_id = c.user_id
        ) THEN TRUE ELSE FALSE
    END AS has_allergies,
    
    -- recepti
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM synergymed.prescription pr
            WHERE pr.client_id = c.user_id
              AND pr.valid_to >= CURRENT_DATE
        ) THEN TRUE ELSE FALSE
    END 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 synergymed.clientorder co ON co.client_id = c.user_id
LEFT JOIN synergymed.payment p ON p.client_id = c.user_id
GROUP BY u.id, client_name, u.e_mail, c.is_verified, cc.points;

---

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;