unit frm_InventarioStockEnvasado;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.ExtCtrls, Vcl.StdCtrls, Vcl.Buttons,
  Vcl.Grids, Vcl.DBGrids, Data.DB, FireDAC.Comp.Client, FireDAC.Stan.Param,
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.DatS,
  FireDAC.Phys.Intf, FireDAC.DApt.Intf, System.UITypes;

type
  TfrmInventarioStockEnvasado = class(TForm)
    pnlMain: TPanel;
    pnlTopToolbar: TPanel;
    lblAlmacen: TLabel;
    cbAlmacen: TComboBox;
    pnlTopButtons: TPanel;
    btnPegarLista: TBitBtn;
    btnAnadirManual: TBitBtn;
    btnLimpiarConteo: TBitBtn;
    btnExcel: TBitBtn;
    btnRegularizar: TBitBtn;
    btnSalir: TBitBtn;
    pnlStats: TPanel;
    pnlCard1: TPanel;
    lblStatTitle1: TLabel;
    lblStatLotesContados: TLabel;
    pnlCard2: TPanel;
    lblStatTitle2: TLabel;
    lblStatUdsContadas: TLabel;
    pnlCard3: TPanel;
    lblStatTitle3: TLabel;
    lblStatLotesCero: TLabel;
    pnlCard4: TPanel;
    lblStatTitle4: TLabel;
    lblStatDescuadre: TLabel;
    pnlScanZone: TPanel;
    lblScanPrompt: TLabel;
    lblScanHint: TLabel;
    edtBarcode: TEdit;
    btnEscanear: TBitBtn;
    pnlFilterBar: TPanel;
    rbFiltroTodos: TRadioButton;
    rbFiltroEscaneados: TRadioButton;
    rbFiltroNoEscaneados: TRadioButton;
    lblSearch: TLabel;
    edtSearch: TEdit;
    dbgInventario: TDBGrid;
    mtInventory: TFDMemTable;
    dsInventory: TDataSource;
    qryTemp: TFDQuery;

    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure cbAlmacenChange(Sender: TObject);
    procedure edtBarcodeKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure btnEscanearClick(Sender: TObject);
    procedure btnPegarListaClick(Sender: TObject);
    procedure btnAnadirManualClick(Sender: TObject);
    procedure btnLimpiarConteoClick(Sender: TObject);
    procedure btnExcelClick(Sender: TObject);
    procedure btnRegularizarClick(Sender: TObject);
    procedure btnSalirClick(Sender: TObject);
    procedure rbFiltroClick(Sender: TObject);
    procedure edtSearchChange(Sender: TObject);
    procedure dbgInventarioDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure dbgInventarioDblClick(Sender: TObject);
    procedure dbgInventarioTitleClick(Column: TColumn);

  private
    FSelectedAlmacenId: Integer;
    FLastSortColumn: string;
    FLastSortDirection: string;

    procedure SetupMemTable;
    procedure LoadAlmacenes;
    procedure LoadSystemStock(AAlmacenId: Integer);
    procedure UpdateStatsAndFilters;
    procedure ApplyTableFilter;
    procedure mtInventoryCantidadContadaChange(Sender: TField);
    procedure mtInventoryAfterPost(DataSet: TDataSet);
    function ProcessSingleBarcode(const ARawCode: string; out AErrorMsg: string): Boolean;
    procedure RegisterScannedItem(AStockId, AArticuloId: Integer; const AArtNombre, AArtEan: string;
      ALoteId: Integer; const ALoteCodigo: string; AFecCaducidad: TDateTime;
      AFormatoId: Integer; const AFormatoNombre: string; AStockSistema, AQtyToAdd: Double);
    function CleanBarcode(const ARaw: string): string;
  public
    property SelectedAlmacenId: Integer read FSelectedAlmacenId write FSelectedAlmacenId;
  end;

var
  frmInventarioStockEnvasado: TfrmInventarioStockEnvasado;

implementation

uses
  dmg_Main, uAppTheme, uGS1BarcodeParser, dlg_PegarListaCodigos, frm_SelectProduct, frm_SelectLote,
  System.Win.ComObj;

{$R *.dfm}

procedure TfrmInventarioStockEnvasado.FormShow(Sender: TObject);
begin
  frmInventarioStockEnvasado := Self;
  TAppTheme.ApplyToForm(Self);
  SetupMemTable;
  LoadAlmacenes;
  if cbAlmacen.Items.Count > 0 then
  begin
    if cbAlmacen.ItemIndex = -1 then
      cbAlmacen.ItemIndex := 0;
    cbAlmacenChange(nil);
  end;
end;

procedure TfrmInventarioStockEnvasado.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
end;

procedure TfrmInventarioStockEnvasado.FormDestroy(Sender: TObject);
begin
  if frmInventarioStockEnvasado = Self then
    frmInventarioStockEnvasado := nil;
end;

procedure TfrmInventarioStockEnvasado.SetupMemTable;
begin
  mtInventory.Close;
  mtInventory.FieldDefs.Clear;
  mtInventory.FieldDefs.Add('stock_id', ftInteger);
  mtInventory.FieldDefs.Add('id_articulo', ftInteger);
  mtInventory.FieldDefs.Add('articulo_nombre', ftWideString, 200);
  mtInventory.FieldDefs.Add('articulo_ean', ftWideString, 30);
  mtInventory.FieldDefs.Add('id_lote', ftInteger);
  mtInventory.FieldDefs.Add('lote_codigo', ftWideString, 50);
  mtInventory.FieldDefs.Add('fecha_caducidad', ftDate);
  mtInventory.FieldDefs.Add('id_formato_presentacion', ftInteger);
  mtInventory.FieldDefs.Add('formato_nombre', ftWideString, 100);
  mtInventory.FieldDefs.Add('stock_sistema', ftFloat);
  mtInventory.FieldDefs.Add('cantidad_contada', ftFloat);
  mtInventory.FieldDefs.Add('descuadre', ftFloat);
  mtInventory.FieldDefs.Add('estado_str', ftWideString, 50);
  mtInventory.FieldDefs.Add('es_escaneado', ftBoolean);
  mtInventory.FieldDefs.Add('es_nuevo', ftBoolean);
  mtInventory.CreateDataSet;

  mtInventory.FieldByName('cantidad_contada').OnChange := mtInventoryCantidadContadaChange;
  mtInventory.AfterPost := mtInventoryAfterPost;
