<?php

namespace App\Services;

use App\Authority;
use App\Interfaces\UserFilter;
use App\OracleBasCustomer;
use App\Owner;
use App\OwnerPriceDirectLogistic;
use App\OwnerPriceExpress;
use App\OwnerPriceLogistic;
use App\OwnerPriceOperation;
use App\OwnerPriceSystem;
use App\OwnerStoragePriceModel;
use App\Services\common\BatchUpdateService;
use App\User;
use Carbon\Carbon;
use Doctrine\DBAL\Exception\DatabaseObjectExistsException;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use App\Traits\ServiceAppAop;


class OwnerService implements UserFilter
{
    use ServiceAppAop;

    protected $modelClass = Owner::class;
    /** @var CacheService $cacheService */
    private $cacheService;

    function __construct()
    {
        $this->instant($this->cacheService, 'CacheService');
    }

    /*
     * array | string $column
     * 默认一些select字段,可传递string 或 array来指定select字段
     */
    public function getIntersectPermitting(array $column = ['id', 'name'])
    {
        $ownerIds = app('OwnerService')->getIdArr();
        return $this->cacheService->getOrExecute('OwnersAll_IdName' . md5(json_encode($column) . json_encode($ownerIds)), function () use ($column, $ownerIds) {
            if (empty($ownerIds)) return new Collection();
            return Owner::query()->select($column)->whereIn('id', $ownerIds)->whereNull('deleted_at')->get();
        }, config('cache.expirations.owners'));
    }

    /**
     *同步WMS全部货主至WAS
     */
    public function syncOwnersData()
    {
        $basCustomers = OracleBasCustomer::query()
            ->select('CUSTOMERID', 'DESCR_C')
            ->where('DESCR_C', 'not like', '%换ERP%')
            ->where('DESCR_C', 'not like', '%退仓%')
            ->where('CUSTOMER_TYPE', 'OW')
            ->get();
        $ownerCount = Owner::query()->count();
        if (count($basCustomers) == $ownerCount) return null;
        foreach ($basCustomers as $basCustomer) {
            $owner = Owner::query()->where('code', $basCustomer['customerid'])->first();
            if (!isset($owner)) {
                Owner::query()->create([
                    'code' => $basCustomer['customerid'],
                    'name' => $basCustomer['descr_c'],
                    'created_at' => Carbon::now()->format('Y-m-d H:i:s'),
                ]);
                continue;
            }
            if ($owner['name'] != $basCustomer['descr_c']) {
                $owner->update([
                    'code' => $basCustomer['customerid'],
                    'name' => $basCustomer['descr_c'],
                ]);
            }
        }
        $owners = Owner::query()->select('id', 'name')->get();
        return $owners;
    }

    public function first(array $params, array $rules = [])
    {
        return $this->cacheService->getOrExecute('OwnersFirst' . md5(json_encode($params), json_encode($rules)), function () use ($params, $rules) {
            $owner = Owner::query();
            foreach ($params as $column => $value) {
                if (!isset($rules[$column])) $owner->where($column, $value);
                else {
                    switch ($rules[$column]) {
                        case "or":
                            $owner->orWhere($column, $value);
                            break;
                    }
                }
            }
            return $owner->first();
        }, config('cache.expirations.rarelyChange'));
    }

    public function find($id, $with = [])
    {
        return Owner::query()->with($with)->find($id);
    }

    public function update(Owner $owner, array $values, array $related = [])
    {
        if ($related["ownerStoragePriceModels"] ?? false) $owner->ownerStoragePriceModels()->sync($related["ownerStoragePriceModels"]);
        return $owner->update($values);
    }

    public function create(array $params, array $related = [])
    {
        /** @var Owner $owner */
        $owner = Owner::query()->create($params);
        if ($related["ownerStoragePriceModels"] ?? false) $owner->ownerStoragePriceModels()->syncWithoutDetaching($related["ownerStoragePriceModels"]);
        return $owner;
    }

