/**
 * Migration to support Role-Based Last Write Wins (LWW)
 * Creates a central metadata table to track the last role that modified a record
 */

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

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const db = new Database(dbPath);

console.log('Running Sync Metadata Migration...');

try {
  // Create central metadata tracking table
  db.prepare(`
    CREATE TABLE IF NOT EXISTS sync_metadata (
      uid TEXT PRIMARY KEY,
      table_name TEXT NOT NULL,
      last_modified_by_role TEXT NOT NULL,
      last_modified_at DATETIME DEFAULT (datetime('now', 'localtime'))
    )
  `).run();

  // Create an index for faster lookups during merge operations
  db.prepare('CREATE INDEX IF NOT EXISTS idx_sync_metadata_lookup ON sync_metadata(uid, table_name)').run();

  console.log('✅ Sync metadata table created successfully.');
  
  // Note: Existing records will dynamically populate this table upon their next update
  // or we assume a base priority for records lacking metadata.

} catch (error) {
  console.error('❌ Migration failed:', error.message);
} finally {
  db.close();
}