= 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` преку странски клучеви.

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

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

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

* [attachment:seed_generator.py seed_generator.py]

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