import json, re

HTML = r"D:\Evolução categorias\.worktrees\comissao\dashboard_estoque.html"

with open(HTML, 'r', encoding='utf-8') as f:
    html = f.read()

# Carregar dados de margem
with open(r"D:\Evolução categorias\.worktrees\comissao\margem_data.json", 'r', encoding='utf-8') as f:
    md = json.load(f)

changes = []

# ── 1. Injetar margemData no RAW (antes do fechamento };)
RAW_CLOSE = '};\n\n// ── FILTER STATE'
md_json = json.dumps({
    "dist_faixas": md["dist_faixas"],
    "margem_media_geral": md["margem_media_geral"],
    "receita_total": md["receita_total"],
    "lucro_total": md["lucro_total"],
    "piores_margem": md["piores_margem"],
    "melhores_margem": md["melhores_margem"],
    "forn_margem": md["forn_margem"],
    "grupo_margem": md["grupo_margem"],
    "todos_produtos": md["todos_produtos"],
}, ensure_ascii=False)

NEW_RAW_CLOSE = f',\n  margemData: {md_json}\n}};\n\n// ── FILTER STATE'
if RAW_CLOSE in html:
    html = html.replace(RAW_CLOSE, NEW_RAW_CLOSE, 1)
    changes.append("RAW margemData injetado OK")
else:
    changes.append("ERRO: RAW_CLOSE nao encontrado")

# ── 2. Adicionar botão de aba ANTES do alto giro
OLD_BTN = '<button class="tab" onclick="showTab(\'tab-altogiro\',this)">⚡ Alto Giro</button>'
NEW_BTN = '''<button class="tab" onclick="showTab('tab-margem',this)">📊 Margem de Lucro</button>
  ''' + OLD_BTN
if OLD_BTN in html:
    html = html.replace(OLD_BTN, NEW_BTN, 1)
    changes.append("Botao aba inserido OK")
else:
    changes.append("ERRO: botao alto-giro nao encontrado")

# ── 3. Inserir aba HTML antes de </div><!-- /wrap -->
ABA_ANCHOR = '\n\n</div><!-- /wrap -->'
ABA_HTML = '''
<div id="tab-margem" class="tab-pane" style="display:none">
  <div style="padding:20px">

    <!-- KPIs -->
    <div style="display:grid;grid-template-columns:repeat(auto-fit,minmax(180px,1fr));gap:14px;margin-bottom:24px" id="margem-kpis"></div>

    <!-- Gráficos -->
    <div style="display:grid;grid-template-columns:1fr 1fr;gap:18px;margin-bottom:24px">
      <div class="card" style="padding:18px">
        <div style="font-weight:700;font-size:13px;margin-bottom:12px;color:#1e293b">Distribuição por Faixa de Margem</div>
        <div style="height:220px"><canvas id="chartFaixaMargem"></canvas></div>
      </div>
      <div class="card" style="padding:18px">
        <div style="font-weight:700;font-size:13px;margin-bottom:12px;color:#1e293b">Margem por Fornecedor (piores / melhores)</div>
        <div style="height:220px"><canvas id="chartFornMargem"></canvas></div>
      </div>
    </div>

    <!-- Filtros -->
    <div class="card" style="padding:16px;margin-bottom:18px">
      <div style="display:flex;flex-wrap:wrap;gap:12px;align-items:flex-end">
        <div>
          <label style="font-size:11px;color:#64748b;display:block;margin-bottom:4px;text-transform:uppercase;letter-spacing:.5px">Buscar</label>
          <input id="mBusca" type="text" placeholder="Produto ou fornecedor..." oninput="renderTabelaMargem()"
            style="border:1px solid #e2e8f0;border-radius:7px;padding:7px 12px;font-size:13px;width:250px">
        </div>
        <div>
          <label style="font-size:11px;color:#64748b;display:block;margin-bottom:4px;text-transform:uppercase;letter-spacing:.5px">Faixa de margem</label>
          <select id="mFaixa" onchange="renderTabelaMargem()"
            style="border:1px solid #e2e8f0;border-radius:7px;padding:7px 12px;font-size:13px">
            <option value="">Todas</option>
            <option value="Negativa">Negativa (&lt;0%)</option>
            <option value="0–10%">0–10%</option>
            <option value="10–20%">10–20%</option>
            <option value="20–30%">20–30%</option>
            <option value="30–40%">30–40%</option>
            <option value="40–50%">40–50%</option>
            <option value=">50%">&gt;50%</option>
          </select>
        </div>
        <div>
          <label style="font-size:11px;color:#64748b;display:block;margin-bottom:4px;text-transform:uppercase;letter-spacing:.5px">Ordenar por</label>
          <select id="mOrdem" onchange="renderTabelaMargem()"
            style="border:1px solid #e2e8f0;border-radius:7px;padding:7px 12px;font-size:13px">
            <option value="margem_asc">Margem ↑ (piores primeiro)</option>
            <option value="margem_desc">Margem ↓ (melhores primeiro)</option>
            <option value="receita_desc">Receita ↓</option>
            <option value="lucro_desc">Lucro ↓</option>
          </select>
        </div>
        <div style="margin-left:auto;font-size:13px;color:#64748b;padding-bottom:2px" id="mContador"></div>
      </div>
    </div>

    <!-- Tabela -->
    <div class="card" style="overflow:hidden">
      <div style="overflow-x:auto">
        <table style="width:100%;border-collapse:collapse;font-size:13px">
          <thead>
            <tr style="background:#f8fafc;border-bottom:2px solid #e2e8f0">
              <th style="padding:10px 14px;text-align:left;color:#64748b;font-weight:600;white-space:nowrap">Produto</th>
              <th style="padding:10px 14px;text-align:left;color:#64748b;font-weight:600;white-space:nowrap">Fornecedor</th>
              <th style="padding:10px 14px;text-align:left;color:#64748b;font-weight:600;white-space:nowrap">Grupo</th>
              <th style="padding:10px 14px;text-align:right;color:#64748b;font-weight:600;white-space:nowrap">Custo</th>
              <th style="padding:10px 14px;text-align:right;color:#64748b;font-weight:600;white-space:nowrap">P.Venda</th>
              <th style="padding:10px 14px;text-align:right;color:#64748b;font-weight:600;white-space:nowrap">Receita</th>
              <th style="padding:10px 14px;text-align:right;color:#64748b;font-weight:600;white-space:nowrap">Lucro</th>
              <th style="padding:10px 14px;text-align:center;color:#64748b;font-weight:600;white-space:nowrap">Margem</th>
              <th style="padding:10px 14px;text-align:right;color:#64748b;font-weight:600;white-space:nowrap">Estoque</th>
            </tr>
          </thead>
          <tbody id="tabelaMargem"></tbody>
        </table>
      </div>
      <div id="mPaginacao" style="display:flex;justify-content:center;align-items:center;gap:8px;padding:14px;border-top:1px solid #f1f5f9"></div>
    </div>
  </div>
</div>
'''

