== Напредни извештаи од базата (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;
}}}