<?php
require_once 'connection.php';
session_start();

$quarter = $_POST['quarter'] ?? "";
$currentYear = $_POST['currentYear'] ?? "";

if (empty($currentYear)) {
    $stmt = $pdo->query("SELECT MAX(financial_year) AS max_year FROM it_sales");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $currentYear = $row['max_year'];
}

list($start, $end) = explode("-", $currentYear);

$prevStart = $start - 1;
$prevEnd   = $start;

$previousYear = $prevStart . "-" . substr($prevEnd, -2);

$condition = "WHERE FINANCIAL_YEAR = ?";
$params = [$currentYear];

if (!empty($quarter)) {
    $condition .= " AND ORDER_MONTH > (3 * (? - 1)) AND ORDER_MONTH <= (3 * ?)";
    $params[] = $quarter;
    $params[] = $quarter;
}

$prevCondition = "WHERE FINANCIAL_YEAR = ?";
$prevParams = [$previousYear];

if (!empty($quarter)) {
    $prevCondition .= " AND ORDER_MONTH > (3 * (? - 1)) AND ORDER_MONTH <= (3 * ?)";
    $prevParams[] = $quarter;
    $prevParams[] = $quarter;
}

$queries = [
    "summary" => "
        SELECT COUNT(DISTINCT CASE WHEN STATUS = 'Open' THEN CLIENT_NAME END) AS CURRENT_OPPORTUNITIES,
               SUM(CASE WHEN STATUS = 'Open' THEN SALES END) AS CURRENT_PURCHASE_VALUE,
               COUNT(CASE WHEN STATUS = 'Won' THEN CLIENT_NAME END) AS TOTAL_WON_OPPORTUNITIES,
               SUM(CASE WHEN STATUS = 'Won' THEN SALES END) AS WON_PURCHASE_VALUE,
               COUNT(DISTINCT CASE WHEN STATUS = 'Lost' THEN CLIENT_NAME END) AS LOST_OPPORTUNITIES,
               COUNT(CASE WHEN STATUS = 'Lost' THEN CLIENT_NAME END) AS TOTAL_LOST_ENTRIES,
               SUM(CASE WHEN STATUS = 'Lost' THEN SALES END) AS LOST_PURCHASE_VALUE,
               COUNT(*) AS TOTAL_ENTRIES
        FROM IT_SALES
        $condition;
    ",

    "salesProfit" => "
        SELECT ORDER_YEAR, ORDER_MONTH, M.MONTHNAME,
               SUM(SALES) AS TOTAL_SALES,
               SUM(PROFIT) AS TOTAL_PROFIT
        FROM it_sales I
        JOIN monthTable M ON I.ORDER_MONTH = M.MONTHID
        $condition
        GROUP BY ORDER_MONTH, ORDER_YEAR, M.MONTHNAME
        ORDER BY ORDER_YEAR, ORDER_MONTH;
    ",

    "prevSalesProfit" => "
        SELECT ORDER_YEAR, ORDER_MONTH, M.MONTHNAME,
               SUM(SALES) AS TOTAL_SALES,
               SUM(PROFIT) AS TOTAL_PROFIT
        FROM it_sales I
        JOIN monthTable M ON I.ORDER_MONTH = M.MONTHID
        $prevCondition
        GROUP BY ORDER_MONTH, ORDER_YEAR, M.MONTHNAME
        ORDER BY ORDER_YEAR, ORDER_MONTH;
    ",

    "churn" => "
    SELECT DISTINCT(CHURN_REASON), SUM(SALES) LOST_SALES
    FROM it_sales
    " . (!empty($condition) ? $condition . " AND" : "WHERE") . "
    CHURN_REASON IS NOT NULL AND STATUS='Lost'
    GROUP BY CHURN_REASON
    ORDER BY CHURN_REASON;
",

    "package" => "
    SELECT COUNT(DISTINCT(client_name)) sum_of_ids,
           SUM(sales)/COUNT(sales) Average_purchase,
           package
    FROM it_sales
    " . (!empty($condition) ? $condition . " AND" : "WHERE") . "
    status='Open'
    GROUP BY package
    ORDER BY package;
",

    "tableData" => "
        SELECT [status] as Stage,
               COUNT(order_month) as [Sum of Scores],
               client_name as [Opportunity_Name],
               COUNT(id) as [Sum of Opportunity_id],
               churn_reason as [Comment],
               package
        FROM it_sales
        $condition
        GROUP BY client_name, [status], churn_reason, package
        ORDER BY client_name;
    "
];

