# Database Map

Source inspected: `huquq_ishleri.sql` exported from phpMyAdmin on 2026-07-27.

The export contains structure only. No data rows were found during inspection.

## Tables

### Core Court Case Tables

`mehkeme_ishleri`

- Active court cases.
- Primary key: `id`
- Main fields: `is_nomresi`, `terefin_adi`, `mehkemenin_adi`, `hakimin_adi`, `fin`, `folder`, `is_statusu`, `mehkeme_tarixi`, `qeydler`, `url`
- Result fields: `netice_verildi`, `netice`, `yekun_akt_netice_kodlari`, `yekun_akt_ugur_kodu`, `ugur_gostericisi`, `mehkeme_akti`, `yekunlasma_tarixi`, `qanuni_quvve_alib`
- Representation fields: `vakil_ozu_temsil`, `vakil_order`, `vakil_order_no`, `vakil_order_tarix`, `etibarname`, `etibarname_bitis`, `etibarname_lazim_deyil`
- Financial fields: `muqavile_qiymeti`, `mukafat`, `mukafat_var`, `mukafat_novu`, `mukafat_serti`
- Other: `yazili_icraat`, `is_zencir_id`, `yaradilma_tarixi`

`mehkeme_is_status_tarixcesi`

- Stores every suspension and resumption of an active court case.
- Fields: `is_id`, `emeliyyat`, `evvelki_status`, `yeni_status`, `deyisme_tarixi`
- Allowed actions: `dayandirildi`, `tezelendi`
- Indexed by `is_id`, `deyisme_tarixi`

`mehkeme_arxiv`

- Archived/finalized court cases.
- Same core fields as `mehkeme_ishleri`.
- Adds `arxiv_tarixi`.
- Archiving keeps the active source row as a hidden backing record so related
  hearings, files, comments, and history retain their original case ID. Restore
  removes the archive overlay and returns the backing row to active proceedings.

`is_bagliliglar`

- Links related court cases.
- Fields: `ana_is_id`, `bagli_is_id`, `bagliliq_novu`, `izah`
- Unique key: `ana_is_id`, `bagli_is_id`, `bagliliq_novu`
- Allowed relation types: `netice_tesiri`, `akt_yoxlama`, `birlestirildi`, `diger_elaqe`

### Subscribers

`abuneciler`

- Physical and legal subscriber profiles.
- Residency: `rezident`, `qeyri_rezident`.
- Stores monthly subscription fee/currency, FIN/VÖEN, legal representative,
  contact details, and notes.

`abuneci_emekdaslari`

- Important employees attached to a subscriber.
- Stores name, position, phone, email, and a short note.

`abuneci_fayllar`

- Subscriber files with display name, stored name, MIME type, size, and upload
  timestamp.

### Hearings

`iclaslar`

- Court hearings.
- Primary key: `id`
- Fields: `is_id`, `tarix`, `saat`, `veziyyet`, `qeydler`, `yaradilma`, `gcal_event_id`, `istirak_edildi`, `iclas_neticesi`
- Allowed statuses: `quvvededir`, `legv_edildi`
- Allowed results: `gozlemede`, `istirak_edildi`, `istirak_edilmedi`, `kecirilmedi`
- Indexed by `is_id`

### Tasks

`tapshiriq_mehkeme`

- Tasks connected to court cases or standalone/project work.
- Primary key: `id`
- Fields: `is_id`, `arxiv_is_id`, `basliq`, `mezmun`, `prioritet`, `veziyyet`, `son_tarix`, `son_tarix_saat`, `mesul`, `qeydler`, `yaradilma`, `yenileme`, `gcal_event_id`, `tapshiriq_novu`, `tapshiriq_kateqoriya`, `layihe_id`
- Allowed statuses: `gozlemede`, `icrada`, `tamamlandi`, `legv_edildi`
- Foreign key: `is_id` -> `mehkeme_ishleri.id` with `ON DELETE CASCADE`

`layiheler`

- Projects for non-case or grouped work.
- Fields: `ad`, `yaranma_tarixi`, `bagli_shexs`, `qeyd`, `status`, `yekunlasma_tarixi`

`mesul_shexsler`

- Registry used by task forms for responsible-person selection.
- Fields: `ad`, `soyad`, `standartdir`, `yaradilma`
- The application keeps one record as the default responsible person.

### Enforcement Case Tables

`icra_ishleri`

- Active enforcement cases.
- Primary key: `id`
- Case link: `mehkeme_is_id`
- Document fields: `sened_nomresi`, `sened_tarixi`, `sened_orqan`
- Authority/officer fields: `icra_qurum_adi`, `icra_qurum_email`, `icra_memur_adi`, `icra_memur_tel`, `icra_memur_wp`, `icra_memur_komekci_wp`
- Debt fields: `pul_borcu`, `borc_meblegi`, `valyuta`, `borc_mahiyyeti`, `icra_edilmis`
- Debtor fields: `borclu_adi`, `borclu_fin`, `borclu_tel`
- Claimant fields: `telekkar_adi`, `telekkar_unvan`, `telekkar_tel`, `telekkar_bank`
- Other fields: `icra_baslama`, `girov_adi`, `hebs_emlak`, `status`, `qeydler`, `yaradilma`
- Allowed currencies: `AZN`, `USD`, `EUR`
- Allowed statuses: `icraatdadir`, `xitam`

