<?php
    if ( !$_POST['projectId'] ) {
        http_response_code(400); //bad request
        exit;
    }

    $userProject = $db->row("SELECT * FROM project WHERE id = ?", $_POST['projectId']);

    if ( time() - strtotime($userProject['updatedAt']) <= 14400 ) {
        http_response_code(403); //forbidden
        exit;
    }

    //check if user is an admin. if not, check if they are assigned to this project
    if ( !$user['admin'] ) {
        if ( !$exists = $db->cell("SELECT id FROM client_project WHERE clientId = ? AND projectId = ?", $user['client']['id'], $_POST['projectId']) ) {
            http_response_code(403); //forbidden
            exit;
        }
    }

    $datetime = date("Y-m-d H:i:s");
$log = "\n$datetime - ".$_SERVER['PHP_SELF']."\n";
    
    $projectId    = $userProject['id'];
    $projectTitle = $userProject['title'];
    $sheetId      = $userProject['sheetId'];
    $zip          = urlencode($userProject['zip']);
$log .= "projectTitle: $projectTitle, projectId: $projectId, sheetId: $sheetId, zip: $zip\n";

    //get job sheet
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, "https://api.smartsheet.com/2.0/sheets/$sheetId");
    curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer '.SS_API_TOKEN, 'Content-Type: application/json']);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$log .= "fetched sheet\n";
    $sheet = json_decode(curl_exec($ch));
    curl_close($ch);

    //find daily log row id
    foreach ( $sheet->rows as $row ) {
        foreach ( $row->cells as $cell ) {
            if ( strtolower($cell->value) == 'daily log' ) {
                $rowId = $row->id;
$log .= "found daily log row. rowId: $rowId\n";
                break;
            }
        }
    }

    //get discussion using rowId
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, "https://api.smartsheet.com/2.0/sheets/$sheetId/rows/$rowId/discussions?include=comments,attachments&includeAll=true");
    curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer '.SS_API_TOKEN, 'Content-Type: application/json']);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$log .= "fetched discussions\n";
    $discussions = json_decode(curl_exec($ch));
    curl_close($ch);

    //build array and save to db
    $i     = 0;
    $ids   = [];
    $array = [];
    foreach ( $discussions->data as $discussion ) {
        foreach ( $discussion->comments as $comment ) {
            $ids[]                       = $comment->id;
            $array[$i]['ssCommentId']    = $comment->id;
            $array[$i]['projectId']      = $userProject['id'];
            $array[$i]['text']           = $comment->text;
            $array[$i]['createdByName']  = $comment->createdBy->name;
            $array[$i]['createdByEmail'] = $comment->createdBy->email;
            $array[$i]['createdAt']      = $comment->createdAt;
            $array[$i]['sheetId']        = $sheetId;
            $array[$i]['rowId']          = $rowId;

            //insert or update comment
            if ( $exists = $db->cell("SELECT id FROM comment WHERE ssCommentId = ?", $array[$i]['ssCommentId']) ) {
                $db->update('comment', $array[$i], [
                    'ssCommentId' => $array[$i]['ssCommentId']
                ]);
$log .= "$i. updated comment. ssCommentId: ".$array[$i]['ssCommentId']."\n";
            } else {
                $db->insert('comment', $array[$i]);
$log .= "$i. inserted comment. ssCommentId: ".$array[$i]['ssCommentId']."\n";
            }
            
            $k = 0;
            foreach ( $comment->attachments as $attachment ) {
                $array[$i]['attachments'][$k]['ssAttachmentId'] = $attachment->id;
                $array[$i]['attachments'][$k]['commentId']      = $db->cell("SELECT id FROM comment WHERE ssCommentId = ?", $comment->id);
                $array[$i]['attachments'][$k]['ssCommentId']    = $comment->id;
                $array[$i]['attachments'][$k]['projectId']      = $userProject['id'];
                $array[$i]['attachments'][$k]['name']           = $attachment->name;
                $array[$i]['attachments'][$k]['mimeType']       = $attachment->mimeType;
                $array[$i]['attachments'][$k]['createdAt']      = $attachment->createdAt;
                $array[$i]['attachments'][$k]['createdByName']  = $attachment->createdBy->name;
                $array[$i]['attachments'][$k]['createdByEmail'] = $attachment->createdBy->email;

                $attachmentId = $attachment->id;

                //save to local filesystem
                $array[$i]['attachments'][$k]['pathname'] = "$attachmentId-".$attachment->name;
                if ( !file_exists(PATH_ATTACHMENTS.'/'.$attachmentId.'-'.$attachment->name) ) {
                    //get attachment url
                    $ch = curl_init();
                    curl_setopt($ch, CURLOPT_URL, "https://api.smartsheet.com/2.0/sheets/$sheetId/attachments/$attachmentId");
                    curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer '.SS_API_TOKEN, 'Content-Type: application/json']);
                    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$log .= "$i.$k. fetched attachment url\n";
                    $response = json_decode(curl_exec($ch));
                    curl_close($ch);

                    $file = file_get_contents($response->url);
                    file_put_contents(PATH_ATTACHMENTS.'/'.$attachmentId.'-'.$attachment->name, $file);
$log .= "$i.$k. downloaded attachment. pathname: ".$attachmentId."-".$attachment->name."\n";
                }

                //insert or update attachment
                if ( $exists = $db->cell("SELECT ssAttachmentId FROM attachment WHERE ssAttachmentId = ?", $array[$i]['attachments'][$k]['ssAttachmentId']) ) {
                    $db->update('attachment', $array[$i]['attachments'][$k], [
                        'ssAttachmentId' => $array[$i]['attachments'][$k]['ssAttachmentId']
                    ]);
$log .= "$i.$k. updated attachment. ssAttachmentId: $attachmentId\n";
                } else {
                    $db->insert('attachment', $array[$i]['attachments'][$k]);
$log .= "$i.$k. inserted attachment. ssAttachmentId: $attachmentId\n";
                }

                $k++;
            }

            //delete orphaned attachments
            if ( is_array($comment->attachments) ) {
                $notIn = implode(',', array_column($comment->attachments, 'id'));
                $sql   = ( $comment->attachments )
                    ? "DELETE FROM attachment WHERE projectId = ".$userProject['id']." AND ssCommentId = ".$comment->id." AND ssAttachmentId NOT IN(".$notIn.")"
                    : "DELETE FROM attachment WHERE projectId = ".$userProject['id']." AND ssCommentId = ".$comment->id
                ;
                $db->run($sql);
$log .= "$i. deleted orphaned attachments not in: ( $notIn )\n";
            }

            $i++;
        }
    }

    //delete orphaned comments
    if ( count($ids) > 0 ) {
        $notIn = implode(',', $ids);
        $cSql  = "DELETE FROM comment WHERE projectId = ".$userProject['id']." AND ssCommentId NOT IN(".$notIn.")";
        $aSql  = "DELETE FROM attachment WHERE projectId = ".$userProject['id']." AND ssCommentId NOT IN(".$notIn.")";
    } else {
        //apparently, there are no comments/daily logs on this sheet
        $cSql  = "DELETE FROM comment WHERE projectId = ".$userProject['id'];
        $aSql  = "DELETE FROM attachment WHERE projectId = ".$userProject['id'];
    }
    $db->run($cSql);
