OtherTopics: other_topics.sql

-- =============================================================
--  CityFix - other_topics.sql  (фаза P9)
--  Индекси, оптимизации и безбедносни мерки.
--
--  Редослед на извршување:
--    1. schema_creation.sql
--    2. advanced_db.sql
--    3. other_topics.sql     (оваа скрипта)
--    4. data_load.sql
--  Скриптата може да се извршува повеќепати.
-- =============================================================

SET search_path TO project;
SET client_min_messages TO warning;


-- =============================================================
--  1. ИНДЕКСИ
-- =============================================================

-- историјата на пријава и последниот статус (тригери, UC0004, погледи, извештаи)
CREATE INDEX IF NOT EXISTS idx_status_logs_report_changed
    ON status_logs (report_id, changed_at, log_id);

-- активните пријави на работник (UC0005) и оптовареност (UC0007)
CREATE INDEX IF NOT EXISTS idx_assignments_worker
    ON assignments (worker_id, report_id);

-- пријавите на граѓанин (UC0004)
CREATE INDEX IF NOT EXISTS idx_reports_citizen
    ON reports (citizen_id, created_at);

-- извештаи за даден период
CREATE INDEX IF NOT EXISTS idx_reports_created
    ON reports (created_at);

-- слични активни пријави во близина; делумен индекс само за отворените пријави
CREATE INDEX IF NOT EXISTS idx_reports_active_location
    ON reports (category_id, latitude, longitude)
    WHERE status NOT IN ('resolved', 'rejected');

-- коментари и фотографии на пријава (UC0004)
CREATE INDEX IF NOT EXISTS idx_comments_report ON comments (report_id);
CREATE INDEX IF NOT EXISTS idx_photos_report   ON photos (report_id);


-- =============================================================
--  2. ОПТИМИЗИРАНА ФУНКЦИЈА ЗА СЛИЧНИ ПРИЈАВИ
--  Пред пресметката на точното растојание, пријавите се филтрираат
--  со правоаголник околу точката (условите BETWEEN), кој може да го
--  користи делумниот индекс idx_reports_active_location.
-- =============================================================
CREATE OR REPLACE FUNCTION find_similar_reports(p_category_id INTEGER,
                                                p_latitude NUMERIC,
                                                p_longitude NUMERIC,
                                                p_radius_m DOUBLE PRECISION DEFAULT 150,
                                                p_exclude_report_id INTEGER DEFAULT NULL)
RETURNS TABLE (report_id INTEGER, description TEXT, status TEXT,
               created_at TIMESTAMP, distance_m DOUBLE PRECISION)
LANGUAGE sql STABLE AS $$
    SELECT r.report_id, r.description, r.status::TEXT, r.created_at,
           ROUND(distance_m(p_latitude, p_longitude, r.latitude, r.longitude)::NUMERIC, 1)
    FROM project.reports r
    WHERE r.category_id = p_category_id
      AND r.status NOT IN ('resolved', 'rejected')
      AND r.latitude  BETWEEN p_latitude - p_radius_m / 111320.0
                          AND p_latitude + p_radius_m / 111320.0
      AND r.longitude BETWEEN p_longitude - p_radius_m / (111320.0 * cos(radians(p_latitude)))
                          AND p_longitude + p_radius_m / (111320.0 * cos(radians(p_latitude)))
      AND r.report_id IS DISTINCT FROM p_exclude_report_id
      AND distance_m(p_latitude, p_longitude, r.latitude, r.longitude) <= p_radius_m
    ORDER BY 5;
$$;


-- =============================================================
--  3. МАТЕРИЈАЛИЗИРАН ПОГЛЕД ЗА КВАРТАЛНИТЕ ИЗВЕШТАИ (P6)
--  Времињата за одговор и решавање по пријава се пресметуваат еднаш
--  и се чуваат; се освежуваат секоја ноќ со позадинската задача.
-- =============================================================
DROP MATERIALIZED VIEW IF EXISTS mv_report_times;
CREATE MATERIALIZED VIEW mv_report_times AS
SELECT r.report_id, r.category_id, r.status::TEXT AS status, r.created_at,
       date_trunc('quarter', r.created_at) AS quarter,
       MIN(l.changed_at) FILTER (WHERE l.status = 'received') AS received_at,
       MIN(l.changed_at) FILTER (WHERE l.status = 'resolved') AS resolved_at
