import { Kysely, sql } from "kysely";
import type { DB } from "@/internal/datastore/db";
import { getMemberPoints, transferPoints, createMemberLog } from "@/viewpoint";
import { env } from "@/lib/env";

const POINTS_TRANSFER = {
  SOURCE_ACCOUNT_DEFAULT: '901171',
  SOURCE_ACCOUNT_INDIA: '901172',
  CUSTOM_TYPE_ID: 70024,
  ENTITLEMENT_POINTS_CUSTOM_TYPE_ID: 95712,
  ENTITLE_ID_DEFAULT: '1031207',
  ENTITLE_ID_INDIA: '1332361',
  KEVO_NON_DORMANT_VALUE: 25,
} as const;

/**
 * Module-level state for the holding-balance sync. The ViewPoint entitlements
 * endpoint takes ~50s for the large holding accounts, so the sync runs in the
 * background (fire-and-forget) rather than blocking the HTTP request. The UI
 * polls getHoldingBalances() and watches `syncing` / `syncError`.
 */
let holdingSyncState: {
  running: boolean;
  error: string | null;
  startedAt: number | null;
  finishedAt: number | null;
  /** Identifies the current run so a stale one can't clobber its successor. */
  runId: number;
} = { running: false, error: null, startedAt: null, finishedAt: null, runId: 0 };

/**
 * The in-flight sync, so a caller can await the exact run rather than polling
 * for it. Never rejects — failures are recorded on holdingSyncState.error.
 */
let holdingSyncPromise: Promise<void> | null = null;

export class PointsTransferService {
  constructor(private db: Kysely<DB>) { }

  private getHoldingAccountNo(region: "INDIA" | "NON_INDIA"): string {
    return region === "INDIA"
      ? POINTS_TRANSFER.SOURCE_ACCOUNT_INDIA
      : POINTS_TRANSFER.SOURCE_ACCOUNT_DEFAULT;
  }

  /**
   * Entitlements a member can actually transfer FROM: ACTIVE, positive
   * balance, and not yet expired — across ALL entitlement years. Sorted
   * soonest-expiry-first (tie-break by PointsEntitlementID) so a transfer
   * drains the points closest to expiring first.
   *
   * NOTE: this deliberately does NOT filter on `EntitlementYear === current
   * year`. The production PHP (api-transfer-points.php) does, but that hides
   * the bulk of the holding accounts' points — the India account holds most
   * of its balance in 2023 and 2027-2029 entitlements — so the panel showed a
   * tiny fraction of the real balance. Every year is counted here.
   */
  private getEligibleEntitlements(pointsArr: any[]): any[] {
    if (!Array.isArray(pointsArr)) return [];

    const today = new Date();
    today.setHours(0, 0, 0, 0);
    const pad = (n: number) => n.toString().padStart(2, '0');
    const todayString = `${today.getFullYear()}-${pad(today.getMonth() + 1)}-${pad(today.getDate())}T00:00:00`;

    const eligible = pointsArr.filter((e) => {
      const balance = e?.Balance || 0;
      return (
        balance > 0 &&
        String(e?.Status).toUpperCase() === "ACTIVE" &&
        e?.ExpiryDate &&
        e.ExpiryDate > todayString
      );
    });

    eligible.sort((a, b) => {
      if (a.ExpiryDate === b.ExpiryDate) {
        return a.PointsEntitlementID - b.PointsEntitlementID;
      }
      return a.ExpiryDate < b.ExpiryDate ? -1 : 1;
    });

    return eligible;
  }

  /**
   * Total points available to transfer — the sum of eligible entitlements.
   * Rounded to fit the integer balance column.
   */
  private sumTransferableBalance(pointsArr: any[]): number {
    const total = this.getEligibleEntitlements(pointsArr).reduce(
      (acc, e) => acc + (e.Balance || 0),
      0,
    );
    return Math.round(total);
  }

