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

class GroupContact {
  static async ensureContactForeignKey () {
    try {
      const [rows] = await pool.execute(`
        SELECT CONSTRAINT_NAME, REFERENCED_TABLE_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'group_contact_mappings'
          AND COLUMN_NAME = 'contact_id'
          AND REFERENCED_TABLE_NAME IS NOT NULL
      `)

      if (rows.length > 0) {
        const ref = rows[0]
        if (ref.REFERENCED_TABLE_NAME.toLowerCase() !== 'contacts') {
          const constraintName = ref.CONSTRAINT_NAME
          await pool.execute(`ALTER TABLE group_contact_mappings DROP FOREIGN KEY \`${constraintName}\``)
          await pool.execute(`
            ALTER TABLE group_contact_mappings
            ADD CONSTRAINT fk_group_contact_mappings_contact_id
            FOREIGN KEY (contact_id) REFERENCES contacts(id)
            ON UPDATE CASCADE ON DELETE CASCADE
          `)
        }
      } else {
        await pool.execute(`
          ALTER TABLE group_contact_mappings
          ADD CONSTRAINT fk_group_contact_mappings_contact_id
          FOREIGN KEY (contact_id) REFERENCES contacts(id)
          ON UPDATE CASCADE ON DELETE CASCADE
        `)
      }
    } catch (err) {
      console.warn('ensureContactForeignKey failed:', err.message)
    }
  }

  static async addMappings (mappings) {
    if (!mappings || mappings.length === 0) return []
    await this.ensureContactForeignKey()
    const sql = `
      INSERT INTO group_contact_mappings (organization_id, department_id, contact_id, created_at)
      VALUES (?, ?, ?, CURRENT_TIMESTAMP)
      ON DUPLICATE KEY UPDATE
        organization_id = VALUES(organization_id),
        department_id = VALUES(department_id),
        contact_id = VALUES(contact_id),
        created_at = VALUES(created_at)
    `
    let inserted = 0
    let updated = 0
    for (const m of mappings) {
      const params = [Number(m.organization_id), m.department_id ? Number(m.department_id) : null, Number(m.contact_id)]
      const [res] = await pool.execute(sql, params)
      if (res.affectedRows === 1) inserted++
      else updated++
    }
    return { inserted, updated, total: mappings.length }
  }

  static async removeMappings (ids = []) {
    if (!ids.length) return 0
    const placeholders = ids.map(() => '?').join(',')
    const [res] = await pool.execute(`DELETE FROM group_contact_mappings WHERE id IN (${placeholders})`, ids)
    return res.affectedRows
  }

  static async getDepartmentContactTree (organization_id) {
    if (!organization_id) throw new Error('organization_id is required')

    const sql = `
      SELECT 
        dp.id AS department_id,
        dp.name AS department_name,
        dp.parent_id AS department_parent_id,
        dp.order_id AS department_order_id,
        c.id AS contact_id,
        c.name AS contact_name,
        c.email AS contact_email,
        c.mobile AS contact_mobile,
        c.phone AS contact_phone,
        c.logo AS contact_logo,
        c.avatar AS contact_avatar,
        c.is_active AS is_active,
        c.designation_id,
        des.name AS designation_name,
        c.order_id AS contact_order_id,
        c.time AS contact_time
      FROM group_contact_mappings gcm
      LEFT JOIN departments dp ON gcm.department_id = dp.id
      LEFT JOIN contacts c ON gcm.contact_id = c.id
      LEFT JOIN designations des ON c.designation_id = des.id
      WHERE gcm.organization_id = ?
        AND c.is_active = 1
        AND (gcm.department_id IS NULL OR (dp.id IS NOT NULL AND dp.is_active = 1))
      ORDER BY 
        CASE WHEN dp.parent_id IS NULL THEN dp.id ELSE dp.parent_id END,
        dp.parent_id, dp.order_id, c.order_id
    `

    const [rows] = await pool.execute(sql, [Number(organization_id)])

    const deptMap = new Map()
    const orgContacts = []

    for (const r of rows) {
      const did = r.department_id ? Number(r.department_id) : null

      if (!did) {
        if (r.contact_id) {
          orgContacts.push({
            id: Number(r.contact_id),
            name: r.contact_name,
            email: r.contact_email,
            mobile: r.contact_mobile,
            phone: r.contact_phone,
            logo: r.contact_logo || r.contact_avatar,
            designation_name: r.designation_name,
            order_id: r.contact_order_id || 0,
            time: r.contact_time || ''
          })
        }
        continue
      }

      if (!deptMap.has(did)) {
        deptMap.set(did, {
          id: did,
          name: r.department_name,
          parent_id: r.department_parent_id ? Number(r.department_parent_id) : null,
          order_id: r.department_order_id || 0,
          contacts: [],
          children: []
        })
      }

      if (r.contact_id) {
        deptMap.get(did).contacts.push({
          id: Number(r.contact_id),
          name: r.contact_name,
          email: r.contact_email,
          mobile: r.contact_mobile,
          phone: r.contact_phone,
          logo: r.contact_logo || r.contact_avatar,
          designation_name: r.designation_name,
          order_id: r.contact_order_id || 0,
          time: r.contact_time || ''
        })
      }
    }

    for (const d of deptMap.values()) {
      d.contacts.sort((a, b) => (a.order_id || 0) - (b.order_id || 0))
    }

    const nodes = Array.from(deptMap.values())
    const byId = new Map(nodes.map(n => [n.id, n]))
    const roots = []

    for (const n of nodes) {
      if (n.parent_id && byId.has(n.parent_id)) {
        byId.get(n.parent_id).children.push(n)
      } else {
        roots.push(n)
      }
    }

    if (orgContacts.length) {
      roots.unshift({ id: -1, name: 'Organization Contacts', contacts: orgContacts, children: [] })
    }

    return roots
  }

