import { sql } from 'drizzle-orm'
import {
  check,
  index,
  integer,
  pgTable,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from 'drizzle-orm/pg-core'
import type { ReviewStatus } from './types.js'

export const review = pgTable(
  'review',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    productId: text('product_id').notNull(),
    userId: text('user_id').notNull(),
    purchaseId: text('purchase_id').notNull(),
    vendorId: text('vendor_id'),
    rating: integer('rating').notNull(),
    body: text('body'),
    status: text('status').$type<ReviewStatus>().notNull().default('pending'),
    vendorReply: text('vendor_reply'),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
  },
  (t) => [
    check('review_rating_chk', sql`${t.rating} >= 1 AND ${t.rating} <= 5`),
    check('review_body_len_chk', sql`${t.body} IS NULL OR length(${t.body}) <= 5000`), // must match REVIEW_BODY_MAX (submit.ts)
    check('review_reply_len_chk', sql`${t.vendorReply} IS NULL OR length(${t.vendorReply}) <= 5000`), // must match REVIEW_REPLY_MAX (reply.ts)
    check('review_status_chk', sql`${t.status} IN ('pending', 'approved', 'rejected')`),
    uniqueIndex('review_user_product_purchase_uq').on(t.userId, t.productId, t.purchaseId),
    index('review_product_status_idx').on(t.productId, t.status),
  ],
)

export const reviewsSchema = {
  review,
}

export type ReviewsSchema = typeof reviewsSchema