import pandas as pd
import re
import os
from glob import glob
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

# -----------------------------
# 0️⃣ Setup input/output folders
# -----------------------------
input_folder = "input"
output_folder = "output"
os.makedirs(output_folder, exist_ok=True)

# Find latest Excel file in input folder
excel_files = glob(os.path.join(input_folder, "*.xlsx"))
if not excel_files:
    raise Exception(f"No Excel files found in '{input_folder}' folder!")

latest_file = max(excel_files, key=os.path.getmtime)
input_filename = os.path.basename(latest_file)
output_file = os.path.join(output_folder, input_filename)

print(f"✔ Latest input file found: {latest_file}")
print(f"✔ Output file will be saved as: {output_file}")

# -----------------------------
# 1️⃣ Read input and process data
# -----------------------------
df_full = pd.read_excel(latest_file, header=None)

# Extract school name and order ID
first_row_str = str(df_full.iat[0, 0])
school_name = first_row_str.split("Order Form")[0].replace("-", "").strip()
match = re.search(r"OL[- ]?\d+", first_row_str)
order_id = match.group(0) if match else "Unknown OL Number"

# Read the main data
data_df = pd.read_excel(latest_file, header=None, skiprows=2)
remarks_col = 0
order_qty_col = 1
qty_cols = list(data_df.columns[2:])

output_data = []
current_boxes = []

for _, row in data_df.iterrows():
    remarks = str(row[remarks_col]).strip()

    # Process Box No row
    if remarks.lower() == "box no":
        current_boxes = []
        for c in qty_cols:
            val = row[c]
            if pd.isna(val):
                continue
            try:
                num = int(float(val))
                current_boxes.append(num)
            except:
                continue
        continue

    # Process product rows
    if remarks != "" and remarks.lower() != "box no":
        product_name = remarks
        order_qty = row[order_qty_col]
        quantities = [row[c] for c in qty_cols]

        for box, qty in zip(current_boxes, quantities):
            if pd.notna(qty) and qty != "" and qty != 0:
                try:
                    qty_val = int(float(qty))
                except:
                    continue
                output_data.append([box, product_name, order_qty, qty_val])

        current_boxes = []

# Create final dataframe
out_df = pd.DataFrame(output_data, columns=["Box No", "Product Name", "Order Qty", "Quantity"])
out_df = out_df.sort_values(by=["Box No", "Product Name"]).reset_index(drop=True)
out_df["Total Qty"] = ""

# Calculate product totals
group_totals = out_df.groupby("Product Name")["Quantity"].sum().to_dict()
for product, total in group_totals.items():
    last_index = out_df[out_df["Product Name"] == product].index[-1]
    out_df.at[last_index, "Total Qty"] = total

unique_boxes = out_df["Box No"].nunique()

# -----------------------------
# 2️⃣ Build final Excel DataFrame
# -----------------------------

# Header rows
header_rows = [
    ["School Name:", school_name, "", "", ""],    ["Order ID:", order_id, "", "", ""],
    ["Box Count:", unique_boxes, "", "", ""]
]
header_df = pd.DataFrame(header_rows, columns=["Box No", "Product Name", "Order Qty", "Quantity", "Total Qty"])

# Table header row
table_header = pd.DataFrame([["Box No", "Product Name", "Order Qty", "Quantity", "Total Qty"]],
                            columns=["Box No", "Product Name", "Order Qty", "Quantity", "Total Qty"])

# Combine header, table header, and data
final_df = pd.concat([header_df, table_header, out_df], ignore_index=True)

# -----------------------------
# 3️⃣ Save output to Excel
# -----------------------------
final_df.to_excel(output_file, index=False, header=False)
print(f"✔ Output saved: {output_file}")

# -----------------------------
# 4️⃣ Format Excel
# -----------------------------
wb = load_workbook(output_file)
ws = wb.active

thin = Side(border_style="thin", color="000000")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
center = Alignment(horizontal="center", vertical="center", wrap_text=True)
left = Alignment(horizontal="left", vertical="center", wrap_text=True)

# Apply borders and alignment
for row in ws.iter_rows():
    for cell in row:
        cell.border = border
        cell.alignment = left if cell.column == 2 else center

# Bold table header row
table_header_row = len(header_rows) + 1
for col in range(1, 6):
    ws.cell(row=table_header_row, column=col).font = Font(bold=True)
    ws.cell(row=table_header_row, column=col).fill = PatternFill(start_color="FFC000", end_color="FFC000", fill_type="solid")

# Merge Box No cells
box_col = 1
row_start = table_header_row + 1
current_val = None
merge_start = None

for row in range(row_start, ws.max_row + 1):
    val = ws.cell(row=row, column=box_col).value
    if val != current_val:
        if merge_start is not None and row - merge_start > 1:
            ws.merge_cells(start_row=merge_start, start_column=box_col,
                           end_row=row - 1, end_column=box_col)
        current_val = val
        merge_start = row

if merge_start is not None and ws.max_row - merge_start >= 1:
    ws.merge_cells(start_row=merge_start, start_column=box_col,
                   end_row=ws.max_row, end_column=box_col)

# Column widths
widths = {1: 10, 2: 55, 3: 12, 4: 12, 5: 12}
for col, width in widths.items():
    ws.column_dimensions[get_column_letter(col)].width = width

wb.save(output_file)
print(f"✔ Formatting completed.\n✔ Final file ready: {output_file}")