import { sql } from 'drizzle-orm'
import { ordersSchema } from '@platform-modules/commerce-orders'
import {
  check,
  integer,
  jsonb,
  pgTable,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from 'drizzle-orm/pg-core'
import type { ShipmentStatus, VoucherState } from './types.js'

export const accessGrant = pgTable(
  'access_grant',
  {
    id: uuid('id').primaryKey(),
    orderId: uuid('order_id').notNull(),
    itemId: text('item_id').notNull(),
    ownerKey: text('owner_key').notNull(),
    blobKey: text('blob_key').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('uq_access_grant_owner').on(t.orderId, t.itemId, t.ownerKey),
  ],
)

export const voucher = pgTable(
  'voucher',
  {
    id: uuid('id').primaryKey(),
    orderId: uuid('order_id').notNull(),
    lineId: text('line_id').notNull(),
    unitIndex: integer('unit_index').notNull(),
    vendorId: text('vendor_id'),
    state: text('state').$type<VoucherState>().notNull().default('UNREDEEMED'),
    expiresAt: timestamp('expires_at', { withTimezone: true }),
    redeemedAt: timestamp('redeemed_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('uq_voucher_unit').on(t.orderId, t.lineId, t.unitIndex),
    check(
      'voucher_state_chk',
      sql`${t.state} IN ('UNREDEEMED', 'REDEEMED', 'EXPIRED', 'CANCELLED')`,
    ),
    check('voucher_unit_index_chk', sql`${t.unitIndex} >= 0`),
  ],
)

export const shipment = pgTable(
  'shipment',
  {
    id: uuid('id').primaryKey(),
    orderId: uuid('order_id').notNull(),
    lineIds: jsonb('line_ids').$type<string[]>().notNull(),
    address: jsonb('address').notNull(),
    status: text('status').$type<ShipmentStatus>().notNull().default('pending'),
    carrierKind: text('carrier_kind'),
    labelId: text('label_id'),
    trackingNumber: text('tracking_number'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check(
      'shipment_status_chk',
      sql`${t.status} IN ('pending', 'labeled', 'in_transit', 'delivered', 'failed')`,
    ),
  ],
)

export const carrierWebhookEvent = pgTable('carrier_webhook_event', {
  eventId: text('event_id').primaryKey(),
  shipmentId: uuid('shipment_id'),
  receivedAt: timestamp('received_at', { withTimezone: true }).notNull().defaultNow(),
})

export const fulfillmentSchema = {
  accessGrant,
  voucher,
  shipment,
  carrierWebhookEvent,
}

export type FulfillmentSchema = typeof fulfillmentSchema

export const fulfillmentDbSchema = { ...fulfillmentSchema, ...ordersSchema }

export type FulfillmentDbSchema = typeof fulfillmentDbSchema