import { pgTable, foreignKey, serial, varchar, integer, doublePrecision, timestamp, unique, text, numeric, boolean, jsonb } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"



export const productCategoryMappings = pgTable("product_category_mappings", {
	id: serial().primaryKey().notNull(),
	feedName: varchar("feed_name", { length: 255 }),
	feedCategoryValue: varchar("feed_category_value", { length: 255 }),
	canonicalCategoryId: integer("canonical_category_id"),
	confidenceScore: doublePrecision("confidence_score"),
	lastReviewedBy: varchar("last_reviewed_by", { length: 255 }),
	lastReviewedAt: timestamp("last_reviewed_at", { mode: 'string' }),
}, (table) => [
	foreignKey({
			columns: [table.canonicalCategoryId],
			foreignColumns: [categories.id],
			name: "product_category_mappings_canonical_category_id_fkey"
		}),
]);

export const categories = pgTable("categories", {
	id: serial().primaryKey().notNull(),
	name: varchar({ length: 255 }).notNull(),
	parentId: integer("parent_id"),
	slug: varchar({ length: 255 }).notNull(),
}, (table) => [
	foreignKey({
			columns: [table.parentId],
			foreignColumns: [table.id],
			name: "categories_parent_id_fkey"
		}),
	unique("categories_slug_key").on(table.slug),
]);

export const products = pgTable("products", {
	id: serial().primaryKey().notNull(),
	name: varchar({ length: 255 }).notNull(),
	brand: varchar({ length: 255 }),
	description: text(),
	upc: varchar({ length: 32 }),
	mpn: varchar({ length: 64 }),
	canonicalCategoryId: integer("canonical_category_id"),
	createdAt: timestamp("created_at", { mode: 'string' }).defaultNow(),
	updatedAt: timestamp("updated_at", { mode: 'string' }).defaultNow(),
}, (table) => [
	foreignKey({
			columns: [table.canonicalCategoryId],
			foreignColumns: [categories.id],
			name: "products_canonical_category_id_fkey"
		}),
]);

export const offers = pgTable("offers", {
	id: serial().primaryKey().notNull(),
	productId: integer("product_id"),
	feedName: varchar("feed_name", { length: 255 }).notNull(),
	feedSku: varchar("feed_sku", { length: 255 }),
	price: numeric({ precision: 10, scale:  2 }),
	url: text(),
	inStock: boolean("in_stock"),
	vendor: varchar({ length: 255 }),
	lastSeenAt: timestamp("last_seen_at", { mode: 'string' }).defaultNow(),
	rawData: jsonb("raw_data"),
}, (table) => [
	foreignKey({
			columns: [table.productId],
			foreignColumns: [products.id],
			name: "offers_product_id_fkey"
		}).onDelete("cascade"),
	unique("offers_product_id_feed_name_feed_sku_key").on(table.productId, table.feedName, table.feedSku),
	unique("offers_feed_unique").on(table.feedName, table.feedSku),
]);

export const offerPriceHistory = pgTable("offer_price_history", {
	id: serial().primaryKey().notNull(),
	offerId: integer("offer_id"),
	price: numeric({ precision: 10, scale:  2 }).notNull(),
	seenAt: timestamp("seen_at", { mode: 'string' }).defaultNow(),
}, (table) => [
	foreignKey({
			columns: [table.offerId],
			foreignColumns: [offers.id],
			name: "offer_price_history_offer_id_fkey"
		}).onDelete("cascade"),
]);

export const feeds = pgTable("feeds", {
	id: serial().primaryKey().notNull(),
	name: varchar({ length: 255 }).notNull(),
	url: text(),
	lastImportedAt: timestamp("last_imported_at", { mode: 'string' }),
}, (table) => [
	unique("feeds_name_key").on(table.name),
]);

export const productAttributes = pgTable("product_attributes", {
	id: serial().primaryKey().notNull(),
	productId: integer("product_id"),
	name: varchar({ length: 255 }).notNull(),
	value: varchar({ length: 255 }).notNull(),
}, (table) => [
	foreignKey({
			columns: [table.productId],
			foreignColumns: [products.id],
			name: "product_attributes_product_id_fkey"
		}).onDelete("cascade"),
]);