#!/usr/bin/env python3
"""Generate HR Employee Salary Accounting Excel file for April."""

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from datetime import date

wb = Workbook()
ws = wb.active
ws.title = "April Salary"

# Styles
header_font = Font(name='Calibri', bold=True, size=12, color='FFFFFF')
header_fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
title_font = Font(name='Calibri', bold=True, size=16, color='2F5496')
subtitle_font = Font(name='Calibri', bold=True, size=11, color='4472C4')
data_font = Font(name='Calibri', size=11)
currency_format = '$#,##0.00'
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
light_blue_fill = PatternFill(start_color='D6E4F0', end_color='D6E4F0', fill_type='solid')
light_green_fill = PatternFill(start_color='E2EFDA', end_color='E2EFDA', fill_type='solid')

# Title Section
ws.merge_cells('A1:E1')
title_cell = ws['A1']
title_cell.value = 'HR Employee Salary Accounting'
title_cell.font = title_font
title_cell.alignment = Alignment(horizontal='center')

ws.merge_cells('A2:E2')
subtitle = ws['A2']
subtitle.value = 'April 2026 - Salary Paid'
subtitle.font = subtitle_font
subtitle.alignment = Alignment(horizontal='center')

# Headers (row 4)
headers = ['Employee Name', 'Position', 'Salary (USD)', 'Payment Status', 'Payment Date']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=4, column=col, value=header)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.border = thin_border

# Employee Data
employees = [
    {'name': 'Hothaifa', 'position': 'Employee', 'salary': 1400, 'status': 'Paid'},
    {'name': 'Mohammed', 'position': 'Employee', 'salary': 700, 'status': 'Paid'},
    {'name': 'Zaynab', 'position': 'Employee', 'salary': 1000, 'status': 'Paid'},
    {'name': 'Company Tools', 'position': 'Expense', 'salary': 50, 'status': 'Paid'},
]

# Income / Revenue
april_income_entries = [
    {'name': 'Shopini (Client)', 'position': 'Revenue', 'salary': 5000, 'status': 'Received', 'date': date(2026, 4, 14)},
]

may_income_entries = [
    {'name': 'FGMM (Client)', 'position': 'Revenue', 'salary': 14000, 'status': 'Received', 'date': date(2026, 5, 1)},
]

payment_date = date(2026, 4, 14)

for i, emp in enumerate(employees, start=5):
    fill = light_blue_fill if i % 2 == 0 else light_green_fill

    ws.cell(row=i, column=1, value=emp['name']).font = data_font
    ws.cell(row=i, column=2, value=emp['position']).font = data_font

    salary_cell = ws.cell(row=i, column=3, value=emp['salary'])
    salary_cell.font = data_font
    salary_cell.number_format = currency_format

    if emp['status'] == 'Paid':
        status_cell = ws.cell(row=i, column=4, value='Paid')
        status_cell.font = Font(name='Calibri', size=11, bold=True, color='006100')
        date_cell = ws.cell(row=i, column=5, value=payment_date)
        date_cell.font = data_font
        date_cell.number_format = 'yyyy-mm-dd'
    else:
        status_cell = ws.cell(row=i, column=4, value='Not Paid')
        status_cell.font = Font(name='Calibri', size=11, bold=True, color='CC0000')
        ws.cell(row=i, column=5, value='').font = data_font
    
    for col in range(1, 6):
        ws.cell(row=i, column=col).fill = fill
        ws.cell(row=i, column=col).border = thin_border
        ws.cell(row=i, column=col).alignment = Alignment(horizontal='center', vertical='center')

# Income Section Header
income_start = 5 + len(employees) + 1
ws.merge_cells(f'A{income_start}:E{income_start}')
ws.cell(row=income_start, column=1, value='INCOME / REVENUE — APRIL').font = Font(name='Calibri', bold=True, size=12, color='2F5496')
ws.cell(row=income_start, column=1).fill = PatternFill(start_color='D6E4F0', end_color='D6E4F0', fill_type='solid')

