from fastapi import APIRouter,Depends, Query, Form 
from sqlalchemy.ext.asyncio import AsyncSession
from src.core.database import get_db 
from datetime import date , datetime 
from dateutil.relativedelta import relativedelta
from src.common.dependencies import get_current_company_user
from src.schemas.compnay_user import CurrentCompanyUser 
from .schemas import (Filters , ViewMoreFilter , TimeFilterEnum , CashFlowViewMoreTimeFilters,
                      CashFlowViewMoreViewFilters , ScenarioFilter , CurrencyFilter , ComparityFilter , BudgetFilter)
# from .controller import apply_entity_hierarchy_filter
# from .model import FinanceGeneralLedger 
from sqlalchemy import select , text 
import pandas as pd
from typing import List, Optional 
from decimal import Decimal 
import numpy as np
from fastapi.encoders import jsonable_encoder 
from fastapi.responses import JSONResponse
import pandas as pd


financial_router1 = APIRouter(prefix="/company/financial", tags=["financial module"]) 


from decimal import Decimal
import math

def clean_data(obj):
    if isinstance(obj, dict):
        return {k: clean_data(v) for k, v in obj.items()}
    
    elif isinstance(obj, list):
        return [clean_data(item) for item in obj]
    
    elif isinstance(obj, Decimal):
        return float(obj)   # convert Decimal to float
    
    elif isinstance(obj, float) and math.isnan(obj):
        return None        # replace NaN with None
    
    else:
        return obj




def apply_smart_currency(response, currency, currency_filter, money_fields):
    money_fields = set(money_fields)

    def format_number(value):
        if value is None:
            return None

        try:
            value = float(value)
        except:
            return value

        # INR → USD conversion
        # if currency == "USD":
        #     value = value / 89.6

        # INR formatting
        if currency == "INR" and abs(value) >= 100_000:
            return f"{round(value / 100_000, 2)} L"

        # USD formatting
        if currency == "USD":
            if currency_filter == "thousands" and abs(value) >= 1_000:
                return f"{round(value / 1_000, 2)} K"
            if currency_filter == "millions" and abs(value) >= 1_000_000:
                return f"{round(value / 1_000_000, 2)} M"

        return round(value, 2)

    def walk(obj):
        if isinstance(obj, list):
            return [walk(item) for item in obj]

        if isinstance(obj, dict):
            new_obj = {}
            for k, v in obj.items():

                # Convert only declared money fields (even if None)
                if k in money_fields:
                    new_obj[k] = format_number(v)
                else:
                    new_obj[k] = walk(v)

            return new_obj

        return obj

    return walk(response)


import numpy as np

def apply_entity_hierarchy_filter(
    data,
    entity=None,
    hierarchy=None,
    product=None,
    costcenter=None,
    profitcenter=None,
    region=None,
    customer=None,
    version=None,
    business_unit=None
):
    def row_matches(row: dict):
        if entity and row.get("entity_id") != int(entity):
            return False
        if hierarchy and row.get("department_id") != hierarchy:
            return False
        if product and row.get("product_id") != product:
            return False
        if costcenter and row.get("cost_center_id") != costcenter:
            return False
        if profitcenter and row.get("profitcenter_id") != profitcenter:
            return False
        if region and row.get("region") != region:
            return False
        if customer and row.get("customer_name") != customer:
            return False
        if version and row.get("version") != version:
            return False
        if business_unit and row.get("business_unit") != business_unit:
            return False
        return True

    def is_not_empty(value):
        if value is None:
            return False
        if isinstance(value, np.ndarray):
            return value.size > 0
        if isinstance(value, (list, dict, set, tuple)):
            return len(value) > 0
        return True  # numbers, strings, etc.

    def recursive_filter(obj):
        # Case 1: List
        if isinstance(obj, list):
            result = []
            for item in obj:
                if isinstance(item, dict):
                    # Looks like a row → apply row filter
                    if any(k in item for k in [
                        "entity_name", "department_name", "product_name",
                        "period", "quarter", "entity_id", "department_id"
                    ]):
                        if row_matches(item):
                            result.append(item)
                    else:
                        # Nested object
                        filtered = recursive_filter(item)
                        if is_not_empty(filtered):
                            result.append(filtered)
                else:
                    result.append(item)
            return result

        # Case 2: Dict
        if isinstance(obj, dict):
            new_obj = {}
            for k, v in obj.items():
                filtered = recursive_filter(v)
                if is_not_empty(filtered):
                    new_obj[k] = filtered
            return new_obj

        return obj

    return recursive_filter(data)


from collections import defaultdict

def mom_comparison(df, selected_year , value_cols):
    d=df
    print(d , "month")

    for col in value_cols: 
        # d[f"{col}_current"] = d[col]
        d[f"{col}_prev"] = d[col].shift(1)
        d[f"{col}_diff"] = d[col] - d[f"{col}_prev"] 
    res = d[d["period"].dt.year == selected_year].copy() 
    res["previous_period"] = (
        res["period"] - pd.DateOffset(months=1)
    ).dt.strftime("%Y-%m")
    res["period"] = pd.to_datetime(res["period"]).dt.strftime("%Y-%m")
    res = res.replace([np.inf, -np.inf], None)
    res = res.where(pd.notnull(res), None)
    res_dict = res.to_dict(orient = "records")
    return res_dict 


def year_on_year_compare(current_df, prev_df, filters , required_cols):
    current_df["period"] = pd.to_datetime(current_df["period"]).dt.strftime("%Y-%m")
    prev_df["period"] = pd.to_datetime(prev_df["period"].dt.strftime("%Y-%m"))
    
    # If prev_df is empty, create an empty DataFrame with same columns
    if prev_df is None or prev_df.empty:
        # Create prev_df with same merge columns and numeric columns set to NaN
        prev_df = pd.DataFrame({col: pd.NA for col in current_df.columns})
        prev_df = prev_df.head(0)  # keep it empty
    
    # Merge current and previous
    merged_df = pd.merge(
        current_df,
        prev_df,
        on=filters,
        how='left',  # keep all current year rows
        suffixes=('_current', '_previous')
    )
    print(merged_df)
    # Identify numeric columns to calculate difference (skip index/merge_cols)
    # numeric_cols = current_df.select_dtypes(include='number').columns.tolist()
    required_cols = [col for col in required_cols if col not in filters + ['index']]
    
    # Calculate diff: current - previous
    for col in required_cols:
        col_current = f'{col}_current'
        col_previous = f'{col}_previous'
        merged_df[f'{col}_diff'] = merged_df[col_current] - merged_df[col_previous] 
    
    print(merged_df.to_dict(orient = "records") , "ppppppppppppppp")
    
    return merged_df.to_dict(orient="records")



async def all_filters_data_display( db: AsyncSession = Depends(get_db)):
    entity_query = text("""Select entity_id , entity_name from fpna_landing.finance_entities""")
    entity_results = await db.execute(entity_query)
    entity_rows = entity_results.fetchall()
    entity_data =[dict(row._mapping) for row in entity_rows]
    entity_df = pd.DataFrame(entity_data) 
    entities = entity_df.to_dict(orient="records")

    department_query = text("""Select department_id , department_name from fpna_landing.finance_departments""")
    department_results = await db.execute(department_query)
    department_rows = department_results.fetchall()
    dep_data =[dict(row._mapping) for row in department_rows]
    dep_df = pd.DataFrame(dep_data)

    departments = dep_df.to_dict(orient = "records") 

    prod_query = text("""Select product_id , product_name from fpna_landing.crm_products""")
    prod_results = await db.execute(prod_query)
    prod_rows = prod_results.fetchall()
    prod_data =[dict(row._mapping) for row in prod_rows]
    dep_df = pd.DataFrame(prod_data)
    products = dep_df.to_dict(orient  = "records")

    cc_query = text("""Select cost_center_id , name from fpna_landing.finance_cost_centers""")
    cc_results = await db.execute(cc_query)
    cc_rows = cc_results.fetchall()
    cc_data =[dict(row._mapping) for row in cc_rows]
    cc_df = pd.DataFrame(cc_data)
    cost_centers = cc_df.to_dict(orient  = "records")

    cust_query = text("""Select customer_id , customer_name , region from fpna_landing.finance_customers""")
    cust_results = await db.execute(cust_query)
    cust_rows = cust_results.fetchall()
    cust_data =[dict(row._mapping) for row in cust_rows]
    cust_df = pd.DataFrame(cust_data)
    customers= cust_df.to_dict(orient  = "records") 

    cc_query = text("""Select cost_center_id , name from fpna_landing.finance_cost_centers""")
    cc_results = await db.execute(cc_query)
    cc_rows = cc_results.fetchall()
    cc_data =[dict(row._mapping) for row in cc_rows]
    cc_df = pd.DataFrame(cc_data)
    cost_centers = cc_df.to_dict(orient  = "records")

    profit_query = text("""Select profit_center_id , name from fpna_landing.finance_profit_centers""")
    profit_results = await db.execute(profit_query)
    profit_rows = profit_results.fetchall()
    profit_data =[dict(row._mapping) for row in profit_rows]
    profit_df = pd.DataFrame(profit_data)
    profit_centers = profit_df.to_dict(orient  = "records") 

    budget_query =  text("""Select version , scenario_id from fpna_landing.finance_budget""")
    budget_results = await db.execute(budget_query)
    budget_rows = budget_results.fetchall()
    budget_data =[dict(row._mapping) for row in budget_rows]
    budget_df = pd.DataFrame(budget_data)
    versions = budget_df.to_dict(orient  = "records") 

    return {"entities" :entities , "departments":departments , "products":products ,
            "cost_centers":cost_centers ,"profit_centers":profit_centers ,"customers" : customers , "versions":versions}


def serialize(value):
    if isinstance(value, Decimal):
        return float(value)
    return value 

