// src/lib/admin/org-queries.ts — God-Mode READ loaders for the organization /
// multi-tenancy surfaces. Gated on `orgs:read` (assertSysPermission): Admin SYS
// always, Staff SYS if granted — the 2FA wall lives inside the gate itself.
import "server-only";
import { asc, count, desc, eq, ilike, type SQL } from "drizzle-orm";
import { assertSysPermission } from "@/lib/auth/sys-permissions";
import {
  getPlan,
  PLANS,
  type PlanLimits,
  type PlanName,
} from "@/lib/billing/plans";
import { getOrgQuotaLimits, resolveQuotaLimits } from "@/lib/billing/quotas";
import {
  getOrgBillingInfo,
  pickRelevantSubscription,
} from "@/lib/billing/status";
import { orgLimitOverrides } from "@/lib/db/admin-schema";
import { member, organization, subscription, user } from "@/lib/db/auth-schema";
import { db } from "@/lib/db/client";

export type OrgSortBy = "created" | "members" | "name";

const PAGE_SIZE = 20;

export interface AdminOrgRow {
  id: string;
  name: string;
  slug: string;
  memberCount: number;
  status: "active" | "trialing" | "expired";
  plan: string | null;
  createdAt: Date;
  trialEndsAt: Date | null;
}

export interface ListOrgsResult {
  orgs: AdminOrgRow[];
  total: number;
  limit: number;
  offset: number;
  search: string;
  sortBy: OrgSortBy;
}

export interface ListOrgsInput {
  search?: string;
  sortBy?: OrgSortBy;
  offset?: number;
}

/**
 * Paginated tenant list (super-admin only). One aggregate query joins the member
 * count per org; billing status/plan is resolved per row via getOrgBillingInfo
 * (acceptable N round-trips for a light admin tool on a ≤20-row page).
 */
export async function listOrgsAction(
  input: ListOrgsInput = {},
): Promise<ListOrgsResult> {
  await assertSysPermission("orgs:read");
  const search = (input.search ?? "").trim();
  const offset = Math.max(0, input.offset ?? 0);
  const sortBy: OrgSortBy = input.sortBy ?? "created";

  const where: SQL | undefined = search
    ? ilike(organization.name, `%${search}%`)
    : undefined;

  const memberCount = count(member.id);
  const orderBy =
    sortBy === "members"
      ? desc(memberCount)
      : sortBy === "name"
        ? asc(organization.name)
        : desc(organization.createdAt);

  const [rows, totalRows] = await Promise.all([
    db
      .select({
        id: organization.id,
        name: organization.name,
        slug: organization.slug,
        createdAt: organization.createdAt,
        trialEndsAt: organization.trialEndsAt,
        memberCount,
      })
      .from(organization)
      .leftJoin(member, eq(member.organizationId, organization.id))
      .where(where)
      .groupBy(organization.id)
      .orderBy(orderBy)
      .limit(PAGE_SIZE)
      .offset(offset),
    db.select({ value: count() }).from(organization).where(where),
  ]);

  const orgs: AdminOrgRow[] = await Promise.all(
    rows.map(async (r) => {
      const billing = await getOrgBillingInfo(r.id);
      return {
        id: r.id,
        name: r.name,
        slug: r.slug,
        memberCount: r.memberCount,
        status: billing.status,
        plan: billing.plan,
        createdAt: r.createdAt,
        trialEndsAt: r.trialEndsAt,
      };
    }),
  );

  return {
    orgs,
    total: totalRows[0]?.value ?? 0,
    limit: PAGE_SIZE,
    offset,
    search,
    sortBy,
  };
}

export interface AdminOrgMember {
  userId: string;
  name: string;
  email: string;
  role: string;
  joinedAt: Date;
}

export interface AdminOrgSubscription {
  plan: string;
  status: string | null;
  periodEnd: Date | null;
  seats: number | null;
  stripeCustomerId: string | null;
  stripeSubscriptionId: string | null;
}

