unit uEnviosWebImporter;

interface

uses
  System.SysUtils, System.Classes, Data.DB, FireDAC.Comp.Client,
  FireDAC.Stan.Param, FireDAC.Phys.MySQL, FireDAC.Phys.MySQLDef,
  FireDAC.Phys.Intf, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error,
  FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, FireDAC.Comp.DataSet;

type
  TWebOrderHeader = record
    OrderId: Int64;
    DateCreated: TDateTime;
    NomCliente: string;
    Nif: string;
    Direccion: string;
    CP: string;
    Poblacion: string;
    Telefono: string;
    Email: string;
    Total: Double;
    MetodoPago: string;
    WcStatus: string;
    Importado: Boolean;
    NumPedidoLocal: string;
  end;

  TWebOrderLine = record
    ProductId: Int64;
    Description: string;
    Qty: Double;
    Price: Double; // Price without tax
    Total: Double; // Line total
  end;

  TWebOrder = record
    Header: TWebOrderHeader;
    Lines: TArray<TWebOrderLine>;
  end;

  TWebProduct = record
    ProductId: Int64;
    ProductName: string;
    LocalArticuloId: string; // Vacío si no está vinculado
  end;

  TWebOrderImporter = class
  private
    class function LoadWebConfig(AEmpresaId: Integer; out AServer, ADB, AUser, APass: string): Boolean;
    class function CreateMySQLConnection(AEmpresaId: Integer; var AErrorMsg: string): TFDConnection;
  public
    class function HasWebConfig(AEmpresaId: Integer): Boolean;
    class function FetchPendingOrders(out AOrders: TArray<TWebOrderHeader>; var AErrorMsg: string): Boolean; overload;
    class function FetchPendingOrders(ADesdeFecha: TDateTime; out AOrders: TArray<TWebOrderHeader>; var AErrorMsg: string): Boolean; overload;
    class function FetchPendingOrders(AEmpresaId: Integer; out AOrders: TArray<TWebOrderHeader>; var AErrorMsg: string): Boolean; overload;
    class function FetchPendingOrders(AEmpresaId: Integer; ADesdeFecha: TDateTime; out AOrders: TArray<TWebOrderHeader>; var AErrorMsg: string): Boolean; overload;
    class function FetchOrderDetails(AEmpresaId: Integer; AOrderId: Int64; out AOrder: TWebOrder; var AErrorMsg: string): Boolean;
    class function ImportOrder(const AOrder: TWebOrder; AEmpresaId: Integer; var AErrorMsg: string): Int64;
    class function FetchUnmatchedProducts(AEmpresaId: Integer; out AProducts: TArray<TWebProduct>; var AErrorMsg: string): Boolean;
    class procedure AssignWebProduct(AProductId: Int64; ALocalArticuloId: string; ADescWeb: string = '');
  end;

implementation

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

const
  REMOTE_HOST = 'dedi6205.your-server.de';
  REMOTE_DB   = 'raulad_db1';
  REMOTE_USER = 'raulad_1';
  REMOTE_PASS = 'byiL3kb8a8Zk7aESU!e';

// ---------------------------------------------------------------------------
class function TWebOrderImporter.LoadWebConfig(AEmpresaId: Integer; out AServer, ADB, AUser, APass: string): Boolean;
var
  Qry: TFDQuery;
begin
  Result := False;
  AServer := ''; ADB := ''; AUser := ''; APass := '';
  Qry := TFDQuery.Create(nil);
  try
    Qry.Connection := dmgMain.dbConn;
    Qry.SQL.Text := 'SELECT web_server, web_db, web_user, web_password FROM ge_empresas WHERE id = :id';
    Qry.ParamByName('id').AsInteger := AEmpresaId;
    Qry.Open;
    if not Qry.IsEmpty then
    begin
      if Qry.FindField('web_server') <> nil then
        AServer := Qry.FieldByName('web_server').AsString;
      if Qry.FindField('web_db') <> nil then
        ADB := Qry.FieldByName('web_db').AsString;
      if Qry.FindField('web_user') <> nil then
        AUser := Qry.FieldByName('web_user').AsString;
      if Qry.FindField('web_password') <> nil then
        APass := Qry.FieldByName('web_password').AsString;
        
      Result := (AServer <> '') and (ADB <> '') and (AUser <> '');
    end;
  except
    // Si falla porque no existen las columnas, se devuelve False y se usará el fallback
    Result := False;
  end;
  Qry.Free;
end;

// ---------------------------------------------------------------------------
class function TWebOrderImporter.CreateMySQLConnection(AEmpresaId: Integer; var AErrorMsg: string): TFDConnection;
var
  LConn: TFDConnection;
  LServer, LDB, LUser, LPass: string;
