# DTP Explain: `dtp_chebi`

## 1. Data source and pipeline role

- `data_source.name`: `chebi`
- `source_system`: `EBI`
- seed URL (in datasource): `https://ftp.ebi.ac.uk/pub/databases/chebi/flat_files/compounds.tsv.gz`
- format (seed): `tsv`

Pipeline role:
- ingests ChEBI compounds and complementary metadata
- creates/updates Chemical entities and aliases
- loads chemical master records into `ChemicalMaster`

Operational dependency:
- requires `EntityGroup` = `Chemicals`
- requires `OmicStatus` values: `active` and `deactive`

## 2. Extract

Source behavior:
- extraction does not download only the seed URL file
- it uses ChEBI flat-files base path:
  - `https://ftp.ebi.ac.uk/pub/databases/chebi/flat_files/`
- and downloads this set:
  - `compounds.tsv.gz`
  - `chemical_data.tsv.gz`
  - `secondary_ids.tsv.gz`
  - `database_accession.tsv.gz`
  - `source.tsv.gz`

Raw output path:
- `<raw_dir>/<source_system>/<data_source>/`

Hash behavior:
- computes `current_hash` from:
  - `compounds.tsv.gz`
- returns `(ok, message, current_hash)`

## 3. Transform

Input files:
- `compounds.tsv.gz`
- `chemical_data.tsv.gz`
- `secondary_ids.tsv.gz`
- `database_accession.tsv.gz`
- `source.tsv.gz`

Output files:
- `<processed_dir>/<source_system>/<data_source>/master_data.parquet`
- debug mode also writes:
  - `master_data.csv`

### 3.1 Main merge

Steps:
- reads `compounds.tsv.gz` and renames:
  - `chebi_accession` -> `chebi_id`
  - `name` -> `label`
- reads `chemical_data.tsv.gz`
- derives numeric `compound_id` from `chebi_id` (`CHEBI:xxxx` -> `xxxx`)
- left-joins compounds + chemical_data on `compound_id`

Status normalization:
- maps `status_id_x` to `omic_status`:
  - `1`, `3`, `9` -> `active`
  - `4` -> `deactive`
  - others/default -> `deactive`

### 3.2 Secondary IDs

Steps:
- reads `secondary_ids.tsv.gz`
- normalizes secondary IDs to `CHEBI:<id>`
- groups by `compound_id` into list
- merges into main dataframe as `secondary_ids`

### 3.3 External accessions as extra aliases

Steps:
- reads `database_accession.tsv.gz`
- reads `source.tsv.gz`
- joins both to map `source_id` -> source name/prefix
- creates alias payload per accession with:
  - `alias_value` = accession number
  - `alias_type` = `code`
  - `xref_source` = source name (fallback `Unknown`)
  - `alias_norm` = lowercase alias value
  - `is_primary` = `False`
- groups payloads per chemical (`CHEBI:<compound_id>`) as `aliases_extra`
- merges into main dataframe by `chebi_id`
- normalizes missing `aliases_extra` to `[]`

### 3.4 Final transformed schema

Selected columns saved in `master_data.parquet`:
- `chebi_id`
- `omic_status`
- `label`
- `definition`
- `source`
- `ascii_name`
- `status_id`
- `formula`
- `charge`
- `mass`
- `monoisotopic_mass`
- `structure_id`
- `is_autogenerated`
- `secondary_ids`
- `aliases_extra`

## 4. Load

Input file:
- `<processed_dir>/<source_system>/<data_source>/master_data.parquet`

Main goal:
- map each ChEBI record to an Entity in group `Chemicals`
- persist aliases in `EntityAlias`
- persist master row in `ChemicalMaster`

### 4.1 Alias schema and normalization

Base alias schema (`build_alias` input):
- `chebi_id` -> `(alias_type=code, xref_source=CheBI, is_primary=True)`
- `ascii_name` -> `(alias_type=label, xref_source=CheBI, is_primary=None)`
- `secondary_ids` -> `(alias_type=code, xref_source=CheBI, is_primary=None)`
- `formula` -> `(alias_type=formula, xref_source=CheBI, is_primary=None)`

Additional alias behavior:
- appends `aliases_extra` from transform
- removes aliases where `xref_source == "PubMed"`

Pre-load dataframe cleaning:
- fills nulls only for text columns (avoids pandas dtype warning)
- drops duplicate rows by `chebi_id` (keeps first)
- fills `definition` and `ascii_name` with `""`
- numeric normalization for:
  - `charge`, `mass`, `monoisotopic_mass`, `structure_id`
- boolean normalization for `is_autogenerated`

### 4.2 Entity and master persistence

Per-row flow:
1. build alias payloads
2. select primary alias (ChEBI code)
3. map active/deactive status from `status_id`:
   - `4` -> deactive
   - other values -> active
4. `get_or_create_entity(...)` with primary alias
5. `get_or_create_entity_name(...)` for non-primary aliases
6. insert `ChemicalMaster` if `chebi_id` is not already present for this data source
7. commit transaction per row

Index/DB mode handling:
- switches DB to write mode before load
- drops chemical/entity indexes before row loop
- recreates indexes and switches DB back to read mode at end

## 5. Filters and guards

Guards:
- requires `processed_dir`
- requires `master_data.parquet`
- aborts if dataframe is empty
- aborts if `EntityGroup(Chemicals)` is missing
- aborts if required `OmicStatus` values are missing

Row-level exclusions:
- rows without `chebi_id` are skipped
- aliases with `xref_source = PubMed` are excluded
- duplicate master rows are prevented by:
  - dataframe de-dup by `chebi_id`
  - in-session `existing_chemical_ids` check

## 6. Practical caveats

- Extract uses a hardcoded ChEBI flat-files base URL and file list, not only `data_source.source_url`.
- Load is incremental for `ChemicalMaster` (no full replace); existing `chebi_id` for same data source are not reinserted.
- `omic_status` column is created during transform but current load decides active/deactive from numeric `status_id`.
- Commits happen per row, which is safe for partial failures but can increase total load time on large volumes.
