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

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

const db = new DatabaseClient();

class BookmarksService {
    async getBookmarks(userId, filters = {}, pagination = {}) {
        const { folder } = filters;
        const { limit = 20, offset = 0 } = pagination;
        
        let whereClause = 'WHERE b.user_id = ?';
        const params = [userId];
        
        if (folder && folder !== 'all') {
            whereClause += ' AND b.folder = ?';
            params.push(folder);
        }
        
        const bookmarks = await db.query(
            `SELECT b.id, b.notes, b.folder, b.created_at, b.updated_at,
                    l.id as lead_id, l.title, l.description, l.service_needed, l.location,
                    l.contact_phone, l.contact_email, l.contact_whatsapp, l.source_url,
                    l.score_category, l.discovered_at
             FROM bookmarks b
             JOIN leads l ON b.lead_id = l.id
             ${whereClause}
             ORDER BY b.created_at DESC
             LIMIT ? OFFSET ?`,
            [...params, limit, offset]
        );
        
        const total = await db.get(
            `SELECT COUNT(*) as total FROM bookmarks b WHERE b.user_id = ?`,
            [userId]
        );
        
        return { bookmarks, total: total.total };
    }
    
    async createBookmark(userId, leadId, notes = null, folder = 'default') {
        const existing = await db.get(
            'SELECT id FROM bookmarks WHERE user_id = ? AND lead_id = ?',
            [userId, leadId]
        );
        
        if (existing) {
            throw new Error('Bookmark already exists');
        }
        
        const result = await db.run(
            'INSERT INTO bookmarks (user_id, lead_id, notes, folder) VALUES (?, ?, ?, ?)',
            [userId, leadId, notes, folder]
        );
        
        await db.run('UPDATE users SET bookmarks_count = bookmarks_count + 1 WHERE id = ?', [userId]);
        
        return result.insertId;
    }
    
    async deleteBookmark(userId, bookmarkId) {
        const result = await db.run(
            'DELETE FROM bookmarks WHERE id = ? AND user_id = ?',
            [bookmarkId, userId]
        );
        
        if (result.changes || result.affectedRows) {
            await db.run('UPDATE users SET bookmarks_count = bookmarks_count - 1 WHERE id = ?', [userId]);
        }
        
        return result.changes > 0 || result.affectedRows > 0;
    }
    
    async updateBookmarkNotes(userId, bookmarkId, notes) {
        await db.run(
            'UPDATE bookmarks SET notes = ? WHERE id = ? AND user_id = ?',
            [notes, bookmarkId, userId]
        );
        return true;
    }
    
    async moveToFolder(userId, bookmarkId, folder) {
        await db.run(
            'UPDATE bookmarks SET folder = ? WHERE id = ? AND user_id = ?',
            [folder, bookmarkId, userId]
        );
        return true;
    }
    
    async getFolders(userId) {
        const customFolders = await db.query(
            `SELECT id, name, color, 
                    (SELECT COUNT(*) FROM bookmarks WHERE user_id = ? AND folder = name) as count
             FROM bookmark_folders
             WHERE user_id = ?`,
            [userId, userId]
        );
        
        const defaultCount = await db.get(
            'SELECT COUNT(*) as count FROM bookmarks WHERE user_id = ? AND folder = "default"',
            [userId]
        );
        
        const folders = [
            { id: null, name: 'default', color: '#6B7280', count: defaultCount.count, is_default: true },
            ...customFolders
        ];
        
        return folders;
    }
    
    async createFolder(userId, name, color = '#6B7280') {
        const existing = await db.get(
            'SELECT id FROM bookmark_folders WHERE user_id = ? AND name = ?',
            [userId, name]
        );
        
        if (existing) {
            throw new Error('Folder already exists');
        }
        
        const result = await db.run(
            'INSERT INTO bookmark_folders (user_id, name, color) VALUES (?, ?, ?)',
            [userId, name, color]
        );
        
        return result.insertId;
    }
    
    async deleteFolder(userId, folderId) {
        const folder = await db.get(
            'SELECT name FROM bookmark_folders WHERE id = ? AND user_id = ?',
            [folderId, userId]
        );
        
        if (folder) {
            await db.run(
                'UPDATE bookmarks SET folder = "default" WHERE user_id = ? AND folder = ?',
                [userId, folder.name]
            );
            await db.run(
                'DELETE FROM bookmark_folders WHERE id = ? AND user_id = ?',
                [folderId, userId]
            );
        }
        
        return true;
    }
    
    async isBookmarked(userId, leadId) {
        const result = await db.get(
            'SELECT id FROM bookmarks WHERE user_id = ? AND lead_id = ?',
            [userId, leadId]
        );
        return !!result;
    }
    
    async getBookmarkByLeadId(userId, leadId) {
        return await db.get(
            'SELECT id, notes, folder FROM bookmarks WHERE user_id = ? AND lead_id = ?',
            [userId, leadId]
        );
    }
    
    async exportBookmarks(userId, format = 'json') {
        const bookmarks = await db.query(
            `SELECT b.notes, b.folder, b.created_at,
                    l.title, l.description, l.service_needed, l.location,
                    l.contact_phone, l.contact_email, l.contact_whatsapp, l.source_url,
                    l.score_category, l.discovered_at
             FROM bookmarks b
             JOIN leads l ON b.lead_id = l.id
             WHERE b.user_id = ?`,
            [userId]
        );
        
        return bookmarks;
    }
}

module.exports = new BookmarksService();