<?php

namespace Ignite\Core\Models\Report;

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

class TotalEarnedReport 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 = [];

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

    /**
     * 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,
            ],
            'email' => [
                'title' => 'E-mail',
                'name' => 'participant.email',
                'orderable' => true,
                'exportable' => true,
            ],
            'company' => [
                'title' => 'Company',
                'name' => 'participant.company',
                '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' => [
                'title' => 'Total Earned',
                'name' => 'total',
                'orderable' => true,
                'exportable' => true,
                'searchable' => false,
            ],
        ];
    }

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

        $query = $this->getConnection()
            ->table('core_participant as participant')
            ->select($columns)
            ->addSelect($this->getConnection()->raw('FORMAT(SUM(transaction.value), 2) as total'))
            ->leftJoin('core_user as user', 'user.user_id', '=', 'participant.user_id')
            ->leftJoin('core_transaction as transaction', 'user.user_id', '=', 'transaction.user_id')
            ->where('user.internal', User::TYPE_PARTICIPANT)
            ->whereNull('transaction.deleted_at')
            ->where(function ($query) {
                $query->where('transaction.type', '=', 'EARNED')
                    ->orWhere('transaction.type', '=', 'MANUAL-RECEIVE');
            })
            ->groupBy($columns);

        return QueryPermissionFilters::for('core.user.participant.browse')
            ->apply($query, 'participant');
    }
}
