AdvancedDatabaseDevelopment: advanced_db.sql

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

SET search_path TO project;
SET client_min_messages TO warning;

-- Тригерите и погледите зависат од колоните што се менуваат подолу, па прво се бришат.
DROP TRIGGER IF EXISTS reports_before_insert ON reports;
DROP TRIGGER IF EXISTS reports_before_status_update ON reports;
DROP TRIGGER IF EXISTS status_logs_before_insert ON status_logs;
DROP TRIGGER IF EXISTS status_logs_after_insert ON status_logs;
DROP TRIGGER IF EXISTS reports_status_consistency ON reports;
DROP TRIGGER IF EXISTS status_logs_immutable ON status_logs;
DROP TRIGGER IF EXISTS assignments_before_insert ON assignments;
DROP TRIGGER IF EXISTS comments_before_insert ON comments;
DROP MATERIALIZED VIEW IF EXISTS mv_report_times;   -- се креира повторно во other_topics.sql (P9)
DROP VIEW IF EXISTS v_public_reports;
DROP VIEW IF EXISTS v_report_overview;
DROP VIEW IF EXISTS v_worker_workload;


-- =============================================================
--  1. ДОМЕНИ
-- =============================================================
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace
                   WHERE n.nspname = 'project' AND t.typname = 'email_address') THEN
        CREATE DOMAIN project.email_address AS VARCHAR(150)
            CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace
                   WHERE n.nspname = 'project' AND t.typname = 'phone_number') THEN
        CREATE DOMAIN project.phone_number AS VARCHAR(20)
            CHECK (VALUE ~ '^\+?[0-9]{6,19}$');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace
                   WHERE n.nspname = 'project' AND t.typname = 'report_status') THEN
        CREATE DOMAIN project.report_status AS VARCHAR(20)
            CHECK (VALUE IN ('submitted', 'received', 'in_progress', 'resolved', 'rejected'));
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace
                   WHERE n.nspname = 'project' AND t.typname = 'priority_level') THEN
        CREATE DOMAIN project.priority_level AS VARCHAR(10)
            CHECK (VALUE IN ('low', 'medium', 'high', 'urgent'));
    END IF;
END $$;

-- Колоните ги користат домените наместо посебните CHECK ограничувања од P2.
ALTER TABLE admins   DROP CONSTRAINT IF EXISTS ck_admins_email;
ALTER TABLE workers  DROP CONSTRAINT IF EXISTS ck_workers_email;
ALTER TABLE citizens DROP CONSTRAINT IF EXISTS ck_citizens_email;
ALTER TABLE citizens DROP CONSTRAINT IF EXISTS ck_citizens_phone;
ALTER TABLE reports  DROP CONSTRAINT IF EXISTS ck_reports_status;
ALTER TABLE reports  DROP CONSTRAINT IF EXISTS ck_reports_priority;
ALTER TABLE status_logs DROP CONSTRAINT IF EXISTS ck_status_logs_status;

ALTER TABLE admins      ALTER COLUMN email    TYPE email_address;
ALTER TABLE workers     ALTER COLUMN email    TYPE email_address;
ALTER TABLE citizens    ALTER COLUMN email    TYPE email_address;
ALTER TABLE citizens    ALTER COLUMN phone    TYPE phone_number;
ALTER TABLE reports     ALTER COLUMN status   TYPE report_status;
ALTER TABLE reports     ALTER COLUMN priority TYPE priority_level;
ALTER TABLE status_logs ALTER COLUMN status   TYPE report_status;


-- =============================================================
--  2. ПОМОШНИ ФУНКЦИИ
-- =============================================================

-- Дозволени премини помеѓу статусите на пријавата.
CREATE OR REPLACE FUNCTION status_transition_allowed(p_from TEXT, p_to TEXT)
RETURNS BOOLEAN LANGUAGE sql IMMUTABLE AS $$
    SELECT (p_from, p_to) IN (('submitted',   'received'),
                              ('submitted',   'rejected'),
                              ('received',    'in_progress'),
                              ('received',    'rejected'),
                              ('in_progress', 'resolved'),
                              ('in_progress', 'rejected'));
$$;

-- Нумерички ранг на приоритетот (low = 1 ... urgent = 4) и обратно.
CREATE OR REPLACE FUNCTION priority_rank(p TEXT)
RETURNS INTEGER LANGUAGE sql IMMUTABLE AS $$
    SELECT CASE p WHEN 'low' THEN 1 WHEN 'medium' THEN 2
                  WHEN 'high' THEN 3 WHEN 'urgent' THEN 4 END;
