// Store 抽象 + InMemoryStore（テスト/ローカル用, CF不要）
import type {
  Broadcast,
  BroadcastSend,
  ExchangeItem,
  ExchangeRedemption,
  Hook,
  List,
  Lottery,
  LotteryDraw,
  LotteryPrize,
  PointEvent,
  PointKind,
  SmsStatus,
  StepCampaign,
  StepChannel,
  StepEmail,
  StepEnrollment,
  Subscriber,
  SubscriberStatus,
  TrackedLink,
} from "./types.js";
import { normalizeEmail, randomToken, smsOnlyEmailFor } from "./util.js";

export interface Store {
  // --- subscribers ---
  upsertSubscriber(
    email: string,
    name: string | null,
    source: string | null,
  ): Promise<{ subscriber: Subscriber; created: boolean }>;
  /**
   * 電話番号だけで登録する（SMS専用読者・v5）。
   * 既に同じ番号の購読者がいればそれを返す。新規作成時は email に
   * 番号から作った到達不能なダミーアドレス（@sms.invalid）を入れる。
   */
  upsertSubscriberByPhone(
    phoneE164: string,
    name: string | null,
    source: string | null,
    now: number,
  ): Promise<{ subscriber: Subscriber; created: boolean }>;
  listSubscribers(): Promise<Subscriber[]>;
  getSubscriber(id: number): Promise<Subscriber | null>;
  getSubscriberByToken(token: string): Promise<Subscriber | null>;
  setSubscriberStatus(id: number, status: SubscriberStatus): Promise<void>;
  setSubscriberStatusByEmail(
    email: string,
    status: SubscriberStatus,
  ): Promise<void>;
  /**
   * 電話番号とSMS同意を設定する（v4）。
   * consent=true のときだけ sms_status='active' にし、同意日時/経路を記録する
   * （番号の保有だけでは送信対象にしない = 特電法のオプトイン原則）。
   * 他の購読者が同じ番号を使っている場合は ok=false（番号は一意）。
   */
  setSubscriberPhone(
    id: number,
    phone: string | null,
    opts: { consent?: boolean; source?: string | null; now: number },
  ): Promise<{ ok: boolean; error?: string }>;
  /** SMSの配信可否だけを変更する（メールの status には影響しない） */
  setSmsStatus(id: number, status: SmsStatus, now: number): Promise<void>;

  // --- lists ---
  createList(name: string): Promise<List>;
  listLists(): Promise<List[]>;
  getList(id: number): Promise<List | null>;
  addToList(listId: number, subscriberId: number): Promise<void>;
  removeFromList(listId: number, subscriberId: number): Promise<void>;
  activeSubscribersInList(listId: number): Promise<Subscriber[]>;
  subscribersInList(listId: number): Promise<Subscriber[]>;

  // --- broadcasts ---
  createBroadcast(input: {
    name: string;
    subject: string;
    body_html: string;
    list_id: number;
    channel?: StepChannel;
    body_text?: string | null;
  }): Promise<Broadcast>;
  listBroadcasts(): Promise<Broadcast[]>;
  getBroadcast(id: number): Promise<Broadcast | null>;
  scheduleBroadcast(id: number, scheduledAt: number): Promise<boolean>;
  approveBroadcast(id: number): Promise<void>;
  cancelBroadcast(id: number): Promise<boolean>; // scheduled のときのみ true
  /** scheduled かつ scheduled_at<=now を sending へ原子的に遷移し返す（楽観ロック） */
  claimDueBroadcasts(now: number): Promise<Broadcast[]>;
  finishBroadcast(id: number, sentAt: number): Promise<void>;
  ensureBroadcastSends(
    broadcastId: number,
    subscriberIds: number[],
  ): Promise<void>;
  pendingBroadcastSends(
    broadcastId: number,
    limit: number,
  ): Promise<BroadcastSend[]>;
  countPendingBroadcastSends(broadcastId: number): Promise<number>;
  markBroadcastSend(
    id: number,
    status: BroadcastSend["status"],
    resendId: string | null,
    error: string | null,
    sentAt: number | null,
  ): Promise<void>;
  listBroadcastSends(broadcastId: number): Promise<BroadcastSend[]>;

