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.

-- 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

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).