import pandas as pd
import os

# Paths
BASE_DIR = "/run/media/ishpreet/New Volume/Auribises/Vardhman Textiles"
DATA_FILE = os.path.join(BASE_DIR, "Final Base Data for PD Gr issue Norsm 15-01-26.xlsx")
NORM_FILE = os.path.join(BASE_DIR, "AT1 MKT PD Gr Norms Rev on 13-12-2025.xlsx")

def inspect_main_data():
    print("--- Inspecting PO Type Sheet ---")
    try:
        df_po = pd.read_excel(DATA_FILE, sheet_name='PO Type')
        print(df_po.head(15).to_markdown())
        print(df_po.columns)
    except Exception as e:
        print(f"Error reading PO Type: {e}")

    print("\n--- Inspecting Detail Sheet (first 20 rows) ---")
    try:
        df_detail = pd.read_excel(DATA_FILE, sheet_name='Detail', header=2) # Assuming header is row 3 based on previous work
        print(df_detail.columns)
        # Look for PO Type column
        print(df_detail[['Sale Order', 'PO No', 'PO Type', 'Order Qty', 'Reserver Qty as per Std Norms', 'Actual Gr Opening']].head(20).to_markdown())
    except Exception as e:
        print(f"Error reading Detail: {e}")

def inspect_norms():
    print("\n--- Inspecting Norms Sheet ---")
    try:
        df_norm = pd.read_excel(NORM_FILE)
        print(df_norm.iloc[0:20].to_markdown()) # Print first 20 rows of raw data
    except Exception as e:
        print(f"Error reading Norms: {e}")

if __name__ == "__main__":
    print("--- PO Type Sheet ---")
    try:
        df_po = pd.read_excel(DATA_FILE, sheet_name='PO Type')
        print(df_po.to_markdown()) 
    except Exception as e:
        print(f"Error: {e}")

    print("\n--- Detail Sheet Sample for PO Type checks ---")
    try:
        df_detail = pd.read_excel(DATA_FILE, sheet_name='Detail', header=2)
        print(f"Detail Columns: {df_detail.columns.tolist()}")
        
        # Check actual column name for Sale Order
        # Likely 'Sales Order' or 'SO' or similar if not 'Sale Order'
        possible_so_cols = [c for c in df_detail.columns if 'Order' in str(c) or 'SO' in str(c)]
        print(f"Possible SO columns: {possible_so_cols}")
        
        so_col = 'Sale Order' if 'Sale Order' in df_detail.columns else possible_so_cols[0] if possible_so_cols else None
        
        if 'COPS_NO' in df_detail.columns:
            counts = df_detail.groupby('COPS_NO')['PO_NO'].nunique()
            print(f"\nMax POs per COPS_NO: {counts.max()}")
            if counts.max() > 1:
                print("COPS_NO is likely Sale Order.")
                so_col = 'COPS_NO'

        if 'OCDKE1' in df_detail.columns:
             counts = df_detail.groupby('OCDKE1')['PO_NO'].nunique()
             print(f"Max POs per OCDKE1: {counts.max()}")
        
        if so_col:
             # Group by Sale Order and count POs
            counts = df_detail.groupby(so_col)['PO_NO'].nunique()
            multi_po_orders = counts[counts > 1].head(5).index.tolist()
            
            print(f"Orders with multiple POs: {multi_po_orders}")
            
            if multi_po_orders:
                sample_order = multi_po_orders[0]
                print(f"\nDetails for Order {sample_order}:")
                # Adjust column selection based on actual names
                cols_to_show = [so_col, 'PO_NO', 'PO Type', 'DORQT1', 'RES_QTY', 'ISS_QTY', 'pack_qty', 'pack_fresh']
                
                print(df_detail[df_detail[so_col] == sample_order][cols_to_show].to_markdown())
        else:
            print("Could not identify Sale Order column.")
            
    except Exception as e:
        print(f"Error: {e}")