import { and, desc, eq, isNull, sql } from "drizzle-orm";
import { getDb } from "@/core/db/client";
import { projectMedia, projects } from "@/core/db/schema";
import { getCache, tenantCacheKey } from "@/core/cache/cache";
import { normalizeSlug } from "@/core/tenant/slug";
import type { TenantContext } from "@/core/tenant/types";
import type { createProjectSchema } from "@/core/validation/schemas";
import type { z } from "zod";

type CreateProjectInput = z.infer<typeof createProjectSchema>;
type UpdateProjectInput = Partial<CreateProjectInput> & {
  status?: "draft" | "published" | "archived";
  heroStorageKey?: string | null;
};

/**
 * All project queries are tenant-scoped via trusted TenantContext.
 * Never accept tenant_id from the client.
 */
export async function listPublishedProjects(ctx: TenantContext) {
  const cache = getCache();
  const key = tenantCacheKey(ctx.tenantId, "projects", "published");
  const cached = cache.get<Awaited<ReturnType<typeof fetchPublishedProjects>>>(
    key,
  );
  if (cached) return cached;

  const rows = await fetchPublishedProjects(ctx.tenantId);
  cache.set(key, rows, 60);
  return rows;
}

async function fetchPublishedProjects(tenantId: string) {
  const db = getDb();
  return db
    .select()
    .from(projects)
    .where(
      and(
        eq(projects.tenantId, tenantId),
        eq(projects.status, "published"),
        isNull(projects.deletedAt),
      ),
    )
    .orderBy(desc(projects.publishedAt));
}

export async function listPublishedFeatured3dProjects(ctx: TenantContext) {
  const db = getDb();
  return db
    .select()
    .from(projects)
    .where(
      and(
        eq(projects.tenantId, ctx.tenantId),
        eq(projects.status, "published"),
        eq(projects.isFeatured3d, true),
        isNull(projects.deletedAt),
      ),
    )
    .orderBy(desc(projects.publishedAt));
}

/** Dashboard: all non-deleted projects for the trusted tenant. */
export async function listDashboardProjects(ctx: TenantContext) {
  const db = getDb();
  return db
    .select()
    .from(projects)
    .where(
      and(eq(projects.tenantId, ctx.tenantId), isNull(projects.deletedAt)),
    )
    .orderBy(desc(projects.updatedAt));
}

export async function getPublishedProjectBySlug(
  ctx: TenantContext,
  slug: string,
) {
  const db = getDb();
  const [project] = await db
    .select()
    .from(projects)
    .where(
      and(
        eq(projects.tenantId, ctx.tenantId),
        eq(projects.slug, slug),
        eq(projects.status, "published"),
        isNull(projects.deletedAt),
      ),
    )
    .limit(1);

  return project ?? null;
}

export async function createProject(
  ctx: TenantContext,
  input: CreateProjectInput,
) {
  const db = getDb();
  const slug = normalizeSlug(input.slug || input.title);

  const [project] = await db
    .insert(projects)
    .values({
      tenantId: ctx.tenantId,
      title: input.title.trim(),
      slug,
      description: input.description,
      location: input.location,
      year: input.year,
      area: input.area,
      clientName: input.clientName,
      categoryId: input.categoryId,
      concept: input.concept,
      seoTitle: input.seoTitle?.trim() || null,
      seoDescription: input.seoDescription?.trim() || null,
      status: "draft",
    })
    .returning();

  getCache().invalidateTenant(ctx.tenantId, "projects");
  return project;
}

