unit frm_InventariosEditor;

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, FireDAC.Stan.Option, uAppTheme, uBaseForm,
  Vcl.ComCtrls, Vcl.Grids, Vcl.DBGrids, System.ImageList, Vcl.ImgList;

type
  TfrmInventariosEditor = class(TfrmBase)
    lblId: TLabel;
    edtId: TEdit;
    lblFecha: TLabel;
    dtpFecha: TDateTimePicker;
    lblDescripcion: TLabel;
    edtDescripcion: TEdit;
    chkRegularizado: TCheckBox;
    btnRegularizar: TBitBtn;
    procedure FormShow(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure btnSalirClick(Sender: TObject);
    procedure btnRegularizarClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnExcelClick(Sender: TObject);
  protected
    procedure DoAnadir; override;
    procedure DoModificar; override;
    procedure DoPrimero; override;
    procedure DoAnterior; override;
    procedure DoSiguiente; override;
    procedure DoUltimo; override;
    procedure UpdateActionButtonsState; override;
  private
    FInventarioId: string;
    FParentForm: TForm;
    fqryLines: TFDQuery;
    fdsLines: TDataSource;
    procedure LoadData;
  public
    property InventarioId: string read FInventarioId write FInventarioId;
    property ParentForm: TForm read FParentForm write FParentForm;
  end;

var
  frmInventariosEditor: TfrmInventariosEditor;

implementation

uses
  dmg_Main, uDbErrorHandler, frm_Inventarios, System.Win.ComObj;

{$R *.dfm}

procedure TfrmInventariosEditor.FormShow(Sender: TObject);
begin
  TAppTheme.ApplyToForm(Self);

  btnGuardar.OnClick := btnSaveClick;
  btnSalir.OnClick := btnSalirClick;

  pgcDetails.Visible := True;
  TsListado.TabVisible := False;
  pgcDetails.ActivePage := tsDatosEnvio;
  
  // Configurar panel lateral y exportación a Excel
  pnlSideToolbar.Visible := True;
  btnNuevoArticulo.Visible := False;
  btnEliminaArticulo.Visible := False;
  btnBuscarArticulo.Visible := False;
  btnExcel.Visible := True;
  btnExcel.OnClick := btnExcelClick;

  dbgItems.Visible := True;
  pnlFooter.Visible := False;

  // Configurar Query y DataSource locales para las líneas
  if not Assigned(fqryLines) then
  begin
    fqryLines := TFDQuery.Create(Self);
    fqryLines.Connection := dmgMain.dbConn;
    
    // Permitir edición directa en la grilla
    fqryLines.UpdateOptions.AssignedValues := [uvEDelete, uvEInsert, uvEUpdate];
    fqryLines.UpdateOptions.EnableDelete := False;
    fqryLines.UpdateOptions.EnableInsert := False;
    fqryLines.UpdateOptions.EnableUpdate := True;
    
    fdsLines := TDataSource.Create(Self);
    fdsLines.DataSet := fqryLines;
    dbgItems.DataSource := fdsLines;
  end;

  edtId.ReadOnly := True;
  edtId.Color := clBtnFace;
  // chkRegularizado.Enabled := True; // Habilitado por defecto en DFM o aquí de forma implícita

  if FInventarioId <> '' then
    LoadData
  else
    DoAnadir;
end;

procedure TfrmInventariosEditor.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  if Assigned(fqryLines) then
    fqryLines.Close;
end;

procedure TfrmInventariosEditor.LoadData;
begin
  if FInventarioId = '' then Exit;

  dmgMain.qryExec.Close;
  dmgMain.qryExec.SQL.Text := 'SELECT * FROM ge_inventarios WHERE id = :id';
  dmgMain.qryExec.ParamByName('id').AsString := FInventarioId;
  dmgMain.qryExec.Open;
  
  if not dmgMain.qryExec.IsEmpty then
  begin
    edtId.Text := dmgMain.qryExec.FieldByName('id').AsString;
    dtpFecha.Date := dmgMain.qryExec.FieldByName('fecha').AsDateTime;
    edtDescripcion.Text := dmgMain.qryExec.FieldByName('descripcion').AsString;
    chkRegularizado.Checked := dmgMain.qryExec.FieldByName('regularizado').AsInteger <> 0;
  end;
  dmgMain.qryExec.Close;

  // Cargar líneas del inventario
  fqryLines.Close;
  fqryLines.SQL.Text := 'SELECT id, id_inventario, id_entrada_materia_prima, id_materia_prima, ' +
                        'descripcion, lote, fecha_caducidad, stock_teorico, cantidad_real, ' +
                        '(cantidad_real - stock_teorico) as diferencia ' +
                        'FROM ge_inventarios_lineas ' +
                        'WHERE id_inventario = :id_inv ' +
                        'ORDER BY descripcion';
  fqryLines.ParamByName('id_inv').AsString := FInventarioId;
  fqryLines.Open;

  // Configurar columnas de la grilla de líneas
  dbgItems.Columns.Clear;
  with dbgItems.Columns.Add do
  begin
    FieldName := 'descripcion';
    Title.Caption := 'Materia Prima';
    Width := 200;
    ReadOnly := True;
  end;
  with dbgItems.Columns.Add do
  begin
    FieldName := 'lote';
    Title.Caption := 'Lote';
    Width := 100;
    ReadOnly := True;
  end;
  with dbgItems.Columns.Add do
  begin
    FieldName := 'fecha_caducidad';
    Title.Caption := 'F. Caducidad';
    Width := 80;
    ReadOnly := True;
  end;
  with dbgItems.Columns.Add do
  begin
    FieldName := 'stock_teorico';
    Title.Caption := 'Stock Teórico';
    Width := 90;
    ReadOnly := True;
  end;
  with dbgItems.Columns.Add do
  begin
    FieldName := 'cantidad_real';
    Title.Caption := 'Cant. Real';
    Width := 90;
    ReadOnly := False; // El operario puede escribir aquí
  end;
  with dbgItems.Columns.Add do
  begin
    FieldName := 'diferencia';
    Title.Caption := 'Diferencia';
    Width := 90;
    ReadOnly := True;
  end;

  UpdateActionButtonsState;
end;

procedure TfrmInventariosEditor.DoAnadir;
begin
  FInventarioId := '';
  edtId.Text := '(Autonumérico)';
  dtpFecha.Date := Date;
  edtDescripcion.Clear;
  chkRegularizado.Checked := False;
  
  if Assigned(fqryLines) then
    fqryLines.Close;
    
  edtDescripcion.SetFocus;
  UpdateActionButtonsState;
end;

procedure TfrmInventariosEditor.DoModificar;
begin
  if not chkRegularizado.Checked then
    edtDescripcion.SetFocus;
end;

procedure TfrmInventariosEditor.DoPrimero;
var
  LParent: TfrmInventarios;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmInventarios) then
    LParent := TfrmInventarios(FParentForm)
  else if Assigned(frmInventarios) then
    LParent := frmInventarios;

  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.First;
    FInventarioId := LParent.QryMain.FieldByName('id').AsString;
    LoadData;
  end;
