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

const { DatabaseClient } = require('../../../packages/db/client');
const scoreService = require('./score.service');

const db = new DatabaseClient();

class LeadsService {
    async getLeads(filters = {}, pagination = {}) {
        const { service, location, score_category, source_type, limit = 20, offset = 0 } = filters;
        
        let whereClause = 'WHERE is_active = 1';
        const params = [];
        
        if (service) {
            whereClause += ' AND (service_needed LIKE ? OR title LIKE ? OR description LIKE ?)';
            const searchTerm = `%${service}%`;
            params.push(searchTerm, searchTerm, searchTerm);
        }
        
        if (location) {
            whereClause += ' AND location LIKE ?';
            params.push(`%${location}%`);
        }
        
        if (score_category) {
            whereClause += ' AND score_category = ?';
            params.push(score_category);
        }
        
        if (source_type) {
            whereClause += ' AND source_type = ?';
            params.push(source_type);
        }
        
        const leads = await db.query(
            `SELECT id, title, description, service_needed, location, contact_phone, contact_email,
                    contact_whatsapp, source_url, source_type, urgency_level, confidence_score,
                    score, score_category, posted_date, discovered_at
             FROM leads
             ${whereClause}
             ORDER BY score DESC, discovered_at DESC
             LIMIT ? OFFSET ?`,
            [...params, limit, offset]
        );
        
        const total = await db.get(
            `SELECT COUNT(*) as total FROM leads ${whereClause}`,
            params
        );
        
        return { leads, total: total.total };
    }
    
    async getLeadById(id, userId = null) {
        let query = `
            SELECT l.*, 
                   (SELECT COUNT(*) FROM bookmarks WHERE lead_id = l.id AND user_id = ?) as is_bookmarked
            FROM leads l
            WHERE l.id = ? AND l.is_active = 1
        `;
        
        const lead = await db.get(query, [userId || 0, id]);
        
        if (lead && userId) {
            await db.run('UPDATE leads SET times_displayed = times_displayed + 1 WHERE id = ?', [id]);
        }
        
        return lead;
    }
    
    async createLead(leadData) {
        const existing = await db.get(
            'SELECT id FROM leads WHERE source_url = ?',
            [leadData.source_url]
        );
        
        if (existing) {
            return existing.id;
        }
        
        const scoreData = scoreService.calculateScore({
            text: `${leadData.title} ${leadData.description || ''}`,
            freshness: leadData.posted_date || new Date(),
            hasContact: !!(leadData.contact_phone || leadData.contact_email),
            urgency: leadData.urgency_level || 'medium',
            clarity: leadData.description && leadData.description.length > 100 ? 'high' : 'medium'
        });
        
        const result = await db.run(
            `INSERT INTO leads (
                title, description, service_needed, location, contact_phone, contact_email,
                contact_whatsapp, source_url, source_type, urgency_level, urgency_keywords,
                budget_hint, confidence_score, score, score_category, posted_date, discovered_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                leadData.title, leadData.description, leadData.service_needed, leadData.location,
                leadData.contact_phone, leadData.contact_email, leadData.contact_whatsapp,
                leadData.source_url, leadData.source_type, leadData.urgency_level || 'medium',
                leadData.urgency_keywords, leadData.budget_hint, leadData.confidence_score || 50,
                scoreData.score, scoreData.category, leadData.posted_date || new Date(), new Date()
            ]
        );
        
        return result.insertId;
    }
    
    async updateLead(id, leadData) {
        const fields = [];
        const values = [];
        
        const allowedFields = ['title', 'description', 'service_needed', 'location', 'contact_phone', 
                               'contact_email', 'contact_whatsapp', 'urgency_level', 'budget_hint', 'is_active'];
        
        for (const field of allowedFields) {
            if (leadData[field] !== undefined) {
                fields.push(`${field} = ?`);
                values.push(leadData[field]);
            }
        }
        
        if (fields.length === 0) return false;
        
        values.push(id);
        await db.run(`UPDATE leads SET ${fields.join(', ')} WHERE id = ?`, values);
        
        if (leadData.title || leadData.description || leadData.urgency_level) {
            const lead = await this.getLeadById(id);
            const scoreData = scoreService.calculateScore({
                text: `${lead.title} ${lead.description || ''}`,
                freshness: lead.posted_date,
                hasContact: !!(lead.contact_phone || lead.contact_email),
                urgency: lead.urgency_level,
                clarity: lead.description && lead.description.length > 100 ? 'high' : 'medium'
            });
            await db.run('UPDATE leads SET score = ?, score_category = ? WHERE id = ?', 
                        [scoreData.score, scoreData.category, id]);
        }
        
        return true;
    }
    
    async deleteLead(id) {
        await db.run('DELETE FROM leads WHERE id = ?', [id]);
        return true;
    }
    
    async getLeadStats() {
        const total = await db.get('SELECT COUNT(*) as count FROM leads WHERE is_active = 1');
        const byCategory = await db.query(
            'SELECT score_category, COUNT(*) as count FROM leads WHERE is_active = 1 GROUP BY score_category'
        );
        const bySource = await db.query(
            'SELECT source_type, COUNT(*) as count FROM leads WHERE is_active = 1 GROUP BY source_type'
        );
        // Fixed for SQLite - removed DATE_SUB and NOW()
        const recent = await db.query(
            `SELECT COUNT(*) as count, date(discovered_at) as date 
             FROM leads 
             WHERE discovered_at >= date('now', '-7 days') 
             GROUP BY date(discovered_at)`
        );
        
        return { total: total.count, byCategory, bySource, recent };
    }
}

module.exports = new LeadsService();