import subprocess
import sys
# Install openpyxl if not available
try:
    import openpyxl
except ImportError:
    subprocess.check_call([sys.executable, "-m", "pip", "install", "openpyxl"])
    import openpyxl
# Install additional dependencies for PDF generation
try:
    from reportlab.pdfgen import canvas
    from reportlab.lib.pagesizes import A4
    from reportlab.lib.units import inch
    from reportlab.lib.colors import HexColor
    from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Image
    from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
    from reportlab.lib import colors
except ImportError:
    subprocess.check_call([sys.executable, "-m", "pip", "install", "reportlab"])
    from reportlab.pdfgen import canvas
    from reportlab.lib.pagesizes import A4
    from reportlab.lib.units import inch
    from reportlab.lib.colors import HexColor
    from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Image
    from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
    from reportlab.lib import colors

import gradio as gr
import pandas as pd
import tempfile
from datetime import datetime, timedelta
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.drawing.image import Image as ExcelImage
import random
import base64
from io import BytesIO
import os
import zipfile

def generate_random_invoice_number():
    """Generate random 6-digit invoice number"""
    return str(random.randint(100000, 999999))

def create_professional_individual_invoice(workbook, employee_data, start_date, end_date):
    """Create individual invoice sheet with professional Glimmr template formatting and logo"""
    # --- PASTE YOUR CLEANED BASE64 STRING HERE ---
    GLIMMR_LOGO_BASE64 = """
    iVBORw0KGgoAAAANSUhEUgAAAOEAAADhCAMAAAAJbSJIAAAAnFBMVEX///8An9r/nzcAm9kAmdgAndkAl9fo8/rQ5/Vjt+L/myug0OzU6fb/yZzC4PIAldeBwudPsOCn1O05qd7/3MDk8fn/mSH/59Xd7vh6v+a73fFrvOWy2vD/nC74/P6Hx+n/qVP/rl3/0qshpdz/8ub//PlYteL/1rX/lACUy+r/o0L/5Mv/lxr/uXj/vYH/69r/w4z/t3Mfm83/y6CSnfTNAAAGLElEQVR4nO2cfXeiOBTGiXlBxRGF1iJabIeOHabOtLv9/t9tkxBAq+juWV4OOc/vn0ajOfcxbzf3hjoOAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH0wSyd9m9AuCeUi7tuIVllyIqK+jWiTjBG+6duINokE4bxvI1qFyDE67tuINplwQtd9G/F/ePlxvX4rJyHpxpSWeH24Wu0KMvQx+uvpanUox+iiG0va4eX5cfr8Ul+/o4Sn3ZnTPG+/DqPR4emtrn6sxuisS4ua5/vhcFdbmXA5RncdWtMKvz/r66S3Nuwxqnmo3y08Ruz2R6W3RmjWtxUNcraippyQ4Y/Rivc/X97YUDlG73uxpRXeR4fPkxkZyzHKhj1GTxzSl4/D9GRbTGQP8rBrm5rl7nSff30+eSm9NSLcLu1pnqen09fvxy8y2YXM69CaFvhYTX/XVs7kJOTLDq1pnJe7p+loNB19r3G6+eDH6I/338rl/vN+uXqvBAbdmtQ8r/UudzD4MZrz10ddDVHLTNKlLe3wUH/w3Qix7dCStrh4pnDDjeq8rQVjtIaUcz7suNMN9D4ohu2NXieWa4xcZZYWLDOXcIOQEc3wQxfBPotnbtVRSTQP1qmgnBiFg19oIp9SxgTjqYRQKgSjvJAn8YcfnAmNHK4gX6EDj5Aq5uJM1hGib/OaYH/ec0cKrYjOEEE1l5QyK/b8ZB54WRZsdxckUhuc0pwkXtILfUgHfzTM2S6JuDhI7Ql0h6f9V4nlg083FRwPUc6WpUSLLs/sS4l/k3Foo0J5mM8lid2PjRbIJym3SqGTCc6p2EfOjpmNwjaFTpzuPddxdsaNEwm3apSWoZpNcTDcO8IqhYZxWqw4bD5jNu0WBVGhkBPHk0U68JTMJTymNUppalekNt4HTnbSg+PcSdSKY8fp6YzES/2ts6Vk6FcRrxFJX1UeG/3BH54iz6s94rpx4M27NKYpgkkYLnd5ynPtUyoGfgfhnL1aRqi+ZbjV3suw7zVfYJK71WlZJLRvkxrGyBKJvm+oi32b1DCFQlff+iXDvwx0xpHChKs56VsQ1j7hSKGTLNJ0Yp3XcqzQCmZxfJo9uqlQfsN4Ack4np/3sDuP5+bLUfnRY97urj+30Sgel66XSMdOsk7lmUHlAo8UeillfK/9llgWaRhIh4Ax+Q11jy2ZCFVM82kacsrI+t5xlTPHxFJ+PeC6mD+C8fprupo+vzrO52G1OoxuPH3TGEuWb34i0DlB7p8qXCjfmvsqrO3pZVVMMhPCYIvEhIi5r7vJV9/nYl68S5O1MNupzqF+m45Go8fDx8dK/h1Nay/oNMumjBCagviisNoPc4WkyiDStMwC68fxxElDymUoi/rCjVYoNT7qP9NvnQicnWUGaxTOSoVHVHFvNq8UXqrXG6pRaFjVP7jRJJMiu0sL8y8rZMcKq14sU8I6ViPO6iuN4kzhoZt5WBgVBsG/Vkj3ZdabTIwGHW8zjfG0+N3k/CuC/6qtUqFccB4fn2/Z1ggzExZUjnVYzbirClV3FWbfO+O8Bb6vFMrGAlrqNnNVhToKhavPt9enn90M0q2xZPIfFKp0ofksc517E+hfVgrlqjOvfgzTFotKhVP9YNFrJwJLo7MuFXa0xORkxmjPeoX29mGxeKytVRgbS4i1Cu+LBT6zVWGxWxF5VCj2OMsUBmbLr/iicHdDoXtL4aZnhWUnli6kUrioFBpZtQqTWwoXfStMuDGmOAopheYuolIY+dcVmmK9QtNWfwrlj8zUMZwHxiodGV348jhMfRWe8PKiPOJmPlP40j1IRV6UCl2urtMydTrK65lsIc6L+mxv2pI/0befK0VHHukx0XwelT5qHvuNvMU6y3Muri4msjDOkbJmpqhvRse79cJTgR7z5rj66H3ZlsqivjzkXPmnDG1QhpHyCWNfNjD2zb071xyDrLtYoU73i3g8z0z4iNmWlI+ZXkdZEcSw7z88fb3z61txo/mY7Ul8LI+LWsZSlJExOSMHmZO/xXjDhdq/hQgtvNtkSKLxeGZdAg0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABgMPwD+p9Y6vy1tXkAAAAASUVORK5CYII=
    """
    ws = workbook.create_sheet(title=employee_data['Row Labels'])
    column_widths = {'A': 25, 'B': 15, 'C': 15, 'D': 15, 'E': 15, 'F': 12, 'G': 12, 'H': 12, 'I': 12}
    for col, width in column_widths.items():
        ws.column_dimensions[col].width = width
    try:
        if GLIMMR_LOGO_BASE64.strip() and "PASTE" not in GLIMMR_LOGO_BASE64 and "YOUR BASE64" not in GLIMMR_LOGO_BASE64:
            logo_data = base64.b64decode(GLIMMR_LOGO_BASE64.strip())
            logo_stream = BytesIO(logo_data)
            img = ExcelImage(logo_stream)
            img.width = 200
            img.height = 100
            ws.add_image(img, 'A1')
    except Exception as e:
        print(f"Logo embedding failed: {e}")
    # Company name
    ws['A6'] = 'Glimmr Ltd'
    ws['A6'].font = Font(bold=True, size=16)
    # --- CORRECTED HEADER STRUCTURE TO MATCH ORIGINAL ---
    # Main header spanning wider (extended one cell left)
    ws.merge_cells('E2:I2')
    ws['E2'] = 'CLEANER INVOICE'
    ws['E2'].font = Font(bold=True, size=14, color='FFFFFF')
    ws['E2'].alignment = Alignment(horizontal='center', vertical='center')
    ws['E2'].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    # Invoice # header (left side)
    ws.merge_cells('E6:F6')
    ws['E6'] = 'Invoice #'
    ws['E6'].font = Font(bold=True, color='FFFFFF')
    ws['E6'].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    ws['E6'].alignment = Alignment(horizontal='center', vertical='center')
    # Date header (right side)
    ws.merge_cells('G6:I6')
    ws['G6'] = 'Date'
    ws['G6'].font = Font(bold=True, color='FFFFFF')
    ws['G6'].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    ws['G6'].alignment = Alignment(horizontal='center', vertical='center')
    # Invoice # value (below header)
    ws.merge_cells('E7:F7')
    ws['E7'] = generate_random_invoice_number()
    ws['E7'].alignment = Alignment(horizontal='center', vertical='center')
    # Date value (below header)
    ws.merge_cells('G7:I7')
    ws['G7'] = datetime.now().strftime('%d/%m/%y')
    ws['G7'].alignment = Alignment(horizontal='center', vertical='center')
    # Company address
    ws['A8'] = '108 Westbourne Terrace'
    ws['A9'] = 'London'
    ws['A10'] = 'W2 6QJ'
    ws['A11'] = 'support@glimmr.co.uk'
    ws['A12'] = '020 8158 8505'
    # Payee information
    ws['A14'] = "Payee's Name"
    ws['A14'].font = Font(bold=True)
    ws['A15'] = employee_data['Row Labels']
    ws['A15'].font = Font(size=11)
    # Payment period header
    ws.merge_cells('A20:D20')
    ws['A20'] = 'Payment Period'
    ws['A20'].font = Font(bold=True, color='FFFFFF')
    ws['A20'].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    ws['A20'].alignment = Alignment(horizontal='center')
    # Table headers - adjusted positions
    ws['E20'] = 'Hours'
    ws['F20'] = '£ / Hour'
    ws.merge_cells('G20:I20')
    ws['G20'] = 'Amount'
    for cell in ['E20', 'F20', 'G20']:
        ws[cell].font = Font(bold=True, color='FFFFFF')
        ws[cell].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
        ws[cell].alignment = Alignment(horizontal='center')
    # Payment period data with center alignment
    ws.merge_cells('A21:D21')
    ws['A21'] = f"{start_date} to {end_date}"
    ws['A21'].alignment = Alignment(horizontal='center')
    ws['E21'] = employee_data['Sum of Hours Worked']
    ws['F21'] = f"£  {employee_data['Average of Hourly Rate']:.2f}"
    ws.merge_cells('G21:I21')
    ws['G21'] = f"£  {employee_data['Sum of Total']:.2f}"
    # Total sections - adjusted positions
    ws.merge_cells('F24:G24')
    ws['F24'] = 'Total Labor'
    ws['F24'].font = Font(bold=True)
    ws.merge_cells('H24:I24')
    ws['H24'] = f"£  {employee_data['Sum of Total']:.2f}"
    ws.merge_cells('F26:G26')
    ws['F26'] = 'Tax (Withholding)'
    # Payment due date and total
    due_date = (datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)).strftime('%d/%m/%Y')
    ws['A27'] = f'Payment is due on {due_date}'
    ws.merge_cells('F27:G27')
    ws['F27'] = 'TOTAL'
    ws['F27'].font = Font(bold=True)
    ws.merge_cells('H27:I27')
    ws['H27'] = f"£  {employee_data['Sum of Total']:.2f}"
    ws['H27'].font = Font(bold=True)
    # Comments section
    ws['A28'] = 'Comments or Special Instructions:'
    ws['A28'].font = Font(bold=True)
    ws['A29'] = 'Payments to be made every other Saturday (or as close to these days).'
    ws['A30'] = 'Please let us know immediately if there are any issues.'
    # Apply borders - updated ranges
    thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
    # Border for payment period table
    for row in range(20, 22):
        for col in range(1, 10):
            ws.cell(row=row, column=col).border = thin_border
    # Border for header section
    for row in range(6, 8):
        for col_letter in ['E', 'F', 'G', 'H', 'I']:
            ws[f'{col_letter}{row}'].border = thin_border

