# PCM Database — Numeric Data Profile

Every number below is the result of an actual read-only query against `pcm` @ 192.168.200.4:3307 (user `developer`, MariaDB).
Connection pattern: `PDO("mysql:host=192.168.200.4;port=3307;dbname=pcm", "developer", "DEVEL!now1")` via `/home/mati/.config/herd-lite/bin/php -r '...'`.
No writes performed.

---

## 1. Per-table profile

### api_version — **1 row** (role: cache/version marker)
- Single row: `id=1`, `version_json={"version": "1.11.0"}` (query: `SELECT * FROM api_version`).
- No timestamps, no other columns.

### brands — **9 rows** (role: lookup)
- `SELECT * FROM brands ORDER BY code`: `abarth, fiat, fiat_pro, hyundai, jeep, kgm, maxus, mg, nissan`.
- Referenced by: models.brand_code (75), structure_categories.brand_code (183), structure_elements.brand_code (5154).

### markets — **1 row** (role: lookup)
- `SELECT * FROM markets`: `CH` (only value).

### models — **75 rows**
- Distinct `market_code`: `CH` (75). Distinct `brand_code`: fiat 14, hyundai 14, mg 11, nissan 11, kgm 8, maxus 7, abarth 4, fiat_pro 4, jeep 2.
- Status: Current 54, Draft 13, Past 4, Archived 4.
- Distinct `model_key`: 75 (UK `(market_code, brand_code, model_key)` — all unique). Samples: `312, 332, 365, 319, 325-313, 332-302, 332A, DOBLO, MGS6`.
- name_json language coverage: de-CH 67/75, fr-CH 65/75, it-CH 65/75.
- Nulls: 0 for all columns (market_code, brand_code, model_key, status, description, name_json).
- Sample rows:
  - `id=1, market=CH, brand=abarth, model_key=332, status=Current, description="500e", name_json={"de-CH":"500e"}`
  - `id=3, market=CH, brand=abarth, model_key=312, status=Current, description="AUSLAUFMODELLE 595_695", name_json={"it-CH":"595_695","de-CH":"AUSLAUFMODELLE 595_695","fr-CH":"595_695"}`

### model_years — **145 rows**
- `year_key` distinct values (14): `2022`(2), `2023`(3), `2024`(6), `2025`(57), `2025_26`(1), `2025DemoRalf`(1), `2026`(42), `2026.5`(2), `2027`(23), `26`(1), `27`(2), `DOBLO`(1), `MGS6`(1), `MY26`(3). → **not clean years**.
- Status: Current 105, Draft 23, Past 17.
- All 75 models have ≥1 year (0 models without years).
- Nulls: 0 everywhere.
- Sample: `id=1, model_id=3, year_key=2025, status=Current, description="2025", name_json={"fr-CH":"2025","it-CH":"2025","de-CH":"2025"}`.

### body_types — **184 rows**
- Status: Current 147, Draft 26, Past 11.
- `flag`: None 183, New 1. `vehicle_group`: PV 184 (single value). `test_drive`: 0 → 133, 1 → 51.
- `salesforce_id_json`: 59 non-null (67.9% null); **19 distinct `$.prod` ids** (shared SF ids); sample `{"test":"a1F7S000000qlByUAI","prod":"a1F7S000000qlByUAI"}`.
- `range_json`: 184 non-null; samples `[]` (most) and `["suv"]` (body_types 36–38).
- Per-brand body_types: hyundai 59, nissan 33, fiat 24, kgm 22, mg 17, maxus 10, jeep 8, abarth 7, fiat_pro 4.
- Body types with 0 engines: 12; 0 trims: 11; 0 variants: 14; 0 section elements: 13.
- Nulls: salesforce_id_json 125/184; all other columns 0.
- Sample: `id=1, model_year_id=2, body_key="312_695_75", status=Current, description="695 75 Anniversario", test_drive=0, flag="None", range_json="[]", vehicle_group="PV"`.

### engines — **479 rows**
- Status: Current 447, Past 21, Draft 7, Archived 4.
- Per body_type: avg 2.8, min 1, max 15. `engine_key` samples: `1.4_ICE_180_Manual`, `PEM`, `BEV_155` (distinct: 479).
- `description`: 226 empty / 253 non-empty. `engine_name_translation_json`: 302 non-`{}` / 177 `{}`.
- Engines with 0 entity_features: 44; 0 entity_data_values: 18.
- Nulls: 0 for all columns.
- Sample: `id=1, body_type_id=1, engine_key="1.4_ICE_180_Manual", status=Current, engine_name="1.4 T-Jet (180 PS) Manual", description="", translation={}`.

