/**
 * Test Table Schema for client-table-v2 테스트용 스키마
 * 
 * 3가지 패턴 테스트를 위한 테이블:
 * 1. testProducts - 기본 상품 테이블 (Client-Side, Factory Service 패턴용)
 * 2. testOrders - 주문 테이블 (Custom Service 패턴용 - 조인 테스트)
 * 3. testCustomers - 고객 테이블 (Custom Service 패턴용 - 조인 테스트)
 */

import {
  integer,
  serial,
  pgTable,
  varchar,
  timestamp,
  pgEnum,
  text,
  numeric,
  boolean,
  index,
} from "drizzle-orm/pg-core";

// === Enums ===

export const testProductStatusEnum = pgEnum("test_product_status", [
  "active",
  "inactive",
  "discontinued",
]);

export const testOrderStatusEnum = pgEnum("test_order_status", [
  "pending",
  "processing",
  "shipped",
  "delivered",
  "cancelled",
]);

// === Tables ===

/**
 * 상품 테이블 - Client-Side 및 Factory Service 패턴 테스트용
 */
export const testProducts = pgTable(
  "test_products",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    name: varchar("name", { length: 255 }).notNull(),
    sku: varchar("sku", { length: 50 }).notNull().unique(),
    description: text("description"),
    category: varchar("category", { length: 100 }).notNull(),
    price: numeric("price", { precision: 10, scale: 2 }).notNull(),
    stock: integer("stock").notNull().default(0),
    status: testProductStatusEnum("status").notNull().default("active"),
    isNew: boolean("is_new").default(false),
    createdAt: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
  },
  (table) => {
    return {
      categoryIdx: index("test_products_category_idx").on(table.category),
      statusIdx: index("test_products_status_idx").on(table.status),
    };
  }
);

/**
 * 고객 테이블 - Custom Service 패턴의 조인 테스트용
 */
export const testCustomers = pgTable(
  "test_customers",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    name: varchar("name", { length: 255 }).notNull(),
    email: varchar("email", { length: 255 }).notNull().unique(),
    phone: varchar("phone", { length: 40 }),
    country: varchar("country", { length: 100 }),
    tier: varchar("tier", { length: 40 }).notNull().default("standard"), // standard, premium, vip
    totalOrders: integer("total_orders").default(0),
    createdAt: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
  },
  (table) => {
    return {
      tierIdx: index("test_customers_tier_idx").on(table.tier),
    };
  }
);

/**
 * 주문 테이블 - Custom Service 패턴용 (고객/상품 조인)
 */
export const testOrders = pgTable(
  "test_orders",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    orderNumber: varchar("order_number", { length: 50 }).notNull().unique(),
    customerId: integer("customer_id")
      .references(() => testCustomers.id, { onDelete: "cascade" })
      .notNull(),
    productId: integer("product_id")
      .references(() => testProducts.id, { onDelete: "set null" }),
    quantity: integer("quantity").notNull().default(1),
    unitPrice: numeric("unit_price", { precision: 10, scale: 2 }).notNull(),
    totalAmount: numeric("total_amount", { precision: 10, scale: 2 }).notNull(),
    status: testOrderStatusEnum("status").notNull().default("pending"),
    notes: text("notes"),
    orderedAt: timestamp("ordered_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    shippedAt: timestamp("shipped_at", { withTimezone: true }),
    deliveredAt: timestamp("delivered_at", { withTimezone: true }),
  },
  (table) => {
    return {
      customerIdx: index("test_orders_customer_idx").on(table.customerId),
      productIdx: index("test_orders_product_idx").on(table.productId),
      statusIdx: index("test_orders_status_idx").on(table.status),
      orderedAtIdx: index("test_orders_ordered_at_idx").on(table.orderedAt),
    };
  }
);

// === Types ===

export type TestProduct = typeof testProducts.$inferSelect;
export type NewTestProduct = typeof testProducts.$inferInsert;

export type TestCustomer = typeof testCustomers.$inferSelect;
export type NewTestCustomer = typeof testCustomers.$inferInsert;

export type TestOrder = typeof testOrders.$inferSelect;
export type NewTestOrder = typeof testOrders.$inferInsert;