unit frm_StockEnvasadoEditor;
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,
  Data.DB, FireDAC.Comp.Client, FireDAC.Stan.Param, uAppTheme, uBaseForm,
  Vcl.Grids, Vcl.DBGrids, Vcl.ComCtrls, Vcl.DBCtrls, Vcl.Mask, System.Generics.Collections,
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.DatS,
  FireDAC.Phys.Intf, FireDAC.DApt.Intf, FireDAC.Stan.Async, FireDAC.DApt,
  FireDAC.Comp.DataSet, System.DateUtils;
type
  TfrmStockEnvasadoEditor = class(TfrmBase)
    qMaster: TFDQuery;
    dsMaster: TDataSource;
    qAlmacenes: TFDQuery;
    dsAlmacenes: TDataSource;
    qArticulos: TFDQuery;
    dsArticulos: TDataSource;
    qLotes: TFDQuery;
    dsLotes: TDataSource;
    qFormatos: TFDQuery;
    dsFormatos: TDataSource;
    lblAlmacen: TLabel;
    dblcAlmacen: TDBLookupComboBox;
    lblArticulo: TLabel;
    dblcArticulo: TDBLookupComboBox;
    btnBuscarProd: TButton;
    lblLote: TLabel;
    dblcLote: TDBLookupComboBox;
    lblFormato: TLabel;
    dblcFormato: TDBLookupComboBox;
    lblCantidadProducida: TLabel;
    dbeCantidadProducida: TDBEdit;
    lblCantidad: TLabel;
    dbeCantidad: TDBEdit;
    lblFechaElaboracion: TLabel;
    lblFechaCaducidad: TLabel;
    dtpFechaElaboracion: TDateTimePicker;
    dtpFechaCaducidad: TDateTimePicker;
    tsTrazabilidadVentas: TTabSheet;
    pnlTrazabilidadTop: TPanel;
    lblResumenTrazabilidad: TLabel;
    btnVerDocumento: TButton;
    btnActualizarTraz: TButton;
    dbgTrazabilidadVentas: TDBGrid;
    qTrazabilidadVentas: TFDQuery;
    dsTrazabilidadVentas: TDataSource;
    procedure FormShow(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure btnSalirClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btnBuscarProdClick(Sender: TObject);
    procedure dblcLoteCloseUp(Sender: TObject);
    procedure btnVerDocumentoClick(Sender: TObject);
    procedure btnActualizarTrazClick(Sender: TObject);
    procedure dbgTrazabilidadVentasDblClick(Sender: TObject);
    procedure dbgTrazabilidadVentasTitleClick(Column: TColumn);
    procedure pgcDetailsChange(Sender: TObject);
    procedure dbeCantidadProducidaExit(Sender: TObject);
    procedure dtpFechaElaboracionChange(Sender: TObject);
    procedure dtpFechaCaducidadChange(Sender: TObject);
  protected
    procedure Loaded; override;
    function IsNewRecord: Boolean; override;
    procedure DoAnadir; override;
    procedure DoModificar; override;
    procedure DoPrimero; override;
    procedure DoAnterior; override;
    procedure DoSiguiente; override;
    procedure DoUltimo; override;
  private
    FStockId: Integer;
    FParentForm: TForm;
    FIsNewRecord: Boolean;
    FTrazabilidadLoaded: Boolean;
    FOpenTraceabilityTab: Boolean;
    FUpdatingDates: Boolean;
    procedure MasterIdLoteChange(Sender: TField);
    procedure LoadData;
    procedure CargarLookups;
    procedure SetupTrazabilidadGrid;
    procedure CargarTrazabilidadVentas;
    procedure CargarFechasLote;
    function ObtenerDiasVidaArticulo: Integer;
  public
    property StockId: Integer read FStockId write FStockId;
    property ParentForm: TForm read FParentForm write FParentForm;
    property OpenTraceabilityTab: Boolean read FOpenTraceabilityTab write FOpenTraceabilityTab;
  end;
var
  frmStockEnvasadoEditor: TfrmStockEnvasadoEditor;
implementation
uses
  dmg_Main, uDbErrorHandler, frm_StockEnvasado, frm_SelectProduct, frm_AlbaranesEditor, frm_FacturasEditor;
{$R *.dfm}
procedure TfrmStockEnvasadoEditor.Loaded;
begin
  inherited Loaded;
  qMaster.SQL.Text := 'SELECT * FROM ge_stock_envasado WHERE id = :id';
end;
procedure TfrmStockEnvasadoEditor.FormShow(Sender: TObject);
begin
  TAppTheme.ApplyToForm(Self);
  btnGuardar.OnClick := btnSaveClick;
  btnSalir.OnClick := btnSalirClick;
  pgcDetails.Visible := True;
  TsListado.TabVisible := False;
  if FOpenTraceabilityTab then
    pgcDetails.ActivePage := tsTrazabilidadVentas
  else
    pgcDetails.ActivePage := tsDatosEnvio;
  pgcDetails.OnChange := pgcDetailsChange;
  pnlSideToolbar.Visible := False;
  dbgItems.Visible := False;
  pnlFooter.Visible := False;
  CargarLookups;
  if FStockId > 0 then
    LoadData
  else
    DoAnadir;
end;
procedure TfrmStockEnvasadoEditor.FormDestroy(Sender: TObject);
begin
  if frmStockEnvasadoEditor = Self then
    frmStockEnvasadoEditor := nil;
end;
procedure TfrmStockEnvasadoEditor.CargarLookups;
begin
  qAlmacenes.Close;
  qAlmacenes.SQL.Text := 'SELECT id, descripcion FROM ge_almacenes WHERE (id_empresa = :emp OR compartido = 1) AND activo = 1 ORDER BY descripcion';
  qAlmacenes.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
  qAlmacenes.Open;
  qArticulos.Close;
  qArticulos.SQL.Text := 'SELECT id, descripcion FROM ge_articulos WHERE activo = 1 ORDER BY descripcion';
  qArticulos.Open;
  qLotes.Close;
  qLotes.SQL.Text := 'SELECT l.id, l.id_articulo, ' +
                     'CONCAT(COALESCE(NULLIF(l.codigo_antiguo, ''''), l.codigo), '' - '', COALESCE(a.descripcion, ''''), '' - '', COALESCE(o.nombre, '''')) as lote_desc ' +
                     'FROM ge_lotes l ' +
                     'LEFT JOIN ge_obradores o ON l.id_obrador = o.id ' +
                     'LEFT JOIN ge_articulos a ON l.id_articulo = a.id ' +
                     'ORDER BY l.created_at DESC';
  qLotes.Open;
  // Asignar evento de selección de lote
  dblcLote.OnCloseUp := dblcLoteCloseUp;
  qFormatos.Close;
  qFormatos.SQL.Text := 'SELECT id, descripcion FROM ge_formatos_presentacion ORDER BY descripcion';
  qFormatos.Open;
end;
procedure TfrmStockEnvasadoEditor.MasterIdLoteChange(Sender: TField);
var
  LLoteId, LArticuloId: Integer;
begin
  if Sender.DataSet.State in [dsEdit, dsInsert] then
  begin
    LLoteId := Sender.AsInteger;
    if (LLoteId > 0) and qLotes.Active and qLotes.Locate('id', LLoteId, []) then
    begin
      LArticuloId := qLotes.FieldByName('id_articulo').AsInteger;
      if (LArticuloId > 0) and (Sender.DataSet.FieldByName('id_articulo').AsInteger <> LArticuloId) then
        Sender.DataSet.FieldByName('id_articulo').AsInteger := LArticuloId;
    end;
  end;
  CargarFechasLote;
end;

procedure TfrmStockEnvasadoEditor.dbeCantidadProducidaExit(Sender: TObject);
var
  LCantProd: Double;
begin
  if not (qMaster.State in [dsEdit, dsInsert]) then Exit;
  LCantProd := qMaster.FieldByName('cantidad_producida').AsFloat;
  
  // Si es un nuevo registro o la cantidad física está en 0, arrastrar automáticamente la cantidad fabricada
  if FIsNewRecord or (qMaster.FieldByName('cantidad_unidades').AsFloat = 0.0) then
  begin
    qMaster.FieldByName('cantidad_unidades').AsFloat := LCantProd;
  end;
end;

function TfrmStockEnvasadoEditor.ObtenerDiasVidaArticulo: Integer;
var
  LQry: TFDQuery;
  LArtId: Integer;
begin
  Result := 0;
  LArtId := qMaster.FieldByName('id_articulo').AsInteger;
  if (LArtId <= 0) and (qMaster.FieldByName('id_lote').AsInteger > 0) then
  begin
    LQry := TFDQuery.Create(nil);
    try
      LQry.Connection := dmgMain.dbConn;
      LQry.SQL.Text := 'SELECT id_articulo FROM ge_lotes WHERE id = :lote_id';
      LQry.ParamByName('lote_id').AsInteger := qMaster.FieldByName('id_lote').AsInteger;
      LQry.Open;
      if not LQry.IsEmpty then
        LArtId := LQry.FieldByName('id_articulo').AsInteger;
    finally
      LQry.Free;
    end;
  end;
  
  if LArtId > 0 then
  begin
    LQry := TFDQuery.Create(nil);
    try
      LQry.Connection := dmgMain.dbConn;
      LQry.SQL.Text := 'SELECT COALESCE(dias_vida, 0) as dias_vida FROM ge_articulos WHERE id = :id';
      LQry.ParamByName('id').AsInteger := LArtId;
      LQry.Open;
      if not LQry.IsEmpty then
        Result := LQry.FieldByName('dias_vida').AsInteger;
    finally
      LQry.Free;
    end;
  end;
end;

procedure TfrmStockEnvasadoEditor.CargarFechasLote;
var
  LQry: TFDQuery;
  LLoteId, LArtId, LDiasVida: Integer;
begin
  FUpdatingDates := True;
  try
    LLoteId := qMaster.FieldByName('id_lote').AsInteger;
    LArtId := qMaster.FieldByName('id_articulo').AsInteger;
    
    dtpFechaElaboracion.Date := Date;
    dtpFechaCaducidad.Date := Date;
    dtpFechaCaducidad.Checked := False;
    
    if LLoteId > 0 then
    begin
      LQry := TFDQuery.Create(nil);
      try
        LQry.Connection := dmgMain.dbConn;
        LQry.SQL.Text :=
          'SELECT l.fecha, l.id_articulo, COALESCE(a.dias_vida, 0) as dias_vida ' +
          'FROM ge_lotes l ' +
          'LEFT JOIN ge_articulos a ON a.id = CASE WHEN :art_id > 0 THEN :art_id2 ELSE l.id_articulo END ' +
          'WHERE l.id = :lote_id ' +
          'LIMIT 1';
        LQry.ParamByName('art_id').AsInteger := LArtId;
        LQry.ParamByName('art_id2').AsInteger := LArtId;
        LQry.ParamByName('lote_id').AsInteger := LLoteId;
        LQry.Open;
        if not LQry.IsEmpty then
        begin
          if not LQry.FieldByName('fecha').IsNull then
            dtpFechaElaboracion.Date := LQry.FieldByName('fecha').AsDateTime
          else
            dtpFechaElaboracion.Date := Date;
            
          LDiasVida := LQry.FieldByName('dias_vida').AsInteger;
          if LDiasVida > 0 then
          begin
            dtpFechaCaducidad.Checked := True;
            dtpFechaCaducidad.Date := IncDay(dtpFechaElaboracion.Date, LDiasVida);
          end
          else
          begin
            dtpFechaCaducidad.Checked := False;
            dtpFechaCaducidad.Date := dtpFechaElaboracion.Date;
          end;
        end;
      finally
        LQry.Free;
      end;
    end;
  finally
    FUpdatingDates := False;
  end;
end;

procedure TfrmStockEnvasadoEditor.dtpFechaElaboracionChange(Sender: TObject);
var
  LDiasVida: Integer;
begin
  if FUpdatingDates then Exit;
  FUpdatingDates := True;
  try
    LDiasVida := ObtenerDiasVidaArticulo;
    if dtpFechaCaducidad.Checked and (LDiasVida > 0) then
      dtpFechaCaducidad.Date := IncDay(dtpFechaElaboracion.Date, LDiasVida);
  finally
    FUpdatingDates := False;
  end;
  ControlChanged(Sender);
end;

procedure TfrmStockEnvasadoEditor.dtpFechaCaducidadChange(Sender: TObject);
begin
  if FUpdatingDates then Exit;
  ControlChanged(Sender);
end;

procedure TfrmStockEnvasadoEditor.LoadData;
begin
  FTrazabilidadLoaded := False;
  qMaster.Close;
  qMaster.ParamByName('id').AsInteger := FStockId;
  qMaster.Open;
  qMaster.FieldByName('id_lote').OnChange := MasterIdLoteChange;
  
  if not qMaster.IsEmpty then
  begin
    FIsNewRecord := False;
    CargarFechasLote;
    ResetChangeTracking;
    ResetModifiedState;
  end;

  if pgcDetails.ActivePage = tsTrazabilidadVentas then
    CargarTrazabilidadVentas;
end;

function TfrmStockEnvasadoEditor.IsNewRecord: Boolean;
begin
  Result := FIsNewRecord;
end;

procedure TfrmStockEnvasadoEditor.DoAnadir;
begin
  FIsNewRecord := True;
  FStockId := 0;
  FTrazabilidadLoaded := False;
  
  qMaster.Close;
  qMaster.ParamByName('id').AsInteger := 0;
  qMaster.Open;
  qMaster.FieldByName('id_lote').OnChange := MasterIdLoteChange;
  qMaster.Append;
  
  if qMaster.FindField('cantidad_producida') <> nil then
    qMaster.FieldByName('cantidad_producida').AsFloat := 0.0;
  qMaster.FieldByName('cantidad_unidades').AsFloat := 0.0;
  if qMaster.FindField('cantidad_reservada') <> nil then
    qMaster.FieldByName('cantidad_reservada').AsFloat := 0.0;
  
  if qAlmacenes.Active and not qAlmacenes.IsEmpty then
    qMaster.FieldByName('id_almacen').AsInteger := qAlmacenes.FieldByName('id').AsInteger;
    
  if qLotes.Active and not qLotes.IsEmpty then
  begin
    qMaster.FieldByName('id_lote').AsInteger := qLotes.FieldByName('id').AsInteger;
    if not qLotes.FieldByName('id_articulo').IsNull and (qLotes.FieldByName('id_articulo').AsInteger > 0) then
      qMaster.FieldByName('id_articulo').AsInteger := qLotes.FieldByName('id_articulo').AsInteger;
  end
  else if qArticulos.Active and not qArticulos.IsEmpty then
    qMaster.FieldByName('id_articulo').AsInteger := qArticulos.FieldByName('id').AsInteger;

  if qFormatos.Active and not qFormatos.IsEmpty then
    qMaster.FieldByName('id_formato_presentacion').AsInteger := qFormatos.FieldByName('id').AsInteger;
  CargarFechasLote;
  ResetChangeTracking;
  ResetModifiedState;

  if pgcDetails.ActivePage = tsTrazabilidadVentas then
  begin
    if Assigned(qTrazabilidadVentas) then qTrazabilidadVentas.Close;
    lblResumenTrazabilidad.Caption := 'Nuevo registro: No hay documentos de venta asociados.';
  end;

  dblcAlmacen.SetFocus;
end;
procedure TfrmStockEnvasadoEditor.DoModificar;
begin
  if (qMaster.Active) and (not qMaster.IsEmpty) and (qMaster.State = dsBrowse) then
    qMaster.Edit;
  dblcAlmacen.SetFocus;
end;
procedure TfrmStockEnvasadoEditor.DoPrimero;
var
  LParent: TfrmStockEnvasado;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmStockEnvasado) then
    LParent := TfrmStockEnvasado(FParentForm)
  else if Assigned(frmStockEnvasado) then
    LParent := frmStockEnvasado;
  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.First;
    FStockId := LParent.QryMain.FieldByName('id').AsInteger;
    LoadData;
  end;
end;
procedure TfrmStockEnvasadoEditor.DoAnterior;
var
  LParent: TfrmStockEnvasado;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmStockEnvasado) then
    LParent := TfrmStockEnvasado(FParentForm)
  else if Assigned(frmStockEnvasado) then
    LParent := frmStockEnvasado;
  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.Prior;
    FStockId := LParent.QryMain.FieldByName('id').AsInteger;
    LoadData;
  end;
end;
procedure TfrmStockEnvasadoEditor.DoSiguiente;
var
  LParent: TfrmStockEnvasado;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmStockEnvasado) then
    LParent := TfrmStockEnvasado(FParentForm)
  else if Assigned(frmStockEnvasado) then
    LParent := frmStockEnvasado;
  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.Next;
    FStockId := LParent.QryMain.FieldByName('id').AsInteger;
    LoadData;
  end;
end;
procedure TfrmStockEnvasadoEditor.DoUltimo;
var
  LParent: TfrmStockEnvasado;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmStockEnvasado) then
    LParent := TfrmStockEnvasado(FParentForm)
  else if Assigned(frmStockEnvasado) then
    LParent := frmStockEnvasado;
  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.Last;
    FStockId := LParent.QryMain.FieldByName('id').AsInteger;
    LoadData;
  end;
end;
procedure TfrmStockEnvasadoEditor.btnSalirClick(Sender: TObject);
begin
  if qMaster.State in [dsInsert, dsEdit] then
    qMaster.Cancel;
  Close;
end;
procedure TfrmStockEnvasadoEditor.btnSaveClick(Sender: TObject);
var
  LIsNew: Boolean;
  LLoteId, LArticuloId, LDiasVida: Integer;
  LQryUpdate: TFDQuery;
begin
  if dblcAlmacen.Text = '' then
  begin
    ShowMessage('Debe seleccionar un almacén.');
    dblcAlmacen.SetFocus;
    Exit;
  end;
  if dblcLote.Text = '' then
  begin
    ShowMessage('Debe seleccionar un lote.');
    dblcLote.SetFocus;
    Exit;
  end;
  if dblcFormato.Text = '' then
  begin
    ShowMessage('Debe seleccionar un formato de presentación.');
    dblcFormato.SetFocus;
    Exit;
  end;

  // Validar coherencia de fechas
  if dtpFechaCaducidad.Checked and (Trunc(dtpFechaCaducidad.Date) < Trunc(dtpFechaElaboracion.Date)) then
  begin
    ShowMessage('La fecha de caducidad no puede ser anterior a la fecha de elaboración.');
    dtpFechaCaducidad.SetFocus;
    Exit;
  end;

  LIsNew := FIsNewRecord;

  // Obtener id_articulo del lote seleccionado si es posible
  LArticuloId := 0;
  if qLotes.Active and not qLotes.IsEmpty and (qMaster.FieldByName('id_lote').AsInteger > 0) then
  begin
    if qLotes.Locate('id', qMaster.FieldByName('id_lote').AsInteger, []) then
    begin
      if (not qLotes.FieldByName('id_articulo').IsNull) and (qLotes.FieldByName('id_articulo').AsInteger > 0) then
        LArticuloId := qLotes.FieldByName('id_articulo').AsInteger;
    end;
  end;
  if LArticuloId <= 0 then
    LArticuloId := qMaster.FieldByName('id_articulo').AsInteger;

  if LArticuloId <= 0 then
  begin
    ShowMessage('El lote seleccionado no tiene un artículo vinculado.');
    Exit;
  end;

  try
    // Asegurar que el dataset esté en modo edición si es un registro existente
    if not (qMaster.State in [dsInsert, dsEdit]) then
      qMaster.Edit;

    // Sincronizar automáticamente el artículo vinculado al lote si difiere
    if (LArticuloId > 0) and (qMaster.FieldByName('id_articulo').AsInteger <> LArticuloId) then
      qMaster.FieldByName('id_articulo').AsInteger := LArticuloId;

    // Arrastrar automáticamente cantidad fabricada si la cantidad física está a cero en nuevo registro
    if LIsNew and (qMaster.FieldByName('cantidad_unidades').AsFloat = 0.0) and (qMaster.FieldByName('cantidad_producida').AsFloat > 0.0) then
      qMaster.FieldByName('cantidad_unidades').AsFloat := qMaster.FieldByName('cantidad_producida').AsFloat;

    if LIsNew then
      qMaster.FieldByName('id_user_creator').AsInteger := StrToIntDef(dmgMain.CurrentUserId, 1);

    qMaster.FieldByName('id_user_update').AsInteger := StrToIntDef(dmgMain.CurrentUserId, 1);
    qMaster.Post;

    // Actualizar fecha en ge_lotes y dias_vida en ge_articulos
    LLoteId := qMaster.FieldByName('id_lote').AsInteger;
    LArticuloId := qMaster.FieldByName('id_articulo').AsInteger;

    if LLoteId > 0 then
    begin
      LQryUpdate := TFDQuery.Create(nil);
      try
        LQryUpdate.Connection := dmgMain.dbConn;
        LQryUpdate.SQL.Text :=
          'UPDATE ge_lotes ' +
          'SET fecha = :fecha, ' +
          '    id_user_update = :user, ' +
          '    updated_at = CURRENT_TIMESTAMP ' +
          'WHERE id = :lote_id';
        LQryUpdate.ParamByName('fecha').AsDate := Trunc(dtpFechaElaboracion.Date);
        LQryUpdate.ParamByName('user').AsInteger := StrToIntDef(dmgMain.CurrentUserId, 1);
        LQryUpdate.ParamByName('lote_id').AsInteger := LLoteId;
        LQryUpdate.ExecSQL;

        if LArticuloId > 0 then
        begin
          if dtpFechaCaducidad.Checked then
          begin
            LDiasVida := Trunc(dtpFechaCaducidad.Date) - Trunc(dtpFechaElaboracion.Date);
            LQryUpdate.SQL.Text :=
              'UPDATE ge_articulos ' +
              'SET dias_vida = :dias_vida, ' +
              '    id_user_update = :user, ' +
              '    update_at = CURRENT_TIMESTAMP ' +
              'WHERE id = :art_id';
            LQryUpdate.ParamByName('dias_vida').AsInteger := LDiasVida;
            LQryUpdate.ParamByName('user').AsInteger := StrToIntDef(dmgMain.CurrentUserId, 1);
            LQryUpdate.ParamByName('art_id').AsInteger := LArticuloId;
            LQryUpdate.ExecSQL;
          end
          else
          begin
            LQryUpdate.SQL.Text :=
              'UPDATE ge_articulos ' +
              'SET dias_vida = NULL, ' +
              '    id_user_update = :user, ' +
              '    update_at = CURRENT_TIMESTAMP ' +
              'WHERE id = :art_id';
            LQryUpdate.ParamByName('user').AsInteger := StrToIntDef(dmgMain.CurrentUserId, 1);
            LQryUpdate.ParamByName('art_id').AsInteger := LArticuloId;
            LQryUpdate.ExecSQL;
          end;
        end;
      finally
        LQryUpdate.Free;
      end;
    end;

    FIsNewRecord := False;
    ResetChangeTracking;
    ResetModifiedState;
    if Assigned(FParentForm) and (FParentForm is TfrmStockEnvasado) then
      TfrmStockEnvasado(FParentForm).btnRefreshClick(nil)
    else if Assigned(frmStockEnvasado) then
      frmStockEnvasado.btnRefreshClick(nil);
    ShowMessage('Stock envasado guardado correctamente.');
  except
    on E: Exception do
      TDbErrorHandler.HandleException(E, 'Error al guardar stock envasado');
  end;
end;
procedure TfrmStockEnvasadoEditor.btnBuscarProdClick(Sender: TObject);
var
  LSelectProduct: TfrmSelectProduct;
  LSelectedId: Integer;
begin
  LSelectProduct := TfrmSelectProduct.Create(Self);
  try
    if LSelectProduct.ShowModal = mrOk then
    begin
      LSelectedId := StrToIntDef(LSelectProduct.SelectedId, 0);
      if LSelectedId > 0 then
      begin
        if not (qMaster.State in [dsEdit, dsInsert]) then
          qMaster.Edit;
        qMaster.FieldByName('id_articulo').AsInteger := LSelectedId;
        CargarFechasLote;
      end;
    end;
  finally
    LSelectProduct.Free;
  end;
end;
procedure TfrmStockEnvasadoEditor.dblcLoteCloseUp(Sender: TObject);
var
  LLoteId, LArticuloId: Integer;
begin
  // Al seleccionar un lote, asignar automáticamente el artículo vinculado
  if not (qMaster.State in [dsEdit, dsInsert]) then Exit;
  LLoteId := qMaster.FieldByName('id_lote').AsInteger;
  if LLoteId <= 0 then Exit;
  if qLotes.Locate('id', LLoteId, []) then
  begin
    LArticuloId := qLotes.FieldByName('id_articulo').AsInteger;
    if LArticuloId > 0 then
    begin
      qMaster.FieldByName('id_articulo').AsInteger := LArticuloId;
    end;
  end;
  CargarFechasLote;
end;

procedure TfrmStockEnvasadoEditor.pgcDetailsChange(Sender: TObject);
begin
  if (pgcDetails.ActivePage = tsTrazabilidadVentas) and not FTrazabilidadLoaded then
  begin
    CargarTrazabilidadVentas;
  end;
end;

procedure TfrmStockEnvasadoEditor.btnActualizarTrazClick(Sender: TObject);
begin
  CargarTrazabilidadVentas;
end;

procedure TfrmStockEnvasadoEditor.btnVerDocumentoClick(Sender: TObject);
var
  LTipoDoc: string;
  LDocId: string;
  LAlbaranEditor: TfrmAlbaranesEditor;
  LFacturaEditor: TfrmFacturasEditor;
begin
  if not qTrazabilidadVentas.Active or qTrazabilidadVentas.IsEmpty then Exit;

  LTipoDoc := UpperCase(Trim(qTrazabilidadVentas.FieldByName('tipo_doc').AsString));
  LDocId := qTrazabilidadVentas.FieldByName('doc_id').AsString;

  if (LTipoDoc = 'ALBARÁN') or (LTipoDoc = 'ALBARAN') then
  begin
    LAlbaranEditor := TfrmAlbaranesEditor.Create(Self);
    try
      LAlbaranEditor.AlbaranId := LDocId;
      LAlbaranEditor.ShowModal;
    finally
      LAlbaranEditor.Free;
    end;
  end
  else if (LTipoDoc = 'FACTURA') then
  begin
    LFacturaEditor := TfrmFacturasEditor.Create(Self);
    try
      LFacturaEditor.FacturaId := LDocId;
      LFacturaEditor.ShowModal;
    finally
      LFacturaEditor.Free;
    end;
  end;
end;

procedure TfrmStockEnvasadoEditor.dbgTrazabilidadVentasDblClick(Sender: TObject);
begin
  btnVerDocumentoClick(Sender);
end;

procedure TfrmStockEnvasadoEditor.dbgTrazabilidadVentasTitleClick(Column: TColumn);
begin
  if (Column.Field <> nil) and qTrazabilidadVentas.Active then
  begin
    if qTrazabilidadVentas.IndexFieldNames = Column.Field.FieldName then
      qTrazabilidadVentas.IndexFieldNames := Column.Field.FieldName + ':D'
    else
      qTrazabilidadVentas.IndexFieldNames := Column.Field.FieldName;
  end;
end;

procedure TfrmStockEnvasadoEditor.SetupTrazabilidadGrid;
begin
  dbgTrazabilidadVentas.Columns.Clear;
  
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'tipo_doc';
    Title.Caption := 'Tipo';
    Width := 75;
  end;
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'doc_numero_completo';
    Title.Caption := 'Serie / Nº';
    Width := 90;
  end;
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'doc_fecha';
    Title.Caption := 'Fecha';
    Width := 85;
  end;
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'cliente_nombre';
    Title.Caption := 'Cliente';
    Width := 210;
  end;
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'destino_nombre';
    Title.Caption := 'Sucursal / Destino';
    Width := 150;
  end;
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'cantidad_vendida';
    Title.Caption := 'Cant. Vendida';
    Width := 85;
    Alignment := taRightJustify;
  end;
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'bultos';
    Title.Caption := 'Bultos';
    Width := 55;
    Alignment := taRightJustify;
  end;
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'precio';
    Title.Caption := 'Precio';
    Width := 70;
    Alignment := taRightJustify;
  end;
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'total';
    Title.Caption := 'Total €';
    Width := 80;
    Alignment := taRightJustify;
  end;
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'lote_codigo';
    Title.Caption := 'Lote';
    Width := 90;
  end;
  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'sscc_bulto';
    Title.Caption := 'Palet / SSCC';
    Width := 140;
  end;

  if qTrazabilidadVentas.FindField('cantidad_vendida') <> nil then
    TNumericField(qTrazabilidadVentas.FieldByName('cantidad_vendida')).DisplayFormat := '#,##0.00';
  if qTrazabilidadVentas.FindField('precio') <> nil then
    TNumericField(qTrazabilidadVentas.FieldByName('precio')).DisplayFormat := '#,##0.00 €';
  if qTrazabilidadVentas.FindField('total') <> nil then
    TNumericField(qTrazabilidadVentas.FieldByName('total')).DisplayFormat := '#,##0.00 €';
  if qTrazabilidadVentas.FindField('bultos') <> nil then
    TNumericField(qTrazabilidadVentas.FieldByName('bultos')).DisplayFormat := '#,##0';
  if qTrazabilidadVentas.FindField('doc_fecha') <> nil then
    TDateTimeField(qTrazabilidadVentas.FieldByName('doc_fecha')).DisplayFormat := 'dd/mm/yyyy';
end;

procedure TfrmStockEnvasadoEditor.CargarTrazabilidadVentas;
var
  LArtId, LLoteId: Integer;
  LLoteCode: string;
  LTotalUnidades: Double;
  LDocSet: TDictionary<string, Boolean>;
  LQryLote: TFDQuery;
  LDocKey: string;
begin
  if not Assigned(qTrazabilidadVentas) then Exit;

  FTrazabilidadLoaded := True;
  LArtId := qMaster.FieldByName('id_articulo').AsInteger;
  LLoteId := qMaster.FieldByName('id_lote').AsInteger;
  LLoteCode := '';

  if LLoteId > 0 then
  begin
    LQryLote := TFDQuery.Create(nil);
    try
      LQryLote.Connection := dmgMain.dbConn;
      LQryLote.SQL.Text := 'SELECT codigo FROM ge_lotes WHERE id = :id';
      LQryLote.ParamByName('id').AsInteger := LLoteId;
      LQryLote.Open;
      if not LQryLote.IsEmpty then
        LLoteCode := Trim(LQryLote.FieldByName('codigo').AsString);
    finally
      LQryLote.Free;
    end;
  end;

  qTrazabilidadVentas.Close;
  qTrazabilidadVentas.SQL.Text :=
    'SELECT ' +
    '  CONVERT(''ALBARÁN'' USING utf8mb4) COLLATE utf8mb4_general_ci AS tipo_doc, ' +
    '  a.id AS doc_id, ' +
    '  CONVERT(CONCAT(COALESCE(a.serie, ''''), CASE WHEN a.serie IS NOT NULL AND a.serie <> '''' THEN '' / '' ELSE '''' END, a.numero) USING utf8mb4) COLLATE utf8mb4_general_ci AS doc_numero_completo, ' +
    '  CAST(a.fecha AS DATE) AS doc_fecha, ' +
    '  CONVERT(COALESCE(c.nombre_fiscal, '''') USING utf8mb4) COLLATE utf8mb4_general_ci AS cliente_nombre, ' +
    '  CONVERT(COALESCE(cd.descripcion, '''') USING utf8mb4) COLLATE utf8mb4_general_ci AS destino_nombre, ' +
    '  al.cantidad AS cantidad_vendida, ' +
    '  COALESCE(al.bultos, 0) AS bultos, ' +
    '  al.precio AS precio, ' +
    '  al.total AS total, ' +
    '  CONVERT(COALESCE(NULLIF(al.lotes, ''''), (SELECT GROUP_CONCAT(DISTINCT alb_b.lote SEPARATOR '', '') FROM ge_albaranes_lineas_bultos alb_b WHERE alb_b.linea_albaran_id = al.id)) USING utf8mb4) COLLATE utf8mb4_general_ci AS lote_codigo, ' +
    '  al.fecha_caducidad AS fecha_caducidad, ' +
    '  CONVERT((SELECT GROUP_CONCAT(DISTINCT COALESCE(NULLIF(bp.sscc, ''''), NULLIF(b.sscc, '''')) SEPARATOR '', '') ' +
    '   FROM ge_albaranes_lineas_bultos alb_b ' +
    '   INNER JOIN ge_albaranes_bultos b ON alb_b.bulto_id = b.id ' +
    '   LEFT JOIN ge_albaranes_bultos bp ON b.bulto_padre_id = bp.id ' +
    '   WHERE alb_b.linea_albaran_id = al.id AND (b.sscc IS NOT NULL OR bp.sscc IS NOT NULL)) USING utf8mb4) COLLATE utf8mb4_general_ci AS sscc_bulto ' +
    'FROM ge_albaranes_lineas al ' +
    'INNER JOIN ge_albaranes a ON al.albaran_id = a.id ' +
    'LEFT JOIN ge_clientes c ON a.cliente_id = c.id ' +
    'LEFT JOIN ge_clientes_destinos cd ON a.clientedestino_id = cd.id ' +
    'WHERE ( ' +
    '  (al.articulo_id = :art_id AND (al.lotes = :lote_cod OR al.lotes LIKE CONCAT(''%'', :lote_cod, ''%'') OR EXISTS (SELECT 1 FROM ge_albaranes_lineas_bultos alb_b WHERE alb_b.linea_albaran_id = al.id AND alb_b.lote = :lote_cod))) ' +
    '  OR ' +
    '  (:lote_cod = '''' AND al.articulo_id = :art_id) ' +
    ') ' +
    'UNION ALL ' +
    'SELECT ' +
    '  CONVERT(''FACTURA'' USING utf8mb4) COLLATE utf8mb4_general_ci AS tipo_doc, ' +
    '  f.id AS doc_id, ' +
    '  CONVERT(CONCAT(COALESCE(f.serie, ''''), CASE WHEN f.serie IS NOT NULL AND f.serie <> '''' THEN '' / '' ELSE '''' END, f.numero) USING utf8mb4) COLLATE utf8mb4_general_ci AS doc_numero_completo, ' +
    '  CAST(f.fecha AS DATE) AS doc_fecha, ' +
    '  CONVERT(COALESCE(c.nombre_fiscal, '''') USING utf8mb4) COLLATE utf8mb4_general_ci AS cliente_nombre, ' +
    '  CONVERT('''' USING utf8mb4) COLLATE utf8mb4_general_ci AS destino_nombre, ' +
    '  fl.cantidad AS cantidad_vendida, ' +
    '  COALESCE(fl.bultos, 0) AS bultos, ' +
    '  fl.precio AS precio, ' +
    '  fl.total AS total, ' +
    '  CONVERT(COALESCE(fl.lotes, '''') USING utf8mb4) COLLATE utf8mb4_general_ci AS lote_codigo, ' +
    '  fl.fecha_caducidad AS fecha_caducidad, ' +
    '  CONVERT('''' USING utf8mb4) COLLATE utf8mb4_general_ci AS sscc_bulto ' +
    'FROM ge_facturas_lineas fl ' +
    'INNER JOIN ge_facturas f ON fl.factura_id = f.id ' +
    'LEFT JOIN ge_clientes c ON f.cliente_id = c.id ' +
    'WHERE f.id NOT IN (SELECT COALESCE(id_factura, 0) FROM ge_albaranes WHERE id_factura IS NOT NULL) ' +
    '  AND ( ' +
    '    (fl.articulo_id = :art_id AND (fl.lotes = :lote_cod OR fl.lotes LIKE CONCAT(''%'', :lote_cod, ''%''))) ' +
    '    OR ' +
    '    (:lote_cod = '''' AND fl.articulo_id = :art_id) ' +
    '  ) ' +
    'ORDER BY doc_fecha DESC, doc_id DESC';

  qTrazabilidadVentas.ParamByName('art_id').AsInteger := LArtId;
  qTrazabilidadVentas.ParamByName('lote_cod').AsString := LLoteCode;
  qTrazabilidadVentas.Open;

  SetupTrazabilidadGrid;

  // Calcular resumen sin duplicados
  LTotalUnidades := 0;
  LDocSet := TDictionary<string, Boolean>.Create;
  try
    qTrazabilidadVentas.DisableControls;
    try
      qTrazabilidadVentas.First;
      while not qTrazabilidadVentas.Eof do
      begin
        LTotalUnidades := LTotalUnidades + qTrazabilidadVentas.FieldByName('cantidad_vendida').AsFloat;
        LDocKey := qTrazabilidadVentas.FieldByName('tipo_doc').AsString + '_' + qTrazabilidadVentas.FieldByName('doc_id').AsString;
        LDocSet.AddOrSetValue(LDocKey, True);
        qTrazabilidadVentas.Next;
      end;
      qTrazabilidadVentas.First;
    finally
      qTrazabilidadVentas.EnableControls;
    end;

    if LLoteCode <> '' then
      lblResumenTrazabilidad.Caption := Format('📦 Lote: %s  |  Total Vendido: %s uds  |  En %d documento(s) de venta (%d líneas)', 
        [LLoteCode, FormatFloat('#,##0.00', LTotalUnidades), LDocSet.Count, qTrazabilidadVentas.RecordCount])
    else
      lblResumenTrazabilidad.Caption := Format('📦 Producto #%d  |  Total Vendido: %s uds  |  En %d documento(s) de venta (%d líneas)', 
        [LArtId, FormatFloat('#,##0.00', LTotalUnidades), LDocSet.Count, qTrazabilidadVentas.RecordCount]);
  finally
    LDocSet.Free;
  end;
end;

end.