<?php

namespace bff\modules\bills;

use func;
use Users;
use Model as BaseModel;

class Model extends BaseModel implements Consts
{
    /** @var array список шифруемых полей в таблице static::TABLE_BILLS */
    public $cryptBills = [];

    /**
     * Bill model
     * @param int|null $id
     * @param array $columns
     * @param array $with
     * @return \modules\bills\models\Bill | \bff\modules\bills\models\Bill | \bff\db\illuminate\Model | array
     */
    public function bill($id = null, $columns = ['*'], array $with = [])
    {
        $model = $this->model('Bill');
        if (! empty($id)) {
            return $model->one($id, $columns, $with);
        }
        return $model;
    }

    /**
     * Список счетов по фильтру (adm)
     * @param array $aFilterRaw фильтр списка (требует подготовки)
     * @param bool $bCount только подсчет кол-ва
     * @param string $sqlLimit
     * @param string $sqlOrderBy
     * @param bool $sum
     * @return mixed
     */
    public function billsListing(array $aFilterRaw, $bCount = false, $sqlLimit = '', $sqlOrderBy = '', $sum = false)
    {
        $aFilter = [];
        $aBind = [];
        $f = $aFilterRaw;
        if (!empty($f['id']) && $f['id'] > 0) {
            $aFilter['id'] = $f['id'];
        }
        if (!empty($f['item']) && $f['item'] > 0) {
            $aFilter['item_id'] = $f['item'];
        }
        if ($f['uid'] > 0) {
            $aFilter['user_id'] = $f['uid'];
        }
        if (isset($f['status']) && $f['status'] > 0) {
            $aFilter['status'] = $f['status'];
        }
        if (isset($f['type']) && $f['type'] > 0) {
            $aFilter['type'] = $f['type'];
        }
        if (isset($f['svc'])) {
            $svc = explode(':', $f['svc']);
            if (! empty($svc[0]) && ! empty($svc[1])) {
                $aFilter['service_group'] = $svc[0];
                $aFilter['service_key'] = $svc[1];
            }
        }

        if (!empty($f['p_from']) || !empty($f['p_to'])) {
            $from = strtotime($f['p_from']);
            $to = strtotime($f['p_to']);

            if (!empty($from) && $from != -1) {
                $aFilter[] = 'created >= :createdFrom';
                $aBind[':createdFrom'] = date('Y-m-d 00:00:00', $from);
            }
            if (!empty($to) && $to != -1) {
                $aFilter[] = 'created <= :createdTo';
                $aBind[':createdTo'] = date('Y-m-d 23:59:59', $to);
            }
        }

        $aFilter = $this->prepareFilter($aFilter, 'B', $aBind);

        if ($bCount) {
            return (int)$this->db->one_data(
                'SELECT COUNT(B.id) FROM ' . static::TABLE_BILLS . ' B ' . $aFilter['where'],
                $aFilter['bind']
            );
        }
        if ($sum) {
            return $this->db->select_key(
                'SELECT B.type, SUM(B.amount) AS amount, COUNT(B.id) AS cnt
                FROM ' . static::TABLE_BILLS . ' B ' . $aFilter['where'] . ' GROUP BY 1',
                'type',
                $aFilter['bind']
            );
        }

        $phone = '';
        if (method_exists(Users::model(), 'userPhoneCrypted')) {
            $phone = Users::model()->userPhoneCrypted() ? ', BFF_DECRYPT(U.phone_number) AS phone_number ' : ', U.phone_number ';
        }

        return $this->db->select(
            'SELECT B.*, U.name, ' . (Users::model()->userEmailCrypted() ? 'BFF_DECRYPT(U.email) AS email' : 'U.email') . $phone . '
            FROM ' . static::TABLE_BILLS . ' B
                LEFT JOIN ' . Users::TABLE_USERS . ' U ON B.user_id = U.user_id
            ' . $aFilter['where'] .
            (!empty($sqlOrderBy) ? ' ORDER BY B.' . $sqlOrderBy : '') .
            (!empty($sqlLimit) ? ' ' . $sqlLimit : ''),
            $aFilter['bind']
        );
    }

    public function userAutocomplete($q)
    {
        $select = ['user_id'];
        $or = [];
        if (Users::model()->userEmailCrypted()) {
            $select[] = 'BFF_DECRYPT(email) AS email';
            $or[] = 'BFF_DECRYPT(email) LIKE (:q)';
        } else {
            $select[] = 'email';
            $or[] = 'email LIKE (:q)';
        }
        $phone = false;
        if (method_exists(Users::model(), 'userPhoneCrypted')) {
            $phone = true;
            if (Users::model()->userPhoneCrypted()) {
                $select[] = 'BFF_DECRYPT(phone_number) AS phone_number';
                $or[] = 'BFF_DECRYPT(phone_number) LIKE (:q)';
            } else {
                $select[] = 'phone_number';
                $or[] = 'phone_number LIKE (:q)';
            }
        }
        $isTitle = method_exists('Users', 'autocompleteTitleEmail');
        if ($isTitle) {
            $forcePhone = Users::isForcePhone($q);
        }
        $filter = [
            'activated' => 1,
            ':or' => ['(' . join(' OR ', $or) . ')', ':q' => $q . '%'],
        ];
        $filter = $this->prepareFilter($filter);
        $data = $this->db->select(
            'SELECT ' . join(',', $select) . ' 
            FROM ' . Users::TABLE_USERS . $filter['where'] . '
            ORDER BY 2' . ($phone ? ',3' : '') . '
            LIMIT 10',
            $filter['bind']
        );
        $result = [];
        foreach ($data as $v) {
            $result[ $v['user_id'] ] = $isTitle ? Users::autocompleteTitleEmail($v, $forcePhone) : $v['email'];
        }
        return $result;
    }

