import { pgTable, text, integer, index, uniqueIndex, jsonb } from 'drizzle-orm/pg-core';
import { createdAt, ts, ratio } from './_common.js';

/**
 * Immutable raw capture (§108, §142). Rows are never updated except `processed_at`/`process_error`
 * bookkeeping columns. Payload is the parsed source payload (JSON from API, or extracted fields);
 * large HTML snapshots live in RI_DATA_DIR/raw/<connector>/<hash>.html referenced by snapshot_ref.
 */
export const rawRecords = pgTable(
  'raw_records',
  {
    id: text('id').primaryKey(),
    connectorId: text('connector_id').notNull(),
    sourceId: text('source_id').notNull(),
    runId: text('run_id'),
    engine: text('engine').notNull(),
    url: text('url').notNull(),
    externalId: text('external_id'),
    kind: text('kind').notNull(),
    fetchedAt: ts('fetched_at').notNull(),
    contentHash: text('content_hash').notNull(),
    httpStatus: integer('http_status'),
    payload: jsonb('payload').notNull(),
    snapshotRef: text('snapshot_ref'),
    parserVersion: text('parser_version').notNull(),
    connectorVersion: text('connector_version').notNull(),
    processedAt: ts('processed_at'),
    processError: text('process_error'),
    createdAt: createdAt(),
  },
  (t) => [
    uniqueIndex('raw_records_connector_hash_uq').on(t.connectorId, t.contentHash),
    index('raw_records_connector_fetched_idx').on(t.connectorId, t.fetchedAt),
    index('raw_records_unprocessed_idx').on(t.processedAt),
    index('raw_records_external_idx').on(t.connectorId, t.externalId),
  ],
);

/** Staging between normalisation and canonical tables; keeps the match decision auditable (§112). */
export const normalizedRecords = pgTable(
  'normalized_records',
  {
    id: text('id').primaryKey(),
    rawRecordId: text('raw_record_id').notNull(),
    connectorId: text('connector_id').notNull(),
    sourceId: text('source_id').notNull(),
    kind: text('kind').notNull(),
    payload: jsonb('payload').notNull(),
    /** position of this record within the output of normalize(raw) — one raw row may yield several records */
    seq: integer('seq').notNull().default(0),
    assetId: text('asset_id'),
    variantId: text('variant_id'),
    matchMethod: text('match_method'), // identifier | canonical_key | fuzzy | embedding | llm | manual
    matchConfidence: ratio('match_confidence'),
    status: text('status').notNull().default('pending'), // pending | matched | unmatched | rejected | applied
    rejectReason: text('reject_reason'),
    targetId: text('target_id'), // resulting sale/listing/observation id
    createdAt: createdAt(),
    processedAt: ts('processed_at'),
  },
  (t) => [
    index('normalized_records_status_idx').on(t.status, t.createdAt),
    index('normalized_records_status_kind_idx').on(t.status, t.kind, t.createdAt),
    index('normalized_records_asset_idx').on(t.assetId),
    uniqueIndex('normalized_records_raw_uq').on(t.rawRecordId, t.kind, t.seq),
  ],
);

/** Pipeline/event log (§140). Queue itself is pg-boss; this is the durable domain event stream. */
export const events = pgTable(
  'events',
  {
    id: text('id').primaryKey(),
    type: text('type').notNull(), // page_discovered | page_crawled | listing_created | listing_updated | sale_detected | entity_matched | valuation_updated | index_updated
    entityType: text('entity_type'),
    entityId: text('entity_id'),
    payload: jsonb('payload').$type<Record<string, unknown>>().notNull().default({}),
    createdAt: createdAt(),
  },
  (t) => [index('events_type_created_idx').on(t.type, t.createdAt), index('events_entity_idx').on(t.entityType, t.entityId)],
);

/** Audit log — never silently delete or alter data (§116). */
export const auditLog = pgTable(
  'audit_log',
  {
    id: text('id').primaryKey(),
    entityType: text('entity_type').notNull(),
    entityId: text('entity_id').notNull(),
    action: text('action').notNull(), // flagged | excluded | restored | merged | edited | rejected
    reason: text('reason').notNull(),
    actor: text('actor').notNull().default('system'),
    details: jsonb('details').$type<Record<string, unknown>>().notNull().default({}),
    createdAt: createdAt(),
  },
  (t) => [index('audit_entity_idx').on(t.entityType, t.entityId)],
);