@financial_router1.get("/profitandloss_statement/{year}/{currency}" ,dependencies=[Depends(get_current_company_user)])
async def  pnl_report(year:int, currency :str , db: AsyncSession = Depends(get_db),
                      time_filter :TimeFilterEnum = Query(default = TimeFilterEnum.all_periods),
                      currency_filter: CurrencyFilter =Query(default = CurrencyFilter.actual_value),
                      entity:Optional[str] = None , 
                      cost_center: Optional[str] = None,
                      profit_center: Optional[str] =None,
                      mom_vs_budget: Optional[BudgetFilter] = None,
                      customer :Optional[str]=None,
                      region:Optional[str]=None,
                      product_name  :Optional[str] = None,
                      comparision: Optional[ComparityFilter] = None,
                      date_from: Optional[str] = Query(default=None),
                      date_to: Optional[str] = Query(default=None)):

    all_filters_data =  await all_filters_data_display(db=db)

    filters = ["period"] 

    c_m = date.today().month 
    target_period = f"{year}-{c_m:02d}"
    dt = datetime.strptime(target_period, "%Y-%m")
    last_month = (dt - relativedelta(months=1)).strftime("%Y-%m") 
    l_m = datetime.strptime(last_month , "%Y-%m") 

    required_years = [year-1 , year]
    params = {"years": required_years}

    active_filters = []

    if entity:
        filters.append("entity_id")
        active_filters.append("entity")
    if cost_center:
        filters.append("cost_center_id")
        active_filters.append("cost_center")
    if profit_center:
        filters.append("profit_center_id")
        active_filters.append("profit_center")
    if customer:
        filters.append("customer_id")
        active_filters.append("customer")
    if product_name:
        filters.append("product_id")
        active_filters.append("product")
    if region:
        filters.append("region")
        active_filters.append("region")

    budget_filter_col_map = {
        "entity":        "entity_id",
        "cost_center":   "cost_center_id",
        "profit_center": "profit_center_id",
        "customer":      "customer_id",
    }
    budget_filters = ["period"] + [
        budget_filter_col_map[f]
        for f in active_filters
        if f in budget_filter_col_map
    ]

    metric_visibility = {
        "revenue":            {"customer": True,  "entity": True,  "region": True,  "profit_center": True,  "cost_center": False, "product": True},
        "cost_of_goods_sold": {"customer": True,  "entity": True,  "region": True,  "profit_center": True,  "cost_center": False, "product": True},
        "operating_expense":  {"customer": False, "entity": True,  "region": None,  "profit_center": True,  "cost_center": True,  "product": False},
        "gross_profit":       {"customer": True,  "entity": True,  "region": True,  "profit_center": True,  "cost_center": False, "product": True},
        "actual_profit":      {"customer": True,  "entity": True,  "region": True,  "profit_center": True,  "cost_center": None,  "product": True},
        "budget_profit":      {"customer": False, "entity": True,  "region": None,  "profit_center": True,  "cost_center": None,  "product": None},
        "variance":           {"customer": True,  "entity": True,  "region": True,  "profit_center": True,  "cost_center": None,  "product": True},
        "variance_percentage":{"customer": True,  "entity": True,  "region": True,  "profit_center": True,  "cost_center": None,  "product": True},
    }

    def is_metric_visible(metric_name):
        if not active_filters:
            return True
        visibilities = [metric_visibility.get(metric_name, {}).get(f) for f in active_filters]
        if False in visibilities:
            return False
        if all(v is True for v in visibilities):
            return True
        return None

    def normalise_id(v):
        if v is None:
            return None
        if isinstance(v, float) and pd.isna(v):
            return None
        s = str(v).strip()
        if s.lower() in ("none", "nan", "nat", ""):
            return None
        if s.endswith(".0"):
            s = s[:-2]
        return s

    query = text("""SELECT
    gl.gl_account_id,
    gl.period,
    gl.entity_id,
    gl.amount_credit,
    gl.amount_debit,
    gl.cost_center_id,
    gl.profit_center_id,
    REGEXP_REPLACE(gl.customer_id::TEXT, '\\.0$', '') AS customer_id,
    coa.account_name,
    coa.account_type,
    cp.product_id,
    ent.region,
    fc.customer_name
FROM fpna_landing.finance_general_ledger_transactions gl
LEFT JOIN fpna_landing.finance_chart_of_accounts coa
    ON gl.gl_account_id = coa.account_id
LEFT JOIN (
    SELECT profit_center_id, MIN(product_id) AS product_id
    FROM fpna_landing.crm_products
    GROUP BY profit_center_id
) cp ON gl.profit_center_id::TEXT = cp.profit_center_id::TEXT
LEFT JOIN fpna_landing.finance_entities ent
    ON gl.entity_id::TEXT = ent.entity_id::TEXT
LEFT JOIN fpna_landing.finance_customers fc
    ON REGEXP_REPLACE(gl.customer_id::TEXT, '\\.0$', '')
     = REGEXP_REPLACE(fc.customer_id::TEXT, '\\.0$', '')
WHERE coa.account_type IN ('Revenue', 'Expense')
AND EXTRACT(YEAR FROM TO_DATE(gl.period, 'YYYY-MM')) = ANY(:years);""")

    budget_query = text("""SELECT
    b.gl_account_id,
    b.period,
    b.gl_account_name,
    b."Type",
    b.entity_id,
    b.cost_center_id,
    b.profit_center_id,
    REGEXP_REPLACE(b.customer_id::TEXT, '\\.0$', '') AS customer_id,
    b.amount as budget_amount
FROM fpna_landing.finance_budget_data b
WHERE b."Type" IN ('Revenue','Expense')
AND EXTRACT(YEAR FROM TO_DATE(b.period, 'YYYY-MM')) = ANY(:years);""")

    budget_res = await db.execute(budget_query, params)
    budget_rows = budget_res.fetchall()
    budget_data = [dict(row._mapping) for row in budget_rows]
    budget_df = pd.DataFrame(budget_data)

    result = await db.execute(query, params)
    rows = result.fetchall()
    all_period_data = [dict(row._mapping) for row in rows]
    data = all_period_data

    df = pd.DataFrame(data)

    if len(df) == 0:
        return {"message" : "There is no data"}

    cogs_accounts =['inventory', 'Professional Services',
        'IT & Software', 'Depreciation Expense']

    opex_accounts = [
        'Marketing', 'Salaries', 'Rent', 'Travel',
        'Employee Benefits', 'Utilities', 'Payroll Taxes' , 'Insurance' , 'Other Operating Expense']

    df = df.copy()

    id_cols_to_normalise = ["entity_id", "cost_center_id", "profit_center_id", "customer_id", "product_id"]
    for col in id_cols_to_normalise:
        if col in df.columns:
            df[col] = df[col].apply(normalise_id)
        if col in budget_df.columns:
            budget_df[col] = budget_df[col].apply(normalise_id)

    if "region" in df.columns:
        df["region"] = df["region"].apply(
            lambda v: None if (v is None or (isinstance(v, float) and pd.isna(v))
                               or str(v).strip().lower() in ("none", "nan", "nat", "")) else str(v).strip()
        )

    if entity:
        df = df[df["entity_id"] == normalise_id(entity)]
        budget_df = budget_df[budget_df["entity_id"] == normalise_id(entity)]
    if cost_center:
        df = df[df["cost_center_id"] == normalise_id(cost_center)]
        budget_df = budget_df[budget_df["cost_center_id"] == normalise_id(cost_center)]
    if profit_center:
        df = df[df["profit_center_id"] == normalise_id(profit_center)]
        budget_df = budget_df[budget_df["profit_center_id"] == normalise_id(profit_center)]
    if customer:
        customer_val = str(customer).strip()
        if customer_val.isdigit():
            df = df[df["customer_id"] == normalise_id(customer_val)]
            budget_df = budget_df[budget_df["customer_id"] == normalise_id(customer_val)]
        else:
            df = df[df["customer_name"].str.strip().str.lower() == customer_val.lower()]
            matched_ids = df["customer_id"].dropna().unique().tolist()
            budget_df = budget_df[budget_df["customer_id"].isin(matched_ids)]
    if product_name:
        df = df[df["product_id"] == normalise_id(product_name)]
    if region:
        df = df[df["region"].str.strip().str.lower() == str(region).strip().lower()]

    if len(df) == 0:
        return {"message" : "No data for selected filters"}

    actual_grouped = (
        df.groupby(filters, as_index=False)
        .apply(lambda x: pd.Series({
            'revenue': (
                (x['account_type'] == 'Revenue') * (x['amount_credit'] - x['amount_debit'])
            ).sum(),
            'cost_of_goods_sold': (
                x['account_name'].isin(cogs_accounts) * (x['amount_debit'] - x['amount_credit'])
            ).sum(),
            'opex': (
                x['account_name'].isin(opex_accounts) * (x['amount_debit'] - x['amount_credit'])
            ).sum()
        }))
        .reset_index(drop=True)
    )

    if len(budget_df) > 0:
        valid_budget_filters = [f for f in budget_filters if f in budget_df.columns]
        budget_grouped = (
            budget_df.groupby(valid_budget_filters, as_index=False)
            .apply(lambda x: pd.Series({
                'budget_revenue': ((x['Type'] == 'Revenue') * x['budget_amount']).sum(),
                'budget_cogs':    (x['gl_account_name'].isin(cogs_accounts) * x['budget_amount']).sum(),
                'budget_opex':    (x['gl_account_name'].isin(opex_accounts) * x['budget_amount']).sum()
            }))
            .reset_index(drop=True)
        )
        merge_keys = [f for f in filters if f in valid_budget_filters]
        for mk in merge_keys:
            if mk in budget_grouped.columns:
                budget_grouped[mk] = budget_grouped[mk].apply(normalise_id)
            if mk in actual_grouped.columns:
                actual_grouped[mk] = actual_grouped[mk].apply(normalise_id)

        result = actual_grouped.merge(budget_grouped, on=merge_keys, how="left")
        result[['budget_revenue', 'budget_cogs', 'budget_opex']] = (
            result[['budget_revenue', 'budget_cogs', 'budget_opex']].fillna(0)
        )
    else:
        result = actual_grouped.copy()
        result['budget_revenue'] = 0
        result['budget_cogs']    = 0
        result['budget_opex']    = 0

    result['actual_profit'] = (
        result['revenue']
        - result['cost_of_goods_sold']
        - result['opex']
    )
    result["gross_profit"] = result["revenue"] - result["cost_of_goods_sold"]

    result['budget_costs'] = (
        result['budget_cogs']
        + result['budget_opex']
    )

    result['budget_profit'] = (
        result['budget_revenue']
        - result['budget_costs']
    )
    result['variance'] = (
        result['actual_profit']
        - result['budget_profit']
    )
    result['variance_percentage'] = np.round(
        np.where(
            result['budget_profit'] != 0,
            (result['variance'] * 100) / result['budget_profit'],
            np.nan), 2)

    result.fillna(0, inplace=True)

    result["period"] = pd.to_datetime(result["period"])
    result["comparision"] = [{} for _ in range(len(result))]

    result = result.replace({np.nan: 0, np.inf: 0, -np.inf: 0})

    f_result = result[result["period"].dt.year == year].copy()
    f_result["period"] = pd.to_datetime(f_result["period"]).dt.strftime("%Y-%m")

    print(f_result , "")
    if len(f_result) == 0:
        return {"message" : "There is no data in selected year"}

    all_periods = f_result.to_dict(orient = "records")

    result["period"] = pd.to_datetime(result["period"], format="%Y-%m")
    c_month = date.today().month
    target_period = f"{year}-{c_month:02d}"

    current_month_df = result[result["period"] == pd.to_datetime(target_period)].copy()
    current_month_df["period"] = current_month_df["period"].dt.strftime("%Y-%m")
    current_month_res = current_month_df.to_dict(orient="records")

    dt = datetime.strptime(target_period, "%Y-%m")
    last_month_date = (dt - relativedelta(months=1)).strftime("%Y-%m")

    last_month_df = result[result["period"].dt.to_period("M") == last_month_date].copy()
    last_month_df["period"] = last_month_df["period"].dt.strftime("%Y-%m")
    last_month_res = last_month_df.to_dict(orient = "records")

    quarter_start_month = ((c_month - 1) // 3) * 3 + 1
    qtd_periods = [f"{year}-{m:02d}" for m in range(quarter_start_month, c_month + 1)]
    qtd_periods = pd.to_datetime(qtd_periods)
    qtd_df = result[result["period"].isin(qtd_periods)].copy()
    qtd_df["period"] = qtd_df["period"].dt.strftime("%Y-%m")
    qtd_res = qtd_df.to_dict(orient = "records")

    ytd_df = result[
        (result["period"].dt.year == year) &
        (result["period"].dt.month <= c_month)].copy()
    ytd_df["period"] = ytd_df["period"].dt.strftime("%Y-%m")
    ytd_res = ytd_df.to_dict(orient = "records")

    if date_from or date_to:
        try:
            custom_range_df = result.copy()
            if date_from:
                custom_from = pd.to_datetime(date_from, format="%Y-%m")
                custom_range_df = custom_range_df[custom_range_df["period"] >= custom_from]
            if date_to:
                custom_to = pd.to_datetime(date_to, format="%Y-%m")
                custom_range_df = custom_range_df[custom_range_df["period"] <= custom_to]
            custom_range_df["period"] = custom_range_df["period"].dt.strftime("%Y-%m")
            custom_range_res = custom_range_df.to_dict(orient="records")
        except Exception:
            custom_range_res = []
    else:
        custom_range_res = []

    time_filter_map = {
        TimeFilterEnum.all_periods: all_periods,
        TimeFilterEnum.current_month: current_month_res,
        TimeFilterEnum.last_month: last_month_res,
        TimeFilterEnum.quarter_to_date: qtd_res,
        TimeFilterEnum.year_to_date: ytd_res,
    }

    if date_from or date_to:
        base_result = custom_range_res
    else:
        base_result = time_filter_map.get(time_filter, all_periods)

    print(base_result , "base result===================================")

    res_df = pd.DataFrame(base_result)
    if len(res_df) == 0:
        return {"message" : "No data"}

    res_df = res_df.rename(columns = {"opex" : "operating_expense"})

    metric_cols = ["revenue", "cost_of_goods_sold", "gross_profit",
                   "operating_expense", "actual_profit", "budget_profit",
                   "variance", "variance_percentage"]
    id_cols_in_df = [f for f in filters if f != "period" and f in res_df.columns]
    desired_cols = ["period"] + id_cols_in_df + [c for c in metric_cols if c in res_df.columns]
    res_df = res_df[desired_cols]

    print(res_df , "kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk")

    for metric_col in metric_cols:
        if metric_col in res_df.columns and is_metric_visible(metric_col) is False:
            res_df[metric_col] = None

    cols_to_separate = ["budget_revenue","budget_cogs","budget_opex","budget_costs"]
    existing_cols = [c for c in cols_to_separate if c in res_df.columns]
    separated_budget_df = res_df[["period"] + id_cols_in_df + existing_cols].copy() if existing_cols else pd.DataFrame()
    remaining_df = res_df.drop(columns=existing_cols, errors="ignore")

    if mom_vs_budget is not None:
        remaining_df = remaining_df.reset_index(drop=True)
        separated_budget_df = separated_budget_df.reset_index(drop=True)

        n_rows = len(remaining_df)
        budget_dict_data = {}

        for metric_col, budget_col, variance_col in [
            ("revenue",            "budget_revenue", "revenue_variance"),
            ("cost_of_goods_sold", "budget_cogs",    "cogs_variance"),
            ("operating_expense",  "budget_opex",    "opex_variance"),
        ]:
            vis = is_metric_visible(metric_col)
            if vis is not False and budget_col in separated_budget_df.columns and metric_col in remaining_df.columns:
                budget_dict_data[budget_col]   = separated_budget_df[budget_col].reset_index(drop=True)
                budget_dict_data[variance_col] = (remaining_df[metric_col].reset_index(drop=True) - separated_budget_df[budget_col].reset_index(drop=True))

        if budget_dict_data:
            budget_df_final = pd.DataFrame(budget_dict_data, index=range(n_rows))
            budget_df_final = budget_df_final.replace({np.nan: None})
            remaining_df["budget_data"] = budget_df_final.to_dict(orient="records")
        else:
            remaining_df["budget_data"] = [{} for _ in range(n_rows)]
    else:
        remaining_df["budget_data"] = [{} for _ in range(len(remaining_df))]

    result_m = remaining_df.to_dict(orient="records")

    prev_df = pd.DataFrame()

    if len(res_df) == 0:
        return {"message" : "No data"}

    if comparision != None:
        res_df["period"] = pd.to_datetime(res_df["period"], errors="coerce")

        if comparision == "year_on_year":
            prev_df["prev_period"] = (res_df["period"] - pd.DateOffset(years=1)).dt.strftime("%Y-%m")
        elif comparision == "month_on_month":
            prev_df["prev_period"] = (res_df["period"] - pd.DateOffset(months=1)).dt.strftime("%Y-%m")

        prev_df["prev_period"] = prev_df["prev_period"].astype(str)

        compare_cols = ["revenue", "cost_of_goods_sold", "operating_expense","gross_profit",
                        "actual_profit" , "budget_profit"]

        visible_compare_cols = [
            col for col in compare_cols
            if is_metric_visible(col) is not False
        ]

        result["period"] = pd.to_datetime(result["period"]).dt.strftime("%Y-%m")
        result_for_compare = result.rename(columns={"opex": "operating_expense"})

        safe_compare_cols = [col for col in visible_compare_cols if col in result_for_compare.columns]

        merged = prev_df.merge(
            result_for_compare[["period"] + safe_compare_cols],
            left_on="prev_period",
            right_on="period",
            how="left")

        merged[safe_compare_cols] = merged[safe_compare_cols].fillna(0)

        final_merged = merged.drop(columns=["period"])

        final_merged['prev_period'] = final_merged['prev_period'].astype('object').where(final_merged['prev_period'].notna(), float('nan'))
        final_merged = final_merged.fillna(0)
        final_merged = final_merged.rename(columns={col: f"prev_{col}" for col in safe_compare_cols})

        final_merged_dict = final_merged.to_dict(orient = "records")
        res_df["period"] = pd.to_datetime(res_df["period"]).dt.strftime("%Y-%m")
        res_df["comparision"] = final_merged_dict

        f_r = res_df.to_dict(orient = "records")

        diff_fields = set()
        for field in safe_compare_cols:
            diff_fields.add(f"{field}_diff")

        for row in f_r:
            current = row
            prev = row.get("comparision", {})
            for field in safe_compare_cols:
                curr_val = current.get(field, 0) or 0
                prev_val = prev.get(f"prev_{field}", 0) or 0
                prev[f"{field}_diff"] = round(curr_val - prev_val, 2)

        comparison_money_fields = set()
        for field in safe_compare_cols:
            comparison_money_fields.add(f"prev_{field}")
            comparison_money_fields.add(f"{field}_diff")

        for row in f_r:
            if "comparision" in row and isinstance(row["comparision"], dict):
                comp = row["comparision"]
                comp_as_list = [comp]
                converted = apply_smart_currency(comp_as_list, currency, currency_filter, comparison_money_fields)
                if converted and len(converted) > 0:
                    row["comparision"] = converted[0]

    else:
        f_r = result_m

    filtered_data_for_metrics = base_result

    filtered_actual_profits = [item["actual_profit"] for item in filtered_data_for_metrics if item.get("actual_profit") is not None]
    filtered_budget_profits = [item["budget_profit"] for item in filtered_data_for_metrics if item.get("budget_profit") is not None]
    filtered_variances = [item["variance"] for item in filtered_data_for_metrics if item.get("variance") is not None]

    filtered_total_actual_profit = sum(filtered_actual_profits)
    filtered_total_budget_profit = sum(filtered_budget_profits)
    filtered_overall_variance = sum(filtered_variances)

    filtered_overall_variance_percentage = ((filtered_total_actual_profit - filtered_total_budget_profit) / filtered_total_budget_profit) * 100 if filtered_total_budget_profit != 0 else 0

    filtered_valid_items = [item for item in filtered_data_for_metrics if item.get("variance_percentage") is not None and item.get("variance_percentage") != 0]

    if filtered_valid_items:
        best_month = max(filtered_valid_items, key=lambda x: x["variance_percentage"])["period"]
        worst_month = min(filtered_valid_items, key=lambda x: x["variance_percentage"])["period"]
    else:
        best_month = None
        worst_month = None

    keymetrics = {
        "total_actual_profit": serialize(filtered_total_actual_profit),
        "total_budget_profit": serialize(filtered_total_budget_profit),
        "overall_variance": serialize(filtered_overall_variance),
        "overall_variance_percentage": serialize(filtered_overall_variance_percentage),
        "best_month": best_month,
        "worst_month": worst_month,
        "aiDriver": ["AI-powered insights coming soon","AI enhances efficiency through automated financial analysis"]
    }
    m_f = ["total_actual_profit" , "total_budget_profit","overall_variance_percentage","overall_variance"]
    metrics = apply_smart_currency(keymetrics , currency , currency_filter ,m_f)

    money_fields = {
        "revenue", "cost_of_goods_sold", "operating_expense","gross_profit",
        "budget_revenue", "budget_cogs", "budget_opex","prev_revenue","prev_cost_of_goods_sold",
        "prev_operating_expense","prev_gross_profit","prev_actual_profit","prev_budget_profit",
        "actual_profit", "budget_costs", "budget_profit","variance","Total_Revenue", "Total_Cost", "Total_Profit",
        "revenue_diff", "cost_of_goods_sold_diff", "operating_expense_diff", "gross_profit_diff",
        "actual_profit_diff", "budget_profit_diff"}

    final_response = apply_smart_currency(f_r, currency , currency_filter ,money_fields)

    if not final_response:
        final_response = {"No data available for selected filters"}
    else:
        try:
            final_response.append({"keymetrics":metrics})
            final_response.append({"entities" : all_filters_data["entities"]})
            final_response.append({"costcenters" : all_filters_data["cost_centers"]})
            final_response.append({"profitcenters" : all_filters_data["profit_centers"]})
            final_response.append({"customers" : all_filters_data["customers"]})
            final_response.append({"products" : all_filters_data["products"]})
            final_response.append({"versions" : all_filters_data["versions"]})
        except:
            final_response["keymetrics"] = metrics
            final_response["entities"] = all_filters_data["entities"]
            final_response["costcenters"] = all_filters_data["cost_centers"]
            final_response["profit_centers"] = all_filters_data["profit_centers"]
            final_response["customers"] = all_filters_data["customers"]
            final_response["products"] = all_filters_data["products"]
            final_response["versions"] = all_filters_data["versions"]

    return final_response



@financial_router1.get("/profitandloss_statement_viewmore/{year}/{currency}", dependencies=[Depends(get_current_company_user)])
async def pnl_report_viewmore(year: int, currency: str, db: AsyncSession = Depends(get_db),
                               currency_filter: CurrencyFilter = Query(default=CurrencyFilter.actual_value),
                               entity: Optional[str] = None,
                               department: Optional[str] = None):

    required_years = [year]
    params = {"years": required_years}
    filters_applied = ["period", "account_id", "account_name"]
    all_filters_data = await all_filters_data_display(db=db)

    if entity:
        filters_applied.append("entity_id")
    if department:
        filters_applied.append("department_id")

    query = text("""select * from fpna_landing.finance_general_ledger_transactions WHERE EXTRACT(YEAR FROM TO_DATE(period, 'YYYY-MM')) = ANY(:years)""")
    result = await db.execute(query, params)
    import pandas as pd
    rows = result.fetchall()
    gl_transaction_data = [dict(row._mapping) for row in rows]
    gl_df = pd.DataFrame(gl_transaction_data)
    gl_df = gl_df.rename(columns={"gl_account_id": "account_id"})

    coa_query = text("""Select account_id, account_name, account_type from fpna_landing.finance_chart_of_accounts""")
    coa_results = await db.execute(coa_query)
    coa_rows = coa_results.fetchall()
    coa_data = [dict(row._mapping) for row in coa_rows]
    coa_df = pd.DataFrame(coa_data)

    entity_query = text("""Select entity_id, entity_name from fpna_landing.finance_entities""")
    entity_results = await db.execute(entity_query)
    entity_rows = entity_results.fetchall()
    entity_data = [dict(row._mapping) for row in entity_rows]
    entity_df = pd.DataFrame(entity_data)
    entity_df["entity_id"] = entity_df["entity_id"].astype("int64")

    department_query = text("""Select department_id, department_name from fpna_landing.finance_departments""")
    department_results = await db.execute(department_query)
    department_rows = department_results.fetchall()
    dep_data = [dict(row._mapping) for row in department_rows]
    dep_df = pd.DataFrame(dep_data)

    df = gl_df.merge(coa_df, on="account_id", how="left") \
              .merge(entity_df, on="entity_id", how="left") \
              .merge(dep_df, on="department_id", how="left")

    requ_df = df.copy()

    df_revenue = (df[df["account_type"] == "Revenue"]
                  .assign(revenue_value=lambda x: x.amount_credit - x.amount_debit)
                  .groupby(filters_applied, as_index=False)
                  .agg(total_revenue=("revenue_value", "sum")))

    Total_revenue = df_revenue.to_dict(orient="records")

    t_revenuedf = pd.DataFrame(Total_revenue)
    t_revenuedf["period"] = pd.to_datetime(t_revenuedf["period"], format="%Y-%m")
    revenuedf = t_revenuedf[t_revenuedf["period"].dt.year == year]
    revenuedf["period"] = revenuedf["period"].dt.strftime("%Y-%m")

    ai_driver = ["AI-powered insights coming soon",
                 "AI enhances efficiency through automated financial analysis"]

    revenue_result = {}
    for account_name, g in revenuedf.groupby("account_name", sort=False):
        revenue_result[account_name] = {
            "data": [],
            "ai_driver": ai_driver
        }
        revenue_result[account_name]["comparision"] = {}
        for _, row in g.iterrows():
            item = {
                "period": row["period"],
                "total_revenue": round(float(row["total_revenue"]), 2)
            }
            if entity and "entity_id" in row and pd.notna(row["entity_id"]):
                item["entity_id"] = row["entity_id"]
            if department and "department_id" in row and pd.notna(row["department_id"]):
                item["department_id"] = row["department_id"]
            revenue_result[account_name]["data"].append(item)

    cogs_accounts = ['inventory', 'Professional Services', 'IT & Software', 'Depreciation Expense']
    opex_accounts = ['Marketing', 'Salaries', 'Rent', 'Travel',
                     'Employee Benefits', 'Utilities', 'Payroll Taxes', 'Insurance', 'Other Operating Expense']

    df_cogs = (df[(df["account_type"] == "Expense") & (df["account_name"].isin(cogs_accounts))]
               .assign(cogs_value=lambda x: x.amount_debit - x.amount_credit)
               .groupby(filters_applied, as_index=False)
               .agg(total_cogs=("cogs_value", "sum")))

    df_expenses = (df[(df["account_type"] == "Expense") & (df["account_name"].isin(opex_accounts))]
                   .assign(expense_value=lambda x: x.amount_debit - x.amount_credit)
                   .groupby(filters_applied, as_index=False)
                   .agg(total_expenses=("expense_value", "sum")))

    Total_cogs = df_cogs.to_dict(orient="records")
    cogsdf = pd.DataFrame(Total_cogs)
    cogsdf["period"] = pd.to_datetime(cogsdf["period"]).dt.to_period("M").astype(str)

    cogs_result = {}
    for account_name, g in cogsdf.groupby("account_name", sort=False):
        cogs_result[account_name] = {
            "data": [],
            "ai_driver": ai_driver,
            "comparision": {"_": None}
        }
        for _, row in g.iterrows():
            item = {
                "period": row["period"],
                "total_cogs": round(float(row["total_cogs"]), 2)
            }
            if entity and "entity_id" in row and pd.notna(row["entity_id"]):
                item["entity_id"] = int(row["entity_id"])
            if department and "department_id" in row and pd.notna(row["department_id"]):
                item["department_id"] = row["department_id"]
            cogs_result[account_name]["data"].append(item)

    Total_expenses = df_expenses.to_dict(orient="records")
    expensedf = pd.DataFrame(Total_expenses)
    expensedf["period"] = pd.to_datetime(expensedf["period"]).dt.to_period("M").astype(str)

    expense_result = {}
    for account_name, g in expensedf.groupby("account_name", sort=False):
        expense_result[account_name] = {
            "data": [],
            "ai_driver": ai_driver,
            "comparision": {"_": None}
        }
        for _, row in g.iterrows():
            item = {
                "period": row["period"],
                "total_expenses": round(float(row["total_expenses"]), 2)
            }
            if entity and "entity_id" in row and pd.notna(row["entity_id"]):
                item["entity_id"] = int(row["entity_id"])
            if department and "department_id" in row and pd.notna(row["department_id"]):
                item["department_id"] = row["department_id"]
            expense_result[account_name]["data"].append(item)

    rev_period = (df_revenue.groupby("period", as_index=False)
                  .agg(total_revenue=("total_revenue", "sum")))

    exp_period = (df_expenses.groupby("period", as_index=False)
                  .agg(total_expenses=("total_expenses", "sum")))

    cogs_period = (df_cogs.groupby("period", as_index=False)
                   .agg(total_cogs=("total_cogs", "sum")))

    profit_df = rev_period.merge(exp_period, on="period", how="outer") \
                          .merge(cogs_period, on="period", how="outer")

    profit_df["Gross_Profit"] = profit_df["total_revenue"] - profit_df["total_cogs"]
    profit_df["Net_Profit"] = profit_df["Gross_Profit"] - profit_df["total_expenses"]

    revenue_result["grand_total_revenue"] = profit_df.set_index("period")["total_revenue"].apply(lambda x: round(float(x), 2)).to_dict()
    expense_result["grand_total_expenses"] = profit_df.set_index("period")["total_expenses"].apply(lambda x: round(float(x), 2)).to_dict()
    cogs_result["grand_total_cogs"] = profit_df.set_index("period")["total_cogs"].apply(lambda x: round(float(x), 2)).to_dict()

    profit = profit_df.to_dict(orient="records")

    m_profit_df = profit_df.drop(columns=["total_revenue", "total_expenses", "total_cogs"])
    profit_m = m_profit_df.to_dict(orient="records")
    for item in profit_m:
        if item.get("Gross_Profit") is not None:
            item["Gross_Profit"] = round(float(item["Gross_Profit"]), 2)
        if item.get("Net_Profit") is not None:
            item["Net_Profit"] = round(float(item["Net_Profit"]), 2)

    metrics = {
        "TotalRevenue": round(float(sum(df_revenue["total_revenue"])), 2),
        "GrossProfit": round(float(sum(item["Gross_Profit"] for item in profit)), 2),
        "NetProfit": round(float(sum(item["Net_Profit"] for item in profit_m)), 2)
    }

    df2 = pd.DataFrame(df_revenue)
    df2["period"] = pd.to_datetime(df2["period"])
    df2["quarter"] = df2["period"].dt.to_period("Q").astype(str)
    numeric_cols = ["total_revenue"]

    f = ["quarter", "account_name"]
    if entity:
        f.append("entity_id")
    if department:
        f.append("department_id")

    quarterly_df = df2.groupby(f, as_index=False)[numeric_cols].sum()
    quarterly_total_revenue = quarterly_df.to_dict(orient="records")

    qtr_df = pd.DataFrame(quarterly_total_revenue)
    qtr_result = {}
    for account_name, g in qtr_df.groupby("account_name", sort=False):
        qtr_result[account_name] = {
            "data": [],
            "ai_driver": ai_driver,
            "comparision": {"_": None}
        }
        for _, row in g.iterrows():
            item = {
                "quarter": row["quarter"],
                "total_revenue": round(float(row["total_revenue"]), 2)
            }
            if entity and "entity_id" in row:
                item["entity_id"] = int(row["entity_id"])
            if department and "department_id" in row:
                item["department_id"] = row["department_id"]
            qtr_result[account_name]["data"].append(item)

    df_qte = pd.DataFrame(df_expenses)
    df_qte["period"] = pd.to_datetime(df_qte["period"])
    df_qte["quarter"] = df_qte["period"].dt.to_period("Q").astype(str)
    numeric_cols_qte = df_qte.select_dtypes(include="number").columns
    quarterly_qte = df_qte.groupby(f, as_index=False)[numeric_cols_qte].sum()
    quarterly_total_expenses = quarterly_qte.to_dict(orient="records")

    qedf = pd.DataFrame(quarterly_total_expenses)
    qex_result = {}
    for account_name, g in qedf.groupby("account_name", sort=False):
        qex_result[account_name] = {
            "data": [],
            "ai_driver": ai_driver,
            "comparision": {"_": None}
        }
        for _, row in g.iterrows():
            item = {
                "quarter": row["quarter"],
                "total_expenses": round(float(row["total_expenses"]), 2)
            }
            if entity and "entity_id" in row and pd.notna(row["entity_id"]):
                item["entity_id"] = row["entity_id"]
            if department and "department_id" in row and pd.notna(row["department_id"]):
                item["department_id"] = row["department_id"]
            qex_result[account_name]["data"].append(item)

    df_qtc = pd.DataFrame(df_cogs)
    df_qtc["period"] = pd.to_datetime(df_qtc["period"])
    df_qtc["quarter"] = df_qtc["period"].dt.to_period("Q").astype(str)
    numeric_cols_qtc = df_qtc.select_dtypes(include="number").columns
    quarterly_qtc = df_qtc.groupby(f, as_index=False)[numeric_cols_qtc].sum()
    quarterly_total_cogs = quarterly_qtc.to_dict(orient="records")

    qtcdf = pd.DataFrame(quarterly_total_cogs)
    qcogs_result = {}
    for account_name, g in qtcdf.groupby("account_name", sort=False):
        qcogs_result[account_name] = {
            "data": [],
            "ai_driver": ai_driver,
            "comparision": {"_": None}
        }
        for _, row in g.iterrows():
            item = {
                "quarter": row["quarter"],
                "total_cogs": round(float(row["total_cogs"]), 2)
            }
            if entity and "entity_id" in row and pd.notna(row["entity_id"]):
                item["entity_id"] = row["entity_id"]
            if department and "department_id" in row and pd.notna(row["department_id"]):
                item["department_id"] = row["department_id"]
            qcogs_result[account_name]["data"].append(item)

    quarterly_profit = profit
    df_p = pd.DataFrame(quarterly_profit)
    df_p["period"] = pd.to_datetime(df_p["period"])
    df_p["quarter"] = df_p["period"].dt.to_period("Q").astype(str)
    numeric_cols_p = df_p.select_dtypes(include="number").columns
    quarterly_profit_df = df_p.groupby(["quarter"], as_index=False)[numeric_cols_p].sum()

    qtr_result["grand_total_revenue"] = {
        row["quarter"]: round(float(row["total_revenue"]), 2)
        for _, row in quarterly_profit_df.iterrows()
    }
    qex_result["grand_total_expenses"] = {
        row["quarter"]: round(float(row["total_expenses"]), 2)
        for _, row in quarterly_profit_df.iterrows()
    }
    qcogs_result["grand_total_cogs"] = {
        row["quarter"]: round(float(row["total_cogs"]), 2)
        for _, row in quarterly_profit_df.iterrows()
    }

    quarterly_profit_df = quarterly_profit_df.drop(columns=["total_revenue", "total_expenses", "total_cogs"])
    q_p = quarterly_profit_df.to_dict(orient="records")
    for item in q_p:
        if item.get("Gross_Profit") is not None:
            item["Gross_Profit"] = round(float(item["Gross_Profit"]), 2)
        if item.get("Net_Profit") is not None:
            item["Net_Profit"] = round(float(item["Net_Profit"]), 2)

    ab_filters = ["account_name"]
    if entity:
        ab_filters.append("entity_id")
    if department:
        ab_filters.append("department_id")

    budget_query = text("""select period, gl_account_id, "Type", gl_account_name, entity_id, department_id, amount from fpna_landing.finance_budget_data WHERE EXTRACT(YEAR FROM TO_DATE(period, 'YYYY-MM')) = ANY(:years)""")
    budget_result = await db.execute(budget_query, params)
    budget_rows = budget_result.fetchall()
    budget_data = [dict(row._mapping) for row in budget_rows]
    budget_df = pd.DataFrame(budget_data)
    budget_df = budget_df.rename(columns={"Type": "account_type", "gl_account_name": "account_name", "amount": "budget_amount"})

    budget_revenue_df = (budget_df
                         .loc[budget_df["account_type"] == "Revenue"]
                         .groupby(ab_filters, as_index=False)["budget_amount"].sum()
                         .rename(columns={"budget_amount": "budget_revenue"}))

    budget_cogs_df = (budget_df
                      .loc[budget_df["account_name"].isin(cogs_accounts)]
                      .groupby(ab_filters, as_index=False)["budget_amount"].sum()
                      .rename(columns={"budget_amount": "budget_cogs"}))

    budget_opex_df = (budget_df
                      .loc[budget_df["account_name"].isin(opex_accounts)]
                      .groupby(ab_filters, as_index=False)["budget_amount"].sum()
                      .rename(columns={"budget_amount": "budget_opex"}))

    actual_revenue = (requ_df[requ_df["account_type"] == "Revenue"]
                      .assign(value=lambda d: d["amount_credit"] - d["amount_debit"])
                      .groupby(ab_filters, as_index=False)["value"].sum())

    actual_costs = (requ_df[requ_df["account_name"].isin(cogs_accounts)]
                    .assign(value=lambda d: d["amount_debit"] - d["amount_credit"])
                    .groupby(ab_filters, as_index=False)["value"].sum())

    actual_opex = (requ_df[requ_df["account_name"].isin(opex_accounts)]
                   .assign(value=lambda d: d["amount_debit"] - d["amount_credit"])
                   .groupby(ab_filters, as_index=False)["value"].sum())

    actual_revenue = actual_revenue.rename(columns={"value": "actual_revenue"})
    actual_costs = actual_costs.rename(columns={"value": "actual_costs"})
    actual_opex = actual_opex.rename(columns={"value": "actual_opex"})

    actual_budget_revenue_df = actual_revenue.merge(budget_revenue_df, on=ab_filters, how="left").fillna(0)
    actual_budget_revenue_df["revenue_achieved_pct"] = round((actual_budget_revenue_df["actual_revenue"] / actual_budget_revenue_df["budget_revenue"]) * 100, 2)

    actual_budget_cogs_df = actual_costs.merge(budget_cogs_df, on=ab_filters, how="left").fillna(0)
    actual_budget_cogs_df["cogs_achieved_pct"] = round((actual_budget_cogs_df["actual_costs"] / actual_budget_cogs_df["budget_cogs"]) * 100, 2)

    actual_budget_expense_df = actual_opex.merge(budget_opex_df, on=ab_filters, how="left").fillna(0)
    actual_budget_expense_df["expense_achieved_pct"] = round((actual_budget_expense_df["actual_opex"] / actual_budget_expense_df["budget_opex"]) * 100, 2)

    revenue_totals = {
        "actual_revenue_total": round(float(actual_budget_revenue_df["actual_revenue"].sum()), 2),
        "budget_revenue_total": round(float(actual_budget_revenue_df["budget_revenue"].sum()), 2),
        "achieved_totals": round(float((actual_budget_revenue_df["actual_revenue"].sum() / actual_budget_revenue_df["budget_revenue"].sum()) * 100), 2)
    }

    cogs_totals = {
        "actual_cogs_total": round(float(actual_budget_cogs_df["actual_costs"].sum()), 2),
        "budget_cogs_total": round(float(actual_budget_cogs_df["budget_cogs"].sum()), 2),
        "achieved_totals": round(float((actual_budget_cogs_df["actual_costs"].sum() / actual_budget_cogs_df["budget_cogs"].sum()) * 100), 2)
    }

    expense_totals = {
        "actual_expense_total": round(float(actual_budget_expense_df["actual_opex"].sum()), 2),
        "budget_expense_total": round(float(actual_budget_expense_df["budget_opex"].sum()), 2),
        "achieved_totals": round(float((actual_budget_expense_df["actual_opex"].sum() / actual_budget_expense_df["budget_opex"].sum()) * 100), 2)
    }

    actual_vs_budget = {
        "Revenue": actual_budget_revenue_df.to_dict(orient="records"),
        "cogs": actual_budget_cogs_df.to_dict(orient="records"),
        "expense": actual_budget_expense_df.to_dict(orient="records")
    }
    actual_vs_budget["Revenue"].append({"Total_revenues": revenue_totals})
    actual_vs_budget["cogs"].append({"Total_cogs": cogs_totals})
    actual_vs_budget["expense"].append({"Total_expense": expense_totals})

    month_abbr = {
        "01": "Jan", "02": "Feb", "03": "Mar", "04": "Apr",
        "05": "May", "06": "Jun", "07": "Jul", "08": "Aug",
        "09": "Sep", "10": "Oct", "11": "Nov", "12": "Dec"
    }
    all_months = ["01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"]

    def build_summary(period_value_list, value_key):
        period_map = {}
        for item in period_value_list:
            m = item["period"].split("-")[1]
            raw = item.get(value_key)
            if raw is not None:
                try:
                    period_map[m] = round(float(raw), 2)
                except:
                    pass
        result = []
        for i in range(len(all_months) - 1):
            m1 = all_months[i]
            m2 = all_months[i + 1]
            v1 = period_map.get(m1, None)
            v2 = period_map.get(m2, None)
            variance = round(v2 - v1, 2) if v1 is not None and v2 is not None else None
            result.append({
                "month1": month_abbr[m1], "value1": v1,
                "month2": month_abbr[m2], "value2": v2,
                "variance": variance, "AI Driven": "Coming soon"
            })
        total = round(sum(v for v in period_map.values()), 2)
        result.append({"total": total})
        return result

    def build_grand_total_summary(grand_dict, value_key):
        period_list = [{"period": k, value_key: v} for k, v in grand_dict.items() if k != "total"]
        return build_summary(period_list, value_key)

    def build_gross_profit_summary(period_value_list):
        gross_map = {}
        for item in period_value_list:
            m = item["period"].split("-")[1]
            raw = item.get("Gross_Profit")
            if raw is not None:
                try:
                    gross_map[m] = round(float(raw), 2)
                except:
                    pass
        result = []
        for i in range(len(all_months) - 1):
            m1 = all_months[i]
            m2 = all_months[i + 1]
            v1 = gross_map.get(m1, None)
            v2 = gross_map.get(m2, None)
            variance = round(v2 - v1, 2) if v1 is not None and v2 is not None else None
            result.append({
                "month1": month_abbr[m1], "gross_value1": v1,
                "month2": month_abbr[m2], "gross_value2": v2,
                "gross_variance": variance, "AI Driven": "Coming soon"
            })
        total = round(sum(v for v in gross_map.values()), 2)
        result.append({"total": total})
        return result

    def build_net_profit_summary(period_value_list):
        net_map = {}
        for item in period_value_list:
            m = item["period"].split("-")[1]
            raw = item.get("Net_Profit")
            if raw is not None:
                try:
                    net_map[m] = round(float(raw), 2)
                except:
                    pass
        result = []
        for i in range(len(all_months) - 1):
            m1 = all_months[i]
            m2 = all_months[i + 1]
            v1 = net_map.get(m1, None)
            v2 = net_map.get(m2, None)
            variance = round(v2 - v1, 2) if v1 is not None and v2 is not None else None
            result.append({
                "month1": month_abbr[m1], "net_value1": v1,
                "month2": month_abbr[m2], "net_value2": v2,
                "net_variance": variance, "AI Driven": "Coming soon"
            })
        total = round(sum(v for v in net_map.values()), 2)
        result.append({"total": total})
        return result

    revenue_summary_all = {}
    for acc_name, acc_data in revenue_result.items():
        if acc_name == "grand_total_revenue":
            revenue_summary_all[acc_name] = build_grand_total_summary(revenue_result[acc_name], "total_revenue")
        elif isinstance(acc_data, dict) and "data" in acc_data:
            revenue_summary_all[acc_name] = build_summary(acc_data["data"], "total_revenue")

    expense_summary_all = {}
    for acc_name, acc_data in expense_result.items():
        if acc_name == "grand_total_expenses":
            expense_summary_all[acc_name] = build_grand_total_summary(expense_result[acc_name], "total_expenses")
        elif isinstance(acc_data, dict) and "data" in acc_data:
            expense_summary_all[acc_name] = build_summary(acc_data["data"], "total_expenses")

    cogs_summary_all = {}
    for acc_name, acc_data in cogs_result.items():
        if acc_name == "grand_total_cogs":
            cogs_summary_all[acc_name] = build_grand_total_summary(cogs_result[acc_name], "total_cogs")
        elif isinstance(acc_data, dict) and "data" in acc_data:
            cogs_summary_all[acc_name] = build_summary(acc_data["data"], "total_cogs")

    gross_profit_summary = build_gross_profit_summary(profit_m)
    net_profit_summary = build_net_profit_summary(profit_m)

    def fmt(value):
        if value is None:
            return None
        try:
            value = round(float(value), 2)
        except:
            return value
        if currency == "INR" and abs(value) >= 100_000:
            return f"{round(value / 100_000, 2)} L"
        if currency == "USD":
            if currency_filter == CurrencyFilter.thousands and abs(value) >= 1_000:
                return f"{round(value / 1_000, 2)} K"
            if currency_filter == CurrencyFilter.millions and abs(value) >= 1_000_000:
                return f"{round(value / 1_000_000, 2)} M"
        return round(value, 2)

    def fmt_summary_list(summary_list, value_keys):
        formatted = []
        for item in summary_list:
            new_item = dict(item)
            for k in value_keys:
                if k in new_item:
                    new_item[k] = fmt(new_item[k])
            formatted.append(new_item)
        return formatted

    for acc_name in list(revenue_summary_all.keys()):
        revenue_summary_all[acc_name] = fmt_summary_list(
            revenue_summary_all[acc_name], ["value1", "value2", "variance", "total"]
        )

    for acc_name in list(expense_summary_all.keys()):
        expense_summary_all[acc_name] = fmt_summary_list(
            expense_summary_all[acc_name], ["value1", "value2", "variance", "total"]
        )

    for acc_name in list(cogs_summary_all.keys()):
        cogs_summary_all[acc_name] = fmt_summary_list(
            cogs_summary_all[acc_name], ["value1", "value2", "variance", "total"]
        )

    gross_profit_summary = fmt_summary_list(
        gross_profit_summary, ["gross_value1", "gross_value2", "gross_variance", "total"]
    )
    net_profit_summary = fmt_summary_list(
        net_profit_summary, ["net_value1", "net_value2", "net_variance", "total"]
    )

    summary = {
        "revenue_summary": revenue_summary_all,
        "expense_summary": expense_summary_all,
        "cogs_summary": cogs_summary_all,
        "profit_summary": {
            "gross_profit_summary": gross_profit_summary,
            "net_profit_summary": net_profit_summary
        }
    }

    def format_period_keyed_dict(d):
        return {k: fmt(v) for k, v in d.items()}

    def raw_sum_dict(d):
        return round(sum(float(v) for v in d.values() if v is not None), 2)

    def raw_sum_list(data_list, value_key):
        return round(sum(float(item[value_key]) for item in data_list if item.get(value_key) is not None), 2)

    for acc_name in list(revenue_result.keys()):
        if acc_name == "grand_total_revenue":
            raw_total = raw_sum_dict(revenue_result[acc_name])
            revenue_result[acc_name] = format_period_keyed_dict(revenue_result[acc_name])
            revenue_result[acc_name]["total"] = fmt(raw_total)
        elif isinstance(revenue_result[acc_name], dict) and "data" in revenue_result[acc_name]:
            raw_total = raw_sum_list(revenue_result[acc_name]["data"], "total_revenue")
            for item in revenue_result[acc_name]["data"]:
                item["total_revenue"] = fmt(item["total_revenue"])
            revenue_result[acc_name]["total"] = fmt(raw_total)

    for acc_name in list(expense_result.keys()):
        if acc_name == "grand_total_expenses":
            raw_total = raw_sum_dict(expense_result[acc_name])
            expense_result[acc_name] = format_period_keyed_dict(expense_result[acc_name])
            expense_result[acc_name]["total"] = fmt(raw_total)
        elif isinstance(expense_result[acc_name], dict) and "data" in expense_result[acc_name]:
            raw_total = raw_sum_list(expense_result[acc_name]["data"], "total_expenses")
            for item in expense_result[acc_name]["data"]:
                item["total_expenses"] = fmt(item["total_expenses"])
            expense_result[acc_name]["total"] = fmt(raw_total)

    for acc_name in list(cogs_result.keys()):
        if acc_name == "grand_total_cogs":
            raw_total = raw_sum_dict(cogs_result[acc_name])
            cogs_result[acc_name] = format_period_keyed_dict(cogs_result[acc_name])
            cogs_result[acc_name]["total"] = fmt(raw_total)
        elif isinstance(cogs_result[acc_name], dict) and "data" in cogs_result[acc_name]:
            raw_total = raw_sum_list(cogs_result[acc_name]["data"], "total_cogs")
            for item in cogs_result[acc_name]["data"]:
                item["total_cogs"] = fmt(item["total_cogs"])
            cogs_result[acc_name]["total"] = fmt(raw_total)

    profit_m_total_gross = round(sum(item["Gross_Profit"] for item in profit_m if item.get("Gross_Profit") is not None), 2)
    profit_m_total_net = round(sum(item["Net_Profit"] for item in profit_m if item.get("Net_Profit") is not None), 2)

    for item in profit_m:
        item["Gross_Profit"] = fmt(item.get("Gross_Profit"))
        item["Net_Profit"] = fmt(item.get("Net_Profit"))

    for acc_name in list(qtr_result.keys()):
        if acc_name == "grand_total_revenue":
            raw_total = raw_sum_dict(qtr_result[acc_name])
            qtr_result[acc_name] = format_period_keyed_dict(qtr_result[acc_name])
            qtr_result[acc_name]["total"] = fmt(raw_total)
        elif isinstance(qtr_result[acc_name], dict) and "data" in qtr_result[acc_name]:
            raw_total = raw_sum_list(qtr_result[acc_name]["data"], "total_revenue")
            for item in qtr_result[acc_name]["data"]:
                item["total_revenue"] = fmt(item["total_revenue"])
            qtr_result[acc_name]["total"] = fmt(raw_total)

    for acc_name in list(qex_result.keys()):
        if acc_name == "grand_total_expenses":
            raw_total = raw_sum_dict(qex_result[acc_name])
            qex_result[acc_name] = format_period_keyed_dict(qex_result[acc_name])
            qex_result[acc_name]["total"] = fmt(raw_total)
        elif isinstance(qex_result[acc_name], dict) and "data" in qex_result[acc_name]:
            raw_total = raw_sum_list(qex_result[acc_name]["data"], "total_expenses")
            for item in qex_result[acc_name]["data"]:
                item["total_expenses"] = fmt(item["total_expenses"])
            qex_result[acc_name]["total"] = fmt(raw_total)

    for acc_name in list(qcogs_result.keys()):
        if acc_name == "grand_total_cogs":
            raw_total = raw_sum_dict(qcogs_result[acc_name])
            qcogs_result[acc_name] = format_period_keyed_dict(qcogs_result[acc_name])
            qcogs_result[acc_name]["total"] = fmt(raw_total)
        elif isinstance(qcogs_result[acc_name], dict) and "data" in qcogs_result[acc_name]:
            raw_total = raw_sum_list(qcogs_result[acc_name]["data"], "total_cogs")
            for item in qcogs_result[acc_name]["data"]:
                item["total_cogs"] = fmt(item["total_cogs"])
            qcogs_result[acc_name]["total"] = fmt(raw_total)

    q_p_total_gross = round(sum(item["Gross_Profit"] for item in q_p if item.get("Gross_Profit") is not None), 2)
    q_p_total_net = round(sum(item["Net_Profit"] for item in q_p if item.get("Net_Profit") is not None), 2)

    for item in q_p:
        item["Gross_Profit"] = fmt(item.get("Gross_Profit"))
        item["Net_Profit"] = fmt(item.get("Net_Profit"))

    metrics["TotalRevenue"] = fmt(metrics["TotalRevenue"])
    metrics["GrossProfit"] = fmt(metrics["GrossProfit"])
    metrics["NetProfit"] = fmt(metrics["NetProfit"])

    for row in actual_vs_budget["Revenue"]:
        if "actual_revenue" in row:
            row["actual_revenue"] = fmt(row["actual_revenue"])
        if "budget_revenue" in row:
            row["budget_revenue"] = fmt(row["budget_revenue"])
        if "Total_revenues" in row:
            row["Total_revenues"]["actual_revenue_total"] = fmt(row["Total_revenues"]["actual_revenue_total"])
            row["Total_revenues"]["budget_revenue_total"] = fmt(row["Total_revenues"]["budget_revenue_total"])

    for row in actual_vs_budget["cogs"]:
        if "actual_costs" in row:
            row["actual_costs"] = fmt(row["actual_costs"])
        if "budget_cogs" in row:
            row["budget_cogs"] = fmt(row["budget_cogs"])
        if "Total_cogs" in row:
            row["Total_cogs"]["actual_cogs_total"] = fmt(row["Total_cogs"]["actual_cogs_total"])
            row["Total_cogs"]["budget_cogs_total"] = fmt(row["Total_cogs"]["budget_cogs_total"])

    for row in actual_vs_budget["expense"]:
        if "actual_opex" in row:
            row["actual_opex"] = fmt(row["actual_opex"])
        if "budget_opex" in row:
            row["budget_opex"] = fmt(row["budget_opex"])
        if "Total_expense" in row:
            row["Total_expense"]["actual_expense_total"] = fmt(row["Total_expense"]["actual_expense_total"])
            row["Total_expense"]["budget_expense_total"] = fmt(row["Total_expense"]["budget_expense_total"])

    monthly_view = {
        "Total_Revenue": revenue_result,
        "Total_Expenses": expense_result,
        "Total_cogs": cogs_result,
        "profit": profit_m,
        "profit_total": {
            "total_Gross_Profit": fmt(profit_m_total_gross),
            "total_Net_Profit": fmt(profit_m_total_net)
        }
    }

    quarterly = {
        "Total_revenue": qtr_result,
        "total_expenses": qex_result,
        "total_cogs": qcogs_result,
        "profit_q": q_p,
        "profit_q_total": {
            "total_Gross_Profit": fmt(q_p_total_gross),
            "total_Net_Profit": fmt(q_p_total_net)
        }
    }

    base_result = {
        "monthly_view": monthly_view,
        "quarterly_view": quarterly,
        "actual_vs_budget": actual_vs_budget,
        "keymetrics": metrics,
        "summary": summary
    }

    result = apply_entity_hierarchy_filter(data=base_result,
                                           entity=entity, hierarchy=department, product=None, costcenter=None,
                                           profitcenter=None, region=None, customer=None, version=None)

    if not result:
        final_result = "No data available for selected filters"
    else:
        final_result = result
        final_result["entities"] = all_filters_data["entities"]
        final_result["departments"] = all_filters_data["departments"]

    return final_result

#############################################################################################################
#############################################################################################################
#############################################################################################################
#cashflow statement

@financial_router1.get("/cashflow_statement/{year}/{currency}", dependencies=[Depends(get_current_company_user)])
async def cashflow_report(
    year: int,
    currency: str,
    db: AsyncSession = Depends(get_db),
    currency_filter: CurrencyFilter = Query(default=CurrencyFilter.actual_value),
    time_filter: Optional[TimeFilterEnum] = None,
    entity: Optional[str] = None,
    comparision: Optional[ComparityFilter] = None
):
    required_years = [year - 1, year]
    params = {"years": required_years}

    query = text("""
    WITH base AS (
        SELECT
            gl.period,
            gl.entity_id,
            e.entity_name,
            gl.department_id,
            CASE
                WHEN coa.account_type IN ('Revenue','Other Income')
                THEN gl.amount_credit - gl.amount_debit
                ELSE 0
            END AS operating_income,
            CASE
                WHEN coa.account_type IN (
                    'COGS','Selling Expense','General Expense',
                    'Administrative Expense','Payroll Expense',
                    'Marketing Expense','Depreciation Expense',
                    'Tax Expense','Other Expense','Expense'
                )
                THEN gl.amount_debit - gl.amount_credit
                ELSE 0
            END AS operating_expense,
            CASE
                WHEN coa.account_type = 'Asset'
                THEN gl.amount_debit
                ELSE 0
            END AS investing_cf,
            CASE
                WHEN coa.account_type = 'Liability'
                THEN gl.amount_credit - gl.amount_debit
                ELSE 0
            END AS financing_cf,
            CASE
                WHEN LOWER(coa.account_name) LIKE '%cash%'
                THEN gl.amount_credit - gl.amount_debit
                ELSE 0
            END AS net_cash_change
        FROM fpna_landing.finance_general_ledger_transactions gl
        JOIN fpna_landing.finance_chart_of_accounts coa
            ON gl.gl_account_id = coa.account_id
        JOIN fpna_landing.finance_entities e
            ON gl.entity_id = e.entity_id::BIGINT
        WHERE EXTRACT(YEAR FROM TO_DATE(gl.period,'YYYY-MM')) = ANY(:years)
    )
    SELECT
        period,
        entity_id,
        entity_name,
        department_id,
        SUM(operating_income) - SUM(operating_expense) AS operating_cf,
        SUM(investing_cf) AS investing_cf,
        SUM(financing_cf) AS financing_cf,
        SUM(net_cash_change) AS net_cash_change
    FROM base
    GROUP BY period, entity_id, entity_name, department_id
    ORDER BY period
    """)

    result = await db.execute(query, params)
    rows = result.fetchall()
    columns = result.keys()

    all_filters_data = await all_filters_data_display(db=db)

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

    if entity:
        grouped = df.groupby(["period", "entity_id"]).agg(
            operating_cf=("operating_cf", "sum"),
            investing_cf=("investing_cf", "sum"),
            financing_cf=("financing_cf", "sum"),
            net_cash_change=("net_cash_change", "sum")
        ).reset_index()
    else:
        grouped = df.groupby(["period"]).agg(
            operating_cf=("operating_cf", "sum"),
            investing_cf=("investing_cf", "sum"),
            financing_cf=("financing_cf", "sum"),
            net_cash_change=("net_cash_change", "sum")
        ).reset_index()

    grouped["period"] = pd.to_datetime(grouped["period"], format="%Y-%m")
    grouped["comparision"] = [{} for _ in range(len(grouped))]

    s_y_grouped = grouped[grouped["period"].dt.year == year]
    all_periods = s_y_grouped.to_dict(orient="records")

    c_month = date.today().month
    target_period = f"{year}-{c_month:02d}"

    current_month_df = grouped[grouped["period"] == target_period]
    current_month_res = current_month_df.to_dict(orient="records")

    dt = datetime.strptime(target_period, "%Y-%m")
    last_month_date = (dt - relativedelta(months=1)).strftime("%Y-%m")

    last_month_df = grouped[grouped["period"].dt.to_period("M") == last_month_date]
    last_month_res = last_month_df.to_dict(orient="records")

    quarter_start_month = ((c_month - 1) // 3) * 3 + 1
    qtd_periods = [f"{year}-{m:02d}" for m in range(quarter_start_month, c_month + 1)]
    qtd_df = grouped[grouped["period"].dt.strftime("%Y-%m").isin(qtd_periods)]
    qtd_res = qtd_df.to_dict(orient="records")

    ytd_df = grouped[(grouped["period"].dt.year == year) & (grouped["period"].dt.month <= c_month)]
    ytd_res = ytd_df.to_dict(orient="records")

    time_filter_map = {
        TimeFilterEnum.all_periods: all_periods,
        TimeFilterEnum.current_month: current_month_res,
        TimeFilterEnum.last_month: last_month_res,
        TimeFilterEnum.quarter_to_date: qtd_res,
        TimeFilterEnum.year_to_date: ytd_res
    }

    base_result = time_filter_map.get(time_filter, all_periods)

    result_r = apply_entity_hierarchy_filter(
        data=base_result,
        entity=entity,
        hierarchy=None,
        product=None,
        costcenter=None,
        profitcenter=None,
        region=None,
        customer=None
    )

    res_df = pd.DataFrame(result_r)
    prev_df = pd.DataFrame()

    filters = ["period"]
    if entity:
        filters.append("entity_id")

    if len(res_df) == 0:
        return {"message": "No data"}

    compare_cols = ["operating_cf", "investing_cf", "financing_cf", "net_cash_change"]

    if comparision is not None:
        res_df["period"] = pd.to_datetime(res_df["period"], errors="coerce", format="%Y-%m")

        if comparision == "year_on_year":
            prev_df["prev_period"] = (res_df["period"] - pd.DateOffset(years=1)).dt.strftime("%Y-%m")
        elif comparision == "month_on_month":
            prev_df["prev_period"] = (res_df["period"] - pd.DateOffset(months=1)).dt.strftime("%Y-%m")

        prev_df["prev_period"] = prev_df["prev_period"].astype(str)

        grouped["period"] = grouped["period"].dt.strftime("%Y-%m")
        grouped_dict = grouped.to_dict(orient="records")

        grouped_r = apply_entity_hierarchy_filter(
            grouped_dict,
            entity=entity,
            hierarchy=None,
            product=None,
            costcenter=None,
            profitcenter=None,
            region=None,
            customer=None
        )

        grouped_res = pd.DataFrame(grouped_r)

        merged = prev_df.merge(
            grouped_res[filters + compare_cols],
            left_on="prev_period",
            right_on="period",
            how="left"
        )

        merged[compare_cols] = merged[compare_cols].fillna(0)
        final_merged = merged.drop(columns=["period"]).fillna(0)
        final_merged_dict = final_merged.to_dict(orient="records")

        res_df["period"] = res_df["period"].dt.strftime("%Y-%m")
        res_df["comparision"] = final_merged_dict

        f_r = res_df.to_dict(orient="records")

        for row in f_r:
            current = row
            prev = row.get("comparision", {})

            for field in compare_cols:
                curr_val = float(current.get(field, 0) or 0)
                prev_val = float(prev.get(field, 0) or 0)
                diff_val = round(curr_val - prev_val, 2)
                prev[f"{field}_diff"] = diff_val

            diff_fields = [f"{f}_diff" for f in compare_cols]
            formatted = apply_smart_currency(prev, currency, currency_filter, diff_fields)

            for k in diff_fields:
                prev[k] = formatted[k]

    else:
        f_r = result_r

    metrics_df = pd.DataFrame(result_r)

    operating_total = float(metrics_df["operating_cf"].sum())
    investing_total = float(metrics_df["investing_cf"].sum())
    financing_total = float(metrics_df["financing_cf"].sum())
    netchange_total = float(metrics_df["net_cash_change"].sum())

    r_metrics = {
        "net_cash_flow": netchange_total,
        "operational_total": operating_total,
        "investing_total": investing_total,
        "finalcial_total": financing_total,
        "aiDriver": [
            "AI-powered insights coming soon",
            "AI enhances efficiency through automated financial analysis"
        ]
    }

    m_r = ["operational_total", "investing_total", "finalcial_total", "net_cash_flow"]
    metrics = apply_smart_currency(r_metrics, currency, currency_filter, m_r)

    gt_operating_raw = 0.0
    gt_investing_raw = 0.0
    gt_financing_raw = 0.0
    gt_netchange_raw = 0.0
    gt_prev_operating_raw = 0.0
    gt_prev_investing_raw = 0.0
    gt_prev_financing_raw = 0.0
    gt_prev_netchange_raw = 0.0

    for row in f_r:
        gt_operating_raw += float(row.get("operating_cf", 0) or 0)
        gt_investing_raw += float(row.get("investing_cf", 0) or 0)
        gt_financing_raw += float(row.get("financing_cf", 0) or 0)
        gt_netchange_raw += float(row.get("net_cash_change", 0) or 0)
        if comparision is not None:
            prev = row.get("comparision", {})
            gt_prev_operating_raw += float(prev.get("operating_cf", 0) or 0)
            gt_prev_investing_raw += float(prev.get("investing_cf", 0) or 0)
            gt_prev_financing_raw += float(prev.get("financing_cf", 0) or 0)
            gt_prev_netchange_raw += float(prev.get("net_cash_change", 0) or 0)

    m_f = ["operating_cf", "investing_cf", "financing_cf", "net_cash_change"]
    final_result = apply_smart_currency(f_r, currency, currency_filter, m_f)

    if not final_result:
        final_result = "No data available for selected filters"
    else:
        gt_raw = {
            "operating_cf": gt_operating_raw,
            "investing_cf": gt_investing_raw,
            "financing_cf": gt_financing_raw,
            "net_cash_change": gt_netchange_raw
        }
        gt_converted = apply_smart_currency(gt_raw, currency, currency_filter, ["operating_cf", "investing_cf", "financing_cf", "net_cash_change"])

        gt_comparision = {}
        if comparision is not None:
            gt_prev_raw = {
                "operating_cf": gt_prev_operating_raw,
                "investing_cf": gt_prev_investing_raw,
                "financing_cf": gt_prev_financing_raw,
                "net_cash_change": gt_prev_netchange_raw
            }
            gt_prev_converted = apply_smart_currency(gt_prev_raw, currency, currency_filter, ["operating_cf", "investing_cf", "financing_cf", "net_cash_change"])

            gt_comparision = {
                "operating_cf": gt_prev_converted["operating_cf"],
                "investing_cf": gt_prev_converted["investing_cf"],
                "financing_cf": gt_prev_converted["financing_cf"],
                "net_cash_change": gt_prev_converted["net_cash_change"],
                "operating_cf_diff": round(gt_operating_raw - gt_prev_operating_raw, 2),
                "investing_cf_diff": round(gt_investing_raw - gt_prev_investing_raw, 2),
                "financing_cf_diff": round(gt_financing_raw - gt_prev_financing_raw, 2),
                "net_cash_change_diff": round(gt_netchange_raw - gt_prev_netchange_raw, 2)
            }

            diff_fields = ["operating_cf_diff", "investing_cf_diff", "financing_cf_diff", "net_cash_change_diff"]
            gt_diff_raw = {k: gt_comparision[k] for k in diff_fields}
            gt_diff_converted = apply_smart_currency(gt_diff_raw, currency, currency_filter, diff_fields)
            for k in diff_fields:
                gt_comparision[k] = gt_diff_converted[k]

        grand_total = {
            "period": None,
            "entity_id": None,
            "operating_cf": gt_converted["operating_cf"],
            "investing_cf": gt_converted["investing_cf"],
            "financing_cf": gt_converted["financing_cf"],
            "net_cash_change": gt_converted["net_cash_change"],
            "comparision": gt_comparision
        }

        final_result.append({"grand_total": grand_total})
        final_result.append({"keymetrics": metrics})
        final_result.append({"entities": all_filters_data["entities"]})

    return final_result

#############################################################################################################
#############################################################################################################
#############################################################################################################
#cashflow statement view more API

@financial_router1.get("/cashflow_statement_viewmore/{year}/{currency}" ,dependencies=[Depends(get_current_company_user)])
async def cashflow_report_viewmore(
    year: int,
    currency: str,
    db: AsyncSession = Depends(get_db),
    time_filter: Optional[CashFlowViewMoreTimeFilters] = None,
    currency_filter: CurrencyFilter = Query(default=CurrencyFilter.actual_value),
    view_filter: Optional[CashFlowViewMoreViewFilters] = None,
    entity: Optional[int] = None,
    comparision : Optional[ComparityFilter] = None):
    params = {"years" : [year-1 , year]}
    query = text("""SELECT 
    gl.period,
    gl.entity_id,
    gl.department_id,
    gl.amount_debit,
    gl.amount_credit,
    coa.account_type,
    coa.account_name , 
    e.entity_name
    FROM fpna_landing.finance_general_ledger_transactions gl
    JOIN fpna_landing.finance_chart_of_accounts coa 
    ON gl.gl_account_id = coa.account_id
    JOIN fpna_landing.finance_entities e
    ON gl.entity_id = e.entity_id::bigint
    where  EXTRACT(YEAR FROM TO_DATE(gl.period, 'YYYY-MM')) = ANY(:years)""")

    # params = {
    #     "year_pattern": f"{year}-%",
    #     # "entity": entity
    # }

    result = await db.execute(query, params)
    df = pd.DataFrame(result.fetchall(), columns=result.keys())

    if df.empty:
        return "No data available"

    # -----------------------------
    # STEP 2: Numeric cleanup
    # -----------------------------
    df["amount_credit"] = pd.to_numeric(df["amount_credit"], errors="coerce").fillna(0)
    df["amount_debit"] = pd.to_numeric(df["amount_debit"], errors="coerce").fillna(0)

    # -----------------------------
    # STEP 3: Vectorized Cash Flow Logic
    # -----------------------------
    income_mask = df["account_type"].isin(["Revenue", "Other Income"])
    expense_mask = df["account_type"].isin([
        "COGS", "Selling Expense", "General Expense",
        "Administrative Expense", "Payroll Expense",
        "Marketing Expense", "Depreciation Expense",
        "Tax Expense", "Other Expense", "Expense"
    ])

    df["operating_income_part"] = np.where(
        income_mask,
        df["amount_credit"] - df["amount_debit"],
        0
    )

    df["operating_expense_part"] = np.where(
        expense_mask,
        df["amount_debit"] - df["amount_credit"],
        0
    )

    df["investing_cf_part"] = np.where(
        df["account_type"] == "Asset",
        df["amount_debit"],
        0
    )

    df["financing_cf_part"] = np.where(
        df["account_type"] == "Equity",
        df["amount_credit"] - df["amount_debit"],
        0
    )

    # -----------------------------
    # STEP 4: Monthly Grouping
    # -----------------------------
    group_cols = ["period", "account_name"]
    if entity:
        group_cols.append("entity_id")

    grouped = (
        df.groupby(group_cols, as_index=False)
        .agg(
            operating_income_sum=("operating_income_part", "sum"),
            operating_expense_sum=("operating_expense_part", "sum"),
            investing_cf=("investing_cf_part", "sum"),
            financing_cf=("financing_cf_part", "sum"),
        )
    )

    grouped["operating_cf"] = (
        grouped["operating_income_sum"] - grouped["operating_expense_sum"]
    )

    grouped["net_cash_change"] = (
        grouped["operating_cf"]
        + grouped["investing_cf"]
        + grouped["financing_cf"]
    )

    grouped["period"] = pd.to_datetime(grouped["period"]).dt.to_period("M").astype(str)

    # -----------------------------
    # STEP 5: Monthly Detailed View
    # -----------------------------
    ai_driver = [
        "AI-powered insights coming soon",
        "AI enhances efficiency through automated financial analysis"
    ]

    mvr_result = {}

    for account_name, g in grouped.groupby("account_name", sort=False):

        g = g.sort_values("period").copy()
        g["period"] = pd.to_datetime(g["period"], format="%Y-%m")

        data = []

        for _, row in g.iterrows():
            current_period = row["period"]
            compare_period = None

            comparison_data = {}


            if comparision:

                if comparision == "month_on_month":
                    compare_period = current_period - pd.DateOffset(months=1)

                elif comparision == "year_on_year":
                    compare_period = current_period - pd.DateOffset(years=1)

                comparison_data = {                   
                    "prev_period": compare_period.strftime("%Y-%m"),
                    "operating_cf": None,
                    "investing_cf": None,
                    "financing_cf": None,
                    "net_cash_change": None,
                    "operating_cf_diff": None,
                    "investing_cf_diff": None,
                    "financing_cf_diff": None,
                    "net_cash_change_diff": None,
                }

                # -----------------------------
                # Fetch comparison row if exists
                # -----------------------------
                if compare_period is not None:

                    prev_row = g[g["period"] == compare_period]

                    if not prev_row.empty:
                        prev_row = prev_row.iloc[0]

                        comparison_data = {
                            "prev_period": compare_period.strftime("%Y-%m"),
                            "operating_cf": round(prev_row["operating_cf"], 2),
                            "investing_cf": round(prev_row["investing_cf"], 2),
                            "financing_cf": round(prev_row["financing_cf"], 2),
                            "net_cash_change": round(prev_row["net_cash_change"], 2),
                            "operating_cf_diff": round(
                                row["operating_cf"] - prev_row["operating_cf"], 2
                            ),
                            "investing_cf_diff": round(
                                row["investing_cf"] - prev_row["investing_cf"], 2
                            ),
                            "financing_cf_diff": round(
                                row["financing_cf"] - prev_row["financing_cf"], 2
                            ),
                            "net_cash_change_diff": round(
                                row["net_cash_change"] - prev_row["net_cash_change"], 2
                            ),
                        }

            item = {
                "period": current_period.strftime("%Y-%m"),
                "operating_cf": round(row["operating_cf"], 2),
                "investing_cf": round(row["investing_cf"], 2),
                "financing_cf": round(row["financing_cf"], 2),
                "net_cash_change": round(row["net_cash_change"], 2),
                "comparision": comparison_data,
            }

            # if entity:
            #     item["entity_id"] = row["entity_id"]

            data.append(item)

        data = [
            d for d in data
            if int(d["period"][:4]) == year
        ]

        mvr_result[account_name] = {
            "data": data,
            "ai_driver": ai_driver
        }



    # mvr_result = {}

    # for account_name, g in grouped.groupby("account_name", sort=False):

    #     g = g.sort_values("period").copy()
    #     g["period"] = pd.to_datetime(g["period"], format="%Y-%m")

    #     data = []

    #     for _, row in g.iterrows():
    #         current_period = row["period"]
    #         compare_period = None

    #         # -----------------------------
    #         # Decide comparison period
    #         # -----------------------------
    #         if comparision == "month_on_month":
    #             compare_period = current_period - pd.DateOffset(months=1)

    #         elif comparision == "year_on_year":
    #             compare_period = current_period - pd.DateOffset(years=1)

    #         comparison_data = {
    #             "prev_period": None,
    #             "operating_cf": None,
    #             "investing_cf": None,
    #             "financing_cf": None,
    #             "net_cash_change": None,
    #             "operating_cf_diff": None,
    #             "investing_cf_diff": None,
    #             "financing_cf_diff": None,
    #             "net_cash_change_diff": None,
    #         }

    #         # -----------------------------
    #         # Fetch comparison row if exists
    #         # -----------------------------
    #         if compare_period is not None:

    #             prev_row = g[g["period"] == compare_period]

    #             if not prev_row.empty:
    #                 prev_row = prev_row.iloc[0]

    #                 comparison_data = {
    #                     "prev_period": compare_period.strftime("%Y-%m"),

    #                     "operating_cf": round(prev_row["operating_cf"], 2),
    #                     "investing_cf": round(prev_row["investing_cf"], 2),
    #                     "financing_cf": round(prev_row["financing_cf"], 2),
    #                     "net_cash_change": round(prev_row["net_cash_change"], 2),

    #                     "operating_cf_diff": round(
    #                         row["operating_cf"] - prev_row["operating_cf"], 2
    #                     ),
    #                     "investing_cf_diff": round(
    #                         row["investing_cf"] - prev_row["investing_cf"], 2
    #                     ),
    #                     "financing_cf_diff": round(
    #                         row["financing_cf"] - prev_row["financing_cf"], 2
    #                     ),
    #                     "net_cash_change_diff": round(
    #                         row["net_cash_change"] - prev_row["net_cash_change"], 2
    #                     ),
    #                 }

    #         item = {
    #             "period": current_period.strftime("%Y-%m"),
    #             "operating_cf": round(row["operating_cf"], 2),
    #             "investing_cf": round(row["investing_cf"], 2),
    #             "financing_cf": round(row["financing_cf"], 2),
    #             "net_cash_change": round(row["net_cash_change"], 2),
    #             "comparision": comparison_data,
    #         }

    #         if entity:
    #             item["entity_id"] = row["entity_id"]

    #         data.append(item)


    #     data = [
    #         d for d in data
    #         if int(d["period"][:4]) == year]

    #     mvr_result[account_name] = {
    #         "data": data,
    #         "ai_driver": ai_driver}


    # derive quarter from monthly grouped data
    grouped["_period_dt"] = pd.PeriodIndex(grouped["period"], freq="M").to_timestamp()
    grouped["quarter"] = grouped["_period_dt"].dt.to_period("Q").astype(str)

    numeric_cols = [
        "operating_cf",
        "investing_cf",
        "financing_cf",
        "net_cash_change"
    ]

    all_quarters = sorted(grouped["quarter"].unique()) 

    summary_group_cols = ["quarter"]
    if entity:
        summary_group_cols.append("entity_id")

    quarterly_summary_df = (
        grouped.groupby(summary_group_cols, as_index=False)[numeric_cols]
        .sum()
    )

    # IMPORTANT: sort by period
    quarterly_summary_df = quarterly_summary_df.sort_values("quarter")

    quarterly_summary = []

    # -----------------------------------
    # BUILD BASE DATA
    # -----------------------------------
    for _, row in quarterly_summary_df.iterrows():
        item = {
            "period": row["quarter"],
            "operating_cf": round(float(row["operating_cf"]), 2),
            "investing_cf": round(float(row["investing_cf"]), 2),
            "financing_cf": round(float(row["financing_cf"]), 2),
            "net_cash_change": round(float(row["net_cash_change"]), 2),
            "comparision": {}
        }

        if entity:
            item["entity_id"] = row["entity_id"]

        quarterly_summary.append(item)


    lookup = {item["period"]: item for item in quarterly_summary}


    def safe_pct(curr_val, prev_val):
        if prev_val in (0, None):
            return None
        return round((curr_val - prev_val) / prev_val * 100, 2)

    for i, item in enumerate(quarterly_summary):

        curr_period = str(item["period"])
        prev = None
        prev_period = None


        if comparision == "year_on_year":
            curr_year = int(curr_period[:4])
            suffix = curr_period[4:]   
            prev_period = f"{curr_year - 1}{suffix}"
            prev = lookup.get(prev_period)

        # -----------------------
        # QUARTER ON QUARTER
        # -----------------------
        elif comparision == "month_on_month":
            if i > 0:
                prev = quarterly_summary[i - 1]
                prev_period = prev["period"]

        # -----------------------
        # IF PREVIOUS DATA EXISTS
        # -----------------------
        if prev:
            item["comparision"] = {
                "previous_period": prev_period,

                "previous_operating_cf": prev["operating_cf"],
                "previous_investing_cf": prev["investing_cf"],
                "previous_financing_cf": prev["financing_cf"],
                "previous_net_cash_change": prev["net_cash_change"],

                "operating_cf_diff": round(item["operating_cf"] - prev["operating_cf"], 2),
                "investing_cf_diff": round(item["investing_cf"] - prev["investing_cf"], 2),
                "financing_cf_diff": round(item["financing_cf"] - prev["financing_cf"], 2),
                "net_cash_change_diff": round(item["net_cash_change"] - prev["net_cash_change"], 2),
            }

        else:
            item["comparision"] = {
                "previous_period": prev_period,

                "previous_operating_cf": None,
                "previous_investing_cf": None,
                "previous_financing_cf": None,
                "previous_net_cash_change": None,

                "operating_cf_diff": None,
                "investing_cf_diff": None,
                "financing_cf_diff": None,
                "net_cash_change_diff": None,

            }
        if comparision == None:
            item["comparision"] = {}


    quarterly_summary = [
        item for item in quarterly_summary
        if str(item["period"]).strip()[:4] == str(year)
    ]
    # -------------------------------------------------------------------#
    grouped["quarter"] = (
    pd.to_datetime(grouped["period"])
        .dt.to_period("Q")
        .astype(str))
    # grouped["quarter"] = (
    # pd.PeriodIndex(grouped["period"], freq="M")
    # .to_period("Q")
    # .astype(str))

    numeric_cols = ["operating_cf", "investing_cf", "financing_cf", "net_cash_change"]

    q_group_cols = ["quarter", "account_name"]
    if entity:
        q_group_cols.append("entity_id")

    quarterly = (
        grouped.groupby(q_group_cols, as_index=False)[numeric_cols]
        .sum()
    )

    # IMPORTANT: sort properly
    quarterly = quarterly.sort_values(["account_name", "quarter"])

    qvr_result = {}


    # -----------------------------------
    # SAFE % FUNCTION
    # -----------------------------------
    def safe_pct(curr_val, prev_val):
        if prev_val in (0, None):
            return None
        return round((curr_val - prev_val) / prev_val * 100, 2)


    # -----------------------------------
    # PROCESS EACH ACCOUNT
    # -----------------------------------
    for account_name, g in quarterly.groupby("account_name"):

        g = g.sort_values("quarter")

        records = g.to_dict(orient="records")

        result_list = []

        # Build base data first
        for row in records:
            item = {
                "period": row["quarter"],
                "operating_cf": round(float(row["operating_cf"]), 2),
                "investing_cf": round(float(row["investing_cf"]), 2),
                "financing_cf": round(float(row["financing_cf"]), 2),
                "net_cash_change": round(float(row["net_cash_change"]), 2),
                "comparision": {}
            }

            if entity:
                item["entity_id"] = row["entity_id"]

            result_list.append(item)

        # Lookup for YOY
        lookup = {item["period"]: item for item in result_list}

        # -----------------------------------
        # APPLY COMPARISON
        # -----------------------------------
        for i, item in enumerate(result_list):

            curr_period = str(item["period"])
            prev = None
            prev_period = None

            # -----------------------
            # YEAR ON YEAR
            # -----------------------
            if comparision == "year_on_year":
                curr_year = int(curr_period[:4])
                suffix = curr_period[4:]   # Q1/Q2 etc
                prev_period = f"{curr_year - 1}{suffix}"
                prev = lookup.get(prev_period)

            elif comparision == "month_on_month":
                if i > 0:
                    prev = result_list[i - 1]
                    prev_period = prev["period"]

            # -----------------------
            # BUILD COMPARISON
            # -----------------------
            if prev:
                item["comparision"] = {
                    "previous_period": prev_period,

                    "previous_operating_cf": prev["operating_cf"],
                    "previous_investing_cf": prev["investing_cf"],
                    "previous_financing_cf": prev["financing_cf"],
                    "previous_net_cash_change": prev["net_cash_change"],

                    "operating_cf_diff": round(item["operating_cf"] - prev["operating_cf"], 2),
                    "investing_cf_diff": round(item["investing_cf"] - prev["investing_cf"], 2),
                    "financing_cf_diff": round(item["financing_cf"] - prev["financing_cf"], 2),
                    "net_cash_change_diff": round(item["net_cash_change"] - prev["net_cash_change"], 2),


                }

            else:
                item["comparision"] = {
                    "previous_period": prev_period,

                    "previous_operating_cf": None,
                    "previous_investing_cf": None,
                    "previous_financing_cf": None,
                    "previous_net_cash_change": None,

                    "operating_cf_diff": None,
                    "investing_cf_diff": None,
                    "financing_cf_diff": None,
                    "net_cash_change_diff": None,

                }
            if comparision == None:
                item["comparision"] = {}

        # Keep ai_driver at end
        result_list.append({"ai_driver": ai_driver})

        qvr_result[account_name] = result_list

    qvr_result = {acc: [item for item in records
        if "ai_driver" in item or (item.get("period", "")).startswith(str(year))]for acc, records in qvr_result.items()}


    summary_cols = ["period"]
    if entity:
        summary_cols.append("entity_id")

    summary = (
        df.groupby(summary_cols, as_index=False)
        .agg(
            operating_cf=("operating_income_part", "sum"),
            investing_cf=("investing_cf_part", "sum"),
            financing_cf=("financing_cf_part", "sum"),
        )
    )

    summary["net_cash_change"] = (
        summary["operating_cf"]
        + summary["investing_cf"]
        + summary["financing_cf"]
    )

    summary = summary.sort_values("period")
    summary["period"] = pd.to_datetime(summary["period"], format="%Y-%m")

    summary_result = []

 

    for _, row in summary.iterrows():

        current_period = row["period"]
        compare_period = None

        if comparision == "month_on_month":
            compare_period = current_period - pd.DateOffset(months=1)

        elif comparision == "year_on_year":
            compare_period = current_period - pd.DateOffset(years=1)

        comparison_data = {
            "prev_period": None,
            "operating_cf": None,
            "investing_cf": None,
            "financing_cf": None,
            "net_cash_change": None,
            "operating_cf_diff": None,
            "investing_cf_diff": None,
            "financing_cf_diff": None,
            "net_cash_change_diff": None,
        }

        if compare_period is not None:

            prev_row = summary.loc[summary["period"] == compare_period]

            if not prev_row.empty:
                prev_row = prev_row.iloc[0]

                comparison_data = {
                    "prev_period": compare_period.strftime("%Y-%m"),
                    "operating_cf": round(prev_row["operating_cf"], 2),
                    "investing_cf": round(prev_row["investing_cf"], 2),
                    "financing_cf": round(prev_row["financing_cf"], 2),
                    "net_cash_change": round(prev_row["net_cash_change"], 2),
                    "operating_cf_diff": round(row["operating_cf"] - prev_row["operating_cf"], 2),
                    "investing_cf_diff": round(row["investing_cf"] - prev_row["investing_cf"], 2),
                    "financing_cf_diff": round(row["financing_cf"] - prev_row["financing_cf"], 2),
                    "net_cash_change_diff": round(row["net_cash_change"] - prev_row["net_cash_change"], 2),
                }

        item = {
            "period": current_period.strftime("%Y-%m"),
            "operating_cf": round(row["operating_cf"], 2),
            "investing_cf": round(row["investing_cf"], 2),
            "financing_cf": round(row["financing_cf"], 2),
            "net_cash_change": round(row["net_cash_change"], 2),
            "comparision": comparison_data if comparision else {},
        }

        if entity:
            item["entity_id"] = row["entity_id"]

        summary_result.append(item)

  
    monthly_summary = [
        r for r in summary_result
        if int(r["period"][:4]) == year
    ]
    # print(monthly_summary , "monthly summary-------------------")


    time_filter = time_filter or CashFlowViewMoreTimeFilters.monthly
    view_filter = view_filter or CashFlowViewMoreViewFilters.detailed_view

    response_map = {
        (CashFlowViewMoreTimeFilters.monthly, CashFlowViewMoreViewFilters.detailed_view): mvr_result,
        (CashFlowViewMoreTimeFilters.monthly, CashFlowViewMoreViewFilters.summery_view): monthly_summary,
        (CashFlowViewMoreTimeFilters.quarterly, CashFlowViewMoreViewFilters.detailed_view): qvr_result,
        (CashFlowViewMoreTimeFilters.quarterly, CashFlowViewMoreViewFilters.summery_view) : quarterly_summary
    }

    result = response_map.get((time_filter, view_filter), monthly_summary)

    metrics = ["operating_cf", "investing_cf", "financing_cf", "net_cash_change"]
    result = apply_smart_currency(result, currency, currency_filter, metrics)
    res = apply_entity_hierarchy_filter( data=result,
                    entity=entity,hierarchy=None , product = None , costcenter=None , profitcenter= None ,
                    region= None , customer= None)
    all_filters_data =  await all_filters_data_display(db=db)
    if res:
        try:
            res["entities"] = all_filters_data["entities"]
        except:
            res.append({"entities" :all_filters_data["entities"]})
    return res



@financial_router1.get("/balance_sheet_statement/{year}/{currency}", dependencies=[Depends(get_current_company_user)])
async def balance_report(
    year: int,
    currency: str,
    time_filter: Optional[TimeFilterEnum] = Query(default=TimeFilterEnum.all_periods, enum=[e.value for e in TimeFilterEnum]),
    currency_filter: CurrencyFilter = Query(default=CurrencyFilter.actual_value, enum=[e.value for e in CurrencyFilter]),
    entity: Optional[str] = Query(default=None),
    comparision: Optional[ComparityFilter] = Query(default=None, enum=[e.value for e in ComparityFilter]),
    show_period: bool = Query(False),
    show_entity: bool = Query(False),
    db: AsyncSession = Depends(get_db)):

    required_years = [year - 1, year]
    params = {"years": required_years}

    query = text("""
        SELECT
            tb.period,
            tb.gl_account_id,
            tb.entity_id,
            tb.ending_balance,
            tb.debit_total,
            tb.credit_total,
            coa.account_code,
            coa.account_name,
            coa.account_type
        FROM fpna_landing.finance_trial_balance tb
        JOIN fpna_landing.finance_chart_of_accounts coa
            ON tb.gl_account_id = coa.account_id
        WHERE EXTRACT(YEAR FROM TO_DATE(tb.period, 'YYYY-MM')) = ANY(:years)
          AND coa.account_type IN ('Asset', 'Liability', 'Equity')
    """)

    result = await db.execute(query, params)
    rows = result.fetchall()
    columns = result.keys()

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

    entity_query = text("""Select entity_id , entity_name from fpna_landing.finance_entities""")
    entity_results = await db.execute(entity_query)
    entity_rows = entity_results.fetchall()
    entity_data = [dict(row._mapping) for row in entity_rows]
    entity_df = pd.DataFrame(entity_data)
    entities = entity_df.to_dict(orient="records")

    if df.empty:
        return "No data available"

    if entity:
        df = df[df["entity_id"] == entity]

    if df.empty:
        return "No data available for selected entity"

    df["ending_balance"] = pd.to_numeric(df["ending_balance"], errors="coerce")
    df = df.sort_values(["account_type", "account_name", "period", "entity_id"])

    if show_entity:
        grouped = df.groupby(["account_type", "account_name", "period", "entity_id"], as_index=False).agg(current_value=("ending_balance", "sum"))
    else:
        grouped = df.groupby(["account_type", "account_name", "period"], as_index=False).agg(current_value=("ending_balance", "sum"))

    grouped["previous_value"] = grouped.groupby(["account_type", "account_name"])["current_value"].shift(1)
    grouped["change_value"] = grouped["current_value"] - grouped["previous_value"]

    grouped["change_pct"] = np.where(
        (grouped["previous_value"].notna()) & (grouped["previous_value"] != 0),
        np.round((grouped["change_value"] / grouped["previous_value"]) * 100, 2),
        None
    )

    grouped = grouped.replace([np.inf, -np.inf], None)
    grouped = grouped.where(pd.notnull(grouped), None)
    grouped["comparision"] = [{} for _ in range(len(grouped))]
    grouped["period"] = pd.to_datetime(grouped["period"], format="%Y-%m")

    s_y_grouped = grouped[grouped["period"].dt.year == year]

    c_month = date.today().month
    target_period = f"{year}-{c_month:02d}"

    current_month_df = grouped[grouped["period"] == pd.to_datetime(target_period)]
    current_month_data = current_month_df.to_dict(orient="records")

    dt = datetime.strptime(target_period, "%Y-%m")
    last_month = (dt - relativedelta(months=1)).strftime("%Y-%m")
    last_month_df = grouped[grouped["period"] == pd.to_datetime(last_month)]
    last_month_data = last_month_df.to_dict(orient="records")

    month = dt.month
    year = dt.year
    quarter_start_month = ((month - 1) // 3) * 3 + 1
    qtd_periods = [f"{year}-{m:02d}" for m in range(quarter_start_month, month + 1)]
    qtd_df = grouped[grouped["period"].isin(pd.to_datetime(qtd_periods))]
    qtd_data = qtd_df.to_dict(orient="records")

    ytd_df = grouped[
        (grouped["period"].dt.year == year) &
        (grouped["period"].dt.month <= c_month)
    ]
    ytd_data = ytd_df.to_dict(orient="records")

    all_periods_data = s_y_grouped.to_dict(orient="records")

    time_filter_map = {
        TimeFilterEnum.all_periods: all_periods_data,
        TimeFilterEnum.current_month: current_month_data,
        TimeFilterEnum.last_month: last_month_data,
        TimeFilterEnum.quarter_to_date: qtd_data,
        TimeFilterEnum.year_to_date: ytd_data,
    }

    result_r = time_filter_map.get(time_filter, all_periods_data)
    res_df = pd.DataFrame(result_r)

    if len(res_df) == 0:
        return {"message": "No data"}

    if comparision is not None:
        res_df["period"] = pd.to_datetime(res_df["period"], errors="coerce")

        if comparision == ComparityFilter.year_on_year:
            res_df["prev_period"] = res_df["period"] - pd.DateOffset(years=1)
        elif comparision == ComparityFilter.month_on_month:
            res_df["prev_period"] = res_df["period"] - pd.DateOffset(months=1)

        res_df["period"] = res_df["period"].dt.strftime("%Y-%m")
        res_df["prev_period"] = res_df["prev_period"].dt.strftime("%Y-%m")

        grouped_copy = grouped.copy()
        grouped_copy["period"] = grouped_copy["period"].dt.strftime("%Y-%m")

        merge_left = ["prev_period", "account_type", "account_name"]
        merge_right = ["period", "account_type", "account_name"]

        if show_entity:
            merge_left.append("entity_id")
            merge_right.append("entity_id")

        merged = res_df.merge(
            grouped_copy[merge_right + ["current_value", "previous_value"]],
            left_on=merge_left,
            right_on=merge_right,
            how="left",
            suffixes=("", "_prev")
        )

        merged["comparision"] = merged.apply(
            lambda row: {
                "prev_period": row["prev_period"] if pd.notna(row.get("current_value_prev")) else None,
                "prev_current_value": row.get("current_value_prev"),
                "prev_previous_value": row.get("previous_value_prev"),
                "current_value_diff": row["current_value"] - row["current_value_prev"] if pd.notna(row.get("current_value_prev")) else None,
                "previous_value_diff": row["previous_value"] - row["previous_value_prev"] if pd.notna(row.get("previous_value_prev")) else None
            },
            axis=1
        )

        merged.drop(columns=["period_prev", "current_value_prev", "previous_value_prev"], inplace=True, errors="ignore")
        res_df = merged.to_dict(orient="records")
    else:
        res_df = result_r

    filtered_df_for_metrics = pd.DataFrame(result_r)

    def compute_key_metrics(source_df):
        if source_df.empty:
            return {}

        source_df = source_df.copy()
        source_df["period"] = pd.to_datetime(source_df["period"], errors="coerce")

        t_assets      = source_df[source_df["account_type"] == "Asset"]["current_value"].sum()
        t_liabilities = source_df[source_df["account_type"] == "Liability"]["current_value"].sum()
        t_equity      = source_df[source_df["account_type"] == "Equity"]["current_value"].sum()

        max_period = source_df["period"].max()
        prev_p     = max_period - pd.DateOffset(months=1)

        prev_slice       = source_df[source_df["period"] == prev_p]
        prev_assets      = prev_slice[prev_slice["account_type"] == "Asset"]["current_value"].sum()
        prev_liabilities = prev_slice[prev_slice["account_type"] == "Liability"]["current_value"].sum()

        a_growth = t_assets      - prev_assets
        l_growth = t_liabilities - prev_liabilities

        c_assets      = source_df[source_df["account_name"].isin(["Cash", "Accounts Receivable", "Inventory"])]["current_value"].sum()
        c_liabilities = source_df[source_df["account_name"].isin(["Accounts Payable", "Short-Term Debt"])]["current_value"].sum()
        c_ratio = c_assets / c_liabilities if c_liabilities not in [0, None] else None
        d_to_e  = t_liabilities / t_equity  if t_equity      not in [0, None] else None

        return {
            "Total Assets":       t_assets,
            "Total Liabilities":  t_liabilities,
            "Net Equity":         t_equity,
            "Asset Growth":       a_growth,
            "Liability Growth":   l_growth,
            "Current Ratio":      c_ratio,
            "Debt To Equity":     d_to_e,
            "aiDriver": [
                "AI-powered insights coming soon",
                "AI enhances efficiency through automated financial analysis"
            ]
        }

    raw_metrics = compute_key_metrics(filtered_df_for_metrics)
    m_r         = ["Debt To Equity", "Current Ratio", "Liability Growth", "Asset Growth", "Net Equity", "Total Liabilities", "Total Assets"]
    key_metrics = apply_smart_currency(raw_metrics, currency, currency_filter, m_r)

    m_f      = ["current_value", "previous_value", "change_value"]
    comp_m_f = ["prev_current_value", "prev_previous_value", "current_value_diff", "previous_value_diff"]

    row_period_data = {}
    row_dim_data    = {}

    for row in res_df:
        p_raw = row.get("period", "")
        try:
            period_v = pd.Timestamp(p_raw).strftime("%Y-%m")
        except Exception:
            period_v = str(p_raw)[:7]

        acct_type = row.get("account_type")
        acct_name = row.get("account_name")
        ent_id    = row.get("entity_id") if show_entity else None
        curr_v    = float(row.get("current_value")  or 0) if row.get("current_value")  is not None else 0.0
        prev_v    = float(row.get("previous_value") or 0) if row.get("previous_value") is not None else None
        chg_v     = float(row.get("change_value")   or 0) if row.get("change_value")   is not None else None
        chg_pct   = row.get("change_pct")
        comp_raw  = row.get("comparision", {}) or {}

        key_parts = [acct_type, acct_name]
        if show_entity: key_parts.append(ent_id)
        key = tuple(key_parts)

        if key not in row_period_data:
            row_period_data[key] = {}
            dims = {"account_type": acct_type, "account_name": acct_name}
            if show_entity: dims["entity_id"] = ent_id
            row_dim_data[key] = dims

        if period_v not in row_period_data[key]:
            row_period_data[key][period_v] = {
                "current_value":  curr_v,
                "previous_value": prev_v,
                "change_value":   chg_v,
                "change_pct":     chg_pct,
                "comp_raw":       comp_raw,
            }
        else:
            ex       = row_period_data[key][period_v]
            new_curr = (ex["current_value"] or 0) + curr_v
            new_prev = ((ex["previous_value"] or 0) + (prev_v or 0)) if (ex["previous_value"] is not None or prev_v is not None) else None
            ex["current_value"]  = new_curr
            ex["previous_value"] = new_prev
            ex["change_value"]   = new_curr - (new_prev or 0) if new_prev is not None else new_curr
            ex["change_pct"]     = None

    periods_available = sorted(set(
        p for pd_data in row_period_data.values() for p in pd_data if p
    ))

    grand_period_map = {}

    records = []
    for key, p_data in row_period_data.items():
        dims = row_dim_data[key]

        months          = {}
        tot_curr        = 0.0
        tot_prev        = 0.0
        tot_chg         = 0.0
        has_prev        = False
        agg_prev_c_cur  = 0.0
        agg_prev_c_pre  = 0.0
        agg_diff_cur    = 0.0
        agg_diff_pre    = 0.0
        agg_prev_period = None
        has_comp        = False

        for p in periods_available:
            if p in p_data:
                entry    = p_data[p]
                curr_v   = entry["current_value"]
                prev_v   = entry["previous_value"]
                chg_v    = entry["change_value"]
                chg_pct  = entry["change_pct"]
                comp_raw = entry.get("comp_raw", {}) or {}

                c_prev_cur = 0.0
                c_prev_pre = 0.0
                c_diff_cur = 0.0
                c_diff_pre = 0.0
                c_prev_p   = None

                if comparision and comp_raw:
                    c_prev_cur = float(comp_raw.get("prev_current_value")  or 0) if comp_raw.get("prev_current_value")  is not None else 0.0
                    c_prev_pre = float(comp_raw.get("prev_previous_value") or 0) if comp_raw.get("prev_previous_value") is not None else 0.0
                    c_diff_cur = float(comp_raw.get("current_value_diff")  or 0) if comp_raw.get("current_value_diff")  is not None else 0.0
                    c_diff_pre = float(comp_raw.get("previous_value_diff") or 0) if comp_raw.get("previous_value_diff") is not None else 0.0
                    c_prev_p   = comp_raw.get("prev_period")

                converted = apply_smart_currency(
                    {"current_value": curr_v, "previous_value": prev_v or 0, "change_value": chg_v or 0},
                    currency, currency_filter, m_f
                )
                month_entry = {
                    "current_value":  converted["current_value"],
                    "previous_value": converted["previous_value"] if prev_v is not None else None,
                    "change_value":   converted["change_value"]   if chg_v  is not None else None,
                    "change_pct":     chg_pct,
                }

                if comparision and comp_raw:
                    comp_conv = apply_smart_currency(
                        {"prev_current_value": c_prev_cur, "prev_previous_value": c_prev_pre,
                         "current_value_diff":  c_diff_cur, "previous_value_diff": c_diff_pre},
                        currency, currency_filter, comp_m_f
                    )
                    month_entry["comparison"] = {"prev_period": c_prev_p, **comp_conv}
                    agg_prev_c_cur  += c_prev_cur
                    agg_prev_c_pre  += c_prev_pre
                    agg_diff_cur    += c_diff_cur
                    agg_diff_pre    += c_diff_pre
                    if c_prev_p:
                        agg_prev_period = c_prev_p
                    has_comp = True
                else:
                    month_entry["comparison"] = {}

                if show_period:
                    months[p] = month_entry

                tot_curr += curr_v
                if prev_v is not None:
                    tot_prev += prev_v
                    has_prev = True
                if chg_v is not None:
                    tot_chg += chg_v

                if p not in grand_period_map:
                    grand_period_map[p] = {
                        "current_value": 0.0, "previous_value": 0.0, "change_value": 0.0,
                        "prev_c_cur": 0.0, "prev_c_pre": 0.0,
                        "diff_cur":   0.0, "diff_pre":   0.0,
                        "comp_prev_period": None,
                    }
                grand_period_map[p]["current_value"]  += curr_v
                grand_period_map[p]["previous_value"] += (prev_v or 0)
                grand_period_map[p]["change_value"]   += (chg_v  or 0)
                grand_period_map[p]["prev_c_cur"]     += c_prev_cur
                grand_period_map[p]["prev_c_pre"]     += c_prev_pre
                grand_period_map[p]["diff_cur"]       += c_diff_cur
                grand_period_map[p]["diff_pre"]       += c_diff_pre
                if c_prev_p:
                    grand_period_map[p]["comp_prev_period"] = c_prev_p

        total_conv = apply_smart_currency(
            {"current_value": tot_curr, "previous_value": tot_prev if has_prev else 0, "change_value": tot_chg},
            currency, currency_filter, m_f
        )
        record           = dict(dims)
        record["months"] = months
        record["total"]  = {
            "current_value":  total_conv["current_value"],
            "previous_value": total_conv["previous_value"] if has_prev else None,
            "change_value":   total_conv["change_value"],
            "change_pct":     None,
        }
        record["comparison"] = {}
        if comparision and has_comp:
            prev_conv = apply_smart_currency(
                {"prev_current_value": agg_prev_c_cur, "prev_previous_value": agg_prev_c_pre,
                 "current_value_diff":  agg_diff_cur,  "previous_value_diff":  agg_diff_pre},
                currency, currency_filter, comp_m_f
            )
            record["comparison"] = {"prev_period": agg_prev_period, **prev_conv}

        records.append(record)

    grand_months        = {}
    gt_curr             = 0.0
    gt_prev             = 0.0
    gt_chg              = 0.0
    gt_prev_c_cur       = 0.0
    gt_prev_c_pre       = 0.0
    gt_diff_cur         = 0.0
    gt_diff_pre         = 0.0
    gt_comp_prev_period = None

    for p in periods_available:
        if p in grand_period_map:
            gm     = grand_period_map[p]
            g_curr = gm["current_value"]
            g_prev = gm["previous_value"]
            g_chg  = gm["change_value"]
            g_pcc  = gm["prev_c_cur"]
            g_pcp  = gm["prev_c_pre"]
            g_dc   = gm["diff_cur"]
            g_dp   = gm["diff_pre"]
            g_cpp  = gm["comp_prev_period"]

            conv_gm = apply_smart_currency(
                {"current_value": g_curr, "previous_value": g_prev, "change_value": g_chg},
                currency, currency_filter, m_f
            )
            gm_entry = {
                "current_value":  conv_gm["current_value"],
                "previous_value": conv_gm["previous_value"],
                "change_value":   conv_gm["change_value"],
                "change_pct":     None,
            }
            if comparision:
                gc_conv = apply_smart_currency(
                    {"prev_current_value": g_pcc, "prev_previous_value": g_pcp,
                     "current_value_diff":  g_dc,  "previous_value_diff":  g_dp},
                    currency, currency_filter, comp_m_f
                )
                gm_entry["comparison"] = {"prev_period": g_cpp, **gc_conv}
            else:
                gm_entry["comparison"] = {}

            if show_period:
                grand_months[p] = gm_entry

            gt_curr         += g_curr
            gt_prev         += g_prev
            gt_chg          += g_chg
            gt_prev_c_cur   += g_pcc
            gt_prev_c_pre   += g_pcp
            gt_diff_cur     += g_dc
            gt_diff_pre     += g_dp
            if g_cpp:
                gt_comp_prev_period = g_cpp

    gt_total_conv = apply_smart_currency(
        {"current_value": gt_curr, "previous_value": gt_prev, "change_value": gt_chg},
        currency, currency_filter, m_f
    )
    grand_total_record = {
        "account_name": "Grand Total",
        "months":       grand_months,
        "total": {
            "current_value":  gt_total_conv["current_value"],
            "previous_value": gt_total_conv["previous_value"],
            "change_value":   gt_total_conv["change_value"],
            "change_pct":     None,
        },
        "comparison": {},
    }
    if comparision:
        gt_comp_conv = apply_smart_currency(
            {"prev_current_value": gt_prev_c_cur, "prev_previous_value": gt_prev_c_pre,
             "current_value_diff":  gt_diff_cur,  "previous_value_diff":  gt_diff_pre},
            currency, currency_filter, comp_m_f
        )
        grand_total_record["comparison"] = {"prev_period": gt_comp_prev_period, **gt_comp_conv}

    records            = clean_data(records)
    grand_total_record = clean_data(grand_total_record)

    return {
        "records":      records,
        "grand_total":  grand_total_record,
        "keymetrics":   key_metrics,
        "periods":      periods_available,
        "show_columns": {
            "period": show_period,
            "entity": show_entity,
        },
        "entities": entities,
    }


####################################################################################################
####################################################################################################
####################################################################################################

@financial_router1.get("/balance_sheet_statement_viewmore/{year}/{currency}", dependencies=[Depends(get_current_company_user)])
async def balance_report_viewmore(
    year: int,
    currency: str,
    time_filter: Optional[TimeFilterEnum] = Query(default=TimeFilterEnum.all_periods, enum=[e.value for e in TimeFilterEnum]),
    currency_filter: CurrencyFilter = Query(default=CurrencyFilter.actual_value, enum=[e.value for e in CurrencyFilter]),
    entity: Optional[str] = Query(default=None),
    comparision: Optional[ComparityFilter] = Query(default=None, enum=[e.value for e in ComparityFilter]),
    db: AsyncSession = Depends(get_db)):

    required_years = [year - 1, year]
    params = {"years": required_years}

    query = text("""
        SELECT
            tb.period,
            tb.gl_account_id,
            tb.entity_id,
            tb.ending_balance,
            tb.debit_total,
            tb.credit_total,
            coa.account_code,
            coa.account_name,
            coa.account_type
        FROM fpna_landing.finance_trial_balance tb
        JOIN fpna_landing.finance_chart_of_accounts coa
            ON tb.gl_account_id = coa.account_id
        WHERE EXTRACT(YEAR FROM TO_DATE(tb.period, 'YYYY-MM')) = ANY(:years)
          AND coa.account_type IN ('Asset', 'Liability', 'Equity')
    """)

    result = await db.execute(query, params)
    rows = result.fetchall()
    columns = result.keys()

    df = pd.DataFrame(rows, columns=columns)
    df.fillna(0, inplace=True)

    entity_query = text("""Select entity_id , entity_name from fpna_landing.finance_entities""")
    entity_results = await db.execute(entity_query)
    entity_rows = entity_results.fetchall()
    entity_data = [dict(row._mapping) for row in entity_rows]
    entity_df = pd.DataFrame(entity_data)
    entities = entity_df.to_dict(orient="records")

    if df.empty:
        return "No data available"

    if entity:
        df = df[df["entity_id"] == entity]

    if df.empty:
        return "No data available for selected entity"

    df = df.sort_values(["account_type", "account_name", "period", "entity_id"])

    if entity:
        grouped = (
            df.groupby(
                ["account_type", "account_name", "period", "entity_id"],
                as_index=False
            )
            .agg(current_value=("ending_balance", "sum"))
        )
    else:
        grouped = (
            df.groupby(
                ["account_type", "account_name", "period"],
                as_index=False
            )
            .agg(current_value=("ending_balance", "sum"))
        )

    grouped["period"] = pd.to_datetime(grouped["period"], format="%Y-%m")

    c_month = date.today().month
    target_period = f"{year}-{c_month:02d}"
    dt = datetime.strptime(target_period, "%Y-%m")
    month = dt.month
    year = dt.year

    s_y_grouped = grouped[grouped["period"].dt.year == year].copy()
    current_month_data = grouped[grouped["period"] == target_period].copy()
    last_month_str = (dt - relativedelta(months=1)).strftime("%Y-%m")
    last_month_data = grouped[grouped["period"] == last_month_str].copy()
    quarter_start_month = ((month - 1) // 3) * 3 + 1
    qtd_periods = pd.to_datetime([f"{year}-{m:02d}" for m in range(quarter_start_month, month + 1)])
    qtd_data = grouped[grouped["period"].isin(qtd_periods)].copy()
    ytd_data = grouped[
        (grouped["period"].dt.year == year) &
        (grouped["period"].dt.month <= c_month)
    ].copy()

    time_filter_df_map = {
        TimeFilterEnum.all_periods: s_y_grouped,
        TimeFilterEnum.current_month: current_month_data,
        TimeFilterEnum.last_month: last_month_data,
        TimeFilterEnum.quarter_to_date: qtd_data,
        TimeFilterEnum.year_to_date: ytd_data,
    }
    base_df = time_filter_df_map.get(time_filter, s_y_grouped).copy()

    if base_df.empty:
        return {"message": "No data"}

    def apply_currency_to_value(val):
        if val is None or (isinstance(val, float) and np.isnan(val)):
            return None
        temp = {"v": float(val)}
        converted = apply_smart_currency(temp, currency, currency_filter, ["v"])
        return converted.get("v", val)

    def compute_key_metrics(source_df):
        if source_df.empty:
            return {}
        source_df = source_df.copy()
        t_assets = source_df[source_df["account_type"] == "Asset"]["current_value"].sum()
        t_liabilities = source_df[source_df["account_type"] == "Liability"]["current_value"].sum()
        t_equity = source_df[source_df["account_type"] == "Equity"]["current_value"].sum()
        max_period = source_df["period"].max()
        prev_p = max_period - pd.DateOffset(months=1)
        prev_slice = source_df[source_df["period"] == prev_p]
        prev_assets = prev_slice[prev_slice["account_type"] == "Asset"]["current_value"].sum()
        prev_liabilities = prev_slice[prev_slice["account_type"] == "Liability"]["current_value"].sum()
        a_growth = t_assets - prev_assets
        l_growth = t_liabilities - prev_liabilities
        c_assets = source_df[source_df["account_name"].isin(["Cash", "Accounts Receivable", "Inventory"])]["current_value"].sum()
        c_liabilities = source_df[source_df["account_name"].isin(["Accounts Payable", "Short-Term Debt"])]["current_value"].sum()
        c_ratio = c_assets / c_liabilities if c_liabilities != 0 else None
        d_to_e = t_liabilities / t_equity if t_equity != 0 else None
        return {
            "Total Assets": t_assets,
            "Total Liabilities": t_liabilities,
            "Net Equity": t_equity,
            "Asset Growth": a_growth,
            "Liability Growth": l_growth,
            "Current Ratio": c_ratio,
            "Debt To Equity": d_to_e,
            "aiDriver": [
                "AI-powered insights coming soon",
                "AI enhances efficiency through automated financial analysis"
            ]
        }

    raw_metrics = compute_key_metrics(base_df)
    m_r = ["Debt To Equity", "Current Ratio", "Liability Growth", "Asset Growth", "Net Equity", "Total Liabilities", "Total Assets"]
    key_metrics = apply_smart_currency(raw_metrics, currency, currency_filter, m_r)

    base_df["period_str"] = base_df["period"].dt.strftime("%Y-%m")
    grouped["period_str"] = grouped["period"].dt.strftime("%Y-%m")

    all_periods_sorted = sorted(base_df["period_str"].unique())

    def build_row_comparisons(acc_type, account_name, base_periods_df, full_grouped_df, comp_type):
        base_acc = base_periods_df[
            (base_periods_df["account_type"] == acc_type) &
            (base_periods_df["account_name"] == account_name)
        ][["period_str", "current_value"]].copy()

        comparisons_list = []

        for _, row in base_acc.iterrows():
            base_period = row["period_str"]
            base_val = float(row["current_value"])
            base_dt = datetime.strptime(base_period, "%Y-%m")

            if comp_type == ComparityFilter.year_on_year:
                compare_period = (base_dt - relativedelta(years=1)).strftime("%Y-%m")
            else:
                compare_period = (base_dt - relativedelta(months=1)).strftime("%Y-%m")

            prev_match = full_grouped_df[
                (full_grouped_df["account_type"] == acc_type) &
                (full_grouped_df["account_name"] == account_name) &
                (full_grouped_df["period_str"] == compare_period)
            ]
            compare_val = float(prev_match["current_value"].values[0]) if not prev_match.empty else 0.0
            pct_change = round(((base_val - compare_val) / abs(compare_val)) * 100, 2) if compare_val != 0 else None

            comparisons_list.append({
                "base_period": base_period,
                "compare_period": compare_period,
                "base_value": apply_currency_to_value(base_val),
                "compare_value": apply_currency_to_value(compare_val),
                "pct_change": pct_change
            })

        base_total = float(base_acc["current_value"].sum())

        if comp_type == ComparityFilter.year_on_year:
            compare_total_df = full_grouped_df[
                (full_grouped_df["account_type"] == acc_type) &
                (full_grouped_df["account_name"] == account_name) &
                (full_grouped_df["period"].dt.year == (year - 1))
            ]
            compare_total = float(compare_total_df["current_value"].sum())
            base_label = str(year)
            compare_label = str(year - 1)
        else:
            all_compare_periods = [c["compare_period"] for c in comparisons_list]
            compare_total_df = full_grouped_df[
                (full_grouped_df["account_type"] == acc_type) &
                (full_grouped_df["account_name"] == account_name) &
                (full_grouped_df["period_str"].isin(all_compare_periods))
            ]
            compare_total = float(compare_total_df["current_value"].sum())
            base_label = str(year)
            compare_label = f"{year}-prev-months"

        total_pct = round(((base_total - compare_total) / abs(compare_total)) * 100, 2) if compare_total != 0 else None

        total_comparison = [{
            "base_period": base_label,
            "compare_period": compare_label,
            "base_value": apply_currency_to_value(base_total),
            "compare_value": apply_currency_to_value(compare_total),
            "pct_change": total_pct
        }]

        return comparisons_list, total_comparison

    def build_grand_comparisons(acc_type, base_periods_df, full_grouped_df, comp_type):
        grand_list = []

        for p in all_periods_sorted:
            base_sum = float(base_periods_df[
                (base_periods_df["account_type"] == acc_type) &
                (base_periods_df["period_str"] == p)
            ]["current_value"].sum())

            base_dt = datetime.strptime(p, "%Y-%m")

            if comp_type == ComparityFilter.year_on_year:
                compare_period = (base_dt - relativedelta(years=1)).strftime("%Y-%m")
            else:
                compare_period = (base_dt - relativedelta(months=1)).strftime("%Y-%m")

            prev_sum = float(full_grouped_df[
                (full_grouped_df["account_type"] == acc_type) &
                (full_grouped_df["period_str"] == compare_period)
            ]["current_value"].sum())

            pct = round(((base_sum - prev_sum) / abs(prev_sum)) * 100, 2) if prev_sum != 0 else None

            grand_list.append({
                "base_period": p,
                "compare_period": compare_period,
                "base_value": apply_currency_to_value(base_sum),
                "compare_value": apply_currency_to_value(prev_sum),
                "pct_change": pct
            })

        grand_base_total = float(base_periods_df[base_periods_df["account_type"] == acc_type]["current_value"].sum())

        if comp_type == ComparityFilter.year_on_year:
            grand_compare_total = float(full_grouped_df[
                (full_grouped_df["account_type"] == acc_type) &
                (full_grouped_df["period"].dt.year == (year - 1))
            ]["current_value"].sum())
            base_label = str(year)
            compare_label = str(year - 1)
        else:
            all_compare_p = [item["compare_period"] for item in grand_list]
            grand_compare_total = float(full_grouped_df[
                (full_grouped_df["account_type"] == acc_type) &
                (full_grouped_df["period_str"].isin(all_compare_p))
            ]["current_value"].sum())
            base_label = str(year)
            compare_label = f"{year}-prev-months"

        grand_total_pct = round(((grand_base_total - grand_compare_total) / abs(grand_compare_total)) * 100, 2) if grand_compare_total != 0 else None

        grand_total_comp = [{
            "base_period": base_label,
            "compare_period": compare_label,
            "base_value": apply_currency_to_value(grand_base_total),
            "compare_value": apply_currency_to_value(grand_compare_total),
            "pct_change": grand_total_pct
        }]

        return grand_list, grand_total_comp

    all_account_types = ["Asset", "Liability", "Equity"]
    final_sections = []

    for acc_type in all_account_types:
        type_df = base_df[base_df["account_type"] == acc_type].copy()

        if type_df.empty:
            continue

        account_names = type_df["account_name"].unique()
        rows_out = []

        for account_name in account_names:
            acc_df = type_df[type_df["account_name"] == account_name]

            period_values = {}
            for p in all_periods_sorted:
                row_match = acc_df[acc_df["period_str"] == p]
                raw_val = float(row_match["current_value"].values[0]) if not row_match.empty else 0.0
                period_values[p] = apply_currency_to_value(raw_val)

            total_raw = float(acc_df["current_value"].sum())

            row_entry = {
                "account_name": account_name,
                **period_values,
                "Total": apply_currency_to_value(total_raw)
            }

            if comparision is not None:
                comp_list, total_comp = build_row_comparisons(
                    acc_type, account_name, base_df, grouped, comparision
                )
                row_entry["comparisons"] = {
                    "periods": comp_list,
                    "total": total_comp
                }
            else:
                row_entry["comparisons"] = {}

            rows_out.append(row_entry)

        grand_period_values = {}
        for p in all_periods_sorted:
            period_sum = float(type_df[type_df["period_str"] == p]["current_value"].sum())
            grand_period_values[p] = apply_currency_to_value(period_sum)

        grand_total_raw = float(type_df["current_value"].sum())

        grand_entry = {
            **grand_period_values,
            "Total": apply_currency_to_value(grand_total_raw)
        }

        if comparision is not None:
            grand_comp_list, grand_total_comp = build_grand_comparisons(
                acc_type, base_df, grouped, comparision
            )
            grand_entry["comparisons"] = {
                "periods": grand_comp_list,
                "total": grand_total_comp
            }
        else:
            grand_entry["comparisons"] = {}

        final_sections.append({
            "account_type": acc_type,
            "rows": rows_out,
            "grand_total": grand_entry
        })

    return {
        "data": final_sections,
        "keymetrics": key_metrics,
        "entities": entities
    }
    
    
####################################################################################################
####################################################################################################
####################################################################################################
#operating expense report

async def expense_calculation(year, db, filters):
    print("entered-------------------------------------------")
    required_years = [year - 1, year]
    params = {"years": required_years}

    opex_accounts = [
        'Marketing', 'Salaries', 'Rent', 'Travel',
        'Employee Benefits', 'Utilities', 'Payroll Taxes', 'Insurance', 'Other Operating Expense'
    ]

    query = text("""SELECT
    gl.gl_account_id,
    gl.period,
    gl.entity_id,
    gl.department_id,
    gl.amount_credit,
    gl.amount_debit,
    gl.cost_center_id,
    gl.profit_center_id,
    coa.account_name,
    coa.account_type
FROM fpna_landing.finance_general_ledger_transactions gl

LEFT JOIN fpna_landing.finance_chart_of_accounts coa
    ON gl.gl_account_id = coa.account_id

WHERE coa.account_type IN ('Expense')
  AND EXTRACT(YEAR FROM TO_DATE(gl.period, 'YYYY-MM')) = ANY(:years);
""")

    budget_query = text("""SELECT
    b.gl_account_id,
    b.period,
    b.gl_account_name as account_name,
    b."Type",
    b.entity_id,
    b.cost_center_id,
    b.profit_center_id,
    b.department_id,
    b.customer_id,
    b.amount as budget_amount
FROM fpna_landing.finance_budget_data b
WHERE b."Type" IN ('Revenue','Expense')
  AND EXTRACT(YEAR FROM TO_DATE(b.period, 'YYYY-MM')) = ANY(:years)""")

    budget_res = await db.execute(budget_query, params)
    budget_rows = budget_res.fetchall()
    budget_data = [dict(row._mapping) for row in budget_rows]
    budget_df = pd.DataFrame(budget_data)

    result = await db.execute(query, params)
    rows = result.fetchall()
    columns = result.keys()

    data = [dict(row._mapping) for row in rows]
    df = pd.DataFrame(data, columns=columns)
    print(df.columns)
    if df.empty:
        return "No data available"

    df_expense = df[
        (df["account_type"] == "Expense") &
        (df["account_name"].isin(opex_accounts))
    ].copy()

    df_budget_expense = budget_df[
        (budget_df["Type"] == "Expense") &
        (budget_df["account_name"].isin(opex_accounts))
    ].copy()

    df_actual_grouped = (
        df_expense
        .groupby(filters, as_index=False)
        .agg(
            actual_operating_expense=(
                "amount_debit",
                lambda x: (x - df_expense.loc[x.index, "amount_credit"]).sum()
            )
        )
    )

    df_budget_grouped = (
        df_budget_expense
        .groupby(filters, as_index=False)
        .agg(budget_operating_expense=("budget_amount", "sum"))
    )

    df_grouped = pd.merge(df_actual_grouped, df_budget_grouped, on=filters, how="left")

    df_grouped["variance"] = df_grouped["actual_operating_expense"] - df_grouped["budget_operating_expense"]

    return df_grouped


@financial_router1.get("/operating_enpense_report/{year}/{currency}", dependencies=[Depends(get_current_company_user)])
async def operating_expenses(
    year: int,
    currency: str,
    db: AsyncSession = Depends(get_db),
    time_filter: Optional[TimeFilterEnum] = None,
    currency_filter: CurrencyFilter = Query(default=CurrencyFilter.actual_value),
    entity: Optional[str] = None,
    department: Optional[str] = None,
    cost_center: Optional[str] = None,
    comparision: Optional[ComparityFilter] = None,
    show_period: bool = Query(False),
    show_entity: bool = Query(False),
    show_department: bool = Query(False),
    show_cost_center: bool = Query(False),
):
    filters = ["period", "account_name"]
    if show_entity:
        filters.append("entity_id")
    if show_department:
        filters.append("department_id")
    if show_cost_center:
        filters.append("cost_center_id")

    all_filters_data = await all_filters_data_display(db=db)
    df_grouped = await expense_calculation(year, db, filters)

    df_grouped["comparision"] = [{} for _ in range(len(df_grouped))]
    print(df_grouped, "dfffffffffffffffffffffffff")


    df_grouped["period"] = pd.to_datetime(df_grouped["period"], format="%Y-%m", errors="coerce")
    selected_year_data = df_grouped[df_grouped["period"].dt.year == year]
    all_periods_data = selected_year_data.to_dict(orient="records")

    c_month = date.today().month
    target_period = f"{year}-{c_month:02d}"
    current_month_df = df_grouped[df_grouped["period"] == target_period]
    current_month_data = current_month_df.to_dict(orient="records")

    all_periods_sorted = sorted(df_grouped["period"].unique())

    dt = datetime.strptime(target_period, "%Y-%m")
    last_month_date = (dt - relativedelta(months=1)).strftime("%Y-%m")

    last_month_df = df_grouped[df_grouped["period"].dt.to_period("M") == last_month_date]
    last_month_data = last_month_df.to_dict(orient="records")

    quarter_start_month = ((c_month - 1) // 3) * 3 + 1
    qtd_periods = [f"{year}-{m:02d}" for m in range(quarter_start_month, c_month + 1)]
    qtd_df = df_grouped[df_grouped["period"].isin(qtd_periods)]
    qtd_data = qtd_df.to_dict(orient="records")

    target_period = pd.to_datetime(target_period)
    year_start = target_period.to_period("Y").start_time
    ytd_df = df_grouped[
        (df_grouped["period"] >= year_start) &
        (df_grouped["period"] <= target_period)
    ]
    ytd_data = ytd_df.to_dict(orient="records")

    time_filter_map = {
        TimeFilterEnum.all_periods: all_periods_data,
        TimeFilterEnum.current_month: current_month_data,
        TimeFilterEnum.last_month: last_month_data,
        TimeFilterEnum.quarter_to_date: qtd_data,
        TimeFilterEnum.year_to_date: ytd_data,
    }
    base_result = time_filter_map.get(time_filter, all_periods_data)

    res = apply_entity_hierarchy_filter(
        data=base_result,
        entity=entity,
        hierarchy=department,
        product=None,
        costcenter=cost_center,
        profitcenter=None,
        region=None,
        customer=None
    )

    res_df = pd.DataFrame(res)

    if len(res_df) == 0:
        return {"message": "No data"}

    compare_cols = ["actual_operating_expense", "budget_operating_expense", "variance"]

    if comparision is not None:
        res_df["period"] = pd.to_datetime(res_df["period"], errors="coerce")

        prev_df = res_df[filters].copy()

        if comparision == "year_on_year":
            prev_df["prev_period"] = (
                res_df["period"] - pd.DateOffset(years=1)
            ).dt.strftime("%Y-%m")

        elif comparision == "month_on_month":
            print("entered============================")
            prev_df["prev_period"] = (
                res_df["period"] - pd.DateOffset(months=1)
            ).dt.strftime("%Y-%m")

        df_grouped["period"] = pd.to_datetime(
            df_grouped["period"], errors="coerce"
        ).dt.strftime("%Y-%m")

        result_dict = df_grouped.to_dict(orient="records")

        result_r = apply_entity_hierarchy_filter(
            result_dict,
            entity=entity,
            hierarchy=None,
            product=None,
            costcenter=cost_center,
            profitcenter=None,
            region=None,
            customer=None
        )

        result = pd.DataFrame(result_r)

        comp_filters = ["prev_period" if c == "period" else c for c in filters]

        lookup_cols = ["actual_operating_expense", "budget_operating_expense", "variance"]

        merged = prev_df.merge(
            result[filters + lookup_cols],
            left_on=comp_filters,
            right_on=filters,
            how="left"
        )

        print(merged, "jjjjjjjjjjjjjjjjjjjjjj")

        merged = merged.rename(columns={"period_x": "period"})
        merged = merged.drop(columns=["period_y", "period"], errors="ignore")
        merged[lookup_cols] = merged[lookup_cols].fillna(0)

        final_merged = merged.drop(columns=["period"], errors="ignore")
        final_merged = final_merged.rename(
            columns={col: f"prev_{col}" for col in lookup_cols}
        )

        final_merged_dict = final_merged.to_dict(orient="records")

        res_df["period"] = res_df["period"].dt.strftime("%Y-%m")
        res_df["comparision"] = final_merged_dict

        f_r = res_df.to_dict(orient="records")

        for row in f_r:
            prev = row.get("comparision", {})
            for field in lookup_cols:
                curr_val = float(row.get(field, 0) or 0)
                prev_val = float(prev.get(f"prev_{field}", 0) or 0)
                prev[f"{field}_diff"] = round(curr_val - prev_val, 2)

    else:
        f_r = res

    km_total_actual    = 0.0
    km_total_budget    = 0.0
    km_total_variance  = 0.0
    km_favorable       = 0
    km_unfavorable     = 0
    for row in f_r:
        km_act = float(row.get("actual_operating_expense", 0) or 0)
        km_bud = float(row.get("budget_operating_expense", 0) or 0)
        km_var = float(row.get("variance", 0) or 0)
        km_total_actual   += km_act
        km_total_budget   += km_bud
        km_total_variance += km_var
        if km_var < 0:
            km_favorable  += 1
        elif km_var > 0:
            km_unfavorable += 1

    km_metrics = {
        "total_budget":           round(km_total_budget,   2),
        "total_actual":           round(km_total_actual,   2),
        "total_variance":         round(km_total_variance, 2),
        "favorable_categories":   km_favorable,
        "unfavorable_categories": km_unfavorable,
        "aiDriver": [
            "AI-powered insights coming soon",
            "AI enhances efficiency through automated financial analysis"
        ]
    }
    km_r        = ["total_budget", "total_actual", "total_variance", "favorable_categories", "unfavorable_categories"]
    key_metrics = apply_smart_currency(km_metrics, currency, currency_filter, km_r)

    if not f_r:
        return {
            "expenses":     [],
            "grand_total":  {},
            "keymetrics":   key_metrics,
            "periods":      [],
            "show_columns": {
                "period":      show_period,
                "entity":      show_entity,
                "department":  show_department,
                "cost_center": show_cost_center,
            },
            "entities":     all_filters_data["entities"],
            "departments":  all_filters_data["departments"],
            "costcenters":  all_filters_data["cost_centers"],
        }

    expense_period_data = {}
    expense_dim_data    = {}

    for row in f_r:
        act_v  = float(row.get("actual_operating_expense", 0) or 0)
        bud_v  = float(row.get("budget_operating_expense", 0) or 0)
        var_v  = float(row.get("variance", 0) or 0)
        p_raw  = row.get("period", "")
        try:
            period_v = pd.Timestamp(p_raw).strftime("%Y-%m")
        except Exception:
            period_v = str(p_raw)[:7]

        key_parts = [row.get("account_name")]
        if show_entity:      key_parts.append(row.get("entity_id"))
        if show_department:  key_parts.append(row.get("department_id"))
        if show_cost_center: key_parts.append(row.get("cost_center_id"))
        key = tuple(key_parts)

        if key not in expense_period_data:
            expense_period_data[key] = {}
            dims = {"account_name": row.get("account_name")}
            if show_entity:      dims["entity_id"]      = row.get("entity_id")
            if show_department:  dims["department_id"]  = row.get("department_id")
            if show_cost_center: dims["cost_center_id"] = row.get("cost_center_id")
            expense_dim_data[key] = dims

        if period_v not in expense_period_data[key]:
            expense_period_data[key][period_v] = {
                "actual":   act_v,
                "budget":   bud_v,
                "variance": var_v,
                "comp_raw": row.get("comparision", {}),
            }
        else:
            ex      = expense_period_data[key][period_v]
            new_act = ex["actual"] + act_v
            new_bud = ex["budget"] + bud_v
            ex["actual"]   = new_act
            ex["budget"]   = new_bud
            ex["variance"] = new_act - new_bud

    periods_available = sorted(set(
        p for pd_data in expense_period_data.values() for p in pd_data if p
    ))

    grand_period_map  = {}
    grand_period_comp = {}

    expenses = []
    for key, p_data in expense_period_data.items():
        dims = expense_dim_data[key]

        months          = {}
        total_actual    = 0.0
        total_budget    = 0.0
        total_variance  = 0.0
        prev_t_actual   = 0.0
        prev_t_budget   = 0.0
        prev_t_variance = 0.0
        has_comp        = False

        for p in periods_available:
            if p in p_data:
                entry    = p_data[p]
                act_v    = entry["actual"]
                bud_v    = entry["budget"]
                var_v    = entry["variance"]
                comp_raw = entry.get("comp_raw", {})

                converted = apply_smart_currency(
                    {"actual": act_v, "budget": bud_v, "variance": var_v},
                    currency, currency_filter, ["actual", "budget", "variance"]
                )
                month_entry = {
                    "actual_operating_expense": converted["actual"],
                    "budget_operating_expense": converted["budget"],
                    "variance":                 converted["variance"],
                }

                if comparision and comp_raw:
                    prev_act_raw = float(comp_raw.get("prev_actual_operating_expense", 0) or 0)
                    prev_bud_raw = float(comp_raw.get("prev_budget_operating_expense", 0) or 0)
                    prev_var_raw = float(comp_raw.get("prev_variance", 0) or 0)
                    diff_act_raw = float(comp_raw.get("actual_operating_expense_diff", 0) or 0)
                    diff_bud_raw = float(comp_raw.get("budget_operating_expense_diff", 0) or 0)
                    diff_var_raw = float(comp_raw.get("variance_diff", 0) or 0)

                    prev_conv = apply_smart_currency(
                        {"actual": prev_act_raw, "budget": prev_bud_raw, "variance": prev_var_raw},
                        currency, currency_filter, ["actual", "budget", "variance"]
                    )
                    diff_conv = apply_smart_currency(
                        {"actual": diff_act_raw, "budget": diff_bud_raw, "variance": diff_var_raw},
                        currency, currency_filter, ["actual", "budget", "variance"]
                    )
                    month_entry["comparison"] = {
                        "prev_actual_operating_expense": prev_conv["actual"],
                        "prev_budget_operating_expense": prev_conv["budget"],
                        "prev_variance":                 prev_conv["variance"],
                        "actual_operating_expense_diff": diff_conv["actual"],
                        "budget_operating_expense_diff": diff_conv["budget"],
                        "variance_diff":                 diff_conv["variance"],
                    }
                    prev_t_actual   += prev_act_raw
                    prev_t_budget   += prev_bud_raw
                    prev_t_variance += prev_var_raw
                    has_comp = True

                    if p not in grand_period_comp:
                        grand_period_comp[p] = {"actual": 0.0, "budget": 0.0, "variance": 0.0}
                    grand_period_comp[p]["actual"]   += prev_act_raw
                    grand_period_comp[p]["budget"]   += prev_bud_raw
                    grand_period_comp[p]["variance"] += prev_var_raw
                else:
                    month_entry["comparison"] = {}

                if show_period:
                    months[p] = month_entry

                total_actual   += act_v
                total_budget   += bud_v
                total_variance += var_v

                if p not in grand_period_map:
                    grand_period_map[p] = {"actual": 0.0, "budget": 0.0}
                grand_period_map[p]["actual"] += act_v
                grand_period_map[p]["budget"] += bud_v

        total_conv = apply_smart_currency(
            {"actual": total_actual, "budget": total_budget, "variance": total_variance},
            currency, currency_filter, ["actual", "budget", "variance"]
        )

        record           = dict(dims)
        record["months"] = months
        record["total"]  = {
            "actual_operating_expense": total_conv["actual"],
            "budget_operating_expense": total_conv["budget"],
            "variance":                 total_conv["variance"],
        }
        record["comparison"] = {}
        if comparision and has_comp:
            prev_t_conv = apply_smart_currency(
                {"actual": prev_t_actual, "budget": prev_t_budget, "variance": prev_t_variance},
                currency, currency_filter, ["actual", "budget", "variance"]
            )
            diff_t_conv = apply_smart_currency(
                {"actual": total_actual - prev_t_actual, "budget": total_budget - prev_t_budget, "variance": total_variance - prev_t_variance},
                currency, currency_filter, ["actual", "budget", "variance"]
            )
            record["comparison"] = {
                "prev_actual_operating_expense": prev_t_conv["actual"],
                "prev_budget_operating_expense": prev_t_conv["budget"],
                "prev_variance":                 prev_t_conv["variance"],
                "actual_operating_expense_diff": diff_t_conv["actual"],
                "budget_operating_expense_diff": diff_t_conv["budget"],
                "variance_diff":                 diff_t_conv["variance"],
            }

        expenses.append(record)

    grand_months         = {}
    gt_total_actual      = 0.0
    gt_total_budget      = 0.0
    gt_prev_total_actual = 0.0
    gt_prev_total_budget = 0.0
    gt_prev_total_var    = 0.0

    for p in periods_available:
        if p in grand_period_map:
            g_act = grand_period_map[p]["actual"]
            g_bud = grand_period_map[p]["budget"]
            g_var = g_act - g_bud

            conv_gm = apply_smart_currency(
                {"actual": g_act, "budget": g_bud, "variance": g_var},
                currency, currency_filter, ["actual", "budget", "variance"]
            )
            gm_entry = {
                "actual_operating_expense": conv_gm["actual"],
                "budget_operating_expense": conv_gm["budget"],
                "variance":                 conv_gm["variance"],
            }

            if comparision and p in grand_period_comp:
                gc           = grand_period_comp[p]
                gp_act       = gc["actual"]
                gp_bud       = gc["budget"]
                gp_var       = gc["variance"]
                prev_gm_conv = apply_smart_currency(
                    {"actual": gp_act, "budget": gp_bud, "variance": gp_var},
                    currency, currency_filter, ["actual", "budget", "variance"]
                )
                diff_gm_conv = apply_smart_currency(
                    {"actual": g_act - gp_act, "budget": g_bud - gp_bud, "variance": g_var - gp_var},
                    currency, currency_filter, ["actual", "budget", "variance"]
                )
                gm_entry["comparison"] = {
                    "prev_actual_operating_expense": prev_gm_conv["actual"],
                    "prev_budget_operating_expense": prev_gm_conv["budget"],
                    "prev_variance":                 prev_gm_conv["variance"],
                    "actual_operating_expense_diff": diff_gm_conv["actual"],
                    "budget_operating_expense_diff": diff_gm_conv["budget"],
                    "variance_diff":                 diff_gm_conv["variance"],
                }
                gt_prev_total_actual += gp_act
                gt_prev_total_budget += gp_bud
                gt_prev_total_var    += gp_var
            else:
                gm_entry["comparison"] = {}

            if show_period:
                grand_months[p] = gm_entry

            gt_total_actual += g_act
            gt_total_budget += g_bud

    gt_total_variance = gt_total_actual - gt_total_budget

    gt_total_conv = apply_smart_currency(
        {"actual": gt_total_actual, "budget": gt_total_budget, "variance": gt_total_variance},
        currency, currency_filter, ["actual", "budget", "variance"]
    )

    grand_total = {
        "account_name": "Grand Total",
        "months":       grand_months,
        "total": {
            "actual_operating_expense": gt_total_conv["actual"],
            "budget_operating_expense": gt_total_conv["budget"],
            "variance":                 gt_total_conv["variance"],
        },
        "comparison": {},
    }

    if comparision and grand_period_comp:
        gt_prev_conv = apply_smart_currency(
            {"actual": gt_prev_total_actual, "budget": gt_prev_total_budget, "variance": gt_prev_total_var},
            currency, currency_filter, ["actual", "budget", "variance"]
        )
        gt_diff_conv = apply_smart_currency(
            {"actual": gt_total_actual - gt_prev_total_actual, "budget": gt_total_budget - gt_prev_total_budget, "variance": gt_total_variance - gt_prev_total_var},
            currency, currency_filter, ["actual", "budget", "variance"]
        )
        grand_total["comparison"] = {
            "prev_actual_operating_expense": gt_prev_conv["actual"],
            "prev_budget_operating_expense": gt_prev_conv["budget"],
            "prev_variance":                 gt_prev_conv["variance"],
            "actual_operating_expense_diff": gt_diff_conv["actual"],
            "budget_operating_expense_diff": gt_diff_conv["budget"],
            "variance_diff":                 gt_diff_conv["variance"],
        }

    return {
        "expenses":     expenses,
        "grand_total":  grand_total,
        "keymetrics":   key_metrics,
        "periods":      periods_available,
        "show_columns": {
            "period":      show_period,
            "entity":      show_entity,
            "department":  show_department,
            "cost_center": show_cost_center,
        },
        "entities":     all_filters_data["entities"],
        "departments":  all_filters_data["departments"],
        "costcenters":  all_filters_data["cost_centers"],
    }


#############################################################################################################
#############################################################################################################
#############################################################################################################
#Operating Expense view more
async def expense_calculation(year, db, filters):
    print("entered-------------------------------------------")
    required_years = [year - 1, year]
    params = {"years": required_years}

    opex_accounts = [
        'Marketing', 'Salaries', 'Rent', 'Travel',
        'Employee Benefits', 'Utilities', 'Payroll Taxes', 'Insurance', 'Other Operating Expense'
    ]

    query = text("""SELECT
    gl.gl_account_id,
    gl.period,
    gl.entity_id,
    gl.department_id,
    gl.amount_credit,
    gl.amount_debit,
    gl.cost_center_id,
    gl.profit_center_id,
    coa.account_name,
    coa.account_type
FROM fpna_landing.finance_general_ledger_transactions gl

LEFT JOIN fpna_landing.finance_chart_of_accounts coa
    ON gl.gl_account_id = coa.account_id

WHERE coa.account_type IN ('Expense')
  AND EXTRACT(YEAR FROM TO_DATE(gl.period, 'YYYY-MM')) = ANY(:years);
""")

    budget_query = text("""SELECT
    b.gl_account_id,
    b.period,
    b.gl_account_name as account_name,
    b."Type",
    b.entity_id,
    b.cost_center_id,
    b.profit_center_id,
    b.department_id,
    b.customer_id,
    b.amount as budget_amount
FROM fpna_landing.finance_budget_data b
WHERE b."Type" IN ('Revenue','Expense')
  AND EXTRACT(YEAR FROM TO_DATE(b.period, 'YYYY-MM')) = ANY(:years)""")

    budget_res = await db.execute(budget_query, params)
    budget_rows = budget_res.fetchall()
    budget_data = [dict(row._mapping) for row in budget_rows]
    budget_df = pd.DataFrame(budget_data)

    result = await db.execute(query, params)
    rows = result.fetchall()
    columns = result.keys()

    data = [dict(row._mapping) for row in rows]
    df = pd.DataFrame(data, columns=columns)
    print(df.columns)
    if df.empty:
        return "No data available"

    df_expense = df[
        (df["account_type"] == "Expense") &
        (df["account_name"].isin(opex_accounts))
    ].copy()

    df_budget_expense = budget_df[
        (budget_df["Type"] == "Expense") &
        (budget_df["account_name"].isin(opex_accounts))
    ].copy()

    df_actual_grouped = (
        df_expense
        .groupby(filters, as_index=False)
        .agg(
            actual_operating_expense=(
                "amount_debit",
                lambda x: (x - df_expense.loc[x.index, "amount_credit"]).sum()
            )
        )
    )

    df_budget_grouped = (
        df_budget_expense
        .groupby(filters, as_index=False)
        .agg(budget_operating_expense=("budget_amount", "sum"))
    )

    df_grouped = pd.merge(df_actual_grouped, df_budget_grouped, on=filters, how="left")

    df_grouped["variance"] = df_grouped["actual_operating_expense"] - df_grouped["budget_operating_expense"]

    return df_grouped


@financial_router1.get("/operating_enpense_report/{year}/{currency}", dependencies=[Depends(get_current_company_user)])
async def operating_expenses(
    year: int,
    currency: str,
    db: AsyncSession = Depends(get_db),
    time_filter: Optional[TimeFilterEnum] = None,
    currency_filter: CurrencyFilter = Query(default=CurrencyFilter.actual_value),
    entity: Optional[str] = None,
    department: Optional[str] = None,
    cost_center: Optional[str] = None,
    comparision: Optional[ComparityFilter] = None,
    show_period: bool = Query(False),
    show_entity: bool = Query(False),
    show_department: bool = Query(False),
    show_cost_center: bool = Query(False),
):
    filters = ["period", "account_name"]
    if show_entity:
        filters.append("entity_id")
    if show_department:
        filters.append("department_id")
    if show_cost_center:
        filters.append("cost_center_id")

    all_filters_data = await all_filters_data_display(db=db)
    df_grouped = await expense_calculation(year, db, filters)

    df_grouped["comparision"] = [{} for _ in range(len(df_grouped))]
    print(df_grouped, "dfffffffffffffffffffffffff")


    df_grouped["period"] = pd.to_datetime(df_grouped["period"], format="%Y-%m", errors="coerce")
    selected_year_data = df_grouped[df_grouped["period"].dt.year == year]
    all_periods_data = selected_year_data.to_dict(orient="records")

    c_month = date.today().month
    target_period = f"{year}-{c_month:02d}"
    current_month_df = df_grouped[df_grouped["period"] == target_period]
    current_month_data = current_month_df.to_dict(orient="records")

    all_periods_sorted = sorted(df_grouped["period"].unique())

    dt = datetime.strptime(target_period, "%Y-%m")
    last_month_date = (dt - relativedelta(months=1)).strftime("%Y-%m")

    last_month_df = df_grouped[df_grouped["period"].dt.to_period("M") == last_month_date]
    last_month_data = last_month_df.to_dict(orient="records")

    quarter_start_month = ((c_month - 1) // 3) * 3 + 1
    qtd_periods = [f"{year}-{m:02d}" for m in range(quarter_start_month, c_month + 1)]
    qtd_df = df_grouped[df_grouped["period"].isin(qtd_periods)]
    qtd_data = qtd_df.to_dict(orient="records")

    target_period = pd.to_datetime(target_period)
    year_start = target_period.to_period("Y").start_time
    ytd_df = df_grouped[
        (df_grouped["period"] >= year_start) &
        (df_grouped["period"] <= target_period)
    ]
    ytd_data = ytd_df.to_dict(orient="records")

    time_filter_map = {
        TimeFilterEnum.all_periods: all_periods_data,
        TimeFilterEnum.current_month: current_month_data,
        TimeFilterEnum.last_month: last_month_data,
        TimeFilterEnum.quarter_to_date: qtd_data,
        TimeFilterEnum.year_to_date: ytd_data,
    }
    base_result = time_filter_map.get(time_filter, all_periods_data)

    res = apply_entity_hierarchy_filter(
        data=base_result,
        entity=entity,
        hierarchy=department,
        product=None,
        costcenter=cost_center,
        profitcenter=None,
        region=None,
        customer=None
    )

    res_df = pd.DataFrame(res)

    if len(res_df) == 0:
        return {"message": "No data"}

    compare_cols = ["actual_operating_expense", "budget_operating_expense", "variance"]

    if comparision is not None:
        res_df["period"] = pd.to_datetime(res_df["period"], errors="coerce")

        prev_df = res_df[filters].copy()

        if comparision == "year_on_year":
            prev_df["prev_period"] = (
                res_df["period"] - pd.DateOffset(years=1)
            ).dt.strftime("%Y-%m")

        elif comparision == "month_on_month":
            print("entered============================")
            prev_df["prev_period"] = (
                res_df["period"] - pd.DateOffset(months=1)
            ).dt.strftime("%Y-%m")

        df_grouped["period"] = pd.to_datetime(
            df_grouped["period"], errors="coerce"
        ).dt.strftime("%Y-%m")

        result_dict = df_grouped.to_dict(orient="records")

        result_r = apply_entity_hierarchy_filter(
            result_dict,
            entity=entity,
            hierarchy=None,
            product=None,
            costcenter=cost_center,
            profitcenter=None,
            region=None,
            customer=None
        )

        result = pd.DataFrame(result_r)

        comp_filters = ["prev_period" if c == "period" else c for c in filters]

        lookup_cols = ["actual_operating_expense", "budget_operating_expense", "variance"]

        merged = prev_df.merge(
            result[filters + lookup_cols],
            left_on=comp_filters,
            right_on=filters,
            how="left"
        )

        print(merged, "jjjjjjjjjjjjjjjjjjjjjj")

        merged = merged.rename(columns={"period_x": "period"})
        merged = merged.drop(columns=["period_y", "period"], errors="ignore")
        merged[lookup_cols] = merged[lookup_cols].fillna(0)

        final_merged = merged.drop(columns=["period"], errors="ignore")
        final_merged = final_merged.rename(
            columns={col: f"prev_{col}" for col in lookup_cols}
        )

        final_merged_dict = final_merged.to_dict(orient="records")

        res_df["period"] = res_df["period"].dt.strftime("%Y-%m")
        res_df["comparision"] = final_merged_dict

        f_r = res_df.to_dict(orient="records")

        for row in f_r:
            prev = row.get("comparision", {})
            for field in lookup_cols:
                curr_val = float(row.get(field, 0) or 0)
                prev_val = float(prev.get(f"prev_{field}", 0) or 0)
                prev[f"{field}_diff"] = round(curr_val - prev_val, 2)

    else:
        f_r = res

    km_total_actual    = 0.0
    km_total_budget    = 0.0
    km_total_variance  = 0.0
    km_favorable       = 0
    km_unfavorable     = 0
    for row in f_r:
        km_act = float(row.get("actual_operating_expense", 0) or 0)
        km_bud = float(row.get("budget_operating_expense", 0) or 0)
        km_var = float(row.get("variance", 0) or 0)
        km_total_actual   += km_act
        km_total_budget   += km_bud
        km_total_variance += km_var
        if km_var < 0:
            km_favorable  += 1
        elif km_var > 0:
            km_unfavorable += 1

    km_metrics = {
        "total_budget":           round(km_total_budget,   2),
        "total_actual":           round(km_total_actual,   2),
        "total_variance":         round(km_total_variance, 2),
        "favorable_categories":   km_favorable,
        "unfavorable_categories": km_unfavorable,
        "aiDriver": [
            "AI-powered insights coming soon",
            "AI enhances efficiency through automated financial analysis"
        ]
    }
    km_r        = ["total_budget", "total_actual", "total_variance", "favorable_categories", "unfavorable_categories"]
    key_metrics = apply_smart_currency(km_metrics, currency, currency_filter, km_r)

    if not f_r:
        return {
            "expenses":     [],
            "grand_total":  {},
            "keymetrics":   key_metrics,
            "periods":      [],
            "show_columns": {
                "period":      show_period,
                "entity":      show_entity,
                "department":  show_department,
                "cost_center": show_cost_center,
            },
            "entities":     all_filters_data["entities"],
            "departments":  all_filters_data["departments"],
            "costcenters":  all_filters_data["cost_centers"],
        }

    expense_period_data = {}
    expense_dim_data    = {}

    for row in f_r:
        act_v  = float(row.get("actual_operating_expense", 0) or 0)
        bud_v  = float(row.get("budget_operating_expense", 0) or 0)
        var_v  = float(row.get("variance", 0) or 0)
        p_raw  = row.get("period", "")
        try:
            period_v = pd.Timestamp(p_raw).strftime("%Y-%m")
        except Exception:
            period_v = str(p_raw)[:7]

        key_parts = [row.get("account_name")]
        if show_entity:      key_parts.append(row.get("entity_id"))
        if show_department:  key_parts.append(row.get("department_id"))
        if show_cost_center: key_parts.append(row.get("cost_center_id"))
        key = tuple(key_parts)

        if key not in expense_period_data:
            expense_period_data[key] = {}
            dims = {"account_name": row.get("account_name")}
            if show_entity:      dims["entity_id"]      = row.get("entity_id")
            if show_department:  dims["department_id"]  = row.get("department_id")
            if show_cost_center: dims["cost_center_id"] = row.get("cost_center_id")
            expense_dim_data[key] = dims

        if period_v not in expense_period_data[key]:
            expense_period_data[key][period_v] = {
                "actual":   act_v,
                "budget":   bud_v,
                "variance": var_v,
                "comp_raw": row.get("comparision", {}),
            }
        else:
            ex      = expense_period_data[key][period_v]
            new_act = ex["actual"] + act_v
            new_bud = ex["budget"] + bud_v
            ex["actual"]   = new_act
            ex["budget"]   = new_bud
            ex["variance"] = new_act - new_bud

    periods_available = sorted(set(
        p for pd_data in expense_period_data.values() for p in pd_data if p
    ))

    grand_period_map  = {}
    grand_period_comp = {}

    expenses = []
    for key, p_data in expense_period_data.items():
        dims = expense_dim_data[key]

        months          = {}
        total_actual    = 0.0
        total_budget    = 0.0
        total_variance  = 0.0
        prev_t_actual   = 0.0
        prev_t_budget   = 0.0
        prev_t_variance = 0.0
        has_comp        = False

        for p in periods_available:
            if p in p_data:
                entry    = p_data[p]
                act_v    = entry["actual"]
                bud_v    = entry["budget"]
                var_v    = entry["variance"]
                comp_raw = entry.get("comp_raw", {})

                converted = apply_smart_currency(
                    {"actual": act_v, "budget": bud_v, "variance": var_v},
                    currency, currency_filter, ["actual", "budget", "variance"]
                )
                month_entry = {
                    "actual_operating_expense": converted["actual"],
                    "budget_operating_expense": converted["budget"],
                    "variance":                 converted["variance"],
                }

                if comparision and comp_raw:
                    prev_act_raw = float(comp_raw.get("prev_actual_operating_expense", 0) or 0)
                    prev_bud_raw = float(comp_raw.get("prev_budget_operating_expense", 0) or 0)
                    prev_var_raw = float(comp_raw.get("prev_variance", 0) or 0)
                    diff_act_raw = float(comp_raw.get("actual_operating_expense_diff", 0) or 0)
                    diff_bud_raw = float(comp_raw.get("budget_operating_expense_diff", 0) or 0)
                    diff_var_raw = float(comp_raw.get("variance_diff", 0) or 0)

                    prev_conv = apply_smart_currency(
                        {"actual": prev_act_raw, "budget": prev_bud_raw, "variance": prev_var_raw},
                        currency, currency_filter, ["actual", "budget", "variance"]
                    )
                    diff_conv = apply_smart_currency(
                        {"actual": diff_act_raw, "budget": diff_bud_raw, "variance": diff_var_raw},
                        currency, currency_filter, ["actual", "budget", "variance"]
                    )
                    month_entry["comparison"] = {
                        "prev_actual_operating_expense": prev_conv["actual"],
                        "prev_budget_operating_expense": prev_conv["budget"],
                        "prev_variance":                 prev_conv["variance"],
                        "actual_operating_expense_diff": diff_conv["actual"],
                        "budget_operating_expense_diff": diff_conv["budget"],
                        "variance_diff":                 diff_conv["variance"],
                    }
                    prev_t_actual   += prev_act_raw
                    prev_t_budget   += prev_bud_raw
                    prev_t_variance += prev_var_raw
                    has_comp = True

                    if p not in grand_period_comp:
                        grand_period_comp[p] = {"actual": 0.0, "budget": 0.0, "variance": 0.0}
                    grand_period_comp[p]["actual"]   += prev_act_raw
                    grand_period_comp[p]["budget"]   += prev_bud_raw
                    grand_period_comp[p]["variance"] += prev_var_raw
                else:
                    month_entry["comparison"] = {}

                if show_period:
                    months[p] = month_entry

                total_actual   += act_v
                total_budget   += bud_v
                total_variance += var_v

                if p not in grand_period_map:
                    grand_period_map[p] = {"actual": 0.0, "budget": 0.0}
                grand_period_map[p]["actual"] += act_v
                grand_period_map[p]["budget"] += bud_v

        total_conv = apply_smart_currency(
            {"actual": total_actual, "budget": total_budget, "variance": total_variance},
            currency, currency_filter, ["actual", "budget", "variance"]
        )

        record           = dict(dims)
        record["months"] = months
        record["total"]  = {
            "actual_operating_expense": total_conv["actual"],
            "budget_operating_expense": total_conv["budget"],
            "variance":                 total_conv["variance"],
        }
        record["comparison"] = {}
        if comparision and has_comp:
            prev_t_conv = apply_smart_currency(
                {"actual": prev_t_actual, "budget": prev_t_budget, "variance": prev_t_variance},
                currency, currency_filter, ["actual", "budget", "variance"]
            )
            diff_t_conv = apply_smart_currency(
                {"actual": total_actual - prev_t_actual, "budget": total_budget - prev_t_budget, "variance": total_variance - prev_t_variance},
                currency, currency_filter, ["actual", "budget", "variance"]
            )
            record["comparison"] = {
                "prev_actual_operating_expense": prev_t_conv["actual"],
                "prev_budget_operating_expense": prev_t_conv["budget"],
                "prev_variance":                 prev_t_conv["variance"],
                "actual_operating_expense_diff": diff_t_conv["actual"],
                "budget_operating_expense_diff": diff_t_conv["budget"],
                "variance_diff":                 diff_t_conv["variance"],
            }

        expenses.append(record)

    grand_months         = {}
    gt_total_actual      = 0.0
    gt_total_budget      = 0.0
    gt_prev_total_actual = 0.0
    gt_prev_total_budget = 0.0
    gt_prev_total_var    = 0.0

    for p in periods_available:
        if p in grand_period_map:
            g_act = grand_period_map[p]["actual"]
            g_bud = grand_period_map[p]["budget"]
            g_var = g_act - g_bud

            conv_gm = apply_smart_currency(
                {"actual": g_act, "budget": g_bud, "variance": g_var},
                currency, currency_filter, ["actual", "budget", "variance"]
            )
            gm_entry = {
                "actual_operating_expense": conv_gm["actual"],
                "budget_operating_expense": conv_gm["budget"],
                "variance":                 conv_gm["variance"],
            }

            if comparision and p in grand_period_comp:
                gc           = grand_period_comp[p]
                gp_act       = gc["actual"]
                gp_bud       = gc["budget"]
                gp_var       = gc["variance"]
                prev_gm_conv = apply_smart_currency(
                    {"actual": gp_act, "budget": gp_bud, "variance": gp_var},
                    currency, currency_filter, ["actual", "budget", "variance"]
                )
                diff_gm_conv = apply_smart_currency(
                    {"actual": g_act - gp_act, "budget": g_bud - gp_bud, "variance": g_var - gp_var},
                    currency, currency_filter, ["actual", "budget", "variance"]
                )
                gm_entry["comparison"] = {
                    "prev_actual_operating_expense": prev_gm_conv["actual"],
                    "prev_budget_operating_expense": prev_gm_conv["budget"],
                    "prev_variance":                 prev_gm_conv["variance"],
                    "actual_operating_expense_diff": diff_gm_conv["actual"],
                    "budget_operating_expense_diff": diff_gm_conv["budget"],
                    "variance_diff":                 diff_gm_conv["variance"],
                }
                gt_prev_total_actual += gp_act
                gt_prev_total_budget += gp_bud
                gt_prev_total_var    += gp_var
            else:
                gm_entry["comparison"] = {}

            if show_period:
                grand_months[p] = gm_entry

            gt_total_actual += g_act
            gt_total_budget += g_bud

    gt_total_variance = gt_total_actual - gt_total_budget

    gt_total_conv = apply_smart_currency(
        {"actual": gt_total_actual, "budget": gt_total_budget, "variance": gt_total_variance},
        currency, currency_filter, ["actual", "budget", "variance"]
    )

    grand_total = {
        "account_name": "Grand Total",
        "months":       grand_months,
        "total": {
            "actual_operating_expense": gt_total_conv["actual"],
            "budget_operating_expense": gt_total_conv["budget"],
            "variance":                 gt_total_conv["variance"],
        },
        "comparison": {},
    }

    if comparision and grand_period_comp:
        gt_prev_conv = apply_smart_currency(
            {"actual": gt_prev_total_actual, "budget": gt_prev_total_budget, "variance": gt_prev_total_var},
            currency, currency_filter, ["actual", "budget", "variance"]
        )
        gt_diff_conv = apply_smart_currency(
            {"actual": gt_total_actual - gt_prev_total_actual, "budget": gt_total_budget - gt_prev_total_budget, "variance": gt_total_variance - gt_prev_total_var},
            currency, currency_filter, ["actual", "budget", "variance"]
        )
        grand_total["comparison"] = {
            "prev_actual_operating_expense": gt_prev_conv["actual"],
            "prev_budget_operating_expense": gt_prev_conv["budget"],
            "prev_variance":                 gt_prev_conv["variance"],
            "actual_operating_expense_diff": gt_diff_conv["actual"],
            "budget_operating_expense_diff": gt_diff_conv["budget"],
            "variance_diff":                 gt_diff_conv["variance"],
        }

    return {
        "expenses":     expenses,
        "grand_total":  grand_total,
        "keymetrics":   key_metrics,
        "periods":      periods_available,
        "show_columns": {
            "period":      show_period,
            "entity":      show_entity,
            "department":  show_department,
            "cost_center": show_cost_center,
        },
        "entities":     all_filters_data["entities"],
        "departments":  all_filters_data["departments"],
        "costcenters":  all_filters_data["cost_centers"],
    }


@financial_router1.get("/operating_expense_view_more/{year}/{currency}", dependencies=[Depends(get_current_company_user)])
async def operating_expenses_view_more(
    year: int,
    currency: str,
    db: AsyncSession = Depends(get_db),
    time_filter: Optional[TimeFilterEnum] = None,
    currency_filter: CurrencyFilter = Query(default=CurrencyFilter.actual_value),
    entity: Optional[str] = None,
    department: Optional[str] = None,
    cost_center: Optional[str] = None,
    comparision: Optional[ComparityFilter] = None,
    show_period: bool = Query(False),
    show_entity: bool = Query(False),
    show_department: bool = Query(False),
    show_cost_center: bool = Query(False),
):
    filters = ["period", "account_name"]
    if show_entity:
        filters.append("entity_id")
    if show_department:
        filters.append("department_id")
    if show_cost_center:
        filters.append("cost_center_id")

    all_filters_data = await all_filters_data_display(db=db)
    df_grouped = await expense_calculation(year, db, filters)

    if isinstance(df_grouped, str):
        return {"message": "No data available"}

    df_grouped["comparision"] = [{} for _ in range(len(df_grouped))]

    df_grouped["period"] = pd.to_datetime(df_grouped["period"], format="%Y-%m", errors="coerce")
    selected_year_data = df_grouped[df_grouped["period"].dt.year == year].copy()

    c_month = date.today().month
    target_period = f"{year}-{c_month:02d}"

    dt = datetime.strptime(target_period, "%Y-%m")
    last_month_date = (dt - relativedelta(months=1)).strftime("%Y-%m")

    current_month_df = selected_year_data[selected_year_data["period"].dt.strftime("%Y-%m") == target_period]
    last_month_df = selected_year_data[selected_year_data["period"].dt.strftime("%Y-%m") == last_month_date]

    quarter_start_month = ((c_month - 1) // 3) * 3 + 1
    qtd_periods = [f"{year}-{m:02d}" for m in range(quarter_start_month, c_month + 1)]
    qtd_df = selected_year_data[selected_year_data["period"].dt.strftime("%Y-%m").isin(qtd_periods)]

    target_period_dt = pd.to_datetime(target_period)
    year_start = target_period_dt.to_period("Y").start_time
    ytd_df = selected_year_data[
        (selected_year_data["period"] >= year_start) &
        (selected_year_data["period"] <= target_period_dt)
    ]

    time_filter_map = {
        TimeFilterEnum.all_periods: selected_year_data,
        TimeFilterEnum.current_month: current_month_df,
        TimeFilterEnum.last_month: last_month_df,
        TimeFilterEnum.quarter_to_date: qtd_df,
        TimeFilterEnum.year_to_date: ytd_df,
    }
    base_df = time_filter_map.get(time_filter, selected_year_data).copy()

    base_df["period"] = base_df["period"].dt.strftime("%Y-%m")
    base_records = base_df.to_dict(orient="records")

    res = apply_entity_hierarchy_filter(
        data=base_records,
        entity=entity,
        hierarchy=department,
        product=None,
        costcenter=cost_center,
        profitcenter=None,
        region=None,
        customer=None
    )

    if not res:
        return {"message": "No data"}

    res_df = pd.DataFrame(res)

    lookup_cols = ["actual_operating_expense", "budget_operating_expense", "variance"]

    if comparision is not None:
        res_df["period"] = pd.to_datetime(res_df["period"], errors="coerce")
        prev_df = res_df[filters].copy()

        if comparision == "year_on_year":
            prev_df["prev_period"] = (
                res_df["period"] - pd.DateOffset(years=1)
            ).dt.strftime("%Y-%m")
        elif comparision == "month_on_month":
            print("entered============================")
            prev_df["prev_period"] = (
                res_df["period"] - pd.DateOffset(months=1)
            ).dt.strftime("%Y-%m")

        df_grouped["period"] = pd.to_datetime(
            df_grouped["period"], errors="coerce"
        ).dt.strftime("%Y-%m")

        result_dict = df_grouped.to_dict(orient="records")

        result_r = apply_entity_hierarchy_filter(
            result_dict,
            entity=entity,
            hierarchy=None,
            product=None,
            costcenter=cost_center,
            profitcenter=None,
            region=None,
            customer=None
        )

        result_ref = pd.DataFrame(result_r)

        comp_filters = ["prev_period" if c == "period" else c for c in filters]

        merged = prev_df.merge(
            result_ref[filters + lookup_cols],
            left_on=comp_filters,
            right_on=filters,
            how="left"
        )

        print(merged, "jjjjjjjjjjjjjjjjjjjjjj")

        merged = merged.rename(columns={"period_x": "period"})
        merged = merged.drop(columns=["period_y", "period"], errors="ignore")
        merged[lookup_cols] = merged[lookup_cols].fillna(0)

        final_merged = merged.drop(columns=["period"], errors="ignore")
        final_merged = final_merged.rename(
            columns={col: f"prev_{col}" for col in lookup_cols}
        )

        final_merged_dict = final_merged.to_dict(orient="records")

        res_df["period"] = res_df["period"].dt.strftime("%Y-%m")
        res_df["comparision"] = final_merged_dict

        f_r = res_df.to_dict(orient="records")

        for row in f_r:
            prev = row.get("comparision", {})
            for field in lookup_cols:
                curr_val = float(row.get(field, 0) or 0)
                prev_val = float(prev.get(f"prev_{field}", 0) or 0)
                prev[f"{field}_diff"] = round(curr_val - prev_val, 2)
    else:
        f_r = res

    m_f = ["actual_operating_expense", "budget_operating_expense", "variance"]
    f_r_converted = apply_smart_currency(f_r, currency, currency_filter, m_f)

    if comparision is not None:
        for row in f_r_converted:
            prev = row.get("comparision", {})
            if not prev:
                continue

            prev_money_fields = ["prev_actual_operating_expense", "prev_budget_operating_expense", "prev_variance"]
            diff_fields = ["actual_operating_expense_diff", "budget_operating_expense_diff", "variance_diff"]

            prev_raw = {k: float(prev.get(k, 0) or 0) for k in prev_money_fields}
            prev_converted = apply_smart_currency(prev_raw, currency, currency_filter, prev_money_fields)
            for k in prev_money_fields:
                prev[k] = prev_converted[k]

            diff_raw = {k: float(prev.get(k, 0) or 0) for k in diff_fields}
            diff_converted = apply_smart_currency(diff_raw, currency, currency_filter, diff_fields)
            for k in diff_fields:
                prev[k] = diff_converted[k]

    if not f_r_converted:
        return {"message": "No data available"}

    flat_df = pd.DataFrame(f_r_converted)
    all_periods = sorted(flat_df["period"].unique().tolist())

    def build_pivot_section(df, metric, all_periods, comparision):
        account_names = df["account_name"].unique().tolist()
        rows = []

        for account in account_names:
            acc_df = df[df["account_name"] == account]
            entry = {"account_name": account}
            row_total = 0.0

            for p in all_periods:
                period_rows = acc_df[acc_df["period"] == p]
                if period_rows.empty:
                    entry[p] = None
                else:
                    val = period_rows[metric].sum()
                    entry[p] = round(float(val), 2)
                    row_total += float(val)

            entry["total"] = round(row_total, 2)

            if comparision is not None:
                comp_entry = {}
                prev_metric = f"prev_{metric}"
                diff_metric = f"{metric}_diff"

                prev_row_total = 0.0
                diff_row_total = 0.0

                for p in all_periods:
                    period_rows = acc_df[acc_df["period"] == p]
                    if period_rows.empty:
                        comp_entry[p] = {
                            prev_metric: None,
                            diff_metric: None
                        }
                    else:
                        comp_list = period_rows["comparision"].tolist()
                        prev_val = sum(float(c.get(prev_metric, 0) or 0) for c in comp_list if isinstance(c, dict))
                        diff_val = sum(float(c.get(diff_metric, 0) or 0) for c in comp_list if isinstance(c, dict))
                        comp_entry[p] = {
                            prev_metric: round(prev_val, 2),
                            diff_metric: round(diff_val, 2)
                        }
                        prev_row_total += prev_val
                        diff_row_total += diff_val

                comp_entry["total"] = {
                    prev_metric: round(prev_row_total, 2),
                    diff_metric: round(diff_row_total, 2)
                }
                entry["comparision"] = comp_entry

            rows.append(entry)

        grand_total = {"total": 0.0}
        for p in all_periods:
            col_sum = df[df["period"] == p][metric].sum()
            grand_total[p] = round(float(col_sum), 2) if not pd.isna(col_sum) else None
            if not pd.isna(col_sum):
                grand_total["total"] += float(col_sum)
        grand_total["total"] = round(grand_total["total"], 2)

        if comparision is not None:
            prev_metric = f"prev_{metric}"
            diff_metric = f"{metric}_diff"
            gt_comp = {}
            gt_prev_total = 0.0
            gt_diff_total = 0.0

            for p in all_periods:
                period_rows = df[df["period"] == p]
                if period_rows.empty:
                    gt_comp[p] = {prev_metric: None, diff_metric: None}
                else:
                    comp_list = period_rows["comparision"].tolist()
                    prev_val = sum(float(c.get(prev_metric, 0) or 0) for c in comp_list if isinstance(c, dict))
                    diff_val = sum(float(c.get(diff_metric, 0) or 0) for c in comp_list if isinstance(c, dict))
                    gt_comp[p] = {
                        prev_metric: round(prev_val, 2),
                        diff_metric: round(diff_val, 2)
                    }
                    gt_prev_total += prev_val
                    gt_diff_total += diff_val

            gt_comp["total"] = {
                prev_metric: round(gt_prev_total, 2),
                diff_metric: round(gt_diff_total, 2)
            }
            grand_total["comparision"] = gt_comp

        return {"rows": rows, "grand_total": grand_total}

    result = {}
    for metric in ["actual_operating_expense", "budget_operating_expense", "variance"]:
        result[metric] = build_pivot_section(flat_df, metric, all_periods, comparision)

    result["periods"] = all_periods
    result["entities"] = all_filters_data["entities"]
    result["departments"] = all_filters_data["departments"]
    result["costcenters"] = all_filters_data["cost_centers"]

    return result



#############################################################################################################
#############################################################################################################
#############################################################################################################
#capital expenditure report

@financial_router1.get("/capitalexpenditure_report/{year}/{currency}" ,dependencies=[Depends(get_current_company_user)])
async def  capitalexpenditure(year:int, currency:str, db: AsyncSession = Depends(get_db),
                    #   currency_filter :Optional[CurrencyFilter] = None,
                      currency_filter: CurrencyFilter =Query(default = CurrencyFilter.actual_value),
                      filters: Optional[ViewMoreFilter] = None ,
                      entity:Optional[str] = None , 
                      department: Optional[str] = None):
    params = {"year_pattern": f"{year}-%"}
    query = text("""SELECT
    -- General Ledger
    gl.transaction_id,
    gl.period,
    gl.gl_account_id,
    gl.cost_center_id,
    gl.amount_debit,

    -- Chart of Accounts
    coa.account_id,
    coa.account_name,
    coa.account_type, 
                 
    --entities
    e.entity_id,
    e.entity_name,

    -- Cost Center
    cc.name,
    cc.department_id,

    -- Department
    d.department_id,
    d.department_name,

    -- Budget Input
    bi.budget_id,
    bi.driver_type,
    bi.driver_value,

    -- Budget
    b.gl_account_id,
    b.period,
    b.amount, 
    -- Product
    p.product_id,
    p.product_name,
    p.profit_center_id

FROM fpna_landing.finance_general_ledger_transactions gl

JOIN fpna_landing.finance_chart_of_accounts coa
    ON gl.gl_account_id = coa.account_id

JOIN fpna_landing.finance_cost_centers cc
    ON gl.cost_center_id = cc.cost_center_id

JOIN fpna_landing.finance_departments d
    ON cc.department_id = d.department_id

LEFT JOIN fpna_landing.finance_budget_input bi
    ON bi.cost_center_id = cc.cost_center_id
   AND bi.account_id = gl.gl_account_id

LEFT JOIN fpna_landing.finance_budget b
    ON b.budget_id = bi.budget_id
   AND b.gl_account_id = gl.gl_account_id
   AND b.period = gl.period 
LEFT JOIN fpna_landing.crm_products p
    ON gl.profit_center_id = p.profit_center_id::bigint 
LEFT JOIN fpna_landing.finance_entities e
  ON gl.entity_id = e.entity_id::bigint

WHERE coa.account_type = 'Asset'
  AND coa.account_name = 'Fixed Assets' AND gl.period LIKE :year_pattern; """)
    

    result = await db.execute(query , params)
    rows = result.fetchall()
    columns = result.keys()
    df = pd.DataFrame(rows, columns=columns)
    print(df)
    print(df.columns) 

@financial_router1.get("/marketing_spend_efficiency_report/{year}/{currency}" ,dependencies=[Depends(get_current_company_user)])
async def  marketingspend_efficiency(year:int,currency:str, db: AsyncSession = Depends(get_db),
                      currency_filter: CurrencyFilter =Query(default = CurrencyFilter.actual_value),
                      filters: Optional[ViewMoreFilter] = None ,
                      entity:Optional[str] = None , 
                      region: Optional[str] = None):
    params = {"year_pattern": f"{year}-%"} 

    query = text("""SELECT
    m.channel,
    m.period,

    -- already available in marketing_spend table
    m.spend_amount,
    m.leads_generated,
    --m.cost_per_lead,

    -- budget from finance
    fb.amount AS budget_amount

FROM fpna_landing.crm_marketing_spend m

LEFT JOIN fpna_landing.finance_budget fb
       ON fb.period = m.period

LEFT JOIN fpna_landing.finance_chart_of_accounts coa
       ON fb.gl_account_id = coa.account_id
      AND coa.account_type = 'Expense'
      AND LOWER(coa.account_name) LIKE '%marketing%'
WHERE m.period Like :year_pattern """)
    
    result = await db.execute(query, params)
    rows = result.fetchall()
    columns = result.keys()

    data =  [dict(row._mapping) for row in rows]
    # print(data)
    df = pd.DataFrame(data , columns= columns)
    print(df.columns)
    print(df)
    
 