unit uClientErpOrderImporter;

interface

uses
  System.SysUtils, System.Classes, System.JSON, System.Math, System.IOUtils,
  System.Generics.Collections, Data.DB, FireDAC.Comp.Client, FireDAC.Stan.Param;

type
  TClientErpOrderImporter = class
  private
    class procedure AsegurarEsquemaCodigosCliente;
    class function ResolveClient(AOwner: TComponent; const AClientName, AClientNif, AClientEan: string;
      out AClienteId, ADestinoId: Integer; out AClienteNombre: string): Boolean;
    class function FindArticleForClientCode(AClienteId: Integer; const AClientCode, AEan: string;
      out AArtId: string; out AArtDesc: string): Boolean;
    class procedure SaveClientArticleMapping(const AArtId: string; AClienteId: Integer;
      const AClientCode, AClientDesc: string);
    class procedure SaveOriginalDocument(ANewPedidoId, AEmpresaId, AClienteId: Integer;
      const ANumPedido, ADocPath: string);
  public
    class function ImportFromDocument(AOwner: TComponent; const ADocPath, AOrderJson: string;
      AEmpresaId: Integer; out ANewPedidoId: Integer; var AErrorMsg: string): Boolean;
  end;

implementation

uses
  Vcl.Forms, Vcl.Dialogs, Vcl.Controls, System.UITypes,
  dmg_Main, frm_SelectCliente, frm_SelectProduct;

type
  TClientOrderLineRec = record
    ClientCode: string;
    Ean: string;
    Description: string;
    Qty: Double;
    Price: Double;
    Total: Double;
  end;

{ TClientErpOrderImporter }

class procedure TClientErpOrderImporter.AsegurarEsquemaCodigosCliente;
var
  LQry: TFDQuery;
begin
  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;

    LQry.SQL.Text :=
      'SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.COLUMNS ' +
      'WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ''ge_articulos_codigos_web'' AND COLUMN_NAME = ''id_cliente''';
    LQry.Open;
    if LQry.FieldByName('cnt').AsInteger = 0 then
    begin
      LQry.Close;
      LQry.SQL.Text :=
        'ALTER TABLE ge_articulos_codigos_web ' +
        'ADD COLUMN id_cliente INT(11) DEFAULT NULL AFTER id_articulo';
      LQry.ExecSQL;
    end;
    LQry.Close;

    LQry.SQL.Text :=
      'SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.STATISTICS ' +
      'WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ''ge_articulos_codigos_web'' AND INDEX_NAME = ''uk_articulo_codigo_web''';
    LQry.Open;
    if LQry.FieldByName('cnt').AsInteger > 0 then
    begin
      LQry.Close;
      LQry.SQL.Text := 'ALTER TABLE ge_articulos_codigos_web DROP INDEX uk_articulo_codigo_web';
      LQry.ExecSQL;
    end;
    LQry.Close;

    LQry.SQL.Text :=
      'SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.STATISTICS ' +
      'WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ''ge_articulos_codigos_web'' AND INDEX_NAME = ''uk_articulo_codigo_cliente''';
    LQry.Open;
    if LQry.FieldByName('cnt').AsInteger = 0 then
    begin
      LQry.Close;
      LQry.SQL.Text :=
        'ALTER TABLE ge_articulos_codigos_web ' +
        'ADD UNIQUE KEY uk_articulo_codigo_cliente (id_articulo, codigo_web, id_cliente), ' +
        'ADD KEY idx_cliente_codigo (id_cliente, codigo_web)';
      LQry.ExecSQL;
    end;
  finally
    LQry.Free;
  end;
end;

class function TClientErpOrderImporter.ResolveClient(AOwner: TComponent;
  const AClientName, AClientNif, AClientEan: string;
  out AClienteId, ADestinoId: Integer; out AClienteNombre: string): Boolean;
var
  LQry: TFDQuery;
  LNifClean, LEanClean, LNameClean: string;
  LFrmCli: TfrmSelectCliente;
