<?php
/**
 * Broker Lead Analytics
 * Returns lead status summary, monthly trend, and next-month prediction.
 */

header('Content-Type: application/json');
require_once '../../config.php';
require_once __DIR__ . '/broker-identity.php';

$session_role = $_SESSION['role'] ?? ($_SESSION['user_role'] ?? '');
if (!isset($_SESSION['user_id']) || $session_role !== 'broker') {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

$session_broker_id = (int)$_SESSION['user_id'];
$brokerCtx = resolveBrokerContext($conn, $session_broker_id);
$broker_id = (int)$brokerCtx['property_broker_id'];
$fallback_broker_id = $session_broker_id !== $broker_id ? $session_broker_id : 0;

try {
    $summary = [
        'total' => 0,
        'assigned_properties_total' => 0,
        'pending' => 0,
        'responded' => 0,
        'approved' => 0,
        'declined' => 0,
        'finished' => 0,
        'working' => 0,
        'handled' => 0,
        'active' => 0,
        'conversion_rate' => 0
    ];

    $statusStmt = $conn->prepare("
        SELECT LOWER(COALESCE(i.status, 'pending')) AS status_name, COUNT(*) AS count
        FROM inquiries i
        LEFT JOIN properties p ON p.id = i.property_id
        WHERE (
            i.assigned_to = ?
            OR i.broker_id = ?
            OR (? > 0 AND i.broker_id = ?)
            OR p.broker_id = ?
            OR (? > 0 AND p.broker_id = ?)
        )
        GROUP BY LOWER(COALESCE(i.status, 'pending'))
    ");
    $statusStmt->bind_param('iiiiiii', $session_broker_id, $broker_id, $fallback_broker_id, $fallback_broker_id, $broker_id, $fallback_broker_id, $fallback_broker_id);
    $statusStmt->execute();
    $statusResult = $statusStmt->get_result();
    while ($row = $statusResult->fetch_assoc()) {
        $status = (string)$row['status_name'];
        $count = (int)($row['count'] ?? 0);
        $summary['total'] += $count;
        if (in_array($status, ['completed', 'done', 'finished'], true)) {
            $summary['finished'] += $count;
        } elseif (isset($summary[$status])) {
            $summary[$status] += $count;
        }
    }
    $statusStmt->close();

    $assignedStmt = $conn->prepare("
        SELECT COUNT(*) AS count
        FROM properties
        WHERE (broker_id = ? OR (? > 0 AND broker_id = ?))
    ");
    $assignedStmt->bind_param('iii', $broker_id, $fallback_broker_id, $fallback_broker_id);
    $assignedStmt->execute();
    $assignedResult = $assignedStmt->get_result();
    $summary['assigned_properties_total'] = (int)($assignedResult->fetch_assoc()['count'] ?? 0);
    $assignedStmt->close();

    if ($conn->query("SHOW TABLES LIKE 'property_assignment_workflow'")->num_rows > 0) {
        $workflowStmt = $conn->prepare("
            SELECT LOWER(COALESCE(workflow_status, 'assigned')) AS workflow_status, COUNT(*) AS count
            FROM property_assignment_workflow
            WHERE (broker_id = ? OR (? > 0 AND broker_id = ?))
            GROUP BY LOWER(COALESCE(workflow_status, 'assigned'))
        ");
        $workflowStmt->bind_param('iii', $broker_id, $fallback_broker_id, $fallback_broker_id);
        $workflowStmt->execute();
        $workflowResult = $workflowStmt->get_result();
        while ($row = $workflowResult->fetch_assoc()) {
            $workflowStatus = (string)($row['workflow_status'] ?? 'assigned');
            $count = (int)($row['count'] ?? 0);
            if (in_array($workflowStatus, ['assigned', 'working'], true)) {
                $summary['working'] += $count;
            } elseif ($workflowStatus === 'finished_pending_admin') {
                $summary['finished'] += $count;
            } elseif ($workflowStatus === 'finished_approved') {
                $summary['approved'] += $count;
                $summary['finished'] += $count;
            }
        }
        $workflowStmt->close();
    }

    $summary['handled'] = $summary['responded'] + $summary['approved'] + $summary['declined'] + $summary['finished'];
    $summary['active'] = $summary['pending'] + $summary['responded'];
    $summary['conversion_rate'] = $summary['total'] > 0
        ? round((($summary['approved'] + $summary['finished']) / $summary['total']) * 100, 1)
        : 0;

    $labels = [];
    $monthlyTotal = [];
    $monthlyHandled = [];

    $start = new DateTime('first day of -5 months');
    $months = [];
    for ($i = 0; $i < 6; $i++) {
        $monthKey = $start->format('Y-m');
        $months[$monthKey] = ['total' => 0, 'handled' => 0];
        $labels[] = $start->format('M Y');
        $start->modify('+1 month');
    }

    $trendStmt = $conn->prepare("
        SELECT
            DATE_FORMAT(i.created_at, '%Y-%m') AS month_key,
            COUNT(*) AS total_count,
            SUM(CASE WHEN LOWER(COALESCE(i.status, 'pending')) IN ('responded', 'approved', 'declined', 'completed', 'done', 'finished') THEN 1 ELSE 0 END) AS handled_count
        FROM inquiries i
        LEFT JOIN properties p ON p.id = i.property_id
        WHERE (
            i.assigned_to = ?
            OR i.broker_id = ?
            OR (? > 0 AND i.broker_id = ?)
            OR p.broker_id = ?
            OR (? > 0 AND p.broker_id = ?)
        )
          AND i.created_at >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH), '%Y-%m-01')
        GROUP BY DATE_FORMAT(i.created_at, '%Y-%m')
        ORDER BY month_key ASC
    ");
    $trendStmt->bind_param('iiiiiii', $session_broker_id, $broker_id, $fallback_broker_id, $fallback_broker_id, $broker_id, $fallback_broker_id, $fallback_broker_id);
    $trendStmt->execute();
    $trendResult = $trendStmt->get_result();
    while ($row = $trendResult->fetch_assoc()) {
        $monthKey = (string)$row['month_key'];
        if (!isset($months[$monthKey])) {
            continue;
        }
        $months[$monthKey]['total'] = (int)($row['total_count'] ?? 0);
        $months[$monthKey]['handled'] = (int)($row['handled_count'] ?? 0);
    }
    $trendStmt->close();

    foreach ($months as $m) {
        $monthlyTotal[] = $m['total'];
        $monthlyHandled[] = $m['handled'];
    }

    $predictedNextMonthHandled = 0;
    $n = count($monthlyHandled);
    if ($n > 0) {
        if ($n === 1) {
            $predictedNextMonthHandled = max(0, (int)$monthlyHandled[0]);
        } else {
            $sumX = 0;
            $sumY = 0;
            $sumXY = 0;
            $sumX2 = 0;
            for ($i = 0; $i < $n; $i++) {
                $x = $i + 1;
                $y = (int)$monthlyHandled[$i];
                $sumX += $x;
                $sumY += $y;
                $sumXY += $x * $y;
                $sumX2 += $x * $x;
            }
            $denominator = ($n * $sumX2) - ($sumX * $sumX);
            if ($denominator !== 0) {
                $slope = (($n * $sumXY) - ($sumX * $sumY)) / $denominator;
                $intercept = ($sumY - ($slope * $sumX)) / $n;
                $predicted = ($slope * ($n + 1)) + $intercept;
                $predictedNextMonthHandled = max(0, (int)round($predicted));
            } else {
                $predictedNextMonthHandled = max(0, (int)end($monthlyHandled));
            }
        }
    }

    echo json_encode([
        'success' => true,
        'analytics' => [
            'summary' => $summary,
            'trend' => [
                'labels' => $labels,
                'total' => $monthlyTotal,
                'handled' => $monthlyHandled
            ],
            'prediction' => [
                'next_month_handled' => $predictedNextMonthHandled
            ]
        ]
    ]);
} catch (Exception $e) {
    echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
}
?>
