const pool = require('../config/db');
const bcrypt = require('bcryptjs');

class User {
   static async create(email, first_name, last_name, contacts, created_by, role) {
    const hashedPassword = await bcrypt.hash("123456789", 10);
    
    // If created_by is null, just create the user normally
    if (!created_by) {
        const [result] = await pool.query(
            'INSERT INTO users (email, password, first_name, last_name, created_by, role, contacts) VALUES (?, ?, ?, ?, ?, ?, ?)',
            [email, hashedPassword, first_name, last_name, null, role, contacts]
        );
        return result.insertId;
    }

    // Get the creator user's role
    const [creator] = await pool.query('SELECT role FROM users WHERE id = ?', [created_by]);
    
    if (creator.length === 0) {
        throw new Error(created_by);
    }
    
    const creatorRole = creator[0].role;
    let cellGroupId = null;
    
    // If creator is a MEMBER, check if they own a cell group
    if (creatorRole === 'MEMBER') {
        const [cellGroup] = await pool.query(
            'SELECT id FROM cell_groups WHERE cell_group_leader = ?',
            [created_by]
        );
        
        if (cellGroup.length > 0) {
            cellGroupId = cellGroup[0].id;
        } else {
            throw new Error('Member creators must be cell group leaders to create users');
        }
    }
    
    // Insert the new user
    const [result] = await pool.query(
        'INSERT INTO users (email, password, first_name, last_name, created_by, role, contacts) VALUES (?, ?, ?, ?, ?, ?, ?)',
        [email, hashedPassword, first_name, last_name, created_by, role, contacts]
    );
    
    const newUserId = result.insertId;
    
    // If creator is a MEMBER and has a cell group, add the new user to that cell group
    if (creatorRole === 'MEMBER' && cellGroupId) {
        await pool.query(
            'INSERT INTO cell_group_members (cell_group_id, user_id) VALUES (?, ?)',
            [cellGroupId, newUserId]
        );
    }
    
    return newUserId;
}

static async createPartner(email, first_name, last_name, contacts, password, role) {
    const hashedPassword = await bcrypt.hash(password, 10);
    
        const [result] = await pool.query(
            'INSERT INTO users (email, password, first_name, last_name, created_by, role, contacts) VALUES (?, ?, ?, ?, ?, ?, ?)',
            [email, hashedPassword, first_name, last_name, null, role, contacts]
        );
        return result.insertId;
    }



    static async findByEmail(email) {
        const [rows] = await pool.query('SELECT * FROM users WHERE email = ?', [email]);
        return rows[0];
    }

    static async findById(id) {
        const [rows] = await pool.query('SELECT id, first_name, last_name, role, email, profile_url FROM users WHERE id = ?', [id]);
        return rows[0];
    }

    static async comparePasswords(candidatePassword, hashedPassword) {
        return await bcrypt.compare(candidatePassword, hashedPassword);
    }

   static async getAll(role = null, userId = null) { 
        let query = 'SELECT id, first_name, last_name, role, email, profile_url, contacts FROM users';
        const params = [];

        if (role && userId) {
            query += ' WHERE role = ? AND id != ?';
            params.push(role, userId);
        } else if (role) {
            query += ' WHERE role = ?';
            params.push(role);
        } else if (userId) {
            query += ' WHERE id != ?';
            params.push(userId);
        }

        const [rows] = await pool.query(query, params);
        return rows;
    }