end;

procedure TfrmInventariosEditor.DoAnterior;
var
  LParent: TfrmInventarios;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmInventarios) then
    LParent := TfrmInventarios(FParentForm)
  else if Assigned(frmInventarios) then
    LParent := frmInventarios;

  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.Prior;
    FInventarioId := LParent.QryMain.FieldByName('id').AsString;
    LoadData;
  end;
end;

procedure TfrmInventariosEditor.DoSiguiente;
var
  LParent: TfrmInventarios;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmInventarios) then
    LParent := TfrmInventarios(FParentForm)
  else if Assigned(frmInventarios) then
    LParent := frmInventarios;

  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.Next;
    FInventarioId := LParent.QryMain.FieldByName('id').AsString;
    LoadData;
  end;
end;

procedure TfrmInventariosEditor.DoUltimo;
var
  LParent: TfrmInventarios;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmInventarios) then
    LParent := TfrmInventarios(FParentForm)
  else if Assigned(frmInventarios) then
    LParent := frmInventarios;

  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.Last;
    FInventarioId := LParent.QryMain.FieldByName('id').AsString;
    LoadData;
  end;
end;

procedure TfrmInventariosEditor.UpdateActionButtonsState;
var
  LReg: Boolean;
begin
  LReg := chkRegularizado.Checked;
  
  if LReg then
  begin
    btnGuardar.Enabled := False;
    btnCancelar.Enabled := False;
    btnRegularizar.Enabled := False;
    if Assigned(dbgItems) then dbgItems.ReadOnly := True;
  end
  else
  begin
    inherited;
    btnRegularizar.Enabled := (FInventarioId <> '');
    if Assigned(dbgItems) then dbgItems.ReadOnly := False;
  end;
