// src/lib/sites/queries.ts — server-side reads for sites.

import { and, asc, eq } from "drizzle-orm";
import { cache } from "react";
import { db } from "@/lib/db/client";
import { sites as sitesTable } from "@/lib/db/domain-schema";
import type { Site } from "@/lib/keywords/types";
import type { SiteDetail } from "./types";

/** Sites belonging to an org (subtitle uses domain when set). Deduped per request. */
export const getSitesForOrg = cache(async (orgId: string): Promise<Site[]> => {
  const rows = await db
    .select()
    .from(sitesTable)
    .where(eq(sitesTable.orgId, orgId))
    .orderBy(asc(sitesTable.name));
  return rows.map((r) => ({
    id: r.id,
    name: r.name,
    plan: r.url ?? "",
    primaryLanguage: r.primaryLanguage,
    languages: r.languages,
    targetCountries: r.targetCountries,
  }));
});

type SiteRow = typeof sitesTable.$inferSelect;

function toSiteDetail(r: SiteRow): SiteDetail {
  return {
    id: r.id,
    name: r.name,
    url: r.url ?? "",
    primaryLanguage: r.primaryLanguage,
    languages: r.languages,
    targetCountries: r.targetCountries,
    createdAt: r.createdAt,
  };
}

/** Full project rows for the management list, sorted by name. */
export async function getProjectsForOrg(orgId: string): Promise<SiteDetail[]> {
  const rows = await db
    .select()
    .from(sitesTable)
    .where(eq(sitesTable.orgId, orgId))
    .orderBy(asc(sitesTable.name));
  return rows.map(toSiteDetail);
}

/** A single project, org-scoped. Returns null when id is absent or foreign. */
export async function getSiteById(
  id: string,
  orgId: string,
): Promise<SiteDetail | null> {
  const [row] = await db
    .select()
    .from(sitesTable)
    .where(and(eq(sitesTable.id, id), eq(sitesTable.orgId, orgId)))
    .limit(1);
  return row ? toSiteDetail(row) : null;
}