<?php
/**
 * AI問い合わせ自動分類・ルーティングシステム
 * 
 * お問い合わせフォームから登録されたデータをAIで分析し、
 * カテゴリ分類、緊急度判定、感情分析、LTV予測、エスカレーション判定を行い、
 * 適切な担当者に自動ルーティングするシステム
 * 
 * SPIRAL ver.2用 - DBトリガの非同期アクションとして設置
 */

// ============================================================
// 設定
// ============================================================

define('OPENAI_API_KEY', 'sk-xxxxxxxxxxxxxxxxxxxxxxxx'); // OpenAI APIキー
define('OPENAI_MODEL', 'gpt-5.2');                    // OpenAI モデル名
define('SPIRAL_API_TOKEN', 'xxxxxxxxxxxxxxxxxxxxxxxx');   // SPIRAL APIトークン
define('SPIRAL_API_URL', 'https://api.spiral-platform.com/v1');
define('APP_ID', 'your_app_id');
define('INQUIRY_DB_ID', 'your_inquiry_db_id');
define('STAFF_DB_ID', 'your_staff_db_id');

// ============================================================
// メイン処理
// ============================================================

function main() {
    global $SPIRAL;
    
    // DBトリガから渡されるレコードデータを取得
    // $SPIRAL->getRecord() を使用（ver.2のDBトリガPHP実行アクション）
    $triggerData = $SPIRAL->getRecord();
    
    if (!$triggerData || !isset($triggerData['item'])) {
        return;
    }
    
    $record = $triggerData['item'];
    $options = $triggerData['options'] ?? [];
    $recordId = $record['_id'] ?? '';
    $content = $record['content'] ?? '';
    $customerName = $record['customer_name'] ?? '';
    $companyName = $record['company'] ?? '';
    $email = $record['email'] ?? '';
    
    // 内容が空の場合はスキップ
    if (empty(trim($content))) {
        return;
    }
    
    // 過去のお問い合わせ履歴を取得（同一メールアドレス）
    $customerHistory = [];
    if (!empty($email)) {
        $customerHistory = getCustomerHistory($email, $recordId);
    }
    
    // AI分析を実行
    $analysisResult = analyzeInquiry($content, $customerName, $companyName, $customerHistory);
    
    // 担当者マスタのoptions（カテゴリのラベル→ID変換用）を取得
    $staffOptions = getStaffDbOptions();
    
    // ルーティング（担当者決定）- 負荷分散機能付き
    $routing = routeWithLoadBalancing(
        $analysisResult['category'],
        $analysisResult['urgency'],
        $analysisResult['ltv_score'],
        $staffOptions
    );
    
    // エスカレーション判定
    $escalation = checkEscalation(
        $analysisResult['category'],
        $analysisResult['urgency'],
        $analysisResult['sentiment'],
        $analysisResult['ltv_score'],
        $analysisResult['churn_risk'],
        $analysisResult['escalation_reason']
    );
    
    // DB更新用データを準備
    
    // optionsからラベル→値の変換マップを動的に生成
    $categoryMap = array_flip($options['category'] ?? []);
    $urgencyMap = array_flip($options['urgency'] ?? []);
    $sentimentMap = array_flip($options['sentiment'] ?? []);
    $escalationMap = array_flip($options['escalation'] ?? []);
    $statusMap = array_flip($options['status'] ?? []);
    
    $updateData = [
        'category' => (string)($categoryMap[$analysisResult['category']] ?? array_key_first($options['category'] ?? [])),
        'urgency' => (string)($urgencyMap[$analysisResult['urgency']] ?? array_key_first($options['urgency'] ?? [])),
        'sentiment' => (string)($sentimentMap[$analysisResult['sentiment']] ?? array_key_first($options['sentiment'] ?? [])),
        'ltv_score' => (string)$analysisResult['ltv_score'],
        'escalation' => (string)($escalationMap[$escalation['required'] ? '要' : '不要'] ?? array_key_first($options['escalation'] ?? [])),
        'assignee_id' => $routing['assignee_id'],
        'ai_analysis' => json_encode([
            'summary' => $analysisResult['summary'],
            'suggested_response_points' => $analysisResult['suggested_response_points'],
            'keywords' => $analysisResult['keywords'],
            'churn_risk' => $analysisResult['churn_risk'],
            'upsell_opportunity' => $analysisResult['upsell_opportunity'],
            'escalation_reason' => $escalation['reason'],
            'escalation_level' => $escalation['level'],
            'routing_reason' => $routing['reason'],
            'analyzed_at' => date('Y-m-d H:i:s')
        ], JSON_UNESCAPED_UNICODE),
        'status' => (string)($statusMap['未対応'] ?? array_key_first($options['status'] ?? []))
    ];
    
    // SPIRAL APIでレコード更新
    $updateResult = updateRecord($recordId, $updateData);
    
    if (!$updateResult) {
        return;
    }
    
    // 担当者の対応件数を更新（負荷分散用）
    if (!empty($routing['assignee_id'])) {
        incrementStaffLoad($routing['assignee_id']);
    }
    
}

