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

export const product = pgTable(
  'product',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    kind: text('kind').$type<ProductKind>().notNull(),
    vendorId: uuid('vendor_id'),
    slug: text('slug').notNull(),
    title: text('title').notNull(),
    description: text('description'),
    status: text('status').$type<ProductStatus>().notNull().default('draft'),
    media: jsonb('media').$type<MediaRef[]>().notNull().default(sql`'[]'::jsonb`),
    tags: jsonb('tags').$type<string[]>().notNull().default(sql`'[]'::jsonb`),
    availableFrom: timestamp('available_from', { withTimezone: true, precision: 3 }),
    availableUntil: timestamp('available_until', { withTimezone: true, precision: 3 }),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('product_vendor_slug_uq').on(t.vendorId, t.slug),
    index('product_vendor_idx').on(t.vendorId),
    index('product_status_idx').on(t.status),
  ],
)

export const variant = pgTable(
  'variant',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    productId: uuid('product_id').notNull(),
    sku: text('sku').notNull(),
    attributes: jsonb('attributes').$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex('variant_product_sku_uq').on(t.productId, t.sku)],
)

export const variantPrice = pgTable(
  'variant_price',
  {
    variantId: uuid('variant_id').notNull(),
    currency: text('currency').notNull(),
    amount: bigint('amount', { mode: 'bigint' }).notNull(),
    priceMode: text('price_mode').$type<PriceMode>().notNull(),
  },
  (t) => [
    primaryKey({ columns: [t.variantId, t.currency] }),
    uniqueIndex('variant_price_variant_currency_uq').on(t.variantId, t.currency),
  ],
)

export const category = pgTable(
  'category',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    parentId: uuid('parent_id'),
    vendorId: uuid('vendor_id'),
    name: text('name').notNull(),
    slug: text('slug').notNull(),
  },
  (t) => [uniqueIndex('category_vendor_slug_uq').on(t.vendorId, t.slug)],
)

export const collection = pgTable(
  'collection',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    vendorId: uuid('vendor_id'),
    name: text('name').notNull(),
    slug: text('slug').notNull(),
  },
  (t) => [uniqueIndex('collection_vendor_slug_uq').on(t.vendorId, t.slug)],
)

export const productCategory = pgTable(
  'product_category',
  {
    productId: uuid('product_id').notNull(),
    categoryId: uuid('category_id').notNull(),
  },
  (t) => [primaryKey({ columns: [t.productId, t.categoryId] })],
)

export const productCollection = pgTable(
  'product_collection',
  {
    collectionId: uuid('collection_id').notNull(),
    productId: uuid('product_id').notNull(),
    position: integer('position').notNull().default(0),
  },
  (t) => [primaryKey({ columns: [t.collectionId, t.productId] })],
)

export const catalogSchema = {
  product,
  variant,
  variantPrice,
  category,
  collection,
  productCategory,
  productCollection,
}

export type CatalogSchema = typeof catalogSchema