// D1Store — Cloudflare D1 実装（本番）。テストは InMemoryStore を使用。
import type { Store } from "./store.js";
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";

function toSub(r: any): Subscriber {
  return {
    id: r.id,
    email: r.email,
    name: r.name ?? null,
    status: r.status,
    source: r.source ?? null,
    unsub_token: r.unsub_token,
    created_at: r.created_at,
    // v4: SMS（未マイグレーションのDBでも既定値で動くようにフォールバック）
    phone: r.phone ?? null,
    sms_status: r.sms_status ?? "none",
    sms_consent_at: r.sms_consent_at ?? null,
    sms_consent_source: r.sms_consent_source ?? null,
  };
}
function toBc(r: any): Broadcast {
  return {
    id: r.id,
    name: r.name,
    subject: r.subject,
    body_html: r.body_html,
    list_id: r.list_id,
    status: r.status,
    scheduled_at: r.scheduled_at ?? null,
    approved: !!r.approved,
    sent_at: r.sent_at ?? null,
    created_at: r.created_at,
    // v5（未マイグレーションのDBでも既定値で動くようフォールバック）
    channel: r.channel ?? "email",
    body_text: r.body_text ?? null,
  };
}

export class D1Store implements Store {
  constructor(
    private db: D1Database,
    private now: () => number = () => Date.now(),
  ) {}

  async upsertSubscriber(email: string, name: string | null, source: string | null) {
    const e = normalizeEmail(email);
    const found = await this.db
      .prepare("SELECT * FROM subscribers WHERE email=?")
      .bind(e)
      .first<any>();
    if (found) {
      if (name && !found.name)
        await this.db
          .prepare("UPDATE subscribers SET name=? WHERE id=?")
          .bind(name, found.id)
          .run();
      return { subscriber: toSub(found), created: false };
    }
    const token = randomToken();
    const ts = this.now();
    const res = await this.db
      .prepare(
        "INSERT INTO subscribers (email,name,status,source,unsub_token,created_at) VALUES (?,?,?,?,?,?)",
      )
      .bind(e, name, "active", source, token, ts)
      .run();
    const id = res.meta.last_row_id as number;
    return {
      subscriber: {
        id,
        email: e,
        name,
        status: "active" as SubscriberStatus,
        source,
        unsub_token: token,
        created_at: ts,
        phone: null,
        sms_status: "none" as SmsStatus,
        sms_consent_at: null,
        sms_consent_source: null,
      },
      created: true,
    };
  }
  async upsertSubscriberByPhone(
    phoneE164: string,
    name: string | null,
    source: string | null,
    now: number,
  ) {
    const found = await this.db
      .prepare("SELECT * FROM subscribers WHERE phone=?")
      .bind(phoneE164)
      .first<any>();
    if (found) {
      if (name && !found.name)
        await this.db
          .prepare("UPDATE subscribers SET name=? WHERE id=?")
          .bind(name, found.id)
          .run();
      return { subscriber: toSub(found), created: false };
    }
    const token = randomToken();
    const email = smsOnlyEmailFor(phoneE164);
    const res = await this.db
      .prepare(
        "INSERT INTO subscribers (email,name,status,source,unsub_token,created_at,phone,sms_status) VALUES (?,?,'active',?,?,?,?,'none')",
      )
      .bind(email, name, source, token, now, phoneE164)
      .run();
    return {
      subscriber: {
        id: res.meta.last_row_id as number,
        email,
        name,
        status: "active" as SubscriberStatus,
        source,
        unsub_token: token,
        created_at: now,
        phone: phoneE164,
        sms_status: "none" as SmsStatus,
        sms_consent_at: null,
        sms_consent_source: null,
      },
      created: true,
    };
  }
  async listSubscribers() {
    const r = await this.db
      .prepare("SELECT * FROM subscribers ORDER BY created_at DESC")
      .all<any>();
    return r.results.map(toSub);
  }
  async getSubscriber(id: number) {
    const r = await this.db.prepare("SELECT * FROM subscribers WHERE id=?").bind(id).first<any>();
    return r ? toSub(r) : null;
  }
  async getSubscriberByToken(token: string) {
    const r = await this.db
      .prepare("SELECT * FROM subscribers WHERE unsub_token=?")
      .bind(token)
      .first<any>();
    return r ? toSub(r) : null;
  }
  async setSubscriberStatus(id: number, status: SubscriberStatus) {
    await this.db.prepare("UPDATE subscribers SET status=? WHERE id=?").bind(status, id).run();
  }
  async setSubscriberStatusByEmail(email: string, status: SubscriberStatus) {
    await this.db
      .prepare("UPDATE subscribers SET status=? WHERE email=?")
      .bind(status, normalizeEmail(email))
      .run();
  }
  async setSubscriberPhone(
    id: number,
    phone: string | null,
    opts: { consent?: boolean; source?: string | null; now: number },
  ) {
    if (phone) {
      const dup = await this.db
        .prepare("SELECT id FROM subscribers WHERE phone=? AND id<>?")
        .bind(phone, id)
        .first<{ id: number }>();
      if (dup) return { ok: false, error: "phone already used by another subscriber" };
    }
    if (!phone) {
      await this.db
        .prepare("UPDATE subscribers SET phone=NULL, sms_status='none' WHERE id=?")
        .bind(id)
        .run();
      return { ok: true };
    }
    if (opts.consent === true) {
      await this.db
        .prepare(
          "UPDATE subscribers SET phone=?, sms_status='active', sms_consent_at=?, sms_consent_source=? WHERE id=?",
        )
        .bind(phone, opts.now, opts.source ?? null, id)
        .run();
    } else {
      // 同意が明示されない限り送信対象にしない（停止済みは尊重して維持）
      await this.db
        .prepare(
          "UPDATE subscribers SET phone=?, sms_status=CASE WHEN sms_status='unsubscribed' THEN 'unsubscribed' ELSE 'none' END WHERE id=?",
        )
        .bind(phone, id)
        .run();
    }
    return { ok: true };
  }
  async setSmsStatus(id: number, status: SmsStatus, now: number) {
    if (status === "active") {
      await this.db
        .prepare(
          "UPDATE subscribers SET sms_status=?, sms_consent_at=?, sms_consent_source=COALESCE(sms_consent_source,'manual') WHERE id=?",
        )
        .bind(status, now, id)
        .run();
      return;
    }
    await this.db
      .prepare("UPDATE subscribers SET sms_status=? WHERE id=?")
      .bind(status, id)
      .run();
  }

