#!/usr/bin/env python3
"""
Create an exact copy of LFCS template that we can edit
"""

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

def create_exact_lfcs():
    # Create new workbook matching LFCS template
    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
    }
    
    start_date = datetime(2026, 3, 2)
    
    # Styles
    header_font = Font(bold=True, size=16)
    subtitle_font = Font(size=10)
    label_font = Font(bold=True)
    border_thin = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    # ===== HEADER =====
    ws.merge_cells('A1:H1')
    ws['A1'] = "  LF CONSTRUCTION SERVICES TIMESHEET"
    ws['A1'].font = header_font
    ws['A1'].alignment = Alignment(horizontal='center')
    
    # Licence info
    ws['A3'] = "Licence No  292303c"
    ws['A4'] = "accounts@lfcs.com.au"
    ws['A5'] = "admin@lfcs.com.au"
    ws['A5'].font = subtitle_font
    
    # ===== EMPLOYEE INFO =====
    ws['A7'] = "Name:"
    ws['A7'].font = label_font
    ws.merge_cells('B7:D7')
    ws['B7'] = "Michael McLoughlin"
    
    ws['A8'] = "Dates:"
    ws['A8'].font = label_font
    ws.merge_cells('B8:D8')
    ws['B8'] = "Week 10 (March 2-7, 2026)"
    
    # ===== TABLE HEADERS =====
    headers = [
        ('A10', 'Day'),
        ('B10', 'Date'),
        ('C10', 'Start Time'),
        ('D10', 'Finish Time'),
        ('E10', 'Lunch Duration'),
        ('F10', 'Total Hours Worked'),
        ('G10', 'Client / Project Name & Address'),
        ('J10', 'Work Description')
    ]
    
    for cell_ref, text in headers:
        ws[cell_ref] = text
        ws[cell_ref].font = label_font
        ws[cell_ref].border = border_thin
        ws[cell_ref].alignment = Alignment(horizontal='center', wrap_text=True)
    
    # Merge the wide columns
    ws.merge_cells('G10:I10')  # Client/Project column
    ws.merge_cells('J10:L10')  # Work Description column
    
    # ===== DAY ROWS =====
    day_rows = {
        'Mon': 12,
        'Tue': 15,
        'Wed': 18,
        'Thur': 21,
        'Fri': 24,
        'Sat': 27,
        'Sun': 30
    }
    
    # Fill days
    for i, (day, row) in enumerate(day_rows.items()):
        date = start_date + timedelta(days=i)
        hrs = hours.get(day, 0)
        
        # Day label
        ws[f'A{row}'] = day
        ws[f'A{row}'].border = border_thin
        
        if hrs > 0:
            # Date
            ws[f'B{row}'] = date.strftime('%d/%m/%Y')
            ws[f'B{row}'].border = border_thin
            
            # Times (based on hours)
            if hrs == 8:
                ws[f'C{row}'] = "7:00"   # Start
                ws[f'D{row}'] = "17:00"  # Finish
                ws[f'E{row}'] = "1:00"   # Lunch
            else:  # 10 hours
                ws[f'C{row}'] = "7:00"   # Start
                ws[f'D{row}'] = "17:30"  # Finish
                ws[f'E{row}'] = "0:30"   # Lunch
            
            # Apply borders to time cells
            for col in ['C', 'D', 'E']:
                ws[f'{col}{row}'].border = border_thin
            
            # Total hours
            ws[f'F{row}'] = hrs
            ws[f'F{row}'].border = border_thin
            
            # Client/Project
            ws[f'G{row}'] = "Ford Civil / Lendlease - Powerhouse Museum Parramatta"
            ws.merge_cells(f'G{row}:I{row}')
            ws[f'G{row}'].border = border_thin
            
            # Work Description
            ws[f'J{row}'] = "General Foreman - Steelfixing/Formwork/Concrete"
            ws.merge_cells(f'J{row}:L{row}')
            ws[f'J{row}'].border = border_thin
        else:
            # Empty row for Sunday
            for col in ['B', 'C', 'D', 'E', 'F', 'G', 'J']:
                ws[f'{col}{row}'].border = border_thin
    
    # ===== TOTAL ROW =====
    ws['A33'] = "Total Hours Worked"
    ws['A33'].font = label_font
    ws['F33'] = sum(hours.values())
    ws['F33'].font = Font(bold=True)
    ws['F33'].border = border_thin
    
    # ===== FROM/TO SECTIONS =====
    ws['A35'] = "From:"
    ws['A35'].font = label_font
    ws['B35'] = "Michael McLoughlin"
    
    ws['A36'] = "Client / Project Name & Address:"
    ws['A36'].font = label_font
    ws.merge_cells('B36:L36')
    ws['B36'] = "Ford Civil / Lendlease - Powerhouse Museum Parramatta, 69 Phillip St"
    
    ws['A37'] = "To:"
    ws['A37'].font = label_font
    ws['B37'] = "Joe @ Ford Civil"
    
    # ===== SIGNATURE AREA =====
    ws['E39'] = "Signature:"
    ws['E39'].font = label_font
    ws.merge_cells('F39:H39')
    
    ws['E40'] = "Date:"
    ws['E40'].font = label_font
    ws['F40'] = datetime.now().strftime('%d/%m/%Y')
    
    ws['E42'] = "Approval Signature:"
    ws['E42'].font = label_font
    ws.merge_cells('F42:H42')
    
    # ===== INSTRUCTIONS =====
    ws.merge_cells('G4:L8')
    ws['G4'] = "Please provide the Client / Project Name & Address and the Work Description you completed for that Client"
    ws['G4'].font = Font(italic=True, size=9)
    ws['G4'].alignment = Alignment(wrap_text=True, vertical='top')
    
    # ===== CORRECTION NOTE =====
    ws.merge_cells('A45:L47')
    ws['A45'] = "CORRECTION: Previously sent Week 9 hours (Feb 16-21, 59hrs) in error. This is the corrected Week 10 timesheet."
    ws['A45'].font = Font(italic=True, color="FF0000", bold=True)
    ws['A45'].alignment = Alignment(horizontal='center', vertical='center')
    
    # ===== COLUMN WIDTHS =====
    column_widths = {
        'A': 8,   # Day
        'B': 12,  # Date
        'C': 10,  # Start
        'D': 10,  # Finish
        'E': 12,  # Lunch
        'F': 12,  # Hours
        'G': 20,  # Client
        'H': 5,   # (merged)
        'I': 5,   # (merged)
        'J': 20,  # Description
        'K': 5,   # (merged)
        'L': 5,   # (merged)
    }
    
    for col, width in column_widths.items():
        ws.column_dimensions[col].width = width
    
    # ===== SAVE =====
    output_path = "/home/ccuser/rateright-growth/rivet/timesheet-Michael-W10-2Mar-7Mar-EXACT-LFCS.xlsx"
    wb.save(output_path)
    
    print(f"✅ Created exact LFCS timesheet: {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 - READY FOR JOE

📁 FILE: {excel_path}

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

⏰ HOURS BREAKDOWN:
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 IN FILE:
"Previously sent Week 9 hours (Feb 16-21, 59hrs) in error.
This is the corrected Week 10 timesheet."

📤 SEND TO: Joe @ Ford Civil
📱 VIA: WhatsApp (+61 414 587 178)
📝 MESSAGE: "Hi Joe, here's my corrected timesheet for Week 10 (March 2-7). 56 hours. Cheers, Michael"
"""
    
    summary_path = "/home/ccuser/rateright-growth/rivet/TIMESHEET-READY-FOR-JOE.txt"
    with open(summary_path, "w") as f:
        f.write(summary)
    
    print("\n" + "="*70)
    print("TIMESHEET READY FOR SUBMISSION:")
    print("="*70)
    print(summary)
    print(f"\n📋 Full instructions saved to: {summary_path}")

if __name__ == "__main__":
    create_exact_lfcs()