#!/usr/bin/env python3

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

def read_employee_list():
    """
    讀取控制中樞表的員工清單資料結構
    """
    SPREADSHEET_ID = '1fTQ3AZ93yP_q7oCncMASozScIJ36NlJwgc3vplr0nJI'
    
    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)
        sheet = service.spreadsheets()
        
        # First get all sheet names
        spreadsheet_metadata = service.spreadsheets().get(spreadsheetId=SPREADSHEET_ID).execute()
        sheets = spreadsheet_metadata.get('sheets', [])
        
        print('='*60)
        print('控制中樞表所有工作表：')
        print('='*60)
        for s in sheets:
            sheet_name = s['properties']['title']
            print(f'工作表: {sheet_name}')
        
        # 嘗試讀取第一個工作表（通常是員工清單）
        first_sheet_name = sheets[0]['properties']['title'] if sheets else 'Sheet1'
        print(f'\n讀取工作表: {first_sheet_name}')
        print('='*60)
        
        # 讀取員工清單
        range_name = f'{first_sheet_name}!A1:F20'
        result = sheet.values().get(
            spreadsheetId=SPREADSHEET_ID,
            range=range_name
        ).execute()
        
        values = result.get('values', [])
        
        if not values:
            print('沒有找到資料')
            return
        
        # 顯示前幾行資料
        for i, row in enumerate(values[:15], 1):
            # 只顯示非空欄位
            filtered_row = []
            for j, cell in enumerate(row):
                if cell and str(cell).strip():
                    cell_content = str(cell).strip()
                    if len(cell_content) > 30:
                        cell_content = cell_content[:30] + '...'
                    filtered_row.append(f'{chr(65+j)}:{cell_content}')
            
            if filtered_row:  # 只顯示有內容的行
                print(f'第 {i:2d} 行: {filtered_row}')
        
        print(f'\n總欄位數: {len(values[0]) if values else 0}')
        
        # 特別顯示標題行分析
        if len(values) >= 1:
            print(f"\n標題行分析:")
            for i in range(min(2, len(values))):
                if values[i] and any(str(cell).strip() for cell in values[i]):
                    print(f"  第{i+1}行: {[str(cell) for cell in values[i] if str(cell).strip()]}")
        
    except Exception as e:
        print(f'讀取控制中樞表時發生錯誤：{e}')

if __name__ == '__main__':
    read_employee_list()