begin
  Result := False;
  AClienteId := 0;
  ADestinoId := 0;
  AClienteNombre := '';

  LNifClean := UpperCase(StringReplace(StringReplace(Trim(AClientNif), '-', '', [rfReplaceAll]), ' ', '', [rfReplaceAll]));
  LEanClean := Trim(AClientEan);
  LNameClean := Trim(AClientName);

  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;

    // 1. Buscar por NIF/CIF
    if LNifClean <> '' then
    begin
      LQry.SQL.Text :=
        'SELECT id, COALESCE(NULLIF(nombre_comercial, ''''), nombre_fiscal) AS nom ' +
        'FROM ge_clientes ' +
        'WHERE UPPER(REPLACE(REPLACE(COALESCE(nif, ''''), ''-'', ''''), '' '', '''')) = :nif ' +
        '  AND activo = 1 LIMIT 1';
      LQry.ParamByName('nif').AsString := LNifClean;
      LQry.Open;
      if not LQry.IsEmpty then
      begin
        AClienteId := LQry.FieldByName('id').AsInteger;
        AClienteNombre := LQry.FieldByName('nom').AsString;
      end;
      LQry.Close;
    end;

    // 2. Buscar por EAN en destinos de clientes
    if (AClienteId = 0) and (LEanClean <> '') then
    begin
      LQry.SQL.Text :=
        'SELECT cd.id_cliente, cd.id AS dest_id, COALESCE(NULLIF(c.nombre_comercial, ''''), c.nombre_fiscal) AS nom ' +
        'FROM ge_clientes_destinos cd ' +
        'JOIN ge_clientes c ON c.id = cd.id_cliente ' +
        'WHERE (cd.ean_cliente = :ean OR cd.ean_receptor = :ean2) AND cd.activo = 1 LIMIT 1';
      LQry.ParamByName('ean').AsString := LEanClean;
      LQry.ParamByName('ean2').AsString := LEanClean;
      LQry.Open;
      if not LQry.IsEmpty then
      begin
        AClienteId := LQry.FieldByName('id_cliente').AsInteger;
        ADestinoId := LQry.FieldByName('dest_id').AsInteger;
        AClienteNombre := LQry.FieldByName('nom').AsString;
      end;
      LQry.Close;
    end;

    // 3. Buscar por nombre exacto o parcial único
    if (AClienteId = 0) and (LNameClean <> '') then
    begin
      LQry.SQL.Text :=
        'SELECT id, COALESCE(NULLIF(nombre_comercial, ''''), nombre_fiscal) AS nom ' +
        'FROM ge_clientes ' +
        'WHERE activo = 1 AND (LOWER(nombre_fiscal) LIKE LOWER(:n1) OR LOWER(nombre_comercial) LIKE LOWER(:n2)) ' +
        'LIMIT 2';
      LQry.ParamByName('n1').AsWideString := '%' + LNameClean + '%';
      LQry.ParamByName('n2').AsWideString := '%' + LNameClean + '%';
      LQry.Open;
      if LQry.RecordCount = 1 then
      begin
        AClienteId := LQry.FieldByName('id').AsInteger;
        AClienteNombre := LQry.FieldByName('nom').AsString;
      end;
      LQry.Close;
    end;

    // 4. Si no se pudo resolver automáticamente, solicitar selección manual al usuario
    if AClienteId = 0 then
    begin
      MessageDlg(
        'No se pudo identificar automáticamente el cliente del documento (' +
        Trim(AClientName + ' ' + AClientNif) + ').' + sLineBreak +
        'Por favor, seleccione el cliente correspondiente en su ficha.',
        mtInformation, [mbOK], 0);

      LFrmCli := TfrmSelectCliente.Create(AOwner);
      try
        if LFrmCli.ShowModal = mrOk then
        begin
          AClienteId := StrToIntDef(Trim(LFrmCli.SelectedId), 0);
          AClienteNombre := Trim(LFrmCli.SelectedDesc);
        end;
      finally
        LFrmCli.Free;
      end;
    end;

    // Si tiene cliente pero no destino asignado, tomar el único destino activo si existe
    if (AClienteId > 0) and (ADestinoId = 0) then
    begin
      LQry.SQL.Text := 'SELECT id FROM ge_clientes_destinos WHERE id_cliente = :cli AND activo = 1 LIMIT 2';
      LQry.ParamByName('cli').AsInteger := AClienteId;
      LQry.Open;
      if LQry.RecordCount = 1 then
        ADestinoId := LQry.FieldByName('id').AsInteger;
      LQry.Close;
    end;

    Result := AClienteId > 0;
  finally
    LQry.Free;
  end;