### trims — **597 rows**
- Status: Current 554, Draft 36, Past 6, Archived 1.
- Per body_type: avg 3.45, min 1, max 18.
- `description`: 323 empty / 274 non-empty. trim_key samples: `Anniversario`, `595_Grand_Prix_Edition`, `EH0`.
- Trims with 0 entity_features: 19; 0 entity_data_values: 304 (51%).
- entity_features per trim: min 1, max 310, avg 119.5. entity_data_values per trim: min 1, max 19, avg 3.7.
- Nulls: 0 for all columns.

### variants — **1080 rows**
- Status: Current 1037, Past 22, Draft 16, Archived 5.
- Per body_type: avg 6.4, min 1, max 28.
- `price`: min 0.00 (11 rows), max 99999.00 (2 rows — sentinel), avg 43070.16, 412 distinct values.
- `name`: 1008/1080 follow `trim_key X engine_key` (e.g. `Anniversario X 1.4_ICE_180_Manual`); **72 deviations** with real names (e.g. `500e`, `500e Cabrio`, `500e Turismo` for trim EH0/EC0/EHT/ECT).
- `variant_disclaimer_json`: 48/1080 non-empty (locale map).
- Variants with entity_features: 73/1080; with entity_data_values: 85/1080.
- Cross-consistency: 0 variants where trim/engine body_type ≠ variant body_type.
- Nulls: 0 for all columns.
- Sample: `id=1, body_type_id=1, trim_id=1, engine_id=1, status=Current, name="Anniversario X 1.4_ICE_180_Manual", price=36990.00, disclaimers={}`.

### body_type_category_sections — **2576 rows** (184 body_types × 14 sections)
- 14 distinct `section_key`, each exactly 184×: `exterior_colors, interior, interior_trim, pack, performance, safety_security, tires, weights_volumes, battery, brakes, consumption_range, dimensions, engine, exterior`.
- `flag`: ENGINE 1472 (8 keys × 184), TRIM 1104 (6 keys × 184).
- `elements_json`: 624 sections `[]`; 1952 sections have ≥1 element; 0 nulls.
- display_name/description identical boilerplate per section_key across all body_types.
- Sample: `body_type_id=1, section_key="engine", display_name="Engines & transmissions", description="Engines & transmissions Description", flag="ENGINE"`.

### body_type_section_elements — **23345 rows**
- `element_type`: feature 16661 (71.4%), data 6684 (28.6%).
- `unit`: null 16661 (100% of features), non-null 6684 (all data rows). `sap_code`: 3735 non-null (22.4% of features), 19610 null (84.0% overall).
- Distinct `code`: 4378; distinct (element_type, code): 4384.
- Per section: avg 12.0, min 1, max 89.
- Nulls: unit 16661, sap_code 19610, all others 0.
- Sample elements_json: `[{"type":"data","description":"Anzahl Gänge","code":"gears_no_free","unit":"Freetext"}, {"type":"data","code":"bore","unit":"Millimeter"}, {"type":"feature","code":"1_4_16_v_t_jet","sap_code":null,...}]`.

### body_type_rules — **184 rows** (1:1 with body_types, PK body_type_id)
- 182 rows `{"includes": {}}`; 2 non-empty:
  - bt 9: `{"includes": {"cross_look": ["cross_look_underbody_protection","cross_look_daytime_running_light"]}}`
  - bt 10/11: `{"includes": {"STYLE": [...], "TECH": [...]}}` (pack → feature list).
- Nulls: 0.

### body_type_marketing_mapping — **184 rows** (1:1, PK body_type_id)
- All rows `{"features": {}, "data": {}}` — JSON_LENGTH(0) on both keys for all 184 (query: `WHERE JSON_LENGTH(JSON_EXTRACT(mapping_json,"$.features"))>0 OR ...` → 0 rows).

### body_type_feature_includes — **666 rows**
- Distinct `parent_feature_code`: 19; distinct `included_feature_code`: 121.
- 8 parent codes and 22 included codes absent from section element codes (pack names like `STYLE`, `TECH`, `cross_look` are rule-labels, not feature codes).
- Sample: `(body_type_id=9, parent=cross_look, included=cross_look_underbody_protection)`, `(10, STYLE, 16_black_alloy_wheels)`.
- Nulls: 0.

