// Pantalla: Planillas — Parser Excel con SheetJS + cálculo patronal + API real

const ISSS_PAT = 0.075;
const AFP_PAT  = 0.0875;

const calcularPatronal = (base) => {
  const isss = Math.round(base * ISSS_PAT * 100) / 100;
  const afp  = Math.round(base * AFP_PAT  * 100) / 100;
  return { isss, afp, costo: Math.round((base + isss + afp) * 100) / 100 };
};

// ─── Parser Excel por encabezados (SheetJS) ───────────────────────────────────
const parsearExcelPlanilla = (workbook) => {
  const sheet = workbook.Sheets[workbook.SheetNames[0]];
  const rows  = XLSX.utils.sheet_to_json(sheet, { header: 1, defval: "" });

  // Buscar fila de encabezados
  let headerRow = -1;
  let colMap    = {};
  const claves  = ["nombre", "dias", "salario", "cargo", "jornal", "extras", "valor_hora"];

  for (let i = 0; i < Math.min(rows.length, 20); i++) {
    const row = rows[i].map(c => String(c).toUpperCase().normalize("NFD").replace(/[\u0300-\u036f]/g,"").trim());
    const found = {};
    row.forEach((cell, j) => {
      if (cell.includes("NOMBRE") || cell.includes("EMPLEADO")) found.nombre = j;
      if (cell.includes("DIAS") || cell === "D") found.dias = j;
      if (cell.includes("SALARIO") || cell.includes("JORNAL") || cell.includes("DIARIO")) found.jornal = j;
      if (cell.includes("CARGO") || cell.includes("PUESTO")) found.cargo = j;
      if (cell.includes("HORA EXTRA") || cell.includes("HORAS EXTRA")) found.extras = j;
      if (cell.includes("VALOR HORA") || cell.includes("V.HORA")) found.valor_hora = j;
    });
    if (found.nombre !== undefined && found.dias !== undefined) {
      headerRow = i;
      colMap = found;
      break;
    }
  }

  if (headerRow === -1) throw new Error("No se encontraron columnas NOMBRE y DIAS en el archivo. Verifica el formato.");

  const empleados = [];
  for (let i = headerRow + 1; i < rows.length; i++) {
    const row = rows[i];
    const nombre = String(row[colMap.nombre] || "").trim();
    if (!nombre || nombre.toUpperCase() === "TOTAL" || nombre.toUpperCase() === "SUBTOTAL") continue;

    const dias   = parseFloat(row[colMap.dias] || 0);
    const jornal = parseFloat(row[colMap.jornal] || 0);
    const extras = parseFloat(row[colMap.extras] || 0);
    const vhora  = parseFloat(row[colMap.valor_hora] || 0);
    const cargo  = String(row[colMap.cargo] || "").trim();

    if (dias <= 0 && jornal <= 0) continue;

    const base = jornal * dias + extras * vhora;
    const { isss, afp, costo } = calcularPatronal(base);

    empleados.push({ nombre, cargo, dias, jornal_diario: jornal, horas_extras: extras, valor_hora_extra: vhora, salario_base: base, isss_patronal: isss, afp_patronal: afp, costo_real: costo });
  }

  if (!empleados.length) throw new Error("No se encontraron empleados con datos válidos en el archivo.");
  return empleados;
};