    static async getMembers(userId) {
    try {
        // First, get the user's role
        const [userRows] = await pool.query('SELECT role FROM users WHERE id = ?', [userId]);
        
        if (userRows.length === 0) {
            throw new Error('User not found');
        }

        const userRole = userRows[0].role;

        // If user is not a MEMBER, return all members
        if (userRole !== 'MEMBER') {
            const [allMembers] = await pool.query(
                'SELECT id, first_name, last_name, role, email, profile_url, contacts ' +
                'FROM users WHERE role = "MEMBER"'
            );
            return allMembers;
        }

        // If user is a MEMBER, check if they're a cell group leader
        const [cellGroup] = await pool.query(
            'SELECT id FROM cell_groups WHERE cell_group_leader = ?',
            [userId]
        );

        // If not a cell group leader, return all members
        if (cellGroup.length === 0) {
            const [allMembers] = await pool.query(
                'SELECT id, first_name, last_name, role, email, profile_url, contacts ' +
                'FROM users WHERE role = "MEMBER"'
            );
            return allMembers;
        }

        // If they are a cell group leader, return their cell group members
        const cellGroupId = cellGroup[0].id;
        const [cellMembers] = await pool.query(
            'SELECT u.id, u.first_name, u.last_name, u.role, u.email, u.profile_url, u.contacts ' +
            'FROM users u JOIN cell_group_members cgm ON u.id = cgm.user_id ' +
            'WHERE cgm.cell_group_id = ?',
            [cellGroupId]
        );

        return cellMembers;
    } catch (error) {
        console.error('Error in getMembersForUser:', error);
        throw error;
    }
}

    static async getCategorizedUsers(userId) {
    try {
        // First, get the role of the requesting user

        const [userRows] = await pool.query('SELECT role FROM users WHERE id = ?', [userId]);
        if (userRows.length === 0) {
            let allUsers = [];
            let categories = {
                Members: [],
                DeskOfficers: [],
                MyMembers: [],
                CellGroupLeaders: []
            };
            return {
            allUsers,
            categories,
            'userRole':null,
            isAllowedBloadcast:false,
            };
        }
        
        const userRole = userRows[0].role;
        let isAllowedBloadcast = false;
        let allUsers = [];
        let categories = {
            Members: [],
            DeskOfficers: [],
            MyMembers: [],
            CellGroupLeaders: []
        };

        if (userRole === 'SUPER_ADMIN' || userRole === 'DESK_OFFICER') {
            // Get all users except SUPER_ADMIN
            [allUsers] = await pool.query(
                'SELECT id, first_name, last_name, role, email, profile_url, contacts FROM users WHERE (role = ? OR role = ?) AND id != ?',
                ['DESK_OFFICER', 'MEMBER', userId]
            );

            // Categorize users
            categories.Members = allUsers.filter(user => user.role === 'MEMBER');
            categories.DeskOfficers = allUsers.filter(user => user.role === 'DESK_OFFICER');
            
            // Get cell group leaders (MEMBERS who are leaders)
            const [leaders] = await pool.query(
                'SELECT u.id, u.first_name, u.last_name, u.role, u.email, u.profile_url, u.contacts ' +
                'FROM users u JOIN cell_groups cg ON u.id = cg.cell_group_leader ' +
                'WHERE u.role = "MEMBER"'
            );
            categories.CellGroupLeaders = leaders;
            isAllowedBloadcast = true;
        } 
        else if (userRole === 'MEMBER') {
            // Check if this member is a cell group leader
            const [isLeader] = await pool.query(
                'SELECT id FROM cell_groups WHERE cell_group_leader = ?',
                [userId]
            );
            
            // Get all users
            [allUsers] = await pool.query(
                'SELECT id, first_name, last_name, role, email, profile_url, contacts FROM users WHERE (role = ? OR role = ?) AND id != ?',
                ['DESK_OFFICER', 'MEMBER', userId]
            );
            
            
            if (isLeader.length > 0) {
                // This member is a leader - get their cell group members
                isAllowedBloadcast = true;
                const [myMembers] = await pool.query(
                    'SELECT u.id, u.first_name, u.last_name, u.role, u.email, u.profile_url, u.contacts ' +
                    'FROM users u JOIN cell_group_members cgm ON u.id = cgm.user_id ' +
                    'WHERE cgm.cell_group_id IN (SELECT id FROM cell_groups WHERE cell_group_leader = ?) AND u.id != ?',
                    [userId, userId]
                );
                categories.MyMembers = myMembers;
            } else {
                categories.MyMembers = [];
            }
            
            // Get all cell group leaders (excluding themselves if they are one)
            const [leaders] = await pool.query(
                'SELECT u.id, u.first_name, u.last_name, u.role, u.email, u.profile_url, u.contacts ' +
                'FROM users u JOIN cell_groups cg ON u.id = cg.cell_group_leader ' +
                'WHERE u.role = "MEMBER" AND u.id != ?',
                [userId]
            );
            categories.CellGroupLeaders = leaders;
            
            // Desk officers is empty for members
            categories.DeskOfficers = [];
        }

        return {
            allUsers,
            categories,
            'userRole':userRole,
            'isAllowedBloadcast': isAllowedBloadcast
        };
    } catch (error) {
        throw error;
    }
}

