// db/schema/billing.ts
import {
  pgTable,
  text,
  timestamp,
  numeric,
  boolean,
  uuid,
  index,
  jsonb,
} from "drizzle-orm/pg-core";

export const invoices = pgTable(
  "invoices",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    stripeInvoiceId: text("stripe_invoice_id").notNull().unique(),
    invoiceNumber: text("invoice_number").notNull().unique(),
    customerId: text("customer_id").notNull(),
    supplierVatId: text("supplier_vat_id").notNull(),
    customerVatId: text("customer_vat_id"),
    currency: text("currency").notNull(),
    invoiceDate: timestamp("invoice_date").notNull(),
    supplyDate: timestamp("supply_date").notNull(),
    periodStart: timestamp("period_start"),
    periodEnd: timestamp("period_end"),
    subtotal: numeric("subtotal", { precision: 12, scale: 2 }).notNull(),
    taxTotal: numeric("tax_total", { precision: 12, scale: 2 }).notNull(),
    grandTotal: numeric("grand_total", { precision: 12, scale: 2 }).notNull(),
    isReverseCharge: boolean("is_reverse_charge").notNull().default(false),
    complianceFlags: jsonb("compliance_flags").$type<string[]>().notNull().default([]),
    finalizedAt: timestamp("finalized_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => ({
    customerIdx: index("invoices_customer_idx").on(t.customerId),
    numberIdx: index("invoices_number_idx").on(t.invoiceNumber),
  })
);

export const invoiceLines = pgTable("invoice_lines", {
  id: uuid("id").primaryKey().defaultRandom(),
  invoiceId: uuid("invoice_id")
    .notNull()
    .references(() => invoices.id, { onDelete: "cascade" }),
  description: text("description").notNull(),
  quantity: numeric("quantity", { precision: 12, scale: 3 }).notNull(),
  unitPrice: numeric("unit_price", { precision: 12, scale: 2 }).notNull(),
  netAmount: numeric("net_amount", { precision: 12, scale: 2 }).notNull(),
  taxRate: numeric("tax_rate", { precision: 6, scale: 4 }).notNull(),
  taxAmount: numeric("tax_amount", { precision: 12, scale: 2 }).notNull(),
  taxCode: text("tax_code"),
});

export const taxIds = pgTable("tax_ids", {
  id: uuid("id").primaryKey().defaultRandom(),
  customerId: text("customer_id").notNull(),
  country: text("country").notNull(),
  type: text("type").notNull(),
  value: text("value").notNull(),
  validatedAt: timestamp("validated_at"),
  validationSource: text("validation_source"),
});

// server/billing/invoice-generator.ts
import Stripe from "stripe";
import { db } from "~/db";
import { invoices, invoiceLines, taxIds } from "~/db/schema/billing";
import { eq } from "drizzle-orm";

type ComplianceFlag =
  | "MISSING_SUPPLIER_VAT"
  | "MISSING_CUSTOMER_VAT_B2B"
  | "MIXED_CURRENCY"
  | "BLANK_LINE_DESCRIPTION"
  | "REVERSE_CHARGE_NOTE_MISSING";

export async function generateCompliantInvoice(input: {
  stripeCustomerId: string;
  periodStart: Date;
  periodEnd: Date;
  stripeInvoiceId: string;
  lines: Array<{
    description: string;
    quantity: number;
    unitPrice: number;
    taxCode: string;
  }>;
  currency: string;
}) {
  const flags: ComplianceFlag[] = [];

  // 一次性拉取供应商和客户税号。
  const customerTaxId = await db.query.taxIds.findFirst({
    where: eq(taxIds.customerId, input.stripeCustomerId),
  });

  // 规则 2：供应商 VAT 号必须在 env 中设置。
  const supplierVat = process.env.COMPANY_VAT_ID;
  if (!supplierVat) flags.push("MISSING_SUPPLIER_VAT");

  // 规则 3：B2B 跨境场景需要客户 VAT 号。
  const isCrossBorderB2B =
    input.currency === "EUR" && customerTaxId?.country !== process.env.COMPANY_COUNTRY;
  if (isCrossBorderB2B && !customerTaxId) flags.push("MISSING_CUSTOMER_VAT_B2B");

  // 规则 5：每行必须有描述。
  if (input.lines.some((l) => !l.description.trim())) {
    flags.push("BLANK_LINE_DESCRIPTION");
  }

  // 构建 Stripe 发票。
  const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!);
  const finalized = await stripe.invoices.finalizeInvoice(input.stripeInvoiceId, {
    auto_advance: false,
  });

  // 带审计追踪持久化到自有 DB。
  const subtotal = input.lines.reduce(
    (acc, l) => acc + l.quantity * l.unitPrice,
    0
  );
  const taxTotal = finalized.tax!;
  const grandTotal = subtotal + (taxTotal ?? 0);

  await db.insert(invoices).values({
    stripeInvoiceId: finalized.id!,
    invoiceNumber: finalized.number!,
    customerId: input.stripeCustomerId,
    supplierVatId: supplierVat ?? "",
    customerVatId: customerTaxId?.value ?? null,
    currency: input.currency,
    invoiceDate: new Date(finalized.created * 1000),
    supplyDate: input.periodEnd,
    periodStart: input.periodStart,
    periodEnd: input.periodEnd,
    subtotal: subtotal.toFixed(2),
    taxTotal: (taxTotal ?? 0).toFixed(2),
    grandTotal: grandTotal.toFixed(2),
    isReverseCharge:
      isCrossBorderB2B && Boolean(customerTaxId) && (taxTotal ?? 0) === 0,
    complianceFlags: flags,
  });

  return { stripeInvoiceId: finalized.id, flags };
}