/**
 * Marketing query helpers — cursor pagination, trigger-state dedup, and
 * candidate queries used by the email-marketing cron handlers.
 */

import { executeRows } from '../execute-rows.js';
import { and, eq, sql } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { marketingTriggerState, syncCursors } from '../schema.js';

type MarketingCandidateRow = {
  user_id: string;
  email: string;
  notif_prefs: unknown;
};

// ─── Cursor helpers ───────────────────────────────────────────────────────────

export async function getCursor(db: DrizzleClient, key: string): Promise<string | null> {
  const [row] = await db
    .select({ value: syncCursors.value })
    .from(syncCursors)
    .where(eq(syncCursors.key, key))
    .limit(1);
  return row?.value ?? null;
}

export async function setCursor(db: DrizzleClient, key: string, value: string): Promise<void> {
  await db
    .insert(syncCursors)
    .values({ key, value, updatedAt: new Date() })
    .onConflictDoUpdate({ target: syncCursors.key, set: { value, updatedAt: new Date() } });
}

// ─── Trigger state helpers ────────────────────────────────────────────────────

export async function hasTriggered(
  db: DrizzleClient,
  userId: string,
  triggerKey: string,
): Promise<boolean> {
  const [row] = await db
    .select({ id: marketingTriggerState.id })
    .from(marketingTriggerState)
    .where(
      and(
        eq(marketingTriggerState.userId, userId),
        eq(marketingTriggerState.triggerKey, triggerKey),
      ),
    )
    .limit(1);
  return !!row;
}

export async function recordTriggered(
  db: DrizzleClient,
  userId: string,
  triggerKey: string,
): Promise<void> {
  await db.insert(marketingTriggerState).values({ userId, triggerKey }).onConflictDoNothing();
}

// ─── Candidate types ──────────────────────────────────────────────────────────

export type BuyerCandidate = {
  userId: string;
  email: string;
  locale: string | null;
  notifPrefs: { marketing?: Record<string, boolean> } | null;
  triggerKey: string;
  payload: Record<string, unknown>;
};

export type VendorCandidate = {
  vendorId: string;
  userId: string;
  email: string;
  locale: string | null;
  notifPrefs: { marketing?: Record<string, boolean> } | null;
  triggerKey: string;
  payload: Record<string, unknown>;
};

// ─── Candidate queries ────────────────────────────────────────────────────────

/**
 * Users who have never received the day-0 welcome email.
 * Cursor is on users.id (UUID text comparison for consistent pagination).
 */
export async function getBuyerWelcomeDay0Candidates(
  db: DrizzleClient,
  cursor: string | null,
  limit: number,
): Promise<BuyerCandidate[]> {
  const result = await db.execute<MarketingCandidateRow>(
    sql`SELECT u.id AS user_id, u.email, u.notif_prefs
        FROM users u
        WHERE u.email IS NOT NULL
          AND ${cursor ? sql`u.id > ${cursor}` : sql`TRUE`}
          AND NOT EXISTS (
            SELECT 1 FROM marketing_trigger_state m
            WHERE m.user_id = u.id AND m.trigger_key = 'buyer.welcome.day0'
          )
        ORDER BY u.id
        LIMIT ${limit}`,
  );

  return executeRows<MarketingCandidateRow>(result).map((r) => ({
    userId: r.user_id,
    email: r.email,
    locale: null,
    notifPrefs: r.notif_prefs as BuyerCandidate['notifPrefs'],
    triggerKey: 'buyer.welcome.day0',
    payload: { userId: r.user_id },
  }));
}

/**
 * Users created more than 3 days ago with no purchases and no day-3 welcome email fired.
 */
export async function getBuyerWelcomeDay3Candidates(
  db: DrizzleClient,
  cursor: string | null,
  limit: number,
): Promise<BuyerCandidate[]> {
  const threeDaysAgo = new Date(Date.now() - 3 * 24 * 60 * 60 * 1000);

  const result = await db.execute<MarketingCandidateRow>(
    sql`SELECT u.id AS user_id, u.email, u.notif_prefs
        FROM users u
        WHERE u.email IS NOT NULL
          AND u.created_at < ${threeDaysAgo}
          AND ${cursor ? sql`u.id > ${cursor}` : sql`TRUE`}
          AND NOT EXISTS (SELECT 1 FROM "order" o WHERE o.buyer_user_id = u.id AND o.status = 'paid' LIMIT 1)
          AND NOT EXISTS (
            SELECT 1 FROM marketing_trigger_state m
            WHERE m.user_id = u.id AND m.trigger_key = 'buyer.welcome.day3'
          )
        ORDER BY u.id
        LIMIT ${limit}`,
  );

  return executeRows<MarketingCandidateRow>(result).map((r) => ({
    userId: r.user_id,
    email: r.email,
    locale: null,
    notifPrefs: r.notif_prefs as BuyerCandidate['notifPrefs'],
    triggerKey: 'buyer.welcome.day3',
    payload: { userId: r.user_id },
  }));
}

/**
 * All users eligible for the weekly digest — respects a 6-day cooldown via
 * marketingTriggerState.firedAt so the same user is not emailed more than once
 * per week.
 */
export async function getBuyerWeeklyDigestCandidates(
  db: DrizzleClient,
  cursor: string | null,
  limit: number,
): Promise<BuyerCandidate[]> {
  const sixDaysAgo = new Date(Date.now() - 6 * 24 * 60 * 60 * 1000);

  const result = await db.execute<MarketingCandidateRow>(
    sql`SELECT u.id AS user_id, u.email, u.notif_prefs
        FROM users u
        WHERE u.email IS NOT NULL
          AND ${cursor ? sql`u.id > ${cursor}` : sql`TRUE`}
          AND NOT EXISTS (
            SELECT 1 FROM marketing_trigger_state m
            WHERE m.user_id = u.id AND m.trigger_key = 'buyer.digest.weekly'
              AND m.fired_at > ${sixDaysAgo}
          )
        ORDER BY u.id
        LIMIT ${limit}`,
  );

  return executeRows<MarketingCandidateRow>(result).map((r) => ({
    userId: r.user_id,
    email: r.email,
    locale: null,
    notifPrefs: r.notif_prefs as BuyerCandidate['notifPrefs'],
    triggerKey: 'buyer.digest.weekly',
    payload: { userId: r.user_id },
  }));
}