/** Idempotent additive DDL for fields EAV tables (spec §4). */
export const fieldsMigrationSql = (): string =>
  `
CREATE TABLE IF NOT EXISTS field_values (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_type text NOT NULL,
  entity_id   text NOT NULL,
  group_id    text NOT NULL,
  field_key   text NOT NULL,
  ordinal     integer NOT NULL DEFAULT 0,
  value_text  text,
  value_num   numeric,
  value_bool  boolean,
  value_date  timestamptz(3),
  ref_type    text,
  ref_id      text,
  ref_meta    jsonb,
  created_at  timestamptz(3) NOT NULL DEFAULT NOW(),
  updated_at  timestamptz(3) NOT NULL DEFAULT NOW(),
  CONSTRAINT field_values_one_lane CHECK (
    (CASE WHEN value_text IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN value_num  IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN value_bool IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN value_date IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN ref_id     IS NOT NULL THEN 1 ELSE 0 END) = 1
  )
);
CREATE UNIQUE INDEX IF NOT EXISTS field_values_entity_group_field_ord_uq
  ON field_values (entity_type, entity_id, group_id, field_key, ordinal);
CREATE INDEX IF NOT EXISTS field_values_entity_idx ON field_values (entity_type, entity_id);
CREATE INDEX IF NOT EXISTS field_values_facet_text_idx ON field_values (entity_type, field_key, value_text);
CREATE INDEX IF NOT EXISTS field_values_facet_num_idx ON field_values (entity_type, field_key, value_num);
CREATE INDEX IF NOT EXISTS field_values_facet_date_idx ON field_values (entity_type, field_key, value_date);
CREATE INDEX IF NOT EXISTS field_values_ref_idx ON field_values (entity_type, ref_type, ref_id);
CREATE TABLE IF NOT EXISTS field_groups (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_type text NOT NULL,
  sub_type    text,
  key         text NOT NULL,
  label       text NOT NULL,
  fields      jsonb NOT NULL,
  position    integer NOT NULL DEFAULT 0,
  created_at  timestamptz(3) NOT NULL DEFAULT NOW(),
  updated_at  timestamptz(3) NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS field_groups_entity_key_uq ON field_groups (entity_type, key);
`.trim()

export {
  FIELDS_RECURSIVE_EAV_MIGRATION_ID,
  canonicalFieldsMigrationHash,
  snapshotFlatFieldsCorpus,
  escapeFieldPathSegment,
  fieldPath,
  planFieldsRecursiveEavMigration,
  fieldsRecursiveEavForwardMigrationSql,
  fieldsRecursiveEavReverseMigrationSql,
  fieldDefinitionRefsForPlan,
  parseLegacyMultipleValueJson,
  type FieldsMigrationCorpusCheckpoint,
  type MigratedFieldValueNode,
  type FieldsRecursiveEavMigrationPlan,
} from './migrations/recursive-eav.js'