end;

procedure TfrmInventarioStockEnvasado.mtInventoryCantidadContadaChange(Sender: TField);
var
  LCur, LDiff: Double;
  LStatus: string;
begin
  if mtInventory.State in [dsEdit, dsInsert] then
  begin
    LCur := Sender.AsFloat;
    if LCur < 0 then
    begin
      LCur := 0;
      Sender.AsFloat := 0;
    end;
    mtInventory.FieldByName('es_escaneado').AsBoolean := (LCur > 0);
    LDiff := LCur - mtInventory.FieldByName('stock_sistema').AsFloat;
    mtInventory.FieldByName('descuadre').AsFloat := LDiff;

    if mtInventory.FieldByName('es_nuevo').AsBoolean then
      LStatus := '✨ NUEVO LOTE'
    else if LCur = 0 then
      LStatus := '⚠️ Sin Escanear -> 0'
    else if LDiff = 0 then
      LStatus := '✅ CUADRADO'
    else if LDiff > 0 then
      LStatus := Format('📈 SOBRANTE (+%s)', [FormatFloat('#,##0', LDiff)])
    else
      LStatus := Format('📉 FALTANTE (%s)', [FormatFloat('#,##0', LDiff)]);

    mtInventory.FieldByName('estado_str').AsString := LStatus;
  end;
end;

procedure TfrmInventarioStockEnvasado.mtInventoryAfterPost(DataSet: TDataSet);
begin
  UpdateStatsAndFilters;
end;

procedure TfrmInventarioStockEnvasado.LoadAlmacenes;
var
  LSql: string;
begin
  cbAlmacen.Items.Clear;
  qryTemp.Close;
  LSql := 'SELECT id, descripcion FROM ge_almacenes ' +
          'WHERE activo = 1 AND (id_empresa = :emp OR compartido = 1) ' +
          'ORDER BY descripcion ASC';
  qryTemp.SQL.Text := LSql;
  qryTemp.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
  qryTemp.Open;

  while not qryTemp.Eof do
  begin
    cbAlmacen.Items.AddObject(qryTemp.FieldByName('descripcion').AsString,
      TObject(IntPtr(qryTemp.FieldByName('id').AsInteger)));
    qryTemp.Next;
  end;
  qryTemp.Close;
end;

procedure TfrmInventarioStockEnvasado.cbAlmacenChange(Sender: TObject);
var
  LId: Integer;
begin
  if cbAlmacen.ItemIndex >= 0 then
  begin
    LId := Integer(IntPtr(cbAlmacen.Items.Objects[cbAlmacen.ItemIndex]));
    FSelectedAlmacenId := LId;
    LoadSystemStock(LId);
  end
  else
  begin
    FSelectedAlmacenId := 0;
    mtInventory.EmptyDataSet;
    UpdateStatsAndFilters;
  end;
end;

procedure TfrmInventarioStockEnvasado.LoadSystemStock(AAlmacenId: Integer);
var
  LSql: string;
