// apps/api/src/controllers/admin.controller.js

const bcrypt = require('bcryptjs');
const { DatabaseClient } = require('../../../../packages/db/client');
const { ROLES } = require('../../../../packages/shared/roles');

// Re-use single instance to protect connection pool architecture
const db = new DatabaseClient();

exports.getStats = async (req, res) => {
    try {
        const [
            userCount,
            activeUserCount,
            leadCount,
            activeLeadCount,
            bookmarkCount,
            searchCount,
            recentLeads,
            scoreDistribution,
            sourceDistribution
        ] = await Promise.all([
            db.get('SELECT COUNT(*) as count FROM users'),
            db.get('SELECT COUNT(*) as count FROM users WHERE is_active = 1'),
            db.get('SELECT COUNT(*) as count FROM leads'),
            db.get('SELECT COUNT(*) as count FROM leads WHERE is_active = 1'),
            db.get('SELECT COUNT(*) as count FROM bookmarks'),
            db.get('SELECT COUNT(*) as count FROM search_history'),
            db.query(
                `SELECT COUNT(*) as count, date(datetime(discovered_at / 1000, 'unixepoch')) as date 
                 FROM leads 
                 WHERE datetime(discovered_at / 1000, 'unixepoch') >= date('now', '-7 days')
                 GROUP BY date(datetime(discovered_at / 1000, 'unixepoch'))
                 ORDER BY date ASC`
            ),
            db.query('SELECT score_category, COUNT(*) as count FROM leads GROUP BY score_category'),
            db.query('SELECT source_type, COUNT(*) as count FROM leads WHERE source_type IS NOT NULL GROUP BY source_type')
        ]);

        res.json({
            users: { total: userCount?.count || 0, active: activeUserCount?.count || 0 },
            leads: { total: leadCount?.count || 0, active: activeLeadCount?.count || 0 },
            bookmarks: bookmarkCount?.count || 0,
            searches: searchCount?.count || 0,
            recent_activity: recentLeads || [],
            score_distribution: scoreDistribution || [],
            source_distribution: sourceDistribution || []
        });
    } catch (error) {
        console.error('Get stats error:', error);
        res.status(500).json({ error: 'Failed to get stats analytics matrices' });
    }
};

exports.getUsers = async (req, res) => {
    try {
        const page = Math.max(1, parseInt(req.query.page) || 1);
        const limit = Math.max(1, Math.min(100, parseInt(req.query.limit) || 20));
        const offset = (page - 1) * limit;

        const [users, totalRow] = await Promise.all([
            db.query(
                `SELECT id, email, full_name, company, phone, role, is_active, credits, 
                        searches_count, leads_viewed, bookmarks_count, last_login, created_at
                 FROM users
                 ORDER BY created_at DESC
                 LIMIT ? OFFSET ?`,
                [limit, offset]
            ),
            db.get('SELECT COUNT(*) as total FROM users')
        ]);

        const total = totalRow?.total || 0;

        res.json({
            users: users || [],
            pagination: { 
                page, 
                limit, 
                total, 
                pages: Math.ceil(total / limit) || 1 
            }
        });
    } catch (error) {
        console.error('Get users error:', error);
        res.status(500).json({ error: 'Failed to retrieve active users' });
    }
};

