// Script to add the 'comment' column to the scores table
const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// Path to the database
const DB_PATH = process.env.DB_PATH || path.join(__dirname, '../../data/framed.db');
console.log(`Connecting to database: ${DB_PATH}`);

// Connect to the database
const db = new sqlite3.Database(DB_PATH, (err) => {
  if (err) {
    console.error('Error connecting to database:', err.message);
    process.exit(1);
  }
  console.log('Connected to the SQLite database.');
  
  // Check if the column already exists
  db.get("PRAGMA table_info(scores)", (err, rows) => {
    if (err) {
      console.error('Error checking table schema:', err.message);
      closeAndExit(1);
    }
    
    // Parse the result to check if the comment column exists
    const hasCommentColumn = rows && rows.some(row => row.name === 'comment');
    
    if (hasCommentColumn) {
      console.log('The comment column already exists in the scores table.');
      closeAndExit(0);
    } else {
      // Add the comment column to the scores table
      db.run('ALTER TABLE scores ADD COLUMN comment TEXT', (err) => {
        if (err) {
          console.error('Error adding comment column:', err.message);
          closeAndExit(1);
        }
        
        console.log('Successfully added comment column to scores table.');
        closeAndExit(0);
      });
    }
  });
});

// Function to close the database connection and exit
function closeAndExit(code) {
  db.close((err) => {
    if (err) {
      console.error('Error closing database connection:', err.message);
    } else {
      console.log('Database connection closed.');
    }
    process.exit(code);
  });
}