// apps/api/src/db/queries.js

const { DatabaseClient } = require('./client');

const db = new DatabaseClient();

class LeadQueries {
    static async findIntentSignals(service, location, limit = 50) {
        let sql = `
            SELECT * FROM leads 
            WHERE is_active = 1 
            AND (service_needed LIKE ? OR title LIKE ? OR description LIKE ?)
        `;
        const params = [`%${service}%`, `%${service}%`, `%${service}%`];
        
        if (location) {
            sql += ' AND location LIKE ?';
            params.push(`%${location}%`);
        }
        
        sql += ' ORDER BY score DESC LIMIT ?';
        params.push(limit);
        
        return await db.query(sql, params);
    }
    
    static async getHotLeads(limit = 20) {
        return await db.query(
            'SELECT * FROM leads WHERE score_category = "hot" AND is_active = 1 ORDER BY score DESC LIMIT ?',
            [limit]
        );
    }
    
    static async getRecentLeads(limit = 20) {
        return await db.query(
            'SELECT * FROM leads WHERE is_active = 1 ORDER BY discovered_at DESC LIMIT ?',
            [limit]
        );
    }
    
    static async getLeadsByService(service, limit = 20) {
        return await db.query(
            `SELECT * FROM leads 
             WHERE service_needed LIKE ? AND is_active = 1 
             ORDER BY score DESC LIMIT ?`,
            [`%${service}%`, limit]
        );
    }
    
    static async incrementContactCount(leadId) {
        await db.run(
            'UPDATE leads SET times_contacted = times_contacted + 1 WHERE id = ?',
            [leadId]
        );
    }
}

class UserQueries {
    static async getUserWithStats(userId) {
        return await db.get(
            `SELECT u.*, 
                    (SELECT COUNT(*) FROM bookmarks WHERE user_id = u.id) as total_bookmarks,
                    (SELECT COUNT(*) FROM search_history WHERE user_id = u.id) as total_searches
             FROM users u WHERE u.id = ?`,
            [userId]
        );
    }
    
    static async deductCredit(userId) {
        await db.run(
            'UPDATE users SET credits = credits - 1 WHERE id = ? AND credits > 0',
            [userId]
        );
    }
    
    static async addCredits(userId, amount) {
        await db.run('UPDATE users SET credits = credits + ? WHERE id = ?', [amount, userId]);
    }
}

class StatsQueries {
    static async getDailyStats(date) {
        // Fixed FROM dual syntax constraint by dropping it (SQLite evaluates pure SELECT values natively)
        // Fixed timestamp conversions to match the millisecond epoch integers architecture
        return await db.get(
            `SELECT 
                (SELECT COUNT(*) FROM leads WHERE date(datetime(discovered_at / 1000, 'unixepoch')) = ?) as new_leads,
                (SELECT COUNT(*) FROM search_history WHERE date(created_at) = ?) as searches,
                (SELECT COUNT(*) FROM users WHERE date(created_at) = ?) as new_users`,
            [date, date, date]
        );
    }
    
    static async getTopServices(limit = 10) {
        return await 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 ?`,
            [limit]
        );
    }
}

module.exports = { LeadQueries, UserQueries, StatsQueries };