<?php

namespace FluentSupport\App\Modules;

use FluentSupport\App\Models\Agent;
use FluentSupport\App\Models\Conversation;
use FluentSupport\App\Models\Ticket;
use FluentSupport\App\Models\Product;
use FluentSupport\App\Services\Tickets\AgentTicketAccess;

/**
 * Dashboard ticket stats, scoped to the current agent's mailbox restrictions.
 */
class StatModule
{
    /**
     * Date range defaults to today.
     *
     * @param $agentId
     * @param string|false $startDate
     * @param string|false $endDate
     * @return array[]
     */
    public static function getAgentStat($agentId, $startDate = false, $endDate = false)
    {
        if (!$startDate) {
            $currentDate = current_time('mysql');
            $startDate = $currentDate;
            $endDate = $currentDate;
        }

        $startDate = gmdate('Y-m-d 00:00.01', strtotime($startDate));
        $endDate = gmdate('Y-m-d 23:59.59', strtotime($endDate));

        $access = new AgentTicketAccess();

        $newTickets = $access->applyMailboxRestrictionScope(
            Ticket::where('agent_id', $agentId)->where('status', 'new')
        )->count();

        $activeTickets = $access->applyMailboxRestrictionScope(
            Ticket::where('agent_id', $agentId)->where('status', 'active')
        )->count();

        $closedTickets = $access->applyMailboxRestrictionScope(
            Ticket::where('agent_id', $agentId)
                ->where('status', 'closed')
                ->whereBetween('resolved_at', [$startDate, $endDate])
        )->count();

        $responses = $access->applyMailboxRestrictionScopeViaTicket(
            Conversation::where('conversation_type', 'response')
                ->where('person_id', $agentId)
                ->whereBetween('created_at', [$startDate, $endDate])
        )->count();

        // Distinct tickets the agent replied to
        $interactions = $access->applyMailboxRestrictionScopeViaTicket(
            Conversation::where('person_id', $agentId)
                ->where('conversation_type', 'response')
                ->whereBetween('created_at', [$startDate, $endDate])
                ->groupBy('ticket_id')
        )->get()->count();

        return [
            'new_tickets'    => [
                'title' => __('New Tickets', 'fluent-support'),
                'count' => $newTickets
            ],
            'active_tickets' => [
                'title' => __('Active Tickets', 'fluent-support'),
                'count' => $activeTickets
            ],
            'closed_tickets' => [
                'title' => __('Closed Tickets', 'fluent-support'),
                'count' => $closedTickets
            ],
            'responses'      => [
                'title' => __('Total Responses', 'fluent-support'),
                'count' => $responses
            ],
            'interactions'   => [
                'title' => __('Interactions', 'fluent-support'),
                'count' => $interactions
            ]
        ];
    }

