/**
 * Data import schema — data-import (P051).
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *  - import_jobs:        one row per import batch submitted by a user
 *  - import_job_results: one row per source row processed (success/skipped/error)
 *
 * DB conventions:
 *  - UUID PK .defaultRandom()
 *  - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 *  - Money: NUMERIC
 *  - Enums: text() + check(... IN (...)) — NEVER pgEnum
 *  - Expression/GIN/partial indexes → manifest.raw_ddl only
 *  - FKs to tenants/users via .references()
 *  - JSONB for column_mappings and row_errors
 *
 * import_jobs.type:
 *   'customers' | 'invoices' | 'products' | 'time_entries' | 'bulk_action'
 *
 * import_jobs.status:
 *   'pending' | 'processing' | 'completed' | 'failed'
 *
 * import_job_results.status:
 *   'success' | 'skipped' | 'error'
 *
 * Partial indexes (returned in manifest.raw_ddl):
 *   - import_job_results_errors: (import_job_id) WHERE status IN ('skipped','error')
 *   - import_jobs_tenant_created: (tenant_id, created_at DESC)
 *
 * Queue: QUEUE binding — job dispatched as 'import.process'
 * Tier gate: data-import is locked for freelancer tier (show upgrade prompt)
 */
import {
  pgTable,
  uuid,
  text,
  integer,
  jsonb,
  timestamp,
  index,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

// ── import_jobs ───────────────────────────────────────────────────────────────

export const importJobs = pgTable(
  'import_jobs',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),

    /** 'customers' | 'invoices' | 'products' | 'time_entries' | 'bulk_action' */
    type: text('type').notNull(),

    /** 'pending' | 'processing' | 'completed' | 'failed' */
    status: text('status').notNull().default('pending'),

    /** R2 key of the uploaded CSV/XLSX file */
    r2Key: text('r2_key').notNull(),

    /** Original filename from the user's upload */
    originalFilename: text('original_filename').notNull(),

    /** MIME type of the upload ('text/csv' | 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') */
    mimeType: text('mime_type').notNull(),

    /** File size in bytes */
    fileSizeBytes: integer('file_size_bytes').notNull(),

    /**
     * JSON map of source column header → target field name.
     * Shape: Record<string, string | null>  (null = ignore column)
     * e.g. { "Customer Name": "name", "Email Address": "email", "Ref Code": null }
     */
    columnMapping: jsonb('column_mapping'),

    /** Total rows detected in the file (set after initial parse, before processing) */
    totalRows: integer('total_rows'),

    /** Rows processed so far (incremented per row by the queue consumer) */
    rowsProcessed: integer('rows_processed').notNull().default(0),

    /** Rows successfully imported */
    successCount: integer('success_count').notNull().default(0),

    /** Rows skipped (duplicates / missing required fields) */
    skippedCount: integer('skipped_count').notNull().default(0),

    /** Rows that errored during import */
    errorCount: integer('error_count').notNull().default(0),

    /** Timestamp when processing started */
    startedAt: timestamp('started_at', { withTimezone: true }),

    /** Timestamp when processing completed (success or failure) */
    completedAt: timestamp('completed_at', { withTimezone: true }),

    /** Top-level error message (set when status = 'failed') */
    errorMessage: text('error_message'),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    typeCheck: check(
      'import_jobs_type_check',
      sql`${t.type} IN ('customers','invoices','products','time_entries','bulk_action')`,
    ),
    statusCheck: check(
      'import_jobs_status_check',
      sql`${t.status} IN ('pending','processing','completed','failed')`,
    ),
    tenantStatusIdx: index('idx_import_jobs_tenant_status').on(t.tenantId, t.status),
    // Note: (tenant_id, created_at DESC) partial index → raw_ddl
    // CREATE INDEX import_jobs_tenant_created ON import_jobs (tenant_id, created_at DESC)
  }),
)

export type ImportJobRow = typeof importJobs.$inferSelect
export type NewImportJob = typeof importJobs.$inferInsert

// ── import_job_results ────────────────────────────────────────────────────────

export const importJobResults = pgTable(
  'import_job_results',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    importJobId: uuid('import_job_id')
      .notNull()
      .references(() => importJobs.id, { onDelete: 'cascade' }),

    /** Denormalized for RLS — matches import_jobs.tenant_id */
    tenantId: uuid('tenant_id').notNull(),

    /** 1-based row number in the source file */
    rowNumber: integer('row_number').notNull(),

    /** 'success' | 'skipped' | 'error' */
    status: text('status').notNull(),

    /** Human-readable reason for skipped or error rows */
    message: text('message'),

    /** Raw CSV row as-received (for error download) */
    originalData: text('original_data'),

    /**
     * UUID of the created entity (customer_id / invoice_id).
     * Null for skipped/error rows.
     */
    entityId: uuid('entity_id'),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    statusCheck: check(
      'import_job_results_status_check',
      sql`${t.status} IN ('success','skipped','error')`,
    ),
    importJobIdx: index('idx_import_job_results_job').on(t.importJobId, t.rowNumber),
    // Note: partial index for error/skipped rows → raw_ddl
    // CREATE INDEX import_job_results_errors ON import_job_results (import_job_id) WHERE status IN ('skipped', 'error')
  }),
)

export type ImportJobResultRow = typeof importJobResults.$inferSelect
export type NewImportJobResult = typeof importJobResults.$inferInsert