unit frm_HistorialInventariosMP;

interface

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

type
  TfrmHistorialInventariosMP = class(TForm)
    pnlTop: TPanel;
    lblTitulo: TLabel;
    lblAlmacen: TLabel;
    cbAlmacen: TComboBox;
    lblSearch: TLabel;
    edtSearch: TEdit;
    chkSoloRegularizados: TCheckBox;
    btnRefrescar: TBitBtn;
    btnNuevoInventario: TBitBtn;
    btnAbrirEditor: TBitBtn;
    btnRecalcular: TBitBtn;
    btnEliminar: TBitBtn;
    btnCerrar: TBitBtn;
    pnlMaster: TPanel;
    dbgCabeceras: TDBGrid;
    Splitter1: TSplitter;
    pnlDetail: TPanel;
    pnlDetailHeader: TPanel;
    lblDetailTitulo: TLabel;
    lblSearchLinea: TLabel;
    edtSearchLinea: TEdit;
    dbgLineas: TDBGrid;
    qryCabeceras: TFDQuery;
    dsCabeceras: TDataSource;
    qryLineas: TFDQuery;
    dsLineas: TDataSource;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure cbAlmacenChange(Sender: TObject);
    procedure edtSearchChange(Sender: TObject);
    procedure chkSoloRegularizadosClick(Sender: TObject);
    procedure edtSearchLineaChange(Sender: TObject);
    procedure btnRefrescarClick(Sender: TObject);
    procedure btnNuevoInventarioClick(Sender: TObject);
    procedure btnAbrirEditorClick(Sender: TObject);
    procedure btnRecalcularClick(Sender: TObject);
    procedure btnEliminarClick(Sender: TObject);
    procedure btnCerrarClick(Sender: TObject);
    procedure qryCabecerasAfterScroll(DataSet: TDataSet);
    procedure dbgCabecerasDblClick(Sender: TObject);
    procedure dbgCabecerasDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure dbgLineasDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
  private
    procedure LoadAlmacenes;
    procedure CargarCabeceras;
    procedure CargarLineas(AInventarioId: Integer);
    procedure RegularizadoGetText(Sender: TField; var Text: string; DisplayText: Boolean);
    function ExisteInventarioPosterior(AInventarioId, AEmpresaId, AAlmacenId: Integer; AFecha: TDateTime): Boolean;
  public
  end;

var
  frmHistorialInventariosMP: TfrmHistorialInventariosMP;

implementation

uses
  dmg_Main, frm_Main, frm_InventarioMateriasPrimas, frm_InventariosEditor, uDbErrorHandler;

{$R *.dfm}

procedure TfrmHistorialInventariosMP.FormCreate(Sender: TObject);
begin
  TUIAutoScaler.RegisterForm(Self);
  qryCabeceras.Connection := dmgMain.dbConn;
  qryLineas.Connection := dmgMain.dbConn;
  dsCabeceras.DataSet := qryCabeceras;
  dsLineas.DataSet := qryLineas;
end;

procedure TfrmHistorialInventariosMP.FormDestroy(Sender: TObject);
begin
  TUIAutoScaler.UnregisterForm(Self);
  if frmHistorialInventariosMP = Self then
    frmHistorialInventariosMP := nil;
end;

procedure TfrmHistorialInventariosMP.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  if (Parent <> nil) and (Parent is TTabSheet) then
    Action := caFree
  else
    Action := caHide;
end;

procedure TfrmHistorialInventariosMP.FormShow(Sender: TObject);
begin
  TAppTheme.ApplyToForm(Self);
  LoadAlmacenes;
  CargarCabeceras;
end;

procedure TfrmHistorialInventariosMP.LoadAlmacenes;
var
  LQry: TFDQuery;
  LAlmId: Integer;
begin
  cbAlmacen.Items.Clear;
  cbAlmacen.Items.AddObject('(Todos los obradores)', TObject(IntPtr(0)));
  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;
    LQry.SQL.Text :=
      'SELECT o.id, o.nombre, ' +
      '       COALESCE(NULLIF(o.id_almacen, 0), ' +
      '                (SELECT a.id FROM ge_almacenes a ' +
      '                 WHERE a.activo = 1 AND (a.id_empresa = :emp OR a.compartido = 1) ' +
      '                 ORDER BY a.id LIMIT 1), 1) AS id_almacen ' +
      'FROM ge_obradores o ' +
      'WHERE o.activo = 1 AND (o.id_empresa = :emp OR o.id_empresa IS NULL) ' +
      'ORDER BY o.nombre';
    LQry.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
    LQry.Open;
    while not LQry.Eof do
    begin
      LAlmId := LQry.FieldByName('id_almacen').AsInteger;
      cbAlmacen.Items.AddObject(
        LQry.FieldByName('nombre').AsString,
        TObject(IntPtr(LAlmId)));
      LQry.Next;
    end;
  finally
    LQry.Free;
  end;
  cbAlmacen.ItemIndex := 0;
