Spaces:
Starting
Starting
File size: 4,362 Bytes
4f7cbb5 7aa8d9d 4f7cbb5 10f0b14 4f7cbb5 10f0b14 4f7cbb5 10f0b14 4f7cbb5 10f0b14 4f7cbb5 10f0b14 4f7cbb5 10f0b14 4f7cbb5 10f0b14 4f7cbb5 | 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 | import { Controller, Get } from "@nestjs/common";
import { Inject } from "@nestjs/common";
import { eq, desc, sql } from "drizzle-orm";
import { schema, type Database } from "@courtmitra/db";
import { DB } from "../db/db.module.js";
@Controller()
export class LedgerController {
constructor(@Inject(DB) private readonly db: Database) {}
/**
* GET /api/ledger
* Returns anonymized, aggregated resolution outcomes.
* Only includes outcomes where the user consented to publish.
*/
@Get("ledger")
async getLedger() {
// 1. Query from ledger_stats_view materialized view
const statsResult = await this.db.execute(
sql`SELECT domain, total_resolved, avg_recovery, avg_satisfaction, total_refunds FROM ledger_stats_view`
);
const viewRows = (statsResult.rows as any[]) || [];
// Calculate overall aggregates
const totalResolved = viewRows.reduce((acc, r) => acc + Number(r.total_resolved || 0), 0);
let recoverySum = 0;
let recoveryCount = 0;
let satisfactionSum = 0;
let satisfactionCount = 0;
for (const r of viewRows) {
const resolved = Number(r.total_resolved || 0);
if (r.avg_recovery !== null && r.avg_recovery !== undefined) {
recoverySum += Number(r.avg_recovery) * resolved;
recoveryCount += resolved;
}
if (r.avg_satisfaction !== null && r.avg_satisfaction !== undefined) {
satisfactionSum += Number(r.avg_satisfaction) * resolved;
satisfactionCount += resolved;
}
}
const avgRecovery = recoveryCount > 0 ? recoverySum / recoveryCount : null;
const avgSatisfaction = satisfactionCount > 0 ? satisfactionSum / satisfactionCount : null;
const byDomain = viewRows.map((r) => ({
domain: String(r.domain),
count: Number(r.total_resolved || 0),
}));
// 2. Query from repeat_offender_view materialized view
const offendersResult = await this.db.execute(
sql`SELECT company_name, total_complaints, total_closed, total_active FROM repeat_offender_view ORDER BY total_complaints DESC`
);
const repeatOffenders = (offendersResult.rows as any[] || []).map((r) => ({
companyName: String(r.company_name),
totalComplaints: Number(r.total_complaints || 0),
totalClosed: Number(r.total_closed || 0),
totalActive: Number(r.total_active || 0),
}));
// 3. Compute route success rates (needs total missions count per domain)
const totalMissionsByDomain = await this.db
.select({
domain: schema.missions.issueDomain,
count: sql<number>`count(*)::int`,
})
.from(schema.missions)
.groupBy(schema.missions.issueDomain);
const missionCounts: Record<string, number> = {};
for (const row of totalMissionsByDomain) {
missionCounts[row.domain] = row.count;
}
const routeSuccessRates = Object.keys(missionCounts).map((domain) => {
const viewRow = viewRows.find((v) => String(v.domain) === domain);
const domainResolved = viewRow ? Number(viewRow.total_resolved || 0) : 0;
const totalMissions = missionCounts[domain] || 0;
const successRate = totalMissions > 0 ? domainResolved / totalMissions : 0;
return {
domain,
totalMissions,
totalResolved: domainResolved,
successRate,
};
});
// 4. Query recent outcomes (this requires individual records, so query from table directly)
const recentRows = await this.db
.select({
outcomeType: schema.resolutionOutcomes.outcomeType,
anonymizedSummary: schema.resolutionOutcomes.anonymizedPublicSummary,
domain: schema.missions.issueDomain,
createdAt: schema.resolutionOutcomes.createdAt,
})
.from(schema.resolutionOutcomes)
.innerJoin(schema.missions, eq(schema.resolutionOutcomes.missionId, schema.missions.id))
.where(eq(schema.resolutionOutcomes.publishConsent, true))
.orderBy(desc(schema.resolutionOutcomes.createdAt))
.limit(20);
return {
totalResolved,
byDomain,
avgRecovery,
avgSatisfaction,
repeatOffenders,
routeSuccessRates,
recentOutcomes: recentRows.map((r) => ({
outcomeType: r.outcomeType,
anonymizedSummary: r.anonymizedSummary,
domain: r.domain,
createdAt: r.createdAt.toISOString(),
})),
};
}
}
|