begin
  Result := nil;
  AErrorMsg := '';
  
  // Intentar cargar credenciales dinámicas de la empresa
  if not LoadWebConfig(AEmpresaId, LServer, LDB, LUser, LPass) then
  begin
    AErrorMsg := 'La empresa actual no tiene configurada la conexión a la base de datos web en ge_empresas (web_server, web_db, web_user).';
    Exit;
  end;
  
  LConn := TFDConnection.Create(nil);
  try
    LConn.DriverName := 'MySQL';
    LConn.Params.Add('Server=' + LServer);
    LConn.Params.Add('Database=' + LDB);
    LConn.Params.Add('User_Name=' + LUser);
    LConn.Params.Add('Password=' + LPass);
    LConn.Params.Add('Port=3306');
    LConn.Params.Add('CharacterSet=utf8mb4');
    LConn.Params.Add('LoginTimeout=5');
    LConn.Connected := True;
    Result := LConn;
  except
    on E: Exception do
    begin
      LConn.Free;
      AErrorMsg := 'Fallo al conectar con la base de datos web de la empresa: ' + E.Message;
    end;
  end;
end;

// ---------------------------------------------------------------------------
class function TWebOrderImporter.HasWebConfig(AEmpresaId: Integer): Boolean;
var
  LSrv, LDb, LUsr, LPass: string;
begin
  Result := LoadWebConfig(AEmpresaId, LSrv, LDb, LUsr, LPass);
end;

// ---------------------------------------------------------------------------
class function TWebOrderImporter.FetchPendingOrders(out AOrders: TArray<TWebOrderHeader>; var AErrorMsg: string): Boolean;
begin
  Result := FetchPendingOrders(StrToIntDef(dmgMain.CurrentCompanyId, 1), Date - 30, AOrders, AErrorMsg);
end;

// ---------------------------------------------------------------------------
class function TWebOrderImporter.FetchPendingOrders(ADesdeFecha: TDateTime; out AOrders: TArray<TWebOrderHeader>; var AErrorMsg: string): Boolean;
begin
  Result := FetchPendingOrders(StrToIntDef(dmgMain.CurrentCompanyId, 1), ADesdeFecha, AOrders, AErrorMsg);
end;

// ---------------------------------------------------------------------------
class function TWebOrderImporter.FetchPendingOrders(AEmpresaId: Integer; out AOrders: TArray<TWebOrderHeader>; var AErrorMsg: string): Boolean;
begin
  Result := FetchPendingOrders(AEmpresaId, Date - 30, AOrders, AErrorMsg);
end;

// ---------------------------------------------------------------------------
class function TWebOrderImporter.FetchPendingOrders(AEmpresaId: Integer; ADesdeFecha: TDateTime; out AOrders: TArray<TWebOrderHeader>; var AErrorMsg: string): Boolean;
var
  LConn: TFDConnection;
  LQry, LLocalQry: TFDQuery;
  LCount: Integer;
  LRefNum: string;
  LActualDateLimit: TDateTime;
