                $outPut = $this->mockPdo->query("select COALESCE(MAX(id), 0) from food_order")->fetchColumn();
                $lastOrderId = (int)$outPut;
                $lastOrderId++;

                // get a user id to use for the order inserts
                $outPut = $this->mockPdo->query("select COALESCE(MAX(pk_user_id), 0) from user_login")->fetchColumn();
                $lastUserId = (int)$outPut;

                // get last inseted order item id to use for the order item inserts
                $outPut = $this->mockPdo->query("select COALESCE(MAX(id), 0) from food_order_item")->fetchColumn();
                $lastOrderItemId = (int)$outPut;

                // get last inseret food order vat summery
                $outPut = $this->mockPdo->query("select COALESCE(MAX(id), 0) from food_order_vat_summary")->fetchColumn();
                $lastVatSummaryId = (int)$outPut;

                // get last inserted id for food_order_refund_event
                $outPut = $this->mockPdo->query("select COALESCE(MAX(id), 0) from food_order_refund_event")->fetchColumn();
                $lastRefundEventId = (int)$outPut;

                // date should be yesterday ad 13:00 to ensure it is within the opening hours and picked up by the reporting function

                $fiveDaysAgoAt1300  = (new DateTime('5 days ago'))->setTime(13, 0)->format('Y-m-d H:i:s');
                $fiveDaysAgoAt1310  = (new DateTime('5 days ago'))->setTime(13, 10)->format('Y-m-d H:i:s');
                $fiveDaysAgoAt0000  = (new DateTime('5 days ago'))->setTime(0, 0)->format('Y-m-d H:i:s');
                $fiveDaysAgo        = (new DateTime('5 days ago'))->format('Y-m-d');
                $fourDaysAgoAt1300  = (new DateTime('4 days ago'))->setTime(13, 0)->format('Y-m-d H:i:s');
                $fourDaysAgoAt0000  = (new DateTime('4 days ago'))->setTime(0, 0)->format('Y-m-d H:i:s');
                $fourDaysAgo        = (new DateTime('4 days ago'))->format('Y-m-d');
                $threeDaysAgoAt1300 = (new DateTime('3 days ago'))->setTime(13, 0)->format('Y-m-d H:i:s');
                $threeDaysAgoAt0000 = (new DateTime('3 days ago'))->setTime(0, 0)->format('Y-m-d H:i:s');
                $threeDaysAgo       = (new DateTime('3 days ago'))->format('Y-m-d');
                $twoDaysAgoAt1300   = (new DateTime('2 days ago'))->setTime(13, 0)->format('Y-m-d H:i:s');
                $twoDaysAgoAt0000   = (new DateTime('2 days ago'))->setTime(0, 0)->format('Y-m-d H:i:s');
                $twoDaysAgo         = (new DateTime('2 days ago'))->format('Y-m-d');
                $yesterdayAt1300    = (new DateTime('yesterday'))->setTime(13, 0)->format('Y-m-d H:i:s');
                $yesterdayAt0000    = (new DateTime('yesterday'))->setTime(0, 0)->format('Y-m-d H:i:s');
                $yesterday          = (new DateTime('yesterday'))->format('Y-m-d');
                $todayAt0000        = (new DateTime('today'))->setTime(0, 0)->format('Y-m-d H:i:s');
        
                $orderId = [];
                // insert and order with an item that is fully charged
                $this->mockPdo->exec("INSERT INTO food_order    (id,                public_order_code,      user_id,                guest_user_id,          tenant_id,      delivery_address_id,    billing_address_id,     order_status,   order_type,     product_total_original_gross,  product_total_current_gross,    order_total_current_gross,      order_total_original_gross,     order_full_refund,      order_total_current_reason,                             payment_status,         delivery_fee_gross,     customer_service_fee_gross,     customer_service_fee_vat,   customer_service_fee_vat_rate,          platform_fee_gross,     platform_fee_vat,   platform_fee_vat_rate,       stripe_fee_total,       small_order_fee_gross,  small_order_fee_vat,   small_order_fee_vat_rate,           comment,    tenant_comment,     currency,   transaction_id,                 refunded_id,            payment_intent_id,              stripe_receipt_url,     created_at,                 updated_at) 
                                            VALUES              ($lastOrderId,      'C02QD57Z',             $lastUserId,            NULL,                   $tenantId,      2,                      NULL,                   'confirmed',   'collection',     2000,                          2000,                           2050,                           2050,                           000,                   'Delivery Charge Applied',                              'paid',                  0,                      050,                            1,                          20.00,                                  050,                    8,                  20.00,                       87,                    0,                       0,                     0,                                 NULL,       '',                 'GBP',      'ch_3TCdZxDRk9nZ67Cn2c0uUuj8',  '',             'pi_3TCdZxDRk9nZ67Cn2DmYfS9e',  '',                     '$fiveDaysAgoAt1300',        '$fiveDaysAgoAt1300')");
                $orderId[] = $lastOrderId;

                // insert an order item for that order
                $this->mockPdo->exec("INSERT INTO `food_order_item` ( `order_id`,   `menu_item_id`,     `item_name`,    `item_base_price`,  `item_total_gross_original`,    `item_modified_reason`,     `item_quantity`,    `item_note`,    `item_status`,      `item_tenant_comment`,      `created_at`,               `updated_at`,           `item_total_net_original`,      `item_total_vat_original`,      `item_total_gross_modified`,    `item_total_net_modified`,      `item_total_vat_modified`,      `item_vat_rate`) 
                                                            VALUES  ($lastOrderId,  91,                 'Medium Cod',   1000,               2000,                           'Base price only',          2,                  NULL,           'pending',          NULL,                       '$fiveDaysAgoAt1300',      '$fiveDaysAgoAt1300',    1667,                           333,                            2000,                           1667,                           333,                            20.00)  ");
                $lastOrderItemId ++;
                
                $this->mockPdo->exec("INSERT INTO food_order_vat_summary    (order_id,      vat_rate,   vat_gross_original,     vat_net_original,   vat_amount_original,    vat_gross_modified,     vat_net_modified,   vat_amount_modified,        created_at,             updated_at) 
                                                            VALUES          ($lastOrderId,  20.00,      2000,                   1667,               333,                    2000,                   1667,               333,                        '$fiveDaysAgoAt1300',   '$fiveDaysAgoAt1300')");
                $lastVatSummaryId++;
                // update the order to have a partial refund to test the impact of refunds on the reporting state
                $this->mockPdo->exec("UPDATE food_order SET product_total_current_gross = 1000, order_total_current_gross = 1050, payment_status = 'partial_refund', updated_at = '$fiveDaysAgoAt1310' WHERE id = $lastOrderId");
                // update the order item to have a partial refund to test the impact of refunds on the reporting state
                $this->mockPdo->exec("UPDATE food_order_item SET item_total_gross_modified = 1000, item_total_net_modified = 833, item_total_vat_modified = 167, item_status = 'partial_refund', updated_at = '$fiveDaysAgoAt1310' WHERE id = $lastOrderItemId");
                // update the vat summary to reflect the partial refund
                $this->mockPdo->exec("UPDATE food_order_vat_summary SET vat_gross_modified = 1000, vat_net_modified = 833, vat_amount_modified = 167, updated_at = '$fiveDaysAgoAt1310' WHERE id = $lastVatSummaryId");
                // insert a refund event for the partial refund
                $this->mockPdo->exec("INSERT INTO food_order_refund_event ( refund_group_id,    order_id,        tenant_id,     refund_type,    order_item_id,      gross_pence,    net_pence,      vat_pence,  vat_rate,   note,                               processed_at) 
                                                                    VALUES (2,                  $lastOrderId,   $tenantId,      'item',         $lastOrderItemId,   1000,           833,            167,        20.00,      'Partial refund for order item',    '$fiveDaysAgoAt1310')");
                $lastRefundEventId++;
// results 

Reporting State after EOD run: Array
(
    [0] => Array
        (
            [id] => 14
            [run_id] => 1
            [run_type] => Automatic
            [tenant_id] => 10
            [window_start] => 2026-04-12 00:00:00
            [window_end] => 2026-04-13 00:00:00
            [business_date] => 2026-04-12
            [order_count] => 1
            [full_refund_count] => 0
            [partial_refund_count] => 0
            [full_refund_total] => 0
            [customer_paid_total] => 2050
            [item_refund_total] => 1000
            [partial_refund_total] => 0
            [platform_stripe_fees_net] => 87
            [platform_fees_net] => 50
            [platform_customer_service_fee_net] => 50
            [platform_tenant_fee_gross] => 50
            [platform_customer_service_fee_gross] => 50
            [platform_gross_revenue] => 100
            [platform_refunds] => 0
            [platform_net_revenue] => 100
            [platform_refund_impact] => 0
            [platform_net_impact] => 100
            [platform_sales_vat] => 0
            [platform_refund_vat] => 0
            [platform_net_vat] => 0
            [created_at] => 2026-04-17 14:00:47
            [updated_at] => 2026-04-17 14:00:47
        )

    [1] => Array
        (
            [id] => 15
            [run_id] => 2
            [run_type] => Automatic
            [tenant_id] => 10
            [window_start] => 2026-04-13 00:00:00
            [window_end] => 2026-04-14 00:00:00
            [business_date] => 2026-04-13
            [order_count] => 0
            [full_refund_count] => 0
            [partial_refund_count] => 0
            [full_refund_total] => 0
            [customer_paid_total] => 0
            [item_refund_total] => 0
            [partial_refund_total] => 0
            [platform_stripe_fees_net] => 0
            [platform_fees_net] => 0
            [platform_customer_service_fee_net] => 0
            [platform_tenant_fee_gross] => 0
            [platform_customer_service_fee_gross] => 0
            [platform_gross_revenue] => 0
            [platform_refunds] => 0
            [platform_net_revenue] => 0
            [platform_refund_impact] => 0
            [platform_net_impact] => 0
            [platform_sales_vat] => 0
            [platform_refund_vat] => 0
            [platform_net_vat] => 0
            [created_at] => 2026-04-17 14:00:47
            [updated_at] => 2026-04-17 14:00:47
        )

    [2] => Array
        (
            [id] => 16
            [run_id] => 3
            [run_type] => Automatic
            [tenant_id] => 10
            [window_start] => 2026-04-14 00:00:00
            [window_end] => 2026-04-15 00:00:00
            [business_date] => 2026-04-14
            [order_count] => 0
            [full_refund_count] => 0
            [partial_refund_count] => 0
            [full_refund_total] => 0
            [customer_paid_total] => 0
            [item_refund_total] => 0
            [partial_refund_total] => 0
            [platform_stripe_fees_net] => 0
            [platform_fees_net] => 0
            [platform_customer_service_fee_net] => 0
            [platform_tenant_fee_gross] => 0
            [platform_customer_service_fee_gross] => 0
            [platform_gross_revenue] => 0
            [platform_refunds] => 0
            [platform_net_revenue] => 0
            [platform_refund_impact] => 0
            [platform_net_impact] => 0
            [platform_sales_vat] => 0
            [platform_refund_vat] => 0
            [platform_net_vat] => 0
            [created_at] => 2026-04-17 14:00:47
            [updated_at] => 2026-04-17 14:00:47
        )

    [3] => Array
        (
            [id] => 17
            [run_id] => 4
            [run_type] => Automatic
            [tenant_id] => 10
            [window_start] => 2026-04-15 00:00:00
            [window_end] => 2026-04-16 00:00:00
            [business_date] => 2026-04-15
            [order_count] => 0
            [full_refund_count] => 0
            [partial_refund_count] => 0
            [full_refund_total] => 0
            [customer_paid_total] => 0
            [item_refund_total] => 0
            [partial_refund_total] => 0
            [platform_stripe_fees_net] => 0
            [platform_fees_net] => 0
            [platform_customer_service_fee_net] => 0
            [platform_tenant_fee_gross] => 0
            [platform_customer_service_fee_gross] => 0
            [platform_gross_revenue] => 0
            [platform_refunds] => 0
            [platform_net_revenue] => 0
            [platform_refund_impact] => 0
            [platform_net_impact] => 0
            [platform_sales_vat] => 0
            [platform_refund_vat] => 0
            [platform_net_vat] => 0
            [created_at] => 2026-04-17 14:00:47
            [updated_at] => 2026-04-17 14:00:47
        )

    [4] => Array
        (
            [id] => 18
            [run_id] => 5
            [run_type] => Automatic
            [tenant_id] => 10
            [window_start] => 2026-04-16 00:00:00
            [window_end] => 2026-04-17 00:00:00
            [business_date] => 2026-04-16
            [order_count] => 0
            [full_refund_count] => 0
            [partial_refund_count] => 0
            [full_refund_total] => 0
            [customer_paid_total] => 0
            [item_refund_total] => 0
            [partial_refund_total] => 0
            [platform_stripe_fees_net] => 0
            [platform_fees_net] => 0
            [platform_customer_service_fee_net] => 0
            [platform_tenant_fee_gross] => 0
            [platform_customer_service_fee_gross] => 0
            [platform_gross_revenue] => 0
            [platform_refunds] => 0
            [platform_net_revenue] => 0
            [platform_refund_impact] => 0
            [platform_net_impact] => 0
            [platform_sales_vat] => 0
            [platform_refund_vat] => 0
            [platform_net_vat] => 0
            [created_at] => 2026-04-17 14:00:47
            [updated_at] => 2026-04-17 14:00:47
        )

)

Reporting State Tenant after EOD run: Array
(
    [0] => Array
        (
            [id] => 14
            [run_id] => 1
            [run_type] => Automatic
            [tenant_id] => 10
            [window_start] => 2026-04-12 00:00:00
            [window_end] => 2026-04-13 00:00:00
            [business_date] => 2026-04-12
            [order_count] => 1
            [full_refund_count] => 0
            [partial_refund_count] => 0
            [customer_paid_total] => 2050
            [item_refund_total] => 1000
            [partial_refund_total] => 0
            [tenant_gross_sales] => 2000
            [tenant_full_refund_total] => 0
            [tenant_total_refunds] => 1000
            [tenant_refund_total] => 1000
            [tenant_delivery_fees_net] => 0
            [tenant_small_order_fees_net] => 0
            [tenant_platform_fees_net] => 50
            [tenant_stripe_fees_net] => 87
            [tenant_net_pre_fee] => 1000
            [tenant_net_post_platform_fee] => 950
            [tenant_net] => 863
            [tenant_refund_impact] => -1000
            [tenant_stripe_fee_impact] => -87
            [tenant_net_impact_pre_fee] => 1000
            [tenant_net_impact_post_platform_fee] => 950
            [tenant_net_impact] => 863
            [tenant_sales_vat_gross] => 2000
            [tenant_sales_vat_net] => 1667
            [tenant_sales_vat] => 333
            [tenant_refund_vat_gross] => 1000
            [tenant_refund_vat_net] => 833
            [tenant_refund_vat] => 167
            [tenant_net_vat_gross] => 1000
            [tenant_net_vat_net] => 834
            [tenant_net_vat] => 166
            [created_at] => 2026-04-17 14:00:47
            [updated_at] => 2026-04-17 14:00:47
        )

    [1] => Array
        (
            [id] => 15
            [run_id] => 2
            [run_type] => Automatic
            [tenant_id] => 10
            [window_start] => 2026-04-13 00:00:00
            [window_end] => 2026-04-14 00:00:00
            [business_date] => 2026-04-13
            [order_count] => 0
            [full_refund_count] => 0
            [partial_refund_count] => 0
            [customer_paid_total] => 0
            [item_refund_total] => 0
            [partial_refund_total] => 0
            [tenant_gross_sales] => 0
            [tenant_full_refund_total] => 0
            [tenant_total_refunds] => 0
            [tenant_refund_total] => 0
            [tenant_delivery_fees_net] => 0
            [tenant_small_order_fees_net] => 0
            [tenant_platform_fees_net] => 0
            [tenant_stripe_fees_net] => 0
            [tenant_net_pre_fee] => 0
            [tenant_net_post_platform_fee] => 0
            [tenant_net] => 0
            [tenant_refund_impact] => 0
            [tenant_stripe_fee_impact] => 0
            [tenant_net_impact_pre_fee] => 0
            [tenant_net_impact_post_platform_fee] => 0
            [tenant_net_impact] => 0
            [tenant_sales_vat_gross] => 0
            [tenant_sales_vat_net] => 0
            [tenant_sales_vat] => 0
            [tenant_refund_vat_gross] => 0
            [tenant_refund_vat_net] => 0
            [tenant_refund_vat] => 0
            [tenant_net_vat_gross] => 0
            [tenant_net_vat_net] => 0
            [tenant_net_vat] => 0
            [created_at] => 2026-04-17 14:00:47
            [updated_at] => 2026-04-17 14:00:47
        )

    [2] => Array
        (
            [id] => 16
            [run_id] => 3
            [run_type] => Automatic
            [tenant_id] => 10
            [window_start] => 2026-04-14 00:00:00
            [window_end] => 2026-04-15 00:00:00
            [business_date] => 2026-04-14
            [order_count] => 0
            [full_refund_count] => 0
            [partial_refund_count] => 0
            [customer_paid_total] => 0
            [item_refund_total] => 0
            [partial_refund_total] => 0
            [tenant_gross_sales] => 0
            [tenant_full_refund_total] => 0
            [tenant_total_refunds] => 0
            [tenant_refund_total] => 0
            [tenant_delivery_fees_net] => 0
            [tenant_small_order_fees_net] => 0
            [tenant_platform_fees_net] => 0
            [tenant_stripe_fees_net] => 0
            [tenant_net_pre_fee] => 0
            [tenant_net_post_platform_fee] => 0
            [tenant_net] => 0
            [tenant_refund_impact] => 0
            [tenant_stripe_fee_impact] => 0
            [tenant_net_impact_pre_fee] => 0
            [tenant_net_impact_post_platform_fee] => 0
            [tenant_net_impact] => 0
            [tenant_sales_vat_gross] => 0
            [tenant_sales_vat_net] => 0
            [tenant_sales_vat] => 0
            [tenant_refund_vat_gross] => 0
            [tenant_refund_vat_net] => 0
            [tenant_refund_vat] => 0
            [tenant_net_vat_gross] => 0
            [tenant_net_vat_net] => 0
            [tenant_net_vat] => 0
            [created_at] => 2026-04-17 14:00:47
            [updated_at] => 2026-04-17 14:00:47
        )

    [3] => Array
        (
            [id] => 17
            [run_id] => 4
            [run_type] => Automatic
            [tenant_id] => 10
            [window_start] => 2026-04-15 00:00:00
            [window_end] => 2026-04-16 00:00:00
            [business_date] => 2026-04-15
            [order_count] => 0
            [full_refund_count] => 0
            [partial_refund_count] => 0
            [customer_paid_total] => 0
            [item_refund_total] => 0
            [partial_refund_total] => 0
            [tenant_gross_sales] => 0
            [tenant_full_refund_total] => 0
            [tenant_total_refunds] => 0
            [tenant_refund_total] => 0
            [tenant_delivery_fees_net] => 0
            [tenant_small_order_fees_net] => 0
            [tenant_platform_fees_net] => 0
            [tenant_stripe_fees_net] => 0
            [tenant_net_pre_fee] => 0
            [tenant_net_post_platform_fee] => 0
            [tenant_net] => 0
            [tenant_refund_impact] => 0
            [tenant_stripe_fee_impact] => 0
            [tenant_net_impact_pre_fee] => 0
            [tenant_net_impact_post_platform_fee] => 0
            [tenant_net_impact] => 0
            [tenant_sales_vat_gross] => 0
            [tenant_sales_vat_net] => 0
            [tenant_sales_vat] => 0
            [tenant_refund_vat_gross] => 0
            [tenant_refund_vat_net] => 0
            [tenant_refund_vat] => 0
            [tenant_net_vat_gross] => 0
            [tenant_net_vat_net] => 0
            [tenant_net_vat] => 0
            [created_at] => 2026-04-17 14:00:47
            [updated_at] => 2026-04-17 14:00:47
        )

    [4] => Array
        (
            [id] => 18
            [run_id] => 5
            [run_type] => Automatic
            [tenant_id] => 10
            [window_start] => 2026-04-16 00:00:00
            [window_end] => 2026-04-17 00:00:00
            [business_date] => 2026-04-16
            [order_count] => 0
            [full_refund_count] => 0
            [partial_refund_count] => 0
            [customer_paid_total] => 0
            [item_refund_total] => 0
            [partial_refund_total] => 0
            [tenant_gross_sales] => 0
            [tenant_full_refund_total] => 0
            [tenant_total_refunds] => 0
            [tenant_refund_total] => 0
            [tenant_delivery_fees_net] => 0
            [tenant_small_order_fees_net] => 0
            [tenant_platform_fees_net] => 0
            [tenant_stripe_fees_net] => 0
            [tenant_net_pre_fee] => 0
            [tenant_net_post_platform_fee] => 0
            [tenant_net] => 0
            [tenant_refund_impact] => 0
            [tenant_stripe_fee_impact] => 0
            [tenant_net_impact_pre_fee] => 0
            [tenant_net_impact_post_platform_fee] => 0
            [tenant_net_impact] => 0
            [tenant_sales_vat_gross] => 0
            [tenant_sales_vat_net] => 0
            [tenant_sales_vat] => 0
            [tenant_refund_vat_gross] => 0
            [tenant_refund_vat_net] => 0
            [tenant_refund_vat] => 0
            [tenant_net_vat_gross] => 0
            [tenant_net_vat_net] => 0
            [tenant_net_vat] => 0
            [created_at] => 2026-04-17 14:00:47
            [updated_at] => 2026-04-17 14:00:47
        )

)