<?php
ob_start();
session_start();

header('Content-Type: application/json');

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    ob_end_clean();
    header("Location: login.html");
    exit();
}

if (
    !isset($_SESSION['username'], $_SESSION['database'], $_SESSION['userid']) ||
    empty($_SESSION['username']) || empty($_SESSION['database']) || empty($_SESSION['userid'])
) {
    ob_end_clean();
    echo json_encode(['error' => 'Unauthorized. Please log in.']);
    http_response_code(401);
    exit();
}

try {
    require_once 'connection.php';

    $stmt = $pdo->prepare("SELECT userid FROM USERS WHERE userid = ? AND username = ? AND databasename = ?");
    $stmt->execute([$_SESSION['userid'], $_SESSION['username'], $_SESSION['database']]);

    if (!$stmt->fetch()) {
        ob_end_clean();
        echo json_encode(['error' => 'Invalid session data.']);
        http_response_code(403);
        exit();
    }

    $revenueType    = $_POST['paymentMethod']   ?? '';
    $fyFilter       = $_POST['fyFilter']        ?? '';
    $quarterFilter  = $_POST['quarterFilter']   ?? '';
    $monthFilter    = $_POST['monthFilter']     ?? '';
    $categoryFilter = $_POST['categoryFilter']  ?? '';
    $licenseFilter  = $_POST['licenseFilter']   ?? '';
    $checker        = ($_POST['checker']        ?? '0') === '1';

    if (!function_exists('fetchResults')) {
        function fetchResults($pdo, $query, $params = []) {
            $stmt = $pdo->prepare($query);
            $stmt->execute($params);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            return array_map(
                fn($row) => array_change_key_case($row, CASE_LOWER),
                $rows
            );
        }
    }

    // ============================================================
    // FINANCE VIEW WHERE
    // ============================================================
    $fWhere  = [];
    $fParams = [];

    if ($fyFilter       !== '') { $fWhere[] = 'FY_Code = ?';      $fParams[] = $fyFilter;       }
    if ($quarterFilter  !== '') { $fWhere[] = 'Quarter = ?';      $fParams[] = $quarterFilter;  }
    if ($monthFilter    !== '') { $fWhere[] = 'Month_Name = ?';   $fParams[] = $monthFilter;    }
    if ($categoryFilter !== '') { $fWhere[] = 'Category = ?';     $fParams[] = $categoryFilter; }
    if ($licenseFilter  !== '') { $fWhere[] = 'LicenseType = ?';  $fParams[] = $licenseFilter;  }
    if ($revenueType    !== '') { $fWhere[] = 'RevenueType = ?';  $fParams[] = $revenueType;    }

    $fFilter = count($fWhere) ? 'WHERE ' . implode(' AND ', $fWhere) : '';

    // ============================================================
    // EXPENSE VIEW WHERE
    // ============================================================
    $eWhere  = [];
    $eParams = [];

    if ($fyFilter      !== '') { $eWhere[] = 'FY_Code = ?';    $eParams[] = $fyFilter;      }
    if ($quarterFilter !== '') { $eWhere[] = 'Quarter = ?';    $eParams[] = $quarterFilter; }
    if ($monthFilter   !== '') { $eWhere[] = 'Month_Name = ?'; $eParams[] = $monthFilter;   }

    $eFilter = count($eWhere) ? 'WHERE ' . implode(' AND ', $eWhere) : '';

    // ============================================================
    // GROUPING LEVEL
    // ============================================================
    if ($monthFilter !== '' || $quarterFilter !== '') {
        $groupLevel = 'month';
    } elseif ($fyFilter !== '') {
        $groupLevel = 'quarter';
    } else {
        $groupLevel = 'fy';
    }

    switch ($groupLevel) {
        case 'fy':
            $fGroup   = 'FY_Code';   // or FY_Short — whichever you display
            $fOrderBy = 'ORDER BY MIN(FY_Order)';
            $eGroup   = 'FY_Code';
            $eOrderBy = 'ORDER BY MIN(FY_Order)';
            break;
        case 'quarter':
            $fGroup   = 'Quarter';
            $fOrderBy = 'ORDER BY MIN(FY_Month_ID)';
            $eGroup   = 'Quarter';
            $eOrderBy = 'ORDER BY MIN(FY_Month_ID)';
            break;
        default: // month
            $fGroup   = 'Month_Name';
            $fOrderBy = 'ORDER BY MIN(FY_Month_ID)';
            $eGroup   = 'Month_Name';
            $eOrderBy = 'ORDER BY MIN(FY_Month_ID)';
            break;
    }

    // ============================================================
    // 1. PERIOD STATS
    // ============================================================
    $periodStats = fetchResults($pdo, "
        SELECT
            $fGroup                        AS periodName,
            SUM(TotalRevenue)              AS totalRevenue,
            COUNT(DISTINCT CustomerName)   AS totalClients
        FROM vw_Finance_Monthly
        $fFilter
        GROUP BY $fGroup
        $fOrderBy
    ", $fParams);

    // ============================================================
    // 2. EXPENSE BY PERIOD
    // ============================================================
    $periodExpenses = fetchResults($pdo, "
        SELECT
            $eGroup               AS periodName,
            SUM(TotalExpenses)    AS totalExpenses
        FROM vw_Expense_Monthly
        $eFilter
        GROUP BY $eGroup
        $eOrderBy
    ", $eParams);

    $expenseMap = [];
    foreach ($periodExpenses as $exp) {
        $expenseMap[$exp['periodname']] = $exp['totalexpenses'];
    }

    $periods = $revenue = $expenseData = $clientsAcquiredByPeriod = [];
    foreach ($periodStats as $stat) {
        $p = $stat['periodname'];
        $periods[]                 = $p;
        $revenue[]                 = (float)$stat['totalrevenue'];
        $expenseData[]             = (float)($expenseMap[$p] ?? 0);
        $clientsAcquiredByPeriod[] = (int)$stat['totalclients'];
    }

    // ============================================================
    // 3. CATEGORY STATS
    // ============================================================
    $categoryStats = fetchResults($pdo, "
        SELECT
            Category                       AS productName,
            SUM(TotalRevenue)              AS totalRevenue,
            COUNT(DISTINCT CustomerName)   AS totalClients
        FROM vw_Finance_Monthly
        $fFilter
        GROUP BY Category
        ORDER BY SUM(TotalRevenue) DESC
    ", $fParams);

    $product                  = array_column($categoryStats, 'productname');
    $revByProduct             = array_map(fn($r) => (float)$r['totalrevenue'], $categoryStats);
    $clientsAcquiredByProduct = array_map(fn($r) => (int)$r['totalclients'],   $categoryStats);

    // ============================================================
    // 4. CATEGORY × PERIOD
    // ============================================================
    $catPeriodStats = fetchResults($pdo, "
        SELECT
            Category              AS productName,
            $fGroup               AS periodName,
            SUM(TotalRevenue)     AS totalRevenue
        FROM vw_Finance_Monthly
        $fFilter
        GROUP BY Category, $fGroup
        ORDER BY Category, MIN(FY_Month_ID)
    ", $fParams);

    // ============================================================
    // 5. EXPENSE BY TYPE × PERIOD
    // ============================================================
    $expTypeStats = fetchResults($pdo, "
        SELECT
            ExpenseType           AS expType,
            $eGroup               AS periodName,
            SUM(TotalExpenses)    AS totalExpenses
        FROM vw_Expense_Monthly
        $eFilter
        GROUP BY ExpenseType, $eGroup
        ORDER BY ExpenseType, MIN(FY_Month_ID)
    ", $eParams);

    // ============================================================
    // 6. SUBCATEGORY PIE
    // ============================================================
    $subCatStats = fetchResults($pdo, "
        SELECT
            SubCategory           AS subCat,
            SUM(TotalRevenue)     AS totalRevenue
        FROM vw_Finance_Monthly
        $fFilter
        GROUP BY SubCategory
        ORDER BY SUM(TotalRevenue) DESC
    ", $fParams);

    $productGroups         = array_column($subCatStats, 'subcat');
    $revenueByProductGroup = array_map(fn($r) => (float)$r['totalrevenue'], $subCatStats);

    // ============================================================
    // 7. LICENSE TYPE × PERIOD
    // ============================================================
    $licenseStats = fetchResults($pdo, "
        SELECT
            LicenseType           AS licType,
            $fGroup               AS periodName,
            SUM(TotalRevenue)     AS totalRevenue
        FROM vw_Finance_Monthly
        $fFilter
        GROUP BY LicenseType, $fGroup
        ORDER BY LicenseType, MIN(FY_Month_ID)
    ", $fParams);

    // ============================================================
    // 8. REVENUE TYPE × PERIOD
    // ============================================================
    $revTypeStats = fetchResults($pdo, "
        SELECT
            RevenueType           AS revType,
            $fGroup               AS periodName,
            SUM(TotalRevenue)     AS totalRevenue
        FROM vw_Finance_Monthly
        $fFilter
        GROUP BY RevenueType, $fGroup
        ORDER BY RevenueType, MIN(FY_Month_ID)
    ", $fParams);

// ============================================================
// 9. TOP 5 PROJECTS
// ============================================================

// Sirf TOP 5 ke liye extra condition
$topProjectWhere = $fFilter === '' 
    ? "WHERE ProjectName IS NOT NULL AND ProjectName <> ''"
    : "$fFilter AND ProjectName IS NOT NULL AND ProjectName <> ''";

$topProjects = fetchResults($pdo, "
    SELECT TOP 5
        ProjectName       AS projectName,
        SUM(TotalRevenue) AS totalRevenue
    FROM vw_Finance_Monthly
    $topProjectWhere
    GROUP BY ProjectName
    ORDER BY SUM(TotalRevenue) DESC
", $fParams);

    // ============================================================
    // 10. CURRENT FY KPIs — always unfiltered, current FY only
    // ============================================================
    $currentFYStats = fetchResults($pdo, "
        SELECT
            SUM(TotalRevenue)            AS totalRevenue,
            COUNT(DISTINCT CustomerName) AS totalClients
        FROM vw_Finance_Monthly
        WHERE FY_Code = (
            SELECT TOP 1 FY_Code FROM Dim_Financial_Year WHERE Is_Current_FY = 1
        )
    ");

    $currentFYExpense = fetchResults($pdo, "
        SELECT SUM(TotalExpenses) AS totalExpenses
        FROM vw_Expense_Monthly
        WHERE FY_Code = (
            SELECT TOP 1 FY_Code FROM Dim_Financial_Year WHERE Is_Current_FY = 1
        )
    ");

    $kpiCurrentFY = [
        'totalRevenue'  => (float)($currentFYStats[0]['totalrevenue']   ?? 0),
        'totalExpenses' => (float)($currentFYExpense[0]['totalexpenses'] ?? 0),
    ];
    $kpiCurrentFY['netProfit']    = $kpiCurrentFY['totalRevenue'] - $kpiCurrentFY['totalExpenses'];
    $kpiCurrentFY['profitMargin'] = $kpiCurrentFY['totalRevenue'] > 0
        ? round(($kpiCurrentFY['netProfit'] / $kpiCurrentFY['totalRevenue']) * 100, 1)
        : 0.0;

    // ============================================================
    // RESPONSE
    // ============================================================
    $response = [
        'periods'                  => array_values($periods),
        'revenue'                  => array_values($revenue),
        'expenseData'              => array_values($expenseData),
        'product'                  => array_values($product),
        'revByProduct'             => array_values($revByProduct),
        'clientsAcquiredByProduct' => array_values($clientsAcquiredByProduct),
        'clientsAcquiredByPeriod'  => array_values($clientsAcquiredByPeriod),
        'productGroups'            => array_values($productGroups),
        'revenueByProductGroup'    => array_values($revenueByProductGroup),
        'groupLevel'               => $groupLevel,
        'catPeriodData'            => $catPeriodStats,
        'expTypeData'              => $expTypeStats,
        'licenseData'              => $licenseStats,
        'revTypeData'              => $revTypeStats,
        'topProjects'              => $topProjects,
        'kpiCurrentFY'             => $kpiCurrentFY,
    ];

    // ============================================================
    // FIRST LOAD — DROPDOWN LISTS
    // ============================================================
    if (!$checker) {

        $fyRaw = fetchResults($pdo, "
            SELECT FY_Code, FY_Short
            FROM Dim_Financial_Year
            WHERE Is_Active = 1
            GROUP BY FY_Code, FY_Short
            ORDER BY MIN(FY_Order)
        ");
        $response['fyList'] = array_map(fn($r) => [
            'FY_Code'  => $r['fy_code'],
            'FY_Short' => $r['fy_short'],
        ], $fyRaw);

        $qRaw = fetchResults($pdo, "
            SELECT Quarter FROM Dim_Financial_Year
            WHERE Is_Active = 1 GROUP BY Quarter ORDER BY Quarter
        ");
        $response['quarterList'] = array_column($qRaw, 'quarter');

        $mRaw = fetchResults($pdo, "
            SELECT Month_Name FROM Dim_Financial_Year
            WHERE Is_Active = 1 GROUP BY Month_Name ORDER BY MIN(FY_Month_ID)
        ");
        $response['monthList'] = array_column($mRaw, 'month_name');

        $cRaw = fetchResults($pdo, "
            SELECT DISTINCT Category FROM vw_Finance_Monthly ORDER BY Category
        ");
        $response['categoryList'] = array_column($cRaw, 'category');

        $lRaw = fetchResults($pdo, "
            SELECT DISTINCT LicenseType FROM vw_Finance_Monthly ORDER BY LicenseType
        ");
        $response['licenseList'] = array_column($lRaw, 'licensetype');

        $rRaw = fetchResults($pdo, "
            SELECT DISTINCT RevenueType FROM vw_Finance_Monthly ORDER BY RevenueType
        ");
        $response['revenueTypeList'] = array_column($rRaw, 'revenuetype');
    }

    ob_end_clean();
    echo json_encode($response);

} catch (PDOException $e) {
    ob_end_clean();
    echo json_encode(['error' => '❌ DB Error: ' . $e->getMessage()]);
    http_response_code(500);
    exit();
}