exports.createUser = async (req, res) => {
    try {
        const { email, password, full_name, company, phone, role } = req.body;
        if (!email || !password) {
            return res.status(400).json({ error: 'Email and password fields are strictly required' });
        }

        const normalizedEmail = email.trim().toLowerCase();
        const existing = await db.get('SELECT id FROM users WHERE email = ?', [normalizedEmail]);
        if (existing) {
            return res.status(400).json({ error: 'User already exists' });
        }

        const targetRole = Object.values(ROLES).includes(role) ? role : 'user';
        const hashedPassword = await bcrypt.hash(password, 10);

        const result = await db.run(
            'INSERT INTO users (email, password_hash, full_name, company, phone, role) VALUES (?, ?, ?, ?, ?, ?)',
            [normalizedEmail, hashedPassword, full_name || null, company || null, phone || null, targetRole]
        );

        await db.run(
            'INSERT INTO admin_activity_log (admin_id, action, target_type, target_id, details) VALUES (?, ?, ?, ?, ?)',
            [req.user.id, 'create_user', 'user', result.insertId, JSON.stringify({ email: normalizedEmail, role: targetRole })]
        );

        res.status(201).json({ message: 'User created successfully', user_id: result.insertId });
    } catch (error) {
        console.error('Create user error:', error);
        res.status(500).json({ error: 'Failed to create user schema entity' });
    }
};

exports.updateUser = async (req, res) => {
    try {
        const { full_name, company, phone, is_active } = req.body;
        const userId = req.params.id;

        const statusValue = is_active === true || is_active === 1 ? 1 : 0;

        const result = await db.run(
            'UPDATE users SET full_name = ?, company = ?, phone = ?, is_active = ? WHERE id = ?',
            [full_name || null, company || null, phone || null, statusValue, userId]
        );

        if (result.changes === 0) {
            return res.status(404).json({ error: 'Target user record not found' });
        }

        await db.run(
            'INSERT INTO admin_activity_log (admin_id, action, target_type, target_id, details) VALUES (?, ?, ?, ?, ?)',
            [req.user.id, 'update_user', 'user', userId, JSON.stringify({ full_name, company, phone, is_active: statusValue })]
        );

        res.json({ message: 'User updated successfully' });
    } catch (error) {
        console.error('Update user error:', error);
        res.status(500).json({ error: 'Failed to update user metrics parameters' });
    }
};

exports.deleteUser = async (req, res) => {
    try {
        const userId = req.params.id;

        const result = await db.run('DELETE FROM users WHERE id = ?', [userId]);
        if (result.changes === 0) {
            return res.status(404).json({ error: 'User target timeline record not found' });
        }

        await db.run(
            'INSERT INTO admin_activity_log (admin_id, action, target_type, target_id) VALUES (?, ?, ?, ?)',
            [req.user.id, 'delete_user', 'user', userId]
        );

        res.json({ message: 'User deleted successfully' });
    } catch (error) {
        console.error('Delete user error:', error);
        res.status(500).json({ error: 'Failed to clear user completely from nodes' });
    }
};

exports.changeUserRole = async (req, res) => {
    try {
        const { role } = req.body;
        const userId = req.params.id;

        if (!Object.values(ROLES).includes(role)) {
            return res.status(400).json({ error: 'Specified privilege context tier is invalid' });
        }

        const result = await db.run('UPDATE users SET role = ? WHERE id = ?', [role, userId]);
        if (result.changes === 0) {
            return res.status(404).json({ error: 'Target user not found' });
        }

        await db.run(
            'INSERT INTO admin_activity_log (admin_id, action, target_type, target_id, details) VALUES (?, ?, ?, ?, ?)',
            [req.user.id, 'change_role', 'user', userId, JSON.stringify({ role })]
        );

        res.json({ message: 'User role context updated successfully' });
    } catch (error) {
        console.error('Change role error:', error);
        res.status(500).json({ error: 'Failed to re-allocate system credentials privilege' });
    }
};