    /**
     * Список счетов по фильтру (frontend)
     * @param array $filter фильтр списка
     * @param bool $countOnly только подсчет кол-ва
     * @param string $limit
     * @param string $orderBy
     * @return mixed
     */
    public function billsList(array $filter, $countOnly, $limit = '', $orderBy = 'created DESC')
    {
        $filter = $this->prepareFilter($filter, 'B');
        if ($countOnly) {
            return (int)$this->db->one_data(
                'SELECT COUNT(B.id) FROM ' . static::TABLE_BILLS . ' B ' . $filter['where'],
                $filter['bind']
            );
        }

        return $this->db->select(
            'SELECT B.* FROM ' . static::TABLE_BILLS . ' B ' . $filter['where']
            . (!empty($orderBy) ? ' ORDER BY B.' . $orderBy : '')
            . $limit,
            $filter['bind']
        );
    }

    /**
     * Поиск счета на основе параметров
     * @param array $filters параметры поиска счета
     * @param array $fields список требуемых полей данных
     * @param string $orderBy порядок сортировки
     * @return mixed
     */
    public function billSearch(array $filters, $fields = ['*'], $orderBy = 'created DESC')
    {
        $data = $this->db->select_row(static::TABLE_BILLS, $fields, $filters, $orderBy, 1, $this->cryptBills);

        if (isset($data['svc_settings'])) {
            $data['svc_settings'] = func::unserialize($data['svc_settings']);
        }

        return $data;
    }

    /**
     * Создаем/обновляем счет
     * @param int $billID ID счета
     * @param array $aData данные
     * @param array $aBind данные подставляемые в запрос
     * @return bool|int
     */
    public function billSave($billID, array $aData, array $aBind = [])
    {
        if (empty($aData)) {
            return false;
        }
        if (isset($aData['service_settings'])) {
            if (is_array($aData['service_settings'])) {
                $aData['service_settings'] = json_encode($aData['service_settings'], JSON_UNESCAPED_UNICODE);
            }
        }
        if (isset($aData['svc_settings'])) {
            if (!is_array($aData['svc_settings'])) {
                $aData['svc_settings'] = [];
            }
            $aSvcSettings = $aData['svc_settings'];
            $aData['svc_settings'] = serialize($aSvcSettings);
        }

        if ($billID > 0) {
            $res = $this->db->update(static::TABLE_BILLS, $aData, ['id' => $billID], $aBind, $this->cryptBills);
            if (! empty($res)) {
                if (isset($aSvcSettings)) {
                    $aData['svc_settings'] = $aSvcSettings;
                }
                $this->app->hook('bills.bill.save', $billID, ['data' => $aData, 'bind' => $aBind, 'action' => 'update']);
                return true;
            } else {
                return false;
            }
        } else {
            $aData['created'] = $this->db->now();
            $aData['ip'] = $this->request->remoteAddress();

            $billID = $this->db->insert(static::TABLE_BILLS, $aData, 'id', $aBind, $this->cryptBills);
            if ($billID > 0) {
                if (isset($aSvcSettings)) {
                    $aData['svc_settings'] = $aSvcSettings;
                }
                $this->app->hook('bills.bill.save', $billID, ['data' => $aData, 'bind' => $aBind, 'action' => 'create']);
            }
            return $billID;
        }
    }

    /**
     * Получаем данные о счете
     * @param int|array $billID ID счета или параметры поиска счета
     * @param array $aFields список требуемых полей данных
     * @param int|bool $userID ID пользователя (проверяем на принадлежность к пользователю)
     * @return mixed
     */
    public function billData($billID, $aFields = ['*'], $userID = false)
    {
        if (is_array($billID)) {
            $aFilter = $billID;
            if (!empty($userID) && !isset($aFilter['user_id'])) {
                $aFilter['user_id'] = $userID;
            }
        } else {
            $aFilter = ['id' => $billID];
            if (!empty($userID)) {
                $aFilter['user_id'] = $userID;
            }
        }

        if (empty($aFields)) {
            $aFields = ['*'];
        }
        if (!is_array($aFields)) {
            $aFields = [$aFields];
        }
        if (!empty($this->cryptBills)) {
            foreach ($aFields as $k => $v) {
                if (in_array($v, $this->cryptBills)) {
                    $aFields[$k] = "BFF_DECRYPT($v) as $v";
                }
            }
        }
        $aFields = join(',', $aFields);

        $aFilter = $this->prepareFilter($aFilter);
        $aData = $this->db->one_array(
            'SELECT ' . $aFields . ' FROM ' . static::TABLE_BILLS . ' ' . $aFilter['where'],
            $aFilter['bind']
        );

        if ($aData && array_key_exists('svc_settings', $aData)) {
            $aData['svc_settings'] = func::unserialize($aData['svc_settings']);
        }
        if ($aData && array_key_exists('service_settings', $aData)) {
            $aData['service_settings'] = json_decode($aData['service_settings'], true);
        }

        return $aData;
    }

    /**
     * Удаляем счет
     * @param int $billID ID счета
     * @return bool
     */
    public function billDelete($billID)
    {
        $res = $this->db->delete(static::TABLE_BILLS, ['id' => $billID]);
        return !empty($res);
    }

    /**
     * Удаление всех счетов
     * @return bool
     */
    public function billsDeleteAll()
    {
        return $this->db->delete(static::TABLE_BILLS, ['1']);
    }
}