Входящие цены — Установка себестоимости

base_price → применить скидку → cost_price

Товар: Гордеев MC150 (батарея салютов)
base_price = 2000 ₽ (прайс производителя)
brand_discount = 20% (скидка Гордеев)
→ cost_price = 2000 × (1 - 0.20) = 1600 ₽

fixed_price → cost_price (без изменений)

Товар: Ольгино CC2600 (фонтан)
fixed_price = 850 ₽ (цена от Ольгино)
→ cost_price = 850 ₽ (напрямую)

cost_price = CASE
    WHEN base_price IS NOT NULL THEN
        base_price * (1 - COALESCE(brand_discount, 0) / 100)
    WHEN fixed_price IS NOT NULL THEN
        fixed_price
    ELSE
        NULL  -- Ошибка: нет входящей цены
END

{
  "Номенклатура": [
    {
      "Ref_Key": "uuid",
      "Description": "Название товара",
      "Артикул": "JF-DM30",
      "Цена": 1000.00,
      "БрендНоменклатуры_Key": "uuid-jf-pyro"
    }
  ]
}

python -m pim.import_from_1c

{
    "Артикул": "article",
    "Цена": "base_price",
    "БрендНоменклатуры_Key": "brand_id"
}

SELECT discount_percent
FROM price_cost_rules
WHERE brand = (SELECT name FROM brands WHERE id = brand_id);

cost_price = base_price * (1 - brand_discount / 100)

INSERT INTO pim_products (article, base_price, cost_price, brand)
VALUES (?, ?, ?, ?)
ON CONFLICT (article) DO UPDATE
SET base_price = EXCLUDED.base_price,
    cost_price = EXCLUDED.cost_price;

INSERT INTO price_history (article, price_type, old_value, new_value, changed_by)
SELECT
    NEW.article,
    'base_price',
    OLD.base_price,
    NEW.base_price,
    'import_1c'
FROM pim_products
WHERE article = NEW.article;

0 6 * * * cd /opt/claude-workspace/projects/org/pirotehnika/app/pim && python -m pim.import_from_1c

Артикул       | Название                  | Цена  | Остаток
--------------+---------------------------+-------+--------
CC2600        | Фонтан "Золотой дождь"    | 850   | 45
VH100         | Вертушка                   | 320   | 120

curl -X POST http://docs.0kt.ru:8000/prices/upload \
  -F "file=@price_olgino.xlsx" \
  -F "supplier=olgino"

python -m pim.import_price /path/to/price_olgino.xlsx --supplier olgino

INSERT INTO pim_price_file_uploads (filename, supplier, uploaded_by, status)
VALUES ('price_olgino.xlsx', 'olgino', 'admin', 'pending');

INSERT INTO pim_staging_products (upload_id, article, price, ...)
SELECT upload_id, Артикул, Цена, ...
FROM excel_data;

# Проверки:
- Артикул не пустой
- Цена > 0
- Артикул существует в pim_products (или создать новый)

# Статус:
'pending' → 'validated' или 'failed'

UPDATE pim_products p
SET
    fixed_price = s.price,
    cost_price = s.price,  -- для fixed_price без скидки
    updated_at = NOW()
FROM pim_staging_products s
WHERE p.article = s.article
  AND s.upload_id = ?
  AND s.status = 'validated';

pending → processing → validated → imported
                      ↓
                    failed

