// src/lib/content/queries.ts — read content items joined with their keyword.
import "server-only";
import { and, eq } from "drizzle-orm";
import { db } from "@/lib/db/client";
import { contentItems, keywords } from "@/lib/db/domain-schema";
import { buildContentAnalysis } from "./analysis";
import type { OnlineRow } from "./online";
import type { ContentItem } from "./types";

export async function getContentItemsForSite(
  siteId: string,
): Promise<ContentItem[]> {
  const rows = await db
    .select({
      id: contentItems.id,
      siteId: contentItems.siteId,
      title: contentItems.title,
      status: contentItems.status,
      primaryKeywordId: contentItems.primaryKeywordId,
      lang: contentItems.lang,
      country: contentItems.country,
      assignee: contentItems.assignee,
      dueDate: contentItems.dueDate,
      url: contentItems.url,
      kind: contentItems.kind,
      warnedPageUrl: contentItems.warnedPageUrl,
      createdAt: contentItems.createdAt,
      keywordTerm: keywords.term,
      keywordCountry: keywords.country,
      keywordBriefs: keywords.briefs,
      serp: keywords.serp,
    })
    .from(contentItems)
    .leftJoin(
      keywords,
      and(
        eq(keywords.id, contentItems.primaryKeywordId),
        eq(keywords.siteId, contentItems.siteId),
      ),
    )
    .where(eq(contentItems.siteId, siteId));

  return rows.map((r) => ({
    id: r.id,
    siteId: r.siteId,
    title: r.title,
    status: r.status,
    primaryKeywordId: r.primaryKeywordId,
    keywordTerm: r.keywordTerm ?? null,
    keywordCountry: r.keywordCountry ?? null,
    hasBrief: r.keywordBriefs ? Object.keys(r.keywordBriefs).length > 0 : false,
    lang: r.lang,
    country: r.country,
    assignee: r.assignee,
    dueDate: r.dueDate ? r.dueDate.toISOString() : null,
    url: r.url,
    kind: r.kind ?? "new",
    warnedPageUrl: r.warnedPageUrl ?? null,
    createdAt: r.createdAt.toISOString(),
    analysis: buildContentAnalysis(
      r.serp ?? null,
      r.keywordBriefs ?? null,
      r.keywordCountry ?? null,
    ),
  }));
}

export async function getPublishedContentForSite(
  siteId: string,
): Promise<OnlineRow[]> {
  const rows = await db
    .select({
      id: contentItems.id,
      title: contentItems.title,
      url: contentItems.url,
      keywordTerm: keywords.term,
      keywordCountry: keywords.country,
      gsc: keywords.gsc,
    })
    .from(contentItems)
    .leftJoin(
      keywords,
      and(
        eq(keywords.id, contentItems.primaryKeywordId),
        eq(keywords.siteId, contentItems.siteId),
      ),
    )
    .where(
      and(
        eq(contentItems.siteId, siteId),
        eq(contentItems.status, "published" as const),
      ),
    );

  return rows.map((r) => ({
    id: r.id,
    title: r.title,
    url: r.url,
    keywordTerm: r.keywordTerm ?? null,
    keywordCountry: r.keywordCountry ?? null,
    position: r.gsc ? r.gsc.position : null,
    clicks: r.gsc ? r.gsc.clicks : 0,
    impressions: r.gsc ? r.gsc.impressions : 0,
    ctr: r.gsc ? r.gsc.ctr : null,
    gscPage: r.gsc?.page ?? null,
  }));
}