import type { SupabaseClient } from "@supabase/supabase-js";
import { revalidateTag, unstable_cache } from "next/cache";
import { ScenarioDefinitionSchema } from "@/lib/schemas";
import {
  formatScenarioDurationLabel,
  getScenarioTensionColor,
} from "@/lib/scenario-utils";
import { createAdminClient } from "@/lib/supabase/admin";
import type {
  AdminDashboardData,
  CatalogStatus,
  ClientRecord,
  CompanyRecord,
  CompanyMembership,
  EffectiveAccessRecord,
  PlatformAdminRecord,
  ScenarioCatalogItem,
  ScenarioDefinition,
  ScenarioGroupMemberRecord,
  ScenarioGroupRecord,
  ScenarioMutationPayload,
  ScenarioTemplateRecord,
  TagRecord,
} from "@/lib/types";

type ScenarioTemplateRow = {
  id: string;
  slug: string;
  status: CatalogStatus;
  current_published_version_id: string | null;
  current_draft_version_id: string | null;
};

type ScenarioVersionRow = {
  id: string;
  template_id: string;
  version_number: number;
  status: CatalogStatus;
  payload: unknown;
  title: string;
  target_skill: string;
  difficulty: ScenarioDefinition["difficulty"];
  duration_label: string;
  short_brief: string;
  published_at: string | null;
};

type TagRow = {
  id: string;
  label: string;
  slug: string;
  status: TagRecord["status"];
};

type ScenarioTagLinkRow = {
  scenario_template_id: string;
  tag: TagRow | TagRow[] | null;
};

type GroupRow = {
  id: string;
  name: string;
  slug: string;
  description: string | null;
  status: CatalogStatus;
  company_id: string | null;
  access_starts_at: string | null;
  access_ends_at: string | null;
  company:
    | {
        id: string;
        name: string;
        slug: string;
      }
    | {
        id: string;
        name: string;
        slug: string;
      }[]
    | null;
};

type GroupTagLinkRow = {
  group_id: string;
  tag: TagRow | TagRow[] | null;
};

type GroupScenarioLinkRow = {
  group_id: string;
  scenario_template_id: string;
};

type GroupMemberLinkRow = {
  group_id: string;
  member:
    | {
        id: string;
        email: string;
        full_name: string | null;
        role: CompanyMembership["role"];
        status: CompanyMembership["status"];
        magic_link_last_sent_at: string | null;
      }
    | {
        id: string;
        email: string;
        full_name: string | null;
        role: CompanyMembership["role"];
        status: CompanyMembership["status"];
        magic_link_last_sent_at: string | null;
      }[]
    | null;
};

type ClientRow = {
  id: string;
  name: string;
  slug: string;
  status: ClientRecord["status"];
  legacy_company_id: string | null;
};

type CompanyRow = {
  id: string;
  name: string;
  slug: string;
};

type DirectScenarioAccessRow = {
  client_id: string;
  scenario_template_id: string;
};

type GroupAccessRow = {
  client_id: string;
  group_id: string;
  group?: {
    status: CatalogStatus;
  } | {
    status: CatalogStatus;
  }[] | null;
};

type PlatformAdminRow = {
  id: string;
  auth_user_id: string | null;
  email: string;
  full_name: string | null;
  status: PlatformAdminRecord["status"];
};

type GroupAccessShape = {
  id: string;
  status: CatalogStatus;
  company_id: string | null;
  access_starts_at: string | null;
  access_ends_at: string | null;
};

type ScenarioGroupMemberAccessRow = {
  group_id: string;
  group: GroupAccessShape | GroupAccessShape[] | null;
};

const ADMIN_CONSOLE_CACHE_TAG = "admin-console";
const PLATFORM_ADMINS_CACHE_TAG = "platform-admins";

function getSingleItem<T>(value: T | T[] | null) {
  return Array.isArray(value) ? value[0] ?? null : value;
}

export function buildScenarioPayload(
  payload: Omit<ScenarioMutationPayload, "tagIds"> & { slug: string },
): ScenarioDefinition {
  return ScenarioDefinitionSchema.parse({
    id: payload.slug,
    title: payload.title,
    targetSkill: payload.targetSkill,
    difficulty: payload.difficulty,
    durationLabel: formatScenarioDurationLabel(payload.maxTurns),
    shortBrief: payload.shortBrief,
    employeeProfile: payload.employeeProfile,
    openingMessage: payload.openingMessage,
    managerGoal: payload.managerGoal,
    successSignals: payload.successSignals,
    failureSignals: payload.failureSignals,
    completionRules: payload.completionRules,
    metricWeights: payload.metricWeights,
    maxTurns: payload.maxTurns,
    tensionColor: getScenarioTensionColor(payload.difficulty),
  });
}