// ============================================================
// AI分析機能
// ============================================================

/**
 * お問い合わせ内容をAIで分析
 */
function analyzeInquiry($content, $customerName = '', $companyName = '', $customerHistory = []) {
    $prompt = buildAnalysisPrompt($content, $customerName, $companyName, $customerHistory);
    $response = callOpenAI($prompt);
    
    if ($response === false) {
        return getDefaultAnalysisResult();
    }
    
    return parseAnalysisResponse($response);
}

/**
 * 分析用プロンプトを構築
 */
function buildAnalysisPrompt($content, $customerName, $companyName, $customerHistory) {
    $systemPrompt = <<<EOT
あなたはお問い合わせ分析AIです。以下の情報を分析し、JSON形式で結果を返してください。

## 分析項目

1. category（カテゴリ）: 以下から1つ選択
   - "技術": 技術的な質問、バグ報告、使い方の質問
   - "営業": 見積もり依頼、導入相談、デモ依頼
   - "クレーム": 不満、苦情、解約示唆
   - "その他": 上記に該当しないもの

2. urgency（緊急度）: 以下から1つ選択
   - "高": 本番障害、業務停止、即時対応必要
   - "中": 当日〜翌日対応が望ましい
   - "低": 通常対応で問題なし

3. sentiment（感情）: 以下から1つ選択
   - "ポジティブ": 好意的、感謝、期待
   - "ニュートラル": 中立的、事務的
   - "ネガティブ": 不満、怒り、失望

4. ltv_score（LTV予測スコア）: 0-100の整数
   - 企業規模、予算言及、長期利用意向などから予測

5. escalation_reason（エスカレーション理由）: 
   - エスカレーションが必要な場合はその理由、不要な場合は空文字

6. summary（要約）: 50文字以内でお問い合わせの要約

7. suggested_response_points（回答のポイント）: 
   - 回答時に含めるべきポイントを箇条書きで

8. keywords（キーワード）:
   - お問い合わせから抽出した重要キーワード（最大5つ）

9. churn_risk（解約リスク）: 0-100の整数

10. upsell_opportunity（アップセル機会）: 0-100の整数

## 出力形式
必ず以下のJSON形式で出力してください：
{
  "category": "技術",
  "urgency": "中",
  "sentiment": "ニュートラル",
  "ltv_score": 50,
  "escalation_reason": "",
  "summary": "APIの認証方法についての質問",
  "suggested_response_points": ["認証フローの説明", "サンプルコードの提供"],
  "keywords": ["API", "認証", "トークン"],
  "churn_risk": 10,
  "upsell_opportunity": 30
}
EOT;

    $userPrompt = "## お問い合わせ情報\n\n";
    if ($customerName) {
        $userPrompt .= "お客様名: {$customerName}\n";
    }
    if ($companyName) {
        $userPrompt .= "会社名: {$companyName}\n";
    }
    
    // 過去の履歴がある場合は追加
    if (!empty($customerHistory)) {
        $userPrompt .= "\n## 過去のお問い合わせ履歴（直近3件）\n";
        foreach (array_slice($customerHistory, 0, 3) as $history) {
            $userPrompt .= "- [{$history['date']}] {$history['category']}: {$history['summary']}\n";
        }
    }
    
    $userPrompt .= "\n## 今回のお問い合わせ内容\n{$content}";
    
    return [
        'system' => $systemPrompt,
        'user' => $userPrompt
    ];
}

/**
 * OpenAI APIを呼び出し
 */
function callOpenAI($prompt) {
    $data = [
        'model' => OPENAI_MODEL,
        'messages' => [
            ['role' => 'system', 'content' => $prompt['system']],
            ['role' => 'user', 'content' => $prompt['user']]
        ],
        'temperature' => 0.3,
        'response_format' => ['type' => 'json_object']
    ];
    
    $ch = curl_init('https://api.openai.com/v1/chat/completions');
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST => true,
        CURLOPT_HTTPHEADER => [
            'Content-Type: application/json',
            'Authorization: Bearer ' . OPENAI_API_KEY
        ],
        CURLOPT_POSTFIELDS => json_encode($data),
        CURLOPT_TIMEOUT => 30
    ]);
    
    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    
    if ($httpCode !== 200) {
        return false;
    }
    
    $result = json_decode($response, true);
    if (isset($result['choices'][0]['message']['content'])) {
        return $result['choices'][0]['message']['content'];
    }
    
    return false;
}

/**
 * AI分析レスポンスをパース
 */
