const XLSX = require('xlsx');
const fs = require('fs').promises;
const path = require('path');

// Mock dependencies before importing the service
jest.mock('../models/database', () => ({
  getDatabase: jest.fn()
}));

jest.mock('fs', () => ({
  promises: {
    mkdir: jest.fn(),
    writeFile: jest.fn(),
    readdir: jest.fn(),
    stat: jest.fn(),
    unlink: jest.fn()
  }
}));

const ExcelExportService = require('../services/ExcelExportService');
const database = require('../models/database');

describe('ExcelExportService', () => {
  let exportService;
  let mockDb;

  beforeEach(() => {
    exportService = new ExcelExportService();
    
    // Mock database instance
    mockDb = {
      prepare: jest.fn(),
      exec: jest.fn()
    };
    
    database.getDatabase = jest.fn().mockReturnValue(mockDb);
    
    // Clear all mocks
    jest.clearAllMocks();
  });

  describe('constructor', () => {
    it('should initialize with correct export directory', () => {
      expect(exportService.exportDirectory).toContain(path.join('data', 'exports'));
    });

    it('should ensure export directory exists', async () => {
      await exportService.ensureExportDirectory();
      expect(fs.mkdir).toHaveBeenCalledWith(
        expect.stringContaining(path.join('data', 'exports')),
        { recursive: true }
      );
    });
  });

  describe('getInventoryData', () => {
    beforeEach(() => {
      const mockStmt = {
        all: jest.fn().mockReturnValue([
          {
            product_code: 'TEST001',
            description: 'Test Product 1',
            category: 'Electronics',
            unit_of_measure: 'pcs',
            current_level: 100,
            minimum_level: 10,
            maximum_level: 500,
            last_updated: '2024-01-15T10:30:00Z',
            updated_by: 'user1',
            stock_status: 'Normal'
          },
          {
            product_code: 'TEST002',
            description: 'Test Product 2',
            category: 'Tools',
            unit_of_measure: 'pcs',
            current_level: 5,
            minimum_level: 10,
            maximum_level: 100,
            last_updated: '2024-01-14T15:45:00Z',
            updated_by: 'user2',
            stock_status: 'Low Stock'
          }
        ])
      };
      mockDb.prepare.mockReturnValue(mockStmt);
    });

    it('should retrieve inventory data without filters', async () => {
      const data = await exportService.getInventoryData();
      
      expect(data).toHaveLength(2);
      expect(data[0].product_code).toBe('TEST001');
      expect(data[1].stock_status).toBe('Low Stock');
    });

    it('should apply category filter', async () => {
      await exportService.getInventoryData({ category: 'Electronics' });
      
      const query = mockDb.prepare.mock.calls[0][0];
      expect(query).toContain('WHERE p.category = ?');
    });

    it('should apply stock status filter', async () => {
      await exportService.getInventoryData({ stockStatus: 'low' });
      
      const query = mockDb.prepare.mock.calls[0][0];
      expect(query).toContain('i.current_level <= i.minimum_level');
    });

    it('should apply updated since filter', async () => {
      const since = '2024-01-01T00:00:00Z';
      await exportService.getInventoryData({ updatedSince: since });
      
      const query = mockDb.prepare.mock.calls[0][0];
      expect(query).toContain('i.last_updated >= ?');
    });

    it('should apply product codes filter', async () => {
      await exportService.getInventoryData({ productCodes: ['TEST001', 'TEST002'] });
      
      const query = mockDb.prepare.mock.calls[0][0];
      expect(query).toContain('p.product_code IN (?,?)');
    });
  });

  describe('createNewExcelFile', () => {
    const mockInventoryData = [
      {
        product_code: 'TEST001',
        description: 'Test Product 1',
        category: 'Electronics',
        unit_of_measure: 'pcs',
        current_level: 100,
        minimum_level: 10,
        maximum_level: 500,
        last_updated: '2024-01-15T10:30:00Z',
        updated_by: 'user1',
        stock_status: 'Normal'
      }
    ];

    it('should create new Excel file with inventory sheet', async () => {
      const result = await exportService.createNewExcelFile(mockInventoryData);
      
      expect(result.workbook).toBeDefined();
      expect(result.metadata.sheets).toContain('Inventory');
      expect(result.metadata.sheets).toContain('Summary');
      expect(result.metadata.recordCount).toBe(1);
    });

    it('should include history sheet when requested', async () => {
      // Mock history data
      const mockHistoryStmt = {
        all: jest.fn().mockReturnValue([
          {
            product_code: 'TEST001',
            description: 'Test Product 1',
            old_level: 90,
            new_level: 100,
            change_reason: 'Stock adjustment',
            updated_by: 'user1',
            updated_at: '2024-01-15T10:30:00Z'
          }
        ])
      };
      mockDb.prepare.mockReturnValue(mockHistoryStmt);

      const result = await exportService.createNewExcelFile(mockInventoryData, { includeHistory: true });
      
      expect(result.metadata.sheets).toContain('History');
      expect(result.metadata.includeHistory).toBe(true);
    });

    it('should exclude audit info when requested', async () => {
      const result = await exportService.createNewExcelFile(mockInventoryData, { includeAuditInfo: false });
      
      expect(result.metadata.includeAuditInfo).toBe(false);
    });
  });

  describe('createInventorySheet', () => {
    const mockInventoryData = [
      {
        product_code: 'TEST001',
        description: 'Test Product 1',
        category: 'Electronics',
        unit_of_measure: 'pcs',
        current_level: 100,
        minimum_level: 10,
        maximum_level: 500,
        last_updated: '2024-01-15T10:30:00Z',
        updated_by: 'user1',
        stock_status: 'Normal'
      }
    ];

    it('should create worksheet with correct headers', () => {
      const worksheet = exportService.createInventorySheet(mockInventoryData, true);
      
      // Convert worksheet to array to check content
      const data = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
      const headers = data[0];
      
      expect(headers).toContain('Product Code');
      expect(headers).toContain('Description');
      expect(headers).toContain('Current Level');
      expect(headers).toContain('Last Updated');
      expect(headers).toContain('Updated By');
    });

    it('should exclude audit info when requested', () => {
      const worksheet = exportService.createInventorySheet(mockInventoryData, false);
      
      const data = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
      const headers = data[0];
      
      expect(headers).not.toContain('Last Updated');
      expect(headers).not.toContain('Updated By');
    });

    it('should include data rows with correct values', () => {
      const worksheet = exportService.createInventorySheet(mockInventoryData, true);
      
      const data = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
      const dataRow = data[1];
      
      expect(dataRow[0]).toBe('TEST001'); // Product Code
      expect(dataRow[1]).toBe('Test Product 1'); // Description
      expect(dataRow[4]).toBe(100); // Current Level
    });

    it('should set column widths', () => {
      const worksheet = exportService.createInventorySheet(mockInventoryData, true);
      
      expect(worksheet['!cols']).toBeDefined();
      expect(worksheet['!cols']).toHaveLength(10); // 8 base + 2 audit columns
    });
  });

  describe('updateOriginalExcelFile', () => {
    let originalFileBuffer;

    beforeEach(() => {
      // Create a mock Excel file buffer
      const mockWorkbook = XLSX.utils.book_new();
      const mockData = [
        ['Product Code', 'Description', 'Quantity'],
        ['TEST001', 'Old Description', 50],
        ['TEST002', 'Another Product', 25]
      ];
      const mockWorksheet = XLSX.utils.aoa_to_sheet(mockData);
      XLSX.utils.book_append_sheet(mockWorkbook, mockWorksheet, 'Sheet1');
      originalFileBuffer = XLSX.write(mockWorkbook, { type: 'buffer', bookType: 'xlsx' });
    });

    it('should update existing products in original file', async () => {
      const inventoryData = [
        {
          product_code: 'TEST001',
          description: 'Updated Description',
          current_level: 75,
          category: 'Electronics',
          last_updated: '2024-01-15T10:30:00Z'
        }
      ];

      const result = await exportService.updateOriginalExcelFile(originalFileBuffer, inventoryData);
      
      expect(result.workbook).toBeDefined();
      expect(result.metadata.updatedRows).toBe(1);
      expect(result.metadata.preservedFormatting).toBe(true);
    });

    it('should add new products when includeNewProducts is true', async () => {
      const inventoryData = [
        {
          product_code: 'TEST003',
          description: 'New Product',
          current_level: 30,
          category: 'Tools'
        }
      ];

      const result = await exportService.updateOriginalExcelFile(
        originalFileBuffer, 
        inventoryData, 
        { includeNewProducts: true }
      );
      
      expect(result.metadata.addedRows).toBe(1);
    });

    it('should handle empty original file', async () => {
      const emptyWorkbook = XLSX.utils.book_new();
      const emptyWorksheet = XLSX.utils.aoa_to_sheet([]);
      XLSX.utils.book_append_sheet(emptyWorkbook, emptyWorksheet, 'Sheet1');
      const emptyBuffer = XLSX.write(emptyWorkbook, { type: 'buffer', bookType: 'xlsx' });

      await expect(
        exportService.updateOriginalExcelFile(emptyBuffer, [])
      ).rejects.toThrow('Original Excel file appears to be empty');
    });

    it('should handle missing sheet', async () => {
      const inventoryData = [];
      
      await expect(
        exportService.updateOriginalExcelFile(originalFileBuffer, inventoryData, { sheetName: 'NonExistent' })
      ).rejects.toThrow('Sheet "NonExistent" not found');
    });
  });

  describe('detectColumnMappings', () => {
    it('should detect standard column mappings', () => {
      const headerRow = ['Product Code', 'Description', 'Quantity', 'Category'];
      const mapping = exportService.detectColumnMappings(headerRow);
      
      expect(mapping.productCode).toBe(0);
      expect(mapping.description).toBe(1);
      expect(mapping.quantity).toBe(2);
      expect(mapping.category).toBe(3);
    });

    it('should handle case-insensitive matching', () => {
      const headerRow = ['PRODUCT_CODE', 'desc', 'QTY'];
      const mapping = exportService.detectColumnMappings(headerRow);
      
      expect(mapping.productCode).toBe(0);
      expect(mapping.description).toBe(1);
      expect(mapping.quantity).toBe(2);
    });

    it('should handle partial matches', () => {
      const headerRow = ['Item Code', 'Product Name', 'Stock Level'];
      const mapping = exportService.detectColumnMappings(headerRow);
      
      expect(mapping.productCode).toBe(0);
      expect(mapping.description).toBe(1);
      expect(mapping.quantity).toBe(2);
    });

    it('should return null for unmatched columns', () => {
      const headerRow = ['Unknown1', 'Unknown2'];
      const mapping = exportService.detectColumnMappings(headerRow);
      
      expect(mapping.productCode).toBeNull();
      expect(mapping.description).toBeNull();
      expect(mapping.quantity).toBeNull();
    });
  });

  describe('exportInventoryToExcel', () => {
    beforeEach(() => {
      // Mock getInventoryData
      jest.spyOn(exportService, 'getInventoryData').mockResolvedValue([
        {
          product_code: 'TEST001',
          description: 'Test Product',
          current_level: 100,
          stock_status: 'Normal'
        }
      ]);

      // Mock createNewExcelFile
      jest.spyOn(exportService, 'createNewExcelFile').mockResolvedValue({
        workbook: XLSX.utils.book_new(),
        metadata: { recordCount: 1 }
      });

      // Mock writeExcelFile
      jest.spyOn(exportService, 'writeExcelFile').mockResolvedValue();

      // Mock createExportSession
      jest.spyOn(exportService, 'createExportSession').mockResolvedValue(123);
    });

    it('should export inventory successfully', async () => {
      const result = await exportService.exportInventoryToExcel();
      
      expect(result.success).toBe(true);
      expect(result.filename).toMatch(/inventory_export_\d{4}-\d{2}-\d{2}_\d{2}-\d{2}-\d{2}\.xlsx/);
      expect(result.recordCount).toBe(1);
      expect(result.sessionId).toBe(123);
    });

    it('should use custom filename when provided', async () => {
      const customFilename = 'custom_export.xlsx';
      const result = await exportService.exportInventoryToExcel({ filename: customFilename });
      
      expect(result.filename).toBe(customFilename);
    });

    it('should handle export errors gracefully', async () => {
      jest.spyOn(exportService, 'getInventoryData').mockRejectedValue(new Error('Database error'));
      
      const result = await exportService.exportInventoryToExcel();
      
      expect(result.success).toBe(false);
      expect(result.error).toBe('Database error');
      expect(result.filePath).toBeNull();
    });

    it('should preserve original formatting when buffer provided', async () => {
      const mockBuffer = Buffer.from('mock excel data');
      jest.spyOn(exportService, 'updateOriginalExcelFile').mockResolvedValue({
        workbook: XLSX.utils.book_new(),
        metadata: { preservedFormatting: true }
      });

      const result = await exportService.exportInventoryToExcel({
        originalFileBuffer: mockBuffer,
        preserveFormatting: true
      });
      
      expect(result.success).toBe(true);
      expect(exportService.updateOriginalExcelFile).toHaveBeenCalledWith(
        mockBuffer,
        expect.any(Array),
        expect.any(Object)
      );
    });
  });

  describe('generateExportFilename', () => {
    it('should generate filename with timestamp', () => {
      const filename = exportService.generateExportFilename();
      
      expect(filename).toMatch(/inventory_export_\d{4}-\d{2}-\d{2}_\d{2}-\d{2}-\d{2}\.xlsx/);
    });

    it('should use specified format', () => {
      const filename = exportService.generateExportFilename('csv');
      
      expect(filename.endsWith('.csv')).toBe(true);
    });
  });

  describe('createExportSession', () => {
    beforeEach(() => {
      const mockStmt = {
        run: jest.fn().mockReturnValue({ lastInsertRowid: 456 })
      };
      mockDb.prepare.mockReturnValue(mockStmt);
    });

    it('should create export session record', async () => {
      const sessionData = {
        filename: 'test_export.xlsx',
        totalRecords: 100,
        filters: { category: 'Electronics' },
        includeHistory: true
      };

      const sessionId = await exportService.createExportSession(sessionData);
      
      expect(sessionId).toBe(456);
      expect(mockDb.exec).toHaveBeenCalledWith(expect.stringContaining('CREATE TABLE IF NOT EXISTS export_sessions'));
    });

    it('should handle database errors gracefully', async () => {
      mockDb.prepare.mockImplementation(() => {
        throw new Error('Database error');
      });

      const sessionId = await exportService.createExportSession({});
      
      expect(sessionId).toBeNull();
    });
  });

  describe('getExportHistory', () => {
    beforeEach(() => {
      const mockStmt = {
        all: jest.fn().mockReturnValue([
          {
            id: 1,
            filename: 'export1.xlsx',
            total_records: 100,
            export_date: '2024-01-15T10:30:00Z'
          }
        ])
      };
      mockDb.prepare.mockReturnValue(mockStmt);
    });

    it('should retrieve export history', async () => {
      const history = await exportService.getExportHistory();
      
      expect(history).toHaveLength(1);
      expect(history[0].filename).toBe('export1.xlsx');
    });

    it('should apply limit and offset', async () => {
      await exportService.getExportHistory({ limit: 10, offset: 5 });
      
      const mockStmt = mockDb.prepare.mock.results[0].value;
      expect(mockStmt.all).toHaveBeenCalledWith(10, 5);
    });

    it('should handle database errors', async () => {
      mockDb.prepare.mockImplementation(() => {
        throw new Error('Database error');
      });

      const history = await exportService.getExportHistory();
      
      expect(history).toEqual([]);
    });
  });

  describe('cleanupOldExports', () => {
    beforeEach(() => {
      const oldDate = new Date(Date.now() - 48 * 60 * 60 * 1000); // 48 hours ago
      const newDate = new Date(Date.now() - 12 * 60 * 60 * 1000); // 12 hours ago

      fs.readdir.mockResolvedValue(['old_export.xlsx', 'new_export.xlsx']);
      fs.stat
        .mockResolvedValueOnce({ mtime: oldDate })
        .mockResolvedValueOnce({ mtime: newDate });
      fs.unlink.mockResolvedValue();
    });

    it('should delete old export files', async () => {
      const result = await exportService.cleanupOldExports(24);
      
      expect(result.success).toBe(true);
      expect(result.deletedCount).toBe(1);
      expect(fs.unlink).toHaveBeenCalledTimes(1);
    });

    it('should handle cleanup errors', async () => {
      fs.readdir.mockRejectedValue(new Error('Directory error'));
      
      const result = await exportService.cleanupOldExports();
      
      expect(result.success).toBe(false);
      expect(result.error).toBe('Directory error');
    });
  });

  describe('writeExcelFile', () => {
    it('should write Excel file to disk', async () => {
      const mockWorkbook = XLSX.utils.book_new();
      // Add a worksheet to make the workbook valid
      const mockWorksheet = XLSX.utils.aoa_to_sheet([['Test']]);
      XLSX.utils.book_append_sheet(mockWorkbook, mockWorksheet, 'Sheet1');
      
      const filePath = '/test/path/export.xlsx';
      
      await exportService.writeExcelFile(mockWorkbook, filePath, 'xlsx');
      
      expect(fs.writeFile).toHaveBeenCalledWith(filePath, expect.any(Buffer));
    });

    it('should handle write errors', async () => {
      fs.writeFile.mockRejectedValue(new Error('Write error'));
      const mockWorkbook = XLSX.utils.book_new();
      // Add a worksheet to make the workbook valid
      const mockWorksheet = XLSX.utils.aoa_to_sheet([['Test']]);
      XLSX.utils.book_append_sheet(mockWorkbook, mockWorksheet, 'Sheet1');
      
      await expect(
        exportService.writeExcelFile(mockWorkbook, '/test/path', 'xlsx')
      ).rejects.toThrow('Failed to write Excel file: Write error');
    });
  });

  describe('getCellValue', () => {
    it('should return cell value as string', () => {
      const row = ['A', 'B', 'C'];
      const value = exportService.getCellValue(row, 1);
      
      expect(value).toBe('B');
    });

    it('should handle null column index', () => {
      const row = ['A', 'B', 'C'];
      const value = exportService.getCellValue(row, null);
      
      expect(value).toBe('');
    });

    it('should handle out of bounds index', () => {
      const row = ['A', 'B', 'C'];
      const value = exportService.getCellValue(row, 5);
      
      expect(value).toBe('');
    });

    it('should handle null/undefined values', () => {
      const row = ['A', null, undefined, 'D'];
      
      expect(exportService.getCellValue(row, 1)).toBe('');
      expect(exportService.getCellValue(row, 2)).toBe('');
      expect(exportService.getCellValue(row, 3)).toBe('D');
    });

    it('should trim whitespace', () => {
      const row = ['  A  ', ' B '];
      
      expect(exportService.getCellValue(row, 0)).toBe('A');
      expect(exportService.getCellValue(row, 1)).toBe('B');
    });
  });
});