import { pgTable, text, integer, bigint, bigserial, boolean, jsonb, timestamp, index, uniqueIndex, real, varchar } from 'drizzle-orm/pg-core';
import { ciId, createdAt, updatedAt } from './_common.js';

/** Per-namespace counters for stable public IDs (never exposed, never reused). */
export const idSequences = pgTable('id_sequences', {
  namespace: varchar('namespace', { length: 16 }).primaryKey(),
  next: bigint('next', { mode: 'number' }).notNull().default(1),
});

/** Source registry + license registry (CLAUDE.md §9, §95, §142). Seeded from connector manifests. */
export const sources = pgTable(
  'sources',
  {
    id: ciId().primaryKey(), // CI-SOURCE-…
    slug: text('slug').notNull(), // connector id, e.g. "clinicaltrials"
    name: text('name').notNull(),
    organization: text('organization'),
    category: text('category').notNull(), // terminology | genomics | epidemiology | trials | literature | variants | drugs | regulatory
    description: text('description'),
    homepage: text('homepage'),
    docsUrl: text('docs_url'),
    termsUrl: text('terms_url'),
    accessType: text('access_type').notNull(), // api|bulk|rss|ftp|graphql|rest|scrape|manual
    accessAuth: text('access_auth').notNull(), // none|api_key|oauth|account|controlled
    license: text('license'),
    licenseStatus: text('license_status').notNull().default('review'), // review | approved | restricted | blocked
    commercialUse: text('commercial_use').notNull().default('unknown'), // allowed | restricted | prohibited | unknown
    redistribution: text('redistribution').notNull().default('unknown'), // allowed | attribution | restricted | prohibited | unknown
    attribution: text('attribution'),
    licenseReviewedAt: timestamp('license_reviewed_at', { withTimezone: true }),
    approvedForProduction: boolean('approved_for_production').notNull().default(false),
    updateFrequency: text('update_frequency'),
    supportsIncremental: boolean('supports_incremental').notNull().default(false),
    entities: text('entities').array().notNull().default([]),
    metrics: text('metrics').array().notNull().default([]),
    rateLimit: text('rate_limit'),
    status: text('status').notNull().default('planned'), // planned | active | paused | degraded | awaiting_credentials | review | retired
    tier: integer('tier').notNull().default(0),
    manifest: jsonb('manifest').$type<Record<string, unknown>>().notNull().default({}),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('sources_slug_uq').on(t.slug)],
);

/** Every connector execution (CLAUDE.md §24, §176). */
export const ingestRuns = pgTable(
  'ingest_runs',
  {
    id: text('id').primaryKey(), // ING-CLINICALTRIALS-20260908-000019
    connectorId: text('connector_id').notNull(),
    sourceId: ciId('source_id').notNull(),
    mode: text('mode').notNull().default('incremental'), // full | incremental | backfill | dry_run | probe
    status: text('status').notNull().default('running'), // running | succeeded | failed | partial | aborted
    startedAt: timestamp('started_at', { withTimezone: true }).notNull().defaultNow(),
    finishedAt: timestamp('finished_at', { withTimezone: true }),
    durationMs: integer('duration_ms'),
    recordsFetched: integer('records_fetched').notNull().default(0),
    recordsCreated: integer('records_created').notNull().default(0),
    recordsUpdated: integer('records_updated').notNull().default(0),
    recordsUnchanged: integer('records_unchanged').notNull().default(0),
    recordsRejected: integer('records_rejected').notNull().default(0),
    httpRequests: integer('http_requests').notNull().default(0),
    httpFailures: integer('http_failures').notNull().default(0),
    rateLimitEvents: integer('rate_limit_events').notNull().default(0),
    validationFailures: integer('validation_failures').notNull().default(0),
    schemaDrift: jsonb('schema_drift').$type<unknown[]>().notNull().default([]),
    cursorBefore: jsonb('cursor_before'),
    cursorAfter: jsonb('cursor_after'),
    error: text('error'),
    log: jsonb('log').$type<Array<{ t: string; level: string; msg: string }>>().notNull().default([]),
    datasetVersion: text('dataset_version'),
    anomaly: text('anomaly'), // set when a destructive update was refused (CLAUDE.md §171)
  },
  (t) => [index('ingest_runs_connector_idx').on(t.connectorId, t.startedAt)],
);

export const connectorCursors = pgTable('connector_cursors', {
  connectorId: text('connector_id').primaryKey(),
  cursor: jsonb('cursor').$type<Record<string, unknown>>().notNull().default({}),
  lastSuccessAt: timestamp('last_success_at', { withTimezone: true }),
  lastAttemptAt: timestamp('last_attempt_at', { withTimezone: true }),
  paused: boolean('paused').notNull().default(false),
  health: text('health').notNull().default('unknown'), // healthy | degraded | failing | review | awaiting_credentials | unknown
  healthDetail: text('health_detail'),
  updatedAt: updatedAt(),
});

/** Schema-drift detection: observed field names/types per connector entity (CLAUDE.md §25). */
export const connectorFieldStats = pgTable(
  'connector_field_stats',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    connectorId: text('connector_id').notNull(),
    entity: text('entity').notNull(),
    field: text('field').notNull(),
    types: text('types').array().notNull().default([]),
    seenCount: integer('seen_count').notNull().default(0),
    nullCount: integer('null_count').notNull().default(0),
    firstSeenRun: text('first_seen_run'),
    lastSeenRun: text('last_seen_run'),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('connector_field_stats_uq').on(t.connectorId, t.entity, t.field)],
);

