unit frm_ImportarFactusol;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.ExtCtrls, Vcl.Buttons,
  Vcl.ComCtrls, Data.DB, Data.Win.ADODB, FireDAC.Comp.Client, uAppTheme,
  FireDAC.Stan.Param, uBaseForm, Vcl.Grids, Vcl.DBGrids;

type
  TfrmImportarFactusol = class(TfrmBase)
    grpOptions: TGroupBox;
    chkClientes: TCheckBox;
    chkArticulos: TCheckBox;
    chkAlbaranes: TCheckBox;
    chkFacturas: TCheckBox;
    grpFile: TGroupBox;
    edtPath: TEdit;
    btnBrowse: TBitBtn;
    pbImport: TProgressBar;
    memLog: TMemo;
    openDialog: TOpenDialog;
    procedure FormShow(Sender: TObject);
    procedure btnBrowseClick(Sender: TObject);
    procedure btnImportClick(Sender: TObject);
    procedure btnSalirClick(Sender: TObject);
  private
    { Private declarations }
    procedure LogMessage(const AMsg: string);
    function GetDefaultIvaId: string;
    function ResolveIvaByFactusolEquiv(const AEquiv: string; out AIvaId: string; out ATipo: Double): Boolean;
    function GetDefaultIva(out AIvaId: string; out ATipo: Double): Boolean;
    function ResolveFamiliaUUID(ACodigoFamilia: string): string;
    function ResolveArticuloUUID(const ACodigoArticulo: string): string;
    function ResolveOrCreateArticulo(const ACodigoArticulo: string; ADefaultDesc: string = ''): string;
    procedure ImportClientes(AConnection: TADOConnection);
    procedure ImportArticulos(AConnection: TADOConnection);
    procedure ImportAlbaranes(AConnection: TADOConnection);
    procedure ImportFacturas(AConnection: TADOConnection);
    function ResolveClienteUUID(AConnection: TADOConnection; ACodigoCliente: Integer): string;
  public
    { Public declarations }
  end;

var
  frmImportarFactusol: TfrmImportarFactusol;

implementation

uses
  dmg_Main, uDbErrorHandler, System.Generics.Collections;

{$R *.dfm}

function NormalizeNIF(const ANif: string): string;
begin
  Result := UpperCase(ANif);
  Result := StringReplace(Result, ' ', '', [rfReplaceAll]);
  Result := StringReplace(Result, '-', '', [rfReplaceAll]);
  Result := Trim(Result);
end;

function CleanFactusolString(const AStr: string): string;
var
  LPos: Integer;
