import pandas as pd
import pdfplumber
import re



# ==========================================
# 3. MARG PARSER ENGINE (STOCK)
# ==========================================
MARG_HEADERS = {
    # Identifiers & Groupings
    "s.no.", "s.no", "code", "sku", "hsn", "sac", "salt", "category", "store", "godown",
    # Entities
    "description", "product", "item", "particulars", "company", "supplier",
    # Quantities, Tracking & Movement
    "packing", "pack", "batch", "btch", "exp", "expiry", "mfg", "mfg.", "days",
    "op.stock", "opening", "purchase", "receipt", "sale", "outwards", "return", "brk/exp",
    "cl.stock", "closing", "stock", "ord qty", "qty", "free", "min", "max", "reorder", "short", "available", "avl. qty",
    # Financials
    "rate", "p.rate", "mrp", "value", "amount", "unit", "gst%", "tax", "total"
}


def marg_is_stop_line(words: list) -> bool:
    text = " ".join(w["text"] for w in words).lower()
    return "grand total" in text or "page total" in text or text.startswith("total")


def parse_universal_marg_pdf(pdf_path: str) -> list:
    data_tree = []
    with pdfplumber.open(pdf_path) as pdf:
        for page_num, page in enumerate(pdf.pages):
            words = page.extract_words(keep_blank_chars=False)
            if not words: continue

            y_buckets = {}
            for w in words:
                y_b = round(w["top"] / 4) * 4
                if w["text"].lower() in MARG_HEADERS:
                    y_buckets.setdefault(y_b, []).append(w)

            if not y_buckets: continue
            best_y = max(y_buckets.keys(), key=lambda y: len(y_buckets[y]))
            if len(y_buckets[best_y]) < 2: continue


            header_top = min([w["top"] for w in y_buckets[best_y]])
            


            # THE FIX: Tighten the merging gap from 15 down to 8 pixels
            anchor_words = [w for w in words if abs(w["top"] - header_top) < 10 and not re.match(r'^[-=]+$', w["text"])]
            if not anchor_words: continue
            anchor_words.sort(key=lambda w: w["x0"])

            columns = []
            current_label = {"text": anchor_words[0]["text"], "x0": anchor_words[0]["x0"], "x1": anchor_words[0]["x1"]}
            
            for w in anchor_words[1:]:
                gap = w["x0"] - current_label["x1"]
                
                # THE FIX: Smart Split. Force a split if the next word is a known independent column, 
                # otherwise allow standard spaces (up to 10px) to keep "Item Name" or "Sale Value" together.
                is_standalone_col = w["text"].lower() in {"days", "mrp", "rate", "ord qty", "qty", "value", "exp.", "expiry", "pack", "short"}
                
                if gap < 4 or (gap < 10 and not is_standalone_col): 
                    current_label["text"] += " " + w["text"]
                    current_label["x1"] = w["x1"]



                else:
                    columns.append({
                        "label": current_label["text"].title(), 
                        "center": (current_label["x0"] + current_label["x1"]) / 2.0,
                        "x0": current_label["x0"]
                    })
                    current_label = {"text": w["text"], "x0": w["x0"], "x1": w["x1"]}
                    
            columns.append({
                "label": current_label["text"].title(), 
                "center": (current_label["x0"] + current_label["x1"]) / 2.0, 
                "x0": current_label["x0"]
            })

            # Find descriptive column
            desc_index = 0
            target_words = ["description", "product", "item", "particulars", "company", "salt", "category", "store", "godown", "supplier"]
            for i, col in enumerate(columns):
                if any(word in col["label"].lower() for word in target_words):
                    desc_index = i
                    break

            # THE FIX: Draw dividers 5 pixels to the left of the NEXT column's start position.
            # This prevents S.No. numbers from spilling into the Item column.
            dividers = []
            for i in range(len(columns) - 1):
                dividers.append(columns[i+1]["x0"] - 5)
            if not dividers: dividers = [9999]


            lines = {}
            for w in words:
                if w["top"] < header_top + 10: continue
                y = round(w["top"] / 3) * 3
                lines.setdefault(y, []).append(w)

            # THE FIX: Filter out purely decorative lines AFTER grouping, not word-by-word.
            # This protects group headers like "=== SUN PHARMA ===" from having their equals signs stripped.
            clean_lines = {}
            for y, lw in lines.items():
                line_text_no_spaces = "".join(w["text"] for w in lw).replace(" ", "")
                if not re.match(r'^[-=]+$', line_text_no_spaces):
                    clean_lines[y] = lw
            lines = clean_lines


            current_block_name = f"Report View {page_num + 1}"
            current_group_name = None
            current_item = None


            dynamic_inline_keys = [col["label"].lower() + ":" for col in columns if len(col["label"]) > 2]



            for y in sorted(lines.keys()):
                lw = sorted(lines[y], key=lambda w: w["x0"])
                if not lw: continue
                if marg_is_stop_line(lw): break

                main_divider = dividers[desc_index] if len(dividers) > desc_index else dividers[0]
                fin_words = [w["text"] for w in lw if midpoint(w) >= main_divider]
                has_grid_data = any(any(char.isdigit() or char == '-' for char in text) for text in fin_words)

                col_bins = [[] for _ in columns]
                for w in lw:
                    cx = midpoint(w)
                    placed = False
                    for i in range(len(dividers)):
                        if cx < dividers[i]:
                            col_bins[i].append(w["text"])
                            placed = True
                            break
                    if not placed:
                        col_bins[-1].append(w["text"])

                text_str = " ".join(col_bins[desc_index]).strip()
                pre_desc_text = ""
                for i in range(desc_index): pre_desc_text += " ".join(col_bins[i]).strip()

                # if "total:" in text_str.lower() or "total :" in text_str.lower() or "grand total" in text_str.lower():
                #     continue

                if "total:" in text_str.lower() or "total :" in text_str.lower() or "grand total" in text_str.lower():
                    # THE FIX: Save and wipe current_item so the next line isn't falsely treated as a description continuation
                    if current_item:
                        clean_item = {k: v for k, v in current_item.items() if k != "_anchor_x0"}
                        block_node = next((n for n in data_tree if n["name"] == current_block_name), None)
                        if not block_node:
                            block_node = {"name": current_block_name, "type": "view", "items": []}
                            data_tree.append(block_node)
                        block_node["items"].append(clean_item)
                        current_item = None
                    continue


                is_continuation_candidate = (
                    current_item is not None
                    and current_x0 is not None
                    and current_item.get("_anchor_x0") is not None
                    and abs(current_x0 - current_item["_anchor_x0"]) <= 10
                )
                


                # Define full_line_text to catch headers regardless of which column they land in
                full_line_text = " ".join(w["text"] for w in lw).strip()
                full_line_lower = full_line_text.lower()
                
                # THE FIX: Dynamically check if the line contains at least 2 active column headers
                header_matches = sum(1 for key in dynamic_inline_keys if key in full_line_lower)
                is_inline_item_header = header_matches >= 2
                
                is_group_format = ("=" in full_line_text) or (full_line_text.startswith("[") and full_line_text.endswith("]"))
                
                # If any explicit format is matched (including inline headers), bypass has_grid_data completely
                is_block_header = is_inline_item_header or is_group_format or (
                    not has_grid_data 
                    and full_line_text.isupper() 
                    and len(full_line_text) > 2
                    and not is_continuation_candidate
                )

                if is_block_header:
                    if current_item:
                        clean_item = {k: v for k, v in current_item.items() if k != "_anchor_x0"}
                        block_node = next((n for n in data_tree if n["name"] == current_block_name), None)
                        if not block_node:
                            block_node = {"name": current_block_name, "type": "view", "items": []}
                            data_tree.append(block_node)
                        block_node["items"].append(clean_item)
                        current_item = None
                    # Use full_line_text here too
                    current_group_name = full_line_text.replace("=", "").replace("-", "").replace("[", "").replace("]", "").strip()
                    continue



                grid_dict = {}
                for i, col in enumerate(columns):
                    if i == desc_index: continue
                    val = " ".join(col_bins[i]).strip()
                    if val: grid_dict[col["label"]] = val




                # ---------------------------------------------------------
                # NEW LOGIC: Tally-Inspired X0 Tracking & Regex Defense
                # ---------------------------------------------------------


                # Calculate exact x0 for indent matching
                desc_left_bound = dividers[desc_index - 1] if desc_index > 0 else 0
                desc_right_bound = dividers[desc_index] if desc_index < len(dividers) else 9999
                desc_words = [w for w in lw if desc_left_bound <= midpoint(w) < desc_right_bound]
                current_x0 = desc_words[0]["x0"] if desc_words else None
                
                has_sno_in_pre = bool(pre_desc_text and any(char.isdigit() for char in pre_desc_text))
                has_sno_in_text = bool(re.match(r'^\d+\s+', text_str)) 
                
                is_new_item = has_sno_in_pre or has_sno_in_text
                is_batch_row = "Batch:" in text_str or "Batch:" in grid_dict.get("Item / Batch", "") or (not is_new_item and has_grid_data and current_item)

                if is_new_item or (not current_item and text_str and not is_batch_row):
                    # Save previous item, stripping the internal _anchor_x0 key
                    if current_item:
                        clean_item = {k: v for k, v in current_item.items() if k != "_anchor_x0"}
                        block_node = next((n for n in data_tree if n["name"] == current_block_name), None)
                        if not block_node:
                            block_node = {"name": current_block_name, "type": "view", "items": []}
                            data_tree.append(block_node)
                        block_node["items"].append(clean_item)
                    
                    clean_sno = pre_desc_text
                    clean_text = text_str
                    # if has_sno_in_text and not has_sno_in_pre:
                    #     match = re.match(r'^(\d+)\s+(.*)', text_str)
                    #     if match:
                    #         clean_sno = match.group(1)
                    #         clean_text = match.group(2)

                    if has_sno_in_text and not has_sno_in_pre:
                        match = re.match(r'^(\d+)\s+(.*)', text_str)
                        if match:
                            clean_sno = match.group(1)
                            clean_text = match.group(2)
                    
                    if current_group_name:
                        grid_dict["Group"] = current_group_name
                        

                

                    # Inside the 'is_new_item' initialization block
                    current_item = {
                        "S.No.": clean_sno, 
                        "particulars": clean_text, 
                        "data": grid_dict, 
                        "batches": [],
                        "_anchor_x0": current_x0  # Store for multi-line check
                    }
                    
                elif is_batch_row and current_item:
                    batch_data = {"Batch_Detail": text_str}
                    batch_data.update(grid_dict)
                    current_item["batches"].append(batch_data)
                    

                else:
                    # Multi-line item description continuation
                    if current_item:
                        # If there are no financials on this line, accept it as text continuation regardless of indent
                        if not has_grid_data and text_str:
                            current_item["particulars"] = (current_item["particulars"] + " " + text_str).strip()
                        # Otherwise, fall back to the strict Tally-like cluster tolerance
                        elif current_x0 is not None and current_item.get("_anchor_x0") is not None:
                            if abs(current_x0 - current_item["_anchor_x0"]) <= 15.0: # relaxed slightly to 15
                                if text_str:
                                    current_item["particulars"] = (current_item["particulars"] + " " + text_str).strip()
                                if grid_dict: 
                                    current_item["data"].update(grid_dict)

            # Commit the final item on the page
            if current_item:
                clean_item = {k: v for k, v in current_item.items() if k != "_anchor_x0"}
                block_node = next((n for n in data_tree if n["name"] == current_block_name), None)
                if not block_node:
                    block_node = {"name": current_block_name, "type": "view", "items": []}
                    data_tree.append(block_node)
                block_node["items"].append(clean_item)

    return data_tree


                


