-- Update Jobs Table - Add Missing Columns
-- GHAMECC Job Portal Database Migration

-- Check if society_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 = 'society_id';

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

-- Check if approval_status 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 = 'approval_status';

SET @sql = IF(@col_exists = 0, 
    'ALTER TABLE jobs ADD COLUMN approval_status enum(\'pending\',\'approved\',\'rejected\') NOT NULL DEFAULT \'pending\' AFTER category_id',
    'SELECT "approval_status column already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check if approved_by 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 = 'approved_by';

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

-- Check if approval_date 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 = 'approval_date';

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

-- Check if approval_notes 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 = 'approval_notes';

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

-- Add indexes if they don't exist
-- Index for society_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 = 'society_id';

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

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

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

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

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

-- Add foreign key constraints if they don't exist
-- Foreign key for society_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_society';

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

-- Foreign key for approved_by
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_approved_by';

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

-- Update existing jobs to have pending approval status if they don't have one
UPDATE jobs SET approval_status = 'pending' WHERE approval_status IS NULL;

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