<?php
// finance-search.php

header('Content-Type: application/json; charset=utf-8');
ini_set('display_errors', 0);
error_reporting(E_ALL);

// 1. Seguridad y Conexión BD
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

require_once __DIR__ . '/../core/api.php';
global $pdo;

$user_id = $_SESSION['user_id'] ?? null;
$q = isset($_GET['q']) ? trim($_GET['q']) : '';

if (!$user_id || strlen($q) < 1 || !$pdo) {
    echo json_encode([]);
    exit;
}

try {
    $pdo->exec("SET NAMES 'utf8mb4'");

    $term = "%" . $q . "%";
    
    $sql = "
        /* 1. BUSCAR EN FACTURAS */
        (SELECT 
            i.id, 
            i.external_id as reference, 
            i.total_amount as amount, 
            i.invoice_date as date_val,
            'Factura' as type,
            i.status,
            COALESCE(CONCAT(c.first_name, ' ', c.last_name), i.external_id) as client_name
         FROM invoices i
         LEFT JOIN clients c ON i.client_id = c.id
         WHERE i.user_id = :uid1
         AND (
            i.external_id LIKE :term1 OR 
            i.total_amount LIKE :term2 OR
            c.first_name LIKE :term3 OR 
            c.last_name LIKE :term4
         )
         LIMIT 5)

        UNION ALL

        /* 2. BUSCAR EN COTIZACIONES (Mapeado de 'estimates' a 'quotes') */
        (SELECT 
            q.id, 
            q.external_id as reference, 
            q.total_amount as amount, 
            q.estimate_date as date_val,
            'Cotización' as type,
            q.status,
            COALESCE(CONCAT(c.first_name, ' ', c.last_name), q.external_id) as client_name
         FROM quotes q
         LEFT JOIN clients c ON q.client_id = c.id
         WHERE q.user_id = :uid2
         AND (
            q.external_id LIKE :term5 OR 
            q.total_amount LIKE :term6 OR
            c.first_name LIKE :term7 OR 
            c.last_name LIKE :term8
         )
         LIMIT 5)

        UNION ALL

        /* 3. BUSCAR EN TRANSACCIONES (Mapeado de 'finances' a 'transactions') */
        (SELECT 
            t.id, 
            t.description as reference, 
            t.amount as amount, 
            t.transaction_date as date_val,
            CONCAT('Finanza: ', t.transaction_type) as type, 
            'Completado' as status, 
            CASE 
                WHEN t.provider_name IS NOT NULL THEN t.provider_name
                ELSE COALESCE(CONCAT(c.first_name, ' ', c.last_name), 'N/A')
            END as client_name
         FROM transactions t
         LEFT JOIN clients c ON t.client_id = c.id
         WHERE t.user_id = :uid3
         AND (
            t.description LIKE :term9 OR 
            t.amount LIKE :term10 OR
            t.provider_name LIKE :term11
         )
         LIMIT 5)
         
         ORDER BY date_val DESC 
    ";
    
    $stmt = $pdo->prepare($sql);
    
    $params = [
        ':uid1' => $user_id,
        ':term1' => $term, ':term2' => $term, ':term3' => $term, ':term4' => $term,
        ':uid2' => $user_id,
        ':term5' => $term, ':term6' => $term, ':term7' => $term, ':term8' => $term,
        ':uid3' => $user_id,
        ':term9' => $term, ':term10' => $term, ':term11' => $term
    ];
    
    $stmt->execute($params);
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $finalResponse = [];
    foreach($results as $row) {
        
        $url = '#';
        if ($row['type'] === 'Factura') $url = 'invoice-details.php?id=' . $row['id'];
        if ($row['type'] === 'Cotización') $url = 'estimate-details.php?id=' . $row['id'];
        if (strpos($row['type'], 'Finanza') !== false) $url = 'finance-details.php?id=' . $row['id'];

        $finalResponse[] = [
            'id' => $row['id'],
            'type' => ucfirst($row['type']), 
            'reference' => $row['reference'], 
            'amount' => '$' . number_format($row['amount'], 2),
            'date' => $row['date_val'],
            'status' => $row['status'],
            'client' => $row['client_name'] ? $row['client_name'] : 'Sin Cliente',
            'url' => $url
        ];
    }
    
    echo json_encode($finalResponse);

} catch (Exception $e) {
    error_log("Error en finance-search.php: " . $e->getMessage());
    echo json_encode([]);
}