quest-vorteq/scripts/migrate-data.ts

/**
 * Data Migration Script
 *
 * Migrates data from legacy SQL Server database to PostgreSQL
 *
 * Usage: npx ts-node scripts/migrate-data.ts
 */

import sql from 'mssql';
import { PrismaClient } from '@prisma/client';
import * as bcrypt from 'bcryptjs';

const prisma = new PrismaClient();

// SQL Server configuration (legacy database)
const legacyConfig: sql.config = {
  server: process.env.MSSQL_HOST || '',
  database: process.env.PORTAL_DB_NAME || 'VorteqPortal',
  user: process.env.MSSQL_USER || '',
  password: process.env.MSSQL_PASSWORD || '',
  options: {
    encrypt: false,
    trustServerCertificate: true,
    enableArithAbort: true,
  },
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000,
  },
};

/**
 * Migration Statistics
 */
const stats = {
  auth_domain: { expected: 0, migrated: 0 },
  auth_user_type: { expected: 0, migrated: 0 },
  auth_user: { expected: 0, migrated: 0 },
  auth_permission_group: { expected: 0, migrated: 0 },
  auth_permission_rule: { expected: 0, migrated: 0 },
  auth_permission_rule_category: { expected: 0, migrated: 0 },
  quest_user: { expected: 0, migrated: 0 },
  quest_company: { expected: 0, migrated: 0 },
  quest_user_company: { expected: 0, migrated: 0 },
  quest_plant: { expected: 0, migrated: 0 },
  quest_inventory_type: { expected: 0, migrated: 0 },
  quest_email_event: { expected: 0, migrated: 0 },
  ship_request: { expected: 0, migrated: 0 },
  ship_request_detail: { expected: 0, migrated: 0 },
  alloc_request: { expected: 0, migrated: 0 },
  alloc_request_detail: { expected: 0, migrated: 0 },
};

/**
 * Connect to legacy SQL Server database
 */
async function connectToLegacy(): Promise<sql.ConnectionPool> {
  console.log('Connecting to legacy SQL Server database...');
  const pool = await sql.connect(legacyConfig);
  console.log('✓ Connected to legacy database');
  return pool;
}

/**
 * Migrate auth_domain table
 */
async function migrateAuthDomains(pool: sql.ConnectionPool) {
  console.log('\nMigrating auth_domain...');

  const result = await pool.request().query(`
    SELECT ID, Name, Description, IsActive, CreatedAt, UpdatedAt
    FROM auth_domain
  `);

  stats.auth_domain.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.auth_domain.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        name: row.Name,
        description: row.Description,
        is_active: row.IsActive,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
      },
    });
    stats.auth_domain.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.auth_domain.migrated}/${stats.auth_domain.expected} auth_domain records`
  );
}

/**
 * Migrate auth_user_type table
 */
async function migrateAuthUserTypes(pool: sql.ConnectionPool) {
  console.log('\nMigrating auth_user_type...');

  const result = await pool.request().query(`
    SELECT ID, Name, Description, IsAdmin, IsCustomer, IsInternal, CreatedAt, UpdatedAt
    FROM auth_user_type
  `);

  stats.auth_user_type.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.auth_user_type.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        name: row.Name,
        description: row.Description,
        is_admin: row.IsAdmin,
        is_customer: row.IsCustomer,
        is_internal: row.IsInternal,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
      },
    });
    stats.auth_user_type.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.auth_user_type.migrated}/${stats.auth_user_type.expected} auth_user_type records`
  );
}

/**
 * Migrate auth_user table
 */
async function migrateAuthUsers(pool: sql.ConnectionPool) {
  console.log('\nMigrating auth_user...');

  const result = await pool.request().query(`
    SELECT
      ID, Email, EmailVerified, Name, Image, CreatedAt, UpdatedAt,
      PasswordHash, Deactivated, DeactivatedAt, DeactivationReason,
      LoginCount, FailedLoginCount, LastLoginAt, LastLoginIP, LastFailedLoginAt,
      TwoFactorEnabled, TwoFactorSecret, TwoFactorBackupCodes,
      SSOProvider, SSOID, AuthUserTypeID, AuthDomainID
    FROM auth_user
  `);

  stats.auth_user.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.auth_user.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        email: row.Email,
        email_verified: row.EmailVerified,
        name: row.Name,
        image: row.Image,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
        password_hash: row.PasswordHash,
        deactivated: row.Deactivated,
        deactivated_at: row.DeactivatedAt,
        deactivation_reason: row.DeactivationReason,
        login_count: row.LoginCount,
        failed_login_count: row.FailedLoginCount,
        last_login_at: row.LastLoginAt,
        last_login_ip: row.LastLoginIP,
        last_failed_login_at: row.LastFailedLoginAt,
        two_factor_enabled: row.TwoFactorEnabled,
        two_factor_secret: row.TwoFactorSecret,
        two_factor_backup_codes: row.TwoFactorBackupCodes,
        sso_provider: row.SSOProvider,
        sso_id: row.SSOID,
        auth_user_type_id: row.AuthUserTypeID,
        auth_domain_id: row.AuthDomainID,
      },
    });
    stats.auth_user.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.auth_user.migrated}/${stats.auth_user.expected} auth_user records`
  );
}

/**
 * Migrate auth_permission_rule_category table
 */
async function migratePermissionRuleCategories(pool: sql.ConnectionPool) {
  console.log('\nMigrating auth_permission_rule_category...');

  const result = await pool.request().query(`
    SELECT ID, Name, Description, CreatedAt, UpdatedAt
    FROM auth_permission_rule_category
  `);

  stats.auth_permission_rule_category.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.auth_permission_rule_category.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        name: row.Name,
        description: row.Description,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
      },
    });
    stats.auth_permission_rule_category.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.auth_permission_rule_category.migrated}/${stats.auth_permission_rule_category.expected} auth_permission_rule_category records`
  );
}