FROM reports r
JOIN status_logs l ON l.report_id = r.report_id
GROUP BY r.report_id;

CREATE UNIQUE INDEX idx_mv_report_times_report   ON mv_report_times (report_id);
CREATE INDEX        idx_mv_report_times_quarter  ON mv_report_times (quarter, category_id);

-- Освежувањето бара сопственик на погледот, па функцијата е SECURITY DEFINER
-- со фиксиран search_path (за да не може да се подметне објект со исто име).
CREATE OR REPLACE FUNCTION refresh_report_stats()
RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER
SET search_path = project, pg_temp AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY project.mv_report_times;
END $$;

SET client_min_messages TO notice;
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_cron') THEN
        PERFORM cron.schedule('cityfix-refresh-report-stats', '30 2 * * *',
                              'SELECT project.refresh_report_stats()');
        RAISE NOTICE 'Освежувањето на mv_report_times е закажано со pg_cron.';
    ELSE
        RAISE NOTICE 'pg_cron не е достапен: refresh_report_stats() закажете ја однадвор.';
    END IF;
END $$;
SET client_min_messages TO warning;


-- =============================================================
--  4. БЕЗБЕДНОСТ
-- =============================================================

-- 4.1 Фиксиран search_path за сите функции и процедури во шемата,
--     за да не можат да се подметнат табели или функции со исто име
--     во друга шема (напад преку search_path).
DO $$
DECLARE
    f RECORD;
BEGIN
    FOR f IN SELECT p.oid::regprocedure AS sig, p.prokind
             FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
             WHERE n.nspname = 'project' AND p.prokind IN ('f', 'p')
    LOOP
        EXECUTE format('ALTER %s %s SET search_path = project, pg_temp',
                       CASE f.prokind WHEN 'p' THEN 'PROCEDURE' ELSE 'FUNCTION' END, f.sig);
    END LOOP;
END $$;

-- 4.2 Јавниот поглед не смее да открие податоци преку функции во WHERE.
ALTER VIEW v_public_reports SET (security_barrier = true);

-- 4.3 Безбедно динамичко пребарување на пријави за администраторите.
--     Колоната за подредување се проверува со листа на дозволени вредности
--     и се вметнува со format('%I') како идентификатор; сите вредности
--     од корисникот се предаваат како параметри (USING), никогаш како текст.
CREATE OR REPLACE FUNCTION search_reports(p_text TEXT DEFAULT NULL,
                                          p_status TEXT DEFAULT NULL,
                                          p_category_id INTEGER DEFAULT NULL,
                                          p_sort_column TEXT DEFAULT 'created_at',
                                          p_descending BOOLEAN DEFAULT TRUE,
                                          p_limit INTEGER DEFAULT 50)
RETURNS TABLE (report_id INTEGER, category TEXT, description TEXT, status TEXT,
               priority TEXT, created_at TIMESTAMP)
LANGUAGE plpgsql STABLE
SET search_path = project, pg_temp AS $$
DECLARE
    v_sql TEXT;
BEGIN
    IF p_sort_column NOT IN ('created_at', 'priority', 'status', 'report_id') THEN
        RAISE EXCEPTION 'Недозволена колона за подредување: %', p_sort_column;
    END IF;

    v_sql := format(
        'SELECT r.report_id, c.name::TEXT, r.description, r.status::TEXT,
                r.priority::TEXT, r.created_at
         FROM reports r
         JOIN categories c ON c.category_id = r.category_id
         WHERE ($1 IS NULL OR r.description ILIKE ''%%'' || $1 || ''%%''
                           OR r.location_text ILIKE ''%%'' || $1 || ''%%'')
           AND ($2 IS NULL OR r.status = $2)
           AND ($3 IS NULL OR r.category_id = $3)
         ORDER BY r.%I %s, r.report_id
         LIMIT $4',
        p_sort_column,
        CASE WHEN p_descending THEN 'DESC' ELSE 'ASC' END);

    RETURN QUERY EXECUTE v_sql
        USING p_text, p_status, p_category_id, LEAST(GREATEST(p_limit, 1), 500);
END $$;

-- 4.4 Никој освен сопственикот нема автоматски права врз објектите на шемата.
REVOKE ALL ON SCHEMA project FROM PUBLIC;
REVOKE ALL ON ALL TABLES    IN SCHEMA project FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA project FROM PUBLIC;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA project FROM PUBLIC;
REVOKE ALL ON ALL PROCEDURES IN SCHEMA project FROM PUBLIC;

