<?php

/**
 * Copyright (c) NMS PRIME GmbH ("NMS PRIME Community Version")
 * and others – powered by CableLabs. All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at:
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

namespace Modules\ProvBase\Entities;

use App\Observers\BaseObserver;
use Illuminate\Support\Facades\Log;
use Module;
use Request;

class Contract extends \BaseModel
{
    // get functions for some address select options
    use \App\AddressFunctionsTrait;
    use \App\extensions\geocoding\GeoReferencable;
    use \App\Traits\HasTickets;
    use \Modules\SpriSupplierApi\Traits\HasSpriSupplier;

    // The associated SQL table for this Model
    public $table = 'contract';

    // temporary Variables filled during accounting command execution (Billing)
    public $expires = false;			// flag if contract expires this month - used in accounting command
    public $charge = [];				// total charge for each different Sepa Account with net and tax values

    // temporary variable used during daily conversion
    private $changes_on_daily_conversion = false;

    // Via modems -> mtas -> phonenumbers assigned Phonenumbers shown as read-only info field in edit view
    public $guarded = ['newsletter', 'related_phonenrs'];

    public const GROUNDS_FOR_DISMISSAL = ['unknown', 'relocation', 'unsatisfied', 'canceled by us', 'deceased', 'competitor', 'fiber', 'customer recovery denied'];

    public $countryDependingTranslationFields = [
        'national_building_id',
    ];

    // Add your validation rules here
    // TODO: dependencies of active modules (billing)
    public function rules()
    {
        $id = $this->id ?: 0;

        $rules = [
            'number' => 'string|iunique:contract,number,'.$id.',id,deleted_at,NULL|max:32',
            'number2' => 'nullable|string|unique:contract,number2,'.$id.',id,deleted_at,NULL',
            'number3' => 'nullable|string|unique:contract,number3,'.$id.',id,deleted_at,NULL',
            // 'number4' => 'string|unique:contract,number4,'.$id.',id,deleted_at,NULL', 	// old customer number must not be unique!
            'house_number' => 'nullable|string|max:20',
            'salutation' => 'required',
            'company' => 'required_if:salutation,placeholder_salutations_institution',
            'firstname' => 'required_if:salutation,placeholder_salutations_person',
            'lastname' => 'required_if:salutation,placeholder_salutations_person',
            'country_code' => ['regex:/^[A-Z]{2}$/'],
            'email' => 'nullable|email',
            'birthday' => 'nullable|required_if:salutation,placeholder_salutations_person|date_format:Y-m-d',
            'contract_start' => 'date_format:Y-m-d',
            'contract_end' => 'nullable|date_format:Y-m-d', // |after:now -> implies we can not change stuff in an out-dated contract
        ];

        $addressKeys = ['street', 'house_number', 'zip', 'city'];
        foreach ($addressKeys as $key) {
            if (Module::collections()->has('PropertyManagement')) {
                $rules[$key] = 'required_without_all:realty_id,apartment_id';
            } else {
                $rules[$key] = 'required';
            }
        }

        // special rules for smartont contract types (=OTO)
        if (Module::collections()->has('SmartOnt')) {
            return $this->rulesSmartOnt($rules);
        }

        if (Module::collections()->has('BillingBase')) {
            $rules['costcenter_id'] = 'required|numeric|min:1';
        } else {
            $rules['number'] .= '|nullable';
        }

        if (Module::collections()->has('BillingBase') || Module::collections()->has('ProvVoipEnvia')) {
            $rules['birthday'] .= '|required_if:salutation,placeholder_salutations_person';
        }

        return $rules;
    }

    /**
     * Define rules used for SmartOnt contracts (= OTO)
     *
     * @author Patrick Reichel
     */
    protected function rulesSmartOnt($rules)
    {
        $rules['birthday'] = 'nullable';
        $rules['company'] = 'nullable';
        $rules['contract_start'] = 'nullable|date_format:Y-m-d';
        $rules['costcenter_id'] = 'nullable';
        $rules['firstname'] = 'nullable';
        $rules['lastname'] = 'nullable';
        $rules['number'] = 'nullable';
        $rules['number2'] = 'nullable';
        $rules['number3'] = 'nullable';
        $rules['number4'] = 'nullable';
        $rules['salutation'] = 'nullable';

        if ('GESA' == config('smartont.flavor.active')) {
            return $this->rulesSmartOntGesa($rules);
        }
        if ('LFO' == config('smartont.flavor.active')) {
            return $this->rulesSmartOntLfo($rules);
        }
    }

    /**
     * Define rules used for SmartOnt contracts (= OTO) (flavor GESA)
     *
     * @author Patrick Reichel
     */
    protected function rulesSmartOntGesa($rules)
    {
        $type = Request::get('type');
        if (in_array($type, ['OTO_FTTH_FR', 'OTO_OWN', 'OTO_STORAGE'])) {
            $rules['sep_id'] = 'string|unique:contract,sep_id,'.$this->id.',id,deleted_at,NULL';
        }

        if (in_array($type, ['OTO_OWN', 'OTO_STORAGE'])) {
            $rules['sep_id'] = 'nullable|string';
        }

        return $rules;
    }

    /**
     * Define rules used for SmartOnt contracts (= OTO) (flavor LFO)
     *
     * @author Patrick Reichel
     */
    protected function rulesSmartOntLfo($rules)
    {
        $rules['sep_id'] = 'nullable|string';

        return $rules;
    }

    // Name of View
    public static function view_headline()
    {
        return 'Contract';
    }

    // View Icon
    public static function view_icon()
    {
        return '<i class="fa fa-address-book-o"></i>';
    }

    /**
     * Get the header for index table depending on activated modules.
     *
     * @author Patrick Reichel
     */
    protected function getIndexHeader()
    {
        $ret = [];

        // special header set for SmartOnt installations
        if (Module::collections()->has('SmartOnt')) {
            if ('GESA' == config('smartont.flavor.active')) {
                $ret[] = $this->table.'.sep_id';
                $ret[] = $this->table.'.oto_id';
                $ret[] = $this->table.'.oto_status';
                $ret[] = $this->table.'.alex_status';
                $ret[] = $this->table.'.omdf_id';
                $ret[] = $this->table.'.boc_label';
                $ret[] = $this->table.'.bof_label';
                $ret[] = $this->table.'.type';
            }

            $ret[] = 'zip';
            $ret[] = 'city';
            if ('GESA' != config('smartont.flavor.active')) {
                $ret[] = 'district';
            }
            $ret[] = 'street';
            $ret[] = $this->table.'.house_number';
            $ret[] = 'national_building_id';

            return $ret;
        }

        // default header set
        $ret[] = $this->table.'.number';
        $ret[] = 'firstname';
        $ret[] = 'lastname';
        $ret[] = 'company';
        $ret[] = 'email';
        $ret[] = 'phone';
        $ret[] = 'zip';
        $ret[] = 'city';
        $ret[] = 'district';
        $ret[] = 'street';
        $ret[] = $this->table.'.house_number';
        $ret[] = 'national_building_id';

        if (Module::collections()->has('PropertyManagement')) {
            $ret[] = 'apartment.number';
            $ret[] = 'contact_id';
        } else {
            $ret[] = $this->table.'.apartment_nr';
        }

        $ret[] = $this->table.'.additional';
        $ret[] = $this->table.'.contract_start';
        $ret[] = $this->table.'.contract_end';
        $ret[] = $this->table.'.ground_for_dismissal';

        if (Module::collections()->has('BillingBase')) {
            $ret[] = 'costcenter.name';
        }

        $ret[] = 'created_at';

        return $ret;
    }

    // AJAX Index list function
    // generates datatable content and classes for model
    public function view_index_label()
    {
        $bsclass = $this->get_bsclass();

        $ret = [
            'table' => $this->table,
            'index_header' => $this->getIndexHeader(),
            'header' =>  $this->label(),
            'edit' => ['ground_for_dismissal' => 'getGroundForDismissal'],
            'sortsearch' => ['ground_for_dismissal' => ['order' => 'false', 'search' => 'false']],
            'bsclass' => $bsclass,
            'order_by' => ['0' => 'asc'],
        ];

        if (Module::collections()->has('BillingBase')) {
            $ret['eager_loading'] = ['apartment', 'costcenter'];
            $ret['edit']['costcenter.name'] = 'get_costcenter_name';
        }

        if (Module::collections()->has('PropertyManagement')) {
            $ret['edit']['apartment.number'] = 'indexTableApartmentNr';
            $ret['sortsearch'][] = ['apartment.number' => ['order' => 'false', 'search' => 'false']];
            // Disabled because this join (needed for search and order) decreases performance dramatically - Can be enabled for small DBs or by adding an index
            // $ret['join'] = [[
            //     'table' => 'apartment',
            //     'local_key' => $this->table.'.apartment_id',
            //     'foreign_key' => 'contract.id',
            //     // 'select' => ['apartment.number as ap_nr', 'apartment.floor'],
            //     'type' => 'left',
            // ]];
            // $ret['filter']['apartment.number'] = "concat(apartment.floor, '/', apartment.number) ilike ?";
            $ret['edit']['contact_id'] = 'isGroupContractAsString';
            $ret['filter']['contact_id'] = $this->groupContractFilterQuery();
        }

        return $ret;
    }

    public function isGroupContractAsString()
    {
        return $this->group_contract ? trans('view.true') : trans('view.false');
    }

    public function indexTableApartmentNr()
    {
        return $this->apartment ? $this->apartment->floor.'/'.$this->apartment->number : '';
    }

    /**
     * Define OTO Bootstrap color class
     *
     * @return string Bootstrap color class
     *
     * @author Patrick Reichel
     */
    private function getBsclassDfOto()
    {
        if ($this->type == 'OTO_STORAGE') {
            return 'success';
        }

        return match ($this->alex_status) {
            'UNDEFINED' => 'info',
            'BEPREADY' => 'danger',
            'PLUGFREE' => 'warning',
            'PLUGINUSE' => 'success',
            default => 'info',
        };
    }

    /**
     * @return string Bootstrap Color Class
     */
    public function get_bsclass(): string
    {
        // Don't do anything when index table header is determined
        if (! $this->id) {
            return '';
        }

        // special case SmartOnt OTO
        if (in_array($this->type, ['OTO_FTTH_FR', 'OTO_OWN', 'OTO_STORAGE'])) {
            return $this->getBsclassDfOto();
        }

        if ($this->contract_start > date('Y-m-d')) {
            return 'info';
        }

        if ($this->lawsuit) {
            return 'danger';
        }

        if ($this->contract_end && $this->contract_end < date('Y-m-d')) {
            return 'active';
        }

        if ($this->hasWarnings()) {
            return 'warning';
        }

        return 'success';
    }

    /**
     * Check if contract shall be marked in yellow to indicate warnings
     */
    private function hasWarnings(): bool
    {
        if (Module::collections()->has('BillingBase') && $this->items->isEmpty()) {
            return true;
        }

        // Attention: This reduces performance when user shows many entries in index table - 250 seems still be ok, but it's recognizable
        if ($this->shouldDebtsBlockInetAccess()) {
            return true;
        }

        return false;
    }

    /**
     * @return string
     */
    public function label()
    {
        // special case SmartOnt OTO
        if (in_array($this->type, ['OTO_FTTH_FR', 'OTO_OWN', 'OTO_STORAGE'])) {
            return "$this->sep_id – $this->omfd_id";
        }

        // default nmsprime contract
        return $this->number.' - '.$this->firstname.' '.$this->lastname;
    }

    public function get_costcenter_name()
    {
        return $this->costcenter ? $this->costcenter->name : trans('messages.noCC');
    }

    // View Relation.
    public function view_has_many()
    {
        $relationThreshold = config('datatables.relationThreshold');
        $this->setRelationCounts($relationThreshold);
        $i18nContract = trans_choice('view.Header_Contract', 1);

        if (
            Module::collections()->has('SmartOnt') &&
            ('GESA' == config('smartont.flavor.active')) &&
            ('OTO_STORAGE' == $this->type)
        ) {
            $ret[$i18nContract]['Import ONT CSV']['class'] = 'bar';
            $ret[$i18nContract]['Import ONT CSV']['view']['view'] = 'provbase::Modem.uploadCsv';
            $ret[$i18nContract]['Import ONT CSV']['view']['vars']['context'] = [
                'route' => 'Modem.import',
                'method' => 'importOntFromCsv',
                'contract' => $this->id,
                'mimetypes' => [
                    'text/csv',
                ],
            ];
        }

        $ret[$i18nContract]['icon'] = 'pencil';
        $ret[$i18nContract]['Modem']['class'] = 'Modem';
        $ret[$i18nContract]['Modem']['count'] = $this->modems_count;

        $ret[$i18nContract]['Modem']['relation'] = collect([new Modem()]);
        if ($this->modems_count <= $relationThreshold) {
            $this->setRelation('modems', $this->modems()->with('contract')->get());
            $ret[$i18nContract]['Modem']['relation'] = $this->modems;
        }

        if (Module::collections()->has('BillingBase')) {
            $ret[$i18nContract]['Item']['class'] = 'Item';
            $ret[$i18nContract]['Item']['count'] = $this->items_count;
            $ret[$i18nContract]['Item']['relation'] = $this->items_count >= $relationThreshold ?
                collect([new \Modules\BillingBase\Entities\Item()]) :
                $this->items;

            $ret[$i18nContract]['SepaMandate']['class'] = 'SepaMandate';
            $ret[$i18nContract]['SepaMandate']['relation'] = $this->sepamandates;

            $ret['Billing']['icon'] = 'line-chart';
            $ret['Billing']['Item'] = $ret[$i18nContract]['Item'];
            $ret['Billing']['SepaMandate']['class'] = 'SepaMandate';
            $ret['Billing']['SepaMandate']['relation'] = $this->sepamandates;

            if (Module::collections()->has('PropertyManagement')) {
                if ($this->group_contract) {
                    $ret[$i18nContract]['Modem']['options']['hide_create_button'] = 1;
                    $ret[$i18nContract]['Modem']['info'] = trans('propertymanagement::messages.groupContract.modem');

                    $tabName = trans('propertymanagement::view.propertyManagement');
                    $ret[$tabName]['Realty']['class'] = 'Realty';
                    $ret[$tabName]['Realty']['relation'] = $this->realties;
                }

                // Display info that TV fees are potentially charged by group contract if this contract belongs to one
                $belongsToGroupContract = $this->belongsToGroupContract();
                if ($belongsToGroupContract) {
                    $msg = trans('propertymanagement::messages.groupContract.item');
                    $ret[$i18nContract]['Item']['info'] = $msg;
                    $ret['Billing']['Item']['info'] = $msg;
                }
            }

            if (Module::collections()->has('OverdueDebts')) {
                // resulting outstanding amount
                $ret[$i18nContract]['DebtResult']['view']['view'] = 'overduedebts::Debt.result';
                $resultingDebt = $this->resultingDebtLabelInfo();
                $ret[$i18nContract]['DebtResult']['view']['vars']['debt'] = moneyFormat($resultingDebt['amount']);
                $ret[$i18nContract]['DebtResult']['view']['vars']['bsclass'] = $resultingDebt['bsclass'];

                $ret['Billing']['Debt']['class'] = 'Debt';
                $ret['Billing']['Debt']['count'] = $this->debtCount;
                $ret['Billing']['Debt']['relation'] = $this->debtCount >= $relationThreshold ?
                    collect([new \Modules\OverdueDebts\Entities\Debt()]) : $this->debts;
            }

            $ret['Billing']['Invoice']['class'] = 'Invoice';
            $ret['Billing']['Invoice']['count'] = $this->invoices_count;
            $ret['Billing']['Invoice']['options']['hide_delete_button'] = 1;
            $ret['Billing']['Invoice']['options']['hide_create_button'] = 1;
            $ret['Billing']['Invoice']['relation'] = $this->invoices_count >= $relationThreshold ?
                collect([new \Modules\BillingBase\Entities\Invoice()]) :
                $this->invoices;
        }

        if (Module::collections()->has('SpriSupplierApi')) {
            $this->addSpriOrderTicketPanel($ret, 'S/PRI', ['KUE-AG', 'KUE-LE', 'LAE', 'AEN-LMAE', 'PV']);
        }

        if (Module::collections()->has('Ticketsystem')) {
            $ret['Tickets']['icon'] = 'ticket';
            $this->addViewHasManyTickets($ret, 'Tickets');
            $ret['Tickets']['Modem Tickets']['class'] = 'Ticket';
            $ret['Tickets']['Modem Tickets']['relation'] = $this->modems()->select('id')->with('tickets')->get()->pluck('tickets')->flatten();
            $ret['Tickets']['Modem Tickets']['options']['hide_create_button'] = 1;
            $ret['Tickets']['Modem Tickets']['options']['empty_message'] = trans('view.contract.noModemTickets');
        }

        if (Module::collections()->has('ProvVoipEnvia') &&
            (! Module::collections()->has('PropertyManagement') || (! $this->group_contract))) {
            $ret['envia TEL']['icon'] = 'volume-control-phone';
            $ret['envia TEL']['EnviaContract']['class'] = 'EnviaContract';
            $ret['envia TEL']['EnviaContract']['relation'] = $this->enviacontracts;
            $ret['envia TEL']['EnviaContract']['options']['hide_create_button'] = 1;
            $ret['envia TEL']['EnviaContract']['options']['hide_delete_button'] = 1;

            $ret['envia TEL']['EnviaOrder']['class'] = 'EnviaOrder';
            $ret['envia TEL']['EnviaOrder']['relation'] = $this->_envia_orders;
            $ret['envia TEL']['EnviaOrder']['options']['create_button_text'] = trans('provvoipenvia::view.enviaOrder.createButton');
            $ret['envia TEL']['EnviaOrder']['options']['delete_button_text'] = trans('provvoipenvia::view.enviaOrder.deleteButton');

            // TODO: auth - loading controller from model could be a security issue ?
            $ret['envia TEL']['EnviaAPI']['view']['view'] = 'provvoipenvia::ProvVoipEnvia.actions';
            $ret['envia TEL']['EnviaAPI']['view']['vars']['extra_data'] = \Modules\ProvBase\Http\Controllers\ContractController::_get_envia_management_jobs($this);

            // for better navigation: show modems also in envia TEL blade
            $ret['envia TEL']['Modem'] = $ret[$i18nContract]['Modem'];
        }

        if (Module::collections()->has('Ccc') && Module::collections()->has('BillingBase')) {
            $ret['Documents']['icon'] = 'file-text-o';
            $ret['Documents']['Documents']['view']['view'] = 'ccc::prov.conn_info';
        }

        if (Module::collections()->has('Mail')) {
            $ret['Email']['Email'] = $this->emails;
        }

        if (Module::collections()->has('SmartOnt') && config('smartont.flavor.hasDreamfiberSubscriptions')) {
            $ret[$i18nContract]['DfSubscription']['class'] = 'DfSubscription';
            $ret[$i18nContract]['DfSubscription']['relation'] = $this->dfsubscriptions;
        }

        if (Module::collections()->has('DocumentManagement')) {
            data_set($ret, 'Documents.UploadDocument.view', [
                'view' => 'documentmanagement::Document.upload',
                'vars' => [
                    'model_id' => $this->getKey(),
                    'model_type' => $this::class,
                ],
            ]);

            data_set(
                $ret,
                'Documents.GenerateDocument.html',
                \Livewire\Livewire::mount(
                    'documentmanagement::generate-document',
                    ['contract' => $this->withoutRelations()]
                )
            );

            data_set($ret, 'Documents.Documents', [
                'class' => 'Document',
                'relation' => $this->documents->sortByDesc('id'),
                'options' => [
                    'hide_create_button' => true,
                ],
            ]);
        }

        return $ret;
    }

    public function setRelationCounts($threshold)
    {
        if (Module::collections()->has('BillingBase')) {
            $this->setRelation('invoices', $this->invoices()->orderBy('id', 'desc')->limit($threshold)->get());
            $this->invoices_count = $this->invoices->count();
            $this->setRelation('items', $this->items()->limit($threshold)->get());
            $this->items_count = $this->items->count();
        }

        if (Module::collections()->has('OverdueDebts')) {
            $this->setRelation('debts', $this->debts()->limit($threshold)->get());
            $this->debtCount = $this->debts->count();
        }

        if ($this->relationLoaded('modems')) {
            $this->modems_count = $this->modems->count();

            return;
        }

        $this->modems_count = $this->modems()->limit($threshold)->get('id')->count();
    }

    public function view_belongs_to()
    {
        if (! Module::collections()->has('PropertyManagement')) {
            return;
        }

        if ($this->realty_id) {
            return $this->realty;
        }

        if ($this->apartment_id) {
            return $this->apartment;
        }

        if ($this->modems->isNotEmpty()) {
            return \Modules\PropertyManagement\Entities\Apartment::join('modem', 'apartment.id', 'modem.apartment_id')
                ->join('contract', 'contract.id', 'modem.contract_id')
                ->where('contract.id', $this->id)
                ->whereNull('modem.deleted_at')
                ->whereNull('apartment.deleted_at')
                ->select('apartment.*')
                ->first();
        }
    }

    /*
     * Relations
     */
    public function debts()
    {
        return $this->hasMany(\Modules\OverdueDebts\Entities\Debt::class)->orderBy('date', 'desc')->orderBy('id', 'desc');
    }

    public function unclearedDebts()
    {
        return $this->debts()->where('cleared', false);
    }

    public function modems()
    {
        return $this->hasMany(Modem::class);
    }

    public function dfsubscriptions()
    {
        if (! Module::collections()->has('SmartOnt') && config('smartont.flavor.hasDreamfiberSubscriptions')) {
            throw new \LogicException(__METHOD__.' only callable if module SmartOnt is active and flavor has DfSubscriptions');
        }

        return $this->hasMany(\Modules\SmartOnt\Entities\DfSubscription::class);
    }

    /**
     * related enviacontracts
     */
    public function enviacontracts()
    {
        if (! Module::collections()->has('ProvVoipEnvia')) {
            throw new \LogicException(__METHOD__.' only callable if module ProvVoipEnvia is active');
        } else {
            return $this->hasMany(\Modules\ProvVoipEnvia\Entities\EnviaContract::class);
        }
    }

    /**
     * Get the purchase tariff
     */
    public function phonetariff_purchase()
    {
        return $this->belongsTo(\Modules\ProvVoip\Entities\PhoneTariff::class, 'purchase_tariff');
    }

    /**
     * Get the next purchase tariff
     */
    public function phonetariff_purchase_next()
    {
        return $this->belongsTo(\Modules\ProvVoip\Entities\PhoneTariff::class, 'next_purchase_tariff');
    }

    /**
     * Get the sale tariff
     */
    public function phonetariff_sale()
    {
        return $this->belongsTo(\Modules\ProvVoip\Entities\PhoneTariff::class, 'voip_id');
    }

    /**
     * Get the next sale tariff
     */
    public function phonetariff_sale_next()
    {
        return $this->belongsTo(\Modules\ProvVoip\Entities\PhoneTariff::class, 'next_voip_id');
    }

    /**
     * Get relation to envia orders.
     *
     * @author Patrick Reichel
     */
    protected function _envia_orders()
    {
        if (! Module::collections()->has('ProvVoipEnvia')) {
            throw new \LogicException(__METHOD__.' only callable if module ProvVoipEnvia as active');
        }

        return $this->hasMany(\Modules\ProvVoipEnvia\Entities\EnviaOrder::class)->where('ordertype', 'NOT LIKE', 'order/create_attachment');
    }

    public function mtas()
    {
        return $this->hasManyThrough(\Modules\ProvVoip\Entities\Mta::class, \Modules\ProvBase\Entities\Modem::class);
    }

    public function items()
    {
        return $this->hasMany(\Modules\BillingBase\Entities\Item::class);
    }

    public function items_sorted_by_valid_from_desc()
    {
        return $this->hasMany(\Modules\BillingBase\Entities\Item::class)->orderBy('valid_from', 'desc');
    }

    public function sepamandates()
    {
        return $this->hasMany(\Modules\BillingBase\Entities\SepaMandate::class);
    }

    public function emails()
    {
        return $this->hasMany(\Modules\NmsMail\Entities\Email::class);
    }

    public function get_email_count()
    {
        $tariff = $this->_get_valid_tariff_item_and_count('Internet');

        return $tariff['count'] ? $tariff['item']->product->email_count : 0;
    }

    public function costcenter()
    {
        return $this->belongsTo(\Modules\BillingBase\Entities\CostCenter::class, 'costcenter_id');
    }

    public function salesman()
    {
        return $this->belongsTo(\Modules\BillingBase\Entities\Salesman::class);
    }

    public function invoices()
    {
        return $this->hasMany(\Modules\BillingBase\Entities\Invoice::class);
        // $srs  = SettlementRun::where('verified', '=', '0')->get(['id'])->pluck('id')->all();
        // $hide = $srs ? : 0;
        // return $this->hasMany(\Modules\BillingBase\Entities\Invoice::class)->where('contract_id', '=', $this->id)->where('settlementrun_id', '!=', [$hide]);
    }

    public function cccUser()
    {
        return $this->hasOne(\Modules\Ccc\Entities\CccUser::class);
    }

    public function realties()
    {
        return $this->hasMany(\Modules\PropertyManagement\Entities\Realty::class)->orderBy('street')->orderBy('house_nr');
    }

    public function apartment()
    {
        return $this->belongsTo(\Modules\PropertyManagement\Entities\Apartment::class);
    }

    public function documents()
    {
        return $this->morphMany(\Modules\DocumentManagement\Entities\Document::class, 'model');
    }

    public function correspondenceRecipient()
    {
        return $this->belongsTo(\Modules\Customer1000\Entities\CorrespondenceRecipient::class);
    }

    /**
     * Scopes
     */

    /**
     * Filter contracts that are active within a specified time period (from - to)
     *
     * @param string date - e.g. '2023-08-10'
     * @param string date
     */
    public function scopeActive($query, $from, $to)
    {
        $query->where('contract_start', '<=', $to)
            ->where(function ($query) use ($from) {
                $query->whereNull('contract_end')
                    ->orWhere('contract_end', '>=', $from);
            });
    }

    /**
     * Emulate a belongsTo contract->realty relation consisting actually of chain contract->modem->apartment->realty
     * Note: A direct contract->realty relation can not exist
     *
     * @return \Modules\PropertyManagement\Entities\Realty
     */
    public function getRealtyAttribute()
    {
        if ($this->relationLoaded('realty')) {
            return $this->getRelation('realty');
        }

        $realty = $this->realty();
        $this->setRelation('realty', $realty);

        return $realty;
    }

    public function realty()
    {
        // TODO - Laravel5.8/L5.8: use hasManyThrough or intermediate Tables (see https://laravel.com/docs/5.8/eloquent-relationships#has-one-through and https://laravel.com/docs/5.8/eloquent-relationships#defining-custom-intermediate-table-models)
        return self::join('modem', 'modem.contract_id', 'contract.id')
            ->join('apartment', 'apartment.id', 'modem.apartment_id')
            ->join('realty', 'apartment.realty_id', 'realty.id')
            ->where('contract.id', $this->id)
            ->whereNull('modem.deleted_at')
            ->whereNull('apartment.deleted_at')
            ->whereNull('realty.deleted_at')
            ->select('realty.*')
            ->first();
    }

    public function getGroundForDismissal()
    {
        return $this->ground_for_dismissal ? trans('view.contract.groundsForDismissal.'.$this->ground_for_dismissal) : '';
    }

    /**
     * Get related documenttemplate of given documenttype.
     *
     * @param [type] $documenttype_id
     * @return void
     *
     * @author Patrick Reichel
     */
    public function get_documenttemplate_by_type($documenttype_id)
    {
        if (! \Module::collections()->has('BillingBase')) {
            // there should – because of validation in documenttemplates – be only one of the given type
            return DocumentTemplate::where('documenttype_id', '=', $documenttype_id)->get();
        }

        return $this->costcenter->sepaaccount->get_documenttemplate_by_type($documenttype_id);
    }

    /**
     * Generate use a new user login password
     * This does not save the involved model
     */
    public function generate_password($length = 10)
    {
        $this->password = \App\extensions\php\Password::generate_password($length);
    }

    /**
     * Helper to get the customer number (may be identical with the contract number).
     * As there is no hard coded customer number in database we have to use this mapper. The semantic meaning of number…number4 can be defined in global configuration.
     *
     * @author Patrick Reichel
     *
     * @todo: in this first step the relation is hardcoded within the function. Later on we have to check the mapping against the configuration.
     *
     * @return current customer number
     */
    public function customer_number()
    {
        if (boolval($this->number3) && \Str::lower($this->number3 != 'n/a')) {
            $customer_number = $this->number3;
        } else {
            $customer_number = $this->number;
        }

        return $customer_number;
    }

    /**
     * Helper to get the legacy customer number (may be identical wtih the legacy contract number).
     * As there is no hard coded customer number in database we have to use this mapper. The semantic meaning of number…number4 can be defined in global configuration.
     *
     * @author Patrick Reichel
     *
     * @todo: in this first step the relation is hardcoded within the function. Later on we have to check the mapping against the configuration.
     *
     * @return current customer number
     */
    public function customer_number_legacy()
    {
        if (boolval($this->number4) && \Str::lower($this->number4 != 'n/a')) {
            $customer_number_lecacy = $this->number4;
        } else {
            $customer_number_lecacy = $this->number2;
        }

        return $customer_number_lecacy;
    }

    /**
     * Helper to get all phonenumbers related to contract.
     *
     * @author Patrick Reichel
     */
    public function related_phonenumbers()
    {
        // if voip module is not active: there can be no phonenumbers
        if (! Module::collections()->has('ProvVoip')) {
            return [];
        }

        $phonenumbers_on_contract = [];

        // else: search all mtas on all modems
        foreach ($this->modems as $modem) {
            foreach ($modem->mtas as $mta) {
                foreach ($mta->phonenumbers as $phonenumber) {
                    array_push($phonenumbers_on_contract, $phonenumber);
                }
            }
        }

        return $phonenumbers_on_contract;
    }

    /*
     * Convert a 'YYYY-MM-DD' to Carbon Time Object
     *
     * We use this to convert a SQL start / end contract date to a carbon
     * object. Carbon Time Objects can be compared with lt(), gt(), ..
     *
     * TODO: move this stuff to extensions
     */
    private function _date_to_carbon($date)
    {
        // createFromFormat crashes if nothing given
        if (! boolval($date)) {
            return;
        }

        return \Carbon\Carbon::createFromFormat('Y-m-d', $date);
    }

    /*
     * Check if Carbon date is null
     *
     * NOTE: This is a little bit of pain, but it works.
     *       But string compare to '0000' is even more pain and
     *       also other tutorials point this out to be a freaky problem.
     * See: http://stackoverflow.com/questions/25959324/comparing-null-date-carbon-object-in-laravel-4-blade-templates
     *
     * TODO: move this stuff to extensions
     *
     * Note by Patrick: null dates can either be Carbons or strings with “0000-00-00”/“0000-00-00 00:00:00” or “NULL”
     */
    private function _date_null($date)
    {
        if (! boolval($date)) {
            return true;
        }

        if (is_string($date)) {
            return \Str::startswith($date, '0000');
        }

        // Carbon object
        return ! ($date->year > 1900);
    }

    /**
     * The Daily Scheduling Function
     *
     * Tasks:
     *  1. Check if $this contract end date is expired -> disable internet_access
     *  2. Check if $this is a new contract and activate it -> enable internet_access
     *  3. Change QoS id and Voip id if actual valid (billing-) tariff changes
     *  4. If module waiputv is enabled: Trigger actions against the waipu.tv API if needed.
     *
     * Attention: To avoid endless loops the Observers of Contract & Item need to be disabled before calling save()
     *      as they would call daily_conversion again. Please only adapt this function with testing all cases. See
     *      https://devel.roetzer-engineering.com/confluence/display/LAR/Contract+-+Daily+conversion for further documentation
     *
     * @return none
     *
     * @author Torsten Schmidt, Nino Ryschawy, Patrick Reichel
     */
    public function daily_conversion()
    {
        // ignore SmartOnt OTO
        if (Module::collections()->has('SmartOnt') && in_array($this->type, ['OTO_FTTH_FR', 'OTO_OWN', 'OTO_STORAGE'])) {
            return;
        }

        // we don't check old invalid items that ended before this number of days in past
        // 90 days is a security span in case it was somehow not possible to examine this function for some days
        // To let the contractCommand belatedly correct everything all former items are needed as well
        // (e.g. in case valid_to_fixed=0 and end date would have always set to today)
        $item_max_ended_before = 90;

        Log::debug('Starting daily conversion for contract '.$this->number, [$this->id]);

        if (! Module::collections()->has('BillingBase')) {
            $this->updateInternetAccess();
        } else {
            // Get items by only 1 db query & set them as contract relations to work with them in next functions
            // with that there are no more refresh database queries necessary (items do not have to be reloaded again)
            $productTypesForDailyConversion = \Modules\BillingBase\Entities\Product::productTypesForDailyConversion();
            $items = $this->items()
                ->leftJoin('product', 'product.id', '=', 'item.product_id')
                ->whereIn('product.type', $productTypesForDailyConversion)
                ->where(whereLaterOrEqual('item.valid_to', date('Y-m-d', strtotime("-$item_max_ended_before days"))))
                // ->orderBy('valid_from', 'desc')
                ->select('item.*')
                ->with('product')
                ->get();

            $this->setRelations(['items' => $items]);

            // Task 3: Check and possibly update item's valid_from and valid_to dates
            $this->_update_inet_voip_dates();

            // deprecated - but kept for reference
            // $this->load('items'); $this->fresh();

            // Task 4: Check and possibly change product related data (qos_id, voip, purchase_tariff)
            // for this contract depending on the start/end times of its items
            $this->update_product_related_data();

            if (\Module::collections()->has('WaipuTV')) {
                $controller = new \Modules\WaipuTV\Http\Controllers\WaipuTVItemController();
                $controller->dailyConversionTVItems($this);
            }

            // NOTE: Keep this order! - update network access after all adaptions are made
            // Task 1 & 2 included
            $this->_update_service_access_from_items();

            if (Module::collections()->has('Mail')) {
                $this->_update_email_index();
            }
        }

        if ($this->changes_on_daily_conversion) {
            // Avoid endless loop by disabling observer but add GuiLog entry
            BaseObserver::addLogEntry($this, 'updated');
            $hasTelephonyChanged = $this->isDirty('has_telephony');

            $this->saveQuietly();

            $this->pushToModems($hasTelephonyChanged);
        }
    }

    /**
     * This enables/disables internet_access according to start and end date of the contract.
     * Used if billing is disabled.
     *
     * @author Torsten Schmidt, Nino Ryschawy
     */
    protected function updateInternetAccess()
    {
        // Enable internet access when contract is valid
        if ($this->isValid('now')) {
            if ($this->internet_access) {
                return;
            }

            Log::info("daily: contract: enable internet access for contract $this->number");
            $this->changes_on_daily_conversion = true;
            $this->internet_access = 1;

            return;
        }

        // Disable internet access when contract is not valid
        if (! $this->internet_access) {
            return;
        }

        $this->internet_access = 0;
        $this->changes_on_daily_conversion = true;
        Log::info("daily: contract: disable internet access for contract $this->number");
    }

    /**
     * This enables/disables network/telephony access based on
     * validity of contract
     * existence of currently active items of types Internet and Voip
     *
     * @author Patrick Reichel, Nino Ryschawy
     */
    protected function _update_service_access_from_items()
    {
        $active_tariff_info_internet = $this->_get_valid_tariff_item_and_count('Internet');
        $active_tariff_info_voip = $this->_get_valid_tariff_item_and_count('Voip');

        $active_count_internet = $active_tariff_info_internet['count'];
        $active_count_voip = $active_tariff_info_voip['count'];

        if ($this->isValid('Now')) {
            // valid internet tariff
            if ($active_count_internet && ! $this->internet_access) {
                $this->internet_access = 1;
                $this->changes_on_daily_conversion = true;
                Log::info('daily: contract: enabling internet_access based on active internet/voip items for contract '.$this->id);
            } elseif (! $active_count_internet && $this->internet_access) {
                // no valid internet tariff
                $this->internet_access = 0;
                $this->changes_on_daily_conversion = true;
                Log::info('daily: contract: disabling internet_access based on active internet/voip items for contract '.$this->id);
            }

            if ($active_count_voip && ! $this->has_telephony) {
                $this->has_telephony = 1;
                $this->changes_on_daily_conversion = true;
                Log::info('daily: contract: switch to has_telephony', [$this->id]);
            } elseif (! $active_count_voip && $this->has_telephony) {
                $this->has_telephony = 0;
                $this->changes_on_daily_conversion = true;
                Log::info('daily: contract: switch from has_telephony to no telephony tariff', [$this->id]);
            }
        } else {
            // invalid contract - disable every access
            if ($this->internet_access) {
                $this->internet_access = 0;
                $this->changes_on_daily_conversion = true;
                Log::info('daily: contract: disabling internet_access based on active internet/voip items for contract '.$this->id);
            }

            if ($this->has_telephony) {
                $this->has_telephony = 0;
                $this->changes_on_daily_conversion = true;
                Log::info('daily: contract: Unset has_telephony as contract is invalid!', [$this->id]);
            }
        }
    }

    /**
     * This helper updates dates for Internet & Voip items on this contract under the following conditions:
     *	- valid_from:
     *		- valid_from_fixed is false
     *		- valid_from is before tomorrow
     *		- if both are true: set to tomorrow
     *	- valid_to:
     *		- valid_to_fixed is false
     *		- valid_to is before today
     *		- if both are true: set to today
     *
     *	This way we ensure:
     *		- items with not fixed end dates are valid today
     *		- items with not fixed start dates are not active
     *
     * Attention: Have in mind that changing item dates also fires in ItemObserver::updating()
     * which for example possibly changes contracts (voip_id, purchase_tariff) etc.!
     *
     * @author Patrick Reichel, Nino Ryschawy
     *
     * @return null
     */
    protected function _update_inet_voip_dates()
    {
        // items only exist if Billingbase is enabled
        if (! Module::collections()->has('BillingBase')) {
            return;
        }

        $tomorrow = \Carbon\Carbon::tomorrow();
        $today = \Carbon\Carbon::today();

        foreach ($this->items as $key => $item) {
            if (! $item->product) {
                Log::error("Product of item $item->id (ID) of contract ".$item->contract->number.' (number) is missing');
                unset($this->items[$key]);

                continue;
            }

            // flag to decide if item has to be saved at the end of the loop
            $item_changed = false;
            $type = $item->product->type;

            if (! in_array($type, ['Internet', 'Voip'])) {
                continue;
            }

            // if the startdate is fixed: ignore
            if (! boolval($item->valid_from_fixed)) {
                // set to tomorrow if there is a start date but this is less then tomorrow
                if (! $this->_date_null($item->valid_from)) {
                    $from = $this->_date_to_carbon($item->valid_from);
                    if ($from->lt($tomorrow)) {
                        $new_date = $tomorrow->toDateString();
                        $item->valid_from = $new_date;
                        $item_changed = true;
                        Log::info('contract: changing item '.$item->id.' ('.$item->product->name.') valid_from to '.$new_date.' for Contract '.$this->number, [$this->id]);
                    }
                }
            }

            // if the enddate is fixed: ignore
            if (! boolval($item->valid_to_fixed)) {
                // set to today if there is an end date less than today
                if (! $this->_date_null($item->valid_to)) {
                    $to = $this->_date_to_carbon($item->valid_to);
                    if ($to->lt($today)) {
                        $new_date = $today->toDateString();
                        $item->valid_to = $new_date;
                        $item_changed = true;
                        Log::info('contract: changing item '.$item->id.' ('.$item->product->name.') valid_to to '.$new_date.' for Contract '.$this->number, [$this->id]);
                    }
                }
            }

            // finally: save the change(s)
            if ($item_changed) {
                // avoid endless loop - dont unnecessarily call daily_conversion again
                /* also the following old concerns are vitiated by disabling the observer:
                    * attention: update youngest valid_from items first (to avoid problems in relation with
                    * ItemObserver::update() which else set valid_to smaller than valid_from in some cases)!
                    * and to avoid “Multipe valid tariffs active” warning
                */
                $item->observer_dailyconversion = false;
                BaseObserver::addLogEntry($item, 'updated');
                $item->save();
            }
        }
    }

    /**
     * Update the email indices according to the number of allowed emails,
     * which is derived from the current internet item.
     * An email index of 0 means disabled, 1 is the primary email address.
     *
     * @return none
     *
     * @author Ole Ernst
     */
    protected function _update_email_index()
    {
        $cnt = $this->get_email_count();

        // fast path: set all indices to 0, as no email is allowed
        if (! $cnt) {
            foreach ($this->emails as $email) {
                $email->index = 0;
                $email->save();
            }

            return;
        }

        // remove all email indices, which are already in use
        $used = [];
        foreach ($this->emails as $email) {
            $used[] = $email->index;
        }
        $avail = array_diff(range($cnt, 1), $used);

        // try to fit all email indices into available slots
        foreach ($this->emails as $email) {
            if ($email->index > $cnt) {
                $email->index = $avail ? array_pop($avail) : 0;
                $email->save();
            }
        }
    }

    /**
     * The Monthly Scheduling Function
     *
     * Tasks:
     *  1. monthly QOS transition / change
     *  2. monthly VOIP transition / change
     *
     * “next*” values are initialized with 0 on ItemObserver::creating() and
     * possibly overwritten by ItemObserver::updating() (which can also be executed by daily conversion)
     *
     * So the daily conversion also can change these values – but this is only triggered on updating an item.
     * To write long term changes to DB we have to check all items in this monthly conversion.
     *
     * @return none
     *
     * @author Torsten Schmidt, Patrick Reichel
     */
    public function monthly_conversion()
    {
        // with billing module -> done by daily conversion
        if (Module::collections()->has('BillingBase')) {
            return;
        }

        // ignore SmartOnt OTO
        if (Module::collections()->has('SmartOnt') && in_array($this->type, ['OTO_FTTH_FR', 'OTO_OWN', 'OTO_STORAGE'])) {
            return;
        }

        $contract_changed = false;

        // Tariff: monthly Tariff change – "Tarifwechsel"
        if (
            ($this->next_qos_id > 0) &&
            ($this->qos_id != $this->next_qos_id)
        ) {
            Log::info('monthly: contract: change Tariff for '.$this->id.' from '.$this->qos_id.' to '.$this->next_qos_id);
            $this->qos_id = $this->next_qos_id;
            $this->next_qos_id = null;
            $contract_changed = true;
        }

        // VOIP: monthly VOIP change
        if ($this->next_voip_id > 0) {
            Log::info('monthly: contract: change VOIP-ID for '.$this->id.' from '.$this->voip_id.' to '.$this->next_voip_id);
            $this->voip_id = $this->next_voip_id;
            $this->next_voip_id = null;
            $contract_changed = true;
        }

        if ($contract_changed) {
            $hasTelephonyChanged = $this->isDirty('has_telephony');
            $this->saveQuietly();
            $this->pushToModems($hasTelephonyChanged);
        }
    }

    /**
     * Returns last started actual valid tariff assigned to this contract.
     *
     * @author Patrick Reichel
     *
     * @param  $type  product type as string (e.g. 'Internet', 'Voip', etc.)
     * @return object item
     */
    public function get_valid_tariff($type)
    {
        return $this->_get_valid_tariff_item_and_count($type)['item'];
    }

    /**
     * Returns number of currently active items of given type assigned to this contract.
     *
     * Use this for checks – a value bigger than 1 should be an error and result in special action!
     *
     * @author Patrick Reichel
     *
     * @param  $type  product type as string (e.g. 'Internet', 'Voip', etc.)
     * @return number of active items for given type and this contract
     */
    public function get_valid_tariff_count($type)
    {
        return $this->_get_valid_tariff_item_and_count($type)['count'];
    }

    /**
     * Return last started actual valid tariff and number of active tariffs of given type for this contract.
     *
     * @author Nino Ryschawy, Patrick Reichel
     *
     * @param  $type  product type as string (e.g. 'Internet', 'Voip', etc.)
     * @return array containing two values:
     *               'item' => the last startet tariff (item object)
     *               'count' => integer
     */
    protected function _get_valid_tariff_item_and_count($type)
    {
        if (! Module::collections()->has('BillingBase')) {
            return ['item' => null, 'count' => 0];
        }

        $last = $count = 0;
        $tariff = null;			// item

        // Dont make a DB request when items are already loaded
        if ($this->relationLoaded('items')) {
            $tariffs = $this->items->where('valid_from', '<=', date('Y-m-d'));
        } else {
            $tariffs = $this->items()
                ->join('product as p', 'item.product_id', '=', 'p.id')
                ->select('item.*', 'p.*', 'item.id as id')
                ->where('type', '=', $type)->where('valid_from', '<=', date('Y-m-d'))
                ->where(whereLaterOrEqual('valid_to', date('Y-m-d')));

            if (strtolower($type) == 'voip') {
                $tariffs->with('product.salestariff');
            }

            $tariffs = $tariffs->get();
        }

        if ($tariffs->isEmpty()) {
            return ['item' => null, 'count' => 0];
        }

        foreach ($tariffs as $item) {
            if ($item->product->type != $type) {
                continue;
            }

            if (! $item->isValid('Now')) {
                continue;
            }

            $count++;

            $start = $item->get_start_time();
            if ($start > $last) {
                $tariff = $item;
                $last = $start;
            }
        }

        // This is an error! There should only be one active item per type and contract
        if ($count > 1) {
            Log::warning('There are '.$count.' active items of product type '.$type.' assigned to contract '.$this->number.' ['.$this->id.'].');
        }

        return ['item' => $tariff, 'count' => $count];
    }

    /**
     * Wrapper to call updater helper methods depending on product type of each given item
     * The called methods write product related data (qos_id, next_qos_id, voip_id, next_voip_id, purchase_tariff, next_purchase_tariff)
     * from items to contract depending on item's valid_from & valid_to dates.
     * So the data is available if billing is deactivated; also ProvVoipEnvia uses this data directly
     * (instead of extracting from items).
     *
     * This is called by ItemObserver::updating() (also indirectly by daily conversion) for currently
     * updated items and also by monthly conversion for all items on contract
     *
     * @author Patrick Reichel
     *
     * @param  $items  iterable (array, Collection) containing items
     * @return null
     */
    public function update_product_related_data()
    {
        $valid_tariff = false;

        foreach ($this->items as $item) {
            if (! $item->product) {
                Log::error("Product of item $item->id (ID) of contract ".$item->contract->number.' (number) is missing');

                continue;
            }

            $type = $item->product->type;

            if (! in_array($type, ['Internet', 'Voip'])) {
                continue;
            }

            // check which month is affected by the currently investigated item
            if (
                // check if information is current
                // this is the case for currently active items:
                //	- latest possible startday is today
                //	- closest possible endday is today
                // there should only be one of each type
                ($item->valid_from <= date('Y-m-d')) &&
                (
                    $this->_date_null($item->valid_to) ||
                    ($item->valid_to >= date('Y-m-d'))
                )
            ) {
                // check if there is more than one active item for given type ⇒ this is an error
                // this can happen if one fixes thè start date of one and forgets to fix the end date
                // of an other item
                if (! isset($valid_tariff_info[$type])) {
                    $valid_tariff_info[$type] = $this->_get_valid_tariff_item_and_count($type);
                }

                $valid_tariff = true;

                if ($valid_tariff_info[$type]['count'] > 1) {
                    // this should never occur!!
                    if ($valid_tariff_info[$type]['item']->id != $item->id) {
                        Log::warning('Using newer item '.$valid_tariff_info[$type]['item']->id.' instead of '.$item->id.' to update current data on contract '.$this->number.' ['.$this->id.'].');
                    }
                    $this->_update_product_related_current_data($valid_tariff_info[$type]['item']);
                } else {
                    // default case
                    $this->_update_product_related_current_data($item);
                }

                // if no enddate is set: check if future data has to be changed to
                // this can occur in the following scenario:
                //	1) new item “B” created (that replaces the current item “A”) ⇒ future data changed
                //	2) item “B” deleted ⇒ nothing done
                //  3) end date in item “A” reset to null
                // now we have to replace the future data by data from item “A” (again)
                if ($this->_date_null($item->valid_to)) {
                    $this->_update_product_related_future_data($item);
                }
            }
            // check if information is for the future
            // this should be save because there is max. one of each type allowed
            // but if there is more than one: no problem – in worst case we overwrite next_* values
            // multiple times
            elseif ($item->valid_from > date('Y-m-d')) {
                $this->_update_product_related_future_data($item);
            } else {
                // items finished before today don't update contracts!
                continue;
            }
        }

        if (! $valid_tariff) {
            $this->qos_id = 0;
            $this->changes_on_daily_conversion = true;
        }
    }

    /**
     * Check for (and possibly perform) product related changes in contract for the current month
     *
     * @author Patrick Reichel
     *
     * @param  $item  to be analyzed
     * @return null
     *              Sets global var $changes_on_daily_conversion when contract data has changed
     */
    protected function _update_product_related_current_data($item)
    {
        $contract_changed = false;

        if ($item->product->type == 'Voip') {
            // check if there are changes in state for voip_id and purchase_tariff
            if ($this->voip_id != $item->product->voip_sales_tariff_id) {
                $this->voip_id = $item->product->voip_sales_tariff_id;
                $this->changes_on_daily_conversion = true;
                Log::info('contract: changing voip_id to '.$this->voip_id.' for contract '.$this->number, [$this->id]);
            }
            if ($this->purchase_tariff != $item->product->voip_purchase_tariff_id) {
                $this->purchase_tariff = $item->product->voip_purchase_tariff_id;
                $this->changes_on_daily_conversion = true;
                Log::info('contract: changing purchase_tariff to '.$this->purchase_tariff.' for contract '.$this->number, [$this->id]);
            }
        }

        if ($item->product->type == 'Internet') {
            if ($this->qos_id != $item->product->qos_id) {
                $this->qos_id = $item->product->qos_id;
                $this->changes_on_daily_conversion = true;
                Log::info('contract: changing  qos_id to '.$this->qos_id.' for contract '.$this->number, [$this->id]);
            }
        }
    }

    /**
     * Check for (and possibly perform) product related changes in contract for the next month
     *
     * @author Patrick Reichel, Nino Ryschawy
     *
     * @param  $item  to be analyzed
     * @return null
     *              Sets global var $changes_on_daily_conversion when contract data has changed
     */
    protected function _update_product_related_future_data($item)
    {
        $contract_changed = false;

        if ($item->product->type == 'Voip') {
            // check if there are changes in state for voip_id and purchase_tariff
            if ($this->next_voip_id != $item->product->voip_sales_tariff_id) {
                $this->next_voip_id = $item->product->voip_sales_tariff_id;
                $this->changes_on_daily_conversion = true;
                Log::info('contract: changing next_voip_id to '.$this->next_voip_id.' for contract '.$this->number, [$this->id]);
            }
            if ($this->next_purchase_tariff != $item->product->voip_purchase_tariff_id) {
                $this->next_purchase_tariff = $item->product->voip_purchase_tariff_id;
                $this->changes_on_daily_conversion = true;
                Log::info('contract: changing next_purchase_tariff to '.$this->next_purchase_tariff.' for contract '.$this->number, [$this->id]);
            }
        }

        if ($item->product->type == 'Internet') {
            if ($this->next_qos_id != $item->product->qos_id) {
                $this->next_qos_id = $item->product->qos_id;
                $this->changes_on_daily_conversion = true;
                Log::info('contract: changing next_qos_id to '.$this->next_qos_id.' for contract '.$this->number, [$this->id]);
            }
        }
    }

    /**
     * Push all settings from Contract layer to the related child Modems (for $this)
     * This includes: internet_access, qos_id
     *
     * Called in daily_conversion only if sth changed
     *
     * Note: This allows only 1 tariff qos_id for all modems
     *
     * @param  $hasTelephonyChanged  Flag to trigger config rebuild
     *
     * @author: Torsten Schmidt, Nino Ryschawy, Patrick Reichel
     */
    public function pushToModems($hasTelephonyChanged = false)
    {
        foreach ($this->modems as $modem) {
            $modem->internet_access = $this->internet_access;
            $modem->qos_id = $this->qos_id;

            // this rebuilds configfile, too (at least one relevant field changed)
            if ($modem->getDirty()) {
                $modem->save();

                continue;
            }

            // rebuild/restart if contract phone state has changed, too
            // this is needed e.g. on activation of voip only items
            if ($hasTelephonyChanged || $this->isDirty('has_telephony')) {
                $modem->make_configfile();
                $modem->restart();
            }
        }
    }

    /**
     * Helper to map database numberX fields to description
     *
     * @author Patrick Reichel
     */
    public function get_column_description($col_name)
    {
        // later use global config to get mapping
        $mappings = [
            'number' => 'Contract number',
            'number2' => 'Contract number legacy',
            'number3' => 'Customer number',
            'number4' => 'Customer number legacy',
        ];

        return $mappings[$col_name];
    }

    /**
     * BOOT:
     * - init observer
     */
    public static function boot()
    {
        parent::boot();

        self::observe(new \Modules\ProvBase\Observers\ContractObserver);
    }

    /**
     * Returns start time of item - Note: contract_start field has higher priority than created_at
     *
     * @return int time in seconds after 1970
     */
    public function get_start_time()
    {
        return strtotime($this->contract_start);
    }

    /**
     * Returns start time of item - Note: contract_start field has higher priority than created_at
     *
     * @return int time in seconds after 1970
     */
    public function validToAsInt()
    {
        return $this->contract_end ? strtotime($this->contract_end) : null;
    }

    /**
     * Returns valid sepa mandate for specific timespan
     *
     * @param 	string 		Timespan - LAST (!!) 'year'/'month' or 'now
     * @param 	int 	If Set only Mandates related to specific SepaAccount are considered (related via CostCenter)
     * @return object Valid Sepa Mandate with latest start date
     *
     * @author Nino Ryschawy
     */
    public function get_valid_mandate($timespan = 'now', $sepaaccount_id = 0)
    {
        $mandate = null;
        $last = null;

        foreach ($this->sepamandates as $m) {
            if (! is_object($m)) {
                continue;
            }

            if ($m->disable || ! $m->isValid($timespan)) {
                continue;
            }

            if ($m->costcenter xor $sepaaccount_id) {
                continue;
            }

            if ($sepaaccount_id && ($m->costcenter?->sepaaccount?->id != $sepaaccount_id)) {
                continue;
            }

            // if ($mandate) {
            //     Log::debug("SepaMandate: Multiple valid mandates active for Contract $this->number", [$this->id]);
            // }

            $start = $m->get_start_time();

            if ($last === null || $start > $last) {
                $mandate = $m;
                $last = $start;
            }
        }

        return $mandate;
    }

    /**
     * Get Contracts next possible cancelation dates - dependent of internet, voip or tv item with this order of priority
     *
     * @author Nino Ryschawy
     *
     * @param string 	Date for that cancelation dates shall be retrieved: e.g. for today (for alert in observer)
     *                  or last day of last month (settlement run)
     * @return array case 1: default
     *               case 2: contract was already canceled (important for settlement run) - 'canceled_to' is set
     *
     * NOTE: if date is last day of last month it's automatically assumed that cancelation dates are requested for/from the settlement run
     */
    public function getCancelationDates($date = '')
    {
        $ret = [
            'cancelation_day' => '',
            'canceled_to' => '',
            'end_of_term' => '',
            'maturity' => '',
            'tariff' => null,           // current valid tariff
        ];

        // check if contract was already canceled for settlement run
        if (! $this->isDirty('contract_end') && $this->contract_end && $this->validToAsInt()) {
            $ret['canceled_to'] = $this->contract_end;

            return $ret;
        }

        // e.g. check for current date or for settlement run
        if (! $date) {
            $date = date('Y-m-d');
        } elseif ($date == date('Y-m-d', strtotime('last day of last month'))) {
            // this is important for the filter of the following db query
            $date = date('Y-m-d', strtotime('first day of this month'));
        }

        // get last internet & voip tariff (take last added if multiple valid tariffs)
        // TODO?: get current inet tariff and all tariffs that start after - check if current tariffs pon is reached than take next if exist?
        // TODO: get rid of DB query to improve performance when items are already loaded (during Settlementrun)
        $tariffs = $this->items()
            ->join('product as p', 'item.product_id', '=', 'p.id')
            ->select('item.*', 'p.type', 'p.bundled_with_voip', 'p.name')
            ->whereIn('type', ['Internet', 'Voip', 'TV'])
            ->where(whereLaterOrEqual('item.valid_to', $date))
            ->orderBy('item.valid_from', 'desc')
            ->with('product')
            ->get();

        $inet = $tariffs->where('type', '=', 'Internet')->first();
        $tariff = $inet;

        // use voip tariff if no inet tariff exists or (inet is not bundled with voip and voip was created last)
        // TODO: consider combi product with parent
        if (! $inet || ! $inet->bundled_with_voip) {
            // take last added (voip or inet)
            $voip = $tariffs->where('type', '=', 'Voip')->first();

            if ($voip) {
                $tariff = $inet && ($inet->valid_from >= $voip->valid_from) ? $inet : $voip;
            }
        }

        // Only take TV tariff if no inet and no voip tariff are valid
        if (! $tariff) {
            $tariff = $tariffs->where('type', 'TV')->first();
        }

        if (! $tariff) {
            return $ret;
        }

        // return end_of_term, last cancelation_day, tariff
        $ret['tariff'] = $tariff;

        return array_merge($ret, $tariff->getNextCancelationDate($date));
    }

    /**
     * Return the outstanding amount and the bootstrap class
     *
     * @return array [(Float) amount, (String) bsclass]
     */
    public function resultingDebt()
    {
        // https://stackoverflow.com/questions/17210787/php-float-calculation-error-when-subtracting
        // $sum = $this->unclearedDebts()
        //     ->select('missing_amount')
        //     ->sum('missing_amount');
        // return round($sum, 2);

        $totalAmount = 0;
        foreach ($this->unclearedDebts as $debt) {
            $totalAmount += $debt->missing_amount;
        }

        return round($totalAmount, 2);
    }

    private function resultingDebtLabelInfo()
    {
        $block = false;
        if (config('overduedebts.debtMgmtType') == 'csv') {
            $block = $this->shouldDebtsBlockInetAccess();
        }

        $totalAmount = $this->resultingDebt();

        $bsclass = '';
        if ($block) {
            $bsclass = 'danger';
        } elseif ($totalAmount < 0) {
            $bsclass = 'success';
        } elseif ($totalAmount > 0) {
            $bsclass = 'warning';
        }

        return [
            'amount' => $totalAmount,
            'bsclass' => $bsclass,
        ];
    }

    /**
     * Check if the Contract has Debts that exceed the thresholds of the module configuration and therefore the internet has to be blocked
     *
     * @author Nino Ryschawy
     *
     * @return bool internet should be blocked
     */
    public function shouldDebtsBlockInetAccess()
    {
        if (! Module::collections()->has('OverdueDebts')) {
            return false;
        }

        $conf = app()->make('OverdueDebtsConfig')->get();

        if (! ($conf->import_inet_block_amount || $conf->import_inet_block_debts || $conf->import_inet_block_indicator)) {
            return false;
        }

        $parser = new \Modules\OverdueDebts\Utils\DefaultTransactionParser;
        $debts = clone $this->unclearedDebts;

        // Filter special debts to exclude (special voucher number and customer had less then 4 weeks to pay)
        foreach ($debts as $key => $debt) {
            $arr = $parser->searchNumbers($debt->voucher_nr);

            // In SWIFT bank file specific transactions have be to excluded, but in CSV they shall be considered
            // Date must be smaller than 4 weeks before now/created_at to make sure the customers have enough time to pay
            // Note: Consider created_at date as result could otherwise be marked as red, but internet was not blocked
            if ($arr['exclude'] && (strtotime($debt->date) > strtotime('-4 week', strtotime($debt->created_at)))) {
                $debts->forget($key);
            }
        }

        $posDebtCount = $debts->where('missing_amount', '>', 0)->count();

        $totalAmount = 0;
        foreach ($debts as $debt) {
            $totalAmount += $debt->missing_amount;
        }

        $highestIndicator = $debts->sortByDesc('indicator')->first();
        $highestIndicator = $highestIndicator ? $highestIndicator->indicator : 0;

        $block =
            // Amount threshold is exceeded
            ($conf->import_inet_block_amount && ($totalAmount >= $conf->import_inet_block_amount)) ||
            // More than max num of positive debts
            ($conf->import_inet_block_debts && ($posDebtCount >= $conf->import_inet_block_debts)) ||
            // Highest dunning indicator too high
            ($conf->import_inet_block_indicator && ($highestIndicator >= $conf->import_inet_block_indicator));

        return $block;
    }

    /**
     * Check if contract belongs to a group contract
     * A group contract is a contract just added to charge multiple contracts belonging to one administration by the TV fee
     *
     * NOTE: Returns
     *  false if contract is actually a group contract
     *  true if realty of contract belongs to a contract
     *
     * @author Nino Ryschawy
     *
     * @return bool
     */
    public function belongsToGroupContract()
    {
        if (! Module::collections()->has('PropertyManagement')) {
            return false;
        }

        // Is group contract itself
        if ($this->realty_id) {
            return false;
        }

        $realty = $this->apartment_id ? $this->apartment->realty : $this->realty;

        if (! $realty) {
            return false;
        }

        // TODO: Check if contract is valid?
        return $realty->contract_id ? true : false;
    }

    public function isCanceled()
    {
        return $this->contract_end && $this->contract_end <= date('Y-m-d');
    }

    /**
     * Synchronize address of Realty and Contract as contract address is used in e.g. invoice
     *
     * @param \Modules\PropertyManagement\Entities\Realty
     * @param array  of Contract IDs to update multiple Contracts by one DB query
     */
    public function updateAddressFromProperty($realty = null, $ids = [])
    {
        if (! Module::collections()->has('PropertyManagement')) {
            return;
        }

        if (! $realty) {
            $realty = $this->realty;
        }

        if (! $realty) {
            if (! $this->apartment_id) {
                return;
            }

            $realty = $this->apartment->realty;
        }

        self::whereIn('id', $ids ?: [$this->id])->update([
            'street' => $realty->street,
            'house_number' => $realty->house_nr,
            'zip' => $realty->zip,
            'city' => $realty->city,
            'district' => $realty->district,
        ]);
    }

    public function groupContractFilterQuery()
    {
        return "contract.id in (SELECT id from contract where (CASE WHEN (group_contract = false or group_contract IS NULL) THEN '".trans('view.false')."'::text ELSE '".trans('view.true')."'::text END) ilike ?)";
    }

    /**
     * Compose list of ordered Realties belonging to a group Contract to be displayed on an Invoice
     *
     * @return array
     */
    public function composeRealtyList()
    {
        if ($this->realties->isEmpty()) {
            return [];
        }

        // Initialise array
        foreach ($this->realties as $key => $realty) {
            $realties[$realty->street][] = str_replace(' ', '', strtolower($realty->house_nr));
        }

        // Sort array by street, even/odd housenr and housenr
        foreach ($realties as $street => $housenrs) {
            natsort($housenrs);

            foreach ($housenrs as $housenr) {
                preg_match('/\d*/', $housenr, $nr);

                if (! $nr[0]) {
                    Log::channel('billing')->error(trans('propertymanagement::messages.invoice.invalidRealtyHousenr', ['id' => $realty->id, 'nr' => $realty->house_nr, 'contractnr' => $contract->number]));

                    continue;
                }

                $nr = intval($nr[0]);

                if (isset($list[$street][$nr % 2 ? 'odd' : 'even'][$nr])) {
                    if (is_string($list[$street][$nr % 2 ? 'odd' : 'even'][$nr])) {
                        $list[$street][$nr % 2 ? 'odd' : 'even'][$nr] = [$list[$street][$nr % 2 ? 'odd' : 'even'][$nr], $housenr];
                    } else {
                        $list[$street][$nr % 2 ? 'odd' : 'even'][$nr][] = $housenr;
                    }
                } else {
                    $list[$street][$nr % 2 ? 'odd' : 'even'][$nr] = $housenr;
                }
            }
        }

        // Build groups in case buildings are adjacent (neighbours)
        foreach ($list as $street => $streets) {
            foreach ($streets as $evenOdd => $nrs) {
                $previous = $key = 0;

                foreach ($nrs as $nr => $block) {
                    if ($previous && ($previous + 2 != $nr)) {
                        $key++;
                    }

                    $groupList[$street][$evenOdd][$key][] = $block;

                    $previous = $nr;
                }
            }
        }

        // Take first and last house of each group
        foreach ($groupList as $street => $streets) {
            foreach ($streets as $evenOdd => $groups) {
                foreach ($groups as $group) {
                    if (count($group) == 1) {
                        if (is_string($group[0])) {
                            $objList[] = $street.' '.$group[0];
                        } else {
                            $separator = count($group[0]) == 2 ? ',' : '-';

                            $objList[] = $street.' '.$group[0][0].$separator.end($group[0]);
                        }

                        continue;
                    }

                    $separator = count($group) == 2 ? ',' : '-';

                    $first = is_string($group[0]) ? $group[0] : $group[0][0];
                    $last = is_string(end($group)) ? end($group) : end(end($group));

                    $objList[] = $street.' '.$first.$separator.$last;
                }
            }
        }

        return $objList;
    }

    /**
     * Collect the necessary data for TicketReceiver and Notifications.
     *
     * @return array
     */
    public function getTicketSummary()
    {
        if ($this->street && $this->city) {
            $navi = [
                'link' => "https://www.google.com/maps/search/{$this->street} {$this->house_number}, {$this->zip} {$this->city}",
                'icon' => 'fa-globe',
                'title' => trans('view.Button_Search'),
            ];
        }

        return [
            trans('messages.Contract Number') => [
                'text' => "{$this->number}",
            ],
            trans('messages.Personal Contact') => [
                'text' => "{$this->company} {$this->department} {$this->salutation} {$this->academic_degree} {$this->firstname} {$this->lastname}",
            ],
            trans('messages.Address') => [
                'text' => "{$this->street} {$this->house_number} | {$this->zip} {$this->city} {$this->district}",
                'action' => $navi ?? null,
            ],
            trans('messages.Phone') => [
                'text' => $this->phone ?? null,
                'action' => [
                    'link' => "tel:{$this->phone}",
                    'icon' => 'fa-phone',
                ],
            ],
            trans('messages.mail') => [
                'text' => $this->email ?? null,
                'action' => [
                    'link' => "mailto:{$this->email}",
                    'icon' => 'fa-envelope',
                ],
            ],
        ];
    }

    /**
     * To reduce AJAX Payload, only this subset is loaded.
     *
     * @return array
     */
    public function reducedFields()
    {
        return [
            'id', 'company', 'department', 'salutation', 'academic_degree', 'firstname', 'lastname',
            'street', 'house_number', 'zip', 'city', 'district', 'phone', 'mail', 'lng', 'lat', 'number',
        ];
    }

    public function ticketInfoProperties()
    {
        return collect($this->only([
            'number', 'company', 'firstname', 'lastname', 'zip', 'city', 'district', 'street', 'house_number', 'phone', 'email',
        ]));
    }

    /**
     * Get possible types for form select.
     *
     * @author Patrick Reichel
     */
    public function getTypesForForm()
    {
        if (! Module::collections()->has('SmartOnt')) {
            return [];
        }

        if ('GESA' == config('smartont.flavor.active')) {
            return [
                '' => '',
                'OTO_FTTH_FR' => 'OTO_FTTH_FR',
                'OTO_OWN' => 'OTO_OWN',
                'OTO_STORAGE' => 'OTO_STORAGE',
            ];
        }

        return [];
    }

    /**
     * Get related Phonenumbers of all modems as comma separated string
     */
    public function relatedPns(): string
    {
        if (! Module::collections()->has('ProvVoip')) {
            return '';
        }

        // Get some necessary relations by one DB query as first step to reduce further queries when accessing related models
        $modems = $this->modems()->with([
            'mtas:id,modem_id',
            'mtas.phonenumbers:id,mta_id,country_code,prefix_number,number',
        ])->get();

        $this->setRelation('modems', $modems);
        $pns = [];

        foreach ($modems as $modem) {
            foreach ($modem->related_phonenumbers() as $pn) {
                $pns[] = $pn->asString();
            }
        }

        return implode(', ', $pns);
    }

    /*
     * Creates a string to be used in modem edit contract form field.
     * Used there to move SmartONT ONTs from on contract to another
     *
     * @author Christian Schramm, Patrick Reichel
     */
    public function composeSmartOntOltDescription($contract)
    {
        $description = str("{$contract->zip} {$contract->city}, {$contract->street}")
            // padLeft is important to get correct housenumber order
            ->append(str($contract->house_number)->padLeft(5, ' '));

        if ($contract->type == 'OTO_FTTH_FR') {
            return $description
                ->append(", OTO-ID: {$contract->oto_id} – {$contract->oto_status} – $contract->alex_status");
        }

        if ($contract->type == 'OTO_OWN') {
            return $description;
        }

        return $description->prepend('STORAGE: ');
    }

    /**
     * Add S/PRI Order Ticket panel to an edit view. This method should be called inside
     * the view_has_many() method and adds a relationship panel to the edit blade.
     *
     * @param  array  $ret
     * @param  string  $tabName
     * @param  array  $businessCases
     * @return void
     */
    public function addSpriOrderTicketPanel(&$ret, $tabName = 'Tickets', $businessCases = ['NEU'])
    {
        if (! Module::collections()->has('SpriSupplierApi')) {
            return;
        }

        // Fetch all business cases with their ticket types in one query
        $businessCasesData = \Modules\SpriSupplierApi\Entities\SpriBusinessCase::with('ticketType')
            ->whereIn('case', $businessCases)
            ->get();

        if ($businessCasesData->isEmpty()) {
            return;
        }

        $htmlButtons = '';

        // Create individual buttons for each business case
        foreach ($businessCasesData as $businessCaseData) {
            if (! $businessCaseData->ticket_type_id || ! $businessCaseData->ticketType) {
                continue;
            }

            $url = route('SpriTicket.create', [
                'tickettypes_ids' => $businessCaseData->ticket_type_id,
                'name' => $businessCaseData->ticketType->name.' ['.$businessCaseData->case.']',
                'contract_id' => $this->id,
                'spri' => '1',
            ]);

            $htmlButtons .= '<a href="'.e($url).'" class="btn btn-primary mr-2 mb-2">'.$businessCaseData->ticketType->name.' ['.$businessCaseData->case.']</a>';
        }

        if (empty($htmlButtons)) {
            return;
        }

        // Note: translation makes no sense here, since S/PRI is only a germany
        $ret[$tabName]['S/PRI']['html'] = '<div class="card card-body">'.$htmlButtons.'</div>';
        $ret[$tabName]['S/PRI']['class'] = 'SpriTicket';
        $ret[$tabName]['S/PRI']['relation'] = $this->spri($businessCases)->get();
        $ret[$tabName]['S/PRI']['options']['hide_create_button'] = 1;
        $ret[$tabName]['S/PRI']['options']['hide_delete_button'] = 1;
        $ret[$tabName]['icon'] = 'briefcase';
    }

    /**
     * Get the button text for a specific S/PRI business case.
     *
     * @param  string  $businessCase
     * @return string
     */
    private function getSpriButtonText($businessCase)
    {
        // Fetch the business case data with the associated ticket type
        $businessCaseData = \Modules\SpriSupplierApi\Entities\SpriBusinessCase::with('ticketType')
            ->where('case', $businessCase)
            ->first();

        if (! $businessCaseData || ! $businessCaseData->ticketType) {
            return 'S/PRI Auftrag anlegen ['.$businessCase.']';
        }

        // Use the ticket type name + business case
        return $businessCaseData->ticketType->name.' ['.$businessCase.']';
    }
}
