-- ФИКС: Очистка linked_user_id у TG-пользователей
-- Проблема: messenger_platform может быть NULL у TG-юзеров,
-- поэтому предыдущая миграция не захватила их.
--
-- Run: mysql -u kadrum_admin -p dev_kadrum < migrations/20260318_fix_linked_users.sql

-- ────────────────────────────────────────────────────────────────────
-- 0) Диагностика: TG-юзеры с linked_user_id → MAX (включая NULL platform)
-- ────────────────────────────────────────────────────────────────────

SELECT u_tg.id AS tg_id, u_tg.name AS tg_name,
       u_tg.messenger_platform AS tg_platform,
       u_tg.balance AS tg_balance,
       u_max.id AS max_id, u_max.name AS max_name,
       u_max.balance AS max_balance,
       u_max.linked_user_id AS max_linked
FROM `user` u_tg
JOIN `user` u_max ON u_max.id = u_tg.linked_user_id
WHERE (u_tg.messenger_platform = 'telegram' OR u_tg.messenger_platform IS NULL OR u_tg.messenger_platform = '')
  AND u_max.messenger_platform = 'max'
  AND u_tg.linked_user_id IS NOT NULL;

-- ────────────────────────────────────────────────────────────────────
-- 1) Переносим баланс MAX → TG (если ещё не перенесён)
-- ────────────────────────────────────────────────────────────────────

-- 1a) Начисляем TG-юзеру
INSERT INTO `credits_ledger` (`user_id`, `delta`, `reason`, `comment`, `created_at`)
SELECT u_tg.id,
       u_max.balance,
       'link_transfer',
       CONCAT('Fix linked_user_id: перенос баланса из MAX #', u_max.id),
       NOW()
FROM `user` u_tg
JOIN `user` u_max ON u_max.id = u_tg.linked_user_id
WHERE (u_tg.messenger_platform = 'telegram' OR u_tg.messenger_platform IS NULL OR u_tg.messenger_platform = '')
  AND u_max.messenger_platform = 'max'
  AND u_tg.linked_user_id IS NOT NULL
  AND u_max.balance > 0;

-- 1b) Списываем с MAX
INSERT INTO `credits_ledger` (`user_id`, `delta`, `reason`, `comment`, `created_at`)
SELECT u_max.id,
       -u_max.balance,
       'link_transfer',
       CONCAT('Fix linked_user_id: перенос баланса в TG #', u_tg.id),
       NOW()
FROM `user` u_tg
JOIN `user` u_max ON u_max.id = u_tg.linked_user_id
WHERE (u_tg.messenger_platform = 'telegram' OR u_tg.messenger_platform IS NULL OR u_tg.messenger_platform = '')
  AND u_max.messenger_platform = 'max'
  AND u_tg.linked_user_id IS NOT NULL
  AND u_max.balance > 0;

-- 1c) Пересчёт баланса TG
UPDATE `user` u_tg
JOIN (
    SELECT u.id AS tg_id
    FROM `user` u
    JOIN `user` u_max ON u_max.id = u.linked_user_id
    WHERE (u.messenger_platform = 'telegram' OR u.messenger_platform IS NULL OR u.messenger_platform = '')
      AND u_max.messenger_platform = 'max'
      AND u.linked_user_id IS NOT NULL
) affected ON affected.tg_id = u_tg.id
JOIN (
    SELECT user_id, COALESCE(SUM(delta), 0) AS total
    FROM `credits_ledger`
    GROUP BY user_id
) ledger ON ledger.user_id = u_tg.id
SET u_tg.balance = ledger.total;

-- 1d) Пересчёт баланса MAX
UPDATE `user` u_max
JOIN (
    SELECT u_m.id AS max_id
    FROM `user` u_m
    JOIN `user` u_t ON u_t.linked_user_id = u_m.id
    WHERE (u_t.messenger_platform = 'telegram' OR u_t.messenger_platform IS NULL OR u_t.messenger_platform = '')
      AND u_m.messenger_platform = 'max'
) affected ON affected.max_id = u_max.id
JOIN (
    SELECT user_id, COALESCE(SUM(delta), 0) AS total
    FROM `credits_ledger`
    GROUP BY user_id
) ledger ON ledger.user_id = u_max.id
SET u_max.balance = ledger.total;

-- ────────────────────────────────────────────────────────────────────
-- 2) Ставим linked_user_id на MAX (если ещё не стоит)
-- ────────────────────────────────────────────────────────────────────

UPDATE `user` u_max
JOIN `user` u_tg ON u_tg.linked_user_id = u_max.id
SET u_max.linked_user_id = u_tg.id
WHERE (u_tg.messenger_platform = 'telegram' OR u_tg.messenger_platform IS NULL OR u_tg.messenger_platform = '')
  AND u_max.messenger_platform = 'max'
  AND u_tg.linked_user_id IS NOT NULL
  AND (u_max.linked_user_id IS NULL OR u_max.linked_user_id = 0);

-- ────────────────────────────────────────────────────────────────────
-- 3) Обнуляем linked_user_id у всех не-MAX юзеров, указывающих на MAX
-- ────────────────────────────────────────────────────────────────────

UPDATE `user` u_tg
SET u_tg.linked_user_id = NULL
WHERE u_tg.linked_user_id IS NOT NULL
  AND (u_tg.messenger_platform != 'max' OR u_tg.messenger_platform IS NULL)
  AND EXISTS (
      SELECT 1 FROM (SELECT id FROM `user` WHERE messenger_platform = 'max') AS u_max
      WHERE u_max.id = u_tg.linked_user_id
  );

-- ────────────────────────────────────────────────────────────────────
-- 4) Проверка
-- ────────────────────────────────────────────────────────────────────

-- Не-MAX юзеры с linked_user_id → MAX (должно быть 0):
SELECT COUNT(*) AS remaining
FROM `user` u
JOIN `user` u_max ON u_max.id = u.linked_user_id AND u_max.messenger_platform = 'max'
WHERE (u.messenger_platform != 'max' OR u.messenger_platform IS NULL);
