24.06-计费系统

-- 账单表
CREATE TABLE invoices (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  subscription_id TEXT,
  invoice_number TEXT NOT NULL UNIQUE,  -- 发票编号
  status TEXT NOT NULL,                 -- draft/open/paid/void/uncollectible
  amount_due INTEGER NOT NULL,          -- 应付金额（美分）
  amount_paid INTEGER DEFAULT 0,        -- 已付金额
  currency TEXT NOT NULL DEFAULT 'usd',
  period_start INTEGER NOT NULL,
  period_end INTEGER NOT NULL,
  stripe_invoice_id TEXT,
  created_at INTEGER NOT NULL,
  due_at INTEGER,
  paid_at INTEGER,
  FOREIGN KEY (subscription_id) REFERENCES subscriptions(id)
)
 
-- 账单明细表
CREATE TABLE invoice_items (
  id TEXT PRIMARY KEY,
  invoice_id TEXT NOT NULL,
  description TEXT NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_amount INTEGER NOT NULL,         -- 单价（美分）
  amount INTEGER NOT NULL,              -- 小计（美分）
  FOREIGN KEY (invoice_id) REFERENCES invoices(id)
)
 
-- 支付方式表
CREATE TABLE payment_methods (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  type TEXT NOT NULL,                   -- card/bank_transfer
  stripe_payment_method_id TEXT,
  is_default INTEGER DEFAULT 0,
  card_brand TEXT,
  card_last4 TEXT,
  card_exp_month INTEGER,
  card_exp_year INTEGER,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
)
 
-- 信用额度表（用于退款、补偿）
CREATE TABLE credits (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  amount INTEGER NOT NULL,              -- 金额（美分）
  remaining INTEGER NOT NULL,           -- 剩余金额
  reason TEXT,
  expires_at INTEGER,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
)

// src/types/billing.ts
export interface Invoice {
  id: string
  tenantId: string
  subscriptionId?: string
  invoiceNumber: string
  status: InvoiceStatus
  amountDue: number
  amountPaid: number
  currency: string
  periodStart: number
  periodEnd: number
  stripeInvoiceId?: string
  createdAt: number
  dueAt?: number
  paidAt?: number
  items: InvoiceItem[]
}
 
export type InvoiceStatus =
  | 'draft'          // 草稿
  | 'open'           // 待支付
  | 'paid'           // 已支付
  | 'void'           // 作废
  | 'uncollectible'  // 无法收取
 
export interface InvoiceItem {
  id: string
  invoiceId: string
  description: string
  quantity: number
  unitAmount: number
  amount: number
}
 
export interface PaymentMethod {
  id: string
  tenantId: string
  type: 'card' | 'bank_transfer'
  stripePaymentMethodId?: string
  isDefault: boolean
  cardBrand?: string
  cardLast4?: string
  cardExpMonth?: number
  cardExpYear?: number
}
 
export interface Credit {
  id: string
  tenantId: string
  amount: number
  remaining: number
  reason?: string
  expiresAt?: number
  createdAt: number
}

// src/lib/billing.ts
export class BillingManager {
  constructor(private db: D1Database, private env: Env) {}
 