function mapTag(
  row: TagRow,
  usage?: {
    scenarioCount?: number;
    groupCount?: number;
  },
): TagRecord {
  return {
    id: row.id,
    label: row.label,
    slug: row.slug,
    status: row.status,
    scenarioCount: usage?.scenarioCount ?? 0,
    groupCount: usage?.groupCount ?? 0,
  };
}

function mapClient(row: ClientRow): ClientRecord {
  return {
    id: row.id,
    name: row.name,
    slug: row.slug,
    status: row.status,
    legacyCompanyId: row.legacy_company_id,
  };
}

function mapCompany(row: CompanyRow, clientsByCompanyId: Map<string, string>): CompanyRecord {
  return {
    id: row.id,
    name: row.name,
    slug: row.slug,
    clientId: clientsByCompanyId.get(row.id) ?? null,
  };
}

function mapGroupMember(
  row: GroupMemberLinkRow["member"],
) {
  const member = getSingleItem(row);

  if (!member) {
    return null;
  }

  return {
    companyMemberId: member.id,
    email: member.email,
    fullName: member.full_name,
    role: member.role,
    status: member.status,
    magicLinkLastSentAt: member.magic_link_last_sent_at,
  } satisfies ScenarioGroupMemberRecord;
}

function sortGroupMembers(members: ScenarioGroupMemberRecord[]) {
  return [...members].sort((left, right) => left.email.localeCompare(right.email));
}

function mapScenarioItem(
  template: ScenarioTemplateRow,
  version: ScenarioVersionRow,
  tags: TagRecord[],
): ScenarioCatalogItem {
  const payload = ScenarioDefinitionSchema.parse(version.payload);

  return {
    ...payload,
    id: template.slug,
    durationLabel: formatScenarioDurationLabel(payload.maxTurns),
    templateId: template.id,
    tensionColor: getScenarioTensionColor(payload.difficulty),
    versionId: version.id,
    versionNumber: version.version_number,
    status: version.status,
    tags,
  };
}

const getCachedAdminDashboardDataInternal = unstable_cache(
  async () => {
    const admin = createAdminClient() as unknown as SupabaseClient;
    return getAdminDashboardData(admin);
  },
  ["admin-dashboard"],
  {
    revalidate: 60,
    tags: [ADMIN_CONSOLE_CACHE_TAG],
  },
);

const getCachedPlatformAdminsInternal = unstable_cache(
  async () => {
    const admin = createAdminClient() as unknown as SupabaseClient;
    return getPlatformAdmins(admin);
  },
  ["platform-admins"],
  {
    revalidate: 60,
    tags: [PLATFORM_ADMINS_CACHE_TAG],
  },
);

function sortTags(tags: TagRecord[]) {
  return [...tags].sort((left, right) => left.label.localeCompare(right.label));
}

export async function isPlatformAdmin(
  admin: SupabaseClient,
  userId: string,
) {
  const { data, error } = await admin
    .from("platform_admins")
    .select("id")
    .eq("auth_user_id", userId)
    .eq("status", "active")
    .maybeSingle();

  if (error) {
    throw error;
  }

  return Boolean(data);
}

export async function ensureDevPlatformAdmin(
  admin: SupabaseClient,
  user: { id: string; email: string; fullName: string },
) {
  const { data: existing, error: existingError } = await admin
    .from("platform_admins")
    .select("id, auth_user_id")
    .eq("email", user.email)
    .maybeSingle();

  if (existingError) {
    throw existingError;
  }

  if (existing) {
    if (existing.auth_user_id === user.id) {
      return existing.id as string;
    }

    const { error: updateError } = await admin
      .from("platform_admins")
      .update({
        auth_user_id: user.id,
        full_name: user.fullName,
        status: "active",
      })
      .eq("id", existing.id);

    if (updateError) {
      throw updateError;
    }

    return existing.id as string;
  }

  const { data: created, error: createError } = await admin
    .from("platform_admins")
    .insert({
      auth_user_id: user.id,
      email: user.email,
      full_name: user.fullName,
      status: "active",
    })
    .select("id")
    .single();

  if (createError) {
    throw createError;
  }

  return created.id as string;
}

