import { index, pgEnum, pgTable, text, timestamp } from "drizzle-orm/pg-core";
import { intId, intPk } from "./columns";
import { users, workspaces } from "./identity";
import { channels } from "./channels";
import { messages } from "./messages";

export const taskStatusEnum = pgEnum("task_status", ["todo", "in_progress", "done"]);

export const tasks = pgTable(
  "tasks",
  {
    id: intPk(),
    workspaceId: intId("workspace_id")
      .notNull()
      .references(() => workspaces.id),
    title: text("title").notNull(),
    description: text("description"),
    status: taskStatusEnum("status").notNull().default("todo"),
    assigneeUserId: intId("assignee_user_id").references(() => users.id),
    dueAt: timestamp("due_at", { withTimezone: true }),
    createdBy: intId("created_by")
      .notNull()
      .references(() => users.id),
    // Optional context, not a scoping mechanism (tasks are workspace-wide —
    // see libs/messaging/src/tasks.ts). Both null for a standalone task; both
    // set together by createTaskFromMessage. The sourceMessage-belongs-to-
    // channel relationship is enforced in the service layer, not the DB, same
    // as every other cross-resource invariant in this schema (see tenant.ts).
    channelId: intId("channel_id").references(() => channels.id),
    sourceMessageId: intId("source_message_id").references(() => messages.id),
    completedAt: timestamp("completed_at", { withTimezone: true }),
    /** Soft delete — rows stay so the Deleted tab can list them. */
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
    deletedBy: intId("deleted_by").references(() => users.id),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    // Hot path for GET /workspaces/:id/tasks (filtered by status tab).
    index("tasks_workspace_status_idx").on(t.workspaceId, t.status),
    // "Assigned to me" filter.
    index("tasks_assignee_idx").on(t.assigneeUserId),
    // Future due-soon reminder scan (not built yet — see tasks.ts).
    index("tasks_due_at_idx").on(t.dueAt),
    index("tasks_channel_idx").on(t.channelId),
    index("tasks_workspace_deleted_idx").on(t.workspaceId, t.deletedAt),
  ],
);