import {
  pgTable,
  uuid,
  text,
  boolean,
  integer,
  bigint,
  jsonb,
  timestamp,
  index,
  uniqueIndex,
  customType,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

import { users } from './core.js';

const byteaColumn = customType<{ data: Buffer; driverData: Buffer }>({
  dataType() {
    return 'bytea';
  },
});

export const addressZipCacheMisses = pgTable(
  'address_zip_cache_misses',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    cityCode: text('city_code').notNull(),
    streetCode: text('street_code'),
    houseNumber: text('house_number'),
    resolution: text('resolution').notNull(),
    resolvedZip: text('resolved_zip'),
    userId: uuid('user_id').references(() => users.id),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [
    index('zip_misses_resolution_idx').on(t.resolution, t.createdAt),
    index('zip_misses_city_idx').on(t.cityCode, t.createdAt),
  ],
);

export const systemHealthFindings = pgTable(
  'system_health_findings',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    kind: text('kind').notNull(),
    entityType: text('entity_type').notNull(),
    entityId: text('entity_id').notNull(),
    detail: text('detail'),
    firstSeenAt: timestamp('first_seen_at', { withTimezone: true }).notNull().defaultNow(),
    lastSeenAt: timestamp('last_seen_at', { withTimezone: true }).notNull().defaultNow(),
    resolvedAt: timestamp('resolved_at', { withTimezone: true }),
  },
  (t) => [
    uniqueIndex('system_health_unresolved_per_entity')
      .on(t.kind, t.entityType, t.entityId)
      .where(sql`resolved_at IS NULL`),
  ],
);

export const opsAlertState = pgTable('ops_alert_state', {
  alertKey: text('alert_key').primaryKey(),
  lastFiredAt: timestamp('last_fired_at', { withTimezone: true }).notNull(),
  lastSeverity: text('last_severity').notNull(),
});

export const alertChannels = pgTable('alert_channels', {
  id: text('id').primaryKey(),
  kind: text('kind').notNull(),
  label: text('label').notNull(),
  enabled: boolean('enabled').notNull().default(false),
  minSeverity: text('min_severity').notNull().default('page'),
  config: jsonb('config').notNull().default({}),
  secretEnc: byteaColumn('secret_enc'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});
export type AlertChannelRecord = typeof alertChannels.$inferSelect;
export type NewAlertChannelRecord = typeof alertChannels.$inferInsert;

export const monitorChecks = pgTable('monitor_checks', {
  key: text('key').primaryKey(),
  enabled: boolean('enabled').notNull().default(true),
  config: jsonb('config').notNull().default({}),
  severityFloor: text('severity_floor'),
  intervalHours: integer('interval_hours'),
  includeInDigest: boolean('include_in_digest').notNull().default(true),
  lastRunAt: timestamp('last_run_at', { withTimezone: true }),
  lastSeverity: text('last_severity'),
  lastDetail: text('last_detail'),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});
export type MonitorCheckRecord = typeof monitorChecks.$inferSelect;
export type NewMonitorCheckRecord = typeof monitorChecks.$inferInsert;

export const cronRuns = pgTable('cron_runs', {
  cronKey: text('cron_key').primaryKey(),
  lastSuccessAt: timestamp('last_success_at', { withTimezone: true }),
  lastErrorAt: timestamp('last_error_at', { withTimezone: true }),
  lastError: text('last_error'),
  expectedIntervalMinutes: integer('expected_interval_minutes').notNull().default(60),
});
export type CronRunRecord = typeof cronRuns.$inferSelect;

export const dlqEvents = pgTable('dlq_events', {
  queueKey: text('queue_key').primaryKey(),
  deadCount: bigint('dead_count', { mode: 'number' }).notNull().default(0),
  lastDeadAt: timestamp('last_dead_at', { withTimezone: true }),
  lastReason: text('last_reason'),
  acknowledgedCount: bigint('acknowledged_count', { mode: 'number' }).notNull().default(0),
});
export type DlqEventRecord = typeof dlqEvents.$inferSelect;