PS_ProserveReport/ProserveAPI/htdocs/proserve/update_proserve_db 1.6.0.sql

-- ============================================================
-- Migration 1.6.0 - Optimisation des temps de reponse
-- ============================================================
-- Cible : MariaDB 10.4+ (XAMPP local).
-- Idempotent : ce fichier peut etre rejoue sans erreur sur n'importe quel poste.
-- Aucun changement de structure : seuls des index sont ajoutes
-- (via une procedure stockee qui teste leur presence) et la vue
-- sessiondebriefs est redefinie via CREATE OR REPLACE VIEW.
-- ============================================================

USE proserveapi;

-- ------------------------------------------------------------
-- Procedure helper : ajoute un index seulement s'il n'existe pas
-- (CREATE INDEX IF NOT EXISTS n'est dispo qu'a partir de MariaDB 10.5.4)
-- ------------------------------------------------------------
DROP PROCEDURE IF EXISTS create_index_if_missing;

DELIMITER $$
CREATE PROCEDURE create_index_if_missing(
    IN p_table VARCHAR(64),
    IN p_index VARCHAR(64),
    IN p_cols  VARCHAR(255)
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.STATISTICS
         WHERE TABLE_SCHEMA = DATABASE()
           AND TABLE_NAME   = p_table
           AND INDEX_NAME   = p_index
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD INDEX `', p_index, '` (', p_cols, ')');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$
DELIMITER ;

-- ------------------------------------------------------------
-- Index manquants identifies a l'analyse des requetes lourdes
-- ------------------------------------------------------------

-- JOIN reactevents <-> triggerevents : la cle composite la plus utilisee
CALL create_index_if_missing('reactevents',   'idx_react_session_event',  '`srcEventSessionId`, `srcEventIndex`');

-- Filtres "tirs d'un user dans une session" (stats_object getShotsFiredByUser, getTargetKilledInSession)
CALL create_index_if_missing('triggerevents', 'idx_trigger_session_user', '`sessionId`, `srcUserId`');

-- Agregation NbKilled (auto-jointure RK dans la vue sessiondebriefs)
CALL create_index_if_missing('reactevents',   'idx_react_killed',         '`targetKilled`, `srcEventSessionId`');

-- Tri/filtrage des sessions par date (listes recentes)
CALL create_index_if_missing('sessions',      'idx_sessions_date',        '`sessionDate` DESC');

-- Nettoyage de la procedure helper
DROP PROCEDURE create_index_if_missing;


-- ------------------------------------------------------------
-- Redefinition de la vue sessiondebriefs (idempotent via CREATE OR REPLACE)
-- ------------------------------------------------------------
-- Modifications par rapport a la definition d'origine :
--   - Suppression des wraps "(SELECT IFNULL(...))" qui forcaient une evaluation par row
--     -> remplaces par IFNULL(...) directement (semantique identique).
--   - Suppression des sous-requetes correlees (SELECT R.displayName FROM userroles R WHERE R.id = ...)
--     -> remplacees par deux LEFT JOIN explicites sur userroles (URS et URT).
--   - Tous les autres JOIN, le GROUP BY et l'ORDER BY restent strictement identiques
--     pour preserver le contrat de sortie (memes colonnes, memes lignes, memes valeurs).
-- ------------------------------------------------------------

CREATE OR REPLACE VIEW sessiondebriefs AS
SELECT
    S.id              AS SessionId,
    S.sessionType     AS SessionTypeId,
    ST.displayName    AS SessionType,
    S.sessionName     AS SessionName,
    S.sessionDate     AS SessionDate,
    S.mapName         AS MapName,
    S.scenarioName    AS ScenarioName,
    S.success         AS SessionSuccessful,
    S.timeToFinish    AS SessionDuration,
    COALESCE(T.type, -1)              AS TriggerTypeId,
    COALESCE(TT.displayName, '')      AS TriggerType,
    IFNULL(T.srcUserId, PS.userId)    AS ShooterId,
    US.username                       AS ShooterName,
    IFNULL(PS.role, 3)                AS ShooterRoleId,
    URS.displayName                   AS ShooterRole,
    COALESCE(T.indexCount, -1)        AS ShotIndex,
    COALESCE(R.id, -1)                AS ReactId,
    COALESCE(R.reactMode, 2)          AS ReactModeId,
    COALESCE(RM.displayName, '')      AS ReactMode,
    COALESCE(R.reactType, -1)         AS ReactTypeId,
    COALESCE(RT.displayName, '')      AS ReactType,
    COALESCE(R.hitUserId, -1)         AS TargetUserId,
    COALESCE(UH.username, '')         AS TargetUserName,
    IFNULL(PH.role, 3)                AS TargetRoleId,
    URT.displayName                   AS TargetRole,
    COALESCE(R.hitTargetName, '')     AS TargetName,
    COALESCE(R.hitBoneName, '')       AS TargetBoneName,
    COALESCE(R.targetKilled, 0)       AS TargetKilled,
    COALESCE(R.objectHitLocationX, 0) AS HitLocationX,
    COALESCE(R.objectHitLocationY, 0) AS HitLocationY,
    COALESCE(R.objectHitTagLocation, '') AS HitLocationTag,
    COALESCE(R.hitPrecision, 0)       AS HitPrecision,
    COALESCE(R.distance, 0)           AS HitTargetDistance,
    COALESCE(R.reactTime, 0)          AS ReactionTime,
    COALESCE(R.timeStamp, 0)          AS TimeStamp,
    COUNT(DISTINCT R.id)              AS NbHit,
    COUNT(DISTINCT RK.srcEventIndex, RK.hitTargetName) AS NbKilled
FROM sessions S
LEFT JOIN participates      PS  ON (S.id = PS.sessionId)
LEFT JOIN participates      PH  ON (S.id = PH.sessionId)
LEFT JOIN triggerevents     T   ON (S.id = T.sessionId)
LEFT JOIN triggereventtypes TT  ON (TT.id = T.type)
LEFT JOIN sessiontypes      ST  ON (ST.id = S.sessionType)
LEFT JOIN reactevents       R   ON (T.indexCount = R.srcEventIndex AND T.sessionId = R.srcEventSessionId)
LEFT JOIN users             UH  ON (UH.id = R.hitUserId)
LEFT JOIN users             US  ON (US.id = T.srcUserId OR US.id = PS.userId)
LEFT JOIN reacteventtypes   RT  ON (RT.id = R.reactType)
LEFT JOIN reacteventmodes   RM  ON (RM.id = COALESCE(R.reactMode, 2))
LEFT JOIN reactevents       RK  ON (R.id = RK.id AND RK.targetKilled = 1)
LEFT JOIN userroles         URS ON (URS.id = IFNULL(PS.role, 3))
LEFT JOIN userroles         URT ON (URT.id = IFNULL(PH.role, 3))
GROUP BY SessionId, ShooterId, ShotIndex, TargetName
ORDER BY SessionId, ShooterId, ShotIndex, ReactId;