exports.getSignals = async (req, res) => {
    try {
        const page = Math.max(1, parseInt(req.query.page) || 1);
        const limit = Math.max(1, Math.min(100, parseInt(req.query.limit) || 20));
        const offset = (page - 1) * limit;
        const { status, source } = req.query;

        let whereClause = 'WHERE 1=1';
        const params = [];

        if (status === 'active') {
            whereClause += ' AND is_active = 1';
        } else if (status === 'inactive') {
            whereClause += ' AND is_active = 0';
        }

        if (source) {
            whereClause += ' AND source_type = ?';
            params.push(source);
        }

        // Add limits dynamically as arrays parameters
        const queryParams = [...params, limit, offset];

        const [signals, totalRow] = await Promise.all([
            db.query(
                `SELECT id, title, service_needed, location, source_url, source_type, 
                        urgency_level, score, score_category, is_active, discovered_at
                 FROM leads
                 ${whereClause}
                 ORDER BY discovered_at DESC
                 LIMIT ? OFFSET ?`,
                queryParams
            ),
            db.get(`SELECT COUNT(*) as total FROM leads ${whereClause}`, params)
        ]);

        const total = totalRow?.total || 0;

        res.json({
            signals: signals || [],
            pagination: { page, limit, total, pages: Math.ceil(total / limit) || 1 }
        });
    } catch (error) {
        console.error('Get signals error:', error);
        res.status(500).json({ error: 'Failed to route incoming context stream data' });
    }
};

exports.updateSignalStatus = async (req, res) => {
    try {
        const { is_active } = req.body;
        const signalId = req.params.id;

        const targetStatus = is_active === true || is_active === 1 ? 1 : 0;

        const result = await db.run('UPDATE leads SET is_active = ? WHERE id = ?', [targetStatus, signalId]);
        if (result.changes === 0) {
            return res.status(404).json({ error: 'Target telemetry context point missing' });
        }

        await db.run(
            'INSERT INTO admin_activity_log (admin_id, action, target_type, target_id, details) VALUES (?, ?, ?, ?, ?)',
            [req.user.id, 'update_signal_status', 'lead', signalId, JSON.stringify({ is_active: targetStatus })]
        );

        res.json({ message: 'Telemetry loop switch parameters applied directly' });
    } catch (error) {
        console.error('Update signal error:', error);
        res.status(500).json({ error: 'Failed to cycle status parameters loop state' });
    }
};

exports.deleteSignal = async (req, res) => {
    try {
        const signalId = req.params.id;

        const result = await db.run('DELETE FROM leads WHERE id = ?', [signalId]);
        if (result.changes === 0) {
            return res.status(404).json({ error: 'Target lead telemetry trace not mapped' });
        }

        await db.run(
            'INSERT INTO admin_activity_log (admin_id, action, target_type, target_id) VALUES (?, ?, ?, ?)',
            [req.user.id, 'delete_signal', 'lead', signalId]
        );

        res.json({ message: 'Signal purged from system telemetry network architecture' });
    } catch (error) {
        console.error('Delete signal error:', error);
        res.status(500).json({ error: 'Failed execution sequence on datastore context' });
    }
};

exports.getAnalytics = async (req, res) => {
    try {
        const period = req.query.period === '30d' ? '-30 days' : '-7 days';

        const [leadsByDay, topServices, topSources, userActivity] = await Promise.all([
            db.query(
                `SELECT date(datetime(discovered_at / 1000, 'unixepoch')) as date, COUNT(*) as count
                 FROM leads
                 WHERE datetime(discovered_at / 1000, 'unixepoch') >= date('now', ?)
                 GROUP BY date(datetime(discovered_at / 1000, 'unixepoch'))
                 ORDER BY date ASC`,
                [period]
            ),
            db.query(
                `SELECT service_needed, COUNT(*) as count
                 FROM leads
                 WHERE service_needed IS NOT NULL
                 GROUP BY service_needed
                 ORDER BY count DESC
                 LIMIT 10`
            ),
            db.query(
                `SELECT source_type, COUNT(*) as count
                 FROM leads
                 GROUP BY source_type
                 ORDER BY count DESC`
            ),
            db.query(
                `SELECT DATE(created_at) as date, COUNT(DISTINCT user_id) as active_users
                 FROM search_history
                 WHERE created_at >= DATE('now', '-7 days')
                 GROUP BY DATE(created_at)
                 ORDER BY date ASC`
            )
        ]);

        res.json({
            leads_by_day: leadsByDay || [],
            top_services: topServices || [],
            top_sources: topSources || [],
            user_activity: userActivity || []
        });
    } catch (error) {
        console.error('Get analytics error:', error);
        res.status(500).json({ error: 'Analytics framework metric trace down' });
    }
};

