app.py

import os
import json
import io

import pandas as pd
import streamlit as st
import gspread
from google.oauth2.service_account import Credentials
import plotly.express as px

# =========================
# 基本設定
# =========================
LATE_TIME = "08:00"
EARLY_LEAVE_TIME = "17:00"
MAX_WORK_HOURS = 12
MIN_WORK_HOURS = 1
NORMAL_WORK_HOURS = 8

GOOGLE_SHEET_URL = "https://docs.google.com/spreadsheets/d/1RGnMJW5Ja0e3rDJrChvrxlExlcqkB1WvIhFU-tRNWmo/edit?usp=sharing"
GOOGLE_WORKSHEET_NAME = "表單回應 1"


# =========================
# Google Sheets 讀取
# =========================
def get_gspread_client():
    secret_text = os.environ.get("GOOGLE_CREDS_JSON")
    if not secret_text:
        raise ValueError("找不到 GOOGLE_CREDS_JSON，請到 Hugging Face Space Settings > Secrets 設定。")

    creds_info = json.loads(secret_text)
    scopes = [
        "https://www.googleapis.com/auth/spreadsheets",
        "https://www.googleapis.com/auth/drive",
    ]
    credentials = Credentials.from_service_account_info(creds_info, scopes=scopes)
    return gspread.authorize(credentials)


def load_google_sheet_by_url(sheet_url, worksheet_name=None):
    gc = get_gspread_client()
    spreadsheet = gc.open_by_url(sheet_url)
    worksheet = spreadsheet.worksheet(worksheet_name) if worksheet_name else spreadsheet.sheet1
    values = worksheet.get_all_values()

    if not values:
        raise ValueError("Google Sheet 是空的，沒有任何資料。")

    header = values[0]
    rows = values[1:] if len(values) > 1 else []
    return pd.DataFrame(rows, columns=header)