end;

procedure TfrmHistorialInventariosMP.RegularizadoGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin
  if Sender.AsInteger <> 0 then
    Text := 'SÍ'
  else
    Text := 'NO';
end;

procedure TfrmHistorialInventariosMP.CargarCabeceras;
var
  LAlmId: Integer;
  LSql, LSearch: string;
  LCol: TColumn;
begin
  LAlmId := 0;
  if cbAlmacen.ItemIndex >= 0 then
    LAlmId := Integer(IntPtr(cbAlmacen.Items.Objects[cbAlmacen.ItemIndex]));
  LSearch := Trim(edtSearch.Text);

  qryCabeceras.AfterScroll := nil;
  try
    qryCabeceras.Close;
    LSql :=
      'SELECT inv.id, inv.fecha, inv.id_almacen, ' +
      '       COALESCE((SELECT o.nombre FROM ge_obradores o WHERE o.id_almacen = inv.id_almacen AND o.activo = 1 LIMIT 1), alm.descripcion, '''') AS almacen_nombre, ' +
      '       COALESCE(inv.descripcion, '''') AS descripcion, ' +
      '       COUNT(il.id) AS num_lineas, ' +
      '       COALESCE(SUM(il.stock_teorico), 0) AS total_teorico, ' +
      '       COALESCE(SUM(COALESCE(il.cantidad_real, 0)), 0) AS total_real, ' +
      '       COALESCE(SUM(COALESCE(il.cantidad_real, 0) - il.stock_teorico), 0) AS total_diferencia, ' +
      '       inv.regularizado, inv.fecha_regularizacion ' +
      'FROM ge_inventarios inv ' +
      'LEFT JOIN ge_almacenes alm ON inv.id_almacen = alm.id ' +
      'LEFT JOIN ge_inventarios_lineas il ON il.id_inventario = inv.id ' +
      'WHERE inv.id_empresa = :emp ' +
      '  AND (EXISTS (SELECT 1 FROM ge_inventarios_lineas il2 ' +
      '               WHERE il2.id_inventario = inv.id AND il2.id_materia_prima IS NOT NULL) ' +
      '       OR (NOT EXISTS (SELECT 1 FROM ge_inventarios_lineas il_any WHERE il_any.id_inventario = inv.id) ' +
      '           AND UPPER(COALESCE(inv.descripcion, '''')) NOT LIKE ''%ENVASADO%'' ' +
      '           AND UPPER(COALESCE(inv.descripcion, '''')) NOT LIKE ''%PRODUCTO%'')) ';

    if LAlmId > 0 then
      LSql := LSql + 'AND inv.id_almacen = :alm ';
    if chkSoloRegularizados.Checked then
      LSql := LSql + 'AND inv.regularizado = 1 ';
    if LSearch <> '' then
      LSql := LSql +
        'AND (inv.descripcion LIKE :search OR alm.descripcion LIKE :search ' +
        '     OR EXISTS (SELECT 1 FROM ge_inventarios_lineas il3 ' +
        '                WHERE il3.id_inventario = inv.id ' +
        '                  AND (il3.descripcion LIKE :search OR il3.lote LIKE :search))) ';

    LSql := LSql +
      'GROUP BY inv.id, inv.fecha, inv.id_almacen, alm.descripcion, inv.descripcion, inv.regularizado, inv.fecha_regularizacion ' +
      'ORDER BY inv.fecha DESC, inv.id DESC';

    qryCabeceras.SQL.Text := LSql;
    qryCabeceras.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
    if LAlmId > 0 then
      qryCabeceras.ParamByName('alm').AsInteger := LAlmId;
    if LSearch <> '' then
      qryCabeceras.ParamByName('search').AsString := '%' + LSearch + '%';
    qryCabeceras.Open;

    if Assigned(qryCabeceras.FindField('regularizado')) then
      qryCabeceras.FieldByName('regularizado').OnGetText := RegularizadoGetText;

    dbgCabeceras.Columns.Clear;
    LCol := dbgCabeceras.Columns.Add;
    LCol.FieldName := 'id'; LCol.Title.Caption := 'ID'; LCol.Width := 50;
    LCol := dbgCabeceras.Columns.Add;
    LCol.FieldName := 'fecha'; LCol.Title.Caption := 'Fecha'; LCol.Width := 85;
    LCol := dbgCabeceras.Columns.Add;
    LCol.FieldName := 'almacen_nombre'; LCol.Title.Caption := 'Obrador'; LCol.Width := 160;
    LCol := dbgCabeceras.Columns.Add;
    LCol.FieldName := 'descripcion'; LCol.Title.Caption := 'Descripción'; LCol.Width := 260;
    LCol := dbgCabeceras.Columns.Add;
    LCol.FieldName := 'num_lineas'; LCol.Title.Caption := 'Líneas'; LCol.Width := 60;
    LCol := dbgCabeceras.Columns.Add;
    LCol.FieldName := 'total_teorico'; LCol.Title.Caption := 'Total Teórico'; LCol.Width := 95;
    LCol := dbgCabeceras.Columns.Add;
    LCol.FieldName := 'total_real'; LCol.Title.Caption := 'Total Real'; LCol.Width := 95;
    LCol := dbgCabeceras.Columns.Add;
    LCol.FieldName := 'total_diferencia'; LCol.Title.Caption := 'Diferencia'; LCol.Width := 90;
    LCol := dbgCabeceras.Columns.Add;
    LCol.FieldName := 'regularizado'; LCol.Title.Caption := 'Reg.'; LCol.Width := 50;
    LCol := dbgCabeceras.Columns.Add;
    LCol.FieldName := 'fecha_regularizacion'; LCol.Title.Caption := 'F. Regularización'; LCol.Width := 130;
  finally
    qryCabeceras.AfterScroll := qryCabecerasAfterScroll;
  end;

  if not qryCabeceras.IsEmpty then
    CargarLineas(qryCabeceras.FieldByName('id').AsInteger)
  else
  begin
    qryLineas.Close;
    lblDetailTitulo.Caption := 'Detalle de materias primas del inventario: (ninguno seleccionado)';
  end;
