<?php

include "../root.class.php";
$auth = new authentication();

$db = new db_safeguard();

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

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'accounts.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 jobcard_sub_accounts exists : {$rowData[3]} <br>";
    $results = $db->query("job_card_sub_accounts", "SELECT * FROM `job_card_sub_accounts` WHERE `name` = '{$rowData[3]}'");
    if ($results->num_rows > 0) {
        echo "job_card_sub_category_id exists : {$rowData[3]} <br>";
        $job_card_sub_category_id = $results->fetch_assoc()['record_id'];
    } else {
        echo "job_card_sub_category_id does not exists and creating new one: {$rowData[3]} <br>";
        $job_card_sub_category_id = $db->query("job_card_sub_accounts", "INSERT INTO `job_card_sub_accounts` (`name`,`status`) VALUES ('{$rowData[3]}',1)");

        echo "job_card_sub_category_id id for: {$rowData[3]} : $category_id <br>";
    }
    // check if suppliers exists
    echo "Checking if user exists : {$rowData[2]} <br>";
    $results1 = $db->query("users", "SELECT * FROM `users` WHERE `username` = '{$rowData[2]}'");
    if ($results1->num_rows > 0) {
        echo "user exists : {$rowData[2]} <br>";
        $user_id = $results1->fetch_assoc()['record_id'];
    } else {
        echo "user does not exists and creating new one: {$rowData[2]} <br>";
        $password = $auth->encrypt_password($rowData[2]);
        $user_id = $db->query("users", "INSERT INTO `users` (`username`,`user_password`) VALUES ('{$rowData[2]}','$password')");

        echo "user id for: {$rowData[2]} : $user_id <br>";
    }


    echo $db->query("job_card_accounts", "INSERT INTO `job_card_accounts`(`name`, `status`, `account_manager_id`) VALUES ('{$rowData[0]}',1,$user_id)");
}
