from sqlalchemy import text 
from src.core.database import get_db 
from fastapi import APIRouter,Depends 
import pandas as pd
from sqlalchemy.ext.asyncio import AsyncSession
from .schemas import DashboardTimeFilter 
from fastapi import Query 
from typing import Optional 
import numpy as np
from .report import apply_entity_hierarchy_filter , all_filters_data_display,apply_smart_currency
from decimal import Decimal 
from datetime import datetime , date
from src.forecasting.schemas import QuarterEnum

def convert_decimal_to_float(obj):
    if isinstance(obj, Decimal):
        return float(obj)

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

    if isinstance(obj, list):
        return [convert_decimal_to_float(i) for i in obj]

    return obj

import math
from decimal import Decimal
import pandas as pd
import numpy as np 


def round_records(data, keys, decimals=2):
    if not isinstance(data, list):
        return data

    for row in data:
        for k in keys:
            if k in row and row[k] is not None:
                row[k] = round(float(row[k]), decimals)
    return data


def to_json(df):
    df = df.copy()
    df["period"] = df["period"].astype(str)
    return df.to_dict(orient="records") 

def normalize_period(df, col):
    df[col] = pd.to_datetime(df[col], errors="coerce").dt.to_period("M")
    return df

# async def dashboard_metrics(year,currency,currency_filter, entity, time_filter, db , actual_vs_budget):
#     import pandas as pd
#     import numpy as np
#     from sqlalchemy import text 
#     all_filters_data =  await all_filters_data_display(db=db)
#     filters = ["period"]
#     if entity:
#         filters.append("entity_id")

#     today = pd.Timestamp.today()
#     month = today.month
#     quarter_start_month = ((month - 1) // 3) * 3 + 1

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

#     # ======================================================
#     #                   REVENUE
#     # ======================================================


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

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

#     budget_query = text("""select period ,gl_account_id, "Type", gl_account_name,entity_id, department_id, amount from fpna_landing.finance_budget_data WHERE period LIKE :year_pattern""")
#     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)
#     budget_df = budget_df.rename(columns = {"Type" : "account_type" , "gl_account_name":"account_name" , "amount":"budget_amount"}) 

#     budget_result =   (budget_df.groupby(filters,as_index= False).apply(lambda x: pd.Series({'budget_revenue': (
#                 (x['account_type'] == 'Revenue')
#                 * x['budget_amount']).sum(),
#             'budget_cogs': (
#                 x['account_name'].isin(cogs_accounts)
#                 * x['budget_amount']
#             ).sum(),

#             'budget_opex': (
#                 x['account_name'].isin(opex_accounts)
#                 * x['budget_amount']
#             ).sum()})).reset_index()) 



#     revenue_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,
#     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 ('Revenue', 'Expense')
#   AND gl.period LIKE :year_pattern;

# """)
#     rows = (await db.execute(revenue_query, params)).mappings().all()
#     df = pd.DataFrame(rows)

    
 
#     result = (
#         df.groupby(filters ,as_index=False)
#         .apply(lambda x: pd.Series({

#             # ---------- ACTUAL METRICS ----------
#             'revenue': (
#                 (x['account_type'] == 'Revenue')
#                 * (x['amount_credit'] - x['amount_debit'])
#             ).sum(),

#             'cogs_accounts': (
#                 x['account_name'].isin(cogs_accounts)
#                 * (x['amount_debit'] - x['amount_credit'])
#             ).sum(),

#             'op_expenditure': (
#                 x['account_name'].isin(opex_accounts)
#                 * (x['amount_debit'] - x['amount_credit'])
#             ).sum()})))
    
#     result["budget_revenue"] = budget_result["budget_revenue"]
#     result["budget_cogs"]= budget_result["budget_cogs"]
#     result["budget_opex"]= budget_result["budget_opex"] 
#     # result["budget_expenses"] = budget_df["budget_cogs"] + budget_df["budget_opex"] 
#     # result["actual_expense"] = result["cogs_accounts"] + result["op_expenditure"]