function parseAnalysisResponse($response) {
    $data = json_decode($response, true);
    
    if (json_last_error() !== JSON_ERROR_NONE) {
        return getDefaultAnalysisResult();
    }
    
    $validCategories = ['技術', '営業', 'クレーム', 'その他'];
    $validUrgencies = ['高', '中', '低'];
    $validSentiments = ['ポジティブ', 'ニュートラル', 'ネガティブ'];
    
    return [
        'category' => in_array($data['category'] ?? '', $validCategories) 
            ? $data['category'] : 'その他',
        'urgency' => in_array($data['urgency'] ?? '', $validUrgencies) 
            ? $data['urgency'] : '中',
        'sentiment' => in_array($data['sentiment'] ?? '', $validSentiments) 
            ? $data['sentiment'] : 'ニュートラル',
        'ltv_score' => max(0, min(100, intval($data['ltv_score'] ?? 50))),
        'escalation_reason' => $data['escalation_reason'] ?? '',
        'summary' => mb_substr($data['summary'] ?? '', 0, 100),
        'suggested_response_points' => $data['suggested_response_points'] ?? [],
        'keywords' => $data['keywords'] ?? [],
        'churn_risk' => max(0, min(100, intval($data['churn_risk'] ?? 0))),
        'upsell_opportunity' => max(0, min(100, intval($data['upsell_opportunity'] ?? 0)))
    ];
}

/**
 * デフォルト分析結果（API失敗時）
 */
function getDefaultAnalysisResult() {
    return [
        'category' => 'その他',
        'urgency' => '中',
        'sentiment' => 'ニュートラル',
        'ltv_score' => 50,
        'escalation_reason' => '',
        'summary' => 'AI分析に失敗しました',
        'suggested_response_points' => [],
        'keywords' => [],
        'churn_risk' => 0,
        'upsell_opportunity' => 0
    ];
}

// ============================================================
// ルーティング機能
// ============================================================

/**
 * カテゴリと部署のマッピング
 */
function getCategoryDepartmentMap() {
    return [
        '技術' => 'テクニカルサポート',
        '営業' => '営業部',
        'クレーム' => 'カスタマーサポート',
        'その他' => 'カスタマーサポート'
    ];
}

/**
 * 負荷分散を考慮したルーティング
 */
function routeWithLoadBalancing($category, $urgency, $ltvScore, $staffOptions = []) {
    $departmentMap = getCategoryDepartmentMap();
    $department = $departmentMap[$category] ?? $departmentMap['その他'];
    
    // VIP閾値
    $vipThreshold = 70;
    
    // 担当者マスタから候補を取得
    $candidates = getRoutingCandidates($category, $urgency, $ltvScore, $vipThreshold, $staffOptions);
    
    if (empty($candidates)) {
        return [
            'department' => $department,
            'assignee_id' => '',
            'assignee_name' => '未割当',
            'assignee_email' => '',
            'reason' => "カテゴリ「{$category}」に対応可能な担当者が見つかりませんでした"
        ];
    }
    
    // 負荷が最も低い担当者を選択を動作させる
    $selectedStaff = selectStaffByLoad($candidates);
    $reason = buildRoutingReason($category, $urgency, $ltvScore, $selectedStaff, $vipThreshold);
    
    return [
        'department' => $department,
        'assignee_id' => $selectedStaff['_id'],
        'assignee_name' => $selectedStaff['staff_name'],
        'assignee_email' => $selectedStaff['staff_email'],
        'reason' => $reason
    ];
}

/**
 * ルーティング候補を取得
 */
function getRoutingCandidates($category, $urgency, $ltvScore, $vipThreshold, $options = []) {
    $url = SPIRAL_API_URL . '/apps/' . APP_ID . '/dbs/' . STAFF_DB_ID . '/records';
    
    // カテゴリのラベルからIDを取得
    $categoryOptions = $options['categories'] ?? [];
    $categoryMap = array_flip($categoryOptions);
    $categoryId = $categoryMap[$category] ?? null;
    
    if (!$categoryId) {
        return [];
    }
    
    // where条件を構築（マルチセレクトフィールドの検索）
    // @categories ANYCONTAINS('2') で「営業」カテゴリを含む担当者を検索
    $where = "@categories ANYCONTAINS('{$categoryId}')";
    
    // 緊急度が高い、またはVIP顧客の場合はエスカレーション権限ありを優先
    if ($urgency === '高' || $ltvScore >= $vipThreshold) {
        $where .= " AND @can_escalate = '1'";
    }
    
    $params = [
        'where' => $where,
        'sort' => 'current_load:asc',
        'limit' => 10
    ];
    
    $response = callSpiralApi('GET', $url, $params);
    
    if (!$response || empty($response['items'])) {
        $where = "@categories ANYCONTAINS('{$categoryId}')";
        $params['where'] = $where;
        $response = callSpiralApi('GET', $url, $params);
    }
    
    return $response['items'] ?? [];
}

/**
 * 負荷が最も低い担当者を選択
 */
function selectStaffByLoad($candidates) {
    $minLoad = $candidates[0]['current_load'] ?? 0;
    $sameLoadCandidates = array_filter($candidates, function($c) use ($minLoad) {
        return ($c['current_load'] ?? 0) === $minLoad;
    });
    
    return $sameLoadCandidates[array_rand($sameLoadCandidates)];
}