def parse_marg_spreadsheet(df: pd.DataFrame) -> list:
    """
    Parses a raw Marg Excel/CSV DataFrame into the Universal JSON Tree structure.
    """
    # 1. Identify the actual table header row
    # Marg exports often have 3-5 rows of metadata at the top before the columns start.
    header_row_index = -1
    # target_headers = {'description', 'particulars', 'item', 'product', 'qty', 'rate', 'mrp'}


    # Scan the first 20 rows to find where the actual table begins
    for idx, row in df.head(20).iterrows():
        row_str_set = set(str(val).lower().strip() for val in row.values if pd.notna(val))
        # Use the master MARG_HEADERS to catch any stock report variation
        if len(MARG_HEADERS.intersection(row_str_set)) >= 2:
            header_row_index = idx
            break

    if header_row_index == -1:
        return []  # Could not find valid table headers

    # 2. Reshape the DataFrame
    # Set the found row as the column headers and drop everything above it
    df.columns = df.iloc[header_row_index].astype(str).str.strip()
    df = df.iloc[header_row_index + 1:].reset_index(drop=True)

    # Drop completely empty columns and "Unnamed" columns
    df = df.loc[
        :, df.columns.notna() & (df.columns != '') & (~df.columns.str.contains('Unnamed', case=False, na=False))]



    # Identify the primary descriptive column
    desc_col = None
    
    # Priority 1: True Item Description (Use substring match to catch 'Item Name')
    for col in df.columns:
        col_lower = str(col).lower()
        if any(k in col_lower for k in ['description', 'particulars', 'item', 'product']):
            desc_col = col
            break
            
    # Priority 2: Groupings (Fallback only if an item column doesn't exist)
    if not desc_col:
        for col in df.columns:
            col_lower = str(col).lower()
            if any(k in col_lower for k in ['company', 'salt', 'category', 'store', 'godown', 'supplier']):
                desc_col = col
                break
                
    if not desc_col and len(df.columns) > 0:
        desc_col = df.columns[0]

    if not desc_col:
        return []



    # 3. Parse Data into the JSON Tree Structure
    data_tree = []
    current_block_name = "Default View"
    current_items = []
    current_item = None # Track the active parent item

    for _, row in df.iterrows():
        desc_val = row[desc_col]
        desc_str = str(desc_val).strip() if pd.notna(desc_val) else ""
        desc_lower = desc_str.lower()

        # 1. GLOBAL TOTAL CHECK
        # Check if the word "total" or "grand" appears ANYWHERE in this row
        is_total_row = any(
            isinstance(val, str) and ("total" in val.lower() or "grand" in val.lower() or "page" in val.lower()) 
            for val in row.values if pd.notna(val)
        )
        
        if is_total_row:
            continue # Safely skip summary lines, even if they are shifted to weird columns




        # Extract row data and check for financials
        has_financials = False
        row_data = {}
        for col in df.columns:
            val = row[col]
            if pd.notna(val) and str(val).strip() != '':
                row_data[col] = str(val).strip()
                # THE FIX: Only flag financials if digits appear in columns OTHER than the description
                if col != desc_col and any(char.isdigit() for char in str(val)):
                    has_financials = True

        # Skip completely empty rows
        if not desc_str and not has_financials:
            continue

        # Is it a Group Header? (e.g., [Store: Main] or === CIPLA ===)
        is_only_desc = len(row_data) == 1 and desc_col in row_data
        is_block_header = (not has_financials and is_only_desc) or (not has_financials and desc_str.isupper()) or (desc_str.startswith("[") and desc_str.endswith("]"))

        
        if is_block_header:
            if current_item:
                current_items.append(current_item)
                current_item = None
                
            if current_items:
                data_tree.append({"name": current_block_name, "type": "view", "items": current_items})
                current_items = [] 
            
            # Clean up formatting
            current_block_name = desc_str.replace("=", "").replace("-", "").replace("[", "").replace("]", "").strip()
            
        else:
            # Data Row Routing
            if desc_str:
                # Normal Item Row
                if current_item:
                    current_items.append(current_item)
                    
                current_item = {
                    "particulars": desc_str,
                    "data": row_data,
                    "batches": [] # Initialize for potential children
                }
            elif has_financials:
                # 2. BATCH KEY CHECK
                # Missing description, but has numbers. Is it truly a batch?
                batch_col = next((k for k in row_data.keys() if 'batch' in k.lower()), None)
                has_batch_val = batch_col and row_data.get(batch_col)

                if current_item and (has_batch_val or len(row_data) >= 2):
                    current_item["batches"].append(row_data)
                elif not current_item:
                    # 3. ORPHAN CHECK
                    # We found numbers, but no parent item exists yet.
                    current_items.append({"particulars": "Unknown Data Row", "data": row_data, "batches": []})

    # Commit final items
    if current_item:
        current_items.append(current_item)
        
    if current_items:
        data_tree.append({
            "name": current_block_name,
            "type": "view",
            "items": current_items
        })

    return data_tree

    