if ABA_ANCHOR in html:
    html = html.replace(ABA_ANCHOR, ABA_HTML + ABA_ANCHOR, 1)
    changes.append("Aba HTML inserida OK")
else:
    changes.append("ERRO: ABA_ANCHOR nao encontrado")

# ── 4. Remover JS duplicado inserido anteriormente (se existir)
OLD_JS_STRAY = '\n// ════════════════════════════════════════════\n//  ABA MARGEM DE LUCRO\n// ════════════════════════════════════════════\n'
if OLD_JS_STRAY in html:
    # Remover o bloco JS até o próximo marcador de comentário
    idx = html.index(OLD_JS_STRAY)
    end_marker = '\n</script>\n</body>'
    idx_end = html.index(end_marker, idx)
    html = html[:idx] + html[idx_end:]
    changes.append("JS duplicado removido OK")

# ── 5. Injetar JS correto antes de </script></body>
JS_MARGEM = r"""
// ════════════════════════════════════════════
//  ABA MARGEM DE LUCRO
// ════════════════════════════════════════════
let _mPag = 0;
const _mPorPag = 50;
let _mFiltrado = [];

function faixaMargem(m) {
  if (m < 0)   return 'Negativa';
  if (m < 10)  return '0\u201310%';
  if (m < 20)  return '10\u201320%';
  if (m < 30)  return '20\u201330%';
  if (m < 40)  return '30\u201340%';
  if (m < 50)  return '40\u201350%';
  return '>50%';
}

function corMargem(m) {
  if (m < 0)   return { bg:'#fef2f2', text:'#dc2626', bdr:'#fca5a5' };
  if (m < 10)  return { bg:'#fff7ed', text:'#ea580c', bdr:'#fdba74' };
  if (m < 20)  return { bg:'#fefce8', text:'#ca8a04', bdr:'#fde047' };
  if (m < 30)  return { bg:'#f7fee7', text:'#65a30d', bdr:'#bef264' };
  if (m < 40)  return { bg:'#f0fdf4', text:'#16a34a', bdr:'#86efac' };
  if (m < 50)  return { bg:'#dcfce7', text:'#15803d', bdr:'#4ade80' };
  return { bg:'#d1fae5', text:'#065f46', bdr:'#34d399' };
}

function initMargem() {
  const md = RAW.margemData;

  // KPIs
  const lucroTotal = md.lucro_total;
  const recTotal   = md.receita_total;
  const margemGeral = recTotal > 0 ? (lucroTotal/recTotal*100).toFixed(1) : md.margem_media_geral.toFixed(1);
  const kpis = [
    { label:'Margem Geral (ponderada)', value: margemGeral+'%', cor:'#6366f1' },
    { label:'Receita Total', value:'R$ '+fmt(recTotal), cor:'#0ea5e9' },
    { label:'Lucro Total', value:'R$ '+fmt(lucroTotal), cor:'#10b981' },
    { label:'Produtos c/ Margem Negativa', value: (md.dist_faixas['Negativa']||0).toLocaleString('pt-BR'), cor:'#ef4444' },
    { label:'Produtos c/ Margem > 50%', value: (md.dist_faixas['>50%']||0).toLocaleString('pt-BR'), cor:'#22c55e' },
    { label:'Total Analisados', value: md.todos_produtos.length.toLocaleString('pt-BR'), cor:'#8b5cf6' },
  ];
  document.getElementById('margem-kpis').innerHTML = kpis.map(k => `
    <div class="card" style="padding:16px 18px;border-left:4px solid ${k.cor}">
      <div style="font-size:10px;color:#64748b;text-transform:uppercase;letter-spacing:.6px;margin-bottom:6px">${k.label}</div>
      <div style="font-size:22px;font-weight:800;color:${k.cor};font-family:var(--display,sans-serif)">${k.value}</div>
    </div>`).join('');

  // Gráfico faixas
  const faixasOrder = ['Negativa','0\u201310%','10\u201320%','20\u201330%','30\u201340%','40\u201350%','>50%'];
  const faixasCores = ['#ef4444','#f97316','#eab308','#84cc16','#22c55e','#10b981','#059669'];
  new Chart(document.getElementById('chartFaixaMargem').getContext('2d'), {
    type: 'bar',
    data: {
      labels: faixasOrder,
      datasets:[{ label:'Qtd', data: faixasOrder.map(f => md.dist_faixas[f]||0),
        backgroundColor: faixasCores, borderRadius: 6 }]
    },
    options: {
      plugins: { legend: { display:false },
        tooltip: { callbacks: { label: ctx => ` ${ctx.raw.toLocaleString('pt-BR')} produtos` }}},
      scales: { y: { beginAtZero:true, ticks: { stepSize: 500 }}},
      responsive: true, maintainAspectRatio: false
    }
  });

  // Gráfico fornecedores (piores 12 + melhores 5)
  const forn = md.forn_margem;
  const bottom = forn.slice(0,12);
  const top    = forn.slice(-5);
  const fornSel = [...bottom, ...top];
  new Chart(document.getElementById('chartFornMargem').getContext('2d'), {
    type: 'bar',
    data: {
      labels: fornSel.map(f => f.nome.length>24 ? f.nome.substring(0,24)+'\u2026' : f.nome),
      datasets:[{ label:'Margem %', data: fornSel.map(f => parseFloat(f.margem.toFixed(1))),
        backgroundColor: fornSel.map(f => f.margem<0?'#ef4444':f.margem<20?'#f97316':f.margem<35?'#eab308':'#22c55e'),
        borderRadius: 4 }]
    },
    options: {
      indexAxis: 'y',
      plugins: { legend: { display:false },
        tooltip: { callbacks: { label: ctx => ` ${ctx.raw.toFixed(1)}% | R$ ${fmt(fornSel[ctx.dataIndex].receita)}` }}},
      scales: { x: { beginAtZero:true, ticks: { callback: v => v+'%' }}},
      responsive: true, maintainAspectRatio: false
    }
  });

  renderTabelaMargem();
}

function renderTabelaMargem() {
  const busca = (document.getElementById('mBusca')||{value:''}).value.toLowerCase();
  const faixa = (document.getElementById('mFaixa')||{value:''}).value;
  const ordem = (document.getElementById('mOrdem')||{value:'margem_asc'}).value;

  let prods = RAW.margemData.todos_produtos.filter(p => {
    if (busca && !p.nome.toLowerCase().includes(busca) && !p.fornecedor.toLowerCase().includes(busca)) return false;
    if (faixa && faixaMargem(p.margem) !== faixa) return false;
    return true;
  });

  if (ordem === 'margem_asc')   prods.sort((a,b) => a.margem - b.margem);
  if (ordem === 'margem_desc')  prods.sort((a,b) => b.margem - a.margem);
  if (ordem === 'receita_desc') prods.sort((a,b) => b.receita - a.receita);
  if (ordem === 'lucro_desc')   prods.sort((a,b) => b.lucro - a.lucro);

  _mFiltrado = prods;
  _mPag = 0;
  const cnt = document.getElementById('mContador');
  if (cnt) cnt.textContent = prods.length.toLocaleString('pt-BR') + ' produtos';
  renderPaginaMargem();
  renderPaginacaoMargem();
}

function renderPaginaMargem() {
  const inicio = _mPag * _mPorPag;
  const fatia  = _mFiltrado.slice(inicio, inicio + _mPorPag);
  const tbody  = document.getElementById('tabelaMargem');
  if (!tbody) return;
  tbody.innerHTML = fatia.map(p => {
    const c = corMargem(p.margem);
    return `<tr style="border-bottom:1px solid #f1f5f9" onmouseover="this.style.background='#f8fafc'" onmouseout="this.style.background=''">
      <td style="padding:9px 14px;color:#1e293b;font-weight:500;max-width:280px;overflow:hidden;text-overflow:ellipsis;white-space:nowrap" title="${p.nome}">${p.nome}</td>
      <td style="padding:9px 14px;color:#475569;font-size:12px">${p.fornecedor}</td>
      <td style="padding:9px 14px;color:#64748b;font-size:12px">${p.grupo}</td>
      <td style="padding:9px 14px;text-align:right;color:#475569;font-family:var(--mono,monospace)">R$ ${fmt(p.custo)}</td>
      <td style="padding:9px 14px;text-align:right;color:#475569;font-family:var(--mono,monospace)">R$ ${fmt(p.venda)}</td>
      <td style="padding:9px 14px;text-align:right;color:#0ea5e9;font-weight:600;font-family:var(--mono,monospace)">R$ ${fmt(p.receita)}</td>
      <td style="padding:9px 14px;text-align:right;color:${p.lucro>=0?'#10b981':'#ef4444'};font-weight:600;font-family:var(--mono,monospace)">R$ ${fmt(p.lucro)}</td>
      <td style="padding:9px 14px;text-align:center">
        <span style="background:${c.bg};color:${c.text};border:1px solid ${c.bdr};padding:3px 10px;border-radius:20px;font-weight:700;font-size:12px;white-space:nowrap">
          ${p.margem.toFixed(1)}%
        </span>
      </td>
      <td style="padding:9px 14px;text-align:right;color:#64748b;font-family:var(--mono,monospace)">${p.estoque.toLocaleString('pt-BR',{minimumFractionDigits:0,maximumFractionDigits:2})}</td>
    </tr>`;
  }).join('');
}

function renderPaginacaoMargem() {
  const total = Math.ceil(_mFiltrado.length / _mPorPag);
  const div = document.getElementById('mPaginacao');
  if (!div) return;
  if (total <= 1) { div.innerHTML = ''; return; }
  const btns = [];
  if (_mPag > 0) btns.push(`<button onclick="_mPag--;renderPaginaMargem();renderPaginacaoMargem()" style="padding:6px 14px;border:1px solid #e2e8f0;border-radius:6px;background:#fff;cursor:pointer;font-size:13px">← Anterior</button>`);
  btns.push(`<span style="font-size:13px;color:#64748b;padding:0 8px">Pág ${_mPag+1} de ${total} &nbsp;|&nbsp; ${_mFiltrado.length.toLocaleString('pt-BR')} produtos</span>`);
  if (_mPag < total-1) btns.push(`<button onclick="_mPag++;renderPaginaMargem();renderPaginacaoMargem()" style="padding:6px 14px;border:1px solid #e2e8f0;border-radius:6px;background:#fff;cursor:pointer;font-size:13px">Próximo →</button>`);
  div.innerHTML = btns.join('');
}

"""

JS_INSERT = '</script>\n</body>'
if JS_INSERT in html:
    html = html.replace(JS_INSERT, JS_MARGEM + JS_INSERT, 1)
    changes.append("JS margem injetado OK")
else:
    changes.append("ERRO: </script></body> nao encontrado")

# ── 6. Hook no showTab para inicializar a aba
OLD_SHOW = "function showTab(id,el){"
HOOK_LINE = "\n  if(id==='tab-margem' && !window._margemInit){ window._margemInit=true; setTimeout(initMargem,80); }"
if OLD_SHOW in html:
    html = html.replace(OLD_SHOW, OLD_SHOW + HOOK_LINE, 1)
    changes.append("showTab hook OK")
else:
    changes.append("ERRO: showTab nao encontrado")

with open(HTML, 'w', encoding='utf-8') as f:
    f.write(html)

for c in changes:
    print(c)
print(f"\nTamanho final: {len(html)//1024} KB")