<?php

namespace Mnv\Core;

use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * Class ImportService
 * @package Mnv\Core
 */
class ImportService
{

    protected int $projectId = 0;

    public function importExcel(string $inputFileName, $managerId): bool
    {
        $spreadsheet = IOFactory::load($inputFileName);
        $sheetData = $spreadsheet->getActiveSheet()->toArray();


        if (!empty($sheetData)) {
            for ($i = 1; $i < count($sheetData); $i++) {
                if (!empty($sheetData[$i])) {
//                    print_r($sheetData[$i]);
                    /** проект */
                    $this->addProject($sheetData[$i], $managerId);
                    if ($this->projectId) {

                        /** дополнительные поля `domain` | `trello` */
                        $this->addProjectData($sheetData[$i][5], $sheetData[$i][6]);

                        /** клиенты */
                        if (!empty($sheetData[$i][7])) {
                            $this->addProjectClient($sheetData[$i][7], $sheetData[$i][8], $sheetData[$i][9], $sheetData[$i][10]);
                        }

//                        /** оплата */
//                        if (!empty($sheetData[$i][10])) {
//                            $this->addProjectPayment($sheetData[$i][10]);
//                        }
                    }
                }
            }

            return true;
        }

        return false;
    }

// TODO: сделать insert -> projects и получить projectId
    protected function addProject($data, $managerId)
    {
        $project = array_filter([
            'publishedOn'   => !empty($data[1]) ? adjustTime(date('Y-m-d H:i:s', strtotime($data[1])),  true) : adjustTime(date('Y-m-d H:i:s', strtotime('01/01/2021')),  true),
            'publicationEnd'=> adjustTime(date('Y-m-d H:i:s', strtotime($data[2])),  true),
            'title'         => $data[3],
            'category'      => $data[4],
            'state'         => $data[11],
            'type_work'     => $data[12],
            'modifiedBy'    => $managerId,
            'modifiedOn'    => gmdate('Y-m-d H:i:s'),
            'addedBy'       => $managerId,
            'addedOn'       => gmdate('Y-m-d H:i:s'),
        ]);

        $this->projectId = (int)connect('projects')->insert($project);
    }

    // TODO: сделать insert -> project_field_value
    protected function addProjectData($domain, $trello)
    {

        $fields['domain'] = $domain;
        $fields['trello'] = $trello;
        foreach ($fields as $fileName => $value) {
            connect('project_field_value')->insert(['projectId' => $this->projectId, 'fileName' => $fileName, 'value' => $value]);
        }
    }

    protected function addProjectClient($fullName, $telegram, $email, $phone)
    {
        $newClient['fullName']  = $fullName ?? NULL; //fullName, address, telegram, email, phone
        $newClient['telegram']  = $telegram ?? NULL;
        $newClient['email']     = $email ?? NULL;
        $newClient['phone']     = $phone ?? NULL;

        if ($userId = $this->checkClientExists($telegram, $email, $phone)) {
            connect('project_clients')->where('userId', $userId)->update($newClient);
        } else {
            // TODO: сделать insert -> project_clients и получить userId
            if ($userId = connect('project_clients')->insert($newClient)) {
                // TODO: сделать insert -> project_client_tracking и получить userId
                $this->addProjectClientTracking(['projectId' => $this->projectId, 'userId' => $userId]);
            }
        }
    }

    protected function checkClientExists($telegram, $email, $phone)
    {
        connect()->grouped(function($q) use ($telegram, $email, $phone) {
            $q->where('telegram', $telegram)->orWhere('email', $email)->orWhere('phone', $phone);
        });

        return connect('project_clients')->select('userId')->getValue();
    }


    protected function addProjectClientTracking($data)
    {
        connect('project_client_tracking')->insert($data);
    }



    // TODO: сделать insert -> project_payments
    protected function addProjectPayment($payments)
    {
        $payments = explode(' | ', $payments);
        if (!empty($payments)) {
            $newPayment = [];
            foreach ($payments as $payment) {
                $array = explode(':', $payment);
                $newPayment['projectId'] = $this->projectId;
                $newPayment['name']      = $array[0] ?? ''; //name, price, payment, percent
                $newPayment['price']     = !empty($array[1]) ? str_replace(" ", "", $array[1]) : 0.00;
                $newPayment['payment']   = !empty($array[2]) ? adjustTime(date('Y-m-d H:i:s', strtotime(str_replace(" ", "", $array[1]))),  true) : '1970-01-01 00:00:00';
                $newPayment['percent']   = !empty($array[2]) ? str_replace(" ", "", $array[3]) : 0;

                connect('project_payments')->insert($newPayment);
            }
        }

    }

}