#     actual_vs_budget_revenue = result[filters + ["revenue", "budget_revenue"] ]
#     actual_vs_budget_revenue = actual_vs_budget_revenue.rename(columns = {"revenue":"actual_revenue"})
#     actual_vs_budget_revenue = actual_vs_budget_revenue.to_dict(orient="records")
#     actual_vs_budget_cogs    = result[filters +["cogs_accounts", "budget_cogs"]]
#     actual_vs_budget_cogs = actual_vs_budget_cogs.rename(columns={"cogs_accounts": "actual_cogs"})
#     actual_vs_budget_cogs_dict = actual_vs_budget_cogs.to_dict(orient = "records") 

#     actual_vs_budget_opex    = result[filters + ["op_expenditure" , "budget_opex"]]
#     actual_vs_budget_opex = actual_vs_budget_opex.rename(columns={"op_expenditure": "actual_operating_expense"})
#     actual_vs_budget_opex_dict = actual_vs_budget_opex.to_dict(orient = "records") 

#     actual_vs_budget_monthly = [{"revenue":actual_vs_budget_revenue , "cogs":actual_vs_budget_cogs_dict ,
#                         "operating_expense":actual_vs_budget_opex_dict}]
    
#     actual_vs_budget_monthly = apply_smart_currency(actual_vs_budget_monthly,currency ,currency_filter ,
#                                                     money_fields=["actual_revenue", "budget_revenue","actual_operating_expense","budget_opex","actual_cogs","budget_cogs"] )
    
#     print(actual_vs_budget_monthly , "lllllllllllllllllllll=========================")


    
  

#     revenue_monthly = result[filters+ ["revenue"]].to_dict(orient="records")
    
#     revenue_monthly = apply_entity_hierarchy_filter(data = revenue_monthly,entity=entity,hierarchy=None,product=None,costcenter=None,
#                                                profitcenter=None,region=None,customer=None,version=None,business_unit = None)
#     Total_Revenue =  sum(item["revenue"] for item in revenue_monthly)
#     # revenue_monthly.append({"chart_type" : "bar_chart" , "Position" :1 , "enable_or_disable" : True})
#     revenue_monthly = apply_smart_currency(revenue_monthly,currency ,currency_filter,money_fields=["revenue"] )


#     # total_expense = (result["cogs_accounts"] + result["op_expenditure"])
#     # print(total_expense , "error ")
#     # total_expense_monthly = total_expense.to_dict(orient = "records") 
#     total_expense_monthly = (
#     result.assign(total_expense = result["cogs_accounts"] + result["op_expenditure"])
#           [filters + ["total_expense"]]
#           .to_dict(orient="records"))


#     Expense = sum(item["total_expense"] for item in total_expense_monthly)

#     # total_expense_monthly.append({"chart_type" : "line_chart" , "Position" :2 , "enable_or_disable" : True})
#     total_expense_monthly = apply_smart_currency(total_expense_monthly, currency, currency_filter,
#                                                 money_fields=["total_expense"])


#     gp_monthly = (
#     result.assign(gross_profit = result["revenue"] - result["cogs_accounts"])
#           [filters +["gross_profit"]]
#           .to_dict(orient="records")) 
#     Gross_Profit = sum(item["gross_profit"] for item in gp_monthly)

#     # gp_monthly.append({"chart_type" : "pie_chart" , "Position" :3 , "enable_or_disable" : True})

#     gp_monthly = apply_smart_currency(gp_monthly , currency, currency_filter, money_fields=["gross_profit"])

    
#     np_monthly = (
#     result.assign(net_profit = result["revenue"] - (result["cogs_accounts"] + result["op_expenditure"]))
#           [filters + ["net_profit"]].to_dict(orient="records"))
#     Net_Profit = sum(item["net_profit"] for item in np_monthly)
#     # np_monthly.append({"chart_type" : "Doughnut_chart" , "Position" :4, "enable_or_disable" : True})
#     np_monthly = apply_smart_currency(np_monthly , currency , currency_filter , money_fields = ["net_profit"])


#     c_month = date.today().month
#     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 = result[result["period"].isin(qtd_periods)]
#     qtd_res = qtd_df.to_dict(orient = "records") 

