-- Purge de remise a zero - Plateforme Atelier Com'
-- Date: 2026-06-04
--
-- Objectif:
-- - conserver uniquement l'utilisateur alves.kyllian@gmail.com;
-- - conserver uniquement la boutique L'Atelier COM' du Roumois;
-- - rattacher la boutique au domaine boutique.ateliercomduroumois.fr;
-- - supprimer commandes, factures, avoirs, paniers, produits boutiques,
--   reseaux/franchises et donnees rattachees;
-- - conserver le catalogue maitre: products, product_assets,
--   product_attributes, product_attribute_options;
-- - conserver les grilles tarifaires: price_grids, price_grid_rows;
-- - conserver la configuration globale utile: modules, roles, permissions,
--   shipping global, price policy globale.
--
-- Aucune creation de table dans ce fichier: uniquement des commandes de
-- maintenance sur une base deja creee.
--
-- A executer apres sauvegarde complete de la base.

SET @keep_user_email := 'alves.kyllian@gmail.com';
SET @keep_shop_name := 'L''Atelier COM'' du Roumois';
SET @keep_shop_domain := 'boutique.ateliercomduroumois.fr';

SET @keep_user_id := (
  SELECT MIN(`id`)
  FROM `users`
  WHERE BINARY `email` = BINARY @keep_user_email
);

SET @keep_shop_id := COALESCE(
  (
    SELECT MIN(s.`id`)
    FROM `shops` s
    INNER JOIN `shop_domains` sd ON sd.`shop_id` = s.`id`
    WHERE BINARY sd.`domain` = BINARY @keep_shop_domain
       OR BINARY sd.`normalized_domain` = BINARY @keep_shop_domain
       OR BINARY sd.`host` = BINARY @keep_shop_domain
  ),
  (
    SELECT MIN(`id`)
    FROM `shops`
    WHERE BINARY `name` = BINARY @keep_shop_name
       OR BINARY `code` = BINARY 'boutique'
       OR BINARY `slug` = BINARY 'boutique'
  )
);

SET @purge_ok := IF(@keep_user_id IS NOT NULL AND @keep_shop_id IS NOT NULL, 1, 0);

SELECT
  @purge_ok AS purge_autorisee,
  @keep_user_id AS utilisateur_conserve_avant_purge,
  @keep_shop_id AS boutique_conservee_avant_purge,
  CASE
    WHEN @purge_ok = 1 THEN 'OK - purge autorisee'
    ELSE 'STOP - utilisateur ou boutique introuvable, les DELETE/UPDATE destructifs ne feront rien'
  END AS controle_avant_purge;

SET @old_foreign_key_checks := @@FOREIGN_KEY_CHECKS;
SET @old_sql_safe_updates := @@SQL_SAFE_UPDATES;

SET SQL_SAFE_UPDATES = 0;

-- 1. Donnees transactionnelles: commandes, paiements, BAT, documents, factures, avoirs.
DELETE FROM `payment_webhook_events` WHERE @purge_ok = 1;
DELETE FROM `order_bat_comments` WHERE @purge_ok = 1;
DELETE FROM `order_bat_requests` WHERE @purge_ok = 1;
DELETE FROM `order_documents` WHERE @purge_ok = 1;
DELETE FROM `order_payments` WHERE @purge_ok = 1;
DELETE FROM `order_status_history` WHERE @purge_ok = 1;
DELETE FROM `promotion_charge_invoice_lines` WHERE @purge_ok = 1;
DELETE FROM `promotion_charge_invoices` WHERE @purge_ok = 1;
DELETE FROM `retrocession_statement_lines` WHERE @purge_ok = 1;
DELETE FROM `retrocession_statements` WHERE @purge_ok = 1;
DELETE FROM `order_items` WHERE @purge_ok = 1;
DELETE FROM `orders` WHERE @purge_ok = 1;

-- 2. Paniers, devis, personnalisation et maquettes boutiques.
DELETE FROM `storefront_cart_states` WHERE @purge_ok = 1;
DELETE FROM `storefront_checkout_states` WHERE @purge_ok = 1;
DELETE FROM `catalog_quote_requests` WHERE @purge_ok = 1;
DELETE FROM `mockup_generations` WHERE @purge_ok = 1;