  // --- step campaigns ---
  createCampaign(input: {
    name: string;
    list_id: number;
    trigger?: string;
    channel?: StepChannel;
  }): Promise<StepCampaign>;
  /** シナリオごとの「メール何通 / SMS何通」（一覧の振り分けと混在バッジに使う） */
  campaignChannelCounts(): Promise<Map<number, { email: number; sms: number }>>;
  listCampaigns(): Promise<StepCampaign[]>;
  getCampaign(id: number): Promise<StepCampaign | null>;
  updateCampaign(
    id: number,
    patch: Partial<Pick<StepCampaign, "name" | "status">>,
  ): Promise<void>;
  addStepEmail(input: {
    campaign_id: number;
    step_order: number;
    delay_minutes: number;
    subject: string;
    body_html: string;
    timing_type?: StepEmail["timing_type"];
    delay_days?: number;
    send_time?: string | null;
    channel?: StepChannel;
    body_text?: string | null;
  }): Promise<StepEmail>;
  updateStepEmail(
    id: number,
    patch: Partial<
      Pick<
        StepEmail,
        | "subject"
        | "body_html"
        | "delay_minutes"
        | "timing_type"
        | "delay_days"
        | "send_time"
        | "channel"
        | "body_text"
      >
    >,
  ): Promise<void>;
  deleteStepEmail(id: number): Promise<void>;
  /** orderedIds の並び順で step_order を 0..n-1 に振り直す */
  reorderStepEmails(campaignId: number, orderedIds: number[]): Promise<void>;
  listStepEmails(campaignId: number): Promise<StepEmail[]>;
  getStepEmail(id: number): Promise<StepEmail | null>;
  /** step_email_id → 送信記録数（管理画面のレポート用） */
  countStepSends(campaignId: number): Promise<Map<number, number>>;

  // --- enrollments ---
  enroll(
    campaignId: number,
    subscriberId: number,
    nextSendAt: number,
    now: number,
  ): Promise<{ enrollment: StepEnrollment; created: boolean }>;
  dueEnrollments(now: number, limit: number): Promise<StepEnrollment[]>;
  advanceEnrollment(
    id: number,
    currentStep: number,
    nextSendAt: number,
    status: StepEnrollment["status"],
  ): Promise<void>;
  listEnrollments(campaignId: number): Promise<StepEnrollment[]>;
  /** (enrollment,step) が送信記録済みか（冪等判定） */
  hasStepSend(enrollmentId: number, stepEmailId: number): Promise<boolean>;
  /** 送信成功/ドライ時に記録（一意制約で二重記録なし） */
  recordStepSend(
    enrollmentId: number,
    stepEmailId: number,
    resendId: string | null,
  ): Promise<void>;

  // --- hooks ---
  createHook(input: {
    list_id: number;
    campaign_id: number | null;
    name: string | null;
  }): Promise<Hook>;
  getHook(token: string): Promise<Hook | null>;
  listHooks(): Promise<Hook[]>;

  // --- audit / settings ---
  audit(action: string, detail: string): Promise<void>;
  listAudit(limit: number): Promise<{ ts: number; action: string; detail: string }[]>;
  getDailySendCount(day: string): Promise<number>;
  incrDailySendCount(day: string, n: number): Promise<void>;
  /** SMSの日次送信数（メール枠と分離。コスト暴走防止の生命線） */
  getSmsDailySendCount(day: string): Promise<number>;
  incrSmsDailySendCount(day: string, n: number): Promise<void>;
  getSetting(key: string): Promise<string | null>;
  setSetting(key: string, value: string): Promise<void>;

  // --- points（台帳。dedupe_key で冪等付与） ---
  /** created=false は dedupe_key 重複（既に付与済み） */
  addPointEvent(
    subscriberId: number,
    points: number,
    kind: PointKind,
    dedupeKey: string | null,
    note: string | null,
  ): Promise<{ created: boolean }>;
  /** 残高>=cost のときだけ cost pt を1文で原子的に消費。ok=false は残高不足（同時実行でも負残高にならない） */
  trySpendPoints(
    subscriberId: number,
    cost: number,
    kind: PointKind,
    note: string | null,
  ): Promise<{ ok: boolean }>;
  pointBalance(subscriberId: number): Promise<number>;
  listPointEvents(subscriberId: number, limit: number): Promise<PointEvent[]>;
  /** 全購読者の残高（管理画面用） */
  listPointBalances(): Promise<{ subscriber_id: number; balance: number }[]>;

  // --- tracked links（クリック計測） ---
  getOrCreateTrackedLink(scope: "bc" | "step", refId: number, url: string): Promise<TrackedLink>;
  getTrackedLink(id: number): Promise<TrackedLink | null>;
  /** その購読者宛に当該配信が実際に送信されたか（開封/クリックの不正加点を防ぐ照合） */
  wasBroadcastSentTo(broadcastId: number, subscriberId: number): Promise<boolean>;
  wasStepSentTo(stepEmailId: number, subscriberId: number): Promise<boolean>;