#     qtd_revenue = qtd_df[filters + ["revenue"]].to_dict(orient="records") 
#     # qtd_revenue.append({"chart_type" : "bar_chart" , "Position" :1 , "enable_or_disable" : True})
#     qtd_revenue=apply_smart_currency(qtd_revenue,currency , currency_filter, money_fields=["revenue"])

#     expense_qtd = (
#     qtd_df.assign(total_expense = qtd_df["cogs_accounts"] + qtd_df["op_expenditure"])
#           [filters + ["total_expense"]]
#           .to_dict(orient="records")) 
#     # expense_qtd.append({"chart_type" : "line_chart" , "Position" :2 , "enable_or_disable" : True})
#     expense_qtd = apply_smart_currency(expense_qtd ,currency , currency_filter , money_fields=["total_expense"])
    
#     gp_qtd = (
#     qtd_df.assign(gross_profit = qtd_df["revenue"] - qtd_df["cogs_accounts"])
#           [filters + ["gross_profit"]]
#           .to_dict(orient="records"))
#     # gp_qtd.append({"chart_type" : "pie_chart" , "Position" :3 , "enable_or_disable" : True})
#     gp_qtd = apply_smart_currency(gp_qtd, currency, currency_filter, money_fields=["gross_profit"])
    
#     np_qtd = (
#     qtd_df.assign(net_profit = qtd_df["revenue"] - (qtd_df["cogs_accounts"] + qtd_df["op_expenditure"]))
#           [filters+["net_profit"]].to_dict(orient="records"))
#     # np_qtd.append({"chart_type" : "Doughnut_chart" , "Position" :4 , "enable_or_disable" : True})
#     np_qtd = apply_smart_currency(np_qtd , currency, currency_filter, money_fields = ["net_profit"])


#     qtd_actual_vs_budget_revenue = qtd_df[filters + ["revenue", "budget_revenue"]].to_dict(orient = "records")
#     qtd_actual_vs_budget_cogs = qtd_df[filters +["cogs_accounts", "budget_cogs"]]
#     qtd_actual_vs_budget_cogs = qtd_actual_vs_budget_cogs.rename(columns={"cogs_accounts": "actual_cogs"})
#     qtd_actual_vs_budget_cogs_dict = qtd_actual_vs_budget_cogs.to_dict(orient = "records")
#     qtd_actual_vs_budget_opex = qtd_df[filters + ["op_expenditure" , "budget_opex"]]
#     qtd_actual_vs_budget_opex = qtd_actual_vs_budget_opex.rename(columns = {"op_expenditure" :"operating_expense" })

#     qtd_actual_vs_budget = [{"revenue":qtd_actual_vs_budget_revenue , "cogs":qtd_actual_vs_budget_cogs_dict ,
#                         "operating_expense":qtd_actual_vs_budget_opex}]
    
#     qtd_actual_vs_budget = apply_smart_currency(qtd_actual_vs_budget,currency ,currency_filter ,
#                                                     money_fields=["revenue", "budget_revenue","operating_expense","budget_opex","actual_cogs","budget_cogs"] )

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

#     ytd_df = result[result["period"].dt.year == year]
#     ytd_res = ytd_df.to_dict(orient = "records") 

#     # print(ytd_df , "ytd_dfffffffffffffff")

#     ytd_revenue = ytd_df[filters + ["revenue"]].to_dict(orient="records")
#     # ytd_revenue.append({"chart_type" : "bar_chart" , "Position" :1, "enable_or_disable" : True})
#     ytd_revenue = apply_smart_currency(ytd_revenue , currency, currency_filter , money_fields = ["revenue"])

#     expense_ytd = (
#     ytd_df.assign(total_expense = ytd_df["cogs_accounts"] + ytd_df["op_expenditure"])
#           [filters + ["total_expense"]]
#           .to_dict(orient="records")) 
#     # expense_ytd.append({"chart_type" : "line_chart" , "Position" :2 , "enable_or_disable" : True})
#     expense_ytd = apply_smart_currency(expense_ytd , currency, currency_filter , money_fields=["total_expense"])
    
