import {
  mysqlTable,
  mysqlEnum,
  serial,
  varchar,
  text,
  timestamp,
  int,
  json,
  boolean,
  decimal,
  bigint,
} from "drizzle-orm/mysql-core";

// Users table (extends auth user)
export const users = mysqlTable("users", {
  id: serial("id").primaryKey(),
  unionId: varchar("unionId", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 255 }),
  email: varchar("email", { length: 320 }),
  avatar: text("avatar"),
  role: mysqlEnum("role", ["user", "admin"]).default("user").notNull(),
  phone: varchar("phone", { length: 20 }),
  walletBalance: decimal("wallet_balance", { precision: 10, scale: 2 }).default("0.00"),
  referralCode: varchar("referral_code", { length: 20 }).unique(),
  referredBy: bigint("referred_by", { mode: "number", unsigned: true }),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().notNull().$onUpdate(() => new Date()),
  lastSignInAt: timestamp("lastSignInAt").defaultNow().notNull(),
});

export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;

// Categories
export const categories = mysqlTable("categories", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull(),
  slug: varchar("slug", { length: 100 }).notNull().unique(),
  icon: varchar("icon", { length: 50 }),
  description: text("description"),
  sortOrder: int("sort_order").default(0),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});

export type Category = typeof categories.$inferSelect;

// Products
export const products = mysqlTable("products", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  slug: varchar("slug", { length: 255 }).notNull().unique(),
  description: text("description"),
  shortDesc: varchar("short_desc", { length: 500 }),
  categoryId: bigint("category_id", { mode: "number", unsigned: true }),
  productType: mysqlEnum("product_type", [
    "shared_account",
    "private_account",
    "lifetime",
    "license_key",
    "subscription",
  ]).notNull(),
  originalPrice: decimal("original_price", { precision: 10, scale: 2 }).notNull(),
  salePrice: decimal("sale_price", { precision: 10, scale: 2 }).notNull(),
  stockStatus: mysqlEnum("stock_status", ["in_stock", "low_stock", "out_of_stock"]).default("in_stock"),
  stockQuantity: int("stock_quantity").default(0),
  deliveryTime: varchar("delivery_time", { length: 50 }).default("1-2 hours"),
  validityPeriod: varchar("validity_period", { length: 50 }).default("1 year"),
  features: json("features"),
  images: json("images"),
  tags: json("tags"),
  isActive: boolean("is_active").default(true),
  isFeatured: boolean("is_featured").default(false),
  isBundle: boolean("is_bundle").default(false),
  seoTitle: varchar("seo_title", { length: 255 }),
  seoDesc: varchar("seo_desc", { length: 500 }),
  sortOrder: int("sort_order").default(0),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().$onUpdate(() => new Date()),
});

export type Product = typeof products.$inferSelect;