  async createList(name: string) {
    const ts = this.now();
    const res = await this.db
      .prepare("INSERT INTO lists (name,created_at) VALUES (?,?)")
      .bind(name, ts)
      .run();
    return { id: res.meta.last_row_id as number, name, created_at: ts } as List;
  }
  async listLists() {
    return (await this.db.prepare("SELECT * FROM lists ORDER BY id").all<List>()).results;
  }
  async getList(id: number) {
    return (await this.db.prepare("SELECT * FROM lists WHERE id=?").bind(id).first<List>()) ?? null;
  }
  async addToList(listId: number, subscriberId: number) {
    await this.db
      .prepare(
        "INSERT OR IGNORE INTO list_subscribers (list_id,subscriber_id) VALUES (?,?)",
      )
      .bind(listId, subscriberId)
      .run();
  }
  async removeFromList(listId: number, subscriberId: number) {
    await this.db
      .prepare("DELETE FROM list_subscribers WHERE list_id=? AND subscriber_id=?")
      .bind(listId, subscriberId)
      .run();
  }
  async subscribersInList(listId: number) {
    const r = await this.db
      .prepare(
        "SELECT s.* FROM subscribers s JOIN list_subscribers ls ON ls.subscriber_id=s.id WHERE ls.list_id=?",
      )
      .bind(listId)
      .all<any>();
    return r.results.map(toSub);
  }
  async activeSubscribersInList(listId: number) {
    const r = await this.db
      .prepare(
        "SELECT s.* FROM subscribers s JOIN list_subscribers ls ON ls.subscriber_id=s.id WHERE ls.list_id=? AND s.status='active'",
      )
      .bind(listId)
      .all<any>();
    return r.results.map(toSub);
  }