#     gp_ytd = (
#     ytd_df.assign(gross_profit = ytd_df["revenue"] - ytd_df["cogs_accounts"])
#           [filters  +["gross_profit"]]
#           .to_dict(orient="records"))
#     # gp_ytd.append({"chart_type" : "pie_chart" , "Position" :3 , "enable_or_disable" : True})
#     gp_ytd = apply_smart_currency(gp_ytd , currency , currency_filter, money_fields=["gross_profit"])
    
#     np_ytd = (
#     ytd_df.assign(net_profit = ytd_df["revenue"] - (ytd_df["cogs_accounts"] + ytd_df["op_expenditure"]))
#           [filters + ["net_profit"]].to_dict(orient="records"))
#     # np_ytd.append({"chart_type" : "Doughnut_chart" , "Position" :4 , "enable_or_disable" : True})
#     np_ytd = apply_smart_currency(np_ytd , currency, currency_filter, money_fields=["net_profit"])

#     ytd_actual_vs_budget_revenue = ytd_df[filters + ["revenue", "budget_revenue"]].to_dict(orient = "records")
#     ytd_actual_vs_budget_cogs = ytd_df[filters +["cogs_accounts", "budget_cogs"]]
#     ytd_actual_vs_budget_cogs = ytd_actual_vs_budget_cogs.rename(columns = {"cogs_accounts" : "actual_cogs"})
#     ytd_actual_vs_budget_cogs = ytd_actual_vs_budget_cogs.to_dict(orient = "records")
#     ytd_actual_vs_budget_opex = ytd_df[filters + ["op_expenditure" , "budget_opex"]]
#     ytd_actual_vs_budget_opex = ytd_actual_vs_budget_opex.rename(columns ={"op_expenditure":"operating_expense"})

#     ytd_actual_vs_budget = [{"revenue":ytd_actual_vs_budget_revenue , "cogs":ytd_actual_vs_budget_cogs ,
#                         "operating_expense":ytd_actual_vs_budget_opex}]
    
#     ytd_actual_vs_budget = apply_smart_currency(ytd_actual_vs_budget,currency ,currency_filter ,
#                                                     money_fields=["revenue", "budget_revenue","operating_expense","budget_opex","actual_cogs","budget_cogs"] )

#     # ======================================================
#     #                       CASHFLOW
#     # ======================================================
#     cashflow_query = text("""
#         SELECT gl.period, coa.account_type, gl.amount_credit, gl.amount_debit,gl.entity_id
#         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 fpna_landing.finance_entities e
#         --ON gl.entity_id = e.entity_id::BIGINT
#         WHERE coa.account_type = 'Equity' AND gl.period LIKE :year_pattern
#     """)
#     cf = pd.DataFrame((await db.execute(cashflow_query, params)).mappings().all())
#     if len(cf) == 0:
#         cashflow = 0
#         monthly_cf = []
#         qtd_res = []
#         ytd_res = []
#     else:
#         cf["period"] = pd.to_datetime(cf["period"]).dt.to_period("M").astype(str)
#         cf["amount_credit"] = cf["amount_credit"].astype(float)
#         cf["amount_debit"] = cf["amount_debit"].astype(float)

#         financing_cf = (
#             cf.groupby(filters, as_index=False)
#             .apply(lambda x: (x["amount_credit"] - x["amount_debit"]).sum())
#             .reset_index(name="financing_cash_flow")
#         )

#         cashflow = financing_cf["financing_cash_flow"].sum()
#         monthly_cf = financing_cf.to_dict("records")
#         # monthly_cf.append({"chart_type" : "bubble_chart" , "Position" :5, "enable_or_disable" : True})
#         qtd_res = financing_cf[financing_cf["period"].isin(qtd_df["period"])].to_dict("records")
#         # qtd_res.append({"chart_type" : "bubble_chart" , "Position" :5, "enable_or_disable" : True})
#         ytd_res = financing_cf[financing_cf["period"].isin(ytd_df["period"])].to_dict("records")
#         # ytd_res.append({"chart_type" : "bubble_chart" , "Position" :2 , "enable_or_disable" : True})


#     # =======================================================
#     #                    Head Count
#     # =======================================================

#     # headcount_query = text("""select headcount from fpna_landing.hr_headcount_history 
#     #                        Where period LIKE :year_pattern""")
  
