= DatabaseCreation =

== Опис ==

Релационата база на податоци е имплементирана во PostgreSQL според претходно дефинираниот ER и релационен модел.

DDL скриптата ги дефинира сите потребни табели, примарни и странски клучеви, ограничувања, проверки и default вредности.

Целосната скрипта е достапна во:

* [attachment:ddl.sql ddl.sql]

== Основни табели ==

Во почетниот дел од скриптата се креираат помошните табели кои понатаму се користат од останатите ентитети.

Пример за `Role`:

{{{#!sql
CREATE TABLE Role (
role_id     SERIAL NOT NULL PRIMARY KEY,
role_name   text   NOT NULL UNIQUE,
description text
);
}}}

На сличен начин се дефинирани `Permission`, `Industry`, `Project_Status`, `Technology`, `Rating_Dimension` и `Subscription_Tier`.

== Корисници ==

Сите типови на корисници ги содржат заедничките податоци во табелата `"User"`:

{{{#!sql
CREATE TABLE "User" (
user_id       SERIAL    NOT NULL PRIMARY KEY,
type          text      NOT NULL
CHECK (type IN ('client', 'vendor', 'management')),
first_name    text      NOT NULL,
last_name     text      NOT NULL,
email         text      NOT NULL UNIQUE,
password_hash text      NOT NULL,
is_active     bool      NOT NULL DEFAULT false,
last_login_at timestamp,
created_at    timestamp NOT NULL DEFAULT NOW(),
updated_at    timestamp NOT NULL DEFAULT NOW()
);
}}}

Полето `type` е ограничено со `CHECK`, така што може да има само една од трите дозволени вредности.

За конкретните типови на корисници се користат дополнителни табели. На пример:

