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

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

const db = new DatabaseClient();

exports.getBookmarks = async (req, res) => {
    try {
        const page = parseInt(req.query.page) || 1;
        const limit = parseInt(req.query.limit) || 20;
        const offset = (page - 1) * limit;
        const folder = req.query.folder;
        
        let whereClause = 'WHERE user_id = ?';
        const params = [req.user.id];
        
        if (folder && folder !== 'all') {
            whereClause += ' AND folder = ?';
            params.push(folder);
        }
        
        // Step 1: Get bookmarks (always works)
        const bookmarksRaw = await db.all(
            `SELECT id, lead_id, notes, folder, created_at, updated_at
             FROM bookmarks
             ${whereClause}
             ORDER BY created_at DESC
             LIMIT ? OFFSET ?`,
            [...params, limit, offset]
        );
        
        // Step 2: For each bookmark, try to get lead details
        const bookmarks = [];
        for (const b of bookmarksRaw) {
            const lead = await db.get(
                `SELECT id, title, description, service_needed, location,
                        contact_phone, contact_email, source_url, score_category, discovered_at
                 FROM leads WHERE id = ?`,
                [b.lead_id]
            );
            
            bookmarks.push({
                id: b.id,
                notes: b.notes,
                folder: b.folder,
                created_at: b.created_at,
                updated_at: b.updated_at,
                lead_id: b.lead_id,
                title: lead ? lead.title : 'Lead not available',
                description: lead ? lead.description : 'This lead may have been removed.',
                service_needed: lead ? lead.service_needed : null,
                location: lead ? lead.location : null,
                contact_phone: lead ? lead.contact_phone : null,
                contact_email: lead ? lead.contact_email : null,
                source_url: lead ? lead.source_url : null,
                score_category: lead ? lead.score_category : null,
                discovered_at: lead ? lead.discovered_at : null
            });
        }
        
        const total = await db.get(
            `SELECT COUNT(*) as total FROM bookmarks WHERE user_id = ?`,
            [req.user.id]
        );
        
        res.json({
            bookmarks: bookmarks,
            pagination: {
                page,
                limit,
                total: total.total,
                pages: Math.ceil(total.total / limit)
            }
        });
    } catch (error) {
        console.error('Get bookmarks error:', error);
        res.status(500).json({ error: 'Failed to get bookmarks: ' + error.message });
    }
};

exports.createBookmark = async (req, res) => {
    try {
        const { lead_id, notes, folder } = req.body;
        const leadIdNum = parseInt(lead_id);
        
        if (isNaN(leadIdNum)) {
            return res.status(400).json({ error: 'Invalid lead_id' });
        }
        
        const existing = await db.get(
            'SELECT id FROM bookmarks WHERE user_id = ? AND lead_id = ?',
            [req.user.id, leadIdNum]
        );
        
        if (existing) {
            return res.status(400).json({ error: 'Bookmark already exists' });
        }
        
        const result = await db.run(
            'INSERT INTO bookmarks (user_id, lead_id, notes, folder) VALUES (?, ?, ?, ?)',
            [req.user.id, leadIdNum, notes || null, folder || 'default']
        );
        
        await db.run('UPDATE users SET bookmarks_count = bookmarks_count + 1 WHERE id = ?', [req.user.id]);
        
        res.status(201).json({ 
            message: 'Bookmark created successfully',
            bookmarkId: result.insertId 
        });
    } catch (error) {
        console.error('Create bookmark error:', error);
        if (error.code === 'SQLITE_CONSTRAINT' || error.code === 'ER_DUP_ENTRY') {
            return res.status(400).json({ error: 'Bookmark already exists' });
        }
        res.status(500).json({ error: 'Failed to create bookmark' });
    }
};

exports.deleteBookmark = async (req, res) => {
    try {
        await db.run('DELETE FROM bookmarks WHERE id = ? AND user_id = ?', [req.params.id, req.user.id]);
        await db.run('UPDATE users SET bookmarks_count = bookmarks_count - 1 WHERE id = ?', [req.user.id]);
        res.json({ message: 'Bookmark deleted successfully' });
    } catch (error) {
        console.error('Delete bookmark error:', error);
        res.status(500).json({ error: 'Failed to delete bookmark' });
    }
};