begin
  if AAlmacenId <= 0 then Exit;

  mtInventory.EmptyDataSet;
  mtInventory.DisableControls;
  try
    qryTemp.Close;
    LSql := 'SELECT se.id AS stock_id, se.id_almacen, se.id_articulo, se.id_lote, se.id_formato_presentacion, ' +
            'COALESCE(se.cantidad_producida, 0) AS cantidad_producida, ' +
            'COALESCE(se.cantidad_unidades, 0) AS cantidad_unidades, ' +
            'COALESCE(se.cantidad_reservada, 0) AS cantidad_reservada, ' +
            'art.descripcion AS articulo_nombre, art.ean AS articulo_ean, ' +
            'COALESCE(art.dias_vida, 0) AS dias_vida, ' +
            'COALESCE(NULLIF(l.codigo_antiguo, ''''), CAST(l.codigo AS CHAR)) AS lote_codigo, ' +
            'l.fecha AS fecha_fabricacion, ' +
            '(CASE WHEN l.fecha IS NOT NULL AND art.dias_vida > 0 THEN DATE_ADD(l.fecha, INTERVAL art.dias_vida DAY) ELSE NULL END) AS fecha_caducidad, ' +
            'fp.descripcion AS formato_nombre ' +
            'FROM ge_stock_envasado se ' +
            'JOIN ge_articulos art ON se.id_articulo = art.id ' +
            'LEFT JOIN ge_lotes l ON se.id_lote = l.id ' +
            'LEFT JOIN ge_formatos_presentacion fp ON se.id_formato_presentacion = fp.id ' +
            'WHERE se.id_almacen = :alm ' +
            'ORDER BY art.descripcion ASC, l.fecha DESC';
    qryTemp.SQL.Text := LSql;
    qryTemp.ParamByName('alm').AsInteger := AAlmacenId;
    qryTemp.Open;

    while not qryTemp.Eof do
    begin
      mtInventory.Append;
      mtInventory.FieldByName('stock_id').AsInteger := qryTemp.FieldByName('stock_id').AsInteger;
      mtInventory.FieldByName('id_articulo').AsInteger := qryTemp.FieldByName('id_articulo').AsInteger;
      mtInventory.FieldByName('articulo_nombre').AsString := qryTemp.FieldByName('articulo_nombre').AsString;
      mtInventory.FieldByName('articulo_ean').AsString := qryTemp.FieldByName('articulo_ean').AsString;
      mtInventory.FieldByName('id_lote').AsInteger := qryTemp.FieldByName('id_lote').AsInteger;
      mtInventory.FieldByName('lote_codigo').AsString := qryTemp.FieldByName('lote_codigo').AsString;
      if not qryTemp.FieldByName('fecha_caducidad').IsNull then
        mtInventory.FieldByName('fecha_caducidad').AsDateTime := qryTemp.FieldByName('fecha_caducidad').AsDateTime;
      mtInventory.FieldByName('id_formato_presentacion').AsInteger := qryTemp.FieldByName('id_formato_presentacion').AsInteger;
      mtInventory.FieldByName('formato_nombre').AsString := qryTemp.FieldByName('formato_nombre').AsString;
      mtInventory.FieldByName('stock_sistema').AsFloat := qryTemp.FieldByName('cantidad_unidades').AsFloat;
      mtInventory.FieldByName('cantidad_contada').AsFloat := 0.0;
      mtInventory.FieldByName('descuadre').AsFloat := -qryTemp.FieldByName('cantidad_unidades').AsFloat;
      mtInventory.FieldByName('estado_str').AsString := '⚠️ Sin Escanear -> 0';
      mtInventory.FieldByName('es_escaneado').AsBoolean := False;
      mtInventory.FieldByName('es_nuevo').AsBoolean := False;
      mtInventory.Post;

      qryTemp.Next;
    end;
    qryTemp.Close;
  finally
    mtInventory.EnableControls;
  end;

  mtInventory.IndexFieldNames := 'articulo_nombre;lote_codigo';
  FLastSortColumn := 'articulo_nombre';
  FLastSortDirection := 'ASC';

  UpdateStatsAndFilters;
  edtBarcode.SetFocus;
end;

procedure TfrmInventarioStockEnvasado.UpdateStatsAndFilters;
var
  LTotalContados, LTotalSinEscanear: Integer;
  LTotalUdsContadas, LTotalStockSistema, LDescuadreGlobal: Double;
  LRecBM: TBookmark;
begin
  LTotalContados := 0;
  LTotalSinEscanear := 0;
  LTotalUdsContadas := 0;
  LTotalStockSistema := 0;

  mtInventory.DisableControls;
  try
    LRecBM := mtInventory.GetBookmark;
    try
      mtInventory.First;
      while not mtInventory.Eof do
      begin
        LTotalStockSistema := LTotalStockSistema + mtInventory.FieldByName('stock_sistema').AsFloat;
        if mtInventory.FieldByName('es_escaneado').AsBoolean and (mtInventory.FieldByName('cantidad_contada').AsFloat > 0) then
        begin
          Inc(LTotalContados);
          LTotalUdsContadas := LTotalUdsContadas + mtInventory.FieldByName('cantidad_contada').AsFloat;
        end
        else if (not mtInventory.FieldByName('es_escaneado').AsBoolean) and (mtInventory.FieldByName('stock_sistema').AsFloat > 0) then
        begin
          Inc(LTotalSinEscanear);
        end;
        mtInventory.Next;
      end;
    finally
      if mtInventory.BookmarkValid(LRecBM) then
        mtInventory.GotoBookmark(LRecBM);
      mtInventory.FreeBookmark(LRecBM);
    end;
  finally
    mtInventory.EnableControls;
  end;

  LDescuadreGlobal := LTotalUdsContadas - LTotalStockSistema;

  lblStatLotesContados.Caption := IntToStr(LTotalContados);
  lblStatUdsContadas.Caption := FormatFloat('#,##0', LTotalUdsContadas);
  lblStatLotesCero.Caption := IntToStr(LTotalSinEscanear);

  if LDescuadreGlobal > 0 then
    lblStatDescuadre.Caption := '+' + FormatFloat('#,##0', LDescuadreGlobal) + ' uds'
  else
    lblStatDescuadre.Caption := FormatFloat('#,##0', LDescuadreGlobal) + ' uds';

  if LDescuadreGlobal = 0 then
    lblStatDescuadre.Font.Color := clWindowText
  else if LDescuadreGlobal > 0 then
    lblStatDescuadre.Font.Color := clGreen
  else
    lblStatDescuadre.Font.Color := clRed;

  rbFiltroTodos.Caption := Format('Todos (%d)', [mtInventory.RecordCount]);
  rbFiltroEscaneados.Caption := Format('Escaneados / Contados (%d)', [LTotalContados]);
  rbFiltroNoEscaneados.Caption := Format('Sin Escanear -> A Cero (%d)', [LTotalSinEscanear]);

  ApplyTableFilter;
end;

procedure TfrmInventarioStockEnvasado.ApplyTableFilter;
var
  LFilter: string;
begin
  LFilter := '';

  if rbFiltroEscaneados.Checked then
    LFilter := '(es_escaneado = True) AND (cantidad_contada > 0)'
  else if rbFiltroNoEscaneados.Checked then
    LFilter := '(es_escaneado = False) AND (stock_sistema > 0)';

  if Trim(edtSearch.Text) <> '' then
  begin
    if LFilter <> '' then
      LFilter := LFilter + ' AND ';
    LFilter := LFilter + Format('(articulo_nombre LIKE ''%%%s%%'' OR articulo_ean LIKE ''%%%s%%'' OR lote_codigo LIKE ''%%%s%%'')',
      [Trim(edtSearch.Text), Trim(edtSearch.Text), Trim(edtSearch.Text)]);
  end;

  mtInventory.Filter := LFilter;
  mtInventory.Filtered := LFilter <> '';
end;

procedure TfrmInventarioStockEnvasado.rbFiltroClick(Sender: TObject);
begin
  ApplyTableFilter;
end;

procedure TfrmInventarioStockEnvasado.edtSearchChange(Sender: TObject);
begin
  ApplyTableFilter;
end;

function TfrmInventarioStockEnvasado.CleanBarcode(const ARaw: string): string;
var
  c: string;
begin
  c := Trim(ARaw);
  while (Length(c) > 0) and (Ord(c[1]) < 32) do
    Delete(c, 1, 1);
  if (Length(c) > 3) and (Copy(c, 1, 1) = ']') then
    Delete(c, 1, 3);
  while (Length(c) > 0) and (Ord(c[1]) < 32) do
    Delete(c, 1, 1);
  Result := Trim(c);
end;

function TfrmInventarioStockEnvasado.ProcessSingleBarcode(const ARawCode: string; out AErrorMsg: string): Boolean;
var
  LCode, LCleanLote: string;
  LGS1: TGS1BarcodeData;
  LArtId, LLoteId, LFmtId, LStockId: Integer;
  LArtNombre, LArtEan, LFmtNombre: string;
  LDiasVida: Integer;
  LFecFab, LFecCad: TDateTime;
  LStockSistema, LUnitsToAdd: Double;
begin
  Result := False;
  AErrorMsg := '';
  LCode := CleanBarcode(ARawCode);
  if LCode = '' then Exit;

  if FSelectedAlmacenId <= 0 then
  begin
    AErrorMsg := 'Debe seleccionar un almacén.';
    Exit;
  end;

  LArtId := 0;
  LLoteId := 0;
  LFmtId := 1;
  LStockId := 0;
  LArtNombre := '';
  LArtEan := '';
  LFmtNombre := 'Estándar';
  LDiasVida := 0;
  LFecFab := 0;
  LFecCad := 0;
  LStockSistema := 0.0;
  LUnitsToAdd := 1.0;
  LCleanLote := '';

  // 1. Probar decodificación GS1-128
  LGS1 := TGS1BarcodeParser.Parse(LCode);
  if LGS1.IsValid and (LGS1.EAN13 <> '') then
  begin
    LArtEan := LGS1.EAN13;
    LCleanLote := LGS1.Lote;
    LUnitsToAdd := LGS1.Cantidad;
    if LUnitsToAdd <= 0 then LUnitsToAdd := 1.0;
    if LGS1.HasCaducidad then LFecCad := LGS1.FechaCaducidad;
  end;

  // 2. Buscar artículo por EAN (o directamente por código)
  qryTemp.Close;
  if LArtEan <> '' then
  begin
    qryTemp.SQL.Text := 'SELECT id, descripcion, ean, dias_vida FROM ge_articulos WHERE ean = :ean AND activo = 1 LIMIT 1';
    qryTemp.ParamByName('ean').AsString := LArtEan;
    qryTemp.Open;
  end;

  if qryTemp.IsEmpty then
  begin
    // Probar búsqueda directa por EAN con el código leído completo
    qryTemp.Close;
    qryTemp.SQL.Text := 'SELECT id, descripcion, ean, dias_vida FROM ge_articulos WHERE ean = :ean AND activo = 1 LIMIT 1';
    qryTemp.ParamByName('ean').AsString := LCode;
    qryTemp.Open;
  end;

  if not qryTemp.IsEmpty then
  begin
    LArtId := qryTemp.FieldByName('id').AsInteger;
    LArtNombre := qryTemp.FieldByName('descripcion').AsString;
    LArtEan := qryTemp.FieldByName('ean').AsString;
    LDiasVida := qryTemp.FieldByName('dias_vida').AsInteger;
  end;
  qryTemp.Close;

  // 3. Si no se encontró por EAN, buscar por Lote directo
  if LArtId = 0 then
  begin
    qryTemp.Close;
    qryTemp.SQL.Text := 'SELECT l.id AS lote_id, l.id_articulo, COALESCE(NULLIF(l.codigo_antiguo, ''''), CAST(l.codigo AS CHAR)) AS lote_cod, ' +
                        'l.fecha AS fecha_fabricacion, a.descripcion AS art_desc, a.ean AS art_ean, a.dias_vida ' +
                        'FROM ge_lotes l ' +
                        'JOIN ge_articulos a ON l.id_articulo = a.id ' +
                        'WHERE (l.codigo = :l1 OR l.codigo_antiguo = :l2) AND l.activo = 1 ' +
                        'ORDER BY l.fecha DESC LIMIT 1';
    qryTemp.ParamByName('l1').AsString := LCode;
    qryTemp.ParamByName('l2').AsString := LCode;
    qryTemp.Open;

    if not qryTemp.IsEmpty then
    begin
      LArtId := qryTemp.FieldByName('id_articulo').AsInteger;
      LArtNombre := qryTemp.FieldByName('art_desc').AsString;
      LArtEan := qryTemp.FieldByName('art_ean').AsString;
      LDiasVida := qryTemp.FieldByName('dias_vida').AsInteger;
      LLoteId := qryTemp.FieldByName('lote_id').AsInteger;
      LCleanLote := qryTemp.FieldByName('lote_cod').AsString;
      if not qryTemp.FieldByName('fecha_fabricacion').IsNull then
      begin
        LFecFab := qryTemp.FieldByName('fecha_fabricacion').AsDateTime;
        if (LDiasVida > 0) and (LFecFab > 0) then
          LFecCad := LFecFab + LDiasVida;
      end;
    end;
    qryTemp.Close;
  end;

  if LArtId = 0 then
  begin
    AErrorMsg := Format('Código "%s" no reconocido como EAN o Lote.', [LCode]);
    Exit;
  end;

  // 4. Si tenemos artículo y lote, resolver fecha de caducidad si falta
  if (LCleanLote <> '') and (LLoteId = 0) then
  begin
    qryTemp.Close;
    qryTemp.SQL.Text := 'SELECT id, fecha FROM ge_lotes WHERE id_articulo = :art AND (codigo = :c1 OR codigo_antiguo = :c2) LIMIT 1';
    qryTemp.ParamByName('art').AsInteger := LArtId;
    qryTemp.ParamByName('c1').AsString := LCleanLote;
    qryTemp.ParamByName('c2').AsString := LCleanLote;
    qryTemp.Open;
    if not qryTemp.IsEmpty then
    begin
      LLoteId := qryTemp.FieldByName('id').AsInteger;
      if (LFecCad = 0) and not qryTemp.FieldByName('fecha').IsNull and (LDiasVida > 0) then
        LFecCad := qryTemp.FieldByName('fecha').AsDateTime + LDiasVida;
    end;
    qryTemp.Close;
  end;

  // 5. Registrar / acumular en mtInventory
  RegisterScannedItem(LStockId, LArtId, LArtNombre, LArtEan, LLoteId, LCleanLote, LFecCad, LFmtId, LFmtNombre, LStockSistema, LUnitsToAdd);
  Result := True;
