<?php

namespace Modules\Reporting\Services;

use Modules\Reporting\Entities\InboundReport;
use App\Models\Company;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Carbon\Carbon;
use Modules\Platform\Core\Helper\CompanySettings;
use Modules\Platform\Core\Helper\SettingsHelper;

class InboundReportService
{
    /**
     * Generate inbound report for a specific day (Answered + Missed calls)
     */
    public function generateDailyReport($date = null)
    {
        try {
            $date = $date ?? Carbon::yesterday()->format('Y-m-d');
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::info("Starting inbound report for date {$date} (Answered + Missed calls)");
            }
            
            // Delete old data for this day
            // $this->deleteExistingReport($date);

            // Fetch answered calls data
            $answeredCalls = $this->fetchAnsweredCalls($date);
            
            // Fetch missed calls data  
            $missedCalls = $this->fetchMissedCalls($date);
            
            // Merge both datasets
            $allCalls = $answeredCalls->merge($missedCalls);
            
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                \Log::info("Total calls fetched - Answered: {$answeredCalls->count()}, Missed: {$missedCalls->count()}, Total: {$allCalls->count()}");
            }
            
            // Remove duplicates
            $uniqueCalls = $allCalls->unique('callid')->values();
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                \Log::info("Unique calls after deduplication: {$uniqueCalls->count()}");
            }
            
            // Process and insert data
            $count = $this->processAndInsertData($uniqueCalls, $date);
            
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::info("Successfully generated {$count} records for date {$date}");
            }
            return $count;
            
        } catch (\Exception $e) {
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::error("Error generating inbound report: " . $e->getMessage(), [
                    'date' => $date,
                    'trace' => $e->getTraceAsString()
                ]);
            }
            return 0;
        }
    }
    
    /**
     * Delete existing report
     */
    private function deleteExistingReport($date)
    {
        return InboundReport::where('report_date', $date)
            ->delete();
    }
    
    /**
     * Fetch answered calls (المكالمات اللي اترد عليها)
     */
    private function fetchAnsweredCalls($date)
    {
        $startOfDay = Carbon::parse($date)->startOfDay()->format('Y-m-d H:i:s');
        $endOfDay = Carbon::parse($date)->endOfDay()->format('Y-m-d H:i:s');
        
        if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
            \Log::info("Fetching answered calls for date: {$date}");
        }
        
        return DB::table('asteriskcdrdb.queuelog')
            ->select([
                'queuelog.callid',
                'queuelog.agent',
                'queuelog.event',
                'queuelog.data2',
                'queuelog.data5',
                'cdr.dstchannel',
                'calls.survey_choice as survey',
                'cdr.linkedid',
                'cdr.did',
                'cdr.src',
                'cdr.disposition',
                DB::raw("
                    (
                        SELECT COUNT(*) 
                        FROM asteriskcdrdb.cdr AS transfer_cdr
                        WHERE transfer_cdr.linkedid = cdr.linkedid 
                        AND transfer_cdr.lastapp = 'Dial'
                        AND EXISTS (
                            SELECT 1
                            FROM asteriskcdrdb.cdr c2
                            WHERE c2.linkedid = cdr.linkedid
                            AND c2.disposition = 'ANSWERED'
                            AND c2.lastapp != 'Queue'
                            AND c2.dcontext = 'from-queue-exten-only'
                            AND c2.calldate < transfer_cdr.calldate
                        )
                    ) AS transfers_count
                "),
                DB::raw('enterqueue.enter_time AS call_time'),
                DB::raw('enterqueue.enter_queuename'),
                'queues_config.descr as queue_description',
                DB::raw("
                    (
                        SELECT SUM(CASE 
                            WHEN event IN ('EXITWITHTIMEOUT', 'ABANDON') THEN CAST(data3 AS UNSIGNED)
                            WHEN event IN ('COMPLETECALLER', 'COMPLETEAGENT') THEN CAST(data1 AS UNSIGNED)
                            ELSE 0
                        END)
                        FROM asteriskcdrdb.queuelog AS q2
                        WHERE q2.callid = queuelog.callid
                    ) AS ringtime
                "),
            ])
            ->join('asteriskcdrdb.cdr', 'cdr.uniqueid', '=', 'queuelog.callid')
            ->leftJoin('calls', 'calls.linkedid', '=', 'cdr.linkedid')
            ->leftJoin(DB::raw("
                (
                    SELECT *
                    FROM (
                        SELECT 
                            callid, 
                            time AS enter_time,
                            queuename AS enter_queuename,
                            ROW_NUMBER() OVER (PARTITION BY callid ORDER BY time ASC, id ASC) AS rn
                        FROM asteriskcdrdb.queuelog
                        WHERE event = 'ENTERQUEUE'
                    ) AS ranked
                    WHERE rn = 1
                ) AS enterqueue
            "), 'enterqueue.callid', '=', 'queuelog.callid')
            ->leftJoin('asterisk.queues_config', 'queues_config.extension', '=', 'enterqueue.enter_queuename')
            ->whereIn('queuelog.event', ['COMPLETECALLER', 'COMPLETEAGENT'])
            ->whereBetween('enterqueue.enter_time', [$startOfDay, $endOfDay])
            ->where('cdr.disposition', 'ANSWERED')
            ->orderBy('enterqueue.enter_time', 'asc')
            ->get();
    }
    
    /**
     * Fetch missed/abandoned calls (المكالمات اللي متردش عليها)
     */
    private function fetchMissedCalls($date)
    {
        $startOfDay = Carbon::parse($date)->startOfDay()->format('Y-m-d H:i:s');
        $endOfDay = Carbon::parse($date)->endOfDay()->format('Y-m-d H:i:s');
        
        if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
            \Log::info("Fetching missed calls for date: {$date}");
        }
        
        return DB::table('asteriskcdrdb.queuelog')
            ->select([
                'queuelog.callid',
                'queuelog.status',
                DB::raw('NULL as agent'), // مش عامل مش مهم في المكالمات المتروكة
                'queuelog.event',
                DB::raw('0 as data2'), // لا يوجد talk time
                DB::raw('NULL as data5'),
                DB::raw('NULL as dstchannel'),
                DB::raw('NULL as survey'),
                'cdr.linkedid',
                'cdr.did',
                'cdr.src',
                'cdr.disposition',
                DB::raw('0 as transfers_count'), // لا يوجد transfers
                DB::raw('enterqueue.enter_time AS call_time'),
                DB::raw('enterqueue.enter_queuename'),
                'queues_config.descr as queue_description',
                DB::raw("
                    (
                        SELECT SUM(CASE 
                            WHEN event IN ('EXITWITHTIMEOUT', 'ABANDON') THEN CAST(data3 AS UNSIGNED)
                            WHEN event IN ('COMPLETECALLER', 'COMPLETEAGENT') THEN CAST(data1 AS UNSIGNED)
                            ELSE 0
                        END)
                        FROM asteriskcdrdb.queuelog AS q2
                        WHERE q2.callid = queuelog.callid
                    ) AS ringtime
                "),
            ])
            ->join('asteriskcdrdb.cdr', 'cdr.uniqueid', '=', 'queuelog.callid')
            ->leftJoin('calls', 'calls.linkedid', '=', 'cdr.linkedid')
            ->leftJoin(DB::raw("
                (
                    SELECT *
                    FROM (
                        SELECT 
                            callid, 
                            time AS enter_time, 
                            queuename AS enter_queuename,
                            ROW_NUMBER() OVER (PARTITION BY callid ORDER BY time ASC, id ASC) AS rn
                        FROM asteriskcdrdb.queuelog
                        WHERE event = 'ENTERQUEUE'
                    ) AS ranked
                    WHERE rn = 1
                ) AS enterqueue
            "), 'enterqueue.callid', '=', 'queuelog.callid')
            ->leftJoin('asterisk.queues_config', 'queues_config.extension', '=', 'enterqueue.enter_queuename')
            ->whereIn('queuelog.event', ['ABANDON', 'EXITWITHTIMEOUT'])
            ->whereBetween('enterqueue.enter_time', [$startOfDay, $endOfDay])
            ->orderBy('enterqueue.enter_time', 'asc')
            ->get();
    }
    
    /**
     * Process and insert data
     */
    private function processAndInsertData($data, $date)
    {
        $reports = [];
        $batchSize = 1000;
        
        foreach ($data as $item) {
            
            $reports[] = [
                'linkedid' => $item->linkedid,
                'callid' => $item->callid,
                'call_time' => $item->call_time,
                'queue_description' => $item->queue_description,
                'agent' => $item->agent,
                'hangup_type' => $item->event,
                'status' => $item->status ?? '',
                'ringtime' => $item->ringtime ?? 0,
                'talktime' => $item->talktime ?? 0,
                'caller_number' => $item->src,
                'did' => $item->did,
                'transfers_count' => $item->transfers_count ?? 0,
                'survey' => $item->survey,
                'user_id' => $this->extractUserIdFromAgent($item->dstchannel),
                'enter_queuename' => $item->enter_queuename,
                'event' => $item->event,
                'data5' => $item->data5,
                'disposition' => $item->disposition,
                'report_date' => $date,
                'created_at' => now(),
                'updated_at' => now()
            ];
            
            // Insert data in batches
            if (count($reports) >= $batchSize) {
                $this->insertBatch($reports);
                $reports = [];
            }
        }
        
        // Insert remaining data
        if (!empty($reports)) {
            $this->insertBatch($reports);
        }
        
        return count($data);
    }
    
    /**
     * Determine call status based on event and disposition
     */
    private function determineCallStatus($event, $disposition)
    {
        // المكالمات المجابة
        if (in_array($event, ['COMPLETECALLER', 'COMPLETEAGENT']) && $disposition === 'ANSWERED') {
            return 'answered';
        }
        
        // المكالمات المفوتة
        if (in_array($event, ['ABANDON', 'EXITWITHTIMEOUT']) && 
            in_array($disposition, ['NO ANSWER', 'BUSY', 'FAILED', 'CONGESTION'])) {
            return 'missed';
        }
        
        return 'unknown';
    }
    
    /**
     * Safe batch insert with error handling
     */
    private function insertBatch($reports)
    {
        try {
            InboundReport::insert($reports);
        } catch (\Exception $e) {
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                \Log::error("Batch insert error: " . $e->getMessage());
            }
            
            // Insert one by one if batch fails
            foreach ($reports as $report) {
                try {
                    InboundReport::updateOrCreate(
                        [
                            'callid' => $report['callid'],
                            'report_date' => $report['report_date']
                        ],
                        $report
                    );
                } catch (\Exception $singleError) {
                    if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                        \Log::error("Failed to insert single report for callid {$report['callid']}: " . $singleError->getMessage());
                    }
                }
            }
        }
    }
    
    /**
     * Extract user_id from agent
     */
    private function extractUserIdFromAgent($agent)
    {
        if (!$agent) return null;
        
        if (preg_match('/\d+/', $agent, $matches)) {
            return $matches[0];
        }
        
        return null;
    }
    
    /**
     * Regenerate reports for a period of days
     */
    public function regenerateReports($days = 7)
    {
        $totalRecords = 0;
        
        for ($i = 1; $i <= $days; $i++) {
            $date = Carbon::now()->subDays($i)->format('Y-m-d');
            $this->info("Processing date: {$date}");
            
            $count = $this->generateDailyReport($date);
            $totalRecords += $count;
            
            $this->info("  Generated {$count} records");
        }
        
        return $totalRecords;
    }

    /**
     * Process a single call by linkedid (answered or missed)
     */
    public function processSingleCall($linkedid)
    {
        try {
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                \Log::info("Processing single call: {$linkedid}");
            }
            
            // First: Check if the call already exists
            $existing = InboundReport::where('linkedid', $linkedid)->first();
            if ($existing) {
                if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                    \Log::info("Call {$linkedid} already exists in reports, deleting old record");
                }
                $existing->delete();
            }
            
            // Second: Fetch call data (answered or missed)
            $callData = $this->fetchSingleCallData($linkedid);
            
            if (!$callData) {
                if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                    \Log::warning("No data found for call: {$linkedid}");
                }
                return false;
            }

            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                \Log::info("Call data fetched successfully for {$linkedid}", (array)$callData);
            }
            
            $report = $this->createReportFromCallData($callData);
            
            if ($report) {
                if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                    \Log::info("Successfully processed call {$linkedid}");
                }
                return $report;
            }
            
            return false;
            
        } catch (\Exception $e) {
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                \Log::error("Error processing single call {$linkedid}: " . $e->getMessage(), [
                    'trace' => $e->getTraceAsString()
                ]);
            }
            return false;
        }
    }

    /**
     * Fetch single call data by linkedid (answered or missed)
     */
    private function fetchSingleCallData($linkedid)
    {
        $cleanedLinkedid = trim($linkedid);
        
        $result = DB::selectOne("
            SELECT 
                ql.callid,
                ql.agent,
                ql.event,
                ql.data2,
                ql.data5,
                cdr.linkedid,
                cdr.did,
                cdr.dstchannel,
                cdr.src,
                cdr.disposition,
                eq.enter_time as call_time,
                eq.enter_queuename,
                qc.descr as queue_description,
                
                (
                    SELECT SUM(CASE 
                        WHEN event IN ('EXITWITHTIMEOUT', 'ABANDON') THEN CAST(data3 AS UNSIGNED)
                        WHEN event IN ('COMPLETECALLER', 'COMPLETEAGENT') THEN CAST(data1 AS UNSIGNED)
                        ELSE 0
                    END)
                    FROM asteriskcdrdb.queuelog AS ql2
                    WHERE ql2.callid = ql.callid
                ) AS ringtime,
                
                CASE 
                    WHEN cdr.disposition = 'ANSWERED' THEN (
                        SELECT COUNT(*) 
                        FROM asteriskcdrdb.cdr AS transfer_cdr
                        WHERE transfer_cdr.linkedid = cdr.linkedid 
                        AND transfer_cdr.lastapp = 'Dial'
                        AND EXISTS (
                            SELECT 1
                            FROM asteriskcdrdb.cdr c2
                            WHERE c2.linkedid = cdr.linkedid
                            AND c2.disposition = 'ANSWERED'
                            AND c2.lastapp != 'Queue'
                            AND c2.dcontext = 'from-queue-exten-only'
                            AND c2.calldate < transfer_cdr.calldate
                        )
                    )
                    ELSE 0
                END AS transfers_count
                
            FROM asteriskcdrdb.queuelog ql
            LEFT JOIN asteriskcdrdb.cdr cdr ON cdr.uniqueid = ql.callid
            LEFT JOIN (
                SELECT 
                    callid,
                    MIN(time) as enter_time,
                    SUBSTRING_INDEX(GROUP_CONCAT(queuename ORDER BY time ASC), ',', 1) as enter_queuename
                FROM asteriskcdrdb.queuelog
                WHERE event = 'ENTERQUEUE'
                GROUP BY callid
            ) eq ON eq.callid = ql.callid
            LEFT JOIN asterisk.queues_config qc ON qc.extension = eq.enter_queuename
            
            WHERE ql.callid = ?
            AND (
                (cdr.disposition = 'ANSWERED' AND ql.event IN ('COMPLETECALLER', 'COMPLETEAGENT'))
                OR 
                ql.event IN ('ABANDON', 'EXITWITHTIMEOUT')
            )
            
            ORDER BY eq.enter_time DESC
            LIMIT 1
        ", [$cleanedLinkedid]);
        
        return $result;
    }

    /**
     * Create report from call data
     */
    private function createReportFromCallData($callData)
    {

        
        // Prepare data for insertion
        $reportData = [
            'linkedid' => $callData->linkedid,
            'callid' => $callData->callid,
            'call_time' => $callData->call_time,
            'queue_description' => $callData->queue_description,
            'agent' => $callData->agent,
            'hangup_type' => $callData->event,
            'ringtime' => $callData->ringtime ?? 0,
            'talktime' => $callData->talktime ?? 0,
            'caller_number' => $callData->src,
            'did' => $callData->did,
            'transfers_count' => $callData->transfers_count ?? 0,
            'user_id' => $this->extractUserIdFromAgent($callData->dstchannel),
            'enter_queuename' => $callData->enter_queuename,
            'event' => $callData->event,
            'data5' => $callData->data5,
            'disposition' => $callData->disposition,
            'report_date' => Carbon::parse($callData->call_time)->format('Y-m-d'),
            'created_at' => now(),
            'updated_at' => now()
        ];
        
        // Insert data
        return InboundReport::updateOrCreate(
            [
                'callid' => $callData->callid,
                'report_date' => Carbon::parse($callData->call_time)->format('Y-m-d')
            ],
            $reportData
        );
    }
    
    /**
     * Get statistics for a date
     */
    public function getDailyStatistics($date)
    {
        $answered = InboundReport::where('report_date', $date)
            ->where('call_status', 'answered')
            ->count();
            
        $missed = InboundReport::where('report_date', $date)
            ->where('call_status', 'missed')
            ->count();
            
        $total = InboundReport::where('report_date', $date)
            ->count();
            
        $avgTalktime = InboundReport::where('report_date', $date)
            ->where('call_status', 'answered')
            ->avg('talktime') ?? 0;
            
        $avgRingtime = InboundReport::where('report_date', $date)
            ->avg('ringtime') ?? 0;
        
        return [
            'date' => $date,
            'answered_calls' => $answered,
            'missed_calls' => $missed,
            'total_calls' => $total,
            'answer_rate' => $total > 0 ? round(($answered / $total) * 100, 2) : 0,
            'avg_talktime_seconds' => round($avgTalktime, 2),
            'avg_ringtime_seconds' => round($avgRingtime, 2),
            'avg_talktime_formatted' => $this->secondsToTime($avgTalktime),
            'avg_ringtime_formatted' => $this->secondsToTime($avgRingtime)
        ];
    }
    
    private function secondsToTime($seconds)
    {
        if (!$seconds) return '00:00:00';
        
        $hours = floor($seconds / 3600);
        $minutes = floor(($seconds % 3600) / 60);
        $secs = $seconds % 60;
        
        return sprintf('%02d:%02d:%02d', $hours, $minutes, $secs);
    }
}