-- Direct SQL script to fix jobs_job table schema
-- Run this with: mysql -u your_user -p your_database < fix_job_table_direct.sql

-- Show current schema
SELECT 'Current columns in jobs_job table:' as '';
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'jobs_job'
ORDER BY ORDINAL_POSITION;

-- Fix 1: Drop 'description' if both 'description' and 'job_description' exist
SET @drop_description = (
    SELECT IF(
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs_job' AND COLUMN_NAME = 'description') > 0
        AND
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs_job' AND COLUMN_NAME = 'job_description') > 0,
        'ALTER TABLE jobs_job DROP COLUMN description;',
        'SELECT "No need to drop description column" as Status;'
    )
);
PREPARE stmt FROM @drop_description;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fix 2: Rename 'description' to 'job_description' if only 'description' exists
SET @rename_description = (
    SELECT IF(
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs_job' AND COLUMN_NAME = 'description') > 0
        AND
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs_job' AND COLUMN_NAME = 'job_description') = 0,
        'ALTER TABLE jobs_job CHANGE COLUMN description job_description TEXT NOT NULL;',
        'SELECT "No need to rename description column" as Status;'
    )
);
PREPARE stmt FROM @rename_description;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fix 3: Drop 'title' if both 'title' and 'job_title' exist
SET @drop_title = (
    SELECT IF(
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs_job' AND COLUMN_NAME = 'title') > 0
        AND
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs_job' AND COLUMN_NAME = 'job_title') > 0,
        'ALTER TABLE jobs_job DROP COLUMN title;',
        'SELECT "No need to drop title column" as Status;'
    )
);
PREPARE stmt FROM @drop_title;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fix 4: Rename 'title' to 'job_title' if only 'title' exists
SET @rename_title = (
    SELECT IF(
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs_job' AND COLUMN_NAME = 'title') > 0
        AND
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs_job' AND COLUMN_NAME = 'job_title') = 0,
        'ALTER TABLE jobs_job CHANGE COLUMN title job_title VARCHAR(255) NOT NULL;',
        'SELECT "No need to rename title column" as Status;'
    )
);
PREPARE stmt FROM @rename_title;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fix 5: Make company_name nullable if it exists
SET @fix_company_name = (
    SELECT IF(
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs_job' AND COLUMN_NAME = 'company_name') > 0,
        'ALTER TABLE jobs_job MODIFY COLUMN company_name VARCHAR(255) NULL DEFAULT NULL;',
        'SELECT "company_name column does not exist" as Status;'
    )
);
PREPARE stmt FROM @fix_company_name;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fix 6: Make status nullable if it exists
SET @fix_status = (
    SELECT IF(
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs_job' AND COLUMN_NAME = 'status') > 0,
        'ALTER TABLE jobs_job MODIFY COLUMN status VARCHAR(50) NULL DEFAULT NULL;',
        'SELECT "status column does not exist" as Status;'
    )
);
PREPARE stmt FROM @fix_status;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Show final schema
SELECT 'Final columns in jobs_job table:' as '';
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'jobs_job'
ORDER BY ORDINAL_POSITION;

SELECT 'Schema fixes completed successfully!' as '';