#!/usr/bin/env python3
"""
Create LFCS timesheet from scratch
"""

import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
from datetime import datetime, timedelta

def create_lfcs_timesheet():
    # Create new workbook
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Timesheet"
    
    # 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
    }
    
    # Styles
    header_font = Font(bold=True, size=14)
    label_font = Font(bold=True)
    border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    # Header
    ws['A1'] = "LF CONSTRUCTION SERVICES TIMESHEET"
    ws['A1'].font = header_font
    ws.merge_cells('A1:H1')
    ws['A1'].alignment = Alignment(horizontal='center')
    
    # Company info
    ws['A3'] = "Licence No  292303c"
    ws['A4'] = "accounts@lfcs.com.au"
    ws['A5'] = "admin@lfcs.com.au"
    
    # Employee info
    ws['A7'] = "Name:"
    ws['B7'] = "Michael McLoughlin"
    ws['B7'].font = label_font
    
    ws['A8'] = "Dates:"
    ws['B8'] = "Week 10 (March 2-7, 2026)"
    ws['B8'].font = label_font
    
    # Table headers
    headers = ['Day', 'Date', 'Start Time', 'Finish Time', 'Lunch Duration', 'Total Hours Worked', 'Work Description']
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=10, column=col, value=header)
        cell.font = label_font
        cell.border = border
        cell.alignment = Alignment(horizontal='center')
    
    # Fill days
    start_date = datetime(2026, 3, 2)
    days_order = ['Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun']
    
    row = 11
    for i, day in enumerate(days_order):
        date = start_date + timedelta(days=i)
        hrs = hours.get(day, 0)
        
        # Only show days with hours
        if hrs > 0:
            # Day
            ws.cell(row=row, column=1, value=day).border = border
            
            # Date
            ws.cell(row=row, column=2, value=date.strftime('%d/%m/%Y')).border = border
            
            # Typical construction hours: 7:00 AM - 5:30 PM with 30 min lunch
            ws.cell(row=row, column=3, value="7:00").border = border
            ws.cell(row=row, column=4, value="17:30").border = border
            ws.cell(row=row, column=5, value="0:30").border = border
            
            # Hours
            ws.cell(row=row, column=6, value=hrs).border = border
            
            # Work description
            ws.cell(row=row, column=7, value="Powerhouse Museum Parramatta / 69 Phillip St").border = border
            
            row += 1
    
    # Total row
    total_row = row + 1
    ws.cell(row=total_row, column=5, value="TOTAL HOURS:").font = label_font
    ws.cell(row=total_row, column=6, value=sum(hours.values())).font = label_font
    ws.cell(row=total_row, column=6).border = border
    
    # Client info
    client_row = total_row + 2
    ws.cell(row=client_row, column=1, value="From:").font = label_font
    ws.cell(row=client_row, column=2, value="Michael McLoughlin")
    
    ws.cell(row=client_row + 1, column=1, value="Client / Project Name & Address:").font = label_font
    ws.merge_cells(f'B{client_row + 1}:H{client_row + 1}')
    ws.cell(row=client_row + 1, column=2, value="Ford Civil / Lendlease - Powerhouse Museum Parramatta, 69 Phillip St")
    
    ws.cell(row=client_row + 2, column=1, value="To:").font = label_font
    ws.cell(row=client_row + 2, column=2, value="Joe @ Ford Civil")
    
    # Signature area
    sig_row = client_row + 4
    ws.cell(row=sig_row, column=1, value="Signature:").font = label_font
    ws.merge_cells(f'B{sig_row}:D{sig_row}')
    
    ws.cell(row=sig_row + 1, column=1, value="Date:").font = label_font
    ws.cell(row=sig_row + 1, column=2, value=datetime.now().strftime('%d/%m/%Y'))
    
    # Approval
    ws.cell(row=sig_row + 3, column=1, value="Approval Signature:").font = label_font
    ws.merge_cells(f'B{sig_row + 3}:D{sig_row + 3}')
    
    # Note
    note_row = sig_row + 5
    ws.merge_cells(f'A{note_row}:H{note_row + 2}')
    note_cell = ws.cell(row=note_row, column=1)
    note_cell.value = "CORRECTION: Previously sent Week 9 hours (Feb 16-21, 59hrs) in error. This is the corrected Week 10 timesheet."
    note_cell.font = Font(italic=True, color="FF0000")
    
    # Adjust column widths
    for col in range(1, 8):
        ws.column_dimensions[openpyxl.utils.get_column_letter(col)].width = 15
    
    # Save
    output_path = "/home/ccuser/rateright-growth/rivet/timesheet-Michael-W10-2Mar-7Mar-LFCS.xlsx"
    wb.save(output_path)
    
    print(f"Created LFCS timesheet: {output_path}")
    
    # Create text summary
    text_summary = create_text_summary(hours)
    text_path = "/home/ccuser/rateright-growth/rivet/timesheet-Michael-W10-2Mar-7Mar-READY.txt"
    with open(text_path, "w") as f:
        f.write(text_summary)
    
    print(f"Created text summary: {text_path}")
    print("\n" + "="*60)
    print("TIMESHEET READY FOR SUBMISSION:")
    print("="*60)
    print(text_summary)
    
    return output_path

def create_text_summary(hours):
    """Create a text summary"""
    summary = f"""LF CONSTRUCTION SERVICES TIMESHEET - WEEK 10 (CORRECTED)

EMPLOYEE: Michael McLoughlin
WEEK: 10 (March 2-7, 2026)
PROJECT: Powerhouse Museum Parramatta / 69 Phillip St
CLIENT: Ford Civil / Lendlease

DAILY HOURS:
------------
Mon 02/03: 8 hours (7:00-17:30, 0:30 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:30, 0:30 lunch)

TOTAL HOURS: 56

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

FILE: timesheet-Michael-W10-2Mar-7Mar-LFCS.xlsx
READY TO SEND TO JOE VIA WHATSAPP
"""
    return summary

if __name__ == "__main__":
    create_lfcs_timesheet()