#!/usr/bin/env python3
"""
Fill LFCS timesheet template with Week 10 hours (March 2-7, 2026)
"""

import zipfile
import xml.etree.ElementTree as ET
import re
import os
from datetime import datetime, timedelta

def fill_timesheet(template_path, output_path, name, start_date, hours_dict):
    """
    Fill LFCS timesheet template
    
    Args:
        template_path: Path to template Excel file
        output_path: Path to save filled timesheet
        name: Employee name
        start_date: First day of week (YYYY-MM-DD)
        hours_dict: Dict of day->hours (e.g., {'Mon': 8, 'Tue': 10, ...})
    """
    
    # Days mapping
    days_order = ['Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun']
    day_names = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
    
    # Parse start date
    start = datetime.strptime(start_date, '%Y-%m-%d')
    
    # Create working copy
    import shutil
    shutil.copy2(template_path, output_path)
    
    # Open the Excel file (which is a ZIP)
    with zipfile.ZipFile(output_path, 'r') as z:
        # Read shared strings
        shared_strings = []
        if 'xl/sharedStrings.xml' in z.namelist():
            with z.open('xl/sharedStrings.xml') as f:
                content = f.read().decode('utf-8')
                # Extract all <t> text elements
                matches = re.findall(r'<t[^>]*>([^<]+)</t>', content)
                shared_strings = matches
        
        print(f"Found {len(shared_strings)} shared strings")
        
        # Find indices for days
        day_indices = {}
        for i, s in enumerate(shared_strings):
            s_clean = s.strip()
            if s_clean in days_order:
                day_indices[s_clean] = i
                print(f"  Day '{s_clean}' at index {i}")
        
        # Read sheet XML
        sheet_xml = None
        if 'xl/worksheets/sheet1.xml' in z.namelist():
            with z.open('xl/worksheets/sheet1.xml') as f:
                sheet_xml = f.read().decode('utf-8')
    
    # We need to modify the sheet XML
    # For now, let's create a simple report of what needs to be filled
    print("\n=== TIMESHEET TO FILL ===")
    print(f"Name: {name}")
    print(f"Week: {start_date} to {(start + timedelta(days=6)).strftime('%Y-%m-%d')}")
    print("\nDaily hours:")
    for i, day_abbr in enumerate(days_order):
        date = start + timedelta(days=i)
        hours = hours_dict.get(day_abbr, 0)
        print(f"  {day_abbr} {date.strftime('%Y-%m-%d')}: {hours} hours")
    
    total_hours = sum(hours_dict.values())
    print(f"\nTotal hours: {total_hours}")
    
    # Create a simple text version
    text_output = f"""LF CONSTRUCTION SERVICES TIMESHEET

Name: {name}
Dates: Week 10 ({start_date} to {(start + timedelta(days=6)).strftime('%Y-%m-%d')})

Day        Date       Hours  Work Description
---        ----       -----  ----------------
"""
    
    for i, day_abbr in enumerate(days_order):
        date = start + timedelta(days=i)
        hours = hours_dict.get(day_abbr, 0)
        if hours > 0:
            text_output += f"{day_abbr:<10} {date.strftime('%Y-%m-%d')}  {hours:<6}  Powerhouse Museum Parramatta / 69 Phillip St\n"
    
    text_output += f"\nTotal Hours: {total_hours}\n"
    text_output += "\nCORRECTION: Previously sent Week 9 hours (Feb 16-21, 59hrs) in error.\n"
    text_output += "This is the corrected Week 10 timesheet.\n"
    
    return text_output

if __name__ == "__main__":
    # Week 10 hours (March 2-7, 2026)
    hours = {
        'Mon': 8,    # March 2
        'Tue': 10,   # March 3
        'Wed': 10,   # March 4
        'Thur': 10,  # March 5
        'Fri': 10,   # March 6
        'Sat': 8,    # March 7
        'Sun': 0     # March 8
    }
    
    template = "/home/ccuser/rateright-growth/rivet/timesheet-template.xlsx"
    output = "/home/ccuser/rateright-growth/rivet/timesheet-Michael-W10-2Mar-7Mar-FILLED.xlsx"
    
    text_version = fill_timesheet(
        template_path=template,
        output_path=output,
        name="Michael McLoughlin",
        start_date="2026-03-02",
        hours_dict=hours
    )
    
    # Save text version
    with open("/home/ccuser/rateright-growth/rivet/timesheet-Michael-W10-2Mar-7Mar-TEXT.txt", "w") as f:
        f.write(text_version)
    
    print("\n" + "="*50)
    print("TEXT VERSION CREATED:")
    print("="*50)
    print(text_version)
    print("\nFiles created:")
    print(f"  1. {output} (Excel - needs manual filling)")
    print(f"  2. /home/ccuser/rateright-growth/rivet/timesheet-Michael-W10-2Mar-7Mar-TEXT.txt")