import { integer, jsonb, pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core';

export const contentModelImportJournal = pgTable('content_model_import_journal', {
  sessionId: uuid('session_id').primaryKey(),
  planHash: text('plan_hash').notNull(),
  manifestHash: text('manifest_hash').notNull(),
  destinationVersion: text('destination_version').notNull(),
  contentPlanHash: text('content_plan_hash').notNull(),
  fieldsPlanId: text('fields_plan_id').notNull(),
  contentManifestHash: text('content_manifest_hash').notNull(),
  fieldsManifestHash: text('fields_manifest_hash').notNull(),
  authorizationPolicyVersion: text('authorization_policy_version').notNull(),
  codeRegistryVersion: text('code_registry_version').notNull(),
  codeRegistryHash: text('code_registry_hash').notNull(),
  entityTypeMapVersion: text('entity_type_map_version').notNull(),
  entityTypeMapHash: text('entity_type_map_hash').notNull(),
  state: text('state').notNull(),
  version: integer('version').notNull().default(0),
  contentImportId: uuid('content_import_id'),
  fieldsImportId: uuid('fields_import_id'),
  payload: jsonb('payload').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
  updatedAt: timestamp('updated_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
});

export const contentModelOperationReceipts = pgTable('content_model_operation_receipts', {
  operationId: text('operation_id').primaryKey(),
  sessionId: uuid('session_id').notNull(),
  kind: text('kind').notNull(),
  requestHash: text('request_hash').notNull(),
  principalId: text('principal_id').notNull(),
  state: text('state').notNull(),
  result: jsonb('result').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
  updatedAt: timestamp('updated_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
});

export const contentModelDefinitionBackups = pgTable('content_model_definition_backups', {
  id: uuid('id').primaryKey(),
  kind: text('kind').notNull(),
  scopeHash: text('scope_hash').notNull(),
  corpusVersion: text('corpus_version').notNull(),
  corpusHash: text('corpus_hash').notNull(),
  itemCounts: jsonb('item_counts').notNull(),
  byteCount: integer('byte_count').notNull(),
  payloadHash: text('payload_hash').notNull(),
  payload: jsonb('payload').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
});

export const contentModelAdminOutbox = pgTable('content_model_admin_outbox', {
  id: uuid('id').primaryKey(),
  kind: text('kind').notNull(),
  payload: jsonb('payload').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
});

export const contentModelHostSchema = {
  contentModelImportJournal,
  contentModelOperationReceipts,
  contentModelDefinitionBackups,
  contentModelAdminOutbox,
};

export type ContentModelHostSchema = typeof contentModelHostSchema;

export function contentModelHostMigrationSql(dialect: 'postgres' | 'd1'): readonly string[] {
  const timestampType = dialect === 'postgres' ? 'timestamptz(3)' : 'text';
  const jsonType = dialect === 'postgres' ? 'jsonb' : 'text';
  const now = dialect === 'postgres' ? 'NOW()' : 'CURRENT_TIMESTAMP';
  const idType = dialect === 'postgres' ? 'uuid' : 'text';
  return Object.freeze([
    `CREATE TABLE IF NOT EXISTS content_model_import_journal (
      session_id ${idType} PRIMARY KEY,
      plan_hash text NOT NULL,
      manifest_hash text NOT NULL,
      destination_version text NOT NULL,
      content_plan_hash text NOT NULL,
      fields_plan_id text NOT NULL,
      content_manifest_hash text NOT NULL,
      fields_manifest_hash text NOT NULL,
      authorization_policy_version text NOT NULL,
      code_registry_version text NOT NULL,
      code_registry_hash text NOT NULL,
      entity_type_map_version text NOT NULL,
      entity_type_map_hash text NOT NULL,
      state text NOT NULL,
      version integer NOT NULL DEFAULT 0,
      content_import_id ${idType},
      fields_import_id ${idType},
      payload ${jsonType} NOT NULL,
      created_at ${timestampType} NOT NULL DEFAULT ${now},
      updated_at ${timestampType} NOT NULL DEFAULT ${now}
    )`,
    `CREATE TABLE IF NOT EXISTS content_model_operation_receipts (
      operation_id text PRIMARY KEY,
      session_id ${idType} NOT NULL,
      kind text NOT NULL,
      request_hash text NOT NULL,
      principal_id text NOT NULL,
      state text NOT NULL,
      result ${jsonType} NOT NULL,
      created_at ${timestampType} NOT NULL DEFAULT ${now},
      updated_at ${timestampType} NOT NULL DEFAULT ${now}
    )`,
    `CREATE INDEX IF NOT EXISTS content_model_operation_receipts_session_idx ON content_model_operation_receipts (session_id, created_at)`,
    `CREATE TABLE IF NOT EXISTS content_model_definition_backups (
      id ${idType} PRIMARY KEY,
      kind text NOT NULL,
      scope_hash text NOT NULL,
      corpus_version text NOT NULL,
      corpus_hash text NOT NULL,
      item_counts ${jsonType} NOT NULL,
      byte_count integer NOT NULL,
      payload_hash text NOT NULL,
      payload ${jsonType} NOT NULL,
      created_at ${timestampType} NOT NULL DEFAULT ${now}
    )`,
    `CREATE INDEX IF NOT EXISTS content_model_definition_backups_created_idx ON content_model_definition_backups (created_at)`,
    `CREATE TABLE IF NOT EXISTS content_model_admin_outbox (
      id ${idType} PRIMARY KEY,
      kind text NOT NULL,
      payload ${jsonType} NOT NULL,
      created_at ${timestampType} NOT NULL DEFAULT ${now}
    )`,
    `CREATE INDEX IF NOT EXISTS content_model_admin_outbox_created_idx ON content_model_admin_outbox (created_at)`,
  ]);
}