Spaces:
Running
Running
File size: 4,496 Bytes
0dd2082 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | const BaseProvider = require('./BaseProvider');
const { getDb } = require('../db/database');
const logger = require('../utils/logger');
class DatabaseProvider extends BaseProvider {
constructor() {
super('custom_db', { priority: 1, timeout: 60000 });
}
async search(intent) {
if (!intent.category || !intent.location) {
throw new Error('DatabaseProvider requires both category and location');
}
const db = await getDb();
const city = intent.location.toLowerCase();
let query, params;
// If GPS is provided, we prioritize proximity over city-string matching
if (intent.userLocation && intent.userLocation.lat && intent.userLocation.lng) {
const { lat, lng } = intent.userLocation;
const radius = 0.1; // roughly 10km in coordinate degrees for SQLite simplicity
if (intent.isSpecific && intent.specificItem) {
const item = `%${intent.specificItem.toLowerCase()}%`;
query = `
SELECT *,
((lat - ?) * (lat - ?) + (lon - ?) * (lon - ?)) AS distance_sq
FROM places
WHERE (category = ? OR name LIKE ? OR features LIKE ?)
AND (city LIKE ? OR (lat BETWEEN ? AND ? AND lon BETWEEN ? AND ?))
ORDER BY distance_sq ASC
`;
params = [lat, lat, lng, lng, intent.category.toLowerCase(), item, item, `%${city}%`, lat - radius, lat + radius, lng - radius, lng + radius];
} else {
query = `
SELECT *,
((lat - ?) * (lat - ?) + (lon - ?) * (lon - ?)) AS distance_sq
FROM places
WHERE category = ?
AND (city LIKE ? OR (lat BETWEEN ? AND ? AND lon BETWEEN ? AND ?))
ORDER BY distance_sq ASC
`;
params = [lat, lat, lng, lng, intent.category.toLowerCase(), `%${city}%`, lat - radius, lat + radius, lng - radius, lng + radius];
}
logger.info(`Performing spatial proximity search near ${lat}, ${lng} (extracted location: ${city})...`);
} else {
// Standard city-string matching
if (intent.isSpecific && intent.specificItem) {
const item = `%${intent.specificItem.toLowerCase()}%`;
query = `
SELECT * FROM places
WHERE (category = ? OR name LIKE ? OR features LIKE ?)
AND LOWER(city) LIKE ?
`;
params = [intent.category.toLowerCase(), item, item, `%${city}%`];
} else {
query = `
SELECT * FROM places
WHERE category = ? AND LOWER(city) LIKE ?
`;
params = [intent.category.toLowerCase(), `%${city}%`];
}
logger.info(`Querying custom DB for ${intent.category} in ${city}...`);
}
const rows = await db.all(query, params);
if (!rows || rows.length === 0) {
logger.warn(`No entries found in DB for ${intent.category} in ${city}. Run the scraper script first!`);
return {
results: [],
meta: {
source: this.name,
total: 0
}
};
}
const safeParse = (str, fallback = []) => {
try {
return str ? JSON.parse(str) : fallback;
} catch (e) {
logger.warn('Failed to parse JSON column in database', { error: e.message, content: str });
return fallback;
}
};
const results = rows.map(row => ({
id: row.id,
name: row.name,
category: row.category,
address: row.address,
rating: row.rating || 'N/A',
priceRange: row.priceRange || 'N/A',
features: safeParse(row.features),
reviews: safeParse(row.reviews),
coordinates: { lat: row.lat, lon: row.lon },
source: this.name
}));
logger.info(`Custom DB returned ${results.length} results.`);
return {
results,
meta: {
source: this.name,
total: results.length
}
};
}
}
module.exports = DatabaseProvider;
|