-- =====================================================
-- SQL Script to Create face_registrations Table
-- =====================================================
-- Description: Creates the face_registrations table for storing encrypted face data
-- Date: 2025-01-15
-- =====================================================

-- Drop table if exists (use with caution in production)
DROP TABLE IF EXISTS `face_registrations`;

-- Create face_registrations table
CREATE TABLE `face_registrations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `company_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `face_data` TEXT NOT NULL COMMENT 'Encrypted face data',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `face_registrations_user_id_index` (`user_id`),
  INDEX `face_registrations_company_id_index` (`company_id`),
  CONSTRAINT `face_registrations_user_id_foreign` 
    FOREIGN KEY (`user_id`) 
    REFERENCES `users` (`id`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE,
  CONSTRAINT `face_registrations_company_id_foreign` 
    FOREIGN KEY (`company_id`) 
    REFERENCES `companies` (`id`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Verification Query (Optional - Run after table creation)
-- =====================================================
-- SELECT * FROM information_schema.tables 
-- WHERE table_schema = DATABASE() 
-- AND table_name = 'face_registrations';

-- =====================================================
-- Notes:
-- =====================================================
-- 1. This table stores encrypted face data for user authentication
-- 2. face_data is encrypted using Laravel's Crypt facade
-- 3. Foreign keys ensure data integrity with users and companies tables
-- 4. CASCADE delete ensures face registrations are removed when user/company is deleted
-- =====================================================