$$;

CREATE OR REPLACE FUNCTION priority_from_rank(r INTEGER)
RETURNS TEXT LANGUAGE sql IMMUTABLE AS $$
    SELECT (ARRAY['low', 'medium', 'high', 'urgent'])[GREATEST(1, LEAST(4, r))];
$$;

-- Рок за реакција (SLA) во денови според приоритетот.
CREATE OR REPLACE FUNCTION sla_days(p TEXT)
RETURNS INTEGER LANGUAGE sql IMMUTABLE AS $$
    SELECT CASE p WHEN 'urgent' THEN 1 WHEN 'high' THEN 3
                  WHEN 'medium' THEN 7 ELSE 14 END;
$$;

-- Растојание во метри помеѓу две точки (формула на Haversine).
CREATE OR REPLACE FUNCTION distance_m(lat1 NUMERIC, lon1 NUMERIC, lat2 NUMERIC, lon2 NUMERIC)
RETURNS DOUBLE PRECISION LANGUAGE sql IMMUTABLE AS $$
    SELECT 2 * 6371000 * asin(sqrt(
               power(sin(radians(lat2 - lat1) / 2), 2)
             + cos(radians(lat1)) * cos(radians(lat2))
             * power(sin(radians(lon2 - lon1) / 2), 2)));
$$;

-- Активни пријави од иста категорија во даден радиус (за проверка на дупликати).
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 IS NOT NULL
      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. ТРИГЕРИ - животен циклус и конзистентност на статусот
-- =============================================================

-- 3.1 Нова пријава: статусот мора да биде 'submitted'. Ако во близина веќе има
--     барем 2 активни пријави за истиот проблем, приоритетот се крева на најмалку 'high'.
CREATE OR REPLACE FUNCTION trg_reports_before_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_similar INTEGER;
BEGIN
    IF NEW.status IS DISTINCT FROM 'submitted' THEN
        RAISE EXCEPTION 'Новата пријава мора да има статус submitted (добиено: %)', NEW.status;
    END IF;
    IF NEW.latitude IS NOT NULL THEN
        SELECT COUNT(*) INTO v_similar
        FROM find_similar_reports(NEW.category_id, NEW.latitude, NEW.longitude, 150);
        IF v_similar >= 2 AND priority_rank(NEW.priority) < priority_rank('high') THEN
            NEW.priority := 'high';
        END IF;
    END IF;
    RETURN NEW;
END $$;

CREATE TRIGGER reports_before_insert
    BEFORE INSERT ON reports
    FOR EACH ROW EXECUTE FUNCTION trg_reports_before_insert();

-- 3.2 Промена на статусот директно во reports мора да биде дозволен премин.
CREATE OR REPLACE FUNCTION trg_reports_before_status_update()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.status IS DISTINCT FROM OLD.status
       AND NOT status_transition_allowed(OLD.status, NEW.status) THEN
        RAISE EXCEPTION 'Недозволена промена на статусот на пријавата % од % во %',
                        OLD.report_id, OLD.status, NEW.status;
    END IF;
    RETURN NEW;
END $$;

CREATE TRIGGER reports_before_status_update
    BEFORE UPDATE OF status ON reports
    FOR EACH ROW EXECUTE FUNCTION trg_reports_before_status_update();

-- 3.3 Нов запис во историјата: првиот мора да биде 'submitted' (без работник),
--     секој следен мора да биде дозволен премин, да не е постар од претходниот
--     и да го направи работник кој е доделен на пријавата.
CREATE OR REPLACE FUNCTION trg_status_logs_before_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_last status_logs%ROWTYPE;
BEGIN
    SELECT * INTO v_last
    FROM status_logs
    WHERE report_id = NEW.report_id
    ORDER BY changed_at DESC, log_id DESC
    LIMIT 1;

    IF NOT FOUND THEN
        IF NEW.status <> 'submitted' OR NEW.worker_id IS NOT NULL THEN
            RAISE EXCEPTION 'Првиот запис за пријавата % мора да биде submitted, без работник',
                            NEW.report_id;
        END IF;
        RETURN NEW;
    END IF;

    IF NOT status_transition_allowed(v_last.status, NEW.status) THEN
        RAISE EXCEPTION 'Недозволена промена на статусот на пријавата % од % во %',
                        NEW.report_id, v_last.status, NEW.status;
    END IF;
    IF NEW.changed_at < v_last.changed_at THEN
        RAISE EXCEPTION 'Промената не може да биде постара од претходната (%)', v_last.changed_at;
    END IF;
    IF NEW.worker_id IS NULL THEN
        RAISE EXCEPTION 'Промената на статусот мора да ја направи работник';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM assignments a
                   WHERE a.report_id = NEW.report_id AND a.worker_id = NEW.worker_id) THEN
        RAISE EXCEPTION 'Работникот % не е доделен на пријавата %', NEW.worker_id, NEW.report_id;
    END IF;
    RETURN NEW;
