Исходящие цены — Расчёт продажных цен

CREATE TABLE price_sale_rules (
    id SERIAL PRIMARY KEY,
    level VARCHAR(20),           -- product, category, brand, global
    entity_id VARCHAR(100),      -- article, category, brand, или NULL для global
    price_type VARCHAR(20),      -- retail, wholesale, marketplace
    markup NUMERIC(5,2),         -- наценка в процентах
    markup_base VARCHAR(20) DEFAULT 'base_price', -- base_price, cost_price, fixed_price
    valid_from DATE,
    valid_to DATE,
    created_at TIMESTAMP DEFAULT NOW()
);

COMMENT ON COLUMN price_sale_rules.markup_base IS 'База для расчёта наценки: base_price (по умолчанию), cost_price, fixed_price';

1. product    — Правило для конкретного товара
2. category   — Правило для категории
3. brand      — Правило для бренда
4. global     — Глобальное правило (запасной вариант)

INSERT INTO price_sale_rules (level, entity_id, price_type, markup, markup_base) VALUES

-- Глобальные правила (запасные)
-- По умолчанию от base_price (можно не указывать markup_base)
('global', NULL, 'retail', 50.00, 'base_price'),
('global', NULL, 'wholesale', 30.00, 'base_price'),
('global', NULL, 'marketplace', 45.00, 'base_price'),

-- Правила по брендам (от base_price)
('brand', 'JF Pyro', 'retail', 55.00, 'base_price'),
('brand', 'Гордеев', 'retail', 48.00, 'base_price'),

-- Правила по категориям (можно от cost_price)
('category', 'Цветной дым', 'retail', 60.00, 'cost_price'),
('category', 'Батареи салютов', 'wholesale', 25.00, 'cost_price'),

-- Правила для конкретных товаров (высший приоритет)
('product', 'JF-DM30', 'retail', 65.00, 'base_price');

CREATE OR REPLACE VIEW v_retail_prices AS
WITH price_rules AS (
    -- Выбрать подходящее правило по иерархии
    SELECT DISTINCT ON (p.article)
        p.article,
        p.base_price,
        p.fixed_price,
        calculate_cost_price(p.article) as cost_price,
        r.markup,
        r.markup_base,
        r.level
    FROM pim_products p
    LEFT JOIN price_sale_rules r ON (
        (r.level = 'product' AND r.entity_id = p.article AND r.price_type = 'retail')
        OR (r.level = 'category' AND r.entity_id = p.category AND r.price_type = 'retail')
        OR (r.level = 'brand' AND r.entity_id = p.brand AND r.price_type = 'retail')
        OR (r.level = 'global' AND r.price_type = 'retail')
    )
    ORDER BY p.article,
        CASE r.level
            WHEN 'product' THEN 1
            WHEN 'category' THEN 2
            WHEN 'brand' THEN 3
            WHEN 'global' THEN 4
            ELSE 5
        END
)
SELECT
    article,
    base_price,
    cost_price,
    markup,
    markup_base,
    level as rule_source,
    -- Расчёт в зависимости от markup_base
    CASE COALESCE(markup_base, 'base_price')
        WHEN 'base_price' THEN ROUND(base_price * (1 + markup/100), 2)
        WHEN 'cost_price' THEN ROUND(cost_price * (1 + markup/100), 2)
        WHEN 'fixed_price' THEN ROUND(fixed_price * (1 + markup/100), 2)
        ELSE ROUND(base_price * (1 + markup/100), 2)  -- fallback
    END as retail_price
FROM price_rules;

-- Получить розничные цены всех товаров
SELECT * FROM v_retail_prices;

-- Розничная цена конкретного товара
SELECT retail_price FROM v_retail_prices WHERE article = 'JF-DM30';

-- Товары с наценкой выше 60%
SELECT * FROM v_retail_prices WHERE markup > 60;

INSERT INTO price_sale_rules (level, entity_id, price_type, markup, markup_base)
VALUES ('brand', 'JF Pyro', 'retail', 55.00, 'base_price');

-- Расчёт:
base_price = 1000 ₽
retail_price = 1000 × 1.55 = 1550 ₽

INSERT INTO price_sale_rules (level, entity_id, price_type, markup, markup_base)
VALUES ('category', 'Цветной дым', 'retail', 70.00, 'cost_price');

-- Расчёт:
base_price = 1000 ₽
cost_price = 800 ₽  (base_price с учётом скидки 20%)
retail_price = 800 × 1.70 = 1360 ₽