  async getHoldingBalances() {
    const indiaAcc = this.getHoldingAccountNo("INDIA");
    const nonIndiaAcc = this.getHoldingAccountNo("NON_INDIA");

    try {
      const dbBalances = await this.db
        .selectFrom("holding_account_balances")
        .selectAll()
        .execute();

      const indiaData = dbBalances.find((b) => b.region === "INDIA") || { account_no: indiaAcc, balance: 0, last_synced_at: null };
      const nonIndiaData = dbBalances.find((b) => b.region === "NON_INDIA") || { account_no: nonIndiaAcc, balance: 0, last_synced_at: null };

      return {
        india: {
          account: indiaData.account_no,
          balance: indiaData.balance,
          lastSyncedAt: indiaData.last_synced_at,
        },
        nonIndia: {
          account: nonIndiaData.account_no,
          balance: nonIndiaData.balance,
          lastSyncedAt: nonIndiaData.last_synced_at,
        },
        syncing: holdingSyncState.running,
        syncError: holdingSyncState.error,
      };
    } catch (error) {
      console.error("Failed to fetch holding balances from DB", error);
      throw error;
    }
  }

  /**
   * Kick off a holding-balance sync in the background and return immediately.
   * If a sync is already running, it's a no-op. The heavy ViewPoint calls run
   * detached from the request; the caller polls getHoldingBalances() to see
   * `syncing` flip back to false (and `syncError` if it failed).
   */
  startHoldingSync(): { started: boolean; alreadyRunning: boolean } {
    // A full sync is ~105s (two sequential ViewPoint calls). If the flag has
    // been stuck on for far longer than that, the previous run died without
    // settling its promise — don't let a lost run wedge the Sync button
    // permanently, just take the lock back.
    const STALE_LOCK_MS = 10 * 60 * 1000;
    const lockAge = holdingSyncState.startedAt ? Date.now() - holdingSyncState.startedAt : 0;
    if (holdingSyncState.running && lockAge < STALE_LOCK_MS) {
      return { started: false, alreadyRunning: true };
    }
    if (holdingSyncState.running) {
      console.warn(`Holding sync lock was stuck for ${Math.round(lockAge / 1000)}s — forcing a new run`);
    }

    const runId = holdingSyncState.runId + 1;
    holdingSyncState = { running: true, error: null, startedAt: Date.now(), finishedAt: null, runId };

    // Only the run that still owns the lock may report its result — otherwise
    // a stale run settling late would mark the current one as finished.
    const settle = (error: string | null) => {
      if (holdingSyncState.runId !== runId) return;
      holdingSyncState = { ...holdingSyncState, running: false, error, finishedAt: Date.now() };
    };

    // The datastore is a long-lived shared pool, so the run is safe to continue
    // after the HTTP response has been sent. Kept on holdingSyncPromise so a
    // caller can await this exact run instead of polling for it; the catch
    // means it settles rather than rejecting, so awaiting it is always safe.
    holdingSyncPromise = this.runHoldingSync()
      .then(() => settle(null))
      .catch((err: any) => {
        console.error("Holding balance background sync failed", err);
        settle(err?.message || "Sync failed");
      });

    return { started: true, alreadyRunning: false };
  }

  /**
   * Start a sync (or join the one already running) and wait for it to finish,
   * then return the fresh balances. Lets the caller respond the instant the
   * run settles instead of polling on an interval. `timedOut` is true if we
   * gave up waiting — the sync itself keeps going, so the client can fall
   * back to polling.
   */
  // Default cap sits below the app-wide 180s request timeout in cmd/main.ts so
  // we return a clean `timedOut` result rather than letting that middleware
  // kill the request with a 408. A normal sync is ~105s.
  async syncHoldingBalancesAndWait(waitMs = 150 * 1000) {
    const start = this.startHoldingSync();
    const inFlight = holdingSyncPromise;

    let timedOut = false;
    if (inFlight) {
      let timer: NodeJS.Timeout | undefined;
      const guard = new Promise<"timeout">((resolve) => {
        timer = setTimeout(() => resolve("timeout"), waitMs);
      });
      try {
        timedOut = (await Promise.race([inFlight.then(() => "done" as const), guard])) === "timeout";
      } finally {
        if (timer) clearTimeout(timer);
      }
    }

    return { ...start, timedOut, balances: await this.getHoldingBalances() };
  }