def create_individual_pdf_invoice(employee_data, start_date, end_date, output_dir):
    """Create individual PDF invoice for each cleaner"""
    GLIMMR_LOGO_BASE64 = """
    iVBORw0KGgoAAAANSUhEUgAAAOEAAADhCAMAAAAJbSJIAAAAnFBMVEX///8An9r/nzcAm9kAmdgAndkAl9fo8/rQ5/Vjt+L/myug0OzU6fb/yZzC4PIAldeBwudPsOCn1O05qd7/3MDk8fn/mSH/59Xd7vh6v+a73fFrvOWy2vD/nC74/P6Hx+n/qVP/rl3/0qshpdz/8ub//PlYteL/1rX/lACUy+r/o0L/5Mv/lxr/uXj/vYH/69r/w4z/t3Mfm83/y6CSnfTNAAAGLElEQVR4nO2cfXeiOBTGiXlBxRGF1iJabIeOHabOtLv9/t9tkxBAq+juWV4OOc/vn0ajOfcxbzf3hjoOAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH0wSyd9m9AuCeUi7tuIVllyIqK+jWiTjBG+6duINokE4bxvI1qFyDE67tuINplwQtd9G/F/ePlxvX4rJyHpxpSWeH24Wu0KMvQx+uvpanUox+iiG0va4eX5cfr8Ul+/o4Sn3ZnTPG+/DqPR4emtrn6sxuisS4ua5/vhcFdbmXA5RncdWtMKvz/r66S3Nuwxqnmo3y08Ruz2R6W3RmjWtxUNcraippyQ4Y/Rivc/X97YUDlG73uxpRXeR4fPkxkZyzHKhj1GTxzSl4/D9GRbTGQP8rBrm5rl7nSff30+eSm9NSLcLu1pnqen09fvxy8y2YXM69CaFvhYTX/XVs7kJOTLDq1pnJe7p+loNB19r3G6+eDH6I/338rl/vN+uXqvBAbdmtQ8r/UudzD4MZrz10ddDVHLTNKlLe3wUH/w3Qix7dCStrh4pnDDjeq8rQVjtIaUcz7suNMN9D4ohu2NXieWa4xcZZYWLDOXcIOQEc3wQxfBPotnbtVRSTQP1qmgnBiFg19oIp9SxgTjqYRQKgSjvJAn8YcfnAmNHK4gX6EDj5Aq5uJM1hGib/OaYH/ec0cKrYjOEEE1l5QyK/b8ZB54WRZsdxckUhuc0pwkXtILfUgHfzTM2S6JuDhI7Ql0h6f9V4nlg083FRwPUc6WpUSLLs/sS4l/k3Foo0J5mM8lid2PjRbIJym3SqGTCc6p2EfOjpmNwjaFTpzuPddxdsaNEwm3apSWoZpNcTDcO8IqhYZxWqw4bD5jNu0WBVGhkBPHk0U68JTMJTymNUppalekNt4HTnbSg+PcSdSKY8fp6YzES/2ts6Vk6FcRrxFJX1UeG/3BH54iz6s94rpx4M27NKYpgkkYLnd5ynPtUyoGfgfhnL1aRqi+ZbjV3suw7zVfYJK71WlZJLRvkxrGyBKJvm+oi32b1DCFQlff+iXDvwx0xpHChKs56VsQ1j7hSKGTLNJ0Yp3XcqzQCmZxfJo9uqlQfsN4Ack4np/3sDuP5+bLUfnRY97urj+30Sgel66XSMdOsk7lmUHlAo8UeillfK/9llgWaRhIh4Ax+Q11jy2ZCFVM82kacsrI+t5xlTPHxFJ+PeC6mD+C8fprupo+vzrO52G1OoxuPH3TGEuWb34i0DlB7p8qXCjfmvsqrO3pZVVMMhPCYIvEhIi5r7vJV9/nYl68S5O1MNupzqF+m45Go8fDx8dK/h1Nay/oNMumjBCagviisNoPc4WkyiDStMwC68fxxElDymUoi/rCjVYoNT7qP9NvnQicnWUGaxTOSoVHVHFvNq8UXqrXG6pRaFjVP7jRJJMiu0sL8y8rZMcKq14sU8I6ViPO6iuN4kzhoZt5WBgVBsG/Vkj3ZdabTIwGHW8zjfG0+N3k/CuC/6qtUqFccB4fn2/Z1ggzExZUjnVYzbirClV3FWbfO+O8Bb6vFMrGAlrqNnNVhToKhavPt9enn90M0q2xZPIfFKp0ofksc517E+hfVgrlqjOvfgzTFotKhVP9YNFrJwJLo7MuFXa0xORkxmjPeoX29mGxeKytVRgbS4i1Cu+LBT6zVWGxWxF5VCj2OMsUBmbLr/iicHdDoXtL4aZnhWUnli6kUrioFBpZtQqTWwoXfStMuDGmOAopheYuolIY+dcVmmK9QtNWfwrlj8zUMZwHxiodGV348jhMfRWe8PKiPOJmPlP40j1IRV6UCl2urtMydTrK65lsIc6L+mxv2pI/0befK0VHHukx0XwelT5qHvuNvMU6y3Muri4msjDOkbJmpqhvRse79cJTgR7z5rj66H3ZlsqivjzkXPmnDG1QhpHyCWNfNjD2zb071xyDrLtYoU73i3g8z0z4iNmWlI+ZXkdZEcSw7z88fb3z61txo/mY7Ul8LI+LWsZSlJExOSMHmZO/xXjDhdq/hQgtvNtkSKLxeGZdAg0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABgMPwD+p9Y6vy1tXkAAAAASUVORK5CYII=
    """
    clean_name = "".join(c for c in employee_data['Row Labels'] if c.isalnum() or c in (' ', '-', '_')).rstrip()
    clean_name = clean_name.replace(' ', '_')
    start_dt = datetime.strptime(start_date, '%Y-%m-%d')
    end_dt = datetime.strptime(end_date, '%Y-%m-%d')
    date_period = f"{start_dt.strftime('%d%b%Y')}-{end_dt.strftime('%d%b%Y')}"
    filename = f"{clean_name}_{date_period}_Glimmr_Invoice.pdf"
    filepath = os.path.join(output_dir, filename)
    doc = SimpleDocTemplate(filepath, pagesize=A4, rightMargin=72, leftMargin=72, topMargin=72, bottomMargin=18)
    story = []
    try:
        if GLIMMR_LOGO_BASE64.strip():
            logo_data = base64.b64decode(GLIMMR_LOGO_BASE64.strip())
            logo_stream = BytesIO(logo_data)
            logo_img = Image(logo_stream)
            logo_img.drawWidth = 2*inch
            logo_img.drawHeight = 1*inch
            story.append(logo_img)
            story.append(Spacer(1, 12))
    except Exception as e:
        print(f"PDF Logo embedding failed: {e}")
    styles = getSampleStyleSheet()
    title_style = ParagraphStyle('CustomTitle', parent=styles['Heading1'], fontSize=18, spaceAfter=30, textColor=HexColor('#4472C4'), alignment=1)
    header_style = ParagraphStyle('CustomHeader', parent=styles['Heading2'], fontSize=14, spaceAfter=12, textColor=HexColor('#4472C4'))
    normal_style = styles['Normal']
    story.append(Paragraph('<b>Glimmr Ltd</b>', title_style))
    address_text = "108 Westbourne Terrace<br/>London<br/>W2 6QJ<br/>support@glimmr.co.uk<br/>020 8158 8505"
    story.append(Paragraph(address_text, normal_style))
    story.append(Spacer(1, 20))
    invoice_number = generate_random_invoice_number()
    current_date = datetime.now().strftime('%d/%m/%Y')
    invoice_header_data = [['CLEANER INVOICE', ''], ['Invoice #: ' + invoice_number, 'Date: ' + current_date]]
    invoice_header_table = Table(invoice_header_data, colWidths=[3*inch, 3*inch])
    invoice_header_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), HexColor('#4472C4')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),
        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, 0), 14),
        ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
        ('FONTSIZE', (0, 1), (-1, -1), 12),
        ('BOTTOMPADDING', (0, 0), (-1, -1), 12),
        ('GRID', (0, 0), (-1, -1), 1, colors.black)
    ]))
    story.append(invoice_header_table)
    story.append(Spacer(1, 20))
    story.append(Paragraph('<b>Payee\'s Name:</b>', header_style))
    story.append(Paragraph(employee_data['Row Labels'], normal_style))
    story.append(Spacer(1, 20))
    due_date = (datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)).strftime('%d/%m/%Y')
    payment_data = [
        ['Payment Period', 'Hours', '£ / Hour', 'Amount'],
        [f"{start_date} to {end_date}",
         str(employee_data['Sum of Hours Worked']),
         f"£ {employee_data['Average of Hourly Rate']:.2f}",
         f"£ {employee_data['Sum of Total']:.2f}"]
    ]
    payment_table = Table(payment_data, colWidths=[2*inch, 1*inch, 1*inch, 1.5*inch])
    payment_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), HexColor('#4472C4')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),
        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
        ('FONTSIZE', (0, 0), (-1, -1), 10),
        ('BOTTOMPADDING', (0, 0), (-1, -1), 8),
        ('GRID', (0, 0), (-1, -1), 1, colors.black)
    ]))
    story.append(payment_table)
    story.append(Spacer(1, 20))
    total_data = [
        ['Total Labor', f"£ {employee_data['Sum of Total']:.2f}"],
        ['Tax (Withholding)', '£ 0.00'],
        ['TOTAL', f"£ {employee_data['Sum of Total']:.2f}"]
    ]
    total_table = Table(total_data, colWidths=[3*inch, 2*inch])
    total_table.setStyle(TableStyle([
        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
        ('FONTNAME', (0, 0), (-1, 1), 'Helvetica'),
        ('FONTNAME', (0, 2), (-1, 2), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, -1), 12),
        ('BOTTOMPADDING', (0, 0), (-1, -1), 8),
        ('GRID', (0, 0), (-1, -1), 1, colors.black),
        ('BACKGROUND', (0, 2), (-1, 2), colors.lightgrey)
    ]))
    story.append(total_table)
    story.append(Spacer(1, 20))
    story.append(Paragraph(f'<b>Payment is due on {due_date}</b>', normal_style))
    story.append(Spacer(1, 20))
    story.append(Paragraph('<b>Comments or Special Instructions:</b>', header_style))
    story.append(Paragraph('Payments to be made every other Saturday (or as close to these days).', normal_style))
    story.append(Paragraph('Please let us know immediately if there are any issues.', normal_style))
    doc.build(story)
    return filepath

