import { pgTable, uuid, text, timestamp, index, primaryKey, foreignKey} from 'drizzle-orm/pg-core';
import { generations } from '../generations/schema';

/**
 * A named set of images — an afternoon's work, an icon suite, a client.
 *
 * `name` is unique, and that is load-bearing rather than tidy: the generation API accepts a
 * collection by name and creates it when it does not exist, so without the constraint a second
 * request with the same name would quietly start a second collection and split the batch in two.
 */
export const collections = pgTable('atelier_collections', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull().unique(),
  description: text('description'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

/**
 * Membership, as a join table rather than a column on `generations`.
 *
 * An image belongs to as many collections as it earns: the icon that came out of Tuesday's session is
 * in "tuesday" and also in "shipped", and removing it from one must not touch the other. A
 * collection_id column would force a choice at generation time and make re-filing a destructive edit.
 *
 * Both sides cascade. Deleting a collection removes its memberships and NOT its images — a collection
 * is a way of looking at generations, not a container that owns them. Deleting a generation removes
 * its memberships, since a row pointing at nothing is only a way to crash a join later.
 */
export const collectionItems = pgTable('atelier_collection_items',
  {
    collectionId: uuid('collection_id').notNull(),
    generationId: uuid('generation_id').notNull(),
    addedAt: timestamp('added_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    /*
      Both foreign keys are NAMED, because drizzle generates names over Postgres's 63-character identifier
      limit — 65 and 64. Postgres stores the truncated form, drizzle keeps comparing against its own longer
      one, and every `db:push` drops and re-adds constraints that are already identical. Four statements per
      push, on every host, for ever.

      It read as harmless noise until a deploy plan said "expect no changes" and this made that impossible
      to satisfy on all five hosts, forcing the deploy agent to overrule its own instructions.
    */
    foreignKey({
      name: 'fk_atelier_collection_items_collection',
      columns: [table.collectionId],
      foreignColumns: [collections.id],
    }).onDelete('cascade'),
    foreignKey({
      name: 'fk_atelier_collection_items_generation',
      columns: [table.generationId],
      foreignColumns: [generations.id],
    }).onDelete('cascade'),
    // The composite key is what makes adding an image twice a no-op instead of a duplicate.
    primaryKey({ columns: [table.collectionId, table.generationId] }),
    index('atelier_collection_items_generation_idx').on(table.generationId),
  ],
);