begin
  LPos := Pos(#0, AStr);
  if LPos > 0 then
    Result := Copy(AStr, 1, LPos - 1)
  else
    Result := AStr;
  Result := Trim(Result);
end;

procedure TfrmImportarFactusol.FormShow(Sender: TObject);
begin
  // Aplicar tema estándar
  TAppTheme.ApplyToForm(Self);

  // Vincular eventos a la barra superior heredada
  btnGuardar.OnClick := btnImportClick;
  btnGuardar.Caption := 'Importar';
  btnSalir.OnClick := btnSalirClick;

  // Ocultar componentes/paneles heredados innecesarios para mantenimiento simple
  pgcDetails.Visible := True;
  TsListado.TabVisible := False;
  pgcDetails.ActivePage := tsDatosEnvio;
  pnlSideToolbar.Visible := False;
  dbgItems.Visible := False;
  pnlFooter.Visible := False;

  // Inicializar componentes
  edtPath.Clear;
  pbImport.Position := 0;
  memLog.Lines.Clear;
  memLog.Lines.Add('Esperando selección de archivo de base de datos Factusol (Access)...');
end;

procedure TfrmImportarFactusol.btnBrowseClick(Sender: TObject);
begin
  openDialog.Title := 'Seleccionar base de datos de Factusol';
  openDialog.Filter := 'Bases de Datos Access (*.mdb;*.accdb)|*.mdb;*.accdb|Todos los archivos (*.*)|*.*';
  if openDialog.Execute then
  begin
    edtPath.Text := openDialog.FileName;
    LogMessage('Archivo seleccionado: ' + openDialog.FileName);
  end;
end;

procedure TfrmImportarFactusol.LogMessage(const AMsg: string);
begin
  memLog.Lines.Add('[' + FormatDateTime('hh:nn:ss', Now) + '] ' + AMsg);
  // Hacer auto-scroll al final del memo
  SendMessage(memLog.Handle, EM_LINESCROLL, 0, memLog.Lines.Count);
  Application.ProcessMessages;
end;

procedure TfrmImportarFactusol.btnImportClick(Sender: TObject);
var
  LConnection: TADOConnection;
  LConnStrings: array[0..3] of string;
  LProviders: array[0..3] of string;
  LConnected: Boolean;
  LErrorMsg: string;
  I: Integer;
begin
  if Trim(edtPath.Text) = '' then
  begin
    ShowMessage('Debe seleccionar el archivo de base de datos Factusol (Access).');
    Exit;
  end;

  if not FileExists(edtPath.Text) then
  begin
    ShowMessage('El archivo seleccionado no existe.');
    Exit;
  end;

  if not (chkClientes.Checked or chkArticulos.Checked or chkAlbaranes.Checked or chkFacturas.Checked) then
  begin
    ShowMessage('Debe seleccionar al menos una tabla para importar.');
    Exit;
  end;

  LogMessage('Iniciando proceso de importación...');
  pbImport.Position := 0;

  LConnection := TADOConnection.Create(nil);
  try
    LConnection.LoginPrompt := False;
    LConnected := False;
    LErrorMsg := '';
    
    LConnStrings[0] := 'Provider=Microsoft.ACE.OLEDB.16.0;Data Source=' + edtPath.Text + ';Persist Security Info=False;';
    LProviders[0] := 'ACE OLEDB 16.0 (Office/Access 2016)';
    
    LConnStrings[1] := 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + edtPath.Text + ';Persist Security Info=False;';
    LProviders[1] := 'ACE OLEDB 12.0 (Access 2010/2013)';
    
    LConnStrings[2] := 'Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + edtPath.Text + ';';
    LProviders[2] := 'ODBC Microsoft Access Driver (*.mdb, *.accdb)';
    
    LConnStrings[3] := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + edtPath.Text + ';Persist Security Info=False;';
    LProviders[3] := 'Jet OLEDB 4.0 (Legacy .mdb)';

    for I := 0 to 3 do
    begin
      try
        LogMessage('Intentando conectar con proveedor ' + LProviders[I] + '...');
        LConnection.ConnectionString := LConnStrings[I];
        LConnection.Open;
        LConnected := True;
        LogMessage('Conexión con Access establecida correctamente usando: ' + LProviders[I]);
        Break;
      except
        on E: Exception do
        begin
          LogMessage('Fallo proveedor ' + LProviders[I] + ': ' + E.Message);
          LErrorMsg := E.Message;
        end;
      end;
    end;

    if not LConnected then
    begin
      ShowMessage(
        'No se pudo conectar a la base de datos de Access.' + sLineBreak + sLineBreak +
        'Detalle del error: ' + LErrorMsg + sLineBreak + sLineBreak +
        'Este error ocurre porque el equipo no tiene instalado el motor de base de datos de Microsoft Access en su versión de 32 bits (necesario para esta aplicación).' + sLineBreak + sLineBreak +
        'PASOS PARA INSTALAR EL CONTROLADOR:' + sLineBreak +
        '1. Descargue el instalador de 32 bits desde el sitio oficial de Microsoft:' + sLineBreak +
        '   https://www.microsoft.com/en-us/download/details.aspx?id=54920' + sLineBreak +
        '   (De la lista de archivos, seleccione: "AccessDatabaseEngine.exe")' + sLineBreak + sLineBreak +
        '2. Si tiene Office de 64 bits instalado, la instalación visual fallará.' + sLineBreak +
        '   Para saltar este bloqueo, instálelo desde el Símbolo del Sistema en modo silencioso:' + sLineBreak +
        '   - Busque "cmd" en Windows, haga clic derecho y elija "Ejecutar como administrador".' + sLineBreak +
        '   - Vaya a su carpeta de Descargas (ej: cd C:\Users\<SuUsuario>\Downloads).' + sLineBreak +
        '   - Ejecute el comando: AccessDatabaseEngine.exe /quiet' + sLineBreak + sLineBreak +
        '3. Espere unos 15 segundos y vuelva a intentar la importación.'
      );
      Exit;
    end;

    // Asegurar que existe la tabla de auditoría para evitar fallos de triggers
    try
      dmgMain.qryExec.Close;
      dmgMain.qryExec.SQL.Text :=
        'CREATE TABLE IF NOT EXISTS `ge_auditoria_cambios` (' +
        '  `id` bigint(20) NOT NULL AUTO_INCREMENT,' +
        '  `nombre_tabla` varchar(50) NOT NULL,' +
        '  `operacion` varchar(10) NOT NULL,' +
        '  `registro_id` varchar(36) DEFAULT NULL,' +
        '  `valor_anterior` longtext DEFAULT NULL,' +
        '  `valor_nuevo` longtext DEFAULT NULL,' +
        '  `usuario_id` varchar(36) DEFAULT NULL,' +
        '  `fecha` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,' +
        '  PRIMARY KEY (`id`)' +
        ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;';
      dmgMain.qryExec.ExecSQL;
    except
      // Continuar si falla
    end;

    // Ejecutar importación bajo transacción
    dmgMain.dbConn.StartTransaction;
    try
      if chkClientes.Checked then
        ImportClientes(LConnection);

      if chkArticulos.Checked then
        ImportArticulos(LConnection);

      if chkFacturas.Checked then
        ImportFacturas(LConnection);

      if chkAlbaranes.Checked then
        ImportAlbaranes(LConnection);

      dmgMain.dbConn.Commit;
      LogMessage('PROCESO FINALIZADO CON ÉXITO. Datos guardados en MariaDB.');
      pbImport.Position := 100;
      ShowMessage('La importación se completó correctamente.');
    except
      on E: Exception do
      begin
        dmgMain.dbConn.Rollback;
        LogMessage('Error durante la importación. Transacción cancelada: ' + E.Message);
        ShowMessage('Ocurrió un error en el proceso. Se deshicieron todos los cambios: ' + E.Message);
      end;
    end;

  finally
    LConnection.Close;
    FreeAndNil(LConnection);
  end;
end;

procedure TfrmImportarFactusol.ImportClientes(AConnection: TADOConnection);
var
  LQry: TADOQuery;
  LCount, LImported: Integer;
  LCodigo: Integer;
  LNombre, LNif, LDomicilio, LPoblacion, LCP, LProvincia, LTelefono, LEmail: string;
  LUUID: string;
begin
  LogMessage('Importando Clientes...');
  LQry := TADOQuery.Create(nil);
  try
    LQry.Connection := AConnection;
    LQry.SQL.Text := 'SELECT * FROM F_CLI';
    try
      LQry.Open;
    except
      on E: Exception do
      begin
        LogMessage('La tabla F_CLI no se encontró o está corrupta. Saltando.');
        Exit;
      end;
    end;

    if LQry.IsEmpty then
    begin
      LogMessage('No se encontraron clientes en la tabla de origen.');
      Exit;
    end;

    // Contar total de registros
    LCount := 0;
    while not LQry.Eof do
    begin
      Inc(LCount);
      LQry.Next;
    end;
    LQry.First;

    LogMessage(Format('Se encontraron %d clientes. Procesando...', [LCount]));
    LImported := 0;

    while not LQry.Eof do
    begin
      try
        LCodigo := LQry.FieldByName('CODCLI').AsInteger;
      except
        LCodigo := 0;
      end;

      if LCodigo > 0 then
      begin
        try LNombre := CleanFactusolString(LQry.FieldByName('NOFCLI').AsString); except LNombre := ''; end;
        try LNif := CleanFactusolString(LQry.FieldByName('NIFCLI').AsString); except LNif := ''; end;
        LNif := NormalizeNIF(LNif);
        try LDomicilio := CleanFactusolString(LQry.FieldByName('DOMCLI').AsString); except LDomicilio := ''; end;
        try LPoblacion := CleanFactusolString(LQry.FieldByName('POBCLI').AsString); except LPoblacion := ''; end;
        try LCP := CleanFactusolString(LQry.FieldByName('CPOCLI').AsString); except LCP := ''; end;
        try LProvincia := CleanFactusolString(LQry.FieldByName('PROCLI').AsString); except LProvincia := ''; end;
        try LTelefono := CleanFactusolString(LQry.FieldByName('TELCLI').AsString); except LTelefono := ''; end;
        try LEmail := CleanFactusolString(LQry.FieldByName('EMACLI').AsString); except LEmail := ''; end;

        // Comprobar si ya existe
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 'SELECT id, nif, nombre_fiscal, domicilio, poblacion, codigo_postal, provincia, telefono, email FROM ge_clientes WHERE nif = :nif';
        dmgMain.qryExec.ParamByName('nif').DataType := ftWideString;
        dmgMain.qryExec.ParamByName('nif').Value := LNif;
        dmgMain.qryExec.Open;

        if (LNif = '') or dmgMain.qryExec.IsEmpty then
        begin
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'SELECT id, nif, nombre_fiscal, domicilio, poblacion, codigo_postal, provincia, telefono, email FROM ge_clientes WHERE nombre_fiscal = :nom';
          dmgMain.qryExec.ParamByName('nom').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('nom').Value := LNombre;
          dmgMain.qryExec.Open;
        end;

        if dmgMain.qryExec.IsEmpty then
        begin
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text :=
            'INSERT INTO ge_clientes (nif, nombre_fiscal, domicilio, ' +
            ' poblacion, codigo_postal, provincia, telefono, email, activo, id_user_creator, id_user_update) ' +
            'VALUES (:nif, :nom, :dom, :pob, :cp, :prov, :tel, :email, 1, :user, :user)';
          dmgMain.qryExec.ParamByName('nif').DataType := ftWideString;
          if LNif = '' then
            dmgMain.qryExec.ParamByName('nif').Clear
          else
            dmgMain.qryExec.ParamByName('nif').Value := LNif;
          dmgMain.qryExec.ParamByName('nom').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('nom').Value := LNombre;
          dmgMain.qryExec.ParamByName('dom').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('dom').Value := LDomicilio;
          dmgMain.qryExec.ParamByName('pob').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('pob').Value := LPoblacion;
          dmgMain.qryExec.ParamByName('cp').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('cp').Value := LCP;
          dmgMain.qryExec.ParamByName('prov').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('prov').Value := LProvincia;
          dmgMain.qryExec.ParamByName('tel').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('tel').Value := LTelefono;
          dmgMain.qryExec.ParamByName('email').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('email').Value := LEmail;
          dmgMain.qryExec.ParamByName('user').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('user').Value := dmgMain.CurrentUserId;
          dmgMain.qryExec.ExecSQL;
          Inc(LImported);
        end
        else
        begin
          if (dmgMain.qryExec.FieldByName('nif').AsString <> LNif) or
             (dmgMain.qryExec.FieldByName('nombre_fiscal').AsString <> LNombre) or
             (dmgMain.qryExec.FieldByName('domicilio').AsString <> LDomicilio) or
             (dmgMain.qryExec.FieldByName('poblacion').AsString <> LPoblacion) or
             (dmgMain.qryExec.FieldByName('codigo_postal').AsString <> LCP) or
             (dmgMain.qryExec.FieldByName('provincia').AsString <> LProvincia) or
             (dmgMain.qryExec.FieldByName('telefono').AsString <> LTelefono) or
             (dmgMain.qryExec.FieldByName('email').AsString <> LEmail) then
          begin
            LUUID := dmgMain.qryExec.FieldByName('id').AsString;
            dmgMain.qryExec.Close;
            dmgMain.qryExec.SQL.Text :=
              'UPDATE ge_clientes SET nif = :nif, nombre_fiscal = :nom, domicilio = :dom, ' +
              ' poblacion = :pob, codigo_postal = :cp, provincia = :prov, telefono = :tel, email = :email, ' +
              ' id_user_update = :user, updated_at = CURRENT_TIMESTAMP ' +
              'WHERE id = :id';
            dmgMain.qryExec.ParamByName('id').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('id').Value := LUUID;
            dmgMain.qryExec.ParamByName('nif').DataType := ftWideString;
            if LNif = '' then
              dmgMain.qryExec.ParamByName('nif').Clear
            else
              dmgMain.qryExec.ParamByName('nif').Value := LNif;
            dmgMain.qryExec.ParamByName('nom').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('nom').Value := LNombre;
            dmgMain.qryExec.ParamByName('dom').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('dom').Value := LDomicilio;
            dmgMain.qryExec.ParamByName('pob').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('pob').Value := LPoblacion;
            dmgMain.qryExec.ParamByName('cp').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('cp').Value := LCP;
            dmgMain.qryExec.ParamByName('prov').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('prov').Value := LProvincia;
            dmgMain.qryExec.ParamByName('tel').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('tel').Value := LTelefono;
            dmgMain.qryExec.ParamByName('email').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('email').Value := LEmail;
            dmgMain.qryExec.ParamByName('user').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('user').Value := dmgMain.CurrentUserId;
            dmgMain.qryExec.ExecSQL;
            Inc(LImported);
          end;
        end;
      end;

      pbImport.Position := Round((LImported / LCount) * 33);
      LQry.Next;
    end;

    LogMessage(Format('Clientes importados/actualizados correctamente: %d/%d', [LImported, LCount]));
  finally
    LQry.Close;
    FreeAndNil(LQry);
  end;
end;

function TfrmImportarFactusol.ResolveClienteUUID(AConnection: TADOConnection; ACodigoCliente: Integer): string;
var
  LQry: TADOQuery;
  LNif, LNombre: string;
begin
  Result := '';
  LNif := '';
  LNombre := '';

  LQry := TADOQuery.Create(nil);
  try
    LQry.Connection := AConnection;
    LQry.SQL.Text := 'SELECT NIFCLI, NOFCLI FROM F_CLI WHERE CODCLI = ' + IntToStr(ACodigoCliente);
    LQry.Open;
    if not LQry.IsEmpty then
    begin
      LNif := NormalizeNIF(LQry.FieldByName('NIFCLI').AsString);
      LNombre := Trim(LQry.FieldByName('NOFCLI').AsString);
    end;
  finally
    LQry.Free;
  end;

  dmgMain.qryExec.Close;
  if LNif <> '' then
  begin
    dmgMain.qryExec.SQL.Text := 'SELECT id FROM ge_clientes WHERE nif = :nif';
    dmgMain.qryExec.ParamByName('nif').AsString := LNif;
    dmgMain.qryExec.Open;
    if not dmgMain.qryExec.IsEmpty then
      Result := dmgMain.qryExec.FieldByName('id').AsString;
  end;

  if (Result = '') and (LNombre <> '') then
  begin
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 'SELECT id FROM ge_clientes WHERE nombre_fiscal = :nom';
    dmgMain.qryExec.ParamByName('nom').AsString := LNombre;
    dmgMain.qryExec.Open;
    if not dmgMain.qryExec.IsEmpty then
      Result := dmgMain.qryExec.FieldByName('id').AsString;
  end;
  dmgMain.qryExec.Close;
end;

function TfrmImportarFactusol.GetDefaultIvaId: string;
begin
  Result := '';
  dmgMain.qryExec.Close;
  dmgMain.qryExec.SQL.Text := 'SELECT id FROM ge_tipos_iva WHERE activo = 1 ORDER BY tipo DESC LIMIT 1';
  dmgMain.qryExec.Open;
  if not dmgMain.qryExec.IsEmpty then
    Result := dmgMain.qryExec.FieldByName('id').AsString;
  dmgMain.qryExec.Close;

  if Result = '' then
  begin
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text :=
      'INSERT INTO ge_tipos_iva (activo, descripcion, tipo, id_user_creator, id_user_update) ' +
      'VALUES (1, ''21%'', 21.00, :user, :user)';
    dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
    dmgMain.qryExec.ExecSQL;
    
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
    dmgMain.qryExec.Open;
    Result := dmgMain.qryExec.Fields[0].AsString;
    dmgMain.qryExec.Close;
  end;
end;

function TfrmImportarFactusol.ResolveIvaByFactusolEquiv(const AEquiv: string; out AIvaId: string; out ATipo: Double): Boolean;
begin
  Result := False;
  AIvaId := '';
  ATipo := 0.0;
  if Trim(AEquiv) = '' then Exit;
  
  dmgMain.qryExec.Close;
  dmgMain.qryExec.SQL.Text := 'SELECT id, tipo FROM ge_tipos_iva WHERE equiv_factusol = :equiv AND activo = 1 LIMIT 1';
  dmgMain.qryExec.ParamByName('equiv').AsString := AEquiv;
  dmgMain.qryExec.Open;
  if not dmgMain.qryExec.IsEmpty then
  begin
    AIvaId := dmgMain.qryExec.FieldByName('id').AsString;
    ATipo := dmgMain.qryExec.FieldByName('tipo').AsFloat;
    Result := True;
  end;
  dmgMain.qryExec.Close;
end;

function TfrmImportarFactusol.GetDefaultIva(out AIvaId: string; out ATipo: Double): Boolean;
begin
  Result := False;
  AIvaId := '';
  ATipo := 21.0;
  
  dmgMain.qryExec.Close;
  dmgMain.qryExec.SQL.Text := 'SELECT id, tipo FROM ge_tipos_iva WHERE activo = 1 ORDER BY tipo DESC LIMIT 1';
  dmgMain.qryExec.Open;
  if not dmgMain.qryExec.IsEmpty then
  begin
    AIvaId := dmgMain.qryExec.FieldByName('id').AsString;
    ATipo := dmgMain.qryExec.FieldByName('tipo').AsFloat;
    Result := True;
  end;
  dmgMain.qryExec.Close;
  
  if AIvaId = '' then
  begin
    AIvaId := GetDefaultIvaId;
    ATipo := 21.0;
    Result := True;
  end;
end;

function TfrmImportarFactusol.ResolveFamiliaUUID(ACodigoFamilia: string): string;
begin
  Result := '';
  if Trim(ACodigoFamilia) = '' then Exit;
  
  dmgMain.qryExec.Close;
  dmgMain.qryExec.SQL.Text := 'SELECT id FROM ge_familias WHERE descripcion = :desc';
  dmgMain.qryExec.ParamByName('desc').AsString := 'Familia Factusol ' + ACodigoFamilia;
  dmgMain.qryExec.Open;
  if not dmgMain.qryExec.IsEmpty then
    Result := dmgMain.qryExec.FieldByName('id').AsString
  else
  begin
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text :=
      'INSERT INTO ge_familias (descripcion, activo, id_user_creator, id_user_update) ' +
      'VALUES (:desc, 1, :user, :user)';
    dmgMain.qryExec.ParamByName('desc').AsString := 'Familia Factusol ' + ACodigoFamilia;
    dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
    dmgMain.qryExec.ExecSQL;
    
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
    dmgMain.qryExec.Open;
    Result := dmgMain.qryExec.Fields[0].AsString;
  end;
  dmgMain.qryExec.Close;
end;

function TfrmImportarFactusol.ResolveArticuloUUID(const ACodigoArticulo: string): string;
begin
  Result := '';
end;

function TfrmImportarFactusol.ResolveOrCreateArticulo(const ACodigoArticulo: string; ADefaultDesc: string = ''): string;
begin
  Result := '';
  ADefaultDesc := CleanFactusolString(ADefaultDesc);
  if Trim(ADefaultDesc) = '' then
    ADefaultDesc := 'Artículo Factusol ' + ACodigoArticulo;

  dmgMain.qryExec.Close;
  dmgMain.qryExec.SQL.Text := 'SELECT id FROM ge_articulos WHERE descripcion = :desc';
  dmgMain.qryExec.ParamByName('desc').DataType := ftWideString;
  dmgMain.qryExec.ParamByName('desc').Value := ADefaultDesc;
  dmgMain.qryExec.Open;
  if not dmgMain.qryExec.IsEmpty then
    Result := dmgMain.qryExec.FieldByName('id').AsString;
  dmgMain.qryExec.Close;

  if (Result = '') and (Trim(ADefaultDesc) <> '') then
  begin
    var LIvaId: string := GetDefaultIvaId;
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text :=
      'INSERT INTO ge_articulos (descripcion, id_tipo_iva, activo, ingrediente, id_user_creator, id_user_update) ' +
      'VALUES (:desc, :iva, 1, 1, :user, :user)';
    dmgMain.qryExec.ParamByName('desc').DataType := ftWideString;
    dmgMain.qryExec.ParamByName('desc').Value := ADefaultDesc;
    dmgMain.qryExec.ParamByName('iva').AsString := LIvaId;
    dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
    dmgMain.qryExec.ExecSQL;
    
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
    dmgMain.qryExec.Open;
    Result := dmgMain.qryExec.Fields[0].AsString;
  end;
  dmgMain.qryExec.Close;
end;

procedure TfrmImportarFactusol.ImportArticulos(AConnection: TADOConnection);
var
  LQry: TADOQuery;
  LCount, LImported: Integer;
  LUUID, LCode, LDesc, LDescTpv, LFamiliaCode, LFamiliaUUID, LIvaId: string;
  LPvp1, LPvp2, LPvp3, LPvp4: Double;
  LEquivIva, LArtIvaId: string;
  LArtIvaRate: Double;
begin
  LogMessage('Importando Artículos...');
  LQry := TADOQuery.Create(nil);
  try
    LQry.Connection := AConnection;
    LQry.SQL.Text := 
      'SELECT a.*, t1.PRELTA as PV1_LTA ' +
      'FROM F_ART a ' +
      'LEFT JOIN (SELECT ARTLTA, PRELTA FROM F_LTA WHERE TARLTA = 1) t1 ON a.CODART = t1.ARTLTA';
    try
      LQry.Open;
    except
      on E: Exception do
      begin
        LogMessage('La tabla F_ART no se encontró o está corrupta. Saltando.');
        Exit;
      end;
    end;

    if LQry.IsEmpty then
    begin
      LogMessage('No se encontraron artículos en la tabla de origen.');
      Exit;
    end;

    // Contar total de registros
    LCount := 0;
    while not LQry.Eof do
    begin
      Inc(LCount);
      LQry.Next;
    end;
    LQry.First;

    LogMessage(Format('Se encontraron %d artículos. Procesando...', [LCount]));
    LImported := 0;
    LIvaId := GetDefaultIvaId;

    while not LQry.Eof do
    begin
      try LCode := CleanFactusolString(LQry.FieldByName('CODART').AsString); except LCode := ''; end;

      if LCode <> '' then
      begin
        try LDesc := CleanFactusolString(LQry.FieldByName('DESART').AsWideString); except LDesc := ''; end;
        try LDescTpv := Copy(LDesc, 1, 15); except LDescTpv := ''; end;
        try LFamiliaCode := CleanFactusolString(LQry.FieldByName('FAMART').AsString); except LFamiliaCode := ''; end;
        
        try 
          if not LQry.FieldByName('PV1_LTA').IsNull then
            LPvp1 := LQry.FieldByName('PV1_LTA').AsFloat
          else
            LPvp1 := LQry.FieldByName('PV1ART').AsFloat;
        except 
          LPvp1 := 0.0; 
        end;
        try LPvp2 := LQry.FieldByName('PV2ART').AsFloat; except LPvp2 := 0.0; end;
        try LPvp3 := LQry.FieldByName('PV3ART').AsFloat; except LPvp3 := 0.0; end;
        try LPvp4 := LQry.FieldByName('PV4ART').AsFloat; except LPvp4 := 0.0; end;

        LFamiliaUUID := ResolveFamiliaUUID(LFamiliaCode);

        // Resolver IVA por código equivalente en Factusol (TIVART)
        try LEquivIva := LQry.FieldByName('TIVART').AsString; except LEquivIva := ''; end;
        if not ResolveIvaByFactusolEquiv(LEquivIva, LArtIvaId, LArtIvaRate) then
          LArtIvaId := LIvaId;

        // Comprobar si ya existe
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 
          'SELECT id, descripcion, descripcion_tpv, pvp1, pvp2, pvp3, pvp4, id_tipo_iva, id_familia ' +
          'FROM ge_articulos WHERE descripcion = :desc';
        dmgMain.qryExec.ParamByName('desc').DataType := ftWideString;
        dmgMain.qryExec.ParamByName('desc').Value := LDesc;
        dmgMain.qryExec.Open;

        if dmgMain.qryExec.IsEmpty then
        begin
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text :=
            'INSERT INTO ge_articulos (descripcion, descripcion_tpv, pvp1, pvp2, pvp3, pvp4, ' +
            '  id_tipo_iva, id_familia, activo, ingrediente, id_user_creator, id_user_update) ' +
            'VALUES (:desc, :desctpv, :pvp1, :pvp2, :pvp3, :pvp4, :iva, :fam, 1, 1, :user, :user)';
          dmgMain.qryExec.ParamByName('desc').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('desc').Value := LDesc;
          dmgMain.qryExec.ParamByName('desctpv').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('desctpv').Value := LDescTpv;
          dmgMain.qryExec.ParamByName('pvp1').AsFloat := LPvp1;
          dmgMain.qryExec.ParamByName('pvp2').AsFloat := LPvp2;
          dmgMain.qryExec.ParamByName('pvp3').AsFloat := LPvp3;
          dmgMain.qryExec.ParamByName('pvp4').AsFloat := LPvp4;
          dmgMain.qryExec.ParamByName('iva').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('iva').Value := LArtIvaId;
          dmgMain.qryExec.ParamByName('fam').DataType := ftWideString;
          if LFamiliaUUID <> '' then
            dmgMain.qryExec.ParamByName('fam').Value := LFamiliaUUID
          else
            dmgMain.qryExec.ParamByName('fam').Clear;
          dmgMain.qryExec.ParamByName('user').DataType := ftWideString;
          dmgMain.qryExec.ParamByName('user').Value := dmgMain.CurrentUserId;
          dmgMain.qryExec.ExecSQL;

          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
          dmgMain.qryExec.Open;
          LUUID := dmgMain.qryExec.Fields[0].AsString;
          dmgMain.qryExec.Close;

          Inc(LImported);
        end
        else
        begin
          LUUID := dmgMain.qryExec.FieldByName('id').AsString;
          if (dmgMain.qryExec.FieldByName('descripcion').AsString <> LDesc) or
             (dmgMain.qryExec.FieldByName('descripcion_tpv').AsString <> LDescTpv) or
             (Abs(dmgMain.qryExec.FieldByName('pvp1').AsFloat - LPvp1) > 0.0001) or
             (Abs(dmgMain.qryExec.FieldByName('pvp2').AsFloat - LPvp2) > 0.0001) or
             (Abs(dmgMain.qryExec.FieldByName('pvp3').AsFloat - LPvp3) > 0.0001) or
             (Abs(dmgMain.qryExec.FieldByName('pvp4').AsFloat - LPvp4) > 0.0001) or
             (dmgMain.qryExec.FieldByName('id_tipo_iva').AsString <> LArtIvaId) or
             (dmgMain.qryExec.FieldByName('id_familia').AsString <> LFamiliaUUID) then
          begin
            dmgMain.qryExec.Close;
            dmgMain.qryExec.SQL.Text :=
              'UPDATE ge_articulos SET descripcion = :desc, descripcion_tpv = :desctpv, ' +
              '  pvp1 = :pvp1, pvp2 = :pvp2, pvp3 = :pvp3, pvp4 = :pvp4, id_tipo_iva = :iva, id_familia = :fam, ' +
              '  id_user_update = :user, update_at = CURRENT_TIMESTAMP ' +
              'WHERE id = :id';
            dmgMain.qryExec.ParamByName('id').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('id').Value := LUUID;
            dmgMain.qryExec.ParamByName('desc').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('desc').Value := LDesc;
            dmgMain.qryExec.ParamByName('desctpv').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('desctpv').Value := LDescTpv;
            dmgMain.qryExec.ParamByName('pvp1').AsFloat := LPvp1;
            dmgMain.qryExec.ParamByName('pvp2').AsFloat := LPvp2;
            dmgMain.qryExec.ParamByName('pvp3').AsFloat := LPvp3;
            dmgMain.qryExec.ParamByName('pvp4').AsFloat := LPvp4;
            dmgMain.qryExec.ParamByName('iva').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('iva').Value := LArtIvaId;
            dmgMain.qryExec.ParamByName('fam').DataType := ftWideString;
            if LFamiliaUUID <> '' then
              dmgMain.qryExec.ParamByName('fam').Value := LFamiliaUUID
            else
              dmgMain.qryExec.ParamByName('fam').Clear;
            dmgMain.qryExec.ParamByName('user').DataType := ftWideString;
            dmgMain.qryExec.ParamByName('user').Value := dmgMain.CurrentUserId;
            dmgMain.qryExec.ExecSQL;
            Inc(LImported);
          end;
        end;
      end;

      pbImport.Position := 33 + Round((LImported / LCount) * 17);
      LQry.Next;
    end;

    LogMessage(Format('Artículos importados/actualizados correctamente: %d/%d', [LImported, LCount]));
  finally
    LQry.Close;
    FreeAndNil(LQry);
  end;
end;

procedure TfrmImportarFactusol.ImportAlbaranes(AConnection: TADOConnection);
var
  LQry, LQryLines: TADOQuery;
  LCount, LImported, LPosicion: Integer;
  LUUID, LClienteUUID, LLineUUID, LArtUUID, LInvoiceId: string;
  LSerie, LInternalSerie, LArtCodigo, LArtDesc: string;
  LNumero, LCodCliente, LFacaAlb, LInvoiceNum, LCobradoAlb: Integer;
  LFecha: TDateTime;
  LBase, LTotal: Double;
  LArtCant, LArtPrecio, LArtTotal: Double;
  LArtDtoPorc, LArtDtoPrecio: Double;
  LEquivIva, LLineIvaId: string;
  LLineIvaRate, LIvaAmount, LTotalLine: Double;
  LIvaDoc: Double;
begin
  LogMessage('Importando Albaranes...');
  LQry := TADOQuery.Create(nil);
  LQryLines := TADOQuery.Create(nil);
  try
    LQry.Connection := AConnection;
    LQryLines.Connection := AConnection;
    LQry.SQL.Text := 'SELECT * FROM F_ALB';
    try
      LQry.Open;
    except
      on E: Exception do
      begin
        LogMessage('La tabla F_ALB no se encontró o está corrupta. Saltando.');
        Exit;
      end;
    end;

    if LQry.IsEmpty then
    begin
      LogMessage('No se encontraron albaranes en la tabla de origen.');
      Exit;
    end;

    LCount := 0;
    while not LQry.Eof do
    begin
      Inc(LCount);
      LQry.Next;
    end;
    LQry.First;

    LogMessage(Format('Se encontraron %d albaranes. Procesando...', [LCount]));
    LImported := 0;

    while not LQry.Eof do
    begin
      try LSerie := LQry.FieldByName('TIPALB').AsString; except LSerie := 'A'; end;
      try LNumero := LQry.FieldByName('CODALB').AsInteger; except LNumero := 0; end;
      try LCodCliente := LQry.FieldByName('CLIALB').AsInteger; except LCodCliente := 0; end;
      try LFecha := LQry.FieldByName('FECALB').AsDateTime; except LFecha := Now; end;
      try LBase := LQry.FieldByName('NET1ALB').AsFloat + LQry.FieldByName('NET2ALB').AsFloat; except LBase := 0.0; end;
      try LTotal := LQry.FieldByName('IMPALB').AsFloat; except LTotal := 0.0; end;

      if LNumero > 0 then
      begin
        // Leer flag facturado (FOPALB)
        try
          if LQry.FindField('FOPALB') <> nil then
          begin
            if LQry.FieldByName('FOPALB').AsInteger = 2 then
              LFacaAlb := 1
            else
              LFacaAlb := 0;
          end
          else
          begin
            if LQry.FieldByName('FACAALB').AsBoolean then
              LFacaAlb := 1
            else
              LFacaAlb := 0;
          end;
        except
          LFacaAlb := 0;
        end;

        // Leer número de factura
        try LInvoiceNum := LQry.FieldByName('FACALB').AsInteger; except LInvoiceNum := 0; end;

        // Leer flag cobrado (ESTALB)
        try
          if LQry.FindField('ESTALB') <> nil then
          begin
            if (LQry.FieldByName('ESTALB').AsInteger = 1) or (LQry.FieldByName('ESTALB').AsInteger = 2) then
              LCobradoAlb := 1
            else
              LCobradoAlb := 0;
          end
          else if LQry.FindField('COBALB') <> nil then
            LCobradoAlb := LQry.FieldByName('COBALB').AsInteger
          else if LQry.FindField('COBRAW') <> nil then
            LCobradoAlb := LQry.FieldByName('COBRAW').AsInteger
          else
            LCobradoAlb := 0;
        except
          LCobradoAlb := 0;
        end;

        // Resolver serie interna equivalente a la serie de Factusol usando la fecha del albarán
        LInternalSerie := LSerie;
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 
          'SELECT serie FROM ge_series ' +
          'WHERE serie_factusol = :equiv AND activo = 1 ' +
          '  AND (fecha_inicio IS NULL OR fecha_inicio <= :fec) ' +
          '  AND (fecha_fin IS NULL OR fecha_fin >= :fec) ' +
          'LIMIT 1';
        dmgMain.qryExec.ParamByName('equiv').AsString := LSerie;
        dmgMain.qryExec.ParamByName('fec').AsDate := LFecha;
        dmgMain.qryExec.Open;
        if not dmgMain.qryExec.IsEmpty then
          LInternalSerie := dmgMain.qryExec.FieldByName('serie').AsString
        else
        begin
          LogMessage('Advertencia: No se encontró serie interna activa para la serie Factusol de Albarán "' + LSerie + 
                     '" con fecha ' + DateToStr(LFecha) + '. Se mantendrá la serie original.');
        end;
        dmgMain.qryExec.Close;

        // Buscar factura relacionada por serie interna y número
        LInvoiceId := '';
        if (LFacaAlb <> 0) and (LInvoiceNum > 0) then
        begin
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'SELECT id FROM ge_facturas WHERE serie = :ser AND numero = :num AND empresa_id = :emp';
          dmgMain.qryExec.ParamByName('ser').AsString := LInternalSerie;
          dmgMain.qryExec.ParamByName('num').AsInteger := LInvoiceNum;
          dmgMain.qryExec.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
          dmgMain.qryExec.Open;
          if not dmgMain.qryExec.IsEmpty then
            LInvoiceId := dmgMain.qryExec.FieldByName('id').AsString;
          dmgMain.qryExec.Close;
        end;

        LClienteUUID := ResolveClienteUUID(AConnection, LCodCliente);
        if LClienteUUID = '' then
        begin
          // Crear un cliente genérico temporal si no existe
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text :=
            'INSERT INTO ge_clientes (nombre_fiscal, activo, id_user_creator, id_user_update) ' +
            'VALUES (:nom, 1, :user, :user)';
          dmgMain.qryExec.ParamByName('nom').AsString := 'Cliente Factusol ' + IntToStr(LCodCliente);
          dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
          dmgMain.qryExec.ExecSQL;

          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
          dmgMain.qryExec.Open;
          LClienteUUID := dmgMain.qryExec.Fields[0].AsString;
          dmgMain.qryExec.Close;
        end;

        // Calcular IVA de cabecera
        LIvaDoc := LTotal - LBase;

        // Comprobar si el albarán ya existe por serie y número
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 'SELECT id, cliente_id, fecha, base_imponible, iva, total, facturado, id_factura, cobrado FROM ge_albaranes WHERE serie = :ser AND numero = :num AND empresa_id = :emp';
        dmgMain.qryExec.ParamByName('ser').AsString := LInternalSerie;
        dmgMain.qryExec.ParamByName('num').AsInteger := LNumero;
        dmgMain.qryExec.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
        dmgMain.qryExec.Open;

        if dmgMain.qryExec.IsEmpty then
        begin
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text :=
            'INSERT INTO ge_albaranes (empresa_id, cliente_id, serie, numero, fecha, ' +
            ' base_imponible, iva, total, facturado, id_factura, cobrado, created_at, id_user_creator, id_user_update) ' +
            'VALUES (:emp, :cli, :ser, :num, :fec, :base, :iva, :tot, :facturado, :id_factura, :cobrado, CURRENT_TIMESTAMP, :user, :user)';
          dmgMain.qryExec.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
          dmgMain.qryExec.ParamByName('cli').AsString := LClienteUUID;
          dmgMain.qryExec.ParamByName('ser').AsString := LInternalSerie;
          dmgMain.qryExec.ParamByName('num').AsInteger := LNumero;
          dmgMain.qryExec.ParamByName('fec').AsDateTime := LFecha;
          dmgMain.qryExec.ParamByName('base').AsFloat := LBase;
          dmgMain.qryExec.ParamByName('iva').AsFloat := LIvaDoc;
          dmgMain.qryExec.ParamByName('tot').AsFloat := LTotal;
          dmgMain.qryExec.ParamByName('facturado').AsInteger := LFacaAlb;
          if LInvoiceId <> '' then
            dmgMain.qryExec.ParamByName('id_factura').AsString := LInvoiceId
          else
          begin
            dmgMain.qryExec.ParamByName('id_factura').DataType := ftString;
            dmgMain.qryExec.ParamByName('id_factura').Clear;
          end;
          dmgMain.qryExec.ParamByName('cobrado').AsInteger := LCobradoAlb;
          dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
          dmgMain.qryExec.ExecSQL;

          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
          dmgMain.qryExec.Open;
          LUUID := dmgMain.qryExec.Fields[0].AsString;
          dmgMain.qryExec.Close;

          Inc(LImported);
        end
        else
        begin
          LUUID := dmgMain.qryExec.FieldByName('id').AsString;
          if (dmgMain.qryExec.FieldByName('cliente_id').AsString <> LClienteUUID) or
             (Abs(dmgMain.qryExec.FieldByName('fecha').AsDateTime - LFecha) > 0.0001) or
             (Abs(dmgMain.qryExec.FieldByName('base_imponible').AsFloat - LBase) > 0.0001) or
             (Abs(dmgMain.qryExec.FieldByName('iva').AsFloat - LIvaDoc) > 0.0001) or
             (Abs(dmgMain.qryExec.FieldByName('total').AsFloat - LTotal) > 0.0001) or
             (dmgMain.qryExec.FieldByName('facturado').AsInteger <> LFacaAlb) or
             (dmgMain.qryExec.FieldByName('id_factura').AsString <> LInvoiceId) or
             (dmgMain.qryExec.FieldByName('cobrado').AsInteger <> LCobradoAlb) then
          begin
            dmgMain.qryExec.Close;
            dmgMain.qryExec.SQL.Text :=
              'UPDATE ge_albaranes SET cliente_id = :cli, fecha = :fec, base_imponible = :base, ' +
              ' iva = :iva, total = :tot, facturado = :facturado, id_factura = :id_factura, cobrado = :cobrado, ' +
              ' id_user_update = :user, updated_at = CURRENT_TIMESTAMP ' +
              'WHERE id = :id';
            dmgMain.qryExec.ParamByName('id').AsString := LUUID;
            dmgMain.qryExec.ParamByName('cli').AsString := LClienteUUID;
            dmgMain.qryExec.ParamByName('fec').AsDateTime := LFecha;
            dmgMain.qryExec.ParamByName('base').AsFloat := LBase;
            dmgMain.qryExec.ParamByName('iva').AsFloat := LIvaDoc;
            dmgMain.qryExec.ParamByName('tot').AsFloat := LTotal;
            dmgMain.qryExec.ParamByName('facturado').AsInteger := LFacaAlb;
            if LInvoiceId <> '' then
              dmgMain.qryExec.ParamByName('id_factura').AsString := LInvoiceId
            else
            begin
              dmgMain.qryExec.ParamByName('id_factura').DataType := ftString;
              dmgMain.qryExec.ParamByName('id_factura').Clear;
            end;
            dmgMain.qryExec.ParamByName('cobrado').AsInteger := LCobradoAlb;
            dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
            dmgMain.qryExec.ExecSQL;
            Inc(LImported);
          end;
        end;

        // Importar líneas asociadas (F_LAL)
        LQryLines.Close;
        LQryLines.SQL.Text := Format('SELECT * FROM F_LAL WHERE TIPLAL = ''%s'' AND CODLAL = %d', [LSerie, LNumero]);
        try
          LQryLines.Open;
        except
          // Continuar si falla F_LAL
        end;

        LPosicion := 0;
        if not LQryLines.IsEmpty then
        begin
          while not LQryLines.Eof do
          begin
            Inc(LPosicion);
            try LArtCodigo := CleanFactusolString(LQryLines.FieldByName('ARTLAL').AsString); except LArtCodigo := ''; end;
            try LArtDesc := CleanFactusolString(LQryLines.FieldByName('DESLAL').AsWideString); except LArtDesc := ''; end;
            try LArtCant := LQryLines.FieldByName('CANLAL').AsFloat; except LArtCant := 0.0; end;
            try LArtPrecio := LQryLines.FieldByName('PRELAL').AsFloat; except LArtPrecio := 0.0; end;
            try LArtTotal := LQryLines.FieldByName('TOTLAL').AsFloat; except LArtTotal := 0.0; end;
            try LArtDtoPorc := LQryLines.FieldByName('DT1LAL').AsFloat; except LArtDtoPorc := 0.0; end;
            LArtDtoPrecio := LArtPrecio * (LArtDtoPorc / 100.0);

            // Resolver IVA por código equivalente en Factusol (IVALAL o TIVLAL)
            LEquivIva := '';
            try
              if LQryLines.FindField('IVALAL') <> nil then
                LEquivIva := LQryLines.FieldByName('IVALAL').AsString
              else if LQryLines.FindField('TIVLAL') <> nil then
                LEquivIva := LQryLines.FieldByName('TIVLAL').AsString;
            except
              LEquivIva := '';
            end;

            if not ResolveIvaByFactusolEquiv(LEquivIva, LLineIvaId, LLineIvaRate) then
              GetDefaultIva(LLineIvaId, LLineIvaRate);

            LIvaAmount := LArtTotal * (LLineIvaRate / 100.0);
            LTotalLine := LArtTotal + LIvaAmount;

            LArtUUID := ResolveOrCreateArticulo(LArtCodigo, LArtDesc);

            // Comprobar si la línea ya existe para este albarán en esta posición
            dmgMain.qryExec.Close;
            dmgMain.qryExec.SQL.Text := 
              'SELECT id, articulo_id, descripcion, cantidad, precio, total, tipo_iva, iva, descuento_porcentaje, descuento_precio ' +
              'FROM ge_albaranes_lineas WHERE albaran_id = :alb AND posicion = :pos';
            dmgMain.qryExec.ParamByName('alb').AsString := LUUID;
            dmgMain.qryExec.ParamByName('pos').AsInteger := LPosicion;
            dmgMain.qryExec.Open;

            if dmgMain.qryExec.IsEmpty then
            begin
              dmgMain.qryExec.Close;
              dmgMain.qryExec.SQL.Text :=
                'INSERT INTO ge_albaranes_lineas (albaran_id, articulo_id, descripcion, ' +
                '  cantidad, precio, total, posicion, tipo_iva, iva, descuento_porcentaje, descuento_precio, id_user_creator, id_user_update) ' +
                'VALUES (:alb, :art, :desc, :cant, :prec, :tot, :pos, :tipoiva, :iva, :dtoporc, :dtoprec, :user, :user)';
              dmgMain.qryExec.ParamByName('alb').AsString := LUUID;
              dmgMain.qryExec.ParamByName('art').DataType := ftString;
              if LArtUUID <> '' then
                dmgMain.qryExec.ParamByName('art').AsString := LArtUUID
              else
                dmgMain.qryExec.ParamByName('art').Clear;
              dmgMain.qryExec.ParamByName('desc').DataType := ftWideString;
              dmgMain.qryExec.ParamByName('desc').Value := LArtDesc;
              dmgMain.qryExec.ParamByName('cant').AsFloat := LArtCant;
              dmgMain.qryExec.ParamByName('prec').AsFloat := LArtPrecio;
              dmgMain.qryExec.ParamByName('tot').AsFloat := LTotalLine;
              dmgMain.qryExec.ParamByName('pos').AsInteger := LPosicion;
              dmgMain.qryExec.ParamByName('tipoiva').AsFloat := LLineIvaRate;
              dmgMain.qryExec.ParamByName('iva').AsFloat := LIvaAmount;
              dmgMain.qryExec.ParamByName('dtoporc').AsFloat := LArtDtoPorc;
              dmgMain.qryExec.ParamByName('dtoprec').AsFloat := LArtDtoPrecio;
              dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
              dmgMain.qryExec.ExecSQL;

              dmgMain.qryExec.Close;
              dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
              dmgMain.qryExec.Open;
              LLineUUID := dmgMain.qryExec.Fields[0].AsString;
              dmgMain.qryExec.Close;
            end
            else
            begin
              LLineUUID := dmgMain.qryExec.FieldByName('id').AsString;
              if (dmgMain.qryExec.FieldByName('articulo_id').AsString <> LArtUUID) or
                 (dmgMain.qryExec.FieldByName('descripcion').AsString <> LArtDesc) or
                 (Abs(dmgMain.qryExec.FieldByName('cantidad').AsFloat - LArtCant) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('precio').AsFloat - LArtPrecio) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('total').AsFloat - LTotalLine) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('tipo_iva').AsFloat - LLineIvaRate) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('iva').AsFloat - LIvaAmount) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('descuento_porcentaje').AsFloat - LArtDtoPorc) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('descuento_precio').AsFloat - LArtDtoPrecio) > 0.0001) then
              begin
                dmgMain.qryExec.Close;
                dmgMain.qryExec.SQL.Text :=
                  'UPDATE ge_albaranes_lineas SET articulo_id = :art, ' +
                  '  descripcion = :desc, cantidad = :cant, precio = :prec, total = :tot, ' +
                  '  tipo_iva = :tipoiva, iva = :iva, descuento_porcentaje = :dtoporc, descuento_precio = :dtoprec, ' +
                  '  id_user_update = :user, update_at = CURRENT_TIMESTAMP ' +
                  'WHERE id = :id';
                dmgMain.qryExec.ParamByName('id').AsString := LLineUUID;
                dmgMain.qryExec.ParamByName('art').DataType := ftString;
                if LArtUUID <> '' then
                  dmgMain.qryExec.ParamByName('art').AsString := LArtUUID
                else
                  dmgMain.qryExec.ParamByName('art').Clear;
                dmgMain.qryExec.ParamByName('desc').DataType := ftWideString;
                dmgMain.qryExec.ParamByName('desc').Value := LArtDesc;
                dmgMain.qryExec.ParamByName('cant').AsFloat := LArtCant;
                dmgMain.qryExec.ParamByName('prec').AsFloat := LArtPrecio;
                dmgMain.qryExec.ParamByName('tot').AsFloat := LTotalLine;
                dmgMain.qryExec.ParamByName('tipoiva').AsFloat := LLineIvaRate;
                dmgMain.qryExec.ParamByName('iva').AsFloat := LIvaAmount;
                dmgMain.qryExec.ParamByName('dtoporc').AsFloat := LArtDtoPorc;
                dmgMain.qryExec.ParamByName('dtoprec').AsFloat := LArtDtoPrecio;
                dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
                dmgMain.qryExec.ExecSQL;
              end;
            end;
            LQryLines.Next;
          end;
        end;

        // Eliminar líneas excedentes si ahora hay menos
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 'DELETE FROM ge_albaranes_lineas WHERE albaran_id = :alb AND posicion > :pos';
        dmgMain.qryExec.ParamByName('alb').AsString := LUUID;
        dmgMain.qryExec.ParamByName('pos').AsInteger := LPosicion;
        dmgMain.qryExec.ExecSQL;

        // Calcular y actualizar totales en la cabecera del albarán
        var LSumBase: Double := 0;
        var LSumIva: Double := 0;
        var LSumTotal: Double := 0;
        
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 
          'SELECT SUM(total - iva) as sum_base, SUM(iva) as sum_iva, SUM(total) as sum_total ' +
          'FROM ge_albaranes_lineas WHERE albaran_id = :alb';
        dmgMain.qryExec.ParamByName('alb').AsString := LUUID;
        dmgMain.qryExec.Open;
        if not dmgMain.qryExec.IsEmpty then
        begin
          LSumBase := dmgMain.qryExec.FieldByName('sum_base').AsFloat;
          LSumIva := dmgMain.qryExec.FieldByName('sum_iva').AsFloat;
          LSumTotal := dmgMain.qryExec.FieldByName('sum_total').AsFloat;
        end;
        dmgMain.qryExec.Close;
        
        dmgMain.qryExec.SQL.Text := 
          'UPDATE ge_albaranes SET base_imponible = :base, iva = :iva, total = :tot WHERE id = :id';
        dmgMain.qryExec.ParamByName('base').AsFloat := LSumBase;
        dmgMain.qryExec.ParamByName('iva').AsFloat := LSumIva;
        dmgMain.qryExec.ParamByName('tot').AsFloat := LSumTotal;
        dmgMain.qryExec.ParamByName('id').AsString := LUUID;
        dmgMain.qryExec.ExecSQL;
      end;

      pbImport.Position := 50 + Round((LImported / LCount) * 25);
      LQry.Next;
    end;

    LogMessage(Format('Albaranes importados/actualizados correctamente: %d/%d', [LImported, LCount]));
  finally
    LQry.Close;
    LQryLines.Close;
    FreeAndNil(LQry);
    FreeAndNil(LQryLines);
  end;