def create_invoices_from_template_with_logo(report_data, start_date, end_date):
    wb = openpyxl.Workbook()
    wb.remove(wb.active)
    # Create Payments sheet
    payments_ws = wb.create_sheet(title="Payments")
    payments_ws['C1'] = 'DO NOT ALTER THIS TEMPLATE'
    payments_ws['C1'].font = Font(bold=True)
    headers = ['First Name', 'Last Name', 'Stripe Name', 'Reference', 'Amount (GBP)', 'Remarks']
    for col, header in enumerate(headers, 1):
        cell = payments_ws.cell(row=2, column=col, value=header)
        cell.font = Font(bold=True)
    for row_idx, employee in enumerate(report_data[report_data['Row Labels'] != 'Grand Total'].itertuples(), 3):
        name_parts = employee[1].split(' ', 1)
        first_name = name_parts[0] if name_parts else ''
        last_name = name_parts[1] if len(name_parts) > 1 else ''
        payments_ws.cell(row=row_idx, column=1, value=first_name)
        payments_ws.cell(row=row_idx, column=2, value=last_name)
        payments_ws.cell(row=row_idx, column=3, value=employee[1])
        payments_ws.cell(row=row_idx, column=4, value='')
        payments_ws.cell(row=row_idx, column=5, value=employee[4]) # Sum of Total
        payments_ws.cell(row=row_idx, column=6, value='')
    # Create Pivot sheet
    pivot_ws = wb.create_sheet(title="Pivot")
    pivot_ws['A1'] = f"{start_date} to {end_date}"
    pivot_ws['A1'].font = Font(bold=True)
    due_date = (datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)).strftime('%d/%m/%Y')
    pivot_ws['A2'] = f"Payment is due on {due_date}"
    # MODIFIED: Updated header name as requested
    pivot_headers = ['Name', 'Fixed Hourly Rate(without TIP)', 'Average of Hourly Rate', 'Sum of Hours Worked', 'Sum of Total']
    for col, header in enumerate(pivot_headers, 1):
        cell = pivot_ws.cell(row=3, column=col, value=header)
        cell.font = Font(bold=True)
    for row_idx, employee in enumerate(report_data.itertuples(index=False), 4):
        # The tuple index corresponds to the column order in the final_invoice DataFrame
        # [0]: Row Labels, [1]: Avg Rate, [2]: Sum Hours, [3]: Sum Total, [4]: Fixed Rate
        pivot_ws.cell(row=row_idx, column=1, value=employee[0])  # Name
        pivot_ws.cell(row=row_idx, column=2, value=employee[4])  # Fixed Hourly Rate(without TIP)
        pivot_ws.cell(row=row_idx, column=3, value=employee[1])  # Average of Hourly Rate
        pivot_ws.cell(row=row_idx, column=4, value=employee[2])  # Sum of Hours Worked
        pivot_ws.cell(row=row_idx, column=5, value=employee[3])  # Sum of Total
    # Create individual invoice sheets
    individual_employees = report_data[report_data['Row Labels'] != 'Grand Total']
    for _, employee in individual_employees.iterrows():
        create_professional_individual_invoice(wb, employee, start_date, end_date)
    return wb

