import {
  boolean,
  decimal,
  index,
  int,
  mysqlEnum,
  mysqlTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  varchar,
} from "drizzle-orm/mysql-core";

const id = () => int("id", { unsigned: true }).autoincrement().primaryKey();
const createdAt = () => timestamp("created_at").defaultNow().notNull();
const updatedAt = () =>
  timestamp("updated_at").defaultNow().onUpdateNow().notNull();

export const staffRoleValues = ["seller", "admin"] as const;
export const otpActorTypeValues = ["customer", "staff"] as const;
export const otpPurposeValues = ["signup", "login", "password_reset"] as const;
export const productStatusValues = [
  "draft",
  "active",
  "archived",
  "out_of_stock",
] as const;
export const mediaOwnerTypeValues = [
  "product",
  "brand",
  "product_type",
  "collection",
  "frame_shape",
  "review",
  "staff",
  "customer",
] as const;
export const fulfillmentMethodValues = ["pickup", "delivery"] as const;
export const orderStatusValues = [
  "pending",
  "confirmed",
  "processing",
  "ready_for_pickup",
  "out_for_delivery",
  "completed",
  "cancelled",
  "refunded",
] as const;
export const paymentMethodValues = [
  "cash_on_delivery",
  "online",
  "bank_transfer",
] as const;
export const paymentStatusValues = [
  "pending",
  "paid",
  "failed",
  "refunded",
] as const;

export const customers = mysqlTable(
  "customers",
  {
    id: id(),
    email: varchar("email", { length: 255 }).notNull(),
    name: varchar("name", { length: 255 }),
    phoneNumber: varchar("phone_number", { length: 30 }),
    emailVerifiedAt: timestamp("email_verified_at"),
    lastLoginAt: timestamp("last_login_at"),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    uniqueIndex("customers_email_unique").on(table.email),
    uniqueIndex("customers_phone_number_unique").on(table.phoneNumber),
  ],
);

export const staffUsers = mysqlTable(
  "staff_users",
  {
    id: id(),
    name: varchar("name", { length: 255 }).notNull(),
    email: varchar("email", { length: 255 }).notNull(),
    passwordHash: varchar("password_hash", { length: 255 }).notNull(),
    role: mysqlEnum("role", staffRoleValues).notNull().default("seller"),
    isActive: boolean("is_active").notNull().default(true),
    emailVerifiedAt: timestamp("email_verified_at"),
    lastLoginAt: timestamp("last_login_at"),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("staff_users_email_unique").on(table.email)],
);

export const authSessions = mysqlTable(
  "auth_sessions",
  {
    id: id(),
    actorType: mysqlEnum("actor_type", otpActorTypeValues).notNull(),
    actorId: int("actor_id", { unsigned: true }).notNull(),
    refreshTokenHash: varchar("refresh_token_hash", { length: 255 }).notNull(),
    userAgent: varchar("user_agent", { length: 512 }),
    ipAddress: varchar("ip_address", { length: 80 }),
    expiresAt: timestamp("expires_at").notNull(),
    revokedAt: timestamp("revoked_at"),
    lastUsedAt: timestamp("last_used_at"),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    uniqueIndex("auth_sessions_refresh_token_hash_unique").on(
      table.refreshTokenHash,
    ),
    index("auth_sessions_actor_idx").on(table.actorType, table.actorId),
    index("auth_sessions_expires_idx").on(table.expiresAt),
  ],
);

export const authOtps = mysqlTable(
  "auth_otps",
  {
    id: id(),
    actorType: mysqlEnum("actor_type", otpActorTypeValues).notNull(),
    email: varchar("email", { length: 255 }).notNull(),
    codeHash: varchar("code_hash", { length: 255 }).notNull(),
    purpose: mysqlEnum("purpose", otpPurposeValues).notNull().default("login"),
    attempts: int("attempts", { unsigned: true }).notNull().default(0),
    expiresAt: timestamp("expires_at").notNull(),
    consumedAt: timestamp("consumed_at"),
    createdAt: createdAt(),
  },
  (table) => [
    index("auth_otps_email_idx").on(table.email),
    index("auth_otps_lookup_idx").on(
      table.actorType,
      table.email,
      table.purpose,
    ),
  ],
);

export const brands = mysqlTable(
  "brands",
  {
    id: id(),
    name: varchar("name", { length: 120 }).notNull(),
    slug: varchar("slug", { length: 140 }).notNull(),
    description: text("description"),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("brands_slug_unique").on(table.slug)],
);

