unit frm_AuditoriaCambios;

interface

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

type
  TfrmAuditoriaCambios = class(TForm)
  private
    FPnlTop: TPanel;
    FPnlGrid: TPanel;
    FSplitter: TSplitter;
    FPnlBottom: TPanel;
    FPnlStatus: TPanel;

    FDtpDesde: TDateTimePicker;
    FDtpHasta: TDateTimePicker;
    FChkTodasFechas: TCheckBox;
    FCbTabla: TComboBox;
    FCbOperacion: TComboBox;
    FEdtRegistroId: TEdit;
    FCbUsuario: TComboBox;
    FEdtTexto: TEdit;
    FCbLimite: TComboBox;
    FBtnBuscar: TButton;
    FBtnHoy: TButton;
    FBtnLimpiar: TButton;
    FBtnHistorialReg: TButton;
    FBtnExportarCsv: TButton;
    FLblStatus: TLabel;

    FGridEventos: TDBGrid;
    FQryEventos: TFDQuery;
    FDsEventos: TDataSource;

    FPgcDetalle: TPageControl;
    FTabDiff: TTabSheet;
    FTabJson: TTabSheet;
    FPnlDiffTop: TPanel;
    FChkSoloModificados: TCheckBox;
    FLblResumenReg: TLabel;
    FGridDiff: TStringGrid;
    FMemAnterior: TMemo;
    FMemNuevo: TMemo;

    FUsuarioIds: TStringList;

    procedure ConstruirUI;
    procedure CargarCombos;
    procedure EjecutarConsulta;
    procedure ActualizarDetalleRegistro;
    procedure CargarDiffCampos(const AJsonAnt, AJsonNew: string);
    procedure ConfigurarColumnasEventos;

    procedure BtnBuscarClick(Sender: TObject);
    procedure BtnHoyClick(Sender: TObject);
    procedure BtnLimpiarClick(Sender: TObject);
    procedure BtnHistorialRegClick(Sender: TObject);
    procedure BtnExportarCsvClick(Sender: TObject);
    procedure ChkTodasFechasClick(Sender: TObject);
    procedure ChkSoloModificadosClick(Sender: TObject);
    procedure FilterKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure QryEventosAfterScroll(DataSet: TDataSet);
    procedure GridEventosDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure GridEventosDblClick(Sender: TObject);
    procedure GridDiffDrawCell(Sender: TObject; ACol, ARow: LongInt;
      Rect: TRect; State: TGridDrawState);
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
  end;

implementation

uses
  dmg_Main, uAppTheme, uDbErrorHandler;

{ TfrmAuditoriaCambios }

constructor TfrmAuditoriaCambios.Create(AOwner: TComponent);
begin
  inherited CreateNew(AOwner);
  FUsuarioIds := TStringList.Create;
  ConstruirUI;
  TAppTheme.ApplyToForm(Self);
  CargarCombos;
  EjecutarConsulta;
end;

destructor TfrmAuditoriaCambios.Destroy;
begin
  FreeAndNil(FUsuarioIds);
  inherited Destroy;
end;

procedure TfrmAuditoriaCambios.ConstruirUI;
var
  LLbl: TLabel;
  LPnlJsonLeft, LPnlJsonRight: TPanel;
  LSplitJson: TSplitter;