end;

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

procedure TfrmInventariosEditor.btnSaveClick(Sender: TObject);
var
  LIsNew: Boolean;
  LQryMP: TFDQuery;       // Para iterar materias primas inventariables
  LQryCheck: TFDQuery;    // Para verificar entradas existentes
  LMPId: Integer;
  LMPDesc: string;
  LEntradaId: Integer;
  LAlmacenId: Integer;
  LProveedorId: Integer;
begin
  if Trim(edtDescripcion.Text) = '' then
  begin
    ShowMessage('Debe introducir la descripción del inventario.');
    edtDescripcion.SetFocus;
    Exit;
  end;

  LIsNew := (FInventarioId = '');
  try
    // Forzar el guardado de cualquier cambio pendiente en la grilla antes de guardar la cabecera
    if fqryLines.Active and (fqryLines.State in [dsEdit, dsInsert]) then
      fqryLines.Post;

    // Obtener el almacén por defecto (el primero activo de la empresa)
    if LIsNew then
    begin
      dmgMain.qryExec.Close;
      dmgMain.qryExec.SQL.Text := 'SELECT id FROM ge_almacenes WHERE id_empresa = :emp AND activo = 1 ORDER BY id LIMIT 1';
      dmgMain.qryExec.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
      dmgMain.qryExec.Open;
      if not dmgMain.qryExec.IsEmpty then
        LAlmacenId := dmgMain.qryExec.FieldByName('id').AsInteger
      else
        LAlmacenId := 1; // Fallback
      dmgMain.qryExec.Close;
    end;

    dmgMain.qryExec.Close;
    if LIsNew then
    begin
      dmgMain.qryExec.SQL.Text := 
        'INSERT INTO ge_inventarios (id_empresa, id_almacen, fecha, descripcion, regularizado, id_user_creator, id_user_update) ' +
        'VALUES (:emp, :alm, :fec, :des, :reg, :user, :user)';
    end
    else
    begin
      dmgMain.qryExec.SQL.Text := 
        'UPDATE ge_inventarios SET fecha = :fec, descripcion = :des, regularizado = :reg, id_user_update = :user, updated_at = CURRENT_TIMESTAMP ' +
        'WHERE id = :id';
    end;

    if not LIsNew then
      dmgMain.qryExec.ParamByName('id').AsString := FInventarioId;
      
    dmgMain.qryExec.ParamByName('fec').AsDate := dtpFecha.Date;
    dmgMain.qryExec.ParamByName('des').AsString := Trim(edtDescripcion.Text);
    dmgMain.qryExec.ParamByName('reg').AsBoolean := chkRegularizado.Checked;
    
    if LIsNew then
    begin
      dmgMain.qryExec.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
      dmgMain.qryExec.ParamByName('alm').AsInteger := LAlmacenId;
    end;
      
    dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
    
    dmgMain.qryExec.ExecSQL;

    if LIsNew then
    begin
      // Recuperar el ID del inventario insertado
      dmgMain.qryExec.Close;
      dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
      dmgMain.qryExec.Open;
      FInventarioId := dmgMain.qryExec.Fields[0].AsString;
      dmgMain.qryExec.Close;
      
      edtId.Text := FInventarioId;

      // LAlmacenId ya fue obtenido antes del INSERT de cabecera

      // ===================================================================
      // Generar líneas de inventario para CADA materia prima inventariable
      // ===================================================================
      LQryMP := TFDQuery.Create(nil);
      LQryCheck := TFDQuery.Create(nil);
      try
        LQryMP.Connection := dmgMain.dbConn;
        LQryCheck.Connection := dmgMain.dbConn;

        // Obtener todas las materias primas activas e inventariables
        LQryMP.SQL.Text := 'SELECT id, descripcion FROM ge_materias_primas WHERE activo = 1 AND inventariable = 1';
        LQryMP.Open;

        while not LQryMP.Eof do
        begin
          LMPId := LQryMP.FieldByName('id').AsInteger;
          LMPDesc := LQryMP.FieldByName('descripcion').AsString;

          // CASO A: ¿Tiene entradas con stock_restante > 0?
          LQryCheck.Close;
          LQryCheck.SQL.Text :=
            'SELECT id, lote, fecha_caducidad, stock_restante ' +
            'FROM ge_entradas_materias_primas ' +
            'WHERE id_empresa = :emp AND id_materia_prima = :mp AND stock_restante > 0 ' +
            'ORDER BY created_at DESC';
          LQryCheck.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
          LQryCheck.ParamByName('mp').AsInteger := LMPId;
          LQryCheck.Open;

          if not LQryCheck.IsEmpty then
          begin
            // Insertar una línea por cada entrada con stock > 0
            while not LQryCheck.Eof do
            begin
              dmgMain.qryExec.Close;
              dmgMain.qryExec.SQL.Text :=
                'INSERT INTO ge_inventarios_lineas (id_inventario, id_entrada_materia_prima, id_materia_prima, ' +
                '  descripcion, lote, fecha_caducidad, stock_teorico, id_user_creator, id_user_update) ' +
                'VALUES (:id_inv, :id_ent, :id_mp, :desc, :lote, :fcad, :stock, :user, :user)';
              dmgMain.qryExec.ParamByName('id_inv').AsString := FInventarioId;
              dmgMain.qryExec.ParamByName('id_ent').AsInteger := LQryCheck.FieldByName('id').AsInteger;
              dmgMain.qryExec.ParamByName('id_mp').AsInteger := LMPId;
              dmgMain.qryExec.ParamByName('desc').AsString := LMPDesc;
              if not LQryCheck.FieldByName('lote').IsNull then
                dmgMain.qryExec.ParamByName('lote').AsString := LQryCheck.FieldByName('lote').AsString
              else
                dmgMain.qryExec.ParamByName('lote').Clear;
              dmgMain.qryExec.ParamByName('fcad').DataType := ftDate;
              if not LQryCheck.FieldByName('fecha_caducidad').IsNull then
                dmgMain.qryExec.ParamByName('fcad').AsDate := LQryCheck.FieldByName('fecha_caducidad').AsDateTime
              else
                dmgMain.qryExec.ParamByName('fcad').Clear;
              dmgMain.qryExec.ParamByName('stock').AsCurrency := LQryCheck.FieldByName('stock_restante').AsCurrency;
              dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
              dmgMain.qryExec.ExecSQL;

              LQryCheck.Next;
            end;
          end
          else
          begin
            // Sin entradas con stock > 0. Verificar si existen entradas (aunque con stock = 0)
            LQryCheck.Close;
            LQryCheck.SQL.Text :=
              'SELECT id, lote, fecha_caducidad, stock_restante ' +
              'FROM ge_entradas_materias_primas ' +
              'WHERE id_empresa = :emp AND id_materia_prima = :mp ' +
              'ORDER BY created_at DESC LIMIT 1';
            LQryCheck.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
            LQryCheck.ParamByName('mp').AsInteger := LMPId;
            LQryCheck.Open;

            if not LQryCheck.IsEmpty then
            begin
              // CASO B: Existe al menos una entrada pero sin stock > 0 → usar la última
              LEntradaId := LQryCheck.FieldByName('id').AsInteger;

              dmgMain.qryExec.Close;
              dmgMain.qryExec.SQL.Text :=
                'INSERT INTO ge_inventarios_lineas (id_inventario, id_entrada_materia_prima, id_materia_prima, ' +
                '  descripcion, lote, fecha_caducidad, stock_teorico, id_user_creator, id_user_update) ' +
                'VALUES (:id_inv, :id_ent, :id_mp, :desc, :lote, :fcad, :stock, :user, :user)';
              dmgMain.qryExec.ParamByName('id_inv').AsString := FInventarioId;
              dmgMain.qryExec.ParamByName('id_ent').AsInteger := LEntradaId;
              dmgMain.qryExec.ParamByName('id_mp').AsInteger := LMPId;
              dmgMain.qryExec.ParamByName('desc').AsString := LMPDesc;
              if not LQryCheck.FieldByName('lote').IsNull then
                dmgMain.qryExec.ParamByName('lote').AsString := LQryCheck.FieldByName('lote').AsString
              else
                dmgMain.qryExec.ParamByName('lote').Clear;
              dmgMain.qryExec.ParamByName('fcad').DataType := ftDate;
              if not LQryCheck.FieldByName('fecha_caducidad').IsNull then
                dmgMain.qryExec.ParamByName('fcad').AsDate := LQryCheck.FieldByName('fecha_caducidad').AsDateTime
              else
                dmgMain.qryExec.ParamByName('fcad').Clear;
              dmgMain.qryExec.ParamByName('stock').AsCurrency := LQryCheck.FieldByName('stock_restante').AsCurrency;
              dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
              dmgMain.qryExec.ExecSQL;
            end
            else
            begin
              // CASO C: No hay ninguna entrada → crear entrada dummy en ge_entradas_materias_primas
              // Primero, obtenemos el ID de un proveedor activo como fallback
              dmgMain.qryExec.Close;
              dmgMain.qryExec.SQL.Text := 'SELECT id FROM ge_proveedores WHERE activo = 1 ORDER BY id LIMIT 1';
              dmgMain.qryExec.Open;
              if not dmgMain.qryExec.IsEmpty then
                LProveedorId := dmgMain.qryExec.Fields[0].AsInteger
              else
                LProveedorId := 1; // Fallback extremo en caso de no haber ningún proveedor
              dmgMain.qryExec.Close;

              dmgMain.qryExec.Close;
              dmgMain.qryExec.SQL.Text :=
                'INSERT INTO ge_entradas_materias_primas (id_empresa, id_almacen, id_materia_prima, ' +
                '  id_proveedor, id_articulo_proveedor, fecha_entrada, descripcion, lote, fecha_caducidad, ' +
                '  precio_compra, cantidad_compra, stock_restante, id_user_creator, id_user_update) ' +
                'VALUES (:emp, :alm, :id_mp, :prov, 0, :fec_ent, :desc, :lote, NULL, 0, 0, 0, :user, :user)';
              dmgMain.qryExec.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
              dmgMain.qryExec.ParamByName('alm').AsInteger := LAlmacenId;
              dmgMain.qryExec.ParamByName('id_mp').AsInteger := LMPId;
              dmgMain.qryExec.ParamByName('prov').AsInteger := LProveedorId;
              dmgMain.qryExec.ParamByName('fec_ent').AsDate := dtpFecha.Date;
              dmgMain.qryExec.ParamByName('desc').AsString := LMPDesc;
              dmgMain.qryExec.ParamByName('lote').AsString := 'INVENTARIO';
              dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
              dmgMain.qryExec.ExecSQL;

              // Obtener el ID de la entrada recién creada
              dmgMain.qryExec.Close;
              dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID()';
              dmgMain.qryExec.Open;
              LEntradaId := dmgMain.qryExec.Fields[0].AsInteger;
              dmgMain.qryExec.Close;

              // Insertar la línea de inventario apuntando a la entrada dummy
              dmgMain.qryExec.Close;
              dmgMain.qryExec.SQL.Text :=
                'INSERT INTO ge_inventarios_lineas (id_inventario, id_entrada_materia_prima, id_materia_prima, ' +
                '  descripcion, lote, fecha_caducidad, stock_teorico, id_user_creator, id_user_update) ' +
                'VALUES (:id_inv, :id_ent, :id_mp, :desc, :lote, NULL, 0, :user, :user)';
              dmgMain.qryExec.ParamByName('id_inv').AsString := FInventarioId;
              dmgMain.qryExec.ParamByName('id_ent').AsInteger := LEntradaId;
              dmgMain.qryExec.ParamByName('id_mp').AsInteger := LMPId;
              dmgMain.qryExec.ParamByName('desc').AsString := LMPDesc;
              dmgMain.qryExec.ParamByName('lote').AsString := 'INVENTARIO';
              dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
              dmgMain.qryExec.ExecSQL;
            end;
          end;

          LQryMP.Next;
        end;

      finally
        LQryCheck.Free;
        LQryMP.Free;
      end;
    end;

    ShowMessage('Inventario guardado correctamente.');
    ResetChangeTracking;
    LoadData; // Recargar cabecera e hilos de líneas

    if Assigned(FParentForm) and (FParentForm is TfrmInventarios) then
    begin
      TfrmInventarios(FParentForm).btnRefreshClick(nil);
      TfrmInventarios(FParentForm).QryMain.Locate('id', FInventarioId, []);
    end;
  except
    on E: Exception do
      TDbErrorHandler.HandleException(E, 'Error al guardar el inventario');
  end;