# April income entries
for j, inc in enumerate(april_income_entries, start=income_start + 1):
    ws.cell(row=j, column=1, value=inc['name']).font = data_font
    ws.cell(row=j, column=2, value=inc['position']).font = data_font

    sal_cell = ws.cell(row=j, column=3, value=inc['salary'])
    sal_cell.font = data_font
    sal_cell.number_format = currency_format

    status_cell = ws.cell(row=j, column=4, value=inc['status'])
    status_cell.font = Font(name='Calibri', size=11, bold=True, color='006100')

    date_cell = ws.cell(row=j, column=5, value=inc['date'])
    date_cell.font = data_font
    date_cell.number_format = 'yyyy-mm-dd'

    for col in range(1, 6):
        ws.cell(row=j, column=col).fill = light_green_fill
        ws.cell(row=j, column=col).border = thin_border
        ws.cell(row=j, column=col).alignment = Alignment(horizontal='center', vertical='center')

# May Income Section Header
may_income_start = income_start + len(april_income_entries) + 2
ws.merge_cells(f'A{may_income_start}:E{may_income_start}')
ws.cell(row=may_income_start, column=1, value='INCOME / REVENUE — MAY').font = Font(name='Calibri', bold=True, size=12, color='2F5496')
ws.cell(row=may_income_start, column=1).fill = PatternFill(start_color='D6E4F0', end_color='D6E4F0', fill_type='solid')

# May income entries
for j, inc in enumerate(may_income_entries, start=may_income_start + 1):
    ws.cell(row=j, column=1, value=inc['name']).font = data_font
    ws.cell(row=j, column=2, value=inc['position']).font = data_font

    sal_cell = ws.cell(row=j, column=3, value=inc['salary'])
    sal_cell.font = data_font
    sal_cell.number_format = currency_format

    status_cell = ws.cell(row=j, column=4, value=inc['status'])
    status_cell.font = Font(name='Calibri', size=11, bold=True, color='006100')

    date_cell = ws.cell(row=j, column=5, value=inc['date'])
    date_cell.font = data_font
    date_cell.number_format = 'yyyy-mm-dd'

    for col in range(1, 6):
        ws.cell(row=j, column=col).fill = light_green_fill
        ws.cell(row=j, column=col).border = thin_border
        ws.cell(row=j, column=col).alignment = Alignment(horizontal='center', vertical='center')

# Totals
total_expenses = sum(emp['salary'] for emp in employees)
april_income = sum(inc['salary'] for inc in april_income_entries)
may_income = sum(inc['salary'] for inc in may_income_entries)
total_income = april_income + may_income
net = total_income - total_expenses

expense_total_row = 5 + len(employees)
ws.cell(row=expense_total_row, column=1, value='TOTAL EXPENSES').font = Font(name='Calibri', bold=True, size=11)
ws.cell(row=expense_total_row, column=3, value=total_expenses).font = Font(name='Calibri', bold=True, size=11)
ws.cell(row=expense_total_row, column=3).number_format = currency_format

april_income_total_row = income_start + len(april_income_entries)
ws.cell(row=april_income_total_row, column=1, value='TOTAL APRIL INCOME').font = Font(name='Calibri', bold=True, size=11)
ws.cell(row=april_income_total_row, column=3, value=april_income).font = Font(name='Calibri', bold=True, size=11)
ws.cell(row=april_income_total_row, column=3).number_format = currency_format

may_income_total_row = may_income_start + len(may_income_entries)
ws.cell(row=may_income_total_row, column=1, value='TOTAL MAY INCOME').font = Font(name='Calibri', bold=True, size=11)
ws.cell(row=may_income_total_row, column=3, value=may_income).font = Font(name='Calibri', bold=True, size=11)
ws.cell(row=may_income_total_row, column=3).number_format = currency_format

# Net Balance
net_row = may_income_total_row + 2
ws.merge_cells(f'A{net_row}:B{net_row}')
ws.cell(row=net_row, column=1, value='NET BALANCE (Total Income - Expenses)').font = Font(name='Calibri', bold=True, size=13, color='FFFFFF')
net_cell = ws.cell(row=net_row, column=3, value=net)
net_cell.font = Font(name='Calibri', bold=True, size=13, color='FFFFFF')
net_cell.number_format = currency_format

for col in range(1, 6):
    cell = ws.cell(row=net_row, column=col)
    cell.fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
    cell.alignment = Alignment(horizontal='center', vertical='center')

# Column widths
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 18
ws.column_dimensions['D'].width = 18
ws.column_dimensions['E'].width = 18

# Row height for headers
ws.row_dimensions[4].height = 25

# Output file
output_path = '/Users/tatweer/coding/menu/menu-backend/HR_Employee_Salary_April.xlsx'
wb.save(output_path)
print(f"Excel file saved: {output_path}")