import { sql } from 'drizzle-orm'
import {
  bigint,
  check,
  integer,
  jsonb,
  pgTable,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from 'drizzle-orm/pg-core'
import type { OrderStatus, PriceMode, ProductKind } from './types.js'

export const order = pgTable(
  'order',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    buyerUserId: uuid('buyer_user_id'),
    buyerGuestEmail: text('buyer_guest_email'),
    status: text('status').$type<OrderStatus>().notNull().default('pending'),
    currency: text('currency').notNull(),
    priceMode: text('price_mode').$type<PriceMode>().notNull(),
    subtotal: bigint('subtotal', { mode: 'bigint' }).notNull(),
    tax: bigint('tax', { mode: 'bigint' }).notNull(),
    discount: bigint('discount', { mode: 'bigint' }).notNull(),
    total: bigint('total', { mode: 'bigint' }).notNull(),
    chargeRef: text('charge_ref'),
    failureReason: text('failure_reason'),
    idempotencyKey: text('idempotency_key').notNull(),
    requestHash: text('request_hash').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('order_buyer_chk', sql`num_nonnulls(${t.buyerUserId}, ${t.buyerGuestEmail}) = 1`),
    uniqueIndex('uq_order_idempotency_key').on(t.idempotencyKey),
  ],
)

export const orderLine = pgTable('order_line', {
  id: uuid('id').primaryKey().defaultRandom(),
  orderId: uuid('order_id').notNull(),
  variantId: uuid('variant_id').notNull(),
  kind: text('kind').$type<ProductKind>().notNull(),
  qty: integer('qty').notNull(),
  unitPrice: bigint('unit_price', { mode: 'bigint' }).notNull(),
  lineTotal: bigint('line_total', { mode: 'bigint' }).notNull(),
  vendorId: text('vendor_id'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const vendorSplit = pgTable('vendor_split', {
  id: uuid('id').primaryKey().defaultRandom(),
  orderId: uuid('order_id').notNull(),
  vendorId: text('vendor_id'),
  amount: bigint('amount', { mode: 'bigint' }).notNull(),
  funder: text('funder').$type<'platform' | 'vendor'>().notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const orderStep = pgTable(
  'order_step',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    orderId: uuid('order_id').notNull(),
    stepId: text('step_id').notNull(),
    result: jsonb('result').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex('uq_order_step').on(t.orderId, t.stepId)],
)

export const refundIntent = pgTable(
  'refund_intent',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    orderId: uuid('order_id').notNull(),
    seq: integer('seq').notNull(),
    amount: bigint('amount', { mode: 'bigint' }).notNull(),
    status: text('status').$type<'pending' | 'executed' | 'failed'>().notNull().default('pending'),
    refundKey: text('refund_key').notNull(),
    providerRef: text('provider_ref'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('uq_refund_intent_seq').on(t.orderId, t.seq),
    check('refund_intent_amount_chk', sql`${t.amount} > 0`),
  ],
)

export const ordersSchema = {
  order,
  orderLine,
  vendorSplit,
  orderStep,
  refundIntent,
}

export type OrdersSchema = typeof ordersSchema