/**
 * Migrate auth_permission_rule table
 */
async function migratePermissionRules(pool: sql.ConnectionPool) {
  console.log('\nMigrating auth_permission_rule...');

  const result = await pool.request().query(`
    SELECT ID, Name, Description, AuthPermissionRuleCategoryID, CreatedAt, UpdatedAt
    FROM auth_permission_rule
  `);

  stats.auth_permission_rule.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.auth_permission_rule.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        name: row.Name,
        description: row.Description,
        auth_permission_rule_category_id: row.AuthPermissionRuleCategoryID,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
      },
    });
    stats.auth_permission_rule.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.auth_permission_rule.migrated}/${stats.auth_permission_rule.expected} auth_permission_rule records`
  );
}

/**
 * Migrate auth_permission_group table
 */
async function migratePermissionGroups(pool: sql.ConnectionPool) {
  console.log('\nMigrating auth_permission_group...');

  const result = await pool.request().query(`
    SELECT ID, Name, Description, IsActive, CreatedAt, UpdatedAt
    FROM auth_permission_group
  `);

  stats.auth_permission_group.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.auth_permission_group.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        name: row.Name,
        description: row.Description,
        is_active: row.IsActive,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
      },
    });
    stats.auth_permission_group.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.auth_permission_group.migrated}/${stats.auth_permission_group.expected} auth_permission_group records`
  );
}

/**
 * Migrate auth_permission_group_rule junction table
 */
async function migratePermissionGroupRules(pool: sql.ConnectionPool) {
  console.log('\nMigrating auth_permission_group_rule...');

  const result = await pool.request().query(`
    SELECT ID, AuthPermissionGroupID, AuthPermissionRuleID, CreatedAt
    FROM auth_permission_group_rule
  `);

  for (const row of result.recordset) {
    await prisma.auth_permission_group_rule.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        auth_permission_group_id: row.AuthPermissionGroupID,
        auth_permission_rule_id: row.AuthPermissionRuleID,
        created_at: row.CreatedAt,
      },
    });
  }

  console.log(
    `✓ Migrated ${result.recordset.length} auth_permission_group_rule records`
  );
}

/**
 * Migrate quest_company table
 */
async function migrateQuestCompanies(pool: sql.ConnectionPool) {
  console.log('\nMigrating quest_company...');

  const result = await pool.request().query(`
    SELECT
      ID, EpicorCustID, DisplayName, CanAccessInvoices,
      InvoicingEmail, OrderAckEmail, ReceivesSOEmails, IsActive,
      CreatedAt, UpdatedAt
    FROM quest_company
  `);

  stats.quest_company.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.quest_company.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        epicor_cust_id: row.EpicorCustID,
        display_name: row.DisplayName,
        can_access_invoices: row.CanAccessInvoices,
        invoicing_email: row.InvoicingEmail,
        order_ack_email: row.OrderAckEmail,
        receives_so_emails: row.ReceivesSOEmails,
        is_active: row.IsActive,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
      },
    });
    stats.quest_company.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.quest_company.migrated}/${stats.quest_company.expected} quest_company records`
  );
}

/**
 * Migrate quest_user table
 */
async function migrateQuestUsers(pool: sql.ConnectionPool) {
  console.log('\nMigrating quest_user...');

  const result = await pool.request().query(`
    SELECT
      ID, AuthUserID, IsSubUser, APIToken, APITokenExpiresAt,
      CreatedAt, UpdatedAt
    FROM quest_user
  `);

  stats.quest_user.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.quest_user.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        auth_user_id: row.AuthUserID,
        is_sub_user: row.IsSubUser,
        api_token: row.APIToken,
        api_token_expires_at: row.APITokenExpiresAt,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
      },
    });
    stats.quest_user.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.quest_user.migrated}/${stats.quest_user.expected} quest_user records`
  );
}