    /**
     * @return array[]
     */
    public static function getOverAllStats()
    {
        $access = new AgentTicketAccess();

        // Same mailbox boundary as the ticket list; these totals are agent-facing.
        $newTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'new'))
            ->count();

        $activeTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'active'))
            ->count();

        $closedTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'closed'))
            ->count();

        $responses = $access->applyMailboxRestrictionScopeViaTicket(
            Conversation::where('conversation_type', 'response')
        )->count();

        return [
            'new_tickets'    => [
                'title' => __('New Tickets', 'fluent-support'),
                'count' => $newTickets
            ],
            'active_tickets' => [
                'title' => __('Active Tickets', 'fluent-support'),
                'count' => $activeTickets
            ],
            'closed_tickets' => [
                'title' => __('Closed Tickets', 'fluent-support'),
                'count' => $closedTickets
            ],
            'responses'      => [
                'title' => __('Responses', 'fluent-support'),
                'count' => $responses
            ]
        ];
    }

    /**
     * @param bool|int $agentId Limit to this agent when given.
     * @return array
     */
    public static function getTodayStats($agentId = false, ?AgentTicketAccess $access = null)
    {
        list($start, $end) = static::todayRange();

        $access = $access ?: new AgentTicketAccess();

        $newTickets = $access->applyMailboxRestrictionScope(
            Ticket::whereBetween('created_at', [$start, $end])
        );

        $closedTickets = $access->applyMailboxRestrictionScope(
            Ticket::where('status', 'closed')->whereBetween('resolved_at', [$start, $end])
        );

        $responses = $access->applyMailboxRestrictionScopeViaTicket(
            Conversation::where('conversation_type', 'response')
                ->whereHas('person', function ($q) {
                    $q->where('person_type', 'agent');
                })
                ->whereBetween('created_at', [$start, $end])
        );

        if ($agentId) {
            $newTickets->where('agent_id', $agentId);
            $closedTickets->where('agent_id', $agentId);
            $responses->where('person_id', $agentId);
        }

        return [
            'new_tickets'    => [
                'title' => __('New Tickets', 'fluent-support'),
                'count' => $newTickets->count()
            ],
            'closed_tickets' => [
                'title' => __('Closed Tickets', 'fluent-support'),
                'count' => $closedTickets->count()
            ],
            'responses'      => [
                'title' => __('Responses', 'fluent-support'),
                'count' => $responses->count()
            ]
        ];
    }

    /**
     * @param $agentId
     * @return array[]
     */
    public static function getAgentOverallStats($agentId)
    {
        $replies_count = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket(
            Conversation::where('person_id', $agentId)
        )->count();

        // Closed tickets assigned to the agent, whoever closed them
        $total_closed = (new AgentTicketAccess())->applyMailboxRestrictionScope(
            Ticket::where('agent_id', $agentId)->where('status', 'closed')
        )->count();

        return [
            'replies_count'      => [
                'title' => __('Total Replies', 'fluent-support'),
                'count' => $replies_count
            ],
            'total_closed'       => [
                'title' => __('Total Closed', 'fluent-support'),
                'count' => $total_closed
            ]
        ];
    }

    /**
     * Open tickets awaiting an agent reply, per product.
     * @return array $result
     */
    public static function getActiveTicketsByProductStats()
    {
        $products = Product::all();
        $result = [];

        $counts = static::awaitingTicketsQuery(new AgentTicketAccess())
            ->whereIn('product_id', $products->pluck('id')->all())
            ->groupBy('product_id')
            ->selectRaw('product_id, COUNT(*) AS total')
            ->pluck('total', 'product_id')
            ->all();

        foreach ($products as $product) {
            $result[$product->id] = [
                'title' => $product->title,
                'count' => (int) ($counts[$product->id] ?? 0)
            ];
        }

        return $result;
    }


    /**
     * Open tickets awaiting an agent reply, optionally filtered by one column.
     *
     * @param string $whereClause Column name.
     * @param string $whereClauseValue
     * @return int $awatingTicketCount
     */
    public static function countAwaitingTickets($whereClause = null, $whereClauseValue = null, ?AgentTicketAccess $access = null)
    {
        $ticket = static::awaitingTicketsQuery($access ?: new AgentTicketAccess());

        if ($whereClause && $whereClauseValue) {
            $ticket->where(sanitize_text_field($whereClause), sanitize_text_field($whereClauseValue));
        }

        return $ticket->count();
    }

    /**
     * Open tickets awaiting an agent reply, within the viewer's mailboxes.
     *
     * @param AgentTicketAccess $access
     * @return \FluentSupport\Framework\Database\Orm\Builder
     */
    protected static function awaitingTicketsQuery(AgentTicketAccess $access)
    {
        // Must be a builder: ->where() on a bare model returns a new one, losing the scope.
        return $access->applyMailboxRestrictionScope(Ticket::query())
            ->where('status', '!=', 'closed')
            ->where(function ($query) {
                $query->whereColumn('last_agent_response', '<', 'last_customer_response')
                    ->orWhereNull('last_agent_response')
                    ->orWhere('status', 'new');
            });
    }

    /**
     * Today in site time, like the stored dates.
     *
     * @return string[] [start, end] as "Y-m-d H:i:s".
     */
    protected static function todayRange()
    {
        $today = current_time('Y-m-d');

        return [$today . ' 00:00:00', $today . ' 23:59:59'];
    }

    /**
     * Today's stats for every agent with replies today or tickets waiting.
     * @return array
     */
    public static function getAgentTodayStats()
    {
        // Share one instance so restrictions are resolved once.
        $access = new AgentTicketAccess();
        list($start, $end) = static::todayRange();

        $agents = Agent::get();
        $agentIds = $agents->pluck('id')->all();

        if (!$agentIds) {
            return [];
        }

        // One grouped query per figure for all agents.
        $countBy = function ($query, $column) {
            return $query->groupBy($column)
                ->selectRaw($column . ' AS grouped_id, COUNT(*) AS total')
                ->pluck('total', 'grouped_id')
                ->all();
        };

        $waiting = $countBy(static::awaitingTicketsQuery($access)->whereIn('agent_id', $agentIds), 'agent_id');

        $newTickets = $countBy($access->applyMailboxRestrictionScope(
            Ticket::whereBetween('created_at', [$start, $end])->whereIn('agent_id', $agentIds)
        ), 'agent_id');

        $closedTickets = $countBy($access->applyMailboxRestrictionScope(
            Ticket::where('status', 'closed')->whereBetween('resolved_at', [$start, $end])->whereIn('agent_id', $agentIds)
        ), 'agent_id');

        // person_id among the agent ids is the "written by an agent" check.
        $responses = $countBy($access->applyMailboxRestrictionScopeViaTicket(
            Conversation::where('conversation_type', 'response')
                ->whereIn('person_id', $agentIds)
                ->whereBetween('created_at', [$start, $end])
        ), 'person_id');

        $stats = [];

        foreach ($agents as $agent) {
            $waitingCount = (int) ($waiting[$agent->id] ?? 0);
            $responseCount = (int) ($responses[$agent->id] ?? 0);

            if (!$responseCount && !$waitingCount) {
                continue;
            }

            $stats[] = [
                'agent_id'    => $agent->id,
                'agent_name'  => $agent->full_name,
                'agent_email' => $agent->email,
                'avatar'      => $agent->photo,
                'stats'       => [
                    'waiting_today'  => [
                        'title' => __('Waiting Today', 'fluent-support'),
                        'count' => $waitingCount
                    ],
                    'new_tickets'    => [
                        'title' => __('New Tickets', 'fluent-support'),
                        'count' => (int) ($newTickets[$agent->id] ?? 0)
                    ],
                    'closed_tickets' => [
                        'title' => __('Closed Tickets', 'fluent-support'),
                        'count' => (int) ($closedTickets[$agent->id] ?? 0)
                    ],
                    'responses'      => [
                        'title' => __('Responses', 'fluent-support'),
                        'count' => $responseCount
                    ]
                ]
            ];
        }

        return $stats;
    }

}