xpeditis2.0/apps/backend/restore-document-references.js

/**
 * Script to restore document references in database from MinIO files
 *
 * Scans MinIO for existing files and creates/updates database references
 */

const { S3Client, ListObjectsV2Command, HeadObjectCommand } = require('@aws-sdk/client-s3');
const { Client } = require('pg');
const { v4: uuidv4 } = require('uuid');
require('dotenv').config();

const MINIO_ENDPOINT = process.env.AWS_S3_ENDPOINT || 'http://localhost:9000';
const BUCKET_NAME = 'xpeditis-documents';

// Initialize MinIO client
const s3Client = new S3Client({
  region: 'us-east-1',
  endpoint: MINIO_ENDPOINT,
  credentials: {
    accessKeyId: process.env.AWS_ACCESS_KEY_ID || 'minioadmin',
    secretAccessKey: process.env.AWS_SECRET_ACCESS_KEY || 'minioadmin',
  },
  forcePathStyle: true,
});

async function restoreDocumentReferences() {
  const pgClient = new Client({
    host: process.env.DATABASE_HOST || 'localhost',
    port: process.env.DATABASE_PORT || 5432,
    user: process.env.DATABASE_USER || 'xpeditis',
    password: process.env.DATABASE_PASSWORD || 'xpeditis_dev_password',
    database: process.env.DATABASE_NAME || 'xpeditis_dev',
  });

  try {
    await pgClient.connect();
    console.log('✅ Connected to database\n');

    // Get all MinIO files
    console.log('📋 Listing files in MinIO...');
    let allFiles = [];
    let continuationToken = null;

    do {
      const command = new ListObjectsV2Command({
        Bucket: BUCKET_NAME,
        ContinuationToken: continuationToken,
      });

      const response = await s3Client.send(command);

      if (response.Contents) {
        allFiles = allFiles.concat(response.Contents);
      }

      continuationToken = response.NextContinuationToken;
    } while (continuationToken);

    console.log(`   Found ${allFiles.length} files in MinIO\n`);

    // Group files by booking ID
    const filesByBooking = {};
    allFiles.forEach(file => {
      const parts = file.Key.split('/');
      if (parts.length >= 3 && parts[0] === 'csv-bookings') {
        const bookingId = parts[1];
        const documentId = parts[2].split('-')[0]; // Extract UUID from filename
        const fileName = parts[2].substring(37); // Remove UUID prefix (36 chars + dash)

        if (!filesByBooking[bookingId]) {
          filesByBooking[bookingId] = [];
        }

        filesByBooking[bookingId].push({
          key: file.Key,
          documentId: documentId,
          fileName: fileName,
          size: file.Size,
          lastModified: file.LastModified,
        });
      }
    });

    console.log(`📦 Found files for ${Object.keys(filesByBooking).length} bookings\n`);

    let updatedCount = 0;
    let createdDocsCount = 0;

    for (const [bookingId, files] of Object.entries(filesByBooking)) {
      // Check if booking exists
      const bookingResult = await pgClient.query(
        'SELECT id, documents FROM csv_bookings WHERE id = $1',
        [bookingId]
      );

      if (bookingResult.rows.length === 0) {
        console.log(`⚠️  Booking not found: ${bookingId.substring(0, 8)}... (skipping)`);
        continue;
      }

      const booking = bookingResult.rows[0];
      const existingDocs = booking.documents || [];

      console.log(`\n📦 Booking: ${bookingId.substring(0, 8)}...`);
      console.log(`   Existing documents in DB: ${existingDocs.length}`);
      console.log(`   Files in MinIO: ${files.length}`);

      // Create document references for files
      const newDocuments = files.map(file => {
        // Determine MIME type from file extension
        const ext = file.fileName.split('.').pop().toLowerCase();
        const mimeTypeMap = {
          pdf: 'application/pdf',
          png: 'image/png',
          jpg: 'image/jpeg',
          jpeg: 'image/jpeg',
          txt: 'text/plain',
        };
        const mimeType = mimeTypeMap[ext] || 'application/octet-stream';

        // Determine document type
        let docType = 'OTHER';
        if (file.fileName.toLowerCase().includes('bill-of-lading') || file.fileName.toLowerCase().includes('bol')) {
          docType = 'BILL_OF_LADING';
        } else if (file.fileName.toLowerCase().includes('packing-list')) {
          docType = 'PACKING_LIST';
        } else if (file.fileName.toLowerCase().includes('commercial-invoice') || file.fileName.toLowerCase().includes('invoice')) {
          docType = 'COMMERCIAL_INVOICE';
        }

        const doc = {
          id: file.documentId,
          type: docType,
          fileName: file.fileName,
          filePath: `${MINIO_ENDPOINT}/${BUCKET_NAME}/${file.key}`,
          mimeType: mimeType,
          size: file.size,
          uploadedAt: file.lastModified.toISOString(),
        };

        console.log(`   ✅ ${file.fileName} (${(file.size / 1024).toFixed(2)} KB)`);
        return doc;
      });

      // Update the booking with new document references
      await pgClient.query(
        'UPDATE csv_bookings SET documents = $1 WHERE id = $2',
        [JSON.stringify(newDocuments), bookingId]
      );

      updatedCount++;
      createdDocsCount += newDocuments.length;
    }

    console.log(`\n📊 Summary:`);
    console.log(`   Bookings updated: ${updatedCount}`);
    console.log(`   Document references created: ${createdDocsCount}`);
    console.log(`\n✅ Document references restored`);
  } catch (error) {
    console.error('❌ Error:', error);
    throw error;
  } finally {
    await pgClient.end();
    console.log('\n👋 Disconnected from database');
  }
}

restoreDocumentReferences()
  .then(() => {
    console.log('\n✅ Script completed successfully');
    process.exit(0);
  })
  .catch((error) => {
    console.error('\n❌ Script failed:', error);
    process.exit(1);
  });