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

class RoleController {
  // Get all roles
  static async getRoles (req, res, next) {
    try {
      const roles = await Role.findAll()

      res.status(200).json({
        status: 'success',
        data: {
          roles
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get roles for organization dialog: organization_id in (1,0) and id != 1
  // Super Admin can see all organizations' roles
  static async getRolesForOrganizationDialog (req, res, next) {
    try {
      const userRoleName = (req.user?.role || req.user?.role_name || '').toString().toLowerCase()
      const isSuperAdmin = userRoleName === 'super admin' || userRoleName === 's_admin' || req.user?.role_id === 1
      
      let roles = []
      
      if (isSuperAdmin) {
        // Super Admin sees all active roles from all organizations
        // Query: SELECT * FROM roles WHERE is_active = 1 ORDER BY id ASC
        const { pool } = require('../config/database')
        const query = 'SELECT * FROM roles WHERE is_active = 1 ORDER BY id ASC'
        const [rows] = await pool.execute(query)
        roles = rows.map(row => {
          // Convert to Role-like object with permissions
          return {
            id: row.id,
            name: row.name,
            organization_id: row.organization_id,
            description: row.description,
            level: row.level,
            is_active: row.is_active,
            created_at: row.created_at,
            updated_at: row.updated_at,
            permissions: [],
            permission_ids: [],
            organization_name: null,
            organization_code: null
          }
        })
        console.log(`🔍 Super Admin accessing roles - showing all ${roles.length} active roles from all organizations (ordered by id ASC)`)
      } else {
        // Regular users see filtered roles (organization_id in (1,0) and id != 1)
        roles = await Role.findForOrganizationDialog()
        console.log(`🔍 Regular user accessing roles - showing ${roles.length} filtered roles`)
      }

      res.status(200).json({
        status: 'success',
        data: {
          roles
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get role by ID
  static async getRoleById (req, res, next) {
    try {
      const { id } = req.params
      const role = await Role.findById(id)

      if (!role) {
        return res.status(404).json({
          status: 'error',
          message: 'Role not found'
        })
      }

      res.status(200).json({
        status: 'success',
        data: {
          role
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get role with permissions
  static async getRoleWithPermissions (req, res, next) {
    try {
      const { id } = req.params
      const role = await Role.getRoleWithPermissions(id)

      if (!role) {
        return res.status(404).json({
          status: 'error',
          message: 'Role not found'
        })
      }

      res.status(200).json({
        status: 'success',
        data: {
          role
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get all permissions
  static async getPermissions (req, res, next) {
    try {
      const query = 'SELECT * FROM permissions WHERE is_active = 1 ORDER BY resource, action'
      const [permissions] = await pool.execute(query)

      res.status(200).json({
        status: 'success',
        data: {
          permissions
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get permissions grouped by resource
  static async getPermissionsGrouped (req, res, next) {
    try {
      const query = 'SELECT * FROM permissions WHERE is_active = 1 ORDER BY resource, action'
      const [permissions] = await pool.execute(query)

      // Group permissions by resource
      const grouped = permissions.reduce((acc, perm) => {
        if (!acc[perm.resource]) {
          acc[perm.resource] = []
        }
        acc[perm.resource].push(perm)
        return acc
      }, {})

      res.status(200).json({
        status: 'success',
        data: {
          permissions: grouped
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get user permissions
  static async getUserPermissions (req, res, next) {
    try {
      const userId = req.params.userId || req.user.id

      // Only allow users to view their own permissions unless they're admin
      if (userId != req.user.id && req.user.role !== 'Super_admin' && req.user.role !== 'Admin') {
        return res.status(403).json({
          status: 'error',
          message: 'You can only view your own permissions'
        })
      }

      const permissions = await Role.getUserPermissions(userId)

      res.status(200).json({
        status: 'success',
        data: {
          permissions
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Check user permission
  static async checkUserPermission (req, res, next) {
    try {
      const userId = req.params.userId || req.user.id
      const { permission } = req.query

      if (!permission) {
        return res.status(400).json({
          status: 'error',
          message: 'Permission name is required'
        })
      }

      const hasPermission = await Role.userHasPermission(userId, permission)

      res.status(200).json({
        status: 'success',
        data: {
          hasPermission,
          permission
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get all lookup data (for dropdowns)
  static async getLookupData (req, res, next) {
    try {
      const [roles, ranks, units, departments] = await Promise.all([
        pool.execute('SELECT id, name, description, level FROM roles WHERE is_active = 1 ORDER BY level DESC'),
        pool.execute('SELECT id, name, level, description FROM ranks WHERE is_active = 1 ORDER BY level DESC'),
        pool.execute('SELECT id, name, description FROM units WHERE is_active = 1 ORDER BY name'),
        pool.execute('SELECT id, name, description FROM departments WHERE is_active = 1 ORDER BY name')
      ])

      res.status(200).json({
        status: 'success',
        data: {
          roles: roles[0],
          ranks: ranks[0],
          units: units[0],
          departments: departments[0]
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get branches by unit
  static async getBranchesByUnit (req, res, next) {
    try {
      const { unitId } = req.params
      const query = 'SELECT id, name, description FROM branches WHERE unit_id = ? AND is_active = 1 ORDER BY name'
      const [branches] = await pool.execute(query, [unitId])

      res.status(200).json({
        status: 'success',
        data: {
          branches
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get sub-branches by branch
  static async getSubBranchesByBranch (req, res, next) {
    try {
      const { branchId } = req.params
      const query = 'SELECT id, name, description FROM sub_branches WHERE branch_id = ? AND is_active = 1 ORDER BY name'
      const [subBranches] = await pool.execute(query, [branchId])

      res.status(200).json({
        status: 'success',
        data: {
          subBranches
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get designations by sub-branch
  static async getDesignationsBySubBranch (req, res, next) {
    try {
      const { subBranchId } = req.params
      const query = 'SELECT id, name, description FROM designations WHERE sub_branch_id = ? AND is_active = 1 ORDER BY name'
      const [designations] = await pool.execute(query, [subBranchId])

      res.status(200).json({
        status: 'success',
        data: {
          designations
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Create new role
  static async createRole (req, res, next) {
    try {
      const { name, description, level, permissions = [], organization_id: bodyOrgId } = req.body
      
      // Determine organization_id: Always use user's organization_id (unless Super Admin)
      // Super Admin can specify organization_id in body, but regular users must use their own
      const userOrgId = req.user?.organization_id
      const userRoleName = req.user?.role || req.user?.role_name || ''
      const isSuperAdmin = userRoleName.toLowerCase() === 'super admin' || userRoleName.toLowerCase() === 'super_admin'
      
      let organization_id
      if (isSuperAdmin && bodyOrgId) {
        // Super Admin can create roles for any organization
        organization_id = parseInt(bodyOrgId, 10)
      } else if (userOrgId) {
        // Regular users must use their own organization_id
        organization_id = parseInt(userOrgId, 10)
      } else {
        // Fallback to default
        organization_id = 1
      }
      
      // Ensure organization_id is a valid number
      if (isNaN(organization_id) || organization_id <= 0) {
        organization_id = userOrgId ? parseInt(userOrgId, 10) : 1
      }

      console.log(`📥 Create Role Request:`, {
        name: name.trim(),
        bodyOrgId,
        userOrgId,
        userRole: userRoleName,
        isSuperAdmin,
        finalOrgId: organization_id,
        userId: req.user?.id
      })

      // Validate required fields
      if (!name || !description || level === undefined) {
        return res.status(400).json({
          status: 'error',
          message: 'Name, description, and level are required'
        })
      }

      // Check for duplicate role name within the same organization (case-insensitive)
      const trimmedName = name.trim()
      console.log(`🔍 Checking for duplicate role: name="${trimmedName}", organization_id=${organization_id}`)
      
      const { pool } = require('../config/database')
      
      // Step 1: Check against roles table with standard query
      const [duplicateCheck] = await pool.execute(
        'SELECT id, name, organization_id FROM roles WHERE LOWER(TRIM(name)) = LOWER(TRIM(?)) AND organization_id = ? AND is_active = 1',
        [trimmedName, organization_id]
      )
      
      if (duplicateCheck && duplicateCheck.length > 0) {
        console.log(`❌ Duplicate role found in roles table:`, duplicateCheck)
        return res.status(400).json({
          status: 'error',
          message: `Duplicate role name: Role with name "${trimmedName}" already exists in your organization. Please use a different name.`
        })
      }
      
      // Step 2: Check against organization's role_permissions (roles that are shown in the list)
      try {
        const Organization = require('../models/Organization')
        const org = await Organization.findById(organization_id)
        
        if (org && org.role_permissions) {
          let roleIdsFromPermissions = []
          
          // Parse role_permissions
          if (Array.isArray(org.role_permissions)) {
            roleIdsFromPermissions = org.role_permissions.map(r => parseInt(String(r), 10)).filter(id => !isNaN(id))
          } else if (typeof org.role_permissions === 'string') {
            try {
              const parsed = JSON.parse(org.role_permissions)
              if (Array.isArray(parsed)) {
                roleIdsFromPermissions = parsed.map(r => parseInt(String(r), 10)).filter(id => !isNaN(id))
              }
            } catch {
              roleIdsFromPermissions = org.role_permissions.split(',').map(r => parseInt(r.trim(), 10)).filter(id => !isNaN(id))
            }
          }
          
          console.log(`🔍 Checking against organization's role_permissions:`, {
            organization_id,
            roleIdsFromPermissions,
            count: roleIdsFromPermissions.length
          })
          
          // Check if any role in role_permissions has the same name
          if (roleIdsFromPermissions.length > 0) {
            const placeholders = roleIdsFromPermissions.map(() => '?').join(',')
            const [rolesInPermissions] = await pool.execute(
              `SELECT id, name, organization_id FROM roles WHERE id IN (${placeholders}) AND LOWER(TRIM(name)) = LOWER(TRIM(?)) AND is_active = 1`,
              [...roleIdsFromPermissions, trimmedName]
            )
            
            if (rolesInPermissions && rolesInPermissions.length > 0) {
              console.log(`❌ Duplicate role found in organization's role_permissions:`, rolesInPermissions)
              return res.status(400).json({
                status: 'error',
                message: `Duplicate role name: Role with name "${trimmedName}" already exists in the role list. Please use a different name.`
              })
            }
          }
        }
      } catch (orgCheckError) {
        console.warn('⚠️ Could not check organization role_permissions:', orgCheckError.message)
        // Continue with role creation even if org check fails
      }

      console.log(`✅ No duplicate found. Creating role: name="${trimmedName}", organization_id=${organization_id}`)

      // Ensure composite unique constraint exists
      await Role.ensureUniqueIndex()

      const role = await Role.create({ name: trimmedName, organization_id, description, level, permissions })

      res.status(201).json({
        status: 'success',
        message: 'Role created successfully',
        data: {
          role
        }
      })
    } catch (error) {
      // Duplicate check removed - allow duplicate role names
      next(error)
    }
  }

  // Update role
  static async updateRole (req, res, next) {
    try {
      const { id } = req.params
      const { name, description, level, permissions = [], organization_id: bodyOrgId } = req.body
      
      // Determine organization_id: body > user > default
      let organization_id = bodyOrgId
      if (!organization_id && req.user?.organization_id) {
        organization_id = req.user.organization_id
      }
      if (!organization_id) {
        organization_id = 1
      }
      
      // Ensure organization_id is a number
      organization_id = parseInt(organization_id, 10)
      if (isNaN(organization_id)) {
        organization_id = 1
      }

      console.log(`📥 Update Role Request:`, {
        roleId: id,
        name,
        bodyOrgId,
        userOrgId: req.user?.organization_id,
        finalOrgId: organization_id,
        userId: req.user?.id
      })

      // Validate required fields
      if (!name || !description || level === undefined) {
        return res.status(400).json({
          status: 'error',
          message: 'Name, description, and level are required'
        })
      }

      // Check if role exists (first by ID only, then verify organization)
      const existingRole = await Role.findById(id, null) // Find by ID only first
      if (!existingRole) {
        console.log(`❌ Role not found: id=${id}`)
        return res.status(404).json({
          status: 'error',
          message: 'Role not found'
        })
      }

      // Verify organization_id matches (unless user is super admin)
      const userRoleName = (req.user?.role || req.user?.role_name || '').toString().toLowerCase()
      const isSuperAdmin = userRoleName === 'super admin' || userRoleName === 's_admin' || req.user?.role_id === 1
      
      if (!isSuperAdmin && existingRole.organization_id !== organization_id) {
        console.log(`❌ Role organization mismatch: role.org_id=${existingRole.organization_id}, requested.org_id=${organization_id}`)
        return res.status(403).json({
          status: 'error',
          message: 'You do not have permission to update this role'
        })
      }

      // Use the existing role's organization_id if not super admin
      if (!isSuperAdmin) {
        organization_id = existingRole.organization_id
      }

      // Check for duplicate role name within the same organization (excluding current role)
      const trimmedName = name.trim()
      const duplicateRole = await Role.findByName(trimmedName, organization_id)
      if (duplicateRole && duplicateRole.id !== parseInt(id, 10)) {
        return res.status(400).json({
          status: 'error',
          message: 'Role with this name already exists in your organization'
        })
      }

      console.log(`✅ Updating role: id=${id}, name="${trimmedName}", organization_id=${organization_id}`)

      // Ensure composite unique constraint exists
      await Role.ensureUniqueIndex()

      const role = await Role.update(id, { name: trimmedName, description, level, permissions })

      res.status(200).json({
        status: 'success',
        message: 'Role updated successfully',
        data: {
          role
        }
      })
    } catch (error) {
      // Duplicate check removed - allow duplicate role names
      next(error)
    }
  }

  // Delete role
  static async deleteRole (req, res, next) {
    try {
      const { id } = req.params
      const organization_id = req.user?.organization_id

      // Check if role exists
      const existingRole = await Role.findById(id, organization_id)
      if (!existingRole) {
        return res.status(404).json({
          status: 'error',
          message: 'Role not found'
        })
      }

      // Check if role is being used by any users
      let userQuery = 'SELECT COUNT(*) as count FROM users WHERE role_id = ? AND is_active = 1'
      const userParams = [id]

      if (organization_id) {
        userQuery += ' AND organization_id = ?'
        userParams.push(organization_id)
      }

      const [users] = await pool.execute(userQuery, userParams)

      if (users[0].count > 0) {
        return res.status(400).json({
          status: 'error',
          message: 'Cannot delete role that is assigned to users'
        })
      }

      await Role.delete(id)

      res.status(200).json({
        status: 'success',
        message: 'Role deleted successfully'
      })
    } catch (error) {
      next(error)
    }
  }

  // Get all roles with permissions (for admin interface)
  static async getRolesWithPermissions (req, res, next) {
    try {
      const organization_id = req.user?.organization_id
      const userRoleName = (req.user?.role || req.user?.role_name || '').toString().toLowerCase()

      // Super Admin can see all organizations' roles
      const isSuperAdmin = userRoleName === 'super admin' || userRoleName === 's_admin' || req.user?.role_id === 1
      
      let roles = []
      
      if (isSuperAdmin) {
        // Super Admin sees all active roles from all organizations
        // Query: SELECT * FROM roles WHERE is_active = 1 ORDER BY id ASC
        const { pool } = require('../config/database')
        const query = 'SELECT * FROM roles WHERE is_active = 1 ORDER BY id ASC'
        const [rows] = await pool.execute(query)
        // Get permissions for each role
        const rolesWithPermissions = await Promise.all(rows.map(async (row) => {
          const [permRows] = await pool.execute(`
            SELECT p.id, p.name 
            FROM permissions p
            INNER JOIN role_permissions rp ON p.id = rp.permission_id
            WHERE rp.role_id = ? AND p.is_active = 1
          `, [row.id])
          
          const permissions = permRows.map(p => p.name)
          const permission_ids = permRows.map(p => p.id)
          
          // Get organization info
          let organization_name = null
          let organization_code = null
          if (row.organization_id) {
            const [orgRows] = await pool.execute(
              'SELECT name, code FROM organizations WHERE id = ?',
              [row.organization_id]
            )
            if (orgRows.length > 0) {
              organization_name = orgRows[0].name
              organization_code = orgRows[0].code
            }
          }
          
          return {
            id: row.id,
            name: row.name,
            organization_id: row.organization_id,
            description: row.description,
            level: row.level,
            is_active: row.is_active,
            created_at: row.created_at,
            updated_at: row.updated_at,
            permissions,
            permission_ids,
            organization_name,
            organization_code
          }
        }))
        roles = rolesWithPermissions
        console.log(`🔍 Super Admin accessing roles with permissions - showing all ${roles.length} active roles from all organizations (ordered by id ASC)`)
      } else if (organization_id) {
        // For regular users: Get roles from organization_id AND role_permissions
        // a array: roles WHERE organization_id = user.organization_id
        const orgRoles = await Role.findAllWithPermissions(organization_id)
        
        // b array: Get organization's role_permissions and fetch those roles
        const Organization = require('../models/Organization')
        const org = await Organization.findById(organization_id)
        
        let roleIdsFromPermissions = []
        if (org && org.role_permissions) {
          if (Array.isArray(org.role_permissions)) {
            roleIdsFromPermissions = org.role_permissions.map(r => parseInt(String(r), 10)).filter(id => !isNaN(id))
          } else if (typeof org.role_permissions === 'string') {
            try {
              const parsed = JSON.parse(org.role_permissions)
              if (Array.isArray(parsed)) {
                roleIdsFromPermissions = parsed.map(r => parseInt(String(r), 10)).filter(id => !isNaN(id))
              }
            } catch {
              roleIdsFromPermissions = org.role_permissions.split(',').map(r => parseInt(r.trim(), 10)).filter(id => !isNaN(id))
            }
          }
        }
        
        // Get roles from role_permissions IDs
        let permissionRoles = []
        if (roleIdsFromPermissions.length > 0) {
          const allRoles = await Role.findAllWithPermissions(null)
          permissionRoles = allRoles.filter(role => roleIdsFromPermissions.includes(role.id))
        }
        
        // Merge orgRoles and permissionRoles (union)
        const rolesMap = new Map()
        orgRoles.forEach(role => {
          rolesMap.set(role.id, { ...role, is_from_permissions: false })
        })
        permissionRoles.forEach(role => {
          rolesMap.set(role.id, { ...role, is_from_permissions: true })
        })
        
        roles = Array.from(rolesMap.values())
        
        console.log('📋 Roles for organization user:', {
          organization_id,
          orgRolesCount: orgRoles.length,
          permissionRolesCount: permissionRoles.length,
          finalRolesCount: roles.length
        })
      } else {
        roles = []
      }

      res.status(200).json({
        status: 'success',
        data: {
          roles
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Get all permissions with roles (for admin interface)
  static async getPermissionsWithRoles (req, res, next) {
    try {
      const permissions = await Role.getAllPermissionsWithRoles()

      res.status(200).json({
        status: 'success',
        data: {
          permissions
        }
      })
    } catch (error) {
      next(error)
    }
  }

  // Create new permission
  static async createPermission (req, res, next) {
    try {
      const { name, description, resource, action } = req.body

      // Build a base name (normalize to lowercase, replace spaces/non-word with dot)
      const normalizedFromParts = `${(resource || '').trim()}.${(action || '').trim()}`
      const provided = (name || '').trim()
      const rawBase = provided.length > 0 ? provided : normalizedFromParts
      const baseName = rawBase
        .toLowerCase()
        .replace(/[\s]+/g, '.')
        .replace(/[^a-z0-9._-]/g, '.')
        .replace(/\.+/g, '.') // collapse multiple dots
        .replace(/(^\.)|(\.$)/g, '') // trim leading/trailing dots

      if (!baseName || baseName === '.') {
        return res.status(400).json({
          status: 'error',
          message: 'Permission name or (resource + action) is required'
        })
      }

      // Idempotency by name: if a permission with this name exists, return/restore it
      const [byExactName] = await pool.execute(
        'SELECT * FROM permissions WHERE LOWER(name) = LOWER(?) LIMIT 1',
        [baseName]
      )
      if (Array.isArray(byExactName) && byExactName.length > 0) {
        const existingPerm = byExactName[0]
        if (existingPerm.is_active) {
          return res.status(200).json({
            status: 'success',
            message: 'Permission already exists',
            data: { permission: existingPerm }
          })
        } else {
          // Reactivate and update metadata to caller’s intent
          await pool.execute(
            'UPDATE permissions SET is_active = 1, description = ?, resource = ?, action = ?, updated_at = NOW() WHERE id = ?',
            [description, resource, action, existingPerm.id]
          )
          const [reactivated] = await pool.execute('SELECT * FROM permissions WHERE id = ?', [existingPerm.id])
          return res.status(200).json({
            status: 'success',
            message: 'Permission reactivated',
            data: { permission: reactivated[0] }
          })
        }
      }

      // Compute next unique suffix using current rows; also consider hyphen/dot variants
      const hyphenVariant = baseName.replace(/\./g, '-')
      const [conflicts] = await pool.execute(
        'SELECT name FROM permissions WHERE LOWER(name) = LOWER(?) OR LOWER(name) = LOWER(?) OR LOWER(name) LIKE CONCAT(LOWER(?), "-%") OR LOWER(name) LIKE CONCAT(LOWER(?), "-%")',
        [baseName, hyphenVariant, baseName, hyphenVariant]
      )
      const existing = new Set(conflicts.map((r) => (r.name || '').toLowerCase().trim()))

      const chooseUnique = (candidateBase) => {
        if (!existing.has(candidateBase.toLowerCase())) return candidateBase
        let i = 2
        while (existing.has(`${candidateBase}-${i}`.toLowerCase())) i++
        return `${candidateBase}-${i}`
      }

      // Prefer the exact normalized base first
      let finalName = chooseUnique(baseName)
      // If still collides with a race, we will retry insert with increment below

      // If DB has a unique index on (resource, action), treat duplicate as idempotent success
      const [existingByRA] = await pool.execute(
        'SELECT * FROM permissions WHERE LOWER(resource) = LOWER(?) AND LOWER(action) = LOWER(?) LIMIT 1',
        [resource, action]
      )
      if (Array.isArray(existingByRA) && existingByRA.length > 0) {
        return res.status(200).json({
          status: 'success',
          message: 'Permission already exists',
          data: {
            permission: existingByRA[0]
          }
        })
      }

      // Attempt insert with up to 5 retries in case of a race on the unique index
      let result
      for (let attempt = 0; attempt < 5; attempt++) {
        try {
          // eslint-disable-next-line no-await-in-loop
          result = await pool.execute(
            'INSERT INTO permissions (name, description, resource, action, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, 1, NOW(), NOW())',
            [finalName, description, resource, action]
          )
          break
        } catch (insertErr) {
          const msg = String(insertErr.message || '')
          if (!msg.includes('Duplicate entry')) {
            throw insertErr
          }
          // collision; increment and retry
          const m = finalName.match(/^(.*?)-(\d+)$/)
          if (m) {
            const base = m[1]
            const n = parseInt(m[2], 10) + 1
            finalName = `${base}-${n}`
          } else {
            finalName = `${finalName}-2`
          }
        }
      }

      if (!result) {
        // Failed after retries; detect if resource/action unique constraint blocks it
        const [ra] = await pool.execute(
          'SELECT id FROM permissions WHERE LOWER(resource) = LOWER(?) AND LOWER(action) = LOWER(?) LIMIT 1',
          [resource, action]
        )
        if (Array.isArray(ra) && ra.length > 0) {
          return res.status(400).json({
            status: 'error',
            message: 'Permission with this resource and action already exists'
          })
        }
        // Fallback generic error
        return res.status(400).json({
          status: 'error',
          message: 'Unable to create permission due to a uniqueness constraint'
        })
      }

      const permissionId = result[0].insertId

      // Get the created permission
      const [permissions] = await pool.execute(
        'SELECT * FROM permissions WHERE id = ?',
        [permissionId]
      )

      res.status(201).json({
        status: 'success',
        message: 'Permission created successfully',
        data: {
          permission: permissions[0]
        }
      })
    } catch (error) {
      // Normalize duplicate errors to user-friendly message
      if (String(error.message).includes('Duplicate entry')) {
        // Try to determine whether name or resource/action is the cause
        if (name) {
          // Check for name duplicate
          try {
            const [byName] = await pool.execute('SELECT id FROM permissions WHERE name = ? LIMIT 1', [name])
            if (Array.isArray(byName) && byName.length > 0) {
              return res.status(400).json({
                status: 'error',
                message: 'Permission with this name already exists'
              })
            }
          } catch {}
        }
        try {
          const [ra] = await pool.execute('SELECT id FROM permissions WHERE LOWER(resource) = LOWER(?) AND LOWER(action) = LOWER(?) LIMIT 1', [resource, action])
          if (Array.isArray(ra) && ra.length > 0) {
            return res.status(400).json({
              status: 'error',
              message: 'Permission with this resource and action already exists'
            })
          }
        } catch {}
        return res.status(400).json({
          status: 'error',
          message: 'Duplicate permission detected'
        })
      }
      next(error)
    }
  }

  // Update permission
  static async updatePermission (req, res, next) {
    try {
      const { id } = req.params
      const { name, description, resource, action } = req.body

      // Check if permission exists
      const [existingPermissions] = await pool.execute(
        'SELECT id FROM permissions WHERE id = ?',
        [id]
      )

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

      // Check if name is already taken by another permission
      const [duplicatePermissions] = await pool.execute(
        'SELECT id FROM permissions WHERE name = ? AND id != ?',
        [name, id]
      )

      if (duplicatePermissions.length > 0) {
        return res.status(400).json({
          status: 'error',
          message: 'Permission with this name already exists'
        })
      }

      // Update permission
      await pool.execute(
        'UPDATE permissions SET name = ?, description = ?, resource = ?, action = ?, updated_at = NOW() WHERE id = ?',
        [name, description, resource, action, id]
      )

      // Get the updated permission
      const [permissions] = await pool.execute(
        'SELECT * FROM permissions WHERE id = ?',
        [id]
      )

      res.status(200).json({
        status: 'success',
        message: 'Permission updated successfully',
        data: {
          permission: permissions[0]
        }
      })
    } catch (error) {
      if (String(error.message).includes('Duplicate entry')) {
        return res.status(400).json({
          status: 'error',
          message: 'Permission with this name already exists'
        })
      }
      next(error)
    }
  }

  // Delete permission
  static async deletePermission (req, res, next) {
    try {
      const { id } = req.params

      // Check if permission exists
      const [existingPermissions] = await pool.execute(
        'SELECT id FROM permissions WHERE id = ?',
        [id]
      )

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

      // Check if permission is being used by any roles
      const [rolePermissions] = await pool.execute(
        'SELECT COUNT(*) as count FROM role_permissions WHERE permission_id = ?',
        [id]
      )

      if (rolePermissions[0].count > 0) {
        return res.status(400).json({
          status: 'error',
          message: 'Cannot delete permission that is assigned to roles. Please remove it from all roles first.'
        })
      }

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

      res.status(200).json({
        status: 'success',
        message: 'Permission deleted successfully'
      })
    } catch (error) {
      next(error)
    }
  }

  // Get permission by ID
  static async getPermissionById (req, res, next) {
    try {
      const { id } = req.params

      const [permissions] = await pool.execute(
        'SELECT * FROM permissions WHERE id = ?',
        [id]
      )

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

      res.status(200).json({
        status: 'success',
        data: {
          permission: permissions[0]
        }
      })
    } catch (error) {
      next(error)
    }
  }
}

module.exports = RoleController