  // --- lotteries（抽選会） ---
  createLottery(input: {
    name: string;
    description: string | null;
    cost_points: number;
  }): Promise<Lottery>;
  updateLottery(
    id: number,
    patch: Partial<Pick<Lottery, "name" | "description" | "cost_points" | "status">>,
  ): Promise<void>;
  listLotteries(): Promise<Lottery[]>;
  getLottery(id: number): Promise<Lottery | null>;
  addPrize(input: {
    lottery_id: number;
    name: string;
    description: string | null;
    probability: number;
    max_winners: number;
  }): Promise<LotteryPrize>;
  updatePrize(
    id: number,
    patch: Partial<Pick<LotteryPrize, "name" | "description" | "probability" | "max_winners" | "active">>,
  ): Promise<void>;
  getPrize(id: number): Promise<LotteryPrize | null>;
  listPrizes(lotteryId: number): Promise<LotteryPrize[]>;
  /** 景品IDごとの当選数（当選本数上限の判定用） */
  prizeWinCounts(lotteryId: number): Promise<Map<number, number>>;
  recordDraw(
    lotteryId: number,
    subscriberId: number,
    prizeId: number | null,
    pointsSpent: number,
  ): Promise<LotteryDraw>;
  /** 当選本数上限を1文で原子的に判定して記録。上限到達済みなら はずれ(prize_id=null) で記録し won=false を返す */
  recordDrawCapped(
    lotteryId: number,
    subscriberId: number,
    prize: { id: number; max_winners: number } | null,
    pointsSpent: number,
  ): Promise<{ draw: LotteryDraw; won: boolean }>;
  listDraws(lotteryId: number | null, limit: number): Promise<LotteryDraw[]>;
  setDrawFulfilled(id: number, fulfilled: boolean): Promise<void>;

  // --- exchange（景品交換） ---
  createExchangeItem(input: {
    name: string;
    description: string | null;
    cost_points: number;
    stock: number;
  }): Promise<ExchangeItem>;
  updateExchangeItem(
    id: number,
    patch: Partial<Pick<ExchangeItem, "name" | "description" | "cost_points" | "stock" | "active">>,
  ): Promise<void>;
  listExchangeItems(): Promise<ExchangeItem[]>;
  getExchangeItem(id: number): Promise<ExchangeItem | null>;
  /** canceled 以外の申請数（在庫判定用） */
  countRedemptions(itemId: number): Promise<number>;
  recordRedemption(
    itemId: number,
    subscriberId: number,
    pointsSpent: number,
  ): Promise<ExchangeRedemption>;
  /** 在庫を1文で原子的に確保して申請を記録。在庫切れなら null（stock<0 は無制限） */
  tryReserveRedemption(
    item: { id: number; cost_points: number; stock: number },
    subscriberId: number,
  ): Promise<ExchangeRedemption | null>;
  getRedemption(id: number): Promise<ExchangeRedemption | null>;
  listRedemptions(limit: number): Promise<ExchangeRedemption[]>;
  setRedemptionStatus(id: number, status: ExchangeRedemption["status"]): Promise<void>;
}

// ===================== InMemoryStore =====================

export class InMemoryStore implements Store {
  private seq = 1;
  private subscribers: Subscriber[] = [];
  private lists: List[] = [];
  private listMembers: { list_id: number; subscriber_id: number }[] = [];
  private broadcasts: Broadcast[] = [];
  private bcSends: BroadcastSend[] = [];
  private campaigns: StepCampaign[] = [];
  private stepEmails: StepEmail[] = [];
  private enrollments: StepEnrollment[] = [];
  private stepSends: { id: number; enrollment_id: number; step_email_id: number }[] = [];
  private hooks: Hook[] = [];
  private auditRows: { ts: number; action: string; detail: string }[] = [];
  private daily = new Map<string, number>();
  private smsDaily = new Map<string, number>();
  private settings = new Map<string, string>();
  private pointEvents: PointEvent[] = [];
  private trackedLinks: TrackedLink[] = [];
  private lotteries: Lottery[] = [];
  private prizes: LotteryPrize[] = [];
  private draws: LotteryDraw[] = [];
  private exchangeItems: ExchangeItem[] = [];
  private redemptions: ExchangeRedemption[] = [];
  private now: () => number;

  constructor(nowFn: () => number = () => Date.now()) {
    this.now = nowFn;
  }
  private id() {
    return this.seq++;
  }

