import { and, desc, eq, sql } from "drizzle-orm";
import { getDb } from "@/core/db/client";
import { inquiries } from "@/core/db/schema";
import type { TenantContext } from "@/core/tenant/types";
import type { createInquirySchema } from "@/core/validation/schemas";
import type { z } from "zod";

type CreateInquiryInput = z.infer<typeof createInquirySchema>;

export type InquiryStatus =
  | "new"
  | "qualified"
  | "consultation"
  | "proposal"
  | "won"
  | "lost";

/**
 * Inquiry creation binds to trusted TenantContext only.
 * Never accept tenant_id from the browser.
 */
export async function createInquiry(
  ctx: TenantContext,
  input: CreateInquiryInput,
) {
  if (ctx.status !== "active") {
    throw new Error("Tenant cannot accept inquiries");
  }

  const db = getDb();
  const [inquiry] = await db
    .insert(inquiries)
    .values({
      tenantId: ctx.tenantId,
      name: input.name.trim(),
      email: input.email.trim().toLowerCase(),
      phone: input.phone,
      projectType: input.projectType,
      budget: input.budget,
      location: input.location,
      timeline: input.timeline,
      projectSize: input.projectSize,
      message: input.message.trim(),
      status: "new",
    })
    .returning({
      id: inquiries.id,
      createdAt: inquiries.createdAt,
    });

  return inquiry;
}

/** Dashboard listing — always scoped to trusted tenant. */
export async function listInquiries(ctx: TenantContext, limit = 100) {
  const db = getDb();
  return db
    .select({
      id: inquiries.id,
      name: inquiries.name,
      email: inquiries.email,
      phone: inquiries.phone,
      projectType: inquiries.projectType,
      budget: inquiries.budget,
      location: inquiries.location,
      timeline: inquiries.timeline,
      projectSize: inquiries.projectSize,
      message: inquiries.message,
      status: inquiries.status,
      createdAt: inquiries.createdAt,
      updatedAt: inquiries.updatedAt,
    })
    .from(inquiries)
    .where(eq(inquiries.tenantId, ctx.tenantId))
    .orderBy(desc(inquiries.createdAt))
    .limit(Math.min(limit, 200));
}

export async function getInquiryForTenant(
  ctx: TenantContext,
  inquiryId: string,
) {
  const db = getDb();
  const [inquiry] = await db
    .select()
    .from(inquiries)
    .where(
      and(eq(inquiries.id, inquiryId), eq(inquiries.tenantId, ctx.tenantId)),
    )
    .limit(1);

  return inquiry ?? null;
}

export async function updateInquiryStatus(
  ctx: TenantContext,
  inquiryId: string,
  status: InquiryStatus,
) {
  const existing = await getInquiryForTenant(ctx, inquiryId);
  if (!existing) return null;

  const db = getDb();
  const [updated] = await db
    .update(inquiries)
    .set({ status, updatedAt: new Date() })
    .where(
      and(eq(inquiries.id, inquiryId), eq(inquiries.tenantId, ctx.tenantId)),
    )
    .returning({
      id: inquiries.id,
      status: inquiries.status,
      updatedAt: inquiries.updatedAt,
    });

  return updated ?? null;
}

export async function deleteInquiry(ctx: TenantContext, inquiryId: string) {
  const existing = await getInquiryForTenant(ctx, inquiryId);
  if (!existing) return null;

  const db = getDb();
  const [deleted] = await db
    .delete(inquiries)
    .where(
      and(eq(inquiries.id, inquiryId), eq(inquiries.tenantId, ctx.tenantId)),
    )
    .returning({ id: inquiries.id });

  return deleted ?? null;
}

export async function countTenantInquiries(
  ctx: TenantContext,
  status?: InquiryStatus,
) {
  const db = getDb();
  const [row] = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(inquiries)
    .where(
      status
        ? and(eq(inquiries.tenantId, ctx.tenantId), eq(inquiries.status, status))
        : eq(inquiries.tenantId, ctx.tenantId),
    );
  return row?.count ?? 0;
}