= Напреден апликативен дизајн

===== [https://github.com/ppaunovski/bus-n-go Линк до GitHub репозиториум со изворен код]

Напредниот апликативен дизајн има за цел да ги примени сите опишани кориснички сценарија и улоги од првите фази во финалната апликација. За таа цел создадена е нова апликација која користи модерни технологии.

За базата повторно се користи Postgres 16 базата, за backend се користи Spring Boot API напишан во Kotlin и за frontend се користи Angular 18.

Во пасусите во продолжение ќе бидат објаснети и покажани сите кориснилки сценарија имплементирани во апликацијата.


== Кориснички сценарија

[https://develop.finki.ukim.mk/projects/BusNGo/wiki/AdvancedApplicationDesign/registracija Регистрација на корисник]

[https://develop.finki.ukim.mk/projects/BusNGo/wiki/AdvancedApplicationDesign/bilet Купување на билет]

[https://develop.finki.ukim.mk/projects/BusNGo/wiki/AdvancedApplicationDesign/vozenje Стартување на возење (патник)]

Вршење на контрола

Пишување казна

[https://develop.finki.ukim.mk/projects/BusNGo/wiki/AdvancedApplicationDesign/ruta Стартување на рута на автобус]

== Материјализирани views

Напредните извештаи од минатите фази се искористени за да се направат materialized views. Материјализираните views значително го намалуваат времето на извршување на квери поради тоа што го чуваат и резултатот. Ова е соодветно поради тоа што ваквите статистики нема потреба да бидат up to date во секој момент, доволно е да се рефрешираат на одреден период (на пример 12 часа).

Во продолжение се прикажани материјалиизираните views (за некои од нив има и графици кои може да се погледнат во амин панелот).

==== Средна разлика на простигнување на инстанците од постојка А до постојка Б

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

{{{#!div
{{{#!sql

CREATE MATERIALIZED VIEW avg_time_diffs AS
WITH ordered_instanca AS (SELECT ilpl.instanca_na_linija_id,
                                 ilpl.postojka_na_linija_id,
                                 pnl.reden_broj,
                                 ilpl.timestamp
                          FROM instanca_na_linija_postojka_na_linija ilpl
                                   JOIN
                               postojka_na_linija pnl ON ilpl.postojka_na_linija_id = pnl.id
                          ORDER BY ilpl.instanca_na_linija_id,
                                   pnl.reden_broj),
     time_differences AS (SELECT o1.instanca_na_linija_id,
                                 o1.postojka_na_linija_id                          AS start_station_id,
                                 o1.timestamp                                      AS start_time,
                                 o2.postojka_na_linija_id                          AS end_station_id,
                                 o2.timestamp                                      AS end_time,
                                 EXTRACT(EPOCH FROM (o2.timestamp - o1.timestamp)) AS time_diff
                          FROM ordered_instanca o1
                                   JOIN
                               ordered_instanca o2 ON o1.instanca_na_linija_id = o2.instanca_na_linija_id
                          WHERE o1.reden_broj + 1 = o2.reden_broj)
SELECT
    row_number() over () as id,
    start_station_id,
       end_station_id,
       AVG(time_diff)::double precision AS avg_time_diff_seconds
FROM time_differences
GROUP BY start_station_id,
         end_station_id
ORDER BY start_station_id,
         end_station_id;

create unique index avg_time_diffs_index on avg_time_diffs (id);


}}}
}}}

==== Број на казни според линија

{{{#!div
{{{#!sql 

create materialized view kazna_po_linija as
select row_number() over () as id, l.id as line_id, count(l.id)
from linija l
         left join instanca_na_linija inl on l.id = inl.linija_id
         left join kontrola k on inl.id = k.instanca_na_linija_id
         left join kazna kaz on k.id = kaz.kontrola_id
where extract(year from kaz.date_created) = extract(year from now())
group by l.id
order by count(l.id) desc;

create unique index index_kazna_po_linija on kazna_po_linija(id);

}}}
}}}

[[Image(fines-per-line.png)]]


==== Зафатеност на линиите по интервали од 1 час

{{{#!div
{{{#!sql

create materialized view most_busy_part_of_the_day as
select row_number() over () as id, id as linija_id, interval_1h, count(id || '-' || interval_1h) broj_pati
from (select li.id,
             (to_char(extract(hour from v.start_date), '00') || ':00 -' || to_char(extract(hour from (v.start_date
                 + '1 hours'::interval))::int, '00') || ':00') as interval_1h
      from linija li
               left join instanca_na_linija inl on li.id = inl.linija_id
               left join vozenje v on inl.id = v.instanca_na_linija_id
      where v.start_date between (make_date(extract(year from (now()))::int, 1, 1)) and (make_date(extract(year from (now()))::int, 12, 31))) as hours
group by id, interval_1h
order by interval_1h desc;

create unique index index_most_busy_part_of_the_day on most_busy_part_of_the_day(id);

}}}
}}}

[[Image(number-of-activities-per-hour.png)]]

==== Commutes per line

{{{#!sql
create materialized view commutes_by_line as
select row_number() over () as id, l.id as linija_id, count(v.id)
from linija l
         left join instanca_na_linija inl on l.id = inl.linija_id
         left join vozenje v on inl.id = v.instanca_na_linija_id
where v.start_date between date_trunc('year', now()) and date_trunc('month', date_trunc('year', now())+ interval '7 months')
group by l.id;
}}}

==== Number of passengers per line and station 

{{#!sql
create materialized view number_passengers_per_line_and_station as
select row_number() over () as id, p.id as station_id, l.id as line_id, count(p.id)
from postojka p
         join postojka_na_linija pnl on p.id = pnl.postojka_id
         join vozenje v on pnl.id = v.postojka_na_linija_start_id
         join patnik pat on v.patnik_id = pat.id
         join linija l on pnl.linija_id = l.id
where v.start_date between (make_date(extract(year from (now() - '1 years'::interval))::int, 4, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 6, 30))
group by p.id, l.id
order by count(p.id) desc;
}}

==== Ticket sales

{{{#!sql
create materialized view ticket_sales as
select row_number() over () as id, filtered.tip_id, filtered.interval_1h, filtered.broj_pati
from (select filter.tip_id, interval_1h, count(tip_id || '-' || interval_1h) broj_pati
      from (select *,
                   (to_char(extract(hour from b.datum_kupuvanje), '00') || ':00 -' ||
                    to_char(extract(hour from (b.datum_kupuvanje + '1 hours'::interval))::int, '00') ||
                    ':00') as interval_1h
            from tipbilet t
                     left join bilet b on t.id = b.tip_id
--     where  b.datum_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))

           ) as filter
      group by filter.tip_id, interval_1h) filtered
where broj_pati = (select max(filtered.broj_pati)
                   from (select filter.tip_id, interval_1h, count(tip_id || '-' || interval_1h) broj_pati
                         from (select *,
                                      (to_char(extract(hour from b.datum_kupuvanje), '00') || ':00 -' ||
                                       to_char(extract(hour from (b.datum_kupuvanje + '1 hours'::interval))::int,
                                               '00') ||
                                       ':00') as interval_1h
                               from tipbilet t
                                        left join bilet b on t.id = b.tip_id
--     where  b.datum_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))

                              ) as filter
                         group by filter.tip_id, interval_1h) filtered)
  and tip_id in (select id
                 from tipbilet
                 where ime in ('Hourly', 'Daily'));
}}}

==== Total income (from ticket sales and fines)

{{{#!sql
create materialized view total_income as
WITH kazna_sum AS (
    SELECT
        EXTRACT(YEAR FROM k.date_payed) AS year,
        SUM(k.iznos) AS sum_kz
    FROM
        kazna k
    WHERE
        k.plateno = TRUE
    GROUP BY
        EXTRACT(YEAR FROM k.date_payed)
),
     bilet_sum AS (
         SELECT
             EXTRACT(YEAR FROM b.datum_kupuvanje) AS year,
             SUM(tb.cena) AS sum_b
         FROM
             bilet b
                 JOIN tipbilet tb ON tb.id = b.tip_id
         GROUP BY
             EXTRACT(YEAR FROM b.datum_kupuvanje)
     )
SELECT
    row_number() over () as id,
    COALESCE(k.year, b.year)::text AS year,
    COALESCE(sum_kz, 0) as fines_income,
    COALESCE(sum_b, 0) as ticket_income,
    COALESCE(sum_kz, 0) + COALESCE(sum_b, 0) AS total_income
FROM
    kazna_sum k
        FULL OUTER JOIN bilet_sum b ON k.year = b.year
ORDER BY
    year desc;
}}}

[[Image(total-income.png)]]

== Интегритет

=== Тригери

{{{#!sql

create or replace function check_expiry_date() returns trigger
as
    $$
    begin
        if exists(
            select 1
            from vozenje v
            where v.patnik_id = new.patnik_id and v.end_date is null
        ) then
            update vozenje set end_date = now(), status = 'FINISHED' where id in (select v.id
                                                                                  from vozenje v
                                                                                  where v.patnik_id = new.patnik_id and v.end_date is null);
        end if;

        if exists(
            select 1
            from bilet
            where id = new.bilet_id and datum_aktivacija is null
        ) then
            update bilet set datum_aktivacija = now(), status = 'ACTIVE' where id = new.bilet_id;
        end if;

        if exists(
            select 1
            from bilet b
            join tipbilet tb on b.tip_id = tb.id
            where b.id = new.bilet_id and ((b.datum_aktivacija + (tb.trajnost || ' milliseconds')::interval) < now())
        ) then
            update bilet set status = 'EXPIRED' where id = new.bilet_id;
            end if;

        if exists(
            select 1
            from bilet b
                     join tipbilet tb on b.tip_id = tb.id
            where b.id = new.bilet_id and ((b.datum_aktivacija + (tb.trajnost || ' milliseconds')::interval) < now())
        ) then
            RAISE exception 'Ticket is expired';
        end if;
        return new;
    end;
    $$ language plpgsql;

create or replace trigger check_validity_of_ticket
    before insert on vozenje
    for each row
    execute function check_expiry_date();


}}}

=== Трансакции

{{{#!java

@Transactional
    fun refreshAverageTimeDiffs() {
        val sql = "REFRESH MATERIALIZED VIEW CONCURRENTLY avg_time_diffs;"
        jdbcTemplate.execute(sql)
    }

    @Transactional
    fun refreshCommutesByHour() {
        val sql = "refresh materialized view concurrently most_busy_part_of_the_day;"
        jdbcTemplate.execute(sql)
    }

    @Transactional
    fun refreshFinesPerLine() {
        val sql = "refresh materialized view concurrently kazna_po_linija;"
        jdbcTemplate.execute(sql)
    }
    
    @Transactional
    fun refreshTotalIncome() {
        val sql = "refresh materialized view concurrently total_income;"
        jdbcTemplate.execute(sql)
    }

}}}

{{{#!java 
@Transactional
    fun createFine(request: FineRequest): FineResponse {
        val kontrola = kontrolaRepository.findByIdOrNull(request.kontrolaId) ?: throw NotFoundException("Kontrola")
        val now = Timestamp.valueOf(LocalDateTime.now())
        val kazna =
            kaznaRepository.save(
                Kazna(
                    dateCreated = now,
                    id = 0L,
                    kondukter = authService.getConductor(),
                    datePayed =
                        if (request.plateno) {
                            now
                        } else {
                            null
                        },
                    iznos = request.iznos,
                    dokument = request.dokument,
                    kontrola = kontrola,
                    plateno = request.plateno,
                ),
            )

        if (request.patnikId != null) {
            val korisnik = korisnikRepository.findByIdOrNull(request.patnikId!!) ?: throw NotFoundException("Korisnik")
            val patnik = patnikRepository.findByKorisnik(korisnik) ?: throw NotFoundException("Patnik")
            val kzr =
                kaznaZaRegistriranRepository.save(
                    KaznaZaRegistriran(
                        patnik = patnik,
                        id = 0L,
                        kazna = kazna,
                    ),
                )
            return kzr.let { dtoMapper.toFineResponse(kazna = kazna, kzr = kzr, kzn = null) }
        }

        if (request.adresa != null && request.ime != null && request.telefon != null) {
            val kzn =
                kaznaZaNeregistriranRepository.save(
                    KaznaZaNeregistriran(
                        id = 0L,
                        kazna = kazna,
                        ime = request.ime!!,
                        telefon = request.telefon!!,
                        adresa = request.adresa!!,
                    ),
                )

            return kzn.let { dtoMapper.toFineResponse(kazna = kazna, kzn = kzn, kzr = null) }
        }
        return dtoMapper.toFineResponse(kazna = kazna, kzr = null, kzn = null)
    }
}}}


{{{#!java

@Transactional
    fun start(request: StartCommuteRequest): CommuteResponse {
        if (!this.authService.hasAuthority(RoleEnum.ROLE_PASSENGER)) throw UnauthorizedAccessException("Unauthorised role")
        val korisnik = this.authService.getAuthenticatedUser()
        val patnik = patnikRepository.findByKorisnik(korisnik) ?: throw NotFoundException("Korisnik")

        var bilet = biletRepository.findByIdAndPatnik(request.ticketId, patnik) ?: throw NotFoundException("Bilet")

        val instancaNaLinija =
            instancaNaLinijaRepository.findByIdOrNull(request.routeInstanceId) ?: throw NotFoundException("RouteInstanceId")
        val postojka = postojkaRepository.findByIdOrNull(request.stationId) ?: throw NotFoundException("StationId")

        val postojkaNaLinijaStart =
            postojkaNaLinijaRepository.findByLinijaAndPostojkaAndPravec(instancaNaLinija.linija, postojka, instancaNaLinija.pravec)
                ?: throw NotFoundException("Station not found")

        if (bilet.status == BiletEnum.INACTIVE) {
            bilet.status = BiletEnum.ACTIVE
            bilet.datumAktivacija = Timestamp.valueOf(LocalDateTime.now())
            bilet = biletRepository.save(bilet)
        }

        val vozenje =
            Vozenje(
                id = 0L,
                startDate = Timestamp.valueOf(LocalDateTime.now()),
                endDate = null,
                status = VozenjeStatus.ACTIVE,
                patnik = patnik,
                postojkaNaLinijaStart = postojkaNaLinijaStart,
                instancaNaLinija = instancaNaLinija,
                bilet = bilet,
            )
        return vozenjeRepository.save(vozenje).let { dtoMapper.toCommuteResponse(it) }
    }

}}}


== Промена на моделот на базата

Моделот на базата е малку променет за разлика од претходните фази. Изоставена е табелата Mesta, која и во нормализацијата е изоставена поради тоа што не е во трета нормална форма. Додадени се нови табели: instanca_na_linija_postojka_na_linija, pravec, linija_pravec, role, korisnik_role.

[[Image(new-model.png)]]

* [attachment:V1__init_db.sql] - DDL за креирање на базата
* [attachment:V1.1__data_init.sql] - DML со првични податоци
* [attachment:V1.2__additional_data.sql] - DML со дополнителни податоци