#!/usr/bin/env python3
"""
填入加班登記表 & 補休登記表

OT 計算來源：2026.01 / 2026.02 / 2026.03 月份工作表（L > 0）
補休條目來源：2026年加班登記表（選擇補休）右側 J~N 欄（動態讀取，不硬編碼）

輸出目標：
  2026年加班登記表          B4:G{n}, H2=累積時數
  2026年加班選擇補休登記表   C~G 各補休行, H9/H24/H39=月後剩餘
"""

import re
from collections import defaultdict
from google.oauth2 import service_account
from googleapiclient.discovery import build

SPREADSHEET_ID = '1Y708RZgZHL3Gqah9ujiw5brMuxAoW3RFl0txfXq8qrE'
SOURCE_SHEET   = '2026年加班登記表（選擇補休）'
OT_SHEET       = '2026年加班登記表'
COMP_SHEET     = '2026年加班選擇補休登記表'

MONTHS         = ['2026.01', '2026.02', '2026.03']
ALREADY_IN_DATES = {'2026-01-02'}   # seq1 已手動預存

JAN_TOTAL_ROW  = 9
FEB_TOTAL_ROW  = 24
MAR_TOTAL_ROW  = 39
OT_WRITE_START = 4    # seq1 在 row3，seq2 起從 row4 寫入
JAN_COMP_START = 2
FEB_COMP_START = 10
MAR_COMP_START = 25

creds = service_account.Credentials.from_service_account_file(
    'service_account_key.json',
    scopes=['https://www.googleapis.com/auth/spreadsheets']
)
service = build('sheets', 'v4', credentials=creds)

# ══════════════════════════════════════════════════════════
# 時間工具
# ══════════════════════════════════════════════════════════

def parse_hhmm(t):
    """'HH:MM' → minutes"""
    h, m = str(t).strip().split(':')
    return int(h) * 60 + int(m)

def fmt_hhmm(mins):
    """minutes → 'H:MM'（可超過 24h）"""
    return f"{mins // 60}:{mins % 60:02d}"

def fmt_clock(mins):
    """minutes → 'HH:MM'（不超 24h 顯示）"""
    return f"{(mins // 60) % 24:02d}:{mins % 60:02d}"

def date_to_display(date_str):
    """'2026-01-06' → '2026/1/6'"""
    y, mo, d = date_str.split('-')
    return f"{y}/{int(mo)}/{int(d)}"

def parse_g_scheduled_end(g_str):
    """解析 G 欄取得應下班時間"""
    match = re.search(r'應下班(\d{1,2}:\d{2})', str(g_str))
    return match.group(1) if match else None

# ══════════════════════════════════════════════════════════
# Tier 定義
# ══════════════════════════════════════════════════════════

STANDARD_TIERS = [
    (0,   60,    '一般第8小時'),
    (60,  180,   '一般第9至第10小時'),
    (180, 99999, '一般第11至第12小時'),
]
RESTDAY_TIERS = [
    (0,   120,   '休息日第1至第2小時'),
    (120, 99999, '休息日第3至第8小時'),
]

def apply_dinner_break(ot_start, ot_end, i_val):
    DINNER_S, DINNER_E = 20 * 60, 21 * 60
    if i_val >= 1 and ot_start < DINNER_S < ot_end:
        segs = [(ot_start, DINNER_S)]
        if ot_end > DINNER_E:
            segs.append((DINNER_E, ot_end))
        return segs
    return [(ot_start, ot_end)]

def get_ot_segments(attr, e_str, f_str, g_str, i_val):
    e_min = parse_hhmm(e_str)
    f_min = parse_hhmm(f_str)
    if f_min < e_min:
        f_min += 24 * 60

    is_rest = ('休息日' in attr) and ('上班日' not in attr)

    if is_rest:
        ot_start = e_min
        if i_val >= 1:
            break_s, break_e = ot_start + 120, ot_start + 180
            segs = [(ot_start, break_s)]
            if break_e < f_min:
                segs.append((break_e, f_min))
        else:
            segs = [(ot_start, f_min)]
        return True, ot_start, segs

    elif '上班日加班' in attr:
        return False, e_min, apply_dinner_break(e_min, f_min, i_val)

    else:
        sched_end = parse_g_scheduled_end(g_str)
        ot_start = parse_hhmm(sched_end) if sched_end else e_min
        ot_end = f_min
        if ot_end < ot_start:
            ot_end += 24 * 60
        return False, ot_start, apply_dinner_break(ot_start, ot_end, i_val)

