const XLSX = require('xlsx')
const bcrypt = require('bcryptjs')
const fs = require('fs')
const User = require('../models/User')
const Rank = require('../models/Rank')
const Unit = require('../models/Unit')
const Department = require('../models/Department')
const Role = require('../models/Role')
const Designation = require('../models/Designation')

class UserExcelController {
  /**
   * Upload and process Excel file for bulk user creation
   * Expected Excel columns:
   * - Name (required)
   * - Unit (required)
   * - Department (required)
   * - Sub-Department (optional) - Must be a child of Department (parent_id reference) if provided
   * - Role (required)
   * - Designation (required)
   * - Phone (required)
   * - Mobile (required) - login_id will be generated from this
   * - Alternative Mobile (optional)
   * - Password (optional, defaults to the Mobile number)
   *
   * Note: login_id will be generated from Mobile by removing "+88" prefix if exists
   * Note: Service No will be auto-generated
   * Note: If Sub-Department is not provided, the parent Department will be used
   */
  static async uploadUsersFromExcel (req, res) {
    try {
      // Check if file was uploaded
      if (!req.file) {
        return res.status(400).json({
          success: false,
          message: 'No Excel file uploaded',
          error: 'FILE_REQUIRED'
        })
      }

      // Validate file type
      const allowedTypes = [
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', // .xlsx
        'application/vnd.ms-excel', // .xls
        'application/vnd.ms-excel.sheet.macroEnabled.12' // .xlsm
      ]

      if (!allowedTypes.includes(req.file.mimetype)) {
        // Clean up uploaded file
        fs.unlinkSync(req.file.path)
        return res.status(400).json({
          success: false,
          message: 'Invalid file type. Please upload an Excel file (.xlsx, .xls)',
          error: 'INVALID_FILE_TYPE'
        })
      }

      // Validate file size (max 10MB)
      const maxSize = 10 * 1024 * 1024 // 10MB
      if (req.file.size > maxSize) {
        fs.unlinkSync(req.file.path)
        return res.status(400).json({
          success: false,
          message: 'File size too large. Maximum size is 10MB',
          error: 'FILE_TOO_LARGE'
        })
      }

      // Get organization_id from authenticated user
      const organization_id = req.user?.organization_id

      if (!organization_id) {
        fs.unlinkSync(req.file.path)
        return res.status(400).json({
          success: false,
          message: 'Organization ID not found in user session',
          error: 'ORGANIZATION_ID_REQUIRED'
        })
      }

      // Process the Excel file
      const result = await UserExcelController.processExcelFile(req.file.path, organization_id)

      // Clean up uploaded file
      fs.unlinkSync(req.file.path)

      // Determine success status and message based on results
      const hasErrors = result.errors && result.errors.length > 0
      const hasInsertionErrors = result.details && result.details.some(d => d.status === 'error')
      const insertedCount = result.insertedUsers || 0

      let statusMessage = 'Excel file processed successfully'
      let isSuccess = true

      if (hasErrors) {
        // Validation errors (couldn't process rows)
        statusMessage = `Excel processing failed with ${result.errors.length} error(s)`
        isSuccess = false
      } else if (hasInsertionErrors && insertedCount === 0) {
        // All insertions failed
        statusMessage = 'Excel file processed but no users were created due to errors'
        isSuccess = false
      } else if (hasInsertionErrors && insertedCount > 0) {
        // Partial success
        statusMessage = `Excel file processed with partial success: ${insertedCount} user(s) created, ${result.details.filter(d => d.status === 'error').length} failed`
      } else if (insertedCount === 0 && result.skippedUsers > 0) {
        // All users were skipped (duplicates)
        statusMessage = 'Excel file processed but all users were skipped (already exist in database)'
      }

      // Return result
      res.json({
        status: isSuccess ? 'success' : 'error',
        message: statusMessage,
        data: result
      })
    } catch (error) {
      console.error('Excel upload error:', error)

      // Clean up file if it exists
      if (req.file && fs.existsSync(req.file.path)) {
        fs.unlinkSync(req.file.path)
      }

      res.status(500).json({
        success: false,
        message: 'Error processing Excel file',
        error: error.message
      })
    }
  }

