== Напредни извештаи од базата (SQL) 

=== Дестинации со најголем број на разновидни настани
Идентификување на дестинации со најголем број на различни видови на настани (концерти, фестивали, саеми и сл.).
Овој извештај е корисен за корисници што сакаат разновидно патување.
{{{
SELECT d.location_name AS destination, COUNT(DISTINCT e.event_type) AS event_number
FROM travel_sage.destination d
JOIN travel_sage.event e ON d.id_destination = e.id_destination
GROUP BY d.location_name
HAVING COUNT(DISTINCT e.event_type) = (
    SELECT MAX(event_number)
    FROM (
        SELECT COUNT(DISTINCT e.event_type) AS event_number
        FROM travel_sage.destination d
        JOIN travel_sage.event e ON d.id_destination = e.id_destination
        GROUP BY d.location_name
    ) AS temp
);
}}}


=== Настани на одредена дестинација
Прикажување на сите категории на настани за одредена дестинација.
{{{
SELECT DISTINCT e.event_type AS event_type
FROM travel_sage.event e
JOIN travel_sage.destination d ON e.id_destination = d.id_destination
WHERE d.location_name = 'Ohrid';
}}}


=== Топ дестинации според просечен квалитет на рецензии
Прикажува првите 10 дестинации со највисок просечен квалитет на рецензии.
{{{
SELECT d.location_name AS destination_name, 
       AVG(r.quality) AS average_quality
FROM travel_sage.destination d
JOIN travel_sage.review r ON d.id_destination = r.id_destination
GROUP BY d.location_name
ORDER BY average_quality DESC
LIMIT 10;
}}}


=== Дестинации со најголем број на активности под различни ценовни опсези
Го прикажува бројот на активности по ценовни групи (<20, <50, <100).
{{{
SELECT d.location_name AS destination_name,
       SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) AS activities_under20,
       SUM(CASE WHEN a.amount < 50 THEN 1 ELSE 0 END) AS activities_under50,
       SUM(CASE WHEN a.amount < 100 THEN 1 ELSE 0 END) AS activities_under100
FROM travel_sage.destination d
JOIN travel_sage.activity a ON d.id_destination = a.id_destination
GROUP BY d.location_name
ORDER BY activities_under20 DESC, activities_under50 DESC, activities_under100 DESC;
}}}


=== Дестинации со најголем процент на достапни(евтини) активности
Овој извештај покажува кои дестинации имаат најголем удел на евтини активности (<20) во однос на вкупната понуда.
{{{
SELECT d.location_name AS destination_name,
       COUNT(a.id_activity) AS total_activities,
       SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) AS cheap_activities,
       (SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) * 100.0 / COUNT(a.id_activity)) AS percent_cheap_activities
FROM travel_sage.destination d
JOIN travel_sage.activity a ON d.id_destination = a.id_destination
GROUP BY d.location_name
HAVING COUNT(a.id_activity) > 0
ORDER BY percent_cheap_activities DESC;
}}}


=== Топ корисници со најголем број напишани рецензии
Идентификува најактивни корисници.
{{{
SELECT u.id_user AS user_id, 
       COUNT(r.id_review) AS review_count
FROM travel_sage.users u
JOIN travel_sage.review r ON u.id_user = r.id_user
GROUP BY u.id_user
ORDER BY review_count DESC
LIMIT 10;
}}}


=== Најисплатливи пакети (Best value for per day)
Пресметува цена по ден за пакетите и враќа најисплатлив пакет.
{{{
SELECT p.package_name, 
       (p.price / GREATEST(DATE_PART('day', p.end_date - p.start_date), 1)) AS price_per_day
FROM travel_sage.package p
ORDER BY price_per_day ASC
LIMIT 1;
}}}


=== Приказ на бројот на резервации по месец
Следење на тренд на резервации низ времето.
{{{
SELECT DATE_TRUNC('month', r.time_point) AS month, 
       COUNT(r.id_reservation) AS reservation_count
FROM travel_sage.reservation r
GROUP BY month
ORDER BY month;
}}}


=== Приказ на бројот на нови корисници по месец
Прикажува број на новорегистрирани корисници по месец.
{{{
SELECT DATE_TRUNC('month', u.birth_date) AS month, 
       COUNT(u.id_user) AS new_user_count
FROM travel_sage.users u
GROUP BY month
ORDER BY month;
}}}


=== Приказ на бројот на пакети во резервации по месец
Ги брои пакетите што се резервирани по месец. Можност за следење на популарноста на различни пакети во текот на годината.
{{{
SELECT DATE_TRUNC('month', p.start_date) AS month, 
       COUNT(pr.id_package) AS reserved_package_count
FROM travel_sage.package p
JOIN travel_sage.package_reservation pr ON p.id_package = pr.id_package
GROUP BY month
ORDER BY month;
}}}


=== Приказ на бројот на настани по месец
Корисно е за да се согледа колку настани се организирани секој месец.
{{{
SELECT DATE_TRUNC('month', e.start_date) AS month, 
       COUNT(e.id_event) AS event_count
FROM travel_sage.event e
GROUP BY month
ORDER BY month;
}}}


=== Приказ на најпопуларните активности на месечно ниво
Овде е прикажана бројката на активности по категорија, со цел да се утврди која активност е најпопуларна во текот на месецот.
{{{
SELECT DATE_TRUNC('month', r.time_point) AS month, 
       a.category AS activity_category, 
       COUNT(a.id_activity) AS activity_count
FROM travel_sage.reservation r
JOIN travel_sage.activity_reservation ar ON r.id_reservation = ar.id_reservation
JOIN travel_sage.activity a ON ar.id_activity = a.id_activity
GROUP BY month, a.category
ORDER BY month, activity_count DESC;
}}}


