import { and, desc, eq, gte, sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { clickEvents, InsertUser, links, localCredentials, users } from "../drizzle/schema";
import { ENV } from "./_core/env";

let _db: ReturnType<typeof drizzle> | null = null;
export async function getDb() { if (!_db && process.env.DATABASE_URL) { try { _db = drizzle(process.env.DATABASE_URL); } catch { _db = null; } } return _db; }
export async function upsertUser(user: InsertUser): Promise<void> { if (!user.openId) throw new Error("User openId is required for upsert"); const db = await getDb(); if (!db) return; const values: InsertUser = { openId: user.openId }; const updateSet: Record<string, unknown> = {}; (['name', 'email', 'loginMethod'] as const).forEach(field => { if (user[field] !== undefined) { values[field] = user[field] ?? null; updateSet[field] = user[field] ?? null; } }); values.lastSignedIn = user.lastSignedIn ?? new Date(); updateSet.lastSignedIn = values.lastSignedIn; if (user.role !== undefined || user.openId === ENV.ownerOpenId) { values.role = user.role ?? 'admin'; updateSet.role = values.role; } await db.insert(users).values(values).onDuplicateKeyUpdate({ set: updateSet }); }
export async function getUserByOpenId(openId: string) { const db = await getDb(); if (!db) return undefined; const result = await db.select().from(users).where(eq(users.openId, openId)).limit(1); return result[0]; }
export async function getUserById(id: number) { const db = await getDb(); if (!db) return undefined; const result = await db.select().from(users).where(eq(users.id, id)).limit(1); return result[0]; }
export async function getLocalCredential(identifier: string) { const db = await getDb(); if (!db) return undefined; const result = await db.select({ credential: localCredentials, user: users }).from(localCredentials).innerJoin(users, eq(localCredentials.userId, users.id)).where(eq(localCredentials.identifier, identifier)).limit(1); return result[0]; }
export async function createLocalUser(input: { name: string; identifier: string; passwordHash: string }) { const db = await getDb(); if (!db) throw new Error("Database is not available"); const result = await db.insert(users).values({ openId: `local_${Date.now()}_${Math.random().toString(36).slice(2, 10)}`, name: input.name, email: input.identifier.includes("@") ? input.identifier : null, loginMethod: "password" }); const userId = Number(result[0].insertId); await db.insert(localCredentials).values({ userId, identifier: input.identifier, passwordHash: input.passwordHash }); return getUserById(userId); }
export async function listLinksByUser(userId: number) { const db = await getDb(); if (!db) return []; return db.select().from(links).where(eq(links.userId, userId)).orderBy(desc(links.createdAt)); }
export async function getLinkById(id: number, userId?: number) { const db = await getDb(); if (!db) return undefined; const conditions = userId === undefined ? eq(links.id, id) : and(eq(links.id, id), eq(links.userId, userId)); const result = await db.select().from(links).where(conditions).limit(1); return result[0]; }
export async function getLinkBySlug(slug: string) { const db = await getDb(); if (!db) return undefined; const result = await db.select().from(links).where(eq(links.slug, slug)).limit(1); return result[0]; }
export async function createLink(input: typeof links.$inferInsert) { const db = await getDb(); if (!db) throw new Error("Database is not available"); const result = await db.insert(links).values(input); return getLinkById(Number(result[0].insertId)); }
export async function updateLink(id: number, userId: number, values: Partial<typeof links.$inferInsert>) { const db = await getDb(); if (!db) throw new Error("Database is not available"); await db.update(links).set(values).where(and(eq(links.id, id), eq(links.userId, userId))); return getLinkById(id, userId); }
export async function deleteLink(id: number, userId: number) { const db = await getDb(); if (!db) throw new Error("Database is not available"); await db.delete(clickEvents).where(eq(clickEvents.linkId, id)); await db.delete(links).where(and(eq(links.id, id), eq(links.userId, userId))); }
export async function recordClick(linkId: number) { const db = await getDb(); if (!db) return; await db.insert(clickEvents).values({ linkId, device: "Unknown", browser: "Unknown" }); await db.update(links).set({ clicks: sql`${links.clicks} + 1` }).where(eq(links.id, linkId)); }
export async function getLinkStats(linkId: number, userId: number) { const db = await getDb(); if (!db) return { link: undefined, devices: [], recentClicks: [] }; const link = await getLinkById(linkId, userId); if (!link) return { link: undefined, devices: [], recentClicks: [] }; const devices = await db.select({ device: clickEvents.device, clicks: sql<number>`count(*)` }).from(clickEvents).where(eq(clickEvents.linkId, linkId)).groupBy(clickEvents.device); const recentClicks = await db.select().from(clickEvents).where(eq(clickEvents.linkId, linkId)).orderBy(desc(clickEvents.clickedAt)).limit(20); return { link, devices, recentClicks }; }
export async function getDashboardSummary(userId: number) { const db = await getDb(); if (!db) return { totalLinks: 0, totalClicks: 0, activeLinks: 0, dailyClicks: [] }; const [summary] = await db.select({ totalLinks: sql<number>`count(*)`, totalClicks: sql<number>`coalesce(sum(${links.clicks}), 0)`, activeLinks: sql<number>`coalesce(sum(case when ${links.status} = 'active' then 1 else 0 end), 0)` }).from(links).where(eq(links.userId, userId)); const start = new Date(Date.now() - 6 * 24 * 60 * 60 * 1000); const dailyClicks = await db.select({ date: sql<string>`date_format(${clickEvents.clickedAt}, '%Y-%m-%d')`, clicks: sql<number>`count(*)` }).from(clickEvents).innerJoin(links, eq(clickEvents.linkId, links.id)).where(and(eq(links.userId, userId), gte(clickEvents.clickedAt, start))).groupBy(sql`date_format(${clickEvents.clickedAt}, '%Y-%m-%d')`).orderBy(sql`date_format(${clickEvents.clickedAt}, '%Y-%m-%d')`); return { ...summary, dailyClicks }; }
export async function listAllLinks() { const db = await getDb(); if (!db) return []; return db.select({ link: links, user: users }).from(links).innerJoin(users, eq(links.userId, users.id)).orderBy(desc(links.createdAt)); }