<?php
function import_amount(string $value,string $currency): float{
 $v=preg_replace('/[^0-9,.-]/','',trim($value));if($v==='')return 0;$comma=strrpos($v,',');$dot=strrpos($v,'.');
 if($comma!==false&&$dot!==false){if($comma>$dot)$v=str_replace(',','.',str_replace('.','',$v));else $v=str_replace(',','',$v);}
 elseif($comma!==false){$dec=strlen($v)-$comma-1;$v=($currency==='USD'&&$dec<=2)?str_replace(',','.',$v):str_replace(',','',$v);}
 elseif($dot!==false){$dec=strlen($v)-$dot-1;if(!($currency==='USD'&&$dec<=2))$v=str_replace('.','',$v);}
 return (float)$v;
}
function import_date(string $value): string{
 $value=trim($value);foreach(['Y-m-d','d/m/Y','d-m-Y','d.m.Y'] as $format){$d=DateTime::createFromFormat('!'.$format,$value);if($d&&$d->format($format)===$value)return $d->format('Y-m-d');}throw new RuntimeException("Fecha de vencimiento inválida: $value");
}
function import_find_client(string $label,bool $create=true): ?int{
 $label=trim($label);if($label==='')return null;$st=db()->prepare("SELECT id FROM clientes WHERE activo=1 AND (documento=? OR razon_social=? OR TRIM(CONCAT(nombres,' ',COALESCE(apellidos,'')))=?) ORDER BY id LIMIT 1");$st->bind_param('sss',$label,$label,$label);$st->execute();$id=(int)($st->get_result()->fetch_assoc()['id']??0);if($id||!$create)return $id?:null;
 $doc='IMP-'.strtoupper(substr(sha1(mb_strtolower($label,'UTF-8')),0,12));$st=db()->prepare("INSERT INTO clientes(documento,nombres,activo,observaciones) VALUES(?,?,1,'Creado automáticamente mediante Importar Datos') ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id),activo=1");$name=mb_substr($label,0,120,'UTF-8');$st->bind_param('ss',$doc,$name);$st->execute();return (int)($st->insert_id?:db()->query("SELECT id FROM clientes WHERE documento='".db()->real_escape_string($doc)."'")->fetch_assoc()['id']);
}
function import_find_bank(string $name): ?int{
 $name=trim($name);if($name==='')return null;$st=db()->prepare("SELECT id FROM bancos WHERE nombre=? ORDER BY activo DESC,id LIMIT 1");$st->bind_param('s',$name);$st->execute();$id=(int)($st->get_result()->fetch_assoc()['id']??0);if($id){db()->query("UPDATE bancos SET activo=1 WHERE id=$id");return $id;}$st=db()->prepare("INSERT INTO bancos(nombre,activo) VALUES(?,1)");$st->bind_param('s',$name);$st->execute();return (int)$st->insert_id;
}
function import_rate(string $table,float $amount): float{
 $st=db()->prepare("SELECT tasa FROM `$table` WHERE activo=1 AND ? BETWEEN monto_desde AND monto_hasta ORDER BY monto_desde,id LIMIT 1");$st->bind_param('d',$amount);$st->execute();return (float)($st->get_result()->fetch_assoc()['tasa']??0);
}