  /**
   * 生成月度账单
   */
  async generateMonthlyInvoice(
    tenantId: string,
    subscriptionId: string
  ): Promise<Invoice> {
    // 1. 获取订阅信息
    const subscription = await this.db.prepare(`
      SELECT * FROM subscriptions WHERE id = ?
    `).bind(subscriptionId).first()
 
    if (!subscription) {
      throw new Error('Subscription not found')
    }
 
    // 2. 获取套餐价格
    const plan = getPlan(subscription.plan_id)
 
    // 3. 生成发票编号
    const invoiceNumber = await this.generateInvoiceNumber()
 
    // 4. 创建账单
    const invoiceId = crypto.randomUUID()
    const now = Date.now()
 
    await this.db.prepare(`
      INSERT INTO invoices (id, tenant_id, subscription_id, invoice_number, status, amount_due, currency, period_start, period_end, created_at, due_at)
      VALUES (?, ?, ?, ?, 'open', ?, ?, ?, ?, ?, ?)
    `).bind(
      invoiceId,
      tenantId,
      subscriptionId,
      invoiceNumber,
      plan.price,
      'usd',
      subscription.current_period_start,
      subscription.current_period_end,
      now,
      subscription.current_period_end
    ).run()
 
    // 5. 创建账单明细
    const itemId = crypto.randomUUID()
    await this.db.prepare(`
      INSERT INTO invoice_items (id, invoice_id, description, quantity, unit_amount, amount)
      VALUES (?, ?, ?, ?, ?, ?)
    `).bind(
      itemId,
      invoiceId,
      `${plan.name} Plan - Monthly`,
      1,
      plan.price,
      plan.price
    ).run()
 
    // 6. 同步到 Stripe
    const stripeInvoice = await this.createStripeInvoice(
      tenantId,
      invoiceId,
      plan.price,
      `${plan.name} Plan - Monthly`
    )
 
    await this.db.prepare(`
      UPDATE invoices SET stripe_invoice_id = ? WHERE id = ?
    `).bind(stripeInvoice.id, invoiceId).run()
 
    return {
      id: invoiceId,
      tenantId,
      subscriptionId,
      invoiceNumber,
      status: 'open',
      amountDue: plan.price,
      amountPaid: 0,
      currency: 'usd',
      periodStart: subscription.current_period_start,
      periodEnd: subscription.current_period_end,
      stripeInvoiceId: stripeInvoice.id,
      createdAt: now,
      dueAt: subscription.current_period_end,
      items: [{
        id: itemId,
        invoiceId,
        description: `${plan.name} Plan - Monthly`,
        quantity: 1,
        unitAmount: plan.price,
        amount: plan.price,
      }],
    }
  }
 
  /**
   * 生成发票编号
   */
  private async generateInvoiceNumber(): Promise<string> {
    const now = new Date()
    const year = now.getFullYear()
    const month = String(now.getMonth() + 1).padStart(2, '0')
 
    // 获取本月已有的发票数量
    const count = await this.db.prepare(`
      SELECT COUNT(*) as count FROM invoices
      WHERE invoice_number LIKE ?
    `).bind(`INV-${year}${month}%`).first()
 
    const seq = String((count?.count || 0) + 1).padStart(4, '0')
 
    return `INV-${year}${month}-${seq}`
  }
 
  /**
   * 创建 Stripe 发票
   */
  private async createStripeInvoice(
    tenantId: string,
    invoiceId: string,
    amount: number,
    description: string
  ) {
    const stripe = new Stripe(this.env.STRIPE_SECRET_KEY)
 
    // 获取 Stripe 客户 ID
    const tenant = await this.db.prepare(`
      SELECT stripe_customer_id FROM tenants WHERE id = ?
    `).bind(tenantId).first()
 
    if (!tenant?.stripe_customer_id) {
      throw new Error('Customer not found in Stripe')
    }
 
    // 创建发票项
    await stripe.invoiceItems.create({
      customer: tenant.stripe_customer_id,
      amount,
      currency: 'usd',
      description,
      metadata: { invoiceId },
    })
 
    // 创建发票
    return stripe.invoices.create({
      customer: tenant.stripe_customer_id,
      auto_advance: true,
      metadata: { invoiceId },
    })
  }
}

// src/routes/payment-methods.ts
import { Hono } from 'hono'
import { zValidator } from '@hono/zod-validator'
import { z } from 'zod'
import Stripe from 'stripe'
 
const app = new Hono()
 
// 获取支付方式列表
app.get('/', async (c) => {
  const tenantId = c.get('tenantId')
 
  const methods = await c.env.DB.prepare(`
    SELECT * FROM payment_methods WHERE tenant_id = ?
    ORDER BY is_default DESC, created_at DESC
  `).bind(tenantId).all()
 
  return c.json({
    paymentMethods: methods.results.map(m => ({
      id: m.id,
      type: m.type,
      isDefault: m.is_default === 1,
      card: m.type === 'card' ? {
        brand: m.card_brand,
        last4: m.card_last4,
        expMonth: m.card_exp_month,
        expYear: m.card_exp_year,
      } : null,
    })),
  })
})
 