begin
  Caption := 'Auditoría de Cambios (ge_auditoria_cambios)';
  Width := 1180;
  Height := 720;
  Position := poScreenCenter;

  // 1. Panel superior de filtros
  FPnlTop := TPanel.Create(Self);
  FPnlTop.Parent := Self;
  FPnlTop.Align := alTop;
  FPnlTop.Height := 104;
  FPnlTop.BevelOuter := bvNone;
  FPnlTop.Padding.SetBounds(8, 8, 8, 6);

  // Fila 1 de filtros: Fechas, Tabla, Operación, ID Registro
  LLbl := TLabel.Create(Self);
  LLbl.Parent := FPnlTop;
  LLbl.Left := 12;
  LLbl.Top := 12;
  LLbl.Caption := 'Desde:';

  FDtpDesde := TDateTimePicker.Create(Self);
  FDtpDesde.Parent := FPnlTop;
  FDtpDesde.Left := 58;
  FDtpDesde.Top := 8;
  FDtpDesde.Width := 105;
  FDtpDesde.Date := Date;

  LLbl := TLabel.Create(Self);
  LLbl.Parent := FPnlTop;
  LLbl.Left := 172;
  LLbl.Top := 12;
  LLbl.Caption := 'Hasta:';

  FDtpHasta := TDateTimePicker.Create(Self);
  FDtpHasta.Parent := FPnlTop;
  FDtpHasta.Left := 214;
  FDtpHasta.Top := 8;
  FDtpHasta.Width := 105;
  FDtpHasta.Date := Date;

  FChkTodasFechas := TCheckBox.Create(Self);
  FChkTodasFechas.Parent := FPnlTop;
  FChkTodasFechas.Left := 326;
  FChkTodasFechas.Top := 10;
  FChkTodasFechas.Width := 108;
  FChkTodasFechas.Caption := 'Todas fechas';
  FChkTodasFechas.Checked := False;
  FChkTodasFechas.OnClick := ChkTodasFechasClick;

  LLbl := TLabel.Create(Self);
  LLbl.Parent := FPnlTop;
  LLbl.Left := 444;
  LLbl.Top := 12;
  LLbl.Caption := 'Tabla:';

  FCbTabla := TComboBox.Create(Self);
  FCbTabla.Parent := FPnlTop;
  FCbTabla.Left := 484;
  FCbTabla.Top := 8;
  FCbTabla.Width := 215;
  FCbTabla.Style := csDropDownList;

  LLbl := TLabel.Create(Self);
  LLbl.Parent := FPnlTop;
  LLbl.Left := 712;
  LLbl.Top := 12;
  LLbl.Caption := 'Operación:';

  FCbOperacion := TComboBox.Create(Self);
  FCbOperacion.Parent := FPnlTop;
  FCbOperacion.Left := 778;
  FCbOperacion.Top := 8;
  FCbOperacion.Width := 105;
  FCbOperacion.Style := csDropDownList;
  FCbOperacion.Items.Add('[Todas]');
  FCbOperacion.Items.Add('INSERT');
  FCbOperacion.Items.Add('UPDATE');
  FCbOperacion.Items.Add('DELETE');
  FCbOperacion.ItemIndex := 0;

  LLbl := TLabel.Create(Self);
  LLbl.Parent := FPnlTop;
  LLbl.Left := 896;
  LLbl.Top := 12;
  LLbl.Caption := 'ID Reg.:';

  FEdtRegistroId := TEdit.Create(Self);
  FEdtRegistroId.Parent := FPnlTop;
  FEdtRegistroId.Left := 946;
  FEdtRegistroId.Top := 8;
  FEdtRegistroId.Width := 115;
  FEdtRegistroId.OnKeyDown := FilterKeyDown;

  // Fila 2 de filtros: Usuario, Texto JSON, Límite, Botones
  LLbl := TLabel.Create(Self);
  LLbl.Parent := FPnlTop;
  LLbl.Left := 12;
  LLbl.Top := 46;
  LLbl.Caption := 'Usuario:';

  FCbUsuario := TComboBox.Create(Self);
  FCbUsuario.Parent := FPnlTop;
  FCbUsuario.Left := 64;
  FCbUsuario.Top := 42;
  FCbUsuario.Width := 190;
  FCbUsuario.Style := csDropDownList;

  LLbl := TLabel.Create(Self);
  LLbl.Parent := FPnlTop;
  LLbl.Left := 266;
  LLbl.Top := 46;
  LLbl.Caption := 'Buscar en JSON:';

  FEdtTexto := TEdit.Create(Self);
  FEdtTexto.Parent := FPnlTop;
  FEdtTexto.Left := 364;
  FEdtTexto.Top := 42;
  FEdtTexto.Width := 230;
  FEdtTexto.TextHint := 'Valor, código, cliente, lote...';
  FEdtTexto.OnKeyDown := FilterKeyDown;

  LLbl := TLabel.Create(Self);
  LLbl.Parent := FPnlTop;
  LLbl.Left := 606;
  LLbl.Top := 46;
  LLbl.Caption := 'Máx.:';

  FCbLimite := TComboBox.Create(Self);
  FCbLimite.Parent := FPnlTop;
  FCbLimite.Left := 642;
  FCbLimite.Top := 42;
  FCbLimite.Width := 75;
  FCbLimite.Style := csDropDownList;
  FCbLimite.Items.Add('200');
  FCbLimite.Items.Add('500');
  FCbLimite.Items.Add('1000');
  FCbLimite.Items.Add('5000');
  FCbLimite.ItemIndex := 1;

  FBtnBuscar := TButton.Create(Self);
  FBtnBuscar.Parent := FPnlTop;
  FBtnBuscar.Left := 730;
  FBtnBuscar.Top := 40;
  FBtnBuscar.Width := 90;
  FBtnBuscar.Height := 27;
  FBtnBuscar.Caption := 'Buscar';
  FBtnBuscar.Default := True;
  FBtnBuscar.OnClick := BtnBuscarClick;

  FBtnHoy := TButton.Create(Self);
  FBtnHoy.Parent := FPnlTop;
  FBtnHoy.Left := 826;
  FBtnHoy.Top := 40;
  FBtnHoy.Width := 65;
  FBtnHoy.Height := 27;
  FBtnHoy.Caption := 'Hoy';
  FBtnHoy.OnClick := BtnHoyClick;

  FBtnLimpiar := TButton.Create(Self);
  FBtnLimpiar.Parent := FPnlTop;
  FBtnLimpiar.Left := 897;
  FBtnLimpiar.Top := 40;
  FBtnLimpiar.Width := 75;
  FBtnLimpiar.Height := 27;
  FBtnLimpiar.Caption := 'Limpiar';
  FBtnLimpiar.OnClick := BtnLimpiarClick;

  FBtnHistorialReg := TButton.Create(Self);
  FBtnHistorialReg.Parent := FPnlTop;
  FBtnHistorialReg.Left := 12;
  FBtnHistorialReg.Top := 72;
  FBtnHistorialReg.Width := 185;
  FBtnHistorialReg.Height := 26;
  FBtnHistorialReg.Caption := 'Historial de este registro';
  FBtnHistorialReg.OnClick := BtnHistorialRegClick;

  FBtnExportarCsv := TButton.Create(Self);
  FBtnExportarCsv.Parent := FPnlTop;
  FBtnExportarCsv.Left := 205;
  FBtnExportarCsv.Top := 72;
  FBtnExportarCsv.Width := 130;
  FBtnExportarCsv.Height := 26;
  FBtnExportarCsv.Caption := 'Exportar CSV';
  FBtnExportarCsv.OnClick := BtnExportarCsvClick;

  // 2. Barra de estado inferior
  FPnlStatus := TPanel.Create(Self);
  FPnlStatus.Parent := Self;
  FPnlStatus.Align := alBottom;
  FPnlStatus.Height := 26;
  FPnlStatus.BevelOuter := bvNone;

  FLblStatus := TLabel.Create(Self);
  FLblStatus.Parent := FPnlStatus;
  FLblStatus.Align := alClient;
  FLblStatus.AlignWithMargins := True;
  FLblStatus.Margins.SetBounds(10, 4, 10, 4);
  FLblStatus.Caption := 'Listo.';

  // 3. Panel inferior de detalle (comparador + JSON)
  FPnlBottom := TPanel.Create(Self);
  FPnlBottom.Parent := Self;
  FPnlBottom.Align := alBottom;
  FPnlBottom.Height := 285;
  FPnlBottom.BevelOuter := bvNone;

  FSplitter := TSplitter.Create(Self);
  FSplitter.Parent := Self;
  FSplitter.Align := alBottom;
  FSplitter.Height := 5;

  FPgcDetalle := TPageControl.Create(Self);
  FPgcDetalle.Parent := FPnlBottom;
  FPgcDetalle.Align := alClient;

  FTabDiff := TTabSheet.Create(Self);
  FTabDiff.PageControl := FPgcDetalle;
  FTabDiff.Caption := 'Comparativa de Campos (Anterior vs Nuevo)';

  FPnlDiffTop := TPanel.Create(Self);
  FPnlDiffTop.Parent := FTabDiff;
  FPnlDiffTop.Align := alTop;
  FPnlDiffTop.Height := 30;
  FPnlDiffTop.BevelOuter := bvNone;

  FChkSoloModificados := TCheckBox.Create(Self);
  FChkSoloModificados.Parent := FPnlDiffTop;
  FChkSoloModificados.Left := 8;
  FChkSoloModificados.Top := 6;
  FChkSoloModificados.Width := 180;
  FChkSoloModificados.Caption := 'Solo campos modificados';
  FChkSoloModificados.Checked := True;
  FChkSoloModificados.OnClick := ChkSoloModificadosClick;

  FLblResumenReg := TLabel.Create(Self);
  FLblResumenReg.Parent := FPnlDiffTop;
  FLblResumenReg.Left := 196;
  FLblResumenReg.Top := 8;
  FLblResumenReg.Font.Style := [fsBold];
  FLblResumenReg.Caption := '';

  FGridDiff := TStringGrid.Create(Self);
  FGridDiff.Parent := FTabDiff;
  FGridDiff.Align := alClient;
  FGridDiff.ColCount := 4;
  FGridDiff.RowCount := 2;
  FGridDiff.FixedCols := 0;
  FGridDiff.FixedRows := 1;
  FGridDiff.Options := [goFixedVertLine, goFixedHorzLine, goVertLine, goHorzLine, goColSizing, goRowSelect];
  FGridDiff.DefaultRowHeight := 22;
  FGridDiff.ColWidths[0] := 210;
  FGridDiff.ColWidths[1] := 370;
  FGridDiff.ColWidths[2] := 370;
  FGridDiff.ColWidths[3] := 120;
  FGridDiff.Cells[0, 0] := 'Campo';
  FGridDiff.Cells[1, 0] := 'Valor Anterior';
  FGridDiff.Cells[2, 0] := 'Valor Nuevo';
  FGridDiff.Cells[3, 0] := 'Estado';
  FGridDiff.OnDrawCell := GridDiffDrawCell;

  FTabJson := TTabSheet.Create(Self);
  FTabJson.PageControl := FPgcDetalle;
  FTabJson.Caption := 'JSON Completo';

  LPnlJsonLeft := TPanel.Create(Self);
  LPnlJsonLeft.Parent := FTabJson;
  LPnlJsonLeft.Align := alLeft;
  LPnlJsonLeft.Width := 560;
  LPnlJsonLeft.BevelOuter := bvNone;

  LLbl := TLabel.Create(Self);
  LLbl.Parent := LPnlJsonLeft;
  LLbl.Align := alTop;
  LLbl.Caption := '  Valor Anterior (JSON)';
  LLbl.Font.Style := [fsBold];

  FMemAnterior := TMemo.Create(Self);
  FMemAnterior.Parent := LPnlJsonLeft;
  FMemAnterior.Align := alClient;
  FMemAnterior.ReadOnly := True;
  FMemAnterior.ScrollBars := ssBoth;
  FMemAnterior.Font.Name := 'Consolas';
  FMemAnterior.Font.Size := 9;

  LSplitJson := TSplitter.Create(Self);
  LSplitJson.Parent := FTabJson;
  LSplitJson.Align := alLeft;
  LSplitJson.Width := 5;

  LPnlJsonRight := TPanel.Create(Self);
  LPnlJsonRight.Parent := FTabJson;
  LPnlJsonRight.Align := alClient;
  LPnlJsonRight.BevelOuter := bvNone;

  LLbl := TLabel.Create(Self);
  LLbl.Parent := LPnlJsonRight;
  LLbl.Align := alTop;
  LLbl.Caption := '  Valor Nuevo (JSON)';
  LLbl.Font.Style := [fsBold];

  FMemNuevo := TMemo.Create(Self);
  FMemNuevo.Parent := LPnlJsonRight;
  FMemNuevo.Align := alClient;
  FMemNuevo.ReadOnly := True;
  FMemNuevo.ScrollBars := ssBoth;
  FMemNuevo.Font.Name := 'Consolas';
  FMemNuevo.Font.Size := 9;

  // 4. Panel central con rejilla de eventos
  FPnlGrid := TPanel.Create(Self);
  FPnlGrid.Parent := Self;
  FPnlGrid.Align := alClient;
  FPnlGrid.BevelOuter := bvNone;

  FQryEventos := TFDQuery.Create(Self);
  FQryEventos.Connection := dmgMain.dbConn;
  FQryEventos.AfterScroll := QryEventosAfterScroll;

  FDsEventos := TDataSource.Create(Self);
  FDsEventos.DataSet := FQryEventos;

  FGridEventos := TDBGrid.Create(Self);
  FGridEventos.Parent := FPnlGrid;
  FGridEventos.Align := alClient;
  FGridEventos.DataSource := FDsEventos;
  FGridEventos.ReadOnly := True;
  FGridEventos.Options := [dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect, dgAlwaysShowSelection];
  FGridEventos.OnDrawColumnCell := GridEventosDrawColumnCell;
  FGridEventos.OnDblClick := GridEventosDblClick;
