-- Migration: Add name and status fields to employee_faces table for registration

USE face_recognition_db;

-- Add name field (if not exists)
ALTER TABLE employee_faces 
ADD COLUMN IF NOT EXISTS name VARCHAR(255) NULL AFTER employee_id;

-- Add status field (0=pending, 1=fetched, 2=completed)
ALTER TABLE employee_faces 
ADD COLUMN IF NOT EXISTS status INT DEFAULT 0 AFTER name;

-- Add index on status for faster queries
ALTER TABLE employee_faces 
ADD INDEX IF NOT EXISTS idx_status (status);

-- Make employee_id nullable for registration (since it's open API)
ALTER TABLE employee_faces 
MODIFY COLUMN employee_id INT NULL;

-- Update existing records to have status 0 if NULL
UPDATE employee_faces SET status = 0 WHERE status IS NULL;