`icra_arxiv`

- Archived enforcement cases.
- Similar to `icra_ishleri`.
- Adds `arxiv_tarixi`.

`icra_odenisler`

- Enforcement case payments.
- Fields: `icra_id`, `tarix`, `meblegi`, `valyuta`, `qeydler`, `yaradilma`

### Criminal Proceeding Tables

`cinayet_icraatlari`

- Holds both material/initial-inquiry records and opened criminal cases.
- Stores the material and criminal-case numbers, investigating authority,
  investigator/inquirer, separate material/case subjects, both start dates,
  lifecycle status, and notes.
- Allowed lifecycle states: initial inquiry, refusal to open, criminal case
  opened, proceeding suspended, terminated, and sent to court.

`cinayet_qerarlari`

- Chronological decisions for opening/refusing a criminal case, cancellation of
  refusal or termination decisions, judicial-control and appellate decisions,
  suspension/resumption, motions, investigator-act review, preventive-measure
  decisions, termination, and sending the case to court.
- `bagli_qerar_id` links a cancellation or appellate ruling to the earlier
  decision it reviews.

`cinayet_shexsleri` and `cinayet_shexs_status_tarixcesi`

- Store every person and their current and historical procedural statuses.
- Current statuses include applicant, investigated person, witness, suspect,
  accused, and no active procedural status.
- The application prevents accused status before a criminal case starts,
  prevents a second suspect period, and closes an open suspect period after 10
  days.
- Removing a person from the current list is non-destructive (`aktiv=0`) and
  preserves the complete status history.

`cinayet_temsil_olunan_shexsler`

- Repeatable persons whose rights are represented or defended.
- `merhele` keeps material-stage persons separate from criminal-case-stage
  persons, so conversion of a material into a criminal case preserves both.

`cinayet_qetimkan_tedbirleri`

- Preventive measures for accused persons: undertaking not to leave, police
  supervision, detention, and house arrest.

`cinayet_muddet_uzatmalari`

- Investigation-period and detention-period extensions, including previous and
  new end dates and the person concerned where applicable.

`cinayet_fayllari`

- PDF documents linked to a procedural decision, preventive measure, or period
  extension. Files are stored under `uploads/cinayet`.

All criminal-module parent relationships use foreign keys with cascading delete;
the self-reference between decisions uses `ON DELETE SET NULL`.

### Files

`mehkeme_fayllar`

- Files attached to active court cases.
- Foreign key: `is_id` -> `mehkeme_ishleri.id` with `ON DELETE CASCADE`

`tapshiriq_fayllar`

- Files attached to tasks.
- Task-file subject/description: `fayl_mahiyyeti`
- Foreign key: `tapshiriq_id` -> `tapshiriq_mehkeme.id` with `ON DELETE CASCADE`

`icra_fayllar`

- Files attached to active enforcement cases.
- Foreign key: `icra_id` -> `icra_ishleri.id` with `ON DELETE CASCADE`

Each file table contains:

- Original name: `fayl_adi`
- Stored path/name: `fayl_yolu`
- MIME/type: `fayl_novu`
- Size: `fayl_hecm`
- Upload timestamp: `yuklenme_tarixi`

### Comments

`kommentler`

- Small comments attached to tasks, court cases, or enforcement cases.
- Fields: `entity_tip`, `entity_id`, `metn`, `yaradilma`
- Allowed entity types: `tapshiriq`, `mehkeme_is`, `icra_is`
- Comment text is limited to 150 characters.

### Reference Tables

`erp_istifadeci_profili`

- Stores the single ERP profile shown under Settings.
- `login` and `password_sha256` override the local `.env` credentials when set.
- Empty credential fields preserve the existing `.env` login configuration.

`mehkemeler`

- Court directory.
- Fields: `kod`, `ad`, `unvan`, `telefon1`, `telefon2`, `email`, `qeydler`

`hakimler`

- Judge directory.
- Fields: `ad`, `mehkeme_kod`

`icra_shobeler`

- Enforcement office directory.
- Fields: `ad`, `unvan`, `telefon1`, `telefon2`, `email`, `qeydler`

`icra_memurlari`

- Enforcement officer directory.
- Fields: `ad`, `telefon`, `qurum`

## Important Rebuild Notes

- Only a few foreign keys are defined in the schema. Several relationships are used by the application but are not enforced by the database, such as:
  - `iclaslar.is_id`
  - `icra_ishleri.mehkeme_is_id`
  - `icra_odenisler.icra_id`
  - `is_bagliliglar.ana_is_id` and `bagli_is_id`
  - `tapshiriq_mehkeme.layihe_id`
- Archived records use separate archive tables instead of a single status column.
- Active case file attachments cascade on delete, but archive attachments are not represented in this schema.
- Task deletion cascades task files.
- Court case deletion cascades active tasks and active court files through foreign keys.
- The rebuild should keep status enums centralized in code so bot, API, and web UI cannot drift.