// ─── Modal subir planilla ─────────────────────────────────────────────────────
const ModalPlanilla = ({proyectos, onClose, onSaved}) => {
  const [form, setForm]           = useState({proyecto_id:"", quincena:"1ra", periodo_mes:new Date().getMonth()+1, periodo_anio:new Date().getFullYear(), tipo:"normal"});
  const [empleados, setEmpleados] = useState([]);
  const [parsing, setParsing]     = useState(false);
  const [parseErr, setParseErr]   = useState("");
  const [saving, setSaving]       = useState(false);
  const [saveErr, setSaveErr]     = useState("");
  const [dupModal, setDupModal]   = useState(false);
  const fileRef = useRef(null);
  const set = k => e => setForm(f=>({...f,[k]:e.target.value}));

  const handleFile = async (e) => {
    const file = e.target.files[0];
    if (!file) return;
    if (!window.XLSX) { setParseErr("SheetJS no disponible. Recarga la página."); return; }
    setParsing(true); setParseErr(""); setEmpleados([]);
    try {
      const buf = await file.arrayBuffer();
      const wb  = XLSX.read(buf, {type:"array"});
      const emp = parsearExcelPlanilla(wb);
      setEmpleados(emp);
    } catch(e) { setParseErr(e.message); }
    finally { setParsing(false); }
  };

  const handleEnviar = async (reemplazar=false) => {
    if (!form.proyecto_id) { setSaveErr("Selecciona un proyecto."); return; }
    if (!empleados.length) { setSaveErr("Carga primero el archivo Excel."); return; }
    setSaving(true); setSaveErr("");
    try {
      const payload = { proyecto_id:parseInt(form.proyecto_id), quincena:form.quincena, periodo_mes:parseInt(form.periodo_mes), periodo_anio:parseInt(form.periodo_anio), empleados };
      let res;
      if (reemplazar) res = await window.API.planillas.reemplazar(payload);
      else res = await window.API.planillas.crear(payload);
      onSaved(res);
      onClose();
    } catch(e) {
      if (e.message.includes("¿Reemplazar?")) { setDupModal(true); }
      else setSaveErr(e.message);
    }
    finally { setSaving(false); }
  };

  const totales = useMemo(()=>({
    empleados: empleados.length,
    bruto:     empleados.reduce((a,e)=>a+e.salario_base,0),
    isss:      empleados.reduce((a,e)=>a+e.isss_patronal,0),
    afp:       empleados.reduce((a,e)=>a+e.afp_patronal,0),
    costo:     empleados.reduce((a,e)=>a+e.costo_real,0),
  }), [empleados]);

  const meses = ["Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre"];

  return (
    <Modal title="Cargar planilla Excel" sub="Detecta columnas automáticamente — NOMBRE y DIAS son requeridos" onClose={onClose} width={640}>
      {saveErr && <ErrorBanner msg={saveErr}/>}
      <div style={{display:"flex",flexDirection:"column",gap:14}}>
        <div style={{display:"grid",gridTemplateColumns:"1fr 1fr 1fr 1fr",gap:12}}>
          <div className="form-row">
            <label className="form-label">Proyecto *</label>
            <select className="form-input" value={form.proyecto_id} onChange={set("proyecto_id")}>
              <option value="">— Seleccionar —</option>
              {proyectos.map(p=><option key={p.id} value={p.id}>{p.nombre}</option>)}
            </select>
          </div>
          <div className="form-row">
            <label className="form-label">Quincena</label>
            <select className="form-input" value={form.quincena} onChange={set("quincena")}>
              <option value="1ra">1ra (1–15)</option>
              <option value="2da">2da (16–30)</option>
            </select>
          </div>
          <div className="form-row">
            <label className="form-label">Mes</label>
            <select className="form-input" value={form.periodo_mes} onChange={set("periodo_mes")}>
              {meses.map((m,i)=><option key={i+1} value={i+1}>{m}</option>)}
            </select>
          </div>
          <div className="form-row">
            <label className="form-label">Año</label>
            <input className="form-input" type="number" value={form.periodo_anio} onChange={set("periodo_anio")}/>
          </div>
        </div>

        {/* Zona de carga */}
        <div style={{border:"2px dashed var(--border-2)",borderRadius:10,padding:"28px 20px",textAlign:"center",cursor:"pointer",background:"var(--bg-3)",transition:"border-color 0.2s"}}
          onClick={()=>fileRef.current?.click()}
          onMouseEnter={e=>e.currentTarget.style.borderColor="var(--yellow)"}
          onMouseLeave={e=>e.currentTarget.style.borderColor="var(--border-2)"}>
          <input ref={fileRef} type="file" accept=".xlsx,.xls" style={{display:"none"}} onChange={handleFile}/>
          <Icon name="upload" size={22} style={{color:"var(--text-3)"}}/>
          <div style={{marginTop:10,fontWeight:600,fontSize:13}}>Arrastra o haz clic para seleccionar</div>
          <div style={{fontSize:12,color:"var(--text-3)",marginTop:4}}>Archivos .xlsx o .xls · El parser detecta las columnas automáticamente</div>
          {parsing && <div style={{marginTop:12,display:"flex",justifyContent:"center"}}><Spinner size={18}/></div>}
        </div>

        {parseErr && <ErrorBanner msg={parseErr}/>}

        {/* Vista previa */}
        {empleados.length > 0 && (
          <div>
            {/* Totales */}
            <div style={{display:"grid",gridTemplateColumns:"repeat(5,1fr)",gap:10,marginBottom:14}}>
              {[
                {l:"Empleados",      v:totales.empleados,            c:"var(--text)"},
                {l:"Salario bruto",  v:fmt(totales.bruto),           c:"var(--text)"},
                {l:"ISSS patronal",  v:fmt(totales.isss),            c:"var(--blue)"},
                {l:"AFP patronal",   v:fmt(totales.afp),             c:"var(--purple)"},
                {l:"Costo real",     v:fmt(totales.costo),           c:"var(--yellow)"},
              ].map((k,i)=>(
                <div key={i} style={{padding:"12px 14px",background:"var(--bg-3)",border:"1px solid var(--border)",borderRadius:8}}>
                  <div style={{fontSize:11,color:"var(--text-3)",marginBottom:4}}>{k.l}</div>
                  <div className="mono" style={{fontSize:14,fontWeight:700,color:k.c}}>{k.v}</div>
                </div>
              ))}
            </div>

            {/* Tabla empleados */}
            <div style={{maxHeight:220,overflowY:"auto",border:"1px solid var(--border)",borderRadius:8}}>
              <table className="table" style={{margin:0}}>
                <thead style={{position:"sticky",top:0,background:"var(--bg-3)",zIndex:1}}>
                  <tr>
                    <th>Empleado</th><th>Cargo</th><th style={{textAlign:"center"}}>Días</th>
                    <th style={{textAlign:"right"}}>Jornal</th><th style={{textAlign:"right"}}>Bruto</th>
                    <th style={{textAlign:"right"}}>ISSS P.</th><th style={{textAlign:"right"}}>AFP P.</th>
                    <th style={{textAlign:"right"}}>Costo Real</th>
                  </tr>
                </thead>
                <tbody>
                  {empleados.map((e,i)=>(
                    <tr key={i}>
                      <td style={{fontSize:12,fontWeight:600}}>{e.nombre}</td>
                      <td style={{fontSize:11,color:"var(--text-3)"}}>{e.cargo||"—"}</td>
                      <td className="num">{e.dias}</td>
                      <td className="num">${e.jornal_diario.toFixed(2)}</td>
                      <td className="num strong">{fmt(e.salario_base)}</td>
                      <td className="num" style={{color:"var(--blue)"}}>{fmt(e.isss_patronal)}</td>
                      <td className="num" style={{color:"var(--purple)"}}>{fmt(e.afp_patronal)}</td>
                      <td className="num strong" style={{color:"var(--yellow)"}}>{fmt(e.costo_real)}</td>
                    </tr>
                  ))}
                </tbody>
              </table>
            </div>
          </div>
        )}
      </div>

      <div style={{display:"flex",justifyContent:"flex-end",gap:10,marginTop:20}}>
        <button className="btn-secondary" onClick={onClose}>Cancelar</button>
        <button className="btn-primary" style={{width:"auto",padding:"10px 18px"}} onClick={()=>handleEnviar(false)} disabled={saving||!empleados.length}>
          {saving ? <Spinner size={14} color="#0A0A0A"/> : <><Icon name="upload" size={14}/> Procesar planilla</>}
        </button>
      </div>

      {/* Modal duplicado */}
      {dupModal && (
        <div className="modal-overlay" onClick={()=>setDupModal(false)}>
          <div className="modal" style={{width:420}} onClick={e=>e.stopPropagation()}>
            <div className="modal-head">
              <div className="modal-title" style={{color:"var(--orange)"}}>⚠️ Planilla duplicada</div>
            </div>
            <div className="modal-body">
              <p style={{fontSize:13,color:"var(--text-2)",marginBottom:16}}>
                Ya existe una planilla para la <strong>{form.quincena} quincena</strong> del período seleccionado en este proyecto.<br/><br/>
                ¿Deseas <strong style={{color:"var(--orange)"}}>reemplazar</strong> la planilla existente con los nuevos datos?
              </p>
              <div style={{display:"flex",justifyContent:"flex-end",gap:10}}>
                <button className="btn-secondary" onClick={()=>setDupModal(false)}>Cancelar</button>
                <button style={{padding:"10px 16px",background:"var(--orange)",color:"#0A0A0A",border:"none",borderRadius:8,fontWeight:600,cursor:"pointer",fontSize:13}}
                  onClick={()=>{ setDupModal(false); handleEnviar(true); }}>
                  <Icon name="refresh" size={14}/> Reemplazar planilla
                </button>
              </div>
            </div>
          </div>
        </div>
      )}
    </Modal>
  );
};

