reporting.erd

[declarations]
id | uuid | not null
declaration_signed_id | uuid | not null
employee_id | uuid | not null
person_id | uuid | not null
start_date | date | not null
end_date | date | not null
status | enum('') | not null
signed_at | timestamp | not null
inserted_at | timestamp | not null
inserted_by | uuid | not null
updated_at | timestamp | not null
updated_by | uuid | not null
is_active | boolean
scope | enum('') | not null
division_id | uuid | not null
legal_entity_id | uuid | not null

[declarations_status_hstr]
id | integer | not null
declaration_id | UUID | not null
status | enum('') | not null
inserted_at | timestamp | not null

[divisions]
id | uuid | not null
legal_entity_id | uuid | not null
name | varchar | not null
addresses | jsonb | not null
external_id | varchar
phones | jsonb[] | not null
email | varchar
mountain_group | varchar
type | varchar | not null
status | varchar | not null
is_active | boolean | not null
inserted_at | timestamp | not null
updated_at | timestamp | not null
location | geometry

[employees]
id | uuid | not null
legal_entity_id | uuid | not null
party_id | uuid | not null
division_id | uuid
position | varchar | not null
start_date | date | not null
end_date | date
inserted_at | timestamp | not null
inserted_by | uuid | not null
updated_by | uuid | not null
updated_at | timestamp | not null
status | enum('') | not null
employee_type | enum('doctor','hr','owner','accountant') | not null
is_active | boolean | not null
status_reason | string

[employee_doctors]
id | uuid | not null
education | jsonb[] | not null
qualification | jsonb[]
specialities | jsonb[] | not null
science_degree | jsonb
employee_id | uuid | not null
inserted_at | timestamp | not null
updated_at | timestamp | not null

[legal_entities]
id | uuid | not null
name | varchar | not null
short_name | varchar
public_name | varchar
type | enum('msp','mis','drug_store') | not null
edrpou | varchar(8) | not null unique
addresses | jsonb[] | not null
phones | jsonb[]
email | varchar
inserted_at | timestamp | not null
inserted_by | uuid | not null
updated_at | timestamp
updated_by | uuid | not null
is_active | boolean | not null
kveds | jsonb | not null
status | enum('new','verified') | not null
owner_property_type | enum('state','private') | not null
legal_form | enum('тов','фоп') | not null
created_via_mis_client_id | uuid | not null
nhs_verified | boolean | not null
mis_verified | enum('verified','not_verified') | not null

[medical_service_providers]
id | uuid | not null
accreditation | jsonb | not null
licenses | jsonb[]
inserted_at | timestamp | not null
updated_at | timestamp | not null
legal_entity_id | uuid | not null

[parties]
id | uuid | not null
last_name | varchar | not null
first_name | varchar | not null
second_name | varchar

[party_users]
id       | uuid | not null
party_id | uuid | not null
user_id  | uuid | not null

[medications]
id | uuid | not null
name | varchar | not null 
type | varchar | not null
manufacturer | jsonb
code_atc | varchar
is_active | boolean | not null
form | varchar | not null
container | jsonb
package_qty | number
package_min_qty | number
certificate | varchar
certificate_expired_at | date
inserted_at | timestamp | not null
inserted_by | uuid | not null
updated_at | timestamp | not null
updated_by | uuid | not null

[medical_programs]
id | uuid | not null
name | varchar | not null
is_active | boolean | not null
inserted_at | timestamp | not null
inserted_by | uuid | not null
updated_at | timestamp | not null
updated_by | uuid | not null

[medication_requests]
id | uuid | not null
request_number | varchar | not null
verification_code | varchar
created_at | date | not null
started_at | date| not null
ended_at | date| not null
dispense_valid_from | date| not null
dispense_valid_to | date| not null
person_id | uuid | not null
employee_id | uuid | not null
division_id | uuid | not null
medication_id | uuid | not null
medication_qty | number | not null
status | varchar | not null
is_active | boolean | not null
rejected_at | date
rejected_by | uuid
reject_reason | varchar
medication_request_requests_id | uuid | not null
medical_program_id | uuid
inserted_at | timestamp | not null
inserted_by | uuid | not null
updated_at | timestamp | not null
updated_by | uuid | not null