def assign_tiers(segments, daily_ot_so_far, is_rest_day):
    tiers = RESTDAY_TIERS if is_rest_day else STANDARD_TIERS
    entries = []
    cumulative = daily_ot_so_far

    for seg_start, seg_end in segments:
        pos = seg_start
        remaining = seg_end - seg_start
        while remaining > 0:
            cur_label = tiers[-1][2]
            cur_max   = 99999
            for t_lo, t_hi, label in tiers:
                if t_lo <= cumulative < t_hi:
                    cur_label = label
                    cur_max   = t_hi
                    break
            take = min(remaining, cur_max - cumulative)
            entries.append((fmt_clock(pos), fmt_clock(pos + take), take, cur_label))
            pos        += take
            cumulative += take
            remaining  -= take

    return entries, cumulative

# ══════════════════════════════════════════════════════════
# STEP 1：讀取各月 OT 資料（L > 0）
# ══════════════════════════════════════════════════════════

raw_records = []

for month in MONTHS:
    res = service.spreadsheets().values().get(
        spreadsheetId=SPREADSHEET_ID,
        range=f'{month}!A1:O200'
    ).execute()
    for row in res.get('values', [])[1:]:
        while len(row) < 15:
            row.append('')

        date_b = row[1].strip()
        attr   = row[3].strip()
        e_str  = row[4].strip()
        f_str  = row[5].strip()
        g_str  = row[6].strip()
        i_str  = row[8].strip()
        l_str  = row[11].strip()

        if not date_b or not e_str or not f_str:
            continue
        try:
            l_h = float(l_str) if l_str else 0
        except Exception:
            l_h = 0
        if l_h <= 0:
            continue
        if date_b in ALREADY_IN_DATES:
            continue
        try:
            i_val = int(float(i_str)) if i_str else 0
        except Exception:
            i_val = 0

        raw_records.append({
            'date': date_b, 'display': date_to_display(date_b),
            'attr': attr, 'e': e_str, 'f': f_str,
            'g': g_str, 'i': i_val, 'month': month,
        })

raw_records.sort(key=lambda x: x['date'])
print(f"共讀取 {len(raw_records)} 筆 OT 記錄（L>0）")

# ══════════════════════════════════════════════════════════
# STEP 2：按日期分組，同日累積 tier 計算
# ══════════════════════════════════════════════════════════

def get_ot_start_key(rec):
    if '休息日' in rec['attr'] and '上班日' not in rec['attr']:
        return parse_hhmm(rec['e'])
    elif '上班日加班' in rec['attr']:
        return parse_hhmm(rec['e'])
    else:
        sched_end = parse_g_scheduled_end(rec['g'])
        return parse_hhmm(sched_end) if sched_end else parse_hhmm(rec['e'])

date_groups = defaultdict(list)
for rec in raw_records:
    date_groups[rec['date']].append(rec)

new_entries = []
seq_credits = {1: 60}   # seq1（1/2, 1:00）已預存
next_seq    = 2

for date_str in sorted(date_groups.keys()):
    recs = sorted(date_groups[date_str], key=get_ot_start_key)
    daily_ot = 0
    for rec in recs:
        is_rest, ot_start, segments = get_ot_segments(
            rec['attr'], rec['e'], rec['f'], rec['g'], rec['i']
        )
        tier_entries, daily_ot = assign_tiers(segments, daily_ot, is_rest)
        for start_s, end_s, dur, label in tier_entries:
            new_entries.append((next_seq, rec['display'], start_s, end_s, fmt_hhmm(dur), label, rec['month']))
            seq_credits[next_seq] = dur
            next_seq += 1

print(f"共產生 {len(new_entries)} 筆新加班序號（seq 2～{next_seq-1}）")

# ══════════════════════════════════════════════════════════
# STEP 3：清除並寫入加班登記表
# ══════════════════════════════════════════════════════════

service.spreadsheets().values().clear(
    spreadsheetId=SPREADSHEET_ID,
    range=f"{OT_SHEET}!B{OT_WRITE_START}:G300",
).execute()

if new_entries:
    rows_to_write = [[e[0], e[1], e[2], e[3], e[4], e[5]] for e in new_entries]
    end_row = OT_WRITE_START + len(rows_to_write) - 1
    service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID,
        range=f"{OT_SHEET}!B{OT_WRITE_START}:G{end_row}",
        valueInputOption='USER_ENTERED',
        body={'values': rows_to_write}
    ).execute()