$log .= "deleted orphaned comments not in: ( $notIn )\n";
    $db->run($aSql);
$log .= "deleted orphaned attachments where ssCommentId not in: ( $notIn )\n";

$log .= print_r($array, true);

    //tasks
    $i = 0;
    $tasks = [];

    foreach ( $sheet->columns as $column ) {
        if ( $column->title == 'Primary Column' ) {
            $titleColumnId = $column->id;
$log .= "found primary column. columnId: $titleColumnId\n";
            continue;
        }
        if ( $column->title == 'Status' ) {
            $statusColumnId = $column->id;
$log .= "found status column. columnId: $statusColumnId\n";
            continue;
        }
    }

    $root = false;
    foreach ( $sheet->rows as $row ) {
        foreach ( $row->cells as $cell ) {
            if ( $cell->columnId == $titleColumnId ) {
                if ( !$root ) {
                    if ( str_contains(strtolower($cell->value), 'task list') ) {
                        // echo "root found<br>";
                        $root = $row->id;
$log .= "found root row. rowId: $root\n";
                        $tasks[$i]['root'] = 1;
                    }
                }
                $tasks[$i]['title'] = $cell->value;
            }
            if ( $cell->columnId == $statusColumnId ) {
                $tasks[$i]['status'] = $cell->value;
            }
            $tasks[$i]['rowId']     = $row->id;
            $tasks[$i]['parentId']  = $row->parentId;
            $tasks[$i]['projectId'] = $userProject['id'];
            $tasks[$i]['rowNumber'] = $row->rowNumber;
        }
        $i++;
    }

    $i   = 0;
    $ids = [];
    foreach ( $tasks as $task ) {
        $ids[] = $task['rowId'];
        if ( $exists = $db->cell("SELECT id FROM task WHERE projectId = ? AND rowId = ?", $userProject['id'], $task['rowId']) ) {
            $db->update('task', $task, ['projectId' => $userProject['id'], 'rowId' => $task['rowId']]);
$log .= "$i. updated task. rowId: ".$task['rowId']."\n";
        } else {
            $db->insert('task', $task);
$log .= "$i. inserted task. rowId: ".$task['rowId']."\n";
        }
        $i++;
    }

    //delete orphaned task rows
    $notIn = implode(',', $ids);
    $sql   = ( count($tasks) > 0 )
        ? "DELETE FROM task WHERE projectId = ".$userProject['id']." AND rowId NOT IN(".$notIn.")"
        : "DELETE FROM task WHERE projectId = ".$userProject['id']
    ;
    $db->run($sql);
$log .= "deleted orphaned tasks not in: ( $notIn )\n";
    

    $updatedAt = date('Y-m-d H:i:s');
    $db->update('project', ['updatedAt' => $updatedAt], ['id' => $userProject['id']]);
$log .= "project fetched. datetime: $updatedAt\n";

    $file = fopen(PATH_LOGS.'/btb.fetch-ajax.log', "a");
    fwrite($file, $log);
    fclose($file);

    http_response_code(200); //OK
    exit;
