const { pool } = require('../config/database');
class Contact {
    constructor(data) {
        this.id = data.id;
        this.name = data.name;
        this.organization_id = data.organization_id;
        this.organization = data.organization;
        this.rank_id = data.rank_id;
        this.service_no = data.service_no;
        this.login_id = data.login_id;
        this.unit_id = data.unit_id;
        this.department_id = data.department_id;
        this.designation_id = data.designation_id;
        this.designation = data.designation;
        this.phone = data.phone;
        this.mobile = data.mobile;
        this.alternative_mobile = data.alternative_mobile;
        this.present_address = data.present_address;
        this.present_lat_lng = data.present_lat_lng;
        this.office_address = data.office_address;
        this.office_lat_lng = data.office_lat_lng;
        this.permanent_address = data.permanent_address;
        this.permanent_lat_lng = data.permanent_lat_lng;
        this.email = data.email;
        this.occupation = data.occupation;
        this.organization = data.organization;
        this.designation = data.designation;
        this.facebook = data.facebook;
        this.linkedin = data.linkedin;
        this.parent_id = data.parent_id;
        this.is_active = data.is_active;
        this.created_at = data.created_at;
        this.updated_at = data.updated_at;
        // Additional fields from users table
        this.role_id = data.role_id;
        this.avatar = data.avatar;
        this.logo = data.logo;
        this.status = data.status;
        this.last_seen = data.last_seen;
        this.password_hash = data.password_hash;
        // Additional fields from joins
        this.rank_name = data.rank_name;
        this.unit_name = data.unit_name;
        this.unit_code = data.unit_code;
        this.department_name = data.department_name;
        this.designation_name = data.designation_name;
        this.role_name = data.role_name;
        this.parent_name = data.parent_name;
        this.children = data.children || [];
        this._table = data._table || 'users';
    }
    static async getTableColumns(table) {
        try {
            const [rows] = await pool.execute(`SHOW COLUMNS FROM ${table}`);
            const cols = new Set();
            for (const r of rows) {
                cols.add(r.Field);
            }
            return cols;
        }
        catch (e) {
            return new Set();
        }
    }
    // Create a new contact (using users table)
    static async create(contactData) {
        const { name, organization_id, rank_id, service_no, login_id, unit_id, department_id, designation_id, phone, mobile, alternative_mobile, present_address, present_lat_lng, office_address, office_lat_lng, permanent_address, permanent_lat_lng, occupation, organization, designation, facebook, linkedin, email, parent_id, password_hash, role_id = 5, status = 'offline', avatar = null, logo = null } = contactData;
        const orgId = organization_id || 1;
        // Allow password_hash to be null for minimal contact creation
        const departmentValue = department_id || null;
        const designationValue = designation_id || null;
        // Ensure all required fields have values (convert undefined to null for optional fields)
        const columns = [
            'name', 'organization_id', 'rank_id', 'service_no', 'login_id', 'unit_id', 'department_id',
            'designation_id', 'phone', 'mobile', 'alternative_mobile',
            'present_address', 'present_lat_lng',
            'office_address', 'office_lat_lng',
            'permanent_address', 'permanent_lat_lng',
            'occupation', 'organization', 'designation',
            'facebook', 'linkedin',
            'email', 'parent_id',
            'password_hash', 'role_id', 'status', 'avatar', 'logo'
        ];
        const values = [
            name || null,
            orgId,
            rank_id || null,
            service_no || null,
            login_id || null,
            unit_id,
            departmentValue,
            designationValue,
            phone || null,
            mobile || null,
            alternative_mobile || null,
            present_address || null,
            present_lat_lng || null,
            office_address || null,
            office_lat_lng || null,
            permanent_address || null,
            permanent_lat_lng || null,
            occupation || null,
            organization || null,
            designation || null,
            facebook || null,
            linkedin || null,
            email || null,
            parent_id || null,
            password_hash || null,
            role_id,
            status,
            avatar,
            logo || null
        ];
        const existingInactive = await Contact.findInactiveDuplicate({
            service_no: service_no || null,
            email: email || null,
            login_id: login_id || null
        });
        if (existingInactive) {
            const reactivated = await Contact.reactivateInactiveContact(existingInactive.id, columns, values);
            if (reactivated) {
                return existingInactive.id;
            }
            throw new Error('Error reactivating inactive contact');
        }
        const placeholders = columns.map(() => '?').join(', ');
        const query = `
      INSERT INTO users (
        ${columns.join(', ')}
      ) VALUES (${placeholders})
    `;
        try {
            const [result] = await pool.execute(query, values);
            return result.insertId;
        }
        catch (error) {
            throw new Error(`Error creating contact: ${error.message}`);
        }
    }
    static async createInContacts(contactData) {
        const { name, organization_id, unit_id, department_id, designation_id, phone, mobile, alternative_mobile, email, present_address, present_lat_lng, office_address, office_lat_lng, permanent_address, permanent_lat_lng, occupation, organization, designation, facebook, linkedin, parent_id, status = 'offline', avatar = null, logo = null, service_no, login_id } = contactData;
        const orgId = organization_id || 1;
        const existing = await Contact.getTableColumns('contacts');
        const columns = [];
        const values = [];
        const pushIfExists = (col, val) => {
            if (existing.has(col)) {
                columns.push(col);
                values.push(val);
            }
        };
        pushIfExists('organization_id', orgId);
        pushIfExists('name', name || null);
        pushIfExists('service_no', service_no || null);
        pushIfExists('login_id', login_id || null);
        {
            const orderVal = (contactData.order_id === undefined || contactData.order_id === null) ? 1 : contactData.order_id;
            pushIfExists('order_id', orderVal);
        }
        pushIfExists('phone', phone || null);
        pushIfExists('mobile', mobile || null);
        pushIfExists('alternative_mobile', alternative_mobile || null);
        pushIfExists('email', email || null);
        pushIfExists('parent_id', parent_id || null);
        pushIfExists('unit_id', unit_id || null);
        pushIfExists('department_id', department_id || null);
        pushIfExists('designation_id', designation_id || null);
        pushIfExists('present_address', present_address || null);
        pushIfExists('present_lat_lng', present_lat_lng || null);
        pushIfExists('office_address', office_address || null);
        pushIfExists('office_lat_lng', office_lat_lng || null);
        pushIfExists('permanent_address', permanent_address || null);
        pushIfExists('permanent_lat_lng', permanent_lat_lng || null);
        pushIfExists('occupation', occupation || null);
        pushIfExists('organization', organization || null);
        pushIfExists('designation', designation || null);
        pushIfExists('facebook', facebook || null);
        pushIfExists('linkedin', linkedin || null);
        pushIfExists('status', status);
        pushIfExists('avatar', avatar || null);
        pushIfExists('logo', logo || null);
        if (columns.length === 0) {
            throw new Error('No valid columns found in contacts table');
        }
        const placeholders = columns.map(() => '?').join(', ');
        const query = `
      INSERT INTO contacts (
        ${columns.join(', ')}
      ) VALUES (${placeholders})
    `;
        try {
            const [result] = await pool.execute(query, values);
            return result.insertId;
        }
        catch (error) {
            throw new Error(`Error creating contact in contacts table: ${error.message}`);
        }
    }
    static async findInactiveDuplicate({ service_no, email, login_id }) {
        const conditions = [];
        const params = [];
        if (service_no) {
            conditions.push('service_no = ?');
            params.push(service_no);
        }
        if (email) {
            conditions.push('email = ?');
            params.push(email);
        }
        if (login_id) {
            conditions.push('login_id = ?');
            params.push(login_id);
        }
        if (conditions.length === 0) {
            return null;
        }
        const query = `
      SELECT id
      FROM users
      WHERE is_active = 0 AND (${conditions.join(' OR ')})
      ORDER BY updated_at DESC, id DESC
      LIMIT 1
    `;
        const [rows] = await pool.execute(query, params);
        if (rows.length === 0) {
            return null;
        }
        return rows[0];
    }
    static async reactivateInactiveContact(id, columns, values) {
        const setClauses = columns.map(column => `${column} = ?`).join(', ');
        const query = `
      UPDATE users
      SET ${setClauses}, is_active = 1, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `;
        const params = [...values, id];
        const [result] = await pool.execute(query, params);
        return result.affectedRows > 0;
    }
    // Find contact by ID with full details
    static async findById(id, organizationId = null) {
        // Try users table first
        try {
            let queryUsers = `
        SELECT 
          u.*, ro.name as role_name, p.name as parent_name
        FROM users u
        LEFT JOIN roles ro ON u.role_id = ro.id
        LEFT JOIN users p ON u.parent_id = p.id
        WHERE u.id = ? AND u.is_active = 1
      `;
            const paramsUsers = [id];
            if (organizationId) {
                queryUsers += ' AND u.organization_id = ?';
                paramsUsers.push(organizationId);
            }
            const [rowsUsers] = await pool.execute(queryUsers, paramsUsers);
            if (rowsUsers.length > 0) {
                return new Contact({ ...rowsUsers[0], _table: 'users' });
            }
        }
        catch (error) { }
        // Fallback to contacts table
        try {
            const existing = await Contact.getTableColumns('contacts');
            const selects = ['c.*'];
            let joins = '';
            if (existing.has('unit_id')) {
            }
            if (existing.has('department_id')) {
                selects.push('d.name AS department_name');
                joins += ' LEFT JOIN departments d ON c.department_id = d.id';
            }
            if (existing.has('designation_id')) {
                selects.push('des.name AS designation_name');
                joins += ' LEFT JOIN designations des ON c.designation_id = des.id';
            }
            if (existing.has('rank_id')) {
                selects.push('r.name AS rank_name');
                joins += ' LEFT JOIN ranks r ON c.rank_id = r.id';
            }
            if (existing.has('parent_id')) {
                selects.push('p.name AS parent_name');
                joins += ' LEFT JOIN contacts p ON c.parent_id = p.id';
            }
            let queryContacts = `
        SELECT ${selects.join(', ')}
        FROM contacts c${joins}
        WHERE c.id = ?
      `;
            const paramsContacts = [id];
            if (organizationId) {
                queryContacts += ' AND c.organization_id = ?';
                paramsContacts.push(organizationId);
            }
            const [rowsContacts] = await pool.execute(queryContacts, paramsContacts);
            if (rowsContacts.length > 0) {
                return new Contact({ ...rowsContacts[0], _table: 'contacts' });
            }
            return null;
        }
        catch (error) {
            throw new Error(`Error finding contact by ID (contacts): ${error.message}`);
        }
    }
    // Explicitly find contact by ID from contacts table (prefer contacts over users)
    static async findByIdFromContacts(id, organizationId = null) {
        try {
            const existing = await Contact.getTableColumns('contacts');
            const selects = ['c.*'];
            let joins = '';
            if (existing.has('department_id')) {
                selects.push('d.name AS department_name');
                joins += ' LEFT JOIN departments d ON c.department_id = d.id';
            }
            if (existing.has('designation_id')) {
                selects.push('des.name AS designation_name');
                joins += ' LEFT JOIN designations des ON c.designation_id = des.id';
            }
            if (existing.has('rank_id')) {
                selects.push('r.name AS rank_name');
                joins += ' LEFT JOIN ranks r ON c.rank_id = r.id';
            }
            if (existing.has('parent_id')) {
                selects.push('p.name AS parent_name');
                joins += ' LEFT JOIN contacts p ON c.parent_id = p.id';
            }
            let queryContacts = `
        SELECT ${selects.join(', ')}
        FROM contacts c${joins}
        WHERE c.id = ?
      `;
            const paramsContacts = [id];
            if (organizationId) {
                queryContacts += ' AND c.organization_id = ?';
                paramsContacts.push(organizationId);
            }
            const [rowsContacts] = await pool.execute(queryContacts, paramsContacts);
            if (rowsContacts.length > 0) {
                return new Contact({ ...rowsContacts[0], _table: 'contacts' });
            }
            return null;
        }
        catch (error) {
            throw new Error(`Error finding contact by ID (contacts): ${error.message}`);
        }
    }
    static async findByLoginId(loginId) {
        if (!loginId)
            return null;
        try {
            const [rows] = await pool.execute('SELECT id FROM users WHERE login_id = ? AND is_active = 1 LIMIT 1', [loginId]);
            if (rows.length === 0) {
                return null;
            }
            return await Contact.findById(rows[0].id);
        }
        catch (error) {
            throw new Error(`Error finding contact by login ID: ${error.message}`);
        }
    }
    // Get all contacts with tree structure
    static async findAll(options = {}) {
        const { page = 1, limit = 50, search = '', parent_id = null, unit_id = '', department_id = '', organization_id = null, include_children = true } = options;
        // Ensure page and limit are valid positive integers
        const validPage = Math.max(1, parseInt(page) || 1);
        const validLimit = Math.min(Math.max(1, parseInt(limit) || 50), 1000); // Cap at 1000
        let query = `
      SELECT 
        u.*,
        ro.name as role_name,
        p.name as parent_name
      FROM users u
      LEFT JOIN roles ro ON u.role_id = ro.id
      LEFT JOIN users p ON u.parent_id = p.id
      WHERE u.is_active = 1
    `;
        const conditions = [];
        const values = [];
        if (organization_id) {
            conditions.push('u.organization_id = ?');
            values.push(organization_id);
        }
        if (search) {
            conditions.push('(u.name LIKE ? OR u.email LIKE ? OR u.service_no LIKE ? OR u.phone LIKE ?)');
            values.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
        }
        if (parent_id !== null) {
            conditions.push('u.parent_id = ?');
            values.push(parent_id);
        }
        // unit_id and department_id filters removed
        if (conditions.length > 0) {
            query += ' AND ' + conditions.join(' AND ');
        }
        query += ' ORDER BY u.id ASC';
        // Add pagination - use validated values
        const offset = (validPage - 1) * validLimit;
        query += ` LIMIT ${validLimit} OFFSET ${offset}`;
        try {
            const [rows] = await pool.execute(query, values);
            const contacts = rows.map(row => new Contact(row));
            // If include_children is true, build tree structure
            if (include_children) {
                return await Contact.buildTreeStructure(contacts);
            }
            return contacts;
        }
        catch (error) {
            throw new Error(`Error finding contacts: ${error.message}`);
        }
    }
    // Build tree structure from flat list
    static async buildTreeStructure(contacts) {
        const contactMap = new Map();
        const rootContacts = [];
        // Create a map of all contacts
        contacts.forEach(contact => {
            contactMap.set(contact.id, { ...contact, children: [] });
        });
        // Build the tree structure
        contacts.forEach(contact => {
            // Handle self-reference (parent_id = id) - treat as root contact
            if (contact.parent_id && contact.parent_id === contact.id) {
                rootContacts.push(contactMap.get(contact.id));
            }
            // Handle normal parent-child relationship
            else if (contact.parent_id && contactMap.has(contact.parent_id)) {
                contactMap.get(contact.parent_id).children.push(contactMap.get(contact.id));
            }
            // Handle root contacts (no parent or parent not found)
            else {
                rootContacts.push(contactMap.get(contact.id));
            }
        });
        return rootContacts;
    }
    // Get contact tree (all contacts in tree format)
    static async getContactTree(options = {}) {
        const { search = '', unit_id = '', department_id = '', organization_id = null, include_inactive = false, role_filter = 'all' } = options;
        try {
            const existingUsers = await Contact.getTableColumns('users');
            let selects = ['u.*', 'ro.name as role_name'];
            let joins = ' LEFT JOIN roles ro ON u.role_id = ro.id';
            if (existingUsers.has('parent_id')) {
                selects.push('p.name as parent_name');
                joins += ' LEFT JOIN users p ON u.parent_id = p.id';
            }
            let query = `
        SELECT ${selects.join(', ')}
        FROM users u${joins}
        WHERE ${include_inactive ? '1=1' : 'u.is_active = 1'}
      `;
            const conditions = [];
            const values = [];
            if (organization_id) {
                conditions.push('u.organization_id = ?');
                values.push(organization_id);
            }
            if (search) {
                conditions.push('(u.name LIKE ? OR u.email LIKE ? OR u.service_no LIKE ? OR u.phone LIKE ?)');
                values.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
            }
            // Apply role filter
            if (role_filter !== 'all') {
                switch (role_filter) {
                    case 'super_admin':
                        conditions.push('u.role_id = 1');
                        break;
                    case 'admin':
                        conditions.push('u.role_id = 2');
                        break;
                    case 'user':
                        conditions.push('u.role_id = 5');
                        break;
                }
            }
            if (conditions.length > 0) {
                query += ' AND ' + conditions.join(' AND ');
            }
            query += ' ORDER BY u.id ASC';
            const [rows] = await pool.execute(query, values);
            const contacts = rows.map(row => new Contact(row));
            return await Contact.buildTreeStructure(contacts);
        }
        catch (error) {
            throw new Error(`Error getting contact tree: ${error.message}`);
        }
    }
    static async getContactTreeFromContacts(options = {}) {
        const { search = '', organization_id = null, department_id = '', include_inactive = false } = options;
        try {
            const existing = await Contact.getTableColumns('contacts');
            const selects = ['c.*'];
            let joins = '';
            if (existing.has('unit_id')) {
            }
            if (existing.has('department_id')) {
                selects.push('d.name AS department_name');
                joins += ' LEFT JOIN departments d ON c.department_id = d.id';
            }
            if (existing.has('designation_id')) {
                selects.push('des.name AS designation_name');
                joins += ' LEFT JOIN designations des ON c.designation_id = des.id';
            }
            if (existing.has('rank_id')) {
                selects.push('r.name AS rank_name');
                joins += ' LEFT JOIN ranks r ON c.rank_id = r.id';
            }
            if (existing.has('parent_id')) {
                selects.push('p.name AS parent_name');
                joins += ' LEFT JOIN contacts p ON c.parent_id = p.id';
            }
            let query = `
        SELECT ${selects.join(', ')}
        FROM contacts c${joins}
        WHERE 1=1
      `;
            const conditions = [];
            const values = [];
            if (organization_id && existing.has('organization_id')) {
                conditions.push('c.organization_id = ?');
                values.push(organization_id);
            }
            if (department_id && existing.has('department_id')) {
                conditions.push('c.department_id = ?');
                values.push(department_id);
            }
            if (search) {
                const likeCols = [];
                if (existing.has('name'))
                    likeCols.push('c.name LIKE ?');
                if (existing.has('email'))
                    likeCols.push('c.email LIKE ?');
                if (existing.has('phone'))
                    likeCols.push('c.phone LIKE ?');
                if (existing.has('mobile'))
                    likeCols.push('c.mobile LIKE ?');
                if (existing.has('service_no'))
                    likeCols.push('c.service_no LIKE ?');
                if (existing.has('designation_id'))
                    likeCols.push('des.name LIKE ?');
                if (likeCols.length > 0) {
                    conditions.push('(' + likeCols.join(' OR ') + ')');
                    const likeVal = `%${search}%`;
                    for (let i = 0; i < likeCols.length; i++)
                        values.push(likeVal);
                }
            }
            if (!include_inactive && existing.has('is_active')) {
                conditions.push('c.is_active = 1');
            }
            if (conditions.length > 0) {
                query += ' AND ' + conditions.join(' AND ');
            }
            query += ' ORDER BY c.id DESC';
            const [rows] = await pool.execute(query, values);
            const contacts = rows.map(row => new Contact({ ...row, _table: 'contacts' }));
            return await Contact.buildTreeStructure(contacts);
        }
        catch (error) {
            throw new Error(`Error getting contacts tree: ${error.message}`);
        }
    }
    static async findAllFromContacts(options = {}) {
        const { page = 1, limit = 50, search = '', organization_id = null, department_id = '', include_inactive = false } = options;
        const validPage = Math.max(1, parseInt(page) || 1);
        const validLimit = Math.min(Math.max(1, parseInt(limit) || 50), 1000);
        try {
            const existing = await Contact.getTableColumns('contacts');
            const selects = ['c.*'];
            let joins = '';
            if (existing.has('unit_id')) {
            }
            if (existing.has('department_id')) {
                selects.push('d.name AS department_name');
                joins += ' LEFT JOIN departments d ON c.department_id = d.id';
            }
            if (existing.has('designation_id')) {
                selects.push('des.name AS designation_name');
                joins += ' LEFT JOIN designations des ON c.designation_id = des.id';
            }
            if (existing.has('rank_id')) {
                selects.push('r.name AS rank_name');
                joins += ' LEFT JOIN ranks r ON c.rank_id = r.id';
            }
            if (existing.has('parent_id')) {
                selects.push('p.name AS parent_name');
                joins += ' LEFT JOIN contacts p ON c.parent_id = p.id';
            }
            let query = `
        SELECT ${selects.join(', ')}
        FROM contacts c${joins}
        WHERE 1=1
      `;
            const conditions = [];
            const values = [];
            if (organization_id && existing.has('organization_id')) {
                conditions.push('c.organization_id = ?');
                values.push(organization_id);
            }
            if (department_id && existing.has('department_id')) {
                conditions.push('c.department_id = ?');
                values.push(department_id);
            }
            if (search) {
                const likeCols = [];
                if (existing.has('name'))
                    likeCols.push('c.name LIKE ?');
                if (existing.has('email'))
                    likeCols.push('c.email LIKE ?');
                if (existing.has('phone'))
                    likeCols.push('c.phone LIKE ?');
                if (likeCols.length > 0) {
                    conditions.push('(' + likeCols.join(' OR ') + ')');
                    values.push(`%${search}%`);
                    values.push(`%${search}%`);
                    values.push(`%${search}%`);
                }
            }
            if (!include_inactive && existing.has('is_active')) {
                conditions.push('c.is_active = 1');
            }
            if (conditions.length > 0) {
                query += ' AND ' + conditions.join(' AND ');
            }
            query += ' ORDER BY c.id DESC';
            const offset = (validPage - 1) * validLimit;
            query += ` LIMIT ${validLimit} OFFSET ${offset}`;
            const [rows] = await pool.execute(query, values);
            return rows.map(row => new Contact({ ...row, _table: 'contacts' }));
        }
        catch (error) {
            throw new Error(`Error finding contacts from contacts table: ${error.message}`);
        }
    }
    static async getContactStatisticsFromContacts(options = {}) {
        const { organization_id = null } = options;
        try {
            const existing = await Contact.getTableColumns('contacts');
            let query = 'SELECT COUNT(*) as total_contacts FROM contacts c WHERE 1=1';
            const values = [];
            if (organization_id && existing.has('organization_id')) {
                query += ' AND c.organization_id = ?';
                values.push(organization_id);
            }
            const [rows] = await pool.execute(query, values);
            return rows[0];
        }
        catch (error) {
            throw new Error(`Error getting contacts statistics: ${error.message}`);
        }
    }
    // Get children of a contact
    static async getChildren(parentId) {
        try {
            const existingUsers = await Contact.getTableColumns('users');
            if (!existingUsers.has('parent_id')) {
                return [];
            }
            const query = `
        SELECT 
          u.*,
          ro.name as role_name,
          p.name as parent_name
        FROM users u
        LEFT JOIN roles ro ON u.role_id = ro.id
        LEFT JOIN users p ON u.parent_id = p.id
        WHERE u.parent_id = ? AND u.is_active = 1
        ORDER BY u.id ASC
      `;
            const [rows] = await pool.execute(query, [parentId]);
            return rows.map(row => new Contact(row));
        }
        catch (error) {
            throw new Error(`Error getting children: ${error.message}`);
        }
    }
    // Update contact
    async update(updateData) {
        const allowedFields = [
            'name', 'rank_id', 'service_no', 'organization_id', 'department_id', 'order_id',
            'designation_id', 'phone', 'mobile', 'alternative_mobile',
            'present_address', 'present_lat_lng',
            'office_address', 'office_lat_lng',
            'permanent_address', 'permanent_lat_lng',
            'occupation', 'organization', 'designation',
            'facebook', 'linkedin',
            'email', 'parent_id', 'login_id', 'avatar', 'logo',
            'status'
        ];
        const targetTable = this._table || 'users';
        const existing = await Contact.getTableColumns(targetTable);
        const fields = [];
        const values = [];
        if (Object.prototype.hasOwnProperty.call(updateData, 'service_no')) {
            const v = updateData.service_no;
            if (v === '' || v === undefined) {
                updateData.service_no = null;
            }
        }
        if (Object.prototype.hasOwnProperty.call(updateData, 'order_id')) {
            const v = updateData.order_id;
            if (v === '' || v === undefined || v === null) {
                updateData.order_id = 1;
            }
        }
        Object.keys(updateData).forEach(key => {
            if (allowedFields.includes(key) && updateData[key] !== undefined && existing.has(key)) {
                fields.push(`${key} = ?`);
                values.push(updateData[key]);
            }
        });
        if (fields.length === 0) {
            return false;
        }
        values.push(this.id);
        const query = `UPDATE ${targetTable} SET ${fields.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`;
        try {
            const [result] = await pool.execute(query, values);
            if (result.affectedRows > 0) {
                Object.assign(this, updateData);
                return true;
            }
            return false;
        }
        catch (error) {
            if (targetTable === 'users' && error.code === 'ER_DUP_ENTRY' && error.message.includes('login_id')) {
                if (updateData.login_id) {
                    try {
                        const [duplicateUsers] = await pool.execute('SELECT id FROM users WHERE login_id = ? AND is_active = 1 AND id != ?', [updateData.login_id, this.id]);
                        if (duplicateUsers.length > 0) {
                            for (const dupUser of duplicateUsers) {
                                await pool.execute('UPDATE users SET login_id = NULL WHERE id = ?', [dupUser.id]);
                            }
                            const [retryResult] = await pool.execute(query, values);
                            if (retryResult.affectedRows > 0) {
                                Object.assign(this, updateData);
                                return true;
                            }
                        }
                    }
                    catch (retryError) { }
                }
                const loginIdIndex = fields.findIndex(f => f.includes('login_id'));
                const fieldsWithoutLoginId = fields.filter(f => !f.includes('login_id'));
                const valuesWithoutLoginId = values.slice(0, -1).filter((v, i) => i !== loginIdIndex);
                valuesWithoutLoginId.push(this.id);
                const queryWithoutLoginId = `UPDATE ${targetTable} SET ${fieldsWithoutLoginId.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`;
                const [fallbackResult] = await pool.execute(queryWithoutLoginId, valuesWithoutLoginId);
                if (fallbackResult.affectedRows > 0) {
                    const updateDataWithoutLoginId = { ...updateData };
                    delete updateDataWithoutLoginId.login_id;
                    Object.assign(this, updateDataWithoutLoginId);
                    return true;
                }
            }
            if (targetTable === 'users' && error.code === 'ER_DUP_ENTRY' && error.message.includes('service_no')) {
                const serviceNoIndex = fields.findIndex(f => f.includes('service_no'));
                const fieldsWithoutServiceNo = fields.filter(f => !f.includes('service_no'));
                const valuesWithoutServiceNo = values.slice(0, -1).filter((v, i) => i !== serviceNoIndex);
                valuesWithoutServiceNo.push(this.id);
                const queryWithoutServiceNo = `UPDATE ${targetTable} SET ${fieldsWithoutServiceNo.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`;
                const [fallbackResult2] = await pool.execute(queryWithoutServiceNo, valuesWithoutServiceNo);
                if (fallbackResult2.affectedRows > 0) {
                    const updatedData = { ...updateData };
                    delete updatedData.service_no;
                    Object.assign(this, updatedData);
                    return true;
                }
            }
            throw new Error(`Error updating contact: ${error.message}`);
        }
    }
    // Soft delete contact
    async delete() {
        const targetTable = this._table || 'users';
        try {
            if (targetTable === 'users') {
                const [result] = await pool.execute('UPDATE users SET is_active = 0, updated_at = CURRENT_TIMESTAMP WHERE id = ?', [this.id]);
                if (result.affectedRows > 0) {
                    this.is_active = 0;
                    return true;
                }
                return false;
            }
            else {
                const [result] = await pool.execute('DELETE FROM contacts WHERE id = ?', [this.id]);
                return result.affectedRows > 0;
            }
        }
        catch (error) {
            throw new Error(`Error deleting contact: ${error.message}`);
        }
    }
    // Get contact statistics
    static async getStatistics() {
        try {
            const existingUsers = await Contact.getTableColumns('users');
            const selectParts = [
                'COUNT(*) as total_contacts',
                "SUM(CASE WHEN u.role_id = 1 THEN 1 ELSE 0 END) as super_admins",
                "SUM(CASE WHEN u.role_id = 2 THEN 1 ELSE 0 END) as admins",
                "SUM(CASE WHEN u.role_id = 3 THEN 1 ELSE 0 END) as regular_users"
            ];
            if (existingUsers.has('parent_id')) {
                selectParts.unshift('SUM(CASE WHEN u.parent_id IS NULL THEN 1 ELSE 0 END) as root_contacts', 'SUM(CASE WHEN u.parent_id IS NOT NULL THEN 1 ELSE 0 END) as child_contacts');
            }
            else {
                selectParts.unshift('0 as root_contacts', '0 as child_contacts');
            }
            const query = `
        SELECT ${selectParts.join(', ')}
        FROM users u
        WHERE u.is_active = 1
      `;
            const [rows] = await pool.execute(query);
            return rows[0];
        }
        catch (error) {
            throw new Error(`Error getting contact statistics: ${error.message}`);
        }
    }
    // Get enhanced contact statistics
    static async getEnhancedStatistics(options = {}) {
        const { unit_id = '', department_id = '', include_inactive = false, role_filter = 'all' } = options;
        try {
            const existingUsers = await Contact.getTableColumns('users');
            let whereClause = include_inactive ? '1=1' : 'u.is_active = 1';
            const conditions = [];
            const values = [];
            if (role_filter !== 'all') {
                switch (role_filter) {
                    case 'super_admin':
                        conditions.push('u.role_id = 1');
                        break;
                    case 'admin':
                        conditions.push('u.role_id = 2');
                        break;
                    case 'user':
                        conditions.push('u.role_id = 5');
                        break;
                }
            }
            if (conditions.length > 0) {
                whereClause += ' AND ' + conditions.join(' AND ');
            }
            const selectParts = [
                'COUNT(*) as total_contacts',
                "SUM(CASE WHEN u.role_id = 1 THEN 1 ELSE 0 END) as super_admins",
                "SUM(CASE WHEN u.role_id = 2 THEN 1 ELSE 0 END) as admins",
                "SUM(CASE WHEN u.role_id = 3 THEN 1 ELSE 0 END) as regular_users",
                "SUM(CASE WHEN u.status = 'online' THEN 1 ELSE 0 END) as online_users",
                "SUM(CASE WHEN u.status = 'offline' THEN 1 ELSE 0 END) as offline_users",
                "SUM(CASE WHEN u.status = 'busy' THEN 1 ELSE 0 END) as busy_users",
                '0 as total_units'
            ];
            if (existingUsers.has('parent_id')) {
                selectParts.splice(1, 0, 'SUM(CASE WHEN u.parent_id IS NULL OR u.parent_id = u.id THEN 1 ELSE 0 END) as root_contacts', 'SUM(CASE WHEN u.parent_id IS NOT NULL AND u.parent_id != u.id THEN 1 ELSE 0 END) as child_contacts');
            }
            else {
                selectParts.splice(1, 0, '0 as root_contacts', '0 as child_contacts');
            }
            const query = `
        SELECT ${selectParts.join(', ')}
        FROM users u
        WHERE ${whereClause}
      `;
            const [rows] = await pool.execute(query, values);
            return rows[0];
        }
        catch (error) {
            throw new Error(`Error getting enhanced contact statistics: ${error.message}`);
        }
    }
    // Search contacts
    static async search(searchTerm, options = {}) {
        const { unit_id = '', department_id = '', organization_id = null, limit = 20 } = options;
        try {
            let query = `
        SELECT 
          u.*,
          ro.name as role_name,
          p.name as parent_name
        FROM users u
        LEFT JOIN roles ro ON u.role_id = ro.id
        LEFT JOIN users p ON u.parent_id = p.id
        WHERE u.is_active = 1
        AND (u.name LIKE ? OR u.email LIKE ? OR u.service_no LIKE ? OR u.phone LIKE ?)
      `;
            const values = [`%${searchTerm}%`, `%${searchTerm}%`, `%${searchTerm}%`, `%${searchTerm}%`];
            if (organization_id) {
                query += ' AND u.organization_id = ?';
                values.push(organization_id);
            }
            // unit_id and department_id filters removed
            query += ' ORDER BY u.id ASC LIMIT ?';
            values.push(limit);
            const [rows] = await pool.execute(query, values);
            return rows.map(row => new Contact(row));
        }
        catch (error) {
            throw new Error(`Error searching contacts: ${error.message}`);
        }
    }
    static async searchFromContacts(searchTerm, options = {}) {
        const { organization_id = null, department_id = '', limit = 20, include_inactive = false } = options;
        try {
            const existing = await Contact.getTableColumns('contacts');
            const selects = ['c.*'];
            let joins = '';
            if (existing.has('unit_id')) {
            }
            if (existing.has('department_id')) {
                selects.push('d.name AS department_name');
                joins += ' LEFT JOIN departments d ON c.department_id = d.id';
            }
            if (existing.has('designation_id')) {
                selects.push('des.name AS designation_name');
                joins += ' LEFT JOIN designations des ON c.designation_id = des.id';
            }
            if (existing.has('rank_id')) {
                selects.push('r.name AS rank_name');
                joins += ' LEFT JOIN ranks r ON c.rank_id = r.id';
            }
            if (existing.has('parent_id')) {
                selects.push('p.name AS parent_name');
                joins += ' LEFT JOIN contacts p ON c.parent_id = p.id';
            }
            let query = `
        SELECT ${selects.join(', ')}
        FROM contacts c${joins}
        WHERE 1=1
      `;
            const values = [];
            if (organization_id && existing.has('organization_id')) {
                query += ' AND c.organization_id = ?';
                values.push(organization_id);
            }
            if (department_id && existing.has('department_id')) {
                query += ' AND c.department_id = ?';
                values.push(department_id);
            }
            if (!include_inactive && existing.has('is_active')) {
                query += ' AND c.is_active = 1';
            }
            const likeClauses = [];
            if (existing.has('name'))
                likeClauses.push('c.name LIKE ?');
            if (existing.has('email'))
                likeClauses.push('c.email LIKE ?');
            if (existing.has('service_no'))
                likeClauses.push('c.service_no LIKE ?');
            if (existing.has('phone'))
                likeClauses.push('c.phone LIKE ?');
            if (existing.has('mobile'))
                likeClauses.push('c.mobile LIKE ?');
            if (existing.has('designation_id'))
                likeClauses.push('des.name LIKE ?');
            if (likeClauses.length > 0) {
                query += ' AND (' + likeClauses.join(' OR ') + ')';
                const likeVal = `%${searchTerm}%`;
                for (let i = 0; i < likeClauses.length; i++)
                    values.push(likeVal);
            }
            query += ' ORDER BY c.id DESC LIMIT ?';
            values.push(limit);
            const [rows] = await pool.execute(query, values);
            return rows.map(row => new Contact({ ...row, _table: 'contacts' }));
        }
        catch (error) {
            throw new Error(`Error searching contacts (contacts table): ${error.message}`);
        }
    }
    // Convert to JSON
    toJSON() {
        return {
            id: this.id,
            name: this.name,
            order_id: this.order_id,
            organization_id: this.organization_id,
            rank_id: this.rank_id,
            rank_name: this.rank_name,
            // Common aliases expected by frontend (Users tab/Directory)
            rank: this.rank_name,
            service_no: this.service_no,
            unit_id: this.unit_id,
            unit_name: this.unit_name,
            unit_code: this.unit_code,
            unit: this.unit_name,
            department_id: this.department_id,
            department_name: this.department_name,
            department: this.department_name,
            designation_id: this.designation_id,
            designation_name: this.designation_name,
            designation: this.designation_name,
            phone: this.phone,
            mobile: this.mobile,
            alternative_mobile: this.alternative_mobile,
            email: this.email,
            present_address: this.present_address,
            present_lat_lng: this.present_lat_lng,
            office_address: this.office_address,
            office_lat_lng: this.office_lat_lng,
            permanent_address: this.permanent_address,
            permanent_lat_lng: this.permanent_lat_lng,
            occupation: this.occupation,
            organization: this.organization,
            designation: this.designation,
            facebook: this.facebook,
            linkedin: this.linkedin,
            parent_id: this.parent_id,
            parent_name: this.parent_name,
            is_active: this.is_active,
            created_at: this.created_at,
            updated_at: this.updated_at,
            // Additional user fields
            role_id: this.role_id,
            role_name: this.role_name,
            role: this.role_name,
            login_id: this.login_id,
            avatar: this.avatar,
            logo: this.logo,
            status: this.status,
            last_seen: this.last_seen,
            children: this.children
        };
    }
    // Update contact password
    static async updatePassword(id, newPassword) {
        try {
            const bcrypt = require('bcryptjs');
            // Hash the new password
            const saltRounds = 10;
            const hashedPassword = await bcrypt.hash(newPassword, saltRounds);
            // Update password in database
            const [result] = await pool.execute('UPDATE users SET password_hash = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?', [hashedPassword, id]);
            if (result.affectedRows > 0) {
                return { success: true, message: 'Password updated successfully' };
            }
            else {
                return { success: false, message: 'Contact not found or no changes made' };
            }
        }
        catch (error) {
            console.error('Error updating password:', error);
            return { success: false, message: 'Failed to update password', error: error.message };
        }
    }
}
module.exports = Contact;
//# sourceMappingURL=Contact.js.map