import { desc, eq } from "drizzle-orm";
import {
  InsertUser,
  users,
  contactSubmissions,
  InsertContactSubmission,
} from "../drizzle/schema";
import { getDb } from "./config/database";

// All DB access flows through the central connection in ./config/database.ts.

export async function upsertUser(user: InsertUser): Promise<void> {
  if (!user.email) throw new Error("User email is required for upsert");
  const db = getDb();
  if (!db) {
    console.warn("[Database] Cannot upsert user: database not available");
    return;
  }
  try {
    const values: InsertUser = {
      email: user.email,
      name: user.name ?? null,
      loginMethod: user.loginMethod ?? "password",
      lastSignedIn: user.lastSignedIn ?? new Date(),
    };
    if (user.passwordHash !== undefined) values.passwordHash = user.passwordHash;
    if (user.role !== undefined) values.role = user.role;

    const updateSet: Record<string, unknown> = { lastSignedIn: values.lastSignedIn };
    if (user.name !== undefined) updateSet.name = user.name ?? null;
    if (user.passwordHash !== undefined) updateSet.passwordHash = user.passwordHash;
    if (user.role !== undefined) updateSet.role = user.role;

    await db.insert(users).values(values).onDuplicateKeyUpdate({ set: updateSet });
  } catch (error) {
    console.error("[Database] Failed to upsert user:", error);
    throw error;
  }
}

export async function getUserByEmail(email: string) {
  const db = getDb();
  if (!db) return undefined;
  const result = await db.select().from(users).where(eq(users.email, email)).limit(1);
  return result.length > 0 ? result[0] : undefined;
}

export async function createContactSubmission(submission: InsertContactSubmission) {
  const db = getDb();
  if (!db) throw new Error("Database not available");
  await db.insert(contactSubmissions).values(submission);
  return { success: true };
}

export async function getContactSubmissions(opts?: {
  status?: string;
  limit?: number;
  offset?: number;
}) {
  const db = getDb();
  if (!db) throw new Error("Database not available");
  let query: any = db.select().from(contactSubmissions);
  if (opts?.status && opts.status !== "all") {
    query = query.where(eq(contactSubmissions.status, opts.status as any));
  }
  query = query.orderBy(desc(contactSubmissions.createdAt));
  if (opts?.limit) query = query.limit(opts.limit);
  if (opts?.offset) query = query.offset(opts.offset);
  return query;
}

export async function getContactSubmissionById(id: number) {
  const db = getDb();
  if (!db) throw new Error("Database not available");
  const result = await db
    .select()
    .from(contactSubmissions)
    .where(eq(contactSubmissions.id, id))
    .limit(1);
  return result.length > 0 ? result[0] : null;
}

export async function updateContactSubmissionStatus(
  id: number,
  status: "new" | "read" | "replied" | "archived"
) {
  const db = getDb();
  if (!db) throw new Error("Database not available");
  await db.update(contactSubmissions).set({ status }).where(eq(contactSubmissions.id, id));
  return { success: true };
}

export async function getContactSubmissionStats() {
  const db = getDb();
  if (!db) throw new Error("Database not available");
  const all = await db.select().from(contactSubmissions);
  return {
    total: all.length,
    new: all.filter((s) => s.status === "new").length,
    read: all.filter((s) => s.status === "read").length,
    replied: all.filter((s) => s.status === "replied").length,
    archived: all.filter((s) => s.status === "archived").length,
  };
}