// 添加支付方式（通过 Stripe SetupIntent）
app.post('/setup', async (c) => {
  const tenantId = c.get('tenantId')
  const stripe = new Stripe(c.env.STRIPE_SECRET_KEY)
 
  // 获取 Stripe 客户 ID
  const tenant = await c.env.DB.prepare(`
    SELECT stripe_customer_id FROM tenants WHERE id = ?
  `).bind(tenantId).first()
 
  if (!tenant?.stripe_customer_id) {
    return c.json({ error: 'Customer not found' }, 404)
  }
 
  // 创建 SetupIntent
  const setupIntent = await stripe.setupIntents.create({
    customer: tenant.stripe_customer_id,
    payment_method_types: ['card'],
  })
 
  return c.json({
    clientSecret: setupIntent.client_secret,
  })
})
 
// 确认支付方式（Webhook 回调）
app.post('/confirm', zValidator('json', z.object({
  paymentMethodId: z.string(),
})), async (c) => {
  const tenantId = c.get('tenantId')
  const { paymentMethodId } = c.req.valid('json')
  const stripe = new Stripe(c.env.STRIPE_SECRET_KEY)
 
  // 获取支付方式详情
  const pm = await stripe.paymentMethods.retrieve(paymentMethodId)
 
  // 保存到数据库
  const id = crypto.randomUUID()
  const now = Date.now()
 
  // 检查是否是第一个支付方式
  const count = await c.env.DB.prepare(`
    SELECT COUNT(*) as count FROM payment_methods WHERE tenant_id = ?
  `).bind(tenantId).first()
 
  const isDefault = count?.count === 0 ? 1 : 0
 
  await c.env.DB.prepare(`
    INSERT INTO payment_methods (id, tenant_id, type, stripe_payment_method_id, is_default, card_brand, card_last4, card_exp_month, card_exp_year, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `).bind(
    id,
    tenantId,
    pm.type,
    pm.id,
    isDefault,
    pm.card?.brand || null,
    pm.card?.last4 || null,
    pm.card?.exp_month || null,
    pm.card?.exp_year || null,
    now
  ).run()
 
  return c.json({ success: true, paymentMethodId: id })
})
 
// 设置默认支付方式
app.put('/:id/default', async (c) => {
  const tenantId = c.get('tenantId')
  const methodId = c.req.param('id')
 
  // 验证支付方式属于该租户
  const method = await c.env.DB.prepare(`
    SELECT * FROM payment_methods WHERE id = ? AND tenant_id = ?
  `).bind(methodId, tenantId).first()
 
  if (!method) {
    return c.json({ error: 'Payment method not found' }, 404)
  }
 
  // 取消其他默认
  await c.env.DB.prepare(`
    UPDATE payment_methods SET is_default = 0 WHERE tenant_id = ?
  `).bind(tenantId).run()
 
  // 设置新的默认
  await c.env.DB.prepare(`
    UPDATE payment_methods SET is_default = 1 WHERE id = ?
  `).bind(methodId).run()
 
  // 同步到 Stripe
  const tenant = await c.env.DB.prepare(`
    SELECT stripe_customer_id FROM tenants WHERE id = ?
  `).bind(tenantId).first()
 
  const stripe = new Stripe(c.env.STRIPE_SECRET_KEY)
  await stripe.customers.update(tenant.stripe_customer_id, {
    invoice_settings: {
      default_payment_method: method.stripe_payment_method_id,
    },
  })
 
  return c.json({ success: true })
})
 
// 删除支付方式
app.delete('/:id', async (c) => {
  const tenantId = c.get('tenantId')
  const methodId = c.req.param('id')
 
  const method = await c.env.DB.prepare(`
    SELECT * FROM payment_methods WHERE id = ? AND tenant_id = ?
  `).bind(methodId, tenantId).first()
 
  if (!method) {
    return c.json({ error: 'Payment method not found' }, 404)
  }
 
  // 不能删除默认支付方式（如果有订阅）
  if (method.is_default) {
    const hasSubscription = await c.env.DB.prepare(`
      SELECT id FROM subscriptions WHERE tenant_id = ? AND status = 'active'
    `).bind(tenantId).first()
 
    if (hasSubscription) {
      return c.json({ error: 'Cannot delete default payment method' }, 400)
    }
  }
 
  // 从 Stripe 删除
  if (method.stripe_payment_method_id) {
    const stripe = new Stripe(c.env.STRIPE_SECRET_KEY)
    await stripe.paymentMethods.detach(method.stripe_payment_method_id)
  }
 
  // 从数据库删除
  await c.env.DB.prepare(`
    DELETE FROM payment_methods WHERE id = ?
  `).bind(methodId).run()
 
  return c.json({ success: true })
})

