<?php

namespace Ignite\Core\Models\Report;

use Ignite\Core\Entities\Filters\QueryPermissionFilters;
use Ignite\Core\Entities\Transaction;
use Ignite\Core\Entities\User;
use Ignite\Core\Models\Grid\QueryTable;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

/**
 */
class TaxReport extends QueryTable
{
    /**
     * This needs to be false for SSN to be decrypted on export.
     *
     * @var boolean
     */
    protected $fastExcel = false;

    /**
     * @var bool
     */
    protected $allowDecrypt = true;

    /**
     * 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.

        $columns = [
            '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,
            ],
            'country' => [
                'title' => 'Country',
                'name' => 'participant.country',
                '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,
            ],
            'tax_year' => [
                'title' => 'Tax Year',
                'name' => 'year.tax_year',
                'orderable' => true,
                'exportable' => true,
                'searchable' => false,
            ],
            'total_earned' => [
                'title' => 'Total Earned Points',
                'name' => 'total_earned',
                'orderable' => true,
                'exportable' => true,
                'searchable' => false,
            ],
            'total_earned_usd' => [
                'title' => 'Total Earned USD',
                'name' => 'total_earned_usd',
                'orderable' => true,
                'exportable' => true,
                'searchable' => false,
            ],
            'total_redeemed' => [
                'title' => 'Total Redeemed Points',
                'name' => 'total_redeemed',
                'orderable' => true,
                'exportable' => true,
                'searchable' => false,
            ],
            'total_redeemed_usd' => [
                'title' => 'Total Redeemed USD',
                'name' => 'total_redeemed_usd',
                'orderable' => true,
                'exportable' => true,
                'searchable' => false,
            ],
        ];

        return $columns;
    }

    /**
     * The report query.
     *
     * @return \Illuminate\Database\Query\Builder
     */
    public function query()
    {
        [$year, $start, $end] = $this->getDateRange();

        $transactionEarnSub = $this->transactionsSubTotal(
            'total_earned',
            'total_earned_usd',
            $start,
            $end,
            1,
            $this->getTypesEarned()
        );
        $transactionRedeemSub = $this->transactionsSubTotal(
            'total_redeemed',
            'total_redeemed_usd',
            $start,
            $end,
            -1,
            $this->getTypesRedeemed()
        );

        $columns = $this->getColumnNames()->toArray();

        $query = $this->getConnection()
            ->table('core_participant as participant')
            ->select($columns)
            // this should include active/inactive or any hidden/deleted participants as well,
            // as long as they have earned/redeemed points last year.
            ->leftJoin('core_user as user', 'user.user_id', '=', 'participant.user_id')
            ->crossJoin(DB::raw("(SELECT '{$year}' AS tax_year) AS year"))
            ->leftJoinSub($transactionEarnSub, 'earned', 'user.user_id', '=', 'earned.user_id')
            ->leftJoinSub($transactionRedeemSub, 'redeemed', 'user.user_id', '=', 'redeemed.user_id')
            // exclude test/demo/internal users
            ->where('user.internal', User::TYPE_PARTICIPANT)
            ->where(function ($query) {
                $query->where('earned.total_earned', '<>', 0)
                    ->orWhere('redeemed.total_redeemed', '<>', 0);
            });

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

    /**
     * @return array
     */
    protected function getDateRange(): array
    {
        $tableKey = md5(static::class);
        $filters = request()->session()->get($tableKey)['tax_date'] ?? [];

        if (isset($filters['startDate']) && isset($filters['endDate'])) {
            $start = Carbon::parse($filters['startDate']);
            $end = Carbon::parse($filters['endDate']);
            $year = $filters['value'];
        } else {
            $year = (now()->month > 6) ? now()->year : now()->year - 1;
            $start = Carbon::parse("{$year}-01-01");
            $end = Carbon::parse("{$year}-12-31");
        }

        return [$year, $start, $end];
    }

    /**
     * @return array
     */
    protected function getTypesEarned(): array
    {
        return config(
            'core.report.tax-report.transaction_type_filters_earned',
            [
                Transaction::EARNED,
                Transaction::MANUAL_RECEIVE,
            ]
        );
    }

    /**
     * @return array
     */
    protected function getTypesRedeemed(): array
    {
        return config(
            'core.report.tax-report.transaction_type_filters_redeemed',
            [
                Transaction::REDEEMED,
                Transaction::CANCELLED,
                Transaction::MANUAL_REDEEM,
                Transaction::RETURN,
            ]
        );
    }

    /**
     * @param  string  $alias
     * @param  string  $aliasUsd
     * @param  Carbon  $start
     * @param  Carbon  $end
     * @param  int     $factor
     * @param  array   $types
     * @return Builder
     */
    protected function transactionsSubTotal(
        string $alias,
        string $aliasUsd,
        Carbon $start,
        Carbon $end,
        int $factor,
        array $types
    ): Builder {
        $pointsPerDollar = config('catalog.default_point_value');

        return Transaction::select('user_id')
            ->selectRaw('SUM(IFNULL(value, 0)) * ' . $factor . ' AS ' . $alias . '')
            ->selectRaw('ROUND(SUM(IFNULL(value, 0)) * ' . $factor . ' * ' . $pointsPerDollar . ', 2) AS ' . $aliasUsd)
            ->whereIn('type', $types)
            ->where('tax_date', '>=', $start->startOfDay())
            ->where('tax_date', '<=', $end->endOfDay())
            ->whereNull('deleted_at')
            ->groupBy('user_id');
    }
}
