app.py

import gradio as gr
import gspread
import os
import json
from google.cloud import storage

# 设置Google Sheets的默认值
DEFAULT_SHEET_URL = "https://docs.google.com/spreadsheets/d/161-2m7mDdEYWtMEjPeMFYlruJ6zvCZPyogns84AmVpw/edit#gid=1159223931"
GCS_BUCKET_NAME = "video_ai_assistant"

# 从环境变量获取GCS密钥
GCS_KEY_JSON = os.getenv("GOOGLE_APPLICATION_CREDENTIALS_JSON")
GCS_KEY = json.loads(GCS_KEY_JSON)
GCS_CLIENT = storage.Client.from_service_account_info(GCS_KEY)

# 检查GCS中是否存在特定文件
def check_file_in_gcs(bucket_name, video_id, file_type):
    bucket = GCS_CLIENT.bucket(bucket_name)
    file_path = f"{video_id}/{video_id}_{file_type}.json"
    blob = bucket.blob(file_path)
    return blob.exists()

def process_sheet_data(sheet_url, sheet_tab_name):
    gc = gspread.service_account_from_dict(GCS_KEY)
    sh = gc.open_by_url(sheet_url)
    
    worksheet = sh.worksheet(sheet_tab_name)
    data = worksheet.get_all_records()

    # 检查GCS中是否存在对应的文件
    results = []
    headers = ['subject', 'grade', '均一平台 標題', '均一平台 YouTube ID', '均一平台 YT Readable ID', 'cooc Youtube ID', 'transcript', 'questions', 'key_moments', 'summary', 'reading_passage', 'mind_map', 'yt_check_results', "cooc_check_results"]
    for row in data:
        video_ids_checked = set()  # 用于存储已检查的ID
        yt_check_results = []
        cooc_check_results = []

        readable_id = row.get('均一平台 YT Readable ID', '')
        youtube_id = row.get('均一平台 YouTube ID', '')
        cooc_id = row.get('cooc Youtube ID', '')

        # 准备结果行
        row_result = [
            row.get('subject', ''), row.get('grade', ''), row.get('均一平台 標題', ''),
            youtube_id, readable_id, cooc_id
        ]

        # 檢查 readable_id
        for file_type in headers[6:-3]:  # 跳过前面的ID和标题列以及最后的结果列
            row_result.append(check_file_in_gcs(GCS_BUCKET_NAME, readable_id, file_type))

        video_ids_checked.add(readable_id)

        # 檢查 youtube_id
        if youtube_id in video_ids_checked:
            yt_check_results = "NA"
        else:
            for file_type in headers[6:-3]:
                yt_check_result = check_file_in_gcs(GCS_BUCKET_NAME, youtube_id, file_type)
                yt_check_results.append(yt_check_result)
        
        video_ids_checked.add(youtube_id)

        # 檢查 cooc_id
        if cooc_id in video_ids_checked or not cooc_id:
            cooc_check_results = "NA"
        else:
            for file_type in headers[6:-3]:
                cooc_check_result = check_file_in_gcs(GCS_BUCKET_NAME, youtube_id, file_type)
                cooc_check_results.append(cooc_check_result)

    
        row_result.append(yt_check_results)
        row_result.append(cooc_check_results)
        results.append(row_result)
        print("=====================================")
        print(row_result)
    
    return results, headers

def update_output_table(sheet_url, sheet_tab_name):
    data, headers = process_sheet_data(sheet_url, sheet_tab_name)
    return gr.update(value=data, headers=headers)

with gr.Blocks() as app:
    with gr.Row():
        sheet_url_input = gr.Textbox(label="Enter Google Sheet URL", value=DEFAULT_SHEET_URL)
        tab_name_input = gr.Textbox(label="Enter Google Sheet Tab Name", value="思考力訓練")
        submit_button = gr.Button("Submit")
    with gr.Row():
        output_table = gr.Dataframe()

    submit_button.click(
        fn=update_output_table, 
        inputs=[sheet_url_input, tab_name_input], 
        outputs=[output_table]
    )

app.launch()