/**
 * ルーティング理由を構築
 */
function buildRoutingReason($category, $urgency, $ltvScore, $staff, $vipThreshold) {
    $reasons = [];
    $reasons[] = "カテゴリ「{$category}」";
    
    if ($ltvScore >= $vipThreshold) {
        $reasons[] = "VIP顧客（LTVスコア: {$ltvScore}）";
    }
    
    if ($urgency === '高') {
        $reasons[] = "緊急度「高」";
    }
    
    $load = $staff['current_load'] ?? 0;
    $reasons[] = "現在の対応件数: {$load}件";
    
    return implode('、', $reasons) . " → {$staff['staff_name']}を選定";
}

// ============================================================
// エスカレーション判定機能
// ============================================================

/**
 * エスカレーション判定を実行
 */
function checkEscalation($category, $urgency, $sentiment, $ltvScore, $churnRisk, $aiEscalationReason = '') {
    // 閾値設定
    $thresholds = [
        'ltv_vip' => 80,
        'ltv_high_value' => 60,
        'churn_risk_high' => 70,
        'churn_risk_medium' => 40
    ];
    
    $reasons = [];
    $level = 0;
    
    // === レベル3（最高緊急度）===
    if ($ltvScore >= $thresholds['ltv_vip'] && $category === 'クレーム' && $urgency === '高') {
        $reasons[] = 'VIP顧客の緊急クレーム';
        $level = max($level, 3);
    }
    
    if ($ltvScore >= $thresholds['ltv_vip'] && $churnRisk >= $thresholds['churn_risk_high']) {
        $reasons[] = 'VIP顧客の解約リスク高';
        $level = max($level, 3);
    }
    
    // === レベル2 ===
    if ($category === 'クレーム' && $urgency === '高') {
        $reasons[] = '緊急クレーム';
        $level = max($level, 2);
    }
    
    if ($ltvScore >= $thresholds['ltv_vip'] && $urgency === '高') {
        $reasons[] = 'VIP顧客の緊急案件';
        $level = max($level, 2);
    }
    
    if ($ltvScore >= $thresholds['ltv_vip'] && $category === 'クレーム') {
        $reasons[] = 'VIP顧客からのクレーム';
        $level = max($level, 2);
    }
    
    if ($churnRisk >= $thresholds['churn_risk_high'] && $sentiment === 'ネガティブ') {
        $reasons[] = '解約リスク高＋ネガティブ';
        $level = max($level, 2);
    }
    
    // === レベル1 ===
    if ($category === 'クレーム' && $sentiment === 'ネガティブ') {
        $reasons[] = 'ネガティブなクレーム';
        $level = max($level, 1);
    }
    
    if ($ltvScore >= $thresholds['ltv_high_value'] && $sentiment === 'ネガティブ') {
        $reasons[] = '高価値顧客の不満';
        $level = max($level, 1);
    }
    
    if ($category === '営業' && $ltvScore >= $thresholds['ltv_vip']) {
        $reasons[] = 'VIP商談機会';
        $level = max($level, 1);
    }
    
    if ($churnRisk >= $thresholds['churn_risk_medium'] && $churnRisk < $thresholds['churn_risk_high']) {
        $reasons[] = '解約リスク中程度';
        $level = max($level, 1);
    }
    
    if (!empty($aiEscalationReason)) {
        $reasons[] = "AI判定: {$aiEscalationReason}";
        $level = max($level, 1);
    }
    
    // レベル説明
    $levelDescriptions = [
        0 => '通常対応',
        1 => '部門責任者へエスカレーション',
        2 => '事業部長へ緊急エスカレーション',
        3 => '経営層へ最優先エスカレーション'
    ];
    
    return [
        'required' => $level > 0,
        'level' => $level,
        'level_description' => $levelDescriptions[$level] ?? '不明',
        'reason' => implode('、', $reasons)
    ];
}

// ============================================================
// SPIRAL API関連
// ============================================================

/**
 * 顧客の過去のお問い合わせ履歴を取得
 */
function getCustomerHistory($email, $excludeRecordId) {
    $url = SPIRAL_API_URL . '/apps/' . APP_ID . '/dbs/' . INQUIRY_DB_ID . '/records';
    
    $params = [
        'where' => "@email = '{$email}' AND @_id <> '{$excludeRecordId}'",
        'sort' => '_createdAt:desc',
        'limit' => 3,
        'fields' => 'category,ai_analysis,_createdAt'
    ];
    
    $response = callSpiralApi('GET', $url, $params);
    
    if (!$response || empty($response['items'])) {
        return [];
    }
    
    $history = [];
    foreach ($response['items'] as $item) {
        $aiAnalysis = json_decode($item['ai_analysis'] ?? '{}', true);
        $history[] = [
            'date' => date('Y-m-d', strtotime($item['_createdAt'])),
            'category' => $item['category'] ?? 'その他',
            'summary' => $aiAnalysis['summary'] ?? ''
        ];
    }
    
    return $history;
}

