Changes between Version 5 and Version 6 of DatabaseCreation


Ignore:
Timestamp:
09/16/26 06:57:25 (11 days ago)
Author:
235013
Comment:

Фаза 2: документација

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseCreation

    v5 v6  
    1 = DatabaseCreation =
    2 
    3 == Опис ==
    4 
    5 Релационата база на податоци е имплементирана во PostgreSQL според претходно дефинираниот ER и релационен модел.
    6 
    7 DDL скриптата ги дефинира сите потребни табели, примарни и странски клучеви, ограничувања, проверки и default вредности.
    8 
    9 Целосната скрипта е достапна во:
    10 
    11 * [attachment:ddl.sql ddl.sql]
    12 
    13 == Основни табели ==
    14 
    15 Во почетниот дел од скриптата се креираат помошните табели кои понатаму се користат од останатите ентитети.
    16 
    17 Пример за `Role`:
    18 
    19 {{{#!sql
    20 CREATE TABLE Role (
    21 role_id     SERIAL NOT NULL PRIMARY KEY,
    22 role_name   text   NOT NULL UNIQUE,
    23 description text
    24 );
    25 }}}
    26 
    27 На сличен начин се дефинирани `Permission`, `Industry`, `Project_Status`, `Technology`, `Rating_Dimension` и `Subscription_Tier`.
    28 
    29 == Корисници ==
    30 
    31 Сите типови на корисници ги содржат заедничките податоци во табелата `"User"`:
    32 
    33 {{{#!sql
    34 CREATE TABLE "User" (
    35 user_id       SERIAL    NOT NULL PRIMARY KEY,
    36 type          text      NOT NULL
    37 CHECK (type IN ('client', 'vendor', 'management')),
    38 first_name    text      NOT NULL,
    39 last_name     text      NOT NULL,
    40 email         text      NOT NULL UNIQUE,
    41 password_hash text      NOT NULL,
    42 is_active     bool      NOT NULL DEFAULT false,
    43 last_login_at timestamp,
    44 created_at    timestamp NOT NULL DEFAULT NOW(),
    45 updated_at    timestamp NOT NULL DEFAULT NOW()
    46 );
    47 }}}
    48 
    49 Полето `type` е ограничено со `CHECK`, така што може да има само една од трите дозволени вредности.
    50 
    51 За конкретните типови на корисници се користат дополнителни табели. На пример:
    52 
    53 {{{#!sql
    54 CREATE TABLE Client_User (
    55 user_id   int4 NOT NULL PRIMARY KEY,
    56 client_id int4 NOT NULL,
    57 
    58 CONSTRAINT fk_clientuser_user
    59 FOREIGN KEY (user_id) REFERENCES "User" (user_id)
    60 ON DELETE CASCADE
    61 ON UPDATE CASCADE,
    62 
    63 CONSTRAINT fk_clientuser_client
    64 FOREIGN KEY (client_id) REFERENCES Client (client_id)
    65 ON DELETE RESTRICT
    66 ON UPDATE CASCADE
    67 );
    68 }}}
    69 
    70 На истиот принцип се дефинирани и `Vendor_User` и `Management_User`.
    71 
    72 == Клиенти, продавачи и договори ==
    73 
    74 Клиентот е поврзан со индустријата преку странски клуч:
    75 
    76 {{{#!sql
    77 CREATE TABLE Client (
    78 client_id     SERIAL NOT NULL PRIMARY KEY,
    79 industry_id   int4   NOT NULL,
    80 company_name  text   NOT NULL,
    81 contact_email text   NOT NULL,
    82 
    83 CONSTRAINT fk_client_industry
    84 FOREIGN KEY (industry_id) REFERENCES Industry (industry_id)
    85 ON DELETE RESTRICT
    86 ON UPDATE CASCADE
    87 );
    88 }}}
    89 
    90 Врската помеѓу клиент и vendor е претставена преку договор:
    91 
    92 {{{#!sql
    93 CREATE TABLE Client_Vendor_Contract (
    94 contract_id     SERIAL NOT NULL PRIMARY KEY,
    95 client_id       int4   NOT NULL,
    96 vendor_id       int4   NOT NULL,
    97 contract_number text UNIQUE,
    98 contract_title  text   NOT NULL,
    99 start_date      date   NOT NULL DEFAULT CURRENT_DATE,
    100 end_date        date,
    101 total_value     numeric(10,2),
    102 currency_code   text,
    103 terms_summary   text,
    104 is_active       bool      NOT NULL DEFAULT true,
    105 created_at      timestamp NOT NULL DEFAULT NOW(),
    106 updated_at      timestamp NOT NULL DEFAULT NOW(),
    107 
    108 CONSTRAINT chk_cvc_dates
    109 CHECK (end_date IS NULL OR end_date > start_date),
    110 
    111 CONSTRAINT fk_cvc_client
    112 FOREIGN KEY (client_id) REFERENCES Client (client_id),
    113 
    114 CONSTRAINT fk_cvc_vendor
    115 FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id)
    116 );
    117 }}}
    118 
    119 Со `CHECK` ограничувањето се спречува крајниот датум на договорот да биде пред почетниот.
    120 
    121 == Проекти ==
    122 
    123 Секој проект е поврзан со договор и со тековен статус:
    124 
    125 {{{#!sql
    126 CREATE TABLE Project (
    127 project_id   SERIAL        NOT NULL PRIMARY KEY,
    128 contract_id  int4          NOT NULL,
    129 status_id    int4          NOT NULL,
    130 project_name text          NOT NULL,
    131 start_date   date          NOT NULL DEFAULT CURRENT_DATE,
    132 end_date     date,
    133 budget       numeric(10,2) NOT NULL,
    134 created_at   timestamp     NOT NULL DEFAULT NOW(),
    135 updated_at   timestamp     NOT NULL DEFAULT NOW(),
    136 
    137 CONSTRAINT chk_project_dates
    138 CHECK (end_date IS NULL OR end_date > start_date),
    139 
    140 CONSTRAINT fk_project_contract
    141 FOREIGN KEY (contract_id)
    142 REFERENCES Client_Vendor_Contract (contract_id),
    143 
    144 CONSTRAINT fk_project_status
    145 FOREIGN KEY (status_id)
    146 REFERENCES Project_Status (status_id)
    147 );
    148 }}}
    149 
    150 Технологиите користени на проектите се моделирани со many-to-many релација:
    151 
    152 {{{#!sql
    153 CREATE TABLE Project_Technology (
    154 project_id    int4 NOT NULL,
    155 technology_id int4 NOT NULL,
    156 
    157 PRIMARY KEY (project_id, technology_id),
    158 
    159 CONSTRAINT fk_projtec_project
    160 FOREIGN KEY (project_id) REFERENCES Project (project_id)
    161 ON DELETE CASCADE,
    162 
    163 CONSTRAINT fk_projtec_technology
    164 FOREIGN KEY (technology_id) REFERENCES Technology (technology_id)
    165 ON DELETE RESTRICT
    166 );
    167 }}}
    168 
    169 == Историја на проектите ==
    170 
    171 За промените на буџетот се користи посебна audit табела:
    172 
    173 {{{#!sql
    174 CREATE TABLE Project_Budget_Audit (
    175 audit_id   SERIAL        NOT NULL PRIMARY KEY,
    176 project_id int4          NOT NULL,
    177 old_budget numeric(10,2) NOT NULL,
    178 new_budget numeric(10,2) NOT NULL,
    179 created_at timestamp     NOT NULL DEFAULT NOW(),
    180 updated_at timestamp     NOT NULL DEFAULT NOW(),
    181 
    182 CONSTRAINT fk_budgetaudit_project
    183 FOREIGN KEY (project_id) REFERENCES Project (project_id)
    184 );
    185 }}}
    186 
    187 Промените на статусот се зачувуваат во `Project_Status_History`, каде покрај проектот и статусот се чува и корисникот кој ја направил промената.
    188 
    189 == Reviews и оценки ==
    190 
    191 За секој проект може да постои една рецензија:
    192 
    193 {{{#!sql
    194 CREATE TABLE Review (
    195 review_id       SERIAL NOT NULL PRIMARY KEY,
    196 project_id      int4   NOT NULL UNIQUE,
    197 client_user_id  int4   NOT NULL,
    198 review_date     date   NOT NULL DEFAULT CURRENT_DATE,
    199 summary_text    text   NOT NULL,
    200 is_published    bool   NOT NULL DEFAULT false,
    201 
    202 CONSTRAINT fk_review_project
    203 FOREIGN KEY (project_id) REFERENCES Project (project_id),
    204 
    205 CONSTRAINT fk_review_clientuser
    206 FOREIGN KEY (client_user_id) REFERENCES Client_User (user_id)
    207 );
    208 }}}
    209 
    210 Оценките се чуваат одделно за секоја димензија:
    211 
    212 {{{#!sql
    213 CREATE TABLE Review_Score (
    214 review_id    int4 NOT NULL,
    215 dimension_id int4 NOT NULL,
    216 score_value  int4 NOT NULL,
    217 
    218 PRIMARY KEY (review_id, dimension_id),
    219 
    220 CONSTRAINT fk_reviewscore_review
    221 FOREIGN KEY (review_id) REFERENCES Review (review_id)
    222 ON DELETE CASCADE,
    223 
    224 CONSTRAINT fk_reviewscore_dimension
    225 FOREIGN KEY (dimension_id)
    226 REFERENCES Rating_Dimension (dimension_id)
    227 );
    228 }}}
    229 
    230 == Dispute систем ==
    231 
    232 При оспорување на review се креира запис во `Dispute_Ticket`:
    233 
    234 {{{#!sql
    235 CREATE TABLE Dispute_Ticket (
    236 ticket_id                     SERIAL NOT NULL PRIMARY KEY,
    237 assigned_management_user_id   int4 DEFAULT NULL,
    238 review_id                     int4 NOT NULL,
    239 vendor_user_id                int4 NOT NULL,
    240 reason                        text NOT NULL,
    241 is_resolved                   bool NOT NULL DEFAULT false,
    242 filed_at                      date NOT NULL DEFAULT CURRENT_DATE,
    243 resolved_at                   timestamp,
    244 resolution_note               text
    245 );
    246 }}}
    247 
    248 Табелата е дополнително поврзана со `Management_User`, `Review` и `Vendor_User` преку странски клучеви.
    249 
    250 == Views ==
    251 
    252 За почесто користените прикази и аналитички податоци се дефинирани PostgreSQL views. Тие ги обединуваат податоците од повеќе поврзани табели и овозможуваат поедноставен пристап до информации за проекти, договори, буџети, оценки, спорови и претплати.
    253 
    254 Целосната скрипта е достапна во:
    255 
    256 * [attachment:views.sql views.sql]
    257 
    258 === Проекти по Vendor и Client ===
    259 
    260 Погледот `vw_projects_per_vendor` ги прикажува сите проекти поврзани со одреден vendor, заедно со статусот, периодот и буџетот:
     1= Креирање на базата (!DatabaseCreation) =
     2
     3Во оваа фаза се прикажани DDL скриптата за креирање на базата (2А), скриптите за генерирање и полнење на податоците (2Б) и погледите што апликацијата ги користи.
     4
     5[[PageOutline(2-3,Содржина,inline)]]
     6
     7== 2А: DDL скрипта ==
     8
     9[attachment:ddl.sql]
     10
     11Скриптата ги креира 23-те табели од релациониот модел (RelationalModel) по редослед на зависност. Надворешните клучеви, проверките и составеното уникатно ограничување се именувани (`fk_*`, `chk_*`, `uq_*`), така што пораката за грешка при прекршување го именува правилото. Табелата за корисници се вика `"User"`, со наводници, бидејќи `USER` е резервиран збор.
     12
     13Табелите се поделени во шест модули:
     14
     15* '''Идентитет и пристап''': `"User"` е централната табела за сите корисници со `type` (`client`, `vendor` или `management`); трите подтипови `Client_User`, `Vendor_User` и `Management_User` го делат нејзиниот примарен клуч и го поврзуваат корисникот со клиентот, софтверската агенција или улогата на која припаѓа. Дозволите се доделуваат на улогите преку `Role_Permission`.
     16* '''Страни''': `Client` (клиентска компанија, припаѓа на една `Industry`) и `Vendor` (софтверска агенција).
     17* '''Комерцијален дел''': `Client_Vendor_Contract` е договорот меѓу клиент и софтверска агенција; `Vendor_Subscription` е претплатата на софтверската агенција на платформата по периоди, на ниво од `Subscription_Tier` (фиксна цена од ценовник или договорна цена).
     18* '''Испорака''': `Project` припаѓа на договор, а не директно на клиент и софтверска агенција, и има тековен статус од `Project_Status`; `Project_Technology` ги поврзува проектите со `Technology`.
     19* '''Квалитет''': `Review` е рецензијата на клиентот за завршен проект (најмногу една по проект), со оценки од 1 до 5 по секоја димензија од `Rating_Dimension` во `Review_Score`; `Dispute_Ticket` е спорот што корисник на софтверската агенција го поднесува врз рецензија, а го решава менаџмент корисник.
     20* '''Историја''': `Project_Status_History` чува по една редица за секоја промена на статус, со точно еден актер (корисник на софтверската агенција или менаџмент корисник); `Project_Budget_Audit` чува стар и нов буџет за секоја промена на буџетот.
     21
     22'''Проверки (CHECK):'''
     23
     24* Датуми
     25  * крајниот датум е по почетниот: договор, претплата, проект
     26  * спорот не се решава пред да е поднесен
     27* Износи: сите се позитивни
     28  * буџет на проект, вредност на договор, цена од ценовник, договорна цена, стар и нов буџет во ревизијата
     29* Оценка: меѓу 1 и 5
     30* Формат
     31  * типот на корисник е една од `client`, `vendor`, `management`
     32  * е-поштата е во облик `име@домен`
     33  * валутата е три големи букви
     34* Непразен текст
     35  * име, презиме и лозинка на корисник
     36  * наслов на договор, име на проект
     37  * резиме на рецензија, причина и белешка на спор
     38* Конзистентна состојба
     39  * промена на статус има точно еден актер
     40  * решен спор има датум на решавање, доделен менаџмент корисник и белешка; нерешен нема датум
     41  * ниво на претплата има или цена од ценовник или дозвола за договорна цена, никогаш двете
     42
     43'''UNIQUE:'''
     44
     45* имињата во табелите со фиксни листи: улоги, дозволи, индустрии, статуси, технологии, димензии, нивоа
     46* е-поштата на корисникот
     47* бројот на договорот
     48* една рецензија по проект
     49* името на проектот во рамки на договорот (`uq_project_contract_name`)
     50
     51'''Стандардни вредности:'''
     52
     53* `created_at` и `updated_at`: `NOW()`
     54* `CURRENT_DATE`: почеток на договор, претплата и проект; датум на рецензија; датум на поднесување на спор
     55* Почетна состојба
     56  * нов корисник е неактивен (се активира по потврда)
     57  * нов договор и нова претплата се активни
     58  * нова рецензија е необјавена
     59  * нов спор е нерешен и недоделен
     60
     61'''Бришење:'''
     62
     63* сите надворешни клучеви се `ON DELETE RESTRICT`: редица на која упатуваат други редици не може да се избрише
     64* апликацијата не брише: корисниците се деактивираат, споровите се решаваат, а договорите и претплатите истекуваат
     65
     66'''Делумни уникатни индекси.''' Две правила важат само за дел од редиците и не можат да се запишат како ограничување на ниво на табела:
     67
     68{{{#!sql
     69-- WHERE: уникатноста важи само за редиците што го исполнуваат условот
     70CREATE UNIQUE INDEX uq_vendorsub_one_active
     71    ON Vendor_Subscription (vendor_id)
     72    WHERE is_active;
     73
     74CREATE UNIQUE INDEX uq_dispute_open_per_vendor_review
     75    ON Dispute_Ticket (review_id, vendor_user_id)
     76    WHERE is_resolved = false;
     77}}}
     78
     79Софтверска агенција има најмногу една активна претплата, а корисник на агенцијата најмногу еден отворен спор за иста рецензија.
     80
     81== 2Б: Податоци ==
     82
     83[attachment:seed_generator.py]
     84
     85[attachment:load_seed.sql]
     86
     87Податоците ги генерира скриптата `seed_generator.py` (Python, библиотека Faker за имиња и текст) како по една CSV датотека за секоја табела; скриптата `load_seed.sql` ги полни табелите по редослед на зависност. Податоците ги задоволуваат сите ограничувања од шемата и правилата од апликациската логика:
     88
     89* Авторот на рецензија е корисник на клиентот од договорот на проектот, поднесувачот на спор е корисник на софтверската агенција од истиот договор, а актерот во историјата на статуси е корисник на софтверската агенција на проектот или менаџмент корисник.
     90* Секој проект минува низ животниот циклус Draft → Under Review → In Progress (→ On Hold) и завршува како Completed или Cancelled, или останува отворен; промените на статус се временски подредени внатре во траењето на проектот, а статусот на проектот е секогаш последната редица од историјата.
     91* Рецензии постојат само за завршени проекти (Completed или Cancelled), најмногу една по проект и со по една оценка за секоја од десетте димензии.
     92* Решените спорови имаат датум на решавање, доделен менаџмент корисник и белешка. Рецензија со отворен спор не е објавена.
     93* Промените на буџетот формираат синџир: стариот буџет на секоја промена е новиот од претходната, а последниот нов буџет е тековниот буџет на проектот.
     94* Периодите на претплата на една софтверска агенција не се преклопуваат и активен е најмногу последниот; договорна цена има само на нивоата што ја дозволуваат.
     95* Проектите лежат внатре во периодот на договорот, секој договор има барем еден проект, а секој клиент и секоја софтверска агенција има барем еден корисник. Е-поштата е во облик `име.презиме@домен-на-компанијата`.
     96
     97Распределбите се избрани при генерирањето, за реалистични податоци:
     98
     99* 78,5 % од проектите се Completed и 5,6 % Cancelled; останатите се отворени.
     100* Рецензијата е напишана 1 до 45 дена по крајот на проектот. Спор е поднесен за 15 % од рецензиите, а од рецензиите без отворен спор се објавени 70 %.
     101* Секоја софтверска агенција има скриен параметар на квалитет околу кој се влечат оценките на нејзините рецензии, па просечните оценки по агенција се распоредени од 1,66 до 4,61, а не сите околу 3.
     102
     103'''Големина на табелите.''' Број на редици по полнењето:
     104
     105||= Табела =||= Редици =||
     106|| `Review_Score` || 10.000.000 ||
     107|| `Project_Status_History` || 5.946.160 ||
     108|| `Project_Technology` || 4.197.954 ||
     109|| `Project` || 1.400.000 ||
     110|| `Project_Budget_Audit` || 1.048.649 ||
     111|| `Review` || 1.000.000 ||
     112|| `Client_Vendor_Contract` || 250.000 ||
     113|| `Dispute_Ticket` || 143.894 ||
     114|| `"User"` || 77.000 ||
     115|| `Client_User` || 50.000 ||
     116|| `Vendor_User` || 25.000 ||
     117|| `Client` || 20.000 ||
     118|| `Vendor_Subscription` || 5.916 ||
     119|| `Vendor` || 5.000 ||
     120|| `Management_User` || 2.000 ||
     121|| `Role_Permission` || 40 ||
     122|| `Technology` || 30 ||
     123|| `Industry` || 20 ||
     124|| `Permission` || 15 ||
     125|| `Rating_Dimension` || 10 ||
     126|| `Project_Status` || 6 ||
     127|| `Role` || 5 ||
     128|| `Subscription_Tier` || 4 ||
     129
     130Шест табели имаат милион или повеќе редици; вкупно има 24,2 милиони редици и базата зафаќа 2,1 GB.
     131
     132== Погледи ==
     133
     134[attachment:views.sql]
     135
     136Скриптата креира 16 погледи. Првите два, `vw_contract_details` и `vw_project_details`, се помошни: апликацијата не ги чита директно, туку врз нив се градат останатите.
     137
     138=== Поглед 1: Детали за договор (vw_contract_details) ===
     139
     140Помошен поглед: по една редица за секој договор со имињата на двете страни. Поврзувањето договор–клиент–софтверска агенција е напишано еднаш, а погледите за договори и за проекти го користат.
     141
     142{{{#!sql
     143CREATE OR REPLACE VIEW vw_contract_details AS
     144SELECT cvc.contract_id,
     145       cvc.contract_number,
     146       cvc.contract_title,
     147       cvc.start_date,
     148       cvc.end_date,
     149       cvc.total_value,
     150       cvc.currency_code,
     151       cvc.is_active,
     152       c.client_id,
     153       c.company_name,
     154       v.vendor_id,
     155       v.agency_name
     156FROM Client_Vendor_Contract cvc
     157         JOIN Client c ON c.client_id = cvc.client_id
     158         JOIN Vendor v ON v.vendor_id = cvc.vendor_id;
     159}}}
     160
     161=== Поглед 2: Детали за проект (vw_project_details) ===
     162
     163Помошен поглед: проектот со името на статусот, договорот и двете страни. Од него читаат погледите по софтверска агенција и по клиент, погледите за оценки и погледот за промени на буџет. Оптимизаторот ја вметнува дефиницијата на помошниот поглед во прашалникот што го користи, па помошниот поглед не додава чекор во планот.
     164
     165{{{#!sql
     166CREATE OR REPLACE VIEW vw_project_details AS
     167SELECT p.project_id,
     168       p.project_name,
     169       p.status_id,
     170       ps.status_name,
     171       p.start_date,
     172       p.end_date,
     173       p.budget,
     174       cd.contract_id,
     175       cd.contract_number,
     176       cd.currency_code,
     177       cd.client_id,
     178       cd.company_name,
     179       cd.vendor_id,
     180       cd.agency_name
     181FROM Project p
     182         -- договорот и двете страни доаѓаат од помошниот поглед
     183         JOIN vw_contract_details cd ON cd.contract_id = p.contract_id
     184         JOIN Project_Status ps ON ps.status_id = p.status_id;
     185}}}
     186
     187=== Поглед 3: Проекти по софтверска агенција (vw_projects_per_vendor) ===
     188
     189Сите проекти на секоја софтверска агенција со статус, датуми, буџет и валута. Апликацијата го чита филтриран по агенција на нејзината контролна табла и на нејзиниот профил, каде што клиентот го разгледува портфолиото на реални проекти пред избор на агенција.
    261190
    262191{{{#!sql
    263192CREATE OR REPLACE VIEW vw_projects_per_vendor AS
    264 SELECT v.vendor_id,
    265 v.agency_name,
    266 p.project_id,
    267 p.project_name,
    268 ps.status_name,
    269 p.start_date,
    270 p.end_date,
    271 p.budget,
    272 cvc.currency_code
    273 FROM Project p
    274 JOIN Client_Vendor_Contract cvc
    275 ON cvc.contract_id = p.contract_id
    276 JOIN Vendor v
    277 ON v.vendor_id = cvc.vendor_id
    278 JOIN Project_Status ps
    279 ON ps.status_id = p.status_id
    280 ORDER BY v.agency_name, ps.status_name, p.project_name;
    281 }}}
    282 
    283 Соодветниот поглед за клиентите е `vw_projects_per_client`:
     193SELECT pd.vendor_id,
     194       pd.agency_name,
     195       pd.project_id,
     196       pd.project_name,
     197       pd.status_name,
     198       pd.start_date,
     199       pd.end_date,
     200       pd.budget,
     201       pd.currency_code
     202FROM vw_project_details pd
     203ORDER BY pd.agency_name, pd.status_name, pd.project_name;
     204}}}
     205
     206=== Поглед 4: Проекти по клиент (vw_projects_per_client) ===
     207
     208Сите проекти на една клиентска компанија низ сите нејзини договори и софтверски агенции. Апликацијата го чита филтриран по клиент на контролната табла на клиентот; оттука клиентот избира завршен проект за кој ќе остави рецензија.
    284209
    285210{{{#!sql
    286211CREATE OR REPLACE VIEW vw_projects_per_client AS
    287 SELECT c.client_id,
    288 c.company_name,
    289 p.project_id,
    290 p.project_name,
    291 ps.status_name,
    292 p.start_date,
    293 p.end_date,
    294 p.budget,
    295 cvc.currency_code
    296 FROM Project p
    297 JOIN Client_Vendor_Contract cvc
    298 ON cvc.contract_id = p.contract_id
    299 JOIN Client c
    300 ON c.client_id = cvc.client_id
    301 JOIN Project_Status ps
    302 ON ps.status_id = p.status_id
    303 ORDER BY c.company_name, ps.status_name, p.project_name;
    304 }}}
    305 
    306 === Буџети по Vendor и Client ===
    307 
    308 За аналитички приказ на вкупниот број на проекти и нивниот буџет се користат `vw_budget_per_vendor` и `vw_budget_per_client`.
     212SELECT pd.client_id,
     213       pd.company_name,
     214       pd.project_id,
     215       pd.project_name,
     216       pd.status_name,
     217       pd.start_date,
     218       pd.end_date,
     219       pd.budget,
     220       pd.currency_code
     221FROM vw_project_details pd
     222ORDER BY pd.company_name, pd.status_name, pd.project_name;
     223}}}
     224
     225=== Поглед 5: Вкупен буџет по софтверска агенција (vw_budget_per_vendor) ===
     226
     227Бројот на проекти и збирот на нивните буџети за секоја софтверска агенција, по валута, за да не се собираат износи во различни валути. Финансиски преглед на обемот на работа на агенцијата: увид во сопствените перформанси и, за клиентот, показател за големината на агенцијата.
    309228
    310229{{{#!sql
    311230CREATE OR REPLACE VIEW vw_budget_per_vendor AS
    312 SELECT v.vendor_id,
    313 v.agency_name,
    314 cvc.currency_code,
    315 COUNT(p.project_id) AS project_count,
    316 SUM(p.budget)       AS total_budget
    317 FROM Project p
    318 JOIN Client_Vendor_Contract cvc
    319 ON cvc.contract_id = p.contract_id
    320 JOIN Vendor v
    321 ON v.vendor_id = cvc.vendor_id
    322 GROUP BY v.vendor_id, v.agency_name, cvc.currency_code
    323 ORDER BY v.agency_name, cvc.currency_code;
    324 }}}
     231SELECT pd.vendor_id,
     232       pd.agency_name,
     233       pd.currency_code,
     234       COUNT(pd.project_id) AS project_count,
     235       SUM(pd.budget)       AS total_budget
     236FROM vw_project_details pd
     237GROUP BY pd.vendor_id, pd.agency_name, pd.currency_code
     238ORDER BY pd.agency_name, pd.currency_code;
     239}}}
     240
     241=== Поглед 6: Вкупен буџет по клиент (vw_budget_per_client) ===
     242
     243Истата агрегација по клиент: вкупниот буџет што клиентот го ангажирал низ сите проекти и договори, по валута. Финансиски преглед на клиентот за следење на потрошениот буџет.
    325244
    326245{{{#!sql
    327246CREATE OR REPLACE VIEW vw_budget_per_client AS
    328 SELECT c.client_id,
    329 c.company_name,
    330 cvc.currency_code,
    331 COUNT(p.project_id) AS project_count,
    332 SUM(p.budget)       AS total_budget
    333 FROM Project p
    334 JOIN Client_Vendor_Contract cvc
    335 ON cvc.contract_id = p.contract_id
    336 JOIN Client c
    337 ON c.client_id = cvc.client_id
    338 GROUP BY c.client_id, c.company_name, cvc.currency_code
    339 ORDER BY c.company_name, cvc.currency_code;
    340 }}}
    341 
    342 Групирањето се врши и според `currency_code`, со што буџетите во различни валути не се собираат во една вредност.
    343 
    344 === Клиенти по индустрија ===
    345 
    346 `vw_clients_per_industry` овозможува приказ на клиентските компании групирани според нивната индустрија:
     247SELECT pd.client_id,
     248       pd.company_name,
     249       pd.currency_code,
     250       COUNT(pd.project_id) AS project_count,
     251       SUM(pd.budget)       AS total_budget
     252FROM vw_project_details pd
     253GROUP BY pd.client_id, pd.company_name, pd.currency_code
     254ORDER BY pd.company_name, pd.currency_code;
     255}}}
     256
     257=== Поглед 7: Клиенти по индустрија (vw_clients_per_industry) ===
     258
     259Клиентите со нивната индустрија. Апликацијата го чита филтриран по индустрија, на пример кога софтверска агенција бара референци во одредена гранка; ја имплементира организацијата на клиентите по индустриска категорија од описот на проектот.
    347260
    348261{{{#!sql
    349262CREATE OR REPLACE VIEW vw_clients_per_industry AS
    350263SELECT i.industry_id,
    351 i.industry_name,
    352 c.client_id,
    353 c.company_name,
    354 c.contact_email
     264       i.industry_name,
     265       c.client_id,
     266       c.company_name,
     267       c.contact_email
    355268FROM Client c
    356 JOIN Industry i
    357 ON i.industry_id = c.industry_id
     269         JOIN Industry i ON i.industry_id = c.industry_id
    358270ORDER BY i.industry_name, c.company_name;
    359271}}}
    360272
    361 === Просечна оценка по Vendor ===
    362 
    363 За секој vendor се пресметува просечна оценка од сите `Review_Score` записи за неговите проекти:
     273=== Поглед 8: Просечна оценка по софтверска агенција (vw_avg_rating_per_vendor) ===
     274
     275Просечната оценка на секоја софтверска агенција преку сите димензии и бројот на рецензии, само од објавени рецензии, така што оспорена или необјавена рецензија не влијае на јавниот просек. Агенциите без објавена рецензија остануваат во резултатот со нула рецензии. Ова е јавната ранг-листа на софтверски агенции, главната функционалност на платформата; филтриран по агенција, погледот ја дава оценката прикажана на нејзиниот профил.
    364276
    365277{{{#!sql
    366278CREATE OR REPLACE VIEW vw_avg_rating_per_vendor AS
    367279SELECT v.vendor_id,
    368 v.agency_name,
    369 COUNT(DISTINCT r.review_id)   AS review_count,
    370 ROUND(AVG(rs.score_value), 2) AS avg_rating
    371 FROM Review_Score rs
    372 JOIN Review r
    373 ON r.review_id = rs.review_id
    374 JOIN Project p
    375 ON p.project_id = r.project_id
    376 JOIN Client_Vendor_Contract cvc
    377 ON cvc.contract_id = p.contract_id
    378 JOIN Vendor v
    379 ON v.vendor_id = cvc.vendor_id
     280       v.agency_name,
     281       COUNT(vr.review_id)                                        AS review_count,
     282       -- просек само од објавените рецензии: збир на збировите / збир на броевите,
     283       -- што е истиот просек како AVG, но без COUNT(DISTINCT); NULLIF штити од делење со нула
     284       ROUND(SUM(vr.score_sum) / NULLIF(SUM(vr.score_cnt), 0), 2) AS avg_rating
     285FROM Vendor v
     286         -- LEFT JOIN: агенција без објавена рецензија останува со review_count = 0
     287         -- потпрашалник: збир и број на оценки по рецензија, само објавени рецензии
     288         LEFT JOIN (SELECT pd.vendor_id,
     289                           r.review_id,
     290                           SUM(rs.score_value)::numeric AS score_sum,  -- ::numeric за децимален просек
     291                           COUNT(*)                     AS score_cnt
     292                    FROM Review r
     293                             JOIN Review_Score rs ON rs.review_id = r.review_id
     294                             JOIN vw_project_details pd ON pd.project_id = r.project_id
     295                    WHERE r.is_published = true
     296                    GROUP BY pd.vendor_id, r.review_id) vr ON vr.vendor_id = v.vendor_id
    380297GROUP BY v.vendor_id, v.agency_name
    381298ORDER BY avg_rating DESC NULLS LAST;
    382299}}}
    383300
    384 `COUNT(DISTINCT r.review_id)` го прикажува бројот на рецензии, додека `AVG` ја пресметува просечната вредност од сите оценети димензии.
    385 
    386 === Нерешени Dispute Tickets ===
    387 
    388 За потребите на dispute системот е креиран `vw_unresolved_dispute_tickets`, кој ги прикажува само тикетите што сè уште не се решени:
    389 
    390 {{{#!sql
    391 CREATE OR REPLACE VIEW vw_unresolved_dispute_tickets AS
     301=== Поглед 9: Нерешени спорови (vw_unresolved_dispute_tickets) ===
     302
     303Сите нерешени спорови со рецензијата, проектот, името на поднесувачот, името на доделениот менаџмент корисник (празно додека спорот не е доделен) и софтверската агенција во чие име е поднесен. Редот на задачи на менаџментот: се чита филтриран по проект, по агенција или по доделен корисник.
     304
     305{{{#!sql
     306-- CREATE OR REPLACE не може да додаде колони во средината на листата,
     307-- па погледот се брише и се креира одново
     308DROP VIEW IF EXISTS vw_unresolved_dispute_tickets;
     309
     310CREATE VIEW vw_unresolved_dispute_tickets AS
    392311SELECT dt.ticket_id,
    393 dt.filed_at,
    394 dt.reason,
    395 r.review_id,
    396 p.project_id,
    397 p.project_name,
    398 vu.user_id AS filed_by_vendor_user_id,
    399 vu_u.first_name || ' ' || vu_u.last_name
    400 AS filed_by_vendor_user,
    401 mu.user_id AS assigned_management_user_id,
    402 mu_u.first_name || ' ' || mu_u.last_name
    403 AS assigned_to_management_user,
    404 dt.created_at,
    405 dt.updated_at
     312       dt.filed_at,
     313       dt.reason,
     314       r.review_id,
     315       p.project_id,
     316       p.project_name,
     317       dt.vendor_user_id                        AS filed_by_vendor_user_id,
     318       -- имињата на поднесувачот (vusr) и на доделениот корисник (musr) од две
     319       -- поврзувања со "User"; musr е NULL додека спорот не е доделен (LEFT JOIN)
     320       vusr.first_name || ' ' || vusr.last_name AS filed_by_vendor_user,
     321       dt.assigned_management_user_id,
     322       musr.first_name || ' ' || musr.last_name AS assigned_to_management_user,
     323       dt.created_at,
     324       dt.updated_at,
     325       ven.vendor_id,
     326       ven.agency_name
    406327FROM Dispute_Ticket dt
    407 JOIN Review r
    408 ON r.review_id = dt.review_id
    409 JOIN Project p
    410 ON p.project_id = r.project_id
    411 JOIN Vendor_User vu
    412 ON vu.user_id = dt.vendor_user_id
    413 JOIN "User" vu_u
    414 ON vu_u.user_id = vu.user_id
    415 LEFT JOIN Management_User mu
    416 ON mu.user_id = dt.assigned_management_user_id
    417 LEFT JOIN "User" mu_u
    418 ON mu_u.user_id = mu.user_id
     328         JOIN Review r ON r.review_id = dt.review_id
     329         JOIN Project p ON p.project_id = r.project_id
     330         JOIN Vendor_User vu ON vu.user_id = dt.vendor_user_id
     331         JOIN Vendor ven ON ven.vendor_id = vu.vendor_id
     332         JOIN "User" vusr ON vusr.user_id = vu.user_id
     333         LEFT JOIN "User" musr ON musr.user_id = dt.assigned_management_user_id
    419334WHERE dt.is_resolved = false
    420335ORDER BY dt.filed_at;
    421336}}}
    422337
    423 За management корисникот се користи `LEFT JOIN`, бидејќи нерешен dispute ticket може сè уште да нема доделен management корисник.
    424 
    425 === Број на проекти по статус ===
    426 
    427 За статистички приказ на состојбата на проектите се користат два погледи кои го пресметуваат бројот на проекти по статус.
    428 
    429 За vendor:
     338=== Поглед 10: Број на проекти по статус по софтверска агенција (vw_project_count_by_status_per_vendor) ===
     339
     340Бројот на проекти на секоја софтверска агенција по статус. Преглед на контролната табла на агенцијата (колку проекти се во тек, завршени, откажани) и, за клиентот, показател колку од проектите на агенцијата навистина се завршуваат.
    430341
    431342{{{#!sql
    432343CREATE OR REPLACE VIEW vw_project_count_by_status_per_vendor AS
    433 SELECT v.vendor_id,
    434 v.agency_name,
    435 ps.status_id,
    436 ps.status_name,
    437 COUNT(p.project_id) AS project_count
    438 FROM Project p
    439 JOIN Client_Vendor_Contract cvc
    440 ON cvc.contract_id = p.contract_id
    441 JOIN Vendor v
    442 ON v.vendor_id = cvc.vendor_id
    443 JOIN Project_Status ps
    444 ON ps.status_id = p.status_id
    445 GROUP BY v.vendor_id,
    446 v.agency_name,
    447 ps.status_id,
    448 ps.status_name
    449 ORDER BY v.agency_name, ps.status_name;
    450 }}}
    451 
    452 За client:
     344SELECT pd.vendor_id,
     345       pd.agency_name,
     346       pd.status_id,
     347       pd.status_name,
     348       COUNT(pd.project_id) AS project_count
     349FROM vw_project_details pd
     350GROUP BY pd.vendor_id, pd.agency_name, pd.status_id, pd.status_name
     351ORDER BY pd.agency_name, pd.status_name;
     352}}}
     353
     354=== Поглед 11: Број на проекти по статус по клиент (vw_project_count_by_status_per_client) ===
     355
     356Истото по клиент: состојбата на портфолиото на една клиентска компанија низ сите нејзини софтверски агенции, на контролната табла на клиентот.
    453357
    454358{{{#!sql
    455359CREATE OR REPLACE VIEW vw_project_count_by_status_per_client AS
    456 SELECT c.client_id,
    457 c.company_name,
    458 ps.status_id,
    459 ps.status_name,
    460 COUNT(p.project_id) AS project_count
    461 FROM Project p
    462 JOIN Client_Vendor_Contract cvc
    463 ON cvc.contract_id = p.contract_id
    464 JOIN Client c
    465 ON c.client_id = cvc.client_id
    466 JOIN Project_Status ps
    467 ON ps.status_id = p.status_id
    468 GROUP BY c.client_id,
    469 c.company_name,
    470 ps.status_id,
    471 ps.status_name
    472 ORDER BY c.company_name, ps.status_name;
    473 }}}
    474 
    475 Овие погледи овозможуваат брзо добивање на бројот на проекти во статуси како `Draft`, `In Progress`, `Completed` и останатите дефинирани статуси.
    476 
    477 === Договори по Vendor и Client ===
    478 
    479 За приказ на договорите од двете перспективи се користат `vw_contracts_per_vendor` и `vw_contracts_per_client`.
     360SELECT pd.client_id,
     361       pd.company_name,
     362       pd.status_id,
     363       pd.status_name,
     364       COUNT(pd.project_id) AS project_count
     365FROM vw_project_details pd
     366GROUP BY pd.client_id, pd.company_name, pd.status_id, pd.status_name
     367ORDER BY pd.company_name, pd.status_name;
     368}}}
     369
     370=== Поглед 12: Договори по софтверска агенција (vw_contracts_per_vendor) ===
     371
     372Сите договори на секоја софтверска агенција со името на клиентот, периодот, вредноста и валутата, прво активните, па најновите. Преглед на активните и историските договори на агенцијата; договорот е врската преку која проектите се врзуваат за клиент и агенција.
    480373
    481374{{{#!sql
    482375CREATE OR REPLACE VIEW vw_contracts_per_vendor AS
    483 SELECT v.vendor_id,
    484 v.agency_name,
    485 cvc.contract_id,
    486 cvc.contract_number,
    487 cvc.contract_title,
    488 c.client_id,
    489 c.company_name AS client_name,
    490 cvc.start_date,
    491 cvc.end_date,
    492 cvc.total_value,
    493 cvc.currency_code,
    494 cvc.is_active
    495 FROM Client_Vendor_Contract cvc
    496 JOIN Vendor v
    497 ON v.vendor_id = cvc.vendor_id
    498 JOIN Client c
    499 ON c.client_id = cvc.client_id
    500 ORDER BY v.agency_name,
    501 cvc.is_active DESC,
    502 cvc.start_date DESC;
    503 }}}
     376SELECT cd.vendor_id,
     377       cd.agency_name,
     378       cd.contract_id,
     379       cd.contract_number,
     380       cd.contract_title,
     381       cd.client_id,
     382       cd.company_name AS client_name,
     383       cd.start_date,
     384       cd.end_date,
     385       cd.total_value,
     386       cd.currency_code,
     387       cd.is_active
     388FROM vw_contract_details cd
     389ORDER BY cd.agency_name, cd.is_active DESC, cd.start_date DESC;
     390}}}
     391
     392=== Поглед 13: Договори по клиент (vw_contracts_per_client) ===
     393
     394Истото од страна на клиентот: сите негови договори со името на софтверската агенција, за прегледот на активните и историските договори на клиентската компанија.
    504395
    505396{{{#!sql
    506397CREATE OR REPLACE VIEW vw_contracts_per_client AS
    507 SELECT c.client_id,
    508 c.company_name,
    509 cvc.contract_id,
    510 cvc.contract_number,
    511 cvc.contract_title,
    512 v.vendor_id,
    513 v.agency_name AS vendor_name,
    514 cvc.start_date,
    515 cvc.end_date,
    516 cvc.total_value,
    517 cvc.currency_code,
    518 cvc.is_active
    519 FROM Client_Vendor_Contract cvc
    520 JOIN Client c
    521 ON c.client_id = cvc.client_id
    522 JOIN Vendor v
    523 ON v.vendor_id = cvc.vendor_id
    524 ORDER BY c.company_name,
    525 cvc.is_active DESC,
    526 cvc.start_date DESC;
    527 }}}
    528 
    529 Со сортирање на `is_active DESC`, активните договори се прикажуваат пред историските договори.
    530 
    531 === Vendor претплати ===
    532 
    533 За приказ на активните и историските претплати на агенциите е дефиниран `vw_vendor_subscriptions`:
     398SELECT cd.client_id,
     399       cd.company_name,
     400       cd.contract_id,
     401       cd.contract_number,
     402       cd.contract_title,
     403       cd.vendor_id,
     404       cd.agency_name AS vendor_name,
     405       cd.start_date,
     406       cd.end_date,
     407       cd.total_value,
     408       cd.currency_code,
     409       cd.is_active
     410FROM vw_contract_details cd
     411ORDER BY cd.company_name, cd.is_active DESC, cd.start_date DESC;
     412}}}
     413
     414=== Поглед 14: Претплати на софтверски агенции (vw_vendor_subscriptions) ===
     415
     416Сите периоди на претплата на секоја софтверска агенција со нивото и ефективната цена: договорната цена ако постои, инаку цената од ценовникот. Преглед на активната и историските претплати на агенцијата и основа за наплатата.
    534417
    535418{{{#!sql
    536419CREATE OR REPLACE VIEW vw_vendor_subscriptions AS
    537420SELECT v.vendor_id,
    538 v.agency_name,
    539 st.tier_id,
    540 st.tier_name,
    541 COALESCE(
    542 vs.negotiated_price,
    543 st.list_price,
    544 0
    545 ) AS effective_price,
    546 vs.start_date,
    547 vs.end_date,
    548 vs.is_active
     421       v.agency_name,
     422       st.tier_id,
     423       st.tier_name,
     424       -- договорената цена ако постои, инаку цената од ценовникот, инаку 0
     425       COALESCE(vs.negotiated_price, st.list_price, 0) AS effective_price,
     426       vs.start_date,
     427       vs.end_date,
     428       vs.is_active
    549429FROM Vendor_Subscription vs
    550 JOIN Vendor v
    551 ON v.vendor_id = vs.vendor_id
    552 JOIN Subscription_Tier st
    553 ON st.tier_id = vs.tier_id
    554 ORDER BY v.agency_name,
    555 vs.is_active DESC,
    556 vs.start_date DESC;
    557 }}}
    558 
    559 Полето `effective_price` ја користи договорената цена доколку постои. Во спротивно се користи стандардната цена од `Subscription_Tier`.
    560 
    561 На овој начин views обезбедуваат готови прикази за најчестите оперативни и аналитички потреби на системот, без истите `JOIN`, `GROUP BY` и агрегатни операции да се повторуваат во секој прашалник.
    562 
    563 == Полнење со податоци ==
    564 
    565 За тестирање на базата се користи `seed_generator.py`, кој генерира реалистични податоци и CSV датотеки за табелите.
    566 
    567 Генерирањето се извршува со:
    568 
    569 * [attachment:seed_generator.py seed_generator.py]
    570 
    571 Податоците се генерираат според зависностите помеѓу табелите, така што се почитуваат дефинираните foreign key ограничувања.
     430         JOIN Vendor v ON v.vendor_id = vs.vendor_id
     431         JOIN Subscription_Tier st ON st.tier_id = vs.tier_id
     432ORDER BY v.agency_name, vs.is_active DESC, vs.start_date DESC;
     433}}}
     434
     435=== Поглед 15: Оценки по димензија по софтверска агенција (vw_vendor_rating_by_dimension) ===
     436
     437За секоја софтверска агенција и димензија на оценување: бројот на оценки, просекот, минимумот и максимумот, само од објавени рецензии. Деталниот приказ зад просечната оценка на профилот на агенцијата (квалитет на код, комуникација, почитување на рокови и останатите димензии), односно повеќедимензионалното оценување по кое платформата се разликува од решенијата со една оценка.
     438
     439{{{#!sql
     440CREATE OR REPLACE VIEW vw_vendor_rating_by_dimension AS
     441SELECT pd.vendor_id,
     442       pd.agency_name,
     443       rd.dimension_id,
     444       rd.dimension_name,
     445       COUNT(*)                      AS score_count,
     446       ROUND(AVG(rs.score_value), 2) AS avg_score,
     447       MIN(rs.score_value)           AS min_score,
     448       MAX(rs.score_value)           AS max_score
     449FROM Review_Score rs
     450         JOIN Rating_Dimension rd ON rd.dimension_id = rs.dimension_id
     451         JOIN Review r ON r.review_id = rs.review_id
     452         JOIN vw_project_details pd ON pd.project_id = r.project_id
     453WHERE r.is_published = true
     454GROUP BY pd.vendor_id, pd.agency_name, rd.dimension_id, rd.dimension_name;
     455}}}
     456
     457=== Поглед 16: Промени на буџетот по проект (vw_project_budget_changes) ===
     458
     459Историјата на буџетот: по една редица за секоја промена на буџетот на проект, со стариот и новиот износ, разликата и процентуалната промена. Се чита филтриран по проект на страницата на проектот или по клиент и софтверска агенција за преглед на сите промени на една страна.
     460
     461{{{#!sql
     462CREATE OR REPLACE VIEW vw_project_budget_changes AS
     463SELECT pba.audit_id,
     464       pd.project_id,
     465       pd.project_name,
     466       pd.vendor_id,
     467       pd.client_id,
     468       pd.currency_code,
     469       pba.old_budget,
     470       pba.new_budget,
     471       pba.new_budget - pba.old_budget AS delta,
     472       -- процентуална промена; NULLIF штити од делење со нула
     473       ROUND(100.0 * (pba.new_budget - pba.old_budget) / NULLIF(pba.old_budget, 0), 1) AS pct_change,
     474       pba.created_at                  AS changed_at
     475FROM Project_Budget_Audit pba
     476         JOIN vw_project_details pd ON pd.project_id = pba.project_id;
     477}}}