/**
 * Member Import System - LocalLendables
 * Handles Excel template generation, bulk member import, avatar assignment, and welcome sequences
 */

import * as XLSX from 'xlsx';
import * as bcrypt from 'bcrypt';
import { storage } from './storage';

export class MemberImportService {
  private readonly avatarColors = [
    '#6366f1', '#8b5cf6', '#ec4899', '#ef4444', '#f97316', 
    '#eab308', '#22c55e', '#10b981', '#06b6d4', '#3b82f6',
    '#6366f1', '#8b5cf6', '#ec4899', '#ef4444', '#f97316'
  ];

  private readonly genericAvatars = [
    'g1.jpg', 'g2.jpg', 'g3.jpg', 'g4.jpg', 'g5.jpg', 'g6.jpg',
    'g7.jpg', 'g8.jpg', 'g9.jpg', 'g10.jpg', 'g11.jpg', 'g12.jpg'
  ];

  /**
   * Generate Excel template for member import
   */
  generateExcelTemplate(): Buffer {
    const sampleData = [
      {
        'First Name': 'John',
        'Last Name': 'Smith',
        'Email': 'john.smith@example.com',
        'Initial Password': 'TempPass123!'
      }
    ];

    const worksheet = XLSX.utils.json_to_sheet(sampleData);
    
    // Set column widths
    worksheet['!cols'] = [
      { width: 15 }, // First Name
      { width: 15 }, // Last Name
      { width: 30 }, // Email
      { width: 20 }  // Initial Password
    ];

    // Add instructions
    const instructions = XLSX.utils.aoa_to_sheet([
      ['LocalLendables Member Import Template'],
      [''],
      ['Instructions:'],
      ['1. Replace sample data with actual member information'],
      ['2. Ensure all email addresses are unique and valid'],
      ['3. Use strong initial passwords (min 8 chars, include letters, numbers, symbols)'],
      ['4. Members will be prompted to change passwords on first login'],
      ['5. Maximum 100 members per import'],
      [''],
      ['Required Columns:'],
      ['- First Name: Member\'s first name'],
      ['- Last Name: Member\'s last name'],
      ['- Email: Unique email address (used for login)'],
      ['- Initial Password: Temporary password for first login'],
      ['']
    ]);

    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, instructions, 'Instructions');
    XLSX.utils.book_append_sheet(workbook, worksheet, 'Members');