def process_invoice_files_with_professional_excel(file1, file2, file3, bookings_file, start_date, end_date):
    try:
        employee_files = [f for f in [file1, file2, file3] if f is not None]
        if not employee_files:
            return None, None, "❗ **Error:** At least one Employee CSV must be uploaded."
        if bookings_file is None:
            return None, None, "❗ **Error:** Bookings CSV is required."
        employee_dfs = [pd.read_csv(ef.name) for ef in employee_files]
        all_employee_data = pd.concat(employee_dfs, ignore_index=True)
        # Add the new Fixed Hour Rate column
        all_employee_data['Fixed Hour Rate'] = all_employee_data['Hourly Rate']

        pdf_bookings = pd.read_csv(bookings_file.name)
        all_employee_data['Date'] = pd.to_datetime(all_employee_data['Date'], dayfirst=True, errors='coerce')
        pdf_bookings['Date'] = pd.to_datetime(pdf_bookings['Date'], dayfirst=True, errors='coerce')
        start_date_dt = pd.to_datetime(start_date)
        end_date_dt = pd.to_datetime(end_date)
        filtered_data = all_employee_data[(all_employee_data['Date'] >= start_date_dt) & (all_employee_data['Date'] <= end_date_dt)].copy()
        filtered_bookings = pdf_bookings[(pdf_bookings['Date'] >= start_date_dt) & (pdf_bookings['Date'] <= end_date_dt)].copy()

        def extract_employee_name(team_string):
            if pd.isna(team_string): return None
            name = team_string.split(',')[0].strip()
            return name.split('(')[0].strip()

        filtered_bookings['Employee_Name'] = filtered_bookings['Teams Assigned  (without IDs)'].apply(extract_employee_name)
        tips_summary = filtered_bookings.groupby('Employee_Name')['Tip'].sum().reset_index()
        tips_with_amount = tips_summary[tips_summary['Tip'] > 0]

        if not tips_with_amount.empty:
            # Add Fixed Hour Rate to tip rows as 0
            tip_rows = [{'Name': row['Employee_Name'], 'Hourly Rate': 0, 'Hours Worked': 0, 'Total': row['Tip'], 'Fixed Hour Rate': 0} for _, row in tips_with_amount.iterrows()]
            tip_df = pd.DataFrame(tip_rows)
            final_data = pd.concat([filtered_data, tip_df], ignore_index=True)
        else:
            final_data = filtered_data.copy()

        # Update aggregation to include Fixed Hour Rate
        report = final_data.groupby('Name').agg({
            'Hourly Rate': 'mean',
            'Hours Worked': 'sum',
            'Total': 'sum',
            'Fixed Hour Rate': 'first' # Use 'first' to get the original rate
        }).reset_index()

        # MODIFIED: Update column renaming as requested
        report = report.rename(columns={
            'Name': 'Row Labels',
            'Hourly Rate': 'Average of Hourly Rate',
            'Hours Worked': 'Sum of Hours Worked',
            'Total': 'Sum of Total',
            'Fixed Hour Rate': 'Fixed Hourly Rate(without TIP)'
        })

        report['Average of Hourly Rate'] = report['Average of Hourly Rate'].round(8)
        report['Sum of Hours Worked'] = report['Sum of Hours Worked'].round(2)
        report['Sum of Total'] = report['Sum of Total'].round(2)

        total_hours = report['Sum of Hours Worked'].sum()
        total_sum = report['Sum of Total'].sum()

        # MODIFIED: Update Grand Total DataFrame to use new column name
        grand_total = pd.DataFrame({
            'Row Labels': ['Grand Total'],
            'Average of Hourly Rate': [total_sum / total_hours if total_hours > 0 else 0],
            'Sum of Hours Worked': [total_hours],
            'Sum of Total': [total_sum],
            'Fixed Hourly Rate(without TIP)': [None]
        })

        final_invoice = pd.concat([report, grand_total], ignore_index=True)

        # Reorder columns for the create_invoices function to process correctly
        # The order here determines the order in `itertuples`
        final_invoice = final_invoice[['Row Labels', 'Average of Hourly Rate', 'Sum of Hours Worked', 'Sum of Total', 'Fixed Hourly Rate(without TIP)']]

        wb = create_invoices_from_template_with_logo(final_invoice, start_date, end_date)

        with tempfile.NamedTemporaryFile(delete=False, suffix='.xlsx') as tmp:
            wb.save(tmp.name)
            temp_excel_file_path = tmp.name

        # Create PDF files for individual cleaners
        pdf_temp_dir = tempfile.mkdtemp()
        individual_employees = final_invoice[final_invoice['Row Labels'] != 'Grand Total']
        pdf_files = []
        for _, employee in individual_employees.iterrows():
            pdf_path = create_individual_pdf_invoice(employee, start_date, end_date, pdf_temp_dir)
            pdf_files.append(pdf_path)

        # Create a ZIP file containing all PDFs
        zip_temp_file = tempfile.NamedTemporaryFile(delete=False, suffix='.zip')
        with zipfile.ZipFile(zip_temp_file.name, 'w') as zipf:
            for pdf_file in pdf_files:
                zipf.write(pdf_file, os.path.basename(pdf_file))

        individual_employees_count = len(final_invoice[final_invoice['Row Labels'] != 'Grand Total'])
        summary_text = f"🎉 **Professional Invoice Generated Successfully!**\n\n- ✅ **Individual Professional Invoices** for each employee ({individual_employees_count} Excel sheets)\n- ✅ **Individual PDF Invoices** for each cleaner ({individual_employees_count} PDFs)\n- ✅ **Payments Summary & Pivot Data** sheets included\n- ✅ **Glimmr Logo & Branding** applied\n\n**Summary for {start_date} to {end_date}:**\n- Total hours: {total_hours:.2f}\n- Total amount: £{total_sum:.2f}\n\n**Files Generated:**\n- Excel file with all data and individual sheets\n- ZIP file containing individual PDF invoices for each cleaner"

        return temp_excel_file_path, zip_temp_file.name, summary_text

    except Exception as e:
        return None, None, f"❌ **An error occurred:**\n\n{str(e)}\n\nPlease check your input files and date formats (YYYY-MM-DD)."

