#!/usr/bin/env python3

from google.oauth2 import service_account
from googleapiclient.discovery import build

def debug_overtime_calculation():
    """
    調試加班計算問題
    """
    SPREADSHEET_ID = '12dg8UjMTUh8SFPS-X4v83aIwNUEm5CYEKO3RNUW7QCA'
    
    try:
        creds = service_account.Credentials.from_service_account_file(
            'service_account_key.json',
            scopes=['https://www.googleapis.com/auth/spreadsheets.readonly']
        )
        
        service = build('sheets', 'v4', credentials=creds)
        
        # 檢查生成的加班費紀錄總表
        print('='*60)
        print('檢查「加班費紀錄總表」內容：')
        print('='*60)
        
        try:
            result = service.spreadsheets().values().get(
                spreadsheetId=SPREADSHEET_ID,
                range='加班費紀錄總表!A1:E20'
            ).execute()
            
            values = result.get('values', [])
            if values:
                for i, row in enumerate(values, 1):
                    print(f'第 {i:2d} 行: {row}')
            else:
                print('沒有找到資料')
                
        except Exception as e:
            print(f'無法讀取加班費紀錄總表: {e}')
        
        # 檢查特定月份的原始數據
        problem_months = ['2025年10月', '2025年11月', '2025年6月工時紀錄', '2025年7月']
        
        for month in problem_months:
            print(f'\n{"="*60}')
            print(f'檢查 {month} 原始數據：')
            print('='*60)
            
            try:
                # 先檢查是否存在這個工作表
                spreadsheet_metadata = service.spreadsheets().get(spreadsheetId=SPREADSHEET_ID).execute()
                sheets = [s['properties']['title'] for s in spreadsheet_metadata.get('sheets', [])]
                
                if month not in sheets:
                    print(f'⚠️ 工作表 "{month}" 不存在')
                    print(f'可用的工作表: {sheets}')
                    continue
                
                result = service.spreadsheets().values().get(
                    spreadsheetId=SPREADSHEET_ID,
                    range=f'{month}!A1:Z10'
                ).execute()
                
                values = result.get('values', [])
                if values:
                    print(f'前10行數據：')
                    for i, row in enumerate(values, 1):
                        if any(str(cell).strip() for cell in row):  # 只顯示非空行
                            filtered_row = []
                            for j, cell in enumerate(row):
                                if cell and str(cell).strip():
                                    cell_content = str(cell).strip()
                                    if len(cell_content) > 15:
                                        cell_content = cell_content[:15] + "..."
                                    filtered_row.append(f"{chr(65+j)}:{cell_content}")
                            print(f'第 {i:2d} 行: {filtered_row}')
                else:
                    print('沒有找到資料')
                    
            except Exception as e:
                print(f'無法讀取 {month}: {e}')
        
        # 檢查所有工作表名稱
        print(f'\n{"="*60}')
        print('所有可用的工作表：')
        print('='*60)
        try:
            spreadsheet_metadata = service.spreadsheets().get(spreadsheetId=SPREADSHEET_ID).execute()
            sheets = spreadsheet_metadata.get('sheets', [])
            for i, sheet in enumerate(sheets, 1):
                sheet_name = sheet['properties']['title']
                print(f'{i:2d}. {sheet_name}')
        except Exception as e:
            print(f'無法讀取工作表列表: {e}')
                
    except Exception as e:
        print(f'執行失敗：{e}')

if __name__ == '__main__':
    debug_overtime_calculation()