begin
  Result := False;
  SetLength(AOrders, 0);
  
  if ADesdeFecha <= 0 then
    LActualDateLimit := Date - 30
  else
    LActualDateLimit := Trunc(ADesdeFecha);
  
  LConn := CreateMySQLConnection(AEmpresaId, AErrorMsg);
  if not Assigned(LConn) then Exit;
  
  LQry := TFDQuery.Create(nil);
  LLocalQry := TFDQuery.Create(nil);
  try
    LQry.Connection := LConn;
    LLocalQry.Connection := dmgMain.dbConn;
    // Consulta agrupada optimizada para recuperar cabeceras desde ADesdeFecha
    LQry.SQL.Text := 
      'SELECT p.ID, p.post_date, p.post_status as wc_status, ' +
      '  COALESCE(' +
      '    NULLIF(CONCAT(' +
      '      MAX(CASE WHEN pm.meta_key = ''_shipping_first_name'' THEN pm.meta_value END), '' '', ' +
      '      MAX(CASE WHEN pm.meta_key = ''_shipping_last_name'' THEN pm.meta_value END)' +
      '    ), '' ''), ' +
      '    CONCAT(' +
      '      MAX(CASE WHEN pm.meta_key = ''_billing_first_name'' THEN pm.meta_value END), '' '', ' +
      '      MAX(CASE WHEN pm.meta_key = ''_billing_last_name'' THEN pm.meta_value END)' +
      '    )' +
      '  ) as customer_name, ' +
      '  COALESCE(MAX(CASE WHEN pm.meta_key = ''_shipping_address_1'' THEN pm.meta_value END), MAX(CASE WHEN pm.meta_key = ''_billing_address_1'' THEN pm.meta_value END)) as address, ' +
      '  COALESCE(MAX(CASE WHEN pm.meta_key = ''_shipping_postcode'' THEN pm.meta_value END), MAX(CASE WHEN pm.meta_key = ''_billing_postcode'' THEN pm.meta_value END)) as cp, ' +
      '  COALESCE(MAX(CASE WHEN pm.meta_key = ''_shipping_city'' THEN pm.meta_value END), MAX(CASE WHEN pm.meta_key = ''_billing_city'' THEN pm.meta_value END)) as pob, ' +
      '  COALESCE(' +
      '    MAX(CASE WHEN pm.meta_key = ''_billing_nif'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''_billing_cif'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''_billing_dni'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''_billing_vat_number'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''_vat_number'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''billing_nif'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''nif'' THEN pm.meta_value END)' +
      '  ) as nif, ' +
      '  MAX(CASE WHEN pm.meta_key = ''_billing_phone'' THEN pm.meta_value END) as tel, ' +
      '  MAX(CASE WHEN pm.meta_key = ''_billing_email'' THEN pm.meta_value END) as email, ' +
      '  MAX(CASE WHEN pm.meta_key = ''_order_total'' THEN pm.meta_value END) as order_total, ' +
      '  MAX(CASE WHEN pm.meta_key = ''_payment_method_title'' THEN pm.meta_value END) as payment_title ' +
      'FROM wp_posts p ' +
      'LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id ' +
      'WHERE p.post_type = ''shop_order'' AND p.post_status NOT IN (''trash'', ''wc-cancelled'', ''wc-refunded'', ''auto-draft'') ' +
      '  AND p.post_date >= :desde ' +
      'GROUP BY p.ID ' +
      'ORDER BY p.ID DESC';
    LQry.ParamByName('desde').AsString := FormatDateTime('yyyy-mm-dd 00:00:00', LActualDateLimit);
    LQry.Open;
    
    LCount := 0;
    while not LQry.Eof do
    begin
      SetLength(AOrders, LCount + 1);
      AOrders[LCount].OrderId     := LQry.FieldByName('ID').AsLargeInt;
      AOrders[LCount].DateCreated := LQry.FieldByName('post_date').AsDateTime;
      AOrders[LCount].NomCliente  := Trim(LQry.FieldByName('customer_name').AsString);
      AOrders[LCount].Nif         := Trim(LQry.FieldByName('nif').AsString);
      AOrders[LCount].Direccion   := Trim(LQry.FieldByName('address').AsString);
      AOrders[LCount].CP          := Trim(LQry.FieldByName('cp').AsString);
      AOrders[LCount].Poblacion   := Trim(LQry.FieldByName('pob').AsString);
      AOrders[LCount].Telefono    := Trim(LQry.FieldByName('tel').AsString);
      AOrders[LCount].Email       := Trim(LQry.FieldByName('email').AsString);
      AOrders[LCount].WcStatus    := LQry.FieldByName('wc_status').AsString;
      
      // Parsear total considerando configuracion regional
      var LTotalStr := LQry.FieldByName('order_total').AsString;
      LTotalStr := StringReplace(LTotalStr, '.', FormatSettings.DecimalSeparator, []);
      LTotalStr := StringReplace(LTotalStr, ',', FormatSettings.DecimalSeparator, []);
      AOrders[LCount].Total       := StrToFloatDef(LTotalStr, 0);
      AOrders[LCount].MetodoPago  := LQry.FieldByName('payment_title').AsString;
      
      // Comprobar si ya está importado localmente y verificar si cambió el estado
      LRefNum := 'WEB-' + IntToStr(AOrders[LCount].OrderId);
      LLocalQry.Close;
      LLocalQry.SQL.Text := 'SELECT id, estado FROM ge_pedidos WHERE numero_pedido = :num LIMIT 1';
      LLocalQry.ParamByName('num').AsString := LRefNum;
      LLocalQry.Open;
      
      if not LLocalQry.IsEmpty then
      begin
        AOrders[LCount].Importado := True;
        AOrders[LCount].NumPedidoLocal := LRefNum;

        // Comprobar si el estado en WooCommerce exige actualizar ge_pedidos
        var LTargetStatus := 'PENDIENTE';
        if SameText(AOrders[LCount].WcStatus, 'wc-completed') then
          LTargetStatus := 'COMPLETADO'
        else if SameText(AOrders[LCount].WcStatus, 'wc-processing') or SameText(AOrders[LCount].WcStatus, 'wc-on-hold') then
          LTargetStatus := 'PENDIENTE';

        var LEstadoActual := LLocalQry.FieldByName('estado').AsString;
        if not SameText(LEstadoActual, LTargetStatus) then
        begin
          var LUpdateStateQry := TFDQuery.Create(nil);
          try
            LUpdateStateQry.Connection := dmgMain.dbConn;
            LUpdateStateQry.SQL.Text := 'UPDATE ge_pedidos SET estado = :est, id_user_update = :usr WHERE id = :id';
            LUpdateStateQry.ParamByName('est').AsString := LTargetStatus;
            LUpdateStateQry.ParamByName('usr').AsString := dmgMain.CurrentUserId;
            LUpdateStateQry.ParamByName('id').AsString := LLocalQry.FieldByName('id').AsString;
            LUpdateStateQry.ExecSQL;
          finally
            LUpdateStateQry.Free;
          end;
          AOrders[LCount].NumPedidoLocal := LRefNum + ' (Estado actualiz. a ' + LTargetStatus + ')';
        end;
      end
      else
      begin
        AOrders[LCount].Importado := False;
        AOrders[LCount].NumPedidoLocal := '';
      end;
      
      Inc(LCount);
      LQry.Next;
    end;
    
    Result := True;
  except
    on E: Exception do
      AErrorMsg := 'Error al recuperar pedidos web: ' + E.Message;
  end;
  
  LLocalQry.Free;
  LQry.Free;
  LConn.Free;
end;