end;

procedure TfrmImportarFactusol.ImportFacturas(AConnection: TADOConnection);
var
  LQry, LQryLines: TADOQuery;
  LCount, LImported, LPosicion: Integer;
  LUUID, LClienteUUID, LLineUUID, LArtUUID: string;
  LSerie, LArtCodigo, LArtDesc: string;
  LNumero, LCodCliente, LCobradaFac: Integer;
  LFecha: TDateTime;
  LBase, LTotal, LIva: Double;
  LArtCant, LArtPrecio, LArtTotal: Double;
  LArtDtoPorc, LArtDtoCant: Double;
  LEquivIva, LLineIvaId: string;
  LLineIvaRate, LIvaAmount, LTotalLine: Double;
  LInternalSerie: string;
  LMaxNumbers: TDictionary<string, Integer>;
  LSeriesKey: string;
begin
  LogMessage('Importando Facturas...');
  LQry := TADOQuery.Create(nil);
  LQryLines := TADOQuery.Create(nil);
  try
    LQry.Connection := AConnection;
    LQryLines.Connection := AConnection;
    LQry.SQL.Text := 'SELECT * FROM F_FAC';
    try
      LQry.Open;
    except
      on E: Exception do
      begin
        LogMessage('La tabla F_FAC no se encontró o está corrupta. Saltando.');
        Exit;
      end;
    end;

    if LQry.IsEmpty then
    begin
      LogMessage('No se encontraron facturas en la tabla de origen.');
      Exit;
    end;

    LCount := 0;
    while not LQry.Eof do
    begin
      Inc(LCount);
      LQry.Next;
    end;
    LQry.First;

    LogMessage(Format('Se encontraron %d facturas. Procesando...', [LCount]));
    LImported := 0;
    LMaxNumbers := TDictionary<string, Integer>.Create;

    while not LQry.Eof do
    begin
      try LSerie := LQry.FieldByName('TIPFAC').AsString; except LSerie := 'A'; end;
      try LNumero := LQry.FieldByName('CODFAC').AsInteger; except LNumero := 0; end;
      try LCodCliente := LQry.FieldByName('CLIFAC').AsInteger; except LCodCliente := 0; end;
      try LFecha := LQry.FieldByName('FECFAC').AsDateTime; except LFecha := Now; end;
      try LBase := LQry.FieldByName('NET1FAC').AsFloat + LQry.FieldByName('NET2FAC').AsFloat; except LBase := 0.0; end;
      try LTotal := LQry.FieldByName('IMPFAC').AsFloat; except LTotal := 0.0; end;
      try LIva := LQry.FieldByName('MIP1FAC').AsFloat + LQry.FieldByName('MIP2FAC').AsFloat; except LIva := 0.0; end;

      if LNumero > 0 then
      begin
        try
          var LEstFacVal: Integer := 0;
          if LQry.FindField('ESTFAC') <> nil then
            LEstFacVal := LQry.FieldByName('ESTFAC').AsInteger
          else if LQry.FindField('ESTFACT') <> nil then
            LEstFacVal := LQry.FieldByName('ESTFACT').AsInteger
          else if LQry.FindField('COBFAC') <> nil then
            LEstFacVal := LQry.FieldByName('COBFAC').AsInteger * 2
          else if LQry.FindField('COBRAW') <> nil then
            LEstFacVal := LQry.FieldByName('COBRAW').AsInteger * 2;

          if LEstFacVal = 2 then
            LCobradaFac := 1
          else
            LCobradaFac := 0;
        except
          LCobradaFac := 0;
        end;

        // Resolver serie interna equivalente a la serie de Factusol usando la fecha de la factura
        LInternalSerie := LSerie;
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 
          'SELECT serie FROM ge_series ' +
          'WHERE serie_factusol = :equiv AND activo = 1 ' +
          '  AND (fecha_inicio IS NULL OR fecha_inicio <= :fec) ' +
          '  AND (fecha_fin IS NULL OR fecha_fin >= :fec) ' +
          'LIMIT 1';
        dmgMain.qryExec.ParamByName('equiv').AsString := LSerie;
        dmgMain.qryExec.ParamByName('fec').AsDate := LFecha;
        dmgMain.qryExec.Open;
        if not dmgMain.qryExec.IsEmpty then
          LInternalSerie := dmgMain.qryExec.FieldByName('serie').AsString
        else
        begin
          LogMessage('Advertencia: No se encontró serie interna activa para la serie Factusol "' + LSerie + 
                     '" con fecha ' + DateToStr(LFecha) + '. Se mantendrá la serie original.');
        end;
        dmgMain.qryExec.Close;

        // Guardar el mayor número de factura de esta serie interna
        if LMaxNumbers.ContainsKey(LInternalSerie) then
        begin
          if LNumero > LMaxNumbers[LInternalSerie] then
            LMaxNumbers[LInternalSerie] := LNumero;
        end
        else
          LMaxNumbers.Add(LInternalSerie, LNumero);

        LClienteUUID := ResolveClienteUUID(AConnection, LCodCliente);
        if LClienteUUID = '' then
        begin
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text :=
            'INSERT INTO ge_clientes (nombre_fiscal, activo, id_user_creator, id_user_update) ' +
            'VALUES (:nom, 1, :user, :user)';
          dmgMain.qryExec.ParamByName('nom').AsString := 'Cliente Factusol ' + IntToStr(LCodCliente);
          dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
          dmgMain.qryExec.ExecSQL;

          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
          dmgMain.qryExec.Open;
          LClienteUUID := dmgMain.qryExec.Fields[0].AsString;
          dmgMain.qryExec.Close;
        end;

        // Comprobar si la factura ya existe por serie interna y número
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 'SELECT id, cliente_id, fecha, base_imponible, total, iva, pagada FROM ge_facturas WHERE serie = :ser AND numero = :num AND empresa_id = :emp';
        dmgMain.qryExec.ParamByName('ser').AsString := LInternalSerie;
        dmgMain.qryExec.ParamByName('num').AsInteger := LNumero;
        dmgMain.qryExec.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
        dmgMain.qryExec.Open;

        if dmgMain.qryExec.IsEmpty then
        begin
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text :=
            'INSERT INTO ge_facturas (empresa_id, cliente_id, serie, numero, fecha, ' +
            ' base_imponible, total, iva, pagada, created_at, id_user_creator, id_user_update) ' +
            'VALUES (:emp, :cli, :ser, :num, :fec, :base, :tot, :iva, :pagada, CURRENT_TIMESTAMP, :user, :user)';
          dmgMain.qryExec.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
          dmgMain.qryExec.ParamByName('cli').AsString := LClienteUUID;
          dmgMain.qryExec.ParamByName('ser').AsString := LInternalSerie;
          dmgMain.qryExec.ParamByName('num').AsInteger := LNumero;
          dmgMain.qryExec.ParamByName('fec').AsDateTime := LFecha;
          dmgMain.qryExec.ParamByName('base').AsFloat := LBase;
          dmgMain.qryExec.ParamByName('tot').AsFloat := LTotal;
          dmgMain.qryExec.ParamByName('iva').AsFloat := LIva;
          dmgMain.qryExec.ParamByName('pagada').AsInteger := LCobradaFac;
          dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
          dmgMain.qryExec.ExecSQL;

          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
          dmgMain.qryExec.Open;
          LUUID := dmgMain.qryExec.Fields[0].AsString;
          dmgMain.qryExec.Close;

          Inc(LImported);
        end
        else
        begin
          LUUID := dmgMain.qryExec.FieldByName('id').AsString;
          if (dmgMain.qryExec.FieldByName('cliente_id').AsString <> LClienteUUID) or
             (Abs(dmgMain.qryExec.FieldByName('fecha').AsDateTime - LFecha) > 0.0001) or
             (Abs(dmgMain.qryExec.FieldByName('base_imponible').AsFloat - LBase) > 0.0001) or
             (Abs(dmgMain.qryExec.FieldByName('total').AsFloat - LTotal) > 0.0001) or
             (Abs(dmgMain.qryExec.FieldByName('iva').AsFloat - LIva) > 0.0001) or
             (dmgMain.qryExec.FieldByName('pagada').AsInteger <> LCobradaFac) then
          begin
            dmgMain.qryExec.Close;
            dmgMain.qryExec.SQL.Text :=
              'UPDATE ge_facturas SET cliente_id = :cli, serie = :ser, fecha = :fec, base_imponible = :base, ' +
              ' total = :tot, iva = :iva, pagada = :pagada, id_user_update = :user, update_at = CURRENT_TIMESTAMP ' +
              'WHERE id = :id';
            dmgMain.qryExec.ParamByName('id').AsString := LUUID;
            dmgMain.qryExec.ParamByName('cli').AsString := LClienteUUID;
            dmgMain.qryExec.ParamByName('ser').AsString := LInternalSerie;
            dmgMain.qryExec.ParamByName('fec').AsDateTime := LFecha;
            dmgMain.qryExec.ParamByName('base').AsFloat := LBase;
            dmgMain.qryExec.ParamByName('tot').AsFloat := LTotal;
            dmgMain.qryExec.ParamByName('iva').AsFloat := LIva;
            dmgMain.qryExec.ParamByName('pagada').AsInteger := LCobradaFac;
            dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
            dmgMain.qryExec.ExecSQL;
            Inc(LImported);
          end;
        end;

        // Importar líneas asociadas (F_LFA)
        LQryLines.Close;
        LQryLines.SQL.Text := Format('SELECT * FROM F_LFA WHERE TIPLFA = ''%s'' AND CODLFA = %d', [LSerie, LNumero]);
        try
          LQryLines.Open;
        except
          // Continuar si falla F_LFA
        end;

        LPosicion := 0;
        if not LQryLines.IsEmpty then
        begin
          while not LQryLines.Eof do
          begin
            Inc(LPosicion);
            try LArtCodigo := CleanFactusolString(LQryLines.FieldByName('ARTLFA').AsString); except LArtCodigo := ''; end;
            try LArtDesc := CleanFactusolString(LQryLines.FieldByName('DESLFA').AsWideString); except LArtDesc := ''; end;
            try LArtCant := LQryLines.FieldByName('CANLFA').AsFloat; except LArtCant := 0.0; end;
            try LArtPrecio := LQryLines.FieldByName('PRELFA').AsFloat; except LArtPrecio := 0.0; end;
            try LArtTotal := LQryLines.FieldByName('TOTLFA').AsFloat; except LArtTotal := 0.0; end;
            try LArtDtoPorc := LQryLines.FieldByName('DT1LFA').AsFloat; except LArtDtoPorc := 0.0; end;
            LArtDtoCant := LArtPrecio * (LArtDtoPorc / 100.0);

            // Resolver IVA por código equivalente en Factusol (IVALFA o TIVLFA)
            LEquivIva := '';
            try
              if LQryLines.FindField('IVALFA') <> nil then
                LEquivIva := LQryLines.FieldByName('IVALFA').AsString
              else if LQryLines.FindField('TIVLFA') <> nil then
                LEquivIva := LQryLines.FieldByName('TIVLFA').AsString;
            except
              LEquivIva := '';
            end;

            if not ResolveIvaByFactusolEquiv(LEquivIva, LLineIvaId, LLineIvaRate) then
              GetDefaultIva(LLineIvaId, LLineIvaRate);

            LIvaAmount := LArtTotal * (LLineIvaRate / 100.0);
            LTotalLine := LArtTotal + LIvaAmount;

            LArtUUID := ResolveOrCreateArticulo(LArtCodigo, LArtDesc);

            // Comprobar si la línea ya existe para esta factura en esta posición
            dmgMain.qryExec.Close;
            dmgMain.qryExec.SQL.Text := 
              'SELECT id, articulo_id, articulo_codigo, descripcion, cantidad, precio, total, tipo_iva, iva, descuento_porc, descuento_cant ' +
              'FROM ge_facturas_lineas WHERE factura_id = :fac AND posicion = :pos';
            dmgMain.qryExec.ParamByName('fac').AsString := LUUID;
            dmgMain.qryExec.ParamByName('pos').AsInteger := LPosicion;
            dmgMain.qryExec.Open;

            if dmgMain.qryExec.IsEmpty then
            begin
              dmgMain.qryExec.Close;
              dmgMain.qryExec.SQL.Text :=
                'INSERT INTO ge_facturas_lineas (factura_id, articulo_id, articulo_codigo, descripcion, ' +
                '  cantidad, precio, total, posicion, tipo_iva, iva, descuento_porc, descuento_cant, id_user_creator, id_user_update) ' +
                'VALUES (:fac, :art, :artcod, :desc, :cant, :prec, :tot, :pos, :tipoiva, :iva, :dtoporc, :dtocant, :user, :user)';
              dmgMain.qryExec.ParamByName('fac').AsString := LUUID;
              dmgMain.qryExec.ParamByName('art').DataType := ftString;
              if LArtUUID <> '' then
                dmgMain.qryExec.ParamByName('art').AsString := LArtUUID
              else
                dmgMain.qryExec.ParamByName('art').Clear;
              dmgMain.qryExec.ParamByName('artcod').AsString := LArtCodigo;
              dmgMain.qryExec.ParamByName('desc').DataType := ftWideString;
              dmgMain.qryExec.ParamByName('desc').Value := LArtDesc;
              dmgMain.qryExec.ParamByName('cant').AsFloat := LArtCant;
              dmgMain.qryExec.ParamByName('prec').AsFloat := LArtPrecio;
              dmgMain.qryExec.ParamByName('tot').AsFloat := LTotalLine;
              dmgMain.qryExec.ParamByName('pos').AsInteger := LPosicion;
              dmgMain.qryExec.ParamByName('tipoiva').AsFloat := LLineIvaRate;
              dmgMain.qryExec.ParamByName('iva').AsFloat := LIvaAmount;
              dmgMain.qryExec.ParamByName('dtoporc').AsFloat := LArtDtoPorc;
              dmgMain.qryExec.ParamByName('dtocant').AsFloat := LArtDtoCant;
              dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
              dmgMain.qryExec.ExecSQL;

              dmgMain.qryExec.Close;
              dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
              dmgMain.qryExec.Open;
              LLineUUID := dmgMain.qryExec.Fields[0].AsString;
              dmgMain.qryExec.Close;
            end
            else
            begin
              LLineUUID := dmgMain.qryExec.FieldByName('id').AsString;
              if (dmgMain.qryExec.FieldByName('articulo_id').AsString <> LArtUUID) or
                 (dmgMain.qryExec.FieldByName('articulo_codigo').AsString <> LArtCodigo) or
                 (dmgMain.qryExec.FieldByName('descripcion').AsString <> LArtDesc) or
                 (Abs(dmgMain.qryExec.FieldByName('cantidad').AsFloat - LArtCant) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('precio').AsFloat - LArtPrecio) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('total').AsFloat - LTotalLine) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('tipo_iva').AsFloat - LLineIvaRate) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('iva').AsFloat - LIvaAmount) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('descuento_porc').AsFloat - LArtDtoPorc) > 0.0001) or
                 (Abs(dmgMain.qryExec.FieldByName('descuento_cant').AsFloat - LArtDtoCant) > 0.0001) then
              begin
                dmgMain.qryExec.Close;
                dmgMain.qryExec.SQL.Text :=
                  'UPDATE ge_facturas_lineas SET articulo_id = :art, articulo_codigo = :artcod, ' +
                  '  descripcion = :desc, cantidad = :cant, precio = :prec, total = :tot, ' +
                  '  tipo_iva = :tipoiva, iva = :iva, descuento_porc = :dtoporc, descuento_cant = :dtocant, ' +
                  '  id_user_update = :user, update_at = CURRENT_TIMESTAMP ' +
                  'WHERE id = :id';
                dmgMain.qryExec.ParamByName('id').AsString := LLineUUID;
                dmgMain.qryExec.ParamByName('art').DataType := ftString;
                if LArtUUID <> '' then
                  dmgMain.qryExec.ParamByName('art').AsString := LArtUUID
                else
                  dmgMain.qryExec.ParamByName('art').Clear;
                dmgMain.qryExec.ParamByName('artcod').AsString := LArtCodigo;
                dmgMain.qryExec.ParamByName('desc').DataType := ftWideString;
                dmgMain.qryExec.ParamByName('desc').Value := LArtDesc;
                dmgMain.qryExec.ParamByName('cant').AsFloat := LArtCant;
                dmgMain.qryExec.ParamByName('prec').AsFloat := LArtPrecio;
                dmgMain.qryExec.ParamByName('tot').AsFloat := LTotalLine;
                dmgMain.qryExec.ParamByName('tipoiva').AsFloat := LLineIvaRate;
                dmgMain.qryExec.ParamByName('iva').AsFloat := LIvaAmount;
                dmgMain.qryExec.ParamByName('dtoporc').AsFloat := LArtDtoPorc;
                dmgMain.qryExec.ParamByName('dtocant').AsFloat := LArtDtoCant;
                dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
                dmgMain.qryExec.ExecSQL;
              end;
            end;
            LQryLines.Next;
          end;
        end;

        // Eliminar líneas excedentes
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 'DELETE FROM ge_facturas_lineas WHERE factura_id = :fac AND posicion > :pos';
        dmgMain.qryExec.ParamByName('fac').AsString := LUUID;
        dmgMain.qryExec.ParamByName('pos').AsInteger := LPosicion;
        dmgMain.qryExec.ExecSQL;

        // Calcular y actualizar totales en la cabecera de la factura
        var LSumBase: Double := 0;
        var LSumIva: Double := 0;
        var LSumTotal: Double := 0;
        
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 
          'SELECT SUM(total - iva) as sum_base, SUM(iva) as sum_iva, SUM(total) as sum_total ' +
          'FROM ge_facturas_lineas WHERE factura_id = :fac';
        dmgMain.qryExec.ParamByName('fac').AsString := LUUID;
        dmgMain.qryExec.Open;
        if not dmgMain.qryExec.IsEmpty then
        begin
          LSumBase := dmgMain.qryExec.FieldByName('sum_base').AsFloat;
          LSumIva := dmgMain.qryExec.FieldByName('sum_iva').AsFloat;
          LSumTotal := dmgMain.qryExec.FieldByName('sum_total').AsFloat;
        end;
        dmgMain.qryExec.Close;
        
        dmgMain.qryExec.SQL.Text := 
          'UPDATE ge_facturas SET base_imponible = :base, iva = :iva, total = :tot WHERE id = :id';
        dmgMain.qryExec.ParamByName('base').AsFloat := LSumBase;
        dmgMain.qryExec.ParamByName('iva').AsFloat := LSumIva;
        dmgMain.qryExec.ParamByName('tot').AsFloat := LSumTotal;
        dmgMain.qryExec.ParamByName('id').AsString := LUUID;
        dmgMain.qryExec.ExecSQL;
      end;

      pbImport.Position := 75 + Round((LImported / LCount) * 25);
      LQry.Next;
    end;

    LogMessage(Format('Facturas importadas/actualizadas correctamente: %d/%d', [LImported, LCount]));

    // Actualizar el valor sig_numero_factura en la tabla ge_series con el mayor número + 1
    if Assigned(LMaxNumbers) then
    begin
      for LSeriesKey in LMaxNumbers.Keys do
      begin
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 
          'UPDATE ge_series SET sig_numero_factura = GREATEST(sig_numero_factura, :nextnum) ' +
          'WHERE serie = :ser AND empresa_id = :emp';
        dmgMain.qryExec.ParamByName('nextnum').AsInteger := LMaxNumbers[LSeriesKey] + 1;
        dmgMain.qryExec.ParamByName('ser').AsString := LSeriesKey;
        dmgMain.qryExec.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
        try
          dmgMain.qryExec.ExecSQL;
          LogMessage(Format('Serie interna "%s" actualizada. Siguiente factura: %d', [LSeriesKey, LMaxNumbers[LSeriesKey] + 1]));
        except
          on E: Exception do
            LogMessage(Format('Error al actualizar secuencia de serie "%s": %s', [LSeriesKey, E.Message]));
        end;
      end;
    end;
  finally
    if Assigned(LMaxNumbers) then
      FreeAndNil(LMaxNumbers);
    LQry.Close;
    LQryLines.Close;
    FreeAndNil(LQry);
    FreeAndNil(LQryLines);
  end;
end;

procedure TfrmImportarFactusol.btnSalirClick(Sender: TObject);
begin
  ModalResult := mrCancel;
  Close;
end;

end.