function isGroupAccessOpen(group: GroupAccessShape, now = Date.now()) {
  if (group.status !== "published" || !group.company_id) {
    return false;
  }

  if (group.access_starts_at) {
    const startsAt = new Date(group.access_starts_at).getTime();
    if (Number.isFinite(startsAt) && now < startsAt) {
      return false;
    }
  }

  if (group.access_ends_at) {
    const endsAt = new Date(group.access_ends_at).getTime();
    if (Number.isFinite(endsAt) && now > endsAt) {
      return false;
    }
  }

  return true;
}

function isMissingGroupSchemaError(error: unknown) {
  if (!error || typeof error !== "object") {
    return false;
  }

  const code =
    "code" in error && typeof error.code === "string" ? error.code : null;
  const message =
    "message" in error && typeof error.message === "string"
      ? error.message
      : "";

  if (code === "PGRST205" || code === "PGRST200") {
    return true;
  }

  return (
    message.includes("scenario_group_members") ||
    message.includes("scenario_groups") ||
    message.includes("companies") ||
    message.includes("company_members") ||
    message.includes("access_starts_at") ||
    message.includes("access_ends_at") ||
    message.includes("company_id")
  );
}

async function getLegacyPublishedAssignedScenarios(
  admin: SupabaseClient,
  clientId: string,
) {
  const [{ data: directRows }, { data: groupAccessRows }, { data: groupScenarioRows }] =
    await Promise.all([
      admin
        .from("client_scenario_access")
        .select("client_id, scenario_template_id")
        .eq("client_id", clientId),
      admin
        .from("client_group_access")
        .select("client_id, group_id, group:scenario_groups(status)")
        .eq("client_id", clientId),
      admin.from("scenario_group_scenarios").select("group_id, scenario_template_id"),
    ]);

  const templateIds = new Set<string>();

  for (const row of (directRows ?? []) as DirectScenarioAccessRow[]) {
    templateIds.add(row.scenario_template_id);
  }

  const activeGroupIds = new Set<string>();
  for (const row of (groupAccessRows ?? []) as GroupAccessRow[]) {
    const group = getSingleItem(
      row.group as { status: CatalogStatus } | { status: CatalogStatus }[] | null,
    );
    if (group?.status === "published") {
      activeGroupIds.add(row.group_id);
    }
  }

  for (const row of (groupScenarioRows ?? []) as GroupScenarioLinkRow[]) {
    if (activeGroupIds.has(row.group_id)) {
      templateIds.add(row.scenario_template_id);
    }
  }

  if (templateIds.size === 0) {
    return [] satisfies ScenarioCatalogItem[];
  }

  const { data: templateRows, error: templateError } = await admin
    .from("scenario_templates")
    .select("id, slug, status, current_published_version_id, current_draft_version_id")
    .in("id", [...templateIds])
    .eq("status", "published");

  if (templateError) {
    throw templateError;
  }

  const templates = (templateRows ?? []) as ScenarioTemplateRow[];
  if (templates.length === 0) {
    return [] satisfies ScenarioCatalogItem[];
  }

  const versionIds = templates
    .map((template) => template.current_published_version_id)
    .filter((value): value is string => Boolean(value));

  const [{ data: versionRows, error: versionError }, tagMap] = await Promise.all([
    admin
      .from("scenario_versions")
      .select(
        "id, template_id, version_number, status, payload, title, target_skill, difficulty, duration_label, short_brief, published_at",
      )
      .in("id", versionIds),
    getScenarioTagMap(admin, templates.map((template) => template.id)),
  ]);

  if (versionError) {
    throw versionError;
  }

  const versionsById = new Map(
    ((versionRows ?? []) as ScenarioVersionRow[]).map((version) => [version.id, version]),
  );

  return templates
    .map((template) => {
      const version = template.current_published_version_id
        ? versionsById.get(template.current_published_version_id)
        : null;

      if (!version) {
        return null;
      }

      return mapScenarioItem(template, version, tagMap.get(template.id) ?? []);
    })
    .filter((item): item is ScenarioCatalogItem => item !== null)
    .sort((left, right) => left.title.localeCompare(right.title));
}