// ---------------------------------------------------------------------------
class function TWebOrderImporter.FetchOrderDetails(AEmpresaId: Integer; AOrderId: Int64; out AOrder: TWebOrder; var AErrorMsg: string): Boolean;
var
  LConn: TFDConnection;
  LQry: TFDQuery;
  LCount: Integer;
begin
  Result := False;
  AOrder := Default(TWebOrder);
  
  LConn := CreateMySQLConnection(AEmpresaId, AErrorMsg);
  if not Assigned(LConn) then Exit;
  
  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := LConn;
    
    // 1. Recuperar cabecera
    LQry.SQL.Text := 
      'SELECT p.ID, p.post_date, p.post_status as wc_status, ' +
      '  COALESCE(' +
      '    NULLIF(CONCAT(' +
      '      MAX(CASE WHEN pm.meta_key = ''_shipping_first_name'' THEN pm.meta_value END), '' '', ' +
      '      MAX(CASE WHEN pm.meta_key = ''_shipping_last_name'' THEN pm.meta_value END)' +
      '    ), '' ''), ' +
      '    CONCAT(' +
      '      MAX(CASE WHEN pm.meta_key = ''_billing_first_name'' THEN pm.meta_value END), '' '', ' +
      '      MAX(CASE WHEN pm.meta_key = ''_billing_last_name'' THEN pm.meta_value END)' +
      '    )' +
      '  ) as customer_name, ' +
      '  COALESCE(MAX(CASE WHEN pm.meta_key = ''_shipping_address_1'' THEN pm.meta_value END), MAX(CASE WHEN pm.meta_key = ''_billing_address_1'' THEN pm.meta_value END)) as address, ' +
      '  COALESCE(MAX(CASE WHEN pm.meta_key = ''_shipping_postcode'' THEN pm.meta_value END), MAX(CASE WHEN pm.meta_key = ''_billing_postcode'' THEN pm.meta_value END)) as cp, ' +
      '  COALESCE(MAX(CASE WHEN pm.meta_key = ''_shipping_city'' THEN pm.meta_value END), MAX(CASE WHEN pm.meta_key = ''_billing_city'' THEN pm.meta_value END)) as pob, ' +
      '  COALESCE(' +
      '    MAX(CASE WHEN pm.meta_key = ''_billing_nif'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''_billing_cif'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''_billing_dni'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''_billing_vat_number'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''_vat_number'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''billing_nif'' THEN pm.meta_value END), ' +
      '    MAX(CASE WHEN pm.meta_key = ''nif'' THEN pm.meta_value END)' +
      '  ) as nif, ' +
      '  MAX(CASE WHEN pm.meta_key = ''_billing_phone'' THEN pm.meta_value END) as tel, ' +
      '  MAX(CASE WHEN pm.meta_key = ''_billing_email'' THEN pm.meta_value END) as email, ' +
      '  MAX(CASE WHEN pm.meta_key = ''_order_total'' THEN pm.meta_value END) as order_total, ' +
      '  MAX(CASE WHEN pm.meta_key = ''_payment_method_title'' THEN pm.meta_value END) as payment_title ' +
      'FROM wp_posts p ' +
      'LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id ' +
      'WHERE p.ID = :id ' +
      'GROUP BY p.ID';
    LQry.ParamByName('id').AsLargeInt := AOrderId;
    LQry.Open;
    
    if LQry.IsEmpty then
    begin
      AErrorMsg := 'No se encontró el pedido web ID ' + IntToStr(AOrderId);
      Exit;
    end;
    
    AOrder.Header.OrderId     := AOrderId;
    AOrder.Header.DateCreated := LQry.FieldByName('post_date').AsDateTime;
    AOrder.Header.NomCliente  := Trim(LQry.FieldByName('customer_name').AsString);
    AOrder.Header.Nif         := Trim(LQry.FieldByName('nif').AsString);
    AOrder.Header.Direccion   := Trim(LQry.FieldByName('address').AsString);
    AOrder.Header.CP          := Trim(LQry.FieldByName('cp').AsString);
    AOrder.Header.Poblacion   := Trim(LQry.FieldByName('pob').AsString);
    AOrder.Header.Telefono    := Trim(LQry.FieldByName('tel').AsString);
    AOrder.Header.Email       := Trim(LQry.FieldByName('email').AsString);
    AOrder.Header.WcStatus    := LQry.FieldByName('wc_status').AsString;
    
    var LTotalStr := LQry.FieldByName('order_total').AsString;
    LTotalStr := StringReplace(LTotalStr, '.', FormatSettings.DecimalSeparator, []);
    LTotalStr := StringReplace(LTotalStr, ',', FormatSettings.DecimalSeparator, []);
    AOrder.Header.Total       := StrToFloatDef(LTotalStr, 0);
    AOrder.Header.MetodoPago  := LQry.FieldByName('payment_title').AsString;
    
    // 2. Recuperar líneas de artículos
    LQry.Close;
    LQry.SQL.Text := 
      'SELECT oi.order_item_id, oi.order_item_name, ' +
      '  MAX(CASE WHEN oim.meta_key = ''_product_id'' THEN oim.meta_value END) as product_id, ' +
      '  MAX(CASE WHEN oim.meta_key = ''_qty'' THEN oim.meta_value END) as qty, ' +
      '  MAX(CASE WHEN oim.meta_key = ''_line_subtotal'' THEN oim.meta_value END) as line_subtotal, ' +
      '  MAX(CASE WHEN oim.meta_key = ''_line_total'' THEN oim.meta_value END) as line_total ' +
      'FROM wp_woocommerce_order_items oi ' +
      'JOIN wp_woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id ' +
      'WHERE oi.order_id = :id AND oi.order_item_type = ''line_item'' ' +
      'GROUP BY oi.order_item_id';
    LQry.ParamByName('id').AsLargeInt := AOrderId;
    LQry.Open;
    
    LCount := 0;
    while not LQry.Eof do
    begin
      SetLength(AOrder.Lines, LCount + 1);
      AOrder.Lines[LCount].ProductId   := StrToInt64Def(LQry.FieldByName('product_id').AsString, 0);
      AOrder.Lines[LCount].Description := LQry.FieldByName('order_item_name').AsString;
      
      var LQtyStr := LQry.FieldByName('qty').AsString;
      LQtyStr := StringReplace(LQtyStr, '.', FormatSettings.DecimalSeparator, []);
      AOrder.Lines[LCount].Qty         := StrToFloatDef(LQtyStr, 0);
      
      // WooCommerce subtotal / total
      var LSubStr := LQry.FieldByName('line_subtotal').AsString;
      if LSubStr = '' then LSubStr := LQry.FieldByName('line_total').AsString;
      LSubStr := StringReplace(LSubStr, '.', FormatSettings.DecimalSeparator, []);
      AOrder.Lines[LCount].Total       := StrToFloatDef(LSubStr, 0);
      
      if AOrder.Lines[LCount].Qty > 0 then
        AOrder.Lines[LCount].Price     := AOrder.Lines[LCount].Total / AOrder.Lines[LCount].Qty
      else
        AOrder.Lines[LCount].Price     := 0;
        
      Inc(LCount);
      LQry.Next;
    end;
    
    Result := True;
  except
    on E: Exception do
      AErrorMsg := 'Error al recuperar detalles del pedido web: ' + E.Message;
  end;
  
  LQry.Free;
  LConn.Free;
