@financial_router.post("/upload-gl/")
async def upload_gl_excel(file: UploadFile = File(...), db: Session = Depends(get_db)):


    if not file.filename.endswith(".xlsx"):
        raise HTTPException(status_code=400, detail="Invalid file type. Please upload an Excel file.")

    # Read Excel file into pandas DataFrame
    contents = await file.read()
    df = pd.read_excel(BytesIO(contents))

    # Apply camelCase renaming to all columns
    df.columns = [to_camel_case(col) for col in df.columns]

    # Convert values in 'accountName' and 'category' columns to camelCase
    df["accountName"] = df["accountName"].astype(str).apply(to_camel_case)
    df["category"] = df["category"].astype(str).apply(to_camel_case)



    normalized_cols = {col.strip().lower(): col for col in df.columns}
    new_columns = {}

    for internal_col, aliases in COLUMN_ALIASES.items():
        found = False
        for alias in aliases:
            normalized_alias = alias.strip().lower()
            if normalized_alias in normalized_cols:
                new_columns[normalized_cols[normalized_alias]] = internal_col
                found = True
                break
        if not found:
            raise HTTPException(
                status_code=400,
                detail=f"Acceptable aliases: {aliases}"
            )

    df.rename(columns=new_columns, inplace=True)

    required_cols = {"date", "general_ledger_code", "account_name", "category", "debit", "credit"}
    missing_cols = required_cols - set(df.columns)

    if missing_cols:
        raise HTTPException(
            status_code=400,
            detail=f"Missing column(s): {', '.join(missing_cols)}. Required: {', '.join(required_cols)}"
        )

    #check for null values in row level
    invalid_row_indices = df[
    df[["general_ledger_code", "account_name", "category"]]
    .apply(lambda x: x.isnull() | (x.astype(str).str.strip() == ""), axis=1)
    .any(axis=1)  # row level check 
    ].index.tolist()

    if invalid_row_indices:
        raise HTTPException(
                status_code=400,
                detail=f"Rows with null or empty values: {invalid_row_indices}"
            )
    
    file_gl_rows = df[["general_ledger_code", "account_name", "category"]].drop_duplicates() # get the unique row of this three cols

    print(file_gl_rows)
    
    stm = select(GeneralLedgerMaster)
    result = await db.execute(stm)
    general_ledger_master_data = result.scalars().all()  
    data_as_dicts = [item.as_dict() for item in general_ledger_master_data] 
    master_gl_df = pd.DataFrame(data_as_dicts)

    master_gl_rows = master_gl_df[["general_ledger_code", "account_name", "category"]].drop_duplicates()


    # Compare rows in file_gl_rows that are not in master_gl_rows
    difference_df = file_gl_rows.merge(
        master_gl_rows,
        on=["general_ledger_code", "account_name", "category"],
        how="left",
        indicator=True
    ).query('_merge == "left_only"').drop(columns=["_merge"]) # gives the Rows present in file but not in master:

    print("Rows present in file but not in master:")
    if not difference_df.empty:
        invalid_rows = difference_df.to_dict(orient="records")
        raise HTTPException(
        status_code=400,
        detail={
            "error": "Invalid GL rows",
            "message": "The following rows do not match master GL data",
            "invalid_rows": invalid_rows
        }
    )




    for index, row in df.iterrows():
        

        try:
            entry = GeneralLedger(
                id=uuid.uuid4(),
                date=pd.to_datetime(row["date"]),
                general_ledger_code =row["general_ledger_code"],
                account_name=row["account_name"],
                category=row["category"],
                description=row.get("description"),
                debit=row["debit"],
                credit=row["credit"],
                entry_by=row.get("entry_by", "system"),
                entry_system_source=row.get("entry_system_source", "excel_upload")
            )
            db.add(entry)
        except Exception as e:
            print(e)
            raise e
           

    await db.commit()







@router.get("/integration-history/{integration_id}/gl-entries")
async def get_gl_entries_by_integration_id(
    integration_id: str,
    page: int = Query(1, ge=1),
    limit: int = Query(100, ge=1),
    last_entry_id: int = Query(None, ge=0),  # Add cursor (last_entry_id)
    db: AsyncSession = Depends(get_db)
):
    query = select(GeneralLedger).where(GeneralLedger.integration_id == integration_id)
    
    if last_entry_id:
        query = query.where(GeneralLedger.id > last_entry_id)
    
    result = await db.execute(query.limit(limit))
    entries = result.scalars().all()

    if not entries:
        raise HTTPException(status_code=404, detail="No GL entries found for this integration.")

    return {
        "page": page,
        "limit": limit,
        "data": [entry.as_dict() for entry in entries],
        "next_page_cursor": entries[-1].id if entries else None
    }
