Analytics & Reporting

// From DashboardController.php:21-44 - Dashboard Data
public function index(FundTransactionService $fundTransactionService)
{
    $data = [
        'finance' => calculateFinancials(),
        'customers' => User::where('role', 'customer')->count(),
        'agents' => User::where('role', 'agent')->count(),
        'stats' => $fundTransactionService->getDashboardStats(),
        'yearlyData' => $fundTransactionService->getTransactionsByPeriod('yearly'),
        'monthlyData' => $fundTransactionService->getTransactionsByPeriod('monthly'),
        'weeklyData' => $fundTransactionService->getTransactionsByPeriod('weekly'),
        'dailyData' => $fundTransactionService->getTransactionsByPeriod('daily'),
        'transactions' => $fundTransactionService->getTransactions(['type' => null], true),
        'transactionsCount' => $fundTransactionService->getTransactions(['type' => null], false),
        'gateways' => $fundTransactionService->getGatewayBasedTotals(),
    ];
    
    return view('backend.dashboard.index', $data);
}

// From AnalyticsController.php:9-24 - Analytics Views
public function index()
{
    return view('backend.analytics.index');
}

public function index_ajax()
{
    return view('backend.analytics.index_ajax');
}

// From AnalyticsController.php:28-39 - Provider Analytics
public function analytic($account_sid, $phone)
{
    return view('backend.analytics.analytic', compact('account_sid', 'phone'));
}

public function analytic_ajax($account_sid, $phone)
{
    return view('backend.analytics.analytic_ajax', compact('account_sid', 'phone'));
}

// From CallHistoryController.php:9-12 - Call History
public function index()
{
    return view('backend.call_history.index');
}

-- From migrations/2023_04_16_231454_create_call_histories_table.php
CREATE TABLE call_histories (
    id BIGINT UNSIGNED PRIMARY KEY,
    caller_uuid TEXT,              -- Unique call identifier
    user_id BIGINT UNSIGNED,       -- Agent who handled the call
    identity_id BIGINT UNSIGNED,   -- Twilio identity
    my_number TEXT,                -- Your Twilio number
    caller_number TEXT,            -- Customer's phone number
    pick_up_time TEXT,             -- When call was answered
    hang_up_time TEXT,             -- When call ended
    dial_call_sid TEXT,            -- Twilio call SID for lookup
    record_file TEXT,              -- Recording URL (MP3)
    status TEXT,                   -- missed, picked, hanged
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- From migrations/2022_06_28_101033_create_campaign_voices_table.php
CREATE TABLE campaign_voices (
    id BIGINT UNSIGNED PRIMARY KEY,
    campaign_id BIGINT UNSIGNED,
    contact_id BIGINT UNSIGNED,
    user_id BIGINT UNSIGNED,
    phone TEXT,
    status TEXT,                    -- Lead status (interested, not_interested, etc.)
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

// Example campaign metrics calculation
$total_contacts = CampaignVoice::where('campaign_id', $campaign_id)->count();
$interested = CampaignVoice::where('campaign_id', $campaign_id)
                           ->where('status', 'interested')
                           ->count();
$conversion_rate = ($interested / $total_contacts) * 100;

-- From migrations/2023_04_19_190736_create_sms_schedules_table.php
CREATE TABLE sms_schedules (
    id BIGINT UNSIGNED PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    campaign_id BIGINT UNSIGNED,
    group_id BIGINT UNSIGNED,
    provider BIGINT UNSIGNED,
    start_at TEXT,
    status TEXT,                    -- PENDING, RUNNING, COMPLETED
    third_party_provider TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- From migrations/2022_08_05_094213_create_twilio_call_costs_table.php
CREATE TABLE twilio_call_costs (
    id BIGINT UNSIGNED PRIMARY KEY,
    code VARCHAR(10),               -- Country code
    teleman_call_cost DECIMAL,      -- Cost per minute
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

// From DashboardController.php:31-41 - Financial Data
'stats' => $fundTransactionService->getDashboardStats(),
'yearlyData' => $fundTransactionService->getTransactionsByPeriod('yearly'),
'monthlyData' => $fundTransactionService->getTransactionsByPeriod('monthly'),
'weeklyData' => $fundTransactionService->getTransactionsByPeriod('weekly'),
'dailyData' => $fundTransactionService->getTransactionsByPeriod('daily'),
'transactions' => $fundTransactionService->getTransactions(['type' => null], true),
'gateways' => $fundTransactionService->getGatewayBasedTotals(),

// From VapiController.php:19-26 - AI Call Analytics
public function index()
{
    $callLogs = collect($this->vapiService->listCalls())->sortByDesc('createdAt')->toArray();
    $reportService = new VapiReportService($callLogs);
    return view('backend.voiceai.index', [
        'reportService' => $reportService,
    ]);
}

// From CampaignController.php:470-482 - Export Leads
public function leads_export($campaign_id)
{
    store_leads_export_history($campaign_id);
    return Excel::download(new LeadsExport($campaign_id), 'leads.csv');
}

// From ContactController.php:371-374 - Export Contacts
public function export()
{
    return Excel::download(new ContactsExport, 'contacts.csv');
}

-- From migrations/2022_06_17_201226_create_quota_logs_table.php
CREATE TABLE quota_logs (
    id BIGINT UNSIGNED PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    provider BIGINT UNSIGNED,
    contact_id BIGINT UNSIGNED,
    phone TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

// Example: Campaign performance by day of week
$performance_by_day = CampaignVoice::selectRaw('DAYNAME(created_at) as day, 
                                                 COUNT(*) as total, 
                                                 SUM(CASE WHEN status="interested" THEN 1 ELSE 0 END) as interested')
                                   ->where('campaign_id', $campaign_id)
                                   ->groupBy('day')
                                   ->get();