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

class User {
  constructor (data) {
    this.id = data.id
    this.name = data.name
    this.organization_id = data.organization_id
    this.login_id = data.login_id
    this.role_id = data.role_id
    this.role = data.role || data.role_name
    this.role_name = data.role_name
    this.phone = data.phone
    this.email = data.email
    this.avatar = data.avatar
    this.logo = data.logo
    this.status = data.status
    this.password_hash = data.password_hash
    this.is_active = data.is_active
    this.is_login = data.is_login
    this.is_password_changed = data.is_password_changed
    this.created_at = data.created_at
    this.updated_at = data.updated_at
  }

  // Create a new user
  static async create (userData) {
    const {
      name,
      login_id,
      organization_id,
      role_id,
      phone,
      email,
      avatar,
      logo,
      status,
      password_hash,
      is_active,
      is_login,
      is_password_changed
    } = userData

    const safeOrganizationId = organization_id === undefined || organization_id === null ? 1 : organization_id
    const safeRoleId = role_id === undefined || role_id === null ? 3 : role_id
    const safePhone = phone === undefined || phone === null ? '' : phone
    const safeEmail = (email === undefined || email === null || String(email).trim() === '')
      ? null
      : String(email).trim()
    const safeStatus = status === undefined || status === null ? 'offline' : status
    const safeIsActive = (is_active === undefined || is_active === null) ? 1 : (is_active ? 1 : 0)
    const safeIsLogin = (is_login === undefined || is_login === null) ? (login_id ? 1 : 0) : (is_login ? 1 : 0)
    const safeIsPasswordChanged = (is_password_changed === undefined || is_password_changed === null) ? 0 : (is_password_changed ? 1 : 0)

    const query = `
      INSERT INTO users (
        name, login_id, organization_id, role_id,
        phone, email, avatar, logo, status,
        password_hash, is_active, is_login, is_password_changed,
        created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
    `

    const values = [
      name === undefined ? null : name,
      login_id === undefined ? null : login_id,
      safeOrganizationId,
      safeRoleId,
      safePhone,
      safeEmail,
      avatar === undefined ? null : avatar,
      logo === undefined ? null : logo,
      safeStatus,
      password_hash === undefined ? null : password_hash,
      safeIsActive,
      safeIsLogin,
      safeIsPasswordChanged
    ]

    try {
      const [result] = await pool.execute(query, values)
      return result.insertId
    } catch (error) {
      throw new Error(`Error creating user: ${error.message}`)
    }
  }

  // Find user by ID
  static async findById (id, organizationId = null) {
    let query = `
      SELECT 
        u.id, u.name, u.login_id, u.organization_id, u.role_id,
        u.phone, u.email, u.avatar, u.logo, u.status,
        u.password_hash, u.is_active, u.is_login, u.is_password_changed,
        u.created_at, u.updated_at
      FROM users u
      WHERE u.id = ?
    `

    const params = [id]

    if (organizationId) {
      query += ' AND u.organization_id = ?'
      params.push(organizationId)
    }

    try {
      const [rows] = await pool.execute(query, params)
      if (rows.length > 0) {
        return new User(rows[0])
      }
      return null
    } catch (error) {
      throw new Error(`Error finding user by ID: ${error.message}`)
    }
  }

  // Find user by login_id
  static async findByLoginId (loginId) {
    const query = `
      SELECT 
        u.id, u.name, u.login_id, u.organization_id, u.role_id,
        u.phone, u.email, u.avatar, u.logo, u.status,
        u.password_hash, u.is_active, u.is_login, u.is_password_changed,
        u.created_at, u.updated_at
      FROM users u
      WHERE u.login_id = ?
    `
    try {
      const [rows] = await pool.execute(query, [loginId])
      if (rows.length > 0) {
        return new User(rows[0])
      }
      return null
    } catch (error) {
      throw new Error(`Error finding user by login_id: ${error.message}`)
    }
  }

  // Find user by email (kept for backward compatibility)
  static async findByEmail (email) {
    const query = `
      SELECT 
        u.id, u.name, u.login_id, u.organization_id, u.role_id,
        u.phone, u.email, u.avatar, u.logo, u.status,
        u.password_hash, u.is_active, u.is_login, u.is_password_changed,
        u.created_at, u.updated_at
      FROM users u
      WHERE u.email = ?
    `
    try {
      const [rows] = await pool.execute(query, [email])
      if (rows.length > 0) {
        return new User(rows[0])
      }
      return null
    } catch (error) {
      throw new Error(`Error finding user by email: ${error.message}`)
    }
  }

  // Find user by service number
  static async findByServiceNo (service_no) {
    const query = `
      SELECT 
        u.id, u.name, u.login_id, u.organization_id, u.role_id,
        u.phone, u.email, u.avatar, u.logo, u.status,
        u.password_hash, u.is_active, u.is_login, u.is_password_changed,
        u.created_at, u.updated_at
      FROM users u
      WHERE u.service_no = ?
    `
    try {
      const [rows] = await pool.execute(query, [service_no])
      if (rows.length > 0) {
        return new User(rows[0])
      }
      return null
    } catch (error) {
      throw new Error(`Error finding user by service number: ${error.message}`)
    }
  }