// src/routes/invoices.ts
import { Hono } from 'hono'
 
const app = new Hono()
 
// 获取账单列表
app.get('/', async (c) => {
  const tenantId = c.get('tenantId')
  const status = c.req.query('status')
  const page = parseInt(c.req.query('page') || '1')
  const pageSize = parseInt(c.req.query('pageSize') || '20')
  const offset = (page - 1) * pageSize
 
  let query = `SELECT * FROM invoices WHERE tenant_id = ?`
  const params: any[] = [tenantId]
 
  if (status) {
    query += ` AND status = ?`
    params.push(status)
  }
 
  query += ` ORDER BY created_at DESC LIMIT ? OFFSET ?`
  params.push(pageSize, offset)
 
  const invoices = await c.env.DB.prepare(query).bind(...params).all()
 
  // 获取账单明细
  const invoicesWithItems = await Promise.all(
    invoices.results.map(async (invoice) => {
      const items = await c.env.DB.prepare(`
        SELECT * FROM invoice_items WHERE invoice_id = ?
      `).bind(invoice.id).all()
 
      return {
        ...invoice,
        items: items.results,
      }
    })
  )
 
  // 获取总数
  let countQuery = `SELECT COUNT(*) as count FROM invoices WHERE tenant_id = ?`
  const countParams: any[] = [tenantId]
 
  if (status) {
    countQuery += ` AND status = ?`
    countParams.push(status)
  }
 
  const total = await c.env.DB.prepare(countQuery).bind(...countParams).first()
 
  return c.json({
    invoices: invoicesWithItems,
    pagination: {
      page,
      pageSize,
      total: total.count,
    },
  })
})
 
// 获取单个账单
app.get('/:id', async (c) => {
  const tenantId = c.get('tenantId')
  const invoiceId = c.req.param('id')
 
  const invoice = await c.env.DB.prepare(`
    SELECT * FROM invoices WHERE id = ? AND tenant_id = ?
  `).bind(invoiceId, tenantId).first()
 
  if (!invoice) {
    return c.json({ error: 'Invoice not found' }, 404)
  }
 
  const items = await c.env.DB.prepare(`
    SELECT * FROM invoice_items WHERE invoice_id = ?
  `).bind(invoiceId).all()
 
  return c.json({
    ...invoice,
    items: items.results,
  })
})
 
// 下载 PDF 发票
app.get('/:id/pdf', async (c) => {
  const tenantId = c.get('tenantId')
  const invoiceId = c.req.param('id')
 
  const invoice = await c.env.DB.prepare(`
    SELECT * FROM invoices WHERE id = ? AND tenant_id = ?
  `).bind(invoiceId, tenantId).first()
 
  if (!invoice?.stripe_invoice_id) {
    return c.json({ error: 'Invoice not found' }, 404)
  }
 
  const stripe = new Stripe(c.env.STRIPE_SECRET_KEY)
  const stripeInvoice = await stripe.invoices.retrieve(invoice.stripe_invoice_id)
 
  if (!stripeInvoice.invoice_pdf) {
    return c.json({ error: 'PDF not available' }, 404)
  }
 
  // 重定向到 Stripe PDF
  return c.redirect(stripeInvoice.invoice_pdf)
})

// src/routes/credits.ts
import { Hono } from 'hono'
import { zValidator } from '@hono/zod-validator'
import { z } from 'zod'
 
const app = new Hono()
 
// 发放信用额度（管理员操作）
app.post('/issue', zValidator('json', z.object({
  tenantId: z.string(),
  amount: z.number().positive(),
  reason: z.string().optional(),
  expiresAt: z.number().optional(),
})), async (c) => {
  const { tenantId, amount, reason, expiresAt } = c.req.valid('json')
 
  const id = crypto.randomUUID()
  const now = Date.now()
 
  await c.env.DB.prepare(`
    INSERT INTO credits (id, tenant_id, amount, remaining, reason, expires_at, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `).bind(
    id,
    tenantId,
    amount,
    amount,
    reason || null,
    expiresAt || null,
    now
  ).run()
 
  return c.json({ success: true, creditId: id })
})
 
