<?php

namespace Mtc\Modules\MembersMessaging\Classes;

use Illuminate\Support\Facades\DB;
use Mtc\Plugins\MembersMessaging\Classes\Message;

$path = '../../../';
require_once $path . 'core/includes/header.inc.php';
$page_meta['title'] = 'Response time';

/*
 * Some good old-fashion SQL.
 * We need to find all doctor messages that have a user message prior them (and don't have a doctor message in between)
 * So we're finding all doctor messages that are a reply to user messages
 */
$messages = collect(
    DB::select(
        DB::raw("
              SELECT 
                  mm.*,
                  (
                      SELECT 
                          created_at
                      FROM
                          messenger_messages mm4
                      WHERE
                          mm4.admin = '0' AND
                          mm4.id < mm.id
                      ORDER BY
                          mm4.id DESC
                      LIMIT
                          0,1
                  ) AS message_created
              FROM 
                  messenger_messages AS mm
              WHERE
                  mm.admin = '1' AND  
                  EXISTS (
                      SELECT 
                          1 
                      FROM 
                          messenger_messages mm2
                      WHERE
                          mm2.admin = '0' AND
                          mm2.id < mm.id AND
                          mm2.thread_id = mm.thread_id AND
                          NOT EXISTS (
                              SELECT
                                  1
                              FROM 
                                  messenger_messages mm3
                              WHERE
                                  mm3.admin = '1' AND
                                  mm3.id < mm.id AND
                                  mm3.id > mm2.id AND
                                  mm3.thread_id = mm.thread_id
                          ) 
                  )
        ")
    )
)
->map(function($message) {
    $message_time = strtotime($message->message_created);
    $reply_time = strtotime($message->created_at);
    $message->response_time = $reply_time - $message_time;
    return $message;
});

$message_count = $messages->count();

$response_stats = [];
foreach (Message::$response_times as $response_in_seconds => $response_time_text) {
    $response_stats[$response_in_seconds] = 0;
}
if ($message_count > 0) {
    foreach ($messages as $message) {
        foreach (Message::$response_times as $response_in_seconds => $response_time_text) {
            if ($message->response_time <= $response_in_seconds) {
                $response_stats[$response_in_seconds]++;
                break;
            }
        }
    }
}

require_once $path . 'core/admin/templates/adminheader.html.php';
?>
    <ul class="breadcrumbs el">
        <li><a href="/admin/">Home</a> /</li>
        <li class="end">Response time</li>
    </ul>

    <h1>Edit template</h1>

    <?php
    require __DIR__ . '/tabnav.php';
    ?>


<table class="manage-items">
    <tr>
        <th>Response Time</th>
        <th>No of Responses</th>
        <th>% of all</th>
    </tr>
    <tbody class="tabular">
    <?php
    if ($message_count > 0) {
        foreach ($response_stats as $response_time => $response_count) {
            ?>
            <tr>
                <td><?= Message::$response_times[$response_time]; ?></td>
                <td><?= $response_count; ?></td>
                <td><?= round($response_count / $message_count * 100); ?> %</td>
            </tr>
            <?php
        }
    }
    ?>
    </tbody>
</table>
<?php

require_once $path . 'core/admin/templates/adminfooter.html.php';