// src/db/schema.ts
import { sqliteTable, text, integer, real } from "drizzle-orm/sqlite-core";
import { sql } from "drizzle-orm";

export const vendors = sqliteTable("vendors", {
  id: text("id").primaryKey(),
  ownerUserId: text("owner_user_id").notNull(),
  displayName: text("display_name").notNull(),
  slug: text("slug").notNull().unique(),
  stripeAccountId: text("stripe_account_id"),          // 来自 Stripe Connect 的 acct_xxx
  kycStatus: text("kyc_status").notNull().default("pending"), // pending|verified|rejected
  payoutScheduleDays: integer("payout_schedule_days").notNull().default(7),
  createdAt: integer("created_at").notNull().default(sql`(unixepoch())`),
});

export const listings = sqliteTable("listings", {
  id: text("id").primaryKey(),
  vendorId: text("vendor_id").notNull().references(() => vendors.id),
  title: text("title").notNull(),
  priceCents: integer("price_cents").notNull(),
  currency: text("currency").notNull().default("USD"),
  inventoryCount: integer("inventory_count").notNull().default(0),
  status: text("status").notNull().default("draft"), // draft|active|paused|archived
  createdAt: integer("created_at").notNull().default(sql`(unixepoch())`),
});

export const orders = sqliteTable("orders", {
  id: text("id").primaryKey(),
  buyerUserId: text("buyer_user_id").notNull(),
  vendorId: text("vendor_id").notNull().references(() => vendors.id),
  listingId: text("listing_id").notNull().references(() => listings.id),
  amountCents: integer("amount_cents").notNull(),
  platformFeeCents: integer("platform_fee_cents").notNull(),
  vendorAmountCents: integer("vendor_amount_cents").notNull(),
  currency: text("currency").notNull().default("USD"),
  paymentIntentId: text("payment_intent_id"),         // 来自 Stripe 的 pi_xxx
  status: text("status").notNull().default("pending_payment"),
  // 状态流转：pending_payment → paid → in_fulfillment → delivered → escrow_held → released | refunded | disputed
  createdAt: integer("created_at").notNull().default(sql`(unixepoch())`),
});

// 仅追加（Append-only）的分类账；每个状态的流转均产生一条记录。
export const ledgerEntries = sqliteTable("ledger_entries", {
  id: text("id").primaryKey(),
  orderId: text("order_id").notNull().references(() => orders.id),
  vendorId: text("vendor_id").notNull().references(() => vendors.id),
  entryType: text("entry_type").notNull(), // hold|release|refund|adjustment|payout
  amountCents: integer("amount_cents").notNull(),     // 正数代表贷记（收入），负数代表借记（支出）
  currency: text("currency").notNull().default("USD"),
  idempotencyKey: text("idempotency_key").notNull().unique(),
  stripeTransferId: text("stripe_transfer_id"),       // tr_xxx
  createdAt: integer("created_at").notNull().default(sql`(unixepoch())`),
});

export const payouts = sqliteTable("payouts", {
  id: text("id").primaryKey(),
  vendorId: text("vendor_id").notNull().references(() => vendors.id),
  amountCents: integer("amount_cents").notNull(),
  currency: text("currency").notNull().default("USD"),
  status: text("status").notNull().default("pending"), // pending|paid|failed
  stripePayoutId: text("stripe_payout_id"),           // po_xxx
  failureReason: text("failure_reason"),
  attemptCount: integer("attempt_count").notNull().default(0),
  createdAt: integer("created_at").notNull().default(sql`(unixepoch())`),
});

// src/db/migrations/0001_indexes.ts
import { sql } from "drizzle-orm";

export async function up(db: DrizzleDB) {
  // Listings：主要用于供应商控制台与激活商品聚合页
  await db.run(sql`CREATE INDEX idx_listings_vendor_status ON listings(vendor_id, status)`);
  // Orders：针对供应商维度的历史订单 + 买家维度的历史订单
  await db.run(sql`CREATE INDEX idx_orders_vendor_created ON orders(vendor_id, created_at DESC)`);
  await db.run(sql`CREATE INDEX idx_orders_buyer_created ON orders(buyer_user_id, created_at DESC)`);
  // Ledger：供应商账户余额查询是关键热路径
  await db.run(sql`CREATE INDEX idx_ledger_vendor_created ON ledger_entries(vendor_id, created_at DESC)`);
  await db.run(sql`CREATE INDEX idx_ledger_idempotency ON ledger_entries(idempotency_key)`);
}

// src/server/checkout.ts
import Stripe from "stripe";
import { db } from "~/db/client";
import { orders, listings, vendors } from "~/db/schema";
import { eq } from "drizzle-orm";

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!);