end;

class function TClientErpOrderImporter.FindArticleForClientCode(AClienteId: Integer;
  const AClientCode, AEan: string; out AArtId: string; out AArtDesc: string): Boolean;
var
  LQry: TFDQuery;
  LCodeTrim, LEanTrim: string;
begin
  Result := False;
  AArtId := '';
  AArtDesc := '';
  LCodeTrim := Trim(AClientCode);
  LEanTrim := Trim(AEan);

  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;

    // 1. Buscar relación específica cliente + código de cliente en ge_articulos_codigos_web
    if (AClienteId > 0) and (LCodeTrim <> '') then
    begin
      LQry.SQL.Text :=
        'SELECT cw.id_articulo, a.descripcion ' +
        'FROM ge_articulos_codigos_web cw ' +
        'JOIN ge_articulos a ON a.id = cw.id_articulo ' +
        'WHERE cw.id_cliente = :cli AND cw.codigo_web = :cod ' +
        'LIMIT 1';
      LQry.ParamByName('cli').AsInteger := AClienteId;
      LQry.ParamByName('cod').AsWideString := LCodeTrim;
      LQry.Open;
      if not LQry.IsEmpty then
      begin
        AArtId := LQry.FieldByName('id_articulo').AsString;
        AArtDesc := LQry.FieldByName('descripcion').AsString;
        Result := True;
        Exit;
      end;
      LQry.Close;
    end;

    // 2. Fallback por EAN del documento (ge_articulos.ean o ge_articulos_eans)
    if LEanTrim <> '' then
    begin
      LQry.SQL.Text := 'SELECT id, descripcion FROM ge_articulos WHERE ean = :ean AND activo = 1 LIMIT 1';
      LQry.ParamByName('ean').AsString := LEanTrim;
      LQry.Open;
      if not LQry.IsEmpty then
      begin
        AArtId := LQry.FieldByName('id').AsString;
        AArtDesc := LQry.FieldByName('descripcion').AsString;
        Result := True;
      end;
      LQry.Close;

      if not Result then
      begin
        LQry.SQL.Text :=
          'SELECT a.id, a.descripcion ' +
          'FROM ge_articulos_eans ae ' +
          'JOIN ge_articulos a ON a.id = ae.id_articulo ' +
          'WHERE ae.ean = :ean LIMIT 1';
        LQry.ParamByName('ean').AsString := LEanTrim;
        LQry.Open;
        if not LQry.IsEmpty then
        begin
          AArtId := LQry.FieldByName('id').AsString;
          AArtDesc := LQry.FieldByName('descripcion').AsString;
          Result := True;
        end;
        LQry.Close;
      end;

      // Si lo localizamos por EAN y el cliente traía código propio, guardar la relación automáticamente
      if Result and (AClienteId > 0) and (LCodeTrim <> '') then
        SaveClientArticleMapping(AArtId, AClienteId, LCodeTrim, AArtDesc);
    end;
  finally
    LQry.Free;
  end;
end;

class procedure TClientErpOrderImporter.SaveClientArticleMapping(const AArtId: string;
  AClienteId: Integer; const AClientCode, AClientDesc: string);
var
  LQry: TFDQuery;
begin
  if (Trim(AArtId) = '') or (AClienteId <= 0) or (Trim(AClientCode) = '') then
    Exit;

  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;
    LQry.SQL.Text :=
      'INSERT INTO ge_articulos_codigos_web (id_articulo, id_cliente, codigo_web, descripcion_web) ' +
      'VALUES (:art, :cli, :cod, :desc) ' +
      'ON DUPLICATE KEY UPDATE descripcion_web = VALUES(descripcion_web)';
    LQry.ParamByName('art').AsString := Trim(AArtId);
    LQry.ParamByName('cli').AsInteger := AClienteId;
    LQry.ParamByName('cod').AsWideString := Copy(Trim(AClientCode), 1, 50);
    LQry.ParamByName('desc').AsWideString := Copy(Trim(AClientDesc), 1, 255);
    LQry.ExecSQL;
  finally
    LQry.Free;
  end;