end;

// ---------------------------------------------------------------------------
class function TWebOrderImporter.ImportOrder(const AOrder: TWebOrder; AEmpresaId: Integer; var AErrorMsg: string): Int64;
var
  LQry: TFDQuery;
  LClienteId: string;
  LDestinoId: string;
  LNewPedidoId: Int64;
  LRefNum: string;
  i: Integer;
  LBase, LIva, LTotal: Double;
  LArtId: string;
begin
  Result := 0;
  AErrorMsg := '';
  LRefNum := 'WEB-' + IntToStr(AOrder.Header.OrderId);
  
  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;
    
    // 1. Evitar duplicados locales
    LQry.SQL.Text := 'SELECT id FROM ge_pedidos WHERE numero_pedido = :num';
    LQry.ParamByName('num').AsString := LRefNum;
    LQry.Open;
    if not LQry.IsEmpty then
    begin
      AErrorMsg := 'El pedido ' + LRefNum + ' ya ha sido importado previamente.';
      Exit;
    end;
    
    dmgMain.dbConn.StartTransaction;
    try
      // 2. Buscar o crear el cliente web general
      var LNifTrim := Trim(AOrder.Header.Nif);
      var LNomTrim := Trim(AOrder.Header.NomCliente);
      var LEmailTrim := Trim(AOrder.Header.Email);

      LQry.Close;
      LQry.SQL.Text := 
        'SELECT id, nif FROM ge_clientes ' +
        'WHERE (:nif <> '''' AND nif = :nif) ' +
        '   OR (:email <> '''' AND LOWER(email) = LOWER(:email)) ' +
        '   OR (LOWER(nombre_fiscal) = LOWER(:nom)) ' +
        '   OR (LOWER(nombre_comercial) = LOWER(:nom)) ' +
        'LIMIT 1';
      LQry.ParamByName('nif').AsString := LNifTrim;
      LQry.ParamByName('nom').AsString := LNomTrim;
      LQry.ParamByName('email').AsString := LEmailTrim;
      LQry.Open;
      
      if not LQry.IsEmpty then
      begin
        LClienteId := LQry.FieldByName('id').AsString;
        // Si el cliente existente no tenía NIF registrado pero el pedido nos aporta uno, actualizarlo
        if (Trim(LQry.FieldByName('nif').AsString) = '') and (LNifTrim <> '') then
        begin
          var LUpdateNifQry := TFDQuery.Create(nil);
          try
            LUpdateNifQry.Connection := dmgMain.dbConn;
            LUpdateNifQry.SQL.Text := 'UPDATE ge_clientes SET nif = :nif WHERE id = :id';
            LUpdateNifQry.ParamByName('nif').AsString := LNifTrim;
            LUpdateNifQry.ParamByName('id').AsString := LClienteId;
            try
              LUpdateNifQry.ExecSQL;
            except
              // Si otro cliente ya tenía ese NIF en la BD, ignorar fallo leve de actualización
            end;
          finally
            LUpdateNifQry.Free;
          end;
        end;
      end
      else
      begin
        // Crear nuevo cliente registrando su NIF/CIF (pasando NULL si está vacío para evitar Duplicate entry '' for key nif_UNIQUE)
        LQry.Close;
        LQry.SQL.Text := 
          'INSERT INTO ge_clientes (nif, nombre_fiscal, nombre_comercial, domicilio, poblacion, codigo_postal, telefono, email, activo, id_user_creator) ' +
          'VALUES (:nif, :nom, :nom, :dom, :pob, :cp, :tel, :email, 1, :user)';
          
        LQry.ParamByName('nif').DataType := ftString;
        if LNifTrim <> '' then
          LQry.ParamByName('nif').AsString := LNifTrim
        else
          LQry.ParamByName('nif').Clear; // Pasar NULL para evitar violar nif_UNIQUE en MySQL
          
        LQry.ParamByName('nom').AsString := LNomTrim;
        LQry.ParamByName('dom').AsString := AOrder.Header.Direccion;
        LQry.ParamByName('pob').AsString := AOrder.Header.Poblacion;
        LQry.ParamByName('cp').AsString := AOrder.Header.CP;
        LQry.ParamByName('tel').AsString := AOrder.Header.Telefono;
        
        LQry.ParamByName('email').DataType := ftString;
        if LEmailTrim <> '' then
          LQry.ParamByName('email').AsString := LEmailTrim
        else
          LQry.ParamByName('email').Clear;
          
        LQry.ParamByName('user').AsString := dmgMain.CurrentUserId;
        LQry.ExecSQL;
        
        // Obtener el ID del nuevo cliente
        LQry.Close;
        LQry.SQL.Text := 'SELECT LAST_INSERT_ID()';
        LQry.Open;
        LClienteId := LQry.Fields[0].AsString;
      end;
      
      // 3. Crear dirección de destino del cliente si no existe
      LQry.Close;
      LQry.SQL.Text := 'SELECT id FROM ge_clientes_destinos WHERE id_cliente = :cli AND direccion = :dir LIMIT 1';
      LQry.ParamByName('cli').AsString := LClienteId;
      LQry.ParamByName('dir').AsString := AOrder.Header.Direccion;
      LQry.Open;
      
      if not LQry.IsEmpty then
      begin
        LDestinoId := LQry.FieldByName('id').AsString;
      end
      else
      begin
        // Crear nuevo destino
        LQry.Close;
        LQry.SQL.Text := 
          'INSERT INTO ge_clientes_destinos (id_cliente, descripcion, direccion, poblacion, cp, activo, id_user_creator) ' +
          'VALUES (:cli, :desc, :dir, :pob, :cp, 1, :user)';
        LQry.ParamByName('cli').AsString := LClienteId;
        LQry.ParamByName('desc').AsString := 'Dirección de Entrega Web';
        LQry.ParamByName('dir').AsString := AOrder.Header.Direccion;
        LQry.ParamByName('pob').AsString := AOrder.Header.Poblacion;
        LQry.ParamByName('cp').AsString := AOrder.Header.CP;
        LQry.ParamByName('user').AsString := dmgMain.CurrentUserId;
        LQry.ExecSQL;
        
        LQry.Close;
        LQry.SQL.Text := 'SELECT LAST_INSERT_ID()';
        LQry.Open;
        LDestinoId := LQry.Fields[0].AsString;
      end;
      
      // 4. Calcular importes totales
      LBase := 0;
      for i := 0 to Length(AOrder.Lines) - 1 do
        LBase := LBase + AOrder.Lines[i].Total;
        
      LIva := LBase * 0.10; // IVA 10% estándar alimentación
      LTotal := LBase + LIva;
      
      // 4.5 Comprobar si la columna origen existe en ge_pedidos
      var LHasOrigen := False;
      LQry.Close;
      LQry.SQL.Text := 'SELECT COUNT(*) FROM information_schema.columns WHERE table_name = ''ge_pedidos'' AND column_name = ''origen'' AND table_schema = DATABASE()';
      LQry.Open;
      if not LQry.IsEmpty and (LQry.Fields[0].AsInteger > 0) then
        LHasOrigen := True;
      
      var LEstadoLocal := 'PENDIENTE';
      if SameText(AOrder.Header.WcStatus, 'wc-completed') then
        LEstadoLocal := 'COMPLETADO'
      else if SameText(AOrder.Header.WcStatus, 'wc-processing') then
        LEstadoLocal := 'PENDIENTE';

      // 5. Insertar cabecera del pedido
      LQry.Close;
      if LHasOrigen then
      begin
        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, :fecha, DATE_ADD(:fecha, INTERVAL 1 DAY), :estado, :base, :total, :iva, :obs, ''WEB'', :user, :user)';
      end
      else
      begin
        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, :fecha, DATE_ADD(:fecha, INTERVAL 1 DAY), :estado, :base, :total, :iva, :obs, :user, :user)';
      end;
      LQry.ParamByName('emp').AsInteger   := AEmpresaId;
      LQry.ParamByName('cli').AsString    := LClienteId;
      LQry.ParamByName('dest').AsString   := LDestinoId;
      LQry.ParamByName('num').AsString    := LRefNum;
      LQry.ParamByName('fecha').AsDateTime := AOrder.Header.DateCreated;
      LQry.ParamByName('estado').AsString := LEstadoLocal;
      LQry.ParamByName('base').AsFloat    := LBase;
      LQry.ParamByName('iva').AsFloat     := LIva;
      LQry.ParamByName('total').AsFloat   := LTotal;
      LQry.ParamByName('obs').AsString    := 'Importado de WooCommerce. Pago: ' + AOrder.Header.MetodoPago;
      LQry.ParamByName('user').AsString   := dmgMain.CurrentUserId;
      LQry.ExecSQL;
      
      // Obtener el ID del pedido local insertado
      LQry.Close;
      LQry.SQL.Text := 'SELECT LAST_INSERT_ID()';
      LQry.Open;
      LNewPedidoId := LQry.Fields[0].AsLargeInt;
      
      // 6. Insertar líneas del pedido
      for i := 0 to Length(AOrder.Lines) - 1 do
      begin
        // Buscar el id_articulo: 1º en ge_articulos_codigos_web, 2º en ge_articulos, 3º por descripcion
        LArtId := '';
        var LProdIdStr := IntToStr(AOrder.Lines[i].ProductId);
        
        // 1º Intentar en la nueva tabla multi-código web
        LQry.Close;
        LQry.SQL.Text := 'SELECT id_articulo FROM ge_articulos_codigos_web WHERE codigo_web = :codweb LIMIT 1';
        LQry.ParamByName('codweb').AsString := LProdIdStr;
        try
          LQry.Open;
          if not LQry.IsEmpty then
            LArtId := LQry.FieldByName('id_articulo').AsString;
        except
          // Si la tabla aún no se ha creado físicamente, continuar con fallback
          LArtId := '';
        end;
        
        // 2º Fallback a la columna legacy ge_articulos.codigo_web
        if LArtId = '' then
        begin
          LQry.Close;
          LQry.SQL.Text := 'SELECT id FROM ge_articulos WHERE codigo_web = :codweb LIMIT 1';
          LQry.ParamByName('codweb').AsString := LProdIdStr;
          LQry.Open;
          if not LQry.IsEmpty then
            LArtId := LQry.FieldByName('id').AsString;
        end;
        
        // 3º Fallback: buscar por descripción exacta en ge_articulos
        if LArtId = '' then
        begin
          LQry.Close;
          LQry.SQL.Text := 'SELECT id FROM ge_articulos WHERE descripcion = :desc LIMIT 1';
          LQry.ParamByName('desc').AsString := AOrder.Lines[i].Description;
          LQry.Open;
          if not LQry.IsEmpty then
          begin
            LArtId := LQry.FieldByName('id').AsString;
            // Registrar inmediatamente la correspondencia en la tabla multi-código web
            AssignWebProduct(AOrder.Lines[i].ProductId, LArtId, AOrder.Lines[i].Description);
          end;
        end;

        // Si tras todas las búsquedas sigue sin estar mapeado
        if LArtId = '' then
        begin
          var LSelectedId: string := '';
          ShowMessage('El artículo de la web "' + AOrder.Lines[i].Description + '" (ID: ' + LProdIdStr + ') no está mapeado.' + #13#10 +
                      'Seleccione el artículo de la pastelería correspondiente.');
                      
          var LFrmSelect := TfrmSelectProduct.Create(nil);
          try
            LFrmSelect.MultiSelect := False;
            if LFrmSelect.ShowModal = mrOk then
            begin
              LSelectedId := LFrmSelect.SelectedId;
            end;
          finally
            LFrmSelect.Free;
          end;
          
          if LSelectedId <> '' then
          begin
            // Guardar la correspondencia en la tabla multi-código web
            AssignWebProduct(AOrder.Lines[i].ProductId, LSelectedId, AOrder.Lines[i].Description);
            LArtId := LSelectedId;
          end
          else
          begin
            raise Exception.Create('No se seleccionó ningún artículo para asociar con "' + AOrder.Lines[i].Description + '". Importación cancelada.');
          end;
        end;
          
        LQry.Close;
        LQry.SQL.Text := 
          'INSERT INTO ge_pedidos_lineas (id_pedido, id_articulo, descripcion, cantidad, precio, total) ' +
          'VALUES (:ped, :art, :desc, :qty, :precio, :tot)';
        LQry.ParamByName('ped').AsLargeInt := LNewPedidoId;
        LQry.ParamByName('art').AsString    := LArtId;
        LQry.ParamByName('desc').AsString   := AOrder.Lines[i].Description;
        LQry.ParamByName('qty').AsFloat     := AOrder.Lines[i].Qty;
        LQry.ParamByName('precio').AsFloat  := AOrder.Lines[i].Price;
        LQry.ParamByName('tot').AsFloat     := AOrder.Lines[i].Total;
        LQry.ExecSQL;
      end;
      
      dmgMain.dbConn.Commit;
      Result := LNewPedidoId;
    except
      on E: Exception do
      begin
        dmgMain.dbConn.Rollback;
        AErrorMsg := 'Error al insertar pedido local: ' + E.Message;
      end;
    end;
  finally
    LQry.Free;
  end;
