import { boolean, index, integer, numeric, pgTable, text, timestamp, uniqueIndex, uuid } from 'drizzle-orm/pg-core'
import { tenants } from './tenants'
import { stockLocations } from './stock-locations'

export const inventoryItem = pgTable('inventory_item', {
  skuId: uuid('sku_id').primaryKey(),
  vendorId: text('vendor_id'),
  quantityTotal: integer('quantity_total'),
  quantitySold: integer('quantity_sold').notNull().default(0),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

export const stockReservation = pgTable('stock_reservation', {
  id: uuid('id').primaryKey().defaultRandom(),
  skuId: uuid('sku_id')
    .notNull()
    .references(() => inventoryItem.skuId),
  qty: integer('qty').notNull(),
  holderRef: text('holder_ref').notNull(),
  expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
  consumedAt: timestamp('consumed_at', { withTimezone: true }),
  releasedAt: timestamp('released_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const stockPosition = pgTable(
  'stock_position',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    itemId: uuid('item_id')
      .notNull()
      .references(() => inventoryItem.skuId),
    locationId: uuid('location_id')
      .notNull()
      .references(() => stockLocations.id),
    qtyOnHand: numeric('qty_on_hand', { precision: 19, scale: 4 }).notNull().default('0'),
    avgUnitCost: numeric('avg_unit_cost', { precision: 19, scale: 4 }).notNull().default('0'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantIdx: index('idx_stock_position_tenant').on(t.tenantId),
    itemIdx: index('idx_stock_position_item').on(t.tenantId, t.itemId),
    locationUnique: uniqueIndex('uq_stock_position_location').on(t.tenantId, t.itemId, t.locationId),
  }),
)

export const stockMovement = pgTable(
  'stock_movement',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    itemId: uuid('item_id')
      .notNull()
      .references(() => inventoryItem.skuId),
    locationId: uuid('location_id')
      .notNull()
      .references(() => stockLocations.id),
    kind: text('kind').notNull(),
    qtyDelta: numeric('qty_delta', { precision: 19, scale: 4 }).notNull(),
    unitCost: numeric('unit_cost', { precision: 19, scale: 4 }),
    cogsAmount: numeric('cogs_amount', { precision: 19, scale: 4 }),
    pendingCost: boolean('pending_cost').notNull().default(false),
    holderRef: text('holder_ref'),
    transferGroup: uuid('transfer_group'),
    occurredAt: timestamp('occurred_at', { withTimezone: true }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantItemIdx: index('idx_stock_movement_tenant_item').on(t.tenantId, t.itemId),
    occurredAtIdx: index('idx_stock_movement_occurred_at').on(t.tenantId, t.occurredAt),
  }),
)

export type InventoryItemRow = typeof inventoryItem.$inferSelect
export type NewInventoryItemRow = typeof inventoryItem.$inferInsert
export type StockReservationRow = typeof stockReservation.$inferSelect
export type NewStockReservationRow = typeof stockReservation.$inferInsert
export type StockPositionRow = typeof stockPosition.$inferSelect
export type NewStockPositionRow = typeof stockPosition.$inferInsert
export type StockMovementRow = typeof stockMovement.$inferSelect
export type NewStockMovementRow = typeof stockMovement.$inferInsert