ПЛАН МИГРАЦИИ PIM → ПРАВИЛЬНАЯ АРХИТЕКТУРА

# Бэкап схемы
PGPASSWORD='NocoDB2025Secure' pg_dump \
  -h localhost -p 5433 -U nocodb -d nocodb \
  --schema=pt7k98pv0fwi1el \
  --schema-only \
  > /tmp/pim_schema_backup_$(date +%Y%m%d_%H%M%S).sql

# Бэкап данных ключевых таблиц
PGPASSWORD='NocoDB2025Secure' pg_dump \
  -h localhost -p 5433 -U nocodb -d nocodb \
  --schema=pt7k98pv0fwi1el \
  -t pt7k98pv0fwi1el.pim_products \
  -t pt7k98pv0fwi1el.stocks \
  -t pt7k98pv0fwi1el.\"PriceListItem\" \
  --data-only \
  > /tmp/pim_data_backup_$(date +%Y%m%d_%H%M%S).sql

cp /opt/claude-workspace/projects/org/pirotehnika/data/nocodb/pim.db \
   /opt/claude-workspace/projects/org/pirotehnika/data/nocodb/pim_backup_$(date +%Y%m%d_%H%M%S).db

CREATE TABLE IF NOT EXISTS pt7k98pv0fwi1el.suppliers (
  code TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  api_type TEXT,
  contact_email TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Наполнить справочник
INSERT INTO pt7k98pv0fwi1el.suppliers (code, name, api_type) VALUES
  ('jf', 'JF-Pyro', 'json'),
  ('maxsem', 'Maxsem', 'xlsx'),
  ('pirosnab', 'Pirosnab', 'web_scraper'),
  ('gordeev', 'ИП Гордеев', 'xlsx'),
  ('1c', '1C Master', '1c_json'),
  ('piroff', 'Piroff Fireworks', 'xlsx'),
  ('supersalut', 'Супер Салют', 'xlsx'),
  ('uralsalut', 'Урал Салют', 'xlsx'),
  ('salutrossii', 'Салют России', 'xlsx'),
  ('streletsky', 'Стрелецкий', 'xlsx'),
  ('narodny', 'Народный Фейерверк', 'xlsx'),
  ('salyuteks', 'Салютекс', 'xlsx'),
  ('zeergo', 'ZEERGO', 'xls')
ON CONFLICT (code) DO NOTHING;

CREATE TABLE IF NOT EXISTS pt7k98pv0fwi1el.supplier_cost (
  id SERIAL PRIMARY KEY,
  article TEXT NOT NULL,
  supplier_code TEXT NOT NULL REFERENCES pt7k98pv0fwi1el.suppliers(code),
  cost_price NUMERIC NOT NULL,
  currency TEXT DEFAULT 'RUB',
  valid_from TIMESTAMP DEFAULT NOW(),
  valid_to TIMESTAMP,
  source_file TEXT,
  notes TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(article, supplier_code, valid_from)
);

CREATE INDEX idx_supplier_cost_article ON pt7k98pv0fwi1el.supplier_cost(article);
CREATE INDEX idx_supplier_cost_supplier ON pt7k98pv0fwi1el.supplier_cost(supplier_code);
CREATE INDEX idx_supplier_cost_active ON pt7k98pv0fwi1el.supplier_cost(article, supplier_code)
  WHERE valid_to IS NULL;

CREATE TABLE IF NOT EXISTS pt7k98pv0fwi1el.supplier_stock (
  id SERIAL PRIMARY KEY,
  article TEXT NOT NULL,
  supplier_code TEXT NOT NULL REFERENCES pt7k98pv0fwi1el.suppliers(code),
  quantity INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(article, supplier_code)
);

CREATE INDEX idx_supplier_stock_article ON pt7k98pv0fwi1el.supplier_stock(article);
CREATE INDEX idx_supplier_stock_supplier ON pt7k98pv0fwi1el.supplier_stock(supplier_code);

-- Цены из pim_products (если есть supplier)
INSERT INTO pt7k98pv0fwi1el.supplier_cost (
  article, supplier_code, cost_price, source_file, valid_from
)
SELECT
  p.article,
  CASE
    WHEN p.supplier = 'ИП Гордеев' THEN 'gordeev'
    WHEN p.supplier = 'Pirosnab' THEN 'pirosnab'
    WHEN p.supplier ILIKE '%jf%' THEN 'jf'
    WHEN p.supplier ILIKE '%maxsem%' THEN 'maxsem'
    ELSE LOWER(REGEXP_REPLACE(p.supplier, '[^a-zA-Z0-9]', '', 'g'))
  END as supplier_code,
  p.cost_price,
  p.data_source,
  p.updated_at
FROM pt7k98pv0fwi1el.pim_products p
WHERE p.cost_price IS NOT NULL
  AND p.cost_price > 0
  AND p.supplier IS NOT NULL
  AND EXISTS (
    SELECT 1 FROM pt7k98pv0fwi1el.suppliers s
    WHERE s.code = CASE
      WHEN p.supplier = 'ИП Гордеев' THEN 'gordeev'
      WHEN p.supplier = 'Pirosnab' THEN 'pirosnab'
      WHEN p.supplier ILIKE '%jf%' THEN 'jf'
      WHEN p.supplier ILIKE '%maxsem%' THEN 'maxsem'
      ELSE LOWER(REGEXP_REPLACE(p.supplier, '[^a-zA-Z0-9]', '', 'g'))
    END
  )
ON CONFLICT (article, supplier_code, valid_from) DO NOTHING;

INSERT INTO pt7k98pv0fwi1el.supplier_cost (
  article, supplier_code, cost_price, source_file, valid_from
)
SELECT
  pli.article,
  pl.supplier_code,
  pli.cost_price,
  pl.source_file,
  pl.loaded_at
FROM pt7k98pv0fwi1el."PriceListItem" pli
JOIN pt7k98pv0fwi1el."PriceList" pl ON pli.pricelist_id = pl.nc_order
WHERE pli.cost_price IS NOT NULL
  AND pli.cost_price > 0
  AND pl.supplier_code IS NOT NULL
  AND EXISTS (
    SELECT 1 FROM pt7k98pv0fwi1el.suppliers s
    WHERE s.code = pl.supplier_code
  )
ON CONFLICT (article, supplier_code, valid_from) DO NOTHING;

-- Сколько цен мигрировали
SELECT
  supplier_code,
  COUNT(*) as prices_count,
  COUNT(DISTINCT article) as unique_articles
FROM pt7k98pv0fwi1el.supplier_cost
GROUP BY supplier_code
ORDER BY prices_count DESC;

-- Товары без цен
SELECT COUNT(*) as products_without_prices
FROM pt7k98pv0fwi1el.pim_products p
WHERE NOT EXISTS (
  SELECT 1 FROM pt7k98pv0fwi1el.supplier_cost sc
  WHERE sc.article = p.article AND sc.valid_to IS NULL
);

-- Представление с cost_price для старого кода
CREATE OR REPLACE VIEW pt7k98pv0fwi1el.pim_products_with_cost AS
SELECT
  p.*,
  sc_primary.cost_price,
  sc_primary.supplier_code as cost_supplier
FROM pt7k98pv0fwi1el.pim_products p
LEFT JOIN LATERAL (
  SELECT cost_price, supplier_code
  FROM pt7k98pv0fwi1el.supplier_cost sc
  WHERE sc.article = p.article
    AND sc.valid_to IS NULL
  ORDER BY
    CASE supplier_code
      WHEN 'jf' THEN 1
      WHEN 'maxsem' THEN 2
      WHEN 'pirosnab' THEN 3
      ELSE 99
    END
  LIMIT 1
) sc_primary ON TRUE;

-- Резервная копия значений
CREATE TABLE pt7k98pv0fwi1el.pim_products_cost_backup AS
SELECT article, cost_price, stock_qty, updated_at
FROM pt7k98pv0fwi1el.pim_products
WHERE cost_price IS NOT NULL OR stock_qty IS NOT NULL;

-- Удаление колонок (раскомментировать после обновления кода)
-- ALTER TABLE pt7k98pv0fwi1el.pim_products
--   DROP COLUMN IF EXISTS cost_price,
--   DROP COLUMN IF EXISTS stock_qty;

"""
Хелперы для работы с ценами и остатками поставщиков
"""
from typing import Optional
import psycopg2
from datetime import datetime

PG_CONFIG = {
    'host': 'localhost',
    'port': 5433,
    'user': 'nocodb',
    'password': 'NocoDB2025Secure',
    'database': 'nocodb'
}
SCHEMA = 'pt7k98pv0fwi1el'

def get_supplier_cost(
    article: str,
    supplier_code: str = 'jf',
    valid_at: datetime = None
) -> Optional[float]:
    """
    Получить цену от поставщика

    Args:
        article: Артикул товара
        supplier_code: Код поставщика (jf, maxsem, pirosnab...)
        valid_at: Дата актуальности (None = сейчас)

    Returns:
        Цена или None
    """
    conn = psycopg2.connect(**PG_CONFIG)
    cursor = conn.cursor()

    if valid_at is None:
        valid_at = datetime.now()

    cursor.execute(f"""
        SELECT cost_price
        FROM {SCHEMA}.supplier_cost
        WHERE article = %s
          AND supplier_code = %s
          AND valid_from <= %s
          AND (valid_to IS NULL OR valid_to > %s)
        ORDER BY valid_from DESC
        LIMIT 1
    """, (article, supplier_code, valid_at, valid_at))

    result = cursor.fetchone()
    cursor.close()
    conn.close()

    return float(result[0]) if result else None


def get_best_supplier_cost(article: str) -> tuple[Optional[float], Optional[str]]:
    """
    Получить лучшую (минимальную) цену среди всех поставщиков

    Returns:
        (цена, код_поставщика) или (None, None)
    """
    conn = psycopg2.connect(**PG_CONFIG)
    cursor = conn.cursor()

    cursor.execute(f"""
        SELECT cost_price, supplier_code
        FROM {SCHEMA}.supplier_cost
        WHERE article = %s
          AND valid_to IS NULL
        ORDER BY cost_price ASC
        LIMIT 1
    """, (article,))

    result = cursor.fetchone()
    cursor.close()
    conn.close()

    if result:
        return (float(result[0]), result[1])
    return (None, None)


def get_supplier_stock(article: str, supplier_code: str) -> int:
    """
    Получить остаток у поставщика
    """
    conn = psycopg2.connect(**PG_CONFIG)
    cursor = conn.cursor()

    cursor.execute(f"""
        SELECT quantity
        FROM {SCHEMA}.supplier_stock
        WHERE article = %s AND supplier_code = %s
    """, (article, supplier_code))

    result = cursor.fetchone()
    cursor.close()
    conn.close()

    return result[0] if result else 0


def update_supplier_cost(
    article: str,
    supplier_code: str,
    cost_price: float,
    source_file: str = None
):
    """
    Обновить цену от поставщика (создаёт новую запись с valid_from=NOW)
    """
    conn = psycopg2.connect(**PG_CONFIG)
    cursor = conn.cursor()

    # Закрыть предыдущую цену
    cursor.execute(f"""
        UPDATE {SCHEMA}.supplier_cost
        SET valid_to = NOW()
        WHERE article = %s
          AND supplier_code = %s
          AND valid_to IS NULL
    """, (article, supplier_code))

    # Создать новую цену
    cursor.execute(f"""
        INSERT INTO {SCHEMA}.supplier_cost (
            article, supplier_code, cost_price, source_file
        ) VALUES (%s, %s, %s, %s)
    """, (article, supplier_code, cost_price, source_file))

    conn.commit()
    cursor.close()
    conn.close()

#!/usr/bin/env python3
"""
Импорт Pirosnab → PIM v3.0
Использует новую архитектуру (supplier_cost, supplier_stock)
"""
import sys
from pathlib import Path
sys.path.append(str(Path(__file__).parent))
sys.path.append('/opt/claude-workspace/projects/org/pirotehnika/app/pim')

from pirosnab_client import PirosnaberClient
from utils.supplier_helpers import update_supplier_cost
import psycopg2

# ... (аналогично v2, но использует supplier_cost через helper)

# СТРУКТУРА БАЗЫ ДАННЫХ PIM

**Версия:** 3.0.0
**Дата:** 2025-12-26
**PRODUCTION:** PostgreSQL NocoDB (pt7k98pv0fwi1el)

## СЛОИ ДАННЫХ

### 1. Товары (характеристики)

**pim_products** (4626 товаров)
- article (PK) — артикул
- name — название
- brand — бренд
- category — категория
- base_price — базовая цена от производителя

**ВАЖНО:** cost_price и stock_qty УДАЛЕНЫ из этой таблицы!

### 2. Цены от поставщиков

**supplier_cost** — цены закупки
- article + supplier_code — от какого поставщика
- cost_price — цена закупки
- valid_from → valid_to — период действия

**suppliers** — справочник поставщиков
- code (PK) — jf, maxsem, pirosnab...

### 3. Остатки

**stocks** — остатки ПО СКЛАДАМ
- article + warehouse_id — на каком складе
- quantity, reserved, available

**warehouses** — склады
- code — pirosnab, gordeev_main, ozon_fbo...

**supplier_stock** — остатки У ПОСТАВЩИКОВ
- article + supplier_code — у какого поставщика
- quantity

...

# CHANGELOG PIM

## [3.0.0] - 2025-12-26

### Добавлено
- Таблицы `supplier_cost` и `supplier_stock` (цены/остатки от поставщиков)
- Справочник `suppliers`
- Представление `pim_products_with_cost` (обратная совместимость)
- Хелперы `utils/supplier_helpers.py`

### Изменено
- Удалены `cost_price` и `stock_qty` из `pim_products`
- Обновлён импорт Pirosnab (v3.0)

### Миграция
- Данные из `pim_products.cost_price` → `supplier_cost`
- Данные из `PriceListItem` → `supplier_cost`
- См. [MIGRATION_PLAN.md](../../data/connectors/MIGRATION_PLAN.md)

mkdir -p /opt/claude-workspace/projects/org/pirotehnika/app/pim/archive/2025-12-26-refactoring

mv /opt/claude-workspace/projects/org/pirotehnika/app/pim/REFACTORING_*.md \
   /opt/claude-workspace/projects/org/pirotehnika/app/pim/archive/2025-12-26-refactoring/

mv /opt/claude-workspace/projects/org/pirotehnika/app/pim/COMBINED_REFACTORING_PLAN.md \
   /opt/claude-workspace/projects/org/pirotehnika/app/pim/archive/2025-12-26-refactoring/

mv /opt/claude-workspace/projects/org/pirotehnika/app/pim/docs/PRICING_STOCK_TABLES.md \
   /opt/claude-workspace/projects/org/pirotehnika/app/pim/archive/2025-12-26-refactoring/

mv /opt/claude-workspace/projects/org/pirotehnika/app/pim/migrations/data_architecture/*.md \
   /opt/claude-workspace/projects/org/pirotehnika/app/pim/archive/2025-12-26-refactoring/

-- Проверить что все товары имеют хотя бы одну цену
SELECT
  (SELECT COUNT(*) FROM pt7k98pv0fwi1el.pim_products) as total_products,
  (SELECT COUNT(DISTINCT article) FROM pt7k98pv0fwi1el.supplier_cost WHERE valid_to IS NULL) as products_with_price,
  (SELECT COUNT(*) FROM pt7k98pv0fwi1el.pim_products p
   WHERE NOT EXISTS (
     SELECT 1 FROM pt7k98pv0fwi1el.supplier_cost sc
     WHERE sc.article = p.article AND sc.valid_to IS NULL
   )) as products_without_price;

-- Проверить историю цен (должны быть записи с valid_to NOT NULL)
SELECT
  COUNT(*) as total_prices,
  COUNT(*) FILTER (WHERE valid_to IS NULL) as active_prices,
  COUNT(*) FILTER (WHERE valid_to IS NOT NULL) as historical_prices
FROM pt7k98pv0fwi1el.supplier_cost;

# Запустить импорт с новой архитектурой
python3 /opt/claude-workspace/projects/org/pirotehnika/data/connectors/import_pirosnab_to_pim_v3.py

# Проверить что цены добавились в supplier_cost
psql ... -c "SELECT COUNT(*) FROM supplier_cost WHERE supplier_code = 'pirosnab'"

# Восстановить из бэкапа
PGPASSWORD='NocoDB2025Secure' psql \
  -h localhost -p 5433 -U nocodb -d nocodb \
  < /tmp/pim_data_backup_YYYYMMDD_HHMMSS.sql

DROP TABLE IF EXISTS pt7k98pv0fwi1el.supplier_cost;
DROP TABLE IF EXISTS pt7k98pv0fwi1el.supplier_stock;
DROP TABLE IF EXISTS pt7k98pv0fwi1el.suppliers;
DROP VIEW IF EXISTS pt7k98pv0fwi1el.pim_products_with_cost;