  private async runHoldingSync() {
    const indiaAcc = this.getHoldingAccountNo("INDIA");
    const nonIndiaAcc = this.getHoldingAccountNo("NON_INDIA");

    const indiaClubResortID = env.GetInt("INDIA_CLUB_RESORT_ID");
    const nonIndiaClubResortID = env.GetInt("NON_INDIA_CLUB_RESORT_ID");

    // Fetch each holding account independently so one ViewPoint failure
    // doesn't 500 the whole sync — we just skip updating that region and
    // keep its last known balance.
    const fetchRegionBalance = async (
      accountNo: string,
      clubResortID: number,
      region: "INDIA" | "NON_INDIA",
    ): Promise<number | null> => {
      try {
        const points = await getMemberPoints(accountNo, clubResortID);
        return this.sumTransferableBalance(points);
      } catch (err) {
        console.error(`Failed to sync ${region} holding balance (${accountNo})`, err);
        return null;
      }
    };

    // MUST stay sequential. Measured against the test gateway: each account
    // returns 200 in ~45-57s on its own, but firing both at once makes them
    // contend inside ViewPoint and BOTH hit the 120s gateway timeout (504).
    // Sequential costs ~105s total, which is fine — this runs detached from
    // the request. Do not "optimise" this back into a Promise.all.
    const indiaBalance = await fetchRegionBalance(indiaAcc, indiaClubResortID, "INDIA");
    const nonIndiaBalance = await fetchRegionBalance(nonIndiaAcc, nonIndiaClubResortID, "NON_INDIA");

    const now = new Date();
    const rows = [
      indiaBalance !== null
        ? { account_no: indiaAcc, region: "INDIA" as const, balance: indiaBalance, last_synced_at: now }
        : null,
      nonIndiaBalance !== null
        ? { account_no: nonIndiaAcc, region: "NON_INDIA" as const, balance: nonIndiaBalance, last_synced_at: now }
        : null,
    ].filter((r): r is NonNullable<typeof r> => r !== null);

    if (rows.length === 0) {
      throw new Error("Failed to sync holding balances: ViewPoint is unavailable for all accounts.");
    }

    try {
      await this.db
        .insertInto("holding_account_balances")
        .values(rows)
        .onConflict((oc) =>
          oc.column("account_no").doUpdateSet({
            balance: (eb) => eb.ref("excluded.balance"),
            last_synced_at: (eb) => eb.ref("excluded.last_synced_at"),
          })
        )
        .execute();
    } catch (error) {
      console.error("Failed to persist holding balances", error);
      throw error;
    }

    return {
      success: true,
      synced: {
        india: indiaBalance !== null,
        nonIndia: nonIndiaBalance !== null,
      },
    };
  }

  // Managers are console users that have been mapped to a ViewPoint account.
  // `ownerId` scopes the result to the managers that user mapped — pass it for
  // everyone except super admins, who see the whole list. Unmapped users are
  // always included: they are the candidate pool for the Map Manager flow.
  async getManagers(includeDeleted = true, ownerId?: string) {
    let query = this.db
      .selectFrom("console_users")
      .select([
        "id", "first_name", "last_name", "email", "service_center",
        "viewpoint_member_no", "viewpoint_account_id",
        "viewpoint_contact_id", "viewpoint_club", "viewpoint_is_active",
        "viewpoint_mapped_by", "is_deleted", "is_active"
      ]);

    if (!includeDeleted) {
      query = query.where("is_deleted", "=", false);
    }

    if (ownerId) {
      query = query.where((eb) =>
        eb.or([
          eb("console_users.viewpoint_mapped_by", "=", ownerId),
          eb("console_users.viewpoint_account_id", "is", null),
        ]),
      );
    }

    const managers = await query.execute();
    return managers;
  }