  /**
   * Process Excel file and extract user data
   */
  static async processExcelFile (filePath, organization_id) {
    try {
      // Read Excel file
      const workbook = XLSX.readFile(filePath)
      const sheetName = workbook.SheetNames[0]
      const worksheet = workbook.Sheets[sheetName]

      // Convert to JSON
      const jsonData = XLSX.utils.sheet_to_json(worksheet, {
        header: 1,
        defval: null,
        blankrows: false
      })

      if (jsonData.length < 2) {
        throw new Error('Excel file must contain at least a header row and one data row')
      }

      // Extract headers and data
      const headers = jsonData[0]
      const dataRows = jsonData.slice(1)

      // Validate headers - All fields are mandatory except Sub-Department, Alternative Mobile and Password
      const requiredHeaders = ['Name', 'Unit', 'Department', 'Role', 'Designation', 'Phone', 'Mobile']
      const missingHeaders = requiredHeaders.filter(header => !headers.includes(header))

      if (missingHeaders.length > 0) {
        throw new Error(`Missing required columns: ${missingHeaders.join(', ')}`)
      }

      // Get lookup data for mapping names to IDs
      const lookupData = await UserExcelController.getLookupData(organization_id)

      // Process each row
      const processedUsers = []
      const errors = []
      const warnings = []

      for (let i = 0; i < dataRows.length; i++) {
        const row = dataRows[i]
        const rowNumber = i + 2 // +2 because we start from row 2 (after header)

        try {
          // Skip empty rows
          if (!row || row.every(cell => !cell || cell.toString().trim() === '')) {
            continue
          }

          const userData = UserExcelController.parseUserRow(row, headers, lookupData, rowNumber, organization_id)
          processedUsers.push(userData)
        } catch (error) {
          errors.push({
            row: rowNumber,
            error: error.message,
            data: row
          })
        }
      }

      // Validate processed users
      const validationResult = await UserExcelController.validateUsers(processedUsers)

      // Add validation errors to errors array
      errors.push(...validationResult.errors)
      warnings.push(...validationResult.warnings)

      // If there are critical errors, don't proceed with insertion
      if (errors.length > 0) {
        return {
          totalRows: dataRows.length,
          processedUsers: processedUsers.length,
          errors,
          warnings,
          insertedUsers: 0,
          skippedUsers: 0
        }
      }

      // Insert users into database
      const insertionResult = await UserExcelController.insertUsers(processedUsers)

      return {
        totalRows: dataRows.length,
        processedUsers: processedUsers.length,
        errors,
        warnings,
        insertedUsers: insertionResult.inserted,
        skippedUsers: insertionResult.skipped,
        details: insertionResult.details
      }
    } catch (error) {
      throw new Error(`Error processing Excel file: ${error.message}`)
    }
  }

  /**
   * Get lookup data for mapping names to IDs
   */
  static async getLookupData (organization_id) {
    try {
      const [units, departments, roles, designations] = await Promise.all([
        Unit.findAll({ include_inactive: false, organization_id }),
        Department.findAll({ include_inactive: false, organization_id }),
        Role.findAll(),
        Designation.findAll({ is_active: 'true' })
      ])

      // Build department lookup with parent relationships
      // Format: { 'parent_name|sub_name': sub_department_id }
      const departmentLookup = {}
      const departmentById = {}

      // Helper function to normalize names for lookup
      const normalizeName = (name) => {
        return name.trim().toLowerCase().replace(/\s+/g, ' ')
      }

      // First pass: index all departments by id and name
      if (Array.isArray(departments)) {
        departments.forEach(dept => {
          if (dept && dept.id) {
            departmentById[dept.id] = dept
            if (dept.name && typeof dept.name === 'string') {
              const normalizedName = normalizeName(dept.name)
              departmentLookup[normalizedName] = dept.id
            }
          }
        })
      }

      // Second pass: create composite keys for sub-departments
      const subDepartmentLookup = {}
      if (Array.isArray(departments)) {
        departments.forEach(dept => {
          if (dept && dept.parent_id && departmentById[dept.parent_id]) {
            const parentDept = departmentById[dept.parent_id]
            if (parentDept && parentDept.name && typeof parentDept.name === 'string' &&
                dept.name && typeof dept.name === 'string') {
              const parentName = normalizeName(parentDept.name)
              const subName = normalizeName(dept.name)
              const compositeKey = `${parentName}|${subName}`
              subDepartmentLookup[compositeKey] = dept.id
            }
          }
        })
      }

      return {
        units: Array.isArray(units)
          ? units.reduce((acc, unit) => {
            if (unit && unit.id) {
              if (unit.name && typeof unit.name === 'string') {
                acc[normalizeName(unit.name)] = unit.id
              }
              if (unit.code && typeof unit.code === 'string') {
                acc[normalizeName(unit.code)] = unit.id
              }
            }
            return acc
          }, {})
          : {},
        departments: departmentLookup,
        subDepartments: subDepartmentLookup,
        roles: Array.isArray(roles)
          ? roles.reduce((acc, role) => {
            if (role && role.id && role.name && typeof role.name === 'string') {
              acc[normalizeName(role.name)] = role.id
            }
            return acc
          }, {})
          : {},
        designations: Array.isArray(designations)
          ? designations.reduce((acc, des) => {
            if (des && des.id && des.name && typeof des.name === 'string') {
              acc[normalizeName(des.name)] = des.id
            }
            return acc
          }, {})
          : {}
      }
    } catch (error) {
      throw new Error(`Error getting lookup data: ${error.message}`)
    }
  }