-- 4.5 Улоги со најмали потребни права и заштита на ниво на ред (RLS).
--     Улогите се креираат само ако корисникот има право CREATEROLE.
--     Корисникот со кој се најавува апликацијата треба да биде член на cityfix_app.
SET client_min_messages TO notice;
DO $$
DECLARE
    v_can_create BOOLEAN;
BEGIN
    SELECT rolcreaterole OR rolsuper INTO v_can_create
    FROM pg_roles WHERE rolname = current_user;

    IF v_can_create THEN
        IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cityfix_app') THEN
            CREATE ROLE cityfix_app NOLOGIN;
        END IF;
        IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cityfix_citizen') THEN
            CREATE ROLE cityfix_citizen NOLOGIN;
        END IF;
        IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cityfix_analyst') THEN
            CREATE ROLE cityfix_analyst NOLOGIN;
        END IF;
        IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cityfix_public') THEN
            CREATE ROLE cityfix_public NOLOGIN;
        END IF;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cityfix_app') THEN
        RAISE NOTICE 'Корисникот нема право CREATEROLE: улогите и RLS не се поставени.';
        RETURN;
    END IF;

    -- апликација: читање и внесување; без бришење, без DDL; историјата само се дополнува
    GRANT USAGE ON SCHEMA project TO cityfix_app, cityfix_citizen, cityfix_analyst, cityfix_public;
    GRANT SELECT, INSERT, UPDATE ON admins, workers, citizens, categories,
                                    reports, photos, comments, assignments TO cityfix_app;
    GRANT SELECT, INSERT ON status_logs TO cityfix_app;
    GRANT SELECT ON v_public_reports, v_report_overview, v_worker_workload,
                    mv_report_times TO cityfix_app;
    GRANT USAGE ON ALL SEQUENCES IN SCHEMA project TO cityfix_app;
    GRANT EXECUTE ON ALL FUNCTIONS  IN SCHEMA project TO cityfix_app;
    GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA project TO cityfix_app;

    -- граѓанин (за идно директно поврзување, на пример мобилна апликација):
    -- ги гледа само своите пријави и јавниот преглед
    GRANT SELECT ON reports, categories, v_public_reports TO cityfix_citizen;

    -- аналитичар: извештаи без лични податоци (правата се по колони)
    GRANT SELECT ON reports, status_logs, assignments, categories,
                    mv_report_times, v_worker_workload TO cityfix_analyst;
    GRANT SELECT (worker_id, full_name) ON workers  TO cityfix_analyst;
    GRANT SELECT (citizen_id)           ON citizens TO cityfix_analyst;
    GRANT EXECUTE ON FUNCTION distance_m(NUMERIC, NUMERIC, NUMERIC, NUMERIC),
                              sla_days(TEXT), priority_rank(TEXT) TO cityfix_analyst;

    -- јавен пристап: само јавниот поглед и категориите
    GRANT SELECT ON v_public_reports, categories TO cityfix_public;

    -- RLS: граѓанинот ги гледа само редовите со неговиот citizen_id,
    -- кој апликацијата го поставува со SET cityfix.citizen_id = ...
    ALTER TABLE reports ENABLE ROW LEVEL SECURITY;
    IF EXISTS (SELECT 1 FROM pg_policies WHERE schemaname = 'project' AND tablename = 'reports') THEN
        DROP POLICY IF EXISTS reports_app_all      ON reports;
        DROP POLICY IF EXISTS reports_analyst_read ON reports;
        DROP POLICY IF EXISTS reports_citizen_own  ON reports;
    END IF;
    CREATE POLICY reports_app_all ON reports
        TO cityfix_app USING (true) WITH CHECK (true);
    CREATE POLICY reports_analyst_read ON reports
        FOR SELECT TO cityfix_analyst USING (true);
    CREATE POLICY reports_citizen_own ON reports
        FOR SELECT TO cityfix_citizen
        USING (citizen_id = NULLIF(current_setting('cityfix.citizen_id', true), '')::INTEGER);

    RAISE NOTICE 'Улогите cityfix_app, cityfix_citizen, cityfix_analyst и cityfix_public се поставени.';
END $$;