export async function getPublishedAssignedScenarios(
  admin: SupabaseClient,
  membership: Pick<CompanyMembership, "id" | "companyId" | "clientId">,
) {
  try {
    const [{ data: groupMemberRows, error: groupMemberError }, { data: groupScenarioRows, error: groupScenarioError }] =
      await Promise.all([
        admin
          .from("scenario_group_members")
          .select(
            "group_id, group:scenario_groups(id, status, company_id, access_starts_at, access_ends_at)",
          )
          .eq("company_member_id", membership.id),
        admin.from("scenario_group_scenarios").select("group_id, scenario_template_id"),
      ]);

    if (groupMemberError) {
      throw groupMemberError;
    }

    if (groupScenarioError) {
      throw groupScenarioError;
    }

    const templateIds = new Set<string>();
    const activeGroupIds = new Set<string>();
    const now = Date.now();

    for (const row of (groupMemberRows ?? []) as ScenarioGroupMemberAccessRow[]) {
      const group = getSingleItem(row.group);

      if (
        group &&
        group.company_id === membership.companyId &&
        isGroupAccessOpen(group, now)
      ) {
        activeGroupIds.add(row.group_id);
      }
    }

    for (const row of (groupScenarioRows ?? []) as GroupScenarioLinkRow[]) {
      if (activeGroupIds.has(row.group_id)) {
        templateIds.add(row.scenario_template_id);
      }
    }

    if (templateIds.size === 0) {
      return [] satisfies ScenarioCatalogItem[];
    }

    const { data: templateRows, error: templateError } = await admin
      .from("scenario_templates")
      .select("id, slug, status, current_published_version_id, current_draft_version_id")
      .in("id", [...templateIds])
      .eq("status", "published");

    if (templateError) {
      throw templateError;
    }

    const templates = (templateRows ?? []) as ScenarioTemplateRow[];
    if (templates.length === 0) {
      return [] satisfies ScenarioCatalogItem[];
    }

    const versionIds = templates
      .map((template) => template.current_published_version_id)
      .filter((value): value is string => Boolean(value));

    const [{ data: versionRows, error: versionError }, tagMap] = await Promise.all([
      admin
        .from("scenario_versions")
        .select(
          "id, template_id, version_number, status, payload, title, target_skill, difficulty, duration_label, short_brief, published_at",
        )
        .in("id", versionIds),
      getScenarioTagMap(admin, templates.map((template) => template.id)),
    ]);

    if (versionError) {
      throw versionError;
    }

    const versionsById = new Map(
      ((versionRows ?? []) as ScenarioVersionRow[]).map((version) => [version.id, version]),
    );

    return templates
      .map((template) => {
        const version = template.current_published_version_id
          ? versionsById.get(template.current_published_version_id)
          : null;

        if (!version) {
          return null;
        }

        return mapScenarioItem(template, version, tagMap.get(template.id) ?? []);
      })
      .filter((item): item is ScenarioCatalogItem => item !== null)
      .sort((left, right) => left.title.localeCompare(right.title));
  } catch (error) {
    if (isMissingGroupSchemaError(error)) {
      return getLegacyPublishedAssignedScenarios(admin, membership.clientId);
    }

    throw error;
  }
}

export async function getAccessiblePublishedScenarioByRef(
  admin: SupabaseClient,
  membership: CompanyMembership,
  ref: string,
) {
  const scenarios = await getPublishedAssignedScenarios(admin, membership);

  return (
    scenarios.find(
      (scenario) => scenario.id === ref || scenario.templateId === ref,
    ) ?? null
  );
}

async function getScenarioTagMap(
  admin: SupabaseClient,
  templateIds: string[],
) {
  if (templateIds.length === 0) {
    return new Map<string, TagRecord[]>();
  }

  const { data: linkRows, error: linkError } = await admin
    .from("scenario_tags")
    .select("scenario_template_id, tag:tags(id, label, slug, status)")
    .in("scenario_template_id", templateIds);

  if (linkError) {
    throw linkError;
  }

  const tagMap = new Map<string, TagRecord[]>();

  for (const link of (linkRows ?? []) as ScenarioTagLinkRow[]) {
    const tag = getSingleItem(link.tag);
    if (!tag) {
      continue;
    }

    const tags = tagMap.get(link.scenario_template_id) ?? [];
    tags.push(mapTag(tag));
    tagMap.set(link.scenario_template_id, sortTags(tags));
  }

  return tagMap;
}