  async createBroadcast(input: {
    name: string;
    subject: string;
    body_html: string;
    list_id: number;
    channel?: StepChannel;
    body_text?: string | null;
  }) {
    const ts = this.now();
    const channel = input.channel ?? "email";
    const bodyText = input.body_text ?? null;
    const res = await this.db
      .prepare(
        "INSERT INTO broadcasts (name,subject,body_html,list_id,status,approved,created_at,channel,body_text) VALUES (?,?,?,?,'draft',0,?,?,?)",
      )
      .bind(
        input.name,
        input.subject,
        input.body_html,
        input.list_id,
        ts,
        channel,
        bodyText,
      )
      .run();
    return toBc({
      id: res.meta.last_row_id,
      ...input,
      channel,
      body_text: bodyText,
      status: "draft",
      approved: 0,
      scheduled_at: null,
      sent_at: null,
      created_at: ts,
    });
  }
  async listBroadcasts() {
    const r = await this.db
      .prepare("SELECT * FROM broadcasts ORDER BY created_at DESC")
      .all<any>();
    return r.results.map(toBc);
  }
  async getBroadcast(id: number) {
    const r = await this.db.prepare("SELECT * FROM broadcasts WHERE id=?").bind(id).first<any>();
    return r ? toBc(r) : null;
  }
  async scheduleBroadcast(id: number, scheduledAt: number) {
    const res = await this.db
      .prepare(
        "UPDATE broadcasts SET status='scheduled', scheduled_at=? WHERE id=? AND status IN ('draft','scheduled')",
      )
      .bind(scheduledAt, id)
      .run();
    return (res.meta.changes ?? 0) > 0;
  }
  async approveBroadcast(id: number) {
    await this.db.prepare("UPDATE broadcasts SET approved=1 WHERE id=?").bind(id).run();
  }
  async cancelBroadcast(id: number) {
    const res = await this.db
      .prepare("UPDATE broadcasts SET status='canceled' WHERE id=? AND status='scheduled'")
      .bind(id)
      .run();
    return (res.meta.changes ?? 0) > 0;
  }
  async claimDueBroadcasts(now: number) {
    const rows = await this.db
      .prepare(
        "SELECT id FROM broadcasts WHERE status='scheduled' AND scheduled_at IS NOT NULL AND scheduled_at<=?",
      )
      .bind(now)
      .all<{ id: number }>();
    const claimed: Broadcast[] = [];
    for (const { id } of rows.results) {
      const res = await this.db
        .prepare("UPDATE broadcasts SET status='sending' WHERE id=? AND status='scheduled'")
        .bind(id)
        .run();
      if ((res.meta.changes ?? 0) === 1) {
        const bc = await this.getBroadcast(id);
        if (bc) claimed.push(bc);
      }
    }
    return claimed;
  }
  async finishBroadcast(id: number, sentAt: number) {
    await this.db
      .prepare("UPDATE broadcasts SET status='sent', sent_at=? WHERE id=?")
      .bind(sentAt, id)
      .run();
  }
  async ensureBroadcastSends(broadcastId: number, subscriberIds: number[]) {
    for (const sid of subscriberIds) {
      await this.db
        .prepare(
          "INSERT OR IGNORE INTO broadcast_sends (broadcast_id,subscriber_id,status) VALUES (?,?,'pending')",
        )
        .bind(broadcastId, sid)
        .run();
    }
  }
  async pendingBroadcastSends(broadcastId: number, limit: number) {
    const r = await this.db
      .prepare(
        "SELECT * FROM broadcast_sends WHERE broadcast_id=? AND status IN ('pending','failed') LIMIT ?",
      )
      .bind(broadcastId, limit)
      .all<BroadcastSend>();
    return r.results;
  }
  async countPendingBroadcastSends(broadcastId: number) {
    const r = await this.db
      .prepare(
        "SELECT COUNT(*) AS n FROM broadcast_sends WHERE broadcast_id=? AND status IN ('pending','failed')",
      )
      .bind(broadcastId)
      .first<{ n: number }>();
    return r?.n ?? 0;
  }
  async markBroadcastSend(
    id: number,
    status: BroadcastSend["status"],
    resendId: string | null,
    error: string | null,
    sentAt: number | null,
  ) {
    await this.db
      .prepare(
        "UPDATE broadcast_sends SET status=?, resend_id=?, error=?, sent_at=? WHERE id=?",
      )
      .bind(status, resendId, error, sentAt, id)
      .run();
  }
  async listBroadcastSends(broadcastId: number) {
    const r = await this.db
      .prepare("SELECT * FROM broadcast_sends WHERE broadcast_id=?")
      .bind(broadcastId)
      .all<BroadcastSend>();
    return r.results;
  }

