// ---------------------------------------------------------------------------
// Cloud's `MemberDirectory`: the shared read seam over the LOCAL membership
// mirror (`memberships` join `accounts`, db/schema.ts), never over WorkOS.
//
// The mirror is written by `WorkOsMirror` (login, write-through, the Events
// API reconciler); this file only reads it. A row without a `membership_id`
// was never written by a feeder — it predates the mirror — and is not
// reported: the directory answers only for memberships it actually knows the
// WorkOS identity of, and every feeder fills the id in on its next pass.
// A deleted membership is never dropped, it is TOMBSTONED (`status =
// 'inactive'`, `deleted_at` set) so a feeder replaying a payload of the
// deleted membership cannot bring it back; every read here filters on status,
// active + pending unless the caller names the statuses it wants.
//
// Per-request layer: it holds the request's postgres socket via `DbService`.
// ---------------------------------------------------------------------------

import { and, asc, eq, ilike, inArray, isNotNull, or, sql } from "drizzle-orm";
import { Effect, Layer } from "effect";

import {
  DEFAULT_MEMBER_STATUSES,
  MemberDirectory,
  MemberDirectoryError,
  normalizeMemberSearch,
  type DirectoryMember,
  type MemberDirectoryShape,
  type MemberQuery,
} from "@executor-js/api/server";

import { accounts, memberships } from "../db/schema";
import { DbService, type DrizzleDb } from "../db/db";
import { tryPromiseService, withServiceLogging } from "./errors";

// Escape LIKE wildcards in a user-typed search term so `_` and `%` match
// themselves. Same treatment `org-deletion.ts` gives an org id prefix.
const escapeLike = (value: string): string => value.replace(/[\\%_]/g, "\\$&");

// The display name the seam reports: first + last, or nothing. Computed in SQL
// too (below) so the search term matches what the caller sees.
const displayName = (firstName: string | null, lastName: string | null): string | null =>
  [firstName, lastName].filter(Boolean).join(" ") || null;