if($p==='importar_datos'){
 require_permission('importar_datos');$imported=[];$importError='';
 if($_SERVER['REQUEST_METHOD']==='POST')try{check_csrf();if(empty($_FILES['archivo']['tmp_name'])||!is_uploaded_file($_FILES['archivo']['tmp_name']))throw new RuntimeException('Seleccione un archivo TXT.');if((int)$_FILES['archivo']['size']>5*1024*1024)throw new RuntimeException('El archivo supera el máximo de 5 MB.');$raw=file_get_contents($_FILES['archivo']['tmp_name']);if($raw===false)throw new RuntimeException('No se pudo leer el archivo.');$raw=preg_replace('/^\xEF\xBB\xBF/','',$raw);$lines=preg_split('/\R/u',$raw);$records=[];$lineNo=0;foreach($lines as $line){$lineNo++;if(trim($line)==='')continue;$cols=str_getcsv($line,"\t");if($lineNo===1&&mb_strtolower(trim($cols[0]??''),'UTF-8')==='cliente')continue;if(count($cols)<8)throw new RuntimeException("La línea $lineNo debe tener 8 columnas separadas por tabulación.");[$clientLabel,$amountText,$currencyText,$dueText,$bankName,$checkNumber,$account,$signerLabel]=array_map('trim',array_slice($cols,0,8));$currencyNormalized=mb_strtolower($currencyText,'UTF-8');$currency=(str_contains($currencyNormalized,'dol')||str_contains($currencyNormalized,'dól')||str_contains($currencyNormalized,'usd')||str_contains($currencyText,'$'))?'USD':'PYG';$amount=import_amount($amountText,$currency);if($clientLabel===''||$amount<=0)throw new RuntimeException("Cliente o importe inválido en la línea $lineNo.");$records[]=['line'=>$lineNo,'client'=>$clientLabel,'amount'=>$amount,'currency'=>$currency,'due'=>import_date($dueText),'bank'=>$bankName,'check'=>$checkNumber,'account'=>$account,'signer'=>$signerLabel];}if(!$records)throw new RuntimeException('El archivo no contiene registros para importar.');
  $defaults=[];foreach(['seller'=>"SELECT id FROM vendedores WHERE activo=1 ORDER BY id LIMIT 1",'channel'=>"SELECT id FROM canales_venta WHERE activo=1 AND nombre='Venta Directa' ORDER BY id LIMIT 1",'guarantee'=>"SELECT id FROM garantias WHERE activo=1 AND nombre='A Sola Firma' ORDER BY id LIMIT 1",'class'=>"SELECT id FROM clases_prestamo WHERE activo=1 AND nombre='Nuevos' ORDER BY id LIMIT 1",'origin'=>"SELECT id FROM origenes_capital WHERE activo=1 AND nombre='Propio' ORDER BY id LIMIT 1",'doctype'=>"SELECT id FROM tipos_documento WHERE activo=1 AND nombre='Cheque' ORDER BY id LIMIT 1"] as $key=>$sql)$defaults[$key]=(int)(rows($sql)[0]['id']??0);foreach($defaults as $key=>$value)if(!$value)throw new RuntimeException("Falta configurar el valor predeterminado: $key.");$seller=rows("SELECT tasa_comision FROM vendedores WHERE id={$defaults['seller']}")[0]??['tasa_comision'=>0];$gp=general_params();$divisor=loan_divisor();$today=date('Y-m-d');$cn=db();$cn->begin_transaction();try{foreach($records as $record){$client=import_find_client($record['client']);$signer=import_find_client($record['signer']);$bank=import_find_bank($record['bank']);$currencyId=(int)(rows("SELECT id FROM monedas WHERE codigo='".$cn->real_escape_string($record['currency'])."' AND activo=1 LIMIT 1")[0]['id']??0);if(!$currencyId)throw new RuntimeException("Moneda no configurada en la línea {$record['line']}.");$days=max(0,(int)(new DateTime($today))->diff(new DateTime($record['due']))->format('%r%a'));$rate=import_rate('tasas_descuento',$record['amount']);$insuranceRate=import_rate('tasa_seguro',$record['amount']);$adminRate=import_rate('gastos_administrativos',$record['amount']);$interest=round($record['amount']*$days*$rate/$divisor,2);$insurance=round($record['amount']*$days*$insuranceRate/$divisor,2);$commission=round($record['amount']*$days*(float)$seller['tasa_comision']/$divisor,2);$admin=round($record['amount']*$days*$adminRate/$divisor,2);$discount=$interest+$insurance+$commission+$admin;$net=round($record['amount']-$discount,2);$limit=rows("SELECT * FROM limites_operativos WHERE activo=1 AND '$today' BETWEEN fecha_desde AND fecha_hasta ORDER BY fecha_desde DESC LIMIT 1")[0]??null;$operating=0;if(!empty($gp['calcula_gastos_bcp'])&&$limit&&$record['amount']<=(float)$gp['salario_minimo']*(float)$limit['max_salarios_credito'])$operating=min((float)$limit['jornal_minimo']*(float)$limit['max_jornales'],max(0,$net)*(float)$limit['porcentaje_neto']/100);$finalNet=$net-$operating;$obs='Importado desde TXT · Línea '.$record['line'];$uid=(int)user()['id'];$st=$cn->prepare("INSERT INTO operaciones(fecha,cliente_id,vendedor_id,canal_id,garantia_id,codeudor_id,clase_prestamo_id,moneda_id,origen_capital_id,tasa_interes,total_importe,total_descuento,total_seguro,total_comision,total_gastos,total_gastos_administrativos,gastos_operativos,total_neto,observaciones,creado_por) VALUES(?,?,?,?,?,NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");$zero=0.0;$st->bind_param('siiiiiiidddddddddsi',$today,$client,$defaults['seller'],$defaults['channel'],$defaults['guarantee'],$defaults['class'],$currencyId,$defaults['origin'],$rate,$record['amount'],$discount,$insurance,$commission,$zero,$admin,$operating,$finalNet,$obs,$uid);$st->execute();$operationId=$st->insert_id;$dt=$cn->prepare("INSERT INTO operacion_detalles(operacion_id,tipo_documento_id,importe,vencimiento,banco_id,nro_cheque,cuenta_corriente,firmante_id,plazo_dias,tasa_aplicada,interes,seguro,comision,gasto,gasto_administrativo,gasto_operativo,descuento,neto) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");$dt->bind_param('iidsissiiddddddddd',$operationId,$defaults['doctype'],$record['amount'],$record['due'],$bank,$record['check'],$record['account'],$signer,$days,$rate,$interest,$insurance,$commission,$zero,$admin,$operating,$discount,$net);$dt->execute();$detailId=$dt->insert_id;$cn->query("INSERT INTO detalle_saldos(operacion_detalle_id,seguro,gastos_administrativos,gastos_operativos,interes,capital) VALUES($detailId,$insurance,$admin,$operating,$interest,$net)");$imported[]=['id'=>$operationId,'client'=>$record['client'],'amount'=>$record['amount'],'currency'=>$record['currency']];}$cn->commit();foreach($imported as $item)audit('IMPORTAR','operaciones',(int)$item['id'],'Importación TXT');flash('success',count($imported).' operaciones importadas correctamente.');}catch(Throwable $e){$cn->rollback();throw $e;}
 }catch(Throwable $e){$importError=$e->getMessage();}
 page_start('Importar Datos');?>
 <h2>Importar Datos</h2><?php if($importError):?><div class="alert alert-danger"><?=e($importError)?></div><?php endif;?><div class="card mb-4 no-print"><div class="card-body"><form method="post" enctype="multipart/form-data"><input type="hidden" name="csrf" value="<?=csrf()?>"><label class="form-label required">Archivo TXT separado por tabulaciones</label><input type="file" class="form-control" name="archivo" accept=".txt,text/plain" required><div class="form-text">Columnas: Cliente, Importe, Moneda, Fecha de vencimiento, Banco, Número de cheque, Cuenta corriente y Librador.</div><button class="btn btn-primary mt-3"><i class="fa fa-file-import me-1"></i>Importar operaciones</button></form></div></div><div class="card"><div class="card-header">Formato esperado</div><div class="card-body"><code>Cliente&nbsp;&nbsp;&nbsp;Importe&nbsp;&nbsp;&nbsp;Moneda&nbsp;&nbsp;&nbsp;Fecha de vencimiento&nbsp;&nbsp;&nbsp;Banco&nbsp;&nbsp;&nbsp;Número de cheque&nbsp;&nbsp;&nbsp;Cuenta corriente&nbsp;&nbsp;&nbsp;Librador</code><p class="text-muted mt-2 mb-0">Puede incluir la primera línea como encabezado. Los clientes, libradores o bancos inexistentes se crean automáticamente.</p></div><?php if($imported):?><div class="table-responsive"><table class="table mb-0"><thead><tr><th>Operación</th><th>Cliente</th><th>Moneda</th><th>Importe</th><th></th></tr></thead><tbody><?php foreach($imported as $item):?><tr><td>#<?=$item['id']?></td><td><?=e($item['client'])?></td><td><?=e($item['currency'])?></td><td><?=money_currency((float)$item['amount'],$item['currency'])?></td><td><a class="btn btn-sm btn-outline-primary" href="?p=operaciones&view=<?=$item['id']?>">Ver</a></td></tr><?php endforeach;?></tbody></table></div><?php endif;?></div><?php page_end();exit;
}