export interface AdminOrgOverride {
  keywords: number | null;
  projects: number | null;
  seats: number | null;
  integrations: number | null;
  backlinkRows: number | null;
  mcpMonthly: number | null;
  note: string | null;
  expiresAt: Date | null;
  updatedAt: Date;
}

export interface AdminOrgDetail {
  id: string;
  name: string;
  slug: string;
  logo: string | null;
  createdAt: Date;
  trialEndsAt: Date | null;
  stripeCustomerId: string | null;
  status: "active" | "trialing" | "expired";
  plan: string | null;
  members: AdminOrgMember[];
  subscription: AdminOrgSubscription | null;
  /** Effective limits AFTER plan resolution + override application. */
  effectiveLimits: PlanLimits;
  /** Pure plan-based limits (no override) — for the "vs plan default" UI. */
  planLimits: PlanLimits;
  override: AdminOrgOverride | null;
}

/** Full detail for the God-Mode org page. Super-admin only. Null if not found. */
export async function getOrgDetailAction(
  orgId: string,
): Promise<AdminOrgDetail | null> {
  await assertSysPermission("orgs:read");

  const [org] = await db
    .select({
      id: organization.id,
      name: organization.name,
      slug: organization.slug,
      logo: organization.logo,
      createdAt: organization.createdAt,
      trialEndsAt: organization.trialEndsAt,
      stripeCustomerId: organization.stripeCustomerId,
    })
    .from(organization)
    .where(eq(organization.id, orgId))
    .limit(1);
  if (!org) return null;

  const [members, subs, [overrideRow], billing, effectiveLimits] =
    await Promise.all([
      db
        .select({
          userId: member.userId,
          name: user.name,
          email: user.email,
          role: member.role,
          joinedAt: member.createdAt,
        })
        .from(member)
        .innerJoin(user, eq(member.userId, user.id))
        .where(eq(member.organizationId, orgId))
        .orderBy(asc(member.createdAt)),
      db
        .select({
          plan: subscription.plan,
          status: subscription.status,
          periodStart: subscription.periodStart,
          periodEnd: subscription.periodEnd,
          seats: subscription.seats,
          stripeCustomerId: subscription.stripeCustomerId,
          stripeSubscriptionId: subscription.stripeSubscriptionId,
        })
        .from(subscription)
        .where(eq(subscription.referenceId, orgId)),
      db
        .select()
        .from(orgLimitOverrides)
        .where(eq(orgLimitOverrides.organizationId, orgId))
        .limit(1),
      getOrgBillingInfo(orgId),
      getOrgQuotaLimits(orgId),
    ]);

  const sub = pickRelevantSubscription(subs);
  const planLimits = resolveQuotaLimits(billing);

  return {
    id: org.id,
    name: org.name,
    slug: org.slug,
    logo: org.logo,
    createdAt: org.createdAt,
    trialEndsAt: org.trialEndsAt,
    stripeCustomerId: org.stripeCustomerId,
    status: billing.status,
    plan: billing.plan,
    members,
    subscription: sub
      ? {
          plan: sub.plan,
          status: sub.status,
          periodEnd: sub.periodEnd,
          seats: sub.seats,
          stripeCustomerId: sub.stripeCustomerId,
          stripeSubscriptionId: sub.stripeSubscriptionId,
        }
      : null,
    effectiveLimits,
    planLimits,
    override: overrideRow
      ? {
          keywords: overrideRow.keywords,
          projects: overrideRow.projects,
          seats: overrideRow.seats,
          integrations: overrideRow.integrations,
          backlinkRows: overrideRow.backlinkRows,
          mcpMonthly: overrideRow.mcpMonthly,
          note: overrideRow.note,
          expiresAt: overrideRow.expiresAt,
          updatedAt: overrideRow.updatedAt,
        }
      : null,
  };
}

/** Plan limit grid for the override editor (so the UI can show "plan default"
 *  per plan). Pure re-export of the plan data, super-admin gated for symmetry. */
export function planLimitGrid(): Record<PlanName, PlanLimits> {
  return Object.fromEntries(PLANS.map((p) => [p.name, p.limits])) as Record<
    PlanName,
    PlanLimits
  >;
}

export { getPlan };