    public function firstOrCreate(array $params, array $values = null)
    {
        if (!$values) return Owner::query()->whereNull("deleted_at")->firstOrCreate($params);
        return Owner::query()->whereNull("deleted_at")->firstOrCreate($params, $values);
    }


    public function 获取订单跟踪的货主()
    {
        return Owner::query()->with('orderTrackingOwner')->whereHas('orderTrackingOwner', function ($query) {
            $query->where('status', '启用');
        })->get();
    }

    public function getByWmsOrders($orderHeaders)
    {
        $customerIds = array_unique(data_get($orderHeaders, '*.customerid'));
        $customerIds = array_diff($customerIds, [null, '', '*']);
        $owners = Owner::query()->whereIn('code', $customerIds)->get();

        if ($owners->count() < count($customerIds)) {
            $customerIds = array_diff($customerIds, data_get($owners, '*.code'));
            $owner_list = $this->createByWmsCustomerIds($customerIds);
            $owners = $owners->concat($owner_list);
        }
        return $owners;
    }

    public function createByWmsCustomerIds($codes)
    {
        if (!$codes) {
            return [];
        }
        $basCustomer = OracleBasCustomer::query()
            ->where('Customer_Type', 'OW')
            ->whereIn('CustomerID', $codes)
            ->get();

        $insert_params = [];
        $created_at = Carbon::now()->format('Y-m-d H:i:s');
        foreach ($basCustomer as $item) {
            $insert_params[] = [
                'code' => $item->customerid,
                'name' => $item->descr_c,
                'created_at' => $created_at,
            ];
        }

        try {
            if (count($insert_params) > 0) {
                $this->insert($insert_params);
                app('LogService')->log(__METHOD__, __FUNCTION__, '批量创建 owner ' . count($insert_params) . json_encode($insert_params));
            }
        } catch (\Exception $e) {
            app('LogService')->log(__METHOD__, __FUNCTION__, '批量创建 owner error' . json_encode($insert_params) . '||' . $e->getMessage() . '||' . $e->getTraceAsString());
        } finally {
            return Owner::query()->whereIn('code', $codes)->get();
        }
    }

    public function insert($fillables)
    {
        return Owner::query()->insert($fillables);
    }

    public function getAuthorizedOwners()
    {
        return Owner::query()->whereIn('id', app("OwnerService")->getQuery())->get();
    }

    public function get(array $params, array $withs = null, bool $authority = true, bool $notShowSoftDelete = false, $user = null)
    {
        return Cache::remember(
            'owner_' . md5(json_encode($params) . json_encode($withs) . $authority . $notShowSoftDelete . json_encode($user))
            , config('cache.expirations.rarelyChange')
            , function () use ($params, $withs, $authority, $notShowSoftDelete) {
            $query = $this->query($this->getQueryConstructor($withs, $authority, $notShowSoftDelete), $params);
            return $query->get();
        });
    }

    private function getQueryConstructor(array $withs, bool $authority, bool $notShowSoftDelete): Builder
    {
        $query = Owner::query();
        if ($withs) $query->with($withs);
        if ($authority) {
            $ids = $this->getIdArr();
            if (count($ids) > 0) {
                $query->whereIn("id", $ids);
            } else {
                $query->where("id", 0);
            }
        }
        if ($notShowSoftDelete) $query->whereNull('deleted_at');
        return $query;
    }

    public function paginate(array $params, array $withs = null, bool $authority = true, bool $notShowSoftDelete = false)
    {
        $query = $this->query($this->getQueryConstructor($withs, $authority, $notShowSoftDelete), $params)->
        orderByDesc("id");
        return $query->paginate($params["paginate"] ?? 50);
    }

