const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./mattress.db');

function extractParishCode(code) {
  if (!code) return null;
  const parts = code.split('/');
  return parts.length === 2 ? parts[1] : code;
}

function migrateParishCodes(tableName, columnName, callback) {
  db.all(`SELECT DISTINCT ${columnName} FROM ${tableName} WHERE ${columnName} LIKE '%/%'`, (err, rows) => {
    if (err) {
      console.error(`Error fetching from ${tableName}:`, err.message);
      return callback(err);
    }

    let updates = rows.map(row => {
      const oldCode = row[columnName];
      const newCode = extractParishCode(oldCode);
      return new Promise((resolve, reject) => {
        db.run(`UPDATE ${tableName} SET ${columnName} = ? WHERE ${columnName} = ?`, [newCode, oldCode], function (updateErr) {
          if (updateErr) {
            console.error(`Failed to update ${tableName} ${oldCode} -> ${newCode}:`, updateErr.message);
            return reject(updateErr);
          }
          console.log(`[${tableName}] Updated ${oldCode} → ${newCode}`);
          resolve();
        });
      });
    });

    Promise.all(updates)
      .then(() => callback(null))
      .catch(callback);
  });
}

// ✅ Use actual column names from your tables
migrateParishCodes('parishes', 'parish_code', err => {
  if (err) return console.error("❌ Migration failed for 'parishes'");
  migrateParishCodes('customer_codes', 'parish_id', err2 => {
    if (err2) return console.error("❌ Migration failed for 'customer_codes'");
    console.log('✅ Parish code migration completed successfully.');
    db.close();
  });
});
