unit frm_InventarioMateriasPrimas;

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.Comp.Client, FireDAC.Stan.Param, FireDAC.Stan.Intf,
  FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf,
  FireDAC.DApt.Intf, FireDAC.Comp.DataSet,
  uAppTheme, uUIAutoScaler;

type
  TfrmInventarioMateriasPrimas = class(TForm)
    pnlMain: TPanel;
    pnlTopToolbar: TPanel;
    lblAlmacen: TLabel;
    cbAlmacen: TComboBox;
    lblFecha: TLabel;
    dtpFecha: TDateTimePicker;
    lblDescripcion: TLabel;
    edtDescripcion: TEdit;
    pnlTopButtons: TPanel;
    btnCopiarTeorico: TBitBtn;
    btnLimpiarConteo: TBitBtn;
    btnVerHistorial: TBitBtn;
    btnGuardarBorrador: TBitBtn;
    btnRegularizar: TBitBtn;
    btnSalir: TBitBtn;
    pnlStats: TPanel;
    pnlCard1: TPanel;
    lblStatTitle1: TLabel;
    lblStatContados: TLabel;
    pnlCard2: TPanel;
    lblStatTitle2: TLabel;
    lblStatTotalReal: TLabel;
    pnlCard3: TPanel;
    lblStatTitle3: TLabel;
    lblStatCero: TLabel;
    pnlCard4: TPanel;
    lblStatTitle4: TLabel;
    lblStatDescuadre: TLabel;
    pnlScanZone: TPanel;
    lblScanPrompt: TLabel;
    lblScanHint: TLabel;
    edtBarcode: TEdit;
    btnBuscarRapido: TBitBtn;
    pnlFilterBar: TPanel;
    rbFiltroTodos: TRadioButton;
    rbFiltroContados: TRadioButton;
    rbFiltroPendientes: TRadioButton;
    rbFiltroDescuadre: TRadioButton;
    lblSearch: TLabel;
    edtSearch: TEdit;
    dbgInventario: TDBGrid;
    mtInventory: TFDMemTable;
    dsInventory: 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 dtpFechaChange(Sender: TObject);
    procedure edtBarcodeKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure btnBuscarRapidoClick(Sender: TObject);
    procedure btnCopiarTeoricoClick(Sender: TObject);
    procedure btnLimpiarConteoClick(Sender: TObject);
    procedure btnVerHistorialClick(Sender: TObject);
    procedure btnGuardarBorradorClick(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 dbgInventarioTitleClick(Column: TColumn);
  private
    FObradorId: Integer;
    FSelectedAlmacenId: Integer;
    FObradorAlmacenMap: TStringList;
    FLastSortColumn: string;
    FLastSortDirection: string;
    FLoadingData: Boolean;
    procedure SetupMemTableAndGrid;
    procedure LoadAlmacenes;
    procedure LoadSystemStock(AAlmacenId: Integer);
    procedure UpdateStats;
    procedure ApplyTableFilter;
    procedure mtInventoryCantidadRealChange(Sender: TField);
    procedure mtInventoryAfterPost(DataSet: TDataSet);
    procedure GuardarInventario(ARegularizar: Boolean);
  public
    property SelectedAlmacenId: Integer read FSelectedAlmacenId write FSelectedAlmacenId;
  end;

var
  frmInventarioMateriasPrimas: TfrmInventarioMateriasPrimas;

implementation

uses
  dmg_Main, frm_Main, frm_HistorialInventariosMP, uDbErrorHandler;

{$R *.dfm}

procedure TfrmInventarioMateriasPrimas.FormCreate(Sender: TObject);
begin
  TUIAutoScaler.RegisterForm(Self);
  FObradorAlmacenMap := TStringList.Create;
  dtpFecha.Date := Date;
  SetupMemTableAndGrid;
end;

procedure TfrmInventarioMateriasPrimas.FormDestroy(Sender: TObject);
begin
  TUIAutoScaler.UnregisterForm(Self);
  FObradorAlmacenMap.Free;
  if frmInventarioMateriasPrimas = Self then
    frmInventarioMateriasPrimas := nil;
end;

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

procedure TfrmInventarioMateriasPrimas.FormShow(Sender: TObject);
begin
  TAppTheme.ApplyToForm(Self);
  try
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text :=
      'UPDATE ge_materias_primas SET inventariable = 1 ' +
      'WHERE COALESCE(inventariable, 0) = 0 ' +
      '  AND (id IN (SELECT DISTINCT id_materia_prima FROM ob_procesos_ingredientes WHERE tipo_origen = ''MP'' AND id_materia_prima IS NOT NULL) ' +
      '       OR id IN (SELECT DISTINCT id_materia_prima FROM ge_formatos_presentacion_materiales WHERE id_materia_prima IS NOT NULL))';
    dmgMain.qryExec.ExecSQL;
  except
    // No bloquear apertura si falla el ajuste preventivo
  end;
  LoadAlmacenes;
end;

procedure TfrmInventarioMateriasPrimas.SetupMemTableAndGrid;
var
  LCol: TColumn;
begin
  mtInventory.Close;
  mtInventory.FieldDefs.Clear;
  mtInventory.FieldDefs.Add('id_materia_prima', ftInteger);
  mtInventory.FieldDefs.Add('descripcion', ftString, 255);
  mtInventory.FieldDefs.Add('codigo_barras', ftString, 100);
  mtInventory.FieldDefs.Add('familia', ftString, 100);
  mtInventory.FieldDefs.Add('stock_teorico', ftFloat);
  mtInventory.FieldDefs.Add('cantidad_real', ftFloat);
  mtInventory.FieldDefs.Add('diferencia', ftFloat);
  mtInventory.FieldDefs.Add('contado', ftInteger);
  mtInventory.FieldDefs.Add('estado', ftString, 20);
  mtInventory.CreateDataSet;

  mtInventory.FieldByName('cantidad_real').OnChange := mtInventoryCantidadRealChange;
  mtInventory.AfterPost := mtInventoryAfterPost;
  dsInventory.DataSet := mtInventory;

  dbgInventario.Columns.Clear;
  LCol := dbgInventario.Columns.Add;
  LCol.FieldName := 'id_materia_prima'; LCol.Title.Caption := 'ID M.P.'; LCol.Width := 65; LCol.ReadOnly := True;
  LCol := dbgInventario.Columns.Add;
  LCol.FieldName := 'descripcion'; LCol.Title.Caption := 'Materia Prima'; LCol.Width := 340; LCol.ReadOnly := True;
  LCol := dbgInventario.Columns.Add;
  LCol.FieldName := 'familia'; LCol.Title.Caption := 'Familia'; LCol.Width := 160; LCol.ReadOnly := True;
  LCol := dbgInventario.Columns.Add;
  LCol.FieldName := 'stock_teorico'; LCol.Title.Caption := 'Stock Teórico'; LCol.Width := 110; LCol.ReadOnly := True;
  LCol := dbgInventario.Columns.Add;
  LCol.FieldName := 'cantidad_real'; LCol.Title.Caption := 'Cantidad Real'; LCol.Width := 110; LCol.ReadOnly := False;
  LCol := dbgInventario.Columns.Add;
  LCol.FieldName := 'diferencia'; LCol.Title.Caption := 'Diferencia'; LCol.Width := 100; LCol.ReadOnly := True;
  LCol := dbgInventario.Columns.Add;
  LCol.FieldName := 'estado'; LCol.Title.Caption := 'Estado'; LCol.Width := 95; LCol.ReadOnly := True;
end;

procedure TfrmInventarioMateriasPrimas.LoadAlmacenes;
var
  LQry: TFDQuery;
  LObrId, LAlmId: Integer;
begin
  cbAlmacen.Items.Clear;
  FObradorAlmacenMap.Clear;
  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
      LObrId := LQry.FieldByName('id').AsInteger;
      LAlmId := LQry.FieldByName('id_almacen').AsInteger;
      cbAlmacen.Items.AddObject(LQry.FieldByName('nombre').AsString, TObject(IntPtr(LObrId)));
      FObradorAlmacenMap.Add(IntToStr(LAlmId));
      LQry.Next;
    end;
  finally
    LQry.Free;
  end;

  if cbAlmacen.Items.Count > 0 then
  begin
    cbAlmacen.ItemIndex := 0;
    cbAlmacenChange(nil);
  end;
end;

procedure TfrmInventarioMateriasPrimas.cbAlmacenChange(Sender: TObject);
begin
  if cbAlmacen.ItemIndex < 0 then
    Exit;
  FObradorId := Integer(IntPtr(cbAlmacen.Items.Objects[cbAlmacen.ItemIndex]));
  if cbAlmacen.ItemIndex < FObradorAlmacenMap.Count then
    FSelectedAlmacenId := StrToIntDef(FObradorAlmacenMap[cbAlmacen.ItemIndex], 1)
  else
    FSelectedAlmacenId := 1;
  edtDescripcion.Text := Format('INVENTARIO MATERIAS PRIMAS - %s', [cbAlmacen.Text]);
  LoadSystemStock(FSelectedAlmacenId);
end;

procedure TfrmInventarioMateriasPrimas.dtpFechaChange(Sender: TObject);
begin
  if FSelectedAlmacenId > 0 then
    LoadSystemStock(FSelectedAlmacenId);
end;

procedure TfrmInventarioMateriasPrimas.LoadSystemStock(AAlmacenId: Integer);
var
  LQryMP, LQryPrevInv, LQryPrevLin, LQryEnt: TFDQuery;
  LMPId, LPrevInvId: Integer;
  LMPDesc, LCodBarras, LFam: string;
  LFechaInv, LPrevFecha: TDateTime;
  LStockPrev, LSumEnt, LStockTeor: Double;
begin
  FLoadingData := True;
  mtInventory.DisableControls;
  LQryMP := TFDQuery.Create(nil);
  LQryPrevInv := TFDQuery.Create(nil);
  LQryPrevLin := TFDQuery.Create(nil);
  LQryEnt := TFDQuery.Create(nil);
  try
    mtInventory.EmptyDataSet;
    LFechaInv := Trunc(dtpFecha.Date);
    LPrevInvId := 0;
    LPrevFecha := 0;

    LQryMP.Connection := dmgMain.dbConn;
    LQryPrevInv.Connection := dmgMain.dbConn;
    LQryPrevLin.Connection := dmgMain.dbConn;
    LQryEnt.Connection := dmgMain.dbConn;

    // 1. Buscar inventario anterior de materias primas para este almacén (fecha < fecha_inv)
    LQryPrevInv.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 ' +
      '  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';
    LQryPrevInv.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
    LQryPrevInv.ParamByName('alm').AsInteger := AAlmacenId;
    LQryPrevInv.ParamByName('fec').AsDate := LFechaInv;
    LQryPrevInv.Open;
    if not LQryPrevInv.IsEmpty then
    begin
      LPrevInvId := LQryPrevInv.FieldByName('id').AsInteger;
      LPrevFecha := LQryPrevInv.FieldByName('fecha').AsDateTime;
    end;
    LQryPrevInv.Close;

    if LPrevInvId > 0 then
      lblScanHint.Caption := Format('Teórico: Inv. anterior (%s) + suma de entradas hasta %s', [
        FormatDateTime('dd/mm/yyyy', LPrevFecha), FormatDateTime('dd/mm/yyyy', LFechaInv)])
    else
      lblScanHint.Caption := Format('Teórico: Suma de entradas hasta %s', [
        FormatDateTime('dd/mm/yyyy', LFechaInv)]);

    LQryMP.SQL.Text :=
      'SELECT mp.id, mp.descripcion, COALESCE(mp.codigo_barras, '''') AS codigo_barras, ' +
      '       COALESCE(f.descripcion, '''') AS familia ' +
      'FROM ge_materias_primas mp ' +
      'LEFT JOIN ge_familias f ON mp.familia_id = f.id ' +
      'WHERE mp.activo = 1 AND mp.inventariable = 1 ' +
      'ORDER BY mp.descripcion ASC';
    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 = :inv_id AND il.id_materia_prima = :mp';

      LQryEnt.SQL.Text :=
        'SELECT COALESCE(SUM(t.cant), 0) AS qty FROM ( ' +
        '  SELECT d.cantidad AS cant ' +
        '  FROM ge_prov_albaranes_detalle d ' +
        '  INNER JOIN ge_prov_albaranes alb ON d.idalbaran = alb.id ' +
        '  WHERE d.idempresa = :emp AND COALESCE(d.en_obrador, alb.id_obrador, 0) = :obr ' +
        '    AND alb.fecha > :prev_fec AND alb.fecha <= :fec_inv ' +
        '    AND (d.id_propio = :mp OR EXISTS ( ' +
        '      SELECT 1 FROM ge_proveedores_materias_primas pmp ' +
        '      WHERE pmp.id_proveedor = d.idproveedor AND pmp.codigo_articulo_proveedor = d.codigo ' +
        '        AND pmp.id_materia_prima = :mp AND pmp.activo = 1)) ' +
        '  UNION ALL ' +
        '  SELECT d.cantidad AS cant ' +
        '  FROM ge_prov_facturas_detalle d ' +
        '  INNER JOIN ge_prov_facturas fac ON d.idfactura = fac.id AND d.idempresa = fac.idempresa ' +
        '  WHERE d.idempresa = :emp AND COALESCE(d.en_obrador, 0) = :obr ' +
        '    AND fac.fecha > :prev_fec AND fac.fecha <= :fec_inv ' +
        '    AND (d.id_propio = :mp OR EXISTS ( ' +
        '      SELECT 1 FROM ge_proveedores_materias_primas pmp ' +
        '      WHERE pmp.id_proveedor = d.idproveedor AND pmp.codigo_articulo_proveedor = d.codigo ' +
        '        AND pmp.id_materia_prima = :mp AND pmp.activo = 1)) ' +
        ') t';
    end
    else
    begin
      LQryEnt.SQL.Text :=
        'SELECT COALESCE(SUM(t.cant), 0) AS qty FROM ( ' +
        '  SELECT d.cantidad AS cant ' +
        '  FROM ge_prov_albaranes_detalle d ' +
        '  INNER JOIN ge_prov_albaranes alb ON d.idalbaran = alb.id ' +
        '  WHERE d.idempresa = :emp AND COALESCE(d.en_obrador, alb.id_obrador, 0) = :obr ' +
        '    AND alb.fecha <= :fec_inv ' +
        '    AND (d.id_propio = :mp OR EXISTS ( ' +
        '      SELECT 1 FROM ge_proveedores_materias_primas pmp ' +
        '      WHERE pmp.id_proveedor = d.idproveedor AND pmp.codigo_articulo_proveedor = d.codigo ' +
        '        AND pmp.id_materia_prima = :mp AND pmp.activo = 1)) ' +
        '  UNION ALL ' +
        '  SELECT d.cantidad AS cant ' +
        '  FROM ge_prov_facturas_detalle d ' +
        '  INNER JOIN ge_prov_facturas fac ON d.idfactura = fac.id AND d.idempresa = fac.idempresa ' +
        '  WHERE d.idempresa = :emp AND COALESCE(d.en_obrador, 0) = :obr ' +
        '    AND fac.fecha <= :fec_inv ' +
        '    AND (d.id_propio = :mp OR EXISTS ( ' +
        '      SELECT 1 FROM ge_proveedores_materias_primas pmp ' +
        '      WHERE pmp.id_proveedor = d.idproveedor AND pmp.codigo_articulo_proveedor = d.codigo ' +
        '        AND pmp.id_materia_prima = :mp AND pmp.activo = 1)) ' +
        ') t';
    end;

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

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

      LQryEnt.Close;
      LQryEnt.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
      LQryEnt.ParamByName('obr').AsInteger := FObradorId;
      LQryEnt.ParamByName('mp').AsInteger := LMPId;
      LQryEnt.ParamByName('fec_inv').AsDate := LFechaInv;
      if LPrevInvId > 0 then
        LQryEnt.ParamByName('prev_fec').AsDate := LPrevFecha;
      LQryEnt.Open;
      LSumEnt := LQryEnt.Fields[0].AsFloat;

      LStockTeor := LStockPrev + LSumEnt;

      mtInventory.Append;
      mtInventory.FieldByName('id_materia_prima').AsInteger := LMPId;
      mtInventory.FieldByName('descripcion').AsString := LMPDesc;
      mtInventory.FieldByName('codigo_barras').AsString := LCodBarras;
      mtInventory.FieldByName('familia').AsString := LFam;
      mtInventory.FieldByName('stock_teorico').AsFloat := LStockTeor;
      mtInventory.FieldByName('cantidad_real').AsFloat := 0.0;
      mtInventory.FieldByName('diferencia').AsFloat := -LStockTeor;
      mtInventory.FieldByName('contado').AsInteger := 0;
      mtInventory.FieldByName('estado').AsString := 'PENDIENTE';
      mtInventory.Post;

      LQryMP.Next;
    end;
    mtInventory.First;
  finally
    LQryEnt.Free;
    LQryPrevLin.Free;
    LQryPrevInv.Free;
    LQryMP.Free;
    mtInventory.EnableControls;
    FLoadingData := False;
  end;

  UpdateStats;
  ApplyTableFilter;
end;

procedure TfrmInventarioMateriasPrimas.mtInventoryCantidadRealChange(Sender: TField);
var
  LTeor, LReal, LDiff: Double;
begin
  if FLoadingData then
    Exit;
  LTeor := mtInventory.FieldByName('stock_teorico').AsFloat;
  LReal := Sender.AsFloat;
  LDiff := LReal - LTeor;

  FLoadingData := True;
  try
    mtInventory.FieldByName('diferencia').AsFloat := LDiff;
    mtInventory.FieldByName('contado').AsInteger := 1;
    if Abs(LDiff) <= 0.0001 then
      mtInventory.FieldByName('estado').AsString := 'OK'
    else
      mtInventory.FieldByName('estado').AsString := 'DESCUADRE';
  finally
    FLoadingData := False;
  end;
end;

procedure TfrmInventarioMateriasPrimas.mtInventoryAfterPost(DataSet: TDataSet);
begin
  if not FLoadingData then
    UpdateStats;
end;

procedure TfrmInventarioMateriasPrimas.UpdateStats;
var
  LBM: TBookmark;
  LContados, LCero: Integer;
  LTotalReal, LDescuadre: Double;
begin
  LContados := 0;
  LCero := 0;
  LTotalReal := 0;
  LDescuadre := 0;

  mtInventory.DisableControls;
  LBM := mtInventory.GetBookmark;
  try
    mtInventory.First;
    while not mtInventory.Eof do
    begin
      if mtInventory.FieldByName('contado').AsInteger <> 0 then
      begin
        Inc(LContados);
        LTotalReal := LTotalReal + mtInventory.FieldByName('cantidad_real').AsFloat;
        if mtInventory.FieldByName('cantidad_real').AsFloat <= 0.0001 then
          Inc(LCero);
      end;
      LDescuadre := LDescuadre + mtInventory.FieldByName('diferencia').AsFloat;
      mtInventory.Next;
    end;
  finally
    if mtInventory.BookmarkValid(LBM) then
      mtInventory.GotoBookmark(LBM);
    mtInventory.FreeBookmark(LBM);
    mtInventory.EnableControls;
  end;

  lblStatContados.Caption := IntToStr(LContados);
  lblStatTotalReal.Caption := FormatFloat('#,##0.00', LTotalReal);
  lblStatCero.Caption := IntToStr(LCero);
  lblStatDescuadre.Caption := FormatFloat('+#,##0.00;-#,##0.00;0.00', LDescuadre);
end;

procedure TfrmInventarioMateriasPrimas.ApplyTableFilter;
var
  LFiltro, LTexto: string;
begin
  LFiltro := '';
  if rbFiltroContados.Checked then
    LFiltro := 'contado = 1'
  else if rbFiltroPendientes.Checked then
    LFiltro := 'contado = 0'
  else if rbFiltroDescuadre.Checked then
    LFiltro := 'estado = ''DESCUADRE''';

  LTexto := Trim(StringReplace(edtSearch.Text, '''', '''''', [rfReplaceAll]));
  if LTexto <> '' then
  begin
    if LFiltro <> '' then
      LFiltro := LFiltro + ' AND ';
    LFiltro := LFiltro + Format(
      '(descripcion LIKE ''%%%s%%'' OR codigo_barras LIKE ''%%%s%%'')',
      [LTexto, LTexto]);
  end;

  mtInventory.Filtered := False;
  mtInventory.Filter := LFiltro;
  mtInventory.Filtered := LFiltro <> '';
end;

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

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

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

procedure TfrmInventarioMateriasPrimas.btnBuscarRapidoClick(Sender: TObject);
var
  LCode, LInput: string;
  LFound: Boolean;
  LQty: Double;
begin
  LCode := Trim(edtBarcode.Text);
  if LCode = '' then
    Exit;

  LFound := False;
  mtInventory.First;
  while not mtInventory.Eof do
  begin
    if SameText(mtInventory.FieldByName('codigo_barras').AsString, LCode) or
       (Pos(UpperCase(LCode), UpperCase(mtInventory.FieldByName('descripcion').AsString)) > 0) then
    begin
      LFound := True;
      Break;
    end;
    mtInventory.Next;
  end;

  if not LFound then
  begin
    ShowMessage('No se encontró ninguna materia prima con ese código/texto.');
    edtBarcode.SelectAll;
    Exit;
  end;

  LInput := FloatToStr(mtInventory.FieldByName('cantidad_real').AsFloat);
  if InputQuery(
    'Conteo de Materia Prima',
    Format('%s (Teórico: %.2f):', [
      mtInventory.FieldByName('descripcion').AsString,
      mtInventory.FieldByName('stock_teorico').AsFloat]),
    LInput) then
  begin
    LInput := StringReplace(Trim(LInput), '.', FormatSettings.DecimalSeparator, [rfReplaceAll]);
    LInput := StringReplace(LInput, ',', FormatSettings.DecimalSeparator, [rfReplaceAll]);
    if TryStrToFloat(LInput, LQty) then
    begin
      mtInventory.Edit;
      mtInventory.FieldByName('cantidad_real').AsFloat := LQty;
      mtInventory.Post;
    end;
  end;

  edtBarcode.Clear;
  edtBarcode.SetFocus;
end;

procedure TfrmInventarioMateriasPrimas.btnCopiarTeoricoClick(Sender: TObject);
begin
  if MessageDlg('¿Desea copiar el Stock Teórico a la Cantidad Real en todas las filas visibles?',
    mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
    Exit;

  mtInventory.DisableControls;
  try
    mtInventory.First;
    while not mtInventory.Eof do
    begin
      mtInventory.Edit;
      mtInventory.FieldByName('cantidad_real').AsFloat := mtInventory.FieldByName('stock_teorico').AsFloat;
      mtInventory.Post;
      mtInventory.Next;
    end;
    mtInventory.First;
  finally
    mtInventory.EnableControls;
  end;
  UpdateStats;
end;

procedure TfrmInventarioMateriasPrimas.btnLimpiarConteoClick(Sender: TObject);
begin
  if MessageDlg('¿Desea reiniciar el conteo de todas las materias primas a 0?',
    mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
    Exit;

  FLoadingData := True;
  mtInventory.DisableControls;
  try
    mtInventory.First;
    while not mtInventory.Eof do
    begin
      mtInventory.Edit;
      mtInventory.FieldByName('cantidad_real').AsFloat := 0.0;
      mtInventory.FieldByName('diferencia').AsFloat := -mtInventory.FieldByName('stock_teorico').AsFloat;
      mtInventory.FieldByName('contado').AsInteger := 0;
      mtInventory.FieldByName('estado').AsString := 'PENDIENTE';
      mtInventory.Post;
      mtInventory.Next;
    end;
    mtInventory.First;
  finally
    mtInventory.EnableControls;
    FLoadingData := False;
  end;
  UpdateStats;
end;

procedure TfrmInventarioMateriasPrimas.btnVerHistorialClick(Sender: TObject);
begin
  if Assigned(frmMain) then
    frmMain.OpenFormInTab(TfrmHistorialInventariosMP, 'Historial Inventarios M.P.');
end;

procedure TfrmInventarioMateriasPrimas.btnGuardarBorradorClick(Sender: TObject);
begin
  GuardarInventario(False);
end;

procedure TfrmInventarioMateriasPrimas.btnRegularizarClick(Sender: TObject);
begin
  if MessageDlg(
    'Se creará el inventario de materias primas y se actualizará el stock restante de las entradas del obrador seleccionado.' +
    sLineBreak + sLineBreak + '¿Desea continuar?',
    mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
    Exit;

  GuardarInventario(True);
end;

procedure TfrmInventarioMateriasPrimas.GuardarInventario(ARegularizar: Boolean);
var
  LQry, LQryEnt, LQryUpd: TFDQuery;
  LInvId, LMPId, LProvId, LEntId, LFirstEntId: Integer;
  LReal, LTeor, LRestante, LCantCompra, LAsignar: Double;
  LDesc: string;
begin
  if FSelectedAlmacenId <= 0 then
  begin
    ShowMessage('Seleccione un obrador válido con almacén asociado.');
    Exit;
  end;

  if mtInventory.State in [dsEdit, dsInsert] then
    mtInventory.Post;

  if mtInventory.IsEmpty then
  begin
    ShowMessage('No hay líneas de materias primas para guardar.');
    Exit;
  end;

  LDesc := Trim(edtDescripcion.Text);
  if LDesc = '' then
    LDesc := Format('INVENTARIO MATERIAS PRIMAS - %s', [cbAlmacen.Text]);

  LQry := TFDQuery.Create(nil);
  LQryEnt := TFDQuery.Create(nil);
  LQryUpd := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;
    LQryEnt.Connection := dmgMain.dbConn;
    LQryUpd.Connection := dmgMain.dbConn;
    dmgMain.dbConn.StartTransaction;
    try
      // 1. Insertar cabecera en ge_inventarios
      LQry.SQL.Text :=
        'INSERT INTO ge_inventarios ' +
        '  (id_empresa, id_almacen, fecha, descripcion, regularizado, fecha_regularizacion, id_user_creator, id_user_update) ' +
        'VALUES (:emp, :alm, :fec, :des, :reg, ' +
        '  CASE WHEN :reg = 1 THEN NOW() ELSE NULL END, :usr, :usr)';
      LQry.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
      LQry.ParamByName('alm').AsInteger := FSelectedAlmacenId;
      LQry.ParamByName('fec').AsDate := dtpFecha.Date;
      LQry.ParamByName('des').AsString := LDesc;
      if ARegularizar then
        LQry.ParamByName('reg').AsInteger := 1
      else
        LQry.ParamByName('reg').AsInteger := 0;
      LQry.ParamByName('usr').AsString := dmgMain.CurrentUserId;
      LQry.ExecSQL;

      LQry.Close;
      LQry.SQL.Text := 'SELECT LAST_INSERT_ID() AS new_id';
      LQry.Open;
      LInvId := LQry.FieldByName('new_id').AsInteger;
      LQry.Close;

      // Proveedor fallback por si hay stock real sin entradas previas
      LQry.SQL.Text := 'SELECT id FROM ge_proveedores WHERE activo = 1 ORDER BY id LIMIT 1';
      LQry.Open;
      if not LQry.IsEmpty then
        LProvId := LQry.Fields[0].AsInteger
      else
        LProvId := 1;
      LQry.Close;

      LQryEnt.SQL.Text :=
        'SELECT t.tipo_doc, t.linea_id, t.fecha_doc, t.cant FROM ( ' +
        '  SELECT ''ALBARAN'' AS tipo_doc, d.id AS linea_id, alb.fecha AS fecha_doc, d.cantidad AS cant ' +
        '  FROM ge_prov_albaranes_detalle d ' +
        '  INNER JOIN ge_prov_albaranes alb ON d.idalbaran = alb.id ' +
        '  WHERE d.idempresa = :emp AND COALESCE(d.en_obrador, alb.id_obrador, 0) = :obr ' +
        '    AND alb.fecha <= :fec ' +
        '    AND (d.id_propio = :mp OR EXISTS ( ' +
        '      SELECT 1 FROM ge_proveedores_materias_primas pmp ' +
        '      WHERE pmp.id_proveedor = d.idproveedor AND pmp.codigo_articulo_proveedor = d.codigo ' +
        '        AND pmp.id_materia_prima = :mp AND pmp.activo = 1)) ' +
        '  UNION ALL ' +
        '  SELECT ''FACTURA'' AS tipo_doc, d.id AS linea_id, fac.fecha AS fecha_doc, d.cantidad AS cant ' +
        '  FROM ge_prov_facturas_detalle d ' +
        '  INNER JOIN ge_prov_facturas fac ON d.idfactura = fac.id AND d.idempresa = fac.idempresa ' +
        '  WHERE d.idempresa = :emp AND COALESCE(d.en_obrador, 0) = :obr ' +
        '    AND fac.fecha <= :fec ' +
        '    AND (d.id_propio = :mp OR EXISTS ( ' +
        '      SELECT 1 FROM ge_proveedores_materias_primas pmp ' +
        '      WHERE pmp.id_proveedor = d.idproveedor AND pmp.codigo_articulo_proveedor = d.codigo ' +
        '        AND pmp.id_materia_prima = :mp AND pmp.activo = 1)) ' +
        ') t ' +
        'ORDER BY t.fecha_doc DESC, t.linea_id DESC';

      mtInventory.DisableControls;
      try
        mtInventory.Filtered := False;
        mtInventory.First;
        while not mtInventory.Eof do
        begin
          LMPId := mtInventory.FieldByName('id_materia_prima').AsInteger;
          LTeor := mtInventory.FieldByName('stock_teorico').AsFloat;
          LReal := mtInventory.FieldByName('cantidad_real').AsFloat;

          // 2. Insertar 1 línea por materia prima (sin lote ni caducidad)
          LQry.Close;
          LQry.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, :real, :usr, :usr)';
          LQry.ParamByName('inv').AsInteger := LInvId;
          LQry.ParamByName('mp').AsInteger := LMPId;
          LQry.ParamByName('des').AsString := mtInventory.FieldByName('descripcion').AsString;
          LQry.ParamByName('teor').AsFloat := LTeor;
          if (mtInventory.FieldByName('contado').AsInteger <> 0) or ARegularizar then
            LQry.ParamByName('real').AsFloat := LReal
          else
            LQry.ParamByName('real').Clear;
          LQry.ParamByName('usr').AsString := dmgMain.CurrentUserId;
          LQry.ExecSQL;

          // 3. Si se regulariza: recorrido FIFO sobre entradas de materias primas
          //    (las más antiguas se consumen primero; el stock real queda en las más recientes)
          if ARegularizar then
          begin
            LRestante := Max(0.0, LReal);
            LFirstEntId := 0;
            var LFirstTipoDoc := '';

            LQryEnt.Close;
            LQryEnt.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
            LQryEnt.ParamByName('obr').AsInteger := FObradorId;
            LQryEnt.ParamByName('mp').AsInteger := LMPId;
            LQryEnt.ParamByName('fec').AsDate := dtpFecha.Date;
            LQryEnt.Open;

            while not LQryEnt.Eof do
            begin
              var LTipoDoc := LQryEnt.FieldByName('tipo_doc').AsString;
              LEntId := LQryEnt.FieldByName('linea_id').AsInteger;
              if LFirstEntId = 0 then
              begin
                LFirstEntId := LEntId;
                LFirstTipoDoc := LTipoDoc;
              end;
              LCantCompra := Max(0.0, LQryEnt.FieldByName('cant').AsFloat);

              if LRestante > 0.0001 then
              begin
                LAsignar := Min(LCantCompra, LRestante);
                LRestante := LRestante - LAsignar;
              end
              else
                LAsignar := 0.0;

              LQryUpd.Close;
              if LTipoDoc = 'ALBARAN' then
                LQryUpd.SQL.Text := 'UPDATE ge_prov_albaranes_detalle SET stock = :stk WHERE id = :id'
              else
                LQryUpd.SQL.Text := 'UPDATE ge_prov_facturas_detalle SET stock = :stk WHERE id = :id';
              LQryUpd.ParamByName('stk').AsFloat := LAsignar;
              LQryUpd.ParamByName('id').AsInteger := LEntId;
              LQryUpd.ExecSQL;

              // Sincronizar ge_entradas_materias_primas si existe registro vinculado
              LQryUpd.Close;
              LQryUpd.SQL.Text :=
                'UPDATE ge_entradas_materias_primas ' +
                'SET stock_restante = :stk, id_user_update = :usr ' +
                'WHERE origen_documento = :doc AND origen_id = :id';
              LQryUpd.ParamByName('stk').AsFloat := LAsignar;
              LQryUpd.ParamByName('usr').AsString := dmgMain.CurrentUserId;
              LQryUpd.ParamByName('doc').AsString := LTipoDoc;
              LQryUpd.ParamByName('id').AsInteger := LEntId;
              LQryUpd.ExecSQL;

              LQryEnt.Next;
            end;

            // Si queda sobrante real (> suma de entradas), sumarlo a la entrada más reciente
            if (LRestante > 0.0001) and (LFirstEntId > 0) then
            begin
              LQryUpd.Close;
              if LFirstTipoDoc = 'ALBARAN' then
                LQryUpd.SQL.Text := 'UPDATE ge_prov_albaranes_detalle SET stock = stock + :extra WHERE id = :id'
              else
                LQryUpd.SQL.Text := 'UPDATE ge_prov_facturas_detalle SET stock = stock + :extra WHERE id = :id';
              LQryUpd.ParamByName('extra').AsFloat := LRestante;
              LQryUpd.ParamByName('id').AsInteger := LFirstEntId;
              LQryUpd.ExecSQL;
            end;
          end;

          mtInventory.Next;
        end;
      finally
        ApplyTableFilter;
        mtInventory.EnableControls;
      end;

      dmgMain.dbConn.Commit;
      if ARegularizar then
        ShowMessage(Format('Inventario #%d creado y regularizado correctamente (FIFO).', [LInvId]))
      else
        ShowMessage(Format('Inventario #%d guardado como borrador.', [LInvId]));

      LoadSystemStock(FSelectedAlmacenId);
    except
      on E: Exception do
      begin
        if dmgMain.dbConn.InTransaction then
          dmgMain.dbConn.Rollback;
        TDbErrorHandler.HandleException(E, 'Guardar inventario de materias primas');
      end;
    end;
  finally
    LQryUpd.Free;
    LQryEnt.Free;
    LQry.Free;
  end;
end;

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

procedure TfrmInventarioMateriasPrimas.dbgInventarioDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
var
  LEstado: string;
  LGrid: TDBGrid;
begin
  LGrid := TDBGrid(Sender);
  if not (gdSelected in State) and Assigned(mtInventory) and not mtInventory.IsEmpty then
  begin
    LEstado := mtInventory.FieldByName('estado').AsString;
    if Column.FieldName = 'estado' then
    begin
      if LEstado = 'OK' then
        LGrid.Canvas.Font.Color := clGreen
      else if LEstado = 'DESCUADRE' then
        LGrid.Canvas.Font.Color := clRed
      else
        LGrid.Canvas.Font.Color := clGray;
    end;
  end;
  LGrid.DefaultDrawColumnCell(Rect, DataCol, Column, State);
end;

procedure TfrmInventarioMateriasPrimas.dbgInventarioTitleClick(Column: TColumn);
var
  LField: string;
begin
  LField := Column.FieldName;
  if LField = '' then
    Exit;
  if FLastSortColumn = LField then
  begin
    if FLastSortDirection = 'ASC' then
      FLastSortDirection := 'DESC'
    else
      FLastSortDirection := 'ASC';
  end
  else
  begin
    FLastSortColumn := LField;
    FLastSortDirection := 'ASC';
  end;

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

end.

