-- ============================================
-- FaceLogX - Camera Stream URL Migration
-- Adds pi_device_id to cameras for proper stream URL construction
-- ============================================

USE facelogx;

-- Add pi_device_id column to cameras table
ALTER TABLE cameras ADD COLUMN IF NOT EXISTS pi_device_id INT NULL AFTER id;

-- Add foreign key constraint if it doesn't exist
ALTER TABLE cameras
  ADD CONSTRAINT fk_camera_pi_device 
  FOREIGN KEY (pi_device_id) REFERENCES pi_devices(id) ON DELETE SET NULL;

-- Add unique constraint to prevent duplicate cameras per device
ALTER TABLE cameras
  ADD CONSTRAINT unique_camera_per_device 
  UNIQUE (pi_device_id, device_index);

-- Create index on pi_device_id for faster queries
CREATE INDEX IF NOT EXISTS idx_camera_pi_device ON cameras(pi_device_id);

-- Create index on status for filtering
CREATE INDEX IF NOT EXISTS idx_camera_status ON cameras(status);