end;

procedure TfrmHistorialInventariosMP.CargarLineas(AInventarioId: Integer);
var
  LSql, LSearch: string;
  LCol: TColumn;
begin
  lblDetailTitulo.Caption := Format('Detalle de materias primas — Inventario #%d (%s)', [
    AInventarioId,
    FormatDateTime('dd/mm/yyyy', qryCabeceras.FieldByName('fecha').AsDateTime)]);

  LSearch := Trim(edtSearchLinea.Text);
  qryLineas.Close;
  LSql :=
    'SELECT il.id_materia_prima, ' +
    '       COALESCE(NULLIF(mp.descripcion_trazabilidad, ''''), mp.descripcion, il.descripcion) AS materia_prima, ' +
    '       COALESCE(il.lote, '''') AS lote, ' +
    '       il.fecha_caducidad, ' +
    '       il.stock_teorico, ' +
    '       il.cantidad_real, ' +
    '       (COALESCE(il.cantidad_real, 0) - il.stock_teorico) AS diferencia ' +
    'FROM ge_inventarios_lineas il ' +
    'LEFT JOIN ge_materias_primas mp ON mp.id = il.id_materia_prima ' +
    'WHERE il.id_inventario = :inv_id ';
  if LSearch <> '' then
    LSql := LSql + 'AND (COALESCE(NULLIF(mp.descripcion_trazabilidad, ''''), mp.descripcion, il.descripcion) LIKE :search OR il.lote LIKE :search) ';
  LSql := LSql + 'ORDER BY COALESCE(NULLIF(mp.descripcion_trazabilidad, ''''), mp.descripcion, il.descripcion) ASC, il.id ASC';

  qryLineas.SQL.Text := LSql;
  qryLineas.ParamByName('inv_id').AsInteger := AInventarioId;
  if LSearch <> '' then
    qryLineas.ParamByName('search').AsString := '%' + LSearch + '%';
  qryLineas.Open;

  dbgLineas.Columns.Clear;
  LCol := dbgLineas.Columns.Add;
  LCol.FieldName := 'id_materia_prima'; LCol.Title.Caption := 'ID M.P.'; LCol.Width := 65;
  LCol := dbgLineas.Columns.Add;
  LCol.FieldName := 'materia_prima'; LCol.Title.Caption := 'Materia Prima'; LCol.Width := 320;
  LCol := dbgLineas.Columns.Add;
  LCol.FieldName := 'lote'; LCol.Title.Caption := 'Lote'; LCol.Width := 130;
  LCol := dbgLineas.Columns.Add;
  LCol.FieldName := 'fecha_caducidad'; LCol.Title.Caption := 'Caducidad'; LCol.Width := 90;
  LCol := dbgLineas.Columns.Add;
  LCol.FieldName := 'stock_teorico'; LCol.Title.Caption := 'Stock Teórico'; LCol.Width := 105;
  LCol := dbgLineas.Columns.Add;
  LCol.FieldName := 'cantidad_real'; LCol.Title.Caption := 'Cantidad Real'; LCol.Width := 105;
  LCol := dbgLineas.Columns.Add;
  LCol.FieldName := 'diferencia'; LCol.Title.Caption := 'Diferencia'; LCol.Width := 100;