  /**
   * Parse a single user row from Excel
   */
  static parseUserRow (row, headers, lookupData, _rowNumber, organization_id) {
    const userData = {}

    // Helper function to get cell value by header name
    const getCellValue = (headerName) => {
      const index = headers.indexOf(headerName)
      return index !== -1 && row[index] ? row[index].toString().trim() : null
    }

    // Get required fields
    userData.name = getCellValue('Name')
    const mobileValue = getCellValue('Mobile')
    userData.mobile = mobileValue
    userData.phone = getCellValue('Phone')
    userData.alternative_mobile = getCellValue('Alternative Mobile') || null
    const normalizedMobile = mobileValue ? mobileValue.replace(/\s+/g, '') : null
    userData.password = getCellValue('Password') || normalizedMobile || 'A12345' // Default password

    // Validate required fields
    if (!userData.name) {
      throw new Error('Name is required')
    }
    if (!userData.mobile) {
      throw new Error('Mobile is required')
    }
    if (!userData.phone) {
      throw new Error('Phone is required')
    }

    // Add +88 prefix to alternative_mobile if it doesn't exist
    if (userData.alternative_mobile) {
      const altMobile = userData.alternative_mobile.toString().trim()
      if (!altMobile.startsWith('+88') && !altMobile.startsWith('88')) {
        userData.alternative_mobile = '+88' + altMobile
      } else if (altMobile.startsWith('88') && !altMobile.startsWith('+88')) {
        userData.alternative_mobile = '+' + altMobile
      }
    }

    // Generate login_id from Mobile (remove +88 prefix if exists)
    let loginId = userData.mobile.toString().trim()
    if (loginId.startsWith('+88')) {
      loginId = loginId.substring(3)
    } else if (loginId.startsWith('88')) {
      loginId = loginId.substring(2)
    }

    userData.login_id = loginId

    // Fields not used in new format
    userData.service_no = null // Will be auto-generated
    userData.email = null
    userData.rank_id = null

    // Set organization_id from authenticated user
    userData.organization_id = organization_id

    // Set parent_id to null (not used in Excel upload)
    userData.parent_id = null

    // Map names to IDs
    const unitName = getCellValue('Unit')
    const departmentName = getCellValue('Department')
    const subDepartmentName = getCellValue('Sub-Department')
    const roleName = getCellValue('Role')
    const designationName = getCellValue('Designation')

    // Validate mandatory fields
    if (!unitName) {
      throw new Error('Unit is required')
    }
    if (!departmentName) {
      throw new Error('Department is required')
    }
    if (!roleName) {
      throw new Error('Role is required')
    }
    if (!designationName) {
      throw new Error('Designation is required')
    }

    // Helper function to normalize names for lookup (matches getLookupData normalization)
    const normalizeName = (name) => {
      return name.trim().toLowerCase().replace(/\s+/g, ' ')
    }

    // Find IDs by names for mandatory fields
    userData.unit_id = lookupData.units[normalizeName(unitName)]
    userData.role_id = lookupData.roles[normalizeName(roleName)]
    userData.designation_id = lookupData.designations[normalizeName(designationName)]

    // Validate Unit, Role, and Designation were found
    if (!userData.unit_id) {
      throw new Error(`Unit "${unitName}" not found in system`)
    }
    if (!userData.role_id) {
      throw new Error(`Role "${roleName}" not found in system`)
    }
    if (!userData.designation_id) {
      throw new Error(`Designation "${designationName}" not found in system`)
    }

    // Find Department ID (parent department)
    const departmentId = lookupData.departments[normalizeName(departmentName)]
    if (!departmentId) {
      throw new Error(`Department "${departmentName}" not found in system`)
    }

    // Handle Sub-Department (optional)
    if (subDepartmentName && subDepartmentName.trim() !== '') {
      // Find Sub-Department ID using composite key
      const compositeKey = `${normalizeName(departmentName)}|${normalizeName(subDepartmentName)}`
      userData.department_id = lookupData.subDepartments[compositeKey]

      // Validate that sub-department was found and is a child of the specified department
      if (!userData.department_id) {
        throw new Error(`Sub-Department "${subDepartmentName}" not found under Department "${departmentName}"`)
      }
    } else {
      // If no sub-department specified, use the parent department
      userData.department_id = departmentId
    }

    // Validate phone format
    const phoneRegex = /^[\+]?[0-9\s\-\(\)]{10,}$/
    if (!phoneRegex.test(userData.phone)) {
      throw new Error('Invalid phone format - must be at least 10 digits')
    }

    // Validate mobile format
    const mobileRegex = /^[\+]?[0-9\s\-\(\)]{10,}$/
    if (!mobileRegex.test(userData.mobile)) {
      throw new Error('Invalid mobile format - must be at least 10 digits')
    }

    // Validate login_id (must be at least 10 digits after removing country code)
    if (userData.login_id.length < 10) {
      throw new Error('Invalid mobile number - must be at least 10 digits after removing country code')
    }

    return userData
  }

