# EMS Questions Import - Hướng dẫn sử dụng

## ✅ **ĐÃ TẠO XONG!**

### **1. Database Schema:**

**Bảng:** `ems_questions` (1 bảng duy nhất cho 380K+ questions)

**Cấu trúc:**

| Column | Type | Description |
|--------|------|-------------|
| `id` | BIGINT | Primary key (auto increment) |
| `idTenant` | INT | Company Share ID từ EMS (_companyShareIdKey) |
| `ems_question_id` | INT (UNIQUE) | ID câu hỏi từ EMS (idQuestion) |
| `name` | VARCHAR(500) | Tên câu hỏi |
| `question_data` | JSON | Toàn bộ data từ EMS API |
| `data_import_prepare` | JSON | Data chuẩn bị trước khi update lên EMS |
| `ems_response` | JSON | Response từ EMS sau khi update |
| `status` | TINYINT | 0-4 (xem bên dưới) |
| `error_message` | TEXT | Lỗi nếu có |
| `created_at` | TIMESTAMP | Thời gian tạo |
| `updated_at` | TIMESTAMP | Thời gian cập nhật |

**Indexes:**
- `ems_question_id` (UNIQUE)
- `status`
- `idTenant` + `status`
- `created_at`
- `status` + `updated_at`

---

### **2. Status Values:**

| Value | Constant | Ý nghĩa |
|-------|----------|---------|
| 0 | `STATUS_NEW` | Mới tạo, chưa xử lý |
| 1 | `STATUS_FORMATTED` | Đã format dữ liệu |
| 2 | `STATUS_UPDATING` | Đang update lên EMS |
| 3 | `STATUS_SUCCESS` | Update thành công |
| 4 | `STATUS_ERROR` | Update lỗi |

---

### **3. Model:** `App\Models\EmsQuestion`

**Fillable fields:**
```php
[
    'idTenant',
    'ems_question_id',
    'name',
    'question_data',
    'data_import_prepare',
    'ems_response',
    'status',
    'error_message',
]
```

**Helper methods:**
```php
// Scopes
EmsQuestion::pending()      // Status = 0
EmsQuestion::formatted()    // Status = 1
EmsQuestion::errors()       // Status = 4
EmsQuestion::success()      // Status = 3

// Status management
$question->markAsFormatted($data);
$question->markAsUpdating();
$question->markAsSuccess($response);
$question->markAsError($message);

// Checks
$question->hasError()
$question->isCompleted()

// Attributes
$question->status_name  // "Mới tạo", "Đã format", etc.
```

---

## 🚀 **APIs ĐÃ TẠO**

### **1. Get Question Total**

**Endpoint:**
```
GET /api/speakup/question-total
```

**Response:**
```json
{
    "status": true,
    "data": {
        "total": 380246,
        "idLastQuestion": 381857
    }
}
```

---

### **2. Get Question Info**

**Endpoint:**
```
GET /api/speakup/question-info?idQuestion=246415
```

**Response:**
```json
{
    "status": true,
    "data": {
        "_companyShareIdKey": 1,
        "name": "24_25_PEN-I_Địa lí_Đề 02_ĐS_04",
        "idQuestion": 309784,
        // ... toàn bộ thông tin câu hỏi
    }
}
```

---

### **3. Import Single Question** ⭐ **NEW**

**Endpoint:**
```
POST /api/speakup/import-question?idQuestion=246415
```

**Request:**
```bash
curl -X POST 'http://your-domain.com/api/speakup/import-question?idQuestion=246415'
```

**Response Success:**
```json
{
    "status": true,
    "message": "Question imported successfully",
    "data": {
        "id": 1,
        "ems_question_id": 246415,
        "name": "24_25_PEN-I_Địa lí_Đề 02_ĐS_04",
        "status": 0,
        "status_name": "Mới tạo",
        "created_at": "2025-11-14T10:00:00.000000Z"
    }
}
```

**Response - Already exists (409):**
```json
{
    "status": false,
    "message": "Question already exists in database",
    "data": {
        "id": 1,
        "ems_question_id": 246415,
        "status": 0,
        "status_name": "Mới tạo"
    }
}
```

---

### **4. Import Batch Questions** ⭐ **NEW**

**Endpoint:**
```
POST /api/speakup/import-questions-batch
```

**Request:**
```bash
curl -X POST 'http://your-domain.com/api/speakup/import-questions-batch' \
  -H 'Content-Type: application/json' \
  -d '{
    "start_id": 246415,
    "end_id": 246514,
    "batch_size": 50
  }'
```

**Parameters:**
- `start_id` (required): ID bắt đầu
- `end_id` (required): ID kết thúc
- `batch_size` (optional): Số lượng mỗi batch, default 100
- **Maximum:** 1000 questions per request

