import pandas as pd
import numpy as np
from datetime import datetime, timedelta
from sklearn.linear_model import LinearRegression

class CashFlowForecaster:
    def __init__(self, historical_data):
        self.data = historical_data
        self.forecast_periods = 13  # weeks

    def calculate_collections_forecast(self, sales_forecast, collection_pattern):
        """
        Forecast cash collections based on sales and collection patterns
        collection_pattern: dict with keys 'current', '30_days', '60_days', '90_days'
        """
        collections = pd.DataFrame()

        for period in range(self.forecast_periods):
            period_collections = 0

            # Current period collections
            if period < len(sales_forecast):
                period_collections += sales_forecast.iloc[period] * collection_pattern['current']

            # 30-day collections
            if period >= 4 and period-4 < len(sales_forecast):
                period_collections += sales_forecast.iloc[period-4] * collection_pattern['30_days']

            # 60-day collections  
            if period >= 8 and period-8 < len(sales_forecast):
                period_collections += sales_forecast.iloc[period-8] * collection_pattern['60_days']

            collections = pd.concat([collections, pd.DataFrame([period_collections])], ignore_index=True)

        return collections

    def forecast_operating_cash_flow(self, revenue_forecast, expense_forecast, 
                                   collection_lag=30, payment_lag=15):
        """
        Generate operating cash flow forecast with timing adjustments
        """
        collection_periods = collection_lag // 7  # Convert to weeks
        payment_periods = payment_lag // 7

        cash_inflows = revenue_forecast.shift(collection_periods).fillna(0)
        cash_outflows = expense_forecast.shift(payment_periods).fillna(0)

        net_operating_cf = cash_inflows - cash_outflows
        return net_operating_cf

class ScenarioAnalysis:
    def __init__(self, base_forecast):
        self.base_forecast = base_forecast

    def monte_carlo_simulation(self, revenue_volatility=0.15, 
                              expense_volatility=0.10, iterations=1000):
        """
        Run Monte Carlo simulation for cash flow uncertainty
        """
        results = []

        for i in range(iterations):
            # Generate random variations
            revenue_shock = np.random.normal(1, revenue_volatility, len(self.base_forecast))
            expense_shock = np.random.normal(1, expense_volatility, len(self.base_forecast))

            # Apply shocks to base forecast
            scenario_cf = (self.base_forecast['revenue'] * revenue_shock - 
                          self.base_forecast['expenses'] * expense_shock)

            # Calculate cumulative cash position
            cumulative_cash = scenario_cf.cumsum() + self.base_forecast['starting_cash']

            results.append({
                'min_cash': cumulative_cash.min(),
                'final_cash': cumulative_cash.iloc[-1],
                'periods_negative': (cumulative_cash < 0).sum()
            })

        return pd.DataFrame(results)

    def stress_test_scenarios(self):
        scenarios = {
            'recession': {'revenue_decline': 0.3, 'expense_increase': 0.1},
            'supply_shock': {'revenue_decline': 0.1, 'expense_increase': 0.25},
            'customer_loss': {'revenue_decline': 0.4, 'expense_increase': 0.05}
        }

        stress_results = {}
        for scenario, params in scenarios.items():
            stressed_revenue = self.base_forecast['revenue'] * (1 - params['revenue_decline'])
            stressed_expenses = self.base_forecast['expenses'] * (1 + params['expense_increase'])

            stressed_cf = stressed_revenue - stressed_expenses
            cumulative_cash = stressed_cf.cumsum() + self.base_forecast['starting_cash']

            stress_results[scenario] = {
                'minimum_cash': cumulative_cash.min(),
                'cash_deficit_periods': (cumulative_cash < 0).sum(),
                'max_funding_need': abs(cumulative_cash.min()) if cumulative_cash.min() < 0 else 0
            }

        return stress_results

def optimize_collection_terms(historical_sales, collection_data, cost_of_capital=0.08):
    """
    Analyze optimal payment terms balancing sales volume and cash timing
    """
    scenarios = {
        'net_15': {'sales_multiplier': 1.05, 'avg_collection': 18},
        'net_30': {'sales_multiplier': 1.0, 'avg_collection': 35},
        'net_45': {'sales_multiplier': 0.95, 'avg_collection': 52}
    }

    analysis = {}
    for term, params in scenarios.items():
        annual_sales = historical_sales.sum() * params['sales_multiplier']
        avg_receivables = (annual_sales / 365) * params['avg_collection']
        carrying_cost = avg_receivables * cost_of_capital

        analysis[term] = {
            'annual_sales': annual_sales,
            'average_receivables': avg_receivables,
            'carrying_cost': carrying_cost,
            'net_benefit': annual_sales - carrying_cost
        }

    return analysis

import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows

def update_forecast_workbook(forecast_data, workbook_path):
    """
    Automatically update Excel-based cash flow models
    """
    wb = openpyxl.load_workbook(workbook_path)
    ws = wb['Cash Flow Forecast']

    # Clear existing forecast data
    for row in ws['C5:O18']:
        for cell in row:
            cell.value = None

    # Insert new forecast data
    for r_idx, row in enumerate(dataframe_to_rows(forecast_data, index=False, header=False)):
        for c_idx, value in enumerate(row):
            ws.cell(row=r_idx+5, column=c_idx+3, value=value)

    # Update formulas for variance analysis
    for row in range(5, 18):
        ws[f'P{row}'] = f'=N{row}-O{row}'  # Actual vs Forecast variance

    wb.save(workbook_path)