end;

procedure TfrmInventariosEditor.btnRegularizarClick(Sender: TObject);
begin
  if FInventarioId = '' then
  begin
    ShowMessage('Primero debe guardar el inventario como borrador.');
    Exit;
  end;

  if chkRegularizado.Checked then
  begin
    ShowMessage('Este inventario ya ha sido regularizado.');
    Exit;
  end;

  if MessageDlg('¿Está seguro de que desea regularizar este inventario? ' + #13#10 +
                'Esta acción actualizará el stock disponible restante en las entradas ' +
                'con los conteos físicos introducidos y no se podrá deshacer.', 
                mtWarning, [mbYes, mbNo], 0) <> mrYes then
    Exit;

  try
    // Asegurar que guardamos la cabecera y grilla de líneas antes de regularizar
    if fqryLines.Active and (fqryLines.State in [dsEdit, dsInsert]) then
      fqryLines.Post;

    // 1. Actualizar stock_restante en las entradas según la cantidad contada (si es nulo asumimos 0)
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 
      'UPDATE ge_entradas_materias_primas emp ' +
      'JOIN ge_inventarios_lineas invl ON emp.id = invl.id_entrada_materia_prima ' +
      'SET emp.stock_restante = COALESCE(invl.cantidad_real, 0.0000), ' +
      '    emp.id_user_update = :user, ' +
      '    emp.updated_at = CURRENT_TIMESTAMP ' +
      'WHERE invl.id_inventario = :id_inv';
    dmgMain.qryExec.ParamByName('id_inv').AsString := FInventarioId;
    dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
    dmgMain.qryExec.ExecSQL;

    // 2. Marcar inventario como regularizado
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 
      'UPDATE ge_inventarios ' +
      'SET regularizado = 1, fecha_regularizacion = CURRENT_TIMESTAMP, id_user_update = :user, updated_at = CURRENT_TIMESTAMP ' +
      'WHERE id = :id';
    dmgMain.qryExec.ParamByName('id').AsString := FInventarioId;
    dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
    dmgMain.qryExec.ExecSQL;

    ShowMessage('Inventario regularizado con éxito. El stock ha sido actualizado.');
    
    LoadData; // Recargar pantalla en modo sólo lectura
    ResetModifiedState; // Evitar que se detecten cambios no guardados al salir

    if Assigned(FParentForm) and (FParentForm is TfrmInventarios) then
    begin
      TfrmInventarios(FParentForm).btnRefreshClick(nil);
      TfrmInventarios(FParentForm).QryMain.Locate('id', FInventarioId, []);
    end;
  except
    on E: Exception do
      TDbErrorHandler.HandleException(E, 'Error al regularizar el inventario');
  end;
