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

class Country {
  constructor (data) {
    this.id = data.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
  }

  // Create a new country
  static async create (countryData) {
    const { name, code } = countryData

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

    const values = [
      name,
      code || null
    ]

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

  // Find country by ID
  static async findById (id) {
    const query = `
      SELECT * FROM countries
      WHERE id = ? AND is_active = 1
    `

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

  // Get all countries
  static async findAll (options = {}) {
    const {
      page = 1,
      limit = 50,
      search = '',
      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 * FROM countries
      WHERE ${include_inactive ? '1=1' : 'is_active = 1'}
    `

    const conditions = []
    const values = []

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

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

    query += ' ORDER BY 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 countries
        WHERE ${include_inactive ? '1=1' : 'is_active = 1'}
        ${conditions.length > 0 ? ' AND ' + conditions.join(' AND ') : ''}
      `
      const [countResult] = await pool.execute(countQuery, values)
      
      return {
        countries: rows.map(row => new Country(row)),
        total: countResult[0].total
      }
    } catch (error) {
      throw new Error(`Error finding countries: ${error.message}`)
    }
  }

  // Update country
  static async update (id, updateData) {
    const allowedFields = ['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 countries 
      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 country: ${error.message}`)
    }
  }

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

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

      const query = 'UPDATE countries 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 country: ${error.message}`)
    }
  }

  // Get country statistics
  static async getStatistics () {
    try {
      const query = `
        SELECT 
          COUNT(*) as total_countries,
          (SELECT COUNT(*) FROM divisions WHERE is_active = 1) as total_divisions,
          (SELECT COUNT(*) FROM districts WHERE is_active = 1) as total_districts,
          (SELECT COUNT(*) FROM thanas WHERE is_active = 1) as total_thanas
        FROM countries 
        WHERE is_active = 1
      `

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

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

module.exports = Country