end;

procedure TfrmInventarioStockEnvasado.RegisterScannedItem(AStockId, AArticuloId: Integer;
  const AArtNombre, AArtEan: string; ALoteId: Integer; const ALoteCodigo: string;
  AFecCaducidad: TDateTime; AFormatoId: Integer; const AFormatoNombre: string;
  AStockSistema, AQtyToAdd: Double);
var
  LFound: Boolean;
  LDiff, LCurCount: Double;
  LStatus: string;
begin
  LFound := False;
  mtInventory.DisableControls;
  try
    mtInventory.First;
    while not mtInventory.Eof do
    begin
      if (mtInventory.FieldByName('id_articulo').AsInteger = AArticuloId) and
         (SameText(Trim(mtInventory.FieldByName('lote_codigo').AsString), Trim(ALoteCodigo))) and
         (mtInventory.FieldByName('id_formato_presentacion').AsInteger = AFormatoId) then
      begin
        LFound := True;
        mtInventory.Edit;
        LCurCount := mtInventory.FieldByName('cantidad_contada').AsFloat + AQtyToAdd;
        mtInventory.FieldByName('cantidad_contada').AsFloat := LCurCount;
        mtInventory.FieldByName('es_escaneado').AsBoolean := True;
        LDiff := LCurCount - mtInventory.FieldByName('stock_sistema').AsFloat;
        mtInventory.FieldByName('descuadre').AsFloat := LDiff;

        if mtInventory.FieldByName('es_nuevo').AsBoolean then
          LStatus := '✨ NUEVO LOTE'
        else if LDiff = 0 then
          LStatus := '✅ CUADRADO'
        else if LDiff > 0 then
          LStatus := Format('📈 SOBRANTE (+%s)', [FormatFloat('#,##0', LDiff)])
        else
          LStatus := Format('📉 FALTANTE (%s)', [FormatFloat('#,##0', LDiff)]);

        mtInventory.FieldByName('estado_str').AsString := LStatus;
        if (AFecCaducidad > 0) and (mtInventory.FieldByName('fecha_caducidad').IsNull or (mtInventory.FieldByName('fecha_caducidad').AsDateTime = 0)) then
          mtInventory.FieldByName('fecha_caducidad').AsDateTime := AFecCaducidad;

        mtInventory.Post;
        Break;
      end;
      mtInventory.Next;
    end;

    if not LFound then
    begin
      // Añadir nueva fila
      mtInventory.Append;
      mtInventory.FieldByName('stock_id').AsInteger := AStockId;
      mtInventory.FieldByName('id_articulo').AsInteger := AArticuloId;
      mtInventory.FieldByName('articulo_nombre').AsString := AArtNombre;
      mtInventory.FieldByName('articulo_ean').AsString := AArtEan;
      mtInventory.FieldByName('id_lote').AsInteger := ALoteId;
      mtInventory.FieldByName('lote_codigo').AsString := ALoteCodigo;
      if AFecCaducidad > 0 then
        mtInventory.FieldByName('fecha_caducidad').AsDateTime := AFecCaducidad;
      mtInventory.FieldByName('id_formato_presentacion').AsInteger := AFormatoId;
      mtInventory.FieldByName('formato_nombre').AsString := AFormatoNombre;
      mtInventory.FieldByName('stock_sistema').AsFloat := 0.0;
      mtInventory.FieldByName('cantidad_contada').AsFloat := AQtyToAdd;
      mtInventory.FieldByName('descuadre').AsFloat := AQtyToAdd;
      mtInventory.FieldByName('estado_str').AsString := '✨ NUEVO LOTE';
      mtInventory.FieldByName('es_escaneado').AsBoolean := True;
      mtInventory.FieldByName('es_nuevo').AsBoolean := True;
      mtInventory.Post;
    end;
  finally
    mtInventory.EnableControls;
  end;

  UpdateStatsAndFilters;
