= Индекси за оптимизација на напредни извештаи 


== Анализа на извештаи и потребни индекси == #анализа

=== 1. Индекси за извештај за процент на изнајмени недвижности во однос на објавени огласи за секоја година.=== #извештај1

Овој извештај користи и извршува операции со:
 * `EXTRACT(YEAR FROM available_from)` на Listing табела
 * `EXTRACT(YEAR FROM l.start_date)` на Lease табела  
 * JOIN помеѓу Lease и Listing преку listing_id

'''Потребни индекси:'''

{{{#!sql
-- За брзо филтрирање по година на објавување
CREATE INDEX IF NOT EXISTS idx_listing_available_from_year ON Listing(EXTRACT(YEAR FROM available_from));

-- За брзо филтрирање по година на започнување на изнајмувањето
CREATE INDEX IF NOT EXISTS idx_lease_start_date_year ON Lease(EXTRACT(YEAR FROM start_date));

-- За оптимизација на JOIN операциите
CREATE INDEX IF NOT EXISTS idx_lease_listing_id ON Lease(listing_id);

-- Композитен индекс за подобра оптимизација на временски операции
CREATE INDEX IF NOT EXISTS idx_listing_available_from ON Listing(available_from);
CREATE INDEX IF NOT EXISTS idx_lease_start_date ON Lease(start_date);
}}}

=== 2. Извештај за раст на приходи по град во однос на претходната година  === #извештај2

Овој извештај е особено сложен бидејќи користи повеќе JOIN операции и агрегатни функции со временски филтрирања.

'''Критични операции:'''
 * JOIN низа: Payment → Lease → Listing → Unit → Property → Address
 * WHERE услов: `p.status = 'завршено'`
 * GROUP BY со `EXTRACT(YEAR FROM p.payment_date)`

'''Потребни индекси:'''

{{{#!sql
-- За брзо филтрирање на успешни плаќања
CREATE INDEX IF NOT EXISTS idx_payment_status ON Payment(status);

-- За временски анализи на плаќања
CREATE INDEX IF NOT EXISTS idx_payment_date_year ON Payment(EXTRACT(YEAR FROM payment_date));
CREATE INDEX IF NOT EXISTS idx_payment_date ON Payment(payment_date);

-- За оптимизација на JOIN операциите
CREATE INDEX IF NOT EXISTS idx_payment_lease_id ON Payment(lease_id);
CREATE INDEX IF NOT EXISTS idx_listing_unit_id ON Listing(unit_id);
CREATE INDEX IF NOT EXISTS idx_unit_property_id ON Unit(property_id);
CREATE INDEX IF NOT EXISTS idx_property_address_id ON Property(address_id);

-- За групирање по град
CREATE INDEX IF NOT EXISTS idx_address_city ON Address(city);

-- Композитен индекс за плаќања со статус и датум
CREATE INDEX IF NOT EXISTS idx_payment_status_date ON Payment(status, payment_date);
}}}

=== 3. Детален месечен извештај за издавачите  === #извештај3

Овој извештај е најсложен и користи повеќе CTE изрази со различни временски периоди.

'''Клучни операции:'''
 * Филтрирање по landlord_id
 * Временски операции со start_date и end_date
 * Проверка на статус на плаќања

'''Потребни индекси:'''

{{{#!sql
-- За брза идентификација на изадавачите
CREATE INDEX IF NOT EXISTS idx_lease_landlord_id ON Lease(landlord_id);

-- За временски филтрирања на изнајмувања
CREATE INDEX IF NOT EXISTS idx_lease_start_date ON Lease(start_date);
CREATE INDEX IF NOT EXISTS idx_lease_end_date ON Lease(end_date);

-- Композитен индекс за временски период на договор
CREATE INDEX IF NOT EXISTS idx_lease_dates ON Lease(start_date, end_date);

-- За филтрирање активни договори
CREATE INDEX IF NOT EXISTS idx_lease_landlord_dates ON Lease(landlord_id, start_date, end_date);

-- За сервисни барања
CREATE INDEX IF NOT EXISTS idx_service_request_status ON ServiceRequest(status);
CREATE INDEX IF NOT EXISTS idx_service_request_lease_id ON ServiceRequest(lease_id);

-- За UserD табела (landlord профили)
CREATE INDEX IF NOT EXISTS idx_userd_id ON UserD(id);
}}}

=== 4. Извештај за платежна историја на станари  === #извештај4

Фокусиран на tenant_id и различни статуси на плаќања.

'''Потребни индекси:'''

