Напредни извештаи од базата (SQL и складирани процедури)

1. Извештај: Број на настани по година и земја

Опис: Овој извештај го прикажува бројот на настани по година, групирани според земјата.

SELECT
    EXTRACT(YEAR FROM e.sql_date) AS year,
    a.country_code,
    COUNT(e.global_event_id) AS event_count
FROM
    events e
JOIN
    event_details ed ON e.global_event_id = ed.global_event_id
JOIN
    actors a ON ed.actor_id = a.actor_id
GROUP BY
    year, a.country_code
ORDER BY
    year DESC, event_count DESC;

---

2. Извештај: Топ 5 актери според бројот на споменувања

Опис: Овој извештај ги прикажува топ 5 актери според бројот на споменувања во настани.

SELECT
    a.actor_name,
    COUNT(ed.actor_id) AS mention_count
FROM
    event_details ed
JOIN
    actors a ON ed.actor_id = a.actor_id
GROUP BY
    a.actor_name
ORDER BY
    mention_count DESC
LIMIT 5;

---

3. Извештај: Просечен тон на настани по месец

Опис: Овој извештај ја пресметува просечната вредност на тонот на настаните за секој месец.

SELECT
    TO_CHAR(e.sql_date, 'YYYY-MM') AS month,
    AVG(e.avg_tone) AS average_tone
FROM
    events e
GROUP BY
    month
ORDER BY
    month DESC;

---

4. Извештај: Број на конфликти во квартален период по земја

Опис: Овој извештај го пресметува бројот на конфликти во тековниот квартал за секоја земја.

SELECT
    EXTRACT(YEAR FROM e.sql_date) AS year,
    EXTRACT(QUARTER FROM e.sql_date) AS quarter,
    a.country_code,
    COUNT(e.global_event_id) AS conflict_count
FROM
    events e
JOIN
    event_details ed ON e.global_event_id = ed.global_event_id
JOIN
    actors a ON ed.actor_id = a.actor_id
WHERE
    e.sql_date >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY
    year, quarter, a.country_code
ORDER BY
    year DESC, quarter DESC, conflict_count DESC;

---

5. Извештај: Конфликти според нивото на доверливост

Опис: Овој извештај ги групира настаните според нивото на доверливост на предвидувањето.

SELECT
    e.event_code,
    COUNT(e.global_event_id) AS event_count,
    ROUND(AVG(p.confidence_score), 2) AS avg_confidence
FROM
    events e
JOIN
    predictions p ON e.global_event_id = p.event_id
GROUP BY
    e.event_code
ORDER BY
    avg_confidence DESC;

---

6. Извештај: Конфликти според број на извори

Опис: Ги прикажува најзначајните конфликти според бројот на извори што ги известиле.

SELECT
    e.global_event_id,
    e.sql_date,
    e.event_code,
    e.num_sources
FROM
    events e
ORDER BY
    e.num_sources DESC
LIMIT 10;

---

7. Извештај: Аналитика на настани и интеракција на актери

Опис: Овој извештај ги анализира податоците од EVENT_ANALYTICS, вклучувајќи конфликтни коефициенти, број на спомнувања, и просечен тон за да се утврдат обрасци на интеракција меѓу актери и настани.

SELECT 
    ea.actor_id, 
    a.actor_name, 
    ea.event_id, 
    e.event_code, 
    ea.date, 
    ea.conflict_coefficient, 
    ea.mentions_count, 
    ea.avg_tone
FROM event_analytics ea
JOIN actors a ON ea.actor_id = a.actor_id
JOIN events e ON ea.event_id = e.global_event_id
WHERE ea.date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY ea.conflict_coefficient DESC;

8. Извештај: Идентификација на конфликтни жаришта

Опис: Овој извештај комбинира податоци од EVENTS, EVENT_ANALYTICS, PREDICTIONS, и CONFLICT_RISK за да идентификува зони со највисока веројатност за идни конфликти.

SELECT 
    l.full_name AS location, 
    COUNT(ea.event_id) AS event_count, 
    AVG(ea.conflict_coefficient) AS avg_conflict_risk, 
    SUM(ea.mentions_count) AS total_mentions
FROM event_analytics ea
JOIN locations l ON ea.event_id = l.location_id
WHERE ea.date >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY l.full_name
HAVING AVG(ea.conflict_coefficient) > 3.5
ORDER BY avg_conflict_risk DESC
LIMIT 10;

9. Извештај: Тренд на зголемување на конфликтите во последните 6 месеци

Опис: Ги прикажува земјите со најголемо зголемување на бројот на конфликти.

SELECT
    a.country_code,
    COUNT(e.global_event_id) AS conflict_count
FROM
    events e
JOIN
    event_details ed ON e.global_event_id = ed.global_event_id
JOIN
    actors a ON ed.actor_id = a.actor_id
WHERE
    e.sql_date >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY
    a.country_code
ORDER BY
    conflict_count DESC
LIMIT 10;

---

10. Складирана процедура: Додавање на нов корисник

Опис: Додава нов корисник во системот.

CREATE OR REPLACE PROCEDURE add_new_user(
    p_first_name VARCHAR,
    p_last_name VARCHAR,
    p_email VARCHAR,
    p_password_hash VARCHAR,
    p_role_id INT,
    p_register_date DATE
) AS $$
BEGIN
    INSERT INTO users (first_name, last_name, email, password_hash, role_id, register_date)
    VALUES (p_first_name, p_last_name, p_email, p_password_hash, p_role_id, p_register_date);
END;
$$ LANGUAGE plpgsql;

---

11. Складирана процедура: Додавање нова претплата

Опис: Ја додава претплатата на корисник за следење на конфликти.

CREATE OR REPLACE PROCEDURE add_subscription(
    p_user_id INT,
    p_plan_id INT,
    p_start_date DATE,
    p_end_date DATE,
    p_status VARCHAR
) AS $$
BEGIN
    INSERT INTO subscription (user_id, plan_id, start_date, end_date, status)
    VALUES (p_user_id, p_plan_id, p_start_date, p_end_date, p_status);
END;
$$ LANGUAGE plpgsql;

---

12. Складирана процедура: Ажурирање на статусот на корисник

Опис: Оваа процедура го ажурира статусот на даден корисник.

CREATE OR REPLACE PROCEDURE update_user_status(
    p_user_id INT,
    p_role_id INT
) AS $$
BEGIN
    UPDATE users
    SET role_id = p_role_id
    WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql;

---

13. Складирана процедура: Генерирање на известувања за конфликти

Опис: Оваа процедура автоматски генерира известувања за корисниците кои следат одредени конфликти.

CREATE OR REPLACE PROCEDURE generate_notifications() AS $$
BEGIN
    INSERT INTO notifications (user_id, event_id, notification_date, status)
    SELECT
        s.user_id,
        e.global_event_id,
        CURRENT_DATE,
        'Pending'
    FROM
        subscription s
    JOIN
        events e ON s.plan_id = e.event_code
    WHERE
        e.sql_date >= CURRENT_DATE - INTERVAL '7 days';
END;
$$ LANGUAGE plpgsql;