  /**
   * Validate users before insertion
   */
  static async validateUsers (users) {
    const errors = []
    const warnings = []

    // Check for duplicate login_ids (mobile numbers)
    const loginIds = new Set()

    for (let i = 0; i < users.length; i++) {
      const user = users[i]

      // Check for duplicate login_id within the batch
      if (loginIds.has(user.login_id)) {
        errors.push({
          row: i + 2,
          error: `Duplicate mobile number "${user.login_id}" in Excel file`,
          data: user
        })
      } else {
        loginIds.add(user.login_id)
      }

      // Check if login_id already exists in database
      try {
        const existingByLoginId = await User.findByLoginId(user.login_id)
        if (existingByLoginId) {
          warnings.push({
            row: i + 2,
            warning: `Mobile number "${user.login_id}" already exists in database`,
            data: user
          })
        }
      } catch (error) {
        // Database check failed, but don't stop the process
        warnings.push({
          row: i + 2,
          warning: `Could not verify if user data already exists: ${error.message}`,
          data: user
        })
      }
    }

    return { errors, warnings }
  }

  /**
   * Insert users into database
   */
  static async insertUsers (users) {
    const inserted = []
    const skipped = []
    const details = []

    // Get the maximum order_id and service_no from the database
    const { pool } = require('../config/database')
    let maxOrderId = 0
    let maxServiceNo = 0

    try {
      const [rows] = await pool.execute('SELECT MAX(order_id) as max_order_id FROM users')
      maxOrderId = rows[0].max_order_id || 0
    } catch (error) {
      console.error('Error getting max order_id:', error)
    }

    // Get max service_no (extract numeric part)
    try {
      const [rows] = await pool.execute(`
        SELECT service_no 
        FROM users 
        WHERE service_no IS NOT NULL 
        ORDER BY CAST(REGEXP_REPLACE(service_no, '[^0-9]', '') AS UNSIGNED) DESC 
        LIMIT 1
      `)
      if (rows.length > 0 && rows[0].service_no) {
        // Extract numeric part from service_no (e.g., "BN10003" -> 10003)
        const match = rows[0].service_no.match(/\d+/)
        if (match) {
          maxServiceNo = parseInt(match[0])
        }
      }
    } catch (error) {
      console.error('Error getting max service_no:', error)
    }

    for (let i = 0; i < users.length; i++) {
      const userData = users[i]

      try {
        // Check if user already exists (by login_id which is mandatory)
        const existingByLoginId = await User.findByLoginId(userData.login_id)

        if (existingByLoginId) {
          skipped.push({
            index: i + 2,
            reason: 'Mobile number already exists',
            data: userData
          })
          continue
        }

        // Auto-generate service_no if not provided
        if (!userData.service_no) {
          maxServiceNo++
          userData.service_no = `BN${maxServiceNo}`
        }

        // Hash password
        const saltRounds = 12
        userData.password_hash = await bcrypt.hash(userData.password, saltRounds)

        // Set order_id to max + 1 (increment for each new user)
        maxOrderId++
        userData.order_id = maxOrderId

        // Create user
        const userId = await User.create(userData)

        inserted.push({
          index: i + 2,
          userId,
          data: userData
        })

        details.push({
          row: i + 2,
          status: 'success',
          message: `User "${userData.name || userData.login_id}" created successfully`,
          userId
        })
      } catch (error) {
        details.push({
          row: i + 2,
          status: 'error',
          message: `Error creating user: ${error.message}`,
          data: userData
        })
      }
    }

    return {
      inserted: inserted.length,
      skipped: skipped.length,
      details
    }
  }

