-- Скрипт для очистки данных кабинета фулфилмента
-- ВНИМАНИЕ: Этот скрипт удаляет все данные организаций типа FULFILLMENT

-- Сначала найдем все организации фулфилмента
SELECT 
  'Найденные организации фулфилмента:' as info,
  id, 
  name, 
  fullName, 
  type,
  inn
FROM organizations 
WHERE type = 'FULFILLMENT';

-- Получаем ID организаций фулфилмента для использования в запросах
WITH fulfillment_orgs AS (
  SELECT id FROM organizations WHERE type = 'FULFILLMENT'
)

-- Показываем что будет удалено
SELECT 
  'Данные для удаления:' as info,
  (SELECT COUNT(*) FROM supplies WHERE "organizationId" IN (SELECT id FROM fulfillment_orgs)) as supplies_count,
  (SELECT COUNT(*) FROM supply_orders WHERE "fulfillmentCenterId" IN (SELECT id FROM fulfillment_orgs)) as supply_orders_count,
  (SELECT COUNT(*) FROM employees WHERE "organizationId" IN (SELECT id FROM fulfillment_orgs)) as employees_count,
  (SELECT COUNT(*) FROM services WHERE "organizationId" IN (SELECT id FROM fulfillment_orgs)) as services_count,
  (SELECT COUNT(*) FROM products WHERE "organizationId" IN (SELECT id FROM fulfillment_orgs)) as products_count,
  (SELECT COUNT(*) FROM counterparties WHERE "organizationId" IN (SELECT id FROM fulfillment_orgs) OR "counterpartyId" IN (SELECT id FROM fulfillment_orgs)) as counterparties_count;

-- ОСТОРОЖНО! Раскомментируйте следующие строки для выполнения удаления:

/*
-- Удаляем данные в правильном порядке (с учетом foreign keys)

-- 1. Удаляем связанные данные employee_schedules
DELETE FROM employee_schedules 
WHERE "employeeId" IN (
  SELECT id FROM employees 
  WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT')
);

-- 2. Удаляем сотрудников
DELETE FROM employees 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 3. Удаляем элементы заказов поставок
DELETE FROM supply_order_items 
WHERE "supplyOrderId" IN (
  SELECT id FROM supply_orders 
  WHERE "fulfillmentCenterId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT')
     OR "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT')
);

-- 4. Удаляем заказы поставок
DELETE FROM supply_orders 
WHERE "fulfillmentCenterId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT')
   OR "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 5. Удаляем расходники
DELETE FROM supplies 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 6. Удаляем услуги
DELETE FROM services 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 7. Удаляем элементы корзины
DELETE FROM cart_items 
WHERE "cartId" IN (
  SELECT id FROM carts 
  WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT')
);

-- 8. Удаляем корзины
DELETE FROM carts 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 9. Удаляем избранное
DELETE FROM favorites 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 10. Удаляем товары
DELETE FROM products 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 11. Удаляем партнерские связи
DELETE FROM counterparties 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT')
   OR "counterpartyId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 12. Удаляем запросы на партнерство
DELETE FROM counterparty_requests 
WHERE "senderId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT')
   OR "receiverId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 13. Удаляем API ключи
DELETE FROM api_keys 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 14. Удаляем кеши
DELETE FROM wb_warehouse_caches 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

DELETE FROM seller_stats_caches 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 15. Удаляем пользователей
DELETE FROM users 
WHERE "organizationId" IN (SELECT id FROM organizations WHERE type = 'FULFILLMENT');

-- 16. Наконец, удаляем сами организации фулфилмента
DELETE FROM organizations WHERE type = 'FULFILLMENT';

-- Показываем результат
SELECT 'Данные фулфилмента удалены' as result;
*/