  async upsertSubscriber(email: string, name: string | null, source: string | null) {
    const e = normalizeEmail(email);
    const existing = this.subscribers.find((s) => s.email === e);
    if (existing) {
      if (name && !existing.name) existing.name = name;
      return { subscriber: existing, created: false };
    }
    const sub: Subscriber = {
      id: this.id(),
      email: e,
      name,
      status: "active",
      source,
      unsub_token: randomToken(),
      created_at: this.now(),
      phone: null,
      sms_status: "none",
      sms_consent_at: null,
      sms_consent_source: null,
    };
    this.subscribers.push(sub);
    return { subscriber: sub, created: true };
  }
  async upsertSubscriberByPhone(
    phoneE164: string,
    name: string | null,
    source: string | null,
    now: number,
  ) {
    const existing = this.subscribers.find((s) => s.phone === phoneE164);
    if (existing) {
      if (name && !existing.name) existing.name = name;
      return { subscriber: existing, created: false };
    }
    const sub: Subscriber = {
      id: this.id(),
      email: smsOnlyEmailFor(phoneE164),
      name,
      status: "active",
      source,
      unsub_token: randomToken(),
      created_at: now,
      phone: phoneE164,
      sms_status: "none", // 同意は setSubscriberPhone / setSmsStatus で明示的に付ける
      sms_consent_at: null,
      sms_consent_source: null,
    };
    this.subscribers.push(sub);
    return { subscriber: sub, created: true };
  }
  async listSubscribers() {
    return [...this.subscribers].sort((a, b) => b.created_at - a.created_at);
  }
  async getSubscriber(id: number) {
    return this.subscribers.find((s) => s.id === id) ?? null;
  }
  async getSubscriberByToken(token: string) {
    return this.subscribers.find((s) => s.unsub_token === token) ?? null;
  }
  async setSubscriberStatus(id: number, status: SubscriberStatus) {
    const s = this.subscribers.find((x) => x.id === id);
    if (s) s.status = status;
  }
  async setSubscriberStatusByEmail(email: string, status: SubscriberStatus) {
    const s = this.subscribers.find((x) => x.email === normalizeEmail(email));
    if (s) s.status = status;
  }
  async setSubscriberPhone(
    id: number,
    phone: string | null,
    opts: { consent?: boolean; source?: string | null; now: number },
  ) {
    const s = this.subscribers.find((x) => x.id === id);
    if (!s) return { ok: false, error: "subscriber not found" };
    if (phone && this.subscribers.some((x) => x.id !== id && x.phone === phone))
      return { ok: false, error: "phone already used by another subscriber" };
    s.phone = phone;
    if (!phone) {
      // 番号を消したらSMSの送信対象からも外す
      s.sms_status = "none";
      return { ok: true };
    }
    if (opts.consent === true) {
      s.sms_status = "active";
      s.sms_consent_at = opts.now;
      s.sms_consent_source = opts.source ?? null;
    } else if (s.sms_status !== "unsubscribed") {
      // 同意が明示されない限り送信対象にしない（停止済みは尊重して維持）
      s.sms_status = "none";
    }
    return { ok: true };
  }
  async setSmsStatus(id: number, status: SmsStatus, now: number) {
    const s = this.subscribers.find((x) => x.id === id);
    if (!s) return;
    s.sms_status = status;
    if (status === "active") {
      s.sms_consent_at = now;
      if (!s.sms_consent_source) s.sms_consent_source = "manual";
    }
  }

  async createList(name: string) {
    const l: List = { id: this.id(), name, created_at: this.now() };
    this.lists.push(l);
    return l;
  }
  async listLists() {
    return [...this.lists];
  }
  async getList(id: number) {
    return this.lists.find((l) => l.id === id) ?? null;
  }
  async addToList(listId: number, subscriberId: number) {
    if (
      !this.listMembers.find(
        (m) => m.list_id === listId && m.subscriber_id === subscriberId,
      )
    )
      this.listMembers.push({ list_id: listId, subscriber_id: subscriberId });
  }
  async removeFromList(listId: number, subscriberId: number) {
    this.listMembers = this.listMembers.filter(
      (m) => !(m.list_id === listId && m.subscriber_id === subscriberId),
    );
  }
  async subscribersInList(listId: number) {
    const ids = this.listMembers
      .filter((m) => m.list_id === listId)
      .map((m) => m.subscriber_id);
    return this.subscribers.filter((s) => ids.includes(s.id));
  }
  async activeSubscribersInList(listId: number) {
    return (await this.subscribersInList(listId)).filter(
      (s) => s.status === "active",
    );
  }