-- 3. Credits et mouvements financiers.
DELETE FROM `credit_transactions` WHERE @purge_ok = 1;
DELETE FROM `credit_accounts` WHERE @purge_ok = 1;

-- 4. Produits boutiques uniquement.
-- Le catalogue maitre et ses grilles tarifaires ne sont pas touches.
DELETE FROM `shop_product_assets` WHERE @purge_ok = 1;
DELETE FROM `shop_products` WHERE @purge_ok = 1;
DELETE FROM `product_pack_items` WHERE @purge_ok = 1;
DELETE FROM `product_packs` WHERE @purge_ok = 1;

-- 5. Promotions, retrocessions et politiques liees aux reseaux/boutiques.
DELETE FROM `promotion_code_shops` WHERE @purge_ok = 1;
DELETE FROM `promotion_codes` WHERE @purge_ok = 1;
DELETE FROM `retrocession_policies` WHERE @purge_ok = 1;
DELETE FROM `price_policies`
WHERE @purge_ok = 1
  AND `scope_type` <> 'global';

UPDATE `price_policies`
SET `scope_id` = NULL
WHERE @purge_ok = 1
  AND `scope_type` = 'global';

-- 6. Transport: on garde uniquement la configuration globale.
DELETE sme
FROM `shipping_method_exclusions` sme
LEFT JOIN `shipping_methods` sm ON sm.`id` = sme.`shipping_method_id`
LEFT JOIN `shipping_zones` sz ON sz.`id` = sme.`shipping_zone_id`
WHERE @purge_ok = 1
  AND (sm.`id` IS NULL OR sz.`id` IS NULL OR sm.`scope_type` <> 'global' OR sz.`scope_type` <> 'global');

DELETE smr
FROM `shipping_method_rates` smr
LEFT JOIN `shipping_methods` sm ON sm.`id` = smr.`shipping_method_id`
LEFT JOIN `shipping_zones` sz ON sz.`id` = smr.`shipping_zone_id`
WHERE @purge_ok = 1
  AND (sm.`id` IS NULL OR sz.`id` IS NULL OR sm.`scope_type` <> 'global' OR sz.`scope_type` <> 'global');

DELETE FROM `shipping_methods`
WHERE @purge_ok = 1
  AND `scope_type` <> 'global';

DELETE FROM `shipping_zones`
WHERE @purge_ok = 1
  AND `scope_type` <> 'global';

UPDATE `shipping_methods`
SET `network_id` = NULL,
    `shop_id` = NULL
WHERE @purge_ok = 1
  AND `scope_type` = 'global';

UPDATE `shipping_zones`
SET `network_id` = NULL,
    `shop_id` = NULL
WHERE @purge_ok = 1
  AND `scope_type` = 'global';

-- 7. Comptes, roles et donnees utilisateurs.
DELETE FROM `addresses` WHERE @purge_ok = 1;
DELETE FROM `login_rate_limits` WHERE @purge_ok = 1;
DELETE FROM `oauth_user_links` WHERE @purge_ok = 1;
DELETE FROM `user_network_memberships` WHERE @purge_ok = 1;
DELETE FROM `user_pack_assignments` WHERE @purge_ok = 1;
DELETE FROM `user_personalization_repiquages` WHERE @purge_ok = 1;
DELETE FROM `user_platform_roles` WHERE @purge_ok = 1;
DELETE FROM `user_shop_memberships` WHERE @purge_ok = 1;

DELETE FROM `users`
WHERE @purge_ok = 1
  AND BINARY `email` <> BINARY @keep_user_email;

UPDATE `users`
SET `id` = 1,
    `network_id` = NULL,
    `created_by_user_id` = NULL,
    `external_identifier` = NULL,
    `status` = 'active',
    `updated_at` = NOW()
WHERE @purge_ok = 1
  AND BINARY `email` = BINARY @keep_user_email;

-- 8. Domaines, modules, boutiques et reseaux/franchises.
DELETE FROM `shop_domains` WHERE @purge_ok = 1;
DELETE FROM `shop_modules` WHERE @purge_ok = 1;

