const request = require('supertest');
const app = require('../server');
const database = require('../models/database');
const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');

describe('Integration Tests - Complete Workflows', () => {
  let testDbPath;
  
  beforeAll(async () => {
    // Set up test database
    testDbPath = path.join(__dirname, '..', 'test_integration.db');
    if (fs.existsSync(testDbPath)) {
      fs.unlinkSync(testDbPath);
    }
    database.dbPath = testDbPath;
    await database.initialize();
  });

  afterAll(async () => {
    database.close();
    if (fs.existsSync(testDbPath)) {
      fs.unlinkSync(testDbPath);
    }
  });

  beforeEach(async () => {
    // Clean up database before each test
    const db = database.getDatabase();
    db.exec('DELETE FROM inventory_history');
    db.exec('DELETE FROM inventory');
    db.exec('DELETE FROM products');
    db.exec('DELETE FROM import_sessions');
  });

  describe('End-to-End Workflow: Import → Generate → Scan → Export', () => {
    test('should complete full workflow successfully', async () => {
      // Step 1: Import Excel file with products
      const testData = [
        ['Product Code', 'Description', 'Quantity', 'Category'],
        ['ABC123', 'Test Product 1', 100, 'Electronics'],
        ['DEF456', 'Test Product 2', 50, 'Books'],
        ['GHI789', 'Test Product 3', 75, 'Clothing']
      ];

      const worksheet = XLSX.utils.aoa_to_sheet(testData);
      const workbook = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(workbook, worksheet, 'Products');
      const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });

      // Import products
      const importResponse = await request(app)
        .post('/api/products/import/excel')
        .attach('file', buffer, 'test-products.xlsx')
        .expect(200);

      expect(importResponse.body.success).toBe(true);
      expect(importResponse.body.data.importResults.imported).toBe(3);

      // Verify products were created
      const productsResponse = await request(app)
        .get('/api/products')
        .expect(200);

      expect(productsResponse.body.data).toHaveLength(3);
      const productIds = productsResponse.body.data.map(p => p.id);

      // Step 2: Generate barcodes for products (using layout generation as proxy)
      const generateResponse = await request(app)
        .post('/api/codes/layouts/preview')
        .send({
          productIds: productIds.slice(0, 3), // Preview only takes first 5
          options: { format: 'code128', includeQR: true }
        })
        .expect(200);

      expect(generateResponse.body.success).toBe(true);
      expect(generateResponse.body.data.sampleProducts).toHaveLength(3);

      // Step 3: Simulate scanning and inventory updates
      for (let i = 0; i < productIds.length; i++) {
        const productId = productIds[i];
        const newLevel = 80 + (i * 10); // Different levels for each product

        const scanResponse = await request(app)
          .put(`/api/inventory/product/${productId}/level`)
          .send({
            newLevel: newLevel,
            changeReason: 'Scanned inventory update',
            updatedBy: 'scanner-user'
          })
          .expect(200);

        expect(scanResponse.body.success).toBe(true);
        expect(scanResponse.body.data.current_level).toBe(newLevel);
      }

      // Verify inventory history was recorded
      const historyResponse = await request(app)
        .get(`/api/inventory/product/${productIds[0]}/history`)
        .expect(200);

      expect(historyResponse.body.data).toHaveLength(2); // Initial + update

      // Step 4: Export updated inventory data
      const exportResponse = await request(app)
        .get('/api/codes/export/excel')
        .expect(200);

      expect(exportResponse.headers['content-type']).toContain('application/vnd.openxmlformats');
      expect(exportResponse.headers['content-disposition']).toContain('attachment');

      // Verify export contains updated data
      const exportedWorkbook = XLSX.read(exportResponse.body, { type: 'buffer' });
      const exportedSheet = exportedWorkbook.Sheets[exportedWorkbook.SheetNames[0]];
      const exportedData = XLSX.utils.sheet_to_json(exportedSheet);

      expect(exportedData).toHaveLength(3);
      expect(exportedData[0]['Current Level']).toBe(80);
      expect(exportedData[1]['Current Level']).toBe(90);
      expect(exportedData[2]['Current Level']).toBe(100);
    });

    test('should handle workflow with validation errors', async () => {
      // Import data with some invalid entries
      const testData = [
        ['Product Code', 'Description', 'Quantity', 'Category'],
        ['ABC123', 'Valid Product', 100, 'Electronics'],
        ['', 'Invalid Product - No Code', 50, 'Books'], // Missing product code
        ['DEF@456', 'Invalid Product - Bad Code', -10, 'Clothing'] // Invalid code and negative quantity
      ];

      const worksheet = XLSX.utils.aoa_to_sheet(testData);
      const workbook = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(workbook, worksheet, 'Products');
      const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });

      const importResponse = await request(app)
        .post('/api/products/import/excel')
        .attach('file', buffer, 'test-invalid.xlsx')
        .expect(200);

      expect(importResponse.body.success).toBe(true);
      expect(importResponse.body.data.importResults.imported).toBe(1); // Only valid product
      expect(importResponse.body.data.validationResults.statistics.invalidProducts).toBe(2); // Two invalid products

      // Verify only valid product was imported
      const productsResponse = await request(app)
        .get('/api/products')
        .expect(200);

      expect(productsResponse.body.data).toHaveLength(1);
      expect(productsResponse.body.data[0].product_code).toBe('ABC123');
    });

    test('should handle concurrent inventory updates', async () => {
      // First, create a product
      const createResponse = await request(app)
        .post('/api/products')
        .send({
          name: 'CONCURRENT123',
          description: 'Concurrent Test Product',
          category: 'Test'
        })
        .expect(201);

      const productId = createResponse.body.data.id;

      // Create initial inventory
      await request(app)
        .post(`/api/inventory/product/${productId}`)
        .send({
          initialLevel: 100,
          minimumLevel: 10,
          maximumLevel: 200,
          updatedBy: 'test-user'
        })
        .expect(201);

      // Simulate concurrent updates
      const updatePromises = [];
      for (let i = 0; i < 5; i++) {
        const promise = request(app)
          .put(`/api/inventory/product/${productId}/level`)
          .send({
            newLevel: 100 + i,
            changeReason: `Concurrent update ${i}`,
            updatedBy: `user-${i}`
          });
        updatePromises.push(promise);
      }

      const results = await Promise.allSettled(updatePromises);
      
      // At least one should succeed
      const successfulUpdates = results.filter(r => r.status === 'fulfilled' && r.value.status === 200);
      expect(successfulUpdates.length).toBeGreaterThan(0);

      // Some might fail due to concurrent update detection
      const conflictUpdates = results.filter(r => r.status === 'fulfilled' && r.value.status === 409);
      
      // Total successful + conflicts should equal total attempts
      expect(successfulUpdates.length + conflictUpdates.length).toBe(5);

      // Verify final state is consistent
      const finalResponse = await request(app)
        .get(`/api/inventory/product/${productId}`)
        .expect(200);

      expect(finalResponse.body.data.current_level).toBeGreaterThanOrEqual(100);
      expect(finalResponse.body.data.current_level).toBeLessThanOrEqual(104);
    });
  });

  describe('Bulk Operations Workflow', () => {
    test('should handle bulk import and bulk updates', async () => {
      // Create large dataset for bulk operations
      const testData = [['Product Code', 'Description', 'Quantity', 'Category']];
      for (let i = 1; i <= 50; i++) {
        testData.push([
          `BULK${i.toString().padStart(3, '0')}`,
          `Bulk Product ${i}`,
          Math.floor(Math.random() * 100) + 10,
          i % 2 === 0 ? 'Electronics' : 'Books'
        ]);
      }

      const worksheet = XLSX.utils.aoa_to_sheet(testData);
      const workbook = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(workbook, worksheet, 'BulkProducts');
      const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });

      // Bulk import
      const importStart = Date.now();
      const importResponse = await request(app)
        .post('/api/products/import/excel')
        .attach('file', buffer, 'bulk-products.xlsx')
        .expect(200);
      const importDuration = Date.now() - importStart;

      expect(importResponse.body.success).toBe(true);
      expect(importResponse.body.data.importResults.imported).toBe(50);
      expect(importDuration).toBeLessThan(5000); // Should complete within 5 seconds

      // Get all product IDs
      const productsResponse = await request(app)
        .get('/api/products')
        .expect(200);

      const productIds = productsResponse.body.data.map(p => p.id);

      // Bulk inventory updates
      const bulkUpdates = productIds.map((id, index) => ({
        productId: id,
        newLevel: 50 + index,
        changeReason: 'Bulk inventory update',
        updatedBy: 'bulk-user'
      }));

      const bulkUpdateStart = Date.now();
      const bulkUpdateResponse = await request(app)
        .post('/api/inventory/bulk-update')
        .send({ updates: bulkUpdates })
        .expect(200);
      const bulkUpdateDuration = Date.now() - bulkUpdateStart;

      expect(bulkUpdateResponse.body.success).toBe(true);
      expect(bulkUpdateResponse.body.count).toBe(50);
      expect(bulkUpdateDuration).toBeLessThan(3000); // Should complete within 3 seconds

      // Verify bulk export performance
      const exportStart = Date.now();
      const exportResponse = await request(app)
        .get('/api/codes/export/excel')
        .expect(200);
      const exportDuration = Date.now() - exportStart;

      expect(exportDuration).toBeLessThan(2000); // Should complete within 2 seconds
      expect(exportResponse.headers['content-type']).toContain('application/vnd.openxmlformats');
    });
  });

  describe('Error Recovery Workflow', () => {
    test('should recover from database connection issues', async () => {
      // Create a product first
      const createResponse = await request(app)
        .post('/api/products')
        .send({
          name: 'RECOVERY123',
          description: 'Recovery Test Product',
          category: 'Test'
        })
        .expect(201);

      const productId = createResponse.body.data.id;

      // Simulate database connection issue by closing and reopening
      database.close();
      
      // This should fail initially
      const failedResponse = await request(app)
        .get(`/api/products/${productId}`)
        .expect(500);

      expect(failedResponse.body.success).toBe(false);

      // Reinitialize database
      await database.initialize();

      // This should work after recovery
      const recoveredResponse = await request(app)
        .get(`/api/products/${productId}`)
        .expect(200);

      expect(recoveredResponse.body.success).toBe(true);
      expect(recoveredResponse.body.data.name).toBe('RECOVERY123');
    });
  });

  describe('Data Consistency Workflow', () => {
    test('should maintain data consistency across operations', async () => {
      // Create products with inventory
      const products = [];
      for (let i = 1; i <= 10; i++) {
        const createResponse = await request(app)
          .post('/api/products')
          .send({
            name: `CONSISTENCY${i}`,
            description: `Consistency Test Product ${i}`,
            category: 'Test'
          })
          .expect(201);

        products.push(createResponse.body.data);

        // Create inventory for each product
        await request(app)
          .post(`/api/inventory/product/${createResponse.body.data.id}`)
          .send({
            initialLevel: 100,
            minimumLevel: 10,
            maximumLevel: 200,
            updatedBy: 'consistency-test'
          })
          .expect(201);
      }

      // Perform various operations and verify consistency
      const operations = [];
      
      // Update inventory levels
      for (let i = 0; i < products.length; i++) {
        operations.push(
          request(app)
            .put(`/api/inventory/product/${products[i].id}/level`)
            .send({
              newLevel: 80 + i,
              changeReason: 'Consistency test update',
              updatedBy: 'consistency-user'
            })
        );
      }

      // Execute all operations
      const results = await Promise.all(operations);
      results.forEach(result => {
        expect(result.status).toBe(200);
        expect(result.body.success).toBe(true);
      });

      // Verify data consistency
      const inventoryResponse = await request(app)
        .get('/api/inventory')
        .expect(200);

      expect(inventoryResponse.body.data).toHaveLength(10);
      
      // Check that all inventory levels are correct
      inventoryResponse.body.data.forEach((item, index) => {
        expect(item.current_level).toBe(80 + index);
      });

      // Verify history records exist for all updates
      for (let i = 0; i < products.length; i++) {
        const historyResponse = await request(app)
          .get(`/api/inventory/product/${products[i].id}/history`)
          .expect(200);

        expect(historyResponse.body.data).toHaveLength(2); // Initial + update
      }
    });
  });
});