def midpoint(w: dict) -> float:
    return (w["x0"] + w["x1"]) / 2.0







# --- ADD THIS NEW CONSTANT ---
# The Ultimate Marg Order Vocabulary List
MARG_ORDER_HEADERS = {
    # Identifiers
    "s.no.", "s.no", "order", "ord.no", "bill", "date", "status", "due",
    # Entities / Groupings
    "party", "customer", "ledger", "item", "item name", "product", "description", "particulars", 
    "supplier", "m.r.", "salesman", "station", "route", "area", "agency", "company",
    # Quantities & Fulfillment
    "qty", "quantity", "ord", "ord qty", "ord.qty", "clear", "supplied", "sup.qty", "pending", "pend.qty", "bal.qty", "shortage", "stock", "pack", "free",
    # Financials
    "rate", "p.rate", "amount", "value", "net", "gross", "basic", "discount", "dis%", 
    "tax", "gst", "cgst", "sgst", "igst", "balance", "mrp", "ptr"
}

# --- ADD THIS NEW FUNCTION ---
def parse_marg_order_pdf(pdf_path: str) -> list:
    """
    Parses Marg Order Reports from PDF using spatial geometry.
    Handles Party-wise, Item-wise, and flat Order Registers.
    """
    data_tree = []
    
    with pdfplumber.open(pdf_path) as pdf:
        for page_num, page in enumerate(pdf.pages):
            words = page.extract_words(keep_blank_chars=False)
            if not words: continue

            # 1. Find the header row based on Order vocabulary
            y_buckets = {}
            for w in words:
                y_b = round(w["top"] / 4) * 4
                if w["text"].lower() in MARG_ORDER_HEADERS:
                    y_buckets.setdefault(y_b, []).append(w)

            if not y_buckets: continue
            best_y = max(y_buckets.keys(), key=lambda y: len(y_buckets[y]))
            if len(y_buckets[best_y]) < 2: continue 

            header_top = min([w["top"] for w in y_buckets[best_y]])


            # # 2. Build Columns



            anchor_words = [w for w in words if abs(w["top"] - header_top) < 15 and w["text"].lower() in MARG_ORDER_HEADERS]
            anchor_words = [w for w in anchor_words if not re.match(r'^[-=]+$', w["text"])]
            if not anchor_words: continue
            anchor_words.sort(key=lambda w: midpoint(w))

            # 2. Build Columns with Smart Split
            columns = []
            current_label = {"text": anchor_words[0]["text"], "x0": anchor_words[0]["x0"], "x1": anchor_words[0]["x1"]}
            for w in anchor_words[1:]:
                gap = w["x0"] - current_label["x1"]
                
                # Order-specific standalone columns
                # Order-specific standalone columns
                is_standalone_col = w["text"].lower() in {"qty", "ord", "clear", "pending", "amount", "value", "rate", "net", "tax", "gst", "cgst", "sgst", "basic", "pack", "balance", "mrp", "shortage", "free", "sup.qty", "pend.qty", "ord.qty"}
                
                if gap < 4 or (gap < 10 and not is_standalone_col):
                    current_label["text"] += " " + w["text"]
                    current_label["x1"] = w["x1"]
                else:
                    columns.append({"label": current_label["text"].title(), "center": (current_label["x0"] + current_label["x1"]) / 2.0, "x0": current_label["x0"]})
                    current_label = {"text": w["text"], "x0": w["x0"], "x1": w["x1"]}
            columns.append({"label": current_label["text"].title(), "center": (current_label["x0"] + current_label["x1"]) / 2.0, "x0": current_label["x0"]})



            # Find the primary descriptive column (Party, Item, Route, MR, etc.)
            desc_index = 0
            target_words = ["party", "customer", "item", "product", "description", "particulars", "m.r.", "salesman", "station", "route", "area", "agency", "company"]
            for i, col in enumerate(columns):
                if any(word in col["label"].lower() for word in target_words):
                    desc_index = i
                    break
            
            # 3. Calculate Dividers
            dividers = []
            for i in range(desc_index): dividers.append((columns[i]["center"] + columns[i+1]["center"]) / 2.0)
            if desc_index + 1 < len(columns): dividers.append(columns[desc_index + 1]["x0"] - 15)
            for i in range(desc_index + 1, len(columns) - 1): dividers.append((columns[i]["center"] + columns[i+1]["center"]) / 2.0)
            if not dividers: dividers = [9999]


            # 4. Group words into lines
            lines = {}
            for w in words:
                if w["top"] < header_top + 10: continue
                y = round(w["top"] / 3) * 3
                lines.setdefault(y, []).append(w)

            # Filter out purely decorative lines AFTER grouping
            clean_lines = {}
            for y, lw in lines.items():
                line_text_no_spaces = "".join(w["text"] for w in lw).replace(" ", "")
                if not re.match(r'^[-=]+$', line_text_no_spaces):
                    clean_lines[y] = lw
            lines = clean_lines



            current_block_name = f"Order View {page_num + 1}"
            current_group_name = None
            current_item = None

            # THE FIX: Dynamically generate inline header traps based on the PDF's actual columns
            # We add a colon ":" to match Marg's inline format (e.g., "Pack:", "Mrp:")
            dynamic_inline_keys = [col["label"].lower() + ":" for col in columns if len(col["label"]) > 2]

            # 5. Extract Data
            for y in sorted(lines.keys()):
                lw = sorted(lines[y], key=lambda w: w["x0"])
                if not lw: continue
                if marg_is_stop_line(lw): break

                # Check if this line has numeric financial data
                main_divider = dividers[desc_index] if len(dividers) > desc_index else dividers[0]
                fin_words = [w["text"] for w in lw if midpoint(w) >= main_divider]
                has_grid_data = any(any(char.isdigit() or char == '-' for char in text) for text in fin_words)

                # Bin words into columns
                col_bins = [[] for _ in columns] 
                for w in lw:
                    cx = midpoint(w)
                    placed = False
                    for i in range(len(dividers)):
                        if cx < dividers[i]:
                            col_bins[i].append(w["text"])
                            placed = True
                            break
                    if not placed:
                        col_bins[-1].append(w["text"])

                
                text_str = " ".join(col_bins[desc_index]).strip()
                pre_desc_text = "".join([" ".join(col_bins[i]).strip() for i in range(desc_index)])

                if "total:" in text_str.lower() or "total :" in text_str.lower() or "grand total" in text_str.lower():
                    if current_item:
                        clean_item = {k: v for k, v in current_item.items() if k != "_anchor_x0"}
                        block_node = next((n for n in data_tree if n["name"] == current_block_name), None)
                        if not block_node:
                            block_node = {"name": current_block_name, "type": "order_group", "items": []}
                            data_tree.append(block_node)
                        block_node["items"].append(clean_item)
                        current_item = None
                    continue

                desc_left_bound = dividers[desc_index - 1] if desc_index > 0 else 0
                desc_right_bound = dividers[desc_index] if desc_index < len(dividers) else 9999
                desc_words = [w for w in lw if desc_left_bound <= midpoint(w) < desc_right_bound]
                current_x0 = desc_words[0]["x0"] if desc_words else None

                is_continuation_candidate = (
                    current_item is not None
                    and current_x0 is not None
                    and current_item.get("_anchor_x0") is not None
                    and abs(current_x0 - current_item["_anchor_x0"]) <= 10
                )
                

                full_line_text = " ".join(w["text"] for w in lw).strip()
                full_line_lower = full_line_text.lower()
                
                # THE FIX: Explicit Keyword Overrides
                is_party_header = full_line_lower.startswith("party:") or full_line_lower.startswith("party ") or full_line_lower.startswith("supplier:")
                
                # THE FIX: Dynamically check if the line contains at least 2 active column headers
                header_matches = sum(1 for key in dynamic_inline_keys if key in full_line_lower)
                is_inline_item_header = header_matches >= 2
                
                is_group_format = ("=" in full_line_text) or (full_line_text.startswith("[") and full_line_text.endswith("]"))
                
                # If any explicit format is matched, bypass has_grid_data completely
                is_block_header = is_party_header or is_inline_item_header or is_group_format or (
                    not has_grid_data 
                    and full_line_text.isupper() 
                    and len(full_line_text) > 2
                    and not is_continuation_candidate
                )

                if is_block_header:
                    if current_item:
                        clean_item = {k: v for k, v in current_item.items() if k != "_anchor_x0"}
                        block_node = next((n for n in data_tree if n["name"] == current_block_name), None)
                        if not block_node:
                            block_node = {"name": current_block_name, "type": "order_group", "items": []}
                            data_tree.append(block_node)
                        block_node["items"].append(clean_item)
                        current_item = None
                    current_group_name = full_line_text.replace("=", "").replace("-", "").replace("[", "").replace("]", "").strip()
                    continue

                grid_dict = {}
                for i, col in enumerate(columns):
                    if i == desc_index: continue
                    val = " ".join(col_bins[i]).strip()
                    if val: grid_dict[col["label"]] = val

                starts_new_item = False
                if pre_desc_text: 
                    starts_new_item = True
                elif has_grid_data and current_item and any(k in current_item["data"] for k in grid_dict.keys()):
                    starts_new_item = True
                elif not current_item and (text_str or has_grid_data):
                    starts_new_item = True

                if starts_new_item:
                    if current_item:
                        clean_item = {k: v for k, v in current_item.items() if k != "_anchor_x0"}
                        block_node = next((n for n in data_tree if n["name"] == current_block_name), None)
                        if not block_node:
                            block_node = {"name": current_block_name, "type": "order_group", "items": []}
                            data_tree.append(block_node)
                        block_node["items"].append(clean_item)
                        
                    if current_group_name:
                        grid_dict["Group"] = current_group_name
                    current_item = {"particulars": text_str, "data": grid_dict, "_anchor_x0": current_x0}
                else:
                    if current_item:
                        if not has_grid_data and text_str:
                            current_item["particulars"] = (current_item["particulars"] + " " + text_str).strip()
                        elif current_x0 is not None and current_item.get("_anchor_x0") is not None:
                            if abs(current_x0 - current_item["_anchor_x0"]) <= 15.0:
                                if text_str:
                                    current_item["particulars"] = (current_item["particulars"] + " " + text_str).strip()
                                if grid_dict: 
                                    current_item["data"].update(grid_dict)

            # Commit the final item on the page
            if current_item:
                block_node = next((n for n in data_tree if n["name"] == current_block_name), None)
                if not block_node:
                    block_node = {"name": current_block_name, "type": "order_group", "items": []}
                    data_tree.append(block_node)
                # block_node["items"].append(current_item)
                clean_item = {k: v for k, v in current_item.items() if k != "_anchor_x0"}
                block_node["items"].append(clean_item)

    return data_tree




