/**
 * Script to update the database schema and reload data
 */

const fs = require('fs');
const { Pool } = require('pg');
const { exec } = require('child_process');
const path = require('path');

// Read database configuration
const dbConfFile = fs.readFileSync('db.conf', 'utf8');
const dbConfig = {};

// Parse the db.conf file
dbConfFile.split('\n').forEach(line => {
  if (line.trim() === '') return;
  
  const [key, value] = line.split('=');
  if (key && value) {
    // Remove quotes if present
    const cleanValue = value.replace(/^['"]|['"]$/g, '');
    dbConfig[key] = cleanValue;
  }
});

// Configure PostgreSQL connection
const pool = new Pool({
  user: dbConfig.PSQL_USER,
  host: dbConfig.PSQL_HOST,
  database: dbConfig.PSQL_DBNAME,
  password: dbConfig.PSQL_PASSWORD,
  port: dbConfig.PSQL_PORT,
});

// Function to execute SQL from a file
async function executeSqlFile(filePath) {
  const client = await pool.connect();
  try {
    const sql = fs.readFileSync(filePath, 'utf8');
    await client.query('BEGIN');
    await client.query(sql);
    await client.query('COMMIT');
    console.log(`SQL file ${filePath} executed successfully`);
  } catch (e) {
    await client.query('ROLLBACK');
    console.error(`Error executing SQL file ${filePath}:`, e);
    throw e;
  } finally {
    client.release();
  }
}

// Function to run a Node.js script
function runScript(scriptPath) {
  return new Promise((resolve, reject) => {
    console.log(`Running script: ${scriptPath}`);
    const process = exec(`node ${scriptPath}`, (error, stdout, stderr) => {
      if (error) {
        console.error(`Error executing ${scriptPath}:`, error);
        return reject(error);
      }
      console.log(stdout);
      if (stderr) {
        console.error(stderr);
      }
      resolve();
    });
  });
}

// Main function
async function main() {
  try {
    // 1. Alter the table structure
    console.log('Altering table structure...');
    await executeSqlFile('alter_table.sql');
    
    // 2. Reload the data
    console.log('Reloading data...');
    await runScript('load_to_db.js');
    
    // 3. Close the pool
    await pool.end();
    
    console.log('Database update completed successfully');
  } catch (e) {
    console.error('Error updating database:', e);
    process.exit(1);
  }
}

// Run the main function
main();