    private function query(Builder $builder, array $params)
    {
        foreach ($params as $column => $param) {
            if ($column == 'paginate' || $column == 'page' || !$param) continue;
            if ($param === true) {
                $builder->whereNotNull($column);
                continue;
            }
            if ($param === false) {
                $builder->whereNull($column);
                continue;
            }
            if ($column == 'created_at_start') {
                $builder->where("created_at", ">=", $param . ":00");
                continue;
            }
            if ($column == 'created_at_end') {
                $builder->where("created_at", "<=", $param . ":59");
                continue;
            }
            if ($column == 'contract_number') {
                $builder->whereHas("contracts", function ($query) use ($param) {
                    /** @var Builder $query */
                    $query->where("contract_number", "like", $param . "%");
                });
                continue;
            }
            if ($column == 'using_type') {
                $builder->whereHas("ownerStoragePriceModels", function ($query) use ($param) {
                    /** @var Builder $query */
                    $query->where("using_type", $param);
                });
                continue;
            }
            if ($column == 'customers') {
                if (is_array($param)) $builder->whereIn('customer_id', $param);
                else $builder->where('customer_id', $param);
                continue;
            }
            if ($column == 'ids') {
                if (is_array($param)) $builder->whereIn('id', $param);
                else $builder->where('id', $param);
                continue;
            }
            if ($column == 'owners') {
                if (is_array($param)) $builder->whereIn('owner_id', $param);
                else $builder->where('owner_id', $param);
                continue;
            }
//            if ($column == 'user_work_group'){
//                $builder->where("user_workgroup_id",$param);
//                continue;
//            }
            if ($column == 'kcGroup') {
                $builder->whereHas("departmentObligationOwner", function ($query) use ($param) {
                    $query->where('obligation_code', 'kc')->where('department_id', $param);
                });
                continue;
            }
            if ($column == 'jgGroup') {
                $builder->whereHas("departmentObligationOwner", function ($query) use ($param) {
                    $query->where('obligation_code', 'jg')->where('department_id', $param);
                });
                continue;
            }
            if ($column == 'fhGroup') {
                $builder->whereHas("departmentObligationOwner", function ($query) use ($param) {
                    $query->where('obligation_code', 'fh')->where('department_id', $param);
                });
                continue;
            }
            if ($column == 'thGroup') {
                $builder->whereHas("departmentObligationOwner", function ($query) use ($param) {
                    $query->where('obligation_code', 'th')->where('department_id', $param);
                });
                continue;
            }
            if ($column == 'shGroup') {
                $builder->whereHas("departmentObligationOwner", function ($query) use ($param) {
                    $query->where('obligation_code', 'sh')->where('department_id', $param);
                });
                continue;
            }
            if (is_array($param)) $builder->whereIn($column, $param);
            else $builder->where($column, $param);
        }
        return $builder;
    }

    public function getOwnerByCodes($codes)
    {
        $collect = collect();
        if (count($codes) == 0) return $collect;
        foreach ($codes as $code) {
            $collect = $collect->push($this->getOwnerByCode($code));
        }
        return $collect;
    }

    public function getOwnerByCode($code)
    {
        return Cache::remember("getOwnerByCode_{$code}", config('cache.expirations.owners'), function () use ($code) {
            $owner = Owner::query()->where('code', $code)->first();
            if ($owner) return $owner;
            $basCustomer = app('OracleBasCustomerService')->first(['Customer_Type' => 'OW', 'CustomerID' => $code]);
            if (!$basCustomer) return null;
            if ($basCustomer && $basCustomer['active_flag'] == 'Y') return Owner::query()
                ->create(['name' => $basCustomer['descr_c'], 'code' => $basCustomer['customerid']]);
            $deleted_at = Carbon::now()->toDateTimeString();
            if ($basCustomer && $basCustomer['active_flag'] == 'N') return Owner::query()
                ->create(['name' => $basCustomer['descr_c'], 'code' => $basCustomer['customerid'], 'deleted_at' => $deleted_at]);
        });
    }

    public function codeGetOwner($code)
    {
        return app(CacheService::class)->getOrExecute("owner_" . $code, function () use ($code) {
            return Owner::query()->firstOrCreate(["code" => $code], ["code" => $code, "name" => $code]);
        });
    }

