<?php

namespace App\Http\Controllers;

use App\Core\Ticket\ConfirmStadiumEnum;
use App\Core\Ticket\TicketStatusEnum;
use App\Core\Ticket\TicketTypesEnum;
use App\MatchEvent;
use App\Services\TicketParametersService;
use Carbon\Carbon;
use PdfReport;
use CSVReport;
use Illuminate\Support\Facades\App;
use Illuminate\Support\Facades\DB;
use Webklex\PDFMerger\Facades\PDFMergerFacade as PDFMerger;

class ReportInternalController extends Controller
{

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $events = MatchEvent::select('id', 'name', 'event_start', 'active')->orderBy('active', 'desc')->orderBy('created_at', 'desc')->get();
        return view("reports_internal.reports_internal", compact('events'));
    }

    public function generateReport($type, $event_id)
    {
        ini_set('memory_limit', '2048M');
        switch ($type) {
            case 'anulada':
                return $this->reportReversedAndReject($event_id);
                break;
            case 'cortesia':
                return $this->reportCourtesy($event_id);
                break;
            case 'vendida':
                return $this->reportTicketsSold($event_id);
                break;
            case 'consolidado':
                return $this->reportTicketsConsolidated($event_id);
                break;
            case 'consolidadoPDF':
                return $this->reportTicketsConsolidatedPDF($event_id);
                break;
            case 'venta-libre-y-credito':
                return $this->reportTicketsOverTheCounterAndCredit($event_id);
                break;
            case 'abonado':
                return $this->reportTicketsSubscriber(['event_id' => $event_id, 'group_by_special_text' => false]);
                break;
            case 'ingreso-tribuna':
                return $this->reportTicketsByGrandstand($event_id);
                break;
            case 'abonado-por-etiqueta':
                return $this->reportTicketsSubscriber(['event_id' => $event_id, 'group_by_special_text' => true]);
                break;
        }
    }

    public function reportReversedAndReject($event_id)
    {
        $title = "Ticket reversados y anulados";
        $event = MatchEvent::find($event_id);
        $meta = [ // For displaying filters description on header
            'Evento' => $title . ' - ' . $event->name,
            'Fecha impresión reporte' => Carbon::now()->format('d/m/Y H:i:s'),
            'Tipo' => 'Tickets Reversados y anulados'
        ];

        // Query a mostrar en el reporte
        $queryBuilder = DB::table('tickets')
            ->select([
                'tickets.id',
                'match_events.name',
                'match_events.code',
                'tickets.zone',
                'tickets.code_ticket',
                'tickets.special_text',
                'tickets.code_seat',
                'tickets.price',
                'tickets.created_at AS ticket_issue_date',
                'users.first_name',
                'users.last_name',
                'users.document',
                'ticket_types.name AS type_ticket',
                's.zone_id',
                'tickets.letter_seat as letter',
                'z.zone_id as id_sub_zone',
                'zsub.name as tribune',
                'ticket_mains.payment_reference'
            ])
            ->join('ticket_mains', 'tickets.ticket_main_id', '=', 'ticket_mains.id')
            ->join('match_events', 'tickets.match_event_id', '=', 'match_events.id')
            ->join('users', 'tickets.user_id', '=', 'users.id')
            ->join('ticket_types', 'tickets.ticket_type_id', '=', 'ticket_types.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id)
            ->whereIn('tickets.ticket_status_id', [TicketStatusEnum::CANCELLED, TicketStatusEnum::REVERSED]);

        $columns = [
            'ID'                    => 'id',
            'Nombre Evento'         => 'name',
            'Codigo evento'         => 'code',
            'Tribuna'               => 'tribune',
            'Sector'                => 'zone',
            'Silla'                 => 'code_seat',
            'Fila'                  => 'letter',
            'Tipo de boleta'        => 'type_ticket',
            'Código ticket'         => 'code_ticket',
            'Boleta Especial'       => 'special_text',
            'Ref. de pago'          => 'payment_reference',
            'Precio'                => 'price',
            'Fecha emisión boleta'  => 'ticket_issue_date',
            'Nombres Usuario'       => function ($obj) {
                return $this->clean($obj->first_name);
            },
            'Apellidos Usuario'     => function ($obj) {
                return $this->clean($obj->last_name);
            },
            'Documento'             => 'document',
        ];

        $name = stripslashes(str_replace('/', '', $event->name));
        return CSVReport::of($title, $meta, $queryBuilder, $columns)->download($name . ' - Interno - Reversados y anulados');
    }

    public function reportCourtesy($event_id)
    {
        $title = "Tickets de cortesía";
        $event = MatchEvent::find($event_id);
        $meta = [ // For displaying filters description on header
            'Evento' => $title . ' - ' . $event->name,
            'Fecha impresión reporte' => Carbon::now()->format('d/m/Y H:i:s'),
            'Tipo' => 'Tickets cortesia'
        ];

        // Query a mostrar en el reporte
        $queryBuilder = DB::table('tickets')
            ->select([
                'tickets.id',
                'match_events.name',
                'match_events.code',
                'tickets.zone',
                'tickets.code_ticket',
                'tickets.special_text',
                'tickets.code_seat',
                'tickets.price',
                'tickets.confirm_stadium_ticket',
                'tickets.created_at AS ticket_issue_date',
                'users.first_name',
                'users.last_name',
                'users.document',
                'users.email',
                'ticket_types.name AS type_ticket',
                's.zone_id',
                'tickets.letter_seat as letter',
                'z.zone_id as id_sub_zone',
                'zsub.name as tribune',
                'tm.invoice_number',
                'tm.cufe',
                'tm.credit_note_invoice_number',
                'tm.cude',
                DB::raw('CONCAT(billingUser.first_name, " ", billingUser.last_name) AS billingUserFullName'),
                'billingUser.email AS billingUserEmail',
                'billingUser.document AS billingUserDocument',
            ])
            ->join('ticket_mains as tm', 'tickets.ticket_main_id', '=', 'tm.id')
            ->leftJoin('users as billingUser', 'tm.user_id_log', '=', 'billingUser.id')
            ->join('match_events', 'tickets.match_event_id', '=', 'match_events.id')
            ->join('users', 'tickets.user_id', '=', 'users.id')
            ->join('ticket_types', 'tickets.ticket_type_id', '=', 'ticket_types.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id)
            ->whereIn('tickets.ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->whereIn('tickets.ticket_type_id', [TicketTypesEnum::COMPLIMENTARY])
            ->groupBy('tickets.id')
            ->orderBy('ticket_issue_date', 'DESC');

        $columns = [
            'ID'                    => 'id',
            'Nombre Evento'         => 'name',
            'Codigo evento'         => 'code',
            'Tribuna'               => 'tribune',
            'Sector'                => 'zone',
            'Silla'                 => 'code_seat',
            'Fila'                  => 'letter',
            'Tipo de boleta'        => 'type_ticket',
            'Código ticket'         => 'code_ticket',
            'Boleta Especial'       => 'special_text',
            'Precio'                => 'price',
            'Ingreso al estadio'    => 'confirm_stadium_ticket',
            'Fecha emisión boleta'  => 'ticket_issue_date',
            'Nombres Usuario'       => function ($obj) {
                return $this->clean($obj->first_name);
            },
            'Apellidos Usuario'     => function ($obj) {
                return $this->clean($obj->last_name);
            },
            'Documento'             => 'document',
            'Email'                 => 'email',
        ];

        $ticketParametersService = new TicketParametersService;
        $enableElectronicInvoiceCreation = $ticketParametersService->validateElectronicInvoiceCreation();
        if ($enableElectronicInvoiceCreation) {
            $columns['Usuario de facturación'] = 'billingUserFullName';
            $columns['Correo de facturación'] = 'billingUserEmail';
            $columns['Documento de facturación'] = 'billingUserDocument';
            $columns['Número de factura electrónica'] = 'invoice_number';
            $columns['CUFE'] = 'cufe';
            $columns['Número de nota de crédito'] = 'credit_note_invoice_number';
            $columns['CUDE'] = 'cude';
        }

        $name = stripslashes(str_replace('/', '', $event->name));
        return CSVReport::of($title, $meta, $queryBuilder, $columns)->download($name . ' - Interno - Cortesias');
    }

    public function reportTicketsSold($event_id)
    {
        $title = "Ticket vendidos";
        $event = MatchEvent::find($event_id);

        $meta = [ // For displaying filters description on header
            'Evento' => $title . ' - ' . $event->name,
            'Fecha impresión reporte' => Carbon::now()->format('d/m/Y H:i:s'),
            'Tipo' => 'Tickets vendidos'
        ];

        $ticketParametersService = new TicketParametersService;
        $iva = 0;
        if ($ticketParametersService->enableElectronicInvoiceServiceCharge()) {
            $iva = $ticketParametersService->ivaPercentageServiceCharge();
        }

        // Query a mostrar en el reporte
        $queryBuilder = DB::table('tickets')
            ->select([
                'tickets.id',
                'match_events.name',
                'match_events.code',
                'tickets.zone',
                'tickets.code_ticket',
                'tickets.special_text',
                'tickets.code_seat',
                'tickets.price',
                DB::raw('DATE(tickets.created_at) AS ticket_issue_date'),
                DB::raw('TIME(tickets.created_at) AS ticket_issue_time'),
                'tickets.confirm_stadium_ticket',
                'users.first_name',
                'users.last_name',
                'users.document',
                'dt.name AS document_type',
                'ticket_types.name AS type_ticket',
                's.zone_id',
                'tickets.letter_seat as letter',
                'z.zone_id as id_sub_zone',
                'zsub.name as tribune',
                DB::raw('IF(tm.pin_tercero IS NOT NULL AND tm.reference_tercero IS NOT NULL, "Tercero", "Pasarela") as f_pago'),
                DB::raw('IF(tm.payment_reference IS NOT NULL, tm.payment_reference, tm.reference_tercero) as payment_reference'),
                'users.email',
                'users.phone',
                DB::raw('CONCAT(sellerUser.first_name, " ", sellerUser.last_name) AS sellerFullName'),
                'sellerUser.email AS sellerEmail',
                'tm.total',
                DB::raw('GROUP_CONCAT(DISTINCT(tags.name)) AS segmentation'),
                'gp.name as gateway',
                'tm.invoice_number',
                'tm.cufe',
                'tm.credit_note_invoice_number',
                'tm.cude',
                DB::raw('ROUND(IF(tickets.service_charge > 0, tickets.service_charge, ROUND(tm.service_charge / (SELECT COUNT(*) FROM tickets t2 WHERE t2.ticket_main_id = tm.id), 0))/(1 + ' . $iva . '/100), 2) as service_charge'),
                DB::raw('ROUND(
                            (
                                IF(
                                    tickets.service_charge > 0,
                                    tickets.service_charge,
                                    ROUND(tm.service_charge / (SELECT COUNT(*) FROM tickets t2 WHERE t2.ticket_main_id = tm.id), 0)
                                )
                                - (
                                    IF(
                                        tickets.service_charge > 0,
                                        tickets.service_charge,
                                        ROUND(tm.service_charge / (SELECT COUNT(*) FROM tickets t2 WHERE t2.ticket_main_id = tm.id), 0)
                                    ) / (1 + ' . $iva . '/100)
                                )
                            ),
                        2) as iva_service_charge'),
                DB::raw('CONCAT(billingUser.first_name, " ", billingUser.last_name) AS billingUserFullName'),
                'billingUser.email AS billingUserEmail',
                'billingUser.document AS billingUserDocument',
            ])
            ->selectRaw('MAX(ui.dob) as dob')
            ->join('ticket_mains as tm', 'tickets.ticket_main_id', '=', 'tm.id')
            ->leftJoin('users as billingUser', 'tm.user_id_log', '=', 'billingUser.id')
            ->join('match_events', 'tickets.match_event_id', '=', 'match_events.id')
            ->join('users', 'tickets.user_id', '=', 'users.id')
            ->leftJoin('document_types as dt', 'users.document_type_id', '=', 'dt.id')
            ->leftJoin('user_informations as ui', 'users.id', '=', 'ui.user_id')
            ->leftJoin('users as sellerUser', 'tm.seller_user_id', '=', 'sellerUser.id')
            ->leftJoin('user_tags', 'users.id', '=', 'user_tags.user_id')
            ->leftjoin('tags', function ($join) {
                $join->on('tags.id', '=', 'user_tags.tag_id')->where('tags.active', 1);
            })
            ->join('ticket_types', 'tickets.ticket_type_id', '=', 'ticket_types.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->leftJoin('gateway_payments as gp', 'tm.gateway_payments_id', '=', 'gp.id')
            ->where('tickets.match_event_id', $event_id)
            ->whereIn('tickets.ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->whereIn('tickets.ticket_type_id', [TicketTypesEnum::FREE_SALE, TicketTypesEnum::SUBSCRIBER, TicketTypesEnum::CREDIT])
            ->groupBy('tickets.id')
            ->orderBy('ticket_issue_date', 'DESC');

        $columns = [
            'ID'                    => 'id',
            'Nombre Evento'         => 'name',
            'Codigo evento'         => 'code',
            'Tribuna'               => 'tribune',
            'Sector'                => 'zone',
            'Silla'                 => 'code_seat',
            'Fila'                  => 'letter',
            'Tipo de boleta'        => 'type_ticket',
            'Código ticket'         => 'code_ticket',
            'Boleta Especial'       => 'special_text',
            'Ref. de pago'          => 'payment_reference',
            'Precio'                => 'price',
            'Cobro de servicio'     => 'service_charge',
            'IVA de servicio'       => 'iva_service_charge',            
            'Fecha compra'          => 'ticket_issue_date',
            'Hora compra'           => 'ticket_issue_time',
            'Forma pago'            => 'f_pago',
            'Nombres Usuario' => function ($obj) {
                return $this->clean($obj->first_name);
            },
            'Apellidos Usuario' => function ($obj) {
                return $this->clean($obj->last_name);
            },
            'Documento'             => 'document',
            'Tipo Documento'        => 'document_type',
            'Email'                 => 'email',
            'Fecha Nacimiento'      => 'dob',
            'Telefono'              => 'phone',
            'Segmentación'          => 'segmentation',
            'Nombres Vendedor'      => 'sellerFullName',
            'Email Vendedor'        => 'sellerEmail',
            'Ingreso al estadio'    => 'confirm_stadium_ticket',
            'Precio total compra'   => 'total',
            'Pasarela de pago'      => 'gateway',
        ];

        $enableElectronicInvoiceCreation = $ticketParametersService->validateElectronicInvoiceCreation();
        if ($enableElectronicInvoiceCreation) {
            $columns['Usuario de facturación'] = 'billingUserFullName';
            $columns['Correo de facturación'] = 'billingUserEmail';
            $columns['Documento de facturación'] = 'billingUserDocument';
            $columns['Número de factura electrónica'] = 'invoice_number';
            $columns['CUFE'] = 'cufe';
            $columns['Número de nota de crédito'] = 'credit_note_invoice_number';
            $columns['CUDE'] = 'cude';
        }

        $name = stripslashes(str_replace('/', '', $event->name));
        return CSVReport::of($title, $meta, $queryBuilder, $columns)->download($name . ' - Interno - Vendidas');
    }

    public function reportTicketsConsolidated($event_id)
    {
        $title = "Reporte consolidado";
        $event = MatchEvent::find($event_id);

        $meta = [ // For displaying filters description on header
            'Evento' => $title . ' - ' . $event->name . '(' . $event->id . ')',
            'Fecha evento' => $event->event_start,
            'Código evento' => $event->code,
            'Fecha impresión reporte' => Carbon::now()->format('d/m/Y H:i:s'),
            'Tipo' => 'Tickets vendidos'
        ];

        $queryBuilder = DB::table('tickets')
            ->select(
                'match_events.name',
                'match_events.code',
                DB::raw("SUBSTRING_INDEX(z.name, '_', 1) as name_sub"),
                'zsub.name as zone',
                DB::raw('SUM(tickets.price) as total'),
                DB::raw("IF(ticket_mains.pin_tercero IS NOT NULL || ticket_mains.pin_internal IS NOT NULL, 'Efectivo', IF(tickets.ticket_type_id = 4, 'Crédito', 'Pasarela')) as f_pago"),
                DB::raw("IF(ticket_mains.payment_reference IS NOT NULL, 'Aplicación', 'Taquilla') as way_payment"),
                DB::raw('COUNT(tickets.id) as t_tickets')
            )
            ->join('ticket_mains', 'tickets.ticket_main_id', '=', 'ticket_mains.id')
            ->join('match_events', 'tickets.match_event_id', '=', 'match_events.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id)
            ->whereIn('tickets.ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->whereIn('tickets.ticket_type_id', [TicketTypesEnum::FREE_SALE, TicketTypesEnum::SUBSCRIBER, TicketTypesEnum::CREDIT])
            ->groupBy('zsub.id', 'match_events.name', 'match_events.code', 'zsub.name', DB::raw("SUBSTRING_INDEX(z.name, '_', 1)"), 'f_pago', 'way_payment')
            ->orderBy('zsub.name', 'ASC')
            ->orderBy(DB::raw("SUBSTRING_INDEX(z.name, '_', 1)"), 'ASC');

        $columns = [
            'Nombre Evento' => 'name',
            'Codigo evento' => 'code',
            'Tribuna' => 'zone',
            'Sector' => 'name_sub',
            'Forma pago' => 'f_pago',
            'Medio de Compra' => 'way_payment',
            'Cant. Tickets' => 't_tickets',
            'Total' => 'total'
        ];

        return PdfReport::of($title, $meta, $queryBuilder, $columns)
            ->editColumns(['Total'], [ // Mass edit column
                'class' => 'right bold padding'
            ])
            ->editColumns(['Cant. Tickets'], [ // Mass edit column
                'class' => 'right bold'
            ])
            ->setCss([
                '.padding' => 'padding: 0px 0px 0px 85px;',
            ])
            ->showTotal([ // Used to sum all value on specified column on the last table (except using groupBy method). 'point' is a type for displaying total with a thousand separator
                'Total' => 'point', // if you want to show dollar sign ($) then use 'Total Balance' => '$'
                'Cant. Tickets' => 'point'
            ])
            ->download('Reporte consolidado - ' . $event->name);
    }

    public function reportTicketsConsolidatedPDF($event_id)
    {
        $title = "Reporte consolidado";
        $event = $this->getMatchEvent($event_id);
        $event['event_start'] = Carbon::parse($event['event_start']);
        $event['printed_at'] = Carbon::now();
        $event['free_sales'] = $this->getFreeSales($event_id);
        $event['credit_sales'] = $this->getCreditSales($event_id);

        $pdf = App::make('snappy.pdf.wrapper');
        $htmlContent = view('reports_internal.consolidated_pdf.consolidated_pdf_body', compact('event'))->render();
        $htmlHeader = view('reports_internal.consolidated_pdf.consolidated_pdf_header', compact('event'))->render();
        $htmlFooter = view('reports_internal.consolidated_pdf.consolidated_pdf_footer', compact('event'))->render();

        $pdf->setOption('enable-local-file-access', true)
            ->setOption('header-html', $htmlHeader)
            ->setOption('footer-html', $htmlFooter)
            ->loadHTML($htmlContent);

        return $pdf->inline();
    }

    public function reportTicketsOverTheCounterAndCredit($event_id)
    {
        $title = "Reporte Venta Libre Y Crédito";
        $event = MatchEvent::find($event_id);

        $meta = [ // For displaying filters description on header
            'Evento' => $title . ' - ' . $event->name . '(' . $event->id . ')',
            'Fecha evento' => $event->event_start,
            'Código evento' => $event->code,
            'Fecha impresión reporte' => Carbon::now()->format('d/m/Y H:i:s'),
            'Tipo' => 'Tickets vendidos'
        ];

        $queryBuilder = DB::table('tickets')
            ->select(
                'match_events.name',
                'match_events.code',
                DB::raw("SUBSTRING_INDEX(z.name, '_', 1) as name_sub"),
                'zsub.name as zone',
                DB::raw('SUM(tickets.price) as total'),
                DB::raw("IF(ticket_mains.pin_tercero IS NOT NULL || ticket_mains.pin_internal IS NOT NULL, 'Efectivo', IF(tickets.ticket_type_id = 4, 'Crédito', 'Pasarela')) as f_pago"),
                DB::raw("IF(ticket_mains.payment_reference IS NOT NULL, 'Aplicación', 'Taquilla') as way_payment"),
                DB::raw('COUNT(tickets.id) as t_tickets')
            )
            ->join('ticket_mains', 'tickets.ticket_main_id', '=', 'ticket_mains.id')
            ->join('match_events', 'tickets.match_event_id', '=', 'match_events.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id)        // Del evento
            ->whereIn('tickets.ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->whereIn('tickets.ticket_type_id', [TicketTypesEnum::FREE_SALE, TicketTypesEnum::CREDIT])
            ->groupBy('zsub.id', 'match_events.name', 'match_events.code', 'zsub.name', DB::raw("SUBSTRING_INDEX(z.name, '_', 1)"), 'f_pago', 'way_payment')
            ->orderBy('zsub.name', 'ASC')
            ->orderBy(DB::raw("SUBSTRING_INDEX(z.name, '_', 1)"), 'ASC');

        $columns = [
            'Nombre Evento' => 'name',
            'Codigo evento' => 'code',
            'Tribuna' => 'zone',
            'Sector' => 'name_sub',
            'Forma pago' => 'f_pago',
            'Medio de Compra' => 'way_payment',
            'Cant. Tickets' => 't_tickets',
            'Total' => 'total'
        ];

        return PdfReport::of($title, $meta, $queryBuilder, $columns)
            ->editColumns(['Total'], [ // Mass edit column
                'class' => 'right bold padding'
            ])
            ->editColumns(['Cant. Tickets'], [ // Mass edit column
                'class' => 'right bold'
            ])
            ->setCss([
                '.padding' => 'padding: 0px 0px 0px 85px;',
            ])
            ->showTotal([ // Used to sum all value on specified column on the last table (except using groupBy method). 'point' is a type for displaying total with a thousand separator
                'Total' => 'point', // if you want to show dollar sign ($) then use 'Total Balance' => '$'
                'Cant. Tickets' => 'point'
            ])
            ->download('Reporte Venta Libre Y Crédito - ' . $event->name);
    }

    public function reportTicketsSubscriber(array $params)
    {
        $event_id = $params['event_id'];
        $group_by_special_text = $params['group_by_special_text'] ?? false;

        $title = "Reporte Abonados" . ($group_by_special_text ? ' por promoción' : '');
        $event = MatchEvent::find($event_id);

        $meta = [ // For displaying filters description on header
            'Evento' => $title . ' - ' . $event->name . '(' . $event->id . ')',
            'Fecha evento' => $event->event_start,
            'Código evento' => $event->code,
            'Fecha impresión reporte' => Carbon::now()->format('d/m/Y H:i:s'),
            'Tipo' => 'Tickets vendidos'
        ];

        $queryBuilder = DB::table('tickets')
            ->select(
                'match_events.name',
                'match_events.code',
                DB::raw("SUBSTRING_INDEX(z.name, '_', 1) as name_sub"),
                'zsub.name as zone',
                DB::raw('SUM(tickets.price) as total'),
                DB::raw("IF(ticket_mains.pin_tercero IS NOT NULL || ticket_mains.pin_internal IS NOT NULL, 'Efectivo', IF(tickets.ticket_type_id = 4, 'Crédito', 'Pasarela')) as f_pago"),
                DB::raw("IF(ticket_mains.payment_reference IS NOT NULL, 'Aplicación', 'Taquilla') as way_payment"),
                DB::raw('COUNT(tickets.id) as t_tickets')
            )
            ->join('ticket_mains', 'tickets.ticket_main_id', '=', 'ticket_mains.id')
            ->join('match_events', 'tickets.match_event_id', '=', 'match_events.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id)                // Del evento
            ->whereIn('tickets.ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->whereIn('tickets.ticket_type_id', [TicketTypesEnum::SUBSCRIBER])
            ->groupBy('zsub.id', 'match_events.name', 'match_events.code', 'zsub.name', DB::raw("SUBSTRING_INDEX(z.name, '_', 1)"), 'f_pago', 'way_payment')
            ->when($group_by_special_text, function ($query) {
                return $query->addSelect('tickets.special_text')->groupBy('tickets.special_text');
            })
            ->orderBy('zsub.name', 'ASC')
            ->orderBy(DB::raw("SUBSTRING_INDEX(z.name, '_', 1)"), 'ASC');

        $columns = [
            'Nombre Evento'     => 'name',
            'Codigo evento'     => 'code',
            'Tribuna'           => 'zone',
            'Sector'            => 'name_sub',
            'Forma pago'        => 'f_pago',
            'Medio de Compra'   => 'way_payment',
            'Cant. Tickets'     => 't_tickets',
            'Total'             => 'total'
        ];

        if ($group_by_special_text) {
            $keys = array_keys($columns);
            $pos = array_search('Sector', $keys);

            $before = array_slice($columns, 0, $pos + 1, true);
            $after  = array_slice($columns, $pos + 1, null, true);

            $columns = $before + ['Promoción' => 'special_text'] + $after;
        }

        return PdfReport::of($title, $meta, $queryBuilder, $columns)
            ->editColumns(['Total'], [ // Mass edit column
                'class' => 'right bold padding'
            ])
            ->editColumns(['Cant. Tickets'], [ // Mass edit column
                'class' => 'right bold'
            ])
            ->setCss([
                '.padding' => 'padding: 0px 0px 0px 85px;',
            ])
            ->showTotal([ // Used to sum all value on specified column on the last table (except using groupBy method). 'point' is a type for displaying total with a thousand separator
                'Total' => 'point', // if you want to show dollar sign ($) then use 'Total Balance' => '$'
                'Cant. Tickets' => 'point'
            ])
            ->download('Reporte Abonados ' . ($group_by_special_text ? 'por promoción' : '') . ' - ' . $event->name);
    }

    public function reportTicketsByGrandstand($event_id)
    {
        $title = "Reporte ingresos por tribuna";
        $event = MatchEvent::find($event_id);
        $date = Carbon::now()->format('Y-m-d H:i:s');

        $meta = [
            'Evento' => $event->name . '(' . $event->id . ')',
            'Fecha evento' => $event->event_start,
            'Código evento' => $event->code,
            'Fecha impresión reporte' => $date,
            'Descripcion' => 'Porcentaje ingresos por tribuna'
        ];

        $pathReportPercentage = '/public/tickets_tmp/' . time() . '-p.pdf';
        $queryBuilderPercentage = DB::table('tickets')
            ->select(DB::raw('CONCAT("Tribuna ",zsub.name) as grandstand'), DB::raw('ROUND((IFNULL(SUM(tv.validation_state), SUM(IF(tickets.confirm_stadium_ticket = "' . ConfirmStadiumEnum::CONFIRMED . '", 1, 0)))/(COUNT(*))*100), 2) AS percentage'))
            ->leftJoin('ticket_validation_histories AS tv', function ($join) {
                $join->on('tickets.id', '=', 'tv.ticket_id')->where('tv.validation_state', '=', 1);
            })
            ->join('ticket_types as tt', 'tickets.ticket_type_id', '=', 'tt.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id)
            ->whereIn('tickets.ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->groupBy('zsub.name')
            ->orderBy('zsub.name', 'ASC');

        $columnsPercentage = [
            'Tribuna' => 'grandstand',
            'Porcentaje' => 'percentage'
        ];

        PdfReport::of($title, $meta, $queryBuilderPercentage, $columnsPercentage)
            ->showNumColumn(false)
            ->editColumn('Porcentaje', [
                'class' => 'right bold',
                'displayAs' => function ($result) {
                    return $result->percentage . ' %';
                }
            ])
            ->store($pathReportPercentage);

        $meta = [
            'Evento' => $event->name . '(' . $event->id . ')',
            'Fecha evento' => $event->event_start,
            'Código evento' => $event->code,
            'Fecha impresión reporte' => $date,
            'Descripcion' => 'Total ingresos por tribuna y tipo'
        ];

        $queryBuilder = DB::table('tickets')
            ->select(DB::raw('CONCAT("Tribuna ",zsub.name) as grandstand'), 'tt.name as type', DB::raw('COUNT(*) as sales'), DB::raw('IFNULL(SUM(tv.validation_state), SUM(IF(tickets.confirm_stadium_ticket = "' . ConfirmStadiumEnum::CONFIRMED . '", 1, 0))) AS receipts'))
            ->leftJoin('ticket_validation_histories AS tv', function ($join) {
                $join->on('tickets.id', '=', 'tv.ticket_id')->where('tv.validation_state', '=', 1);
            })
            ->join('ticket_types as tt', 'tickets.ticket_type_id', '=', 'tt.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id)
            ->whereIn('tickets.ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->groupBy('zsub.name', 'tt.name')
            ->orderBy('zsub.name', 'ASC');

        $columns = [
            'Tribuna' => 'grandstand',
            'Tipo' => 'type',
            'Total Boletas' => 'sales',
            'Total Ingresos' => 'receipts'
        ];

        $pathReport = '/public/tickets_tmp/' . time() . '.pdf';
        PdfReport::of($title, $meta, $queryBuilder, $columns)
            ->showNumColumn(false)
            ->editColumn('Total Boletas', [
                'class' => 'right bold',
            ])
            ->editColumn('Total Ingresos', [
                'class' => 'right bold',
            ])
            ->showTotal([
                'Total Boletas' => 'point',
                'Total Ingresos' => 'point',
            ])
            ->store($pathReport);

        $pdf = PDFMerger::init();
        $pdf->addPDF(storage_path() . '/app' . $pathReport, 'all');
        $pdf->addPDF(storage_path() . '/app' . $pathReportPercentage, 'all');
        $fileName = $title . ' - ' . $event->name . '.pdf';
        $pdf->merge();
        $pdf->save(storage_path() . '/app/public/tickets_tmp/' . $fileName);
        return response()->download(storage_path() . '/app/public/tickets_tmp/' . $fileName);
    }

    public static function clean($string)
    {
        if (!$string) {
            return "";
        }
        $string = str_replace(' ', '-', $string); // Replaces all spaces with hyphens.
        return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
    }

    private function getMatchEvent($event_id)
    {
        return MatchEvent::find($event_id);
    }

    private function getFreeSales($event_id)
    {
        return $this->getSalesByTicketType($event_id, [TicketTypesEnum::FREE_SALE, TicketTypesEnum::SUBSCRIBER]);
    }

    private function getCreditSales($event_id)
    {
        return $this->getSalesByTicketType($event_id, [TicketTypesEnum::CREDIT]);
    }

    private function getSalesByTicketType(int $event_id, array $ticketTypesIds)
    {
        $summary = DB::table('tickets')
            ->select([
                'sellerUser.id',
                'sellerUser.email',
                DB::raw('COUNT(tickets.id) as num_tickets'),
                DB::raw('SUM(tickets.price) as total'),

            ])
            ->join('ticket_mains', 'tickets.ticket_main_id', '=', 'ticket_mains.id')
            ->join('users as sellerUser', 'ticket_mains.seller_user_id', '=', 'sellerUser.id')
            ->where('tickets.match_event_id', $event_id)
            ->whereIn('tickets.ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->whereIn('tickets.ticket_type_id', $ticketTypesIds)
            ->groupBy(['sellerUser.id', 'sellerUser.email'])
            ->orderBy('sellerUser.email', 'ASC')
            ->get();

        $details = $this->getSalesDetailsByTicketType($event_id, $ticketTypesIds)->toArray();

        foreach ($summary->toArray() as &$s) {
            $s->details = array_filter($details, function ($d) use ($s) {
                return $d->id == $s->id;
            });
        }
        unset($s);

        return $summary;
    }

    private function getSalesDetailsByTicketType(int $event_id, array $ticketTypesIds)
    {
        return DB::table('tickets')
            ->select([
                'sellerUser.id',
                'zsub.name as tribune',
                DB::raw('COUNT(tickets.id) as num_tickets'),
                DB::raw('SUM(tickets.price) as total'),

            ])
            ->join('ticket_mains', 'tickets.ticket_main_id', '=', 'ticket_mains.id')
            ->join('users as sellerUser', 'ticket_mains.seller_user_id', '=', 'sellerUser.id')
            ->join('seats as s', 'tickets.seat_id', '=', 's.id')
            ->join('zones as z', 's.zone_id', '=', 'z.id')
            ->join('zones as zsub', 'z.zone_id', '=', 'zsub.id')
            ->where('tickets.match_event_id', $event_id)
            ->whereIn('tickets.ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->whereIn('tickets.ticket_type_id', $ticketTypesIds)
            ->groupBy(['sellerUser.id', 'tribune'])
            ->orderBy('sellerUser.email', 'ASC')
            ->get();
    }
}