end;

class procedure TClientErpOrderImporter.SaveOriginalDocument(ANewPedidoId, AEmpresaId, AClienteId: Integer;
  const ANumPedido, ADocPath: string);
var
  LQry: TFDQuery;
  LBytes: TBytes;
  LNombreArch, LExt, LDesc: string;
  LStream: TMemoryStream;
begin
  if (ANewPedidoId <= 0) or (not FileExists(ADocPath)) then
    Exit;

  LBytes := TFile.ReadAllBytes(ADocPath);
  LNombreArch := Copy(ExtractFileName(ADocPath), 1, 100);
  LExt := LowerCase(StringReplace(ExtractFileExt(ADocPath), '.', '', [rfReplaceAll]));
  LExt := Copy(LExt, 1, 10);
  LDesc := Copy('Pedido ERP ' + Trim(ANumPedido), 1, 50);

  LQry := TFDQuery.Create(nil);
  LStream := TMemoryStream.Create;
  try
    if Length(LBytes) > 0 then
      LStream.WriteBuffer(LBytes[0], Length(LBytes));
    LStream.Position := 0;

    LQry.Connection := dmgMain.dbConn;
    LQry.SQL.Text :=
      'INSERT INTO ge_documentos (' +
      '  id_empresa, fecha, descripcion, archivo, nombre_archivo, extension, ' +
      '  cod_partner, id_pedido_cliente, publicar, id_user_creator, id_user_update' +
      ') VALUES (' +
      '  :emp, :fecha, :desc, :arch, :nom, :ext, ' +
      '  :partner, :ped, 1, :usr, :usr' +
      ')';
    LQry.ParamByName('emp').AsInteger := AEmpresaId;
    LQry.ParamByName('fecha').AsDate := Date;
    LQry.ParamByName('desc').AsWideString := LDesc;
    LQry.ParamByName('arch').LoadFromStream(LStream, ftBlob);
    LQry.ParamByName('nom').AsWideString := LNombreArch;
    LQry.ParamByName('ext').AsString := LExt;
    LQry.ParamByName('partner').AsInteger := AClienteId;
    LQry.ParamByName('ped').AsInteger := ANewPedidoId;
    LQry.ParamByName('usr').AsInteger := StrToIntDef(dmgMain.CurrentUserId, 1);
    LQry.ExecSQL;
  finally
    LStream.Free;
    LQry.Free;
  end;
end;

class function TClientErpOrderImporter.ImportFromDocument(AOwner: TComponent;
  const ADocPath, AOrderJson: string; AEmpresaId: Integer;
  out ANewPedidoId: Integer; var AErrorMsg: string): Boolean;
var
  LJSONObj, LLineObj: TJSONObject;
  LVal: TJSONValue;
  LLinesArr: TJSONArray;
  LNumPedido, LFechaStr, LFechaEntStr: string;
  LCliName, LCliNif, LCliEan, LCliNombreFinal: string;
  LClienteId, LDestinoId: Integer;
  LFechaPed, LFechaEnt: TDateTime;
  FS: TFormatSettings;
  LLines: TList<TClientOrderLineRec>;
  LRec: TClientOrderLineRec;
  I: Integer;
  LQry, LCompQry: TFDQuery;
  LBase, LIva, LTotal: Double;
  LHasOrigen: Boolean;