    static async updateDeskOfficer(id, { first_name, last_name, contacts }) {
        const [result] = await pool.query(
            'UPDATE users SET first_name = ?, last_name = ?, contacts = ? WHERE id = ? AND role = "DESK_OFFICER"',
            [first_name, last_name, contacts, id]
        );
        return result.affectedRows;
    }

    static async approvePartner(id) {
        const [result] = await pool.query(
            'UPDATE users SET role = "PARTNER" WHERE id = ?',
            [id]
        );
        return result.affectedRows;
    }

    static async declinePartner(id) {
        const [result] = await pool.query(
            'UPDATE users SET role = "PARTNER_DECLINED" WHERE id = ?',
            [id]
        );
        return result.affectedRows;
    }

    static async deleteDeskOfficer(id) {
        const [result] = await pool.query(
            'DELETE FROM users WHERE id = ? AND role = "DESK_OFFICER"',
            [id]
        );
        return result.affectedRows;
    }

    static async updateMember(id, { first_name, last_name, contacts }) {
        const [result] = await pool.query(
            'UPDATE users SET first_name = ?, last_name = ?, contacts = ? WHERE id = ? AND role = "MEMBER"',
            [first_name, last_name, contacts, id]
        );
        return result.affectedRows;
    }

    static async deleteMember(id) {
        const [result] = await pool.query(
            'DELETE FROM users WHERE id = ? AND role = "MEMBER"',
            [id]
        );
        return result.affectedRows;
    }

    // Add to userModel.js
static async generateResetToken(email) {
    // Generate a 4-digit OTP
    const otp = Math.floor(1000 + Math.random() * 9000);
    const token = require('crypto').randomBytes(32).toString('hex');
    
    // Set expiration to 10 minutes from now
    const expiration = new Date(Date.now() + 10 * 60 * 1000);
    
    await pool.query(
        'UPDATE users SET OTP = ?, passwordResetToken = ?, updated_at = ? WHERE email = ?',
        [otp, token, expiration, email]
    );
    
    return { otp, token };
}

static async verifyOTP(email, otp) {
    const [rows] = await pool.query(
        'SELECT passwordResetToken FROM users WHERE email = ? AND OTP = ? AND updated_at > NOW()',
        [email, otp]
    );
    
    if (rows.length === 0) {
        return null;
    }
    
    return rows[0].passwordResetToken;
}

static async resetPassword(token, newPassword) {
    const hashedPassword = await bcrypt.hash(newPassword, 10);
    
    const [result] = await pool.query(
        'UPDATE users SET password = ?, OTP = NULL, passwordResetToken = NULL WHERE passwordResetToken = ? AND updated_at > NOW()',
        [hashedPassword, token]
    );
    
    return result.affectedRows > 0;
}

// Add to userModel.js (optional - you can use direct query as shown above)
static async updateProfilePicture(userId, profileUrl) {
    const [result] = await pool.query(
        'UPDATE users SET profile_url = ? WHERE id = ?',
        [profileUrl, userId]
    );
    return result.affectedRows;
}

}

module.exports = User;