import {
  customType,
  index,
  integer,
  jsonb,
  pgTable,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from "drizzle-orm/pg-core";
import { adminUsers } from "./auth.js";

export const bytea = customType<{ data: Buffer; driverData: Buffer }>({
  dataType() {
    return "bytea";
  },
});

export const researchProjects = pgTable("research_projects", {
  id: uuid("id").primaryKey().defaultRandom(),
  title: text("title").notNull(),
  discipline: text("discipline").notNull(),
  description: text("description").notNull().default(""),
  createdBy: uuid("created_by").references(() => adminUsers.id),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
  archivedAt: timestamp("archived_at", { withTimezone: true }),
});

/** Immutable, versioned research instruction (CLAUDE.md) uploads. Original bytes retained. */
export const projectInstructionVersions = pgTable(
  "project_instruction_versions",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    projectId: uuid("project_id")
      .notNull()
      .references(() => researchProjects.id, { onDelete: "cascade" }),
    version: integer("version").notNull(),
    filename: text("filename").notNull(),
    sha256: text("sha256").notNull(),
    byteSize: integer("byte_size").notNull(),
    contentText: text("content_text").notNull(),
    originalBytes: bytea("original_bytes").notNull(),
    frontmatter: jsonb("frontmatter").$type<Record<string, unknown> | null>(),
    validation: jsonb("validation").$type<Record<string, unknown>>().notNull().default({}),
    note: text("note"),
    createdBy: uuid("created_by").references(() => adminUsers.id),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("piv_project_version_uq").on(t.projectId, t.version),
    uniqueIndex("piv_project_sha_uq").on(t.projectId, t.sha256),
    index("piv_project_idx").on(t.projectId),
  ],
);

export const researchTemplates = pgTable("research_templates", {
  id: uuid("id").primaryKey().defaultRandom(),
  slug: text("slug").notNull().unique(),
  name: text("name").notNull(),
  mode: text("mode").notNull(),
  description: text("description").notNull().default(""),
  content: text("content").notNull(),
  version: integer("version").notNull().default(1),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});