def find_markup_rule(article: str, category: str, brand: str) -> float:
    # 1. Правило для товара (высший приоритет)
    rule = db.query("""
        SELECT markup FROM price_sale_rules
        WHERE level='product' AND entity_id=? AND price_type='retail'
    """, article)
    if rule:
        return rule.markup

    # 2. Правило для категории
    rule = db.query("""
        SELECT markup FROM price_sale_rules
        WHERE level='category' AND entity_id=? AND price_type='retail'
    """, category)
    if rule:
        return rule.markup

    # 3. Правило для бренда
    rule = db.query("""
        SELECT markup FROM price_sale_rules
        WHERE level='brand' AND entity_id=? AND price_type='retail'
    """, brand)
    if rule:
        return rule.markup

    # 4. Глобальное правило (запасной вариант)
    rule = db.query("""
        SELECT markup FROM price_sale_rules
        WHERE level='global' AND price_type='retail'
    """)
    return rule.markup if rule else 50.0  # fallback

retail_price = cost_price * (1 + markup / 100)

retail_price = round(retail_price, 0)  # 1549.99 → 1550

ALTER TABLE pim_products
ADD COLUMN retail_price NUMERIC(10,2);

CREATE INDEX idx_pim_products_retail_price ON pim_products(retail_price);

CREATE OR REPLACE FUNCTION update_retail_price()
RETURNS TRIGGER AS $$
BEGIN
    -- Пересчитать при изменении cost_price
    IF OLD.cost_price IS DISTINCT FROM NEW.cost_price THEN
        UPDATE pim_products
        SET retail_price = (
            SELECT retail_price FROM v_retail_prices WHERE article = NEW.article
        )
        WHERE article = NEW.article;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER retail_price_update
AFTER UPDATE ON pim_products
FOR EACH ROW
EXECUTE FUNCTION update_retail_price();

-- Пересчитать все цены
UPDATE pim_products p
SET retail_price = v.retail_price
FROM v_retail_prices v
WHERE p.article = v.article;

psql -d pim -c "UPDATE pim_products p SET retail_price = (SELECT retail_price FROM v_retail_prices WHERE article = p.article);"

INSERT INTO price_sale_rules (level, entity_id, price_type, markup) VALUES
-- Глобальная оптовая наценка
('global', NULL, 'wholesale', 30.00),

-- По категориям
('category', 'Батареи салютов', 'wholesale', 25.00),
('category', 'Цветной дым', 'wholesale', 28.00),

-- По брендам
('brand', 'Гордеев', 'wholesale', 22.00);

wholesale_price = cost_price × (1 + wholesale_markup / 100)

ALTER TABLE pim_products ADD COLUMN wholesale_price NUMERIC(10,2);

CREATE VIEW v_wholesale_prices AS
SELECT
    article,
    cost_price,
    cost_price * (1 + markup/100) as wholesale_price
FROM pim_products p
LEFT JOIN price_sale_rules r ON ...
WHERE r.price_type = 'wholesale';

# Получить оптовую цену для клиента
price = db.query(
    "SELECT wholesale_price FROM pim_products WHERE article = ?",
    article
).scalar()

# OZON комиссия 15%
marketplace_price = cost_price × (1 + markup) / (1 - commission)

# Пример:
cost = 1000 ₽
markup = 45%
commission = 15%
→ marketplace_price = 1000 × 1.45 / 0.85 = 1705.88 ₽

min_price = cost_price * 1.15
marketplace_price = max(marketplace_price, min_price)

CREATE TABLE marketplace_settings (
    marketplace VARCHAR(50) PRIMARY KEY,
    commission_percent NUMERIC(5,2),
    min_markup_percent NUMERIC(5,2),
    rounding_rule VARCHAR(20)  -- none, to_ruble, to_10_rub
);

INSERT INTO marketplace_settings VALUES
('ozon', 15.00, 15.00, 'to_ruble'),
('wildberries', 18.00, 20.00, 'to_10_rub');

def calculate_marketplace_price(
    cost_price: float,
    markup: float,
    marketplace: str = 'ozon'
) -> float:
    settings = get_marketplace_settings(marketplace)

    # Базовая цена с наценкой
    price = cost_price * (1 + markup / 100)

    # Учёт комиссии
    price = price / (1 - settings.commission / 100)

    # Минимальная цена
    min_price = cost_price * (1 + settings.min_markup / 100)
    price = max(price, min_price)

    # Округление
    if settings.rounding == 'to_ruble':
        price = round(price, 0)
    elif settings.rounding == 'to_10_rub':
        price = round(price / 10) * 10

    return price

