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

class CellGroup {
    static async create(name, location_name, location_address, location_latitude = null, location_longitude = null, cell_group_leader = null) {
        try {
            const sql = `
                INSERT INTO cell_groups 
                (name, location_name, location_address, location_latitude, location_longtude, cell_group_leader) 
                VALUES (?, ?, ?, ?, ?, ?)
            `;
            const params = [name, location_name, location_address, location_latitude, location_longitude, cell_group_leader];

            const [result] = await pool.query(sql, params);
            return result.insertId;
        } catch (error) {
            throw error;
        }
    }

    static async getById(id) {
        try {
            const [rows] = await pool.query('SELECT * FROM cell_groups WHERE id = ?', [id]);
            return rows[0];
        } catch (error) {
            throw error;
        }
    }

    static async getAll() {
        try {
            const [rows] = await pool.query('SELECT * FROM cell_groups');
            return rows;
        } catch (error) {
            throw error;
        }
    }

    static async update(id, name, location_name, location_address, location_latitude = null, location_longitude = null) {
        try {
            const sql = `
                UPDATE cell_groups 
                SET name = ?, 
                    location_name = ?,
                    location_address = ?,
                    location_latitude = ?,
                    location_longtude = ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            `;
            const params = [name, location_name, location_address, location_latitude, location_longitude, id];

            const [result] = await pool.query(sql, params);
            return result.affectedRows > 0;
        } catch (error) {
            throw error;
        }
    }

    static async remove(id) {
        try {
            const [result] = await pool.query('DELETE FROM cell_groups WHERE id = ?', [id]);
            return result.affectedRows > 0;
        } catch (error) {
            throw error;
        }
    }

    static async assignLeader(cellGroupId, userId) {
        try {
            const sql = `
                UPDATE cell_groups 
                SET cell_group_leader = ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            `;
            const params = [userId, cellGroupId];

            const [result] = await pool.query(sql, params);
            return result.affectedRows > 0;
        } catch (error) {
            throw error;
        }
    }

    static async getByLeader(userId) {
        try {
            const [rows] = await pool.query('SELECT * FROM cell_groups WHERE cell_group_leader = ?', [userId]);
            return rows;
        } catch (error) {
            throw error;
        }
    }
}

module.exports = CellGroup;