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

class District {
  constructor (data) {
    this.id = data.id
    this.division_id = data.division_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.division_name = data.division_name
  }

  // Create a new district
  static async create (districtData) {
    const { division_id, name, code } = districtData

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

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

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

  // Find district by ID
  static async findById (id) {
    const query = `
      SELECT d.*, dv.name as division_name
      FROM districts d
      LEFT JOIN divisions dv ON d.division_id = dv.id
      WHERE d.id = ? AND d.is_active = 1
    `

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

  // Get all districts
  static async findAll (options = {}) {
    const {
      page = 1,
      limit = 50,
      search = '',
      division_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.*, dv.name as division_name
      FROM districts d
      LEFT JOIN divisions dv ON d.division_id = dv.id
      WHERE ${include_inactive ? '1=1' : 'd.is_active = 1'}
    `

    const conditions = []
    const values = []

    if (division_id) {
      conditions.push('d.division_id = ?')
      values.push(division_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 districts 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 {
        districts: rows.map(row => new District(row)),
        total: countResult[0].total
      }
    } catch (error) {
      throw new Error(`Error finding districts: ${error.message}`)
    }
  }

  // Get districts by division ID (for cascading dropdowns)
  static async findByDivisionId (divisionId) {
    const query = `
      SELECT d.*, dv.name as division_name
      FROM districts d
      LEFT JOIN divisions dv ON d.division_id = dv.id
      WHERE d.division_id = ? AND d.is_active = 1
      ORDER BY d.name ASC
    `

    try {
      const [rows] = await pool.execute(query, [divisionId])
      return rows.map(row => new District(row))
    } catch (error) {
      throw new Error(`Error finding districts by division: ${error.message}`)
    }
  }

  // Update district
  static async update (id, updateData) {
    const allowedFields = ['name', 'code', 'is_active', 'division_id']
    const fields = []
    const values = []

    for (const [key, value] of Object.entries(updateData)) {
      if (allowedFields.includes(key)) {
        fields.push(`${key} = ?`)
        values.push(value)
      }
    }

    if (fields.length === 0) return false

    values.push(id)

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

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

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

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

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

module.exports = District
