581 lines
22 KiB
JavaScript
581 lines
22 KiB
JavaScript
/**
|
|
* CaStAD Statistics Service v3.0.0
|
|
* 고급 통계 및 분석 서비스
|
|
*/
|
|
|
|
const db = require('./db');
|
|
|
|
/**
|
|
* 일별 시스템 통계 스냅샷 생성/업데이트
|
|
*/
|
|
async function updateDailyStats() {
|
|
const today = new Date().toISOString().split('T')[0];
|
|
|
|
return new Promise((resolve, reject) => {
|
|
// 오늘의 통계 계산
|
|
db.serialize(() => {
|
|
// 전체 사용자 수
|
|
db.get('SELECT COUNT(*) as total FROM users', [], (err, totalUsers) => {
|
|
if (err) return reject(err);
|
|
|
|
// 오늘 신규 가입자
|
|
db.get(`
|
|
SELECT COUNT(*) as count FROM users
|
|
WHERE date(createdAt) = date('now')
|
|
`, [], (err, newUsers) => {
|
|
if (err) return reject(err);
|
|
|
|
// 오늘 활성 사용자 (영상 생성)
|
|
db.get(`
|
|
SELECT COUNT(DISTINCT user_id) as count FROM history
|
|
WHERE date(createdAt) = date('now')
|
|
`, [], (err, activeUsers) => {
|
|
if (err) return reject(err);
|
|
|
|
// 오늘 생성된 영상 수
|
|
db.get(`
|
|
SELECT COUNT(*) as count FROM history
|
|
WHERE date(createdAt) = date('now')
|
|
`, [], (err, videos) => {
|
|
if (err) return reject(err);
|
|
|
|
// 오늘 전체 업로드 수 (YouTube + Instagram + TikTok)
|
|
db.get(`
|
|
SELECT
|
|
(SELECT COUNT(*) FROM upload_history WHERE date(uploaded_at) = date('now')) +
|
|
(SELECT COUNT(*) FROM instagram_upload_history WHERE date(createdAt) = date('now')) +
|
|
(SELECT COUNT(*) FROM tiktok_upload_history WHERE date(createdAt) = date('now'))
|
|
as total_uploads,
|
|
(SELECT COUNT(*) FROM upload_history WHERE date(uploaded_at) = date('now')) as youtube,
|
|
(SELECT COUNT(*) FROM instagram_upload_history WHERE date(createdAt) = date('now')) as instagram,
|
|
(SELECT COUNT(*) FROM tiktok_upload_history WHERE date(createdAt) = date('now')) as tiktok
|
|
`, [], (err, uploads) => {
|
|
if (err) return reject(err);
|
|
|
|
// 오늘 사용된 크레딧
|
|
db.get(`
|
|
SELECT COALESCE(SUM(ABS(amount)), 0) as total FROM credit_history
|
|
WHERE amount < 0 AND date(createdAt) = date('now')
|
|
`, [], (err, credits) => {
|
|
if (err) return reject(err);
|
|
|
|
// 저장 또는 업데이트
|
|
db.run(`
|
|
INSERT OR REPLACE INTO system_stats_daily
|
|
(date, total_users, new_users, active_users, total_videos_generated,
|
|
total_uploads, youtube_uploads, instagram_uploads, tiktok_uploads,
|
|
total_credits_used, createdAt)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
|
|
`, [
|
|
today,
|
|
totalUsers.total,
|
|
newUsers.count,
|
|
activeUsers.count,
|
|
videos.count,
|
|
uploads?.total_uploads || 0,
|
|
uploads?.youtube || 0,
|
|
uploads?.instagram || 0,
|
|
uploads?.tiktok || 0,
|
|
credits.total
|
|
], function (err) {
|
|
if (err) reject(err);
|
|
else resolve({ success: true });
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 사용자 성장 트렌드 조회
|
|
* @param {number} days - 조회 기간 (일)
|
|
*/
|
|
function getUserGrowthTrend(days = 30) {
|
|
return new Promise((resolve, reject) => {
|
|
db.all(`
|
|
SELECT
|
|
date(createdAt) as date,
|
|
COUNT(*) as new_users,
|
|
SUM(COUNT(*)) OVER (ORDER BY date(createdAt)) as cumulative_users
|
|
FROM users
|
|
WHERE createdAt >= date('now', '-' || ? || ' days')
|
|
GROUP BY date(createdAt)
|
|
ORDER BY date ASC
|
|
`, [days], (err, rows) => {
|
|
if (err) reject(err);
|
|
else resolve(rows || []);
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 영상 생성 트렌드 조회
|
|
* @param {number} days - 조회 기간 (일)
|
|
*/
|
|
function getVideoGenerationTrend(days = 30) {
|
|
return new Promise((resolve, reject) => {
|
|
db.all(`
|
|
SELECT
|
|
date(createdAt) as date,
|
|
COUNT(*) as videos_generated,
|
|
COUNT(DISTINCT user_id) as unique_users
|
|
FROM history
|
|
WHERE createdAt >= date('now', '-' || ? || ' days')
|
|
GROUP BY date(createdAt)
|
|
ORDER BY date ASC
|
|
`, [days], (err, rows) => {
|
|
if (err) reject(err);
|
|
else resolve(rows || []);
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 플랫폼별 업로드 통계
|
|
* @param {number} days - 조회 기간 (일)
|
|
*/
|
|
function getPlatformUploadStats(days = 30) {
|
|
return new Promise((resolve, reject) => {
|
|
const result = {
|
|
youtube: [],
|
|
instagram: [],
|
|
tiktok: [],
|
|
summary: {}
|
|
};
|
|
|
|
db.all(`
|
|
SELECT date(uploaded_at) as date, COUNT(*) as count, status
|
|
FROM upload_history
|
|
WHERE uploaded_at >= date('now', '-' || ? || ' days')
|
|
GROUP BY date(uploaded_at), status
|
|
ORDER BY date ASC
|
|
`, [days], (err, youtubeRows) => {
|
|
if (err) return reject(err);
|
|
result.youtube = youtubeRows || [];
|
|
|
|
db.all(`
|
|
SELECT date(createdAt) as date, COUNT(*) as count, status
|
|
FROM instagram_upload_history
|
|
WHERE createdAt >= date('now', '-' || ? || ' days')
|
|
GROUP BY date(createdAt), status
|
|
ORDER BY date ASC
|
|
`, [days], (err, instaRows) => {
|
|
if (err) return reject(err);
|
|
result.instagram = instaRows || [];
|
|
|
|
db.all(`
|
|
SELECT date(createdAt) as date, COUNT(*) as count, status
|
|
FROM tiktok_upload_history
|
|
WHERE createdAt >= date('now', '-' || ? || ' days')
|
|
GROUP BY date(createdAt), status
|
|
ORDER BY date ASC
|
|
`, [days], (err, tiktokRows) => {
|
|
if (err) return reject(err);
|
|
result.tiktok = tiktokRows || [];
|
|
|
|
// 요약 통계 계산
|
|
db.get(`
|
|
SELECT
|
|
(SELECT COUNT(*) FROM upload_history WHERE uploaded_at >= date('now', '-' || ? || ' days')) as youtube_total,
|
|
(SELECT COUNT(*) FROM upload_history WHERE uploaded_at >= date('now', '-' || ? || ' days') AND status = 'completed') as youtube_success,
|
|
(SELECT COUNT(*) FROM instagram_upload_history WHERE createdAt >= date('now', '-' || ? || ' days')) as instagram_total,
|
|
(SELECT COUNT(*) FROM instagram_upload_history WHERE createdAt >= date('now', '-' || ? || ' days') AND status = 'completed') as instagram_success,
|
|
(SELECT COUNT(*) FROM tiktok_upload_history WHERE createdAt >= date('now', '-' || ? || ' days')) as tiktok_total,
|
|
(SELECT COUNT(*) FROM tiktok_upload_history WHERE createdAt >= date('now', '-' || ? || ' days') AND status = 'completed') as tiktok_success
|
|
`, [days, days, days, days, days, days], (err, summary) => {
|
|
if (err) return reject(err);
|
|
result.summary = summary || {};
|
|
resolve(result);
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 크레딧 사용 통계
|
|
* @param {number} days - 조회 기간 (일)
|
|
*/
|
|
function getCreditUsageStats(days = 30) {
|
|
return new Promise((resolve, reject) => {
|
|
db.all(`
|
|
SELECT
|
|
date(createdAt) as date,
|
|
type,
|
|
SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as credits_added,
|
|
SUM(CASE WHEN amount < 0 THEN ABS(amount) ELSE 0 END) as credits_used
|
|
FROM credit_history
|
|
WHERE createdAt >= date('now', '-' || ? || ' days')
|
|
GROUP BY date(createdAt), type
|
|
ORDER BY date ASC
|
|
`, [days], (err, rows) => {
|
|
if (err) reject(err);
|
|
else resolve(rows || []);
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 플랜별 사용자 분포
|
|
*/
|
|
function getPlanDistribution() {
|
|
return new Promise((resolve, reject) => {
|
|
db.all(`
|
|
SELECT
|
|
COALESCE(plan_type, 'free') as plan,
|
|
COUNT(*) as count,
|
|
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM users), 2) as percentage
|
|
FROM users
|
|
GROUP BY plan_type
|
|
ORDER BY count DESC
|
|
`, [], (err, rows) => {
|
|
if (err) reject(err);
|
|
else resolve(rows || []);
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 톱 사용자 (가장 많은 영상 생성)
|
|
* @param {number} limit - 조회 개수
|
|
*/
|
|
function getTopUsers(limit = 10) {
|
|
return new Promise((resolve, reject) => {
|
|
db.all(`
|
|
SELECT
|
|
u.id,
|
|
u.username,
|
|
u.name,
|
|
u.plan_type,
|
|
u.credits,
|
|
COUNT(h.id) as total_videos,
|
|
(SELECT COUNT(*) FROM upload_history WHERE user_id = u.id) as youtube_uploads,
|
|
(SELECT COUNT(*) FROM instagram_upload_history WHERE user_id = u.id) as instagram_uploads,
|
|
(SELECT COUNT(*) FROM tiktok_upload_history WHERE user_id = u.id) as tiktok_uploads,
|
|
(SELECT COUNT(*) FROM pension_profiles WHERE user_id = u.id) as pension_count
|
|
FROM users u
|
|
LEFT JOIN history h ON u.id = h.user_id
|
|
WHERE u.role != 'admin'
|
|
GROUP BY u.id
|
|
ORDER BY total_videos DESC
|
|
LIMIT ?
|
|
`, [limit], (err, rows) => {
|
|
if (err) reject(err);
|
|
else resolve(rows || []);
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 최근 활동 로그
|
|
* @param {number} limit - 조회 개수
|
|
*/
|
|
function getRecentActivityLogs(limit = 50) {
|
|
return new Promise((resolve, reject) => {
|
|
db.all(`
|
|
SELECT
|
|
al.*,
|
|
u.username,
|
|
u.name
|
|
FROM activity_logs al
|
|
LEFT JOIN users u ON al.user_id = u.id
|
|
ORDER BY al.createdAt DESC
|
|
LIMIT ?
|
|
`, [limit], (err, rows) => {
|
|
if (err) reject(err);
|
|
else resolve(rows || []);
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 활동 로그 기록
|
|
* @param {number|null} userId - 사용자 ID
|
|
* @param {string} actionType - 액션 타입
|
|
* @param {string} actionDetail - 액션 상세
|
|
* @param {object} metadata - 추가 메타데이터
|
|
*/
|
|
function logActivity(userId, actionType, actionDetail, metadata = {}) {
|
|
return new Promise((resolve, reject) => {
|
|
db.run(`
|
|
INSERT INTO activity_logs (user_id, action_type, action_detail, metadata, createdAt)
|
|
VALUES (?, ?, ?, ?, datetime('now'))
|
|
`, [userId, actionType, actionDetail, JSON.stringify(metadata)], function (err) {
|
|
if (err) reject(err);
|
|
else resolve({ id: this.lastID });
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 대시보드 요약 통계
|
|
*/
|
|
function getDashboardSummary() {
|
|
return new Promise((resolve, reject) => {
|
|
const summary = {};
|
|
|
|
db.serialize(() => {
|
|
// 전체 사용자
|
|
db.get('SELECT COUNT(*) as total FROM users WHERE role != ?', ['admin'], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.totalUsers = row.total;
|
|
|
|
// 오늘 신규 가입
|
|
db.get(`
|
|
SELECT COUNT(*) as count FROM users
|
|
WHERE date(createdAt) = date('now') AND role != 'admin'
|
|
`, [], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.newUsersToday = row.count;
|
|
|
|
// 이번 주 활성 사용자
|
|
db.get(`
|
|
SELECT COUNT(DISTINCT user_id) as count FROM history
|
|
WHERE createdAt >= date('now', '-7 days')
|
|
`, [], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.activeUsersWeek = row.count;
|
|
|
|
// 전체 생성 영상 수
|
|
db.get('SELECT COUNT(*) as total FROM history', [], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.totalVideos = row.total;
|
|
|
|
// 오늘 생성 영상
|
|
db.get(`
|
|
SELECT COUNT(*) as count FROM history
|
|
WHERE date(createdAt) = date('now')
|
|
`, [], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.videosToday = row.count;
|
|
|
|
// 전체 업로드 수
|
|
db.get(`
|
|
SELECT
|
|
(SELECT COUNT(*) FROM upload_history) +
|
|
(SELECT COUNT(*) FROM instagram_upload_history) +
|
|
(SELECT COUNT(*) FROM tiktok_upload_history) as total
|
|
`, [], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.totalUploads = row.total;
|
|
|
|
// 플랫폼별 업로드
|
|
db.get(`
|
|
SELECT
|
|
(SELECT COUNT(*) FROM upload_history) as youtube,
|
|
(SELECT COUNT(*) FROM instagram_upload_history) as instagram,
|
|
(SELECT COUNT(*) FROM tiktok_upload_history) as tiktok
|
|
`, [], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.platformUploads = row;
|
|
|
|
// 전체 펜션 수
|
|
db.get('SELECT COUNT(*) as total FROM pension_profiles', [], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.totalPensions = row.total;
|
|
|
|
// 대기중인 승인 요청
|
|
db.get(`
|
|
SELECT COUNT(*) as count FROM users
|
|
WHERE approved = 0 AND role != 'admin'
|
|
`, [], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.pendingApprovals = row.count;
|
|
|
|
// 대기중인 크레딧 요청
|
|
db.get(`
|
|
SELECT COUNT(*) as count FROM credit_requests
|
|
WHERE status = 'pending'
|
|
`, [], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.pendingCreditRequests = row.count;
|
|
|
|
// 전체 크레딧 발행량
|
|
db.get(`
|
|
SELECT
|
|
COALESCE(SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END), 0) as issued,
|
|
COALESCE(SUM(CASE WHEN amount < 0 THEN ABS(amount) ELSE 0 END), 0) as used
|
|
FROM credit_history
|
|
`, [], (err, row) => {
|
|
if (err) return reject(err);
|
|
summary.credits = row;
|
|
|
|
resolve(summary);
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 시간대별 사용 패턴 분석
|
|
*/
|
|
function getUsagePattern() {
|
|
return new Promise((resolve, reject) => {
|
|
db.all(`
|
|
SELECT
|
|
strftime('%H', createdAt) as hour,
|
|
COUNT(*) as count
|
|
FROM history
|
|
WHERE createdAt >= date('now', '-30 days')
|
|
GROUP BY strftime('%H', createdAt)
|
|
ORDER BY hour ASC
|
|
`, [], (err, rows) => {
|
|
if (err) reject(err);
|
|
else resolve(rows || []);
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 지역별 사용자 분포 (펜션 주소 기반)
|
|
*/
|
|
function getRegionalDistribution() {
|
|
return new Promise((resolve, reject) => {
|
|
db.all(`
|
|
SELECT
|
|
COALESCE(region, '미설정') as region,
|
|
COUNT(*) as count
|
|
FROM pension_profiles
|
|
GROUP BY region
|
|
ORDER BY count DESC
|
|
LIMIT 20
|
|
`, [], (err, rows) => {
|
|
if (err) reject(err);
|
|
else resolve(rows || []);
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 월별 수익 예측 (플랜 기반)
|
|
*/
|
|
function getRevenueProjection() {
|
|
return new Promise((resolve, reject) => {
|
|
const PLAN_PRICES = {
|
|
free: 0,
|
|
basic: 29000,
|
|
pro: 99000,
|
|
business: 299000
|
|
};
|
|
|
|
db.all(`
|
|
SELECT
|
|
COALESCE(plan_type, 'free') as plan,
|
|
COUNT(*) as count
|
|
FROM users
|
|
WHERE role != 'admin'
|
|
GROUP BY plan_type
|
|
`, [], (err, rows) => {
|
|
if (err) return reject(err);
|
|
|
|
let monthlyRevenue = 0;
|
|
const breakdown = {};
|
|
|
|
(rows || []).forEach(row => {
|
|
const price = PLAN_PRICES[row.plan] || 0;
|
|
const revenue = price * row.count;
|
|
monthlyRevenue += revenue;
|
|
breakdown[row.plan] = {
|
|
users: row.count,
|
|
price,
|
|
revenue
|
|
};
|
|
});
|
|
|
|
resolve({
|
|
monthlyRevenue,
|
|
annualProjection: monthlyRevenue * 12,
|
|
breakdown
|
|
});
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* 시스템 헬스 체크
|
|
*/
|
|
function getSystemHealth() {
|
|
return new Promise((resolve, reject) => {
|
|
const health = {
|
|
database: 'ok',
|
|
diskUsage: null,
|
|
lastVideoGenerated: null,
|
|
uptime: process.uptime()
|
|
};
|
|
|
|
// DB 연결 확인
|
|
db.get('SELECT 1', [], (err) => {
|
|
if (err) {
|
|
health.database = 'error';
|
|
}
|
|
|
|
// 마지막 영상 생성 시간
|
|
db.get(`
|
|
SELECT createdAt FROM history
|
|
ORDER BY createdAt DESC LIMIT 1
|
|
`, [], (err, row) => {
|
|
if (!err && row) {
|
|
health.lastVideoGenerated = row.createdAt;
|
|
}
|
|
|
|
// 디스크 사용량 (downloads 폴더)
|
|
const fs = require('fs');
|
|
const path = require('path');
|
|
const downloadsDir = path.join(__dirname, 'downloads');
|
|
|
|
try {
|
|
let totalSize = 0;
|
|
const files = fs.readdirSync(downloadsDir);
|
|
files.forEach(file => {
|
|
const filePath = path.join(downloadsDir, file);
|
|
const stats = fs.statSync(filePath);
|
|
totalSize += stats.size;
|
|
});
|
|
health.diskUsage = {
|
|
bytes: totalSize,
|
|
mb: Math.round(totalSize / 1024 / 1024),
|
|
fileCount: files.length
|
|
};
|
|
} catch (e) {
|
|
health.diskUsage = { error: e.message };
|
|
}
|
|
|
|
resolve(health);
|
|
});
|
|
});
|
|
});
|
|
}
|
|
|
|
module.exports = {
|
|
updateDailyStats,
|
|
getUserGrowthTrend,
|
|
getVideoGenerationTrend,
|
|
getPlatformUploadStats,
|
|
getCreditUsageStats,
|
|
getPlanDistribution,
|
|
getTopUsers,
|
|
getRecentActivityLogs,
|
|
logActivity,
|
|
getDashboardSummary,
|
|
getUsagePattern,
|
|
getRegionalDistribution,
|
|
getRevenueProjection,
|
|
getSystemHealth
|
|
};
|