const express = require('express')
const { pool } = require('../config/database')
const { authenticateToken, authorizePermission } = require('../middleware/auth')
const UserController = require('../controllers/userController')
const { createOptionalImageUploadMiddleware, ensureAbsoluteUrl, ensureImagesRelativePath } = require('../middleware/fileUpload')

const router = express.Router()

let hasDesignationIdColumn = null
async function ensureSchemaChecks () {
  if (hasDesignationIdColumn === null) {
    try {
      const [rows] = await pool.execute(
        "SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'designation_id' LIMIT 1"
      )
      hasDesignationIdColumn = rows.length > 0
    } catch (_e) {
      hasDesignationIdColumn = false
    }
  }
}

// Apply authentication middleware to all user routes
router.use(authenticateToken)

// Get users with pagination, search, and filters
// GET /api/users?page=1&limit=10&search=ahmed&role=Admin&status=active&organization_id=1
router.get('/', authorizePermission('user.read'), async (req, res) => {
  try {
    const {
      page = 1,
      limit = 10,
      search = '',
      role = 'all',
      status = 'all',
      organization_id
    } = req.query

    const pageNum = Math.max(1, parseInt(page) || 1)
    const limitNum = Math.max(1, Math.min(100, parseInt(limit) || 10))
    const offset = (pageNum - 1) * limitNum
    const userId = req.user.id
    const userRole = req.user.role
    const userRoleId = req.user.role_id

    await ensureSchemaChecks()

    // Build WHERE conditions
    const whereConditions = []
    const queryParams = []

    // Search condition
    if (search) {
      const searchPattern = `%${search}%`
      if (hasDesignationIdColumn) {
        whereConditions.push(`(
          u.name LIKE ? OR 
          u.email LIKE ? OR 
          u.phone LIKE ? OR
          r.name LIKE ? OR
          des.name LIKE ?
        )`)
        queryParams.push(searchPattern, searchPattern, searchPattern, searchPattern, searchPattern)
      } else {
        whereConditions.push(`(
          u.name LIKE ? OR 
          u.email LIKE ? OR 
          u.phone LIKE ? OR
          r.name LIKE ?
        )`)
        queryParams.push(searchPattern, searchPattern, searchPattern, searchPattern)
      }
    }

    // Role filter
    if (role !== 'all') {
      whereConditions.push('r.name = ?')
      queryParams.push(role)
    }

    // Status filter
    if (status !== 'all') {
      if (status === 'active') {
        whereConditions.push('u.is_active = 1')
      } else if (status === 'inactive') {
        whereConditions.push('u.is_active = 0')
      }
    }

    // Organization filter (Super Admin/Admin can explicitly filter)
    if (organization_id && (userRoleId === 1 || userRole === 'Admin')) {
      whereConditions.push('u.organization_id = ?')
      queryParams.push(parseInt(organization_id))
    } else if (userRoleId !== 1 && userRole !== 'Admin') {
      // Regular users can only see users in their organization
      whereConditions.push('u.organization_id = (SELECT organization_id FROM users WHERE id = ?)')
      queryParams.push(userId)
    }

    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : ''

    // Count total users
    const countQuery = `
      SELECT COUNT(*) as total
      FROM users u
      LEFT JOIN roles r ON u.role_id = r.id
      LEFT JOIN organizations o ON u.organization_id = o.id
      ${hasDesignationIdColumn ? 'LEFT JOIN designations des ON u.designation_id = des.id' : ''}
      ${whereClause}
    `

    const [countResult] = await pool.execute(countQuery, queryParams)
    const totalUsers = countResult[0].total
    const totalPages = Math.ceil(totalUsers / limitNum)

    // Get users with pagination
    const usersQuery = `
      SELECT 
        u.id,
        u.name,
        u.email,
        u.phone,
        u.login_id,
        u.logo,
        u.is_active,
        u.created_at,
        u.updated_at,
        u.organization_id,
        o.name as organization_name,
        r.name as role,
        r.id as role_id,
        ${hasDesignationIdColumn ? 'des.name as designation_name,' : ''}
        u.status,
        DATE_FORMAT(u.updated_at, '%Y-%m-%d %H:%i') as last_active
      FROM users u
      LEFT JOIN roles r ON u.role_id = r.id
      LEFT JOIN organizations o ON u.organization_id = o.id
      ${hasDesignationIdColumn ? 'LEFT JOIN designations des ON u.designation_id = des.id' : ''}
      ${whereClause}
      ORDER BY u.created_at DESC
      LIMIT ${limitNum} OFFSET ${offset}
    `

    const [users] = await pool.execute(usersQuery, queryParams)

    // Format users data
    const formattedUsers = users.map(user => {
      const relLogo = ensureImagesRelativePath('contacts', user.logo)
      const relAvatar = ensureImagesRelativePath('contacts', user.avatar)
      const absLogo = relLogo ? ensureAbsoluteUrl(req, relLogo) : null
      const absAvatar = relAvatar ? ensureAbsoluteUrl(req, relAvatar) : null
      return ({
        id: user.id,
        name: user.name,
        email: user.email,
        phone: user.phone,
        login_id: user.login_id,
        logo: absLogo,
        logo_url: absLogo,
        avatar: absAvatar,
        avatar_url: absAvatar,
        role: user.role,
        role_id: user.role_id,
        organization_id: user.organization_id,
        organization_name: user.organization_name,
        is_active: Boolean(user.is_active),
        status: user.status,
        last_active: user.last_active,
        created_at: user.created_at,
        updated_at: user.updated_at
      })
    })

    res.status(200).json({
      status: 'success',
      data: {
        users: formattedUsers,
        pagination: {
          currentPage: pageNum,
          totalPages,
          totalUsers,
          usersPerPage: limitNum,
          hasNextPage: pageNum < totalPages,
          hasPrevPage: pageNum > 1
        },
        filters: {
          search,
          role,
          status,
          organization_id
        }
      },
      message: `Found ${totalUsers} users`
    })
  } catch (error) {
    console.error('Error fetching users:', error)
    res.status(500).json({
      status: 'error',
      message: 'Failed to fetch users',
      error: error.message
    })
  }
})