  async createCampaign(input: {
    name: string;
    list_id: number;
    trigger?: string;
    channel?: StepChannel;
  }) {
    const ts = this.now();
    const channel = input.channel ?? "email";
    const res = await this.db
      .prepare(
        "INSERT INTO step_campaigns (name,list_id,trigger,status,created_at,channel) VALUES (?,?,?,'active',?,?)",
      )
      .bind(input.name, input.list_id, input.trigger ?? "on_enroll", ts, channel)
      .run();
    return {
      id: res.meta.last_row_id as number,
      name: input.name,
      list_id: input.list_id,
      trigger: input.trigger ?? "on_enroll",
      status: "active",
      created_at: ts,
      channel,
    } as StepCampaign;
  }
  async campaignChannelCounts() {
    const r = await this.db
      .prepare(
        "SELECT campaign_id AS cid, COALESCE(channel,'email') AS ch, COUNT(*) AS c FROM step_emails GROUP BY campaign_id, COALESCE(channel,'email')",
      )
      .all<{ cid: number; ch: string; c: number }>();
    const map = new Map<number, { email: number; sms: number }>();
    for (const row of r.results) {
      const cur = map.get(row.cid) ?? { email: 0, sms: 0 };
      if (row.ch === "sms") cur.sms += row.c;
      else cur.email += row.c;
      map.set(row.cid, cur);
    }
    return map;
  }
  async listCampaigns() {
    return (await this.db.prepare("SELECT * FROM step_campaigns ORDER BY id").all<StepCampaign>())
      .results;
  }
  async getCampaign(id: number) {
    return (
      (await this.db.prepare("SELECT * FROM step_campaigns WHERE id=?").bind(id).first<StepCampaign>()) ??
      null
    );
  }
  async updateCampaign(
    id: number,
    patch: Partial<Pick<StepCampaign, "name" | "status">>,
  ) {
    const sets: string[] = [];
    const vals: unknown[] = [];
    if (patch.name !== undefined) {
      sets.push("name=?");
      vals.push(patch.name);
    }
    if (patch.status !== undefined) {
      sets.push("status=?");
      vals.push(patch.status);
    }
    if (!sets.length) return;
    await this.db
      .prepare(`UPDATE step_campaigns SET ${sets.join(",")} WHERE id=?`)
      .bind(...vals, id)
      .run();
  }
  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;
  }) {
    const timing = input.timing_type ?? "relative";
    const days = input.delay_days ?? 0;
    const sendTime = input.send_time ?? null;
    const channel = input.channel ?? "email";
    const bodyText = input.body_text ?? null;
    const res = await this.db
      .prepare(
        "INSERT INTO step_emails (campaign_id,step_order,delay_minutes,subject,body_html,timing_type,delay_days,send_time,channel,body_text) VALUES (?,?,?,?,?,?,?,?,?,?)",
      )
      .bind(
        input.campaign_id,
        input.step_order,
        input.delay_minutes,
        input.subject,
        input.body_html,
        timing,
        days,
        sendTime,
        channel,
        bodyText,
      )
      .run();
    return {
      id: res.meta.last_row_id as number,
      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: timing,
      delay_days: days,
      send_time: sendTime,
      channel,
      body_text: bodyText,
    } as StepEmail;
  }
  async updateStepEmail(
    id: number,
    patch: Partial<
      Pick<
        StepEmail,
        | "subject"
        | "body_html"
        | "delay_minutes"
        | "timing_type"
        | "delay_days"
        | "send_time"
        | "channel"
        | "body_text"
      >
    >,
  ) {
    const cols = [
      "subject",
      "body_html",
      "delay_minutes",
      "timing_type",
      "delay_days",
      "send_time",
      "channel",
      "body_text",
    ] as const;
    const sets: string[] = [];
    const vals: unknown[] = [];
    for (const c of cols)
      if (patch[c] !== undefined) {
        sets.push(`${c}=?`);
        vals.push(patch[c]);
      }
    if (!sets.length) return;
    await this.db
      .prepare(`UPDATE step_emails SET ${sets.join(",")} WHERE id=?`)
      .bind(...vals, id)
      .run();
  }
  async deleteStepEmail(id: number) {
    await this.db.prepare("DELETE FROM step_emails WHERE id=?").bind(id).run();
  }
  async reorderStepEmails(campaignId: number, orderedIds: number[]) {
    // UNIQUE(campaign_id, step_order) の一時衝突を避けるため2段階で振り直す
    const stmts = [
      this.db
        .prepare("UPDATE step_emails SET step_order = step_order + 100000 WHERE campaign_id=?")
        .bind(campaignId),
      ...orderedIds.map((sid, idx) =>
        this.db
          .prepare("UPDATE step_emails SET step_order=? WHERE id=? AND campaign_id=?")
          .bind(idx, sid, campaignId),
      ),
    ];
    await this.db.batch(stmts);
  }
  async countStepSends(campaignId: number) {
    const r = await this.db
      .prepare(
        "SELECT ss.step_email_id AS sid, COUNT(*) AS c FROM step_sends ss JOIN step_emails se ON se.id=ss.step_email_id WHERE se.campaign_id=? GROUP BY ss.step_email_id",
      )
      .bind(campaignId)
      .all<{ sid: number; c: number }>();
    return new Map(r.results.map((row) => [row.sid, row.c]));
  }
  async listStepEmails(campaignId: number) {
    return (
      await this.db
        .prepare("SELECT * FROM step_emails WHERE campaign_id=? ORDER BY step_order")
        .bind(campaignId)
        .all<StepEmail>()
    ).results;
  }
  async getStepEmail(id: number) {
    return (
      (await this.db.prepare("SELECT * FROM step_emails WHERE id=?").bind(id).first<StepEmail>()) ??
      null
    );
  }

  async enroll(campaignId: number, subscriberId: number, nextSendAt: number, now: number) {
    const existing = await this.db
      .prepare("SELECT * FROM step_enrollments WHERE campaign_id=? AND subscriber_id=?")
      .bind(campaignId, subscriberId)
      .first<StepEnrollment>();
    if (existing) return { enrollment: existing, created: false };
    const res = await this.db
      .prepare(
        "INSERT INTO step_enrollments (campaign_id,subscriber_id,current_step,status,next_send_at,enrolled_at) VALUES (?,?,0,'active',?,?)",
      )
      .bind(campaignId, subscriberId, nextSendAt, now)
      .run();
    return {
      enrollment: {
        id: res.meta.last_row_id as number,
        campaign_id: campaignId,
        subscriber_id: subscriberId,
        current_step: 0,
        status: "active",
        next_send_at: nextSendAt,
        enrolled_at: now,
      } as StepEnrollment,
      created: true,
    };
  }
  async dueEnrollments(now: number, limit: number) {
    return (
      await this.db
        .prepare(
          "SELECT * FROM step_enrollments WHERE status='active' AND next_send_at<=? LIMIT ?",
        )
        .bind(now, limit)
        .all<StepEnrollment>()
    ).results;
  }
  async advanceEnrollment(
    id: number,
    currentStep: number,
    nextSendAt: number,
    status: StepEnrollment["status"],
  ) {
    await this.db
      .prepare(
        "UPDATE step_enrollments SET current_step=?, next_send_at=?, status=? WHERE id=?",
      )
      .bind(currentStep, nextSendAt, status, id)
      .run();
  }
  async listEnrollments(campaignId: number) {
    return (
      await this.db
        .prepare("SELECT * FROM step_enrollments WHERE campaign_id=?")
        .bind(campaignId)
        .all<StepEnrollment>()
    ).results;
  }
  async hasStepSend(enrollmentId: number, stepEmailId: number) {
    const r = await this.db
      .prepare("SELECT 1 FROM step_sends WHERE enrollment_id=? AND step_email_id=?")
      .bind(enrollmentId, stepEmailId)
      .first();
    return !!r;
  }
  async recordStepSend(enrollmentId: number, stepEmailId: number, resendId: string | null) {
    await this.db
      .prepare(
        "INSERT OR IGNORE INTO step_sends (enrollment_id,step_email_id,status,resend_id,sent_at) VALUES (?,?,'sent',?,?)",
      )
      .bind(enrollmentId, stepEmailId, resendId, this.now())
      .run();
  }

  async createHook(input: { list_id: number; campaign_id: number | null; name: string | null }) {
    const token = randomToken();
    const ts = this.now();
    const res = await this.db
      .prepare(
        "INSERT INTO hooks (token,list_id,campaign_id,name,created_at) VALUES (?,?,?,?,?)",
      )
      .bind(token, input.list_id, input.campaign_id, input.name, ts)
      .run();
    return {
      id: res.meta.last_row_id as number,
      token,
      list_id: input.list_id,
      campaign_id: input.campaign_id,
      name: input.name,
      created_at: ts,
    } as Hook;
  }
  async getHook(token: string) {
    return (await this.db.prepare("SELECT * FROM hooks WHERE token=?").bind(token).first<Hook>()) ?? null;
  }
  async listHooks() {
    return (await this.db.prepare("SELECT * FROM hooks ORDER BY id").all<Hook>()).results;
  }

  async audit(action: string, detail: string) {
    await this.db
      .prepare("INSERT INTO audit_log (ts,action,detail) VALUES (?,?,?)")
      .bind(this.now(), action, detail)
      .run();
  }
  async listAudit(limit: number) {
    const r = await this.db
      .prepare("SELECT ts,action,detail FROM audit_log ORDER BY id DESC LIMIT ?")
      .bind(limit)
      .all<{ ts: number; action: string; detail: string }>();
    return r.results;
  }
  async getDailySendCount(day: string) {
    const r = await this.db
      .prepare("SELECT value FROM settings WHERE key=?")
      .bind(`sent:${day}`)
      .first<{ value: string }>();
    return r ? Number(r.value) : 0;
  }
  async incrDailySendCount(day: string, n: number) {
    const cur = await this.getDailySendCount(day);
    await this.db
      .prepare("INSERT OR REPLACE INTO settings (key,value) VALUES (?,?)")
      .bind(`sent:${day}`, String(cur + n))
      .run();
  }
  async getSmsDailySendCount(day: string) {
    const r = await this.db
      .prepare("SELECT value FROM settings WHERE key=?")
      .bind(`smsDaily:${day}`)
      .first<{ value: string }>();
    return r ? Number(r.value) : 0;
  }
  async incrSmsDailySendCount(day: string, n: number) {
    const cur = await this.getSmsDailySendCount(day);
    await this.db
      .prepare("INSERT OR REPLACE INTO settings (key,value) VALUES (?,?)")
      .bind(`smsDaily:${day}`, String(cur + n))
      .run();
  }
  async getSetting(key: string) {
    const r = await this.db
      .prepare("SELECT value FROM settings WHERE key=?")
      .bind(key)
      .first<{ value: string }>();
    return r?.value ?? null;
  }
  async setSetting(key: string, value: string) {
    await this.db
      .prepare("INSERT OR REPLACE INTO settings (key,value) VALUES (?,?)")
      .bind(key, value)
      .run();
  }

  // --- points ---
  async addPointEvent(
    subscriberId: number,
    points: number,
    kind: PointKind,
    dedupeKey: string | null,
    note: string | null,
  ) {
    try {
      const res = await this.db
        .prepare(
          "INSERT INTO point_events (subscriber_id,points,kind,dedupe_key,note,created_at) VALUES (?,?,?,?,?,?)",
        )
        .bind(subscriberId, points, kind, dedupeKey, note, this.now())
        .run();
      return { created: (res.meta.changes ?? 0) > 0 };
    } catch (e) {
      // UNIQUE(dedupe_key) 違反 = 既に付与済み（冪等）
      if (String(e).includes("UNIQUE")) return { created: false };
      throw e;
    }
  }
  async trySpendPoints(subscriberId: number, cost: number, kind: PointKind, note: string | null) {
    // 消費額は必ず正（cost<=0 の不正/レガシー設定でポイントが増えるのを防ぐ）
    if (!(cost > 0)) return { ok: false };
    // 残高判定と減算を1文にまとめて原子的に。同時実行でも SQLite が直列化するため負残高にならない
    const res = await this.db
      .prepare(
        `INSERT INTO point_events (subscriber_id,points,kind,dedupe_key,note,created_at)
         SELECT ?,?,?,NULL,?,?
         WHERE (SELECT COALESCE(SUM(points),0) FROM point_events WHERE subscriber_id=?) >= ?`,
      )
      .bind(subscriberId, -cost, kind, note, this.now(), subscriberId, cost)
      .run();
    return { ok: (res.meta.changes ?? 0) > 0 };
  }
  async pointBalance(subscriberId: number) {
    const r = await this.db
      .prepare("SELECT COALESCE(SUM(points),0) AS b FROM point_events WHERE subscriber_id=?")
      .bind(subscriberId)
      .first<{ b: number }>();
    return r?.b ?? 0;
  }
  async listPointEvents(subscriberId: number, limit: number) {
    const r = await this.db
      .prepare("SELECT * FROM point_events WHERE subscriber_id=? ORDER BY id DESC LIMIT ?")
      .bind(subscriberId, limit)
      .all<PointEvent>();
    return r.results;
  }
  async listPointBalances() {
    const r = await this.db
      .prepare(
        "SELECT subscriber_id, SUM(points) AS balance FROM point_events GROUP BY subscriber_id ORDER BY balance DESC",
      )
      .all<{ subscriber_id: number; balance: number }>();
    return r.results;
  }

  // --- tracked links ---
  async getOrCreateTrackedLink(scope: "bc" | "step", refId: number, url: string) {
    const found = await this.db
      .prepare("SELECT * FROM tracked_links WHERE scope=? AND ref_id=? AND url=?")
      .bind(scope, refId, url)
      .first<TrackedLink>();
    if (found) return found;
    const ts = this.now();
    const res = await this.db
      .prepare("INSERT OR IGNORE INTO tracked_links (scope,ref_id,url,created_at) VALUES (?,?,?,?)")
      .bind(scope, refId, url, ts)
      .run();
    if ((res.meta.changes ?? 0) > 0)
      return { id: res.meta.last_row_id as number, scope, ref_id: refId, url, created_at: ts };
    // 並行INSERTで負けた場合は再取得
    const again = await this.db
      .prepare("SELECT * FROM tracked_links WHERE scope=? AND ref_id=? AND url=?")
      .bind(scope, refId, url)
      .first<TrackedLink>();
    return again as TrackedLink;
  }
  async getTrackedLink(id: number) {
    return (
      (await this.db.prepare("SELECT * FROM tracked_links WHERE id=?").bind(id).first<TrackedLink>()) ??
      null
    );
  }
  async wasBroadcastSentTo(broadcastId: number, subscriberId: number) {
    // 実際に送信済み(sent)の行のみ。pending/skipped/failed では加点しない
    const r = await this.db
      .prepare("SELECT 1 FROM broadcast_sends WHERE broadcast_id=? AND subscriber_id=? AND status='sent' LIMIT 1")
      .bind(broadcastId, subscriberId)
      .first();
    return !!r;
  }
  async wasStepSentTo(stepEmailId: number, subscriberId: number) {
    const r = await this.db
      .prepare(
        `SELECT 1 FROM step_sends ss
         JOIN step_enrollments se ON ss.enrollment_id = se.id
         WHERE ss.step_email_id=? AND se.subscriber_id=? LIMIT 1`,
      )
      .bind(stepEmailId, subscriberId)
      .first();
    return !!r;
  }

  // --- lotteries ---
  async createLottery(input: { name: string; description: string | null; cost_points: number }) {
    const ts = this.now();
    const res = await this.db
      .prepare(
        "INSERT INTO lotteries (name,description,cost_points,status,created_at) VALUES (?,?,?,'open',?)",
      )
      .bind(input.name, input.description, input.cost_points, ts)
      .run();
    return {
      id: res.meta.last_row_id as number,
      ...input,
      status: "open",
      created_at: ts,
    } as Lottery;
  }
  async updateLottery(
    id: number,
    patch: Partial<Pick<Lottery, "name" | "description" | "cost_points" | "status">>,
  ) {
    // 渡されたカラムだけを UPDATE（同時編集で他フィールドを巻き戻さない = lost update 回避）
    const sets: string[] = [];
    const vals: unknown[] = [];
    if (patch.name !== undefined) (sets.push("name=?"), vals.push(patch.name));
    if (patch.description !== undefined) (sets.push("description=?"), vals.push(patch.description));
    if (patch.cost_points !== undefined) (sets.push("cost_points=?"), vals.push(patch.cost_points));
    if (patch.status !== undefined) (sets.push("status=?"), vals.push(patch.status));
    if (!sets.length) return;
    await this.db
      .prepare(`UPDATE lotteries SET ${sets.join(", ")} WHERE id=?`)
      .bind(...vals, id)
      .run();
  }
  async listLotteries() {
    return (await this.db.prepare("SELECT * FROM lotteries ORDER BY id DESC").all<Lottery>()).results;
  }
  async getLottery(id: number) {
    return (await this.db.prepare("SELECT * FROM lotteries WHERE id=?").bind(id).first<Lottery>()) ?? null;
  }
  async addPrize(input: {
    lottery_id: number;
    name: string;
    description: string | null;
    probability: number;
    max_winners: number;
  }) {
    const ts = this.now();
    const res = await this.db
      .prepare(
        "INSERT INTO lottery_prizes (lottery_id,name,description,probability,max_winners,active,created_at) VALUES (?,?,?,?,?,1,?)",
      )
      .bind(input.lottery_id, input.name, input.description, input.probability, input.max_winners, ts)
      .run();
    return {
      id: res.meta.last_row_id as number,
      ...input,
      active: true,
      created_at: ts,
    } as LotteryPrize;
  }
  async getPrize(id: number) {
    const r = await this.db.prepare("SELECT * FROM lottery_prizes WHERE id=?").bind(id).first<any>();
    return r ? ({ ...r, active: !!r.active } as LotteryPrize) : null;
  }
  async updatePrize(
    id: number,
    patch: Partial<Pick<LotteryPrize, "name" | "description" | "probability" | "max_winners" | "active">>,
  ) {
    const sets: string[] = [];
    const vals: unknown[] = [];
    if (patch.name !== undefined) (sets.push("name=?"), vals.push(patch.name));
    if (patch.description !== undefined) (sets.push("description=?"), vals.push(patch.description));
    if (patch.probability !== undefined) (sets.push("probability=?"), vals.push(patch.probability));
    if (patch.max_winners !== undefined) (sets.push("max_winners=?"), vals.push(patch.max_winners));
    if (patch.active !== undefined) (sets.push("active=?"), vals.push(patch.active ? 1 : 0));
    if (!sets.length) return;
    await this.db
      .prepare(`UPDATE lottery_prizes SET ${sets.join(", ")} WHERE id=?`)
      .bind(...vals, id)
      .run();
  }
  async listPrizes(lotteryId: number) {
    const r = await this.db
      .prepare("SELECT * FROM lottery_prizes WHERE lottery_id=? ORDER BY id")
      .bind(lotteryId)
      .all<any>();
    return r.results.map((p) => ({ ...p, active: !!p.active }) as LotteryPrize);
  }
  async prizeWinCounts(lotteryId: number) {
    const r = await this.db
      .prepare(
        "SELECT prize_id, COUNT(*) AS n FROM lottery_draws WHERE lottery_id=? AND prize_id IS NOT NULL GROUP BY prize_id",
      )
      .bind(lotteryId)
      .all<{ prize_id: number; n: number }>();
    return new Map(r.results.map((x) => [x.prize_id, x.n]));
  }
  async recordDraw(
    lotteryId: number,
    subscriberId: number,
    prizeId: number | null,
    pointsSpent: number,
  ) {
    const ts = this.now();
    const res = await this.db
      .prepare(
        "INSERT INTO lottery_draws (lottery_id,subscriber_id,prize_id,points_spent,fulfilled,drawn_at) VALUES (?,?,?,?,0,?)",
      )
      .bind(lotteryId, subscriberId, prizeId, pointsSpent, ts)
      .run();
    return {
      id: res.meta.last_row_id as number,
      lottery_id: lotteryId,
      subscriber_id: subscriberId,
      prize_id: prizeId,
      points_spent: pointsSpent,
      fulfilled: false,
      drawn_at: ts,
    } as LotteryDraw;
  }
  async recordDrawCapped(
    lotteryId: number,
    subscriberId: number,
    prize: { id: number; max_winners: number } | null,
    pointsSpent: number,
  ) {
    const ts = this.now();
    if (prize && prize.max_winners > 0) {
      // 当選本数上限を1文で判定して記録。上限到達なら 0 行 → はずれで記録し直す
      const res = await this.db
        .prepare(
          `INSERT INTO lottery_draws (lottery_id,subscriber_id,prize_id,points_spent,fulfilled,drawn_at)
           SELECT ?,?,?,?,0,?
           WHERE (SELECT COUNT(*) FROM lottery_draws WHERE lottery_id=? AND prize_id=?) < ?`,
        )
        .bind(lotteryId, subscriberId, prize.id, pointsSpent, ts, lotteryId, prize.id, prize.max_winners)
        .run();
      if ((res.meta.changes ?? 0) > 0) {
        return {
          draw: {
            id: res.meta.last_row_id as number,
            lottery_id: lotteryId,
            subscriber_id: subscriberId,
            prize_id: prize.id,
            points_spent: pointsSpent,
            fulfilled: false,
            drawn_at: ts,
          } as LotteryDraw,
          won: true,
        };
      }
      // 上限到達 → はずれとして記録
      const miss = await this.recordDraw(lotteryId, subscriberId, null, pointsSpent);
      return { draw: miss, won: false };
    }
    // 無制限当選 or はずれ
    const draw = await this.recordDraw(lotteryId, subscriberId, prize ? prize.id : null, pointsSpent);
    return { draw, won: !!prize };
  }
  async listDraws(lotteryId: number | null, limit: number) {
    const q =
      lotteryId === null
        ? this.db.prepare("SELECT * FROM lottery_draws ORDER BY id DESC LIMIT ?").bind(limit)
        : this.db
            .prepare("SELECT * FROM lottery_draws WHERE lottery_id=? ORDER BY id DESC LIMIT ?")
            .bind(lotteryId, limit);
    const r = await q.all<any>();
    return r.results.map((d) => ({ ...d, fulfilled: !!d.fulfilled }) as LotteryDraw);
  }
  async setDrawFulfilled(id: number, fulfilled: boolean) {
    await this.db
      .prepare("UPDATE lottery_draws SET fulfilled=? WHERE id=?")
      .bind(fulfilled ? 1 : 0, id)
      .run();
  }

  // --- exchange ---
  async createExchangeItem(input: {
    name: string;
    description: string | null;
    cost_points: number;
    stock: number;
  }) {
    const ts = this.now();
    const res = await this.db
      .prepare(
        "INSERT INTO exchange_items (name,description,cost_points,stock,active,created_at) VALUES (?,?,?,?,1,?)",
      )
      .bind(input.name, input.description, input.cost_points, input.stock, ts)
      .run();
    return {
      id: res.meta.last_row_id as number,
      ...input,
      active: true,
      created_at: ts,
    } as ExchangeItem;
  }
  async updateExchangeItem(
    id: number,
    patch: Partial<Pick<ExchangeItem, "name" | "description" | "cost_points" | "stock" | "active">>,
  ) {
    const sets: string[] = [];
    const vals: unknown[] = [];
    if (patch.name !== undefined) (sets.push("name=?"), vals.push(patch.name));
    if (patch.description !== undefined) (sets.push("description=?"), vals.push(patch.description));
    if (patch.cost_points !== undefined) (sets.push("cost_points=?"), vals.push(patch.cost_points));
    if (patch.stock !== undefined) (sets.push("stock=?"), vals.push(patch.stock));
    if (patch.active !== undefined) (sets.push("active=?"), vals.push(patch.active ? 1 : 0));
    if (!sets.length) return;
    await this.db
      .prepare(`UPDATE exchange_items SET ${sets.join(", ")} WHERE id=?`)
      .bind(...vals, id)
      .run();
  }
  async listExchangeItems() {
    const r = await this.db.prepare("SELECT * FROM exchange_items ORDER BY id DESC").all<any>();
    return r.results.map((x) => ({ ...x, active: !!x.active }) as ExchangeItem);
  }
  async getExchangeItem(id: number) {
    const r = await this.db.prepare("SELECT * FROM exchange_items WHERE id=?").bind(id).first<any>();
    return r ? ({ ...r, active: !!r.active } as ExchangeItem) : null;
  }
  async countRedemptions(itemId: number) {
    const r = await this.db
      .prepare("SELECT COUNT(*) AS n FROM exchange_redemptions WHERE item_id=? AND status!='canceled'")
      .bind(itemId)
      .first<{ n: number }>();
    return r?.n ?? 0;
  }
  async recordRedemption(itemId: number, subscriberId: number, pointsSpent: number) {
    const ts = this.now();
    const res = await this.db
      .prepare(
        "INSERT INTO exchange_redemptions (item_id,subscriber_id,points_spent,status,created_at) VALUES (?,?,?,'pending',?)",
      )
      .bind(itemId, subscriberId, pointsSpent, ts)
      .run();
    return {
      id: res.meta.last_row_id as number,
      item_id: itemId,
      subscriber_id: subscriberId,
      points_spent: pointsSpent,
      status: "pending",
      created_at: ts,
    } as ExchangeRedemption;
  }
  async tryReserveRedemption(
    item: { id: number; cost_points: number; stock: number },
    subscriberId: number,
  ) {
    // 在庫確認と申請記録を1文で原子的に。stock<0 は無制限
    const ts = this.now();
    const res = await this.db
      .prepare(
        `INSERT INTO exchange_redemptions (item_id,subscriber_id,points_spent,status,created_at)
         SELECT ?,?,?,'pending',?
         WHERE ? < 0 OR (SELECT COUNT(*) FROM exchange_redemptions WHERE item_id=? AND status!='canceled') < ?`,
      )
      .bind(item.id, subscriberId, item.cost_points, ts, item.stock, item.id, item.stock)
      .run();
    if ((res.meta.changes ?? 0) > 0) {
      return {
        id: res.meta.last_row_id as number,
        item_id: item.id,
        subscriber_id: subscriberId,
        points_spent: item.cost_points,
        status: "pending",
        created_at: ts,
      } as ExchangeRedemption;
    }
    return null;
  }
  async getRedemption(id: number) {
    return (
      (await this.db
        .prepare("SELECT * FROM exchange_redemptions WHERE id=?")
        .bind(id)
        .first<ExchangeRedemption>()) ?? null
    );
  }
  async listRedemptions(limit: number) {
    const r = await this.db
      .prepare("SELECT * FROM exchange_redemptions ORDER BY id DESC LIMIT ?")
      .bind(limit)
      .all<ExchangeRedemption>();
    return r.results;
  }
  async setRedemptionStatus(id: number, status: ExchangeRedemption["status"]) {
    await this.db
      .prepare("UPDATE exchange_redemptions SET status=? WHERE id=?")
      .bind(status, id)
      .run();
  }
}
