<?php
/**
 * Action: Generate AI-powered financial snapshot with executive summary.
 */

if (!defined('DB_HOST')) {
    require_once __DIR__ . '/../../config.php';
}
require_once __DIR__ . '/../provider_engine.php';

function action_financial_snapshot(array $params): array
{
    $conn      = getDBConnection();
    $date_from = $params['date_from'] ?? date('Y-m-01');
    $date_to   = $params['date_to']   ?? date('Y-m-d');

    // Total collections in period
    $col_stmt = $conn->prepare(
        "SELECT COALESCE(SUM(amount_paid), 0) AS total,
                COALESCE(SUM(CASE WHEN payment_method='online' THEN amount_paid ELSE 0 END), 0) AS online,
                COALESCE(SUM(CASE WHEN payment_method='cash' OR payment_method IS NULL THEN amount_paid ELSE 0 END), 0) AS cash
         FROM payments WHERE payment_date BETWEEN ? AND ?"
    );
    $col_stmt->bind_param('ss', $date_from, $date_to);
    $col_stmt->execute();
    $collections = $col_stmt->get_result()->fetch_assoc();

    // Total expenses
    $exp_stmt = $conn->prepare(
        "SELECT COALESCE(SUM(amount), 0) AS total FROM expenses WHERE expense_date BETWEEN ? AND ?"
    );
    $exp_stmt->bind_param('ss', $date_from, $date_to);
    $exp_stmt->execute();
    $expenses_row = $exp_stmt->get_result()->fetch_assoc();
    $total_expenses = (float) ($expenses_row['total'] ?? 0);

    // Collections by class
    $class_stmt = $conn->prepare(
        "SELECT s.class, COALESCE(SUM(p.amount_paid), 0) AS collected
         FROM payments p
         JOIN students s ON s.id = p.student_id
         WHERE p.payment_date BETWEEN ? AND ?
         GROUP BY s.class ORDER BY collected DESC"
    );
    $class_stmt->bind_param('ss', $date_from, $date_to);
    $class_stmt->execute();
    $by_class = $class_stmt->get_result()->fetch_all(MYSQLI_ASSOC);

    // Top expense categories
    $cat_stmt = $conn->prepare(
        "SELECT category, COALESCE(SUM(amount), 0) AS total
         FROM expenses WHERE expense_date BETWEEN ? AND ?
         GROUP BY category ORDER BY total DESC LIMIT 5"
    );
    $cat_stmt->bind_param('ss', $date_from, $date_to);
    $cat_stmt->execute();
    $top_categories = $cat_stmt->get_result()->fetch_all(MYSQLI_ASSOC);

    $total_collected = (float) ($collections['total'] ?? 0);
    $net_balance     = $total_collected - $total_expenses;

    $financial_data = [
        'period'         => ['from' => $date_from, 'to' => $date_to],
        'collections'    => $total_collected,
        'online_payments'=> (float) ($collections['online'] ?? 0),
        'cash_payments'  => (float) ($collections['cash'] ?? 0),
        'expenses'       => $total_expenses,
        'net_balance'    => $net_balance,
        'by_class'       => $by_class,
        'top_categories' => $top_categories,
    ];

    // AI executive summary
    $school_name = getSettingValue('school_name') ?: 'the school';
    $ai_result = RBCAIEngine::generate(
        "Analyze this school financial data for {$school_name} and write a 3-paragraph executive summary " .
        "with key insights and recommendations. Focus on cash flow health, collection performance, and expense management.\n" .
        "All amounts are in Ghana Cedis. Write every figure as 'GHS 1,234.56'. " .
        "Never use a dollar sign or the word dollars.\n" .
        "Financial Data: " . json_encode($financial_data),
        "You are a school finance analyst writing an executive summary for the headteacher of a Ghanaian school. " .
        "The school's currency is the Ghana Cedi (GHS)."
    );

    $summary_text = $ai_result['success']
        ? $ai_result['content']
        : "Financial data collected for the period {$date_from} to {$date_to}. " .
          "Total collections: GHS " . number_format($total_collected, 2) . ". " .
          "Total expenses: GHS " . number_format($total_expenses, 2) . ". " .
          "Net balance: GHS " . number_format($net_balance, 2) . ".";

    return [
        'success'       => true,
        'summary_text'  => $summary_text,
        'data'          => $financial_data,
        'provider_used' => $ai_result['provider'] ?? 'none',
    ];
}