// Get user statistics for dashboard
// GET /api/users/stats
router.get('/stats', authorizePermission('user.read'), async (req, res) => {
  try {
    const userId = req.user.id

    // Get current user's organization_id
    const [currentUserResult] = await pool.execute(
      'SELECT organization_id FROM users WHERE id = ?',
      [userId]
    )
    const organizationId = currentUserResult.length ? currentUserResult[0].organization_id : null
    const roleName = (req.user.role || '').toLowerCase()
    const isSuperAdmin = req.user.role_id === 1 || roleName === 'super admin' || roleName === 'super_admin'

    let adminQuery = `
      SELECT COUNT(u.id) as count
      FROM users u
      LEFT JOIN roles r ON u.role_id = r.id
      WHERE u.is_active = 1
        AND r.id != 1
        AND LOWER(r.name) LIKE '%admin%'
    `
    const adminParams = []
    if (organizationId && organizationId !== 0 && !isSuperAdmin) {
      adminQuery += ' AND u.organization_id = ?'
      adminParams.push(organizationId)
    }
    const [adminCountResult] = await pool.execute(adminQuery, adminParams)
    const adminUsersCount = adminCountResult[0]?.count || 0

    let viewQuery = `
      SELECT COUNT(u.id) as count
      FROM users u
      LEFT JOIN roles r ON u.role_id = r.id
      WHERE u.is_active = 1
        AND (r.id IS NULL OR r.id != 1)
        AND (r.name IS NULL OR LOWER(r.name) NOT LIKE '%admin%')
    `
    const viewParams = []
    if (organizationId && organizationId !== 0 && !isSuperAdmin) {
      viewQuery += ' AND u.organization_id = ?'
      viewParams.push(organizationId)
    }
    const [viewCountResult] = await pool.execute(viewQuery, viewParams)
    const viewUsersCount = viewCountResult[0]?.count || 0

    let newUsersQuery = `
      SELECT COUNT(u.id) as count
      FROM users u
      WHERE u.is_active = 1 AND u.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    `
    const newUsersParams = []
    if (organizationId && organizationId !== 0 && !isSuperAdmin) {
      newUsersQuery += ' AND u.organization_id = ?'
      newUsersParams.push(organizationId)
    }
    const [newUsersResult] = await pool.execute(newUsersQuery, newUsersParams)
    const newUsersCount = newUsersResult[0]?.count || 0

    let failedLoginsQuery = `
      SELECT COUNT(id) as count FROM failed_login_attempts 
      WHERE attempted_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
    `
    const failedLoginsParams = []
    if (organizationId && organizationId !== 0 && !isSuperAdmin) {
      failedLoginsQuery += ' AND organization_id = ?'
      failedLoginsParams.push(organizationId)
    }
    const [failedLoginsResult] = await pool.execute(failedLoginsQuery, failedLoginsParams)
    const failedLoginsCount = failedLoginsResult[0]?.count || 0

    let passwordChangesQuery = `
      SELECT COUNT(id) as count FROM password_change_history 
      WHERE changed_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    `
    const passwordChangesParams = []
    if (organizationId && organizationId !== 0 && !isSuperAdmin) {
      passwordChangesQuery += ' AND organization_id = ?'
      passwordChangesParams.push(organizationId)
    }
    const [passwordChangesResult] = await pool.execute(passwordChangesQuery, passwordChangesParams)
    const passwordChangesCount = passwordChangesResult[0]?.count || 0

    res.status(200).json({
      status: 'success',
      data: {
        adminUsers: adminUsersCount,
        viewUsers: viewUsersCount,
        newUsers: newUsersCount,
        failedLogins: failedLoginsCount,
        passwordChanges: passwordChangesCount,
        organizationId: organizationId && organizationId !== 0 && !isSuperAdmin ? organizationId : null
      }
    })
  } catch (error) {
    console.error('Error fetching user statistics:', error)
    res.status(500).json({
      status: 'error',
      message: 'Failed to fetch user statistics',
      error: error.message
    })
  }
})

