unit frm_StockMateriasPrimas;

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.Grids,
  Vcl.DBGrids, Data.DB, Vcl.Buttons, System.UITypes, uAppTheme, FireDAC.Stan.Param, FireDAC.Comp.Client,
  frm_BaseList;

type
  TfrmStockMateriasPrimas = class(TfrmBaseList)
    pnlSummaryStock: TPanel;
    lblTotalStockTitulo: TLabel;
    lblTotalStockValor: TLabel;
    grpObradores: TGroupBox;
    cbObradorFilter: TComboBox;
    grpStock: TGroupBox;
    chkSoloConStock: TCheckBox;
    grpFamilias: TGroupBox;
    cbFamiliaFilter: TComboBox;
    grpTipoDoc: TGroupBox;
    cbTipoDocFilter: TComboBox;
    procedure FormShow(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btnRefreshClick(Sender: TObject); override;
    procedure btnEditClick(Sender: TObject); override;
    procedure OnFilterControlChange(Sender: TObject);
  private
    { Private declarations }
    FObradorIds: TStringList;
    FFamiliaIds: TStringList;

    procedure CargarCombosFiltro;
    procedure FormatoPrecioGetText(Sender: TField; var Text: string; DisplayText: Boolean);
    procedure FormatoCantidadGetText(Sender: TField; var Text: string; DisplayText: Boolean);
    procedure ActualizarResumenStock;
  protected
    procedure dbGridMainDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState); override;
    procedure ApplyAllFilters; override;
  public
    { Public declarations }
  end;

var
  frmStockMateriasPrimas: TfrmStockMateriasPrimas;

implementation

uses
  dmg_Main, frm_ProvAlbaranesEditor, frm_ProvFacturasEditor, uDbErrorHandler;

{$R *.dfm}

procedure TfrmStockMateriasPrimas.FormShow(Sender: TObject);
begin
  AlphaFilterField := 'materia_prima';
  DateFilterField := 'fecha_doc';

  // Ocultar botones de inserción y borrado directo (el stock se nutre de albaranes y facturas)
  btnNuevo.Visible := False;
  btnBorrar.Visible := False;

  // Adaptar botón editar para abrir el documento de origen
  btnEditar.Caption := 'Ver Doc.';
  btnEditar.Hint := 'Abrir el Albarán o Factura de origen';
  btnEditar.ShowHint := True;

  pnlSearch.Visible := True;
  lblSearch.Caption := 'Buscar M.P. / Lote / Prov:';

  if not Assigned(FObradorIds) then
    FObradorIds := TStringList.Create;
  if not Assigned(FFamiliaIds) then
    FFamiliaIds := TStringList.Create;

  CargarCombosFiltro;

  inherited;
  frmStockMateriasPrimas := Self;
  OnDestroy := FormDestroy;

  if Assigned(rbDateAll) then
    rbDateAll.Checked := True;

  btnRefreshClick(Self);
end;

procedure TfrmStockMateriasPrimas.FormDestroy(Sender: TObject);
begin
  if Assigned(FObradorIds) then
    FreeAndNil(FObradorIds);
  if Assigned(FFamiliaIds) then
    FreeAndNil(FFamiliaIds);

  if frmStockMateriasPrimas = Self then
    frmStockMateriasPrimas := nil;
end;

procedure TfrmStockMateriasPrimas.CargarCombosFiltro;
var
  LQry: TFDQuery;
