CREATE TABLE IF NOT EXISTS crm_stage_tb (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  color VARCHAR(20) NOT NULL DEFAULT 'blue',
  stage_group VARCHAR(20) NOT NULL DEFAULT 'pipeline',
  sort_order INT NOT NULL DEFAULT 0,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  status TINYINT(1) NOT NULL DEFAULT 1,
  user_created VARCHAR(255) NULL,
  date_created DATETIME NOT NULL,
  user_updated VARCHAR(255) NULL,
  date_updated DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_crm_stage_code (code),
  UNIQUE KEY uq_crm_stage_name (name),
  KEY idx_crm_stage_status_sort (status, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS crm_source_tb (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  color VARCHAR(20) NOT NULL DEFAULT 'blue',
  note VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  status TINYINT(1) NOT NULL DEFAULT 1,
  user_created VARCHAR(255) NULL,
  date_created DATETIME NOT NULL,
  user_updated VARCHAR(255) NULL,
  date_updated DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_crm_source_name (name),
  KEY idx_crm_source_status_sort (status, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS crm_lead_tb (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id INT UNSIGNED NOT NULL,
  stage_id INT UNSIGNED NOT NULL,
  source_id INT UNSIGNED NULL,
  assigned_member_id INT UNSIGNED NULL,
  interest_class_type_id INT UNSIGNED NULL,
  learning_type VARCHAR(20) NULL,
  branch_id INT UNSIGNED NULL,
  next_follow_up_at DATETIME NULL,
  last_activity_at DATETIME NULL,
  converted_at DATETIME NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  user_created VARCHAR(255) NULL,
  date_created DATETIME NOT NULL,
  user_updated VARCHAR(255) NULL,
  date_updated DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_crm_lead_student (student_id),
  KEY idx_crm_lead_stage (stage_id, status),
  KEY idx_crm_lead_assigned (assigned_member_id, status),
  KEY idx_crm_lead_source (source_id, status),
  KEY idx_crm_lead_follow_up (next_follow_up_at, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS crm_activity_tb (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  lead_id INT UNSIGNED NOT NULL,
  activity_type VARCHAR(30) NOT NULL DEFAULT 'note',
  content TEXT NULL,
  from_stage_id INT UNSIGNED NULL,
  to_stage_id INT UNSIGNED NULL,
  activity_at DATETIME NOT NULL,
  member_id INT UNSIGNED NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  user_created VARCHAR(255) NULL,
  date_created DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_crm_activity_lead_time (lead_id, status, activity_at),
  KEY idx_crm_activity_member (member_id, activity_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS crm_appointment_tb (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  lead_id INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  start_at DATETIME NOT NULL,
  end_at DATETIME NULL,
  appointment_status VARCHAR(20) NOT NULL DEFAULT 'scheduled',
  assigned_member_id INT UNSIGNED NULL,
  note TEXT NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  user_created VARCHAR(255) NULL,
  date_created DATETIME NOT NULL,
  user_updated VARCHAR(255) NULL,
  date_updated DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_crm_appointment_start (start_at, status),
  KEY idx_crm_appointment_lead (lead_id, status),
  KEY idx_crm_appointment_member (assigned_member_id, start_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO crm_stage_tb (code,name,color,stage_group,sort_order,is_system,status,user_created,date_created) VALUES
('lead_new','Lead mới','blue','pipeline',10,1,1,'migration',NOW()),
('consulting','Đang tư vấn','orange','pipeline',20,1,1,'migration',NOW()),
('trial','Học thử','purple','pipeline',30,1,1,'migration',NOW()),
('studying','Đang học','green','student',40,1,1,'migration',NOW()),
('reserved','Bảo lưu','gray','student',50,1,1,'migration',NOW()),
('stopped','Đã dừng','red','lost',60,1,1,'migration',NOW());

INSERT IGNORE INTO crm_source_tb (name,color,note,sort_order,status,user_created,date_created) VALUES
('Fanpage Facebook','blue','Khách hàng từ Fanpage Facebook',10,1,'migration',NOW()),
('Website','green','Khách hàng từ website',20,1,'migration',NOW()),
('Giới thiệu','purple','Được học viên hoặc phụ huynh giới thiệu',30,1,'migration',NOW()),
('Trực tiếp','orange','Khách đến trực tiếp trung tâm',40,1,'migration',NOW()),
('Hotline','red','Khách gọi hotline',50,1,'migration',NOW()),
('Zalo','sky','Khách liên hệ qua Zalo',60,1,'migration',NOW());

INSERT IGNORE INTO crm_lead_tb
  (student_id,stage_id,assigned_member_id,converted_at,status,user_created,date_created)
SELECT s.id,
       CASE WHEN s.class_id IS NULL OR s.class_id='' OR s.class_id='[]'
            THEN (SELECT id FROM crm_stage_tb WHERE code='lead_new' LIMIT 1)
            ELSE (SELECT id FROM crm_stage_tb WHERE code='studying' LIMIT 1) END,
       CASE WHEN JSON_VALID(s.multi_input) THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(s.multi_input,'$.consultant_id')) AS UNSIGNED) ELSE NULL END,
       CASE WHEN s.class_id IS NULL OR s.class_id='' OR s.class_id='[]' THEN NULL ELSE COALESCE(s.date_created,NOW()) END,
       1,'migration',COALESCE(s.date_created,NOW())
FROM student_tb s WHERE s.status<>3;

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 hiện có.',l.stage_id,l.date_created,1,'migration',NOW()
FROM crm_lead_tb l
WHERE NOT EXISTS (SELECT 1 FROM crm_activity_tb a WHERE a.lead_id=l.id);