END $$;

CREATE TRIGGER status_logs_before_insert
    BEFORE INSERT ON status_logs
    FOR EACH ROW EXECUTE FUNCTION trg_status_logs_before_insert();

-- 3.4 По внесот во историјата, статусот во reports автоматски се усогласува.
CREATE OR REPLACE FUNCTION trg_status_logs_after_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    UPDATE reports
    SET status = NEW.status
    WHERE report_id = NEW.report_id
      AND status IS DISTINCT FROM NEW.status;
    RETURN NULL;
END $$;

CREATE TRIGGER status_logs_after_insert
    AFTER INSERT ON status_logs
    FOR EACH ROW EXECUTE FUNCTION trg_status_logs_after_insert();

-- 3.5 Одложено ограничување: на крајот на трансакцијата статусот во reports
--     мора да е еднаков на последниот запис во историјата.
CREATE OR REPLACE FUNCTION trg_reports_status_consistency()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_current TEXT;
    v_logged  TEXT;
BEGIN
    SELECT status INTO v_current FROM reports WHERE report_id = NEW.report_id;
    IF NOT FOUND THEN
        RETURN NULL;   -- пријавата е избришана во истата трансакција
    END IF;
    SELECT status INTO v_logged
    FROM status_logs
    WHERE report_id = NEW.report_id
    ORDER BY changed_at DESC, log_id DESC
    LIMIT 1;
    IF v_logged IS DISTINCT FROM v_current THEN
        RAISE EXCEPTION 'Статусот на пријавата % (%) не одговара на историјата (%)',
                        NEW.report_id, v_current, COALESCE(v_logged, 'нема запис');
    END IF;
    RETURN NULL;
END $$;

CREATE CONSTRAINT TRIGGER reports_status_consistency
    AFTER INSERT OR UPDATE OF status ON reports
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW EXECUTE FUNCTION trg_reports_status_consistency();

-- 3.6 Историјата на статуси не смее да се менува ни брише (ревизорска трага).
--     Дозволено е само каскадно бришење заедно со пријавата.
CREATE OR REPLACE FUNCTION trg_status_logs_immutable()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF TG_OP = 'DELETE'
       AND NOT EXISTS (SELECT 1 FROM reports WHERE report_id = OLD.report_id) THEN
        RETURN OLD;    -- каскадно бришење: пријавата веќе е избришана
    END IF;
    RAISE EXCEPTION 'Историјата на статусите не може да се менува ни брише';
END $$;

CREATE TRIGGER status_logs_immutable
    BEFORE UPDATE OR DELETE ON status_logs
    FOR EACH ROW EXECUTE FUNCTION trg_status_logs_immutable();


-- =============================================================
--  4. ТРИГЕРИ - правила за доделувања и коментари
-- =============================================================

-- 4.1 Затворена пријава не може да се доделува; доделувањето не смее
--     да биде пред поднесувањето на пријавата.
CREATE OR REPLACE FUNCTION trg_assignments_before_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_report reports%ROWTYPE;
BEGIN
    SELECT * INTO v_report FROM reports WHERE report_id = NEW.report_id;
    IF v_report.status IN ('resolved', 'rejected') THEN
        RAISE EXCEPTION 'Пријавата % е затворена (%) и не може да се доделува',
                        NEW.report_id, v_report.status;
    END IF;
    IF NEW.assigned_at < v_report.created_at THEN
        RAISE EXCEPTION 'Доделувањето не може да биде пред поднесувањето на пријавата';
    END IF;
    RETURN NEW;
END $$;

CREATE TRIGGER assignments_before_insert
    BEFORE INSERT ON assignments
    FOR EACH ROW EXECUTE FUNCTION trg_assignments_before_insert();