  /**
   * Get Excel template for user upload
   */
  static async getExcelTemplate (req, res) {
    try {
      // Create sample data with new format
      // Required: Name, Unit, Department, Role, Designation, Phone, Mobile
    // Optional: Sub-Department, Alternative Mobile, Password (defaults to the Mobile number)
      // Auto-generated: Service No
      const sampleData = [
        ['Name', 'Unit', 'Department', 'Sub-Department', 'Role', 'Designation', 'Phone', 'Mobile', 'Alternative Mobile', 'Password'],
        ['John Doe', 'Naval Headquarters', 'CNS', 'Sub Department 1', 'User', 'SO(O)', '+880211111113', '+8801811111113', '01811111114', 'password123'],
        ['Jane Smith', 'Dhaka Naval Area', 'Operations Division', '', 'User', 'SO(Plan-1)', '+880211111115', '+8801811111115', '01811111116', 'password123']
      ]

      // Create workbook
      const workbook = XLSX.utils.book_new()
      const worksheet = XLSX.utils.aoa_to_sheet(sampleData)

      // Set column widths
      const colWidths = [
        { wch: 20 }, // Name (required)
        { wch: 20 }, // Unit (required)
        { wch: 20 }, // Department (required)
        { wch: 20 }, // Sub-Department (required)
        { wch: 15 }, // Role (required)
        { wch: 20 }, // Designation (required)
        { wch: 15 }, // Phone (required)
        { wch: 15 }, // Mobile (required)
        { wch: 20 }, // Alternative Mobile (optional)
        { wch: 15 } // Password (optional)
      ]
      worksheet['!cols'] = colWidths

      // Add worksheet to workbook
      XLSX.utils.book_append_sheet(workbook, worksheet, 'Users')

      // Generate buffer
      const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' })

      // Set headers for file download
      res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
      res.setHeader('Content-Disposition', 'attachment; filename="user_upload_template.xlsx"')
      res.setHeader('Content-Length', buffer.length)

      // Send file
      res.send(buffer)
    } catch (error) {
      console.error('Template generation error:', error)
      res.status(500).json({
        success: false,
        message: 'Error generating Excel template',
        error: error.message
      })
    }
  }

  /**
   * Get available lookup values for Excel upload
   */
  static async getLookupValues (req, res) {
    try {
      const organization_id = req.user?.organization_id
      const lookupData = await UserExcelController.getLookupData(organization_id)

      // Convert back to arrays with names
      const result = {
        units: Object.keys(lookupData.units).map(name => ({ name, id: lookupData.units[name] })),
        departments: Object.keys(lookupData.departments).map(name => ({ name, id: lookupData.departments[name] })),
        subDepartments: Object.keys(lookupData.subDepartments).map(compositeKey => {
          const [parentName, subName] = compositeKey.split('|')
          return {
            parentDepartment: parentName,
            subDepartment: subName,
            id: lookupData.subDepartments[compositeKey]
          }
        }),
        roles: Object.keys(lookupData.roles).map(name => ({ name, id: lookupData.roles[name] })),
        designations: Object.keys(lookupData.designations).map(name => ({ name, id: lookupData.designations[name] }))
      }

      res.json({
        success: true,
        message: 'Lookup values retrieved successfully',
        data: result
      })
    } catch (error) {
      console.error('Lookup values error:', error)
      res.status(500).json({
        success: false,
        message: 'Error retrieving lookup values',
        error: error.message
      })
    }
  }
}

module.exports = UserExcelController