// 使用信用额度抵扣账单
async function applyCreditToInvoice(
  invoiceId: string,
  tenantId: string,
  env: Env
) {
  // 获取账单
  const invoice = await env.DB.prepare(`
    SELECT * FROM invoices WHERE id = ? AND tenant_id = ?
  `).bind(invoiceId, tenantId).first()
 
  if (!invoice || invoice.status !== 'open') {
    throw new Error('Invoice not found or not open')
  }
 
  // 获取可用信用额度
  const credits = await env.DB.prepare(`
    SELECT * FROM credits
    WHERE tenant_id = ? AND remaining > 0
    AND (expires_at IS NULL OR expires_at > ?)
    ORDER BY created_at ASC
  `).bind(tenantId, Date.now()).all()
 
  let remainingDue = invoice.amount_due - invoice.amount_paid
 
  for (const credit of credits.results) {
    if (remainingDue <= 0) break
 
    const useAmount = Math.min(credit.remaining, remainingDue)
 
    // 更新信用额度
    await env.DB.prepare(`
      UPDATE credits SET remaining = remaining - ? WHERE id = ?
    `).bind(useAmount, credit.id).run()
 
    // 更新账单已付金额
    await env.DB.prepare(`
      UPDATE invoices SET amount_paid = amount_paid + ? WHERE id = ?
    `).bind(useAmount, invoiceId).run()
 
    remainingDue -= useAmount
  }
 
  // 检查是否已全额支付
  const updatedInvoice = await env.DB.prepare(`
    SELECT * FROM invoices WHERE id = ?
  `).bind(invoiceId).first()
 
  if (updatedInvoice.amount_paid >= updatedInvoice.amount_due) {
    await env.DB.prepare(`
      UPDATE invoices SET status = 'paid', paid_at = ? WHERE id = ?
    `).bind(Date.now(), invoiceId).run()
  }
}
 
// 获取租户信用额度
app.get('/', async (c) => {
  const tenantId = c.get('tenantId')
 
  const credits = await c.env.DB.prepare(`
    SELECT * FROM credits
    WHERE tenant_id = ? AND remaining > 0
    AND (expires_at IS NULL OR expires_at > ?)
    ORDER BY created_at DESC
  `).bind(tenantId, Date.now()).all()
 
  const totalCredit = credits.results.reduce(
    (sum, c) => sum + c.remaining,
    0
  )
 
  return c.json({
    credits: credits.results,
    totalCredit,
  })
})

// src/routes/webhook.ts（补充）
async function handleStripeEvent(event: Stripe.Event, env: Env) {
  switch (event.type) {
    // ... 其他事件
 
    case 'invoice.paid':
      await handleInvoicePaid(event.data.object as Stripe.Invoice, env)
      break
 
    case 'invoice.payment_failed':
      await handleInvoicePaymentFailed(event.data.object as Stripe.Invoice, env)
      break
  }
}
 
async function handleInvoicePaid(
  invoice: Stripe.Invoice,
  env: Env
) {
  // 查找本地账单
  const localInvoice = await env.DB.prepare(`
    SELECT * FROM invoices WHERE stripe_invoice_id = ?
  `).bind(invoice.id).first()
 
  if (!localInvoice) return
 
  // 更新账单状态
  await env.DB.prepare(`
    UPDATE invoices
    SET status = 'paid', amount_paid = ?, paid_at = ?
    WHERE id = ?
  `).bind(invoice.amount_paid, Date.now(), localInvoice.id).run()
}
 
async function handleInvoicePaymentFailed(
  invoice: Stripe.Invoice,
  env: Env
) {
  const localInvoice = await env.DB.prepare(`
    SELECT * FROM invoices WHERE stripe_invoice_id = ?
  `).bind(invoice.id).first()
 
  if (!localInvoice) return
 
  // 更新账单状态
  await env.DB.prepare(`
    UPDATE invoices SET status = 'open' WHERE id = ?
  `).bind(localInvoice.id).run()
 
  // 发送催款通知
  await sendPaymentFailedEmail(localInvoice.tenant_id, env)
}