// Orders
export const orders = mysqlTable("orders", {
  id: serial("id").primaryKey(),
  userId: bigint("user_id", { mode: "number", unsigned: true }),
  orderNumber: varchar("order_number", { length: 20 }).notNull().unique(),
  status: mysqlEnum("status", [
    "pending",
    "payment_pending",
    "payment_verified",
    "processing",
    "delivered",
    "cancelled",
    "refunded",
  ]).default("pending"),
  totalAmount: decimal("total_amount", { precision: 10, scale: 2 }).notNull(),
  discountAmount: decimal("discount_amount", { precision: 10, scale: 2 }).default("0.00"),
  finalAmount: decimal("final_amount", { precision: 10, scale: 2 }).notNull(),
  currency: varchar("currency", { length: 3 }).default("USD"),
  couponCode: varchar("coupon_code", { length: 50 }),
  customerName: varchar("customer_name", { length: 255 }),
  customerEmail: varchar("customer_email", { length: 255 }),
  customerPhone: varchar("customer_phone", { length: 20 }),
  notes: text("notes"),
  checkoutMethod: mysqlEnum("checkout_method", ["whatsapp", "telegram", "website"]).default("whatsapp"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().$onUpdate(() => new Date()),
});

export type Order = typeof orders.$inferSelect;

// Order Items
export const orderItems = mysqlTable("order_items", {
  id: serial("id").primaryKey(),
  orderId: bigint("order_id", { mode: "number", unsigned: true }).notNull(),
  productId: bigint("product_id", { mode: "number", unsigned: true }).notNull(),
  quantity: int("quantity").default(1),
  unitPrice: decimal("unit_price", { precision: 10, scale: 2 }).notNull(),
  totalPrice: decimal("total_price", { precision: 10, scale: 2 }).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
});

export type OrderItem = typeof orderItems.$inferSelect;

// Cart Items
export const cartItems = mysqlTable("cart_items", {
  id: serial("id").primaryKey(),
  userId: bigint("user_id", { mode: "number", unsigned: true }),
  sessionId: varchar("session_id", { length: 100 }),
  productId: bigint("product_id", { mode: "number", unsigned: true }).notNull(),
  quantity: int("quantity").default(1),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().$onUpdate(() => new Date()),
});

export type CartItem = typeof cartItems.$inferSelect;

// Coupons
export const coupons = mysqlTable("coupons", {
  id: serial("id").primaryKey(),
  code: varchar("code", { length: 50 }).notNull().unique(),
  type: mysqlEnum("type", ["percentage", "fixed"]).notNull(),
  value: decimal("value", { precision: 10, scale: 2 }).notNull(),
  minOrder: decimal("min_order", { precision: 10, scale: 2 }).default("0.00"),
  maxDiscount: decimal("max_discount", { precision: 10, scale: 2 }),
  usageLimit: int("usage_limit"),
  usageCount: int("usage_count").default(0),
  validFrom: timestamp("valid_from"),
  validUntil: timestamp("valid_until"),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});

export type Coupon = typeof coupons.$inferSelect;

// Payments
export const payments = mysqlTable("payments", {
  id: serial("id").primaryKey(),
  orderId: bigint("order_id", { mode: "number", unsigned: true }).notNull(),
  paymentMethod: mysqlEnum("payment_method", [
    "upi",
    "paypal",
    "jazzcash",
    "binance_pay",
    "bitcoin",
    "ethereum",
    "usdt",
    "litecoin",
    "solana",
    "other",
  ]).notNull(),
  amount: decimal("amount", { precision: 10, scale: 2 }).notNull(),
  currency: varchar("currency", { length: 10 }).notNull(),
  status: mysqlEnum("status", ["pending", "completed", "failed", "refunded"]).default("pending"),
  transactionId: varchar("transaction_id", { length: 255 }),
  screenshotUrl: varchar("screenshot_url", { length: 500 }),
  verifiedBy: bigint("verified_by", { mode: "number", unsigned: true }),
  verifiedAt: timestamp("verified_at"),
  createdAt: timestamp("created_at").defaultNow(),
});

export type Payment = typeof payments.$inferSelect;

// Reviews
export const reviews = mysqlTable("reviews", {
  id: serial("id").primaryKey(),
  userId: bigint("user_id", { mode: "number", unsigned: true }).notNull(),
  productId: bigint("product_id", { mode: "number", unsigned: true }).notNull(),
  orderId: bigint("order_id", { mode: "number", unsigned: true }),
  rating: int("rating").notNull(),
  comment: text("comment"),
  isVerified: boolean("is_verified").default(false),
  createdAt: timestamp("created_at").defaultNow(),
});

export type Review = typeof reviews.$inferSelect;

// FAQs
export const faqs = mysqlTable("faqs", {
  id: serial("id").primaryKey(),
  question: varchar("question", { length: 500 }).notNull(),
  answer: text("answer").notNull(),
  category: varchar("category", { length: 100 }),
  sortOrder: int("sort_order").default(0),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});

export type FAQ = typeof faqs.$inferSelect;

// Blogs
export const blogs = mysqlTable("blogs", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  slug: varchar("slug", { length: 255 }).notNull().unique(),
  excerpt: varchar("excerpt", { length: 500 }),
  content: text("content"),
  coverImage: varchar("cover_image", { length: 500 }),
  authorId: bigint("author_id", { mode: "number", unsigned: true }),
  tags: json("tags"),
  isPublished: boolean("is_published").default(false),
  publishedAt: timestamp("published_at"),
  seoTitle: varchar("seo_title", { length: 255 }),
  seoDesc: varchar("seo_desc", { length: 500 }),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().$onUpdate(() => new Date()),
});

export type Blog = typeof blogs.$inferSelect;

// Referrals
export const referrals = mysqlTable("referrals", {
  id: serial("id").primaryKey(),
  referrerId: bigint("referrer_id", { mode: "number", unsigned: true }).notNull(),
  referredId: bigint("referred_id", { mode: "number", unsigned: true }).notNull(),
  status: mysqlEnum("status", ["pending", "completed"]).default("pending"),
  rewardAmount: decimal("reward_amount", { precision: 10, scale: 2 }).default("0.00"),
  createdAt: timestamp("created_at").defaultNow(),
});

export type Referral = typeof referrals.$inferSelect;

// Notifications
export const notifications = mysqlTable("notifications", {
  id: serial("id").primaryKey(),
  userId: bigint("user_id", { mode: "number", unsigned: true }).notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  message: text("message"),
  type: mysqlEnum("type", ["order", "payment", "delivery", "system", "promo"]).default("system"),
  isRead: boolean("is_read").default(false),
  createdAt: timestamp("created_at").defaultNow(),
});

export type Notification = typeof notifications.$inferSelect;