/**
 * Unified attachments schema — unified-attachments (P065).
 * Postgres / Neon via Hyperdrive.
 *
 * Table:
 *  - attachments: polymorphic attachment record for any entity type
 *
 * DB conventions:
 *  - UUID PK .defaultRandom()
 *  - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 *  - Enums: text() + check(... IN (...)) — NEVER pgEnum
 *  - Soft-delete via deleted_at TIMESTAMPTZ (indexes filtered with WHERE deleted_at IS NULL)
 *  - Polymorphic entity reference: (entity_type, entity_id) — entity_id is a UUID string
 *
 * entity_type values (per spec 41):
 *   'task_message' | 'expense' | 'ticket_message' | 'kb_article' | 'vendor'
 *
 * Partial/composite indexes (returned in manifest.raw_ddl):
 *   - idx_attachments_entity:   (tenant_id, entity_type, entity_id) WHERE deleted_at IS NULL
 *   - idx_attachments_uploader: (tenant_id, uploader_id)             WHERE deleted_at IS NULL
 *
 * R2 bucket binding: STORAGE (same as rest of the app — leaf notes said ATTACHMENTS_BUCKET
 * but codebase uses `c.env.STORAGE`; using STORAGE for consistency)
 *
 * Migration note:
 *   Existing task_message_attachments and ticket_message_attachments rows should be
 *   migrated into this table with entity_type='task_message' / 'ticket_message'.
 *   The integrator applies that migration in 0006_wave7.sql.
 *   expenses table loses r2_key/file_name/file_type/file_size_bytes columns
 *   (integrator applies DROP COLUMNs in 0006_wave7.sql).
 */
import {
  pgTable,
  uuid,
  text,
  integer,
  timestamp,
  index,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

// ── attachments ───────────────────────────────────────────────────────────────

export const attachments = pgTable(
  'attachments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    uploaderId: uuid('uploader_id')
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),

    /**
     * Polymorphic entity type (spec 41).
     * 'task_message' | 'expense' | 'ticket_message' | 'kb_article' | 'vendor'
     */
    entityType: text('entity_type').notNull(),

    /**
     * UUID of the referenced entity (matches entity_type).
     * Stored as text to avoid cross-schema circular FK constraints;
     * application layer must validate the entity exists.
     */
    entityId: text('entity_id').notNull(),

    /** Original filename provided by the user */
    filename: text('filename').notNull(),

    /** MIME type of the file */
    mimeType: text('mime_type').notNull(),

    /** File size in bytes */
    sizeBytes: integer('size_bytes').notNull(),

    /**
     * R2 object key (never returned to clients directly).
     * Format: {tenantId}/{entity_type}/{entityId}/{uuid}-{safeFilename}
     */
    r2Key: text('r2_key').notNull(),

    /**
     * Cached signed URL (short-lived; refreshed on access).
     * May be stale — routes should re-sign on demand.
     */
    signedUrl: text('signed_url'),

    /** Soft-delete timestamp — null means live */
    deletedAt: timestamp('deleted_at', { withTimezone: true }),

    /** If non-null, identifies the user who deleted this attachment */
    deletedBy: uuid('deleted_by').references(() => users.id, { onDelete: 'set null' }),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    entityTypeCheck: check(
      'attachments_entity_type_check',
      sql`${t.entityType} IN ('task_message','expense','ticket_message','kb_article','vendor')`,
    ),
    // Standard btree index on tenant only (partial composite lives in raw_ddl)
    tenantCreatedIdx: index('idx_attachments_tenant_created').on(t.tenantId, t.createdAt),
    // Note: partial indexes → raw_ddl
    // CREATE INDEX idx_attachments_entity ON attachments (tenant_id, entity_type, entity_id) WHERE deleted_at IS NULL
    // CREATE INDEX idx_attachments_uploader ON attachments (tenant_id, uploader_id) WHERE deleted_at IS NULL
  }),
)

export type AttachmentRow = typeof attachments.$inferSelect
export type NewAttachment = typeof attachments.$inferInsert