  // Find user by exact name (for fallback when login_id/email/service_no not found)
  static async findByName (name) {
    const query = `
      SELECT 
        u.id, u.name, u.login_id, u.organization_id, u.role_id,
        u.phone, u.email, u.avatar, u.logo, u.status,
        u.password_hash, u.is_active, u.is_login, u.is_password_changed,
        u.created_at, u.updated_at
      FROM users u
      WHERE u.name = ?
      LIMIT 1
    `
    try {
      const [rows] = await pool.execute(query, [name])
      if (rows.length > 0) {
        return new User(rows[0])
      }
      return null
    } catch (error) {
      throw new Error(`Error finding user by name: ${error.message}`)
    }
  }

  // Find users by parent_id
  static async findByParentId (_parentId) {
    return []
  }

  // Get all users with pagination
  static async findAll (options = {}) {
    try {
      const {
        page = 1,
        limit = 50,
        search = '',
        role_id = '',
        parent_id,
        organization_id = null,
        include_inactive = false
      } = options

      // Ensure page and limit are integers
      const pageNum = parseInt(page) || 1
      const limitNum = parseInt(limit) || 50
      const offset = (pageNum - 1) * limitNum

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

      // Base condition for active users
      if (!include_inactive) {
        whereClauses.push('u.is_active = 1')
      }

      // Organization filter
      if (organization_id) {
        whereClauses.push('u.organization_id = ?')
        queryParams.push(organization_id)
      }

      // Search condition
      if (search && search.trim() !== '') {
        whereClauses.push('(u.name LIKE ? OR u.email LIKE ? OR u.phone LIKE ?)')
        const searchTerm = `%${search}%`
        queryParams.push(searchTerm, searchTerm, searchTerm)
      }

      // Role filter
      if (role_id && role_id !== '') {
        whereClauses.push('u.role_id = ?')
        queryParams.push(parseInt(role_id))
      }

      // Parent filter
      if (parent_id !== undefined && parent_id !== null && parent_id !== '') {
        if (parent_id === 'null') {
          whereClauses.push('u.parent_id IS NULL')
        } else {
          whereClauses.push('u.parent_id = ?')
          queryParams.push(parseInt(parent_id))
        }
      }

      // Build WHERE clause
      const whereSQL = whereClauses.length > 0 ? 'WHERE ' + whereClauses.join(' AND ') : ''

      // Build final query
      const query = `
        SELECT 
          u.id, u.name, u.organization_id, u.login_id,
          u.role_id, u.phone,
          u.email, u.avatar, u.logo, u.status,
          u.password_hash, u.is_active, u.is_login, u.is_password_changed,
          u.created_at, u.updated_at,
          r.name as role_name,
          r.level as role_level,
          o.name as organization_name
        FROM users u
        LEFT JOIN roles r ON u.role_id = r.id
        LEFT JOIN organizations o ON u.organization_id = o.id
        ${whereSQL}
        ORDER BY u.created_at DESC
        LIMIT ${limitNum} OFFSET ${offset}
      `

      const [rows] = await pool.execute(query, queryParams)

      return rows.map(row => {
        const userData = { ...row, role: row.role_name }
        return new User(userData)
      })
    } catch (error) {
      throw new Error(`Error finding users: ${error.message}`)
    }
  }

  // Update user
  async update (updateData) {
    const allowedFields = [
      'name', 'phone', 'email', 'role_id', 'organization_id', 'login_id', 'avatar', 'logo', 'status', 'is_active', 'is_login', 'is_password_changed', 'password_hash'
    ]

    const fields = []
    const values = []

    Object.keys(updateData).forEach(key => {
      if (allowedFields.includes(key) && updateData[key] !== undefined) {
        fields.push(`${key} = ?`)
        values.push(updateData[key])
      }
    })

    if (fields.length === 0) {
      throw new Error('No valid fields to update')
    }

    values.push(this.id)

    const query = `UPDATE users SET ${fields.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`

    try {
      const [result] = await pool.execute(query, values)
      if (result.affectedRows > 0) {
        // Update local instance
        Object.assign(this, updateData)
        return true
      }
      return false
    } catch (error) {
      throw new Error(`Error updating user: ${error.message}`)
    }
  }

  // Update password
  async updatePassword (newPasswordHash) {
    const query = 'UPDATE users SET password_hash = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?'
    try {
      const [result] = await pool.execute(query, [newPasswordHash, this.id])
      if (result.affectedRows > 0) {
        this.password_hash = newPasswordHash
        return true
      }
      return false
    } catch (error) {
      throw new Error(`Error updating password: ${error.message}`)
    }
  }

  async markPasswordChanged () {
    const query = 'UPDATE users SET is_password_changed = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?'
    try {
      const [result] = await pool.execute(query, [this.id])
      if (result.affectedRows > 0) {
        this.is_password_changed = 1
        return true
      }
      return false
    } catch (error) {
      throw new Error(`Error marking password as changed: ${error.message}`)
    }
  }