// Get user by ID
router.get('/:id', authorizePermission('user.read'), (req, res, next) => UserController.getUserById(req, res, next))

// Create user (restricted fields only)
// POST /api/users
router.post(
  '/',
  authorizePermission('user.create'),
  createOptionalImageUploadMiddleware('logo', 'user'),
  (req, res, next) => UserController.createUser(req, res, next)
)

// Update user (restricted fields only)
// PUT /api/users/:id
router.put(
  '/:id',
  authorizePermission('user.update'),
  createOptionalImageUploadMiddleware('logo', 'user'),
  (req, res, next) => UserController.updateUser(req, res, next)
)

// Change password for a specific user
// PUT /api/users/:id/change-password
router.put(
  '/:id/change-password',
  authorizePermission('user.update'),
  async (req, res, next) => {
    try {
      const { id } = req.params
      const { password } = req.body
      if (!password || String(password).trim().length < 1) {
        return res.status(400).json({ status: 'error', message: 'Password is required' })
      }
      const bcrypt = require('bcryptjs')
      const config = require('../config/config')
      const hash = await bcrypt.hash(String(password).trim(), config.security.bcryptRounds || 10)

      const { pool } = require('../config/database')
      const User = require('../models/User')

      // Try updating directly (works even if user is inactive)
      const updated = await User.update(parseInt(id), { password_hash: hash, is_password_changed: 1 })
      if (!updated) {
        return res.status(400).json({ status: 'error', message: 'Failed to update password' })
      }

      // Get organization_id for logging
      let organizationId = null
      try {
        const [rows] = await pool.execute('SELECT organization_id FROM users WHERE id = ? LIMIT 1', [parseInt(id)])
        organizationId = rows.length ? rows[0].organization_id : null
      } catch {}

      // Log password change
      try {
        const SecurityController = require('../controllers/securityController')
        const ipAddress = req.ip || req.connection.remoteAddress
        const userAgent = req.get('user-agent')
        await SecurityController.logPasswordChange(parseInt(id), req.user.id, organizationId || req.user.organization_id, ipAddress, userAgent, 'admin_reset')
      } catch (logErr) {
        console.warn('Password change logged failed:', logErr?.message)
      }

      return res.status(200).json({ status: 'success', message: 'Password updated successfully' })
    } catch (error) {
      next(error)
    }
  }
)