DELETE FROM `shops`
WHERE @purge_ok = 1
  AND `id` <> @keep_shop_id;

UPDATE `shops`
SET `id` = 1,
    `network_id` = NULL,
    `code` = 'boutique',
    `name` = @keep_shop_name,
    `slug` = 'boutique',
    `status` = 'active',
    `is_generic` = 1,
    `primary_color` = COALESCE(`primary_color`, '#dc5d58'),
    `secondary_color` = COALESCE(`secondary_color`, '#053535'),
    `retrocession_rate` = NULL,
    `updated_at` = NOW()
WHERE @purge_ok = 1
  AND `id` = @keep_shop_id;

DELETE FROM `oauth_providers` WHERE @purge_ok = 1;
DELETE FROM `networks` WHERE @purge_ok = 1;

-- 9. Reconstruction minimale de la boutique conservee.
INSERT INTO `shop_domains` (
  `id`,
  `shop_id`,
  `network_id`,
  `host`,
  `domain`,
  `normalized_domain`,
  `type`,
  `is_primary`,
  `redirect_to_primary`,
  `verification_status`,
  `ssl_status`,
  `ssl_error`,
  `verification_token`,
  `verified_at`,
  `is_active`,
  `created_at`,
  `updated_at`
)
SELECT
  1,
  1,
  NULL,
  @keep_shop_domain,
  @keep_shop_domain,
  @keep_shop_domain,
  'platform_subdomain',
  1,
  0,
  'verified',
  'active',
  NULL,
  NULL,
  NOW(),
  1,
  NOW(),
  NOW()
WHERE @purge_ok = 1;

ALTER TABLE `shop_modules` AUTO_INCREMENT = 1;

INSERT INTO `shop_modules` (`shop_id`, `module_id`, `enabled`, `created_at`)
SELECT 1, m.`id`, 1, NOW()
FROM `modules` m
WHERE @purge_ok = 1
  AND BINARY m.`code` IN (BINARY 'catalog', BINARY 'pricing', BINARY 'orders', BINARY 'credits')
ORDER BY CASE
  WHEN BINARY m.`code` = BINARY 'catalog' THEN 1
  WHEN BINARY m.`code` = BINARY 'pricing' THEN 2
  WHEN BINARY m.`code` = BINARY 'orders' THEN 3
  WHEN BINARY m.`code` = BINARY 'credits' THEN 4
  ELSE 99
END;

INSERT INTO `user_platform_roles` (`id`, `user_id`, `role_code`, `is_active`, `created_at`)
SELECT 1, 1, 'super_admin', 1, NOW()
WHERE @purge_ok = 1;

INSERT INTO `user_shop_memberships` (`id`, `user_id`, `shop_id`, `role_code`, `is_active`, `created_at`)
SELECT 1, 1, 1, 'master_admin', 1, NOW()
WHERE @purge_ok = 1;

INSERT INTO `credit_accounts` (`id`, `owner_type`, `owner_id`, `currency`, `balance_cached`, `created_at`)
SELECT 1, 'user', 1, 'EUR', 0.00, NOW()
WHERE @purge_ok = 1;