end;

procedure TfrmInventarioStockEnvasado.edtBarcodeKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
begin
  if Key = VK_RETURN then
  begin
    Key := 0;
    btnEscanearClick(nil);
  end;
end;

procedure TfrmInventarioStockEnvasado.btnEscanearClick(Sender: TObject);
var
  LCode, LErr: string;
begin
  LCode := CleanBarcode(edtBarcode.Text);
  if LCode = '' then Exit;

  edtBarcode.Clear;
  if ProcessSingleBarcode(LCode, LErr) then
  begin
    MessageBeep(MB_OK);
  end
  else
  begin
    MessageBeep(MB_ICONHAND);
    MessageDlg(LErr, mtError, [mbOK], 0);
  end;
  edtBarcode.SetFocus;
end;

procedure TfrmInventarioStockEnvasado.btnPegarListaClick(Sender: TObject);
var
  LCodes: TArray<string>;
  i, LOkCount, LErrCount: Integer;
  LErrList: TStringList;
  LErrMsg: string;
begin
  if FSelectedAlmacenId <= 0 then
  begin
    MessageDlg('Por favor seleccione un almacén antes de procesar códigos.', mtWarning, [mbOK], 0);
    cbAlmacen.SetFocus;
    Exit;
  end;

  if TdlgPegarListaCodigos.Execute(Self, LCodes) then
  begin
    LOkCount := 0;
    LErrCount := 0;
    LErrList := TStringList.Create;
    try
      Screen.Cursor := crHourGlass;
      try
        for i := 0 to High(LCodes) do
        begin
          if ProcessSingleBarcode(LCodes[i], LErrMsg) then
            Inc(LOkCount)
          else
          begin
            Inc(LErrCount);
            if LErrList.Count < 10 then
              LErrList.Add('• ' + LCodes[i] + ': ' + LErrMsg);
          end;
        end;
      finally
        Screen.Cursor := crDefault;
      end;

      if LErrCount > 0 then
      begin
        MessageDlg(Format('Proceso completado.'#13#10#13#10'• Códigos procesados con éxito: %d'#13#10'• Códigos no reconocidos: %d'#13#10#13#10'%s',
          [LOkCount, LErrCount, LErrList.Text]), mtWarning, [mbOK], 0);
      end
      else
      begin
        MessageDlg(Format('¡Se han procesado e incorporado %d códigos con éxito al inventario!', [LOkCount]), mtInformation, [mbOK], 0);
      end;
    finally
      LErrList.Free;
    end;
  end;
  edtBarcode.SetFocus;
end;

procedure TfrmInventarioStockEnvasado.btnAnadirManualClick(Sender: TObject);
var
  LSelectProduct: TfrmSelectProduct;
  LArtId: Integer;
  LArtName, LArtEan, LLoteCod: string;
  LQtyStr: string;
  LQty: Double;