end;

procedure TfrmHistorialInventariosMP.qryCabecerasAfterScroll(DataSet: TDataSet);
begin
  if not qryCabeceras.IsEmpty then
    CargarLineas(qryCabeceras.FieldByName('id').AsInteger);
end;

procedure TfrmHistorialInventariosMP.cbAlmacenChange(Sender: TObject);
begin
  CargarCabeceras;
end;

procedure TfrmHistorialInventariosMP.edtSearchChange(Sender: TObject);
begin
  CargarCabeceras;
end;

procedure TfrmHistorialInventariosMP.chkSoloRegularizadosClick(Sender: TObject);
begin
  CargarCabeceras;
end;

procedure TfrmHistorialInventariosMP.edtSearchLineaChange(Sender: TObject);
begin
  if not qryCabeceras.IsEmpty then
    CargarLineas(qryCabeceras.FieldByName('id').AsInteger);
end;

procedure TfrmHistorialInventariosMP.btnRefrescarClick(Sender: TObject);
begin
  CargarCabeceras;
end;

procedure TfrmHistorialInventariosMP.btnNuevoInventarioClick(Sender: TObject);
begin
  if Assigned(frmMain) then
    frmMain.OpenFormInTab(TfrmInventarioMateriasPrimas, 'Inventario Materias Primas');
end;

procedure TfrmHistorialInventariosMP.btnAbrirEditorClick(Sender: TObject);
begin
  if qryCabeceras.IsEmpty then
    Exit;

  if not Assigned(frmInventariosEditor) then
    Application.CreateForm(TfrmInventariosEditor, frmInventariosEditor);

  frmInventariosEditor.ParentForm := Self;
  frmInventariosEditor.InventarioId := qryCabeceras.FieldByName('id').AsString;
  if frmInventariosEditor.ShowModal = mrOk then
    CargarCabeceras;
end;

procedure TfrmHistorialInventariosMP.dbgCabecerasDblClick(Sender: TObject);
begin
  btnAbrirEditorClick(nil);
end;

function TfrmHistorialInventariosMP.ExisteInventarioPosterior(AInventarioId, AEmpresaId,
  AAlmacenId: Integer; AFecha: TDateTime): Boolean;
var
  LQry: TFDQuery;
begin
  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;
    LQry.SQL.Text :=
      'SELECT COUNT(*) FROM ge_inventarios inv ' +
      'WHERE inv.id_empresa = :emp AND inv.id_almacen = :alm ' +
      '  AND (inv.fecha > :fec OR (inv.fecha = :fec AND inv.id > :id)) ' +
      '  AND (EXISTS (SELECT 1 FROM ge_inventarios_lineas il ' +
      '               WHERE il.id_inventario = inv.id AND il.id_materia_prima IS NOT NULL) ' +
      '       OR (NOT EXISTS (SELECT 1 FROM ge_inventarios_lineas il_any WHERE il_any.id_inventario = inv.id) ' +
      '           AND UPPER(COALESCE(inv.descripcion, '''')) NOT LIKE ''%ENVASADO%'' ' +
      '           AND UPPER(COALESCE(inv.descripcion, '''')) NOT LIKE ''%PRODUCTO%''))';
    LQry.ParamByName('emp').AsInteger := AEmpresaId;
    LQry.ParamByName('alm').AsInteger := AAlmacenId;
    LQry.ParamByName('fec').AsDate := AFecha;
    LQry.ParamByName('id').AsInteger := AInventarioId;
    LQry.Open;
    Result := LQry.Fields[0].AsInteger > 0;
  finally
    LQry.Free;
  end;
end;

procedure TfrmHistorialInventariosMP.btnRecalcularClick(Sender: TObject);
var
  LInvId, LEmpId, LAlmId, LPrevInvId, LEntId, LMPId, LFirstEntId: Integer;
  LFechaInv, LPrevFecha: TDateTime;
  LIsReg: Boolean;
  LQryPrev, LQryMP, LQryPrevLin, LQryEnt, LQryCheck, LQryExec: TFDQuery;
  LTeor, LReal, LRestante, LCantCompra, LAsignar: Double;
