Mohan Ki
71 lines
2.9 KiB
JavaScript
71 lines
2.9 KiB
JavaScript
const express = require('express');
|
|
const router = express.Router();
|
|
const { Pool } = require('pg');
|
|
const { verifyToken } = require('../middleware/auth');
|
|
|
|
const pgPool = new Pool({
|
|
host: process.env.PGHOST || 'postgres',
|
|
port: process.env.PGPORT || 5432,
|
|
user: process.env.PGUSER || 'postgres',
|
|
password: process.env.PGPASSWORD || 'postgres',
|
|
database: process.env.PGDATABASE || 'ckqr',
|
|
});
|
|
|
|
router.get('/:qrCodeId', verifyToken, async (req, res) => {
|
|
const { qrCodeId } = req.params;
|
|
const userId = req.user.id;
|
|
|
|
try {
|
|
// Ensure this user owns the QR code
|
|
const checkOwner = await pgPool.query('SELECT id FROM qr_codes WHERE id = $1 AND user_id = $2', [qrCodeId, userId]);
|
|
if (checkOwner.rows.length === 0) {
|
|
return res.status(403).json({ error: 'Unauthorized or QR code not found' });
|
|
}
|
|
|
|
// Get total scans and unique scanners
|
|
const totalQuery = await pgPool.query('SELECT COUNT(*) as total, COUNT(DISTINCT ip_address) as unique_scanners FROM qr_scans WHERE qr_code_id = $1', [qrCodeId]);
|
|
|
|
// Get OS breakdown
|
|
const osQuery = await pgPool.query(
|
|
'SELECT os, COUNT(*) as count FROM qr_scans WHERE qr_code_id = $1 GROUP BY os',
|
|
[qrCodeId]
|
|
);
|
|
|
|
// Get Device Type breakdown
|
|
const deviceQuery = await pgPool.query(
|
|
'SELECT device_type, COUNT(*) as count FROM qr_scans WHERE qr_code_id = $1 GROUP BY device_type',
|
|
[qrCodeId]
|
|
);
|
|
|
|
// Get Country breakdown
|
|
const countryQuery = await pgPool.query(
|
|
'SELECT country, COUNT(*) as count FROM qr_scans WHERE qr_code_id = $1 GROUP BY country ORDER BY count DESC',
|
|
[qrCodeId]
|
|
);
|
|
|
|
// Get Scans over time (last 7 days grouped by date)
|
|
const timeQuery = await pgPool.query(
|
|
`SELECT DATE(scanned_at) as date, COUNT(*) as count
|
|
FROM qr_scans
|
|
WHERE qr_code_id = $1 AND scanned_at >= NOW() - INTERVAL '7 days'
|
|
GROUP BY DATE(scanned_at) ORDER BY date ASC`,
|
|
[qrCodeId]
|
|
);
|
|
|
|
// Parse string counts from PG back to integers for Recharts
|
|
res.json({
|
|
totalScans: parseInt(totalQuery.rows[0].total) || 0,
|
|
uniqueScanners: parseInt(totalQuery.rows[0].unique_scanners) || 0,
|
|
osBreakdown: osQuery.rows.map(r => ({ ...r, count: parseInt(r.count) })),
|
|
deviceBreakdown: deviceQuery.rows.map(r => ({ ...r, count: parseInt(r.count) })),
|
|
countryBreakdown: countryQuery.rows.map(r => ({ country: r.country || 'Unknown', count: parseInt(r.count) })),
|
|
timeline: timeQuery.rows.map(r => ({ ...r, count: parseInt(r.count) }))
|
|
});
|
|
} catch (err) {
|
|
console.error(err);
|
|
res.status(500).json({ error: 'Server Error fetching analytics' });
|
|
}
|
|
});
|
|
|
|
module.exports = router;
|