begin
  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;

    // Obradores
    cbObradorFilter.Items.BeginUpdate;
    try
      cbObradorFilter.Items.Clear;
      cbObradorFilter.Items.Add('[Todos los Obradores]');
      FObradorIds.Clear;

      LQry.SQL.Text := 'SELECT id, nombre FROM ge_obradores WHERE activo = 1 ORDER BY nombre';
      LQry.Open;
      while not LQry.Eof do
      begin
        cbObradorFilter.Items.Add(LQry.FieldByName('nombre').AsString);
        FObradorIds.Add(LQry.FieldByName('id').AsString);
        LQry.Next;
      end;
      cbObradorFilter.ItemIndex := 0;
    finally
      cbObradorFilter.Items.EndUpdate;
    end;

    // Familias
    cbFamiliaFilter.Items.BeginUpdate;
    try
      cbFamiliaFilter.Items.Clear;
      cbFamiliaFilter.Items.Add('[Todas las Familias]');
      FFamiliaIds.Clear;

      LQry.Close;
      LQry.SQL.Text := 'SELECT id, descripcion FROM ge_familias WHERE activo = 1 ORDER BY descripcion';
      LQry.Open;
      while not LQry.Eof do
      begin
        cbFamiliaFilter.Items.Add(LQry.FieldByName('descripcion').AsString);
        FFamiliaIds.Add(LQry.FieldByName('id').AsString);
        LQry.Next;
      end;
      cbFamiliaFilter.ItemIndex := 0;
    finally
      cbFamiliaFilter.Items.EndUpdate;
    end;
  finally
    LQry.Free;
  end;
end;

procedure TfrmStockMateriasPrimas.OnFilterControlChange(Sender: TObject);
begin
  btnRefreshClick(Self);
end;

procedure TfrmStockMateriasPrimas.FormatoPrecioGetText(Sender: TField; var Text: string; DisplayText: Boolean);
begin
  if Sender.IsNull then
    Text := '---'
  else
    Text := FormatFloat('#,##0.0000 €', Sender.AsFloat);
end;

procedure TfrmStockMateriasPrimas.FormatoCantidadGetText(Sender: TField; var Text: string; DisplayText: Boolean);
begin
  if Sender.IsNull then
    Text := '0,00'
  else
    Text := FormatFloat('#,##0.00', Sender.AsFloat);
end;

procedure TfrmStockMateriasPrimas.btnRefreshClick(Sender: TObject);
var
  LSql: string;
  LSearch: string;