  // Throws unless `ownerId` is undefined (super admin) or the manager was
  // mapped by that user. Every write path and the history read go through this.
  private async assertManagerOwnership(managerId: string, ownerId?: string) {
    if (!ownerId) return;

    const manager = await this.db
      .selectFrom("console_users")
      .select(["id", "viewpoint_mapped_by"])
      .where("id", "=", managerId)
      .executeTakeFirst();

    if (!manager) {
      throw new Error("Manager not found");
    }

    if (manager.viewpoint_mapped_by !== ownerId) {
      throw new Error("You can only manage managers you created");
    }
  }

  async getTransactions(limit = 3, offset = 0, search?: string, ownerId?: string) {
    let query = this.db
      .selectFrom("point_transactions")
      .leftJoin("console_users as manager", "manager.id", "point_transactions.target_manager_id")
      .leftJoin("console_users as initiator", "initiator.id", "point_transactions.initiator_id");

    if (ownerId) {
      query = query.where("manager.viewpoint_mapped_by", "=", ownerId);
    }

    if (search) {
      const searchPattern = `%${search}%`;
      query = query.where((eb) =>
        eb.or([
          eb("manager.first_name", "ilike", searchPattern),
          eb("manager.last_name", "ilike", searchPattern),
          eb("manager.email", "ilike", searchPattern),
          eb("initiator.first_name", "ilike", searchPattern),
          eb("initiator.last_name", "ilike", searchPattern),
          eb("initiator.email", "ilike", searchPattern),
          eb("point_transactions.notes", "ilike", searchPattern),
          eb("point_transactions.viewpoint_ref_id", "ilike", searchPattern),
          eb("point_transactions.source_holding_account", "ilike", searchPattern),
        ])
      );
    }

    const { count } = await query
      .select((eb) => eb.fn.count("point_transactions.id").as("count"))
      .executeTakeFirstOrThrow();

    const txs = await query
      .select([
        "point_transactions.id",
        "point_transactions.source_holding_account",
        "point_transactions.target_manager_id",
        "manager.first_name as manager_first_name",
        "manager.last_name as manager_last_name",
        "manager.email as manager_email",
        "point_transactions.points",
        "point_transactions.initiator_id",
        "initiator.first_name as initiator_first_name",
        "initiator.last_name as initiator_last_name",
        "initiator.email as initiator_email",
        "point_transactions.notes",
        "point_transactions.viewpoint_ref_id",
        "point_transactions.status",
        "point_transactions.created_at",
        "point_transactions.application",
      ])
      .orderBy("point_transactions.created_at", "desc")
      .limit(limit)
      .offset(offset)
      .execute();

    return { transactions: txs, total: Number(count) };
  }