export async function updateProject(
  ctx: TenantContext,
  projectId: string,
  input: UpdateProjectInput,
) {
  const existing = await getProjectForTenant(ctx, projectId);
  if (!existing) return null;

  const db = getDb();
  const slug = input.slug
    ? normalizeSlug(input.slug)
    : input.title
      ? normalizeSlug(input.title)
      : undefined;

  const [updated] = await db
    .update(projects)
    .set({
      ...(input.title !== undefined ? { title: input.title.trim() } : {}),
      ...(slug ? { slug } : {}),
      ...(input.description !== undefined
        ? { description: input.description }
        : {}),
      ...(input.location !== undefined ? { location: input.location } : {}),
      ...(input.year !== undefined ? { year: input.year } : {}),
      ...(input.area !== undefined ? { area: input.area } : {}),
      ...(input.clientName !== undefined ? { clientName: input.clientName } : {}),
      ...(input.categoryId !== undefined ? { categoryId: input.categoryId } : {}),
      ...(input.concept !== undefined ? { concept: input.concept } : {}),
      ...(input.designProcess !== undefined
        ? { designProcess: input.designProcess }
        : {}),
      ...(input.materials !== undefined ? { materials: input.materials } : {}),
      ...(input.architectCredit !== undefined
        ? { architectCredit: input.architectCredit }
        : {}),
      ...(input.projectType !== undefined
        ? { projectType: input.projectType }
        : {}),
      ...(input.videoUrl !== undefined
        ? { videoUrl: input.videoUrl?.trim() || null }
        : {}),
      ...(input.isFeatured3d !== undefined
        ? { isFeatured3d: input.isFeatured3d }
        : {}),
      ...(input.seoTitle !== undefined
        ? { seoTitle: input.seoTitle?.trim() || null }
        : {}),
      ...(input.seoDescription !== undefined
        ? { seoDescription: input.seoDescription?.trim() || null }
        : {}),
      ...(input.heroStorageKey !== undefined
        ? { heroStorageKey: input.heroStorageKey }
        : {}),
      ...(input.status !== undefined
        ? {
            status: input.status,
            publishedAt:
              input.status === "published"
                ? (existing.publishedAt ?? new Date())
                : existing.publishedAt,
          }
        : {}),
      updatedAt: new Date(),
    })
    .where(
      and(eq(projects.id, projectId), eq(projects.tenantId, ctx.tenantId)),
    )
    .returning();

  getCache().invalidateTenant(ctx.tenantId, "projects");
  return updated ?? null;
}

export async function publishProject(ctx: TenantContext, projectId: string) {
  return updateProject(ctx, projectId, { status: "published" });
}

export async function archiveProject(ctx: TenantContext, projectId: string) {
  return updateProject(ctx, projectId, { status: "archived" });
}

/** Soft delete — retains row for restore/audit; media cleanup is async later. */
export async function softDeleteProject(ctx: TenantContext, projectId: string) {
  const existing = await getProjectForTenant(ctx, projectId);
  if (!existing) return null;

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

  getCache().invalidateTenant(ctx.tenantId, "projects");
  return updated ?? null;
}

/**
 * Attempt to load a project by ID within tenant scope.
 * Cross-tenant IDs return null (never another tenant's data).
 */
export async function getProjectForTenant(
  ctx: TenantContext,
  projectId: string,
) {
  const db = getDb();
  const [project] = await db
    .select()
    .from(projects)
    .where(
      and(
        eq(projects.id, projectId),
        eq(projects.tenantId, ctx.tenantId),
        isNull(projects.deletedAt),
      ),
    )
    .limit(1);

  return project ?? null;
}

export async function setProjectHeroImage(
  ctx: TenantContext,
  projectId: string,
  mediaId: string,
) {
  const project = await getProjectForTenant(ctx, projectId);
  if (!project) return null;

  const db = getDb();
  const [media] = await db
    .select()
    .from(projectMedia)
    .where(
      and(
        eq(projectMedia.id, mediaId),
        eq(projectMedia.tenantId, ctx.tenantId),
        eq(projectMedia.projectId, projectId),
        eq(projectMedia.fileType, "image"),
      ),
    )
    .limit(1);

  if (!media) return null;

  return updateProject(ctx, projectId, { heroStorageKey: media.storageKey });
}

export async function listProjectMedia(ctx: TenantContext, projectId: string) {
  const project = await getProjectForTenant(ctx, projectId);
  if (!project) return [];

  const db = getDb();
  return db
    .select()
    .from(projectMedia)
    .where(
      and(
        eq(projectMedia.tenantId, ctx.tenantId),
        eq(projectMedia.projectId, projectId),
      ),
    )
    .orderBy(projectMedia.sortOrder);
}

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