begin
  FQryMain.Close;

  LSql :=
    'SELECT * FROM (' +
    '  SELECT ' +
    '    ''ALBARAN'' AS tipo_doc, ' +
    '    d.id AS linea_id, ' +
    '    d.idempresa, ' +
    '    d.idalbaran AS doc_id, ' +
    '    alb.documento AS num_doc, ' +
    '    alb.fecha AS fecha_doc, ' +
    '    d.idproveedor, ' +
    '    COALESCE(p.nombre, ''- SIN PROVEEDOR -'') AS proveedor_nombre, ' +
    '    d.codigo AS cod_articulo_prov, ' +
    '    d.id_propio AS id_materia_prima, ' +
    '    COALESCE(mp.descripcion, d.descripcion) AS materia_prima, ' +
    '    mp.familia_id, ' +
    '    fam.descripcion AS familia_nombre, ' +
    '    d.lote, ' +
    '    d.fecha_caducidad, ' +
    '    d.bultos, ' +
    '    d.cantidad_bulto, ' +
    '    d.cantidad, ' +
    '    COALESCE(d.stock, d.cantidad, 0.0000) AS stock, ' +
    '    d.precio, ' +
    '    d.total, ' +
    '    COALESCE(d.en_obrador, alb.id_obrador, 0) AS id_obrador, ' +
    '    COALESCE(obr.nombre, CONCAT(''Obrador '', COALESCE(d.en_obrador, alb.id_obrador))) AS obrador_nombre, ' +
    '    d.validado, ' +
    '    d.created_at ' +
    '  FROM ge_prov_albaranes_detalle d ' +
    '  INNER JOIN ge_prov_albaranes alb ON (d.idalbaran = alb.id) ' +
    '  LEFT JOIN ge_proveedores p ON (d.idproveedor = p.id) ' +
    '  LEFT JOIN ge_materias_primas mp ON (d.id_propio = mp.id) ' +
    '  LEFT JOIN ge_familias fam ON (mp.familia_id = fam.id) ' +
    '  LEFT JOIN ge_obradores obr ON (COALESCE(d.en_obrador, alb.id_obrador) = obr.id) ' +
    '  WHERE (COALESCE(d.en_obrador, alb.id_obrador, 0) > 0) ' +
    '  UNION ALL ' +
    '  SELECT ' +
    '    ''FACTURA'' AS tipo_doc, ' +
    '    d.id AS linea_id, ' +
    '    d.idempresa, ' +
    '    d.idfactura AS doc_id, ' +
    '    fac.documento AS num_doc, ' +
    '    fac.fecha AS fecha_doc, ' +
    '    d.idproveedor, ' +
    '    COALESCE(p.nombre, ''- SIN PROVEEDOR -'') AS proveedor_nombre, ' +
    '    d.codigo AS cod_articulo_prov, ' +
    '    d.id_propio AS id_materia_prima, ' +
    '    COALESCE(mp.descripcion, d.descripcion) AS materia_prima, ' +
    '    mp.familia_id, ' +
    '    fam.descripcion AS familia_nombre, ' +
    '    d.lote, ' +
    '    d.fecha_caducidad, ' +
    '    d.bultos, ' +
    '    d.cantidad_bulto, ' +
    '    d.cantidad, ' +
    '    COALESCE(d.stock, d.cantidad, 0.0000) AS stock, ' +
    '    d.precio, ' +
    '    d.total, ' +
    '    COALESCE(d.en_obrador, 0) AS id_obrador, ' +
    '    COALESCE(obr.nombre, CONCAT(''Obrador '', d.en_obrador)) AS obrador_nombre, ' +
    '    d.validado, ' +
    '    d.created_at ' +
    '  FROM ge_prov_facturas_detalle d ' +
    '  INNER JOIN ge_prov_facturas fac ON (d.idfactura = fac.id AND d.idempresa = fac.idempresa) ' +
    '  LEFT JOIN ge_proveedores p ON (d.idproveedor = p.id) ' +
    '  LEFT JOIN ge_materias_primas mp ON (d.id_propio = mp.id) ' +
    '  LEFT JOIN ge_familias fam ON (mp.familia_id = fam.id) ' +
    '  LEFT JOIN ge_obradores obr ON (d.en_obrador = obr.id) ' +
    '  WHERE (COALESCE(d.en_obrador, 0) > 0) ' +
    ') t ' +
    'WHERE 1=1 ';

  // Filtro por Obrador
  if Assigned(cbObradorFilter) and (cbObradorFilter.ItemIndex > 0) and
     (cbObradorFilter.ItemIndex - 1 < FObradorIds.Count) then
    LSql := LSql + ' AND t.id_obrador = :obrador ';

  // Filtro por Familia
  if Assigned(cbFamiliaFilter) and (cbFamiliaFilter.ItemIndex > 0) and
     (cbFamiliaFilter.ItemIndex - 1 < FFamiliaIds.Count) then
    LSql := LSql + ' AND t.familia_id = :familia ';

  // Filtro por Tipo de Documento
  if Assigned(cbTipoDocFilter) then
  begin
    if cbTipoDocFilter.ItemIndex = 1 then
      LSql := LSql + ' AND t.tipo_doc = ''ALBARAN'' '
    else if cbTipoDocFilter.ItemIndex = 2 then
      LSql := LSql + ' AND t.tipo_doc = ''FACTURA'' ';
  end;

  // Filtro Solo con Stock > 0
  if Assigned(chkSoloConStock) and chkSoloConStock.Checked then
    LSql := LSql + ' AND t.stock > 0 ';

  // Búsqueda rápida por texto
  LSearch := Trim(edtSearch.Text);
  if LSearch <> '' then
    LSql := LSql + ' AND (t.materia_prima LIKE :search OR t.lote LIKE :search OR ' +
                   '      t.proveedor_nombre LIKE :search OR t.num_doc LIKE :search OR ' +
                   '      t.cod_articulo_prov LIKE :search) ';

  LSql := LSql + ' ORDER BY t.fecha_doc DESC, t.linea_id DESC';

  FQryMain.SQL.Text := LSql;

  if Assigned(cbObradorFilter) and (cbObradorFilter.ItemIndex > 0) and
     (cbObradorFilter.ItemIndex - 1 < FObradorIds.Count) then
    FQryMain.ParamByName('obrador').AsInteger := StrToIntDef(FObradorIds[cbObradorFilter.ItemIndex - 1], 0);

  if Assigned(cbFamiliaFilter) and (cbFamiliaFilter.ItemIndex > 0) and
     (cbFamiliaFilter.ItemIndex - 1 < FFamiliaIds.Count) then
    FQryMain.ParamByName('familia').AsInteger := StrToIntDef(FFamiliaIds[cbFamiliaFilter.ItemIndex - 1], 0);

  if LSearch <> '' then
    FQryMain.ParamByName('search').AsString := '%' + LSearch + '%';

  FQryMain.Open;

  // Asignar formateadores de visualización
  var LFld := FQryMain.FindField('precio');
  if LFld <> nil then LFld.OnGetText := FormatoPrecioGetText;

  LFld := FQryMain.FindField('total');
  if LFld <> nil then LFld.OnGetText := FormatoPrecioGetText;

  LFld := FQryMain.FindField('cantidad');
  if LFld <> nil then LFld.OnGetText := FormatoCantidadGetText;

  LFld := FQryMain.FindField('stock');
  if LFld <> nil then LFld.OnGetText := FormatoCantidadGetText;

  LFld := FQryMain.FindField('cantidad_bulto');
  if LFld <> nil then LFld.OnGetText := FormatoCantidadGetText;

  // Definición de columnas de la rejilla
  dbGridMain.Columns.Clear;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'tipo_doc';
    Title.Caption := 'Tipo Doc';
    Width := 75;
    Alignment := taCenter;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'num_doc';
    Title.Caption := 'Nº Doc';
    Width := 90;
    Alignment := taCenter;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'fecha_doc';
    Title.Caption := 'Fecha';
    Width := 80;
    Alignment := taCenter;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'proveedor_nombre';
    Title.Caption := 'Proveedor';
    Width := 160;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'materia_prima';
    Title.Caption := 'Materia Prima';
    Width := 210;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'familia_nombre';
    Title.Caption := 'Familia';
    Width := 105;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'obrador_nombre';
    Title.Caption := 'Obrador';
    Width := 115;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'lote';
    Title.Caption := 'Lote Fabricante';
    Width := 100;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'fecha_caducidad';
    Title.Caption := 'F. Caducidad';
    Width := 85;
    Alignment := taCenter;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'bultos';
    Title.Caption := 'Bultos';
    Width := 55;
    Alignment := taRightJustify;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'cantidad_bulto';
    Title.Caption := 'Cant./Bulto';
    Width := 75;
    Alignment := taRightJustify;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'cantidad';
    Title.Caption := 'Entrada';
    Width := 75;
    Alignment := taRightJustify;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'stock';
    Title.Caption := 'Stock Actual';
    Width := 85;
    Alignment := taRightJustify;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'precio';
    Title.Caption := 'Precio';
    Width := 75;
    Alignment := taRightJustify;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'total';
    Title.Caption := 'Total';
    Width := 80;
    Alignment := taRightJustify;
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'cod_articulo_prov';
    Title.Caption := 'Cód. Prov';
    Width := 80;
    Alignment := taCenter;
  end;

  ActualizarResumenStock;
