<?php

namespace Espo\Custom\Core\Utils\Database\Orm\Fields;

use Espo\Core\Utils\Util;

class PostalCode extends \Espo\Core\Utils\Database\Orm\Fields\Base
{
    protected function load($fieldName, $entityName)
    {
        $GLOBALS['log']->error('****************************************************************************');
        $subList = ['addressPostalCode', ' ', 'addressPostalCode4'];

        $tableName = Util::toUnderScore($entityName);

        $orderBy1Field = 'addressPostalCodet';
        $orderBy2Field = 'addressPostalCode4';

        $postalCodeName = ucfirst($fieldName);

        $fullList = [];
        $fieldList = [];

        $parts = [];

        foreach ($subList as $subFieldName) {
            $fullList[] = "'".$subFieldName."'";
        }

        $postalCode = $tableName.'.'.Util::toUnderScore('first'.$postalCodeName);
        $postalCode4 = $tableName.'.'.Util::toUnderScore('last'.$postalCodeName);

        $whereString = ''.implode(' OR ', $parts);

        $whereString .= " OR CONCAT({$postalCode}, ' ', {$postalCode4}) {operator} {value}";

        $selectString = $this->getSelect($fullList);

        $GLOBALS['log']->error($selectString);

        $selectString = "REPLACE({$selectString}, '  ', ' ')";

        return [
            $entityName => [
                'fields' => [
                    $fieldName => [
                        'type' => 'varchar',
                        'select' => $selectString,
                        'where' => [
                            'LIKE' => str_replace('{operator}', 'LIKE', $whereString),
                            '=' => str_replace('{operator}', '=', $whereString),
                        ],
                        'orderBy' => "{$tableName}.".Util::toUnderScore($orderBy1Field)." {direction}, {$tableName}.".Util::toUnderScore($orderBy2Field),
                    ],
                ],
            ],
        ];
    }

    protected function getSelect(array $fullList)
    {
        foreach ($fullList as &$item) {
            $rowItem = trim($item, " '");

            if (!empty($rowItem)) {
                $item = 'IFNULL('.$item.", '')";
            }
        }

        $select = 'TRIM(CONCAT('.implode(', ', $fullList).'))';

        return $select;
    }
}