  async transfer(
    payload: { targetManagerId: string; points: number; notes?: string; application?: string },
    adminUserId: string,
    ownerId?: string,
  ) {
    // 1. Get Target Manager
    const manager = await this.db
      .selectFrom("console_users")
      .select(["id", "first_name", "last_name", "email", "service_center", "viewpoint_account_id", "viewpoint_club", "viewpoint_mapped_by"])
      .where("id", "=", payload.targetManagerId)
      .executeTakeFirst();

    if (!manager) {
      throw new Error("Manager not found");
    }

    if (ownerId && manager.viewpoint_mapped_by !== ownerId) {
      throw new Error("You can only transfer points to managers you created");
    }

    if (!manager.viewpoint_account_id) {
      throw new Error("Manager has no ViewPoint Account configured");
    }

    // Get initiator info
    let initiatorName = "Super Admin";
    let initiatorEmail = "";
    if (adminUserId) {
      const initiator = await this.db
        .selectFrom("console_users")
        .select(["first_name", "last_name", "email"])
        .where("id", "=", adminUserId)
        .executeTakeFirst();
      if (initiator) {
        initiatorName = `${initiator.first_name} ${initiator.last_name || ""}`.trim();
        initiatorEmail = initiator.email;
      }
    }

    const isIndia = manager.service_center?.toUpperCase().includes("INDIA") || manager.viewpoint_club?.toUpperCase().includes("INDIA");
    const sourceAccount = this.getHoldingAccountNo(isIndia ? "INDIA" : "NON_INDIA");

    // 2. Execute VP Transfer
    const vpPayload = {
      SourceAccountID: sourceAccount,
      TargetAccountID: manager.viewpoint_account_id,
      Description: payload.notes || "Point Transfer from Super Admin",
      CustomTypeID: POINTS_TRANSFER.CUSTOM_TYPE_ID,
      Entitlements: [
        {
          PointsEntitlement: true,
          EntitleID: isIndia ? POINTS_TRANSFER.ENTITLE_ID_INDIA : POINTS_TRANSFER.ENTITLE_ID_DEFAULT,
          Value: payload.points,
          // Using 1 year expiry based on reference project
          ExpiryDate: new Date(new Date().setFullYear(new Date().getFullYear() + 1)).toISOString().replace(/\.\d{3}Z$/, 'Z'),
        }
      ]
    };

    let vpRefId = "";
    const appName = payload.application || "Admin Console";
    try {
      const transferRes = await transferPoints(vpPayload);
      // Assume transferRes returns some ref id
      vpRefId = transferRes?.TransferID || "VP_TRANS_MOCK";

      // Create Log with detailed structure
      const logComment = `Points Transfer Details:
- Amount: ${payload.points} pts
- Source Holding Account: ${sourceAccount}
- Target Manager: ${manager.first_name} ${manager.last_name || ""} (${manager.email}) [Account: ${manager.viewpoint_account_id}]
- Initiator: ${initiatorName} (${initiatorEmail || "N/A"})
- Application: ${appName}
- Reference ID: ${vpRefId}
- Notes: ${payload.notes || "None"}`;

      await createMemberLog(manager.viewpoint_account_id, {
        LogType: "VPONLINE",
        LogStatus: "OPEN",
        LogComment: logComment,
        Subject: "Super Admin Points Transfer",
        AssignedToUser: "vp2329", // default vp user
        LogSourceID: 95010,
        UserDate: new Date().toISOString(),
        DueDate: new Date().toISOString(),
      });
    } catch (err: any) {
      console.error("ViewPoint transfer failed:", err);
      throw new Error("ViewPoint transfer failed: " + err.message);
    }

    // 3. Save to DB
    await this.db
      .insertInto("point_transactions")
      .values({
        source_holding_account: sourceAccount,
        target_manager_id: manager.id,
        points: payload.points,
        initiator_id: adminUserId,
        notes: payload.notes,
        viewpoint_ref_id: vpRefId,
        status: "SUCCESS",
        application: appName,
      })
      .execute();

    console.log(`[Points Transfer] ${payload.points} points transferred successfully from holding account ${sourceAccount} to manager ${manager.first_name} ${manager.last_name || ""} (${manager.viewpoint_account_id}). Initiator: ${initiatorName} (${initiatorEmail || "N/A"}), App: ${appName}, VP Ref: ${vpRefId}`);

    return { success: true, vpRefId };
  }

  async searchViewPointByEmail(email: string) {
    try {
      const { searchMemberByEmail } = await import("@/viewpoint");
      return await searchMemberByEmail(email);
    } catch (err) {
      console.error("ViewPoint search failed", err);
      throw new Error("Failed to search member in ViewPoint");
    }
  }

