-- Face photo enrolment columns on AccessProfile (tenant DBs).
--
-- TenantDbContext has no EF migration history -- its schema is created by EnsureCreated --
-- so schema changes are applied with an idempotent script, matching how this project
-- already works. Safe to re-run: each ADD COLUMN is skipped when the column exists.
--
-- The face image itself is NEVER stored here. It travels in DeviceCommand.PayloadJson only
-- until a Hikvision terminal confirms enrolment, at which point AgentService purges the
-- payload. What these columns keep is the enrolment state plus the device-side record id,
-- which is what a later replace/delete needs.
--
-- Run once per tenant database. Replace the USE line, or run the whole file per schema:
--
--   mysql -u root hexa_tenant_ironcore  < add-face-photo-columns.sql
--   mysql -u root hexa_tenant_pulsefit  < add-face-photo-columns.sql
--   mysql -u root hexa_tenant_flexzone  < add-face-photo-columns.sql
--   mysql -u root hexa_tenant_titanfit  < add-face-photo-columns.sql

DROP PROCEDURE IF EXISTS add_face_photo_columns;

DELIMITER //

CREATE PROCEDURE add_face_photo_columns()
BEGIN
    -- True once a terminal has accepted a face for this profile.
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME   = 'accessprofiles'
          AND COLUMN_NAME  = 'HasFacePhoto'
    ) THEN
        ALTER TABLE accessprofiles
            ADD COLUMN HasFacePhoto tinyint(1) NOT NULL DEFAULT 0;
    END IF;

    -- None | Pending | Synced | Failed.
    -- Distinguishes "still uploading" from "the terminal rejected the face", which
    -- HasFacePhoto alone cannot express.
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME   = 'accessprofiles'
          AND COLUMN_NAME  = 'FacePhotoStatus'
    ) THEN
        ALTER TABLE accessprofiles
            ADD COLUMN FacePhotoStatus varchar(20) NOT NULL DEFAULT 'None';
    END IF;

    -- Hikvision face-record id (FPID) returned on enrolment; the handle for replace/delete.
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME   = 'accessprofiles'
          AND COLUMN_NAME  = 'FacePhotoFPID'
    ) THEN
        ALTER TABLE accessprofiles
            ADD COLUMN FacePhotoFPID varchar(64) NULL;
    END IF;

    -- Why the terminal rejected the face (not frontal, eyes closed, poor lighting).
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME   = 'accessprofiles'
          AND COLUMN_NAME  = 'FacePhotoError'
    ) THEN
        ALTER TABLE accessprofiles
            ADD COLUMN FacePhotoError varchar(500) NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME   = 'accessprofiles'
          AND COLUMN_NAME  = 'FacePhotoUpdatedAt'
    ) THEN
        ALTER TABLE accessprofiles
            ADD COLUMN FacePhotoUpdatedAt datetime(6) NULL;
    END IF;
END //

DELIMITER ;

CALL add_face_photo_columns();
DROP PROCEDURE add_face_photo_columns;

-- Verification: should list all five columns.
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'accessprofiles'
  AND (COLUMN_NAME LIKE 'FacePhoto%' OR COLUMN_NAME = 'HasFacePhoto')
ORDER BY COLUMN_NAME;