import "server-only";
import { and, asc, desc, eq, ilike, inArray, or, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import {
  researchStudiesTable,
  researchSites,
  researchInvestigators,
  researchDocuments,
  researchPublications,
  researchUpdates,
  researchApplications,
  researchProposals,
  type ResearchPublication,
  type ResearchUpdate,
} from "@/lib/db/schema";

export async function listPublishedStudies() {
  return db
    .select()
    .from(researchStudiesTable)
    .where(eq(researchStudiesTable.status, "published"))
    .orderBy(desc(researchStudiesTable.publishedAt));
}

export async function listPublishedStudiesFiltered(filters: {
  query?: string;
  phase?: string;
  specialty?: string;
  recruitingOnly?: boolean;
  sort?: "newest" | "enrolment" | "title";
}) {
  const rows = await listPublishedStudies();
  const q = (filters.query ?? "").trim().toLowerCase();
  let filtered = rows;
  if (q) {
    filtered = filtered.filter((r) =>
      [
        r.title,
        r.conditionKeyword,
        r.sponsor,
        r.principalInvestigator,
        r.background,
        r.specialty,
        r.themes.join(" "),
      ]
        .join(" ")
        .toLowerCase()
        .includes(q),
    );
  }
  if (filters.phase) filtered = filtered.filter((r) => r.phase === filters.phase);
  if (filters.specialty)
    filtered = filtered.filter(
      (r) => r.specialty.toLowerCase() === filters.specialty!.toLowerCase(),
    );
  if (filters.recruitingOnly) {
    filtered = filtered.filter(
      (r) => r.phase === "Recruiting" || r.isRecruitingCollaborators,
    );
  }
  if (filters.sort === "enrolment") {
    filtered = [...filtered].sort((a, b) => b.currentEnrolment - a.currentEnrolment);
  } else if (filters.sort === "title") {
    filtered = [...filtered].sort((a, b) => a.title.localeCompare(b.title));
  }
  return filtered;
}

export async function getPublishedStudyBySlug(slug: string) {
  const [study] = await db
    .select()
    .from(researchStudiesTable)
    .where(
      and(
        eq(researchStudiesTable.slug, slug),
        eq(researchStudiesTable.status, "published"),
      ),
    )
    .limit(1);
  if (!study) return null;
  return loadStudyRelations(study);
}

export async function listStudiesForAdmin() {
  return db
    .select()
    .from(researchStudiesTable)
    .orderBy(desc(researchStudiesTable.createdAt));
}

export async function getStudyForAdmin(id: string) {
  const [study] = await db
    .select()
    .from(researchStudiesTable)
    .where(eq(researchStudiesTable.id, id))
    .limit(1);
  if (!study) return null;
  return loadStudyRelations(study);
}

async function loadStudyRelations(study: typeof researchStudiesTable.$inferSelect) {
  const [sites, investigators, documents, publications, updates] =
    await Promise.all([
      db
        .select()
        .from(researchSites)
        .where(eq(researchSites.studyId, study.id))
        .orderBy(asc(researchSites.position)),
      db
        .select()
        .from(researchInvestigators)
        .where(eq(researchInvestigators.studyId, study.id))
        .orderBy(asc(researchInvestigators.position)),
      db
        .select()
        .from(researchDocuments)
        .where(eq(researchDocuments.studyId, study.id))
        .orderBy(asc(researchDocuments.position)),
      db
        .select()
        .from(researchPublications)
        .where(eq(researchPublications.studyId, study.id))
        .orderBy(desc(researchPublications.year), asc(researchPublications.position)),
      db
        .select()
        .from(researchUpdates)
        .where(eq(researchUpdates.studyId, study.id))
        .orderBy(desc(researchUpdates.postedAt))
        .limit(20),
    ]);
  return { study, sites, investigators, documents, publications, updates };
}

// -------------------------------------------------------------------------
// Portfolio dashboard on /products/research
// -------------------------------------------------------------------------

export async function getResearchPortfolioStats() {
  const [
    [studyRow],
    [pubRow],
    [recruitRow],
    [countryRow],
    [siteRow],
    [investigatorRow],
  ] = await Promise.all([
    db
      .select({ n: sql<number>`count(*)`.mapWith(Number) })
      .from(researchStudiesTable)
      .where(eq(researchStudiesTable.status, "published")),
    db
      .select({ n: sql<number>`count(*)`.mapWith(Number) })
      .from(researchPublications),
    db
      .select({ n: sql<number>`count(*)`.mapWith(Number) })
      .from(researchStudiesTable)
      .where(
        and(
          eq(researchStudiesTable.status, "published"),
          or(
            eq(researchStudiesTable.phase, "Recruiting"),
            eq(researchStudiesTable.isRecruitingCollaborators, true),
          ),
        ),
      ),
    db
      .select({
        n: sql<number>`count(distinct country)`.mapWith(Number),
      })
      .from(researchSites),
    db
      .select({ n: sql<number>`count(*)`.mapWith(Number) })
      .from(researchSites),
    db
      .select({
        n: sql<number>`count(distinct lower(name))`.mapWith(Number),
      })
      .from(researchInvestigators),
  ]);
  return {
    studies: studyRow?.n ?? 0,
    publications: pubRow?.n ?? 0,
    recruiting: recruitRow?.n ?? 0,
    countries: countryRow?.n ?? 0,
    sites: siteRow?.n ?? 0,
    investigators: investigatorRow?.n ?? 0,
  };
}

export async function listFeaturedStudies() {
  return db
    .select()
    .from(researchStudiesTable)
    .where(
      and(
        eq(researchStudiesTable.status, "published"),
        eq(researchStudiesTable.featured, true),
      ),
    )
    .orderBy(desc(researchStudiesTable.publishedAt))
    .limit(6);
}

export async function listAllSpecialties(): Promise<string[]> {
  const rows = await db
    .select({ specialty: researchStudiesTable.specialty })
    .from(researchStudiesTable)
    .where(eq(researchStudiesTable.status, "published"));
  const set = new Set<string>();
  for (const r of rows) {
    if (r.specialty && r.specialty.trim()) set.add(r.specialty.trim());
  }
  return [...set].sort((a, b) => a.localeCompare(b));
}

// -------------------------------------------------------------------------
// Investigators directory
// -------------------------------------------------------------------------

export type InvestigatorSummary = {
  name: string;
  institution: string;
  role: string;
  /** How many published studies this person appears in. */
  studyCount: number;
  /** Up to 3 study slug+title pairs for the profile card. */
  studies: Array<{ slug: string; title: string }>;
};

export async function listInvestigatorsDirectory(
  query: string = "",
): Promise<InvestigatorSummary[]> {
  const rows = await db
    .select({
      name: researchInvestigators.name,
      institution: researchInvestigators.institution,
      role: researchInvestigators.role,
      studySlug: researchStudiesTable.slug,
      studyTitle: researchStudiesTable.title,
    })
    .from(researchInvestigators)
    .innerJoin(
      researchStudiesTable,
      eq(researchStudiesTable.id, researchInvestigators.studyId),
    )
    .where(eq(researchStudiesTable.status, "published"));

  const byKey = new Map<string, InvestigatorSummary>();
  for (const r of rows) {
    const key = r.name.trim().toLowerCase() + "::" + r.institution.trim().toLowerCase();
    const cur = byKey.get(key) ?? {
      name: r.name,
      institution: r.institution,
      role: r.role,
      studyCount: 0,
      studies: [],
    };
    cur.studyCount += 1;
    if (cur.studies.length < 3)
      cur.studies.push({ slug: r.studySlug, title: r.studyTitle });
    byKey.set(key, cur);
  }
  let out = [...byKey.values()];
  const q = query.trim().toLowerCase();
  if (q) {
    out = out.filter((i) =>
      [i.name, i.institution, i.role].join(" ").toLowerCase().includes(q),
    );
  }
  out.sort((a, b) => b.studyCount - a.studyCount || a.name.localeCompare(b.name));
  return out;
}

// -------------------------------------------------------------------------
// Publications
// -------------------------------------------------------------------------

export async function listAllPublications(): Promise<
  Array<ResearchPublication & { studySlug: string; studyTitle: string }>
> {
  const rows = await db
    .select({
      pub: researchPublications,
      studySlug: researchStudiesTable.slug,
      studyTitle: researchStudiesTable.title,
    })
    .from(researchPublications)
    .innerJoin(
      researchStudiesTable,
      eq(researchStudiesTable.id, researchPublications.studyId),
    )
    .where(eq(researchStudiesTable.status, "published"))
    .orderBy(desc(researchPublications.year));
  return rows.map((r) => ({ ...r.pub, studySlug: r.studySlug, studyTitle: r.studyTitle }));
}

// -------------------------------------------------------------------------
// Applications & proposals (admin review queues)
// -------------------------------------------------------------------------

export async function listApplicationsForAdmin(statusFilter?: string) {
  const rows = await db
    .select({
      app: researchApplications,
      studySlug: researchStudiesTable.slug,
      studyTitle: researchStudiesTable.title,
    })
    .from(researchApplications)
    .leftJoin(
      researchStudiesTable,
      eq(researchStudiesTable.id, researchApplications.studyId),
    )
    .orderBy(desc(researchApplications.createdAt))
    .limit(500);
  if (!statusFilter || statusFilter === "all") return rows;
  return rows.filter((r) => r.app.status === statusFilter);
}

export async function getApplicationForAdmin(id: string) {
  const [row] = await db
    .select({
      app: researchApplications,
      studySlug: researchStudiesTable.slug,
      studyTitle: researchStudiesTable.title,
    })
    .from(researchApplications)
    .leftJoin(
      researchStudiesTable,
      eq(researchStudiesTable.id, researchApplications.studyId),
    )
    .where(eq(researchApplications.id, id))
    .limit(1);
  return row ?? null;
}

export async function listProposalsForAdmin(statusFilter?: string) {
  const rows = await db
    .select()
    .from(researchProposals)
    .orderBy(desc(researchProposals.createdAt))
    .limit(500);
  if (!statusFilter || statusFilter === "all") return rows;
  return rows.filter((r) => r.status === statusFilter);
}

export async function getProposalForAdmin(id: string) {
  const [row] = await db
    .select()
    .from(researchProposals)
    .where(eq(researchProposals.id, id))
    .limit(1);
  return row ?? null;
}

export async function countPendingApplicationsAndProposals() {
  const [[appRow], [propRow]] = await Promise.all([
    db
      .select({ n: sql<number>`count(*)`.mapWith(Number) })
      .from(researchApplications)
      .where(eq(researchApplications.status, "pending")),
    db
      .select({ n: sql<number>`count(*)`.mapWith(Number) })
      .from(researchProposals)
      .where(eq(researchProposals.status, "pending")),
  ]);
  return {
    applications: appRow?.n ?? 0,
    proposals: propRow?.n ?? 0,
  };
}

export type ListedPublicationsByStudy = Record<
  string,
  ResearchPublication[]
>;

export type UpdatesByStudy = Record<string, ResearchUpdate[]>;

/** Used by admin list / study-card views to show quick counts. */
export async function studyChildCounts(studyIds: string[]) {
  if (studyIds.length === 0) return { publications: {}, updates: {} };
  const [pubs, ups] = await Promise.all([
    db
      .select({
        studyId: researchPublications.studyId,
        n: sql<number>`count(*)`.mapWith(Number),
      })
      .from(researchPublications)
      .where(inArray(researchPublications.studyId, studyIds))
      .groupBy(researchPublications.studyId),
    db
      .select({
        studyId: researchUpdates.studyId,
        n: sql<number>`count(*)`.mapWith(Number),
      })
      .from(researchUpdates)
      .where(inArray(researchUpdates.studyId, studyIds))
      .groupBy(researchUpdates.studyId),
  ]);
  const publications: Record<string, number> = {};
  const updates: Record<string, number> = {};
  for (const p of pubs) publications[p.studyId] = p.n;
  for (const u of ups) updates[u.studyId] = u.n;
  return { publications, updates };
}

void ilike;