-- 4.2 Коментар може да пишува само работник доделен на пријавата,
--     и само додека пријавата не е затворена.
CREATE OR REPLACE FUNCTION trg_comments_before_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_status TEXT;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM assignments a
                   WHERE a.report_id = NEW.report_id AND a.worker_id = NEW.worker_id) THEN
        RAISE EXCEPTION 'Работникот % не е доделен на пријавата %', NEW.worker_id, NEW.report_id;
    END IF;
    SELECT status INTO v_status FROM reports WHERE report_id = NEW.report_id;
    IF v_status IN ('resolved', 'rejected') THEN
        RAISE EXCEPTION 'Пријавата % е затворена и не прима нови коментари', NEW.report_id;
    END IF;
    RETURN NEW;
END $$;

CREATE TRIGGER comments_before_insert
    BEFORE INSERT ON comments
    FOR EACH ROW EXECUTE FUNCTION trg_comments_before_insert();


-- =============================================================
--  5. СКЛАДИРАНИ ПРОЦЕДУРИ И ФУНКЦИИ ЗА АПЛИКАЦИЈАТА
-- =============================================================

-- 5.1 Поднесување на пријава со фотографии во една операција.
CREATE OR REPLACE FUNCTION submit_report(p_citizen_id INTEGER,
                                         p_category_id INTEGER,
                                         p_description TEXT,
                                         p_location_text TEXT,
                                         p_latitude NUMERIC,
                                         p_longitude NUMERIC,
                                         p_photos TEXT[] DEFAULT '{}')
RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE
    v_report_id  INTEGER;
    v_created_at TIMESTAMP;
BEGIN
    INSERT INTO reports (description, location_text, latitude, longitude, category_id, citizen_id)
    VALUES (p_description, p_location_text, p_latitude, p_longitude, p_category_id, p_citizen_id)
    RETURNING report_id, created_at INTO v_report_id, v_created_at;

    INSERT INTO status_logs (status, changed_at, report_id, worker_id)
    VALUES ('submitted', v_created_at, v_report_id, NULL);

    INSERT INTO photos (image_url, report_id)
    SELECT '/uploads/reports/' || v_report_id || '/' || f, v_report_id
    FROM unnest(p_photos) AS f;

    RETURN v_report_id;
END $$;

-- 5.2 Промена на статусот од страна на работник.
--     Тригерите ги проверуваат правилата и го усогласуваат статусот во reports.
CREATE OR REPLACE PROCEDURE change_report_status(p_report_id INTEGER,
                                                 p_worker_id INTEGER,
                                                 p_new_status TEXT,
                                                 p_note TEXT DEFAULT NULL)
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO status_logs (status, note, report_id, worker_id)
    VALUES (p_new_status, p_note, p_report_id, p_worker_id);
END $$;

-- 5.3 Автоматско зголемување на приоритетот на пријави кои долго чекаат.
--     Потребен минимален приоритет според деновите од последната промена на статусот:
--     3+ дена -> medium, 7+ дена -> high, 14+ дена -> urgent.
CREATE OR REPLACE FUNCTION escalate_overdue_reports()
RETURNS TABLE (report_id INTEGER, old_priority TEXT, new_priority TEXT, days_waiting INTEGER)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
    WITH waiting AS (
        SELECT r.report_id, r.priority::TEXT AS priority,
               EXTRACT(DAY FROM now()::TIMESTAMP - MAX(l.changed_at))::INTEGER AS days_waiting
        FROM project.reports r
        JOIN project.status_logs l ON l.report_id = r.report_id
        WHERE r.status NOT IN ('resolved', 'rejected')
        GROUP BY r.report_id
    ),
    target AS (
        SELECT w.report_id, w.priority, w.days_waiting,
               priority_from_rank(GREATEST(
                   priority_rank(w.priority),
                   CASE WHEN w.days_waiting >= 14 THEN 4
                        WHEN w.days_waiting >= 7  THEN 3
                        WHEN w.days_waiting >= 3  THEN 2
                        ELSE 1 END)) AS new_priority
        FROM waiting w
    ),
    updated AS (
        UPDATE project.reports r
        SET priority = t.new_priority
        FROM target t
        WHERE r.report_id = t.report_id
          AND t.new_priority <> t.priority
        RETURNING r.report_id, t.priority, t.new_priority, t.days_waiting
    )
    SELECT * FROM updated ORDER BY 1;