end;

// ---------------------------------------------------------------------------
class function TWebOrderImporter.FetchUnmatchedProducts(AEmpresaId: Integer; out AProducts: TArray<TWebProduct>; var AErrorMsg: string): Boolean;
var
  LConn: TFDConnection;
  LQry, LLocalQry: TFDQuery;
  LCount: Integer;
  LProdId: string;
begin
  Result := False;
  SetLength(AProducts, 0);
  
  LConn := CreateMySQLConnection(AEmpresaId, AErrorMsg);
  if not Assigned(LConn) then Exit;
  
  LQry := TFDQuery.Create(nil);
  LLocalQry := TFDQuery.Create(nil);
  try
    LQry.Connection := LConn;
    LLocalQry.Connection := dmgMain.dbConn;
    
    // Obtener todos los product_id distintos de pedidos
    LQry.SQL.Text :=
      'SELECT DISTINCT ' +
      '  MAX(CASE WHEN oim.meta_key = ''_product_id'' THEN oim.meta_value END) as product_id, ' +
      '  oi.order_item_name ' +
      'FROM wp_woocommerce_order_items oi ' +
      'JOIN wp_woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id ' +
      'JOIN wp_posts p ON oi.order_id = p.ID ' +
      'WHERE p.post_type = ''shop_order'' AND p.post_status NOT IN (''trash'', ''wc-cancelled'', ''wc-refunded'', ''auto-draft'') ' +
      '  AND oi.order_item_type = ''line_item'' ' +
      'GROUP BY oi.order_item_name ' +
      'ORDER BY oi.order_item_name';
    LQry.Open;
    
    LCount := 0;
    while not LQry.Eof do
    begin
      LProdId := LQry.FieldByName('product_id').AsString;
      
      // Comprobar si tiene vinculación local 1º en ge_articulos_codigos_web y 2º en ge_articulos
      var LFound := False;
      LLocalQry.Close;
      LLocalQry.SQL.Text := 'SELECT id_articulo FROM ge_articulos_codigos_web WHERE codigo_web = :codweb LIMIT 1';
      LLocalQry.ParamByName('codweb').AsString := LProdId;
      try
        LLocalQry.Open;
        LFound := not LLocalQry.IsEmpty;
      except
        LFound := False;
      end;
      
      if not LFound then
      begin
        LLocalQry.Close;
        LLocalQry.SQL.Text := 'SELECT id FROM ge_articulos WHERE codigo_web = :codweb LIMIT 1';
        LLocalQry.ParamByName('codweb').AsString := LProdId;
        LLocalQry.Open;
        LFound := not LLocalQry.IsEmpty;
      end;
      
      if not LFound then
      begin
        // Producto no vinculado
        SetLength(AProducts, LCount + 1);
        AProducts[LCount].ProductId := StrToInt64Def(LProdId, 0);
        AProducts[LCount].ProductName := LQry.FieldByName('order_item_name').AsString;
        AProducts[LCount].LocalArticuloId := '';
        Inc(LCount);
      end;
      
      LQry.Next;
    end;
    
    Result := True;
  except
    on E: Exception do
      AErrorMsg := 'Error al comprobar productos web: ' + E.Message;
  end;
  
  LLocalQry.Free;
  LQry.Free;
  LConn.Free;
