csv_module.py

# csv_module.py - 完整的CSV成績更新模組
"""
CSV成績更新模組 - 將Agent批改結果更新回原始CSV成績表
整合到工作流程中，自動將.txt檔案中的成績寫入CSV
"""

import pandas as pd
import os
import glob
import re
from typing import Optional, Dict, List

class CSVGradingModule:
    """CSV成績更新模組"""
    
    def __init__(self):
        self.base_path = None
        self.selected_folder = None
        self.csv_file_path = None
        self.gradebook_df = None
        self.updated_count = 0
        self.failed_count = 0
        
    def find_csv_file(self) -> bool:
        """尋找當前目錄下的CSV檔案，優先選擇Grades-開頭的檔案"""
        print("正在尋找CSV檔案...")
        
        # 優先尋找Grades-開頭的CSV檔案
        grades_csv_files = glob.glob("Grades-*.csv")
        if grades_csv_files:
            # 如果有多個，選擇最新的
            self.csv_file_path = max(grades_csv_files, key=os.path.getmtime)
            print(f"找到成績表CSV檔案: {self.csv_file_path}")
            return True
        
        # 如果沒有Grades-開頭的，尋找任何CSV檔案
        csv_files = glob.glob("*.csv")
        if csv_files:
            # 選擇最新的CSV檔案
            self.csv_file_path = max(csv_files, key=os.path.getmtime)
            print(f"找到CSV檔案: {self.csv_file_path}")
            return True
        
        print("當前目錄下沒有找到CSV檔案")
        return False
    
    def load_gradebook(self) -> bool:
        """載入成績表CSV檔案"""
        try:
            # 嘗試不同的編碼
            encodings = ['utf-8', 'utf-8-sig', 'gbk', 'big5']
            
            for encoding in encodings:
                try:
                    self.gradebook_df = pd.read_csv(self.csv_file_path, encoding=encoding)
                    print(f"成功載入成績表（編碼: {encoding}），共 {len(self.gradebook_df)} 筆學生資料")
                    
                    # 顯示CSV欄位資訊
                    print(f"CSV欄位: {list(self.gradebook_df.columns)}")
                    return True
                except UnicodeDecodeError:
                    continue
                except Exception as e:
                    print(f"使用編碼 {encoding} 載入失敗: {str(e)}")
                    continue
            
            print("無法使用任何編碼載入CSV檔案")
            return False
            
        except Exception as e:
            print(f"載入CSV檔案失敗: {str(e)}")
            return False
    
    def extract_student_info_from_folder(self, folder_name: str) -> Optional[Dict[str, str]]:
        """從資料夾名稱中提取學生資訊"""
        try:
            # 移除常見的後綴
            clean_name = folder_name.replace('_assignsubmission_file', '')
            clean_name = clean_name.replace('_submission', '')
            clean_name = clean_name.replace('_file', '')
            
            # 分割名稱
            parts = clean_name.split('_')
            
            if len(parts) >= 2:
                name = parts[0]
                temp_number = parts[1]  # 臨時編號
            else:
                name = parts[0] if parts else "未知"
                temp_number = "未知"
            
            return {
                'name': name,
                'temp_number': temp_number,
                'full_id': clean_name,
                'original_folder': folder_name
            }
            
        except Exception as e:
            print(f"解析資料夾名稱失敗 {folder_name}: {e}")
            return None
    
    def find_participant_in_csv(self, student_info: Dict[str, str]) -> Optional[int]:
        """在CSV中尋找對應的學生記錄"""
        temp_number = student_info['temp_number']
        name = student_info['name']
        
        # 方法1: 使用Participant + 臨時編號匹配
        if 'Identifier' in self.gradebook_df.columns:
            participant_id = f"Participant {temp_number}"
            mask = self.gradebook_df['Identifier'] == participant_id
            if mask.any():
                return self.gradebook_df[mask].index[0]
        
        # 方法2: 使用姓名匹配
        if 'Full name' in self.gradebook_df.columns:
            # 完全匹配
            name_mask = self.gradebook_df['Full name'] == name
            if name_mask.any():
                return self.gradebook_df[name_mask].index[0]
            
            # 部分匹配
            partial_mask = self.gradebook_df['Full name'].str.contains(name, na=False)
            if partial_mask.any():
                return self.gradebook_df[partial_mask].index[0]
        
        # 方法3: 使用臨時編號直接搜索
        if 'Identifier' in self.gradebook_df.columns:
            temp_mask = self.gradebook_df['Identifier'].str.contains(temp_number, na=False)
            if temp_mask.any():
                return self.gradebook_df[temp_mask].index[0]
        
        return None
    
    def parse_feedback_file(self, file_path: str) -> Optional[float]:
        """解析回饋.txt檔案，提取總分"""
        try:
            with open(file_path, 'r', encoding='utf-8') as f:
                content = f.read()
            
            # 各種可能的總分格式
            patterns = [
                r'總分:\s*([0-9.]+)/100',      # 總分: XX/100
                r'總分:\s*([0-9.]+)',          # 總分: XX
                r'分數:\s*([0-9.]+)',          # 分數: XX  
                r'score:\s*([0-9.]+)',         # score: XX
                r'Grade:\s*([0-9.]+)',         # Grade: XX
                r'等第:.*?([0-9.]+)分',        # 等第: A+ (XX分)
            ]
            
            for pattern in patterns:
                match = re.search(pattern, content, re.IGNORECASE)
                if match:
                    score = float(match.group(1))
                    # 確保分數在合理範圍內
                    if 0 <= score <= 100:
                        return score
                    elif score > 100:  # 可能是百分制以外的分數，需要轉換
                        return min(score, 100)
            
            print(f"無法在檔案中找到有效的分數格式: {file_path}")
            print(f"檔案內容預覽: {content[:200]}...")
            return None
            
        except Exception as e:
            print(f"讀取檔案 {file_path} 時發生錯誤: {str(e)}")
            return None
    
    def find_student_folders(self) -> List[str]:
        """找到所有學生資料夾"""
        if not os.path.exists(self.base_path):
            print(f"學生資料夾不存在: {self.base_path}")
            return []
        
        student_folders = []
        for item in os.listdir(self.base_path):
            folder_path = os.path.join(self.base_path, item)
            if os.path.isdir(folder_path):
                student_folders.append(item)
        
        return sorted(student_folders)
    
    def get_or_create_grade_column(self) -> str:
        """獲取或創建成績欄位"""
        # 檢查常見的成績欄位名稱
        possible_grade_columns = [
            'Grade', 'grade', '成績', '分數', 'Score', 'score',
            'Total', 'total', 'Final Grade', 'Final_Grade'
        ]
        
        for col in possible_grade_columns:
            if col in self.gradebook_df.columns:
                print(f"使用現有成績欄位: {col}")
                return col
        
        # 如果沒有找到，創建新的欄位
        grade_column = 'Grade'
        self.gradebook_df[grade_column] = None
        print(f"創建新的成績欄位: {grade_column}")
        return grade_column
    
    def process_grades(self) -> bool:
        """處理成績並更新到CSV"""
        student_folders = self.find_student_folders()
        
        if not student_folders:
            print("沒有找到學生資料夾")
            return False
        
        print(f"\n開始處理 {len(student_folders)} 個學生資料夾...")
        
        # 獲取成績欄位
        grade_column = self.get_or_create_grade_column()
        
        self.updated_count = 0
        self.failed_count = 0
        not_found_count = 0
        
        for folder_name in student_folders:
            print(f"\n處理資料夾: {folder_name}")
            
            # 提取學生資訊
            student_info = self.extract_student_info_from_folder(folder_name)
            if not student_info:
                print(f"  ✗ 無法解析資料夾名稱")
                self.failed_count += 1
                continue
            
            # 尋找資料夾內的.txt檔案
            folder_path = os.path.join(self.base_path, folder_name)
            txt_files = glob.glob(os.path.join(folder_path, "*.txt"))
            
            if not txt_files:
                print(f"  ⚠ 找不到.txt檔案")
                not_found_count += 1
                continue
            
            # 使用最新的.txt檔案（如果有多個）
            feedback_file = max(txt_files, key=os.path.getmtime)
            print(f"  📄 使用檔案: {os.path.basename(feedback_file)}")
            
            # 解析總分
            total_score = self.parse_feedback_file(feedback_file)
            if total_score is None:
                print(f"  ✗ 無法解析總分")
                self.failed_count += 1
                continue
            
            # 在CSV中找到對應的學生
            csv_index = self.find_participant_in_csv(student_info)
            
            if csv_index is not None:
                # 更新成績
                self.gradebook_df.loc[csv_index, grade_column] = total_score
                student_name = self.gradebook_df.loc[csv_index, 'Full name'] if 'Full name' in self.gradebook_df.columns else student_info['name']
                identifier = self.gradebook_df.loc[csv_index, 'Identifier'] if 'Identifier' in self.gradebook_df.columns else f"Temp {student_info['temp_number']}"
                
                print(f"  ✓ 已更新 {student_name} ({identifier}) 的成績: {total_score}")
                self.updated_count += 1
            else:
                print(f"  ✗ 在CSV中找不到對應的學生: {student_info['name']} (臨時編號: {student_info['temp_number']})")
                self.failed_count += 1
        
        # 顯示處理結果摘要
        print(f"\n📊 處理結果摘要:")
        print(f"  ✅ 成功更新: {self.updated_count} 筆")
        print(f"  ⚠  找不到txt檔案: {not_found_count} 筆") 
        print(f"  ❌ 處理失敗: {self.failed_count} 筆")
        print(f"  📁 總計處理: {len(student_folders)} 個資料夾")
        
        return self.updated_count > 0
    
    def save_gradebook(self) -> bool:
        """儲存更新後的成績表，直接覆蓋原檔案"""
        try:
            # 備份原檔案
            backup_path = f"{self.csv_file_path}.backup"
            if os.path.exists(self.csv_file_path):
                import shutil
                shutil.copy2(self.csv_file_path, backup_path)
                print(f"已創建備份檔案: {backup_path}")
            
            # 保存更新後的檔案
            self.gradebook_df.to_csv(self.csv_file_path, index=False, encoding='utf-8-sig')
            print(f"✅ 已更新原成績表: {self.csv_file_path}")
            return True
            
        except Exception as e:
            print(f"❌ 儲存檔案失敗: {str(e)}")
            return False
    
    def run(self) -> bool:
        """執行完整的成績更新流程（互動模式）"""
        print("=== CSV成績更新模組 ===\n")
        
        # 1. 尋找並載入CSV檔案
        if not self.find_csv_file():
            return False
        
        if not self.load_gradebook():
            return False
        
        # 2. 選擇作業資料夾
        if not self.select_folder():
            return False
        
        # 3. 處理成績
        if not self.process_grades():
            print("沒有成功更新任何成績")
            return False
        
        # 4. 儲存結果
        save_choice = input("\n是否要儲存更新後的成績表? (y/n): ").lower().strip()
        if save_choice in ['y', 'yes', '']:
            return self.save_gradebook()
        else:
            print("取消儲存")
            return False
    
    def select_folder(self) -> bool:
        """讓使用者選擇作業類型資料夾（互動模式）"""
        available_folders = self.list_available_folders()
        
        if not available_folders:
            print("沒有找到可用的作業資料夾")
            return False
        
        print("\n可選擇的作業資料夾:")
        for i, folder in enumerate(available_folders, 1):
            folder_name = folder.split('/')[-1]
            print(f"{i}. {folder_name} ({folder})")
        
        try:
            choice = int(input(f"\n請選擇資料夾 (1-{len(available_folders)}): ")) - 1
            if 0 <= choice < len(available_folders):
                self.selected_folder = available_folders[choice]
                self.base_path = os.path.join(self.selected_folder, "student")
                print(f"已選擇: {self.selected_folder}")
                return True
            else:
                print("無效的選擇")
                return False
        except ValueError:
            print("請輸入有效的數字")
            return False
    
    def list_available_folders(self) -> List[str]:
        """列出可選擇的作業類型資料夾"""
        available_folders = []
        
        # 檢查是否有base資料夾
        if os.path.exists("base"):
            base_folders = [f for f in os.listdir("base") 
                          if os.path.isdir(os.path.join("base", f)) and f not in ["test_results", "workflow_reports", "transfer_logs"]]
            available_folders.extend([f"base/{f}" for f in base_folders])
        
        # 檢查test_results資料夾
        if os.path.exists("test_results"):
            test_folders = [f for f in os.listdir("test_results") 
                          if os.path.isdir(os.path.join("test_results", f))]
            available_folders.extend([f"test_results/{f}" for f in test_folders])
        
        return available_folders


# 使用範例
if __name__ == "__main__":
    # 互動模式
    module = CSVGradingModule()
    module.run()