-- Migration: Update Photos Table for Enhanced Functionality
-- Run this if you already have the database installed

USE scubatricky_risk;

-- Add missing columns if they don't exist
ALTER TABLE photos 
ADD COLUMN IF NOT EXISTS original_filename VARCHAR(255) AFTER filename,
ADD COLUMN IF NOT EXISTS file_path VARCHAR(500) AFTER original_filename,
ADD COLUMN IF NOT EXISTS display_order INT DEFAULT 0 AFTER caption,
ADD COLUMN IF NOT EXISTS uploaded_by INT AFTER display_order;

-- Add foreign key for uploaded_by if it doesn't exist
-- Note: This will fail silently if constraint already exists
SET @constraint_exists = (SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = 'scubatricky_risk' 
    AND TABLE_NAME = 'photos' 
    AND CONSTRAINT_NAME = 'photos_ibfk_3');

SET @sql = IF(@constraint_exists = 0,
    'ALTER TABLE photos ADD FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL',
    'SELECT "Foreign key already exists"');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS idx_display_order ON photos(assessment_id, display_order);

-- Update existing records
UPDATE photos SET uploaded_by = 1 WHERE uploaded_by IS NULL;

-- Verify changes
DESCRIBE photos;

SELECT 'Photos table migration completed successfully!' AS status;