const makeService = (db: DrizzleDb): MemberDirectoryShape => {
  const read = <A>(op: string, fn: () => Promise<A>) =>
    withServiceLogging(
      `member_directory.${op}`,
      () =>
        new MemberDirectoryError({
          message: `Failed to read the member directory (${op})`,
        }),
      tryPromiseService(fn),
    );

  // One projection for every read, so the seam's row shape is built in exactly
  // one place. `membershipId` is non-null by the `known` predicate below.
  const select = () =>
    db
      .select({
        accountId: memberships.accountId,
        membershipId: memberships.membershipId,
        organizationId: memberships.organizationId,
        role: memberships.role,
        status: memberships.status,
        email: accounts.email,
        firstName: accounts.firstName,
        lastName: accounts.lastName,
        avatarUrl: accounts.avatarUrl,
        lastSignInAt: accounts.lastSignInAt,
      })
      .from(memberships)
      .innerJoin(accounts, eq(accounts.id, memberships.accountId))
      .$dynamic();

  type Row = Awaited<ReturnType<typeof select>>[number];

  const toMember = (row: Row): DirectoryMember | null =>
    row.membershipId === null
      ? null
      : {
          accountId: row.accountId,
          membershipId: row.membershipId,
          organizationId: row.organizationId,
          email: row.email,
          name: displayName(row.firstName, row.lastName),
          avatarUrl: row.avatarUrl,
          role: row.role,
          status: row.status,
          lastActiveAt: row.lastSignInAt === null ? null : row.lastSignInAt.getTime(),
        };

  const toMembers = (rows: readonly Row[]): DirectoryMember[] => {
    const members: DirectoryMember[] = [];
    for (const row of rows) {
      const member = toMember(row);
      if (member !== null) members.push(member);
    }
    return members;
  };

  const known = isNotNull(memberships.membershipId);

  const members = (organizationId: string, query: MemberQuery = {}) =>
    read("members", async () => {
      const term = normalizeMemberSearch(query.search);
      const pattern = term === undefined ? undefined : `%${escapeLike(term)}%`;
      let statement = select()
        .where(
          and(
            eq(memberships.organizationId, organizationId),
            inArray(memberships.status, query.statuses ?? DEFAULT_MEMBER_STATUSES),
            known,
            pattern === undefined
              ? undefined
              : or(
                  ilike(accounts.email, pattern),
                  ilike(sql`concat_ws(' ', ${accounts.firstName}, ${accounts.lastName})`, pattern),
                ),
          ),
        )
        .orderBy(asc(accounts.email), asc(memberships.accountId));
      if (query.limit !== undefined) statement = statement.limit(query.limit);
      if (query.offset !== undefined) statement = statement.offset(query.offset);
      return toMembers(await statement);
    });

  return {
    membership: (accountId, organizationId, statuses = DEFAULT_MEMBER_STATUSES) =>
      read("membership", async () => {
        const rows = await select()
          .where(
            and(
              eq(memberships.accountId, accountId),
              eq(memberships.organizationId, organizationId),
              inArray(memberships.status, statuses),
              known,
            ),
          )
          .limit(1);
        const row = rows[0];
        return row === undefined ? null : toMember(row);
      }),

    // The unique index on `membership_id` makes this a point read; the org
    // predicate is what refuses an id that belongs to another org.
    membershipById: (organizationId, membershipId) =>
      read("membershipById", async () => {
        const rows = await select()
          .where(
            and(
              eq(memberships.organizationId, organizationId),
              eq(memberships.membershipId, membershipId),
            ),
          )
          .limit(1);
        const row = rows[0];
        return row === undefined ? null : toMember(row);
      }),

    membershipsOf: (accountId, statuses = DEFAULT_MEMBER_STATUSES) =>
      read("membershipsOf", async () => {
        const rows = await select()
          .where(
            and(eq(memberships.accountId, accountId), inArray(memberships.status, statuses), known),
          )
          .orderBy(asc(memberships.organizationId));
        return toMembers(rows);
      }),

    members,

    membersById: (organizationId, accountIds, statuses = DEFAULT_MEMBER_STATUSES) =>
      accountIds.length === 0
        ? Effect.succeed(new Map<string, DirectoryMember>())
        : read("membersById", async () => {
            const rows = await select().where(
              and(
                eq(memberships.organizationId, organizationId),
                inArray(memberships.accountId, accountIds),
                inArray(memberships.status, statuses),
                known,
              ),
            );
            return new Map(toMembers(rows).map((member) => [member.accountId, member]));
          }),

    findByEmail: (organizationId, email, statuses = DEFAULT_MEMBER_STATUSES) =>
      read("findByEmail", async () => {
        const rows = await select()
          .where(
            and(
              eq(memberships.organizationId, organizationId),
              eq(sql`lower(${accounts.email})`, email),
              inArray(memberships.status, statuses),
              known,
            ),
          )
          .limit(1);
        const row = rows[0];
        return row === undefined ? null : toMember(row);
      }),
  };
};

/** The cloud `MemberDirectory` over the per-request `DbService`. */
export const cloudMemberDirectoryLayer: Layer.Layer<MemberDirectory, never, DbService> =
  Layer.effect(MemberDirectory)(Effect.map(DbService.asEffect(), ({ db }) => makeService(db)));

/**
 * A FRESH `MemberDirectory` layer (new layer value per call), for a service
 * built once but invoked across many Workers requests — the MCP
 * org-authorization seam and the document gate — for the same reason
 * `makeUserStoreLayer` exists: a memoized const layer would pin the first
 * request's postgres socket. See [[makeDbLayer]].
 */
export const makeMemberDirectoryLayer = (): Layer.Layer<MemberDirectory, never, DbService> =>
  Layer.effect(MemberDirectory)(Effect.map(DbService.asEffect(), ({ db }) => makeService(db)));