# =========================
# 資料清洗
# =========================
def clean_attendance_data(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    df.columns = [str(col).strip() for col in df.columns]

    column_map = {
        "員工編號": "emp_id",
        "員工姓名": "emp_name",
        "部門": "department",
        "日期": "date",
        "上班時間": "check_in",
        "下班時間": "check_out",
        "備註": "remark",
    }
    df = df.rename(columns={k: v for k, v in column_map.items() if k in df.columns})

    required_cols = ["emp_id", "emp_name", "date", "check_in", "check_out"]
    for col in required_cols:
        if col not in df.columns:
            raise ValueError(f"缺少必要欄位：{col}")

    if "department" not in df.columns:
        df["department"] = ""

    if "remark" not in df.columns:
        df["remark"] = ""

    df["date"] = pd.to_datetime(df["date"], errors="coerce")

    df["check_in"] = df["check_in"].replace("", pd.NA)
    df["check_out"] = df["check_out"].replace("", pd.NA)

    df["check_in"] = pd.to_datetime(
        df["date"].dt.strftime("%Y-%m-%d") + " " + df["check_in"].astype(str),
        errors="coerce"
    )
    df["check_out"] = pd.to_datetime(
        df["date"].dt.strftime("%Y-%m-%d") + " " + df["check_out"].astype(str),
        errors="coerce"
    )

    return df


# =========================
# 備註自動判斷
# =========================
def build_remark(row, normal_work_hours: int) -> str:
    remarks = []

    if row["漏打卡"]:
        if pd.isna(row["check_in"]) and pd.isna(row["check_out"]):
            remarks.append("上班、下班皆未打卡")
        elif pd.isna(row["check_in"]):
            remarks.append("上班未打卡")
        elif pd.isna(row["check_out"]):
            remarks.append("下班未打卡")

    if row["遲到"]:
        remarks.append("遲到")

    if row["早退"]:
        remarks.append("早退")

    if row["工時過長"]:
        remarks.append("工時過長")

    if row["工時過短"]:
        remarks.append("工時過短")

    if row["工時不足"]:
        remarks.append(f"工時不足{normal_work_hours}小時")

    return "、".join(remarks) if remarks else "正常"


# =========================
# 稽核邏輯
# =========================
def audit_attendance(
    df: pd.DataFrame,
    late_time: str,
    early_leave_time: str,
    max_work_hours: float,
    min_work_hours: float,
    normal_work_hours: float,
) -> pd.DataFrame:
    df = df.copy()

    df["漏打卡"] = df["check_in"].isna() | df["check_out"].isna()

    df["標準上班時間"] = pd.to_datetime(
        df["date"].dt.strftime("%Y-%m-%d") + f" {late_time}",
        errors="coerce"
    )
    df["標準下班時間"] = pd.to_datetime(
        df["date"].dt.strftime("%Y-%m-%d") + f" {early_leave_time}",
        errors="coerce"
    )

    df["遲到"] = (df["check_in"] > df["標準上班時間"]).fillna(False)
    df["早退"] = (df["check_out"] < df["標準下班時間"]).fillna(False)

    df["工時"] = (df["check_out"] - df["check_in"]).dt.total_seconds() / 3600
    df["工時過長"] = (df["工時"] > max_work_hours).fillna(False)
    df["工時過短"] = (df["工時"] < min_work_hours).fillna(False)
    df["工時不足"] = ((df["工時"] >= min_work_hours) & (df["工時"] < normal_work_hours)).fillna(False)

    df["是否異常"] = (
        df["漏打卡"]
        | df["遲到"]
        | df["早退"]
        | df["工時過長"]
        | df["工時過短"]
        | df["工時不足"]
    )

    # 保留原始備註供查閱
    df["原始備註"] = df["remark"].astype(str).fillna("").str.strip()

    # 系統自動備註
    df["系統備註"] = df.apply(lambda row: build_remark(row, int(normal_work_hours)), axis=1)

    # 正式版：最終備註完全採用系統自動判斷
    df["最終備註"] = df["系統備註"]

    return df


# =========================
# 統計
# =========================
def build_employee_summary(df: pd.DataFrame) -> pd.DataFrame:
    summary = df.groupby(["emp_id", "emp_name", "department"], dropna=False).agg(
        遲到次數=("遲到", "sum"),
        早退次數=("早退", "sum"),
        漏打卡次數=("漏打卡", "sum"),
        工時過長次數=("工時過長", "sum"),
        工時過短次數=("工時過短", "sum"),
        工時不足次數=("工時不足", "sum"),
        總異常次數=("是否異常", "sum"),
    ).reset_index()
    return summary


def build_department_summary(df: pd.DataFrame) -> pd.DataFrame:
    summary = df.groupby("department", dropna=False).agg(
        遲到次數=("遲到", "sum"),
        早退次數=("早退", "sum"),
        漏打卡次數=("漏打卡", "sum"),
        工時過長次數=("工時過長", "sum"),
        工時過短次數=("工時過短", "sum"),
        工時不足次數=("工時不足", "sum"),
        總異常次數=("是否異常", "sum"),
    ).reset_index().rename(columns={"department": "部門"})
    return summary


# =========================
# 匯出 Excel
# =========================
def to_excel_bytes(sheets: dict) -> bytes:
    buf = io.BytesIO()
    with pd.ExcelWriter(buf, engine="openpyxl") as writer:
        for sheet_name, df_sheet in sheets.items():
            df_sheet.to_excel(writer, sheet_name=sheet_name, index=False)
    return buf.getvalue()


# =========================
# 顯示用 index 從 1 開始
# =========================
def reset_display_index(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    df.index = range(1, len(df) + 1)
    return df


# =========================
# Streamlit UI
# =========================
st.set_page_config(page_title="打卡稽核系統", layout="wide", page_icon="🕐")

with st.sidebar:
    st.header("⚙️ 稽核參數設定")

    late_time_input = st.time_input(
        "遲到判定時間",
        value=pd.Timestamp(f"2000-01-01 {LATE_TIME}").time()
    )
    early_time_input = st.time_input(
        "早退判定時間",
        value=pd.Timestamp(f"2000-01-01 {EARLY_LEAVE_TIME}").time()
    )
    max_hours_input = st.number_input(
        "工時過長上限（小時）",
        min_value=1.0,
        max_value=24.0,
        value=float(MAX_WORK_HOURS)
    )
    min_hours_input = st.number_input(
        "工時過短下限（小時）",
        min_value=0.0,
        max_value=12.0,
        value=float(MIN_WORK_HOURS)
    )
    normal_hours_input = st.number_input(
        "標準工時（小時，低於此值視為工時不足）",
        min_value=1.0,
        max_value=24.0,
        value=float(NORMAL_WORK_HOURS)
    )

    st.divider()
    st.caption("Google Sheet 設定")
    sheet_url = st.text_input(
        "試算表連結",
        value=GOOGLE_SHEET_URL
    )
    worksheet_name = st.text_input(
        "工作表名稱",
        value=GOOGLE_WORKSHEET_NAME
    )

    late_time_str = late_time_input.strftime("%H:%M")
    early_leave_time_str = early_time_input.strftime("%H:%M")

st.title("🕐 Google 打卡稽核系統")
st.caption("連接 Google Sheets，自動偵測遲到 / 早退 / 漏打卡 / 異常工時 / 工時不足 / 自動備註")

run_btn = st.button("📥 讀取並開始分析", type="primary", use_container_width=True)

if run_btn:
    with st.spinner("讀取 Google Sheet 中..."):
        try:
            df_raw = load_google_sheet_by_url(sheet_url, worksheet_name)
        except Exception as e:
            st.error(f"❌ 無法讀取 Google Sheet：{e}")
            st.stop()

    with st.spinner("資料清洗與稽核中..."):
        try:
            df_clean = clean_attendance_data(df_raw)
            df_audit = audit_attendance(
                df_clean,
                late_time=late_time_str,
                early_leave_time=early_leave_time_str,
                max_work_hours=max_hours_input,
                min_work_hours=min_hours_input,
                normal_work_hours=normal_hours_input,
            )
            emp_summary = build_employee_summary(df_audit)
            dept_summary = build_department_summary(df_audit)
            abnormal_df = df_audit[df_audit["是否異常"]].copy()
        except Exception as e:
            st.error(f"❌ 資料處理失敗：{e}")
            st.stop()

    total = len(df_audit)
    abn_cnt = int(df_audit["是否異常"].sum())
    late_cnt = int(df_audit["遲到"].sum())
    early_cnt = int(df_audit["早退"].sum())
    miss_cnt = int(df_audit["漏打卡"].sum())
    short_cnt = int(df_audit["工時不足"].sum())

    c1, c2, c3, c4, c5, c6 = st.columns(6)
    c1.metric("📋 總筆數", total)
    c2.metric(
        "⚠️ 總異常數",
        abn_cnt,
        delta=f"{abn_cnt / total * 100:.1f}%" if total > 0 else "0%",
        delta_color="inverse"
    )
    c3.metric("🕗 遲到", late_cnt)
    c4.metric("🏃 早退", early_cnt)
    c5.metric("❓ 漏打卡", miss_cnt)
    c6.metric("⏱️ 工時不足", short_cnt)

    st.divider()

    tab1, tab2, tab3, tab4, tab5 = st.tabs([
        "📊 圖表總覽",
        "⚠️ 異常明細",
        "👤 員工統計",
        "🏢 部門統計",
        "🗃️ 原始資料"
    ])

    with tab1:
        col_a, col_b = st.columns(2)

        with col_a:
            st.subheader("各部門異常次數")
            if not dept_summary.empty:
                fig = px.bar(
                    dept_summary,
                    x="部門",
                    y="總異常次數",
                    color="總異常次數",
                    color_continuous_scale="Reds",
                    text="總異常次數"
                )
                fig.update_traces(textposition="outside")
                fig.update_layout(showlegend=False, height=350)
                st.plotly_chart(fig, use_container_width=True)

        with col_b:
            st.subheader("異常類型分佈")
            anomaly_types = {
                "遲到": late_cnt,
                "早退": early_cnt,
                "漏打卡": miss_cnt,
                "工時過長": int(df_audit["工時過長"].sum()),
                "工時過短": int(df_audit["工時過短"].sum()),
                "工時不足": short_cnt,
            }
            fig2 = px.pie(
                names=list(anomaly_types.keys()),
                values=list(anomaly_types.values()),
                hole=0.4
            )
            fig2.update_layout(height=350)
            st.plotly_chart(fig2, use_container_width=True)

        st.subheader("員工工時分佈")
        work_hours_data = df_audit.dropna(subset=["工時"])
        if not work_hours_data.empty:
            fig3 = px.histogram(
                work_hours_data,
                x="工時",
                nbins=20,
                labels={"工時": "工時（小時）", "count": "筆數"}
            )
            fig3.add_vline(
                x=min_hours_input,
                line_dash="dash",
                line_color="orange",
                annotation_text="最短工時"
            )
            fig3.add_vline(
                x=normal_hours_input,
                line_dash="dash",
                line_color="blue",
                annotation_text=f"標準工時({normal_hours_input:g}h)"
            )
            fig3.add_vline(
                x=max_hours_input,
                line_dash="dash",
                line_color="red",
                annotation_text="最長工時"
            )
            fig3.update_layout(height=300)
            st.plotly_chart(fig3, use_container_width=True)

    with tab2:
        st.subheader(f"⚠️ 異常紀錄（共 {len(abnormal_df)} 筆）")

        col_f1, col_f2 = st.columns(2)
        with col_f1:
            anomaly_filter = st.multiselect(
                "篩選異常類型",
                ["遲到", "早退", "漏打卡", "工時過長", "工時過短", "工時不足"],
                default=[]
            )
        with col_f2:
            dept_options = ["全部"] + sorted(df_audit["department"].dropna().astype(str).unique().tolist())
            dept_filter = st.selectbox("篩選部門", dept_options)

        filtered = abnormal_df.copy()

        if anomaly_filter:
            mask = pd.Series(False, index=filtered.index)
            for col in anomaly_filter:
                mask = mask | filtered[col]
            filtered = filtered[mask]

        if dept_filter != "全部":
            filtered = filtered[filtered["department"].astype(str) == dept_filter]

        display_cols = [
            "emp_id",
            "emp_name",
            "department",
            "date",
            "check_in",
            "check_out",
            "工時",
            "遲到",
            "早退",
            "漏打卡",
            "工時過長",
            "工時過短",
            "工時不足",
            "系統備註",
            "最終備註",
        ]
        display_cols = [c for c in display_cols if c in filtered.columns]
        flag_cols = [c for c in ["遲到", "早退", "漏打卡", "工時過長", "工時過短", "工時不足"] if c in filtered.columns]

        st.dataframe(
            reset_display_index(filtered[display_cols]).style.applymap(
                lambda v: "background-color: #ffe0e0;" if v is True else "",
                subset=flag_cols
            ),
            use_container_width=True,
            height=400
        )

    with tab3:
        st.subheader("👤 員工異常統計")
        sort_col = st.selectbox(
            "排序依據",
            ["總異常次數", "遲到次數", "早退次數", "漏打卡次數", "工時不足次數"]
        )
        emp_sorted = emp_summary.sort_values(sort_col, ascending=False)
        st.dataframe(reset_display_index(emp_sorted), use_container_width=True, height=400)

        if not emp_sorted.empty:
            top10 = emp_sorted.head(10)
            fig4 = px.bar(
                top10,
                x="emp_name",
                y=sort_col,
                color=sort_col,
                color_continuous_scale="Oranges",
                title=f"Top 10 員工 — {sort_col}",
                text=sort_col
            )
            fig4.update_traces(textposition="outside")
            fig4.update_layout(showlegend=False, height=350)
            st.plotly_chart(fig4, use_container_width=True)

    with tab4:
        st.subheader("🏢 部門異常統計")
        st.dataframe(reset_display_index(dept_summary), use_container_width=True)

        if not dept_summary.empty:
            melt = dept_summary.melt(id_vars="部門", var_name="類型", value_name="次數")
            fig5 = px.bar(
                melt[melt["類型"] != "總異常次數"],
                x="部門",
                y="次數",
                color="類型",
                barmode="group",
                title="部門異常類型明細"
            )
            fig5.update_layout(height=350)
            st.plotly_chart(fig5, use_container_width=True)

    with tab5:
        st.subheader("🗃️ 原始資料")
        st.dataframe(reset_display_index(df_raw), use_container_width=True, height=400)

    st.divider()
    st.subheader("📤 匯出報表")

    excel_bytes = to_excel_bytes({
        "異常明細": abnormal_df,
        "員工統計": emp_summary,
        "部門統計": dept_summary,
        "完整稽核": df_audit,
    })

    st.download_button(
        label="⬇️ 下載 Excel 報表（含四個工作表）",
        data=excel_bytes,
        file_name="打卡稽核報表.xlsx",
        mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        use_container_width=True,
        type="primary"
    )

    st.success("✅ 分析完成！")