total_credits = sum(seq_credits.values())
service.spreadsheets().values().update(
    spreadsheetId=SPREADSHEET_ID,
    range=f"{OT_SHEET}!H2",
    valueInputOption='USER_ENTERED',
    body={'values': [[fmt_hhmm(total_credits)]]}
).execute()
print(f"✅ 加班登記表：{len(new_entries)} 行，H2={fmt_hhmm(total_credits)}")

# ══════════════════════════════════════════════════════════
# STEP 4：從來源表讀取補休條目（右側 J~N 欄）
# ══════════════════════════════════════════════════════════

res = service.spreadsheets().values().get(
    spreadsheetId=SPREADSHEET_ID,
    range=f'{SOURCE_SHEET}!A1:O200'
).execute()
src_rows = res.get('values', [])
for r in src_rows:
    while len(r) < 15:
        r.append('')

jan_comp, feb_comp, mar_comp = [], [], []
jan_comp_min = feb_comp_min = mar_comp_min = 0

for r in src_rows[1:]:
    date_j   = r[9].strip()
    if not date_j or '/' not in date_j or '合計' in date_j:
        continue
    start    = r[10].strip()
    end      = r[11].strip()
    hours    = r[12].strip()
    seq_used = r[13].strip()

    if not start or not end or not hours or hours in ('-', '0'):
        continue
    try:
        dur_min = parse_hhmm(hours)
    except Exception:
        continue

    mo    = int(date_j.replace('2026/', '').split('/')[0])
    entry = [date_j, start, end, hours, seq_used]

    if   mo == 1: jan_comp.append(entry); jan_comp_min += dur_min
    elif mo == 2: feb_comp.append(entry); feb_comp_min += dur_min
    else:         mar_comp.append(entry); mar_comp_min += dur_min

print(f"補休條目：1月 {len(jan_comp)} 筆，2月 {len(feb_comp)} 筆，3月 {len(mar_comp)} 筆")

# ══════════════════════════════════════════════════════════
# STEP 5：各月剩餘計算
# ══════════════════════════════════════════════════════════

def seq_month(seq):
    if seq == 1:
        return 'jan'
    idx = seq - 2
    if idx < len(new_entries):
        mo_str = new_entries[idx][1].split('/')[1]   # '2026/2/5' → '2'
        return {'1': 'jan', '2': 'feb', '3': 'mar'}.get(mo_str, 'mar')
    return 'mar'

jan_ot_min = sum(v for k, v in seq_credits.items() if seq_month(k) == 'jan')
feb_ot_min = sum(v for k, v in seq_credits.items() if seq_month(k) == 'feb')
mar_ot_min = sum(v for k, v in seq_credits.items() if seq_month(k) == 'mar')

jan_remain = jan_ot_min - jan_comp_min
feb_remain = jan_remain + feb_ot_min - feb_comp_min
mar_remain = feb_remain + mar_ot_min - mar_comp_min

print(f"OT：1月 {fmt_hhmm(jan_ot_min)}，2月 {fmt_hhmm(feb_ot_min)}，3月 {fmt_hhmm(mar_ot_min)}")
print(f"剩餘：1月後 {fmt_hhmm(jan_remain)}，2月後 {fmt_hhmm(feb_remain)}，3月後 {fmt_hhmm(mar_remain)}")

# ══════════════════════════════════════════════════════════
# STEP 6：寫入補休登記表
# ══════════════════════════════════════════════════════════

def write_comp(entries, start_row, max_rows):
    end_row = start_row + max_rows - 1
    service.spreadsheets().values().clear(
        spreadsheetId=SPREADSHEET_ID,
        range=f"{COMP_SHEET}!C{start_row}:G{end_row}",
    ).execute()
    if entries:
        service.spreadsheets().values().update(
            spreadsheetId=SPREADSHEET_ID,
            range=f"{COMP_SHEET}!C{start_row}:G{start_row + len(entries) - 1}",
            valueInputOption='USER_ENTERED',
            body={'values': entries}
        ).execute()

write_comp(jan_comp, JAN_COMP_START, 7)
write_comp(feb_comp, FEB_COMP_START, 14)
write_comp(mar_comp, MAR_COMP_START, 14)

for row, val in [(JAN_TOTAL_ROW, jan_remain),
                 (FEB_TOTAL_ROW, feb_remain),
                 (MAR_TOTAL_ROW, mar_remain)]:
    service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID,
        range=f"{COMP_SHEET}!H{row}",
        valueInputOption='USER_ENTERED',
        body={'values': [[fmt_hhmm(val)]]}
    ).execute()

print(f"✅ 補休登記表：寫入完成")
print(f"🎉 全部完成！")