-- Migration : Add face/reid embedding storage tables.
-- Date : 2026-06-04
-- Author : Louis (toastaspiring)
-- Context :
--   Incident 2026-06-04 — rsync sur la VM Scaleway a écrasé les fichiers
--   .pkl des galleries face/reid (runtime data, jamais commités). Re-
--   enrolment requis pour chaque athlète. Pour éviter que ça se
--   reproduise, on persiste les embeddings dans MySQL côté OVH plutôt
--   que dans des .pkl sur la VM.
--
--   Le worker Scaleway POSTe les embeddings via /v1/server/embeddings/*
--   à chaque enrolment, et GETe la galerie complète au démarrage.
--
-- Schéma :
--   face_embeddings(user_id, embedding BLOB 2048b, source, created_at)
--   reid_embeddings(user_id, embedding BLOB 18432b, vis_score, source,
--                   keypoint_count, created_at)
--
-- BLOB tailles :
--   AdaFace IR-50 → 512 float32 = 2048 bytes
--   BPBreID HRNet32 → 4608 float32 = 18432 bytes (foreg 512 + 8 parts × 512)
--
--   On stocke en ``LONGBLOB`` pour rester compatible si on change de
--   backbone (e.g. AdaFace IR-101 → 1024d, BPBreID OSNet → 3328d).

-- Note charset : on n''écrit PAS de `DEFAULT CHARSET=...` au bout pour ne
-- pas forcer un charset différent de la table users — sinon MySQL refuse
-- la FK avec "Referencing column and referenced column are incompatible".
-- La DB hérite déjà du charset par défaut (utf8mb4) au niveau base.

CREATE TABLE IF NOT EXISTS face_embeddings (
    id             CHAR(36)     PRIMARY KEY,
    user_id        CHAR(36)     NOT NULL,
    embedding      LONGBLOB     NOT NULL,
    embedding_dim  SMALLINT     UNSIGNED  NOT NULL DEFAULT 512,
    source         VARCHAR(20)  NOT NULL DEFAULT 'video',
    quality_score  FLOAT        NULL,
    aligned_path   VARCHAR(512) NULL,
    created_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_created (created_at)
);

CREATE TABLE IF NOT EXISTS reid_embeddings (
    id              CHAR(36)     PRIMARY KEY,
    user_id         CHAR(36)     NOT NULL,
    embedding       LONGBLOB     NOT NULL,
    embedding_dim   SMALLINT     UNSIGNED  NOT NULL DEFAULT 4608,
    vis_score       FLOAT        NULL,
    keypoint_count  TINYINT      UNSIGNED  NULL,
    source          VARCHAR(20)  NOT NULL DEFAULT 'video',
    aligned_path    VARCHAR(512) NULL,
    created_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_created (created_at)
);

-- Vue pratique : count d''embeddings par utilisateur, pour le dashboard.
CREATE OR REPLACE VIEW v_embedding_counts AS
SELECT
    u.id                                    AS user_id,
    u.username                              AS username,
    COALESCE(f.n, 0)                        AS face_count,
    COALESCE(r.n, 0)                        AS reid_count,
    GREATEST(IFNULL(f.last_at, 0), IFNULL(r.last_at, 0)) AS last_enroll_at
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS n, UNIX_TIMESTAMP(MAX(created_at)) AS last_at
    FROM face_embeddings GROUP BY user_id
) f ON f.user_id = u.id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS n, UNIX_TIMESTAMP(MAX(created_at)) AS last_at
    FROM reid_embeddings GROUP BY user_id
) r ON r.user_id = u.id;
