PriceForge/bible-local/pricelist.md

# Pricelists

## Types

PriceForge supports four pricelist types (`source` field):

| Type | Data source | supplier_type | Status |
|------|-------------|---------------|--------|
| `estimate` | `parts_log` where `supplier_type='trader'` | trader | Active |
| `warehouse` | `parts_log` where `supplier_type='self'` | self | Active |
| `competitor` | `parts_log` where `supplier_type='competitor'` | competitor | Active |
| `world` | `parts_log` — all supplier types | trader + self + competitor | Active |

All pricelist types now read from the unified `parts_log` table. Legacy tables (`stock_log`, `partnumber_log_competitors`, `lot_log`) are read-only archives.

---

## Estimate

**Source**: `parts_log` rows where `supplier.supplier_type = 'trader'`.

**Settings per lot** (from `qt_lot_metadata WHERE pricelist_type='estimate'`):
- `price_period_days` — quote window in days (default 90)
- `price_method` — calculation method (median, avg, weighted_median, etc.)
- `price_coefficient` — markup coefficient
- `manual_price` — manually set price
- `meta_prices` — additional lot sources (wildcards supported)
- `on_missing_quotes` — `'drop'` (exclude lot) or `'keep'` (last known price)

**Purpose**: primary pricelist for calculations and estimates.

**Categories**: loaded from `lot.lot_category` for each component.

**Note**: All `qt_lot_metadata` queries MUST filter `WHERE pricelist_type = 'estimate'` since the table now has a composite PK `(lot_name, pricelist_type)`.

---

## Warehouse

**Source**: `parts_log` rows where `supplier.supplier_type = 'self'`.

**Rules (CRITICAL)**:

1. **Only mapped lots** — only positions with a non-NULL `lot_name` in `parts_log` are included.
2. **Settings from qt_lot_metadata**: `pricelist_type='warehouse'` row per lot.
3. **Price method**: `weighted_avg` (quantity-weighted average) by default.
4. `qty NULL` in `parts_log` → weight = 1 for averaging.

**Categories**: loaded from `lot.lot_category`.

**lead_time_weeks**: stored in `qt_pricelist_items.lead_time_weeks`; 99 if no data.

**Implementation**: `internal/warehouse/snapshot.go`

---

## Competitor

**Source**: `parts_log` rows where `supplier.supplier_type = 'competitor'`.

**Rules (CRITICAL)**:

1. **Only mapped lots** — only `parts_log` rows with non-NULL `lot_name` are included.
2. **Settings from qt_lot_metadata**: `pricelist_type='competitor'` row per lot.
3. **Price method**: `weighted_median` (quantity-weighted median). `qty NULL` → weight = 1.
4. **Uplift applied at import**: `effective_price = raw_price / supplier.price_uplift`. Prices in `parts_log` are already in USD.
5. **Deduplication**: INSERT IGNORE dedup on `(supplier_code, partnumber, quote_date)`.
6. **period_days**: default 90 days.

**Key tables**:
- `supplier` — supplier profiles with `supplier_type='competitor'`, `price_uplift DECIMAL(8,4) DEFAULT 1.3`
- `parts_log` — unified quote journal (all supplier types)
- `qt_competitors` — legacy competitor profiles (still exists, being phased out)
- `partnumber_log_competitors` — legacy quote log (read-only archive)

**Implementation**: `internal/services/competitor_import.go`, `internal/services/pricelist/service.go`

---

## World

**Source**: `parts_log` — all supplier types (trader + self + competitor).

**Purpose**: unified market price view across all sources.

**Rules**:

1. **All supplier types** included.
2. **Settings from qt_lot_metadata**: `pricelist_type='world'` row per lot.
3. **Price method**: `weighted_median` by default. `qty NULL` → weight = 1.
4. **period_days**: default 90 days.
5. **on_missing_quotes**: `'drop'` by default (exclude lot); `'keep'` uses last known price from `parts_log`.
6. **lead_time_weeks**: weighted median of non-NULL lead times; 99 if no data.

**Implementation**: `internal/services/pricelist/world.go`

---

## qt_lot_metadata PK change

`qt_lot_metadata` now has composite PK `(lot_name, pricelist_type)`. Each lot has up to 4 rows — one per pricelist type. **All queries reading `qt_lot_metadata` MUST include `WHERE pricelist_type = '...'`** to avoid returning multiple rows.

Default settings for new lots are copied from the `__default__` row for each pricelist_type:

| pricelist_type | price_method | period_days | on_missing_quotes |
|----------------|-------------|-------------|-------------------|
| estimate | median | 90 | drop |
| warehouse | weighted_avg | 7 | drop |
| competitor | weighted_median | 90 | drop |
| world | weighted_median | 90 | drop |

---

## parts_log — Unified Quote Journal

All new quotes are written to `parts_log`. Legacy tables remain as read-only archives.

```sql
-- Key columns
supplier_code  VARCHAR(100)  -- FK → supplier.supplier_code
partnumber     VARCHAR(255)  -- raw p/n from source file
lot_name       VARCHAR(255)  -- NULL until backfill resolves it
price          DECIMAL(12,4) -- always USD (converted at import)
qty            DECIMAL(12,4) -- NULL means weight=1 in median calc
offer_type     ENUM('private','public')
lead_time_weeks INT          -- NULL = not specified (≠ 0)
quote_date     DATE
```