export const productTypes = mysqlTable(
  "product_types",
  {
    id: id(),
    name: varchar("name", { length: 120 }).notNull(),
    slug: varchar("slug", { length: 140 }).notNull(),
    description: text("description"),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("product_types_slug_unique").on(table.slug)],
);

export const collections = mysqlTable(
  "collections",
  {
    id: id(),
    name: varchar("name", { length: 120 }).notNull(),
    slug: varchar("slug", { length: 140 }).notNull(),
    description: text("description"),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("collections_slug_unique").on(table.slug)],
);

export const occasions = mysqlTable(
  "occasions",
  {
    id: id(),
    name: varchar("name", { length: 120 }).notNull(),
    slug: varchar("slug", { length: 140 }).notNull(),
    description: text("description"),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("occasions_slug_unique").on(table.slug)],
);

export const frameShapes = mysqlTable(
  "frame_shapes",
  {
    id: id(),
    name: varchar("name", { length: 120 }).notNull(),
    slug: varchar("slug", { length: 140 }).notNull(),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("frame_shapes_slug_unique").on(table.slug)],
);

export const faceShapes = mysqlTable(
  "face_shapes",
  {
    id: id(),
    name: varchar("name", { length: 120 }).notNull(),
    slug: varchar("slug", { length: 140 }).notNull(),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("face_shapes_slug_unique").on(table.slug)],
);

export const genders = mysqlTable(
  "genders",
  {
    id: id(),
    name: varchar("name", { length: 80 }).notNull(),
    slug: varchar("slug", { length: 100 }).notNull(),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("genders_slug_unique").on(table.slug)],
);

export const frameColors = mysqlTable(
  "frame_colors",
  {
    id: id(),
    name: varchar("name", { length: 80 }).notNull(),
    slug: varchar("slug", { length: 100 }).notNull(),
    hexCode: varchar("hex_code", { length: 7 }),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("frame_colors_slug_unique").on(table.slug)],
);

export const lensFeatures = mysqlTable(
  "lens_features",
  {
    id: id(),
    name: varchar("name", { length: 140 }).notNull(),
    slug: varchar("slug", { length: 160 }).notNull(),
    description: text("description"),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("lens_features_slug_unique").on(table.slug)],
);

export const ageGroups = mysqlTable(
  "age_groups",
  {
    id: id(),
    name: varchar("name", { length: 80 }).notNull(),
    slug: varchar("slug", { length: 100 }).notNull(),
    minAge: int("min_age", { unsigned: true }),
    maxAge: int("max_age", { unsigned: true }),
    sortOrder: int("sort_order", { unsigned: true }).notNull().default(0),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("age_groups_slug_unique").on(table.slug)],
);

export const sizes = mysqlTable(
  "sizes",
  {
    id: id(),
    name: varchar("name", { length: 20 }).notNull(),
    slug: varchar("slug", { length: 40 }).notNull(),
    sortOrder: int("sort_order", { unsigned: true }).notNull().default(0),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("sizes_slug_unique").on(table.slug)],
);