  async createBroadcast(input: {
    name: string;
    subject: string;
    body_html: string;
    list_id: number;
    channel?: StepChannel;
    body_text?: string | null;
  }) {
    const b: Broadcast = {
      id: this.id(),
      name: input.name,
      subject: input.subject,
      body_html: input.body_html,
      list_id: input.list_id,
      status: "draft",
      scheduled_at: null,
      approved: false,
      sent_at: null,
      created_at: this.now(),
      channel: input.channel ?? "email",
      body_text: input.body_text ?? null,
    };
    this.broadcasts.push(b);
    return b;
  }
  async listBroadcasts() {
    return [...this.broadcasts].sort((a, b) => b.created_at - a.created_at);
  }
  async getBroadcast(id: number) {
    return this.broadcasts.find((b) => b.id === id) ?? null;
  }
  async scheduleBroadcast(id: number, scheduledAt: number) {
    const b = this.broadcasts.find((x) => x.id === id);
    if (!b || (b.status !== "draft" && b.status !== "scheduled")) return false;
    b.status = "scheduled";
    b.scheduled_at = scheduledAt;
    return true;
  }
  async approveBroadcast(id: number) {
    const b = this.broadcasts.find((x) => x.id === id);
    if (b) b.approved = true;
  }
  async cancelBroadcast(id: number) {
    const b = this.broadcasts.find((x) => x.id === id);
    if (!b || b.status !== "scheduled") return false;
    b.status = "canceled";
    return true;
  }
  async claimDueBroadcasts(now: number) {
    const due = this.broadcasts.filter(
      (b) =>
        b.status === "scheduled" &&
        b.scheduled_at !== null &&
        b.scheduled_at <= now,
    );
    for (const b of due) b.status = "sending"; // 単一スレッドなので原子的
    return due.map((b) => ({ ...b }));
  }
  async finishBroadcast(id: number, sentAt: number) {
    const b = this.broadcasts.find((x) => x.id === id);
    if (b) {
      b.status = "sent";
      b.sent_at = sentAt;
    }
  }
  async ensureBroadcastSends(broadcastId: number, subscriberIds: number[]) {
    for (const sid of subscriberIds) {
      if (
        !this.bcSends.find(
          (s) => s.broadcast_id === broadcastId && s.subscriber_id === sid,
        )
      )
        this.bcSends.push({
          id: this.id(),
          broadcast_id: broadcastId,
          subscriber_id: sid,
          status: "pending",
          resend_id: null,
          error: null,
          sent_at: null,
        });
    }
  }
  async pendingBroadcastSends(broadcastId: number, limit: number) {
    // pending と failed（再試行対象）を「未送信」とみなす
    return this.bcSends
      .filter(
        (s) =>
          s.broadcast_id === broadcastId &&
          (s.status === "pending" || s.status === "failed"),
      )
      .slice(0, limit)
      .map((s) => ({ ...s }));
  }
  async countPendingBroadcastSends(broadcastId: number) {
    return this.bcSends.filter(
      (s) =>
        s.broadcast_id === broadcastId &&
        (s.status === "pending" || s.status === "failed"),
    ).length;
  }
  async markBroadcastSend(
    id: number,
    status: BroadcastSend["status"],
    resendId: string | null,
    error: string | null,
    sentAt: number | null,
  ) {
    const s = this.bcSends.find((x) => x.id === id);
    if (s) {
      s.status = status;
      s.resend_id = resendId;
      s.error = error;
      s.sent_at = sentAt;
    }
  }
  async listBroadcastSends(broadcastId: number) {
    return this.bcSends
      .filter((s) => s.broadcast_id === broadcastId)
      .map((s) => ({ ...s }));
  }

  async createCampaign(input: {
    name: string;
    list_id: number;
    trigger?: string;
    channel?: StepChannel;
  }) {
    const c: StepCampaign = {
      id: this.id(),
      name: input.name,
      list_id: input.list_id,
      trigger: input.trigger ?? "on_enroll",
      status: "active",
      created_at: this.now(),
      channel: input.channel ?? "email",
    };
    this.campaigns.push(c);
    return c;
  }
  async campaignChannelCounts() {
    const map = new Map<number, { email: number; sms: number }>();
    for (const s of this.stepEmails) {
      const cur = map.get(s.campaign_id) ?? { email: 0, sms: 0 };
      if ((s.channel ?? "email") === "sms") cur.sms++;
      else cur.email++;
      map.set(s.campaign_id, cur);
    }
    return map;
  }
  async listCampaigns() {
    return [...this.campaigns];
  }
  async getCampaign(id: number) {
    return this.campaigns.find((c) => c.id === id) ?? null;
  }
  async updateCampaign(
    id: number,
    patch: Partial<Pick<StepCampaign, "name" | "status">>,
  ) {
    const c = this.campaigns.find((x) => x.id === id);
    if (c) Object.assign(c, patch);
  }
  async addStepEmail(input: {
    campaign_id: number;
    step_order: number;
    delay_minutes: number;
    subject: string;
    body_html: string;
    timing_type?: StepEmail["timing_type"];
    delay_days?: number;
    send_time?: string | null;
    channel?: StepChannel;
    body_text?: string | null;
  }) {
    if (
      this.stepEmails.find(
        (s) =>
          s.campaign_id === input.campaign_id &&
          s.step_order === input.step_order,
      )
    )
      throw new Error("duplicate step_order");
    const s: StepEmail = {
      id: this.id(),
      campaign_id: input.campaign_id,
      step_order: input.step_order,
      delay_minutes: input.delay_minutes,
      subject: input.subject,
      body_html: input.body_html,
      timing_type: input.timing_type ?? "relative",
      delay_days: input.delay_days ?? 0,
      send_time: input.send_time ?? null,
      channel: input.channel ?? "email",
      body_text: input.body_text ?? null,
    };
    this.stepEmails.push(s);
    return s;
  }
  async updateStepEmail(
    id: number,
    patch: Partial<
      Pick<
        StepEmail,
        | "subject"
        | "body_html"
        | "delay_minutes"
        | "timing_type"
        | "delay_days"
        | "send_time"
        | "channel"
        | "body_text"
      >
    >,
  ) {
    const s = this.stepEmails.find((x) => x.id === id);
    if (s) Object.assign(s, patch);
  }
  async deleteStepEmail(id: number) {
    this.stepEmails = this.stepEmails.filter((s) => s.id !== id);
  }
  async reorderStepEmails(campaignId: number, orderedIds: number[]) {
    orderedIds.forEach((sid, idx) => {
      const s = this.stepEmails.find(
        (x) => x.id === sid && x.campaign_id === campaignId,
      );
      if (s) s.step_order = idx;
    });
  }
  async listStepEmails(campaignId: number) {
    return this.stepEmails
      .filter((s) => s.campaign_id === campaignId)
      .sort((a, b) => a.step_order - b.step_order);
  }
  async getStepEmail(id: number) {
    return this.stepEmails.find((s) => s.id === id) ?? null;
  }
  async countStepSends(campaignId: number) {
    const stepIds = new Set(
      this.stepEmails.filter((s) => s.campaign_id === campaignId).map((s) => s.id),
    );
    const map = new Map<number, number>();
    for (const ss of this.stepSends)
      if (stepIds.has(ss.step_email_id))
        map.set(ss.step_email_id, (map.get(ss.step_email_id) ?? 0) + 1);
    return map;
  }

