[9.0] JobDB snapshot: use a summary table for jobs in final state

[fstagni]

• [x] do the same for pilot
• [x] add tests
• [x] try to avoid the initial loop from taking too long
• [x] update the v9 notes
• [ ] Port to DiracX

BEGINRELEASENOTES

*WMS: NEW: JobDB snapshot: use a summary table for jobs in final state

ENDRELEASENOTES

Aims to improve the performance of job and pilot lookups in large Jobs tables by introducing summary tables. This requires the creation of new database tables and includes the following updates:

Description:

1. New Tables:

CREATE TABLE IF NOT EXISTS JobsHistorySummary (
  Status VARCHAR(32),
  Site VARCHAR(100),
  Owner VARCHAR(32),
  OwnerGroup VARCHAR(128),
  JobGroup VARCHAR(32),
  JobType VARCHAR(32),
  ApplicationStatus VARCHAR(255),
  MinorStatus VARCHAR(128),
  JobCount INT,
  RescheduleSum INT,
  PRIMARY KEY (Status, Site, Owner, OwnerGroup, JobGroup, JobType, ApplicationStatus, MinorStatus)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `PilotsHistorySummary` (
  `GridSite` VARCHAR(128),
  `GridType` VARCHAR(32),
  `Status` VARCHAR(32),
  `PilotCount` INT,
  PRIMARY KEY (`GridSite`,`GridType`,`Status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Benefits:

This change significantly reduces the lookup time for large Jobs tables.