import requests
from bs4 import BeautifulSoup
import pandas as pd
import gradio as gr
from google.oauth2.service_account import Credentials
import gspread

def get_hospital_data(url):
    # 發送GET請求獲取網頁內容
    response = requests.get(url)
    response.encoding = 'utf-8'  # 設置正確的編碼

    # 使用BeautifulSoup解析HTML
    soup = BeautifulSoup(response.text, 'html.parser')

    # 提取醫院名稱
    hospital_name = soup.find('span', id='Lbl抬頭').text.strip()

    # 提取查詢院區
    queried_hospital = soup.find('span', id='Lbl結果').text.strip().split(':')[1].split()[0]

    # 提取病床數據
    table = soup.find('table', id='DG1')
    rows = table.find_all('tr')[1:]  # 跳過表頭

    # 解析病床數據
    bed_data = []
    for row in rows:
        cols = row.find_all('td')
        if len(cols) == 5:
            category = cols[0].text.strip()
            total = int(cols[1].text.strip())
            occupied = int(cols[2].text.strip())
            available = int(cols[3].text.strip())
            rate = float(cols[4].text.strip().rstrip('%'))
            bed_data.append([category, total, occupied, available, rate])

    # 創建DataFrame
    df = pd.DataFrame(bed_data, columns=['病床類別', '總床數', '佔床數', '空床數', '佔床率'])

    # 提取備註
    remarks = []
    for i in range(6):
        remark = soup.find('span', id=f'Lbl備註{i}')
        if remark:
            remarks.append(remark.text.strip())

    # 格式化輸出
    result = f"{hospital_name}\n查詢院區: {queried_hospital}\n\n各類病床明細表:\n{df.to_string(index=False)}\n\n備註:\n" + "\n".join(remarks)

    # 儲存 CSV 檔案
    df.to_csv("CM2024.csv", encoding="utf-8-sig")

    return result, df

def upload_to_google_sheets(df):
    # Google Sheets API 認證
    scope = ['https://www.googleapis.com/auth/spreadsheets']
    creds = Credentials.from_service_account_file("/content/gdrive/My Drive/omega-wind-430312-e2-eb1dbac8ba3d.json", scopes=scope)
    gs = gspread.authorize(creds)

    # 打開 Google 試算表
    sheet = gs.open_by_url('https://docs.google.com/spreadsheets/d/1puPO2mIwwTLSqQ-E3tB15vIl4WTclSRvHghdrwgeN9c/edit?gid=0#gid=0')
    worksheet = sheet.get_worksheet(0)

    # 轉換 DataFrame 為字串格式
    df1 = df.astype(str)

    # 更新試算表
    worksheet.update([df1.columns.values.tolist()] + df1.values.tolist())

def gradio_interface(url):
    result, df = get_hospital_data(url)
    upload_to_google_sheets(df)
    return result

iface = gr.Interface(fn=gradio_interface, inputs="text", outputs="text", title="Hospital Bed Data")
iface.launch()