Repository navigation
Expand file tree
/
Copy pathdb-codes.ts
More file actions
877 lines (753 loc) · 27.1 KB
/
Copy pathdb-codes.ts
File metadata and controls
877 lines (753 loc) · 27.1 KB
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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
8000
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
import type { InviteBatchResult, InviteCode, RedemptionBatchSummary, RedemptionCode, StatsOverview, DailyStats } from '@/types';
import { generateId } from './utils';
import { createDatabaseAdapter, type DatabaseAdapter } from './db-adapter';
import { getSystemConfig } from './db';
// ========================================
// Database adapter
// ========================================
let adapter: DatabaseAdapter | null = null;
function getAdapter(): DatabaseAdapter {
if (!adapter) {
adapter = createDatabaseAdapter();
}
return adapter;
}
// ========================================
// Table initialization
// ========================================
let tablesInitialized = false;
export async function initializeCodesTables(): Promise<void> {
if (tablesInitialized) return;
const db = getAdapter();
const dbType = process.env.DB_TYPE || 'sqlite';
// Invite codes table
if (dbType === 'mysql') {
await db.execute(`
CREATE TABLE IF NOT EXISTS invite_codes (
id VARCHAR(36) PRIMARY KEY,
code VARCHAR(20) UNIQUE NOT NULL,
creator_id VARCHAR(36) NOT NULL,
used_by VARCHAR(36),
used_at BIGINT,
bonus_points INT DEFAULT 0,
creator_bonus INT DEFAULT 0,
expires_at BIGINT,
created_at BIGINT NOT NULL,
INDEX idx_code (code),
INDEX idx_creator (creator_id)
)
`);
} else {
await db.execute(`
CREATE TABLE IF NOT EXISTS invite_codes (
id TEXT PRIMARY KEY,
code TEXT UNIQUE NOT NULL,
creator_id TEXT NOT NULL,
used_by TEXT,
used_at INTEGER,
bonus_points INTEGER DEFAULT 0,
creator_bonus INTEGER DEFAULT 0,
expires_at INTEGER,
created_at INTEGER NOT NULL
)
`);
try { await db.execute('CREATE INDEX IF NOT EXISTS idx_invite_code ON invite_codes(code)'); } catch {}
try { await db.execute('CREATE INDEX IF NOT EXISTS idx_invite_creator ON invite_codes(creator_id)'); } catch {}
}
// Redemption codes table
if (dbType === 'mysql') {
await db.execute(`
CREATE TABLE IF NOT EXISTS redemption_codes (
id VARCHAR(36) PRIMARY KEY,
code VARCHAR(32) UNIQUE NOT NULL,
points INT NOT NULL,
used_by VARCHAR(36),
used_at BIGINT,
expires_at BIGINT,
batch_id VARCHAR(36),
note VARCHAR(200),
created_at BIGINT NOT NULL,
INDEX idx_redeem_code (code),
INDEX idx_batch (batch_id)
)
`);
} else {
await db.execute(`
CREATE TABLE IF NOT EXISTS redemption_codes (
id TEXT PRIMARY KEY,
code TEXT UNIQUE NOT NULL,
points INTEGER NOT NULL,
used_by TEXT,
used_at INTEGER,
expires_at INTEGER,
batch_id TEXT,
note TEXT,
created_at INTEGER NOT NULL
)
`);
try { await db.execute('CREATE INDEX IF NOT EXISTS idx_redeem_code ON redemption_codes(code)'); } catch {}
try { await db.execute('CREATE INDEX IF NOT EXISTS idx_redeem_batch ON redemption_codes(batch_id)'); } catch {}
}
tablesInitialized = true;
}
// ========================================
// Invite code functions
// ========================================
function generateInviteCode(): string {
const chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
let code = '';
for (let i = 0; i < 8; i++) {
code += chars.charAt(Math.floor(Math.random() * chars.length));
}
return code;
}
// Get user's own invite code (for sharing)
export async function getUserInviteCode(userId: string): Promise<string | null> {
await initializeCodesTables();
const db = getAdapter();
// Find an unused invite code created by this user
const [rows] = await db.execute(
'SELECT code FROM invite_codes WHERE creator_id = ? AND used_by IS NULL ORDER BY created_at DESC LIMIT 1',
[userId]
);
const arr = rows as any[];
return arr.length > 0 ? arr[0].code : null;
}
// Create a personal invite code for user (with default bonuses from site settings)
export async function createUserInviteCode(userId: string): Promise<string> {
await initializeCodesTables();
const db = getAdapter();
const config = await getSystemConfig();
if (!config.inviteSettings.enabled) {
throw new Error('邀请码功能未启用');
}
const bonusPoints = config.inviteSettings.rewardEnabled
? config.inviteSettings.inviteeBonusPoints
: 0;
const creatorBonus = config.inviteSettings.rewardEnabled
? config.inviteSettings.inviterBonusPoints
: 0;
// Retry up to 5 times in case of code collision
for (let attempt = 0; attempt < 5; attempt++) {
const code = generateInviteCode();
const id = generateId();
const now = Date.now();
try {
await db.execute(
`INSERT INTO invite_codes (id, code, creator_id, bonus_points, creator_bonus, created_at)
VALUES (?, ?, ?, ?, ?, ?)`,
[id, code, userId, bonusPoints, creatorBonus, now]
);
return code;
} catch (err: any) {
if (err?.code === 'ER_DUP_ENTRY' || err?.code === 'SQLITE_CONSTRAINT') {
continue;
}
throw err;
}
}
throw new Error('Failed to generate unique invite code');
}
export async function createInviteCode(
creatorId: string,
bonusPoints?: number,
creatorBonus?: number,
expiresAt?: number
): Promise<InviteCode> {
await initializeCodesTables();
const db = getAdapter();
const config = await getSystemConfig();
const resolvedBonusPoints = typeof bonusPoints === 'number'
? bonusPoints
: config.inviteSettings.rewardEnabled
? config.inviteSettings.inviteeBonusPoints
: 0;
const resolvedCreatorBonus = typeof creatorBonus === 'number'
? creatorBonus
: config.inviteSettings.rewardEnabled
? config.inviteSettings.inviterBonusPoints
: 0;
// Retry up to 5 times in case of code collision
for (let attempt = 0; attempt < 5; attempt++) {
const invite: InviteCode = {
id: generateId(),
code: generateInviteCode(),
creatorId,
bonusPoints: resolvedBonusPoints,
creatorBonus: resolvedCreatorBonus,
expiresAt,
createdAt: Date.now(),
};
try {
await db.execute(
`INSERT INTO invite_codes (id, code, creator_id, bonus_points, creator_bonus, expires_at, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
[invite.id, invite.code, invite.creatorId, invite.bonusPoints, invite.creatorBonus, invite.expiresAt || null, invite.createdAt]
);
return invite;
} catch (err: any) {
// If duplicate key error, retry with new code
if (err?.code === 'ER_DUP_ENTRY' || err?.code === 'SQLITE_CONSTRAINT') {
continue;
}
throw err;
}
}
throw new Error('Failed to generate unique invite code');
}
export async function createInviteBatch(
creatorId: string,
count: number,
bonusPoints?: number,
creatorBonus?: number,
expiresAt?: number
): Promise<InviteBatchResult> {
const safeCount = Math.max(1, Math.min(100, Math.floor(count || 1)));
const codes: InviteCode[] = [];
let resolvedInviteeBonus = bonusPoints;
let resolvedInviterBonus = creatorBonus;
for (let index = 0; index < safeCount; index += 1) {
const invite = await createInviteCode(creatorId, bonusPoints, creatorBonus, expiresAt);
if (resolvedInviteeBonus === undefined) {
resolvedInviteeBonus = invite.bonusPoints;
}
if (resolvedInviterBonus === undefined) {
resolvedInviterBonus = invite.creatorBonus;
}
codes.push(invite);
}
return {
createdAt: Date.now(),
count: codes.length,
bonusPoints: resolvedInviteeBonus ?? 0,
creatorBonus: resolvedInviterBonus ?? 0,
expiresAt,
codes,
};
}
export async function getInviteCodeByCode(code: string): Promise<InviteCode | null> {
await initializeCodesTables();
const db = getAdapter();
const [rows] = await db.execute('SELECT * FROM invite_codes WHERE code = ?', [code.toUpperCase()]);
const codes = rows as any[];
if (codes.length === 0) return null;
const row = codes[0];
return {
id: row.id,
code: row.code,
creatorId: row.creator_id,
usedBy: row.used_by || undefined,
usedAt: row.used_at ? Number(row.used_at) : undefined,
bonusPoints: row.bonus_points || 0,
creatorBonus: row.creator_bonus || 0,
expiresAt: row.expires_at ? Number(row.expires_at) : undefined,
createdAt: Number(row.created_at),
};
}
export async function applyInviteCode(code: string, userId: string): Promise<{ success: boolean; error?: string; bonusPoints?: number }> {
await initializeCodesTables();
const db = getAdapter();
const config = await getSystemConfig();
const inviteSettings = config.inviteSettings;
if (!inviteSettings.enabled) {
return { success: false, error: '邀请码功能已关闭' };
}
const invite = await getInviteCodeByCode(code);
if (!invite) return { success: false, error: '邀请码不存在' };
if (invite.usedBy) return { success: false, error: '邀请码已被使用' };
if (invite.expiresAt && invite.expiresAt < Date.now()) return { success: false, error: '邀请码已过期' };
if (invite.creatorId === userId) return { success: false, error: '不能使用自己的邀请码' };
const now = Date.now();
// Use atomic update with WHERE condition to prevent race condition
const [result] = await db.execute(
'UPDATE invite_codes SET used_by = ?, used_at = ? WHERE id = ? AND used_by IS NULL',
[userId, now, invite.id]
);
const affected = (result as any).affectedRows ?? (result as any).changes ?? 0;
if (affected === 0) {
return { success: false, error: '邀请码已被使用' };
}
const inviteeBonus = inviteSettings.rewardEnabled
? Math.max(0, inviteSettings.inviteeBonusPoints)
: 0;
const inviterBonus = inviteSettings.rewardEnabled
? Math.max(0, inviteSettings.inviterBonusPoints)
: 0;
// Update user balance (bonus points)
if (inviteeBonus > 0) {
await db.execute(
'UPDATE users SET balance = balance + ?, updated_at = ? WHERE id = ?',
[inviteeBonus, now, userId]
);
}
// Update creator balance (creator bonus)
if (inviterBonus > 0) {
await db.execute(
'UPDATE users SET balance = balance + ?, updated_at = ? WHERE id = ?',
[inviterBonus, now, invite.creatorId]
);
}
return { success: true, bonusPoints: inviteeBonus };
}
export async function syncUnusedInviteCodeBonuses(
bonusPoints: number,
creatorBonus: number
): Promise<number> {
await initializeCodesTables();
const db = getAdapter();
const [result] = await db.execute(
'UPDATE invite_codes SET bonus_points = ?, creator_bonus = ? WHERE used_by IS NULL',
[Math.max(0, bonusPoints), Math.max(0, creatorBonus)]
);
return (result as any).affectedRows ?? (result as any).changes ?? 0;
}
export async function getInviteCodes(options: {
creatorId?: string;
limit?: number;
offset?: number;
showUsed?: boolean;
} = {}): Promise<InviteCode[]> {
await initializeCodesTables();
const db = getAdapter();
const limit = Math.max(Number(options.limit) || 50, 1);
const offset = Math.max(Number(options.offset) || 0, 0);
let sql = `
SELECT i.*,
u.email as creator_email, u.name as creator_name,
used.email as used_email, used.name as used_name
FROM invite_codes i
LEFT JOIN users u ON i.creator_id = u.id
LEFT JOIN users used ON i.used_by = used.id
WHERE 1=1
`;
const params: unknown[] = [];
if (options.creatorId) {
sql += ' AND creator_id = ?';
params.push(options.creatorId);
}
if (!options.showUsed) {
sql += ' AND used_by IS NULL';
}
sql += ` ORDER BY created_at DESC LIMIT ${limit} OFFSET ${offset}`;
const [rows] = await db.execute(sql, params);
return (rows as any[]).map(row => ({
id: row.id,
code: row.code,
creatorId: row.creator_id,
usedBy: row.used_by || undefined,
usedAt: row.used_at ? Number(row.used_at) : undefined,
bonusPoints: row.bonus_points || 0,
creatorBonus: row.creator_bonus || 0,
expiresAt: row.expires_at ? Number(row.expires_at) : undefined,
createdAt: Number(row.created_at),
creatorEmail: row.creator_email || undefined,
creatorName: row.creator_name || undefined,
usedByEmail: row.used_email || undefined,
usedByName: row.used_name || undefined,
}));
}
export async function getInviteCodesCount(options: { creatorId?: string; showUsed?: boolean } = {}): Promise<number> {
await initializeCodesTables();
const db = getAdapter();
let sql = 'SELECT COUNT(1) as count FROM invite_codes WHERE 1=1';
const params: unknown[] = [];
if (options.creatorId) {
sql += ' AND creator_id = ?';
params.push(options.creatorId);
}
if (!options.showUsed) {
sql += ' AND used_by IS NULL';
}
const [rows] = await db.execute(sql, params);
return Number((rows as any[])[0]?.count || 0);
}
export async function deleteInviteCode(id: string): Promise<boolean> {
await initializeCodesTables();
const db = getAdapter();
const [result] = await db.execute('DELETE FROM invite_codes WHERE id = ?', [id]);
const affected = (result as any).affectedRows ?? (result as any).changes ?? 0;
return affected > 0;
}
// ========================================
// Redemption code functions
// ========================================
function generateRedemptionCode(): string {
const chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
let code = '';
for (let i = 0; i < 16; i++) {
if (i > 0 && i % 4 === 0) code += '-';
code += chars.charAt(Math.floor(Math.random() * chars.length));
}
return code;
}
export async function createRedemptionCodes(
count: number,
points: number,
options: { expiresAt?: number; note?: string } = {}
): Promise<RedemptionCode[]> {
await initializeCodesTables();
const db = getAdapter();
const batchId = generateId();
const now = Date.now();
const codes: RedemptionCode[] = [];
for (let i = 0; i < count; i++) {
// Retry up to 5 times per code in case of collision
for (let attempt = 0; attempt < 5; attempt++) {
const code: RedemptionCode = {
id: generateId(),
code: generateRedemptionCode(),
points,
batchId,
note: options.note,
expiresAt: options.expiresAt,
createdAt: now,
};
try {
await db.execute(
`INSERT INTO redemption_codes (id, code, points, batch_id, note, expires_at, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
[code.id, code.code, code.points, code.batchId, code.note || null, code.expiresAt || null, code.createdAt]
);
codes.push(code);
break;
} catch (err: any) {
if (err?.code === 'ER_DUP_ENTRY' || err?.code === 'SQLITE_CONSTRAINT') {
if (attempt === 4) throw new Error('Failed to generate unique redemption code');
continue;
}
throw err;
}
}
}
return codes;
}
export async function getRedemptionCodeByCode(code: string): Promise<RedemptionCode | null> {
await initializeCodesTables();
const db = getAdapter();
const normalizedCode = code.toUpperCase().replace(/[^A-Z0-9]/g, '');
const formattedCode = normalizedCode.match(/.{1,4}/g)?.join('-') || normalizedCode;
const [rows] = await db.execute('SELECT * FROM redemption_codes WHERE code = ?', [formattedCode]);
const codes = rows as any[];
if (codes.length === 0) return null;
const row = codes[0];
return {
id: row.id,
code: row.code,
points: row.points,
usedBy: row.used_by || undefined,
usedAt: row.used_at ? Number(row.used_at) : undefined,
expiresAt: row.expires_at ? Number(row.expires_at) : undefined,
batchId: row.batch_id || undefined,
note: row.note || undefined,
createdAt: Number(row.created_at),
};
}
export async function redeemCode(code: string, userId: string): Promise<{ success: boolean; error?: string; points?: number }> {
await initializeCodesTables();
const db = getAdapter();
const redemption = await getRedemptionCodeByCode(code);
if (!redemption) return { success: false, error: '卡密不存在' };
if (redemption.usedBy) return { success: false, error: '卡密已被使用' };
if (redemption.expiresAt && redemption.expiresAt < Date.now()) return { success: false, error: '卡密已过期' };
const now = Date.now();
// Use atomic update with WHERE condition to prevent race condition
const [result] = await db.execute(
'UPDATE redemption_codes SET used_by = ?, used_at = ? WHERE id = ? AND used_by IS NULL',
[userId, now, redemption.id]
);
const affected = (result as any).affectedRows ?? (result as any).changes ?? 0;
if (affected === 0) {
return { success: false, error: '卡密已被使用' };
}
await db.execute(
'UPDATE users SET balance = balance + ?, updated_at = ? WHERE id = ?',
[redemption.points, now, userId]
);
return { success: true, points: redemption.points };
}
export async function getRedemptionCodes(options: {
batchId?: string;
limit?: number;
offset?: number;
showUsed?: boolean;
} = {}): Promise<RedemptionCode[]> {
await initializeCodesTables();
const db = getAdapter();
const limit = Math.max(Number(options.limit) || 50, 1);
const offset = Math.max(Number(options.offset) || 0, 0);
let sql = 'SELECT * FROM redemption_codes WHERE 1=1';
const params: unknown[] = [];
if (options.batchId) {
sql += ' AND batch_id = ?';
params.push(options.batchId);
}
if (!options.showUsed) {
sql += ' AND used_by IS NULL';
}
sql += ` ORDER BY created_at DESC LIMIT ${limit} OFFSET ${offset}`;
const [rows] = await db.execute(sql, params);
return (rows as any[]).map(row => ({
id: row.id,
code: row.code,
points: row.points,
usedBy: row.used_by || undefined,
usedAt: row.used_at ? Number(row.used_at) : undefined,
expiresAt: row.expires_at ? Number(row.expires_at) : undefined,
batchId: row.batch_id || undefined,
note: row.note || undefined,
createdAt: Number(row.created_at),
}));
}
export async function getRedemptionCodesCount(options: { batchId?: string; showUsed?: boolean } = {}): Promise<number> {
await initializeCodesTables();
const db = getAdapter();
let sql = 'SELECT COUNT(1) as count FROM redemption_codes WHERE 1=1';
const params: unknown[] = [];
if (options.batchId) {
sql += ' AND batch_id = ?';
params.push(options.batchId);
}
if (!options.showUsed) {
sql += ' AND used_by IS NULL';
}
const [rows] = await db.execute(sql, params);
return Number((rows as any[])[0]?.count || 0);
}
export async function getRecentRedemptionBatches(limit = 8): Promise<RedemptionBatchSummary[]> {
await initializeCodesTables();
const db = getAdapter();
const safeLimit = Math.max(1, Math.min(20, Math.floor(limit || 8)));
const [rows] = await db.execute(
`SELECT
batch_id,
COUNT(1) as count,
SUM(CASE WHEN used_by IS NOT NULL THEN 1 ELSE 0 END) as used_count,
MAX(points) as points,
MAX(note) as note,
MAX(expires_at) as expires_at,
MAX(created_at) as created_at
FROM redemption_codes
WHERE batch_id IS NOT NULL AND batch_id != ''
GROUP BY batch_id
ORDER BY created_at DESC
LIMIT ${safeLimit}`
);
return (rows as any[]).map((row) => {
const count = Number(row.count || 0);
const usedCount = Number(row.used_count || 0);
return {
batchId: row.batch_id,
count,
usedCount,
unusedCount: Math.max(0, count - usedCount),
points: Number(row.points || 0),
note: row.note || undefined,
expiresAt: row.expires_at ? Number(row.expires_at) : undefined,
createdAt: Number(row.created_at || 0),
};
});
}
export async function deleteRedemptionCode(id: string): Promise<boolean> {
await initializeCodesTables();
const db = getAdapter();
const [result] = await db.execute('DELETE FROM redemption_codes WHERE id = ?', [id]);
const affected = (result as any).affectedRows ?? (result as any).changes ?? 0;
return affected > 0;
}
export async function deleteRedemptionCodesByBatch(batchId: string): Promise<number> {
await initializeCodesTables();
const db = getAdapter();
const [result] = await db.execute('DELETE FROM redemption_codes WHERE batch_id = ? AND used_by IS NULL', [batchId]);
return (result as any).affectedRows ?? (result as any).changes ?? 0;
}
// ========================================
// Statistics functions
// ========================================
export async function getStatsOverview(days = 30): Promise<StatsOverview> {
await initializeCodesTables();
const db = getAdapter();
const dbType = process.env.DB_TYPE || 'sqlite';
// Use UTC for consistency
const now = new Date();
const todayUTC = Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate());
const startDate = todayUTC - (days - 1) * 24 * 60 * 60 * 1000;
// Total counts
const [userRows] = await db.execute('SELECT COUNT(1) as count FROM users');
const totalUsers = Number((userRows as any[])[0]?.count || 0);
const [activeRows] = await db.execute('SELECT COUNT(1) as count FROM users WHERE disabled = 0');
const activeUsers = Number((activeRows as any[])[0]?.count || 0);
const [chatModelRows] = await db.execute('SELECT COUNT(1) as count FROM chat_models');
const totalChatModels = Number((chatModelRows as any[])[0]?.count || 0);
const [chatEnabledRows] = await db.execute('SELECT COUNT(1) as count FROM chat_models WHERE enabled = 1');
const enabledChatModels = Number((chatEnabledRows as any[])[0]?.count || 0);
const [genRows] = await db.execute('SELECT COUNT(1) as count FROM generations');
const totalGenerations = Number((genRows as any[])[0]?.count || 0);
const [pointsRows] = await db.execute('SELECT SUM(balance) as total FROM users');
const totalPoints = Number((pointsRows as any[])[0]?.total || 0);
// Today counts
const [todayUserRows] = await db.execute('SELECT COUNT(1) as count FROM users WHERE created_at >= ?', [todayUTC]);
const todayUsers = Number((todayUserRows as any[])[0]?.count || 0);
const [todayGenRows] = await db.execute('SELECT COUNT(1) as count FROM generations WHERE created_at >= ?', [todayUTC]);
const todayGenerations = Number((todayGenRows as any[])[0]?.count || 0);
// Daily stats - use aggregation query instead of per-day queries
const dailyStats: DailyStats[] = [];
// Initialize all days with zero values
const dayMap = new Map<string, DailyStats>();
for (let i = 0; i < days; i++) {
const dayStart = startDate + i * 24 * 60 * 60 * 1000;
const dateStr = new Date(dayStart).toISOString().split('T')[0];
dayMap.set(dateStr, { date: dateStr, generations: 0, users: 0, points: 0 });
}
// Aggregate generations by day - ensure consistent YYYY-MM-DD string format
const dateExpr = dbType === 'mysql'
? "DATE_FORMAT(FROM_UNIXTIME(created_at / 1000), '%Y-%m-%d')"
: "strftime('%Y-%m-%d', created_at / 1000, 'unixepoch')";
const [genByDay] = await db.execute(
`SELECT ${dateExpr} as day, COUNT(1) as count, SUM(cost) as points
FROM generations
WHERE created_at >= ?
GROUP BY day`,
[startDate]
);
for (const row of genByDay as any[]) {
// Handle both string and Date object formats
let dayKey: string;
if (row.day instanceof Date) {
dayKey = row.day.toISOString().split('T')[0];
} else {
dayKey = String(row.day);
}
const stat = dayMap.get(dayKey);
if (stat) {
stat.generations = Number(row.count || 0);
stat.points = Number(row.points || 0);
}
}
// Aggregate users by day
const [usersByDay] = await db.execute(
`SELECT ${dateExpr} as day, COUNT(1) as count
FROM users
WHERE created_at >= ?
GROUP BY day`,
[startDate]
);
for (const row of usersByDay as any[]) {
// Handle both string and Date object formats
let dayKey: string;
if (row.day instanceof Date) {
dayKey = row.day.toISOString().split('T')[0];
} else {
dayKey = String(row.day);
}
const stat = dayMap.get(dayKey);
if (stat) {
stat.users = Number(row.count || 0);
}
}
// Convert map to sorted array
for (let i = 0; i < days; i++) {
const dayStart = startDate + i * 24 * 60 * 60 * 1000;
const dateStr = new Date(dayStart).toISOString().split('T')[0];
const stat = dayMap.get(dateStr);
if (stat) dailyStats.push(stat);
}
const [typeRows] = await db.execute(
`SELECT type, COUNT(1) as count
FROM generations
WHERE created_at >= ?
GROUP BY type`,
[startDate]
);
const generationTypes = (typeRows as any[]).map((row) => ({
type: row.type,
count: Number(row.count || 0),
}));
return {
totalUsers,
activeUsers,
totalChatModels,
enabledChatModels,
totalGenerations,
totalPoints,
todayUsers,
todayGenerations,
dailyStats,
generationTypes,
};
}
// ========================================
// Admin generation management
// ========================================
export async function getAllGenerations(options: {
limit?: number;
offset?: number;
userId?: string;
type?: string;
status?: string;
search?: string;
} = {}): Promise<{ generations: any[]; total: number }> {
await initializeCodesTables();
const db = getAdapter();
const limit = Math.max(Number(options.limit) || 50, 1);
const offset = Math.max(Number(options.offset) || 0, 0);
const whereClauses: string[] = [];
const params: unknown[] = [];
if (options.userId) {
whereClauses.push('g.user_id = ?');
params.push(options.userId);
}
if (options.type) {
whereClauses.push('g.type = ?');
params.push(options.type);
}
if (options.status) {
whereClauses.push('g.status = ?');
params.push(options.status);
}
if (options.search) {
const pattern = `%${options.search}%`;
whereClauses.push('(u.email LIKE ? OR u.name LIKE ? OR g.prompt LIKE ?)');
params.push(pattern, pattern, pattern);
}
const whereStr = whereClauses.length > 0 ? 'WHERE ' + whereClauses.join(' AND ') : '';
const countJoin = options.search ? 'LEFT JOIN users u ON g.user_id = u.id' : '';
// Get total count
const [countRows] = await db.execute(
`SELECT COUNT(1) as count FROM generations g ${countJoin} ${whereStr}`,
params
);
const total = Number((countRows as any[])[0]?.count || 0);
// Get generations with user info
const [rows] = await db.execute(
`SELECT g.*, u.email as user_email, u.name as user_name
FROM generations g
LEFT JOIN users u ON g.user_id = u.id
${whereStr}
ORDER BY g.created_at DESC
LIMIT ${limit} OFFSET ${offset}`,
params
);
const generations = (rows as any[]).map(row => ({
id: row.id,
userId: row.user_id,
userEmail: row.user_email,
userName: row.user_name,
type: row.type,
prompt: row.prompt,
params: typeof row.params === 'string' ? JSON.parse(row.params || '{}') : row.params,
resultUrl: row.result_url,
cost: row.cost,
status: row.status || 'completed',
errorMessage: row.error_message,
createdAt: Number(row.created_at),
updatedAt: Number(row.updated_at || row.created_at),
}));
return { generations, total };
}
export async function adminDeleteGeneration(id: string): Promise<boolean> {
await initializeCodesTables();
const db = getAdapter();
const [result] = await db.execute('DELETE FROM generations WHERE id = ?', [id]);
const affected = (result as any).affectedRows ?? (result as any).changes ?? 0;
return affected > 0;
}