**Response:**
```json
{
    "status": true,
    "message": "Batch import completed",
    "data": {
        "total_processed": 100,
        "imported": 95,
        "skipped": 3,
        "errors": 2,
        "start_id": 246415,
        "end_id": 246514
    }
}
```

---

### **5. Get Import Statistics** ⭐ **NEW**

**Endpoint:**
```
GET /api/speakup/import-stats
```

**Response:**
```json
{
    "status": true,
    "data": {
        "total": 1500,
        "by_status": {
            "new": 100,
            "formatted": 500,
            "updating": 50,
            "success": 800,
            "error": 50
        },
        "by_tenant": {
            "1": 1200,
            "2": 300
        },
        "recent_imports": [
            {
                "id": 1500,
                "ems_question_id": 246515,
                "name": "Question name...",
                "status": 0,
                "status_name": "Mới tạo",
                "created_at": "2025-11-14T10:00:00.000000Z"
            }
            // ... 9 questions khác
        ]
    }
}
```

---

## 📝 **SETUP INSTRUCTIONS**

### **Bước 1: Run Migration**

```bash
cd /var/www/html/lms_hocmai

# Run migration
php artisan migrate

# Output:
# Migrating: 2025_11_14_031414_create_ems_questions_table
# Migrated:  2025_11_14_031414_create_ems_questions_table
```

---

### **Bước 2: Verify Table**

```bash
# Check table created
php artisan tinker
>>> Schema::hasTable('ems_questions')
=> true

>>> DB::table('ems_questions')->count()
=> 0
```

---

### **Bước 3: Test Import**

```bash
# Import 1 câu hỏi
curl -X POST 'http://your-domain.com/api/speakup/import-question?idQuestion=246415'

# Import batch (100 câu hỏi)
curl -X POST 'http://your-domain.com/api/speakup/import-questions-batch' \
  -H 'Content-Type: application/json' \
  -d '{
    "start_id": 246415,
    "end_id": 246514,
    "batch_size": 50
  }'

# Check statistics
curl 'http://your-domain.com/api/speakup/import-stats'
```

---

## 🔄 **WORKFLOW ĐỂ IMPORT TẤT CẢ 380K QUESTIONS**

### **Chiến lược: Chia nhỏ thành batches**

```
Total: 380,361 questions (ID từ 1 đến 381857)

Chia thành batches:
- Batch 1:   1 -   1,000  (1,000 questions)
- Batch 2:   1,001 - 2,000  (1,000 questions)
- Batch 3:   2,001 - 3,000  (1,000 questions)
...
- Batch 382: 381,001 - 381,857 (857 questions)

Total: 382 batches
```

---

### **Script để import tự động:**

```bash
#!/bin/bash
# import_all_questions.sh

START_ID=1
END_ID=381857
BATCH_SIZE=1000
API_URL="http://your-domain.com/api/speakup/import-questions-batch"

for ((start=$START_ID; start<=$END_ID; start+=$BATCH_SIZE)); do
    end=$((start + BATCH_SIZE - 1))
    
    if [ $end -gt $END_ID ]; then
        end=$END_ID
    fi
    
    echo "Importing questions $start to $end..."
    
    curl -X POST "$API_URL" \
      -H 'Content-Type: application/json' \
      -d "{
        \"start_id\": $start,
        \"end_id\": $end,
        \"batch_size\": 50
      }"
    
    echo ""
    echo "Batch completed. Waiting 5 seconds..."
    sleep 5
done

echo "All questions imported!"
```

**Chạy script:**
```bash
chmod +x import_all_questions.sh
./import_all_questions.sh
```

**Thời gian ước tính:**
- 382 batches × (10s import + 5s sleep) = ~95 phút (1.5 giờ)

---

### **Hoặc dùng Laravel Queue (Recommended):**

Tôi có thể tạo Job để import background, bạn có muốn không?

---

## 📊 **QUERY EXAMPLES**

### **Lấy questions theo status:**

```php
// Lấy questions mới tạo
$newQuestions = EmsQuestion::pending()->get();

// Lấy questions có lỗi
$errorQuestions = EmsQuestion::errors()->get();

// Lấy questions thành công
$successQuestions = EmsQuestion::success()->get();
```

---

### **Lấy questions theo tenant:**

```php
$tenant1Questions = EmsQuestion::byTenant(1)->get();
```

---

### **Update status:**

```php
$question = EmsQuestion::find(1);

// Mark as formatted
$question->markAsFormatted([
    'prepared_data' => '...'
]);

// Mark as success
$question->markAsSuccess([
    'ems_response' => '...'
]);

// Mark as error
$question->markAsError('Connection timeout');
```

---

### **Statistics:**

```php
// Count by status
$stats = [
    'total' => EmsQuestion::count(),
    'pending' => EmsQuestion::pending()->count(),
    'formatted' => EmsQuestion::formatted()->count(),
    'success' => EmsQuestion::success()->count(),
    'errors' => EmsQuestion::errors()->count(),
];
```

---