end;

procedure TfrmInventariosEditor.btnExcelClick(Sender: TObject);
var
  SaveDialog: TSaveDialog;
  ExcelApp: OleVariant;
  Workbook: OleVariant;
  Sheet: OleVariant;
  Row: Integer;
  Bookmark: TBookmark;
begin
  if not Assigned(fqryLines) or (not fqryLines.Active) or fqryLines.IsEmpty then
  begin
    ShowMessage('No hay datos para exportar.');
    Exit;
  end;

  SaveDialog := TSaveDialog.Create(Self);
  try
    SaveDialog.Filter := 'Archivos de Excel (*.xlsx)|*.xlsx|Todos los archivos (*.*)|*.*';
    SaveDialog.DefaultExt := 'xlsx';
    SaveDialog.Title := 'Exportar Inventario a Excel';
    SaveDialog.FileName := 'Inventario_' + FormatDateTime('yyyyMMdd_hhmmss', dtpFecha.Date) + '.xlsx';
    
    if SaveDialog.Execute then
    begin
      try
        // Crear objeto OLE para Excel
        ExcelApp := CreateOleObject('Excel.Application');
      except
        on E: Exception do
        begin
          ShowMessage('No se pudo iniciar Microsoft Excel. Asegúrese de tener Excel instalado.' + #13#10 + E.Message);
          Exit;
        end;
      end;
      
      try
        ExcelApp.Visible := False;
        ExcelApp.DisplayAlerts := False;
        Workbook := ExcelApp.Workbooks.Add;
        Sheet := Workbook.ActiveSheet;
        Sheet.Name := 'Inventario';
        
        // Cabeceras
        Sheet.Cells[1, 1] := 'Materia Prima';
        Sheet.Cells[1, 2] := 'Lote';
        Sheet.Cells[1, 3] := 'F. Caducidad';
        Sheet.Cells[1, 4] := 'Stock Teórico';
        Sheet.Cells[1, 5] := 'Cant. Real';
        Sheet.Cells[1, 6] := 'Diferencia';
        
        // Formato para cabeceras (Negrita)
        Sheet.Rows[1].Font.Bold := True;
        
        Row := 2;
        fqryLines.DisableControls;
        Bookmark := fqryLines.GetBookmark;
        try
          fqryLines.First;
          while not fqryLines.Eof do
          begin
            Sheet.Cells[Row, 1] := fqryLines.FieldByName('descripcion').AsString;
            Sheet.Cells[Row, 2] := fqryLines.FieldByName('lote').AsString;
            if not fqryLines.FieldByName('fecha_caducidad').IsNull then
              Sheet.Cells[Row, 3] := FormatDateTime('dd/mm/yyyy', fqryLines.FieldByName('fecha_caducidad').AsDateTime)
            else
              Sheet.Cells[Row, 3] := '';
              
            Sheet.Cells[Row, 4] := fqryLines.FieldByName('stock_teorico').AsFloat;
            Sheet.Cells[Row, 5] := fqryLines.FieldByName('cantidad_real').AsFloat;
            Sheet.Cells[Row, 6] := fqryLines.FieldByName('diferencia').AsFloat;
            
            Inc(Row);
            fqryLines.Next;
          end;
        finally
          if fqryLines.BookmarkValid(Bookmark) then
            fqryLines.GotoBookmark(Bookmark);
          fqryLines.FreeBookmark(Bookmark);
          fqryLines.EnableControls;
        end;
        
        // Ajustar columnas
        Sheet.Columns.AutoFit;
        
        // Guardar archivo (Formato 51 es xlOpenXMLWorkbook para .xlsx)
        Workbook.SaveAs(SaveDialog.FileName, 51);
        Workbook.Close(True);
        ShowMessage('El inventario ha sido exportado correctamente a Excel.');
      finally
        ExcelApp.Quit;
        ExcelApp := Unassigned;
      end;
    end;
  finally
    SaveDialog.Free;
  end;
end;

end.