<?php
function cmp($a, $b) {
    $t1 = strtotime($a['createdAt']);
    // echo $a['createdAt']."<br>";
    $t2 = strtotime($b['createdAt']);
    // echo $b['createdAt']."<br>";

    if ( $t1 == $t2 ) {
        return 0;
    }

    return ( $t1 > $t2 ) ? -1 : 1;
}

// $validNumPerPage = [
//     6,
//     12,
//     18,
//     24
// ];
// $numPerPage = ( in_array($_GET['pp'], $validNumPerPage) )
//     ? $_GET['pp']
//     : $validNumPerPage[0]
// ;
$numPerPage = 10;
$page       = ( isset($_GET['page']) ) 
    ? $_GET['page']
    : 1
;
$offset     = ($page * $numPerPage) - ($numPerPage);

$totalComments = $db->cell("SELECT COUNT(*) FROM comment WHERE sheetId = ?", $userProject['sheetId']);
$numPages      = ceil($totalComments / $numPerPage);

$pdo      = $db->getPdo();
$comments = $db->run("SELECT * FROM comment WHERE sheetId = ? ORDER BY createdAt DESC LIMIT ?,?", $userProject['sheetId'], $offset, $numPerPage);
foreach ( $comments as &$comment ) {
    $comment['attachments'] = $db->run("SELECT * FROM attachment WHERE commentId = ? ORDER BY createdAt DESC", $comment['id']);
    $comment['count']       = $db->cell("SELECT COUNT(commentId) FROM thumb WHERE commentId = ?", $comment['id']);
    $comment['city']        = $db->cell("SELECT city FROM project WHERE id = ?", $userProject['id']);
    $comment['weatherIcon'] = $db->cell("SELECT icon FROM weather WHERE (projectId = ? OR sheetId = ?) AND date = ?", $comment['projectId'], $comment['sheetId'], date('Y-m-d', strtotime($comment['createdAt'])));
    
    // $comment['replies'] = $db->run("SELECT * FROM comment WHERE parentId = ?", $comment['id']);
    // $stmt               = $pdo->prepare("SELECT reply.id, reply.userId, reply.commentId, reply.text, reply.createdAt, user.name, user.email, user.clientId FROM reply, user WHERE reply.commentId = ? AND reply.userId = user.id");
    $stmt               = $pdo->prepare("SELECT comment.id, comment.parentId, comment.projectId, comment.text, comment.userId, comment.createdAt, user.name, user.email, user.clientId FROM comment, user WHERE comment.parentId = ? AND user.id = comment.userId");
    $exec               = $stmt->execute([$comment['id']]);
    $comment['replies'] = $stmt->fetchAll(\PDO::FETCH_ASSOC);

    foreach ( $comment['replies'] as &$commentReply ) {
        $stmt                       = $pdo->prepare("SELECT user.name FROM thumb, user WHERE thumb.commentId = ? AND user.id = thumb.userId");
        $exec                       = $stmt->execute([$commentReply['id']]);
        $commentReply['likes']      = $stmt->fetchAll(\PDO::FETCH_COLUMN, 0);
// echo "<pre>";
// print_r($commentReply);
// echo "</pre>";
        $commentReply['likeString'] = implode("<br>", $commentReply['likes']);
    }

    $stmt               = $pdo->prepare("SELECT user.name FROM thumb, user WHERE thumb.commentId = ? AND thumb.userId = user.id");
    $exec               = $stmt->execute([$comment['id']]);
    $result             = $stmt->fetchAll(\PDO::FETCH_COLUMN, 0);
    $comment['likedBy'] = implode("<br>", $result);
}
// echo "<pre>";
// print_r($comments);
// echo "</pre>";

$earliest = date('Y-m-d H:i:s', strtotime($comments[count($comments) - 1]['createdAt']));
$latest = ( $page == 1 )
    ? date('Y-m-d H:i:s')
    : date('Y-m-d H:i:s', strtotime($comments[0]['createdAt']))
;

$notifications = $db->run("SELECT * FROM notification WHERE projectId = ? AND objectType = 'task' AND updateType = 'update' AND createdAt BETWEEN ? AND ? ORDER BY createdAt DESC LIMIT 0,20", $userProject['id'], $earliest, $latest);

$i = 0;
$tasks = [];
$root = $db->cell("SELECT rowId FROM task WHERE root = 1 AND projectId = ?", $userProject['id']);
foreach ( $notifications as $notification ) {
    $tasks[$i] = $db->row("SELECT * FROM task WHERE id = ?", $notification['objectId']);
    $tasks[$i]['createdAt'] = $notification['createdAt'];
    $tasks[$i]['createdByName'] = $notification['createdByName'];
    $tasks[$i]['category'] = $db->cell("SELECT title FROM task WHERE rowId = ?", $tasks[$i]['parentId']);

    if ( $tasks[$i]['status'] == 'N/A' || $tasks[$i]['status'] == 'Not Started' || $tasks[$i]['title'] == 'Enter Additional Items' || $tasks[$i]['parentId'] == $root ) {
        unset($tasks[$i]);
        continue;
    }

    $i++;
}

$comments = array_merge($comments, $tasks);

usort($comments, "cmp");