  // Soft delete user
  async delete () {
    const query = 'UPDATE users SET is_active = 0, updated_at = CURRENT_TIMESTAMP WHERE id = ?'
    try {
      const [result] = await pool.execute(query, [this.id])
      if (result.affectedRows > 0) {
        this.is_active = 0
        return true
      }
      return false
    } catch (error) {
      throw new Error(`Error deleting user: ${error.message}`)
    }
  }

  // Static update method
  static async update (id, updateData) {
    const allowedFields = [
      'name', 'phone', 'email', 'role_id', 'organization_id', 'login_id', 'avatar', 'logo', 'status', 'is_active', 'is_login', 'is_password_changed', 'password_hash'
    ]

    const fields = []
    const values = []

    Object.keys(updateData).forEach(key => {
      if (allowedFields.includes(key) && updateData[key] !== undefined) {
        fields.push(`${key} = ?`)
        values.push(updateData[key])
      }
    })

    if (fields.length === 0) {
      throw new Error('No valid fields to update')
    }

    values.push(id)

    const query = `UPDATE users SET ${fields.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`

    try {
      const [result] = await pool.execute(query, values)
      return result.affectedRows > 0
    } catch (error) {
      throw new Error(`Error updating user: ${error.message}`)
    }
  }

  // Static delete method
  static async delete (id) {
    const query = 'UPDATE users SET is_active = 0, updated_at = CURRENT_TIMESTAMP WHERE id = ?'
    try {
      const [result] = await pool.execute(query, [id])
      return result.affectedRows > 0
    } catch (error) {
      throw new Error(`Error deleting user: ${error.message}`)
    }
  }

  // Get user statistics
  static async getStatistics () {
    const query = `
      SELECT 
        COUNT(*) as total_users,
        SUM(CASE WHEN status = 'online' THEN 1 ELSE 0 END) as online_users,
        SUM(CASE WHEN status = 'offline' THEN 1 ELSE 0 END) as offline_users,
        SUM(CASE WHEN status = 'busy' THEN 1 ELSE 0 END) as busy_users,
        SUM(CASE WHEN role_id = 1 THEN 1 ELSE 0 END) as super_admins,
        SUM(CASE WHEN role_id = 2 THEN 1 ELSE 0 END) as admins,
        SUM(CASE WHEN role_id = 3 THEN 1 ELSE 0 END) as regular_users
      FROM users 
      WHERE is_active = 1
    `

    try {
      const [rows] = await pool.execute(query)
      return rows[0]
    } catch (error) {
      throw new Error(`Error getting user statistics: ${error.message}`)
    }
  }

  // Get users by unit
  static async getByUnit (_unit_id) {
    const query = `
      SELECT 
        id, name, login_id, organization_id, role_id,
        phone, email, avatar, logo, status,
        password_hash, is_active, is_login, is_password_changed,
        created_at, updated_at
      FROM users
      WHERE is_active = 1
      ORDER BY id ASC
    `
    try {
      const [rows] = await pool.execute(query)
      return rows.map(row => new User(row))
    } catch (error) {
      throw new Error(`Error getting users by unit: ${error.message}`)
    }
  }

  // Get users by role
  static async getByRole (role_id) {
    const query = `
      SELECT 
        id, name, login_id, organization_id, role_id,
        phone, email, avatar, logo, status,
        password_hash, is_active, is_login, is_password_changed,
        created_at, updated_at
      FROM users
      WHERE role_id = ? AND is_active = 1
      ORDER BY id ASC
    `
    try {
      const [rows] = await pool.execute(query, [role_id])
      return rows.map(row => new User(row))
    } catch (error) {
      throw new Error(`Error getting users by role: ${error.message}`)
    }
  }

  // Check if the user has changed their password based on users table flag
  static async hasPasswordChanged (userId) {
    // Check if column exists first by trying to query it
    const query = 'SELECT is_password_changed FROM users WHERE id = ? LIMIT 1'

    try {
      const [rows] = await pool.execute(query, [userId])
      if (rows.length === 0) {
        return false
      }

      // Check if column exists in result
      if (!('is_password_changed' in rows[0])) {
        // Column doesn't exist, return false (default behavior)
        return false
      }

      const value = rows[0].is_password_changed
      const numericValue = Number(value)
      if (!Number.isNaN(numericValue)) {
        return numericValue === 1
      }

      return value === true
    } catch (error) {
      // If column doesn't exist or other database error, return false
      if (error.code === 'ER_BAD_FIELD_ERROR' || error.message.includes('Unknown column')) {
        console.warn('is_password_changed column does not exist in users table. Returning false.')
        return false
      }
      // For other errors, log and return false to not break login flow
      console.error('Error checking password change status:', error.message)
      return false
    }
  }

  // Convert to JSON (exclude sensitive data)
  toJSON () {
    // eslint-disable-next-line no-unused-vars
    const { password_hash, ...userWithoutPassword } = this
    return userWithoutPassword
  }
}

module.exports = User
