// apps/api/src/services/admin.service.js

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

const db = new DatabaseClient();

class AdminService {
    async getSystemStats() {
        const userCount = await db.get('SELECT COUNT(*) as count FROM users');
        const activeUserCount = await db.get('SELECT COUNT(*) as count FROM users WHERE is_active = 1');
        const leadCount = await db.get('SELECT COUNT(*) as count FROM leads');
        const activeLeadCount = await db.get('SELECT COUNT(*) as count FROM leads WHERE is_active = 1');
        const bookmarkCount = await db.get('SELECT COUNT(*) as count FROM bookmarks');
        const searchCount = await db.get('SELECT COUNT(*) as count FROM search_history');
        
        const recentLeads = await 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`
        );
        
        const scoreDistribution = await db.query(
            'SELECT score_category, COUNT(*) as count FROM leads GROUP BY score_category'
        );
        
        const sourceDistribution = await db.query(
            'SELECT source_type, COUNT(*) as count FROM leads WHERE source_type IS NOT NULL GROUP BY source_type'
        );
        
        return {
            users: { total: userCount.count, active: activeUserCount.count },
            leads: { total: leadCount.count, active: activeLeadCount.count },
            bookmarks: bookmarkCount.count,
            searches: searchCount.count,
            recent_activity: recentLeads,
            score_distribution: scoreDistribution,
            source_distribution: sourceDistribution
        };
    }
    
    async getUsers(filters = {}, pagination = {}) {
        const { role, is_active, search } = filters;
        const { limit = 20, offset = 0 } = pagination;
        
        let whereClause = 'WHERE 1=1';
        const params = [];
        
        if (role) {
            whereClause += ' AND role = ?';
            params.push(role);
        }
        
        if (is_active !== undefined) {
            whereClause += ' AND is_active = ?';
            params.push(is_active);
        }
        
        if (search) {
            whereClause += ' AND (email LIKE ? OR full_name LIKE ?)';
            const searchTerm = `%${search}%`;
            params.push(searchTerm, searchTerm);
        }
        
        const users = await 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
             ${whereClause}
             ORDER BY created_at DESC
             LIMIT ? OFFSET ?`,
            [...params, limit, offset]
        );
        
        const total = await db.get(`SELECT COUNT(*) as total FROM users ${whereClause}`, params);
        
