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

class Division {
  constructor (data) {
    this.id = data.id
    this.country_id = data.country_id
    this.name = data.name
    this.code = data.code
    this.is_active = data.is_active
    this.created_at = data.created_at
    this.updated_at = data.updated_at
    this.country_name = data.country_name
  }

  // Create a new division
  static async create (divisionData) {
    const { country_id, name, code } = divisionData

    const query = `
      INSERT INTO divisions (country_id, name, code)
      VALUES (?, ?, ?)
    `

    const values = [
      country_id,
      name,
      code || null
    ]

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

  // Find division by ID
  static async findById (id) {
    const query = `
      SELECT d.*, c.name as country_name
      FROM divisions d
      LEFT JOIN countries c ON d.country_id = c.id
      WHERE d.id = ? AND d.is_active = 1
    `

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

  // Get all divisions
  static async findAll (options = {}) {
    const {
      page = 1,
      limit = 50,
      search = '',
      country_id = null,
      include_inactive = false
    } = options

    const validPage = Math.max(1, parseInt(page) || 1)
    const validLimit = Math.min(Math.max(1, parseInt(limit) || 50), 1000)

    let query = `
      SELECT d.*, c.name as country_name
      FROM divisions d
      LEFT JOIN countries c ON d.country_id = c.id
      WHERE ${include_inactive ? '1=1' : 'd.is_active = 1'}
    `

    const conditions = []
    const values = []

    if (country_id) {
      conditions.push('d.country_id = ?')
      values.push(country_id)
    }

    if (search) {
      conditions.push('(d.name LIKE ? OR d.code LIKE ?)')
      values.push(`%${search}%`, `%${search}%`)
    }

    if (conditions.length > 0) {
      query += ' AND ' + conditions.join(' AND ')
    }

    query += ' ORDER BY d.name ASC'

    const offset = (validPage - 1) * validLimit
    query += ` LIMIT ${validLimit} OFFSET ${offset}`

    try {
      const [rows] = await pool.execute(query, values)
      const countQuery = `
        SELECT COUNT(*) as total
        FROM divisions d
        WHERE ${include_inactive ? '1=1' : 'd.is_active = 1'}
        ${conditions.length > 0 ? ' AND ' + conditions.join(' AND ') : ''}
      `
      const [countResult] = await pool.execute(countQuery, values)
      
      return {
        divisions: rows.map(row => new Division(row)),
        total: countResult[0].total
      }
    } catch (error) {
      throw new Error(`Error finding divisions: ${error.message}`)
    }
  }

  // Get divisions by country ID (for cascading dropdowns)
  static async findByCountryId (countryId) {
    const query = `
      SELECT d.*, c.name as country_name
      FROM divisions d
      LEFT JOIN countries c ON d.country_id = c.id
      WHERE d.country_id = ? AND d.is_active = 1
      ORDER BY d.name ASC
    `

    try {
      const [rows] = await pool.execute(query, [countryId])
      return rows.map(row => new Division(row))
    } catch (error) {
      throw new Error(`Error finding divisions by country: ${error.message}`)
    }
  }

  // Update division
  static async update (id, updateData) {
    const allowedFields = ['country_id', 'name', 'code', 'is_active']
    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 divisions 
      SET ${fields.join(', ')}, updated_at = CURRENT_TIMESTAMP
      WHERE id = ? AND is_active = 1
    `

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

  // Soft delete division
  static async delete (id) {
    try {
      // Check if division is being used in districts table
      const [districts] = await pool.execute(
        'SELECT COUNT(*) as count FROM districts WHERE division_id = ? AND is_active = 1',
        [id]
      )
      
      if (districts[0].count > 0) {
        throw new Error('Cannot delete division. It is currently being used by active districts. Please delete or deactivate all associated districts first.')
      }

      // Check if division is being used in contacts table
      const [contacts] = await pool.execute(
        `SELECT COUNT(*) as count FROM contacts 
         WHERE (present_division_id = ? OR office_division_id = ? OR permanent_division_id = ?) 
         AND is_active = 1`,
        [id, id, id]
      )
      
      if (contacts[0].count > 0) {
        throw new Error('Cannot delete division. It is currently being used by active contacts. Please update or deactivate all associated contacts first.')
      }

      const query = 'UPDATE divisions SET is_active = 0, updated_at = CURRENT_TIMESTAMP WHERE id = ?'
      const [result] = await pool.execute(query, [id])
      return result.affectedRows > 0
    } catch (error) {
      throw new Error(error.message || `Error deleting division: ${error.message}`)
    }
  }

  // Convert to JSON
  toJSON () {
    return {
      id: this.id,
      country_id: this.country_id,
      country_name: this.country_name,
      name: this.name,
      code: this.code,
      is_active: this.is_active,
      created_at: this.created_at,
      updated_at: this.updated_at
    }
  }
}

module.exports = Division