[medication_dispenses]
id | uuid | not null
medication_request_id | uuid | not null
dispensed_at | date | not null
party_id | uuid | not null
legal_entity_id | uuid | not null
division_id | uuid | not null
medical_program_id | uuid
payment_id | varchar
status | varchar | not null
is_active | boolean | not null
inserted_at | timestamp | not null
inserted_by | uuid | not null
updated_at | timestamp | not null
updated_by | uuid | not null

[medication_dispense_details]
id | uuid | not null
medication_dispense_id | uuid | not null
medication_id | uuid | not null
medication_qty | number | not null
sell_price | number | not null
sell_amount | number | not null
discount_amount | number | not null
reimbursement_amount | number | not null

[medication_dispense_status_hstr]
id | integer | not null
medication_dispense_id | uuid | not null
status | varchar | not null
inserted_at | timestamp | not null

[medication_requests_status_hstr]
id | integer | not null
medication_request_id | uuid | not null
status | varchar | not null
inserted_at | timestamp | not null

[regions]
id | UUID | NOT NULL
koatuu | VARCHAR('10')
name | VARCHAR('50') | NOT NULL

[districts]
id | UUID | NOT NULL
koatuu | VARCHAR('10')
region_id | UUID | NOT NULL
name | VARCHAR('100') | NOT NULL

[settlements]
id | UUID | NOT NULL
type | VARCHAR(1)
koatuu | VARCHAR('10')
name | VARCHAR | NOT NULL
district_id | UUID
region_id | UUID | NOT NULL
mountain_group | VARCHAR(150)
parent_settlement | UUID

[streets]
id | UUID | NOT NULL
settlement_id | UUID | NOT NULL
type | VARCHAR(255) | NOT NULL
name | VARCHAR(150) | NOT NULL
inserted_at | timestamp | not null
updated_at | timestamp

[streets_aliases]
id | UUID | NOT NULL
street_id | UUID | NOT NULL
name | VARCHAR(150) | NOT NULL

[billings]
id | UUID | NOT NULL
billing_date | date 
declaration_id | uuid 
legal_entity_id | uuid 
division_id | uuid 
mountain_group | boolean
person_age | integer
inserted_at | timestamp | not null
updated_at | timestamp | not null
compensation_group | string | not null
decision_id | string | not null
red_msp_id | uuid | not null
is_valid | boolean | not null

[billing_logs]
id | UUID | NOT NULL
billing_date | date | NOT NULL
legal_entity_id | uuid 
declaration_id| uuid 
person_id | uuid 
division_id | uuid 
message | ARCHAR(255)
person_age | integer
inserted_at | timestamp | not null

[report_logs]
id | UUID | NOT NULL
type | VARCHAR(255)
public_url | VARCHAR(255)
inserted_at | timestamp | not null

[red_msps]
id | uuid | not null
name | varchar | not null
edrpou | varchar(8) | not null unique
inserted_at | timestamp | not null
updated_at | timestamp
is_active | boolean | not null
type | enum('child','general') | not null
population_count | integer| not null

[red_msps_territories]
id | uuid | not null
red_msp_id | uuid | not null
settlement_id | uuid | not null
street_type | VARCHAR(250)
street_name | VARCHAR(250)
postal_code | VARCHAR(5)
buildings   | VARCHAR(2000)


employees 1--* declarations
divisions *--1 legal_entities
employees *--1 legal_entities
parties 1--+ employees
parties 1--* party_users
employees *--1 divisions
employees 1--? employee_doctors
medical_service_providers 1--1 legal_entities
parties 1--? doctors
declarations 1--* declarations_status_hstr

medication_requests *--1 legal_entities
medication_requests *--1 divisions
medication_requests *--1 employees 
medication_requests *--1 medications
medication_requests 1--* medication_dispenses
medication_requests *--1 medication_requests_status_hstr
medication_dispenses *--1 legal_entities
medication_dispenses *--1 divisions
medication_dispenses *--1 employees
medication_dispenses *--1 medication_dispense_status_hstr
medication_dispenses 1--* medication_dispense_details
medication_dispense_details 1--1 medications



regions 1--+ districts
regions 1--+ settlements
districts 1--+ settlements
settlements 1--+ streets
regions 1--+ streets
districts 1--+ streets
streets 1--+ streets_aliases


billings 1--* declarations
billings *--1 legal_entities
billings *--1 divisions
billings 1--* red_msps
red_msps 1--* red_msps_territories