import {
  pgTable,
  text,
  timestamp,
  integer,
  boolean,
  pgEnum,
  index,
  uniqueIndex,
  type AnyPgColumn,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { users } from "./users";
import { recipes, recipeBatchDishes, visibilityEnum } from "./recipes";

export const notificationTypeEnum = pgEnum("notification_type", [
  "follow",
  "comment",
  "reply",
  "reaction",
  "rating",
  "mention",
]);

export const ratings = pgTable("ratings", {
  id: text("id").primaryKey(),
  recipeId: text("recipe_id").notNull().references(() => recipes.id, { onDelete: "cascade" }),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  score: integer("score").notNull(),
  reviewText: text("review_text"),
  photoKey: text("photo_key"),
  photoSizeMb: integer("photo_size_mb").notNull().default(0),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, (t) => [
  index("ratings_user_idx").on(t.userId),
  index("ratings_recipe_idx").on(t.recipeId),
]);

export const favorites = pgTable("favorites", {
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  recipeId: text("recipe_id").notNull().references(() => recipes.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").notNull().defaultNow(),
}, (t) => [
  index("favorites_user_idx").on(t.userId),
]);

export const comments = pgTable("comments", {
  id: text("id").primaryKey(),
  recipeId: text("recipe_id").notNull().references(() => recipes.id, { onDelete: "cascade" }),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  parentId: text("parent_id").references((): AnyPgColumn => comments.id, { onDelete: "cascade" }),
  content: text("content").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, (t) => [
  index("comments_recipe_idx").on(t.recipeId),
]);

export const collections = pgTable("collections", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  name: text("name").notNull(),
  description: text("description"),
  notes: text("notes"),
  tags: text("tags").array().notNull().default([]),
  visibility: visibilityEnum("visibility").notNull().default("private"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, (t) => [
  index("collections_user_idx").on(t.userId),
]);

export const collectionRecipes = pgTable("collection_recipes", {
  collectionId: text("collection_id").notNull().references(() => collections.id, { onDelete: "cascade" }),
  recipeId: text("recipe_id").notNull().references(() => recipes.id, { onDelete: "cascade" }),
  position: integer("position").notNull().default(0),
  addedAt: timestamp("added_at").notNull().defaultNow(),
}, (t) => [
  index("collection_recipes_collection_position_idx").on(t.collectionId, t.position),
]);

export const collectionFavorites = pgTable("collection_favorites", {
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  collectionId: text("collection_id").notNull().references(() => collections.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").notNull().defaultNow(),
}, (t) => [
  index("collection_favorites_collection_idx").on(t.collectionId),
  uniqueIndex("collection_favorites_user_collection_idx").on(t.userId, t.collectionId),
]);

export const cookingHistory = pgTable("cooking_history", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  recipeId: text("recipe_id").notNull().references(() => recipes.id, { onDelete: "cascade" }),
  batchDishId: text("batch_dish_id").references(() => recipeBatchDishes.id, { onDelete: "cascade" }),
  cookedAt: timestamp("cooked_at").notNull().defaultNow(),
  servings: integer("servings"),
  notes: text("notes"),
  expiryReminderSentAt: timestamp("expiry_reminder_sent_at"),
}, (t) => [
  index("cooking_history_user_idx").on(t.userId),
  index("cooking_history_batch_dish_idx").on(t.batchDishId),
]);

export const notifications = pgTable("notifications", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }), // recipient
  type: notificationTypeEnum("type").notNull(),
  actorId: text("actor_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  recipeId: text("recipe_id").references(() => recipes.id, { onDelete: "cascade" }),
  commentId: text("comment_id").references(() => comments.id, { onDelete: "cascade" }),
  read: boolean("read").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
}, (t) => [
  index("notifications_user_idx").on(t.userId, t.createdAt),
  index("notifications_user_unread_idx").on(t.userId, t.read),
]);

export const cookingHistoryRelations = relations(cookingHistory, ({ one }) => ({
  user: one(users, { fields: [cookingHistory.userId], references: [users.id] }),
  recipe: one(recipes, { fields: [cookingHistory.recipeId], references: [recipes.id] }),
  batchDish: one(recipeBatchDishes, { fields: [cookingHistory.batchDishId], references: [recipeBatchDishes.id] }),
}));

export const notificationsRelations = relations(notifications, ({ one }) => ({
  actor: one(users, { fields: [notifications.actorId], references: [users.id] }),
  recipe: one(recipes, { fields: [notifications.recipeId], references: [recipes.id] }),
}));

export const ratingsRelations = relations(ratings, ({ one }) => ({
  recipe: one(recipes, { fields: [ratings.recipeId], references: [recipes.id] }),
  user: one(users, { fields: [ratings.userId], references: [users.id] }),
}));

export const collectionsRelations = relations(collections, ({ one, many }) => ({
  user: one(users, { fields: [collections.userId], references: [users.id] }),
  recipes: many(collectionRecipes),
}));

export const collectionRecipesRelations = relations(collectionRecipes, ({ one }) => ({
  collection: one(collections, { fields: [collectionRecipes.collectionId], references: [collections.id] }),
  recipe: one(recipes, { fields: [collectionRecipes.recipeId], references: [recipes.id] }),
}));

export const collectionFavoritesRelations = relations(collectionFavorites, ({ one }) => ({
  collection: one(collections, { fields: [collectionFavorites.collectionId], references: [collections.id] }),
  user: one(users, { fields: [collectionFavorites.userId], references: [users.id] }),
}));

export const collectionMemberRoleEnum = pgEnum("collection_member_role", ["viewer", "editor"]);

export const collectionMembers = pgTable("collection_members", {
  id: text("id").primaryKey(),
  collectionId: text("collection_id").notNull().references(() => collections.id, { onDelete: "cascade" }),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  role: collectionMemberRoleEnum("role").notNull().default("viewer"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
}, (t) => [
  index("collection_members_user_idx").on(t.userId),
]);

export const commentReactionTypeEnum = pgEnum("comment_reaction_type", ["like", "love", "laugh", "wow", "sad", "fire"]);

export const commentReactions = pgTable("comment_reactions", {
  id: text("id").primaryKey(),
  commentId: text("comment_id").notNull().references(() => comments.id, { onDelete: "cascade" }),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  type: commentReactionTypeEnum("type").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
}, (t) => [
  index("comment_reactions_comment_idx").on(t.commentId),
]);

export const collectionMembersRelations = relations(collectionMembers, ({ one }) => ({
  collection: one(collections, { fields: [collectionMembers.collectionId], references: [collections.id] }),
  user: one(users, { fields: [collectionMembers.userId], references: [users.id] }),
}));

export const commentReactionsRelations = relations(commentReactions, ({ one }) => ({
  comment: one(comments, { fields: [commentReactions.commentId], references: [comments.id] }),
  user: one(users, { fields: [commentReactions.userId], references: [users.id] }),
}));
