-- Add posted_by_user_id to Jobs Table
-- GHAMECC Job Portal Database Migration
-- This migration adds a direct link between jobs and job_portal_users

-- Check if posted_by_user_id column exists, if not add it
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'jobs' 
AND COLUMN_NAME = 'posted_by_user_id';

SET @sql = IF(@col_exists = 0, 
    'ALTER TABLE jobs ADD COLUMN posted_by_user_id int(11) DEFAULT NULL AFTER member_id',
    'SELECT "posted_by_user_id column already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add index for posted_by_user_id
SET @index_exists = 0;
SELECT COUNT(*) INTO @index_exists 
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'jobs' 
AND INDEX_NAME = 'posted_by_user_id';

SET @sql = IF(@index_exists = 0, 
    'ALTER TABLE jobs ADD KEY posted_by_user_id (posted_by_user_id)',
    'SELECT "posted_by_user_id index already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add foreign key constraint for posted_by_user_id
SET @fk_exists = 0;
SELECT COUNT(*) INTO @fk_exists 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'jobs' 
AND CONSTRAINT_NAME = 'fk_jobs_posted_by_user';

SET @sql = IF(@fk_exists = 0, 
    'ALTER TABLE jobs ADD CONSTRAINT fk_jobs_posted_by_user FOREIGN KEY (posted_by_user_id) REFERENCES job_portal_users (id) ON DELETE SET NULL',
    'SELECT "posted_by_user foreign key already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Update existing jobs to set posted_by_user_id based on contact_email
-- This will match jobs to job_portal_users by email
UPDATE jobs j 
SET posted_by_user_id = (
    SELECT jpu.id 
    FROM job_portal_users jpu 
    WHERE jpu.email = j.contact_email 
    LIMIT 1
) 
WHERE posted_by_user_id IS NULL;

SELECT 'Jobs table posted_by_user_id migration completed successfully!' as result;
