import { pgTable, text, boolean, integer, jsonb, uuid, index, uniqueIndex } from 'drizzle-orm/pg-core';
import { id, tenantId, createdAt, updatedAt, ts } from './_common';
import { connectorTypeEnum, messageDirectionEnum, messageStatusEnum } from './enums';
import { storers } from './master-data';

export const integrationConnectors = pgTable(
  'integration_connectors',
  {
    id: id(),
    tenantId: tenantId(),
    storerId: uuid('storer_id').references(() => storers.id),
    type: connectorTypeEnum('type').notNull(),
    name: text('name').notNull(),
    config: jsonb('config').notNull().default({}).$type<Record<string, unknown>>(),
    active: boolean('active').notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index('integration_connectors_type_idx').on(t.tenantId, t.type, t.active)],
);

/** Every inbound/outbound integration message with its processing state. */
export const integrationMessages = pgTable(
  'integration_messages',
  {
    id: id(),
    tenantId: tenantId(),
    storerId: uuid('storer_id'),
    connectorId: uuid('connector_id'),
    direction: messageDirectionEnum('direction').notNull(),
    messageType: text('message_type').notNull(),
    externalId: text('external_id'),
    idempotencyKey: text('idempotency_key'),
    payload: jsonb('payload').notNull().$type<unknown>(),
    rawPayload: text('raw_payload'),
    status: messageStatusEnum('status').notNull().default('RECEIVED'),
    attempts: integer('attempts').notNull().default(0),
    lastError: text('last_error'),
    resultRefType: text('result_ref_type'),
    resultRefId: uuid('result_ref_id'),
    processedAt: ts('processed_at'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('integration_messages_idem_uq').on(t.tenantId, t.idempotencyKey),
    index('integration_messages_status_idx').on(t.tenantId, t.status, t.createdAt),
    index('integration_messages_type_ext_idx').on(t.tenantId, t.messageType, t.externalId),
  ],
);

export const webhookSubscriptions = pgTable(
  'webhook_subscriptions',
  {
    id: id(),
    tenantId: tenantId(),
    storerId: uuid('storer_id'),
    name: text('name').notNull(),
    url: text('url').notNull(),
    secretEncrypted: text('secret_encrypted').notNull(),
    events: text('events').array().notNull().default([]),
    active: boolean('active').notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index('webhook_subscriptions_active_idx').on(t.tenantId, t.active)],
);

export const webhookDeliveries = pgTable(
  'webhook_deliveries',
  {
    id: id(),
    tenantId: tenantId(),
    subscriptionId: uuid('subscription_id').notNull().references(() => webhookSubscriptions.id, { onDelete: 'cascade' }),
    eventType: text('event_type').notNull(),
    eventId: uuid('event_id').notNull(),
    payload: jsonb('payload').notNull().$type<unknown>(),
    status: text('status').notNull().default('PENDING'),
    attempts: integer('attempts').notNull().default(0),
    responseCode: integer('response_code'),
    responseBody: text('response_body'),
    nextAttemptAt: ts('next_attempt_at'),
    deliveredAt: ts('delivered_at'),
    createdAt: createdAt(),
  },
  (t) => [index('webhook_deliveries_status_next_idx').on(t.tenantId, t.status, t.nextAttemptAt)],
);

export const importJobs = pgTable(
  'import_jobs',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id'),
    storerId: uuid('storer_id'),
    type: text('type').notNull(),
    fileName: text('file_name').notNull(),
    status: text('status').notNull().default('PENDING'),
    dryRun: boolean('dry_run').notNull().default(false),
    totalRows: integer('total_rows').notNull().default(0),
    successRows: integer('success_rows').notNull().default(0),
    errorRows: integer('error_rows').notNull().default(0),
    errors: jsonb('errors').notNull().default([]).$type<{ row: number; field?: string; message: string }[]>(),
    resultRefs: jsonb('result_refs').notNull().default([]).$type<unknown[]>(),
    createdById: uuid('created_by_id').notNull(),
    createdAt: createdAt(),
    completedAt: ts('completed_at'),
  },
  (t) => [index('import_jobs_status_idx').on(t.tenantId, t.status)],
);