  async mapManager(
    managerId: string,
    payload: { accountId: string; contactId: string; club: string; isActive: boolean },
    actorId?: string,
    ownerId?: string,
  ) {
    const existing = await this.db
      .selectFrom("console_users")
      .select(["id", "viewpoint_account_id", "viewpoint_mapped_by"])
      .where("id", "=", managerId)
      .executeTakeFirst();

    if (!existing) {
      throw new Error("Manager not found");
    }

    // Re-mapping someone else's manager is only allowed for super admins; the
    // original creator keeps ownership so the scoping stays stable.
    if (ownerId && existing.viewpoint_account_id && existing.viewpoint_mapped_by !== ownerId) {
      throw new Error("You can only manage managers you created");
    }

    await this.db
      .updateTable("console_users")
      .set({
        viewpoint_account_id: payload.accountId,
        viewpoint_contact_id: payload.contactId,
        viewpoint_club: payload.club,
        viewpoint_is_active: payload.isActive,
        viewpoint_mapped_by: existing.viewpoint_mapped_by ?? actorId ?? null,
      })
      .where("id", "=", managerId)
      .execute();
    return { success: true };
  }

  async getManagerBalances(managerId: string) {
    const manager = await this.db
      .selectFrom("console_users")
      .select(["viewpoint_account_id", "viewpoint_club", "service_center"])
      .where("id", "=", managerId)
      .executeTakeFirst();

    if (!manager || !manager.viewpoint_account_id) {
      return { balance: 0, account: "Unmapped" };
    }

    const isIndia = manager.service_center?.toUpperCase().includes("INDIA") || manager.viewpoint_club?.toUpperCase().includes("INDIA");
    let clubResortID = isIndia ? 3208 : 3206;

    try {
      const points = await getMemberPoints(manager.viewpoint_account_id, clubResortID);
      const balance = this.sumTransferableBalance(points);
      return { balance, account: manager.viewpoint_account_id };
    } catch (err) {
      console.error("Failed to fetch manager balances", err);
      return { balance: 0, account: manager.viewpoint_account_id };
    }
  }

  async getMemberByNumber(memberNo: string) {
    try {
      const { getMemberByNumber } = await import("@/viewpoint");
      const account = await getMemberByNumber(memberNo);
      if (!account || !account.AccountID) {
        return [];
      }
      return [account];
    } catch (err) {
      console.error("ViewPoint account retrieval failed", err);
      return [];
    }
  }

  async getManagerEntitlements(managerId: string) {
    const manager = await this.db
      .selectFrom("console_users")
      .select(["viewpoint_account_id", "service_center", "viewpoint_club"])
      .where("id", "=", managerId)
      .executeTakeFirst();

    if (!manager || !manager.viewpoint_account_id) {
      throw new Error("Manager not found or not mapped to ViewPoint.");
    }

    const isIndia = manager.service_center?.toUpperCase().includes("INDIA") || manager.viewpoint_club?.toUpperCase().includes("INDIA");
    const clubResortID = isIndia ? 3208 : 3206;

    const points = await getMemberPoints(manager.viewpoint_account_id, clubResortID);
    return this.getEligibleEntitlements(points);
  }

