<?php

namespace Ignite\Core\Models\Report;

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

class TaxReport extends QueryTable
{
    /**
     * @var bool
     */
    protected $allowDecrypt = true;

    /**
     * The columns to show.
     *
     * @return array
     */
    public function columns()
    {
        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,
            ],
            'ssn' => [
                'title' => 'SSN',
                'name' => 'participant.ssn',
                'orderable' => true,
                'exportable' => true,
                'sensitive' => 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'))
            ->distinct()
            ->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)
            ->where(function ($query) {
                $query->where('transaction.type', '=', 'EARNED')
                    ->orWhere('transaction.type', '=', 'MANUAL-RECEIVE');
            })
            ->groupBy($columns);

        return $this->applyScopes($query);
    }
}
