<?php
require_once(dirname(__DIR__) . '/config/db.php');

ini_set('display_errors', '0');
error_reporting(E_ALL);

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$cdempresa = $_SESSION['cdempresa'] ?? 1;

function json_out_orcreal($arr, $code = 200) {
  http_response_code($code);
  header('Content-Type: application/json; charset=utf-8');
  echo json_encode($arr, JSON_UNESCAPED_UNICODE);
  exit;
}

set_error_handler(function($severity, $message, $file, $line) {
  $loc = basename($file) . ':' . $line;
  throw new ErrorException("$message @ $loc", 0, $severity, $file, $line);
});

set_exception_handler(function($e) {
  $loc = basename($e->getFile()) . ':' . $e->getLine();
  $msg = $e->getMessage();
  json_out_orcreal(['ok'=>false,'msg'=>"Erro interno ($loc): $msg"], 500);
});

$ajax = $_GET['ajax'] ?? '';
if ($ajax === '') { http_response_code(204); exit; }

switch ($ajax) {
  case 'comparar_periodos':
    compararPeriodosOrcReal();
    break;

  case 'detalhe_lancamentos':
    // Reutiliza o detalhe do realizado
    require_once __DIR__ . '/comparativo_realizado_actions.php';
    break;

  default:
    json_out_orcreal(['ok' => false, 'msg' => 'Ação inválida'], 400);
}

/* ============================================================
 * COMPARAR PERÍODOS — ORÇADO x REALIZADO
 * Retorna para cada conta: orc_a, real_a, orc_b, real_b + variações
 * ============================================================ */