begin
  if qryCabeceras.IsEmpty then
    Exit;

  LInvId := qryCabeceras.FieldByName('id').AsInteger;
  LEmpId := StrToIntDef(dmgMain.CurrentCompanyId, 1);
  LAlmId := qryCabeceras.FieldByName('id_almacen').AsInteger;
  LFechaInv := qryCabeceras.FieldByName('fecha').AsDateTime;
  LIsReg := qryCabeceras.FieldByName('regularizado').AsInteger <> 0;

  if ExisteInventarioPosterior(LInvId, LEmpId, LAlmId, LFechaInv) then
  begin
    ShowMessage('No se puede recalcular este inventario porque existe un inventario posterior para el mismo almacén.');
    Exit;
  end;

  if MessageDlg(
    Format('Se recalculará el stock teórico del inventario #%d (%s) a partir del inventario anterior y las entradas hasta esa fecha.' +
           sLineBreak + '¿Desea continuar?', [LInvId, FormatDateTime('dd/mm/yyyy', LFechaInv)]),
    mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
    Exit;

  LQryPrev := TFDQuery.Create(nil);
  LQryMP := TFDQuery.Create(nil);
  LQryPrevLin := TFDQuery.Create(nil);
  LQryEnt := TFDQuery.Create(nil);
  LQryCheck := TFDQuery.Create(nil);
  LQryExec := TFDQuery.Create(nil);
  try
    LQryPrev.Connection := dmgMain.dbConn;
    LQryMP.Connection := dmgMain.dbConn;
    LQryPrevLin.Connection := dmgMain.dbConn;
    LQryEnt.Connection := dmgMain.dbConn;
    LQryCheck.Connection := dmgMain.dbConn;
    LQryExec.Connection := dmgMain.dbConn;

    dmgMain.dbConn.StartTransaction;
    try
      // 1. Buscar inventario anterior
      LPrevInvId := 0;
      LPrevFecha := 0;
      LQryPrev.SQL.Text :=
        'SELECT inv.id, inv.fecha FROM ge_inventarios inv ' +
        'WHERE inv.id_empresa = :emp AND inv.id_almacen = :alm ' +
        '  AND (inv.fecha < :fec OR (inv.fecha = :fec AND inv.id < :id)) ' +
        '  AND (EXISTS (SELECT 1 FROM ge_inventarios_lineas il ' +
        '               WHERE il.id_inventario = inv.id AND il.id_materia_prima IS NOT NULL) ' +
        '       OR (NOT EXISTS (SELECT 1 FROM ge_inventarios_lineas il_any WHERE il_any.id_inventario = inv.id) ' +
        '           AND UPPER(COALESCE(inv.descripcion, '''')) NOT LIKE ''%ENVASADO%'' ' +
        '           AND UPPER(COALESCE(inv.descripcion, '''')) NOT LIKE ''%PRODUCTO%'')) ' +
        'ORDER BY inv.fecha DESC, inv.id DESC LIMIT 1';
      LQryPrev.ParamByName('emp').AsInteger := LEmpId;
      LQryPrev.ParamByName('alm').AsInteger := LAlmId;
      LQryPrev.ParamByName('fec').AsDate := LFechaInv;
      LQryPrev.ParamByName('id').AsInteger := LInvId;
      LQryPrev.Open;
      if not LQryPrev.IsEmpty then
      begin
        LPrevInvId := LQryPrev.FieldByName('id').AsInteger;
        LPrevFecha := LQryPrev.FieldByName('fecha').AsDateTime;
      end;
      LQryPrev.Close;

      // 2. Recorrer materias primas inventariables y actualizar/insertar stock_teorico
      LQryMP.SQL.Text :=
        'SELECT id, descripcion FROM ge_materias_primas ' +
        'WHERE activo = 1 AND inventariable = 1 ORDER BY id';
      LQryMP.Open;

      if LPrevInvId > 0 then
      begin
        LQryPrevLin.SQL.Text :=
          'SELECT COALESCE(SUM(COALESCE(il.cantidad_real, il.stock_teorico, 0)), 0) AS qty ' +
          'FROM ge_inventarios_lineas il ' +
          'WHERE il.id_inventario = :prev_inv AND il.id_materia_prima = :mp';

        LQryEnt.SQL.Text :=
          'SELECT COALESCE(SUM(emp.cantidad_compra), 0) AS qty ' +
          'FROM ge_entradas_materias_primas emp ' +
          'LEFT JOIN ge_proveedores_materias_primas pmp ON pmp.id = emp.id_articulo_proveedor ' +
          'WHERE emp.id_empresa = :emp AND emp.id_almacen = :alm ' +
          '  AND (emp.id_materia_prima = :mp OR pmp.id_materia_prima = :mp) ' +
          '  AND COALESCE(emp.fecha_entrada, DATE(emp.created_at)) > :prev_fec ' +
          '  AND COALESCE(emp.fecha_entrada, DATE(emp.created_at)) <= :fec_inv ' +
          '  AND NOT (COALESCE(emp.lote, '''') = ''INVENTARIO'' AND emp.cantidad_compra <= 0)';
      end
      else
      begin
        LQryEnt.SQL.Text :=
          'SELECT COALESCE(SUM(emp.cantidad_compra), 0) AS qty ' +
          'FROM ge_entradas_materias_primas emp ' +
          'LEFT JOIN ge_proveedores_materias_primas pmp ON pmp.id = emp.id_articulo_proveedor ' +
          'WHERE emp.id_empresa = :emp AND emp.id_almacen = :alm ' +
          '  AND (emp.id_materia_prima = :mp OR pmp.id_materia_prima = :mp) ' +
          '  AND COALESCE(emp.fecha_entrada, DATE(emp.created_at)) <= :fec_inv ' +
          '  AND NOT (COALESCE(emp.lote, '''') = ''INVENTARIO'' AND emp.cantidad_compra <= 0)';
      end;

      while not LQryMP.Eof do
      begin
        LMPId := LQryMP.FieldByName('id').AsInteger;
        LTeor := 0.0;

        if LPrevInvId > 0 then
        begin
          LQryPrevLin.Close;
          LQryPrevLin.ParamByName('prev_inv').AsInteger := LPrevInvId;
          LQryPrevLin.ParamByName('mp').AsInteger := LMPId;
          LQryPrevLin.Open;
          LTeor := LTeor + LQryPrevLin.Fields[0].AsFloat;
        end;

        LQryEnt.Close;
        LQryEnt.ParamByName('emp').AsInteger := LEmpId;
        LQryEnt.ParamByName('alm').AsInteger := LAlmId;
        LQryEnt.ParamByName('mp').AsInteger := LMPId;
        LQryEnt.ParamByName('fec_inv').AsDate := LFechaInv;
        if LPrevInvId > 0 then
          LQryEnt.ParamByName('prev_fec').AsDate := LPrevFecha;
        LQryEnt.Open;
        LTeor := LTeor + LQryEnt.Fields[0].AsFloat;

        LQryCheck.Close;
        LQryCheck.SQL.Text :=
          'SELECT id, COALESCE(cantidad_real, 0) AS cantidad_real ' +
          'FROM ge_inventarios_lineas ' +
          'WHERE id_inventario = :inv AND id_materia_prima = :mp ' +
          'ORDER BY id ASC LIMIT 1';
        LQryCheck.ParamByName('inv').AsInteger := LInvId;
        LQryCheck.ParamByName('mp').AsInteger := LMPId;
        LQryCheck.Open;

        if not LQryCheck.IsEmpty then
        begin
          LReal := LQryCheck.FieldByName('cantidad_real').AsFloat;
          LQryExec.Close;
          LQryExec.SQL.Text :=
            'UPDATE ge_inventarios_lineas SET stock_teorico = :teor, id_user_update = :usr WHERE id = :id';
          LQryExec.ParamByName('teor').AsFloat := LTeor;
          LQryExec.ParamByName('usr').AsString := dmgMain.CurrentUserId;
          LQryExec.ParamByName('id').AsInteger := LQryCheck.FieldByName('id').AsInteger;
          LQryExec.ExecSQL;
        end
        else
        begin
          LReal := 0.0;
          LQryExec.Close;
          LQryExec.SQL.Text :=
            'INSERT INTO ge_inventarios_lineas (id_inventario, id_entrada_materia_prima, id_materia_prima, descripcion, lote, fecha_caducidad, stock_teorico, cantidad_real, id_user_creator, id_user_update) ' +
            'VALUES (:inv, NULL, :mp, :des, NULL, NULL, :teor, 0, :usr, :usr)';
          LQryExec.ParamByName('inv').AsInteger := LInvId;
          LQryExec.ParamByName('mp').AsInteger := LMPId;
          LQryExec.ParamByName('des').AsString := LQryMP.FieldByName('descripcion').AsString;
          LQryExec.ParamByName('teor').AsFloat := LTeor;
          LQryExec.ParamByName('usr').AsString := dmgMain.CurrentUserId;
          LQryExec.ExecSQL;
        end;

        // Si está regularizado, reaplicar recorrido FIFO sobre las entradas
        if LIsReg then
        begin
          LRestante := Max(0.0, LReal);
          LFirstEntId := 0;

          LQryEnt.Close;
          LQryEnt.SQL.Text :=
            'SELECT emp.id, emp.cantidad_compra ' +
            'FROM ge_entradas_materias_primas emp ' +
            'LEFT JOIN ge_proveedores_materias_primas pmp ON pmp.id = emp.id_articulo_proveedor ' +
            'WHERE emp.id_empresa = :emp AND emp.id_almacen = :alm ' +
            '  AND (emp.id_materia_prima = :mp OR pmp.id_materia_prima = :mp) ' +
            '  AND COALESCE(emp.fecha_entrada, DATE(emp.created_at)) <= :fec_inv ' +
            '  AND NOT (COALESCE(emp.lote, '''') = ''INVENTARIO'' AND emp.cantidad_compra <= 0) ' +
            'ORDER BY COALESCE(emp.fecha_entrada, DATE(emp.created_at)) DESC, emp.id DESC';
          LQryEnt.ParamByName('emp').AsInteger := LEmpId;
          LQryEnt.ParamByName('alm').AsInteger := LAlmId;
          LQryEnt.ParamByName('mp').AsInteger := LMPId;
          LQryEnt.ParamByName('fec_inv').AsDate := LFechaInv;
          LQryEnt.Open;

          while not LQryEnt.Eof do
          begin
            LEntId := LQryEnt.FieldByName('id').AsInteger;
            if LFirstEntId = 0 then
              LFirstEntId := LEntId;
            LCantCompra := Max(0.0, LQryEnt.FieldByName('cantidad_compra').AsFloat);
            if LRestante > 0.0001 then
            begin
              LAsignar := Min(LCantCompra, LRestante);
              LRestante := LRestante - LAsignar;
            end
            else
              LAsignar := 0.0;

            LQryExec.Close;
            LQryExec.SQL.Text :=
              'UPDATE ge_entradas_materias_primas SET stock_restante = :stk, id_user_update = :usr WHERE id = :id';
            LQryExec.ParamByName('stk').AsFloat := LAsignar;
            LQryExec.ParamByName('usr').AsString := dmgMain.CurrentUserId;
            LQryExec.ParamByName('id').AsInteger := LEntId;
            LQryExec.ExecSQL;

            LQryEnt.Next;
          end;

          if (LRestante > 0.0001) and (LFirstEntId > 0) then
          begin
            LQryExec.Close;
            LQryExec.SQL.Text :=
              'UPDATE ge_entradas_materias_primas SET stock_restante = stock_restante + :extra, id_user_update = :usr WHERE id = :id';
            LQryExec.ParamByName('extra').AsFloat := LRestante;
            LQryExec.ParamByName('usr').AsString := dmgMain.CurrentUserId;
            LQryExec.ParamByName('id').AsInteger := LFirstEntId;
            LQryExec.ExecSQL;
          end;

          // Restaurar SQL de suma para la siguiente materia prima
          if LPrevInvId > 0 then
            LQryEnt.SQL.Text :=
              'SELECT COALESCE(SUM(emp.cantidad_compra), 0) AS qty ' +
              'FROM ge_entradas_materias_primas emp ' +
              'LEFT JOIN ge_proveedores_materias_primas pmp ON pmp.id = emp.id_articulo_proveedor ' +
              'WHERE emp.id_empresa = :emp AND emp.id_almacen = :alm ' +
              '  AND (emp.id_materia_prima = :mp OR pmp.id_materia_prima = :mp) ' +
              '  AND COALESCE(emp.fecha_entrada, DATE(emp.created_at)) > :prev_fec ' +
              '  AND COALESCE(emp.fecha_entrada, DATE(emp.created_at)) <= :fec_inv ' +
              '  AND NOT (COALESCE(emp.lote, '''') = ''INVENTARIO'' AND emp.cantidad_compra <= 0)'
          else
            LQryEnt.SQL.Text :=
              'SELECT COALESCE(SUM(emp.cantidad_compra), 0) AS qty ' +
              'FROM ge_entradas_materias_primas emp ' +
              'LEFT JOIN ge_proveedores_materias_primas pmp ON pmp.id = emp.id_articulo_proveedor ' +
              'WHERE emp.id_empresa = :emp AND emp.id_almacen = :alm ' +
              '  AND (emp.id_materia_prima = :mp OR pmp.id_materia_prima = :mp) ' +
              '  AND COALESCE(emp.fecha_entrada, DATE(emp.created_at)) <= :fec_inv ' +
              '  AND NOT (COALESCE(emp.lote, '''') = ''INVENTARIO'' AND emp.cantidad_compra <= 0)';
        end;

        LQryMP.Next;
      end;

      dmgMain.dbConn.Commit;
      ShowMessage(Format('Inventario #%d recalculado correctamente.', [LInvId]));
      CargarCabeceras;
      qryCabeceras.Locate('id', LInvId, []);
    except
      on E: Exception do
      begin
        if dmgMain.dbConn.InTransaction then
          dmgMain.dbConn.Rollback;
        TDbErrorHandler.HandleException(E, 'Error al recalcular el inventario');
      end;
    end;
  finally
    LQryExec.Free;
    LQryCheck.Free;
    LQryEnt.Free;
    LQryPrevLin.Free;
    LQryMP.Free;
    LQryPrev.Free;
  end;
end;

procedure TfrmHistorialInventariosMP.btnEliminarClick(Sender: TObject);
var
  LInvId, LEmpId, LAlmId: Integer;
  LFechaInv: TDateTime;
  LIsReg: Boolean;
begin
  if qryCabeceras.IsEmpty then
    Exit;

  LInvId := qryCabeceras.FieldByName('id').AsInteger;
  LEmpId := StrToIntDef(dmgMain.CurrentCompanyId, 1);
  LAlmId := qryCabeceras.FieldByName('id_almacen').AsInteger;
  LFechaInv := qryCabeceras.FieldByName('fecha').AsDateTime;
  LIsReg := qryCabeceras.FieldByName('regularizado').AsInteger <> 0;

  if ExisteInventarioPosterior(LInvId, LEmpId, LAlmId, LFechaInv) then
  begin
    ShowMessage('No se puede eliminar este inventario porque existe un inventario posterior para el mismo almacén.');
    Exit;
  end;

  if MessageDlg(
    '¿Está seguro de que desea eliminar este inventario y todas sus líneas?' +
    sLineBreak + 'Si estaba regularizado, se revertirá el stock restante al teórico previo.',
    mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
    Exit;

  dmgMain.dbConn.StartTransaction;
  try
    if LIsReg then
    begin
      dmgMain.qryExec.Close;
      dmgMain.qryExec.SQL.Text :=
        'UPDATE ge_entradas_materias_primas emp ' +
        'JOIN ge_inventarios_lineas il ON il.id_entrada_materia_prima = emp.id ' +
        'SET emp.stock_restante = il.stock_teorico, emp.id_user_update = :usr ' +
        'WHERE il.id_inventario = :id';
      dmgMain.qryExec.ParamByName('usr').AsString := dmgMain.CurrentUserId;
      dmgMain.qryExec.ParamByName('id').AsInteger := LInvId;
      dmgMain.qryExec.ExecSQL;
    end;

    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 'DELETE FROM ge_inventarios WHERE id = :id';
    dmgMain.qryExec.ParamByName('id').AsInteger := LInvId;
    dmgMain.qryExec.ExecSQL;

    dmgMain.dbConn.Commit;
    CargarCabeceras;
  except
    on E: Exception do
    begin
      if dmgMain.dbConn.InTransaction then
        dmgMain.dbConn.Rollback;
      TDbErrorHandler.HandleException(E, 'Error al eliminar el inventario');
    end;
  end;
end;

procedure TfrmHistorialInventariosMP.btnCerrarClick(Sender: TObject);
begin
  Close;
end;

procedure TfrmHistorialInventariosMP.dbgCabecerasDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
var
  LGrid: TDBGrid;
begin
  LGrid := TDBGrid(Sender);
  if not (gdSelected in State) and qryCabeceras.Active and not qryCabeceras.IsEmpty then
  begin
    if Column.FieldName = 'regularizado' then
    begin
      if qryCabeceras.FieldByName('regularizado').AsInteger <> 0 then
        LGrid.Canvas.Font.Color := clGreen
      else
        LGrid.Canvas.Font.Color := clMaroon;
    end;
  end;
  LGrid.DefaultDrawColumnCell(Rect, DataCol, Column, State);
end;

procedure TfrmHistorialInventariosMP.dbgLineasDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
var
  LGrid: TDBGrid;
  LDiff: Double;
begin
  LGrid := TDBGrid(Sender);
  if not (gdSelected in State) and qryLineas.Active and not qryLineas.IsEmpty then
  begin
    if Column.FieldName = 'diferencia' then
    begin
      LDiff := qryLineas.FieldByName('diferencia').AsFloat;
      if Abs(LDiff) <= 0.0001 then
        LGrid.Canvas.Font.Color := clGreen
      else
        LGrid.Canvas.Font.Color := clRed;
    end;
  end;
  LGrid.DefaultDrawColumnCell(Rect, DataCol, Column, State);
end;

end.