$results = [
    "sales" => [],
    "profit" => [],
    "months" => [],
    "prevSales" => [],
    "prevProfit" => [],
    "prevMonths" => [],
    "churnReasons" => [],
    "lostSales" => [],
    "oppByPack" => [],
    "avgByPack" => [],
    "tableData" => []
];

foreach ($queries as $key => $sql) {
    $stmt = $pdo->prepare($sql);

    // Always pass the right param set:
    $bind = ($key === "prevSalesProfit") ? $prevParams : $params;

    $stmt->execute($bind);

    if ($key === "summary") {
        $userdata = $stmt->fetch();
        if (!$userdata) {
            echo json_encode(['error' => 'Invalid session data.']);
            http_response_code(403);
            exit();
        }
        $results["userdata"] = $userdata;
    } else {
        $rows = $stmt->fetchAll();
        if ($key === "salesProfit") {
            foreach ($rows as $row) {
                $results["sales"][]  = $row['TOTAL_SALES'];
                $results["profit"][] = $row['TOTAL_PROFIT'];
                $results["months"][] = $row['MONTHNAME'] . " " . $row['ORDER_YEAR'];
            }
        } elseif ($key === "prevSalesProfit") {
            foreach ($rows as $row) {
                $results["prevSales"][]  = $row['TOTAL_SALES'];
                $results["prevProfit"][] = $row['TOTAL_PROFIT'];
                $results["prevMonths"][] = $row['MONTHNAME'] . " " . $row['ORDER_YEAR'];
            }
        } elseif ($key === "churn") {
            foreach ($rows as $row) {
                $results["churnReasons"][] = $row['CHURN_REASON'];
                $results["lostSales"][]    = $row['LOST_SALES'];
            }
        } elseif ($key === "package") {
            foreach ($rows as $row) {
                $results["oppByPack"][] = $row['sum_of_ids'] . " - " . $row['package'];
                $results["avgByPack"][] = $row['Average_purchase'];
            }
        } elseif ($key === "tableData") {
            $results["tableData"] = $rows;
        }
    }
}


$response = [
    'TOTAL_ENTRIES' => $results['userdata']['TOTAL_ENTRIES'],
    'LOST_PURCHASE_VALUE' => $results['userdata']['LOST_PURCHASE_VALUE'],
    'TOTAL_LOST_ENTRIES' => $results['userdata']['TOTAL_LOST_ENTRIES'],
    'LOST_OPPORTUNITIES' => $results['userdata']['LOST_OPPORTUNITIES'],
    'WON_PURCHASE_VALUE' => $results['userdata']['WON_PURCHASE_VALUE'],
    'TOTAL_WON_OPPORTUNITIES' => $results['userdata']['TOTAL_WON_OPPORTUNITIES'],
    'CURRENT_PURCHASE_VALUE' => $results['userdata']['CURRENT_PURCHASE_VALUE'],
    'CURRENT_OPPORTUNITIES' => $results['userdata']['CURRENT_OPPORTUNITIES'],
    'TOTAL_SALES' => $results['sales'],
    'TOTAL_PROFIT' => $results['profit'],
    'MONTHS' => $results['months'],
    'PREV_TOTAL_SALES' => $results['prevSales'],
    'PREV_TOTAL_PROFIT' => $results['prevProfit'],
    'PREV_MONTHS' => $results['prevMonths'],
    'churnReasons' => $results['churnReasons'],
    'lostSales' => $results['lostSales'],
    'oppByPack' => $results['oppByPack'],
    'avgByPack' => $results['avgByPack'],
    'tableData' => $results['tableData']
];

echo json_encode($response);