async function getLegacyAdminDashboardData(
  admin: SupabaseClient,
): Promise<AdminDashboardData> {
  const [
    { data: clientRows, error: clientError },
    { data: companyRows, error: companyError },
    { data: tagRows, error: tagError },
    { data: groupRows, error: groupError },
    { data: groupTagRows, error: groupTagError },
    { data: groupScenarioRows, error: groupScenarioError },
    { data: templateRows, error: templateError },
    { data: versionRows, error: versionError },
    { data: scenarioTagRows, error: scenarioTagError },
  ] = await Promise.all([
    admin
      .from("clients")
      .select("id, name, slug, status, legacy_company_id")
      .order("name"),
    admin.from("companies").select("id, name, slug").order("name"),
    admin.from("tags").select("id, label, slug, status").order("label"),
    admin
      .from("scenario_groups")
      .select("id, name, slug, description, status")
      .order("name"),
    admin.from("group_tags").select("group_id, tag:tags(id, label, slug, status)"),
    admin.from("scenario_group_scenarios").select("group_id, scenario_template_id"),
    admin
      .from("scenario_templates")
      .select("id, slug, status, current_published_version_id, current_draft_version_id")
      .order("slug"),
    admin
      .from("scenario_versions")
      .select(
        "id, template_id, version_number, status, payload, title, target_skill, difficulty, duration_label, short_brief, published_at",
      )
      .order("template_id")
      .order("version_number"),
    admin.from("scenario_tags").select("scenario_template_id, tag:tags(id, label, slug, status)"),
  ]);

  for (const error of [
    clientError,
    companyError,
    tagError,
    groupError,
    groupTagError,
    groupScenarioError,
    templateError,
    versionError,
    scenarioTagError,
  ]) {
    if (error) {
      throw error;
    }
  }

  const tags = ((tagRows ?? []) as TagRow[]).map((row) => mapTag(row));
  const tagsById = new Map(tags.map((tag) => [tag.id, tag]));

  const tagIdsByTemplate = new Map<string, string[]>();
  for (const row of (scenarioTagRows ?? []) as ScenarioTagLinkRow[]) {
    const tag = getSingleItem(row.tag);
    if (!tag) {
      continue;
    }

    const values = tagIdsByTemplate.get(row.scenario_template_id) ?? [];
    values.push(tag.id);
    tagIdsByTemplate.set(row.scenario_template_id, values);
  }

  const versionRowsByTemplate = new Map<string, ScenarioVersionRow[]>();
  for (const version of (versionRows ?? []) as ScenarioVersionRow[]) {
    const values = versionRowsByTemplate.get(version.template_id) ?? [];
    values.push(version);
    versionRowsByTemplate.set(version.template_id, values);
  }

  const scenarioTemplates = ((templateRows ?? []) as ScenarioTemplateRow[]).map(
    (template) => {
      const versions = versionRowsByTemplate.get(template.id) ?? [];
      const publishedVersion =
        versions.find(
          (version) => version.id === template.current_published_version_id,
        ) ?? null;
      const draftVersion =
        versions.find((version) => version.id === template.current_draft_version_id) ??
        null;
      const templateTags = sortTags(
        (tagIdsByTemplate.get(template.id) ?? [])
          .map((tagId) => tagsById.get(tagId))
          .filter((tag): tag is TagRecord => Boolean(tag)),
      );

      return {
        id: template.id,
        slug: template.slug,
        status: template.status,
        currentPublishedVersionId: template.current_published_version_id,
        currentDraftVersionId: template.current_draft_version_id,
        publishedVersionNumber: publishedVersion?.version_number ?? null,
        draftVersionNumber: draftVersion?.version_number ?? null,
        publishedScenario: publishedVersion
          ? mapScenarioItem(template, publishedVersion, templateTags)
          : null,
        draftScenario: draftVersion
          ? mapScenarioItem(template, draftVersion, templateTags)
          : null,
        tags: templateTags,
      } satisfies ScenarioTemplateRecord;
    },
  );

  const tagIdsByGroup = new Map<string, string[]>();
  for (const row of (groupTagRows ?? []) as GroupTagLinkRow[]) {
    const tag = getSingleItem(row.tag);
    if (!tag) {
      continue;
    }

    const values = tagIdsByGroup.get(row.group_id) ?? [];
    values.push(tag.id);
    tagIdsByGroup.set(row.group_id, values);
  }

  const scenarioIdsByGroup = new Map<string, string[]>();
  for (const row of (groupScenarioRows ?? []) as GroupScenarioLinkRow[]) {
    const values = scenarioIdsByGroup.get(row.group_id) ?? [];
    values.push(row.scenario_template_id);
    scenarioIdsByGroup.set(row.group_id, values);
  }

  const clients = ((clientRows ?? []) as ClientRow[]).map(mapClient);
  const clientsByCompanyId = new Map(
    clients
      .filter((client) => client.legacyCompanyId)
      .map((client) => [client.legacyCompanyId as string, client.id]),
  );
  const companies = ((companyRows ?? []) as CompanyRow[]).map((row) =>
    mapCompany(row, clientsByCompanyId),
  );

  const groups = ((groupRows ?? []) as Array<{
    id: string;
    name: string;
    slug: string;
    description: string | null;
    status: CatalogStatus;
  }>).map((row) => ({
    id: row.id,
    name: row.name,
    slug: row.slug,
    description: row.description,
    status: row.status,
    companyId: null,
    companyName: null,
    accessStartsAt: null,
    accessEndsAt: null,
    tags: sortTags(
      (tagIdsByGroup.get(row.id) ?? [])
        .map((tagId) => tagsById.get(tagId))
        .filter((tag): tag is TagRecord => Boolean(tag)),
    ),
    scenarioTemplateIds: [...new Set(scenarioIdsByGroup.get(row.id) ?? [])],
    members: [],
    memberCount: 0,
  } satisfies ScenarioGroupRecord));

  return {
    companies,
    clients,
    tags,
    groups,
    scenarios: scenarioTemplates,
    effectiveAccess: [] satisfies EffectiveAccessRecord[],
    clientAssignments: [],
  };
}

