import { bigint, boolean, index, integer, jsonb, pgTable, text, timestamp, uniqueIndex } from "drizzle-orm/pg-core";
import { intId, intPk } from "./columns";
import { users, workspaces } from "./identity";
import { channels } from "./channels";

export const messages = pgTable(
  "messages",
  {
    id: intPk(),
    workspaceId: intId("workspace_id")
      .notNull()
      .references(() => workspaces.id),
    channelId: intId("channel_id")
      .notNull()
      .references(() => channels.id),
    seq: bigint("seq", { mode: "number" }).notNull(),
    clientMsgId: text("client_msg_id").notNull(),
    authorId: intId("author_id")
      .notNull()
      .references(() => users.id),
    type: text("type").notNull().default("text"),
    text: text("text").notNull(),
    blocks: jsonb("blocks").notNull(),
    revision: integer("revision").notNull().default(0),
    // NULL => top-level channel message; set => a thread reply (ADR-001 / §4.7).
    parentId: intId("parent_id"),
    isBroadcast: boolean("is_broadcast").notNull().default(false),
    threadReplyCount: integer("thread_reply_count").notNull().default(0),
    threadLastReplyAt: timestamp("thread_last_reply_at", { withTimezone: true }),
    editedAt: timestamp("edited_at", { withTimezone: true }),
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("messages_channel_seq_idx").on(t.channelId, t.seq),
    uniqueIndex("messages_channel_client_msg_idx").on(t.channelId, t.clientMsgId),
  ],
);

/**
 * Private objects uploaded to the S3-compatible file store. The object is
 * created before the message, then atomically claimed by sendMessage through
 * messageId. Keeping metadata in Postgres makes ACL-safe browsing/search and
 * deletion possible without listing the bucket or parsing message JSON.
 */
export const attachments = pgTable(
  "attachments",
  {
    id: intPk(),
    workspaceId: intId("workspace_id")
      .notNull()
      .references(() => workspaces.id),
    channelId: intId("channel_id")
      .notNull()
      .references(() => channels.id),
    messageId: intId("message_id").references(() => messages.id),
    uploadedBy: intId("uploaded_by")
      .notNull()
      .references(() => users.id),
    objectKey: text("object_key").notNull(),
    originalName: text("original_name").notNull(),
    mimeType: text("mime_type").notNull(),
    size: bigint("size", { mode: "number" }).notNull(),
    category: text("category").notNull(),
    status: text("status").notNull().default("pending"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    completedAt: timestamp("completed_at", { withTimezone: true }),
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
  },
  (t) => [
    uniqueIndex("attachments_object_key_idx").on(t.objectKey),
    index("attachments_workspace_created_idx").on(t.workspaceId, t.createdAt),
    index("attachments_channel_created_idx").on(t.channelId, t.createdAt),
    index("attachments_message_idx").on(t.messageId),
  ],
);

// New in v3.0 (ADR-014) — fixes the I5/I5b channel-vs-thread unread defect.
export const threadSubscriptions = pgTable(
  "thread_subscriptions",
  {
    userId: intId("user_id")
      .notNull()
      .references(() => users.id),
    rootMessageId: intId("root_message_id")
      .notNull()
      .references(() => messages.id),
    channelId: intId("channel_id")
      .notNull()
      .references(() => channels.id),
    workspaceId: intId("workspace_id")
      .notNull()
      .references(() => workspaces.id),
    lastReadReplySeq: bigint("last_read_reply_seq", { mode: "number" }).notNull().default(0),
    reason: text("reason").notNull(),
    isMuted: boolean("is_muted").notNull().default(false),
    subscribedAt: timestamp("subscribed_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex("thread_subscriptions_pk").on(t.userId, t.rootMessageId)],
);

export const reactions = pgTable(
  "reactions",
  {
    messageId: intId("message_id")
      .notNull()
      .references(() => messages.id),
    userId: intId("user_id")
      .notNull()
      .references(() => users.id),
    emoji: text("emoji").notNull(),
  },
  (t) => [uniqueIndex("reactions_pk").on(t.messageId, t.userId, t.emoji)],
);

export const messageMentions = pgTable("message_mentions", {
  messageId: intId("message_id")
    .notNull()
    .references(() => messages.id),
  targetType: text("target_type").notNull(),
  targetId: intId("target_id"),
});

export const pins = pgTable(
  "pins",
  {
    channelId: intId("channel_id")
      .notNull()
      .references(() => channels.id),
    messageId: intId("message_id")
      .notNull()
      .references(() => messages.id),
    pinnedBy: intId("pinned_by")
      .notNull()
      .references(() => users.id),
    pinnedAt: timestamp("pinned_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex("pins_pk").on(t.channelId, t.messageId)],
);

/** A user's durable "save for later" pointer to a message. */
export const savedItems = pgTable(
  "saved_items",
  {
    id: intPk(),
    workspaceId: intId("workspace_id")
      .notNull()
      .references(() => workspaces.id),
    userId: intId("user_id")
      .notNull()
      .references(() => users.id),
    messageId: intId("message_id")
      .notNull()
      .references(() => messages.id, { onDelete: "cascade" }),
    savedAt: timestamp("saved_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("saved_items_user_message_idx").on(t.userId, t.messageId),
    index("saved_items_user_workspace_saved_idx").on(t.userId, t.workspaceId, t.savedAt),
  ],
);

/**
 * Text composer drafts. `contextKey` makes channel and thread drafts share a
 * single conflict target (`channel` or `thread:<root id>`) even though
 * PostgreSQL treats NULLs as distinct in ordinary unique indexes.
 */
export const messageDrafts = pgTable(
  "message_drafts",
  {
    id: intPk(),
    workspaceId: intId("workspace_id")
      .notNull()
      .references(() => workspaces.id),
    userId: intId("user_id")
      .notNull()
      .references(() => users.id),
    channelId: intId("channel_id")
      .notNull()
      .references(() => channels.id, { onDelete: "cascade" }),
    threadRootMessageId: intId("thread_root_message_id").references(() => messages.id, { onDelete: "cascade" }),
    contextKey: text("context_key").notNull(),
    text: text("text").notNull(),
    blocks: jsonb("blocks"),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("message_drafts_user_context_idx").on(t.userId, t.workspaceId, t.channelId, t.contextKey),
    index("message_drafts_user_workspace_updated_idx").on(t.userId, t.workspaceId, t.updatedAt),
  ],
);

/** Durable read receipts for the Activity/Mentions inbox. */
export const activityReads = pgTable(
  "activity_reads",
  {
    id: intPk(),
    workspaceId: intId("workspace_id")
      .notNull()
      .references(() => workspaces.id, { onDelete: "cascade" }),
    userId: intId("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    messageId: intId("message_id")
      .notNull()
      .references(() => messages.id, { onDelete: "cascade" }),
    readAt: timestamp("read_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("activity_reads_user_message_idx").on(t.userId, t.messageId),
    index("activity_reads_user_workspace_read_idx").on(t.userId, t.workspaceId, t.readAt),
  ],
);