import type express from 'express';
import {
ADMIN_DOWNLOAD_PATH,
ADMIN_LICENSE_PATH,
ADMIN_LICENSES_ACTIVE_PATH,
ADMIN_POPULATE_PATH,
ADMIN_SIGNUPS_PATH,
} from './api-types.js';
import {v4 as uuidv4} from 'uuid';
import {
dbDateToday,
prisma,
monthsAgo,
lastDayOfMonth,
firstDayOfMonth,
} from './db.js';
import {dbDateFromString} from './db.js';
import type {LogContext} from '@rocicorp/logger';
import {esc} from './xss.js';
import type {PrismaClient, Prisma} from '@prisma/client';
// This is the admin index page.
export function index(
_: express.Request,
res: express.Response,
_2: LogContext,
) {
const licenseLocation = ADMIN_LICENSE_PATH.replace(':key', '');
const licenseLookup =
`License look up: ` +
` ` +
``;
const body =
`
');
}
// This is the view of a single license.
export async function licenseView(
req: express.Request,
res: express.Response,
lc: LogContext,
) {
const {key} = req.params;
const license = await prisma.license.findUnique({
where: {key},
include: {
customer: true,
},
});
if (!license) {
res.status(404);
res.send(`License not found: ${esc(key)}`);
return;
}
const licenseInfo = JSON.stringify(license, null, 2);
let activesSummary = '
Actives in recent months:
';
for (let i = 0; i < 6; i++) {
const date = monthsAgo(await dbDateToday(), i);
const first = firstDayOfMonth(date);
const last = lastDayOfMonth(date);
const activesCount = await activesForLicenseInWindow(
license.key,
first,
last,
lc,
);
activesSummary += `${date.toISOString().slice(0, 7)}: ${activesCount}\n`;
}
activesSummary += '
';
const body =
`
License ${esc(key)}
` +
`
${esc(licenseInfo)}
` +
`${activesSummary}` +
``;
res.send(body);
}
// This is the view of all active licenses and their MAP counts.
export async function activesView(
req: express.Request,
res: express.Response,
_2: LogContext,
) {
const windows: {
start: string;
end: string;
actives: ActivesInWindowResult[];
}[] = [];
const monthsAgoParam = parseInt((req.query['monthsAgo'] as string) ?? 0, 10);
const date = monthsAgo(await dbDateToday(), monthsAgoParam);
const start = firstDayOfMonth(date);
const end = lastDayOfMonth(date);
const actives = await activesInWindow(start, end);
windows.push({
start: start.toISOString().slice(0, 10),
end: end.toISOString().slice(0, 10),
actives,
});
// Calculate last month and next month values.
const lastMonth = monthsAgoParam + 1;
const nextMonth = Math.max(0, monthsAgoParam - 1); // Ensure it doesn't go negative.
const url = req.originalUrl.split('?')[0];
const lastMonthLink = `${url}?monthsAgo=${lastMonth}`;
const nextMonthLink = `${url}?monthsAgo=${nextMonth}`;
const navigationLinks =
`Last Month ` +
(monthsAgoParam > 0 ? `| Next Month` : '');
let activesSummaryEscaped = '';
for (let i = 0; i < windows.length; i++) {
const {start, end, actives} = windows[i];
activesSummaryEscaped +=
`
${esc(start)} - ${esc(end)}
` +
navigationLinks +
`
customerId
customerName
email
` +
`
project
commercial
licenseKey
unique active browser profiles (in this time period)
`;
for (const active of actives) {
// TODO we should have a better way to do this, and also test these links work.
const licenseKeyPath = ADMIN_LICENSE_PATH.replace(
':key',
active.licenseKey,
);
const licenseKeyEscaped = `${esc(
active.licenseKey,
)}`;
activesSummaryEscaped += `
${active.customerId}
${esc(
active.customerName,
)}
${esc(active.email)}
${esc(
active.project ?? '',
)}
${active.commercial}
${licenseKeyEscaped}
${
active.cnt
}
`;
}
activesSummaryEscaped += `
`;
}
const body =
`` +
`` +
`
License Actives in recent months
` +
`(shows licenses with >0 MAP)` +
`${activesSummaryEscaped}` +
``;
res.send(body);
}
export async function activesForLicenseInWindow(
licenseKey: string,
gte: Date,
lte: Date,
_: LogContext,
) {
// OMG this is SO LAME. Count distinct is not supported in Prisma?!
// https://github.com/prisma/prisma/issues/4228
// So instead we do the query and return the length of records returned.
// Probably cheaper to write the raw SQL, but this works.
const actives = await prisma.active.findMany({
where: {
licenseKey,
date: {gte, lte},
},
distinct: ['profileID'],
});
return actives.length;
}
export type ActivesInWindowResult = {
customerId: number;
customerName: string;
email: string;
project: string | undefined;
commercial: boolean;
licenseKey: string;
cnt: number;
};
export async function activesInWindow(gte: Date, lte: Date) {
const actives = await prisma.$queryRaw`
WITH ActiveDate AS (
SELECT distinct
"Active"."licenseKey", "Active"."profileID"
FROM
"Active"
WHERE date>=${gte}
AND date<=${lte}
) SELECT "Customer"."id" as "customerId", "Customer"."name" as "customerName",
"Customer"."email" as "email", "Customer"."project" as "project", "licenseKey" , "commercial",
count(distinct("profileID")) as cnt FROM ActiveDate INNER JOIN "License" on
ActiveDate."licenseKey" = "License"."key" INNER JOIN "Customer" on
"License"."customerId"="Customer"."id" GROUP BY ("Customer"."id", "licenseKey", "commercial") ORDER BY "Customer"."project"`;
return actives as ActivesInWindowResult[];
}
// This is the view of all recently created licenses and their total ping counts.
export async function newSignupsView(
req: express.Request,
res: express.Response,
_2: LogContext,
) {
// Show the most recent n licenses created.
const {n} = req.query;
const nInt = parseInt(n as string, 10);
if (isNaN(nInt)) {
// TODO why can't we just raise here?
res.status(400).send('n must be a number');
return;
}
const newSignups = await newSignupActives(nInt);
let newSignupsEscaped =
`
Most recent ${n} licenses created
` +
`
customerId
createdAt (UTC)
name
` +
`
project
email
licenseKey
commercial
` +
`
total unique active browser profiles (over all time)
`;
for (let i = 0; i < newSignups.length; i++) {
const row = newSignups[i];
// TODO we should have a better way to do this, and also test these links work.
const licenseKeyPath = ADMIN_LICENSE_PATH.replace(':key', row.licenseKey);
const licenseKeyHtml = `${esc(
row.licenseKey,
)}`;
newSignupsEscaped += `
${row.customerId}
${esc(
row.createdAt.toString(),
)}
${esc(row.name)}
${esc(row.project ?? '')}
${
row.email
}
${licenseKeyHtml}
${row.commercial}
${
row.cnt
}
`;
}
newSignupsEscaped += `
`;
const html =
`` +
`` +
`` +
`${newSignupsEscaped}` +
``;
res.send(html);
}
export type NewSignupActivesResult = {
customerId: number;
createdAt: Date;
name: string;
project: string | undefined;
email: string;
licenseKey: string;
commercial: boolean;
cnt: number;
};
export async function newSignupActives(n: number) {
const actives = await prisma.$queryRaw`
select "Customer".id as "customerId", "Customer"."createdAt", "Customer".name, "Customer".project, "Customer".email,
"License".key as "licenseKey", "License".commercial,
count(distinct("Active"."profileID")) as "cnt"
from
"Customer" JOIN "License" on "Customer".id="License"."customerId"
LEFT JOIN "Active" on "License".key="Active"."licenseKey"
where
"Customer".id in (select id from "Customer" order by id desc limit ${n})
group by "Customer"."id", "License".key
order by "customerId" desc;
`;
return actives as NewSignupActivesResult[];
}
// TODO with some small tweaks, activesInWindow could serve for the query here,
// it's much cleaner.
export async function downloadActives(
_: express.Request,
res: express.Response,
_2: LogContext,
) {
// Another way to do this instead of the join is with something like:
// await prisma.customer.findMany({
// include: {
// license: {
// include: {
// actives: true,
// },
// },
// },
// });
// This is so dumb. What we want to do is select a subset of the fields of the joined
// table, but prisma chokes with a rust error when I try to select more than one field
// at a time. So instead we select the entire joined table and then output the fields
// we want, in the order we want.
const joinResult = await prisma.$queryRaw`SELECT * FROM "Customer"
FULL JOIN "License" ON "Customer"."id" = "License"."customerId"
FULL JOIN "Active" ON "License"."key" = "Active"."licenseKey"
ORDER BY "Customer"."id" ASC`;
// First element is field name, second is label which defaults to field name.
const wantedFields = [
['customerId'],
['name'],
['email'],
['project'],
['commercial'],
['status'],
['key'],
['profileID'],
['date', 'pingDate'],
];
const rows: string[][] = [];
const headings: string[] = wantedFields.map(entry => {
return entry[1] ?? entry[0];
});
rows.push(headings);
// eslint-disable-next-line @typescript-eslint/no-explicit-any
for (const joinResultRow of joinResult as any[]) {
const row: string[] = [];
for (const wantedField of wantedFields) {
let value = joinResultRow[wantedField[0]];
if (typeof value === 'string') {
value = cleanForCSV(value);
}
row.push(value);
}
rows.push(row);
}
res.set('Content-Type', 'text/csv');
res.set('Content-Disposition', 'attachment; filename=actives.csv');
res.send(rows.map(row => row.join(',')).join('\n'));
}
// I'm not sure how to properly escape CSV data but also I don't think we
// care about anything other than just not allowing potentially dangerous cells
// (e.g., macros) or format-busting characters (e.g., commas). So, just allowlist
// the characters we want. If people submit non-ascii strings we can do
// something smarter.
export function cleanForCSV(s: string) {
return s.replace(/[^0-9a-zA-Z.\-@:/?& ]/g, '_');
}
// LicensePings is a set of active pings to create for the given
// license key.
type LicensePings = {
licenseKey: string;
profiles: ProfilePings[];
};
type ProfilePings = {
profileID: string;
dates: string[];
};
export async function populateActives(
prisma: PrismaClient<
Prisma.PrismaClientOptions,
never,
Prisma.RejectOnNotFound | Prisma.RejectPerOperation | undefined
>,
licensePings: LicensePings[],
) {
const customerIds = [];
for (const licensePing of licensePings) {
const license = await prisma.license.create({
data: {
key: licensePing.licenseKey,
commercial: true,
acceptedTOS: true,
customer: {
create: {
name: 'some customer',
email: 'fake@example.com',
},
},
},
});
customerIds.push(license.customerId);
for (const profile of licensePing.profiles) {
for (const date of profile.dates) {
await prisma.active.create({
data: {
date: dbDateFromString(date),
licenseKey: licensePing.licenseKey,
profileID: profile.profileID,
},
});
}
}
}
return customerIds;
}