begin
  if FSelectedAlmacenId <= 0 then
  begin
    MessageDlg('Por favor seleccione un almacén.', mtWarning, [mbOK], 0);
    cbAlmacen.SetFocus;
    Exit;
  end;

  LSelectProduct := TfrmSelectProduct.Create(Self);
  try
    if LSelectProduct.ShowModal = mrOk then
    begin
      LArtId := StrToIntDef(LSelectProduct.SelectedId, 0);
      LArtName := LSelectProduct.SelectedDesc;
      if LArtId > 0 then
      begin
        LLoteCod := '';
        InputQuery('Lote de Fabricación', 'Introduzca el código de lote (o dejar vacío):', LLoteCod);

        LQtyStr := '1';
        if InputQuery('Unidades Contadas', Format('Indique las unidades físicas para %s:', [LArtName]), LQtyStr) then
        begin
          LQty := StrToFloatDef(LQtyStr, 1.0);
          if LQty <= 0 then LQty := 1.0;

          // Obtener EAN
          qryTemp.Close;
          qryTemp.SQL.Text := 'SELECT ean, dias_vida FROM ge_articulos WHERE id = :id';
          qryTemp.ParamByName('id').AsInteger := LArtId;
          qryTemp.Open;
          LArtEan := qryTemp.FieldByName('ean').AsString;
          qryTemp.Close;

          RegisterScannedItem(0, LArtId, LArtName, LArtEan, 0, Trim(LLoteCod), 0, 1, 'Estándar', 0.0, LQty);
          MessageBeep(MB_OK);
        end;
      end;
    end;
  finally
    LSelectProduct.Free;
  end;
  edtBarcode.SetFocus;
end;

procedure TfrmInventarioStockEnvasado.btnLimpiarConteoClick(Sender: TObject);
begin
  if mtInventory.IsEmpty then Exit;

  if MessageDlg('¿Está seguro de que desea restablecer a cero todos los conteos de este inventario?',
    mtConfirmation, [mbYes, mbNo], 0) = mrYes then
  begin
    mtInventory.DisableControls;
    try
      mtInventory.First;
      while not mtInventory.Eof do
      begin
        mtInventory.Edit;
        mtInventory.FieldByName('cantidad_contada').AsFloat := 0.0;
        mtInventory.FieldByName('descuadre').AsFloat := -mtInventory.FieldByName('stock_sistema').AsFloat;
        mtInventory.FieldByName('estado_str').AsString := '⚠️ Sin Escanear -> 0';
        mtInventory.FieldByName('es_escaneado').AsBoolean := False;
        mtInventory.Post;
        mtInventory.Next;
      end;
    finally
      mtInventory.EnableControls;
    end;
    UpdateStatsAndFilters;
  end;
end;

procedure TfrmInventarioStockEnvasado.btnRegularizarClick(Sender: TObject);
var
  LAlmNombre: string;
  LCountActualizados, LCountInsertados, LCountPuestosACero: Integer;
  LTotalUds: Double;
  LArtId, LLoteId, LFmtId, LStockId: Integer;
  LLoteCod: string;
  LFecCad: TDateTime;
  LCantContada: Double;
  LScannedIds: TStringList;
  LSql: string;
