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

class Thana {
  constructor (data) {
    this.id = data.id
    this.district_id = data.district_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.district_name = data.district_name
  }

  // Create a new thana
  static async create (thanaData) {
    const { district_id, name, code } = thanaData

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

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

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

  // Find thana by ID
  static async findById (id) {
    const query = `
      SELECT t.*, d.name as district_name
      FROM thanas t
      LEFT JOIN districts d ON t.district_id = d.id
      WHERE t.id = ? AND t.is_active = 1
    `

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

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

    const conditions = []
    const values = []

    if (district_id) {
      conditions.push('t.district_id = ?')
      values.push(district_id)
    }

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

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

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

  // Get thanas by district ID (for cascading dropdowns)
  static async findByDistrictId (districtId) {
    const query = `
      SELECT t.*, d.name as district_name
      FROM thanas t
      LEFT JOIN districts d ON t.district_id = d.id
      WHERE t.district_id = ? AND t.is_active = 1
      ORDER BY t.name ASC
    `

    try {
      const [rows] = await pool.execute(query, [districtId])
      return rows.map(row => new Thana(row))
    } catch (error) {
      throw new Error(`Error finding thanas by district: ${error.message}`)
    }
  }

  // Update thana
  static async update (id, updateData) {
    const allowedFields = ['name', 'code', 'is_active', 'district_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 thanas 
      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 thana: ${error.message}`)
    }
  }

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

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

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

module.exports = Thana
