<?php
$projectId = 9;

echo "<br>fetching sheet: 8243363664842628";

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://api.smartsheet.com/2.0/sheets/8243363664842628");
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer '.SS_API_TOKEN, 'Content-Type: application/json']);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

$sheet = json_decode(curl_exec($ch));
curl_close($ch);

echo "<br>Sheet:<pre>";
print_r($sheet);
echo "</pre>";

$map = [
    'Selection' => 'title',
    'Selected' => 'selected',
    'Priority' => 'priority',
    'N/A' => 'na',
    'Category' => 'category',
    'Room' => 'room',
    'Level' => 'level',
    'Type' => 'type',
    'Vendor' => 'vendor',
    'Link' => 'link',
    'Description' => 'description',
    'Room Image' => 'roomImage',
    'Selection Image' => 'selectionImage',
    'Cost Per Unit' => 'cost',
    'Unit' => 'unit',
    'Quantity' => 'quantity'
];

$columnTitles = [];
foreach ( $sheet->columns as $column ) {
    $columnTitles[$column->id] = $column->title;
}
echo "<br>Columns:<pre>";
print_r($columnTitles);
echo "</pre>";

echo "<br>looping over rows";
$i = 0;
$array = [];
$cellImages = [];
$imageNames = [];
foreach ( $sheet->rows as $row ) {
    $array[$i]['projectId'] = $projectId;
    $array[$i]['rowId'] = $row->id;
echo "<br>rowId: ".$row->id;
echo "<br>looping over cells";
    foreach ( $row->cells as $cell ) {
        if ( $columnTitles[$cell->columnId] === 'Room Image' || $columnTitles[$cell->columnId] === 'Selection Image' ) {
echo "<br>image cell";
            if ( isset($cell->image) ) {
echo "<br>image present: ".$cell->value;
                $array[$i][$map[$columnTitles[$cell->columnId]]] = $cell->value;
                $imageNames[$cell->image->id] = $cell->value;

                $cellImages[] = [
                    'imageId' => $cell->image->id,
                    'height' => $cell->image->height,
                    'width' => $cell->image->width
                ];
    
                continue;
            }
        }
echo "<br>boolean cell. value: ".$cell->value;
        if ( $columnTitles[$cell->columnId] === 'Selected' || $columnTitles[$cell->columnId] === 'Priority' || $columnTitles[$cell->columnId] === 'N/A' ) {
            $array[$i][$map[$columnTitles[$cell->columnId]]] = ( !$cell->value )
                ? 0
                : 1
            ;

            continue;
        }
echo "<br>regular cell. value: ".$cell->value;
        if ( in_array($columnTitles[$cell->columnId], array_keys($map)) ) {
            $array[$i][$map[$columnTitles[$cell->columnId]]] = $cell->value;
        }
    }

echo "<br>saving selection to db";
echo "<br>selection:<pre>";
print_r($array[$i]);
echo "</pre>";
    //save selections array to db
    if ( $exists = $db->cell("SELECT id FROM selection WHERE rowId = ?", $array[$i]['rowId']) ) {
        $db->update('selection', $array[$i], ['rowId' => $array[$i]['rowId']]);
    } else {
        $db->insert('selection', $array[$i]);
    }

    $i++;
}

echo "<br>fetching cellImage URLs";
//fetch cellImage URLs
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://api.smartsheet.com/2.0/imageurls");
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer '.SS_API_TOKEN, 'Content-Type: application/json']);
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($cellImages));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

$response = json_decode(curl_exec($ch));
curl_close($ch);

echo "<br>cellImages:<pre>";
print_r($response);
echo "</pre>";

echo "<br>looping over cellImage URLs";
foreach ( $response->imageUrls as $image ) {
    if ( !file_exists(PATH_SELECTION_ATTACHMENTS.'/'.$imageNames[$image->imageId]) ) {
echo "<br>cellImage doesn't exist";
        $file = file_get_contents($image->url);
        file_put_contents(PATH_SELECTION_ATTACHMENTS.'/'.$imageNames[$image->imageId], $file);
echo "<br>cellImage saved";
    }
}