import { bigint, bigserial, index, pgTable, text } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'

export const notifications = pgTable(
  'notifications',
  {
    seq: bigserial('seq', { mode: 'number' }).notNull(),
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    title: text('title').notNull(),
    body: text('body'),
    href: text('href'),
    kind: text('kind'),
    createdAtMs: bigint('created_at_ms', { mode: 'number' }).notNull(),
    createdAt: text('created_at').notNull(),
    readAt: text('read_at'),
  },
  (t) => [
    index('notifications_user_keyset_idx').on(t.userId, t.createdAtMs, t.seq),
    index('notifications_user_unread_idx').on(t.userId).where(sql`read_at IS NULL`),
  ],
)

export const notificationsSchema = { notifications }
export type NotificationsSchema = typeof notificationsSchema

/** Pure DDL helper — column names/types match the drizzle table above exactly (parity requirement). */
export function notificationsTableSql(table = 'notifications'): string {
  return `
CREATE TABLE IF NOT EXISTS ${table} (
  seq        bigserial NOT NULL,
  id         text PRIMARY KEY,
  user_id    text NOT NULL,
  title      text NOT NULL,
  body       text NULL,
  href       text NULL,
  kind       text NULL,
  created_at_ms bigint NOT NULL,
  created_at text NOT NULL,
  read_at    text NULL
);
CREATE INDEX IF NOT EXISTS ${table}_user_keyset_idx ON ${table} (user_id, created_at_ms, seq);
CREATE INDEX IF NOT EXISTS ${table}_user_unread_idx ON ${table} (user_id) WHERE read_at IS NULL;
`.trim()
}