exports.getLogs = async (req, res) => {
    try {
        const page = Math.max(1, parseInt(req.query.page) || 1);
        const limit = Math.max(1, Math.min(200, parseInt(req.query.limit) || 50));
        const offset = (page - 1) * limit;

        const [logs, totalRow] = await Promise.all([
            db.query(
                `SELECT a.*, u.email as admin_email
                 FROM admin_activity_log a
                 LEFT JOIN users u ON a.admin_id = u.id
                 ORDER BY a.created_at DESC
                 LIMIT ? OFFSET ?`,
                [limit, offset]
            ),
            db.get('SELECT COUNT(*) as total FROM admin_activity_log')
        ]);

        const total = totalRow?.total || 0;

        res.json({
            logs: logs || [],
            pagination: { page, limit, total, pages: Math.ceil(total / limit) || 1 }
        });
    } catch (error) {
        console.error('Get logs error:', error);
        res.status(500).json({ error: 'Unable to scan central audit registry logs' });
    }
};

exports.getSettings = async (req, res) => {
    try {
        const settings = await db.query('SELECT * FROM system_settings');
        const settingsObject = {};
        
        if (settings && Array.isArray(settings)) {
            for (const setting of settings) {
                let value = setting.setting_value;
                if (setting.setting_type === 'boolean') {
                    value = value === 'true' || value === '1';
                } else if (setting.setting_type === 'integer') {
                    value = parseInt(value, 10);
                } else if (setting.setting_type === 'json') {
                    try { value = JSON.parse(value); } catch { value = {}; }
                }
                settingsObject[setting.setting_key] = value;
            }
        }
        res.json(settingsObject);
    } catch (error) {
        console.error('Get settings error:', error);
        res.status(500).json({ error: 'Failed structural pull of environment settings' });
    }
};

exports.updateSettings = async (req, res) => {
    try {
        const settings = req.body;
        if (!settings || typeof settings !== 'object') {
            return res.status(400).json({ error: 'Payload configuration invalid shape' });
        }
        
        for (const [key, value] of Object.entries(settings)) {
            if (value === undefined || value === null) continue;

            let settingValue = value.toString();
            let settingType = 'string';

            if (typeof value === 'boolean') {
                settingType = 'boolean';
                settingValue = value ? 'true' : 'false';
            } else if (typeof value === 'number') {
                settingType = 'integer';
            } else if (typeof value === 'object') {
                settingValue = JSON.stringify(value);
                settingType = 'json';
            }

            await db.run(
                `INSERT INTO system_settings (setting_key, setting_value, setting_type, updated_by)
                 VALUES (?, ?, ?, ?)
                 ON CONFLICT(setting_key) DO UPDATE SET 
                    setting_value = excluded.setting_value, 
                    setting_type = excluded.setting_type, 
                    updated_by = excluded.updated_by`,
                [key, settingValue, settingType, req.user.id]
            );
        }

        await db.run(
            'INSERT INTO admin_activity_log (admin_id, action, details) VALUES (?, ?, ?)',
            [req.user.id, 'update_settings', JSON.stringify(Object.keys(settings))]
        );

        res.json({ message: 'Global cluster metrics variables saved' });
    } catch (error) {
        console.error('Update settings error:', error);
        res.status(500).json({ error: 'Upsert collision error applying system variables' });
    }
};

// =========================================================================
// Data Stream Channels (Registry Tracking Control)
// =========================================================================

