# ER-диаграмма схемы `dkb`

Диаграмма построена по SQLAlchemy-моделям и Alembic-миграциям. `PK` — первичный ключ, `FK` — внешний ключ, `UK` — уникальное поле. Все таблицы находятся в PostgreSQL-схеме `dkb`.

```mermaid
erDiagram
    CHART_OF_ACCOUNT {
        uuid id PK
        string code UK
        uuid parent_id FK "nullable"
        string name
        enum kind "A | P | AP"
    }

    REGIONAL_OFFICE {
        uuid id PK
        string name
        boolean is_active
    }

    COMPANY_GROUP {
        uuid id PK
        string crm_id UK
        string name
        uuid base_regional_office_id FK
        datetime valid_from
        string updated_by
        datetime created_at
    }

    COMPANY_GROUP_HISTORY {
        uuid id PK
        uuid company_group_id FK
        string crm_id UK
        string name
        uuid base_regional_office_id "snapshot, not FK"
        datetime valid_from
        datetime valid_to
        string updated_by
    }

    LEGAL_ENTITY {
        uuid id PK
        string inn UK
        enum entity_type "IP | UL"
        string name
        uuid company_group_id FK
        uuid regional_office_id FK
        date close_date
        string updated_by
        datetime created_at
    }

    LEGAL_ENTITY_HISTORY {
        uuid id PK
        uuid legal_entity_id FK
        string inn UK
        enum entity_type "IP | UL"
        string name
        uuid company_group_id FK
        uuid regional_office_id FK
        date close_date
        datetime valid_from
        datetime valid_to
        string changed_by
    }

    LEGAL_ENTITY_ALIAS {
        uuid id PK
        uuid legal_entity_id FK
        string alias
        string normalized_alias
        string created_by
        datetime created_at
    }

    RELATED_GROUP {
        uuid id PK
        string code UK
        uuid company_group_id FK
        enum kind "GSK | GSZ"
        string name
        date created_date
    }

    RELATED_GROUP_MEMBER {
        uuid id PK
        uuid related_group_id FK
        uuid legal_entity_id FK
        enum kind "GSK | GSZ"
        datetime valid_from
        datetime valid_to
        boolean is_current
        string created_by
    }

    CONSOLIDATION {
        uuid id PK
        uuid related_group_id FK
        smallint year
        enum quarter "Q1 | Q2 | Q3 | Q4"
        datetime created_at
    }

    CONSOLIDATION_VERSION {
        uuid id PK
        uuid consolidation_id FK
        string version_no
        date consolidation_date
        string initiator
        string approved_in_rf
        string verified_in_go
        boolean kfo_confirmation_enabled
        boolean is_in_work
        datetime created_at
        string created_by
    }

    CONSOLIDATION_MEMBER {
        uuid id PK
        uuid consolidation_version_id FK
        uuid legal_entity_id FK
    }

    CORRECTION {
        uuid id PK
        uuid consolidation_version_id FK
        uuid correction_id FK "nullable; parent correction"
        uuid legal_entity_id FK "nullable"
        string debit_line_code
        string debit_account_code
        numeric debit_adjustment
        string credit_line_code
        string credit_account_code
        numeric credit_adjustment
        numeric deviation_amount
        numeric deviation_percent
        text comment
        string created_by
        datetime created_at
        string uploaded_by
        datetime uploaded_at
    }

    STORED_FILE {
        uuid id PK
        uuid legal_entity_id FK
        enum document_type "nullable"
        enum file_type "XLSX | XLS | PDF | PNG | JPG"
        string original_name
        string s3_key UK "nullable"
        string content_hash "nullable"
        bigint size_bytes
        smallint period_year
        enum period_quarter "nullable"
        enum status "nullable"
        uuid current_recognition_job_id FK "nullable"
        string uploaded_by "nullable"
        datetime uploaded_at "nullable"
    }

    RECOGNITION_JOB {
        uuid id PK
        uuid file_id FK
        uuid legal_entity_id FK
        enum pipeline "nullable"
        string service_version "nullable"
        enum status "nullable"
        smallint attempt
        string queue_msg_id "nullable"
        datetime requested_at "nullable"
        datetime started_at "nullable"
        datetime finished_at "nullable"
        text error_message "nullable"
        string raw_result_s3_key "nullable"
    }

    FINANCIAL_STATEMENT_META {
        uuid id PK
        uuid stored_file_id FK
        enum statement_type "BALANCE | INCOME"
        string inn
        boolean inn_verified
        date report_date "nullable"
        boolean report_date_verified
        string units
        boolean units_verified
    }

    FINANCIAL_STATEMENT_LINE {
        uuid id PK
        uuid recognition_job_id FK
        uuid meta_id FK "nullable"
        uuid legal_entity_id FK
        string line_code
        boolean line_code_recognized
        string line_name
        boolean line_name_recognized
        enum row_kind
        bigint ordinal
    }

    FINANCIAL_STATEMENT_VALUE {
        uuid id PK
        uuid line_id FK
        numeric amount "nullable"
        string string_value "nullable"
        smallint column_index
        boolean is_recognized
    }

    OSV_ENTRY {
        uuid id PK
        uuid recognition_job_id FK
        uuid legal_entity_id FK
        uuid stored_file_id FK
        string account_code
        uuid account_id FK
        string account_root
        string counterparty_name
        string counterparty_inn
        uuid counterparty_id FK "nullable"
        numeric opening_debit
        numeric opening_credit
        numeric turnover_debit
        numeric turnover_credit
        numeric closing_debit
        numeric closing_credit
    }

    CHART_OF_ACCOUNT o|--o{ CHART_OF_ACCOUNT : "parent_id"
    REGIONAL_OFFICE ||--o{ COMPANY_GROUP : "base office"
    COMPANY_GROUP ||--o{ COMPANY_GROUP_HISTORY : "history"
    COMPANY_GROUP ||--o{ LEGAL_ENTITY : "contains"
    REGIONAL_OFFICE ||--o{ LEGAL_ENTITY : "serves"
    LEGAL_ENTITY ||--o{ LEGAL_ENTITY_ALIAS : "aliases"
    LEGAL_ENTITY ||--o{ LEGAL_ENTITY_HISTORY : "history"
    COMPANY_GROUP ||--o{ LEGAL_ENTITY_HISTORY : "group snapshot"
    REGIONAL_OFFICE ||--o{ LEGAL_ENTITY_HISTORY : "office snapshot"

    COMPANY_GROUP ||--o{ RELATED_GROUP : "owns"
    RELATED_GROUP ||--o{ RELATED_GROUP_MEMBER : "memberships"
    LEGAL_ENTITY ||--o{ RELATED_GROUP_MEMBER : "participates"
    RELATED_GROUP ||--o{ CONSOLIDATION : "periods"
    CONSOLIDATION ||--o{ CONSOLIDATION_VERSION : "versions"
    CONSOLIDATION_VERSION ||--o{ CONSOLIDATION_MEMBER : "members"
    LEGAL_ENTITY ||--o{ CONSOLIDATION_MEMBER : "included"
    CONSOLIDATION_VERSION ||--o{ CORRECTION : "corrections"
    LEGAL_ENTITY o|--o{ CORRECTION : "entity correction"
    CORRECTION o|--o{ CORRECTION : "parent correction"

    LEGAL_ENTITY ||--o{ STORED_FILE : "files"
    STORED_FILE ||--o{ RECOGNITION_JOB : "recognition attempts"
    LEGAL_ENTITY ||--o{ RECOGNITION_JOB : "recognized for"
    RECOGNITION_JOB o|--o{ STORED_FILE : "current job"
    STORED_FILE ||--o{ FINANCIAL_STATEMENT_META : "metadata"
    FINANCIAL_STATEMENT_META o|--o{ FINANCIAL_STATEMENT_LINE : "lines"
    RECOGNITION_JOB ||--o{ FINANCIAL_STATEMENT_LINE : "produces"
    LEGAL_ENTITY ||--o{ FINANCIAL_STATEMENT_LINE : "owns"
    FINANCIAL_STATEMENT_LINE ||--o{ FINANCIAL_STATEMENT_VALUE : "values"

    RECOGNITION_JOB ||--o{ OSV_ENTRY : "produces"
    STORED_FILE ||--o{ OSV_ENTRY : "contains"
    LEGAL_ENTITY ||--o{ OSV_ENTRY : "owner"
    LEGAL_ENTITY o|--o{ OSV_ENTRY : "counterparty"
    CHART_OF_ACCOUNT ||--o{ OSV_ENTRY : "account"
```

## Важные ограничения

- `consolidation`: уникальна комбинация (`related_group_id`, `year`, `quarter`).
- `consolidation_version`: уникальна пара (`consolidation_id`, `version_no`).
- `consolidation_member`: уникальна пара (`consolidation_version_id`, `legal_entity_id`).
- `legal_entity_history`: уникальна пара (`legal_entity_id`, `valid_from`).
- `related_group_member`: действуют частичные уникальные индексы для текущих участников.
- `stored_file` и `recognition_job` связаны циклически: файл хранит текущую попытку, а каждая попытка ссылается на файл.

## Представление

`dkb.v_osv_entry_with_file_flags` объединяет `osv_entry`, `stored_file`, `recognition_job`, `legal_entity`, `related_group_member` и `related_group`. Оно добавляет вычисляемый тип контрагента: `ГСК`, `ГК` или `Внешний`.

## Источники

- `api/src/models/*.py`
- `api/alembic/versions/1c4e0a7d9612_first_migration.py`
- `api/alembic/versions/c89d082850bd_add_default_and_nullability.py`
- `api/scripts/create_osv_entry_view.sql`