import { bigint, boolean, foreignKey, index, integer, pgTable, text } from 'drizzle-orm/pg-core'

export const MENU_KEY_RE = /^[a-z0-9]+(?:-[a-z0-9]+)*$/

export const menus = pgTable('menus', {
  id: text('id').primaryKey(),
  key: text('key').notNull().unique(),
  label: text('label').notNull(),
  createdAtMs: bigint('created_at_ms', { mode: 'number' }).notNull(),
  updatedAtMs: bigint('updated_at_ms', { mode: 'number' }).notNull(),
})

export const menuItems = pgTable(
  'menu_items',
  {
    id: text('id').primaryKey(),
    menuId: text('menu_id')
      .notNull()
      .references(() => menus.id, { onDelete: 'cascade' }),
    parentId: text('parent_id'),
    position: integer('position').notNull(),
    depth: integer('depth').notNull(),
    label: text('label').notNull(),
    targetKind: text('target_kind').notNull(),
    url: text('url'),
    targetEntityType: text('target_entity_type'),
    targetEntityId: text('target_entity_id'),
    openInNew: boolean('open_in_new').notNull().default(false),
    createdAtMs: bigint('created_at_ms', { mode: 'number' }).notNull(),
    updatedAtMs: bigint('updated_at_ms', { mode: 'number' }).notNull(),
  },
  (t) => [
    foreignKey({
      columns: [t.parentId],
      foreignColumns: [t.id],
      name: 'menu_items_parent_id_menu_items_id_fk',
    }).onDelete('cascade'),
    index('menu_items_menu_parent_position_idx').on(t.menuId, t.parentId, t.position),
  ],
)

export const menusSchema = { menus, menuItems }
export type MenusSchema = typeof menusSchema

/** Idempotent additive DDL for menus tables. */
export const menusMigrationSql = (): string =>
  `
CREATE TABLE IF NOT EXISTS menus (
  id            text PRIMARY KEY,
  key           text NOT NULL UNIQUE,
  label         text NOT NULL,
  created_at_ms bigint NOT NULL,
  updated_at_ms bigint NOT NULL
);
CREATE TABLE IF NOT EXISTS menu_items (
  id                  text PRIMARY KEY,
  menu_id             text NOT NULL REFERENCES menus(id) ON DELETE CASCADE,
  parent_id           text REFERENCES menu_items(id) ON DELETE CASCADE,
  position            integer NOT NULL,
  depth               integer NOT NULL,
  label               text NOT NULL,
  target_kind         text NOT NULL,
  url                 text,
  target_entity_type  text,
  target_entity_id    text,
  open_in_new         boolean NOT NULL DEFAULT false,
  created_at_ms       bigint NOT NULL,
  updated_at_ms       bigint NOT NULL
);
CREATE INDEX IF NOT EXISTS menu_items_menu_parent_position_idx
  ON menu_items (menu_id, parent_id, position);
`.trim()