  async enroll(
    campaignId: number,
    subscriberId: number,
    nextSendAt: number,
    now: number,
  ) {
    const existing = this.enrollments.find(
      (e) => e.campaign_id === campaignId && e.subscriber_id === subscriberId,
    );
    if (existing) return { enrollment: existing, created: false };
    const e: StepEnrollment = {
      id: this.id(),
      campaign_id: campaignId,
      subscriber_id: subscriberId,
      current_step: 0,
      status: "active",
      next_send_at: nextSendAt,
      enrolled_at: now,
    };
    this.enrollments.push(e);
    return { enrollment: e, created: true };
  }
  async dueEnrollments(now: number, limit: number) {
    return this.enrollments
      .filter((e) => e.status === "active" && e.next_send_at <= now)
      .slice(0, limit)
      .map((e) => ({ ...e }));
  }
  async advanceEnrollment(
    id: number,
    currentStep: number,
    nextSendAt: number,
    status: StepEnrollment["status"],
  ) {
    const e = this.enrollments.find((x) => x.id === id);
    if (e) {
      e.current_step = currentStep;
      e.next_send_at = nextSendAt;
      e.status = status;
    }
  }
  async listEnrollments(campaignId: number) {
    return this.enrollments
      .filter((e) => e.campaign_id === campaignId)
      .map((e) => ({ ...e }));
  }
  async hasStepSend(enrollmentId: number, stepEmailId: number) {
    return !!this.stepSends.find(
      (s) => s.enrollment_id === enrollmentId && s.step_email_id === stepEmailId,
    );
  }
  async recordStepSend(
    enrollmentId: number,
    stepEmailId: number,
    _resendId: string | null,
  ) {
    if (await this.hasStepSend(enrollmentId, stepEmailId)) return;
    this.stepSends.push({
      id: this.id(),
      enrollment_id: enrollmentId,
      step_email_id: stepEmailId,
    });
  }

  async createHook(input: {
    list_id: number;
    campaign_id: number | null;
    name: string | null;
  }) {
    const h: Hook = {
      id: this.id(),
      token: randomToken(),
      list_id: input.list_id,
      campaign_id: input.campaign_id,
      name: input.name,
      created_at: this.now(),
    };
    this.hooks.push(h);
    return h;
  }
  async getHook(token: string) {
    return this.hooks.find((h) => h.token === token) ?? null;
  }
  async listHooks() {
    return [...this.hooks];
  }

  async audit(action: string, detail: string) {
    this.auditRows.push({ ts: this.now(), action, detail });
  }
  async listAudit(limit: number) {
    return [...this.auditRows].reverse().slice(0, limit);
  }
  async getDailySendCount(day: string) {
    return this.daily.get(day) ?? 0;
  }
  async incrDailySendCount(day: string, n: number) {
    this.daily.set(day, (this.daily.get(day) ?? 0) + n);
  }
  async getSmsDailySendCount(day: string) {
    return this.smsDaily.get(day) ?? 0;
  }
  async incrSmsDailySendCount(day: string, n: number) {
    this.smsDaily.set(day, (this.smsDaily.get(day) ?? 0) + n);
  }
  async getSetting(key: string) {
    return this.settings.get(key) ?? null;
  }
  async setSetting(key: string, value: string) {
    this.settings.set(key, value);
  }