{{{#!sql
-- За идентификација на станари
CREATE INDEX IF NOT EXISTS idx_lease_tenant_id ON Lease(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_profile_id ON TenantProfile(id);

-- За анализа на плаќања по статус
CREATE INDEX IF NOT EXISTS idx_payment_status ON Payment(status);
CREATE INDEX IF NOT EXISTS idx_payment_lease_status ON Payment(lease_id, status);

-- За пресметка на просечна кирија
CREATE INDEX IF NOT EXISTS idx_lease_rent_amount ON Lease(rent_amount);

-- За временска анализа на изнајмувања
CREATE INDEX IF NOT EXISTS idx_lease_tenant_dates ON Lease(tenant_id, start_date, end_date);
}}}

=== 5. Извештај за сервисни барања по општина  === #извештај5

Анализира сервисни барања со географска и категориска сегментација.

'''Потребни индекси:'''

{{{#!sql
-- За категоризација на сервисни барања
CREATE INDEX IF NOT EXISTS idx_service_request_category ON ServiceRequest(service_category_id);

-- За временска анализа на барања
CREATE INDEX IF NOT EXISTS idx_service_request_date ON ServiceRequest(request_date);

-- За анализа на одржување
CREATE INDEX IF NOT EXISTS idx_maintenance_log_service_request ON MaintenanceLog(service_request_id);
CREATE INDEX IF NOT EXISTS idx_maintenance_log_date ON MaintenanceLog(maintenance_date);

-- За географска анализа
CREATE INDEX IF NOT EXISTS idx_address_municipality ON Address(municipality);
CREATE INDEX IF NOT EXISTS idx_address_municipality_city ON Address(municipality, city);

-- Композитен индекс за временска анализа на сервиси
CREATE INDEX IF NOT EXISTS idx_service_request_status_date ON ServiceRequest(status, request_date);
}}}

=== 6. Анализа на пазарни трендови === #извештај6

Анализира понуда и побарувачка по регион и тип на недвижност.

'''Потребни индекси:'''

{{{#!sql
-- За анализа по тип на недвижност
CREATE INDEX IF NOT EXISTS idx_property_type_id ON Property(property_type_id);

-- За статус на огласи
CREATE INDEX IF NOT EXISTS idx_listing_status ON Listing(status);

-- За пресметка на цена по квадратен метар
CREATE INDEX IF NOT EXISTS idx_unit_rent_area ON Unit(rent_amount, area_sq_m);

-- За заинтересираност
CREATE INDEX IF NOT EXISTS idx_interested_listing_id ON Interested(listing_id);
CREATE INDEX IF NOT EXISTS idx_interested_tenant_profile ON Interested(tenant_profile_id);

-- Композитен индекс за пазарна анализа
CREATE INDEX IF NOT EXISTS idx_listing_unit_status ON Listing(unit_id, status);
}}}

== Тестирање на индексите со EXPLAIN ANALYZE == #тестирање

За да се провери ефикасноста на индексите, можеме да користиме EXPLAIN ANALYZE:

{{{#!sql
-- Пример за тестирање на Извештај 4
EXPLAIN ANALYZE
WITH TenantPaymentStats AS (
    SELECT 
        t.id as tenant_id,
        CONCAT(u.first_name, ' ', u.last_name) as tenant_name,
        u.rating as tenant_rating,
        COUNT(DISTINCT le.id) as total_leases,
        COUNT(DISTINCT pay.id) as total_payments,
        SUM(CASE WHEN pay.status = 'завршено' THEN pay.amount ELSE 0 END) as total_paid,
        SUM(CASE WHEN pay.status = 'доцнење' THEN pay.amount ELSE 0 END) as late_payments_amount,
        COUNT(CASE WHEN pay.status = 'доцнење' THEN 1 END) as late_payment_count,
        COUNT(CASE WHEN pay.status = 'во тек' THEN 1 END) as pending_payment_count,
        AVG(le.rent_amount) as avg_rent_amount,
        MIN(le.start_date) as first_lease_date,
        MAX(le.end_date) as latest_lease_end
    FROM TenantProfile t
    JOIN UserD u ON t.id = u.id
    LEFT JOIN Lease le ON t.id = le.tenant_id
    LEFT JOIN Payment pay ON le.id = pay.lease_id
    GROUP BY t.id, u.first_name, u.last_name, u.rating
)
SELECT 
    tenant_id,
    tenant_name,
    tenant_rating,
    total_leases,
    total_payments,
    total_paid,
    late_payments_amount,
    late_payment_count,
    pending_payment_count,
    ROUND((late_payment_count * 100.0 / NULLIF(total_payments, 0)), 2) as late_payment_percentage,
    avg_rent_amount,
    first_lease_date,
    latest_lease_end,
    CASE 
        WHEN late_payment_count = 0 THEN 'Одличен'
        WHEN late_payment_count <= 2 THEN 'Добар' 
        WHEN late_payment_count <= 5 THEN 'Среден'
        ELSE 'Ризичен'
    END as payment_reliability_score
FROM TenantPaymentStats
WHERE total_leases > 0
ORDER BY late_payment_percentage ASC, total_paid DESC;

}}}

'''Што да бараме во резултатот:'''
 * '''Index Scan''' наместо '''Seq Scan''' - значи дека индексот се користи
 * '''Nested Loop''' со '''Index Scan''' на внатрешната страна на JOIN
 * '''Execution time''' треба да биде значително намален