export async function getAdminDashboardData(
  admin: SupabaseClient,
): Promise<AdminDashboardData> {
  try {
  const [
    { data: clientRows, error: clientError },
    { data: companyRows, error: companyError },
    { data: tagRows, error: tagError },
    { data: groupRows, error: groupError },
    { data: groupTagRows, error: groupTagError },
    { data: groupScenarioRows, error: groupScenarioError },
    { data: groupMemberRows, error: groupMemberError },
    { data: templateRows, error: templateError },
    { data: versionRows, error: versionError },
    { data: scenarioTagRows, error: scenarioTagError },
  ] = await Promise.all([
    admin
      .from("clients")
      .select("id, name, slug, status, legacy_company_id")
      .order("name"),
    admin.from("companies").select("id, name, slug").order("name"),
    admin.from("tags").select("id, label, slug, status").order("label"),
    admin
      .from("scenario_groups")
      .select(
        "id, name, slug, description, status, company_id, access_starts_at, access_ends_at, company:companies(id, name, slug)",
      )
      .order("name"),
    admin.from("group_tags").select("group_id, tag:tags(id, label, slug, status)"),
    admin.from("scenario_group_scenarios").select("group_id, scenario_template_id"),
    admin.from("scenario_group_members").select(
      "group_id, member:company_members(id, email, full_name, role, status, magic_link_last_sent_at)",
    ),
    admin
      .from("scenario_templates")
      .select("id, slug, status, current_published_version_id, current_draft_version_id")
      .order("slug"),
    admin
      .from("scenario_versions")
      .select(
        "id, template_id, version_number, status, payload, title, target_skill, difficulty, duration_label, short_brief, published_at",
      )
      .order("template_id")
      .order("version_number"),
    admin.from("scenario_tags").select("scenario_template_id, tag:tags(id, label, slug, status)"),
  ]);

  for (const error of [
    clientError,
    companyError,
    tagError,
    groupError,
    groupTagError,
    groupScenarioError,
    groupMemberError,
    templateError,
    versionError,
    scenarioTagError,
  ]) {
    if (error) {
      throw error;
    }
  }

  const tags = ((tagRows ?? []) as TagRow[]).map((row) => mapTag(row));
  const tagsById = new Map(tags.map((tag) => [tag.id, tag]));

  const tagIdsByTemplate = new Map<string, string[]>();
  for (const row of (scenarioTagRows ?? []) as ScenarioTagLinkRow[]) {
    const tag = getSingleItem(row.tag);
    if (!tag) {
      continue;
    }

    const values = tagIdsByTemplate.get(row.scenario_template_id) ?? [];
    values.push(tag.id);
    tagIdsByTemplate.set(row.scenario_template_id, values);
  }

  const versionRowsByTemplate = new Map<string, ScenarioVersionRow[]>();
  for (const version of (versionRows ?? []) as ScenarioVersionRow[]) {
    const values = versionRowsByTemplate.get(version.template_id) ?? [];
    values.push(version);
    versionRowsByTemplate.set(version.template_id, values);
  }

  const scenarioTemplates = ((templateRows ?? []) as ScenarioTemplateRow[]).map(
    (template) => {
      const versions = versionRowsByTemplate.get(template.id) ?? [];
      const publishedVersion =
        versions.find(
          (version) => version.id === template.current_published_version_id,
        ) ?? null;
      const draftVersion =
        versions.find((version) => version.id === template.current_draft_version_id) ??
        null;
      const templateTags = sortTags(
        (tagIdsByTemplate.get(template.id) ?? [])
          .map((tagId) => tagsById.get(tagId))
          .filter((tag): tag is TagRecord => Boolean(tag)),
      );

      return {
        id: template.id,
        slug: template.slug,
        status: template.status,
        currentPublishedVersionId: template.current_published_version_id,
        currentDraftVersionId: template.current_draft_version_id,
        publishedVersionNumber: publishedVersion?.version_number ?? null,
        draftVersionNumber: draftVersion?.version_number ?? null,
        publishedScenario: publishedVersion
          ? mapScenarioItem(template, publishedVersion, templateTags)
          : null,
        draftScenario: draftVersion
          ? mapScenarioItem(template, draftVersion, templateTags)
          : null,
        tags: templateTags,
      } satisfies ScenarioTemplateRecord;
    },
  );

  const tagIdsByGroup = new Map<string, string[]>();
  for (const row of (groupTagRows ?? []) as GroupTagLinkRow[]) {
    const tag = getSingleItem(row.tag);
    if (!tag) {
      continue;
    }

    const values = tagIdsByGroup.get(row.group_id) ?? [];
    values.push(tag.id);
    tagIdsByGroup.set(row.group_id, values);
  }

  const scenarioIdsByGroup = new Map<string, string[]>();
  for (const row of (groupScenarioRows ?? []) as GroupScenarioLinkRow[]) {
    const values = scenarioIdsByGroup.get(row.group_id) ?? [];
    values.push(row.scenario_template_id);
    scenarioIdsByGroup.set(row.group_id, values);
  }

  const clients = ((clientRows ?? []) as ClientRow[]).map(mapClient);
  const clientsByCompanyId = new Map(
    clients
      .filter((client) => client.legacyCompanyId)
      .map((client) => [client.legacyCompanyId as string, client.id]),
  );
  const companies = ((companyRows ?? []) as CompanyRow[]).map((row) =>
    mapCompany(row, clientsByCompanyId),
  );

  const membersByGroup = new Map<string, ScenarioGroupMemberRecord[]>();
  for (const row of (groupMemberRows ?? []) as GroupMemberLinkRow[]) {
    const member = mapGroupMember(row.member);
    if (!member) {
      continue;
    }

    const values = membersByGroup.get(row.group_id) ?? [];
    values.push(member);
    membersByGroup.set(row.group_id, values);
  }

  const groups = ((groupRows ?? []) as GroupRow[]).map((row) => {
    const members = sortGroupMembers(membersByGroup.get(row.id) ?? []);

    return {
      id: row.id,
      name: row.name,
      slug: row.slug,
      description: row.description,
      status: row.status,
      companyId: row.company_id,
      companyName: getSingleItem(row.company)?.name ?? null,
      accessStartsAt: row.access_starts_at,
      accessEndsAt: row.access_ends_at,
      tags: sortTags(
        (tagIdsByGroup.get(row.id) ?? [])
          .map((tagId) => tagsById.get(tagId))
          .filter((tag): tag is TagRecord => Boolean(tag)),
      ),
      scenarioTemplateIds: [...new Set(scenarioIdsByGroup.get(row.id) ?? [])],
      members,
      memberCount: members.length,
    } satisfies ScenarioGroupRecord;
  });

  return {
    companies,
    clients,
    tags,
    groups,
    scenarios: scenarioTemplates,
    effectiveAccess: [] satisfies EffectiveAccessRecord[],
    clientAssignments: [] satisfies { clientId: string; groupIds: string[]; scenarioTemplateIds: string[] }[],
  };
  } catch (error) {
    if (isMissingGroupSchemaError(error)) {
      return getLegacyAdminDashboardData(admin);
    }

    throw error;
  }
}
export async function getPlatformAdmins(admin: SupabaseClient) {
  const { data, error } = await admin
    .from("platform_admins")
    .select("id, auth_user_id, email, full_name, status")
    .order("email");

  if (error) {
    throw error;
  }

  return ((data ?? []) as PlatformAdminRow[]).map((row) => ({
    id: row.id,
    authUserId: row.auth_user_id,
    email: row.email,
    fullName: row.full_name,
    status: row.status,
  }));
}

