import { pgTable, uuid, varchar, text, timestamp, boolean, integer, decimal, json } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// profiles synced with Supabase auth ids
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  fullName: varchar('full_name', { length: 255 }),
  role: varchar('role', { length: 50 }).notNull().default('user'), // 'user' or 'admin'
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const categories = pgTable('categories', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: varchar('name', { length: 100 }).notNull().unique(),
  description: text('description'),
  color: varchar('color', { length: 7 }), // Hex color code
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// shared shelf inventory
export const pantryItems = pgTable('pantry_items', {
  id: uuid('id').primaryKey().defaultRandom(),
  categoryId: uuid('category_id').references(() => categories.id),
  name: varchar('name', { length: 255 }).notNull(),
  description: text('description'),
  quantity: integer('quantity').notNull().default(0),
  unit: varchar('unit', { length: 50 }), // kg, lbs, pieces, loaves, etc.
  expiryDate: timestamp('expiry_date'),
  purchaseDate: timestamp('purchase_date').defaultNow().notNull(),
  price: decimal('price', { precision: 10, scale: 2 }),
  location: varchar('location', { length: 100 }), // pantry, fridge, freezer, etc.
  isLowStock: boolean('is_low_stock').default(false),
  lowStockThreshold: integer('low_stock_threshold').default(5),
  barcode: varchar('barcode', { length: 50 }),
  imageUrl: text('image_url'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// orders row (we overload a few columns — see routes)
export const orders = pgTable('orders', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id),
  name: varchar('name', { length: 255 }).notNull(),
  quantity: integer('quantity').notNull().default(1),
  unit: varchar('unit', { length: 50 }), // order status: pending | ready | picked-up
  isPurchased: boolean('is_purchased').default(false),
  priority: varchar('priority', { length: 50 }).default('medium'), // pickup window label
  notes: text('notes'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const orderItems = pgTable('order_items', {
  id: uuid('id').primaryKey().defaultRandom(),
  orderId: uuid('order_id').notNull().references(() => orders.id),
  pantryItemId: uuid('pantry_item_id').references(() => pantryItems.id),
  name: varchar('name', { length: 255 }).notNull(),
  quantity: integer('quantity').notNull().default(1),
  unit: varchar('unit', { length: 50 }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const stockAnalysis = pgTable('stock_analysis', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id),
  analysisType: varchar('analysis_type', { length: 100 }).notNull(),
  data: json('data').notNull(),
  insights: text('insights'),
  recommendations: text('recommendations'),
  confidence: decimal('confidence', { precision: 3, scale: 2 }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const pantrySettings = pgTable('pantry_settings', {
  id: uuid('id').primaryKey().defaultRandom(),
  pantryName: varchar('pantry_name', { length: 255 }).notNull().default('Community Food Pantry'),
  contactPhone: varchar('contact_phone', { length: 50 }),
  address: text('address'),
  lowStockThreshold: integer('low_stock_threshold').notNull().default(5),
  expiryWarningDays: integer('expiry_warning_days').notNull().default(7),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  orders: many(orders),
  stockAnalysis: many(stockAnalysis),
}));

export const categoriesRelations = relations(categories, ({ many }) => ({
  pantryItems: many(pantryItems),
}));

export const pantryItemsRelations = relations(pantryItems, ({ one, many }) => ({
  category: one(categories, {
    fields: [pantryItems.categoryId],
    references: [categories.id],
  }),
  orderItems: many(orderItems),
}));

export const ordersRelations = relations(orders, ({ one, many }) => ({
  user: one(users, {
    fields: [orders.userId],
    references: [users.id],
  }),
  orderItems: many(orderItems),
}));

export const orderItemsRelations = relations(orderItems, ({ one }) => ({
  order: one(orders, {
    fields: [orderItems.orderId],
    references: [orders.id],
  }),
  pantryItem: one(pantryItems, {
    fields: [orderItems.pantryItemId],
    references: [pantryItems.id],
  }),
}));

export const stockAnalysisRelations = relations(stockAnalysis, ({ one }) => ({
  user: one(users, {
    fields: [stockAnalysis.userId],
    references: [users.id],
  }),
}));