-- 10. Remise a plat des compteurs AUTO_INCREMENT.
ALTER TABLE `addresses` AUTO_INCREMENT = 1;
ALTER TABLE `catalog_quote_requests` AUTO_INCREMENT = 1;
ALTER TABLE `credit_accounts` AUTO_INCREMENT = 1;
ALTER TABLE `credit_transactions` AUTO_INCREMENT = 1;
ALTER TABLE `login_rate_limits` AUTO_INCREMENT = 1;
ALTER TABLE `mockup_generations` AUTO_INCREMENT = 1;
ALTER TABLE `networks` AUTO_INCREMENT = 1;
ALTER TABLE `oauth_providers` AUTO_INCREMENT = 1;
ALTER TABLE `oauth_user_links` AUTO_INCREMENT = 1;
ALTER TABLE `orders` AUTO_INCREMENT = 1;
ALTER TABLE `order_bat_comments` AUTO_INCREMENT = 1;
ALTER TABLE `order_bat_requests` AUTO_INCREMENT = 1;
ALTER TABLE `order_documents` AUTO_INCREMENT = 1;
ALTER TABLE `order_items` AUTO_INCREMENT = 1;
ALTER TABLE `order_payments` AUTO_INCREMENT = 1;
ALTER TABLE `order_status_history` AUTO_INCREMENT = 1;
ALTER TABLE `payment_webhook_events` AUTO_INCREMENT = 1;
ALTER TABLE `price_policies` AUTO_INCREMENT = 1;
ALTER TABLE `product_packs` AUTO_INCREMENT = 1;
ALTER TABLE `product_pack_items` AUTO_INCREMENT = 1;
ALTER TABLE `promotion_charge_invoices` AUTO_INCREMENT = 1;
ALTER TABLE `promotion_charge_invoice_lines` AUTO_INCREMENT = 1;
ALTER TABLE `promotion_codes` AUTO_INCREMENT = 1;
ALTER TABLE `promotion_code_shops` AUTO_INCREMENT = 1;
ALTER TABLE `retrocession_policies` AUTO_INCREMENT = 1;
ALTER TABLE `retrocession_statements` AUTO_INCREMENT = 1;
ALTER TABLE `retrocession_statement_lines` AUTO_INCREMENT = 1;
ALTER TABLE `shipping_method_exclusions` AUTO_INCREMENT = 1;
ALTER TABLE `shipping_method_rates` AUTO_INCREMENT = 1;
ALTER TABLE `shipping_methods` AUTO_INCREMENT = 1;
ALTER TABLE `shipping_zones` AUTO_INCREMENT = 1;
ALTER TABLE `shops` AUTO_INCREMENT = 1;
ALTER TABLE `shop_domains` AUTO_INCREMENT = 1;
ALTER TABLE `shop_modules` AUTO_INCREMENT = 1;
ALTER TABLE `shop_products` AUTO_INCREMENT = 1;
ALTER TABLE `shop_product_assets` AUTO_INCREMENT = 1;
ALTER TABLE `storefront_cart_states` AUTO_INCREMENT = 1;
ALTER TABLE `storefront_checkout_states` AUTO_INCREMENT = 1;
ALTER TABLE `users` AUTO_INCREMENT = 1;
ALTER TABLE `user_network_memberships` AUTO_INCREMENT = 1;
ALTER TABLE `user_pack_assignments` AUTO_INCREMENT = 1;
ALTER TABLE `user_personalization_repiquages` AUTO_INCREMENT = 1;
ALTER TABLE `user_platform_roles` AUTO_INCREMENT = 1;
ALTER TABLE `user_shop_memberships` AUTO_INCREMENT = 1;

SET SQL_SAFE_UPDATES = @old_sql_safe_updates;

-- 11. Controle final rapide.
SELECT 'users' AS table_name, COUNT(*) AS rows_count, MIN(`id`) AS min_id, MAX(`id`) AS max_id FROM `users`
UNION ALL
SELECT 'shops', COUNT(*), MIN(`id`), MAX(`id`) FROM `shops`
UNION ALL
SELECT 'shop_domains', COUNT(*), MIN(`id`), MAX(`id`) FROM `shop_domains`
UNION ALL
SELECT 'networks', COUNT(*), MIN(`id`), MAX(`id`) FROM `networks`
UNION ALL
SELECT 'orders', COUNT(*), MIN(`id`), MAX(`id`) FROM `orders`
UNION ALL
SELECT 'order_documents', COUNT(*), MIN(`id`), MAX(`id`) FROM `order_documents`
UNION ALL
SELECT 'shop_products', COUNT(*), MIN(`id`), MAX(`id`) FROM `shop_products`
UNION ALL
SELECT 'credit_accounts', COUNT(*), MIN(`id`), MAX(`id`) FROM `credit_accounts`;

SELECT
  u.`id` AS user_id,
  u.`email`,
  s.`id` AS shop_id,
  s.`name` AS shop_name,
  sd.`domain` AS shop_domain
FROM `users` u
CROSS JOIN `shops` s
LEFT JOIN `shop_domains` sd ON sd.`shop_id` = s.`id`
WHERE BINARY u.`email` = BINARY @keep_user_email
  AND s.`id` = 1;