    /**
     * 向FLUX同步推送WAS本地录入信息
     *
     * @param array|Owner|integer $owner
     * @return bool
     */
    public function syncPush($owner)
    {
        if (is_array($owner)) {
            $owner = new Owner();
            foreach ($owner as $column => $value) {
                $owner[$column] = $value;
            }
        }
        if (is_numeric($owner)) {
            $owner = Owner::query()->find($owner);
            if (!$owner) return false;
        }
        $wms = DB::connection("oracle")->selectOne(DB::raw("SELECT CUSTOMERID FROM BAS_CUSTOMER WHERE CUSTOMER_TYPE = ? AND CUSTOMERID = ?"), ["OW", $owner->code]);
        if (!$wms && $owner->code) {
            $query = DB::raw(<<<sql
    INSERT INTO BAS_CUSTOMER(CUSTOMERID,CUSTOMER_TYPE,DESCR_C,ADDTIME,EDITTIME,ADDWHO)
        VALUES(?,?,?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?)
sql
            );
            $date = date('Y-m-d H:i:s');
            DB::connection("oracle")->insert($query, [$owner->code, 'OW', $owner->name, $date, $date, 'WAS-' . (Auth::user() ? Auth::user()['name'] : 'SYSTEM')]);
        }
        return true;
    }


    public function syncUpdate($owner)
    {
        if (is_array($owner)) {
            $owner = new Owner();
            foreach ($owner as $column => $value) {
                $owner[$column] = $value;
            }
        }
        if (is_numeric($owner)) {
            $owner = Owner::query()->find($owner);
            if (!$owner) return false;
        }
        $sql = DB::raw(<<<sql
    update BAS_CUSTOMER set ACTIVE_FLAG = ?,EDITTIME = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),EDITWHO = ? where CUSTOMERID = ? and CUSTOMER_TYPE = ?
sql
        );
        $date = date('Y-m-d H:i:s');
        if ($owner && $owner->deleted_at) {
            DB::connection("oracle")->update($sql, ['N', $date, 'WAS-' . (Auth::user() ? Auth::user()['name'] : 'SYSTEM'), $owner->code, 'OW']);
        }
        if ($owner && $owner->deleted_at == null) {
            DB::connection("oracle")->update($sql, ['Y', $date, 'WAS-' . (Auth::user() ? Auth::user()['name'] : 'SYSTEM'), $owner->code, 'OW']);
        }
        return true;
    }

    /**
     * 同步货主时创建权限
     *
     * @param array|Owner $owner
     */
    public function createAuthority($owner)
    {
        Authority::query()->create([
            'name' => "_{$owner['id']}",
            'alias_name' => "(货主：{$owner['name']})",
            'remark' => "(key: _{$owner['id']})",
        ]);
    }

    /**
     * 停用货主时删除权限
     *
     * @param array|Owner $owner
     */
    public function deleteAuthority($owner)
    {
        $authorities = Authority::query()->where('name', "_{$owner['id']}")
            ->where("alias_name", "like", "(货主%")
            ->get(["id"]);
        $ids = array_column($authorities->toArray(), "id");
        DB::table("authority_role")->whereIn("id_authority", $ids)->delete();
        Authority::destroy($ids);
    }

    /**
     * 计费模型变动时更新货主中关联属性
     *
     * @param integer $ownerId
     *
     */
    public function refreshRelevance($ownerId)
    {
        $relevance = [];
        $sql = <<<sql
SELECT 1 FROM owner_storage_price_models a
LEFT JOIN owner_storage_price_model_owner b ON a.id =  b.owner_storage_price_model_id
LEFT JOIN owners c ON b.owner_id = c.id
WHERE (a.operation IS NULL OR a.operation = '') AND c.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql), [$ownerId])) $relevance[] = 0;
        $sql = <<<sql
SELECT 1 FROM owner_price_operations a
LEFT JOIN owner_price_operation_owner b ON a.id =  b.owner_price_operation_id
LEFT JOIN owners c ON b.owner_id = c.id
WHERE (a.operation IS NULL OR a.operation = '') AND c.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql), [$ownerId])) $relevance[] = 1;
        $sql = <<<sql
