import pandas as pd

# JSON to Excel Template
def json_to_dms_excel(res, employee_code=""):
    columns = [
        "STT *",
        "Số thứ tự đơn hàng *",
        "Mã nhân viên kinh doanh *",
        "Tên nhân viên kinh doanh",
        "Mã tự sinh đại lý mua *",
        "Tên đại lý mua",
        "Mã tự sinh đại lý bán *",
        "Tên đại lý bán",
        "Ngày đặt hàng *",
        "Ngày giao hàng",
        "Ghi chú",
        "Sửa giá bán *",
        "Chiết khấu tổng đơn hàng (%)",
        "Mã sản phẩm *",
        "Tên sản phẩm",
        "Mã đơn vị tính *",
        "Là sản phẩm? *",
        "Số lượng *",
        "Giá bán (bao gồm VAT)"
    ]
    
    rows = []
    idx = 1

    for _, orders in res.items():
        for order in orders:
            row = {col: '' for col in columns}  # Initialize all columns with empty string

            row["STT *"] = idx
            row["Số thứ tự đơn hàng *"] = order.get("order_id", idx)
            row["Mã nhân viên kinh doanh *"] = employee_code
            row["Tên nhân viên kinh doanh"] = order.get("saler", "")
            row["Mã tự sinh đại lý mua *"] = order.get("buyer_id", "")
            row["Tên đại lý mua"] = order.get("buyer", "")
            row["Mã tự sinh đại lý bán *"] = order.get("seller_id", "")
            row["Tên đại lý bán"] = order.get("seller", "")
            row["Ngày đặt hàng *"] = order.get("order_date", "")
            row["Ngày giao hàng"] = order.get("delivery_date", "")
            row["Ghi chú"] = order.get("note", "")
            row["Sửa giá bán *"] = "Không"
            row["Chiết khấu tổng đơn hàng (%)"] = order.get("discount", "")
            row["Mã sản phẩm *"] = order.get("product_id", "")
            row["Tên sản phẩm"] = order.get("product_name", "")
            row["Mã đơn vị tính *"] = order.get("unit_id", "")
            row["Là sản phẩm? *"] = "Sản phẩm bán"
            row["Số lượng *"] = order.get("quantity", "")
            rows.append(row)
            idx += 1

    df = pd.DataFrame(rows, columns=columns)

    # Convert "STT" and "Số lượng" columns into INT
    df["STT *"] = df["STT *"].astype(int)
    df["Số lượng *"] = df["Số lượng *"].astype(int)

    # Create "End" row, first column is "End", other is blank like in the excel template
    end_row = pd.DataFrame([["End"] + [""] * (df.shape[1] - 1)], columns= df.columns)

    df = pd.concat([df, end_row], ignore_index= True)
    
    return df