/**
 * Portal file sharing schema — portal-file-sharing (wave-11 leaf C).
 *
 * Tables:
 *  - portal_files: files shared between staff and portal customers
 *
 * Enum-like columns use text() + CHECK constraints, never pgEnum.
 * JSONB columns use jsonb().
 * Plain btree indexes via drizzle index().
 * GIN/partial/expression indexes in raw migration SQL only.
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  integer,
  timestamp,
  index,
} from 'drizzle-orm/pg-core'
import { tenants } from './tenants'
import { customers } from './customers'
import { projects } from './projects'
import { users } from './users'

// ── portal_files ─────────────────────────────────────────────────────────────

export const portalFiles = pgTable(
  'portal_files',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    customerId: uuid('customer_id')
      .notNull()
      .references(() => customers.id, { onDelete: 'cascade' }),
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }),
    // Exactly one of uploadedBy / uploadedByPortalUser must be non-null (enforced in code)
    uploadedBy: uuid('uploaded_by').references(() => users.id, { onDelete: 'set null' }),
    uploadedByPortalUser: uuid('uploaded_by_portal_user'), // FK to portal users (not yet in schema)
    r2Key: text('r2_key').notNull().unique(),
    filename: text('filename').notNull(),
    fileSizeBytes: integer('file_size_bytes').notNull(),
    mimeType: text('mime_type').notNull(),
    description: text('description'),
    visibleToPortal: boolean('visible_to_portal').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    expiresAt: timestamp('expires_at', { withTimezone: true }), // null = no expiry
  },
  (t) => ({
    customerIdx: index('idx_portal_files_customer').on(t.tenantId, t.customerId, t.createdAt),
  }),
)

export type PortalFileRow = typeof portalFiles.$inferSelect
export type NewPortalFile = typeof portalFiles.$inferInsert