<?php

include "../root.class.php";

$db = new db_safeguard();

require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'stock_data.xlsx';

$spreadsheet = IOFactory::load($inputFileName);
$worksheet = $spreadsheet->getActiveSheet();
$data = [];

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);

    $rowData = [];

    if ($row->getRowIndex() === 1) {
        continue;
    }
    foreach ($cellIterator as $cell) {
        $rowData[] = preg_replace('/[\/]/', ' ', $cell->getValue());
    }
    
    // check ig categorie exists
    echo "Checking if category exists : {$rowData[1]} <br>";
    $results = $db->query("stock_categories", "SELECT * FROM `stock_categories` WHERE `name` = '{$rowData[1]}'");
    if ($results->num_rows > 0) {
        echo "category exists : {$rowData[1]} <br>";
        $category_id = $results->fetch_assoc()['record_id'];
    } else {
        echo "category does not exists and creating new one: {$rowData[1]} <br>";
        $category_id = $db->query("stock_categories", "INSERT INTO `stock_categories` (`name`) VALUES ('{$rowData[1]}')");

        echo "category id for: {$rowData[1]} : $category_id <br>";
    }
    // check if suppliers exists
    echo "Checking if supplier exists : {$rowData[7]} <br>";
    $results = $db->query("suppliers", "SELECT * FROM `suppliers` WHERE `name` = '{$rowData[7]}'");
    if ($results->num_rows > 0) {
        echo "supplier exists : {$rowData[7]} <br>";
        $supplier_id = $results->fetch_assoc()['record_id'];
    } else {
        echo "supplier does not exists and creating new one: {$rowData[7]} <br>";
        $supplier_id = $db->query("suppliers", "INSERT INTO `suppliers`(`name`) VALUES ('{$rowData[7]}')");

        echo "supplier id for: {$rowData[7]} : $supplier_id <br>";
    }


    echo $db->query("stock", "INSERT INTO `stock`( `category_id`, `supplier_id`, `description`, `packging_size`, `cost_price`, `max`, `min`,`barcode`) VALUES ('$category_id','$supplier_id','{$rowData[2]}','{$rowData[4]}','{$rowData[3]}','{$rowData[5]}','{$rowData[6]}','{$rowData[0]}')");
}
