-- Migration: Remove user_id foreign key from journey_executions
-- Purpose: Allow both workers and hirers to be enrolled in journeys without failing on foreign key constraints

USE aayusha_backend;

-- 1. Remove the foreign key that references users(id) from journey_executions
SET @constraint_name_je = (
    SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = 'aayusha_backend' 
    AND TABLE_NAME = 'journey_executions' 
    AND COLUMN_NAME = 'user_id' 
    AND REFERENCED_TABLE_NAME = 'users'
    LIMIT 1
);

SET @query_je = IF(@constraint_name_je IS NOT NULL, 
    CONCAT('ALTER TABLE journey_executions DROP FOREIGN KEY ', @constraint_name_je), 
    'SELECT "No FK on journey_executions.user_id found"');

PREPARE stmt_je FROM @query_je;
EXECUTE stmt_je;
DEALLOCATE PREPARE stmt_je;

-- 2. Also remove any foreign key from notification_logs
SET @constraint_name_nl = (
    SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = 'aayusha_backend' 
    AND TABLE_NAME = 'notification_logs' 
    AND COLUMN_NAME = 'user_id' 
    AND REFERENCED_TABLE_NAME = 'users'
    LIMIT 1
);

SET @query_nl = IF(@constraint_name_nl IS NOT NULL, 
    CONCAT('ALTER TABLE notification_logs DROP FOREIGN KEY ', @constraint_name_nl), 
    'SELECT "No FK on notification_logs.user_id found"');

PREPARE stmt_nl FROM @query_nl;
EXECUTE stmt_nl;
DEALLOCATE PREPARE stmt_nl;

-- 3. Also check journey_execution_logs
SET @constraint_name_logs = (
    SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = 'aayusha_backend' 
    AND TABLE_NAME = 'journey_execution_logs' 
    AND COLUMN_NAME = 'user_id' 
    AND REFERENCED_TABLE_NAME = 'users'
    LIMIT 1
);

SET @query_logs = IF(@constraint_name_logs IS NOT NULL, 
    CONCAT('ALTER TABLE journey_execution_logs DROP FOREIGN KEY ', @constraint_name_logs), 
    'SELECT "No FK on journey_execution_logs.user_id found"');

PREPARE stmt_logs FROM @query_logs;
EXECUTE stmt_logs;
DEALLOCATE PREPARE stmt_logs;

-- 4. Add user_type column to journey_execution_logs if it doesn't exist
SET @column_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'aayusha_backend' 
    AND TABLE_NAME = 'journey_execution_logs' 
    AND COLUMN_NAME = 'user_type'
);

SET @query_add_col = IF(@column_exists = 0, 
    'ALTER TABLE journey_execution_logs ADD COLUMN user_type VARCHAR(20) DEFAULT "Hirer" AFTER user_id', 
    'SELECT "user_type already exists in journey_execution_logs"');

PREPARE stmt_add_col FROM @query_add_col;
EXECUTE stmt_add_col;
DEALLOCATE PREPARE stmt_add_col;

-- Verification
SELECT 
    TABLE_NAME, 
    CONSTRAINT_NAME, 
    COLUMN_NAME, 
    REFERENCED_TABLE_NAME 
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
    TABLE_SCHEMA = 'aayusha_backend' 
    AND TABLE_NAME IN ('journey_executions', 'notification_logs', 'journey_execution_logs')
    AND REFERENCED_TABLE_NAME = 'users';