CREATE TABLE price_cost_rules (
    id SERIAL PRIMARY KEY,
    brand VARCHAR(100),
    discount_percent NUMERIC(5,2),  -- 0.00 - 100.00
    valid_from DATE,
    valid_to DATE,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO price_cost_rules (brand, discount_percent) VALUES
('JF Pyro', 25.00),
('Гордеев', 20.00),
('Maxsem', 22.00),
('КТС Пиро', 18.00);

def calculate_cost_price(base_price: float, brand: str) -> float:
    discount = db.query(
        "SELECT discount_percent FROM price_cost_rules WHERE brand = ?",
        brand
    ).scalar() or 0

    return base_price * (1 - discount / 100)

CREATE TABLE price_supplier_cost (
    id SERIAL PRIMARY KEY,
    supplier VARCHAR(100),
    article VARCHAR(100),
    real_cost_price NUMERIC(10,2),
    quantity_available INTEGER,
    updated_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO price_supplier_cost (supplier, article, real_cost_price, quantity_available)
SELECT
    'olgino',
    s.article,
    s.price,
    s.stock
FROM pim_staging_products s
WHERE s.upload_id = ?
ON CONFLICT (supplier, article) DO UPDATE
SET real_cost_price = EXCLUDED.real_cost_price,
    quantity_available = EXCLUDED.quantity_available,
    updated_at = NOW();

-- У кого дешевле CC2600?
SELECT supplier, real_cost_price, quantity_available
FROM price_supplier_cost
WHERE article = 'CC2600'
ORDER BY real_cost_price ASC;

supplier      | real_cost_price | quantity_available
--------------+-----------------+-------------------
olgino        | 850.00          | 45
pirosnab      | 920.00          | 18

ALTER TABLE pim_products
ADD CONSTRAINT check_price_exists
CHECK (base_price IS NOT NULL OR fixed_price IS NOT NULL);

ALTER TABLE pim_products
ADD CONSTRAINT check_base_price_positive
CHECK (base_price IS NULL OR base_price >= 0);

ALTER TABLE pim_products
ADD CONSTRAINT check_fixed_price_positive
CHECK (fixed_price IS NULL OR fixed_price >= 0);

def validate_staging_product(row):
    warnings = []

    if row.price <= 0:
        warnings.append("Цена должна быть > 0")

    if row.price > 100000:
        warnings.append("Подозрительно высокая цена")

    if not row.article:
        warnings.append("Отсутствует артикул")

    return warnings

UPDATE pim_products
SET
    base_price = 2000,      -- из 1С
    fixed_price = 1600,     -- из прайса
    cost_price = 2000 * (1 - 0.20) = 1600  -- из base_price
WHERE article = 'CC2600';

INSERT INTO pim_products (article, fixed_price, cost_price)
VALUES ('OLGINO-NEW-ITEM', 1200, 1200);

CREATE TABLE price_history (
    id SERIAL PRIMARY KEY,
    article VARCHAR(100),
    price_type VARCHAR(50),  -- base_price, fixed_price, cost_price
    old_value NUMERIC(10,2),
    new_value NUMERIC(10,2),
    changed_at TIMESTAMP DEFAULT NOW(),
    changed_by VARCHAR(100)  -- import_1c, import_price, admin_user
);

CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.base_price IS DISTINCT FROM NEW.base_price THEN
        INSERT INTO price_history (article, price_type, old_value, new_value, changed_by)
        VALUES (NEW.article, 'base_price', OLD.base_price, NEW.base_price, current_user);
    END IF;

    IF OLD.fixed_price IS DISTINCT FROM NEW.fixed_price THEN
        INSERT INTO price_history (article, price_type, old_value, new_value, changed_by)
        VALUES (NEW.article, 'fixed_price', OLD.fixed_price, NEW.fixed_price, current_user);
    END IF;

    IF OLD.cost_price IS DISTINCT FROM NEW.cost_price THEN
        INSERT INTO price_history (article, price_type, old_value, new_value, changed_by)
        VALUES (NEW.article, 'cost_price', OLD.cost_price, NEW.cost_price, current_user);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER price_change_log
AFTER UPDATE ON pim_products
FOR EACH ROW
EXECUTE FUNCTION log_price_change();

-- История изменения цены товара CC2600
SELECT
    price_type,
    old_value,
    new_value,
    changed_at,
    changed_by
FROM price_history
WHERE article = 'CC2600'
ORDER BY changed_at DESC
LIMIT 10;

price_type   | old_value | new_value | changed_at          | changed_by
-------------+-----------+-----------+---------------------+--------------
fixed_price  | 800.00    | 850.00    | 2025-12-28 10:30:00 | import_price
cost_price   | 800.00    | 850.00    | 2025-12-28 10:30:00 | import_price
base_price   | 1800.00   | 2000.00   | 2025-12-27 06:05:12 | import_1c

cd /opt/claude-workspace/projects/org/pirotehnika/app/pim

# Полный импорт
python -m pim.import_from_1c

# С обновлением только цен
python -m pim.import_from_1c --prices-only

# CLI
python -m pim.import_price /path/to/price.xlsx --supplier olgino

# С превью (без сохранения)
python -m pim.import_price /path/to/price.xlsx --supplier olgino --dry-run

# API
curl -X POST http://docs.0kt.ru:8000/prices/upload \
  -F "file=@price.xlsx" \
  -F "supplier=olgino"

psql -d pim -c "SELECT * FROM price_cost_rules;"

psql -d pim -c "SELECT * FROM pim_staging_products WHERE upload_id = 123;"