-- Giữ hồ sơ học viên và CRM theo quan hệ 1-1.
-- An toàn khi chạy lại: stage dùng unique code, lead dùng unique student_id.

INSERT INTO `crm_stage_tb`
    (`code`,`name`,`color`,`stage_group`,`sort_order`,`is_system`,`status`,`user_created`,`date_created`)
VALUES
    ('student_waiting','Chờ xếp lớp','sky','student',35,1,1,'migration',NOW())
ON DUPLICATE KEY UPDATE
    `name`=VALUES(`name`),
    `color`=VALUES(`color`),
    `stage_group`=VALUES(`stage_group`),
    `sort_order`=VALUES(`sort_order`),
    `is_system`=1,
    `status`=1,
    `user_updated`='migration',
    `date_updated`=NOW();

SET @crm_student_waiting_stage_id := (
    SELECT `id` FROM `crm_stage_tb` WHERE `code`='student_waiting' LIMIT 1
);

SET @crm_studying_stage_id := (
    SELECT `id` FROM `crm_stage_tb` WHERE `code`='studying' LIMIT 1
);

-- Khôi phục các liên kết CRM từng bị soft-delete nhưng học viên vẫn hoạt động.
UPDATE `crm_lead_tb` AS l
INNER JOIN `student_tb` AS s ON s.id=l.student_id AND s.status<>3
SET
    l.stage_id=CASE
        WHEN s.class_id IS NULL OR TRIM(s.class_id)='' OR TRIM(s.class_id)='[]' OR TRIM(s.class_id)='null'
            THEN @crm_student_waiting_stage_id
        ELSE @crm_studying_stage_id
    END,
    l.status=1,
    l.converted_at=COALESCE(l.converted_at,NOW()),
    l.user_updated='student-sync-migration',
    l.date_updated=NOW()
WHERE l.status<>1;

-- Bù các học viên đang hoạt động nhưng chưa có hồ sơ CRM.
INSERT IGNORE INTO `crm_lead_tb`
    (`student_id`,`stage_id`,`assigned_member_id`,`last_activity_at`,`converted_at`,`status`,`user_created`,`date_created`)
SELECT
    s.id,
    CASE
        WHEN s.class_id IS NULL OR TRIM(s.class_id)='' OR TRIM(s.class_id)='[]' OR TRIM(s.class_id)='null'
            THEN @crm_student_waiting_stage_id
        ELSE @crm_studying_stage_id
    END,
    CASE
        WHEN JSON_VALID(s.multi_input)
            THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(s.multi_input,'$.consultant_id')) AS UNSIGNED)
        ELSE NULL
    END,
    NOW(),
    NOW(),
    1,
    'student-sync-migration',
    COALESCE(s.date_created,NOW())
FROM `student_tb` AS s
LEFT JOIN `crm_lead_tb` AS l ON l.student_id=s.id
WHERE s.status<>3 AND l.id IS NULL;

INSERT INTO `crm_activity_tb`
    (`lead_id`,`activity_type`,`content`,`to_stage_id`,`activity_at`,`status`,`user_created`,`date_created`)
SELECT
    l.id,
    'system',
    'Khởi tạo hồ sơ CRM từ dữ liệu học viên.',
    l.stage_id,
    NOW(),
    1,
    'student-sync-migration',
    NOW()
FROM `crm_lead_tb` AS l
WHERE l.status=1
  AND NOT EXISTS (
      SELECT 1 FROM `crm_activity_tb` AS a WHERE a.lead_id=l.id
  );