/**
 * Migrate quest_user_company junction table
 */
async function migrateQuestUserCompanies(pool: sql.ConnectionPool) {
  console.log('\nMigrating quest_user_company...');

  const result = await pool.request().query(`
    SELECT ID, QuestUserID, QuestCompanyID, IsActive, CreatedAt
    FROM quest_user_company
  `);

  stats.quest_user_company.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.quest_user_company.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        quest_user_id: row.QuestUserID,
        quest_company_id: row.QuestCompanyID,
        is_active_company: row.IsActive,
        created_at: row.CreatedAt,
      },
    });
    stats.quest_user_company.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.quest_user_company.migrated}/${stats.quest_user_company.expected} quest_user_company records`
  );
}

/**
 * Migrate quest_plant table
 */
async function migrateQuestPlants(pool: sql.ConnectionPool) {
  console.log('\nMigrating quest_plant...');

  const result = await pool.request().query(`
    SELECT ID, Name, Code, IsActive, CreatedAt, UpdatedAt
    FROM quest_plant
  `);

  stats.quest_plant.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.quest_plant.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        name: row.Name,
        code: row.Code,
        is_active: row.IsActive,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
      },
    });
    stats.quest_plant.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.quest_plant.migrated}/${stats.quest_plant.expected} quest_plant records`
  );
}

/**
 * Migrate quest_inventory_type table
 */
async function migrateQuestInventoryTypes(pool: sql.ConnectionPool) {
  console.log('\nMigrating quest_inventory_type...');

  const result = await pool.request().query(`
    SELECT ID, Name, Code, Description, IsActive, SortOrder, CreatedAt, UpdatedAt
    FROM quest_inventory_type
  `);

  stats.quest_inventory_type.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.quest_inventory_type.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        name: row.Name,
        code: row.Code,
        description: row.Description,
        is_active: row.IsActive,
        sort_order: row.SortOrder || 0,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
      },
    });
    stats.quest_inventory_type.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.quest_inventory_type.migrated}/${stats.quest_inventory_type.expected} quest_inventory_type records`
  );
}

/**
 * Migrate quest_email_event table
 */
async function migrateQuestEmailEvents(pool: sql.ConnectionPool) {
  console.log('\nMigrating quest_email_event...');

  const result = await pool.request().query(`
    SELECT ID, Name, Description, CreatedAt, UpdatedAt
    FROM quest_email_event
  `);

  stats.quest_email_event.expected = result.recordset.length;

  for (const row of result.recordset) {
    await prisma.quest_email_event.upsert({
      where: { id: row.ID },
      update: {},
      create: {
        id: row.ID,
        name: row.Name,
        description: row.Description,
        created_at: row.CreatedAt,
        updated_at: row.UpdatedAt,
      },
    });
    stats.quest_email_event.migrated++;
  }

  console.log(
    `✓ Migrated ${stats.quest_email_event.migrated}/${stats.quest_email_event.expected} quest_email_event records`
  );
}

/**
 * Main migration function
 */
async function main() {
  console.log('=== Vorteq Quest Portal Data Migration ===\n');
  console.log('Source: SQL Server (VorteqPortal)');
  console.log('Target: PostgreSQL (via Prisma)\n');

  try {
    const pool = await connectToLegacy();

    // Migrate auth domain
    await migrateAuthDomains(pool);
    await migrateAuthUserTypes(pool);
    await migrateAuthUsers(pool);
    await migratePermissionRuleCategories(pool);
    await migratePermissionRules(pool);
    await migratePermissionGroups(pool);
    await migratePermissionGroupRules(pool);

    // Migrate quest domain
    await migrateQuestCompanies(pool);
    await migrateQuestUsers(pool);
    await migrateQuestUserCompanies(pool);
    await migrateQuestPlants(pool);
    await migrateQuestInventoryTypes(pool);
    await migrateQuestEmailEvents(pool);

    // Close connections
    await pool.close();

    // Print summary
    console.log('\n=== Migration Summary ===\n');
    for (const [table, { expected, migrated }] of Object.entries(stats)) {
      if (expected > 0) {
        const status = expected === migrated ? '✓' : '⚠';
        console.log(`${status} ${table}: ${migrated}/${expected}`);
      }
    }

    console.log('\n✓ Migration completed successfully!');
  } catch (error) {
    console.error('\n✗ Migration failed:', error);
    process.exit(1);
  } finally {
    await prisma.$disconnect();
  }
}

// Run migration
main();