=== ПАктивност на корисници по месец
Статистика за секој корисник по месец, вклучувајќи број на резервации и активности. Така, се гледа активноста на корисниците и популарноста на нивните активности.
{{{
SELECT DATE_TRUNC('month', r.time_point) AS month, 
       u.first_name, u.last_name,
       COUNT(DISTINCT r.id_reservation) AS reservation_count,
       COUNT(DISTINCT ar.id_activity) AS activity_count
FROM travel_sage.users u
JOIN travel_sage.reservation r ON u.id_user = r.id_user
LEFT JOIN travel_sage.activity_reservation ar ON r.id_reservation = ar.id_reservation
GROUP BY month, u.id_user
ORDER BY month, reservation_count DESC;
}}}


=== Просечен попуст на премиум корисници
Пресметува просечен попуст што го добиваат премиум корисници.
{{{
SELECT AVG(pu.discount) AS average_discount
FROM travel_sage.premium pu;
}}}


=== Детален преглед на дестинација
Детален приказ на одредена туристичка дестинација т.е. покрај основните информации (име, опис, држава), ги вклучува и сите поврзани тагови, настани, активности и туристички пакети, како и тековни метеоролошки услови.
{{{
SELECT 
    d.id_destination,
    d.location_name,
    d.location_desc,
    d.country,
    ARRAY_AGG(DISTINCT t.tag_name) AS tags,
    ARRAY_AGG(DISTINCT e.event_name) AS events,
    ARRAY_AGG(DISTINCT a.activity_name) AS activities,
    ARRAY_AGG(DISTINCT p.package_name) AS packages,
    m.current_temp,
    m.weather_condition
FROM travel_sage.destination d
LEFT JOIN travel_sage.destination_tag dt ON d.id_destination = dt.id_destination
LEFT JOIN travel_sage.tag t ON dt.id_tag = t.id_tag
LEFT JOIN travel_sage.event e ON d.id_destination = e.id_destination
LEFT JOIN travel_sage.activity a ON d.id_destination = a.id_destination
LEFT JOIN travel_sage.package p ON d.id_destination = p.id_destination
LEFT JOIN travel_sage.meteorological_condition m ON d.id_destination = m.id_destination
WHERE d.id_destination = 2
GROUP BY d.id_destination, d.location_name, d.location_desc, d.country, m.current_temp, m.weather_condition;
}}}


=== Извештај за сите корисници според месец и година
Овој извештај овозможува следење на растот на базата на корисници преку време.
* Се групираат корисниците според месецот и годината на нивната регистрација (или датум на креирање во системот).
* За секој месец се прикажува бројот на нови корисници што се приклучиле.

Со ова се добива тренд анализа на растот на платформата:
* Може да се забележат периоди кога бројот на регистрации нагло расте (на пример, после маркетинг кампања или воведување на нова функционалност).
* Може да се идентификуваат и „слаби“ месеци кога има малку нови регистрации.
{{{
CREATE OR REPLACE FUNCTION GetUsersReport(month INT, year INT)
RETURNS TABLE(
    UserFullName varchar(100),
    TotalReservations bigint,
    TotalActivities bigint,
    TotalSpent numeric,
    AverageReview numeric
) 
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
       u.first_name || ' ' || u.last_name AS UserFullName,
       COUNT(DISTINCT r.id_reservation) AS TotalReservations,
       COUNT(DISTINCT ar.id_activity) AS TotalActivities,
       COALESCE(SUM(p.price),0) AS TotalSpent,
       COALESCE(AVG(rv.quality),0) AS AverageReview
    FROM travel_sage.users u
    LEFT JOIN travel_sage.reservation r ON u.id_user = r.id_user
    LEFT JOIN travel_sage.activity_reservation ar ON r.id_reservation = ar.id_reservation
    LEFT JOIN travel_sage.package_reservation pr ON r.id_reservation = pr.id_reservation
    LEFT JOIN travel_sage.package p ON pr.id_package = p.id_package
    LEFT JOIN travel_sage.review rv ON u.id_user = rv.id_user
    WHERE EXTRACT(MONTH FROM r.time_point) = month AND EXTRACT(YEAR FROM r.time_point) = year
    GROUP BY u.id_user;
END;
$$;
}}}


=== Извештај за сите пакети според месец и година
Овој извештај прикажува како пакетите (туристички аранжмани) се користат низ времето.
* Се групираат пакетите според месецот и годината на нивниот почетен датум.
* Се брои колку пати тие пакети биле резервирани во тој период.

На овој начин може да се следи популарноста на различни пакети во текот на годината:
* Може да се открие кои пакети се најбарани во летниот период, а кои во зимскиот.
* Овозможува да се прави подобро планирање на ресурсите и промоциите (на пример, да се засили реклама за пакет кој е популарен пред лето).
{{{
CREATE OR REPLACE FUNCTION GetPackagesReport(month INT, year INT)
RETURNS TABLE(
    PackageName varchar(100),
    ReservationCount bigint,
    TotalRevenue numeric,
    AvgDuration numeric,
    PricePerDay numeric
) 
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
       p.package_name,
       COUNT(pr.id_reservation) AS ReservationCount,
       SUM(p.price) AS TotalRevenue,
       AVG(DATE_PART('day', p.end_date - p.start_date)) AS AvgDuration,
       AVG(p.price / GREATEST(DATE_PART('day', p.end_date - p.start_date),1)) AS PricePerDay
    FROM travel_sage.package p
    JOIN travel_sage.package_reservation pr ON p.id_package = pr.id_package
    JOIN travel_sage.reservation r ON pr.id_reservation = r.id_reservation
    WHERE EXTRACT(MONTH FROM r.time_point) = month AND EXTRACT(YEAR FROM r.time_point) = year
    GROUP BY p.package_name;
END;
$$;
}}}