def parse_marg_order_spreadsheet(df: pd.DataFrame) -> list:
    """
    Parses Marg Order Reports (Party-wise, Item-wise, or Flat Register) into JSON.
    """
    # 1. Identify the actual table header row
    header_row_index = -1
    
    
    for idx, row in df.head(20).iterrows():
        row_str_set = set(str(val).lower().strip() for val in row.values if pd.notna(val))
        
        # If we find at least 2 common Order headers, this is our row
        if len(MARG_ORDER_HEADERS.intersection(row_str_set)) >= 2: 
            header_row_index = idx
            break
            
    if header_row_index == -1:
        return [] 
        
    # 2. Reshape the DataFrame
    df.columns = df.iloc[header_row_index].astype(str).str.strip()
    df = df.iloc[header_row_index + 1:].reset_index(drop=True)
    df = df.loc[:, df.columns.notna() & (df.columns != '') & (~df.columns.str.contains('Unnamed', case=False, na=False))]
    



    # 3. Identify the primary descriptive column
    desc_col = None
    
    # Priority 1: True Item/Order Description 
    for col in df.columns:
        col_lower = str(col).lower()
        if any(k in col_lower for k in ['item', 'product', 'description', 'particulars']):
            desc_col = col
            break
            
    # Priority 2: Groupings / Parties (Fallback)
    if not desc_col:
        for col in df.columns:
            col_lower = str(col).lower()
            if any(k in col_lower for k in ['party', 'customer', 'party name', 'm.r.', 'salesman', 'station', 'route', 'area', 'agency', 'company']):
                desc_col = col
                break
            
    # Fallback if specific columns aren't found, just grab the first column
    if not desc_col and len(df.columns) > 0:
        desc_col = df.columns[0]

    # 4. Parse Data into the JSON Tree Structure
    data_tree = []
    current_block_name = "Order Register View" # Default name for flat files
    current_items = []
    
    for _, row in df.iterrows():
        desc_val = row[desc_col]
        
        if pd.isna(desc_val) or str(desc_val).strip() == '':
            continue
            
        desc_str = str(desc_val).strip()
        desc_lower = desc_str.lower()
        
        if "total" in desc_lower or "page" in desc_lower or "grand" in desc_lower:
            continue
            
        has_financials = False
        row_data = {}
        

        # Check ALL columns for data (including desc_col)
        for col in df.columns:
            val = row[col]
            if pd.notna(val) and str(val).strip() != '':
                row_data[col] = str(val).strip()
                # THE FIX: Only flag financials if digits appear in columns OTHER than the description
                if col != desc_col and any(char.isdigit() for char in str(val)):
                    has_financials = True
                        
        # Routing Logic: Is it a Group Header (e.g. === PARTY NAME ===) or an Item?
        is_only_desc = len(row_data) == 1 and desc_col in row_data
        # THE FIX: If it's the only column populated, it's definitively a group header, even if the drug name has numbers (e.g. 500MG)
        is_block_header = is_only_desc or (not has_financials and desc_str.isupper()) or (desc_str.startswith("[") and desc_str.endswith("]"))


        
        if is_block_header:
            if current_items:
                data_tree.append({"name": current_block_name, "type": "order_group", "items": current_items})
                current_items = [] 
            
            # Clean up Marg's formatting
            current_block_name = desc_str.replace("=", "").replace("-", "").strip()
        else:
            current_items.append({"particulars": desc_str, "data": row_data})
            
    if current_items:
        data_tree.append({"name": current_block_name, "type": "order_group", "items": current_items})
        
    return data_tree