SELECT 1 FROM owner_price_expresses a
LEFT JOIN owner_price_express_owner b ON a.id =  b.owner_price_express_id
LEFT JOIN owners c ON b.owner_id = c.id
WHERE (a.operation IS NULL OR a.operation = '') AND c.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql), [$ownerId])) $relevance[] = 2;
        $sql = <<<sql
SELECT 1 FROM owner_price_logistics a
LEFT JOIN owner_price_logistic_owner b ON a.id =  b.owner_price_logistic_id
LEFT JOIN owners c ON b.owner_id = c.id
WHERE (a.operation IS NULL OR a.operation = '') AND c.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql), [$ownerId])) $relevance[] = 3;
        $sql = <<<sql
SELECT 1 FROM owner_price_direct_logistics a
LEFT JOIN owner_price_direct_logistic_owner b ON a.id =  b.owner_price_direct_logistic_id
LEFT JOIN owners c ON b.owner_id = c.id
WHERE (a.operation IS NULL OR a.operation = '') AND c.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql), [$ownerId])) $relevance[] = 4;
        $sql = <<<sql
SELECT 1 FROM owner_price_systems a LEFT JOIN owners b ON a.owner_id = b.id
WHERE b.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql), [$ownerId])) $relevance[] = 5;
        Owner::query()->where("id", $ownerId)->update(["relevance" => $relevance]);
    }

    /**
     * 税率变更时附加税率
     *
     * @param Owner|\stdClass $owner
     */
    public function attachTaxRate(Owner $owner)
    {
        OwnerStoragePriceModel::query()->whereHas("owners", function (Builder $query) use ($owner) {
            $query->where("id", $owner->id);
        })->whereNull("tax_rate_id")->update(["tax_rate_id" => $owner->tax_rate_id]);
        OwnerPriceOperation::query()->whereHas("owners", function (Builder $query) use ($owner) {
            $query->where("id", $owner->id);
        })->whereNull("tax_rate_id")->update(["tax_rate_id" => $owner->tax_rate_id]);
        OwnerPriceExpress::query()->whereHas("owners", function (Builder $query) use ($owner) {
            $query->where("id", $owner->id);
        })->whereNull("tax_rate_id")->update(["tax_rate_id" => $owner->tax_rate_id]);
        OwnerPriceLogistic::query()->whereHas("owners", function (Builder $query) use ($owner) {
            $query->where("id", $owner->id);
        })->whereNull("tax_rate_id")->update(["tax_rate_id" => $owner->tax_rate_id]);
        OwnerPriceDirectLogistic::query()->whereHas("owners", function (Builder $query) use ($owner) {
            $query->where("id", $owner->id);
        })->whereNull("tax_rate_id")->update(["tax_rate_id" => $owner->tax_rate_id]);
        OwnerPriceSystem::query()->where("owner_id", $owner->id)->whereNull("tax_rate_id")
            ->update(["tax_rate_id" => $owner->tax_rate_id]);
    }

    /**
     * 税率变更时取消税率
     *
     * @param Owner|\stdClass $owner
     */
    public function removeTaxRate(Owner $owner)
    {
        OwnerStoragePriceModel::query()->whereHas("owners", function (Builder $query) use ($owner) {
            $query->where("id", $owner->id);
        })->update(["tax_rate_id" => null]);
        OwnerPriceOperation::query()->whereHas("owners", function (Builder $query) use ($owner) {
            $query->where("id", $owner->id);
        })->update(["tax_rate_id" => null]);
        OwnerPriceExpress::query()->whereHas("owners", function (Builder $query) use ($owner) {
            $query->where("id", $owner->id);
        })->update(["tax_rate_id" => null]);
        OwnerPriceLogistic::query()->whereHas("owners", function (Builder $query) use ($owner) {
            $query->where("id", $owner->id);
        })->update(["tax_rate_id" => null]);
        OwnerPriceDirectLogistic::query()->whereHas("owners", function (Builder $query) use ($owner) {
            $query->where("id", $owner->id);
        })->update(["tax_rate_id" => null]);
        OwnerPriceSystem::query()->where("owner_id", $owner->id)
            ->update(["tax_rate_id" => null]);
    }

    /**
     * 获取税率 或 税费
     *
     * @param Model $model
     * @param int $ownerId
     * @param float|null $money
     *
     * @return float|null
     */
    public function getTaxRateFee(Model $model, int $ownerId, ?float $money = null): ?float
    {
        $taxRate = null;
        if ($model->tax_rate_id) {
            $model->loadMissing("taxRate");
            $taxRate = $model->taxRate;
        }
        if (!$taxRate) {
            /** @var Model|\stdClass $owner */
            $owner = new Owner();
            $owner->id = $ownerId;
            $owner->load("taxRate");
            $taxRate = $owner->taxRate;
        }
        if (!$taxRate) return null;
        if ($money === null) return $taxRate->value;
        return $money * ($taxRate->value / 100);
    }

    public function changeManualBackStatus($id, $isManual)
    {
        $owner = Owner::query()->find($id);
        if ($isManual == 0) $owner->update(['is_manual_back' => 1]);
        else $owner->update(['is_manual_back' => 0]);
        return $owner;
    }

    public function changeIntervalTime($id, $intervalTime)
    {
        $owner = Owner::query()->find($id);
        $owner->update(['interval_time' => $intervalTime]);
        return $owner;
    }

    function getIdArr(?int $userId = null): array
    {
        if (!$userId) {
            $userId = Auth::id();
        }
        return $this->cacheService->getOrExecute("USER.{$userId}.OWNER.ID", function () use ($userId) {
            return array_column($this->getQuery($userId)->get()->toArray(), "id");
        });
    }

    function getCodeArr(?int $userId = null): array
    {
        $column = "code";
        if (!$userId) {
            $userId = Auth::id();
        }
        return $this->cacheService->getOrExecute("USER.{$userId}.OWNER.CODE", function () use ($userId, $column) {
            return array_column($this->getQuery($userId, $column)->get()->toArray(), $column);
        });
    }

    function getQuery(?int $userId = null, $column = "id"): Builder
    {
        if (!$userId) $userId = Auth::id();
        $query = Owner::query()->select("owners." . $column);
        if (!app("UserService")->checkAdminIdentity($userId) && !app("AuthorityService")->checkAllOwner($userId)) {
            $query->whereHas("users", function ($query) use ($userId) {
                $query->where("users.id", $userId);
            });
        }
        return $query->whereNull("deleted_at");
    }

    public function combineOwners($owners)
    {
        foreach ($owners as $owner) {
            $departmentObligationOwner = $owner->departmentObligationOwner ?? false;
            if (!$departmentObligationOwner) continue;
            foreach ($departmentObligationOwner as $item) {
                if ($item->obligation_code == 'kc') {
                    $owner->kc = $item->department_id;
                    $owner->kcGroup = $item->department ? $item->department->name : '';
                }
                if ($item->obligation_code == 'jg') {
                    $owner->jg = $item->department_id;
                    $owner->jgGroup = $item->department ? $item->department->name : '';
                }
                if ($item->obligation_code == 'th') {
                    $owner->th = $item->department_id;
                    $owner->thGroup = $item->department ? $item->department->name : '';
                }
                if ($item->obligation_code == 'sh') {
                    $owner->sh = $item->department_id;
                    $owner->shGroup = $item->department ? $item->department->name : '';
                }
                if ($item->obligation_code == 'fh') {
                    $owner->fh = $item->department_id;
                    $owner->fhGroup = $item->department ? $item->department->name : '';
                }
            }
        }
        return $owners;
    }

    public function getOwnerGroupUnderOwner($ids): array
    {
        return Owner::query()->select("id")->whereIn("user_owner_group_id", $ids)
            ->pluck("id")->toArray();
    }
}