import { sql } from 'drizzle-orm'
import { boolean, check, index, integer, jsonb, numeric, pgTable, text, timestamp, uniqueIndex, uuid } from 'drizzle-orm/pg-core'

export const fieldValues = pgTable(
  'field_values',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    entityType: text('entity_type').notNull(),
    entityId: text('entity_id').notNull(),
    groupId: text('group_id').notNull(),
    fieldKey: text('field_key').notNull(),
    ordinal: integer('ordinal').notNull().default(0),
    valueText: text('value_text'),
    valueNum: numeric('value_num'),
    valueBool: boolean('value_bool'),
    valueDate: timestamp('value_date', { withTimezone: true, precision: 3 }),
    refType: text('ref_type'),
    refId: text('ref_id'),
    refMeta: jsonb('ref_meta'),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('field_values_entity_group_field_ord_uq').on(t.entityType, t.entityId, t.groupId, t.fieldKey, t.ordinal),
    index('field_values_entity_idx').on(t.entityType, t.entityId),
    index('field_values_facet_text_idx').on(t.entityType, t.fieldKey, t.valueText),
    index('field_values_facet_num_idx').on(t.entityType, t.fieldKey, t.valueNum),
    index('field_values_facet_date_idx').on(t.entityType, t.fieldKey, t.valueDate),
    index('field_values_ref_idx').on(t.entityType, t.refType, t.refId),
    check(
      'field_values_one_lane',
      sql`
      (CASE WHEN ${t.valueText} IS NOT NULL THEN 1 ELSE 0 END
     + CASE WHEN ${t.valueNum} IS NOT NULL THEN 1 ELSE 0 END
     + CASE WHEN ${t.valueBool} IS NOT NULL THEN 1 ELSE 0 END
     + CASE WHEN ${t.valueDate} IS NOT NULL THEN 1 ELSE 0 END
     + CASE WHEN ${t.refId} IS NOT NULL THEN 1 ELSE 0 END) = 1`,
    ),
  ],
)

export const fieldGroups = pgTable(
  'field_groups',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    entityType: text('entity_type').notNull(),
    subType: text('sub_type'),
    key: text('key').notNull(),
    label: text('label').notNull(),
    fields: jsonb('fields').notNull(),
    position: integer('position').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex('field_groups_entity_key_uq').on(t.entityType, t.key)],
)

export const fieldsSchema = { fieldValues, fieldGroups }
export type FieldsSchema = typeof fieldsSchema