import pandas as pd
import xlsxwriter
import os

# File names
input_excel = "Project Inventory Status 20250429.xlsx"
output_excel = "CCS_Filled_Report.xlsx"
output_pdf = "CCS_Filled_Report.pdf"

# Step 1: Read and filter data
df = pd.read_excel(input_excel, sheet_name="Data")

df = df.sort_values(by="Room")

# Step 2: Create Excel workbook
workbook = xlsxwriter.Workbook(output_excel)
worksheet = workbook.add_worksheet("Inventory Data")

# Define formats
header_format = workbook.add_format({
    'bold': True, 'font_name': 'Ebrima', 'font_size': 14,
    'bg_color': '#4B2061', 'font_color': 'white'
})
body_format = workbook.add_format({
    'font_name': 'Futura Book', 'font_size': 10,
    'text_wrap': True
})
worksheet.set_column("A:A", 20)
worksheet.set_column("B:B", 30)
worksheet.set_column("C:C", 80)

# Write headers and data
for col_num, header in enumerate(df.columns):
    worksheet.write(0, col_num, header, header_format)
for row_num, row in enumerate(df.itertuples(index=False), start=1):
    for col_num, value in enumerate(row):
        worksheet.write(row_num, col_num, value, body_format)

workbook.close()

# Step 3: Export to PDF using Excel (Windows only)
try:
    import win32com.client
    excel = win32com.client.Dispatch("Excel.Application")
    wb = excel.Workbooks.Open(os.path.abspath(output_excel))
    ws = wb.Worksheets[0]
    wb.ExportAsFixedFormat(0, os.path.abspath(output_pdf))
    wb.Close(False)
    excel.Quit()
    print("✅ PDF export complete!")
except Exception as e:
    print("❌ PDF export failed:", e)