/**
 * 担当者マスタのoptionsを取得
 */
function getStaffDbOptions() {
    $url = SPIRAL_API_URL . '/apps/' . APP_ID . '/dbs/' . STAFF_DB_ID . '/records';
    $params = ['limit' => 1];
    $response = callSpiralApi('GET', $url, $params);
    return $response['options'] ?? [];
}

/**
 * レコードを更新
 */
function updateRecord($recordId, $data) {
    $url = SPIRAL_API_URL . '/apps/' . APP_ID . '/dbs/' . INQUIRY_DB_ID . '/records/' . $recordId;
    $response = callSpiralApi('PATCH', $url, $data);
    return $response !== false;
}

/**
 * 担当者の対応件数をインクリメント
 */
function incrementStaffLoad($staffId) {
    $url = SPIRAL_API_URL . '/apps/' . APP_ID . '/dbs/' . STAFF_DB_ID . '/records/' . $staffId;
    $staff = callSpiralApi('GET', $url);
    
    if (!$staff) {
        return;
    }
    
    $currentLoad = intval($staff['current_load'] ?? 0);
    callSpiralApi('PATCH', $url, ['current_load' => (string)($currentLoad + 1)]);
}

/**
 * SPIRAL API呼び出し
 */
function callSpiralApi($method, $url, $data = null) {
    $ch = curl_init();
    
    $headers = [
        'Authorization: Bearer ' . SPIRAL_API_TOKEN,
        'Content-Type: application/json',
        'X-Spiral-Api-Version: 1.1'
    ];
    
    $options = [
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HTTPHEADER => $headers,
        CURLOPT_TIMEOUT => 30
    ];
    
    switch ($method) {
        case 'GET':
            if ($data) {
                $options[CURLOPT_URL] .= '?' . http_build_query($data);
            }
            break;
        case 'POST':
            $options[CURLOPT_POST] = true;
            $options[CURLOPT_POSTFIELDS] = json_encode($data);
            break;
        case 'PATCH':
            $options[CURLOPT_CUSTOMREQUEST] = 'PATCH';
            $options[CURLOPT_POSTFIELDS] = json_encode($data);
            break;
    }
    
    curl_setopt_array($ch, $options);
    
    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    
    if ($httpCode < 200 || $httpCode >= 300) {
        return false;
    }
    
    return json_decode($response, true);
}

// ============================================================
// 実行
// ============================================================

main();

            

<?php
// ============================================================
// AI分析機能
// ============================================================

/**
 * お問い合わせ内容をAIで分析
 */
function analyzeInquiry($content, $customerName = '', $companyName = '', $customerHistory = []) {
    $prompt = buildAnalysisPrompt($content, $customerName, $companyName, $customerHistory);
    $response = callOpenAI($prompt);
    
    if ($response === false) {
        return getDefaultAnalysisResult();
    }
    
    return parseAnalysisResponse($response);
}

/**
 * 分析用プロンプトを構築
 */
function buildAnalysisPrompt($content, $customerName, $companyName, $customerHistory) {
    $systemPrompt = <<<EOT
あなたはお問い合わせ分析AIです。以下の情報を分析し、JSON形式で結果を返してください。

## 分析項目

1. category（カテゴリ）: 以下から1つ選択
    - "技術": 技術的な質問、バグ報告、使い方の質問
    - "営業": 見積もり依頼、導入相談、デモ依頼
    - "クレーム": 不満、苦情、解約示唆
    - "その他": 上記に該当しないもの

2. urgency（緊急度）: 以下から1つ選択
    - "高": 本番障害、業務停止、即時対応必要
    - "中": 当日〜翌日対応が望ましい
    - "低": 通常対応で問題なし

3. sentiment（感情）: 以下から1つ選択
    - "ポジティブ": 好意的、感謝、期待
    - "ニュートラル": 中立的、事務的
    - "ネガティブ": 不満、怒り、失望

4. ltv_score（LTV予測スコア）: 0-100の整数
    - 企業規模、予算言及、長期利用意向などから予測

5. escalation_reason（エスカレーション理由）: 
    - エスカレーションが必要な場合はその理由、不要な場合は空文字

6. summary（要約）: 50文字以内でお問い合わせの要約

7. suggested_response_points（回答のポイント）: 
    - 回答時に含めるべきポイントを箇条書きで

8. keywords（キーワード）:
    - お問い合わせから抽出した重要キーワード（最大5つ）

9. churn_risk（解約リスク）: 0-100の整数

10. upsell_opportunity（アップセル機会）: 0-100の整数

## 出力形式
必ず以下のJSON形式で出力してください：
{
  "category": "技術",
  "urgency": "中",
  "sentiment": "ニュートラル",
  "ltv_score": 50,
  "escalation_reason": "",
  "summary": "APIの認証方法についての質問",
  "suggested_response_points": ["認証フローの説明", "サンプルコードの提供"],
  "keywords": ["API", "認証", "トークン"],
  "churn_risk": 10,
  "upsell_opportunity": 30
}
EOT;

    $userPrompt = "## お問い合わせ情報\n\n";
    if ($customerName) {
        $userPrompt .= "お客様名: {$customerName}\n";
    }
    if ($companyName) {
        $userPrompt .= "会社名: {$companyName}\n";
    }
    
    // 過去の履歴がある場合は追加
    if (!empty($customerHistory)) {
        $userPrompt .= "\n## 過去のお問い合わせ履歴（直近3件）\n";
        foreach (array_slice($customerHistory, 0, 3) as $history) {
            $userPrompt .= "- [{$history['date']}] {$history['category']}: {$history['summary']}\n";
        }
    }
    
    $userPrompt .= "\n## 今回のお問い合わせ内容\n{$content}";
    
    return [
        'system' => $systemPrompt,
        'user' => $userPrompt
    ];
}