#     headcount_query = text("""
#         SELECT COUNT(*) AS total_employees
#         FROM fpna_landing.hr_employees
#         WHERE hire_date >= make_date(:year, 1, 1)
#         AND hire_date <  make_date(:year + 1, 1, 1)
#         """)

#     result = await db.execute(headcount_query, {"year": year})
#     count = result.scalar()

#     # h_res= await db.execute(headcount_query , params)
#     # headcount_rows  = h_res.fetchall() 
#     # columns = h_res.keys()
#     # h_df = pd.DataFrame(headcount_rows , columns= columns)

#     # sum_of_headcount = h_df["headcount"].sum()

    

#     print(count, "hres------------------------")


#     # headcount_query = text(headcount_sql = """
#     # WITH target AS (
#     #     SELECT COALESCE(SUM(headcount), 0) AS target_headcount_end
#     #     FROM fpna_landing.hr_headcount_history
#     #     WHERE period = :period
#     # ),
#     # projected AS (
#     #     SELECT COUNT(DISTINCT employee_id) AS projected_headcount_end
#     #     FROM fpna_landing.hr_employees
#     #     WHERE hire_date <= :end_date
#     #       AND (
#     #             termination_date IS NULL
#     #             OR termination_date > :end_date
#     #           )
#     # )
#     # SELECT
#     #     target_headcount_end,
#     #     projected_headcount_end,
#     #     target_headcount_end - projected_headcount_end
#     #         AS variance_target_vs_projected
#     # FROM target, projected;
#     # """)
#     # headcount = pd.DataFrame((await db.execute(headcount_query, params)).mappings().all())
    
        

#     # ======================================================
#     #                      API OUTPUT
#     # ======================================================
#     monthly_data = {
#         "Revenue": revenue_monthly ,
#         "Grossprofit": gp_monthly ,
#         "expense":total_expense_monthly ,
#         "netprofit":np_monthly,
#         "cashflow": round_records(monthly_cf, ["financing_cash_flow"]) if isinstance(monthly_cf, list) and len(monthly_cf) >0 else [],
#         "actual_vs_budget" : actual_vs_budget_monthly }
#     quarterly_data = {
#         "Revenue": qtd_revenue,
#         "Grossprofit": gp_qtd ,
#         "expense":expense_qtd,
#         "netprofit": np_qtd ,
#         "cashflow": round_records(qtd_res , ["financing_cash_flow"]) if isinstance(qtd_res, list) and len(qtd_res) >0 else [],
#         "actual_vs_budget" : qtd_actual_vs_budget
#     }

#     yearly_data = {
#         "Revenue":ytd_revenue,
#         "Grossprofit": gp_ytd,
#         "expense": expense_ytd,
#         "netprofit":np_ytd,
#         "cashflow": round_records(ytd_res,["financing_cash_flow"]) if isinstance(ytd_res, list) and len(ytd_res) >0 else [],
#         "actual_vs_budget" : ytd_actual_vs_budget
#     }

#     dashboard_metrics = {
#         "Revenue": round(Total_Revenue , 2),
#         "Grossprofit": round(Gross_Profit , 2) ,
#         "NetProfit": round(Net_Profit,2),
#         "Expense": round(Expense,2),
#         "cashflow": round(cashflow,2),
#         "headcount" : count
#     }
#     dashboard_metrics = apply_smart_currency(dashboard_metrics, currency, currency_filter,
#                                              money_fields=["Revenue","Grossprofit","NetProfit","Expense"])

#     time_filter_map = {
#         DashboardTimeFilter.monthly: monthly_data,
#         DashboardTimeFilter.quarterly: quarterly_data,
#         DashboardTimeFilter.year_to_date: yearly_data
#     }

#     result =  time_filter_map.get(time_filter, monthly_data) 


#     final_result = apply_entity_hierarchy_filter(data = result,entity=entity,hierarchy=None,product=None,costcenter=None,
#                                                profitcenter=None,region=None,customer=None,version=None,business_unit = None)
#     final_result["keymetrics"] = dashboard_metrics
#     final_result["entities"] = all_filters_data["entities"]
#     return final_result






    


    
    



    
