location-mqtt-tracker-app/scripts/fix-acl-topic-patterns.js

#!/usr/bin/env node

/**
 * Migration script to fix ACL topic patterns
 *
 * Changes: owntracks/owntrack/<device_id> → owntracks/<username>/#
 *
 * This script:
 * 1. Finds all ACL rules with the old pattern
 * 2. Looks up the correct MQTT username for each device
 * 3. Updates the topic_pattern to use the username
 */

const Database = require('better-sqlite3');
const path = require('path');

const dbPath = path.join(__dirname, '..', 'tracker.db');
const db = new Database(dbPath);

console.log('🔧 Fixing ACL topic patterns...\n');

try {
  // Get all ACL rules with the old pattern
  const aclRules = db.prepare(`
    SELECT id, device_id, topic_pattern, permission
    FROM mqtt_acl_rules
    WHERE topic_pattern LIKE 'owntracks/owntrack/%'
  `).all();

  console.log(`Found ${aclRules.length} ACL rules to fix\n`);

  if (aclRules.length === 0) {
    console.log('✓ No ACL rules need fixing!');
    db.close();
    process.exit(0);
  }

  let fixed = 0;
  let failed = 0;

  for (const rule of aclRules) {
    // Look up the MQTT username for this device
    const credential = db.prepare(`
      SELECT mqtt_username
      FROM mqtt_credentials
      WHERE device_id = ?
    `).get(rule.device_id);

    if (!credential) {
      console.log(`⚠ Warning: No MQTT credentials found for device ${rule.device_id}, skipping...`);
      failed++;
      continue;
    }

    const oldPattern = rule.topic_pattern;
    const newPattern = `owntracks/${credential.mqtt_username}/#`;

    // Update the ACL rule
    db.prepare(`
      UPDATE mqtt_acl_rules
      SET topic_pattern = ?
      WHERE id = ?
    `).run(newPattern, rule.id);

    console.log(`✓ Fixed rule for device ${rule.device_id}:`);
    console.log(`  Old: ${oldPattern}`);
    console.log(`  New: ${newPattern}\n`);
    fixed++;
  }

  // Mark pending changes for MQTT sync
  db.prepare(`
    UPDATE mqtt_sync_status
    SET pending_changes = pending_changes + 1
  `).run();

  console.log('─'.repeat(50));
  console.log(`\n✅ Migration complete!`);
  console.log(`   Fixed: ${fixed}`);
  console.log(`   Failed: ${failed}`);
  console.log(`\n⚠️  Run MQTT Sync to apply changes to Mosquitto broker`);

} catch (error) {
  console.error('❌ Error during migration:', error);
  process.exit(1);
} finally {
  db.close();
}