import { sql } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import { ReviewsMigrateError } from './errors.js'
import type { ReviewsSchema } from './schema.js'

export async function pushReviewsSchema(db: Querier<ReviewsSchema>): Promise<void> {
  try {
    // Preflight: if the review table exists but lacks purchase_id, drop it (dev-only — no prod data)
    await db.execute(sql`
      DO $$
      BEGIN
        IF EXISTS (
          SELECT 1 FROM information_schema.tables WHERE table_name = 'review'
        ) AND NOT EXISTS (
          SELECT 1 FROM information_schema.columns WHERE table_name = 'review' AND column_name = 'purchase_id'
        ) THEN
          DROP TABLE review CASCADE;
        END IF;
      END$$
    `)

    // CREATE TABLE (includes purchase_id NOT NULL)
    await db.execute(sql`
      CREATE TABLE IF NOT EXISTS review (
        id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        product_id text NOT NULL,
        user_id text NOT NULL,
        purchase_id text NOT NULL,
        vendor_id text,
        rating integer NOT NULL CHECK (rating >= 1 AND rating <= 5),
        body text CHECK (body IS NULL OR length(body) <= 5000),
        status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
        vendor_reply text CHECK (vendor_reply IS NULL OR length(vendor_reply) <= 5000),
        created_at timestamptz(3) NOT NULL DEFAULT NOW(),
        updated_at timestamptz(3) NOT NULL DEFAULT NOW()
      )
    `)

    // Drop old unique index (if it exists from before the purchaseId change)
    await db.execute(sql`DROP INDEX IF EXISTS review_user_product_uq`)

    // New unique index on (user_id, product_id, purchase_id)
    await db.execute(sql`CREATE UNIQUE INDEX IF NOT EXISTS review_user_product_purchase_uq ON review (user_id, product_id, purchase_id)`)

    // Product-status index (unchanged)
    await db.execute(sql`CREATE INDEX IF NOT EXISTS review_product_status_idx ON review (product_id, status)`)
  } catch (e) {
    throw new ReviewsMigrateError(e instanceof Error ? e.message : String(e))
  }
}