// Update user role
// PUT /api/users/:id/role
router.put('/:id/role', authorizePermission('user.update'), async (req, res) => {
  try {
    const { id } = req.params
    const { role } = req.body
    const userId = req.user.id
    const userRole = req.user.role

    // Check if user is trying to update their own role
    if (parseInt(id) === userId) {
      return res.status(400).json({
        status: 'error',
        message: 'You cannot change your own role'
      })
    }

    // Check if user is trying to update Super Admin role
    const [existingUser] = await pool.execute(
      'SELECT u.id, r.name as role FROM users u LEFT JOIN roles r ON u.role_id = r.id WHERE u.id = ?',
      [id]
    )

    if (existingUser.length === 0) {
      return res.status(404).json({
        status: 'error',
        message: 'User not found'
      })
    }

    if (existingUser[0].role === 'Super_admin' && userRole !== 'Super_admin') {
      return res.status(403).json({
        status: 'error',
        message: 'Only Super Admin can modify Super Admin roles'
      })
    }

    // Get role ID
    const [roleData] = await pool.execute('SELECT id FROM roles WHERE name = ?', [role])
    if (roleData.length === 0) {
      return res.status(400).json({
        status: 'error',
        message: 'Invalid role'
      })
    }

    // Update user role
    await pool.execute(
      'UPDATE users SET role_id = ?, updated_at = NOW() WHERE id = ?',
      [roleData[0].id, id]
    )

    res.status(200).json({
      status: 'success',
      message: 'User role updated successfully'
    })
  } catch (error) {
    console.error('Error updating user role:', error)
    res.status(500).json({
      status: 'error',
      message: 'Failed to update user role',
      error: error.message
    })
  }
})

// Update user status
// PUT /api/users/:id/status
router.put('/:id/status', authorizePermission('user.update'), async (req, res) => {
  try {
    const { id } = req.params
    const { is_active } = req.body
    const userId = req.user.id

    // Check if user is trying to update their own status
    if (parseInt(id) === userId) {
      return res.status(400).json({
        status: 'error',
        message: 'You cannot change your own status'
      })
    }

    // Check if user exists
    const [existingUser] = await pool.execute('SELECT id FROM users WHERE id = ?', [id])
    if (existingUser.length === 0) {
      return res.status(404).json({
        status: 'error',
        message: 'User not found'
      })
    }

    // Update user status
    await pool.execute(
      'UPDATE users SET is_active = ?, updated_at = NOW() WHERE id = ?',
      [is_active ? 1 : 0, id]
    )

    res.status(200).json({
      status: 'success',
      message: 'User status updated successfully'
    })
  } catch (error) {
    console.error('Error updating user status:', error)
    res.status(500).json({
      status: 'error',
      message: 'Failed to update user status',
      error: error.message
    })
  }
})

// Delete user
// DELETE /api/users/:id
router.delete('/:id', authorizePermission('user.delete'), async (req, res) => {
  try {
    const { id } = req.params
    const userId = req.user.id

    // Check if user is trying to delete themselves
    if (parseInt(id) === userId) {
      return res.status(400).json({
        status: 'error',
        message: 'You cannot delete yourself'
      })
    }

    // Check if user exists and get role
    const [existingUser] = await pool.execute(
      'SELECT u.id, r.name as role FROM users u LEFT JOIN roles r ON u.role_id = r.id WHERE u.id = ?',
      [id]
    )

    if (existingUser.length === 0) {
      return res.status(404).json({
        status: 'error',
        message: 'User not found'
      })
    }

    // Prevent deletion of Super Admin
    if (existingUser[0].role === 'Super_admin') {
      return res.status(403).json({
        status: 'error',
        message: 'Cannot delete Super Admin user'
      })
    }

    // Delete user
    await pool.execute('DELETE FROM users WHERE id = ?', [id])

    res.status(200).json({
      status: 'success',
      message: 'User deleted successfully'
    })
  } catch (error) {
    console.error('Error deleting user:', error)
    res.status(500).json({
      status: 'error',
      message: 'Failed to delete user',
      error: error.message
    })
  }
})

module.exports = router
