import { desc, sql } from 'drizzle-orm'
import {
  bigserial,
  index,
  integer,
  jsonb,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  type AnyPgColumn,
} from 'drizzle-orm/pg-core'
import type { ContentStatus, ContentVisibility } from './model.js'

// Tenantless public schema — NO tenant_id/scope column (spec §4.4). The private SaaS owns its
// own schema variant; the `scope` store param is a no-op readiness slot here.
export const contentEntries = pgTable(
  'content_entries',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    slug: text('slug').notNull(),
    type: text('type').notNull(),
    title: text('title').notNull(),
    body: text('body').notNull().default(''),
    status: text('status').$type<ContentStatus>().notNull().default('draft'),
    visibility: text('visibility').$type<ContentVisibility>().notNull().default('public'),
    publishedAt: timestamp('published_at', { withTimezone: true, precision: 3 }),
    author: text('author').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('content_entries_type_slug_uq').on(t.type, t.slug),
    index('content_entries_type_status_pub_idx').on(t.type, t.status, desc(t.publishedAt)),
    index('content_entries_type_status_vis_pub_idx').on(t.type, t.status, t.visibility, desc(t.publishedAt)),
  ],
)

export const contentTerms = pgTable(
  'content_terms',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    taxonomy: text('taxonomy').notNull(),
    slug: text('slug').notNull(),
    name: text('name').notNull(),
    parentId: uuid('parent_id').references((): AnyPgColumn => contentTerms.id, { onDelete: 'restrict' }),
    depth: integer('depth').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('content_terms_sibling_slug_uq').on(
      t.taxonomy,
      sql`COALESCE(${t.parentId}, '00000000-0000-0000-0000-000000000000'::uuid)`,
      t.slug,
    ),
    index('content_terms_taxonomy_parent_idx').on(t.taxonomy, t.parentId),
  ],
)

export const contentEntryTerms = pgTable(
  'content_entry_terms',
  {
    entryId: uuid('entry_id')
      .notNull()
      .references(() => contentEntries.id, { onDelete: 'cascade' }),
    termId: uuid('term_id')
      .notNull()
      .references(() => contentTerms.id, { onDelete: 'cascade' }),
  },
  (t) => [primaryKey({ columns: [t.entryId, t.termId] }), index('content_entry_terms_term_idx').on(t.termId)],
)

export const contentRevisions = pgTable(
  'content_revisions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    entryId: uuid('entry_id')
      .notNull()
      .references(() => contentEntries.id, { onDelete: 'cascade' }),
    seq: bigserial('seq', { mode: 'number' }).notNull(),
    title: text('title').notNull(),
    body: text('body').notNull(),
    slug: text('slug').notNull(),
    type: text('type').notNull(),
    termIds: jsonb('term_ids').$type<string[]>().notNull().default(sql`'[]'::jsonb`),
    editor: text('editor').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
  },
  (t) => [index('content_revisions_entry_seq_idx').on(t.entryId, desc(t.seq))],
)

export const contentSchema = { contentEntries, contentTerms, contentEntryTerms, contentRevisions }
export type ContentSchema = typeof contentSchema