<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');

$host = 'localhost';
$user = 'reportes';
$pass = 'Rep0rt3s2024';
$db = 'asteriskcdrdb';

try {
    $conn = new PDO("mysql:host=$host;dbname=$db;charset=utf8", $user, $pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Obtener parámetros y agregar hora completa
    $fecha_inicio = isset($_GET['fecha_inicio']) ? $_GET['fecha_inicio'] . ' 00:00:00' : date('Y-m-01') . ' 00:00:00';
    $fecha_fin = isset($_GET['fecha_fin']) ? $_GET['fecha_fin'] . ' 23:59:59' : date('Y-m-t') . ' 23:59:59';
    
    $response = [
        'periodo' => [
            'inicio' => $_GET['fecha_inicio'] ?? date('Y-m-01'),
            'fin' => $_GET['fecha_fin'] ?? date('Y-m-t')
        ],
        'salientes' => getSalientes($conn, $fecha_inicio, $fecha_fin),
        'entrantes' => getEntrantes($conn, $fecha_inicio, $fecha_fin),
        'internas' => getInternas($conn, $fecha_inicio, $fecha_fin)
    ];
    
    echo json_encode($response, JSON_PRETTY_PRINT);
    
} catch(PDOException $e) {
    echo json_encode(['error' => $e->getMessage()]);
}

function getSalientes($conn, $inicio, $fin) {
    // Llamadas salientes: dst externo (>4 caracteres); extensión = src con longitud <= 4 (excluye números largos).
    // # Extensiones: COUNT(DISTINCT src) solo entre esas filas.
    $stmt = $conn->prepare("
        SELECT 
            COUNT(*) as total_llamadas,
            SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) as contestadas,
            SUM(CASE WHEN disposition = 'NO ANSWER' THEN 1 ELSE 0 END) as no_contestadas,
            SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) as fallidas,
            SUM(CASE WHEN disposition = 'BUSY' THEN 1 ELSE 0 END) as ocupadas,
            SUM(billsec) as minutos_totales,
            AVG(billsec) as duracion_promedio,
            COUNT(DISTINCT NULLIF(TRIM(src), '')) as extensiones
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND LENGTH(dst) > 4
        AND TRIM(IFNULL(src, '')) <> ''
        AND LENGTH(TRIM(src)) <= 4
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $stats = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Por día
    $stmt = $conn->prepare("
        SELECT 
            DATE(calldate) as fecha,
            COUNT(*) as llamadas,
            SUM(billsec) as minutos
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND LENGTH(dst) > 4
        AND TRIM(IFNULL(src, '')) <> ''
        AND LENGTH(TRIM(src)) <= 4
        GROUP BY DATE(calldate)
        ORDER BY fecha
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $por_dia = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Top extensiones
    $stmt = $conn->prepare("
        SELECT 
            src as extension,
            cnam as nombre,
            COUNT(*) as llamadas,
            SUM(billsec) as minutos
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND LENGTH(dst) > 4
        AND TRIM(IFNULL(src, '')) <> ''
        AND LENGTH(TRIM(src)) <= 4
        GROUP BY src, cnam
        ORDER BY llamadas DESC
        LIMIT 10
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $top_extensiones = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Destinos más llamados
    $stmt = $conn->prepare("
        SELECT 
            dst as destino,
            COUNT(*) as llamadas
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND LENGTH(dst) > 4
        AND TRIM(IFNULL(src, '')) <> ''
        AND LENGTH(TRIM(src)) <= 4
        GROUP BY dst
        ORDER BY llamadas DESC
        LIMIT 5
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $destinos = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    return [
        'stats' => $stats,
        'por_dia' => $por_dia,
        'top_extensiones' => $top_extensiones,
        'destinos' => $destinos
    ];
}

function getEntrantes($conn, $inicio, $fin) {
    // Llamadas entrantes: src vacío o externo
    $stmt = $conn->prepare("
        SELECT 
            COUNT(*) as total_llamadas,
            SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) as contestadas,
            SUM(CASE WHEN disposition = 'NO ANSWER' THEN 1 ELSE 0 END) as no_contestadas,
            SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) as fallidas,
            SUM(CASE WHEN disposition = 'BUSY' THEN 1 ELSE 0 END) as ocupadas,
            SUM(billsec) as minutos_totales,
            COUNT(DISTINCT dst) as extensiones
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND (LENGTH(src) > 4 OR src = '' OR src IS NULL)
        AND LENGTH(dst) <= 4
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $stats = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Por día
    $stmt = $conn->prepare("
        SELECT 
            DATE(calldate) as fecha,
            COUNT(*) as llamadas,
            SUM(billsec) as minutos
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND (LENGTH(src) > 4 OR src = '' OR src IS NULL)
        AND LENGTH(dst) <= 4
        GROUP BY DATE(calldate)
        ORDER BY fecha
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $por_dia = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Top destinos (extensiones que reciben)
    $stmt = $conn->prepare("
        SELECT 
            dst as extension,
            COUNT(*) as llamadas,
            SUM(billsec) as minutos
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND (LENGTH(src) > 4 OR src = '' OR src IS NULL)
        AND LENGTH(dst) <= 4
        GROUP BY dst
        ORDER BY llamadas DESC
        LIMIT 10
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $top_destinos = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    return [
        'stats' => $stats,
        'por_dia' => $por_dia,
        'top_destinos' => $top_destinos
    ];
}

function getInternas($conn, $inicio, $fin) {
    // Llamadas internas: src y dst extensión exactamente 4 dígitos numéricos
    $stmt = $conn->prepare("
        SELECT 
            COUNT(*) as total_llamadas,
            SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) as contestadas,
            SUM(CASE WHEN disposition = 'NO ANSWER' THEN 1 ELSE 0 END) as no_contestadas,
            SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) as fallidas,
            SUM(CASE WHEN disposition = 'BUSY' THEN 1 ELSE 0 END) as ocupadas,
            SUM(billsec) as minutos_totales,
            COUNT(DISTINCT NULLIF(TRIM(src), '')) as extensiones_origen
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND TRIM(IFNULL(src, '')) REGEXP '^[0-9]{4}$'
        AND TRIM(IFNULL(dst, '')) REGEXP '^[0-9]{4}$'
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $stats = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Por día
    $stmt = $conn->prepare("
        SELECT 
            DATE(calldate) as fecha,
            COUNT(*) as llamadas,
            SUM(billsec) as minutos
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND TRIM(IFNULL(src, '')) REGEXP '^[0-9]{4}$'
        AND TRIM(IFNULL(dst, '')) REGEXP '^[0-9]{4}$'
        GROUP BY DATE(calldate)
        ORDER BY fecha
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $por_dia = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Top orígenes
    $stmt = $conn->prepare("
        SELECT 
            src as extension,
            cnam as nombre,
            COUNT(*) as llamadas,
            SUM(billsec) as minutos
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND TRIM(IFNULL(src, '')) REGEXP '^[0-9]{4}$'
        AND TRIM(IFNULL(dst, '')) REGEXP '^[0-9]{4}$'
        GROUP BY src, cnam
        ORDER BY llamadas DESC
        LIMIT 10
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $top_origen = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Top destinos
    $stmt = $conn->prepare("
        SELECT 
            dst as extension,
            COUNT(*) as llamadas,
            SUM(billsec) as minutos
        FROM cdr
        WHERE calldate BETWEEN :inicio AND :fin
        AND TRIM(IFNULL(src, '')) REGEXP '^[0-9]{4}$'
        AND TRIM(IFNULL(dst, '')) REGEXP '^[0-9]{4}$'
        GROUP BY dst
        ORDER BY llamadas DESC
        LIMIT 5
    ");
    $stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
    $top_destinos = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    return [
        'stats' => $stats,
        'por_dia' => $por_dia,
        'top_origen' => $top_origen,
        'top_destinos' => $top_destinos
    ];
}
?>