end;

// ---------------------------------------------------------------------------
class procedure TWebOrderImporter.AssignWebProduct(AProductId: Int64; ALocalArticuloId: string; ADescWeb: string = '');
var
  LQry: TFDQuery;
begin
  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;
    // 1. Guardar en la nueva tabla multi-código web ge_articulos_codigos_web
    try
      LQry.SQL.Text := 
        'INSERT INTO ge_articulos_codigos_web (id_articulo, codigo_web, descripcion_web) ' +
        'VALUES (:id, :codweb, :desc) ' +
        'ON DUPLICATE KEY UPDATE id_articulo = VALUES(id_articulo), descripcion_web = COALESCE(VALUES(descripcion_web), descripcion_web)';
      LQry.ParamByName('id').AsString := ALocalArticuloId;
      LQry.ParamByName('codweb').AsString := IntToStr(AProductId);
      if ADescWeb <> '' then
        LQry.ParamByName('desc').AsString := ADescWeb
      else
        LQry.ParamByName('desc').Clear;
      LQry.ExecSQL;
    except
      // Si la tabla no se ha creado aún en la BD, ignorar fallo leve
    end;

    // 2. Mantener actualizado ge_articulos.codigo_web por retrocompatibilidad
    LQry.Close;
    LQry.SQL.Text := 'UPDATE ge_articulos SET codigo_web = :codweb WHERE id = :id';
    LQry.ParamByName('codweb').AsString := IntToStr(AProductId);
    LQry.ParamByName('id').AsString := ALocalArticuloId;
    LQry.ExecSQL;
  finally
    LQry.Free;
  end;
end;

end.