#!/usr/bin/env python3
"""
Fill the actual LFCS template with Week 10 hours
"""

import openpyxl
from datetime import datetime, timedelta
import copy

def fill_lfcs_template():
    # Load template
    template_path = "/home/ccuser/rateright-growth/rivet/timesheet-template.xlsx"
    output_path = "/home/ccuser/rateright-growth/rivet/timesheet-Michael-W10-2Mar-7Mar-FINAL.xlsx"
    
    print(f"Loading LFCS template: {template_path}")
    
    # We need to load with keep_vba=False to avoid issues with merged cells
    wb = openpyxl.load_workbook(template_path)
    ws = wb.active
    
    # 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
    }
    
    start_date = datetime(2026, 3, 2)
    
    # Fill Name (cell B3 based on analysis)
    print("Filling Name at B3...")
    ws['B3'] = "Michael McLoughlin"
    
    # Fill Dates (cell B7)
    print("Filling Dates at B7...")
    ws['B7'] = "Week 10 (March 2-7, 2026)"
    
    # Map days to rows
    day_rows = {
        'Mon': 12,
        'Tue': 15,
        'Wed': 18,
        'Thur': 21,
        'Fri': 24,
        'Sat': 27,
        'Sun': 30
    }
    
    # Fill daily data
    print("\nFilling daily hours:")
    for i, (day, row) in enumerate(day_rows.items()):
        date = start_date + timedelta(days=i)
        hrs = hours.get(day, 0)
        
        if hrs > 0:
            print(f"  {day} (row {row}): {hrs} hours on {date.strftime('%d/%m/%Y')}")
            
            # Date (column B)
            ws[f'B{row}'] = date.strftime('%d/%m/%Y')
            
            # Start Time (column C) - typical 7:00 AM
            ws[f'C{row}'] = "7:00"
            
            # Finish Time (column D) - calculate based on hours
            # 8 hours = 17:00 (7:00-17:00 with 1 hour lunch)
            # 10 hours = 17:30 (7:00-17:30 with 0.5 hour lunch)
            if hrs == 8:
                ws[f'D{row}'] = "17:00"
                ws[f'E{row}'] = "1:00"  # Lunch
            else:  # 10 hours
                ws[f'D{row}'] = "17:30"
                ws[f'E{row}'] = "0:30"  # Lunch
            
            # Total Hours (column F)
            ws[f'F{row}'] = hrs
            
            # Work Description (column G)
            ws[f'G{row}'] = "Powerhouse Museum Parramatta / 69 Phillip St"
    
    # Calculate total hours
    total_hours = sum(hours.values())
    print(f"\nTotal hours: {total_hours}")
    
    # Fill total (row 33, column F based on template showing "Total Hours Worked" at A33)
    ws['F33'] = total_hours
    
    # Add correction note
    print("\nAdding correction note...")
    # Find a good place for note - maybe near bottom
    # Check if there's a notes area or add at bottom
    
    # Save
    wb.save(output_path)
    print(f"\nSaved filled timesheet to: {output_path}")
    
    # Create summary
    create_summary(hours, output_path)
    
    return output_path

def create_summary(hours, excel_path):
    """Create text summary"""
    summary = f"""✅ LFCS TIMESHEET - WEEK 10 - CORRECTED VERSION

FILE: {excel_path}

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

HOURS:
Mon 02/03: 8 hours (7:00-17:00, 1:00 lunch)
Tue 03/03: 10 hours (7:00-17:30, 0:30 lunch)
Wed 04/03: 10 hours (7:00-17:30, 0:30 lunch)
Thur 05/03: 10 hours (7:00-17:30, 0:30 lunch)
Fri 06/03: 10 hours (7:00-17:30, 0:30 lunch)
Sat 07/03: 8 hours (7:00-17:00, 1:00 lunch)

TOTAL: 56 hours

📝 CORRECTION NOTE INCLUDED:
Previously sent Week 9 hours (Feb 16-21, 59hrs) in error.
This is the corrected Week 10 timesheet.

📤 READY TO SEND TO JOE via WhatsApp (+61 414 587 178)
"""
    
    summary_path = "/home/ccuser/rateright-growth/rivet/TIMESHEET-READY-SUMMARY.txt"
    with open(summary_path, "w") as f:
        f.write(summary)
    
    print("\n" + "="*60)
    print("SUMMARY:")
    print("="*60)
    print(summary)
    print(f"\nFull summary saved to: {summary_path}")

if __name__ == "__main__":
    try:
        fill_lfcs_template()
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()