with gr.Blocks(title="Complete Professional Invoice Generator", theme=gr.themes.Soft()) as interface:
    gr.Markdown("# 🧾 Complete Professional Invoice Generator for Launch27 Data")
    with gr.Row():
        with gr.Column(scale=2):
            gr.Markdown("### 📁 Upload CSV Files")
            file1 = gr.File(label="Upload 1.csv (Optional Employee Data)", file_types=[".csv"])
            file2 = gr.File(label="Upload 2.csv (Optional Employee Data)", file_types=[".csv"])
            file3 = gr.File(label="Upload 3.csv (Optional Employee Data)", file_types=[".csv"])
            bookings_file = gr.File(label="Upload Bookings CSV (Required)", file_types=[".csv"])
        with gr.Column(scale=1):
            gr.Markdown("### 📅 Select Date Range")
            start_date = gr.Textbox(label="Start Date (YYYY-MM-DD)", value="2025-08-09")
            end_date = gr.Textbox(label="End Date (YYYY-MM-DD)", value="2025-08-22")
            process_btn = gr.Button("🚀 Generate Professional Invoice Excel & PDFs", variant="primary", size="lg")
    gr.Markdown("---")
    gr.Markdown("### 📋 Results")
    with gr.Row():
        summary_output = gr.Markdown(label="Summary")
    with gr.Row():
        download_excel_file = gr.File(label="📥 Download Professional Invoice Excel (.xlsx)")
        download_pdf_zip = gr.File(label="📥 Download Individual PDF Invoices (.zip)")
    process_btn.click(
        fn=process_invoice_files_with_professional_excel,
        inputs=[file1, file2, file3, bookings_file, start_date, end_date],
        outputs=[download_excel_file, download_pdf_zip, summary_output]
    )
    with gr.Accordion("📖 How to Use", open=False):
        gr.Markdown("""
1.  **Upload Files**: Upload 1-3 employee CSVs and the required bookings CSV.
2.  **Select Dates**: Enter the date range in YYYY-MM-DD format.
3.  **Generate**: Click the button.
4.  **Download**: Your complete Excel file and ZIP of individual PDF invoices will appear for download.

**New Features:**
- Individual PDF invoices for each cleaner with the naming convention: `Cleaner_Name_Dateperiod_Glimmr_Invoice.pdf`
- All PDF files are packaged in a convenient ZIP file for download
- Professional PDF formatting matching the Excel invoice design
        """)

if __name__ == "__main__":
    interface.launch(debug=True)