  async transferToMember(payload: { targetAccountId: string; targetContactId: string; targetFirstName?: string; targetLastName?: string; targetEmail?: string; points: number; notes?: string; application?: string }, managerId: string) {
    const manager = await this.db
      .selectFrom("console_users")
      .select(["first_name", "last_name", "email", "viewpoint_account_id", "service_center", "viewpoint_club"])
      .where("id", "=", managerId)
      .executeTakeFirst();

    if (!manager || !manager.viewpoint_account_id) {
      throw new Error("You must be mapped to a ViewPoint account to transfer points.");
    }

    const isIndia = manager.service_center?.toUpperCase().includes("INDIA") || manager.viewpoint_club?.toUpperCase().includes("INDIA");
    const clubResortID = isIndia ? 3208 : 3206;

    // Fetch the manager's current entitlements and keep only those eligible to
    // transfer from (ACTIVE, in-year, not expired, positive balance), ordered
    // soonest-expiry-first.
    const entitlements = await getMemberPoints(manager.viewpoint_account_id, clubResortID);
    const eligible = this.getEligibleEntitlements(entitlements);

    const totalAvailable = eligible.reduce((acc, e) => acc + (e.Balance || 0), 0);
    if (payload.points > totalAvailable) {
      throw new Error(`Insufficient balance. Available balance is ${Math.round(totalAvailable)} points.`);
    }

    // Waterfall the requested amount across eligible entitlements: drain each
    // fully until the remaining amount is satisfied, with the last entitlement
    // taking the remainder (mirrors api-transfer-points.php).
    let remaining = payload.points;
    const entitlementAllocations: { PointsEntitlement: true; EntitleID: number; Value: number }[] = [];
    for (const e of eligible) {
      if (remaining <= 0) break;
      const take = Math.min(e.Balance, remaining);
      entitlementAllocations.push({ PointsEntitlement: true, EntitleID: e.PointsEntitlementID, Value: take });
      remaining -= take;
    }

    if (entitlementAllocations.length === 0 || remaining > 0) {
      throw new Error("Unable to allocate the requested points from available entitlements.");
    }

    const managerName = `${manager.first_name} ${manager.last_name || ""}`.trim();
    const managerEmail = manager.email;

    const vpPayload = {
      SourceAccountID: manager.viewpoint_account_id,
      TargetAccountID: payload.targetAccountId, // Or targetContactId if VP requires it
      Description: payload.notes || "Point Transfer from Manager",
      CustomTypeID: POINTS_TRANSFER.ENTITLEMENT_POINTS_CUSTOM_TYPE_ID,
      Entitlements: entitlementAllocations,
    };

    const usedEntitlementIds = entitlementAllocations.map((a) => a.EntitleID).join(",");

    let vpRefId = "";
    const appName = payload.application || "Admin Console";
    try {
      const transferRes = await transferPoints(vpPayload);
      vpRefId = transferRes?.TransferID || "VP_MGR_TRANS_MOCK";

      const logComment = `Points Transfer Details:
- Amount: ${payload.points} pts
- From Manager: ${managerName} (${managerEmail}) [Account: ${manager.viewpoint_account_id}]
- To Member: Account ${payload.targetAccountId} (Contact: ${payload.targetContactId})
- Source Entitlements: ${entitlementAllocations.map((a) => `${a.EntitleID} (${a.Value} pts)`).join(", ")}
- Initiator: ${managerName} (${managerEmail})
- Application: ${appName}
- Reference ID: ${vpRefId}
- Notes: ${payload.notes || "None"}`;

      await createMemberLog(payload.targetAccountId, {
        LogType: "VPONLINE",
        LogStatus: "OPEN",
        LogComment: logComment,
        Subject: `Points Transfer from ${managerName}`,
        AssignedToUser: "vp2329",
        LogSourceID: 95010,
        UserDate: new Date().toISOString(),
        DueDate: new Date().toISOString(),
      });
    } catch (err: any) {
      throw new Error("ViewPoint transfer failed: " + err.message);
    }

    await this.db
      .insertInto("manager_point_transactions")
      .values({
        manager_id: managerId,
        target_account_id: payload.targetAccountId,
        target_contact_id: payload.targetContactId,
        target_first_name: payload.targetFirstName || null,
        target_last_name: payload.targetLastName || null,
        target_email: payload.targetEmail || null,
        points: payload.points,
        notes: payload.notes,
        viewpoint_ref_id: vpRefId,
        entitlement_id: usedEntitlementIds,
        status: "SUCCESS",
        application: appName,
      })
      .execute();

    console.log(`[Points Transfer] ${payload.points} points transferred successfully from manager ${managerName} (${manager.viewpoint_account_id}) to member account ${payload.targetAccountId} across entitlements [${usedEntitlementIds}]. Initiator: ${managerName} (${managerEmail}), App: ${appName}, VP Ref: ${vpRefId}`);

    return { success: true, vpRefId };
  }