### structure_categories — **183 rows**
- Per brand: fiat_pro 26, nissan 22, mg 20, abarth 20, fiat 20, hyundai 19, jeep 19, kgm 19, maxus 18.
- Distinct `code`: 32 (codes reused across brands; e.g. `safety_security`, `comfort`, `driver_assistance`, `OPTIONALS_AB_HIER`).
- `category_disclaimer_json`: 38 non-null (20.8%), 145 null (79.2%). Sample disclaimer (abarth, fuel_consumption_and_range): CO₂ WLTP text in 3 locales.
- Nulls: category_disclaimer_json 145; others 0.

### structure_elements — **5154 rows**
- `element_type`: Feature 4065, Data 1085, SubCategory 4.
- Distinct `code`: 4662 (per-brand duplicates across 9 brands: e.g. `JAL`, `018`).
- Per brand: nissan 1175, hyundai 909, fiat 785, mg 656, jeep 352, kgm 342, abarth 333, maxus 304, fiat_pro 298.
- name_json language coverage: de 5151, fr 5137, it 5135 of 5154.
- Sample: `(hyundai, SubCategory, hyundai_smart_sense)`, `(abarth, Feature, 018, "Verchromte Zierleisten")`.
- Nulls: 0.

### structure_category_items — **5129 rows**
- `item_type`: Feature 4070, Data 1056, SubCategory 3.
- Distinct `element_code`: 4637; **only 2 codes not present in structure_elements.code** (near-complete internal integrity).
- Per category: avg 30.2, min 1, max 196.
- Sample: `(category_id=5, element_code=JAL, item_type=Feature, sort_order=0, name="Display TFT a colori 7'' ...")`.
- Nulls: 0.

### entity_features — **73370 rows** (largest table, 15.55 MB)
- By entity_type: trim 69098, engine 4053, variant 219.
- `availability`: Standard 50939 (69.4%), NotAvailable 17059 (23.3%), Optional 5244 (7.1%), NULL 128 (0.2%).
- Per type × availability: trim: Standard 47821 / NotAvailable 15975 / Optional 5177 / NULL 125; engine: Standard 3068 / NotAvailable 984 / NULL 1; variant: NotAvailable 100 / Optional 67 / Standard 50 / NULL 2.
- `surcharge`: non-null 5244 (== Optional count, all Optionals carry surcharge); min 0.00, max 99999999.00 (sentinel), avg 39105.72, 48 distinct values.
- `raw_value_json`: non-null 5372 (5244 × `{"Optional":{"Surcharge":N}}` + 128 × `{"Optional":"Free"}`). **All 128 "Free" rows have availability=NULL** (anomaly). Only 1 engine row has raw_value (`{"Optional":"Free"}`, entity 48, feature battery_weight).
- Distinct `feature_code`: 4194; 621 codes never in section elements; 680 never in structure_elements; **14948/69098 trim rows (21.6%) reference codes absent from their own body_type's section catalog**.
- Top data-ish feature codes (by row count): rain_sensor 376, high_beam_assistant 308, heated_steering_wheel 297, traffic_sign_recognition 295, ecall 294, rear_view_mirror_auto_dim 292, lane_keeping_assist 271, front_seat_heating 266.
- Nulls: availability 128 (0.17%), surcharge 68126 (92.8%), raw_value_json 67998 (92.7%).
- Sample: `(trim, 2, 0SD_883_B, Optional, 1300.00, {"Optional":{"Surcharge":1300.0}})`, `(variant, 173, pack_cargo_l4_C7F, NotAvailable, NULL, NULL)`.

### entity_data_values — **19950 rows**
- By entity_type: engine 18536 (92.9%), trim 1089 (5.5%), variant 325 (1.6%).
- Distinct `data_code`: 919; distinct (data_code, unit): 996; 27 distinct units.
- Top units: Millimeter 4963, Kilogramm 2709, Freetext 2539, Units 1422, Liter 1007, CO2Emissions 985, Kilowatt 876, HorsePower 657, Torque 613, FuelConsumption 491, KilometerPerHour 470, Meter 450, RevolutionsPerMinute 428, Second 426, EnergyEfficiency 398.
- Top data_codes: length 458, height 450, max_speed 427, wheelbase 352, turning_circle 331, width 326, energy_efficiency 287, acceleration 272, emission_co2_fuel_supply 263, displacement 260, roof_load 260, emission_co2_combined 251.
- Multi-unit groups: 292 `(entity_type, entity_id, data_code)` groups with >1 unit (max 4) — e.g. `battery_capacity` in `KilowattHours` AND `Consumption` (both 42.2, engine 8) — **unit misuse anomaly**. Also engine 1 `fuel_consumption_combined` unit=`Millimeter` (should be FuelConsumption).
- 137 distinct data_codes absent from section `data` elements dictionary.
- Nulls: **0 for all columns** (value never NULL).
- Samples: `(trim, 9, tire_size, TireDescription, "205/45 R17")`, `(engine, 1, acceleration_100, Second, "6.7")`, `(variant, 18, electric_range_combined, Units, "265")`.