export const priceRanges = mysqlTable(
  "price_ranges",
  {
    id: id(),
    name: varchar("name", { length: 120 }).notNull(),
    slug: varchar("slug", { length: 140 }).notNull(),
    minPrice: int("min_price", { unsigned: true }).notNull(),
    maxPrice: int("max_price", { unsigned: true }),
    sortOrder: int("sort_order", { unsigned: true }).notNull().default(0),
    isActive: boolean("is_active").notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [uniqueIndex("price_ranges_slug_unique").on(table.slug)],
);

export const products = mysqlTable(
  "products",
  {
    id: id(),
    sku: varchar("sku", { length: 80 }).notNull(),
    name: varchar("name", { length: 255 }).notNull(),
    slug: varchar("slug", { length: 280 }).notNull(),
    description: text("description"),
    brandId: int("brand_id", { unsigned: true }).references(() => brands.id),
    productTypeId: int("product_type_id", { unsigned: true }).references(
      () => productTypes.id,
    ),
    collectionId: int("collection_id", { unsigned: true }).references(
      () => collections.id,
    ),
    frameShapeId: int("frame_shape_id", { unsigned: true }).references(
      () => frameShapes.id,
    ),
    frameColorId: int("frame_color_id", { unsigned: true }).references(
      () => frameColors.id,
    ),
    ageGroupId: int("age_group_id", { unsigned: true }).references(
      () => ageGroups.id,
    ),
    sizeId: int("size_id", { unsigned: true }).references(() => sizes.id),
    basePrice: int("base_price", { unsigned: true }).notNull(),
    discountPercent: int("discount_percent", { unsigned: true })
      .notNull()
      .default(0),
    salePrice: int("sale_price", { unsigned: true }).notNull(),
    stockQuantity: int("stock_quantity", { unsigned: true })
      .notNull()
      .default(0),
    status: mysqlEnum("status", productStatusValues).notNull().default("draft"),
    isFeatured: boolean("is_featured").notNull().default(false),
    averageRating: decimal("average_rating", { precision: 3, scale: 2 })
      .notNull()
      .default("0.00"),
    ratingCount: int("rating_count", { unsigned: true }).notNull().default(0),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    uniqueIndex("products_sku_unique").on(table.sku),
    uniqueIndex("products_slug_unique").on(table.slug),
    index("products_brand_idx").on(table.brandId),
    index("products_product_type_idx").on(table.productTypeId),
    index("products_collection_idx").on(table.collectionId),
    index("products_price_idx").on(table.salePrice),
    index("products_status_idx").on(table.status),
    index("products_status_price_idx").on(table.status, table.salePrice),
    index("products_status_created_idx").on(table.status, table.createdAt),
    index("products_status_rating_idx").on(
      table.status,
      table.averageRating,
      table.ratingCount,
    ),
    index("products_status_featured_idx").on(table.status, table.isFeatured),
  ],
);

export const productGenders = mysqlTable(
  "product_genders",
  {
    productId: int("product_id", { unsigned: true })
      .notNull()
      .references(() => products.id),
    genderId: int("gender_id", { unsigned: true })
      .notNull()
      .references(() => genders.id),
  },
  (table) => [primaryKey({ columns: [table.productId, table.genderId] })],
);

export const productFaceShapes = mysqlTable(
  "product_face_shapes",
  {
    productId: int("product_id", { unsigned: true })
      .notNull()
      .references(() => products.id),
    faceShapeId: int("face_shape_id", { unsigned: true })
      .notNull()
      .references(() => faceShapes.id),
  },
  (table) => [primaryKey({ columns: [table.productId, table.faceShapeId] })],
);

export const productLensFeatures = mysqlTable(
  "product_lens_features",
  {
    productId: int("product_id", { unsigned: true })
      .notNull()
      .references(() => products.id),
    lensFeatureId: int("lens_feature_id", { unsigned: true })
      .notNull()
      .references(() => lensFeatures.id),
  },
  (table) => [primaryKey({ columns: [table.productId, table.lensFeatureId] })],
);

export const productOccasions = mysqlTable(
  "product_occasions",
  {
    productId: int("product_id", { unsigned: true })
      .notNull()
      .references(() => products.id),
    occasionId: int("occasion_id", { unsigned: true })
      .notNull()
      .references(() => occasions.id),
  },
  (table) => [primaryKey({ columns: [table.productId, table.occasionId] })],
);

export const mediaAssets = mysqlTable(
  "media_assets",
  {
    id: id(),
    ownerType: mysqlEnum("owner_type", mediaOwnerTypeValues).notNull(),
    ownerId: int("owner_id", { unsigned: true }).notNull(),
    url: varchar("url", { length: 512 }).notNull(),
    storageKey: varchar("storage_key", { length: 1024 }),
    fileName: varchar("file_name", { length: 255 }),
    mimeType: varchar("mime_type", { length: 120 }),
    sizeBytes: int("size_bytes", { unsigned: true }),
    altText: varchar("alt_text", { length: 255 }),
    sortOrder: int("sort_order", { unsigned: true }).notNull().default(0),
    isPrimary: boolean("is_primary").notNull().default(false),
    uploadedByStaffId: int("uploaded_by_staff_id", {
      unsigned: true,
    }).references(() => staffUsers.id),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    uniqueIndex("media_assets_owner_url_unique").on(
      table.ownerType,
      table.ownerId,
      table.url,
    ),
    index("media_assets_owner_idx").on(table.ownerType, table.ownerId),
    index("media_assets_uploaded_by_staff_idx").on(table.uploadedByStaffId),
  ],
);

export const orders = mysqlTable(
  "orders",
  {
    id: id(),
    orderNumber: varchar("order_number", { length: 40 }).notNull(),
    customerId: int("customer_id", { unsigned: true })
      .notNull()
      .references(() => customers.id),
    managedByStaffId: int("managed_by_staff_id", { unsigned: true }).references(
      () => staffUsers.id,
    ),
    status: mysqlEnum("status", orderStatusValues).notNull().default("pending"),
    fulfillmentMethod: mysqlEnum(
      "fulfillment_method",
      fulfillmentMethodValues,
    ).notNull(),
    contactPhone: varchar("contact_phone", { length: 30 }).notNull(),
    deliveryAddress: text("delivery_address"),
    subtotal: int("subtotal", { unsigned: true }).notNull(),
    totalDiscount: int("total_discount", { unsigned: true })
      .notNull()
      .default(0),
    totalAmount: int("total_amount", { unsigned: true }).notNull(),
    paymentMethod: mysqlEnum("payment_method", paymentMethodValues)
      .notNull()
      .default("cash_on_delivery"),
    paymentStatus: mysqlEnum("payment_status", paymentStatusValues)
      .notNull()
      .default("pending"),
    customerNotes: text("customer_notes"),
    staffNotes: text("staff_notes"),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    uniqueIndex("orders_order_number_unique").on(table.orderNumber),
    index("orders_customer_idx").on(table.customerId),
    index("orders_status_idx").on(table.status),
    index("orders_managed_by_staff_idx").on(table.managedByStaffId),
    index("orders_created_idx").on(table.createdAt),
    index("orders_status_created_idx").on(table.status, table.createdAt),
    index("orders_payment_status_created_idx").on(
      table.paymentStatus,
      table.createdAt,
    ),
    index("orders_fulfillment_created_idx").on(
      table.fulfillmentMethod,
      table.createdAt,
    ),
  ],
);

export const orderItems = mysqlTable(
  "order_items",
  {
    id: id(),
    orderId: int("order_id", { unsigned: true })
      .notNull()
      .references(() => orders.id),
    productId: int("product_id", { unsigned: true }).references(
      () => products.id,
    ),
    productName: varchar("product_name", { length: 255 }).notNull(),
    sku: varchar("sku", { length: 80 }).notNull(),
    quantity: int("quantity", { unsigned: true }).notNull(),
    unitPrice: int("unit_price", { unsigned: true }).notNull(),
    discountAmount: int("discount_amount", { unsigned: true })
      .notNull()
      .default(0),
    lineTotal: int("line_total", { unsigned: true }).notNull(),
    lensChoice: varchar("lens_choice", { length: 30 })
      .notNull()
      .default("none"),
    prescriptionUrl: varchar("prescription_url", { length: 500 }),
    createdAt: createdAt(),
  },
  (table) => [
    uniqueIndex("order_items_order_sku_unique").on(table.orderId, table.sku),
    index("order_items_order_idx").on(table.orderId),
    index("order_items_product_idx").on(table.productId),
  ],
);

export const clientReviews = mysqlTable(
  "client_reviews",
  {
    id: id(),
    clientName: varchar("client_name", { length: 255 }).notNull(),
    reviewText: text("review_text").notNull(),
    postUrl: varchar("post_url", { length: 1024 }),
    starRating: int("star_rating", { unsigned: true }).notNull().default(5),
    isActive: boolean("is_active").notNull().default(true),
    sortOrder: int("sort_order", { unsigned: true }).notNull().default(0),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    index("client_reviews_active_sort_idx").on(
      table.isActive,
      table.sortOrder,
    ),
  ],
);

export const banners = mysqlTable(
  "banners",
  {
    id: id(),
    title: varchar("title", { length: 255 }).notNull(),
    subtitle: varchar("subtitle", { length: 255 }),
    imageUrl: varchar("image_url", { length: 512 }).notNull(),
    linkUrl: varchar("link_url", { length: 512 }),
    linkLabel: varchar("link_label", { length: 120 }),
    isActive: boolean("is_active").notNull().default(true),
    sortOrder: int("sort_order", { unsigned: true }).notNull().default(0),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    index("banners_active_sort_idx").on(table.isActive, table.sortOrder),
  ],
);

export const productReviews = mysqlTable(
  "product_reviews",
  {
    id: id(),
    productId: int("product_id", { unsigned: true })
      .notNull()
      .references(() => products.id),
    customerId: int("customer_id", { unsigned: true })
      .notNull()
      .references(() => customers.id),
    rating: int("rating", { unsigned: true }).notNull(),
    title: varchar("title", { length: 160 }),
    comment: text("comment"),
    isApproved: boolean("is_approved").notNull().default(false),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    uniqueIndex("product_reviews_customer_product_unique").on(
      table.productId,
      table.customerId,
    ),
    index("product_reviews_product_idx").on(table.productId),
    index("product_reviews_customer_idx").on(table.customerId),
  ],
);