/**
 * OpenAI APIを呼び出し
 */
function callOpenAI($prompt) {
    $data = [
        'model' => OPENAI_MODEL,
        'messages' => [
            ['role' => 'system', 'content' => $prompt['system']],
            ['role' => 'user', 'content' => $prompt['user']]
        ],
        'temperature' => 0.3,
        'response_format' => ['type' => 'json_object']
    ];
    
    $ch = curl_init('https://api.openai.com/v1/chat/completions');
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST => true,
        CURLOPT_HTTPHEADER => [
            'Content-Type: application/json',
            'Authorization: Bearer ' . OPENAI_API_KEY
        ],
        CURLOPT_POSTFIELDS => json_encode($data),
        CURLOPT_TIMEOUT => 30
    ]);
    
    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    
    if ($httpCode !== 200) {
        return false;
    }
    
    $result = json_decode($response, true);
    if (isset($result['choices'][0]['message']['content'])) {
        return $result['choices'][0]['message']['content'];
    }
    
    return false;
}

/**
 * AI分析レスポンスをパース
 */
function parseAnalysisResponse($response) {
    $data = json_decode($response, true);
    if (json_last_error() !== JSON_ERROR_NONE) {
        return getDefaultAnalysisResult();
    }
    
    $validCategories = ['技術', '営業', 'クレーム', 'その他'];
    $validUrgencies = ['高', '中', '低'];
    $validSentiments = ['ポジティブ', 'ニュートラル', 'ネガティブ'];
    
    return [
        'category' => in_array($data['category'] ?? '', $validCategories) 
            ? $data['category'] : 'その他',
        'urgency' => in_array($data['urgency'] ?? '', $validUrgencies) 
            ? $data['urgency'] : '中',
        'sentiment' => in_array($data['sentiment'] ?? '', $validSentiments) 
            ? $data['sentiment'] : 'ニュートラル',
        'ltv_score' => max(0, min(100, intval($data['ltv_score'] ?? 50))),
        'escalation_reason' => $data['escalation_reason'] ?? '',
        'summary' => mb_substr($data['summary'] ?? '', 0, 100),
        'suggested_response_points' => $data['suggested_response_points'] ?? [],
        'keywords' => $data['keywords'] ?? [],
        'churn_risk' => max(0, min(100, intval($data['churn_risk'] ?? 0))),
        'upsell_opportunity' => max(0, min(100, intval($data['upsell_opportunity'] ?? 0)))
    ];
}

/**
 * デフォルト分析結果（API失敗時）
 */
function getDefaultAnalysisResult() {
    return [
        'category' => 'その他',
        'urgency' => '中',
        'sentiment' => 'ニュートラル',
        'ltv_score' => 50,
        'escalation_reason' => '',
        'summary' => 'AI分析に失敗しました',
        'suggested_response_points' => [],
        'keywords' => [],
        'churn_risk' => 0,
        'upsell_opportunity' => 0
    ];
}

            

<?php
// ============================================================
// ルーティング機能
// ============================================================

/**
 * カテゴリと部署のマッピング
 */
function getCategoryDepartmentMap() {
    return [
        '技術' => 'テクニカルサポート',
        '営業' => '営業部',
        'クレーム' => 'カスタマーサポート',
        'その他' => 'カスタマーサポート'
    ];
}

/**
 * 負荷分散を考慮したルーティング
 */