v_retail_prices
       ↓
sync_pim_to_webasyst.py
       ↓
UPDATE shop_product SET price = ?

cd /opt/claude-workspace/projects/org/pirotehnika/app/site/feyerverk.spb.ru/solution
python sync_pim_to_webasyst.py --prices-only

0 8,14,20 * * * python sync_pim_to_webasyst.py --prices-only

calculate_marketplace_price()
       ↓
POST /v1/product/import/prices

from library.connectors.api.ozon import OzonClient

client = OzonClient(client_id=..., api_key=...)

prices = []
for product in pim_products:
    prices.append({
        "offer_id": product.article,
        "price": calculate_marketplace_price(
            product.cost_price,
            markup=45,
            marketplace='ozon'
        )
    })

client.update_prices(prices)

0 9,15,21 * * * python sync_pim_to_ozon.py --prices

pim_products.wholesale_price
       ↓
Telegram Bot API
       ↓
Сообщение клиенту

@bot.message_handler(commands=['price'])
def get_price(message):
    article = message.text.split()[1]

    price = db.query(
        "SELECT wholesale_price FROM pim_products WHERE article = ?",
        article
    ).scalar()

    bot.reply_to(message, f"Оптовая цена: {price} ₽")

CREATE TABLE price_promotions (
    id SERIAL PRIMARY KEY,
    article VARCHAR(100),
    discount_percent NUMERIC(5,2),
    valid_from DATE,
    valid_to DATE
);

SELECT
    p.article,
    p.retail_price,
    pr.discount_percent,
    p.retail_price * (1 - pr.discount_percent/100) as promo_price
FROM pim_products p
LEFT JOIN price_promotions pr ON p.article = pr.article
WHERE pr.valid_from <= CURRENT_DATE
  AND pr.valid_to >= CURRENT_DATE;

ALTER TABLE pim_products
ADD CONSTRAINT check_retail_above_cost
CHECK (retail_price >= cost_price);

def set_retail_price(article: str, price: float):
    cost = get_cost_price(article)

    if price < cost:
        raise ValueError(f"Цена {price} ниже себестоимости {cost}")

    db.execute(
        "UPDATE pim_products SET retail_price = ? WHERE article = ?",
        price, article
    )

price_with_vat = retail_price * 1.20  # НДС 20%

ALTER TABLE pim_products
ADD COLUMN vat_rate NUMERIC(5,2) DEFAULT 20.00;

-- VIEW с НДС
CREATE VIEW v_prices_with_vat AS
SELECT
    article,
    retail_price,
    vat_rate,
    retail_price * (1 + vat_rate/100) as price_with_vat
FROM pim_products;

psql -d pim -c "
UPDATE pim_products p
SET retail_price = (SELECT retail_price FROM v_retail_prices WHERE article = p.article);
"

psql -d pim -c "SELECT * FROM price_sale_rules ORDER BY level, entity_id;"

psql -d pim -c "
SELECT p.article, p.brand, p.category
FROM pim_products p
LEFT JOIN v_retail_prices v ON p.article = v.article
WHERE v.retail_price IS NULL;
"

psql -d pim -c "
INSERT INTO price_sale_rules (level, price_type, markup, base_value_source)
VALUES ('global', 'retail', 50.00, 'from_cost');
"

cd /opt/claude-workspace/projects/org/pirotehnika/app/ozon
python sync_prices_to_ozon.py --account O1

SELECT
    article,
    cost_price,
    retail_price,
    ROUND((retail_price / cost_price - 1) * 100, 2) as actual_markup
FROM pim_products
WHERE retail_price / cost_price < 1.30  -- наценка < 30%
ORDER BY actual_markup ASC;

SELECT
    p.article,
    p.retail_price as our_price,
    c.price as competitor_price,
    p.retail_price - c.price as difference
FROM pim_products p
LEFT JOIN competitor_prices c ON p.article = c.article
WHERE p.retail_price > c.price * 1.10
ORDER BY difference DESC;

SELECT
    article,
    price_type,
    old_value,
    new_value,
    changed_at,
    changed_by
FROM price_history
WHERE price_type IN ('retail_price', 'wholesale_price')
  AND changed_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY changed_at DESC;