{{{#!sql
CREATE TABLE Client_User (
user_id   int4 NOT NULL PRIMARY KEY,
client_id int4 NOT NULL,

CONSTRAINT fk_clientuser_user
FOREIGN KEY (user_id) REFERENCES "User" (user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,

CONSTRAINT fk_clientuser_client
FOREIGN KEY (client_id) REFERENCES Client (client_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
}}}

На истиот принцип се дефинирани и `Vendor_User` и `Management_User`.

== Клиенти, продавачи и договори ==

Клиентот е поврзан со индустријата преку странски клуч:

{{{#!sql
CREATE TABLE Client (
client_id     SERIAL NOT NULL PRIMARY KEY,
industry_id   int4   NOT NULL,
company_name  text   NOT NULL,
contact_email text   NOT NULL,

CONSTRAINT fk_client_industry
FOREIGN KEY (industry_id) REFERENCES Industry (industry_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
}}}

Врската помеѓу клиент и vendor е претставена преку договор:

{{{#!sql
CREATE TABLE Client_Vendor_Contract (
contract_id     SERIAL NOT NULL PRIMARY KEY,
client_id       int4   NOT NULL,
vendor_id       int4   NOT NULL,
contract_number text UNIQUE,
contract_title  text   NOT NULL,
start_date      date   NOT NULL DEFAULT CURRENT_DATE,
end_date        date,
total_value     numeric(10,2),
currency_code   text,
terms_summary   text,
is_active       bool      NOT NULL DEFAULT true,
created_at      timestamp NOT NULL DEFAULT NOW(),
updated_at      timestamp NOT NULL DEFAULT NOW(),

CONSTRAINT chk_cvc_dates
CHECK (end_date IS NULL OR end_date > start_date),

CONSTRAINT fk_cvc_client
FOREIGN KEY (client_id) REFERENCES Client (client_id),

CONSTRAINT fk_cvc_vendor
FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id)
);
}}}

Со `CHECK` ограничувањето се спречува крајниот датум на договорот да биде пред почетниот.

== Проекти ==

Секој проект е поврзан со договор и со тековен статус:

{{{#!sql
CREATE TABLE Project (
project_id   SERIAL        NOT NULL PRIMARY KEY,
contract_id  int4          NOT NULL,
status_id    int4          NOT NULL,
project_name text          NOT NULL,
start_date   date          NOT NULL DEFAULT CURRENT_DATE,
end_date     date,
budget       numeric(10,2) NOT NULL,
created_at   timestamp     NOT NULL DEFAULT NOW(),
updated_at   timestamp     NOT NULL DEFAULT NOW(),

CONSTRAINT chk_project_dates
CHECK (end_date IS NULL OR end_date > start_date),

CONSTRAINT fk_project_contract
FOREIGN KEY (contract_id)
REFERENCES Client_Vendor_Contract (contract_id),

CONSTRAINT fk_project_status
FOREIGN KEY (status_id)
REFERENCES Project_Status (status_id)
);
}}}

Технологиите користени на проектите се моделирани со many-to-many релација:

{{{#!sql
CREATE TABLE Project_Technology (
project_id    int4 NOT NULL,
technology_id int4 NOT NULL,

PRIMARY KEY (project_id, technology_id),

CONSTRAINT fk_projtec_project
FOREIGN KEY (project_id) REFERENCES Project (project_id)
ON DELETE CASCADE,

CONSTRAINT fk_projtec_technology
FOREIGN KEY (technology_id) REFERENCES Technology (technology_id)
ON DELETE RESTRICT
);
}}}

== Историја на проектите ==

За промените на буџетот се користи посебна audit табела:

{{{#!sql
CREATE TABLE Project_Budget_Audit (
audit_id   SERIAL        NOT NULL PRIMARY KEY,
project_id int4          NOT NULL,
old_budget numeric(10,2) NOT NULL,
new_budget numeric(10,2) NOT NULL,
created_at timestamp     NOT NULL DEFAULT NOW(),
updated_at timestamp     NOT NULL DEFAULT NOW(),

CONSTRAINT fk_budgetaudit_project
FOREIGN KEY (project_id) REFERENCES Project (project_id)
);
}}}

Промените на статусот се зачувуваат во `Project_Status_History`, каде покрај проектот и статусот се чува и корисникот кој ја направил промената.

== Reviews и оценки ==

За секој проект може да постои една рецензија:

{{{#!sql
CREATE TABLE Review (
review_id       SERIAL NOT NULL PRIMARY KEY,
project_id      int4   NOT NULL UNIQUE,
client_user_id  int4   NOT NULL,
review_date     date   NOT NULL DEFAULT CURRENT_DATE,
summary_text    text   NOT NULL,
is_published    bool   NOT NULL DEFAULT false,

CONSTRAINT fk_review_project
FOREIGN KEY (project_id) REFERENCES Project (project_id),

CONSTRAINT fk_review_clientuser
FOREIGN KEY (client_user_id) REFERENCES Client_User (user_id)
);
}}}

Оценките се чуваат одделно за секоја димензија:

{{{#!sql
CREATE TABLE Review_Score (
review_id    int4 NOT NULL,
dimension_id int4 NOT NULL,
score_value  int4 NOT NULL,

PRIMARY KEY (review_id, dimension_id),

CONSTRAINT fk_reviewscore_review
FOREIGN KEY (review_id) REFERENCES Review (review_id)
ON DELETE CASCADE,

CONSTRAINT fk_reviewscore_dimension
FOREIGN KEY (dimension_id)
REFERENCES Rating_Dimension (dimension_id)
);
}}}

== Dispute систем ==

При оспорување на review се креира запис во `Dispute_Ticket`:

{{{#!sql
CREATE TABLE Dispute_Ticket (
ticket_id                     SERIAL NOT NULL PRIMARY KEY,
assigned_management_user_id   int4 DEFAULT NULL,
review_id                     int4 NOT NULL,
vendor_user_id                int4 NOT NULL,
reason                        text NOT NULL,
is_resolved                   bool NOT NULL DEFAULT false,
filed_at                      date NOT NULL DEFAULT CURRENT_DATE,
resolved_at                   timestamp,
resolution_note               text
);
}}}

Табелата е дополнително поврзана со `Management_User`, `Review` и `Vendor_User` преку странски клучеви.

== Views ==

За почесто користените прикази и аналитички податоци се дефинирани PostgreSQL views. Тие ги обединуваат податоците од повеќе поврзани табели и овозможуваат поедноставен пристап до информации за проекти, договори, буџети, оценки, спорови и претплати.

Целосната скрипта е достапна во:

* [attachment:views.sql views.sql]

=== Проекти по Vendor и Client ===

Погледот `vw_projects_per_vendor` ги прикажува сите проекти поврзани со одреден vendor, заедно со статусот, периодот и буџетот:

{{{#!sql
CREATE OR REPLACE VIEW vw_projects_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
p.project_id,
p.project_name,
ps.status_name,
p.start_date,
p.end_date,
p.budget,
cvc.currency_code
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
JOIN Project_Status ps
ON ps.status_id = p.status_id
ORDER BY v.agency_name, ps.status_name, p.project_name;
}}}

Соодветниот поглед за клиентите е `vw_projects_per_client`:

{{{#!sql
CREATE OR REPLACE VIEW vw_projects_per_client AS
SELECT c.client_id,
c.company_name,
p.project_id,
p.project_name,
ps.status_name,
p.start_date,
p.end_date,
p.budget,
cvc.currency_code
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Client c
ON c.client_id = cvc.client_id
JOIN Project_Status ps
ON ps.status_id = p.status_id
ORDER BY c.company_name, ps.status_name, p.project_name;
}}}

=== Буџети по Vendor и Client ===

За аналитички приказ на вкупниот број на проекти и нивниот буџет се користат `vw_budget_per_vendor` и `vw_budget_per_client`.

{{{#!sql
CREATE OR REPLACE VIEW vw_budget_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
cvc.currency_code,
COUNT(p.project_id) AS project_count,
SUM(p.budget)       AS total_budget
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
GROUP BY v.vendor_id, v.agency_name, cvc.currency_code
ORDER BY v.agency_name, cvc.currency_code;
}}}

{{{#!sql
CREATE OR REPLACE VIEW vw_budget_per_client AS
SELECT c.client_id,
c.company_name,
cvc.currency_code,
COUNT(p.project_id) AS project_count,
SUM(p.budget)       AS total_budget
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Client c
ON c.client_id = cvc.client_id
GROUP BY c.client_id, c.company_name, cvc.currency_code
ORDER BY c.company_name, cvc.currency_code;
}}}

Групирањето се врши и според `currency_code`, со што буџетите во различни валути не се собираат во една вредност.

=== Клиенти по индустрија ===

`vw_clients_per_industry` овозможува приказ на клиентските компании групирани според нивната индустрија:

{{{#!sql
CREATE OR REPLACE VIEW vw_clients_per_industry AS
SELECT i.industry_id,
i.industry_name,
c.client_id,
c.company_name,
c.contact_email
FROM Client c
JOIN Industry i
ON i.industry_id = c.industry_id
ORDER BY i.industry_name, c.company_name;
}}}

=== Просечна оценка по Vendor ===

За секој vendor се пресметува просечна оценка од сите `Review_Score` записи за неговите проекти:

{{{#!sql
CREATE OR REPLACE VIEW vw_avg_rating_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
COUNT(DISTINCT r.review_id)   AS review_count,
ROUND(AVG(rs.score_value), 2) AS avg_rating
FROM Review_Score rs
JOIN Review r
ON r.review_id = rs.review_id
JOIN Project p
ON p.project_id = r.project_id
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
GROUP BY v.vendor_id, v.agency_name
ORDER BY avg_rating DESC NULLS LAST;
}}}

`COUNT(DISTINCT r.review_id)` го прикажува бројот на рецензии, додека `AVG` ја пресметува просечната вредност од сите оценети димензии.

=== Нерешени Dispute Tickets ===

За потребите на dispute системот е креиран `vw_unresolved_dispute_tickets`, кој ги прикажува само тикетите што сè уште не се решени:

{{{#!sql
CREATE OR REPLACE VIEW vw_unresolved_dispute_tickets AS
SELECT dt.ticket_id,
dt.filed_at,
dt.reason,
r.review_id,
p.project_id,
p.project_name,
vu.user_id AS filed_by_vendor_user_id,
vu_u.first_name || ' ' || vu_u.last_name
AS filed_by_vendor_user,
mu.user_id AS assigned_management_user_id,
mu_u.first_name || ' ' || mu_u.last_name
AS assigned_to_management_user,
dt.created_at,
dt.updated_at
FROM Dispute_Ticket dt
JOIN Review r
ON r.review_id = dt.review_id
JOIN Project p
ON p.project_id = r.project_id
JOIN Vendor_User vu
ON vu.user_id = dt.vendor_user_id
JOIN "User" vu_u
ON vu_u.user_id = vu.user_id
LEFT JOIN Management_User mu
ON mu.user_id = dt.assigned_management_user_id
LEFT JOIN "User" mu_u
ON mu_u.user_id = mu.user_id
WHERE dt.is_resolved = false
ORDER BY dt.filed_at;
}}}

За management корисникот се користи `LEFT JOIN`, бидејќи нерешен dispute ticket може сè уште да нема доделен management корисник.

=== Број на проекти по статус ===

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

За vendor:

{{{#!sql
CREATE OR REPLACE VIEW vw_project_count_by_status_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
ps.status_id,
ps.status_name,
COUNT(p.project_id) AS project_count
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
JOIN Project_Status ps
ON ps.status_id = p.status_id
GROUP BY v.vendor_id,
v.agency_name,
ps.status_id,
ps.status_name
ORDER BY v.agency_name, ps.status_name;
}}}

За client:

{{{#!sql
CREATE OR REPLACE VIEW vw_project_count_by_status_per_client AS
SELECT c.client_id,
c.company_name,
ps.status_id,
ps.status_name,
COUNT(p.project_id) AS project_count
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Client c
ON c.client_id = cvc.client_id
JOIN Project_Status ps
ON ps.status_id = p.status_id
GROUP BY c.client_id,
c.company_name,
ps.status_id,
ps.status_name
ORDER BY c.company_name, ps.status_name;
}}}

Овие погледи овозможуваат брзо добивање на бројот на проекти во статуси како `Draft`, `In Progress`, `Completed` и останатите дефинирани статуси.

=== Договори по Vendor и Client ===

За приказ на договорите од двете перспективи се користат `vw_contracts_per_vendor` и `vw_contracts_per_client`.

{{{#!sql
CREATE OR REPLACE VIEW vw_contracts_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
cvc.contract_id,
cvc.contract_number,
cvc.contract_title,
c.client_id,
c.company_name AS client_name,
cvc.start_date,
cvc.end_date,
cvc.total_value,
cvc.currency_code,
cvc.is_active
FROM Client_Vendor_Contract cvc
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
JOIN Client c
ON c.client_id = cvc.client_id
ORDER BY v.agency_name,
cvc.is_active DESC,
cvc.start_date DESC;
}}}

{{{#!sql
CREATE OR REPLACE VIEW vw_contracts_per_client AS
SELECT c.client_id,
c.company_name,
cvc.contract_id,
cvc.contract_number,
cvc.contract_title,
v.vendor_id,
v.agency_name AS vendor_name,
cvc.start_date,
cvc.end_date,
cvc.total_value,
cvc.currency_code,
cvc.is_active
FROM Client_Vendor_Contract cvc
JOIN Client c
ON c.client_id = cvc.client_id
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
ORDER BY c.company_name,
cvc.is_active DESC,
cvc.start_date DESC;
}}}

Со сортирање на `is_active DESC`, активните договори се прикажуваат пред историските договори.

=== Vendor претплати ===

За приказ на активните и историските претплати на агенциите е дефиниран `vw_vendor_subscriptions`:

{{{#!sql
CREATE OR REPLACE VIEW vw_vendor_subscriptions AS
SELECT v.vendor_id,
v.agency_name,
st.tier_id,
st.tier_name,
COALESCE(
vs.negotiated_price,
st.list_price,
0
) AS effective_price,
vs.start_date,
vs.end_date,
vs.is_active
FROM Vendor_Subscription vs
JOIN Vendor v
ON v.vendor_id = vs.vendor_id
JOIN Subscription_Tier st
ON st.tier_id = vs.tier_id
ORDER BY v.agency_name,
vs.is_active DESC,
vs.start_date DESC;
}}}

Полето `effective_price` ја користи договорената цена доколку постои. Во спротивно се користи стандардната цена од `Subscription_Tier`.

На овој начин views обезбедуваат готови прикази за најчестите оперативни и аналитички потреби на системот, без истите `JOIN`, `GROUP BY` и агрегатни операции да се повторуваат во секој прашалник.

== Полнење со податоци ==

За тестирање на базата се користи `seed_generator.py`, кој генерира реалистични податоци и CSV датотеки за табелите.

Генерирањето се извршува со:

* [attachment:seed_generator.py seed_generator.py]

Податоците се генерираат според зависностите помеѓу табелите, така што се почитуваат дефинираните foreign key ограничувања.