**Dedup index**: `UNIQUE (supplier_code, partnumber(191), quote_date)` → INSERT IGNORE.

**Append-only**: the only permitted mutation after insert is setting `lot_name` / `lot_category` by the backfill job.

**Backfill** (`internal/services/parts_log_backfill.go`): daily cron resolves `lot_name=NULL` rows via direct lot_name match or `qt_partnumber_book_items` mapping.

---

## Data Model

```go
type Pricelist struct {
    ID      uint   `gorm:"primaryKey"`
    Version string
    Source  string // "estimate" | "warehouse" | "competitor" | "world"
    // ...
}

type PricelistItem struct {
    ID             uint    `gorm:"primaryKey"`
    PricelistID    uint
    LotName        string  `gorm:"size:255"`
    Price          float64 `gorm:"type:decimal(12,2)"`
    LotCategory    *string `gorm:"column:lot_category;size:50" json:"category,omitempty"`
    LeadTimeWeeks  *int    `gorm:"column:lead_time_weeks" json:"lead_time_weeks,omitempty"`

    // Virtual fields (via JOIN or programmatically)
    LotDescription  string             `gorm:"-:migration" json:"lot_description,omitempty"`
    AvailableQty    *float64           `gorm:"-" json:"available_qty,omitempty"`
    Partnumbers     []string           `gorm:"-" json:"partnumbers,omitempty"`

    // Enriched per-source virtual fields (warehouse / competitor only)
    CompetitorNames []string           `gorm:"-" json:"competitor_names,omitempty"`
    PriceSpreadPct  *float64           `gorm:"-" json:"price_spread_pct,omitempty"`
    PartnumberQtys  map[string]float64 `gorm:"-" json:"partnumber_qtys,omitempty"`
}
```

`lead_time_weeks` is nullable. Value `99` means "no data". Old clients that don't read it are unaffected.

> `gorm:"-:migration"` — no DB column created, but mapped on SELECT.
> `gorm:"-"` — fully ignored in all DB operations.

---

## Categories (lot_category)

- **Source**: `lot.lot_category` column in table `lot`.
- **NOT** from `qt_lot_metadata`.
- **NOT** derived from LOT name.
- Persisted into `qt_pricelist_items.lot_category` when pricelist is created.
- JSON field name: `"category"`.
- JOIN with `lot` is only needed for `lot_description`; category is already in `qt_pricelist_items`.
- Default value when category is missing: `PART_`.

---

## Pricelist Deletion Guard

`CountUsage(id)` checks `qt_configurations` for references across **all four** pricelist columns:
`pricelist_id`, `warehouse_pricelist_id`, `competitor_pricelist_id`, `world_pricelist_id`.

A pricelist is only deletable when all counts are zero.

---

## Pricelist Creation (background task)

Creation runs via Task Manager. Handler returns `task_id`; frontend polls.

```
POST /api/pricelists/create
→ { "task_id": "uuid" }
→ polling GET /api/tasks/:id
→ { "status": "completed", "result": { "pricelist_id": 42 } }
```

Task type: `TaskTypePricelistCreate`.

**Implementation**:
- Service: `internal/services/pricelist/service.go`
- Warehouse calc: `internal/warehouse/snapshot.go`
- Handler: `internal/handlers/pricelist.go`

---

## Pricelist Deletion Guard

`CountUsage(id)` checks `qt_configurations` for references across **all three** pricelist columns:
`pricelist_id`, `warehouse_pricelist_id`, `competitor_pricelist_id`.

A pricelist is only deletable when all counts are zero.

---

## Pricelist Detail Page — Display Layouts

Page: `/pricelists/:id` — `web/templates/pricelist_detail.html` + `web/static/js/pricelist_detail.js`

Two display layouts exist based on `source`:

### Estimate layout (`source = "estimate"`)

Columns: Артикул | Категория | Описание | Цена, $ | Настройки

- Description truncated to 60 chars.
- "Настройки" column shows per-row price adjustment controls.
- No partnumber or supplier column.

### Stock layout (`source = "warehouse"` or `"competitor"`)

Columns: Артикул | Категория | Описание | Partnumbers | Поставщик | Цена, $

- "Настройки" column is hidden.
- "Доступно" (available qty) column is hidden.
- Артикул column has word-break enabled (long lot names wrap).
- Description truncated to 30 chars.

**Partnumbers column**: shows only PNs with `partnumber_qtys[pn] > 0`, formatted as `PN (qty шт.)`. Up to 4 shown, then `+N ещё`.

**Поставщик column**:
- Warehouse: grey text "склад".
- Competitor: blue badge(s) with competitor name(s) from `competitor_names`.

**Spread badge** (competitor only): `±N%` in amber next to the price, computed from `price_spread_pct`.

### Per-source enrichment (`internal/repository/pricelist.go`)

- `enrichWarehouseItems`: calls `warehouse.LoadLotMetrics` → fills `Partnumbers` and `PartnumberQtys`.
- `enrichCompetitorItems`: loads latest competitor quotes (qty > 0 only) → resolves to lots via `qt_partnumber_book_items` → fills `CompetitorNames`, `Partnumbers`, `PartnumberQtys`, `PriceSpreadPct`.

`LoadLotMetrics` signature: `(db, lotNames, latestOnly) → (qtyByLot, partnumbersByLot, pnQtysByLot, error)`.