setup-db.js

import 'dotenv/config';
import pg from 'pg';

const { Pool } = pg;

// Configure connection using your existing .env variables
const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5433', 10),
  database: process.env.DB_NAME || 'job_queue',
  user: process.env.DB_USER || 'job_user',
  password: process.env.DB_PASSWORD || 'job_password',
});

const setupQuery = `
  -- 1. Ensure the jobs table has a progress column
  ALTER TABLE jobs ADD COLUMN IF NOT EXISTS progress INTEGER DEFAULT 0;

  -- 2. Ensure we have an index on status for faster dashboard queries
  CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status);

  -- 3. (Optional) Verify the table structure
  SELECT column_name, data_type 
  FROM information_schema.columns 
  WHERE table_name = 'jobs' AND column_name = 'progress';
`;

async function runSetup() {
  console.log('🚀 Starting Database Schema Update...');
  
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    
    await client.query(setupQuery);
    
    await client.query('COMMIT');
    console.log('✅ Database schema updated successfully!');
    console.log('👉 Added: "progress" column (Integer)');
    console.log('👉 Added: "idx_jobs_status" index');
    
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('❌ Database setup failed:', error.message);
  } finally {
    client.release();
    await pool.end();
    process.exit(0);
  }
}

runSetup();