end;

procedure TfrmStockMateriasPrimas.ActualizarResumenStock;
var
  LTotalStock: Double;
  LBookmark: TBookmark;
begin
  LTotalStock := 0.0;
  if FQryMain.Active and not FQryMain.IsEmpty then
  begin
    FQryMain.DisableControls;
    try
      LBookmark := FQryMain.Bookmark;
      FQryMain.First;
      while not FQryMain.Eof do
      begin
        LTotalStock := LTotalStock + FQryMain.FieldByName('stock').AsFloat;
        FQryMain.Next;
      end;
      if FQryMain.BookmarkValid(LBookmark) then
        FQryMain.GotoBookmark(LBookmark);
    finally
      FQryMain.EnableControls;
    end;
  end;

  if Assigned(lblTotalStockValor) then
    lblTotalStockValor.Caption := FormatFloat('#,##0.00', LTotalStock) + ' uds/kg';
end;

procedure TfrmStockMateriasPrimas.btnEditClick(Sender: TObject);
var
  LTipoDoc: string;
  LDocId: Integer;
begin
  if not FQryMain.Active or FQryMain.IsEmpty then
    Exit;

  LTipoDoc := UpperCase(FQryMain.FieldByName('tipo_doc').AsString);
  LDocId := FQryMain.FieldByName('doc_id').AsInteger;

  if LTipoDoc = 'ALBARAN' then
  begin
    if not Assigned(frmProvAlbaranesEditor) then
      Application.CreateForm(TfrmProvAlbaranesEditor, frmProvAlbaranesEditor);

    frmProvAlbaranesEditor.qMaster.Close;
    frmProvAlbaranesEditor.qMaster.SQL.Text :=
      'SELECT f.*, p.nombre as nombre_proveedor FROM ge_prov_albaranes f ' +
      'LEFT JOIN ge_proveedores p ON f.idproveedor = p.id ' +
      'WHERE f.id = :id';
    frmProvAlbaranesEditor.qMaster.ParamByName('id').AsInteger := LDocId;
    frmProvAlbaranesEditor.qMaster.Open;

    if frmProvAlbaranesEditor.ShowModal = mrOk then
      btnRefreshClick(nil);
  end
  else if LTipoDoc = 'FACTURA' then
  begin
    if not Assigned(frmProvFacturasEditor) then
      Application.CreateForm(TfrmProvFacturasEditor, frmProvFacturasEditor);

    frmProvFacturasEditor.qMaster.Close;
    frmProvFacturasEditor.qMaster.SQL.Text :=
      'SELECT f.*, p.nombre as nombre_proveedor FROM ge_prov_facturas f ' +
      'LEFT JOIN ge_proveedores p ON f.idproveedor = p.id ' +
      'WHERE f.id = :id';
    frmProvFacturasEditor.qMaster.ParamByName('id').AsInteger := LDocId;
    frmProvFacturasEditor.qMaster.Open;

    if frmProvFacturasEditor.ShowModal = mrOk then
      btnRefreshClick(nil);
  end;
end;

procedure TfrmStockMateriasPrimas.dbGridMainDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
var
  LStock: Double;
begin
  if not (gdSelected in State) and Assigned(Column.Field) and (FQryMain.Active) then
  begin
    LStock := FQryMain.FieldByName('stock').AsFloat;
    if LStock <= 0 then
    begin
      dbGridMain.Canvas.Brush.Color := $00F7F7F7; // Fondo gris muy suave
      dbGridMain.Canvas.Font.Color := clGray;     // Texto atenuado
    end
    else
    begin
      if SameText(Column.FieldName, 'stock') then
      begin
        dbGridMain.Canvas.Font.Style := [fsBold];
        dbGridMain.Canvas.Font.Color := $001B6B2B; // Verde corporativo para existencias activas
      end;
    end;
  end;

  dbGridMain.DefaultDrawColumnCell(Rect, DataCol, Column, State);
end;


procedure TfrmStockMateriasPrimas.ApplyAllFilters;
begin
  inherited ApplyAllFilters;
  ActualizarResumenStock;
end;

end.