const Planillas = ({user}) => {
  const [proyectos, setProyectos] = useState([]);
  const [planillas, setPlanillas] = useState([]);
  const [loading, setLoading]     = useState(true);
  const [error, setError]         = useState("");
  const [projSel, setProjSel]     = useState("");
  const [showModal, setShowModal] = useState(false);
  const [detalle, setDetalle]     = useState(null);
  const [loadingDetalle, setLoadingDetalle] = useState(false);

  const load = async () => {
    setLoading(true); setError("");
    try {
      const p = await window.API.proyectos.list();
      setProyectos(p);
      if (p.length && !projSel) setProjSel(String(p[0].id));
    } catch(e) { setError(e.message); }
    finally { setLoading(false); }
  };

  useEffect(()=>{ load(); }, []);

  useEffect(()=>{
    if (!projSel) return;
    setPlanillas([]); setDetalle(null);
    window.API.planillas.list(projSel).then(setPlanillas).catch(e=>setError(e.message));
  }, [projSel]);

  const verDetalle = async (p) => {
    setLoadingDetalle(true); setDetalle(null);
    try {
      const d = await window.API.planillas.detalle(projSel, p.periodo_mes, p.periodo_anio, p.quincena);
      setDetalle({...p, empleados: d});
    } catch(e) { alert(e.message); }
    finally { setLoadingDetalle(false); }
  };

  const exportar = (p) => window.API.planillas.exportCsv(projSel, p.periodo_mes, p.periodo_anio, p.quincena);
  const exportarIsss = () => window.API.planillas.exportIsss(projSel);

  const meses = ["","Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre"];

  return (
    <div className="content">
      <div className="page-title-row">
        <div>
          <div className="page-title">Planillas</div>
          <div className="page-sub">Cálculo automático de carga patronal · ISSS 7.5% + AFP 8.75% = 16.25%</div>
        </div>
        <div className="page-actions">
          <button className="btn-secondary" onClick={exportarIsss}><Icon name="download" size={14}/> ISSS/AFP</button>
          <button className="btn-secondary" onClick={load}><Icon name="refresh" size={14}/></button>
          <button className="btn-primary" style={{width:"auto",padding:"10px 14px"}} onClick={()=>setShowModal(true)}>
            <Icon name="upload" size={14}/> Cargar planilla Excel
          </button>
        </div>
      </div>

      {/* Selector de proyecto */}
      {proyectos.length > 0 && (
        <div style={{display:"flex",alignItems:"center",gap:12,marginBottom:18}}>
          <span style={{fontSize:13,color:"var(--text-3)"}}>Proyecto:</span>
          <select className="filter-chip" value={projSel} onChange={e=>setProjSel(e.target.value)} style={{appearance:"none",minWidth:200}}>
            {proyectos.map(p=><option key={p.id} value={p.id}>{p.nombre}</option>)}
          </select>
        </div>
      )}

      {error && <ErrorBanner msg={error} onRetry={load}/>}

      {loading ? (
        <div style={{display:"flex",justifyContent:"center",padding:60}}><Spinner size={28}/></div>
      ) : planillas.length === 0 ? (
        <EmptyState icon="users" title="Sin planillas"
          sub="Carga la primera planilla Excel para este proyecto."
          action={<button className="btn-primary" style={{width:"auto",padding:"10px 18px"}} onClick={()=>setShowModal(true)}><Icon name="upload" size={14}/> Cargar planilla</button>}
        />
      ) : (
        <div style={{display:"flex",flexDirection:"column",gap:14}}>
          {/* Detalle si está seleccionado */}
          {detalle && (
            <div className="card">
              <div className="card-head">
                <div>
                  <div className="card-title">{detalle.quincena} quincena — {meses[detalle.periodo_mes]} {detalle.periodo_anio}</div>
                  <div className="card-sub">{detalle.empleados?.length} empleados · Desglose completo</div>
                </div>
                <div style={{display:"flex",gap:8}}>
                  <button className="btn-secondary" onClick={()=>exportar(detalle)}><Icon name="download" size={13}/> CSV</button>
                  <button className="btn-ghost" onClick={()=>setDetalle(null)}><Icon name="x" size={14}/></button>
                </div>
              </div>
              <div style={{overflowX:"auto"}}>
                <table className="table">
                  <thead>
                    <tr>
                      <th>Empleado</th><th>Cargo</th><th style={{textAlign:"center"}}>Días</th>
                      <th style={{textAlign:"right"}}>Jornal</th><th style={{textAlign:"right"}}>Bruto</th>
                      <th style={{textAlign:"right",color:"var(--blue)"}}>ISSS P.</th>
                      <th style={{textAlign:"right",color:"var(--purple)"}}>AFP P.</th>
                      <th style={{textAlign:"right",color:"var(--yellow)"}}>Costo Real</th>
                    </tr>
                  </thead>
                  <tbody>
                    {detalle.empleados?.map((e,i)=>(
                      <tr key={i}>
                        <td style={{fontWeight:600,fontSize:12}}>{e.nombre_empleado}</td>
                        <td style={{fontSize:11,color:"var(--text-3)"}}>{e.cargo||"—"}</td>
                        <td className="num">{e.dias_trabajados}</td>
                        <td className="num">${parseFloat(e.jornal_diario||0).toFixed(2)}</td>
                        <td className="num strong">{fmt(e.salario_base)}</td>
                        <td className="num" style={{color:"var(--blue)"}}>{fmt(e.isss_patronal)}</td>
                        <td className="num" style={{color:"var(--purple)"}}>{fmt(e.afp_patronal)}</td>
                        <td className="num strong" style={{color:"var(--yellow)"}}>{fmt(e.costo_real)}</td>
                      </tr>
                    ))}
                  </tbody>
                  <tfoot>
                    <tr style={{borderTop:"2px solid var(--border-2)"}}>
                      <td colSpan={4} style={{fontWeight:700,fontSize:13}}>TOTALES</td>
                      <td className="num strong">{fmt(detalle.empleados?.reduce((a,e)=>a+parseFloat(e.salario_base||0),0))}</td>
                      <td className="num" style={{color:"var(--blue)",fontWeight:700}}>{fmt(detalle.empleados?.reduce((a,e)=>a+parseFloat(e.isss_patronal||0),0))}</td>
                      <td className="num" style={{color:"var(--purple)",fontWeight:700}}>{fmt(detalle.empleados?.reduce((a,e)=>a+parseFloat(e.afp_patronal||0),0))}</td>
                      <td className="num" style={{color:"var(--yellow)",fontWeight:700}}>{fmt(detalle.empleados?.reduce((a,e)=>a+parseFloat(e.costo_real||0),0))}</td>
                    </tr>
                  </tfoot>
                </table>
              </div>
            </div>
          )}

          {/* Lista de planillas */}
          <div className="card">
            <div className="card-head"><div className="card-title">Planillas procesadas</div></div>
            <table className="table">
              <thead>
                <tr>
                  <th>Período</th><th>Quincena</th><th style={{textAlign:"center"}}>Empleados</th>
                  <th style={{textAlign:"right"}}>Bruto</th>
                  <th style={{textAlign:"right",color:"var(--blue)"}}>ISSS P.</th>
                  <th style={{textAlign:"right",color:"var(--purple)"}}>AFP P.</th>
                  <th style={{textAlign:"right",color:"var(--yellow)"}}>Costo Real</th>
                  <th style={{textAlign:"center"}}>Acciones</th>
                </tr>
              </thead>
              <tbody>
                {planillas.map((p,i)=>(
                  <tr key={i}>
                    <td className="mono" style={{fontSize:12}}>{meses[p.periodo_mes]} {p.periodo_anio}</td>
                    <td>{p.quincena === "1ra" ? <Pill color="blue">1ra (1–15)</Pill> : <Pill color="purple">2da (16–30)</Pill>}</td>
                    <td className="num">{p.empleados}</td>
                    <td className="num strong">{fmt(p.bruto)}</td>
                    <td className="num" style={{color:"var(--blue)"}}>{fmt(p.isss_p)}</td>
                    <td className="num" style={{color:"var(--purple)"}}>{fmt(p.afp_p)}</td>
                    <td className="num" style={{color:"var(--yellow)",fontWeight:700}}>{fmt(p.costo_real)}</td>
                    <td style={{textAlign:"center"}}>
                      <div style={{display:"flex",gap:6,justifyContent:"center"}}>
                        <button className="btn-ghost" style={{fontSize:11}} onClick={()=>verDetalle(p)}>
                          {loadingDetalle?<Spinner size={11}/>:<><Icon name="eye" size={12}/> Ver</>}
                        </button>
                        <button className="btn-ghost" style={{fontSize:11}} onClick={()=>exportar(p)}>
                          <Icon name="download" size={12}/> CSV
                        </button>
                      </div>
                    </td>
                  </tr>
                ))}
              </tbody>
            </table>
          </div>
        </div>
      )}

      {showModal && <ModalPlanilla proyectos={proyectos} onClose={()=>setShowModal(false)} onSaved={()=>{ setShowModal(false); if(projSel) window.API.planillas.list(projSel).then(setPlanillas); }}/>}
    </div>
  );
};

window.Planillas = Planillas;