/** Source-native records (idempotency key = source + entity + source record id; CLAUDE.md §91-92). */
export const sourceRecords = pgTable(
  'source_records',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    sourceId: ciId('source_id').notNull(),
    entityKind: text('entity_kind').notNull(),
    sourceRecordId: text('source_record_id').notNull(),
    payloadHash: text('payload_hash').notNull(),
    rawPath: text('raw_path'), // data lake path /raw/{source}/{date}/{entity}/{id}.json.gz
    status: text('status').notNull().default('active'), // active | deprecated | retracted | withdrawn | source_missing
    firstSeenRun: text('first_seen_run'),
    lastSeenRun: text('last_seen_run'),
    retrievedAt: timestamp('retrieved_at', { withTimezone: true }).notNull().defaultNow(),
    sourceUpdatedAt: timestamp('source_updated_at', { withTimezone: true }),
    canonicalType: text('canonical_type'),
    canonicalId: text('canonical_id'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('source_records_uq').on(t.sourceId, t.entityKind, t.sourceRecordId),
    index('source_records_canonical_idx').on(t.canonicalType, t.canonicalId),
  ],
);

/** Provenance record (CLAUDE.md §2). Referenced by observations, edges, descriptions, rankings. */
export const provenance = pgTable(
  'provenance',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    publicId: ciId('public_id'), // CI-PROV-… (minted lazily when exposed)
    sourceId: ciId('source_id').notNull(),
    sourceRecordId: text('source_record_id'),
    sourceUrl: text('source_url'),
    dataset: text('dataset'),
    datasetVersion: text('dataset_version'),
    publicationId: ciId('publication_id'),
    pmid: text('pmid'),
    doi: text('doi'),
    retrievedAt: timestamp('retrieved_at', { withTimezone: true }).notNull(),
    publishedAt: text('published_at'),
    updatedAtSource: text('updated_at_source'),
    geography: text('geography'),
    population: text('population'),
    cohortSize: integer('cohort_size'),
    methodology: text('methodology'),
    evidenceType: text('evidence_type').notNull(),
    accessLevel: text('access_level').notNull().default('open'),
    confidence: real('confidence'),
    license: text('license'),
    ingestRunId: text('ingest_run_id'),
    createdAt: createdAt(),
  },
  (t) => [index('provenance_source_idx').on(t.sourceId, t.sourceRecordId), index('provenance_pmid_idx').on(t.pmid)],
);

/** Unknown disease/drug labels are never discarded (CLAUDE.md §222). */
export const unresolvedLabels = pgTable(
  'unresolved_labels',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    sourceId: ciId('source_id').notNull(),
    entityKind: text('entity_kind').notNull(), // cancer | drug | gene | biomarker
    sourceText: text('source_text').notNull(),
    normalized: text('normalized').notNull(),
    context: jsonb('context').$type<Record<string, unknown>>().notNull().default({}),
    count: integer('count').notNull().default(1),
    status: text('status').notNull().default('open'), // open | mapped | rejected | ignored
    suggestedId: text('suggested_id'),
    suggestedMatchType: text('suggested_match_type'),
    suggestedScore: real('suggested_score'),
    resolvedId: text('resolved_id'),
    resolvedBy: text('resolved_by'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('unresolved_labels_uq').on(t.sourceId, t.entityKind, t.normalized), index('unresolved_labels_count_idx').on(t.status, t.count)],
);

/** Entity change history (CLAUDE.md §96). */
export const changeEvents = pgTable(
  'change_events',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    entityType: text('entity_type').notNull(),
    entityId: text('entity_id').notNull(),
    kind: text('kind').notNull(), // created | updated | trial_added | approval_added | ranking_changed | merged | deprecated …
    summary: text('summary').notNull(),
    before: jsonb('before'),
    after: jsonb('after'),
    ingestRunId: text('ingest_run_id'),
    createdAt: createdAt(),
  },
  (t) => [index('change_events_entity_idx').on(t.entityType, t.entityId, t.createdAt)],
);

/** Curator/admin audit log (CLAUDE.md §348). */
export const auditLog = pgTable('audit_log', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  actor: text('actor').notNull(),
  action: text('action').notNull(),
  entityType: text('entity_type'),
  entityId: text('entity_id'),
  before: jsonb('before'),
  after: jsonb('after'),
  reason: text('reason'),
  createdAt: createdAt(),
});

/** Entity merge queue + audit (CLAUDE.md §70). Reversible. */
export const entityMerges = pgTable('entity_merges', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  entityType: text('entity_type').notNull(),
  keepId: text('keep_id').notNull(),
  mergeId: text('merge_id').notNull(),
  evidence: jsonb('evidence').$type<Record<string, unknown>>().notNull().default({}),
  status: text('status').notNull().default('proposed'), // proposed | merged | kept_separate | reverted
  decidedBy: text('decided_by'),
  decidedAt: timestamp('decided_at', { withTimezone: true }),
  createdAt: createdAt(),
});