### entity_feature_disclaimers — **48 rows**
- entity_type: trim 46 (23 distinct trims), variant 2 (2 distinct variants).
- Distinct feature_code: 46. All 4 columns 0 nulls.
- Sample: `(trim, 67, Pack_Magic_Top, {"it-CH":"Disponibile solo per la versione a 5 posti","de-CH":"Nur verfügbar für die Version mit 5 Sitzen","fr-CH":"Disponible uniquement pour la version 5 places"})`, `(trim, 108, front_bumper_painted_MBP, {"de-CH":"AYQ"})`.

---

## 2. Referential integrity (orphan counts — all `LEFT JOIN ... WHERE target IS NULL` queries)

All orphan counts = **0**:

| FK pair | orphans |
|---|---|
| entity_data_values.entity_id → engines / trims / variants (per entity_type) | 0 / 0 / 0 |
| entity_features.entity_id → engines / trims / variants | 0 / 0 / 0 |
| entity_feature_disclaimers.entity_id → engines / trims / variants | 0 / 0 / 0 |
| body_types.model_year_id → model_years | 0 |
| model_years.model_id → models | 0 |
| models.brand_code → brands, market_code → markets | 0 / 0 |
| engines.body_type_id, trims.body_type_id, variants.body_type_id | 0 / 0 / 0 |
| variants.trim_id → trims, engine_id → engines | 0 / 0 |
| variants trim↔body mismatch, engine↔body mismatch | 0 / 0 |
| body_type_section_elements.section_id → body_type_category_sections | 0 |
| structure_category_items.category_id → structure_categories | 0 |
| body_type_feature_includes.body_type_id → body_types | 0 |
| Unknown entity_type values in polymorphic tables | 0 |

Duplicate audit: unique keys on all tables preclude exact duplicates; the only "duplicate-like" pattern is multi-unit data_codes (292 groups, 996 unique (code,unit) pairs — intentional, unit is part of the key).

---

## 3. Key queries executed (patterns)

- `SHOW TABLES`; `SHOW CREATE TABLE \`<t>\`` for all 21 tables (full DDL in pcm-analysis.md §1 notes).
- `SELECT COUNT(*) FROM \`<t>\`` per table.
- `SELECT COALESCE(<enumcol>,"(NULL)") v, COUNT(*) n FROM \`<t>\` GROUP BY <enumcol> ORDER BY n DESC` for every enum-like field.
- `SELECT * FROM \`<t>\` LIMIT n` for samples (10–20 rows where feasible).
- Orphan queries: `SELECT COUNT(*) FROM <child> LEFT JOIN <parent> ON ... WHERE <parent>.id IS NULL`.
- JSON parsing done in PHP (`json_decode`) because MariaDB lacks JSON_TABLE; key-frequency aggregated over full column scans.
- `SELECT JSON_LENGTH(JSON_EXTRACT(mapping_json,"$.features"))` to prove all-empty mapping payloads.
- `SELECT ROUND((data_length+index_length)/1024/1024,2) FROM information_schema.tables WHERE table_schema='pcm'` for sizes.
- Coverage: `SELECT COUNT(*) FROM entity_features f JOIN trims t ON t.id=f.entity_id AND f.entity_type='trim' JOIN body_types b ON b.id=t.body_type_id WHERE NOT EXISTS (SELECT 1 FROM body_type_category_sections s JOIN body_type_section_elements e ON e.section_id=s.id WHERE s.body_type_id=b.id AND e.code=f.feature_code)` → 14948.

---

## 4. Summary of anomalies worth carrying into product_db design

1. `entity_data_values` unit free-text (27 units, 2 misuse cases proven) → needs a units lookup + validation.
2. 128 `availability=NULL` rows that are semantically "Optional (Free)" (raw_value_json proof).
3. Surcharge sentinel `99999999.00` (2 rows); price sentinel `99999.00`.
4. Salesforce ids not 1:1 (19 ids / 59 body types).
5. 21.6% of trim feature rows reference codes outside the body-type section dictionary.
6. 93% of variants carry no entity_features (mostly engine-only data? UNKNOWN).
7. No audit columns in any table.