  static async findAll ({ page = 1, limit = 50, search = '', organization_id = '', department_id = '', contact_id = '' }) {
    const where = []
    const params = []
    if (organization_id) { where.push('gcm.organization_id = ?'); params.push(Number(organization_id)) }
    if (department_id !== '' && department_id !== null && department_id !== undefined) {
      if (department_id === 'null') { where.push('gcm.department_id IS NULL') } else { where.push('gcm.department_id = ?'); params.push(Number(department_id)) }
    }
    if (contact_id) { where.push('gcm.contact_id = ?'); params.push(Number(contact_id)) }
    if (search) {
      const s = `%${search}%`
      where.push('(c.name LIKE ? OR c.email LIKE ? OR c.mobile LIKE ? OR c.phone LIKE ? OR dp.name LIKE ?)')
      params.push(s, s, s, s, s)
    }
    const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : ''
    const countSql = `
      SELECT COUNT(*) AS total
      FROM group_contact_mappings gcm
      LEFT JOIN departments dp ON gcm.department_id = dp.id AND dp.is_active = 1
      LEFT JOIN contacts c ON gcm.contact_id = c.id AND c.is_active = 1
      ${whereSql}
    `
    const [countRows] = await pool.execute(countSql, params)
    const total = Number(countRows[0]?.total || 0)
    const offset = (Number(page) - 1) * Number(limit)
    const safeLimit = Math.max(Number(limit) || 50, 1)
    const safeOffset = Math.max(Number(offset) || 0, 0)
    const dataSql = `
      SELECT 
        gcm.id,
        gcm.organization_id,
        gcm.department_id,
        gcm.contact_id,
        dp.name AS department_name,
        dp.parent_id AS department_parent_id,
        dp.order_id AS department_order_id,
        c.is_active AS is_active,
        c.name AS contact_name,
        c.email AS contact_email,
        c.mobile AS contact_mobile,
        c.phone AS contact_phone,
        c.logo AS contact_logo,
        c.avatar AS contact_avatar,
        c.order_id AS contact_order_id,
        c.time AS contact_time
      FROM group_contact_mappings gcm
      LEFT JOIN departments dp ON gcm.department_id = dp.id AND dp.is_active = 1
      LEFT JOIN contacts c ON gcm.contact_id = c.id AND c.is_active = 1
      ${whereSql}
      ORDER BY dp.order_id, c.order_id, gcm.id DESC
      LIMIT ${safeLimit} OFFSET ${safeOffset}
    `
    const [rows] = await pool.execute(dataSql, params)
    const pagination = { page: Number(page), limit: Number(limit), total, pages: Math.max(Math.ceil(total / Number(limit)), 1) }
    return { mappings: rows, pagination }
  }

  static async getMappingById (id) {
    const sql = `
      SELECT 
        gcm.id,
        gcm.organization_id,
        gcm.department_id,
        gcm.contact_id,
        dp.name AS department_name,
        c.name AS contact_name
      FROM group_contact_mappings gcm
      LEFT JOIN departments dp ON gcm.department_id = dp.id
      LEFT JOIN contacts c ON gcm.contact_id = c.id
      WHERE gcm.id = ?
      LIMIT 1
    `
    const [rows] = await pool.execute(sql, [Number(id)])
    return rows[0] || null
  }

  static async getContactsFromMappings ({ organization_id }) {
    const sql = `
      SELECT 
        c.id,
        c.name,
        c.email,
        c.mobile,
        c.phone,
        c.logo,
        c.avatar,
        c.order_id,
        c.time,
        COALESCE(gcm.department_id, c.department_id) as department_id
      FROM contacts c
      LEFT JOIN group_contact_mappings gcm ON c.id = gcm.contact_id AND gcm.organization_id = ?
      WHERE c.organization_id = ? AND c.is_active = 1
      ORDER BY c.order_id
    `
    const [rows] = await pool.execute(sql, [Number(organization_id), Number(organization_id)])
    return rows
  }

  static async getMappings ({ organization_id = null, department_id = null, contact_id = null }) {
    const where = []
    const params = []
    if (organization_id) { where.push('gcm.organization_id = ?'); params.push(Number(organization_id)) }
    if (department_id !== null && department_id !== undefined && department_id !== '') {
      if (department_id === 'null') { where.push('gcm.department_id IS NULL') } else { where.push('gcm.department_id = ?'); params.push(Number(department_id)) }
    }
    if (contact_id) { where.push('gcm.contact_id = ?'); params.push(Number(contact_id)) }
    const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : ''
    const sql = `
      SELECT 
        gcm.id,
        gcm.organization_id,
        gcm.department_id,
        gcm.contact_id
      FROM group_contact_mappings gcm
      ${whereSql}
      ORDER BY gcm.id DESC
      LIMIT 10000
    `
    const [rows] = await pool.execute(sql, params)
    return rows
  }

  static async forceInsert (organization_id, department_id, contact_id) {
    const sql = `
      INSERT IGNORE INTO group_contact_mappings (organization_id, department_id, contact_id, created_at)
      VALUES (?, ?, ?, CURRENT_TIMESTAMP)
    `
    await pool.execute(sql, [Number(organization_id), department_id ? Number(department_id) : null, Number(contact_id)])
  }
}

module.exports = GroupContact