function compararPeriodosOrcReal() {
  global $pdo, $cdempresa;

  $anoA   = (int)($_GET['ano_a'] ?? 0);
  $anoB   = (int)($_GET['ano_b'] ?? 0);
  $mesesA = array_filter(array_map('intval', (array)($_GET['meses_a'] ?? [])));
  $mesesB = array_filter(array_map('intval', (array)($_GET['meses_b'] ?? [])));

  if (!$anoA || !$anoB || empty($mesesA) || empty($mesesB)) {
    json_out_orcreal(['ok' => false, 'msg' => 'Informe os dois períodos para comparar.'], 400);
  }

  foreach (array_merge($mesesA, $mesesB) as $m) {
    if ($m < 1 || $m > 12) {
      json_out_orcreal(['ok' => false, 'msg' => 'Mês inválido informado.'], 400);
    }
  }

  $cdcentrocusto = !empty($_GET['cdcentrocusto']) ? (int)$_GET['cdcentrocusto'] : null;
  $tpmovimento   = (!empty($_GET['tpmovimento']) && in_array($_GET['tpmovimento'], ['C','D'])) ? $_GET['tpmovimento'] : null;

  $phA = implode(',', array_fill(0, count($mesesA), '?'));
  $phB = implode(',', array_fill(0, count($mesesB), '?'));

  // ---------- ORÇADO (via MV, sem filtro CC/tpmov) ----------
  // Agregado
  $sqlOrc = "
    SELECT 'A' AS periodo, v.cdcontacontabil, COALESCE(SUM(v.valor_mes), 0) AS valor
      FROM back.v_orcado_conta_mes v
     WHERE v.cdempresa = ?
       AND v.anoref = ?
       AND v.mesref IN ($phA)
     GROUP BY v.cdcontacontabil

    UNION ALL

    SELECT 'B' AS periodo, v.cdcontacontabil, COALESCE(SUM(v.valor_mes), 0) AS valor
      FROM back.v_orcado_conta_mes v
     WHERE v.cdempresa = ?
       AND v.anoref = ?
       AND v.mesref IN ($phB)
     GROUP BY v.cdcontacontabil
  ";
  $paramsOrc = array_merge(
    [$cdempresa, $anoA], $mesesA,
    [$cdempresa, $anoB], $mesesB
  );

  $st = $pdo->prepare($sqlOrc);
  $st->execute($paramsOrc);

  $orcA = []; // cdcontacontabil => valor
  $orcB = [];
  while ($row = $st->fetch(PDO::FETCH_ASSOC)) {
    $cd  = (int)$row['cdcontacontabil'];
    $val = (float)$row['valor'];
    if ($row['periodo'] === 'A') $orcA[$cd] = ($orcA[$cd] ?? 0) + $val;
    else                         $orcB[$cd] = ($orcB[$cd] ?? 0) + $val;
  }

  // Orçado por mês
  $sqlOrcMes = "
    SELECT 'A' AS periodo, v.cdcontacontabil, v.mesref, COALESCE(SUM(v.valor_mes), 0) AS valor
      FROM back.v_orcado_conta_mes v
     WHERE v.cdempresa = ? AND v.anoref = ? AND v.mesref IN ($phA)
     GROUP BY v.cdcontacontabil, v.mesref
    UNION ALL
    SELECT 'B' AS periodo, v.cdcontacontabil, v.mesref, COALESCE(SUM(v.valor_mes), 0) AS valor
      FROM back.v_orcado_conta_mes v
     WHERE v.cdempresa = ? AND v.anoref = ? AND v.mesref IN ($phB)
     GROUP BY v.cdcontacontabil, v.mesref
  ";
  $stOM = $pdo->prepare($sqlOrcMes);
  $stOM->execute(array_merge([$cdempresa, $anoA], $mesesA, [$cdempresa, $anoB], $mesesB));
  $orcMesA = []; $orcMesB = [];
  while ($r = $stOM->fetch(PDO::FETCH_ASSOC)) {
    $cd = (int)$r['cdcontacontabil']; $m = (int)$r['mesref'];
    if ($r['periodo'] === 'A') { $orcMesA[$cd][$m] = (float)$r['valor']; }
    else                       { $orcMesB[$cd][$m] = (float)$r['valor']; }
  }

  // ---------- REALIZADO ----------
  $usarQueryDireta = ($cdcentrocusto || $tpmovimento);

  if ($usarQueryDireta) {
    $sqlReal = buildQueryDiretaReal($phA, $phB, $cdcentrocusto, $tpmovimento);
    $paramsReal = buildParamsDiretaReal($cdempresa, $cdcentrocusto, $tpmovimento, $anoA, $mesesA, $anoB, $mesesB);
  } else {
    $sqlReal = "
      SELECT 'A' AS periodo, v.cdcontacontabil, COALESCE(SUM(v.valor_mes), 0) AS valor
        FROM back.v_realizado_conta_mes v
       WHERE v.cdempresa = ?
         AND v.anoref = ?
         AND v.mesref IN ($phA)
       GROUP BY v.cdcontacontabil

      UNION ALL

      SELECT 'B' AS periodo, v.cdcontacontabil, COALESCE(SUM(v.valor_mes), 0) AS valor
        FROM back.v_realizado_conta_mes v
       WHERE v.cdempresa = ?
         AND v.anoref = ?
         AND v.mesref IN ($phB)
       GROUP BY v.cdcontacontabil
    ";
    $paramsReal = array_merge(
      [$cdempresa, $anoA], $mesesA,
      [$cdempresa, $anoB], $mesesB
    );
  }

  $st2 = $pdo->prepare($sqlReal);
  $st2->execute($paramsReal);

  $realA = [];
  $realB = [];
  while ($row = $st2->fetch(PDO::FETCH_ASSOC)) {
    $cd  = (int)$row['cdcontacontabil'];
    $val = (float)$row['valor'];
    if ($row['periodo'] === 'A') $realA[$cd] = ($realA[$cd] ?? 0) + $val;
    else                         $realB[$cd] = ($realB[$cd] ?? 0) + $val;
  }

  // Realizado por mês (sem filtro CC/tpmov para simplificar — usa MV)
  $sqlRealMes = "
    SELECT 'A' AS periodo, v.cdcontacontabil, v.mesref, COALESCE(SUM(v.valor_mes), 0) AS valor
      FROM back.v_realizado_conta_mes v
     WHERE v.cdempresa = ? AND v.anoref = ? AND v.mesref IN ($phA)
     GROUP BY v.cdcontacontabil, v.mesref
    UNION ALL
    SELECT 'B' AS periodo, v.cdcontacontabil, v.mesref, COALESCE(SUM(v.valor_mes), 0) AS valor
      FROM back.v_realizado_conta_mes v
     WHERE v.cdempresa = ? AND v.anoref = ? AND v.mesref IN ($phB)
     GROUP BY v.cdcontacontabil, v.mesref
  ";
  $stRM = $pdo->prepare($sqlRealMes);
  $stRM->execute(array_merge([$cdempresa, $anoA], $mesesA, [$cdempresa, $anoB], $mesesB));
  $realMesA = []; $realMesB = [];
  while ($r = $stRM->fetch(PDO::FETCH_ASSOC)) {
    $cd = (int)$r['cdcontacontabil']; $m = (int)$r['mesref'];
    if ($r['periodo'] === 'A') { $realMesA[$cd][$m] = (float)$r['valor']; }
    else                       { $realMesB[$cd][$m] = (float)$r['valor']; }
  }

  // ---------- Plano de contas ----------
  $stPlano = $pdo->query("
    SELECT cdcontacontabil, cdconta, cdcontapai, dsconta, fltipo, flnatureza
      FROM back.planocontabil
     WHERE flativo = 0
     ORDER BY cdconta ASC
  ");
  $contas = $stPlano->fetchAll(PDO::FETCH_ASSOC);

  // Totais raiz
  $totOrcA = 0; $totOrcB = 0; $totRealA = 0; $totRealB = 0;
  foreach ($contas as $c) {
    if (empty($c['cdcontapai'])) {
      $cd = (int)$c['cdcontacontabil'];
      $totOrcA  += abs($orcA[$cd] ?? 0);
      $totOrcB  += abs($orcB[$cd] ?? 0);
      $totRealA += abs($realA[$cd] ?? 0);
      $totRealB += abs($realB[$cd] ?? 0);
    }
  }

  $calcVar = function(float $va, float $vb): array {
    $varAbs = $vb - $va;
    $varPct = ($va != 0) ? (($vb - $va) / abs($va)) * 100 : ($vb != 0 ? 100 : 0);
    return [round($varAbs, 2), round($varPct, 2)];
  };

  // ---------- Montar dados ----------
  $dados = [];
  foreach ($contas as $c) {
    $cd = (int)$c['cdcontacontabil'];
    $oa = $orcA[$cd] ?? 0;
    $ra = $realA[$cd] ?? 0;
    $ob = $orcB[$cd] ?? 0;
    $rb = $realB[$cd] ?? 0;

    if ($oa == 0 && $ra == 0 && $ob == 0 && $rb == 0) continue;

    [$varOrcAbs, $varOrcPct]   = $calcVar($oa, $ob);
    [$varRealAbs, $varRealPct] = $calcVar($ra, $rb);

    // Dados por mês
    $omA = new \stdClass(); $rmA = new \stdClass();
    $omB = new \stdClass(); $rmB = new \stdClass();
    foreach ($mesesA as $m) {
      $omA->{$m} = round($orcMesA[$cd][$m] ?? 0, 2);
      $rmA->{$m} = round($realMesA[$cd][$m] ?? 0, 2);
    }
    foreach ($mesesB as $m) {
      $omB->{$m} = round($orcMesB[$cd][$m] ?? 0, 2);
      $rmB->{$m} = round($realMesB[$cd][$m] ?? 0, 2);
    }

    $dados[] = [
      'cdcontacontabil' => $cd,
      'cdconta'         => $c['cdconta'],
      'cdcontapai'      => $c['cdcontapai'] ?: null,
      'dsconta'         => $c['dsconta'],
      'fltipo'          => $c['fltipo'],
      'flnatureza'      => $c['flnatureza'] ?? null,
      'orc_a'           => round($oa, 2),
      'real_a'          => round($ra, 2),
      'orc_b'           => round($ob, 2),
      'real_b'          => round($rb, 2),
      'orc_meses_a'     => $omA,
      'real_meses_a'    => $rmA,
      'orc_meses_b'     => $omB,
      'real_meses_b'    => $rmB,
      'var_orc_abs'     => round($varOrcAbs, 2),
      'var_orc_pct'     => round($varOrcPct, 2),
      'var_real_abs'    => round($varRealAbs, 2),
      'var_real_pct'    => round($varRealPct, 2),
    ];
  }

  // Totais gerais (com sinal)
  $totGeralOrcA = 0; $totGeralOrcB = 0; $totGeralRealA = 0; $totGeralRealB = 0;
  foreach ($contas as $c) {
    if (empty($c['cdcontapai'])) {
      $cd = (int)$c['cdcontacontabil'];
      $totGeralOrcA  += ($orcA[$cd] ?? 0);
      $totGeralOrcB  += ($orcB[$cd] ?? 0);
      $totGeralRealA += ($realA[$cd] ?? 0);
      $totGeralRealB += ($realB[$cd] ?? 0);
    }
  }

  [$vOrcAbs, $vOrcPct]   = $calcVar($totGeralOrcA, $totGeralOrcB);
  [$vRealAbs, $vRealPct] = $calcVar($totGeralRealA, $totGeralRealB);

  // Totais por mês
  $totOrcMesA = []; $totRealMesA = []; $totOrcMesB = []; $totRealMesB = [];
  foreach ($contas as $c) {
    if (empty($c['cdcontapai'])) {
      $cd = (int)$c['cdcontacontabil'];
      foreach ($mesesA as $m) {
        $totOrcMesA[$m]  = ($totOrcMesA[$m] ?? 0)  + ($orcMesA[$cd][$m] ?? 0);
        $totRealMesA[$m] = ($totRealMesA[$m] ?? 0) + ($realMesA[$cd][$m] ?? 0);
      }
      foreach ($mesesB as $m) {
        $totOrcMesB[$m]  = ($totOrcMesB[$m] ?? 0)  + ($orcMesB[$cd][$m] ?? 0);
        $totRealMesB[$m] = ($totRealMesB[$m] ?? 0) + ($realMesB[$cd][$m] ?? 0);
      }
    }
  }

  json_out_orcreal([
    'ok'     => true,
    'dados'  => $dados,
    'totais' => [
      'orc_a'        => round($totGeralOrcA, 2),
      'real_a'       => round($totGeralRealA, 2),
      'orc_b'        => round($totGeralOrcB, 2),
      'real_b'       => round($totGeralRealB, 2),
      'orc_meses_a'  => (object)array_map(fn($v) => round($v, 2), $totOrcMesA),
      'real_meses_a' => (object)array_map(fn($v) => round($v, 2), $totRealMesA),
      'orc_meses_b'  => (object)array_map(fn($v) => round($v, 2), $totOrcMesB),
      'real_meses_b' => (object)array_map(fn($v) => round($v, 2), $totRealMesB),
      'var_orc_abs'  => round($vOrcAbs, 2),
      'var_orc_pct'  => round($vOrcPct, 2),
      'var_real_abs' => round($vRealAbs, 2),
      'var_real_pct' => round($vRealPct, 2),
    ],
  ]);
}

/* ============================================================
 * QUERY DIRETA PARA REALIZADO (com filtros CC / tpmovimento)
 * ============================================================ */
function buildQueryDiretaReal(string $phA, string $phB, ?int $cdcentrocusto, ?string $tpmovimento): string {
  $extraWhere = '';
  if ($cdcentrocusto) $extraWhere .= ' AND r.cdcentrocusto = ?';
  if ($tpmovimento)   $extraWhere .= ' AND r.tpmovimento = ?';

  if ($tpmovimento === 'D') {
    $valorExpr = '-SUM(r.valor)';
  } elseif ($tpmovimento === 'C') {
    $valorExpr = 'SUM(r.valor)';
  } else {
    $valorExpr = "SUM(CASE WHEN r.tpmovimento = 'C' THEN r.valor ELSE -r.valor END)";
  }

  return "
    WITH RECURSIVE hier AS (
      SELECT pc.cdcontacontabil AS leaf,
             pc.cdcontacontabil AS node,
             pc.cdcontapai
        FROM back.planocontabil pc
      UNION ALL
      SELECT h.leaf,
             p.cdcontacontabil AS node,
             p.cdcontapai
        FROM hier h
        JOIN back.planocontabil p ON p.cdconta = h.cdcontapai
    ),
    real_a AS (
      SELECT h.node AS cdcontacontabil,
             {$valorExpr} AS valor
        FROM back.realizado r
        JOIN back.planocontabil pc ON pc.cdcontacontabil = r.cdplanocontabil
        JOIN hier h ON h.leaf = pc.cdcontacontabil
       WHERE r.cdempresa = ?
         AND r.flcancelado = 0
         {$extraWhere}
         AND r.anoref = ?
         AND r.mesref IN ({$phA})
       GROUP BY h.node
    ),
    real_b AS (
      SELECT h.node AS cdcontacontabil,
             {$valorExpr} AS valor
        FROM back.realizado r
        JOIN back.planocontabil pc ON pc.cdcontacontabil = r.cdplanocontabil
        JOIN hier h ON h.leaf = pc.cdcontacontabil
       WHERE r.cdempresa = ?
         AND r.flcancelado = 0
         {$extraWhere}
         AND r.anoref = ?
         AND r.mesref IN ({$phB})
       GROUP BY h.node
    )
    SELECT 'A' AS periodo, cdcontacontabil, valor FROM real_a
    UNION ALL
    SELECT 'B' AS periodo, cdcontacontabil, valor FROM real_b
  ";
}

function buildParamsDiretaReal(int $cdempresa, ?int $cdcentrocusto, ?string $tpmovimento, int $anoA, array $mesesA, int $anoB, array $mesesB): array {
  $bloco = function($ano, $meses) use ($cdempresa, $cdcentrocusto, $tpmovimento) {
    $p = [$cdempresa];
    if ($cdcentrocusto) $p[] = $cdcentrocusto;
    if ($tpmovimento)   $p[] = $tpmovimento;
    $p[] = $ano;
    $p = array_merge($p, $meses);
    return $p;
  };
  return array_merge($bloco($anoA, $mesesA), $bloco($anoB, $mesesB));
}