begin
  Result := False;
  ANewPedidoId := 0;
  AErrorMsg := '';

  LJSONObj := TJSONObject.ParseJSONValue(AOrderJson) as TJSONObject;
  if not Assigned(LJSONObj) then
  begin
    AErrorMsg := 'El contenido extraído del documento no es un JSON válido.';
    Exit;
  end;

  LLines := TList<TClientOrderLineRec>.Create;
  try
    try
      LVal := LJSONObj.GetValue('order_number');
      if Assigned(LVal) then LNumPedido := Trim(LVal.Value) else LNumPedido := '';
      if LNumPedido = '' then
        LNumPedido := 'ERP-' + FormatDateTime('yyyymmddhhnnss', Now);

      LVal := LJSONObj.GetValue('date');
      if Assigned(LVal) then LFechaStr := Trim(LVal.Value) else LFechaStr := '';

      LVal := LJSONObj.GetValue('delivery_date');
      if Assigned(LVal) then LFechaEntStr := Trim(LVal.Value) else LFechaEntStr := '';

      LVal := LJSONObj.GetValue('client_name');
      if Assigned(LVal) then LCliName := Trim(LVal.Value) else LCliName := '';

      LVal := LJSONObj.GetValue('client_nif');
      if Assigned(LVal) then LCliNif := Trim(LVal.Value) else LCliNif := '';

      LVal := LJSONObj.GetValue('client_ean');
      if Assigned(LVal) then LCliEan := Trim(LVal.Value) else LCliEan := '';

      FS := TFormatSettings.Create('en-US');
      FS.DateSeparator := '-';
      FS.ShortDateFormat := 'yyyy-mm-dd';

      if not TryStrToDate(LFechaStr, LFechaPed, FS) then
        LFechaPed := Date;
      if not TryStrToDate(LFechaEntStr, LFechaEnt, FS) then
        LFechaEnt := LFechaPed + 1;

      LVal := LJSONObj.GetValue('lines');
      if (LVal = nil) or not (LVal is TJSONArray) then
      begin
        AErrorMsg := 'El documento no contiene líneas de pedido válidas.';
        Exit;
      end;

      LLinesArr := LVal as TJSONArray;
      for I := 0 to LLinesArr.Count - 1 do
      begin
        LLineObj := LLinesArr.Items[I] as TJSONObject;
        LRec.ClientCode := '';
        LRec.Ean := '';
        LRec.Description := '';
        LRec.Qty := 0;
        LRec.Price := 0;

        LVal := LLineObj.GetValue('client_code');
        if Assigned(LVal) then LRec.ClientCode := Trim(LVal.Value);
        LVal := LLineObj.GetValue('ean');
        if Assigned(LVal) then LRec.Ean := Trim(LVal.Value);
        LVal := LLineObj.GetValue('description');
        if Assigned(LVal) then LRec.Description := Trim(LVal.Value);
        LVal := LLineObj.GetValue('quantity');
        if Assigned(LVal) then
          LRec.Qty := StrToFloatDef(StringReplace(LVal.Value, ',', '.', [rfReplaceAll]), 0.0, FS);
        LVal := LLineObj.GetValue('price');
        if Assigned(LVal) then
          LRec.Price := StrToFloatDef(StringReplace(LVal.Value, ',', '.', [rfReplaceAll]), 0.0, FS);

        if LRec.ClientCode = '' then
          LRec.ClientCode := LRec.Ean;

        LRec.Total := RoundTo(LRec.Qty * LRec.Price, -2);
        if (LRec.Qty > 0) and ((LRec.ClientCode <> '') or (LRec.Description <> '')) then
          LLines.Add(LRec);
      end;

      if LLines.Count = 0 then
      begin
        AErrorMsg := 'No se encontraron líneas con cantidad mayor que cero en el documento.';
        Exit;
      end;

      // Asegurar columnas e índices antes de abrir transacción
      AsegurarEsquemaCodigosCliente;

      // Resolver cliente
      if not ResolveClient(AOwner, LCliName, LCliNif, LCliEan, LClienteId, LDestinoId, LCliNombreFinal) then
      begin
        AErrorMsg := 'Importación cancelada: no se seleccionó ningún cliente.';
        Exit;
      end;

      LQry := TFDQuery.Create(nil);
      try
        LQry.Connection := dmgMain.dbConn;

        // Evitar duplicar el mismo pedido del cliente
        LQry.SQL.Text :=
          'SELECT id FROM ge_pedidos WHERE cliente_id = :cli AND numero_pedido = :num LIMIT 1';
        LQry.ParamByName('cli').AsInteger := LClienteId;
        LQry.ParamByName('num').AsWideString := LNumPedido;
        LQry.Open;
        if not LQry.IsEmpty then
        begin
          AErrorMsg := Format('El pedido "%s" del cliente "%s" ya existe en el sistema.', [LNumPedido, LCliNombreFinal]);
          Exit;
        end;
        LQry.Close;

        // Verificar si existe columna origen en ge_pedidos
        LQry.SQL.Text :=
          'SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS ' +
          'WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ''ge_pedidos'' AND COLUMN_NAME = ''origen''';
        LQry.Open;
        LHasOrigen := LQry.Fields[0].AsInteger > 0;
        LQry.Close;

        LBase := 0;
        for LRec in LLines do
          LBase := LBase + LRec.Total;
        LIva := RoundTo(LBase * 0.10, -2);
        LTotal := RoundTo(LBase + LIva, -2);

        dmgMain.dbConn.StartTransaction;
        try
          if LHasOrigen then
            LQry.SQL.Text :=
              'INSERT INTO ge_pedidos (empresa_id, cliente_id, clientedestino_id, numero_pedido, ' +
              '  fecha_pedido, fecha_entrega, estado, base_imponible, total, iva, observaciones, origen, id_user_creator, id_user_update) ' +
              'VALUES (:emp, :cli, :dest, :num, :fped, :fent, ''PENDIENTE'', :base, :tot, :iva, :obs, ''ERP_CLIENTE'', :usr, :usr)'
          else
            LQry.SQL.Text :=
              'INSERT INTO ge_pedidos (empresa_id, cliente_id, clientedestino_id, numero_pedido, ' +
              '  fecha_pedido, fecha_entrega, estado, base_imponible, total, iva, observaciones, id_user_creator, id_user_update) ' +
              'VALUES (:emp, :cli, :dest, :num, :fped, :fent, ''PENDIENTE'', :base, :tot, :iva, :obs, :usr, :usr)';

          LQry.ParamByName('emp').AsInteger := AEmpresaId;
          LQry.ParamByName('cli').AsInteger := LClienteId;
          LQry.ParamByName('dest').DataType := ftInteger;
          if LDestinoId > 0 then
            LQry.ParamByName('dest').AsInteger := LDestinoId
          else
            LQry.ParamByName('dest').Clear;
          LQry.ParamByName('num').AsWideString := LNumPedido;
          LQry.ParamByName('fped').AsDate := LFechaPed;
          LQry.ParamByName('fent').AsDate := LFechaEnt;
          LQry.ParamByName('base').AsFloat := LBase;
          LQry.ParamByName('iva').AsFloat := LIva;
          LQry.ParamByName('tot').AsFloat := LTotal;
          LQry.ParamByName('obs').AsWideString := 'Importado desde documento ERP cliente (' + ExtractFileName(ADocPath) + ')';
          LQry.ParamByName('usr').AsString := dmgMain.CurrentUserId;
          LQry.ExecSQL;

          LQry.SQL.Text := 'SELECT LAST_INSERT_ID()';
          LQry.Open;
          ANewPedidoId := LQry.Fields[0].AsInteger;
          LQry.Close;

          var LRealBase: Double := 0;
          var LRealIva: Double := 0;

          for LRec in LLines do
          begin
            var LArtId := '';
            var LArtDesc := '';

            if not FindArticleForClientCode(LClienteId, LRec.ClientCode, LRec.Ean, LArtId, LArtDesc) then
            begin
              MessageDlg(
                Format('El producto del cliente "%s" (Cód. Cliente: %s) no está asociado a ninguna referencia propia.' + sLineBreak +
                       'Seleccione nuestro artículo correspondiente para guardarlo para futuros pedidos.',
                       [LRec.Description, LRec.ClientCode]),
                mtInformation, [mbOK], 0);

              var LFrmProd := TfrmSelectProduct.Create(AOwner);
              try
                LFrmProd.MultiSelect := False;
                if LFrmProd.ShowModal = mrOk then
                begin
                  LArtId := Trim(LFrmProd.SelectedId);
                  LArtDesc := Trim(LFrmProd.SelectedDesc);
                end;
              finally
                LFrmProd.Free;
              end;

              if LArtId = '' then
                raise Exception.CreateFmt('No se seleccionó ningún artículo para "%s" (%s). Importación cancelada.',
                  [LRec.Description, LRec.ClientCode]);

              var LCodGuardar := LRec.ClientCode;
              if LCodGuardar = '' then
                LCodGuardar := Copy(LRec.Description, 1, 50);
              SaveClientArticleMapping(LArtId, LClienteId, LCodGuardar, LRec.Description);
            end;

            // Obtener precio tarifa (pvp1), IVA y EAN de nuestro artículo, y aplicar tarifa de distribuidor / precio especial del cliente
            var LPvpTarifa: Double := 0;
            var LIvaRate: Double := 10.0;
            var LArtEan: string := '';
            LQry.SQL.Text :=
              'SELECT COALESCE(a.pvp1, 0) AS pvp1, COALESCE(ti.tipo, 10) AS iva_porc, COALESCE(a.ean, '''') AS ean ' +
              'FROM ge_articulos a ' +
              'LEFT JOIN ge_tipos_iva ti ON ti.id = a.id_tipo_iva ' +
              'WHERE a.id = :id LIMIT 1';
            LQry.ParamByName('id').AsWideString := LArtId;
            LQry.Open;
            if not LQry.IsEmpty then
            begin
              LPvpTarifa := LQry.FieldByName('pvp1').AsFloat;
              LIvaRate := LQry.FieldByName('iva_porc').AsFloat;
              LArtEan := Trim(LQry.FieldByName('ean').AsString);
            end;
            LQry.Close;
            if LArtEan = '' then
              LArtEan := Trim(LRec.Ean);
            LArtEan := Copy(LArtEan, 1, 20);

            var LDefaultPrice := LPvpTarifa;
            if LDefaultPrice <= 0 then
              LDefaultPrice := LRec.Price;

            var LUnitPrice := dmgMain.GetPrecioArticuloCliente(
              IntToStr(LClienteId), LArtId, LFechaPed, LDefaultPrice, LRec.Qty);
            var LLineTotal := RoundTo(LRec.Qty * LUnitPrice, -2);
            LRealBase := LRealBase + LLineTotal;
            LRealIva := LRealIva + RoundTo(LLineTotal * (LIvaRate / 100.0), -2);

            // Comprobar si es artículo compuesto (lote)
            LCompQry := TFDQuery.Create(nil);
            try
              LCompQry.Connection := dmgMain.dbConn;
              LCompQry.SQL.Text :=
                'SELECT ac.id_articulo_componente, ac.cantidad, a.descripcion, COALESCE(a.pvp1, 0) AS pvp1, COALESCE(a.ean, '''') AS ean ' +
                'FROM ge_articulos_compuestos ac ' +
                'JOIN ge_articulos a ON a.id = ac.id_articulo_componente ' +
                'WHERE ac.id_articulo_padre = :padre ' +
                'ORDER BY ac.orden, ac.id';
              LCompQry.ParamByName('padre').AsWideString := LArtId;
              try
                LCompQry.Open;
              except
                LCompQry.Close;
              end;

              if (not LCompQry.Active) or LCompQry.IsEmpty then
              begin
                LQry.SQL.Text :=
                  'INSERT INTO ge_pedidos_lineas (id_pedido, id_articulo, descripcion, cantidad, precio, total, ean_producto) ' +
                  'VALUES (:ped, :art, :desc, :qty, :precio, :tot, :ean)';
                LQry.ParamByName('ped').AsInteger := ANewPedidoId;
                LQry.ParamByName('art').AsWideString := LArtId;
                if LRec.Description <> '' then
                  LQry.ParamByName('desc').AsWideString := Copy(LRec.Description, 1, 255)
                else
                  LQry.ParamByName('desc').AsWideString := Copy(LArtDesc, 1, 255);
                LQry.ParamByName('qty').AsFloat := LRec.Qty;
                LQry.ParamByName('precio').AsFloat := LUnitPrice;
                LQry.ParamByName('tot').AsFloat := LLineTotal;
                if LArtEan <> '' then
                  LQry.ParamByName('ean').AsString := LArtEan
                else
                begin
                  LQry.ParamByName('ean').DataType := ftString;
                  LQry.ParamByName('ean').Clear;
                end;
                LQry.ExecSQL;
              end
              else
              begin
                var LCompCount := LCompQry.RecordCount;
                var LTotalValorRef: Double := 0;
                LCompQry.First;
                while not LCompQry.Eof do
                begin
                  LTotalValorRef := LTotalValorRef + (Max(0.0, LCompQry.FieldByName('pvp1').AsFloat) * Max(0.0, LCompQry.FieldByName('cantidad').AsFloat));
                  LCompQry.Next;
                end;

                var LAcumTotal: Double := 0;
                var LIdx: Integer := 0;
                LCompQry.First;
                while not LCompQry.Eof do
                begin
                  var LCompId := LCompQry.FieldByName('id_articulo_componente').AsString;
                  var LCompNombre := Trim(LCompQry.FieldByName('descripcion').AsString);
                  var LCompFactor := LCompQry.FieldByName('cantidad').AsFloat;
                  var LCompEan := Copy(Trim(LCompQry.FieldByName('ean').AsString), 1, 20);
                  var LCompQty := LRec.Qty * LCompFactor;
                  var LCompDesc := Copy(Format('%s [%s]', [LCompNombre, LRec.Description]), 1, 255);
                  var LCompTotal: Double;
                  if LIdx = LCompCount - 1 then
                    LCompTotal := RoundTo(LLineTotal - LAcumTotal, -2)
                  else
                  begin
                    var LWeight: Double;
                    if LTotalValorRef > 0 then
                      LWeight := (Max(0.0, LCompQry.FieldByName('pvp1').AsFloat) * Max(0.0, LCompFactor)) / LTotalValorRef
                    else
                      LWeight := 1.0 / LCompCount;
                    LCompTotal := RoundTo(LLineTotal * LWeight, -2);
                    LAcumTotal := LAcumTotal + LCompTotal;
                  end;
                  var LCompPrice: Double := 0;
                  if LCompQty > 0 then
                    LCompPrice := RoundTo(LCompTotal / LCompQty, -4);

                  LQry.SQL.Text :=
                    'INSERT INTO ge_pedidos_lineas (id_pedido, id_articulo, descripcion, cantidad, precio, total, ean_producto) ' +
                    'VALUES (:ped, :art, :desc, :qty, :precio, :tot, :ean)';
                  LQry.ParamByName('ped').AsInteger := ANewPedidoId;
                  LQry.ParamByName('art').AsWideString := LCompId;
                  LQry.ParamByName('desc').AsWideString := LCompDesc;
                  LQry.ParamByName('qty').AsFloat := LCompQty;
                  LQry.ParamByName('precio').AsFloat := LCompPrice;
                  LQry.ParamByName('tot').AsFloat := LCompTotal;
                  if LCompEan <> '' then
                    LQry.ParamByName('ean').AsString := LCompEan
                  else
                  begin
                    LQry.ParamByName('ean').DataType := ftString;
                    LQry.ParamByName('ean').Clear;
                  end;
                  LQry.ExecSQL;

                  Inc(LIdx);
                  LCompQry.Next;
                end;
              end;
            finally
              LCompQry.Free;
            end;
          end;

          // Actualizar totales reales de cabecera con los precios aplicados (tarifa distribuidor / especial / pvp1)
          LQry.SQL.Text :=
            'UPDATE ge_pedidos SET base_imponible = :base, iva = :iva, total = :tot WHERE id = :ped';
          LQry.ParamByName('base').AsFloat := RoundTo(LRealBase, -2);
          LQry.ParamByName('iva').AsFloat := RoundTo(LRealIva, -2);
          LQry.ParamByName('tot').AsFloat := RoundTo(LRealBase + LRealIva, -2);
          LQry.ParamByName('ped').AsInteger := ANewPedidoId;
          LQry.ExecSQL;

          // Guardar el documento original en ge_documentos vinculado al pedido
          SaveOriginalDocument(ANewPedidoId, AEmpresaId, LClienteId, LNumPedido, ADocPath);

          dmgMain.dbConn.Commit;
          Result := True;
        except
          on E: Exception do
          begin
            dmgMain.dbConn.Rollback;
            AErrorMsg := E.Message;
          end;
        end;
      finally
        LQry.Free;
      end;
    except
      on E: Exception do
        AErrorMsg := E.Message;
    end;
  finally
    LLines.Free;
    LJSONObj.Free;
  end;
end;

end.