end;

procedure TfrmAuditoriaCambios.CargarCombos;
var
  LQry: TFDQuery;
begin
  FCbTabla.Items.Clear;
  FCbTabla.Items.Add('[Todas]');

  FCbUsuario.Items.Clear;
  FCbUsuario.Items.Add('[Todos]');
  FUsuarioIds.Clear;

  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;

    // Cargar tablas presentes en auditoría (desde information_schema o últimos 5000 registros para evitar full scan)
    LQry.SQL.Text :=
      'SELECT DISTINCT nombre_tabla FROM (' +
      '  SELECT nombre_tabla FROM ge_auditoria_cambios ORDER BY id DESC LIMIT 10000' +
      ') t ORDER BY nombre_tabla';
    try
      LQry.Open;
      while not LQry.Eof do
      begin
        if Trim(LQry.FieldByName('nombre_tabla').AsString) <> '' then
          FCbTabla.Items.Add(LQry.FieldByName('nombre_tabla').AsString);
        LQry.Next;
      end;
    except
      // Ignorar si falla
    end;
    FCbTabla.ItemIndex := 0;

    LQry.Close;
    LQry.SQL.Text := 'SELECT id, nombre_completo, username FROM ge_usuarios ORDER BY nombre_completo, username';
    try
      LQry.Open;
      while not LQry.Eof do
      begin
        var LNom := Trim(LQry.FieldByName('nombre_completo').AsString);
        if LNom = '' then
          LNom := Trim(LQry.FieldByName('username').AsString);
        FCbUsuario.Items.Add(Format('%s (#%d)', [LNom, LQry.FieldByName('id').AsInteger]));
        FUsuarioIds.Add(LQry.FieldByName('id').AsString);
        LQry.Next;
      end;
    except
    end;
    FCbUsuario.ItemIndex := 0;
  finally
    LQry.Free;
  end;