    return XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
  }

  /**
   * Process uploaded Excel file and import members
   */
  async processExcelImport(buffer: Buffer, adminUserId: number): Promise<{
    success: boolean;
    imported: number;
    errors: string[];
    duplicates: string[];
  }> {
    const result = {
      success: false,
      imported: 0,
      errors: [] as string[],
      duplicates: [] as string[]
    };

    try {
      const workbook = XLSX.read(buffer, { type: 'buffer' });
      const sheetName = workbook.SheetNames.find(name => name.toLowerCase() === 'members') || workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];
      const jsonData = XLSX.utils.sheet_to_json(worksheet) as any[];

      if (jsonData.length === 0) {
        result.errors.push('No data found in Excel file');
        return result;
      }

      if (jsonData.length > 1000) {
        result.errors.push('Maximum 1000 members per import. Please split into multiple files.');
        return result;
      }

      // Validate and process each member
      for (let i = 0; i < jsonData.length; i++) {
        const row = jsonData[i];
        const rowNum = i + 2; // Excel row number (accounting for header)

        try {
          const memberData = this.validateMemberRow(row, rowNum);
          if (memberData.errors.length > 0) {
            result.errors.push(...memberData.errors);
            continue;
          }

          // Check for duplicate email
          const existingUser = await storage.getUserByEmail(memberData.email);
          if (existingUser) {
            result.duplicates.push(`Row ${rowNum}: ${memberData.email} already exists`);
            continue;
          }

          // Create member
          const createdUser = await this.createImportedMember(memberData, adminUserId);
          if (createdUser) {
            result.imported++;
          } else {
            result.errors.push(`Row ${rowNum}: Failed to create member ${memberData.email}`);
          }
        } catch (error) {
          result.errors.push(`Row ${rowNum}: ${error.message}`);
        }
      }

      result.success = result.imported > 0;
      return result;
    } catch (error) {
      result.errors.push(`File processing error: ${error.message}`);
      return result;
    }
  }

  /**
   * Validate a single member row from Excel
   */
  private validateMemberRow(row: any, rowNum: number): {
    firstName: string;
    lastName: string;
    email: string;
    password: string;
    errors: string[];
  } {
    const errors: string[] = [];
    const firstName = (row['First Name'] || '').toString().trim();
    const lastName = (row['Last Name'] || '').toString().trim();
    const email = (row['Email'] || '').toString().trim().toLowerCase();
    const password = (row['Initial Password'] || '').toString().trim();

    if (!firstName) errors.push(`Row ${rowNum}: First Name is required`);
    if (!lastName) errors.push(`Row ${rowNum}: Last Name is required`);
    
    if (!email) {
      errors.push(`Row ${rowNum}: Email is required`);
    } else if (!this.isValidEmail(email)) {
      errors.push(`Row ${rowNum}: Invalid email format`);
    }

    if (!password) {
      errors.push(`Row ${rowNum}: Initial Password is required`);
    } else if (password.length < 8) {
      errors.push(`Row ${rowNum}: Password must be at least 8 characters`);
    }

    return { firstName, lastName, email, password, errors };
  }

  /**
   * Create an imported member with random avatar and background color
   */
  private async createImportedMember(memberData: {
    firstName: string;
    lastName: string;
    email: string;
    password: string;
  }, adminUserId: number): Promise<boolean> {
    try {
      const hashedPassword = await bcrypt.hash(memberData.password, 10);
      const randomAvatar = this.genericAvatars[Math.floor(Math.random() * this.genericAvatars.length)];
      const randomColor = this.avatarColors[Math.floor(Math.random() * this.avatarColors.length)];

      const userData = {
        firstName: memberData.firstName,
        lastName: memberData.lastName,
        email: memberData.email,
        password: hashedPassword,
        avatar: randomAvatar,
        avatarBackgroundColor: randomColor,
        invitedBy: adminUserId,
        isImported: true,
        welcomeSequenceCompleted: false,
        active: true,
        roles: '["ROLE_USER"]'
      };

      const userId = await storage.createUser(userData);
      return userId > 0;
    } catch (error) {
      console.error('Error creating imported member:', error);
      return false;
    }
  }

  /**
   * Get welcome sequence progress for a user
   */
  async getWelcomeProgress(userId: number): Promise<{
    completed: boolean;
    steps: {
      profileUpdate: boolean;
      tourComplete: boolean;
      firstItemAdded: boolean;
      communityIntroRead: boolean;
    };
  }> {
    try {
      const user = await storage.getUser(userId);
      const preferences = await storage.getUserPreferences(userId);
      const userItems = await storage.getUserItems(userId);

      return {
        completed: user?.welcomeSequenceCompleted || false,
        steps: {
          profileUpdate: !!(user?.bio && user?.avatar),
          tourComplete: preferences?.tourCompleted || false,
          firstItemAdded: userItems.length > 0,
          communityIntroRead: preferences?.communityIntroRead || false
        }
      };
    } catch (error) {
      console.error('Error getting welcome progress:', error);
      return {
        completed: false,
        steps: {
          profileUpdate: false,
          tourComplete: false,
          firstItemAdded: false,
          communityIntroRead: false
        }
      };
    }
  }

  /**
   * Mark welcome sequence as completed
   */
  async completeWelcomeSequence(userId: number): Promise<boolean> {
    try {
      return await storage.updateUserWelcomeStatus(userId, true);
    } catch (error) {
      console.error('Error completing welcome sequence:', error);
      return false;
    }
  }

  private isValidEmail(email: string): boolean {
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    return emailRegex.test(email);
  }
}

export const memberImportService = new MemberImportService();