function routeWithLoadBalancing($category, $urgency, $ltvScore, $staffOptions = []) {
    $departmentMap = getCategoryDepartmentMap();
    $department = $departmentMap[$category] ?? $departmentMap['その他'];
    
    // VIP閾値
    $vipThreshold = 70;
    
    // 担当者マスタから候補を取得
    $candidates = getRoutingCandidates($category, $urgency, $ltvScore, $vipThreshold, $staffOptions);
    
    if (empty($candidates)) {
        return [
            'department' => $department,
            'assignee_id' => '',
            'assignee_name' => '未割当',
            'assignee_email' => '',
            'reason' => "カテゴリ「{$category}」に対応可能な担当者が見つかりませんでした"
        ];
    }
    
    // 負荷が最も低い担当者を選択を動作させる
    $selectedStaff = selectStaffByLoad($candidates);
    $reason = buildRoutingReason($category, $urgency, $ltvScore, $selectedStaff, $vipThreshold);
    
    return [
        'department' => $department,
        'assignee_id' => $selectedStaff['_id'],
        'assignee_name' => $selectedStaff['staff_name'],
        'assignee_email' => $selectedStaff['staff_email'],
        'reason' => $reason
    ];
}

/**
 * ルーティング候補を取得
 */
function getRoutingCandidates($category, $urgency, $ltvScore, $vipThreshold, $options = []) {
    $url = SPIRAL_API_URL . '/apps/' . APP_ID . '/dbs/' . STAFF_DB_ID . '/records';
    
    // カテゴリのラベルからIDを取得
    $categoryOptions = $options['categories'] ?? [];
    $categoryMap = array_flip($categoryOptions);
    $categoryId = $categoryMap[$category] ?? null;
    
    if (!$categoryId) {
        return [];
    }
    
    // where条件を構築（マルチセレクトフィールドの検索）
    // @categories ANYCONTAINS('2') で「営業」カテゴリを含む担当者を検索
    $where = "@categories ANYCONTAINS('{$categoryId}')";
    
    // 緊急度が高い、またはVIP顧客の場合はエスカレーション権限ありを優先
    if ($urgency === '高' || $ltvScore >= $vipThreshold) {
        $where .= " AND @can_escalate = '1'";
    }
    
    $params = [
        'where' => $where,
        'sort' => 'current_load:asc',
        'limit' => 10
    ];
    
    $response = callSpiralApi('GET', $url, $params);
    
    if (!$response || empty($response['items'])) {
        $where = "@categories ANYCONTAINS('{$categoryId}')";
        $params['where'] = $where;
        $response = callSpiralApi('GET', $url, $params);
    }
    
    return $response['items'] ?? [];
}

/**
 * 負荷が最も低い担当者を選択
 */
function selectStaffByLoad($candidates) {
    $minLoad = $candidates[0]['current_load'] ?? 0;
    $sameLoadCandidates = array_filter($candidates, function($c) use ($minLoad) {
        return ($c['current_load'] ?? 0) === $minLoad;
    });
    return $sameLoadCandidates[array_rand($sameLoadCandidates)];
}

/**
 * ルーティング理由を構築
 */
function buildRoutingReason($category, $urgency, $ltvScore, $staff, $vipThreshold) {
    $reasons = [];
    $reasons[] = "カテゴリ「{$category}」";
    
    if ($ltvScore >= $vipThreshold) {
        $reasons[] = "VIP顧客（LTVスコア: {$ltvScore}）";
    }
    
    if ($urgency === '高') {
        $reasons[] = "緊急度「高」";
    }
    
    $load = $staff['current_load'] ?? 0;
    $reasons[] = "現在の対応件数: {$load}件";
    
    return implode('、', $reasons) . " → {$staff['staff_name']}を選定";
}
            

<?php
// ============================================================
// エスカレーション判定機能
// ============================================================

/**
 * エスカレーション判定を実行
 */
function checkEscalation($category, $urgency, $sentiment, $ltvScore, $churnRisk, $aiEscalationReason = '') {
    // 閾値設定
    $thresholds = [
        'ltv_vip' => 80,
        'ltv_high_value' => 60,
        'churn_risk_high' => 70,
        'churn_risk_medium' => 40
    ];
    
    $reasons = [];
    $level = 0;
    
    // === レベル3（最高緊急度）===
    if ($ltvScore >= $thresholds['ltv_vip'] && $category === 'クレーム' && $urgency === '高') {
        $reasons[] = 'VIP顧客の緊急クレーム';
        $level = max($level, 3);
    }
    if ($ltvScore >= $thresholds['ltv_vip'] && $churnRisk >= $thresholds['churn_risk_high']) {
        $reasons[] = 'VIP顧客の解約リスク高';
        $level = max($level, 3);
    }
    
    // === レベル2 ===
    if ($category === 'クレーム' && $urgency === '高') {
        $reasons[] = '緊急クレーム';
        $level = max($level, 2);
    }
    if ($ltvScore >= $thresholds['ltv_vip'] && $urgency === '高') {
        $reasons[] = 'VIP顧客の緊急案件';
        $level = max($level, 2);
    }
    if ($ltvScore >= $thresholds['ltv_vip'] && $category === 'クレーム') {
        $reasons[] = 'VIP顧客からのクレーム';
        $level = max($level, 2);
    }
    if ($churnRisk >= $thresholds['churn_risk_high'] && $sentiment === 'ネガティブ') {
        $reasons[] = '解約リスク高＋ネガティブ';
        $level = max($level, 2);
    }
    
    // === レベル1 ===
    if ($category === 'クレーム' && $sentiment === 'ネガティブ') {
        $reasons[] = 'ネガティブなクレーム';
        $level = max($level, 1);
    }
    if ($ltvScore >= $thresholds['ltv_high_value'] && $sentiment === 'ネガティブ') {
        $reasons[] = '高価値顧客の不満';
        $level = max($level, 1);
    }
    if ($category === '営業' && $ltvScore >= $thresholds['ltv_vip']) {
        $reasons[] = 'VIP商談機会';
        $level = max($level, 1);
    }
    if ($churnRisk >= $thresholds['churn_risk_medium'] && $churnRisk < $thresholds['churn_risk_high']) {
        $reasons[] = '解約リスク中程度';
        $level = max($level, 1);
    }
    if (!empty($aiEscalationReason)) {
        $reasons[] = "AI判定: {$aiEscalationReason}";
        $level = max($level, 1);
    }
    
    // レベル説明
    $levelDescriptions = [
        0 => '通常対応',
        1 => '部門責任者へエスカレーション',
        2 => '事業部長へ緊急エスカレーション',
        3 => '経営層へ最優先エスカレーション'
    ];
    
    return [
        'required' => $level > 0,
        'level' => $level,
        'level_description' => $levelDescriptions[$level] ?? '不明',
        'reason' => implode('、', $reasons)
    ];
}

            

