/**
 * Database Migration Runner
 *
 * Runs on every deploy via `npm run build`.
 *
 * How it works:
 * 1. Creates core tables (users, _migrations) - always runs, idempotent
 * 2. Reads migrations from migrations/ folder
 * 3. Runs new migrations in order (tracked in _migrations table)
 *
 * To create a new migration:
 *   Create a file in migrations/ with format: {timestamp}_{name}.js
 *   Example: migrations/1704067200000_add_products_table.js
 *
 * Migration file format:
 *   module.exports = {
 *     name: 'add_products_table',
 *     up: async (client) => {
 *       await client.query(`CREATE TABLE products (...)`);
 *     }
 *   };
 */
const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL
});

async function migrate() {
  console.log('Running migrations...');

  const client = await pool.connect();
  try {
    // 1. Baseline schema — on a fresh DB only, apply the full schema dump first.
    //    It is a raw pg_dump that INCLUDES the _migrations table, so it must run
    //    before we create the tracking table (otherwise CREATE TABLE collides).
    await runBaselineSchema(client);

    // 2. Migration tracking table (no-op if the baseline dump already created it)
    await client.query(`
      CREATE TABLE IF NOT EXISTS _migrations (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL UNIQUE,
        applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // 3. Core tables (idempotent - safe to run every time)
    await runCoreMigrations(client);

    // 4. Run migrations from migrations/ folder
    await runFolderMigrations(client);

    console.log('Migrations complete.');
  } finally {
    client.release();
    await pool.end();
  }
}

/**
 * Baseline schema — applied ONCE on a fresh database.
 *
 * The full app schema (~80 tables) lives in db/baseline-schema.sql, generated from
 * a known-good DB with:
 *   pg_dump --schema-only --no-owner --no-privileges --exclude-table=_migrations \
 *           "$DATABASE_URL" > db/baseline-schema.sql
 *
 * It is applied only when the DB is fresh (uses `health_checks` as the
 * "schema-present" sentinel), so it is a no-op on existing/production databases —
 * which is why the dump can be raw `CREATE TABLE` (no IF NOT EXISTS needed).
 */
async function runBaselineSchema(client) {
  const baselinePath = path.join(__dirname, 'db', 'baseline-schema.sql');

  // Sentinel: if a core app table exists, the schema is already present — skip.
  const { rows } = await client.query("SELECT to_regclass('public.health_checks') AS t");
  if (rows[0] && rows[0].t) return;

  if (!fs.existsSync(baselinePath)) {
    console.warn('WARNING: fresh database but db/baseline-schema.sql is missing — ' +
                 'delta migrations that ALTER base tables will fail. Generate it with ' +
                 'pg_dump (see migrate.js / README).');
    return;
  }

  console.log('Fresh database — applying baseline schema (db/baseline-schema.sql)...');
  let sql = fs.readFileSync(baselinePath, 'utf8');
  // baseline-schema.sql is a raw `pg_dump --schema-only`. Strip the psql
  // meta-commands it emits (e.g. \restrict / \unrestrict on pg_dump 16+) — those
  // are psql client directives, not SQL, and the node-postgres driver can't run them.
  sql = sql.split('\n').filter((line) => !/^\s*\\/.test(line)).join('\n');
  await client.query(sql);
  // The dump runs `SELECT set_config('search_path','',false)`, which leaves this
  // session with an empty search_path. Restore it so the unqualified statements in
  // the core + delta migrations below resolve to the public schema.
  await client.query('SET search_path TO public');
  console.log('Baseline schema applied.');
}

/**
 * Core tables that every app needs.
 * These use CREATE IF NOT EXISTS so they're safe to run repeatedly.
 */
async function runCoreMigrations(client) {
  // Users table with subscription support
  // Used by Polsia for syncing end-user subscription status
  await client.query(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) NOT NULL,
      name VARCHAR(255),
      password_hash VARCHAR(255),
      created_at TIMESTAMPTZ DEFAULT NOW(),
      updated_at TIMESTAMPTZ DEFAULT NOW(),
      -- Subscription fields (synced by Polsia when customer subscribes)
      stripe_subscription_id VARCHAR(255),
      subscription_status VARCHAR(50),
      subscription_plan VARCHAR(255),
      subscription_expires_at TIMESTAMPTZ,
      subscription_updated_at TIMESTAMPTZ
    )
  `);

  // Unique constraint on email (required for UPSERT)
  await client.query(`
    CREATE UNIQUE INDEX IF NOT EXISTS users_email_unique_idx ON users (LOWER(email))
  `);

  // Index for subscription lookups
  await client.query(`
    CREATE INDEX IF NOT EXISTS users_stripe_subscription_id_idx ON users (stripe_subscription_id)
  `);

  // oracle_connections — referenced by ~9 delta migrations. Created here so a
  // completely fresh DB can run `node migrate.js` before server.js ever starts.
  // server.js ensureColumns() still adds any missing columns on normal startup;
  // this just guarantees the table exists for the delta migrations to ALTER.
  await client.query(`
    CREATE TABLE IF NOT EXISTS oracle_connections (
      id SERIAL PRIMARY KEY,
      user_id INTEGER REFERENCES users(id),
      name VARCHAR(255) NOT NULL,
      host VARCHAR(255),
      port INTEGER DEFAULT 1521,
      service_name VARCHAR(255),
      username VARCHAR(255),
      password_enc TEXT,
      connection_type VARCHAR(50) DEFAULT 'direct',
      server_type VARCHAR(50) DEFAULT 'db',
      proxy_url TEXT,
      proxy_key TEXT,
      connectivity_mode VARCHAR(50) DEFAULT 'tns',
      privilege_model VARCHAR(50) DEFAULT 'reader',
      apps_pwd_enc TEXT,
      weblogic_pwd_enc TEXT,
      ebs_instance_name VARCHAR(255),
      auto_upgrade_enabled BOOLEAN DEFAULT true,
      client_name VARCHAR(255),
      client_slug VARCHAR(255),
      created_at TIMESTAMPTZ DEFAULT NOW(),
      updated_at TIMESTAMPTZ DEFAULT NOW()
    )
  `);
}

/**
 * Run migrations from migrations/ folder.
 * Each migration runs once and is tracked in _migrations table.
 */
async function runFolderMigrations(client) {
  const migrationsDir = path.join(__dirname, 'migrations');

  // Skip if no migrations folder
  if (!fs.existsSync(migrationsDir)) {
    return;
  }

  // Get all migration files, sorted by name (timestamp prefix ensures order)
  const files = fs.readdirSync(migrationsDir)
    .filter(f => f.endsWith('.js'))
    .sort();

  if (files.length === 0) {
    return;
  }

  // Get already-applied migrations
  const applied = await client.query('SELECT name FROM _migrations');
  const appliedNames = new Set(applied.rows.map(r => r.name));

  // Run pending migrations
  for (const file of files) {
    const migration = require(path.join(migrationsDir, file));
    const name = migration.name || file.replace('.js', '');

    if (appliedNames.has(name)) {
      continue; // Already applied
    }

    console.log(`Running migration: ${name}`);

    try {
      await client.query('BEGIN');
      await migration.up(client);
      await client.query('INSERT INTO _migrations (name) VALUES ($1)', [name]);
      await client.query('COMMIT');
      console.log(`Migration complete: ${name}`);
    } catch (err) {
      await client.query('ROLLBACK');
      throw new Error(`Migration failed (${name}): ${err.message}`);
    }
  }
}

migrate().catch(err => {
  console.error('Migration failed:', err.message);
  process.exit(1);
});
