from PDAPI import *
from Utils.Models import GERPCashReceiptsPaymentsLedger, GERPAsset, GERPLiability
import datetime
from sqlalchemy import func
import flask

__metadata__ = {
    "route": "{DEFAULT_ROUTE}",
    "methods": ["GET"],
    "blueprint": "GERP",
    "permission": 1.0,
    "attributes": [],
    "limit": "1 per second",
    "args": [
        {"Name": "year", "Type": "int", "Required": False},
        {"Name": "month", "Type": "int", "Required": False, "MinLen": 1, "MaxLen": 12}
    ]
}

def HandleRequest():
    # 获取请求参数
    year = flask.request.args.get('year', type=int)
    month = flask.request.args.get('month', type=int)
    
    # 获取当前时间
    now = datetime.datetime.now()
    current_year = year if year else now.year
    
    # 判断是否为全年统计
    is_full_year = not month
    
    if is_full_year:
        # 全年统计：计算全年的开始和结束时间
        current_year_start = datetime.datetime(current_year, 1, 1)
        current_year_end = datetime.datetime(current_year + 1, 1, 1)
        
        # 上一年的开始和结束时间
        last_year_start = datetime.datetime(current_year - 1, 1, 1)
        last_year_end = current_year_start
        
        # 查询今年数据
        current_month_data = GERPCashReceiptsPaymentsLedger.query.filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= current_year_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < current_year_end
        ).all()
        
        # 查询去年数据
        last_month_data = GERPCashReceiptsPaymentsLedger.query.filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= last_year_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < last_year_end
        ).all()
        
        # 设置当前月份为12（用于显示）
        current_month = 12
    else:
        # 月度统计：使用提供的月份或当前月份
        current_month = int(month)
        
        # 计算本月和上月的开始和结束时间
        current_month_start = datetime.datetime(current_year, current_month, 1)
        if current_month == 12:
            next_month_start = datetime.datetime(current_year + 1, 1, 1)
            last_month_start = datetime.datetime(current_year, 11, 1)
            last_month_end = current_month_start
        elif current_month == 1:
            next_month_start = datetime.datetime(current_year, current_month + 1, 1)
            last_month_start = datetime.datetime(current_year - 1, 12, 1)
            last_month_end = current_month_start
        else:
            next_month_start = datetime.datetime(current_year, current_month + 1, 1)
            last_month_start = datetime.datetime(current_year, current_month - 1, 1)
            last_month_end = current_month_start
        
        # 查询本月数据
        current_month_data = GERPCashReceiptsPaymentsLedger.query.filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= current_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < next_month_start
        ).all()
        
        # 查询上月数据
        last_month_data = GERPCashReceiptsPaymentsLedger.query.filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= last_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < last_month_end
        ).all()
    
    # 计算本月营收（收入类型：CashBookTransactionType = 1）
    if is_full_year:
        current_month_revenue = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= current_year_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < current_year_end,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 1
        ).scalar() or 0
        
        # 计算本月支出（支出类型：CashBookTransactionType = 0）
        current_month_expense = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= current_year_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < current_year_end,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 0
        ).scalar() or 0
        
        # 计算上月（去年）营收和支出
        last_month_revenue = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= last_year_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < last_year_end,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 1
        ).scalar() or 0
        
        last_month_expense = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= last_year_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < last_year_end,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 0
        ).scalar() or 0
    else:
        current_month_revenue = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= current_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < next_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 1
        ).scalar() or 0
        
        # 计算本月支出（支出类型：CashBookTransactionType = 0）
        current_month_expense = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= current_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < next_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 0
        ).scalar() or 0
        
        # 计算上月营收和支出
        last_month_revenue = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= last_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < last_month_end,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 1
        ).scalar() or 0
        
        last_month_expense = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= last_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < last_month_end,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 0
        ).scalar() or 0
    
    # 计算本月净利润
    current_month_profit = current_month_revenue - current_month_expense
    last_month_profit = last_month_revenue - last_month_expense
    
    # 计算环比增长率
    def calculate_growth(current, last):
        if last == 0:
            return "0%"
        growth_rate = ((current - last) / last) * 100
        return f"{growth_rate:.1f}%"
    
    # 计算增长率数值（用于趋势判断）
    def get_growth_value(current, last):
        if last == 0:
            return 0
        return ((current - last) / last) * 100
    
    revenue_growth = calculate_growth(current_month_revenue, last_month_revenue)
    expense_growth = calculate_growth(current_month_expense, last_month_expense)
    profit_growth = calculate_growth(current_month_profit, last_month_profit)
    
    # 获取增长率数值
    revenue_growth_value = get_growth_value(current_month_revenue, last_month_revenue)
    expense_growth_value = get_growth_value(current_month_expense, last_month_expense)
    profit_growth_value = get_growth_value(current_month_profit, last_month_profit)
    
    # 计算各项财务指标
    # 未结款项（状态为0的交易）
    unsettled_amount = sum(item.CashBookAmount for item in current_month_data if item.CashBookStatus == 0)
    
    # 现金余额（计算到本月末的累计值）
    # 优化：使用SQL直接聚合计算现金余额
    def calculate_cash_balance(end_time):
        # 计算收入总额
        income = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookStatus == 1,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 1,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < end_time
        ).scalar() or 0
        
        # 计算支出总额
        expense = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookStatus == 1,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 0,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < end_time
        ).scalar() or 0
        
        # 现金余额 = 收入 - 支出
        return income - expense
    
    if is_full_year:
        # 全年统计：计算到本年末的现金余额
        cash_balance = calculate_cash_balance(current_year_end)
        
        # 全年统计：比较上一年结束时的现金余额
        last_month_cash_balance = calculate_cash_balance(last_year_end)
    else:
        # 月度统计：计算到本月末的现金余额
        cash_balance = calculate_cash_balance(next_month_start)
        
        # 月度统计：比较上月结束时的现金余额
        last_month_cash_balance = calculate_cash_balance(last_month_end)
    
    # 毛利率（简化计算：(营收 - 采购成本) / 营收）
    # 假设采购成本是支出中类别为"计划采购"的部分
    if is_full_year:
        # 全年统计：使用SQL直接聚合计算采购成本
        purchase_cost = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= current_year_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < current_year_end,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 0,
            GERPCashReceiptsPaymentsLedger.CashBookCategory == "计划采购"
        ).scalar() or 0
        
        # 上月（去年）采购成本
        last_month_purchase_cost = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= last_year_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < last_year_end,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 0,
            GERPCashReceiptsPaymentsLedger.CashBookCategory == "计划采购"
        ).scalar() or 0
    else:
        # 月度统计：使用SQL直接聚合计算采购成本
        purchase_cost = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= current_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < next_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 0,
            GERPCashReceiptsPaymentsLedger.CashBookCategory == "计划采购"
        ).scalar() or 0
        
        # 上月采购成本
        last_month_purchase_cost = db.session.query(func.sum(GERPCashReceiptsPaymentsLedger.CashBookAmount)).filter(
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime >= last_month_start,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionTime < last_month_end,
            GERPCashReceiptsPaymentsLedger.CashBookTransactionType == 0,
            GERPCashReceiptsPaymentsLedger.CashBookCategory == "计划采购"
        ).scalar() or 0
    
    # 修复毛利率计算：当营收>0时，毛利率取非负值（最低为0）
    if current_month_revenue > 0:
        gross_margin_value = current_month_revenue - purchase_cost
        gross_margin = max(0, gross_margin_value / current_month_revenue * 100)
    else:
        gross_margin = 0
    
    # 上月毛利率
    if last_month_revenue > 0:
        last_month_gross_margin_value = last_month_revenue - last_month_purchase_cost
        last_month_gross_margin = max(0, last_month_gross_margin_value / last_month_revenue * 100)
    else:
        last_month_gross_margin = 0
    
    # 计算现金余额趋势
    if last_month_cash_balance != 0:
        cash_balance_growth = (cash_balance - last_month_cash_balance) / last_month_cash_balance * 100
        cash_balance_trend = f"{cash_balance_growth:.1f}% 环比增长" if cash_balance_growth > 0 else f"{abs(cash_balance_growth):.1f}% 环比下降"
    else:
        cash_balance_trend = "0.0% 环比持平"
    
    # 计算毛利率趋势
    if last_month_gross_margin != 0:
        gross_margin_growth = (gross_margin - last_month_gross_margin) / last_month_gross_margin * 100
        gross_margin_trend = f"{gross_margin_growth:.1f}% 环比增长" if gross_margin_growth > 0 else f"{abs(gross_margin_growth):.1f}% 环比下降"
    else:
        gross_margin_trend = "0.0% 环比持平"
    
    # 回款率（简化计算：已结算收入 / 总收入）
    settled_revenue = sum(
        item.CashBookAmount for item in current_month_data 
        if item.CashBookTransactionType == 1 and item.CashBookStatus == 1
    )
    # 修复回款率计算：确保结果在合理范围内（0-100%）
    if current_month_revenue > 0:
        collection_rate = min(100, max(0, settled_revenue / current_month_revenue * 100))
    else:
        collection_rate = 0
    
    # 主营业务收入和其他业务收入（简化分类）
    # 修复：只将真正的收入类别归为主营业务收入，移除支出类别
    main_business_income = sum(
        item.CashBookAmount for item in current_month_data 
        if item.CashBookTransactionType == 1 and item.CashBookCategory in ["项目收入", "客户收入", "自愿赠与"]
    )
    other_business_income = current_month_revenue - main_business_income
    
    # 人员工资支出
    salary_expense = sum(
        item.CashBookAmount for item in current_month_data 
        if item.CashBookTransactionType == 0 and item.CashBookCategory in ["工资", "社保", "加班酬劳"]
    )
    
    # 运营费用（除采购和工资外的其他支出）
    operation_expense = current_month_expense - purchase_cost - salary_expense
    
    # 现金流分析（简化计算）
    operating_cash_inflow = current_month_revenue
    operating_cash_outflow = current_month_expense
    operating_cash_net = operating_cash_inflow - operating_cash_outflow
    
    # 投资活动和筹资活动（暂时设为0，需要更多数据支持）
    investing_cash_inflow = 0
    investing_cash_outflow = 0
    investing_cash_net = investing_cash_inflow - investing_cash_outflow
    
    financing_cash_inflow = 0
    financing_cash_outflow = 0
    financing_cash_net = financing_cash_inflow - financing_cash_outflow
    
    net_cash_flow = operating_cash_net + investing_cash_net + financing_cash_net
    
    # 计算收支费用占比
    expense_by_category = {}
    income_by_category = {}
    for item in current_month_data:
        if item.CashBookTransactionType == 0:  # 支出
            category = item.CashBookCategory
            if category not in expense_by_category:
                expense_by_category[category] = 0
            expense_by_category[category] += item.CashBookAmount
        elif item.CashBookTransactionType == 1:  # 收入
            category = item.CashBookCategory
            if category not in income_by_category:
                income_by_category[category] = 0
            income_by_category[category] += item.CashBookAmount
    
    # 构建费用占比列表
    expense_category_list = []
    for category, amount in expense_by_category.items():
        if current_month_expense > 0:
            percentage = (amount / current_month_expense) * 100
        else:
            percentage = 0
        expense_category_list.append({
            "category": category,
            "amount": float(amount),
            "percentage": round(percentage, 2)
        })
    
    # 构建收入占比列表
    income_category_list = []
    for category, amount in income_by_category.items():
        if current_month_revenue > 0:
            percentage = (amount / current_month_revenue) * 100
        else:
            percentage = 0
        income_category_list.append({
            "category": category,
            "amount": float(amount),
            "percentage": round(percentage, 2)
        })
    
    # 按金额排序
    expense_category_list.sort(key=lambda x: x['amount'], reverse=True)
    income_category_list.sort(key=lambda x: x['amount'], reverse=True)
    
    # 计算资产负债率（使用实际数据）
    total_assets = db.session.query(func.sum(GERPAsset.AssetAmount)).scalar() or 0
    total_liabilities = db.session.query(func.sum(GERPLiability.LiabilityAmount)).scalar() or 0
    if total_assets > 0:
        asset_liability_ratio = f"{total_liabilities / total_assets * 100:.1f}%"
    else:
        asset_liability_ratio = "0.0%"
    
    # 格式化金额
    def format_currency(amount):
        return f"¥{amount:,.2f}".replace(".00", "")
    
    # 构建财务数据字典
    financial_data = {
        "year": current_year,
        "month": current_month,
        "monthlyRevenue": format_currency(current_month_revenue),
        "monthlyRevenueTrend": f"{revenue_growth} 环比增长" if revenue_growth_value > 0 else (f"{revenue_growth} 环比下降" if revenue_growth_value < 0 else f"{revenue_growth} 环比持平"),


        "monthlyExpense": format_currency(current_month_expense),
        "monthlyExpenseTrend": f"{expense_growth} 环比增长" if expense_growth_value > 0 else (f"{expense_growth} 环比下降" if expense_growth_value < 0 else f"{expense_growth} 环比持平"),


        "monthlyProfit": format_currency(current_month_profit),
        "monthlyProfitTrend": f"{profit_growth} 环比增长" if profit_growth_value > 0 else (f"{profit_growth} 环比下降" if profit_growth_value < 0 else f"{profit_growth} 环比持平"),


        "unsettledAmount": format_currency(unsettled_amount),

        "cashBalance": format_currency(cash_balance),
        "cashBalanceTrend": cash_balance_trend,
        "grossMargin": f"{gross_margin:.1f}%",
        "grossMarginTrend": gross_margin_trend,
        "revenueGrowth": f"+{format_currency(current_month_revenue - last_month_revenue)} ({revenue_growth})" if current_month_revenue > last_month_revenue else f"-{format_currency(last_month_revenue - current_month_revenue)} ({revenue_growth})",
        "expenseGrowth": f"+{format_currency(current_month_expense - last_month_expense)} ({expense_growth})" if current_month_expense > last_month_expense else f"-{format_currency(last_month_expense - current_month_expense)} ({expense_growth})",
        "profitGrowth": f"+{format_currency(current_month_profit - last_month_profit)} ({profit_growth})" if current_month_profit > last_month_profit else f"-{format_currency(last_month_profit - current_month_profit)} ({profit_growth})",
        "collectionRate": f"{collection_rate:.1f}%",
        "mainBusinessIncome": format_currency(main_business_income),
        "otherBusinessIncome": format_currency(other_business_income),
        "purchaseCost": format_currency(purchase_cost),
        "salaryExpense": format_currency(salary_expense),
        "operationExpense": format_currency(operation_expense),
        "assetLiabilityRatio": asset_liability_ratio,
        "operatingCashInflow": format_currency(operating_cash_inflow),
        "operatingCashOutflow": format_currency(operating_cash_outflow),
        "operatingCashNet": format_currency(operating_cash_net),
        "investingCashInflow": format_currency(investing_cash_inflow),
        "investingCashOutflow": format_currency(investing_cash_outflow),
        "investingCashNet": format_currency(investing_cash_net),
        "financingCashInflow": format_currency(financing_cash_inflow),
        "financingCashOutflow": format_currency(financing_cash_outflow),
        "financingCashNet": format_currency(financing_cash_net),
        "netCashFlow": format_currency(net_cash_flow),
        "expenseCategoryList": expense_category_list,
        "incomeCategoryList": income_category_list,
        "pageTitle": f"{current_year}年财务核心数据全景" if is_full_year else f"{current_year}年{current_month}月财务核心数据全景",
        "updateTime": now.strftime("%Y-%m-%d %H:%M")
    }
    
    return {"code": 0, "msg": "success", "data": financial_data}