  // --- points ---
  async addPointEvent(
    subscriberId: number,
    points: number,
    kind: PointKind,
    dedupeKey: string | null,
    note: string | null,
  ) {
    if (dedupeKey && this.pointEvents.find((e) => e.dedupe_key === dedupeKey))
      return { created: false };
    this.pointEvents.push({
      id: this.id(),
      subscriber_id: subscriberId,
      points,
      kind,
      dedupe_key: dedupeKey,
      note,
      created_at: this.now(),
    });
    return { created: true };
  }
  async trySpendPoints(subscriberId: number, cost: number, kind: PointKind, note: string | null) {
    // 消費額は必ず正（cost<=0 の不正/レガシー設定でポイントが増えるのを防ぐ）
    if (!(cost > 0)) return { ok: false };
    // 残高の読み取りと消費の書き込みの間に await を挟まず同期実行し、原子的にする
    // （await を挟むと並行リクエストが読み取り〜書き込みの間に割り込み、二重消費・負残高になる）
    const bal = this.pointEvents
      .filter((e) => e.subscriber_id === subscriberId)
      .reduce((a, e) => a + e.points, 0);
    if (bal < cost) return { ok: false };
    this.pointEvents.push({
      id: this.id(),
      subscriber_id: subscriberId,
      points: -cost,
      kind,
      dedupe_key: null,
      note,
      created_at: this.now(),
    });
    return { ok: true };
  }
  async pointBalance(subscriberId: number) {
    return this.pointEvents
      .filter((e) => e.subscriber_id === subscriberId)
      .reduce((a, e) => a + e.points, 0);
  }
  async listPointEvents(subscriberId: number, limit: number) {
    return this.pointEvents
      .filter((e) => e.subscriber_id === subscriberId)
      .sort((a, b) => b.id - a.id)
      .slice(0, limit)
      .map((e) => ({ ...e }));
  }
  async listPointBalances() {
    const map = new Map<number, number>();
    for (const e of this.pointEvents)
      map.set(e.subscriber_id, (map.get(e.subscriber_id) ?? 0) + e.points);
    return [...map.entries()]
      .map(([subscriber_id, balance]) => ({ subscriber_id, balance }))
      .sort((a, b) => b.balance - a.balance);
  }

  // --- tracked links ---
  async getOrCreateTrackedLink(scope: "bc" | "step", refId: number, url: string) {
    const found = this.trackedLinks.find(
      (l) => l.scope === scope && l.ref_id === refId && l.url === url,
    );
    if (found) return found;
    const link: TrackedLink = {
      id: this.id(),
      scope,
      ref_id: refId,
      url,
      created_at: this.now(),
    };
    this.trackedLinks.push(link);
    return link;
  }
  async getTrackedLink(id: number) {
    return this.trackedLinks.find((l) => l.id === id) ?? null;
  }
  async wasBroadcastSentTo(broadcastId: number, subscriberId: number) {
    // 実際に送信済み(sent)の行のみ。pending/skipped/failed では加点しない
    return this.bcSends.some(
      (s) => s.broadcast_id === broadcastId && s.subscriber_id === subscriberId && s.status === "sent",
    );
  }
  async wasStepSentTo(stepEmailId: number, subscriberId: number) {
    return this.stepSends.some(
      (ss) =>
        ss.step_email_id === stepEmailId &&
        this.enrollments.some((e) => e.id === ss.enrollment_id && e.subscriber_id === subscriberId),
    );
  }

