= Напредни извештаи од базата (SQL и складирани процедури)
== Прикажување на вработен (доктор) кој што има напишено најмногу извештаи за едно исто милениче.
За да го тестираме овој прашалник ги додадовме следниве податоци:
{{{
  INSERT INTO project.reports
(description, usersid, petsid)
VALUES('Za isto zivotno', 8, 1);
}}}
{{{
WITH DoctorPetReportsCount AS (
    SELECT
        u.id AS doctor_id,
        u.name AS doctor_name,
        r.petsID AS pet_id,
        COUNT(r.id) AS reports_count
    FROM
        project.reports r
    LEFT JOIN
        project.users u ON r.usersID = u.id
    GROUP BY
        u.id, u.name, r.petsID
),

MaxReportsPerPet AS (
    SELECT
        pet_id,
        MAX(reports_count) AS max_reports_count
    FROM
        DoctorPetReportsCount
    GROUP BY
        pet_id
),

PetsWithMaxReports AS (
    SELECT
        pet_id,
        max_reports_count
    FROM
        MaxReportsPerPet
    WHERE
        max_reports_count = (SELECT MAX(max_reports_count) FROM MaxReportsPerPet)
)

SELECT
    dprc.doctor_id,
    dprc.doctor_name,
    dprc.pet_id,
    dprc.reports_count
FROM
    DoctorPetReportsCount dprc
JOIN
    PetsWithMaxReports pwmr ON dprc.pet_id = pwmr.pet_id AND dprc.reports_count = pwmr.max_reports_count
JOIN
    project.users u ON dprc.doctor_id = u.id;

}}}
== Прикажување на совет чија што дата на побарување не е постара од 6 месеци гледано од денешната дата, прикажување на корисник кој го побарал советот и прикажување на број на одговори на прашањето.
{{{
   SELECT
    bpc.id AS consultation_id,
    bpc.date_askes AS claim_date,
    bpc.title AS consultation_title,
    bpc.description AS consultation_description,
    u.id AS user_id,
    u.name AS user_name,
    COUNT(bpa.id) AS answers_count,
    ARRAY_AGG(bpa.reply) AS all_answers
FROM
    project.blog_post_for_consultations bpc
LEFT JOIN
    project.users u ON bpc.users_id = u.id
LEFT JOIN
    project.blog_post_answers bpa ON bpc.id = bpa.blogpostconsid
WHERE
    bpc.date_askes >= current_date - interval '6 months'
GROUP BY
    bpc.id, bpc.date_askes, bpc.title, bpc.description, u.id, u.name
ORDER BY
    answers_count DESC;
}}} 
== За секое прашање кое е поставено во Blog Post For Consultations колку одговори има, од кои корисници се дадени одговорите и каква е улогата на корисниците кои одговориле(admin, manager, employee, standard).
{{{
SELECT
    bpc.ID AS question_id,
    bpc.title AS question_title,
    COUNT(bpa.ID) AS number_of_answers,
    u."name" AS user_name,
    r.type AS user_role
FROM
    project.blog_post_for_consultations bpc
JOIN
    project.blog_post_answers bpa ON bpc.ID = bpa.blogpostconsid
JOIN
    project.users u ON bpa.usersID = u.ID
JOIN
    project.roles r ON u.role_id = r.ID
GROUP BY
    bpc.ID, u.ID, r.ID  -- Include the role ID in the GROUP BY clause
ORDER BY
    bpc.ID;
}}}
== Прикажување на продукт кој е прв додаден во системот за продажба и за него постојат најмалку нарачки кои биле направени од корисниците.
{{{
WITH ProductSales AS (
    SELECT
        p.id AS product_id,
        p.name AS product_name,
        p.description AS product_description,
        p.price AS product_price,
        MIN(o.quantity) AS sold_quantity,
        p.dateadded AS date_added
    FROM
        project.products p
    LEFT JOIN
        project.product_are_made_orders pamo ON p.id = pamo.id_products
    LEFT JOIN
        project.orders o ON pamo.id_orders = o.id
    GROUP BY
        p.id, p.name, p.description, p.price, p.dateadded
),

RankedProducts AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY sold_quantity, date_added) AS rn
    FROM
        ProductSales
)

SELECT
    product_id,
    product_name,
    product_description,
    product_price,
    sold_quantity,
    date_added
FROM
    RankedProducts
WHERE
    rn = 1;

}}}
== Секој ветеринарен центар колку вкупно мачиња а колку вкупно кучиња примил, и колку е просечната возраст на мачиња и кучиња во секој ветеринарен центар.
{{{

SELECT
    vc.name AS vet_center_name,
    COUNT(CASE WHEN p.typeOfPetsID = (SELECT ID FROM project.type_of_pets WHERE kind_of_pet = 'Dog') THEN 1 END) AS total_dogs,
    COUNT(CASE WHEN p.typeOfPetsID = (SELECT ID FROM project.type_of_pets WHERE kind_of_pet = 'Cat') THEN 1 END) AS total_cats,
    AVG(CASE WHEN p.typeOfPetsID = (SELECT ID FROM project.type_of_pets WHERE kind_of_pet = 'Dog') THEN EXTRACT(YEAR FROM age(current_date, p.dateOfBirthday)) END) AS avg_age_dogs,
    AVG(CASE WHEN p.typeOfPetsID = (SELECT ID FROM project.type_of_pets WHERE kind_of_pet = 'Cat') THEN EXTRACT(YEAR FROM age(current_date, p.dateOfBirthday)) END) AS avg_age_cats
FROM
    project.vet_centers vc
JOIN
    project.pets_visit_vet_centers pvvc ON vc.ID = pvvc.id_vet_centers
JOIN
    project.pets p ON pvvc.id_pets = p.ID
GROUP BY
    vc.ID, vc.name
ORDER BY
    vc.name;
}}}
== Koja e најновата терапија според датум која е препишена, за која дијагноза е препишена, и во кој ветеринарен центар е препишена.
{{{
 WITH LatestTherapies AS (
    SELECT
        ptvc.id_pets,
        MAX(t.appoitmentDate) AS latest_therapy_date
    FROM
        project.pets_visit_vet_centers ptvc
    JOIN
        project.therapy t ON ptvc.id_pets = t.diagnosticsID
    GROUP BY
        ptvc.id_pets
)

SELECT
    vc.name AS vet_center_name,
    p.id AS pet_id,
    --p.description AS pet_description,
    d.description AS diagnosis_description,
    t.description AS latest_therapy_description,
    lt.latest_therapy_date
FROM
    project.vet_centers vc
JOIN
    project.pets_visit_vet_centers ptvc ON vc.id = ptvc.id_vet_centers
JOIN
    project.pets p ON ptvc.id_pets = p.id
JOIN
    project.diagnostics d ON p.id = d.usersID
LEFT JOIN
    LatestTherapies lt ON p.id = lt.id_pets
LEFT JOIN
    project.therapy t ON lt.latest_therapy_date = t.appoitmentDate AND p.id = t.diagnosticsID
ORDER BY
   latest_therapy_date DESC;
}}}  
== За секој ветеринарен центар, колку пари одвојува за нови вработени и која е просечната плата по вработен.
{{{
WITH SalaryData AS (
    SELECT
        v.id AS vet_center_id,
        v.name AS vet_center_name,
        j.predictedsalery AS salary
    FROM
        vet_centers v
    JOIN
        jobs j ON v.id = j.vetCentersID
),
SalaryAggregates AS (
    SELECT
        vet_center_id,
        vet_center_name,
        COUNT(salary) AS number_of_employees,
        SUM(CAST(REPLACE(salary, ' EUR', '') AS DECIMAL)) AS total_salary_expenses,
        AVG(CAST(REPLACE(salary, ' EUR', '') AS DECIMAL)) AS average_salary
    FROM
        SalaryData
    GROUP BY
        vet_center_id, vet_center_name
)

SELECT
    sa.vet_center_name,
    sa.number_of_employees,
    sa.total_salary_expenses,
    sa.average_salary
FROM
    SalaryAggregates sa
ORDER BY
    sa.total_salary_expenses DESC;
}}}
== Прикажување на ветеринарни центри кои што корисникот може да ги посети доколку живее во Охрид, број на слободни работни места во тие ветеринарни центри и број на посетители.
{{{ 
SELECT
    vc.name AS vet_center_name,
    vc.description AS vet_center_description,
    vc.adress AS vet_center_address,
    vc.phoneNumber AS vet_center_phone_number,
    COUNT(DISTINCT j.ID) AS number_of_employees,
    COUNT(DISTINCT pv.id_pets) AS number_of_pets_visited
FROM
    project.vet_centers vc
LEFT JOIN
    project.jobs j ON vc.ID = j.vetCentersID
LEFT JOIN
    project.pets_visit_vet_centers pv ON vc.ID = pv.id_vet_centers
WHERE
    vc.citiesID = (SELECT ID FROM project.cities WHERE name = 'Ohrid')
GROUP BY
    vc.ID, vc.name, vc.description, vc.adress, vc.phoneNumber;

}}}
== За секој продукт колку нарачки за него се направени и од кои корисници се направени. 
За да го тестираме овој прашалник ги додадовме следниве податоци:
{{{
 INSERT INTO project.orders
(quantity,userid)
VALUES (3,2);


INSERT INTO project.product_are_made_orders
(id_products, id_orders)
VALUES (1, 5);
}}}
{{{
WITH ProductOrders AS (
    SELECT
        p.id AS product_id,
        p.name AS product_name,
        o.id AS order_id,
        o.quantity AS order_quantity,
        u.id AS user_id,
        u.name AS user_name,
        u.lastname AS user_lastname
    FROM
        project.products p
    JOIN
        project.product_are_made_orders pmo ON p.id = pmo.id_products
    JOIN
        project.orders o ON pmo.id_orders = o.id
    JOIN
        project.users u ON o.userid = u.id
)
SELECT
    po.product_id,
    po.product_name,
    COUNT(po.order_id) AS total_orders,
    STRING_AGG(DISTINCT CONCAT(po.user_name, ' ', po.user_lastname), ', ') AS users
FROM
    ProductOrders po
GROUP BY
    po.product_id, po.product_name
ORDER BY
    po.product_name;

}}}
=== За секој миленик кои болести се дијагностицирани, какви лекови и терапија е препишена.
{{{
WITH PetDiagnosis AS (
    SELECT
        p.id AS pet_id,
        p.name AS pet_name,
        d.description AS diagnosis_description
    FROM
        pets p
    JOIN
        diagnostics_established_pets dep ON p.id = dep.id_pets
    JOIN
        diagnostics d ON dep.id_diagnostics = d.id
),
PetMedications AS (
    SELECT
        p.id AS pet_id,
        m.name AS medicine_name,
        m.description AS medicine_description
    FROM
        pets p
    JOIN
        therapy_takes_pets ttp ON p.id = ttp.id_pets
    JOIN
        therapy t ON ttp.id_therapy = t.id
    JOIN
        medecines m ON t.diagnosticsID = m.diagnosticsID
),
PetTherapy AS (
    SELECT
        p.id AS pet_id,
        t.description AS therapy_description,
        t.appoitmentDate AS therapy_date
    FROM
        pets p
    JOIN
        therapy_takes_pets ttp ON p.id = ttp.id_pets
    JOIN
        therapy t ON ttp.id_therapy = t.id
)

SELECT
    pd.pet_id,
    pd.pet_name,
    pd.diagnosis_description,
    pm.medicine_name,
    pm.medicine_description,
    pt.therapy_description,
    pt.therapy_date
FROM
    PetDiagnosis pd
LEFT JOIN
    PetMedications pm ON pd.pet_id = pm.pet_id
LEFT JOIN
    PetTherapy pt ON pd.pet_id = pt.pet_id
ORDER BY
    pd.pet_id, pt.therapy_date;

}}}