begin
  if FSelectedAlmacenId <= 0 then
  begin
    MessageDlg('Por favor seleccione un almacén para regularizar.', mtWarning, [mbOK], 0);
    cbAlmacen.SetFocus;
    Exit;
  end;

  LAlmNombre := cbAlmacen.Text;

  if MessageDlg(Format('¿Confirma la REGULARIZACIÓN FÍSICA del almacén "%s"?'#13#10#13#10 +
                       '• Se actualizarán las existencias reales contadas en ge_stock_envasado.'#13#10 +
                       '• Se pondrán a 0 las existencias de todos los lotes no escaneados.', [LAlmNombre]),
                mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
    Exit;

  LCountActualizados := 0;
  LCountInsertados := 0;
  LCountPuestosACero := 0;
  LTotalUds := 0;
  LScannedIds := TStringList.Create;
  try
    dmgMain.dbConn.StartTransaction;
    try
      mtInventory.DisableControls;
      try
        mtInventory.First;
        while not mtInventory.Eof do
        begin
          if mtInventory.FieldByName('es_escaneado').AsBoolean and (mtInventory.FieldByName('cantidad_contada').AsFloat > 0) then
          begin
            LStockId     := mtInventory.FieldByName('stock_id').AsInteger;
            LArtId       := mtInventory.FieldByName('id_articulo').AsInteger;
            LLoteId      := mtInventory.FieldByName('id_lote').AsInteger;
            LLoteCod     := Trim(mtInventory.FieldByName('lote_codigo').AsString);
            LFmtId       := mtInventory.FieldByName('id_formato_presentacion').AsInteger;
            if LFmtId <= 0 then LFmtId := 1;
            LCantContada := mtInventory.FieldByName('cantidad_contada').AsFloat;
            LFecCad      := mtInventory.FieldByName('fecha_caducidad').AsDateTime;

            LTotalUds := LTotalUds + LCantContada;

            // Si el lote no existe en ge_lotes, crearlo
            if (LLoteId <= 0) and (LLoteCod <> '') then
            begin
              qryTemp.Close;
              qryTemp.SQL.Text := 'SELECT id FROM ge_lotes WHERE id_articulo = :art AND (codigo = :c1 OR codigo_antiguo = :c2) LIMIT 1';
              qryTemp.ParamByName('art').AsInteger := LArtId;
              qryTemp.ParamByName('c1').AsString := LLoteCod;
              qryTemp.ParamByName('c2').AsString := LLoteCod;
              qryTemp.Open;
              if not qryTemp.IsEmpty then
                LLoteId := qryTemp.FieldByName('id').AsInteger
              else
              begin
                qryTemp.Close;
                qryTemp.SQL.Text := 'INSERT INTO ge_lotes (id_articulo, codigo, codigo_antiguo, fecha, activo) ' +
                                    'VALUES (:art, :cod, :cod_ant, CURDATE(), 1)';
                qryTemp.ParamByName('art').AsInteger := LArtId;
                qryTemp.ParamByName('cod').AsString := LLoteCod;
                qryTemp.ParamByName('cod_ant').AsString := LLoteCod;
                qryTemp.ExecSQL;
                
                qryTemp.Close;
                qryTemp.SQL.Text := 'SELECT LAST_INSERT_ID() AS new_id';
                qryTemp.Open;
                LLoteId := qryTemp.FieldByName('new_id').AsInteger;
              end;
              qryTemp.Close;
            end;

            // Actualizar o insertar en ge_stock_envasado
            if LStockId > 0 then
            begin
              qryTemp.Close;
              qryTemp.SQL.Text := 'UPDATE ge_stock_envasado SET cantidad_unidades = :qty, id_lote = :lot, updated_at = NOW() WHERE id = :id';
              qryTemp.ParamByName('qty').AsFloat := LCantContada;
              qryTemp.ParamByName('lot').AsInteger := LLoteId;
              qryTemp.ParamByName('id').AsInteger := LStockId;
              qryTemp.ExecSQL;
              Inc(LCountActualizados);
              LScannedIds.Add(IntToStr(LStockId));
            end
            else
            begin
              // Verificar si ya existe registro por clave única
              qryTemp.Close;
              qryTemp.SQL.Text := 'SELECT id FROM ge_stock_envasado WHERE id_almacen = :alm AND id_articulo = :art AND id_lote = :lot AND id_formato_presentacion = :fmt LIMIT 1';
              qryTemp.ParamByName('alm').AsInteger := FSelectedAlmacenId;
              qryTemp.ParamByName('art').AsInteger := LArtId;
              qryTemp.ParamByName('lot').AsInteger := LLoteId;
              qryTemp.ParamByName('fmt').AsInteger := LFmtId;
              qryTemp.Open;

              if not qryTemp.IsEmpty then
              begin
                LStockId := qryTemp.FieldByName('id').AsInteger;
                qryTemp.Close;
                qryTemp.SQL.Text := 'UPDATE ge_stock_envasado SET cantidad_unidades = :qty, updated_at = NOW() WHERE id = :id';
                qryTemp.ParamByName('qty').AsFloat := LCantContada;
                qryTemp.ParamByName('id').AsInteger := LStockId;
                qryTemp.ExecSQL;
                Inc(LCountActualizados);
              end
              else
              begin
                qryTemp.Close;
                qryTemp.SQL.Text := 'INSERT INTO ge_stock_envasado (id_almacen, id_articulo, id_lote, id_formato_presentacion, cantidad_unidades, cantidad_producida, created_at) ' +
                                    'VALUES (:alm, :art, :lot, :fmt, :qty, :qty, NOW())';
                qryTemp.ParamByName('alm').AsInteger := FSelectedAlmacenId;
                qryTemp.ParamByName('art').AsInteger := LArtId;
                qryTemp.ParamByName('lot').AsInteger := LLoteId;
                qryTemp.ParamByName('fmt').AsInteger := LFmtId;
                qryTemp.ParamByName('qty').AsFloat := LCantContada;
                qryTemp.ExecSQL;
                
                qryTemp.Close;
                qryTemp.SQL.Text := 'SELECT LAST_INSERT_ID() AS new_id';
                qryTemp.Open;
                LStockId := qryTemp.FieldByName('new_id').AsInteger;
                Inc(LCountInsertados);
              end;
              LScannedIds.Add(IntToStr(LStockId));
            end;
          end;
          mtInventory.Next;
        end;

        // Poner a 0 los lotes no escaneados de este almacén
        LSql := 'UPDATE ge_stock_envasado SET cantidad_unidades = 0, updated_at = NOW() WHERE id_almacen = :alm ';
        if LScannedIds.Count > 0 then
          LSql := LSql + 'AND id NOT IN (' + String.Join(',', LScannedIds.ToStringArray) + ') ';
        LSql := LSql + 'AND cantidad_unidades > 0';

        qryTemp.Close;
        qryTemp.SQL.Text := LSql;
        qryTemp.ParamByName('alm').AsInteger := FSelectedAlmacenId;
        qryTemp.ExecSQL;
        LCountPuestosACero := qryTemp.RowsAffected;

      finally
        mtInventory.EnableControls;
      end;

      dmgMain.dbConn.Commit;
    except
      on E: Exception do
      begin
        dmgMain.dbConn.Rollback;
        raise Exception.Create('Error al ejecutar la regularización: ' + E.Message);
      end;
    end;

    MessageDlg(Format('Inventario regularizado con éxito en "%s".'#13#10#13#10 +
                      '• Lotes actualizados / creados: %d'#13#10 +
                      '• Lotes puestos a cero: %d'#13#10 +
                      '• Total unidades físicas registradas: %s uds',
                      [LAlmNombre, LCountActualizados + LCountInsertados, LCountPuestosACero, FormatFloat('#,##0', LTotalUds)]),
               mtInformation, [mbOK], 0);

    LoadSystemStock(FSelectedAlmacenId);
  finally
    LScannedIds.Free;
  end;
end;

procedure TfrmInventarioStockEnvasado.btnExcelClick(Sender: TObject);
var
  XLApp, Sheet: Variant;
  Row: Integer;
  LBookmark: TBookmark;
begin
  if mtInventory.IsEmpty then
  begin
    MessageDlg('No hay datos para exportar.', mtInformation, [mbOK], 0);
    Exit;
  end;

  try
    XLApp := CreateOleObject('Excel.Application');
  except
    MessageDlg('No se pudo iniciar Microsoft Excel.', mtError, [mbOK], 0);
    Exit;
  end;

  try
    XLApp.Visible := True;
    XLApp.Workbooks.Add;
    Sheet := XLApp.ActiveSheet;
    Sheet.Name := 'Inventario';

    // Títulos de columnas
    Sheet.Cells[1, 1] := 'Estado';
    Sheet.Cells[1, 2] := 'Artículo / Producto';
    Sheet.Cells[1, 3] := 'EAN';
    Sheet.Cells[1, 4] := 'Lote';
    Sheet.Cells[1, 5] := 'F. Caducidad';
    Sheet.Cells[1, 6] := 'Presentación';
    Sheet.Cells[1, 7] := 'Stock Sistema';
    Sheet.Cells[1, 8] := 'Stock Contado';
    Sheet.Cells[1, 9] := 'Descuadre';

    Row := 2;
    mtInventory.DisableControls;
    try
      LBookmark := mtInventory.GetBookmark;
      try
        mtInventory.First;
        while not mtInventory.Eof do
        begin
          Sheet.Cells[Row, 1] := mtInventory.FieldByName('estado_str').AsString;
          Sheet.Cells[Row, 2] := mtInventory.FieldByName('articulo_nombre').AsString;
          Sheet.Cells[Row, 3] := mtInventory.FieldByName('articulo_ean').AsString;
          Sheet.Cells[Row, 4] := mtInventory.FieldByName('lote_codigo').AsString;
          if not mtInventory.FieldByName('fecha_caducidad').IsNull then
            Sheet.Cells[Row, 5] := FormatDateTime('dd/mm/yyyy', mtInventory.FieldByName('fecha_caducidad').AsDateTime);
          Sheet.Cells[Row, 6] := mtInventory.FieldByName('formato_nombre').AsString;
          Sheet.Cells[Row, 7] := mtInventory.FieldByName('stock_sistema').AsFloat;
          Sheet.Cells[Row, 8] := mtInventory.FieldByName('cantidad_contada').AsFloat;
          Sheet.Cells[Row, 9] := mtInventory.FieldByName('descuadre').AsFloat;
          Inc(Row);
          mtInventory.Next;
        end;
      finally
        if mtInventory.BookmarkValid(LBookmark) then
          mtInventory.GotoBookmark(LBookmark);
        mtInventory.FreeBookmark(LBookmark);
      end;
    finally
      mtInventory.EnableControls;
    end;

    Sheet.Columns.AutoFit;
  except
    on E: Exception do
      MessageDlg('Error exportando a Excel: ' + E.Message, mtError, [mbOK], 0);
  end;
end;

procedure TfrmInventarioStockEnvasado.btnSalirClick(Sender: TObject);
begin
  Close;
end;

procedure TfrmInventarioStockEnvasado.dbgInventarioDblClick(Sender: TObject);
var
  LCur, LNew: Double;
  LInput: string;
  LArt, LLote: string;
begin
  if mtInventory.IsEmpty then Exit;

  LArt := mtInventory.FieldByName('articulo_nombre').AsString;
  LLote := mtInventory.FieldByName('lote_codigo').AsString;
  LCur := mtInventory.FieldByName('cantidad_contada').AsFloat;

  LInput := FloatToStr(LCur);
  if InputQuery('Ajustar Unidades Contadas', Format('%s (Lote: %s)'#13#10'Indique unidades contadas:', [LArt, LLote]), LInput) then
  begin
    LNew := StrToFloatDef(LInput, LCur);
    if LNew < 0 then LNew := 0;

    mtInventory.Edit;
    mtInventory.FieldByName('cantidad_contada').AsFloat := LNew;
    mtInventory.FieldByName('es_escaneado').AsBoolean := LNew > 0;
    mtInventory.FieldByName('descuadre').AsFloat := LNew - mtInventory.FieldByName('stock_sistema').AsFloat;

    if LNew = mtInventory.FieldByName('stock_sistema').AsFloat then
      mtInventory.FieldByName('estado_str').AsString := '✅ CUADRADO'
    else if LNew > mtInventory.FieldByName('stock_sistema').AsFloat then
      mtInventory.FieldByName('estado_str').AsString := Format('📈 SOBRANTE (+%s)', [FormatFloat('#,##0', LNew - mtInventory.FieldByName('stock_sistema').AsFloat)])
    else if LNew > 0 then
      mtInventory.FieldByName('estado_str').AsString := Format('📉 FALTANTE (%s)', [FormatFloat('#,##0', LNew - mtInventory.FieldByName('stock_sistema').AsFloat)])
    else
      mtInventory.FieldByName('estado_str').AsString := '⚠️ Sin Escanear -> 0';

    mtInventory.Post;
    UpdateStatsAndFilters;
  end;
end;

procedure TfrmInventarioStockEnvasado.dbgInventarioDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
var
  LDiff: Double;
  LEscaneado, LNuevo: Boolean;
  LBgColor, LFgColor: TColor;
begin
  if not (gdSelected in State) then
  begin
    LEscaneado := mtInventory.FieldByName('es_escaneado').AsBoolean;
    LNuevo := mtInventory.FieldByName('es_nuevo').AsBoolean;
    LDiff := mtInventory.FieldByName('descuadre').AsFloat;

    LBgColor := clWhite;
    LFgColor := clBlack;

    if LNuevo then
    begin
      LBgColor := $00FDF2F5; // Suave morado
      LFgColor := $00902040;
    end
    else if LEscaneado then
    begin
      if LDiff = 0 then
      begin
        LBgColor := $00F0FDF4; // Suave verde
        LFgColor := $0015803D;
      end
      else if LDiff > 0 then
      begin
        LBgColor := $00DCFCE7; // Verde más claro
        LFgColor := $0015803D;
      end
      else
      begin
        LBgColor := $00FEE2E2; // Suave rojo
        LFgColor := $00B91C1C;
      end;
    end;

    dbgInventario.Canvas.Brush.Color := LBgColor;
    dbgInventario.Canvas.Font.Color := LFgColor;
  end;

  dbgInventario.DefaultDrawColumnCell(Rect, DataCol, Column, State);
end;

procedure TfrmInventarioStockEnvasado.dbgInventarioTitleClick(Column: TColumn);
var
  LField: string;
begin
  if not Assigned(Column) or not Assigned(Column.Field) then Exit;
  LField := Column.Field.FieldName;

  if SameText(FLastSortColumn, LField) then
  begin
    if FLastSortDirection = 'ASC' then
      FLastSortDirection := 'DESC'
    else
      FLastSortDirection := 'ASC';
  end
  else
  begin
    FLastSortColumn := LField;
    FLastSortDirection := 'ASC';
  end;

  if FLastSortDirection = 'DESC' then
    mtInventory.IndexFieldNames := LField + ':D'
  else
    mtInventory.IndexFieldNames := LField;
end;

end.