end;

procedure TfrmAuditoriaCambios.EjecutarConsulta;
var
  LSql, LWhere: string;
  LLimit: Integer;
  LHasWhere: Boolean;

  procedure AddCond(const ACond: string);
  begin
    if not LHasWhere then
    begin
      LWhere := 'WHERE ' + ACond + ' ';
      LHasWhere := True;
    end
    else
      LWhere := LWhere + 'AND ' + ACond + ' ';
  end;

begin
  LLimit := StrToIntDef(FCbLimite.Text, 500);
  if LLimit <= 0 then
    LLimit := 500;

  LWhere := '';
  LHasWhere := False;

  if not FChkTodasFechas.Checked then
    AddCond('a.created_at >= :fdesde AND a.created_at < DATE_ADD(:fhasta, INTERVAL 1 DAY)');

  if FCbTabla.ItemIndex > 0 then
    AddCond('a.nombre_tabla = :tabla');

  if FCbOperacion.ItemIndex > 0 then
    AddCond('a.operacion = :op');

  if Trim(FEdtRegistroId.Text) <> '' then
    AddCond('a.registro_id = :reg_id');

  if (FCbUsuario.ItemIndex > 0) and (FCbUsuario.ItemIndex - 1 < FUsuarioIds.Count) then
    AddCond('(a.id_user_update = :uid OR a.id_user_creator = :uid2)');

  if Trim(FEdtTexto.Text) <> '' then
    AddCond('(a.valor_anterior LIKE :txt OR a.valor_nuevo LIKE :txt2)');

  LSql :=
    'SELECT a.id, a.created_at, a.nombre_tabla, a.operacion, a.registro_id, ' +
    '       COALESCE(NULLIF(u.nombre_completo, ''''), u.username, CAST(a.id_user_update AS CHAR)) AS usuario_nombre, ' +
    '       a.valor_anterior, a.valor_nuevo ' +
    'FROM ge_auditoria_cambios a ' +
    'LEFT JOIN ge_usuarios u ON u.id = a.id_user_update ' +
    LWhere +
    'ORDER BY a.id DESC ' +
    'LIMIT ' + IntToStr(LLimit);

  Screen.Cursor := crHourGlass;
  try
    FQryEventos.AfterScroll := nil;
    FQryEventos.Close;
    FQryEventos.SQL.Text := LSql;

    if not FChkTodasFechas.Checked then
    begin
      FQryEventos.ParamByName('fdesde').AsDate := Trunc(FDtpDesde.Date);
      FQryEventos.ParamByName('fhasta').AsDate := Trunc(FDtpHasta.Date);
    end;
    if FCbTabla.ItemIndex > 0 then
      FQryEventos.ParamByName('tabla').AsString := FCbTabla.Text;
    if FCbOperacion.ItemIndex > 0 then
      FQryEventos.ParamByName('op').AsString := FCbOperacion.Text;
    if Trim(FEdtRegistroId.Text) <> '' then
      FQryEventos.ParamByName('reg_id').AsString := Trim(FEdtRegistroId.Text);
    if (FCbUsuario.ItemIndex > 0) and (FCbUsuario.ItemIndex - 1 < FUsuarioIds.Count) then
    begin
      var LUid := StrToIntDef(FUsuarioIds[FCbUsuario.ItemIndex - 1], 0);
      FQryEventos.ParamByName('uid').AsInteger := LUid;
      FQryEventos.ParamByName('uid2').AsInteger := LUid;
    end;
    if Trim(FEdtTexto.Text) <> '' then
    begin
      var LTxt := '%' + Trim(FEdtTexto.Text) + '%';
      FQryEventos.ParamByName('txt').AsWideString := LTxt;
      FQryEventos.ParamByName('txt2').AsWideString := LTxt;
    end;

    FQryEventos.Open;
    ConfigurarColumnasEventos;
    FQryEventos.AfterScroll := QryEventosAfterScroll;
    ActualizarDetalleRegistro;

    FLblStatus.Caption := Format('Mostrando %d registros (límite %d). Doble clic en una fila para ver todo el historial de ese registro.',
      [FQryEventos.RecordCount, LLimit]);
  finally
    Screen.Cursor := crDefault;
  end;
end;

procedure TfrmAuditoriaCambios.ConfigurarColumnasEventos;
var
  I: Integer;
  LCol: TColumn;
begin
  FGridEventos.Columns.Clear;
  if not FQryEventos.Active then
    Exit;

  with FGridEventos.Columns.Add do
  begin
    FieldName := 'id';
    Title.Caption := 'ID Aud.';
    Width := 75;
  end;
  with FGridEventos.Columns.Add do
  begin
    FieldName := 'created_at';
    Title.Caption := 'Fecha / Hora';
    Width := 145;
  end;
  with FGridEventos.Columns.Add do
  begin
    FieldName := 'nombre_tabla';
    Title.Caption := 'Tabla';
    Width := 230;
  end;
  with FGridEventos.Columns.Add do
  begin
    FieldName := 'operacion';
    Title.Caption := 'Operación';
    Width := 90;
    Alignment := taCenter;
  end;
  with FGridEventos.Columns.Add do
  begin
    FieldName := 'registro_id';
    Title.Caption := 'ID Registro';
    Width := 100;
    Alignment := taCenter;
  end;
  with FGridEventos.Columns.Add do
  begin
    FieldName := 'usuario_nombre';
    Title.Caption := 'Usuario';
    Width := 180;
  end;

  for I := 0 to FGridEventos.Columns.Count - 1 do
  begin
    LCol := FGridEventos.Columns[I];
    LCol.Title.Font.Style := [fsBold];
  end;
end;

procedure TfrmAuditoriaCambios.ActualizarDetalleRegistro;
var
  LAnt, LNew: string;
  LJsonVal: TJSONValue;
begin
  if (not FQryEventos.Active) or FQryEventos.IsEmpty then
  begin
    FLblResumenReg.Caption := 'Sin registro seleccionado';
    FMemAnterior.Clear;
    FMemNuevo.Clear;
    FGridDiff.RowCount := 2;
    FGridDiff.Rows[1].Clear;
    Exit;
  end;

  FLblResumenReg.Caption := Format('Tabla: %s  |  Registro: %s  |  Operación: %s  |  Fecha: %s', [
    FQryEventos.FieldByName('nombre_tabla').AsString,
    FQryEventos.FieldByName('registro_id').AsString,
    FQryEventos.FieldByName('operacion').AsString,
    FQryEventos.FieldByName('created_at').AsString
  ]);

  LAnt := FQryEventos.FieldByName('valor_anterior').AsString;
  LNew := FQryEventos.FieldByName('valor_nuevo').AsString;

  LJsonVal := TJSONObject.ParseJSONValue(LAnt);
  try
    if Assigned(LJsonVal) then
      FMemAnterior.Text := LJsonVal.Format(2)
    else
      FMemAnterior.Text := LAnt;
  finally
    LJsonVal.Free;
  end;

  LJsonVal := TJSONObject.ParseJSONValue(LNew);
  try
    if Assigned(LJsonVal) then
      FMemNuevo.Text := LJsonVal.Format(2)
    else
      FMemNuevo.Text := LNew;
  finally
    LJsonVal.Free;
  end;

  CargarDiffCampos(LAnt, LNew);
end;

procedure TfrmAuditoriaCambios.CargarDiffCampos(const AJsonAnt, AJsonNew: string);
var
  LObjAnt, LObjNew: TJSONObject;
  LKeys: TList<string>;
  LKeySet: TDictionary<string, Boolean>;
  I, LRow: Integer;
  LKey, LValAnt, LValNew, LEstado: string;
  LOnlyChanged: Boolean;

  function JsonPairValueStr(AObj: TJSONObject; const AK: string; out AExists: Boolean): string;
  var
    LPair: TJSONPair;
  begin
    Result := '';
    AExists := False;
    if not Assigned(AObj) then
      Exit;
    LPair := AObj.Get(AK);
    if Assigned(LPair) and Assigned(LPair.JsonValue) then
    begin
      AExists := True;
      if LPair.JsonValue is TJSONNull then
        Result := '<NULL>'
      else
        Result := LPair.JsonValue.Value;
    end;
  end;

begin
  LObjAnt := nil;
  LObjNew := nil;
  LKeys := TList<string>.Create;
  LKeySet := TDictionary<string, Boolean>.Create;
  try
    if Trim(AJsonAnt) <> '' then
      LObjAnt := TJSONObject.ParseJSONValue(AJsonAnt) as TJSONObject;
    if Trim(AJsonNew) <> '' then
      LObjNew := TJSONObject.ParseJSONValue(AJsonNew) as TJSONObject;

    if Assigned(LObjAnt) then
      for I := 0 to LObjAnt.Count - 1 do
      begin
        LKey := LObjAnt.Pairs[I].JsonString.Value;
        if not LKeySet.ContainsKey(LKey) then
        begin
          LKeySet.Add(LKey, True);
          LKeys.Add(LKey);
        end;
      end;

    if Assigned(LObjNew) then
      for I := 0 to LObjNew.Count - 1 do
      begin
        LKey := LObjNew.Pairs[I].JsonString.Value;
        if not LKeySet.ContainsKey(LKey) then
        begin
          LKeySet.Add(LKey, True);
          LKeys.Add(LKey);
        end;
      end;

    LOnlyChanged := FChkSoloModificados.Checked and Assigned(LObjAnt) and Assigned(LObjNew);

    FGridDiff.RowCount := 1;
    for LKey in LKeys do
    begin
      var LExAnt, LExNew: Boolean;
      LValAnt := JsonPairValueStr(LObjAnt, LKey, LExAnt);
      LValNew := JsonPairValueStr(LObjNew, LKey, LExNew);

      if not LExAnt then
        LEstado := 'AÑADIDO'
      else if not LExNew then
        LEstado := 'ELIMINADO'
      else if LValAnt <> LValNew then
        LEstado := 'MODIFICADO'
      else
        LEstado := 'IGUAL';

      if LOnlyChanged and (LEstado = 'IGUAL') then
        Continue;

      LRow := FGridDiff.RowCount;
      FGridDiff.RowCount := LRow + 1;
      FGridDiff.Cells[0, LRow] := LKey;
      FGridDiff.Cells[1, LRow] := LValAnt;
      FGridDiff.Cells[2, LRow] := LValNew;
      FGridDiff.Cells[3, LRow] := LEstado;
    end;

    if FGridDiff.RowCount = 1 then
    begin
      FGridDiff.RowCount := 2;
      FGridDiff.Cells[0, 1] := '(Sin cambios en campos)';
      FGridDiff.Cells[1, 1] := '';
      FGridDiff.Cells[2, 1] := '';
      FGridDiff.Cells[3, 1] := '';
    end;
  finally
    LKeySet.Free;
    LKeys.Free;
    LObjAnt.Free;
    LObjNew.Free;
  end;
end;

procedure TfrmAuditoriaCambios.BtnBuscarClick(Sender: TObject);
begin
  EjecutarConsulta;
end;

procedure TfrmAuditoriaCambios.BtnHoyClick(Sender: TObject);
begin
  FChkTodasFechas.Checked := False;
  FDtpDesde.Enabled := True;
  FDtpHasta.Enabled := True;
  FDtpDesde.Date := Date;
  FDtpHasta.Date := Date;
  EjecutarConsulta;
end;

procedure TfrmAuditoriaCambios.BtnLimpiarClick(Sender: TObject);
begin
  FChkTodasFechas.Checked := False;
  FDtpDesde.Enabled := True;
  FDtpHasta.Enabled := True;
  FDtpDesde.Date := Date;
  FDtpHasta.Date := Date;
  FCbTabla.ItemIndex := 0;
  FCbOperacion.ItemIndex := 0;
  FEdtRegistroId.Clear;
  FCbUsuario.ItemIndex := 0;
  FEdtTexto.Clear;
  EjecutarConsulta;
end;

procedure TfrmAuditoriaCambios.BtnHistorialRegClick(Sender: TObject);
var
  LTabla, LRegId: string;
  LIdx: Integer;
begin
  if (not FQryEventos.Active) or FQryEventos.IsEmpty then
    Exit;

  LTabla := FQryEventos.FieldByName('nombre_tabla').AsString;
  LRegId := FQryEventos.FieldByName('registro_id').AsString;

  FChkTodasFechas.Checked := True;
  ChkTodasFechasClick(nil);
  LIdx := FCbTabla.Items.IndexOf(LTabla);
  if LIdx >= 0 then
    FCbTabla.ItemIndex := LIdx;
  FEdtRegistroId.Text := LRegId;
  FCbOperacion.ItemIndex := 0;
  EjecutarConsulta;
end;

procedure TfrmAuditoriaCambios.BtnExportarCsvClick(Sender: TObject);
var
  LSaveDlg: TSaveDialog;
  LSB: TStringBuilder;
  LBm: TBookmark;

  function CsvEsc(const S: string): string;
  begin
    Result := '"' + StringReplace(StringReplace(S, '"', '""', [rfReplaceAll]), sLineBreak, ' ', [rfReplaceAll]) + '"';
  end;

begin
  if (not FQryEventos.Active) or FQryEventos.IsEmpty then
  begin
    ShowMessage('No hay registros para exportar.');
    Exit;
  end;

  LSaveDlg := TSaveDialog.Create(Self);
  try
    LSaveDlg.Title := 'Exportar auditoría de cambios a CSV';
    LSaveDlg.Filter := 'Archivos CSV (*.csv)|*.csv';
    LSaveDlg.DefaultExt := 'csv';
    LSaveDlg.FileName := 'auditoria_cambios_' + FormatDateTime('yyyymmdd_hhnn', Now) + '.csv';
    if not LSaveDlg.Execute then
      Exit;

    LSB := TStringBuilder.Create;
    FQryEventos.DisableControls;
    LBm := FQryEventos.GetBookmark;
    try
      LSB.AppendLine('ID;Fecha;Tabla;Operacion;ID_Registro;Usuario;Valor_Anterior;Valor_Nuevo');
      FQryEventos.First;
      while not FQryEventos.Eof do
      begin
        LSB.AppendLine(
          CsvEsc(FQryEventos.FieldByName('id').AsString) + ';' +
          CsvEsc(FQryEventos.FieldByName('created_at').AsString) + ';' +
          CsvEsc(FQryEventos.FieldByName('nombre_tabla').AsString) + ';' +
          CsvEsc(FQryEventos.FieldByName('operacion').AsString) + ';' +
          CsvEsc(FQryEventos.FieldByName('registro_id').AsString) + ';' +
          CsvEsc(FQryEventos.FieldByName('usuario_nombre').AsString) + ';' +
          CsvEsc(FQryEventos.FieldByName('valor_anterior').AsString) + ';' +
          CsvEsc(FQryEventos.FieldByName('valor_nuevo').AsString)
        );
        FQryEventos.Next;
      end;
      TFile.WriteAllText(LSaveDlg.FileName, LSB.ToString, TEncoding.UTF8);
      ShowMessage('Auditoría exportada correctamente en:' + sLineBreak + LSaveDlg.FileName);
    finally
      if FQryEventos.BookmarkValid(LBm) then
        FQryEventos.GotoBookmark(LBm);
      FQryEventos.FreeBookmark(LBm);
      FQryEventos.EnableControls;
      LSB.Free;
    end;
  finally
    LSaveDlg.Free;
  end;
end;

procedure TfrmAuditoriaCambios.ChkTodasFechasClick(Sender: TObject);
begin
  FDtpDesde.Enabled := not FChkTodasFechas.Checked;
  FDtpHasta.Enabled := not FChkTodasFechas.Checked;
end;

procedure TfrmAuditoriaCambios.ChkSoloModificadosClick(Sender: TObject);
begin
  ActualizarDetalleRegistro;
end;

procedure TfrmAuditoriaCambios.FilterKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
begin
  if Key = VK_RETURN then
  begin
    Key := 0;
    EjecutarConsulta;
  end;
end;

procedure TfrmAuditoriaCambios.QryEventosAfterScroll(DataSet: TDataSet);
begin
  ActualizarDetalleRegistro;
end;

procedure TfrmAuditoriaCambios.GridEventosDblClick(Sender: TObject);
begin
  BtnHistorialRegClick(nil);
end;

procedure TfrmAuditoriaCambios.GridEventosDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
var
  LOp: string;
begin
  if not (gdSelected in State) and Assigned(Column.Field) and SameText(Column.Field.FieldName, 'operacion') then
  begin
    LOp := UpperCase(Trim(Column.Field.AsString));
    if LOp = 'INSERT' then
    begin
      FGridEventos.Canvas.Brush.Color := RGB(220, 245, 225);
      FGridEventos.Canvas.Font.Color := RGB(20, 110, 40);
      FGridEventos.Canvas.Font.Style := [fsBold];
    end
    else if LOp = 'UPDATE' then
    begin
      FGridEventos.Canvas.Brush.Color := RGB(255, 244, 214);
      FGridEventos.Canvas.Font.Color := RGB(155, 95, 0);
      FGridEventos.Canvas.Font.Style := [fsBold];
    end
    else if LOp = 'DELETE' then
    begin
      FGridEventos.Canvas.Brush.Color := RGB(255, 225, 225);
      FGridEventos.Canvas.Font.Color := RGB(175, 25, 25);
      FGridEventos.Canvas.Font.Style := [fsBold];
    end;
  end;
  FGridEventos.DefaultDrawColumnCell(Rect, DataCol, Column, State);
end;

procedure TfrmAuditoriaCambios.GridDiffDrawCell(Sender: TObject; ACol, ARow: LongInt;
  Rect: TRect; State: TGridDrawState);
var
  LEstado: string;
begin
  if (ARow > 0) and not (gdSelected in State) then
  begin
    LEstado := FGridDiff.Cells[3, ARow];
    if LEstado = 'MODIFICADO' then
    begin
      FGridDiff.Canvas.Brush.Color := RGB(255, 248, 220);
      if ACol in [0, 3] then
        FGridDiff.Canvas.Font.Style := [fsBold];
    end
    else if LEstado = 'AÑADIDO' then
      FGridDiff.Canvas.Brush.Color := RGB(230, 248, 232)
    else if LEstado = 'ELIMINADO' then
      FGridDiff.Canvas.Brush.Color := RGB(255, 232, 232);

    FGridDiff.Canvas.FillRect(Rect);
    FGridDiff.Canvas.TextRect(Rect, Rect.Left + 4, Rect.Top + 3, FGridDiff.Cells[ACol, ARow]);
  end;
end;

end.

