#!/usr/bin/env python3
"""
Fill LFCS timesheet template using openpyxl
"""

import openpyxl
from datetime import datetime, timedelta
import sys

def fill_lfcs_timesheet():
    # Load template
    template_path = "/home/ccuser/rateright-growth/rivet/timesheet-template.xlsx"
    output_path = "/home/ccuser/rateright-growth/rivet/timesheet-Michael-W10-2Mar-7Mar-FILLED-FINAL.xlsx"
    
    print(f"Loading template: {template_path}")
    wb = openpyxl.load_workbook(template_path)
    ws = wb.active
    
    # Week 10 hours
    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
    }
    
    # Find cells with placeholders
    print("\nSearching for cells to fill...")
    
    # Scan cells for key markers
    name_cell = None
    date_cell = None
    day_cells = {}
    
    for row in ws.iter_rows(min_row=1, max_row=50, min_col=1, max_col=10):
        for cell in row:
            if cell.value:
                cell_str = str(cell.value).strip()
                
                # Look for "Name:" label
                if "Name:" in cell_str and name_cell is None:
                    # Name should be in next cell
                    name_cell = ws.cell(row=cell.row, column=cell.column + 1)
                    print(f"Found 'Name:' at {cell.coordinate}, will fill at {name_cell.coordinate}")
                
                # Look for "Dates:" label  
                elif "Dates:" in cell_str and date_cell is None:
                    # Dates should be in next cell
                    date_cell = ws.cell(row=cell.row, column=cell.column + 1)
                    print(f"Found 'Dates:' at {cell.coordinate}, will fill at {date_cell.coordinate}")
                
                # Look for day abbreviations
                elif cell_str in ['Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun']:
                    day_cells[cell_str] = cell
                    print(f"Found day '{cell_str}' at {cell.coordinate}")
    
    # Fill the cells
    print("\nFilling timesheet...")
    
    # Fill name
    if name_cell:
        name_cell.value = "Michael McLoughlin"
        print(f"Set name to: {name_cell.value}")
    
    # Fill dates
    if date_cell:
        date_cell.value = "Week 10 (March 2-7, 2026)"
        print(f"Set dates to: {date_cell.value}")
    
    # For hours, we need to find the corresponding hour cells
    # In LFCS template, hours are typically in column after date
    # Let's search for hour cells near day cells
    print("\nAttempting to fill hours...")
    
    # Simple approach: Try to fill based on pattern
    # Look for rows with day names and fill hours in nearby columns
    
    # First, let's see the structure by printing some rows
    print("\nSample of sheet structure (rows 10-20):")
    for row in range(10, 21):
        row_vals = []
        for col in range(1, 8):
            val = ws.cell(row=row, column=col).value
            row_vals.append(str(val) if val is not None else "")
        print(f"Row {row:2}: {row_vals}")
    
    # Save the workbook
    wb.save(output_path)
    print(f"\nSaved filled timesheet to: {output_path}")
    
    # Also create a simple text summary
    text_summary = create_text_summary(hours)
    text_path = "/home/ccuser/rateright-growth/rivet/timesheet-Michael-W10-2Mar-7Mar-SUMMARY.txt"
    with open(text_path, "w") as f:
        f.write(text_summary)
    
    print(f"Created text summary: {text_path}")
    print("\n" + "="*60)
    print("TIMESHEET SUMMARY:")
    print("="*60)
    print(text_summary)

def create_text_summary(hours):
    """Create a text summary of the timesheet"""
    start_date = "2026-03-02"
    start = datetime.strptime(start_date, '%Y-%m-%d')
    
    summary = f"""LF CONSTRUCTION SERVICES TIMESHEET - CORRECTED VERSION

Employee: Michael McLoughlin
Week: 10 (March 2-7, 2026)
Project: Powerhouse Museum Parramatta / 69 Phillip St
Client: Ford Civil / Lendlease

DAILY HOURS:
------------"""
    
    days_order = ['Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun']
    total = 0
    
    for i, day in enumerate(days_order):
        date = start + timedelta(days=i)
        hrs = hours.get(day, 0)
        if hrs > 0:
            summary += f"\n{day} {date.strftime('%d/%m')}: {hrs} hours"
            total += hrs
    
    summary += f"\n\nTOTAL HOURS: {total}"
    summary += "\n\nCORRECTION NOTE:"
    summary += "\nPreviously sent Week 9 hours (Feb 16-21, 59hrs) in error."
    summary += "\nThis is the corrected Week 10 timesheet."
    summary += "\n\nPlease use the attached Excel file for official submission."
    
    return summary

if __name__ == "__main__":
    try:
        fill_lfcs_timesheet()
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()
        
        # Create text version as fallback
        hours = {'Mon': 8, 'Tue': 10, 'Wed': 10, 'Thur': 10, 'Fri': 10, 'Sat': 8, 'Sun': 0}
        text_summary = create_text_summary(hours)
        print("\n" + "="*60)
        print("FALLBACK TEXT VERSION:")
        print("="*60)
        print(text_summary)