import { sql } from "drizzle-orm";
import { boolean, index, pgTable, text, timestamp } from "drizzle-orm/pg-core";

export const posts = pgTable(
  "posts",
  {
    slug: text().primaryKey(),
    title: text().notNull(),
    description: text().notNull(),
    category: text().notNull(),
    primaryKeyword: text("primary_keyword").notNull().unique(),
    secondaryKeywords: text("secondary_keywords")
      .array()
      .notNull()
      .default(sql`'{}'::text[]`),
    length: text().notNull(),
    tags: text()
      .array()
      .notNull()
      .default(sql`'{}'::text[]`),
    author: text().notNull(),
    image: text(),
    body: text().notNull(),
    draft: boolean().notNull().default(true),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    publishedAt: timestamp("published_at", { withTimezone: true }),
    updatedAt: timestamp("updated_at", { withTimezone: true }),
    // md5 of the body as last written by the seed, so a re-seed can tell an
    // edit made in the database from one made in the staged file
    seededHash: text("seeded_hash"),
  },
  (t) => [
    index("posts_recent_idx").on(t.draft, t.publishedAt.desc()),
    index("posts_updated_idx").on(t.draft, t.updatedAt.desc()),
    index("posts_category_idx").on(t.category, t.draft, t.publishedAt.desc()),
  ],
);

export type PostRow = typeof posts.$inferSelect;