export async function createCheckoutSession(orderId: string) {
  const order = await db.select().from(orders).where(eq(orders.id, orderId)).get();
  if (!order) throw new Error(`找不到订单 ${orderId}`);

  const vendor = await db.select().from(vendors).where(eq(vendors.id, order.vendorId)).get();
  if (!vendor?.stripeAccountId) throw new Error("该供应商尚未挂载 Stripe 收款账号");

  const intent = await stripe.paymentIntents.create(
    {
      amount: order.amountCents,
      currency: order.currency.toLowerCase(),
      transfer_data: { destination: vendor.stripeAccountId },
      application_fee_amount: order.platformFeeCents,
      metadata: {
        order_id: order.id,
        vendor_id: order.vendorId,
      },
    },
    { idempotencyKey: `pi:${order.id}` },
  );

  await db
    .update(orders)
    .set({ paymentIntentId: intent.id })
    .where(eq(orders.id, order.id));

  return { clientSecret: intent.client_secret };
}

// src/server/onboarding.ts
import Stripe from "stripe";
import { db } from "~/db/client";
import { vendors } from "~/db/schema";
import { eq } from "drizzle-orm";

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!, {
  apiVersion: "2024-06-20",
});

export async function createOnboardingLink(vendorId: string) {
  const vendor = await db
    .select()
    .from(vendors)
    .where(eq(vendors.id, vendorId))
    .get();
  if (!vendor) throw new Error(`找不到供应商 ${vendorId}`);

  let stripeAccountId = vendor.stripeAccountId;
  if (!stripeAccountId) {
    const account = await stripe.accounts.create({
      type: "express",
      country: "US", 
      capabilities: {
        card_payments: { requested: true },
        transfers: { requested: true },
      },
    });
    stripeAccountId = account.id;
    await db
      .update(vendors)
      .set({ stripeAccountId })
      .where(eq(vendors.id, vendorId));
  }

  const link = await stripe.accountLinks.create({
    account: stripeAccountId,
    refresh_url: `${process.env.APP_URL}/vendor/onboarding/refresh`,
    return_url: `${process.env.APP_URL}/vendor/onboarding/complete`,
    type: "account_onboarding",
  });

  return { url: link.url, expiresAt: link.expires_at };
}

// src/workers/reconcile.ts
import { db } from "~/db/client";
import { orders, ledgerEntries, payouts, vendors } from "~/db/schema";
import { eq, and, sql } from "drizzle-orm";
import Stripe from "stripe";

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!);

export async function reconcileEscrow() {
  // 1. 遍历并找到所有暂处于冻结资金状态的订单，确保它们在本地有对应的 'hold' 条目
  const paidOrders = await db
    .select()
    .from(orders)
    .where(eq(orders.status, "escrow_held"))
    .all();

  for (const order of paidOrders) {
    const existing = await db
      .select()
      .from(ledgerEntries)
      .where(
        and(
          eq(ledgerEntries.orderId, order.id),
          eq(ledgerEntries.entryType, "hold"),
        ),
      )
      .get();
    if (!existing) {
      // 补全由于可能偶遇的部署期中断不稳错过的 hold 操作底单
      await db.insert(ledgerEntries).values({
        id: crypto.randomUUID(),
        orderId: order.id,
        vendorId: order.vendorId,
        entryType: "hold",
        amountCents: order.vendorAmountCents,
        idempotencyKey: `hold:${order.id}`,
      });
    }
  }

  // 2. 依次遍历并检查所有期满该放款退还周期的封存记录，提交到 Stripe 行使解除并转划的命令
  const releasable = await db
    .select()
    .from(orders)
    .where(
      and(
        eq(orders.status, "escrow_held"),
        sql`created_at + (payout_schedule_days * 86400) <= unixepoch()`,
      ),
    )
    .all();

  for (const order of releasable) {
    const vendor = await db
      .select()
      .from(vendors)
      .where(eq(vendors.id, order.vendorId))
      .get();
    if (!vendor?.stripeAccountId) continue;

    // 指引 Stripe 拨付下发到下挂绑定的实体商家端帐户上
    const transfer = await stripe.transfers.create(
      {
        amount: order.vendorAmountCents,
        currency: order.currency.toLowerCase(),
        destination: vendor.stripeAccountId,
        transfer_group: order.id,
      },
      { idempotencyKey: `transfer:${order.id}` },
    );

    // 录入释放操作实体表记录 —— 基于 unique 的约束保持 transfer.id 等幂安全
    await db.insert(ledgerEntries).values({
      id: crypto.randomUUID(),
      orderId: order.id,
      vendorId: order.vendorId,
      entryType: "release",
      amountCents: -order.vendorAmountCents,
      idempotencyKey: `release:${transfer.id}`,
      stripeTransferId: transfer.id,
    });

    await db
      .update(orders)
      .set({ status: "released" })
      .where(eq(orders.id, order.id));
  }

  // 3. 将本地记录同远端 Stripe API 间进行最终校验与失联断崖比查 —— 主动找出任何漂移缺位。
  const allTransfers = await stripe.transfers.list({ limit: 100 });
  for (const t of allTransfers.data) {
    const local = await db
      .select()
      .from(ledgerEntries)
      .where(eq(ledgerEntries.stripeTransferId, t.id))
      .get();
    if (!local) {
      console.warn(`检测到漂移问题：Stripe 端的划转项 ${t.id} 并未记录收存在我方本地分类账内`);
    }
  }
}

await db
  .update(vendors)
  .set({ payoutScheduleDays: 14 })
  .where(eq(vendors.id, vendorId));