<?php

namespace Ignite\Core\Models\Report;

use Ignite\Core\Entities\User;
use Ignite\Core\Models\Grid\QueryTable;

class TotalBalanceReport extends QueryTable
{
    protected $fastExcel = true;

    /**
     * The forms to look up for extra columns.
     * @var array
     */
    protected $forms = [];

    /**
     * The excluded column keys.
     * @var array
     */
    protected $excludedKeys = [
        'username',
    ];

    /**
     * The column keys that should be always visible.
     * @var array
     */
    protected $alwaysVisible = [
        'user_id', 'total',
    ];

    /**
     * The columns to show.
     *
     * @return array
     */
    public function columns()
    {
        // @todo: use themes/<theme>/forms/enrollment.json to filter out
        // fields not used in this application.

        return [
            'user_id' => [
                'title' => 'ID',
                'name' => 'participant.user_id',
                'orderable' => true,
                'exportable' => true,
            ],
            'first' => [
                'title' => 'First',
                'name' => 'participant.first',
                'orderable' => true,
                'exportable' => true,
            ],
            'last' => [
                'title' => 'Last',
                'name' => 'participant.last',
                'orderable' => true,
                'exportable' => true,
            ],
            'type' => [
                'title' => 'Participant Type',
                'name' => 'participant.type',
                'orderable' => true,
                'exportable' => true,
            ],
            'email' => [
                'title' => 'E-mail',
                'name' => 'participant.email',
                'orderable' => true,
                'exportable' => true,
            ],
            'address_1' => [
                'title' => 'Address 1',
                'name' => 'participant.address_1',
                'orderable' => true,
                'exportable' => true,
            ],
            'address_2' => [
                'title' => 'Address 2',
                'name' => 'participant.address_2',
                'orderable' => true,
                'exportable' => true,
            ],
            'city' => [
                'title' => 'City',
                'name' => 'participant.city',
                'orderable' => true,
                'exportable' => true,
            ],
            'state' => [
                'title' => 'State',
                'name' => 'participant.state',
                'orderable' => true,
                'exportable' => true,
            ],
            'postal' => [
                'title' => 'Postal',
                'name' => 'participant.postal',
                'orderable' => true,
                'exportable' => true,
            ],
            'phone1' => [
                'title' => 'Phone',
                'name' => 'participant.phone1',
                'orderable' => true,
                'exportable' => true,
            ],
            'total' => [
                'order' => 1000,
                'title' => 'Total Balance',
                'name' => 'total',
                'searchable' => false,
                'orderable' => true,
                'exportable' => true,
            ],
        ];
    }

    /**
     * The report query.
     *
     * @return \Illuminate\Database\Query\Builder
     */
    public function query()
    {
        $columns = collect($this->getColumns())
            ->keyBy('name')
            ->except('total')
            ->keys()
            ->toArray();

        return $this->getConnection()->table('core_participant as participant')
            ->select($columns)
            ->addSelect($this->getConnection()->raw('FORMAT(SUM(transaction.value),0) as total'))
            ->distinct()
            ->leftJoin('core_user as user', 'user.user_id', '=', 'participant.user_id')
            ->leftJoin('core_transaction as transaction', 'user.user_id', '=', 'transaction.user_id')
            ->whereNotNull('transaction.value')
            ->whereNull('transaction.deleted_at')
            ->where('user.internal', User::TYPE_PARTICIPANT)
            ->groupBy($columns);
    }
}