END $$;


-- =============================================================
--  6. ПОГЛЕДИ
-- =============================================================

-- 6.1 Јавен преглед за мапата: без лични податоци за граѓаните.
CREATE VIEW v_public_reports AS
SELECT r.report_id, c.name AS category, r.description, r.location_text,
       r.latitude, r.longitude, r.status, r.priority, r.created_at,
       (SELECT COUNT(*) FROM photos p WHERE p.report_id = r.report_id) AS photo_count
FROM reports r
JOIN categories c ON c.category_id = r.category_id
WHERE r.status NOT IN ('resolved', 'rejected')
   OR r.report_id IN (SELECT l.report_id FROM status_logs l
                      WHERE l.status = 'resolved'
                        AND l.changed_at >= now() - INTERVAL '30 days');

-- 6.2 Целосен преглед за администраторите: доделени работници, последна промена и SLA.
CREATE VIEW v_report_overview AS
WITH last_change AS (
    SELECT report_id, MAX(changed_at) AS last_changed_at
    FROM status_logs
    GROUP BY report_id
)
SELECT r.report_id, c.name AS category, r.description, r.status, r.priority,
       ci.full_name AS citizen, r.created_at, lc.last_changed_at,
       (SELECT string_agg(w.full_name, ', ' ORDER BY w.full_name)
        FROM assignments a JOIN workers w ON w.worker_id = a.worker_id
        WHERE a.report_id = r.report_id) AS assigned_workers,
       EXTRACT(DAY FROM now()::TIMESTAMP - lc.last_changed_at)::INTEGER AS days_since_change,
       sla_days(r.priority) AS sla_days,
       (r.status NOT IN ('resolved', 'rejected')
        AND now()::TIMESTAMP - lc.last_changed_at > sla_days(r.priority) * INTERVAL '1 day') AS is_overdue
FROM reports r
JOIN categories c   ON c.category_id = r.category_id
JOIN citizens ci    ON ci.citizen_id = r.citizen_id
JOIN last_change lc ON lc.report_id = r.report_id;

-- 6.3 Оптовареност и учинок на работниците.
CREATE VIEW v_worker_workload AS
WITH resolved AS (
    SELECT l.worker_id, l.report_id, l.changed_at AS resolved_at, r.created_at
    FROM status_logs l
    JOIN reports r ON r.report_id = l.report_id
    WHERE l.status = 'resolved'
)
SELECT w.worker_id, w.full_name,
       COUNT(DISTINCT r.report_id) FILTER (WHERE r.status NOT IN ('resolved', 'rejected'))
           AS active_reports,
       COUNT(DISTINCT r.report_id) FILTER (WHERE r.status NOT IN ('resolved', 'rejected')
                                            AND r.priority IN ('high', 'urgent'))
           AS active_high_priority,
       (SELECT COUNT(*) FROM resolved rs
        WHERE rs.worker_id = w.worker_id
          AND rs.resolved_at >= now() - INTERVAL '90 days') AS resolved_last_90_days,
       (SELECT ROUND(AVG(EXTRACT(EPOCH FROM rs.resolved_at - rs.created_at) / 86400)::NUMERIC, 1)
        FROM resolved rs WHERE rs.worker_id = w.worker_id) AS avg_resolution_days
FROM workers w
LEFT JOIN assignments a ON a.worker_id = w.worker_id
LEFT JOIN reports r     ON r.report_id = a.report_id
GROUP BY w.worker_id, w.full_name;


-- =============================================================
--  7. ПОЗАДИНСКА ЗАДАЧА
--  Секој ден во 06:00 се зголемува приоритетот на пријавите кои долго чекаат.
--  Потребна е екстензијата pg_cron; ако ја нема, задачата се извршува
--  однадвор (на пример cron на серверот) со:
--    psql -c "SELECT * FROM project.escalate_overdue_reports();"
-- =============================================================
SET client_min_messages TO notice;
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_cron') THEN
        PERFORM cron.schedule('cityfix-escalate-overdue', '0 6 * * *',
                              'SELECT * FROM project.escalate_overdue_reports()');
        RAISE NOTICE 'Позадинската задача е закажана со pg_cron.';
    ELSE
        RAISE NOTICE 'pg_cron не е достапен: escalate_overdue_reports() закажете ја однадвор.';
    END IF;
END $$;