## 🎯 **TẠI SAO 1 BẢNG LÀ TỐT?**

### **Performance với 380K records:**

```sql
-- Query by ems_question_id (với index unique)
SELECT * FROM ems_questions WHERE ems_question_id = 246415;
-- Execution time: <1ms ✅

-- Query by status (với index)
SELECT * FROM ems_questions WHERE status = 0 LIMIT 100;
-- Execution time: <10ms ✅

-- Count total
SELECT COUNT(*) FROM ems_questions;
-- Execution time: <50ms ✅

-- Pagination
SELECT * FROM ems_questions 
WHERE status = 0 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 1000;
-- Execution time: <10ms ✅
```

**Kết luận:** 380K records = RẤT NHỎ, performance EXCELLENT!

---

### **Database Size:**

```
380,000 questions × 5KB avg = 1.9GB
+ Indexes: ~500MB
Total: ~2.5GB

So sánh:
- Disk space hiện đại: TB+
- 2.5GB = 0.25% của 1TB
- → KHÔNG VẤN ĐỀ GÌ!
```

---

### **4 bảng vs 1 bảng:**

```
┌─────────────────────┬──────────────┬──────────────┐
│ Aspect              │ 4 Bảng       │ 1 Bảng ⭐     │
├─────────────────────┼──────────────┼──────────────┤
│ Code complexity     │ ⭐           │ ⭐⭐⭐⭐⭐     │
│ Query performance   │ ⭐⭐⭐        │ ⭐⭐⭐⭐⭐     │
│ Maintenance         │ ⭐           │ ⭐⭐⭐⭐⭐     │
│ Migration           │ ⭐           │ ⭐⭐⭐⭐⭐     │
│ Backup              │ ⭐⭐          │ ⭐⭐⭐⭐⭐     │
│ Scalability         │ ⭐⭐          │ ⭐⭐⭐⭐⭐     │
└─────────────────────┴──────────────┴──────────────┘

VERDICT: 1 bảng TỐT HƠN NHIỀU!
```

---

## 🔧 **TROUBLESHOOTING**

### **Lỗi: "Table already exists"**

```bash
# Rollback migration
php artisan migrate:rollback

# Run lại
php artisan migrate
```

---

### **Lỗi: "Question already exists"**

Bình thường! API sẽ skip question đã tồn tại.

---

### **Lỗi: "Could not get access token"**

Kiểm tra `.env`:
```bash
EMS_API_DOMAIN_EMAIL_LOGIN=your_email
EMS_API_DOMAIN_PASSWORD_LOGIN=your_password
```

---

## 📈 **MONITORING**

### **Check progress:**

```bash
# Total imported
curl 'http://your-domain.com/api/speakup/import-stats'

# Check database
php artisan tinker
>>> EmsQuestion::count()
>>> EmsQuestion::pending()->count()
>>> EmsQuestion::errors()->count()
```

---

### **View logs:**

```bash
tail -f storage/logs/laravel.log | grep -E "import|Question"
```

---

## ✅ **NEXT STEPS**

### **Sau khi import:**

1. **Format data:**
   ```php
   $questions = EmsQuestion::pending()->get();
   foreach ($questions as $q) {
       $preparedData = formatQuestionData($q->question_data);
       $q->markAsFormatted($preparedData);
   }
   ```

2. **Update lên EMS:**
   ```php
   $questions = EmsQuestion::formatted()->get();
   foreach ($questions as $q) {
       $q->markAsUpdating();
       $response = updateToEms($q->data_import_prepare);
       $q->markAsSuccess($response);
   }
   ```

3. **Retry errors:**
   ```php
   $errorQuestions = EmsQuestion::errors()->get();
   // Process lại
   ```

---

## 🎉 **TÓM TẮT**

✅ **Migration:** `database/migrations/*_create_ems_questions_table.php`  
✅ **Model:** `app/Models/EmsQuestion.php`  
✅ **Controller:** `SpeakupImportController` (3 APIs mới)  
✅ **Routes:** `/api/speakup/*`  

**APIs:**
1. `GET /api/speakup/question-total` - Lấy tổng số
2. `GET /api/speakup/question-info?idQuestion=X` - Lấy thông tin
3. `POST /api/speakup/import-question?idQuestion=X` - Import 1 câu hỏi
4. `POST /api/speakup/import-questions-batch` - Import batch
5. `GET /api/speakup/import-stats` - Thống kê

**Bước tiếp theo:**
```bash
# 1. Run migration
php artisan migrate

# 2. Test import 1 question
curl -X POST 'http://your-domain.com/api/speakup/import-question?idQuestion=246415'

# 3. Test import batch
curl -X POST 'http://your-domain.com/api/speakup/import-questions-batch' \
  -d '{"start_id": 1, "end_id": 100}'

# 4. Check stats
curl 'http://your-domain.com/api/speakup/import-stats'
```

**🚀 Sẵn sàng import 380K questions!**