  async getManagerTransactions(managerId: string, limit = 5, offset = 0, search?: string) {
    let query = this.db
      .selectFrom("manager_point_transactions")
      .where("manager_id", "=", managerId);

    if (search) {
      const s = `%${search.toLowerCase()}%`;
      query = query.where((eb) =>
        eb.or([
          eb("target_account_id", "ilike", s),
          eb("target_contact_id", "ilike", s),
          eb("notes", "ilike", s),
          eb("viewpoint_ref_id", "ilike", s),
        ])
      );
    }

    const [transactions, stats] = await Promise.all([
      query
        .selectAll()
        .orderBy("created_at", "desc")
        .limit(limit)
        .offset(offset)
        .execute(),
      query
        .select([
          sql<number>`count(*)`.as("total"),
          sql<number>`sum(points)`.as("totalPoints"),
          sql<number>`count(distinct target_contact_id)`.as("totalUsers"),
        ])
        .executeTakeFirst(),
    ]);

    return {
      transactions,
      total: Number(stats?.total || 0),
      totalPoints: Number(stats?.totalPoints || 0),
      totalUsers: Number(stats?.totalUsers || 0),
    };
  }

  async updateManager(managerId: string, payload: {
    first_name?: string;
    last_name?: string;
    email?: string;
    service_center?: string;
    viewpoint_account_id?: string | null;
    viewpoint_contact_id?: string | null;
    viewpoint_club?: string | null;
    viewpoint_is_active?: boolean | null;
  }, ownerId?: string) {
    await this.assertManagerOwnership(managerId, ownerId);

    const updateData: any = {};
    if (payload.first_name !== undefined) updateData.first_name = payload.first_name;
    if (payload.last_name !== undefined) updateData.last_name = payload.last_name;
    if (payload.email !== undefined) updateData.email = payload.email;
    if (payload.service_center !== undefined) updateData.service_center = payload.service_center;
    if (payload.viewpoint_account_id !== undefined) updateData.viewpoint_account_id = payload.viewpoint_account_id;
    if (payload.viewpoint_contact_id !== undefined) updateData.viewpoint_contact_id = payload.viewpoint_contact_id;
    if (payload.viewpoint_club !== undefined) updateData.viewpoint_club = payload.viewpoint_club;
    if (payload.viewpoint_is_active !== undefined) updateData.viewpoint_is_active = payload.viewpoint_is_active;

    await this.db
      .updateTable("console_users")
      .set(updateData)
      .where("id", "=", managerId)
      .execute();

    return { success: true };
  }

  async deleteManager(managerId: string, ownerId?: string) {
    await this.assertManagerOwnership(managerId, ownerId);

    await this.db
      .updateTable("console_users")
      .set({
        is_deleted: true,
        is_active: false,
      })
      .where("id", "=", managerId)
      .execute();
    return { success: true };
  }

  async restoreManager(managerId: string, ownerId?: string) {
    await this.assertManagerOwnership(managerId, ownerId);

    await this.db
      .updateTable("console_users")
      .set({
        is_deleted: false,
        is_active: true,
      })
      .where("id", "=", managerId)
      .execute();
    return { success: true };
  }

  async getManagerHistory(managerId: string, ownerId?: string) {
    await this.assertManagerOwnership(managerId, ownerId);

    const received = await this.db
      .selectFrom("point_transactions")
      .leftJoin("console_users as initiator", "initiator.id", "point_transactions.initiator_id")
      .select([
        "point_transactions.id",
        "point_transactions.source_holding_account",
        "point_transactions.points",
        "point_transactions.created_at",
        "point_transactions.notes",
        "point_transactions.viewpoint_ref_id",
        "point_transactions.status",
        "point_transactions.application",
        "initiator.first_name as initiator_first_name",
        "initiator.last_name as initiator_last_name",
      ])
      .where("target_manager_id", "=", managerId)
      .orderBy("point_transactions.created_at", "desc")
      .execute();

    const sent = await this.db
      .selectFrom("manager_point_transactions")
      .select([
        "id",
        "target_account_id",
        "target_contact_id",
        "target_first_name",
        "target_last_name",
        "target_email",
        "points",
        "created_at",
        "notes",
        "viewpoint_ref_id",
        "status",
        "application",
      ])
      .where("manager_id", "=", managerId)
      .orderBy("created_at", "desc")
      .execute();

    return { received, sent };
  }
}