<?php
// ============================================================
// SPIRAL API関連
// ============================================================

/**
 * 顧客の過去のお問い合わせ履歴を取得
 */
function getCustomerHistory($email, $excludeRecordId) {
    $url = SPIRAL_API_URL . '/apps/' . APP_ID . '/dbs/' . INQUIRY_DB_ID . '/records';
    
    $params = [
        'where' => "@email = '{$email}' AND @_id <> '{$excludeRecordId}'",
        'sort' => '_createdAt:desc',
        'limit' => 3,
        'fields' => 'category,ai_analysis,_createdAt'
    ];
    
    $response = callSpiralApi('GET', $url, $params);
    
    if (!$response || empty($response['items'])) {
        return [];
    }
    
    $history = [];
    foreach ($response['items'] as $item) {
        $aiAnalysis = json_decode($item['ai_analysis'] ?? '{}', true);
        $history[] = [
            'date' => date('Y-m-d', strtotime($item['_createdAt'])),
            'category' => $item['category'] ?? 'その他',
            'summary' => $aiAnalysis['summary'] ?? ''
        ];
    }
    
    return $history;
}

/**
 * 担当者マスタのoptionsを取得
 */
function getStaffDbOptions() {
    $url = SPIRAL_API_URL . '/apps/' . APP_ID . '/dbs/' . STAFF_DB_ID . '/records';
    $params = ['limit' => 1];
    $response = callSpiralApi('GET', $url, $params);
    return $response['options'] ?? [];
}

/**
 * レコードを更新
 */
function updateRecord($recordId, $data) {
    $url = SPIRAL_API_URL . '/apps/' . APP_ID . '/dbs/' . INQUIRY_DB_ID . '/records/' . $recordId;
    $response = callSpiralApi('PATCH', $url, $data);
    return $response !== false;
}

/**
 * 担当者の対応件数をインクリメント
 */
function incrementStaffLoad($staffId) {
    $url = SPIRAL_API_URL . '/apps/' . APP_ID . '/dbs/' . STAFF_DB_ID . '/records/' . $staffId;
    $staff = callSpiralApi('GET', $url);
    
    if (!$staff) {
        return;
    }
    
    $currentLoad = intval($staff['current_load'] ?? 0);
    callSpiralApi('PATCH', $url, ['current_load' => (string)($currentLoad + 1)]);
}

/**
 * SPIRAL API呼び出し
 */
function callSpiralApi($method, $url, $data = null) {
    $ch = curl_init();
    
    $headers = [
        'Authorization: Bearer ' . SPIRAL_API_TOKEN,
        'Content-Type: application/json',
        'X-Spiral-Api-Version: 1.1'
    ];
    
    $options = [
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HTTPHEADER => $headers,
        CURLOPT_TIMEOUT => 30
    ];
    
    switch ($method) {
        case 'GET':
            if ($data) $options[CURLOPT_URL] .= '?' . http_build_query($data);
            break;
        case 'POST':
            $options[CURLOPT_POST] = true;
            $options[CURLOPT_POSTFIELDS] = json_encode($data);
            break;
        case 'PATCH':
            $options[CURLOPT_CUSTOMREQUEST] = 'PATCH';
            $options[CURLOPT_POSTFIELDS] = json_encode($data);
            break;
    }
    
    curl_setopt_array($ch, $options);
    
    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    
    if ($httpCode < 200 || $httpCode >= 300) {
        return false;
    }
    
    return json_decode($response, true);
}