  // --- lotteries ---
  async createLottery(input: { name: string; description: string | null; cost_points: number }) {
    const l: Lottery = {
      id: this.id(),
      name: input.name,
      description: input.description,
      cost_points: input.cost_points,
      status: "open",
      created_at: this.now(),
    };
    this.lotteries.push(l);
    return l;
  }
  async updateLottery(
    id: number,
    patch: Partial<Pick<Lottery, "name" | "description" | "cost_points" | "status">>,
  ) {
    const l = this.lotteries.find((x) => x.id === id);
    if (!l) return;
    // 既知カラムのみ反映（余分なキーで行を汚染しない = D1と挙動一致）
    if (patch.name !== undefined) l.name = patch.name;
    if (patch.description !== undefined) l.description = patch.description;
    if (patch.cost_points !== undefined) l.cost_points = patch.cost_points;
    if (patch.status !== undefined) l.status = patch.status;
  }
  async listLotteries() {
    return [...this.lotteries].sort((a, b) => b.id - a.id);
  }
  async getLottery(id: number) {
    return this.lotteries.find((l) => l.id === id) ?? null;
  }
  async addPrize(input: {
    lottery_id: number;
    name: string;
    description: string | null;
    probability: number;
    max_winners: number;
  }) {
    const p: LotteryPrize = {
      id: this.id(),
      ...input,
      active: true,
      created_at: this.now(),
    };
    this.prizes.push(p);
    return p;
  }
  async getPrize(id: number) {
    const p = this.prizes.find((x) => x.id === id);
    return p ? { ...p } : null;
  }
  async updatePrize(
    id: number,
    patch: Partial<Pick<LotteryPrize, "name" | "description" | "probability" | "max_winners" | "active">>,
  ) {
    const p = this.prizes.find((x) => x.id === id);
    if (!p) return;
    if (patch.name !== undefined) p.name = patch.name;
    if (patch.description !== undefined) p.description = patch.description;
    if (patch.probability !== undefined) p.probability = patch.probability;
    if (patch.max_winners !== undefined) p.max_winners = patch.max_winners;
    if (patch.active !== undefined) p.active = patch.active;
  }
  async listPrizes(lotteryId: number) {
    return this.prizes.filter((p) => p.lottery_id === lotteryId).map((p) => ({ ...p }));
  }
  async prizeWinCounts(lotteryId: number) {
    const map = new Map<number, number>();
    for (const d of this.draws)
      if (d.lottery_id === lotteryId && d.prize_id !== null)
        map.set(d.prize_id, (map.get(d.prize_id) ?? 0) + 1);
    return map;
  }
  async recordDraw(
    lotteryId: number,
    subscriberId: number,
    prizeId: number | null,
    pointsSpent: number,
  ) {
    const d: LotteryDraw = {
      id: this.id(),
      lottery_id: lotteryId,
      subscriber_id: subscriberId,
      prize_id: prizeId,
      points_spent: pointsSpent,
      fulfilled: false,
      drawn_at: this.now(),
    };
    this.draws.push(d);
    return d;
  }
  async recordDrawCapped(
    lotteryId: number,
    subscriberId: number,
    prize: { id: number; max_winners: number } | null,
    pointsSpent: number,
  ) {
    let finalPrizeId: number | null = null;
    let won = false;
    if (prize) {
      if (prize.max_winners > 0) {
        const already = this.draws.filter(
          (d) => d.lottery_id === lotteryId && d.prize_id === prize.id,
        ).length;
        if (already < prize.max_winners) {
          finalPrizeId = prize.id;
          won = true;
        }
      } else {
        finalPrizeId = prize.id;
        won = true;
      }
    }
    const draw = await this.recordDraw(lotteryId, subscriberId, finalPrizeId, pointsSpent);
    return { draw, won };
  }
  async listDraws(lotteryId: number | null, limit: number) {
    return this.draws
      .filter((d) => lotteryId === null || d.lottery_id === lotteryId)
      .sort((a, b) => b.id - a.id)
      .slice(0, limit)
      .map((d) => ({ ...d }));
  }
  async setDrawFulfilled(id: number, fulfilled: boolean) {
    const d = this.draws.find((x) => x.id === id);
    if (d) d.fulfilled = fulfilled;
  }

  // --- exchange ---
  async createExchangeItem(input: {
    name: string;
    description: string | null;
    cost_points: number;
    stock: number;
  }) {
    const item: ExchangeItem = {
      id: this.id(),
      ...input,
      active: true,
      created_at: this.now(),
    };
    this.exchangeItems.push(item);
    return item;
  }
  async updateExchangeItem(
    id: number,
    patch: Partial<Pick<ExchangeItem, "name" | "description" | "cost_points" | "stock" | "active">>,
  ) {
    const item = this.exchangeItems.find((x) => x.id === id);
    if (!item) return;
    if (patch.name !== undefined) item.name = patch.name;
    if (patch.description !== undefined) item.description = patch.description;
    if (patch.cost_points !== undefined) item.cost_points = patch.cost_points;
    if (patch.stock !== undefined) item.stock = patch.stock;
    if (patch.active !== undefined) item.active = patch.active;
  }
  async listExchangeItems() {
    return [...this.exchangeItems].sort((a, b) => b.id - a.id);
  }
  async getExchangeItem(id: number) {
    return this.exchangeItems.find((x) => x.id === id) ?? null;
  }
  async countRedemptions(itemId: number) {
    return this.redemptions.filter((r) => r.item_id === itemId && r.status !== "canceled").length;
  }
  async recordRedemption(itemId: number, subscriberId: number, pointsSpent: number) {
    const r: ExchangeRedemption = {
      id: this.id(),
      item_id: itemId,
      subscriber_id: subscriberId,
      points_spent: pointsSpent,
      status: "pending",
      created_at: this.now(),
    };
    this.redemptions.push(r);
    return r;
  }
  async tryReserveRedemption(
    item: { id: number; cost_points: number; stock: number },
    subscriberId: number,
  ) {
    if (item.stock >= 0) {
      const used = this.redemptions.filter(
        (r) => r.item_id === item.id && r.status !== "canceled",
      ).length;
      if (used >= item.stock) return null;
    }
    return this.recordRedemption(item.id, subscriberId, item.cost_points);
  }
  async getRedemption(id: number) {
    return this.redemptions.find((r) => r.id === id) ?? null;
  }
  async listRedemptions(limit: number) {
    return [...this.redemptions]
      .sort((a, b) => b.id - a.id)
      .slice(0, limit)
      .map((r) => ({ ...r }));
  }
  async setRedemptionStatus(id: number, status: ExchangeRedemption["status"]) {
    const r = this.redemptions.find((x) => x.id === id);
    if (r) r.status = status;
  }
}
