File size: 3,463 Bytes
e8e032d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
import "server-only";
import { and, desc, eq, gte, ilike, sql } from "drizzle-orm";
import { db } from "@/db";
import { applications, jobs, profiles } from "@/db/schema";
import { scoreJob, type Job, type MatchResult, type Profile } from "./match";

const DAY = 86400_000;

export type JobFilters = {
  q?: string;
  remote?: string;
  type?: string;
  source?: string;
  minMatch?: number;
  posted?: string;
  sort?: string;
  limit?: number;
};

export type JobListItem = Omit<Job, "description"> & { score: number };

export async function getProfile(userId: string): Promise<Profile> {
  const [p] = await db.select().from(profiles).where(eq(profiles.userId, userId));
  if (p) return p;
  const [created] = await db.insert(profiles).values({ userId }).returning();
  return created;
}

export async function searchJobs(profile: Profile, f: JobFilters) {
  const conds = [];
  const terms = (f.q || "").trim().split(/\s+/).filter(Boolean).slice(0, 4);
  for (const t of terms) {
    const p = `%${t}%`;
    conds.push(
      sql`(${jobs.title} ilike ${p} or ${jobs.company} ilike ${p} or ${jobs.tags}::text ilike ${p} or ${jobs.category} ilike ${p})`,
    );
  }
  if (f.remote === "1") conds.push(eq(jobs.remote, true));
  if (f.type) conds.push(ilike(jobs.jobType, `%${f.type}%`));
  if (f.source) conds.push(eq(jobs.source, f.source));
  const days = Number(f.posted);
  if (days > 0) conds.push(gte(jobs.postedAt, new Date(Date.now() - days * DAY)));

  const rows = await db
    .select()
    .from(jobs)
    .where(conds.length ? and(...conds) : undefined)
    .orderBy(desc(jobs.postedAt))
    .limit(1500);

  const seen = new Set<string>();
  const unique = rows.filter((j) => {
    const k = `${j.company.toLowerCase().trim()}|${j.title.toLowerCase().replace(/\s+/g, " ").trim()}`;
    if (seen.has(k)) return false;
    seen.add(k);
    return true;
  });
  let scored = unique.map((j) => ({ j, m: scoreJob(j, profile) }));
  if (f.minMatch) scored = scored.filter((s) => s.m.score >= (f.minMatch as number));
  if (f.sort === "new") scored.sort((a, b) => b.j.postedAt.getTime() - a.j.postedAt.getTime());
  else if (f.sort === "salary")
    scored.sort((a, b) => (b.j.salaryMax ?? b.j.salaryMin ?? 0) - (a.j.salaryMax ?? a.j.salaryMin ?? 0));
  else scored.sort((a, b) => b.m.score - a.m.score || b.j.postedAt.getTime() - a.j.postedAt.getTime());

  const total = scored.length;
  const items: JobListItem[] = scored.slice(0, f.limit ?? 60).map(({ j, m }) => {
    // eslint-disable-next-line @typescript-eslint/no-unused-vars
    const { description, ...rest } = j;
    return { ...rest, score: m.score };
  });
  return { items, total };
}

export async function getJobDetail(userId: string, profile: Profile, id: number) {
  const [job] = await db.select().from(jobs).where(eq(jobs.id, id));
  if (!job) return null;
  const match: MatchResult = scoreJob(job, profile);
  const [app] = await db
    .select({ id: applications.id, status: applications.status })
    .from(applications)
    .where(and(eq(applications.userId, userId), eq(applications.jobId, id)));
  return { job, match, app: app ?? null };
}

export async function getJobStats() {
  const r = await db.execute(sql`
    select count(*)::int as total,
      count(*) filter (where posted_at > now() - interval '24 hours')::int as today,
      count(*) filter (where remote)::int as remote
    from jobs`);
  return r.rows[0] as { total: number; today: number; remote: number };
}