        return { users, total: total.total };
    }
    
    async createUser(userData) {
        const existing = await db.get('SELECT id FROM users WHERE email = ?', [userData.email]);
        if (existing) {
            throw new Error('User already exists');
        }
        
        const hashedPassword = await bcrypt.hash(userData.password, 10);
        
        const result = await db.run(
            `INSERT INTO users (email, password_hash, full_name, company, phone, role, is_active)
             VALUES (?, ?, ?, ?, ?, ?, ?)`,
            [userData.email, hashedPassword, userData.full_name, userData.company, 
             userData.phone, userData.role || 'user', userData.is_active !== false]
        );
        
        return result.insertId;
    }
    
    async updateUser(userId, userData) {
        const fields = [];
        const values = [];
        
        const allowedFields = ['full_name', 'company', 'phone', 'role', 'is_active'];
        
        for (const field of allowedFields) {
            if (userData[field] !== undefined) {
                fields.push(`${field} = ?`);
                values.push(userData[field]);
            }
        }
        
        if (userData.password) {
            const hashedPassword = await bcrypt.hash(userData.password, 10);
            fields.push('password_hash = ?');
            values.push(hashedPassword);
        }
        
        if (fields.length === 0) return false;
        
        values.push(userId);
        await db.run(`UPDATE users SET ${fields.join(', ')} WHERE id = ?`, values);
        
        return true;
    }
    
    async deleteUser(userId) {
        // Check if user has bookmarks or other data
        await db.run('DELETE FROM bookmarks WHERE user_id = ?', [userId]);
        await db.run('DELETE FROM search_history WHERE user_id = ?', [userId]);
        await db.run('DELETE FROM users WHERE id = ?', [userId]);
        return true;
    }
    
    async changeUserRole(userId, role) {
        if (!Object.values(ROLES).includes(role)) {
            throw new Error('Invalid role');
        }
        
        await db.run('UPDATE users SET role = ? WHERE id = ?', [role, userId]);
        return true;
    }
    
    async getSignals(filters = {}, pagination = {}) {
        const { status, source_type, service_needed } = filters;
        const { limit = 20, offset = 0 } = pagination;
        
        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_type) {
            whereClause += ' AND source_type = ?';
            params.push(source_type);
        }
        
        if (service_needed) {
            whereClause += ' AND service_needed LIKE ?';
            params.push(`%${service_needed}%`);
        }
        
        const signals = await db.query(
            `SELECT id, title, service_needed, location, source_url, source_type, 
                    urgency_level, score, score_category, is_active, confidence_score,
                    times_displayed, times_contacted, discovered_at
             FROM leads
             ${whereClause}
             ORDER BY discovered_at DESC
             LIMIT ? OFFSET ?`,
            [...params, limit, offset]
        );
        
        const total = await db.get(`SELECT COUNT(*) as total FROM leads ${whereClause}`, params);
        
        return { signals, total: total.total };
    }
    
    async updateSignalStatus(signalId, isActive) {
        await db.run('UPDATE leads SET is_active = ? WHERE id = ?', [isActive, signalId]);
        return true;
    }
    
    async deleteSignal(signalId) {
        await db.run('DELETE FROM bookmarks WHERE lead_id = ?', [signalId]);
        await db.run('DELETE FROM signal_feedback WHERE lead_id = ?', [signalId]);
        await db.run('DELETE FROM leads WHERE id = ?', [signalId]);
        return true;
    }
    
    async getSystemSettings() {
        const settings = await db.query('SELECT * FROM system_settings');
        const settingsObject = {};
        
        for (const setting of settings) {
            let value = setting.setting_value;
            if (setting.setting_type === 'boolean') {
                value = value === 'true';
            } else if (setting.setting_type === 'integer') {
                value = parseInt(value);
            } else if (setting.setting_type === 'json') {
                value = JSON.parse(value);
            }
            settingsObject[setting.setting_key] = value;
        }
        
        return settingsObject;
    }
    
    async updateSystemSetting(key, value, adminId) {
        let settingType = 'string';
        let settingValue = value.toString();
        
        if (typeof value === 'boolean') {
            settingType = 'boolean';
        } 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, adminId]
        );
        
        return true;
    }
    
    async logAdminActivity(adminId, action, targetType = null, targetId = null, details = null) {
        await db.run(
            `INSERT INTO admin_activity_log (admin_id, action, target_type, target_id, details)
             VALUES (?, ?, ?, ?, ?)`,
            [adminId, action, targetType, targetId, details ? JSON.stringify(details) : null]
        );
    }
    
    async getAdminLogs(filters = {}, pagination = {}) {
        const { admin_id, action } = filters;
        const { limit = 50, offset = 0 } = pagination;
        
        let whereClause = 'WHERE 1=1';
        const params = [];
        
        if (admin_id) {
            whereClause += ' AND admin_id = ?';
            params.push(admin_id);
        }
        
        if (action) {
            whereClause += ' AND action = ?';
            params.push(action);
        }
        
        const logs = await db.query(
            `SELECT a.*, u.email as admin_email
             FROM admin_activity_log a
             LEFT JOIN users u ON a.admin_id = u.id
             ${whereClause}
             ORDER BY a.created_at DESC
             LIMIT ? OFFSET ?`,
            [...params, limit, offset]
        );
        
        const total = await db.get(`SELECT COUNT(*) as total FROM admin_activity_log ${whereClause}`, params);
        
        return { logs, total: total.total };
    }
}

module.exports = new AdminService();