exports.updateNotes = async (req, res) => {
    try {
        const { notes } = req.body;
        await db.run('UPDATE bookmarks SET notes = ? WHERE id = ? AND user_id = ?', [notes, req.params.id, req.user.id]);
        res.json({ message: 'Notes updated successfully' });
    } catch (error) {
        console.error('Update notes error:', error);
        res.status(500).json({ error: 'Failed to update notes' });
    }
};

exports.moveToFolder = async (req, res) => {
    try {
        const { folder } = req.body;
        await db.run('UPDATE bookmarks SET folder = ? WHERE id = ? AND user_id = ?', [folder, req.params.id, req.user.id]);
        res.json({ message: 'Moved to folder successfully' });
    } catch (error) {
        console.error('Move to folder error:', error);
        res.status(500).json({ error: 'Failed to move bookmark' });
    }
};

exports.getFolders = async (req, res) => {
    try {
        const folders = await db.query(
            `SELECT name, color, 
                    (SELECT COUNT(*) FROM bookmarks WHERE user_id = ? AND folder = name) as count
             FROM bookmark_folders
             WHERE user_id = ?
             UNION
             SELECT 'default' as name, '#6B7280' as color, 
                    (SELECT COUNT(*) FROM bookmarks WHERE user_id = ? AND folder = 'default') as count`,
            [req.user.id, req.user.id, req.user.id]
        );
        res.json(folders);
    } catch (error) {
        console.error('Get folders error:', error);
        res.status(500).json({ error: 'Failed to get folders' });
    }
};

exports.createFolder = async (req, res) => {
    try {
        const { name, color } = req.body;
        await db.run(
            'INSERT INTO bookmark_folders (user_id, name, color) VALUES (?, ?, ?)',
            [req.user.id, name, color || '#6B7280']
        );
        res.status(201).json({ message: 'Folder created successfully' });
    } catch (error) {
        console.error('Create folder error:', error);
        res.status(500).json({ error: 'Failed to create folder' });
    }
};

exports.deleteFolder = async (req, res) => {
    try {
        const folder = await db.get('SELECT name FROM bookmark_folders WHERE id = ? AND user_id = ?', [req.params.id, req.user.id]);
        if (folder) {
            await db.run('UPDATE bookmarks SET folder = "default" WHERE user_id = ? AND folder = ?', [req.user.id, folder.name]);
            await db.run('DELETE FROM bookmark_folders WHERE id = ? AND user_id = ?', [req.params.id, req.user.id]);
        }
        res.json({ message: 'Folder deleted successfully' });
    } catch (error) {
        console.error('Delete folder error:', error);
        res.status(500).json({ error: 'Failed to delete folder' });
    }
};

exports.exportBookmarks = async (req, res) => {
    try {
        const { format = 'json' } = req.body;
        
        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.source_url, l.score_category
             FROM bookmarks b
             LEFT JOIN leads l ON b.lead_id = l.id
             WHERE b.user_id = ?`,
            [req.user.id]
        );
        
        if (format === 'csv') {
            let csv = 'Title,Description,Service Needed,Location,Phone,Email,Source URL,Score,Folder,Notes,Bookmarked At\n';
            for (const b of bookmarks) {
                csv += `"${(b.title || '').replace(/"/g, '""')}",`;
                csv += `"${(b.description || '').replace(/"/g, '""')}",`;
                csv += `"${b.service_needed || ''}",`;
                csv += `"${b.location || ''}",`;
                csv += `"${b.contact_phone || ''}",`;
                csv += `"${b.contact_email || ''}",`;
                csv += `"${b.source_url || ''}",`;
                csv += `${b.score_category || ''},`;
                csv += `${b.folder || 'default'},`;
                csv += `"${(b.notes || '').replace(/"/g, '""')}",`;
                csv += `${b.created_at}\n`;
            }
            res.setHeader('Content-Type', 'text/csv');
            res.setHeader('Content-Disposition', `attachment; filename=bookmarks_${Date.now()}.csv`);
            res.send(csv);
        } else {
            res.setHeader('Content-Type', 'application/json');
            res.setHeader('Content-Disposition', `attachment; filename=bookmarks_${Date.now()}.json`);
            res.json(bookmarks);
        }
    } catch (error) {
        console.error('Export bookmarks error:', error);
        res.status(500).json({ error: 'Failed to export bookmarks' });
    }
};