-- Fix job_applications table to use job_portal_user_id as primary identifier
-- GHAMECC Job Portal - Database Schema Update

-- First, add job_portal_user_id column if it doesn't exist
ALTER TABLE `job_applications` 
ADD COLUMN IF NOT EXISTS `job_portal_user_id` int(11) NOT NULL AFTER `member_id`,
ADD KEY IF NOT EXISTS `job_portal_user_id` (`job_portal_user_id`),
ADD CONSTRAINT IF NOT EXISTS `job_applications_ibfk_3` FOREIGN KEY (`job_portal_user_id`) REFERENCES `job_portal_users` (`id`) ON DELETE CASCADE;

-- Update existing records to populate job_portal_user_id from member_id where possible
-- This assumes that members who have job_portal_users accounts should be linked
UPDATE `job_applications` ja 
JOIN `job_portal_users` jpu ON ja.member_id = jpu.member_id 
SET ja.job_portal_user_id = jpu.id 
WHERE ja.job_portal_user_id IS NULL AND ja.member_id IS NOT NULL;

-- Remove the old unique constraint
ALTER TABLE `job_applications` 
DROP INDEX IF EXISTS `unique_application`;

-- Remove the member-specific constraints we added earlier
ALTER TABLE `job_applications` 
DROP INDEX IF EXISTS `unique_application_member`,
DROP INDEX IF EXISTS `unique_application_job_portal`;

-- Add new unique constraint using job_portal_user_id only
ALTER TABLE `job_applications` 
ADD UNIQUE KEY `unique_application` (`job_id`, `job_portal_user_id`);

-- Make member_id nullable and optional (keep for reference but not required)
ALTER TABLE `job_applications` 
MODIFY COLUMN `member_id` int(11) DEFAULT NULL;

-- Make job_portal_user_id NOT NULL since it's now the primary identifier
ALTER TABLE `job_applications` 
MODIFY COLUMN `job_portal_user_id` int(11) NOT NULL;
