<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
set_time_limit(60);
header('Content-Type: application/json; charset=utf-8');

require_once dirname(__FILE__) . '/../../config.php';
$db = getDB();

$fechaInicio = '2026-03-01';
$fechaFin    = '2026-03-31';
$results = [];

// Query 1: IDs de notas
$t0 = microtime(true);
$stmt = $db->prepare("SELECT t.idtransacciones FROM transacciones t WHERE t.tipostr_idtipostr = 11 AND t.fechat BETWEEN ? AND ?");
$stmt->execute([$fechaInicio, $fechaFin]);
$ids = array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'idtransacciones');
$results['q1_ids'] = ['time_ms' => round((microtime(true)-$t0)*1000), 'count' => count($ids)];

if (empty($ids)) { echo json_encode($results); exit; }
$placeholders = implode(',', array_fill(0, count($ids), '?'));

// Query 2: depositos
$t0 = microtime(true);
$stmt = $db->prepare("SELECT transacciones_idtransacciones AS idtx, COALESCE(SUM(puntos),0) AS p FROM depositos WHERE transacciones_idtransacciones IN ($placeholders) GROUP BY transacciones_idtransacciones");
$stmt->execute($ids);
$stmt->fetchAll();
$results['q2_depositos'] = ['time_ms' => round((microtime(true)-$t0)*1000)];

// Query 3: nsalida con JOIN a productos y promod
$t0 = microtime(true);
$stmt = $db->prepare("SELECT ns.transacciones_idtransacciones AS idtx, SUM(ns.cant) AS total_items, SUM(ns.asignar) AS items_asig, SUM(CASE WHEN (ns.cant - ns.asignar) > 0 THEN (ns.cant - ns.asignar) ELSE 0 END) AS items_pend, SUM(CASE WHEN ns.productos_idprod <= 1000 THEN ns.cant * COALESCE(p.puntos,0) ELSE 0 END) AS puntos_prod, SUM(CASE WHEN ns.productos_idprod > 1000 THEN ns.cant * COALESCE(pm.puntos,0) ELSE 0 END) AS puntos_promo FROM nsalida ns LEFT JOIN productos p ON p.id_prod = ns.productos_idprod AND ns.productos_idprod <= 1000 LEFT JOIN promod pm ON pm.id_promo = ns.productos_idprod AND ns.productos_idprod > 1000 WHERE ns.transacciones_idtransacciones IN ($placeholders) GROUP BY ns.transacciones_idtransacciones");
$stmt->execute($ids);
$stmt->fetchAll();
$results['q3_nsalida'] = ['time_ms' => round((microtime(true)-$t0)*1000)];

// Query 4: asignaciones tipo 12
$t0 = microtime(true);
$stmt = $db->prepare("SELECT t2.nota AS idtx_origen, COALESCE(SUM(d2.puntos),0) AS puntos_asig FROM transacciones t2 LEFT JOIN depositos d2 ON d2.transacciones_idtransacciones = t2.idtransacciones WHERE t2.tipostr_idtipostr = 12 AND t2.nota IN ($placeholders) GROUP BY t2.nota");
$stmt->execute($ids);
$stmt->fetchAll();
$results['q4_asignaciones'] = ['time_ms' => round((microtime(true)-$t0)*1000)];

// Query 5: inversionista
$t0 = microtime(true);
$stmt = $db->prepare("SELECT t.idtransacciones, t.fechat, t.nota, t.inversionista_id_inver, CONCAT(i.nombre,' ',i.appat,' ',COALESCE(i.apmat,'')) AS nombre_dist, i.referencia, i.rfc FROM transacciones t JOIN inversionista i ON i.id_inver = t.inversionista_id_inver WHERE t.tipostr_idtipostr = 11 AND t.fechat BETWEEN ? AND ?");
$stmt->execute([$fechaInicio, $fechaFin]);
$stmt->fetchAll();
$results['q5_inversionista'] = ['time_ms' => round((microtime(true)-$t0)*1000)];

echo json_encode($results, JSON_PRETTY_PRINT);