== Додадени прашалници
'''''Самостојна изработка'''''
1. За секој ветеринарен центар да се прикажат вкупниот број на посети, вкупниот број на работни позиции за доктори и вкупниот број на посети изразени во проценти.
'''''Резултантна шема: (vet_center_name, total_visits, total_jobs, visit_percentage)''''' 
{{{
WITH VetCenterVisitData AS (
    SELECT 
        vc.id AS vet_center_id,
        vc.name AS vet_center_name,
        COUNT(pc.id) AS total_visits,
        COUNT(DISTINCT j.id) AS total_jobs
    FROM 
        vet_centers vc
    LEFT JOIN 
        pet_cares pc ON vc.id = pc.vetcentersID
    LEFT JOIN 
        jobs j ON vc.id = j.vetCentersID
    GROUP BY 
        vc.id
),
TotalVisitCount AS (
    SELECT 
        SUM(total_visits) AS total_visits_all_centers
    FROM 
        VetCenterVisitData
)
SELECT 
    vcd.vet_center_name,
    vcd.total_visits,
    vcd.total_jobs,
    ROUND((vcd.total_visits::decimal / tvc.total_visits_all_centers) * 100, 2) AS visit_percentage
FROM 
    VetCenterVisitData vcd,
    TotalVisitCount tvc
ORDER BY 
    visit_percentage DESC;
}}}


