import "server-only";
import { and, asc, desc, eq, ilike, or, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import {
  atlasConditions,
  atlasRegions,
  type AtlasConditionRow,
  type AtlasRegionRow,
} from "@/lib/db/schema";
import {
  regions as staticRegions,
  type AtlasRegion,
} from "@/data/atlas/regions";
import {
  conditions as staticConditions,
  type AtlasCondition,
} from "@/data/atlas/conditions";

/** Map a DB region row into the public-facing AtlasRegion shape. */
export function rowToPublicRegion(row: AtlasRegionRow): AtlasRegion {
  return {
    slug: row.slug,
    name: row.name,
    summary: row.summary,
    icon: row.icon ?? undefined,
    photoUrl: row.photoUrl ?? undefined,
    photoAlt: row.photoAlt ?? undefined,
  };
}

/** Map a DB condition row into the public-facing AtlasCondition shape. */
export function rowToPublicCondition(row: AtlasConditionRow): AtlasCondition {
  return {
    slug: row.slug,
    name: row.name,
    regionSlug: row.regionSlug,
    category: row.category,
    summary: row.summary,
    sections: row.sections,
    references: row.referencesList.length > 0 ? row.referencesList : undefined,
  };
}

export async function listPublishedRegions(): Promise<AtlasRegion[]> {
  try {
    const rows = await db
      .select()
      .from(atlasRegions)
      .where(eq(atlasRegions.status, "published"))
      .orderBy(asc(atlasRegions.position), asc(atlasRegions.name));
    if (rows.length === 0) return staticRegions;
    return rows.map(rowToPublicRegion);
  } catch {
    return staticRegions;
  }
}

export async function getPublishedRegionBySlug(
  slug: string,
): Promise<AtlasRegion | null> {
  try {
    const [row] = await db
      .select()
      .from(atlasRegions)
      .where(and(eq(atlasRegions.slug, slug), eq(atlasRegions.status, "published")))
      .limit(1);
    if (row) return rowToPublicRegion(row);
  } catch {
    // fall through
  }
  return staticRegions.find((r) => r.slug === slug) ?? null;
}

export async function listPublishedConditions(): Promise<AtlasCondition[]> {
  try {
    const rows = await db
      .select()
      .from(atlasConditions)
      .where(eq(atlasConditions.status, "published"))
      .orderBy(asc(atlasConditions.position), asc(atlasConditions.name));
    if (rows.length === 0) return staticConditions;
    return rows.map(rowToPublicCondition);
  } catch {
    return staticConditions;
  }
}

export async function getPublishedConditionBySlug(
  slug: string,
): Promise<AtlasCondition | null> {
  try {
    const [row] = await db
      .select()
      .from(atlasConditions)
      .where(and(eq(atlasConditions.slug, slug), eq(atlasConditions.status, "published")))
      .limit(1);
    if (row) return rowToPublicCondition(row);
  } catch {
    // fall through
  }
  return staticConditions.find((c) => c.slug === slug) ?? null;
}

// -------------------------------------------------------------------------
// Admin queries — use the raw DB row shape (full metadata).
// -------------------------------------------------------------------------

export async function listRegionsForAdmin(): Promise<AtlasRegionRow[]> {
  return db
    .select()
    .from(atlasRegions)
    .orderBy(asc(atlasRegions.position), asc(atlasRegions.name));
}

export async function getRegionForAdmin(id: string): Promise<AtlasRegionRow | null> {
  const [row] = await db
    .select()
    .from(atlasRegions)
    .where(eq(atlasRegions.id, id))
    .limit(1);
  return row ?? null;
}

export async function listConditionsForAdmin({
  q,
  regionSlug,
  category,
  status,
}: {
  q?: string;
  regionSlug?: string;
  category?: string;
  status?: "draft" | "published" | "archived" | "all";
} = {}): Promise<AtlasConditionRow[]> {
  const conditionsList = [];
  if (status && status !== "all") {
    conditionsList.push(eq(atlasConditions.status, status));
  }
  if (regionSlug && regionSlug.length > 0) {
    conditionsList.push(eq(atlasConditions.regionSlug, regionSlug));
  }
  if (category && category.length > 0) {
    conditionsList.push(eq(atlasConditions.category, category as AtlasConditionRow["category"]));
  }
  if (q && q.trim().length > 0) {
    const pattern = `%${q.trim()}%`;
    conditionsList.push(
      or(
        ilike(atlasConditions.name, pattern),
        ilike(atlasConditions.summary, pattern),
      )!,
    );
  }
  return db
    .select()
    .from(atlasConditions)
    .where(conditionsList.length > 0 ? and(...conditionsList) : undefined)
    .orderBy(desc(atlasConditions.updatedAt));
}

export async function getConditionForAdmin(id: string): Promise<AtlasConditionRow | null> {
  const [row] = await db
    .select()
    .from(atlasConditions)
    .where(eq(atlasConditions.id, id))
    .limit(1);
  return row ?? null;
}

export async function getAtlasKpis() {
  const [regionCount, conditionCount, publishedConditions, categories] =
    await Promise.all([
      db.select({ count: sql<number>`count(*)::int` }).from(atlasRegions),
      db.select({ count: sql<number>`count(*)::int` }).from(atlasConditions),
      db
        .select({ count: sql<number>`count(*)::int` })
        .from(atlasConditions)
        .where(eq(atlasConditions.status, "published")),
      db
        .select({ count: sql<number>`count(distinct ${atlasConditions.category})::int` })
        .from(atlasConditions),
    ]);
  return {
    regions: Number(regionCount[0]?.count ?? 0),
    conditions: Number(conditionCount[0]?.count ?? 0),
    publishedConditions: Number(publishedConditions[0]?.count ?? 0),
    categories: Number(categories[0]?.count ?? 0),
  };
}