export async function getCachedAdminDashboardData() {
  return getCachedAdminDashboardDataInternal();
}

export async function getCachedPlatformAdmins() {
  return getCachedPlatformAdminsInternal();
}

export function revalidateAdminConsoleCache() {
  revalidateTag(ADMIN_CONSOLE_CACHE_TAG, "max");
  revalidateTag(PLATFORM_ADMINS_CACHE_TAG, "max");
}

export async function syncScenarioTags(
  admin: SupabaseClient,
  templateId: string,
  tagIds: string[],
) {
  const { error: deleteError } = await admin
    .from("scenario_tags")
    .delete()
    .eq("scenario_template_id", templateId);

  if (deleteError) {
    throw deleteError;
  }

  if (tagIds.length === 0) {
    return;
  }

  const { error: insertError } = await admin.from("scenario_tags").insert(
    tagIds.map((tagId) => ({
      scenario_template_id: templateId,
      tag_id: tagId,
    })),
  );

  if (insertError) {
    throw insertError;
  }
}

export async function syncGroupTags(
  admin: SupabaseClient,
  groupId: string,
  tagIds: string[],
) {
  const { error: deleteError } = await admin
    .from("group_tags")
    .delete()
    .eq("group_id", groupId);

  if (deleteError) {
    throw deleteError;
  }

  if (tagIds.length === 0) {
    return;
  }

  const { error: insertError } = await admin.from("group_tags").insert(
    tagIds.map((tagId) => ({
      group_id: groupId,
      tag_id: tagId,
    })),
  );

  if (insertError) {
    throw insertError;
  }
}

