const { pool } = require('../config/database');
class Department {
    constructor(data) {
        this.id = data.id;
        this.name = data.name;
        this.organization_id = data.organization_id;
        this.order_id = data.order_id;
        this.parent_id = data.parent_id;
        this.description = data.description;
        this.is_active = data.is_active;
        this.logo = data.logo;
        this.created_at = data.created_at;
        this.updated_at = data.updated_at;
        // Additional fields from joins
        this.parent_name = data.parent_name;
        this.total_users = data.total_users || 0;
    }
    // Create a new department
    static async create(departmentData) {
        const { name, organization_id, order_id, parent_id, description, logo } = departmentData;
        // No duplicate checking - allow any name
        const query = `
      INSERT INTO departments (name, organization_id, order_id, parent_id, description, logo)
      VALUES (?, ?, ?, ?, ?, ?)
    `;
        const values = [
            name,
            organization_id || 1,
            order_id || null,
            parent_id || null,
            description || null,
            logo || null
        ];
        try {
            const [result] = await pool.execute(query, values);
            return result.insertId;
        }
        catch (error) {
            if (error.code === 'ER_DUP_ENTRY') {
                throw new Error('Department name already exists');
            }
            if (error.code === 'ER_NO_REFERENCED_ROW_2') {
                if (error.message.includes('parent_id')) {
                    throw new Error('Invalid parent_id. Parent department does not exist');
                }
                throw new Error('Invalid reference. Referenced record does not exist');
            }
            throw new Error(`Error creating department: ${error.message}`);
        }
    }
    // Find department by ID with full details
    static async findById(id, organizationId = null, include_inactive = false) {
        const query = `
      SELECT 
        d.*,
        pd.name as parent_name,
        0 as total_users
      FROM departments d
      LEFT JOIN departments pd ON d.parent_id = pd.id
      WHERE d.id = ? ${include_inactive ? '' : 'AND d.is_active = 1'} ${organizationId ? 'AND d.organization_id = ?' : ''}
    `;
        try {
            const params = organizationId ? [id, organizationId] : [id];
            const [rows] = await pool.execute(query, params);
            if (rows.length > 0) {
                return new Department(rows[0]);
            }
            return null;
        }
        catch (error) {
            throw new Error(`Error finding department: ${error.message}`);
        }
    }
    // Get all departments with optional filtering
    static async findAll(options = {}) {
        const { page = 1, limit = 50, search = '', organization_id = null, include_inactive = false } = 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 
        d.*,
        pd.name as parent_name,
        0 as total_users
      FROM departments d
      LEFT JOIN departments pd ON d.parent_id = pd.id
      WHERE ${include_inactive ? '1=1' : 'd.is_active = 1'}
    `;
        const conditions = [];
        const values = [];
        if (organization_id) {
            conditions.push('d.organization_id = ?');
            values.push(organization_id);
        }
        if (search) {
            conditions.push('(d.name LIKE ? OR d.description LIKE ?)');
            values.push(`%${search}%`, `%${search}%`);
        }
        if (conditions.length > 0) {
            query += ' AND ' + conditions.join(' AND ');
        }
        query += ' ORDER BY d.order_id ASC';
        // Add pagination - use validated values
        const offset = (validPage - 1) * validLimit;
        query += ` LIMIT ${validLimit} OFFSET ${offset}`;
        try {
            console.log('Departments:findAll executing', { query, values });
            const [rows] = await pool.execute(query, values);
            return rows.map(row => new Department(row));
        }
        catch (error) {
            console.error('Departments:findAll query error', { message: error.message, stack: error.stack });
            throw new Error(`Error finding departments: ${error.message}`);
        }
    }
    static async countAll(options = {}) {
        const { search = '', organization_id = null, include_inactive = false } = options;
        let query = `
      SELECT COUNT(*) as total
      FROM departments d
      LEFT JOIN departments pd ON d.parent_id = pd.id
      WHERE ${include_inactive ? '1=1' : 'd.is_active = 1'}
    `;
        const conditions = [];
        const values = [];
        if (organization_id) {
            conditions.push('d.organization_id = ?');
            values.push(organization_id);
        }
        if (search) {
            conditions.push('(d.name LIKE ? OR d.description LIKE ?)');
            values.push(`%${search}%`, `%${search}%`);
        }
        if (conditions.length > 0) {
            query += ' AND ' + conditions.join(' AND ');
        }
        try {
            console.log('Departments:countAll executing', { query, values });
            const [rows] = await pool.execute(query, values);
            return Number(rows[0]?.total || 0);
        }
        catch (error) {
            console.error('Departments:countAll query error', { message: error.message, stack: error.stack });
            throw new Error(`Error counting departments: ${error.message}`);
        }
    }
    // Get departments by unit
    static async findByUnit() {
        throw new Error('findByUnit is not supported: departments no longer have unit_id');
    }
    // Update department
    static async update(id, updateData, organizationId = null, include_inactive = false) {
        const allowedFields = ['name', 'order_id', 'parent_id', 'description', 'is_active', 'logo'];
        const fields = [];
        const values = [];
        // No duplicate checking - allow any name
        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);
        if (organizationId) {
            values.push(organizationId);
        }
        const query = `
      UPDATE departments 
      SET ${fields.join(', ')}, updated_at = CURRENT_TIMESTAMP
      WHERE id = ? ${include_inactive ? '' : 'AND is_active = 1'} ${organizationId ? 'AND organization_id = ?' : ''}
    `;
        try {
            const [result] = await pool.execute(query, values);
            return result.affectedRows > 0;
        }
        catch (error) {
            if (error.code === 'ER_DUP_ENTRY') {
                throw new Error('Department name already exists');
            }
            if (error.code === 'ER_NO_REFERENCED_ROW_2') {
                if (error.message.includes('parent_id')) {
                    throw new Error('Invalid parent_id. Parent department does not exist');
                }
                throw new Error('Invalid reference. Referenced record does not exist');
            }
            throw new Error(`Error updating department: ${error.message}`);
        }
    }
    // Soft delete department
    static async delete(id) {
        // Check if department has users (adapt to schema)
        try {
            let targetColumn = null;
            try {
                const [cols] = await pool.execute(`SELECT COLUMN_NAME 
           FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_SCHEMA = DATABASE() 
             AND TABLE_NAME = 'users' 
             AND COLUMN_NAME IN ('department_id','branch_id')`);
                const names = new Set((cols || []).map(r => r.COLUMN_NAME));
                if (names.has('department_id'))
                    targetColumn = 'department_id';
                else if (names.has('branch_id'))
                    targetColumn = 'branch_id';
            }
            catch (_) { }
            if (targetColumn) {
                const [users] = await pool.execute(`SELECT COUNT(*) as count FROM users WHERE ${targetColumn} = ? AND is_active = 1`, [id]);
                if (users[0]?.count > 0) {
                    throw new Error('Cannot delete department with active users. Please reassign or delete users first.');
                }
            }
        }
        catch (error) {
            if (error instanceof Error && error.message.startsWith('Cannot delete')) {
                throw error;
            }
            // If schema check fails, proceed without blocking
        }
        const query = 'UPDATE departments SET is_active = 0, updated_at = CURRENT_TIMESTAMP WHERE id = ?';
        try {
            const [result] = await pool.execute(query, [id]);
            return result.affectedRows > 0;
        }
        catch (error) {
            throw new Error(`Error deleting department: ${error.message}`);
        }
    }
    // Get department statistics
    static async getStatistics() {
        try {
            const query = `
        SELECT 
          COUNT(*) as total_departments,
          0 as total_users_in_departments,
          0 as total_users_without_department
        FROM departments 
        WHERE is_active = 1
      `;
            const [rows] = await pool.execute(query);
            return rows[0];
        }
        catch (error) {
            throw new Error(`Error getting department statistics: ${error.message}`);
        }
    }
    // Get departments grouped by unit
    static async getDepartmentsByUnit() {
        throw new Error('Grouping departments by unit is not supported');
    }
    // Check if sub-department name exists within parent department
    static async nameExistsInParentDepartment(name, parentId, unitId, excludeId = null) {
        let query = 'SELECT COUNT(*) as count FROM departments WHERE name = ? AND parent_id = ? AND is_active = 1';
        const values = [name, parentId];
        if (excludeId) {
            query += ' AND id != ?';
            values.push(excludeId);
        }
        try {
            const [rows] = await pool.execute(query, values);
            return rows[0].count > 0;
        }
        catch (error) {
            throw new Error(`Error checking sub-department name: ${error.message}`);
        }
    }
    // Check if department name exists in unit
    static async nameExistsInUnit(name, unitId, excludeId = null) {
        let query = 'SELECT COUNT(*) as count FROM departments WHERE name = ? AND parent_id IS NULL AND is_active = 1';
        const values = [name];
        if (excludeId) {
            query += ' AND id != ?';
            values.push(excludeId);
        }
        try {
            const [rows] = await pool.execute(query, values);
            return rows[0].count > 0;
        }
        catch (error) {
            throw new Error(`Error checking department name: ${error.message}`);
        }
    }
    // Get child departments
    static async getChildren(departmentId) {
        const query = `
      SELECT 
        d.*,
        pd.name as parent_name,
        (SELECT COUNT(*) FROM users WHERE department_id = d.id AND is_active = 1) as total_users
      FROM departments d
      LEFT JOIN departments pd ON d.parent_id = pd.id
      WHERE d.parent_id = ? AND d.is_active = 1
      ORDER BY d.order_id ASC
    `;
        try {
            const [rows] = await pool.execute(query, [departmentId]);
            return rows.map(row => new Department(row));
        }
        catch (error) {
            throw new Error(`Error getting child departments: ${error.message}`);
        }
    }
    // Get department hierarchy (tree structure)
    static async getHierarchy() {
        const whereClause = 'WHERE d.is_active = 1';
        const values = [];
        const query = `
      SELECT 
        d.*,
        pd.name as parent_name,
        (SELECT COUNT(*) FROM users WHERE department_id = d.id AND is_active = 1) as total_users
      FROM departments d
      LEFT JOIN departments pd ON d.parent_id = pd.id
      ${whereClause}
      ORDER BY d.order_id ASC
    `;
        try {
            const [rows] = await pool.execute(query, values);
            const departments = rows.map(row => new Department(row));
            // Build hierarchy tree
            const departmentMap = new Map();
            const rootDepartments = [];
            // First pass: create map of all departments
            departments.forEach(dept => {
                departmentMap.set(dept.id, { ...dept.toJSON(), children: [] });
            });
            // Second pass: build tree structure
            departments.forEach(dept => {
                if (dept.parent_id) {
                    const parent = departmentMap.get(dept.parent_id);
                    if (parent) {
                        parent.children.push(departmentMap.get(dept.id));
                    }
                }
                else {
                    rootDepartments.push(departmentMap.get(dept.id));
                }
            });
            return rootDepartments;
        }
        catch (error) {
            throw new Error(`Error getting department hierarchy: ${error.message}`);
        }
    }
    // Check if department can be parent (avoid circular references)
    static async canBeParent(departmentId, potentialParentId) {
        if (departmentId === potentialParentId) {
            return false; // Cannot be parent of itself
        }
        try {
            // Check if potential parent is a descendant of the department
            const query = `
        WITH RECURSIVE dept_hierarchy AS (
          SELECT id, parent_id, 1 as level
          FROM departments 
          WHERE id = ? AND is_active = 1
          
          UNION ALL
          
          SELECT d.id, d.parent_id, dh.level + 1
          FROM departments d
          INNER JOIN dept_hierarchy dh ON d.parent_id = dh.id
          WHERE d.is_active = 1 AND dh.level < 10 -- Prevent infinite loops
        )
        SELECT COUNT(*) as count
        FROM dept_hierarchy 
        WHERE id = ?
      `;
            const [rows] = await pool.execute(query, [departmentId, potentialParentId]);
            return rows[0].count === 0;
        }
        catch (error) {
            throw new Error(`Error checking parent validity: ${error.message}`);
        }
    }
    // Convert to JSON
    toJSON() {
        return {
            id: this.id,
            name: this.name,
            organization_id: this.organization_id,
            order_id: this.order_id,
            parent_id: this.parent_id,
            parent_name: this.parent_name,
            description: this.description,
            is_active: this.is_active,
            total_users: this.total_users,
            logo: this.logo,
            created_at: this.created_at,
            updated_at: this.updated_at
        };
    }
}
module.exports = Department;
//# sourceMappingURL=Department.js.map