exports.getChannels = async (req, res) => {
    try {
        const page = Math.max(1, parseInt(req.query.page) || 1);
        const limit = Math.max(1, Math.min(100, parseInt(req.query.limit) || 20));
        const offset = (page - 1) * limit;

        const [channels, totalRow] = await Promise.all([
            db.query(
                `SELECT id, name, engine_type, target_url, extraction_config, interval_minutes, is_active, last_run_at, next_run_at, created_at, updated_at
                 FROM pure_demand_channels
                 ORDER BY created_at DESC
                 LIMIT ? OFFSET ?`,
                [limit, offset]
            ),
            db.get('SELECT COUNT(*) as total FROM pure_demand_channels')
        ]);

        const total = totalRow?.total || 0;

        res.json({
            channels: channels || [],
            pagination: {
                page,
                limit,
                total,
                pages: Math.ceil(total / limit) || 1
            }
        });
    } catch (error) {
        console.error('Get channels error:', error);
        res.status(500).json({ error: 'Failed to retrieve active ingestion channels data' });
    }
};

exports.createChannel = async (req, res) => {
    try {
        const { name, engine_type, target_url, extraction_config, interval_minutes } = req.body;
        if (!name || !engine_type || !target_url) {
            return res.status(400).json({ error: 'Channel name, engine type, and target URL are strictly required' });
        }

        const configString = extraction_config && typeof extraction_config === 'object' 
            ? JSON.stringify(extraction_config) 
            : extraction_config || null;

        const interval = parseInt(interval_minutes) || 60;

        const result = await db.run(
            `INSERT INTO pure_demand_channels (name, engine_type, target_url, extraction_config, interval_minutes) 
             VALUES (?, ?, ?, ?, ?)`,
            [name.trim(), engine_type.trim(), target_url.trim(), configString, interval]
        );

        await db.run(
            'INSERT INTO admin_activity_log (admin_id, action, target_type, target_id, details) VALUES (?, ?, ?, ?, ?)',
            [req.user.id, 'create_channel', 'channel', result.insertId, JSON.stringify({ name, engine_type })]
        );

        res.status(201).json({ message: 'Data channel provisioned successfully', channel_id: result.insertId });
    } catch (error) {
        console.error('Create channel error:', error);
        res.status(500).json({ error: 'Failed to register source channel pipeline' });
    }
};

exports.updateChannelStatus = async (req, res) => {
    try {
        const { is_active } = req.body;
        const channelId = req.params.id;

        const targetStatus = is_active === true || is_active === 1 ? 1 : 0;

        const result = await db.run(
            'UPDATE pure_demand_channels SET is_active = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?', 
            [targetStatus, channelId]
        );
        
        if (result.changes === 0) {
            return res.status(404).json({ error: 'Target ingestion channel record not found' });
        }

        await db.run(
            'INSERT INTO admin_activity_log (admin_id, action, target_type, target_id, details) VALUES (?, ?, ?, ?, ?)',
            [req.user.id, 'update_channel_status', 'channel', channelId, JSON.stringify({ is_active: targetStatus })]
        );

        res.json({ message: 'Channel pipeline loop status updated successfully' });
    } catch (error) {
        console.error('Update channel status error:', error);
        res.status(500).json({ error: 'Failed to toggle channel runtime parameters' });
    }
};

exports.deleteChannel = async (req, res) => {
    try {
        const channelId = req.params.id;

        const result = await db.run('DELETE FROM pure_demand_channels WHERE id = ?', [channelId]);
        if (result.changes === 0) {
            return res.status(404).json({ error: 'Target ingestion channel trace not mapped' });
        }

        await db.run(
            'INSERT INTO admin_activity_log (admin_id, action, target_type, target_id) VALUES (?, ?, ?, ?)',
            [req.user.id, 'delete_channel', 'channel', channelId]
        );

        res.json({ message: 'Channel registry purged safely from node architecture' });
    } catch (error) {
        console.error('Delete channel error:', error);
        res.status(500).json({ error: 'Failed execution sequence on datastore context' });
    }
};