export async function syncGroupScenarios(
  admin: SupabaseClient,
  groupId: string,
  scenarioTemplateIds: string[],
) {
  const { error: deleteError } = await admin
    .from("scenario_group_scenarios")
    .delete()
    .eq("group_id", groupId);

  if (deleteError) {
    throw deleteError;
  }

  if (scenarioTemplateIds.length === 0) {
    return;
  }

  const { error: insertError } = await admin
    .from("scenario_group_scenarios")
    .insert(
      scenarioTemplateIds.map((scenarioTemplateId) => ({
        group_id: groupId,
        scenario_template_id: scenarioTemplateId,
      })),
    );

  if (insertError) {
    throw insertError;
  }
}

export async function syncClientAccess(
  admin: SupabaseClient,
  clientId: string,
  groupIds: string[],
  scenarioTemplateIds: string[],
) {
  const [{ error: deleteGroupError }, { error: deleteScenarioError }] =
    await Promise.all([
      admin.from("client_group_access").delete().eq("client_id", clientId),
      admin.from("client_scenario_access").delete().eq("client_id", clientId),
    ]);

  if (deleteGroupError) {
    throw deleteGroupError;
  }

  if (deleteScenarioError) {
    throw deleteScenarioError;
  }

  const inserts = [];

  if (groupIds.length > 0) {
    inserts.push(
      admin
        .from("client_group_access")
        .insert(groupIds.map((groupId) => ({ client_id: clientId, group_id: groupId }))),
    );
  }

  if (scenarioTemplateIds.length > 0) {
    inserts.push(
      admin.from("client_scenario_access").insert(
        scenarioTemplateIds.map((scenarioTemplateId) => ({
          client_id: clientId,
          scenario_template_id: scenarioTemplateId,
        })),
      ),
    );
  }

  for (const promise of inserts) {
    const { error } = await promise;
    if (error) {
      throw error;
    }
  }
}