2. За секој корисник покрај неговото име и email, да се испечати email_Provider, вкупниот број на миленици кои што ги поседува корисникот, колку од нив се мачиња а колку кучиња и дали корисникот има посетено ветеринарен центар во Skopje.  
'''''Резултантна шема: (name, email, email_provider, total_pets, total_cats, total dogs, visited_vet_center_in_skopje)''''' 
{{{
SELECT 
    u.name,
    u.email,
    CASE 
        WHEN u.email LIKE '%@yahoo.com' THEN 'yahoo'
        WHEN u.email LIKE '%@gmail.com' THEN 'gmail'
        WHEN u.email LIKE '%@hotmail.com' THEN 'hotmail'
        ELSE 'other'
    END AS email_provider,
    COUNT(DISTINCT p.ID) AS total_pets,  -- Вкупно животни, избегнувајќи дуплирања
    SUM(CASE WHEN tp.kind_of_pet = 'Cat' THEN 1 ELSE 0 END) AS total_cats,  -- Вкупно мачки
    SUM(CASE WHEN tp.kind_of_pet = 'Dog' THEN 1 ELSE 0 END) AS total_dogs,  -- Вкупно кучиња
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM pets_visit_vet_centers pv 
            JOIN vet_centers vc ON pv.id_vet_centers = vc.ID
            JOIN cities c ON vc.citiesid = c.ID  -- Поврзување со табелата cities
            WHERE pv.id_pets IN (SELECT ID FROM pets WHERE usersID = u.ID)
            AND c.name = 'Skopje'  -- Проверка за Скопје
        ) THEN TRUE 
        ELSE FALSE 
    END AS visited_vet_center_in_skopje
FROM 
    users u
LEFT JOIN 
    pets p ON u.ID = p.usersID
LEFT JOIN 
    type_of_pets tp ON p.typeofpetsid = tp.ID  -- Поврзување со табелата type_of_pets
GROUP BY 
    u.ID, u.email;
}}}