migrate-pax8-tables.js

Go to the documentation of this file.

/**
 * Pax8 Integration Database Migration
 * Creates missing tables for Pax8 Microsoft 365 integration
 * 
 * Tables created:
 * - pax8_customers: Maps local customers to Pax8 companies
 * - pax8_products: Caches Pax8 product catalog
 * 
 * Also adds missing columns to pax8_config
 */
 
const { Pool } = require('pg');
const fs = require('fs');
 
const envContent = fs.readFileSync('.env', 'utf8');
const envVars = {};
envContent.split('\n').forEach(line => {
  const match = line.match(/^([^=]+)=(.*)$/);
  if (match) envVars[match[1]] = match[2];
});
 
const dbUrl = envVars.DATABASE_URL.replace('sslmode=require', 'sslmode=no-verify');
const pool = new Pool({ connectionString: dbUrl, ssl: { rejectUnauthorized: false } });
 
async function migrate() {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    
    console.log('Starting Pax8 database migration...\n');
    
    // 1. Create pax8_customers table
    console.log('Creating pax8_customers table...');
    await client.query(`
      CREATE TABLE IF NOT EXISTS pax8_customers (
        id SERIAL PRIMARY KEY,
        tenant_id UUID NOT NULL REFERENCES tenants(tenant_id) ON DELETE CASCADE,
        customer_id INTEGER NOT NULL REFERENCES customers(customer_id) ON DELETE CASCADE,
        pax8_company_id TEXT NOT NULL,
        pax8_company_name TEXT,
        last_synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        sync_status TEXT DEFAULT 'pending' CHECK (sync_status IN ('pending', 'synced', 'failed')),
        sync_error TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(tenant_id, customer_id),
        UNIQUE(tenant_id, pax8_company_id)
      )
    `);
    console.log('✅ pax8_customers table created');
    
    // Create index for faster lookups
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_pax8_customers_tenant 
      ON pax8_customers(tenant_id)
    `);
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_pax8_customers_customer 
      ON pax8_customers(customer_id)
    `);
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_pax8_customers_pax8_company 
      ON pax8_customers(pax8_company_id)
    `);
    console.log('✅ Indexes created for pax8_customers');
    
    // 2. Create pax8_products table
    console.log('\nCreating pax8_products table...');
    await client.query(`
      CREATE TABLE IF NOT EXISTS pax8_products (
        id SERIAL PRIMARY KEY,
        pax8_product_id TEXT UNIQUE NOT NULL,
        name TEXT NOT NULL,
        vendor TEXT,
        category TEXT,
        description TEXT,
        sku TEXT,
        unit_price DECIMAL(10, 2),
        term TEXT,
        pricing JSONB,
        metadata JSONB,
        is_active BOOLEAN DEFAULT true,
        last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
    console.log('✅ pax8_products table created');
    
    // Create indexes for product searches
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_pax8_products_vendor 
      ON pax8_products(vendor)
    `);
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_pax8_products_category 
      ON pax8_products(category)
    `);
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_pax8_products_active 
      ON pax8_products(is_active)
    `);
    console.log('✅ Indexes created for pax8_products');
    
    // 3. Add missing columns to pax8_config if they don't exist
    console.log('\nChecking pax8_config table for missing columns...');
    
    // Check if pax8_company_id exists
    const companyIdCheck = await client.query(`
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name = 'pax8_config' AND column_name = 'pax8_company_id'
    `);
    
    if (companyIdCheck.rows.length === 0) {
      await client.query(`
        ALTER TABLE pax8_config 
        ADD COLUMN pax8_company_id TEXT
      `);
      console.log('✅ Added pax8_company_id column to pax8_config');
    } else {
      console.log('✓ pax8_company_id column already exists');
    }
    
    // Check if last_sync_status exists
    const syncStatusCheck = await client.query(`
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name = 'pax8_config' AND column_name = 'last_sync_status'
    `);
    
    if (syncStatusCheck.rows.length === 0) {
      await client.query(`
        ALTER TABLE pax8_config 
        ADD COLUMN last_sync_status TEXT DEFAULT 'not_configured'
      `);
      console.log('✅ Added last_sync_status column to pax8_config');
    } else {
      console.log('✓ last_sync_status column already exists');
    }
    
    // Check if sync_error exists
    const syncErrorCheck = await client.query(`
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name = 'pax8_config' AND column_name = 'sync_error'
    `);
    
    if (syncErrorCheck.rows.length === 0) {
      await client.query(`
        ALTER TABLE pax8_config 
        ADD COLUMN sync_error TEXT
      `);
      console.log('✅ Added sync_error column to pax8_config');
    } else {
      console.log('✓ sync_error column already exists');
    }
    
    await client.query('COMMIT');
    
    console.log('\n✅ Migration completed successfully!\n');
    
    // Verify tables exist
    const verification = await client.query(`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public' 
        AND (table_name LIKE 'pax8%' OR table_name LIKE '%office365%')
      ORDER BY table_name
    `);
    
    console.log('Pax8/Office365 tables:');
    verification.rows.forEach(row => {
      console.log('  ✓', row.table_name);
    });
    
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('\n❌ Migration failed:', err.message);
    throw err;
  } finally {
    client.release();
    await pool.end();
  }
}
 
// Run migration
migrate()
  .then(() => {
    console.log('\n✅ Database ready forPax8 integration');
    process.exit(0);
  })
  .catch(err => {
    console.error('\n❌ Migration error:', err);
    process.exit(1);
  });