<?php

use DI\Container;
use DownlineAutomator\Exceptions\Database\DatabaseConnectionFailedException;

/*
 * Using this manual connection method does not result in an error
$db = new mysqli(
    '127.0.0.1',
    'XXXXXXXX',
    'XXXXXXXX',
    'XXXXXXXX',
    3306,
    'unix:/var/run/mysqld/mysqld.sock'
);

if ($db->connect_errno) {
    throw new DatabaseConnectionFailedException(
        'Could not connect to the database'
    );
}

$db->set_charset('utf8mb4');
*/

/*
 * Using the container results in the function get_prospects() returning NULL instead of an array
 */
/** @var Container $container */
$container = require '../src/DownlineAutomator/Bootstrap.php';
$db = $container->get(mysqli::class);

xdebug_start_trace();
function get_prospects(mysqli $db) {
    $queryStr = <<<SQL
SELECT
  d.memberID,
  d.memberHasAutoship,
  d.dataReportingPeriod,
  d.memberPV
  FROM (
    SELECT
      downline_data.memberID,
      downline_data.memberHasAutoship,
      downline_data.dataReportingPeriod,
      downline_data.memberPV
    FROM downline_data
    LEFT JOIN downline_members ON downline_members.memberID = downline_data.memberID
    LEFT JOIN downline_members_inactive ON downline_data.memberID = downline_members_inactive.memberID
    WHERE downline_members.accountID = ?
      AND (downline_members.sponsorLeft BETWEEN ? AND ?)
      AND downline_members_inactive.memberID IS NULL
      AND (downline_data.dataReportingPeriod BETWEEN ? AND ?)
      AND downline_data.memberHasAutoship = 0
  ) d
SQL;

    $stmt = $db->prepare($queryStr);

    if ($stmt === false) {
        return null;
    }

    $data = [
        'accountId' => 123456789,
        'sponsorLeft' => 1,
        'sponsorRight' => 7262,
        'reportingPeriodStart' => 454,
        'reportingPeriodEnd' => 460,
    ];

    $stmt->bind_param(
        'iiiii',
        $data['accountId'],
        $data['sponsorLeft'],
        $data['sponsorRight'],
        $data['reportingPeriodStart'],
        $data['reportingPeriodEnd']
    );

    if (!$stmt->execute()) {
        $stmt->close();
        return null;
    }

    $stmt->bind_result(
        $memberId,
        $autoship,
        $reportingPeriod,
        $pv
    );

    $data = [];
    while ($stmt->fetch()) {
        $data[] = [
            'memberId' => $memberId,
            'autoship' => $autoship,
            'reportingPeriod' => $reportingPeriod,
            'pv' => $pv,
        ];
    }

    $stmt->close();

    $out = array_filter(
            $data,
            static function ($v) {
                return $v[ 'pv' ] >= 0;
            }
        );

    return $out;                                //BREAKPOINT HERE CAUSES NULL RETURN
}
$prospects = get_prospects($db);
var_dump($prospects);
xdebug_stop_trace();