import { pgTable, text, integer, jsonb, uuid, date, index, uniqueIndex } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import { id, tenantId, createdAt, updatedAt, ts, qty } from './_common';
import { taskTypeEnum, taskStatusEnum, packLevelEnum } from './enums';
import { warehouses } from './master-data';

/** Unified work queue consumed by handheld (RF) users and by supervisors. */
export const tasks = pgTable(
  'tasks',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    taskNumber: text('task_number').notNull(),
    type: taskTypeEnum('type').notNull(),
    status: taskStatusEnum('status').notNull().default('PENDING'),
    priority: integer('priority').notNull().default(5),
    sequence: integer('sequence').notNull().default(0),
    groupKey: text('group_key'),
    zoneId: uuid('zone_id'),
    storerId: uuid('storer_id'),
    skuId: uuid('sku_id'),
    qty: qty('qty'),
    qtyDone: qty('qty_done').notNull().default(sql`0`),
    packLevel: packLevelEnum('pack_level'),
    fromLocationId: uuid('from_location_id'),
    toLocationId: uuid('to_location_id'),
    lpnId: uuid('lpn_id'),
    toLpnId: uuid('to_lpn_id'),
    lotNumber: text('lot_number'),
    expiryDate: date('expiry_date'),
    refType: text('ref_type'),
    refId: uuid('ref_id'),
    assignedToId: uuid('assigned_to_id'),
    assignedAt: ts('assigned_at'),
    startedAt: ts('started_at'),
    completedAt: ts('completed_at'),
    completedById: uuid('completed_by_id'),
    exceptionCode: text('exception_code'),
    exceptionNotes: text('exception_notes'),
    data: jsonb('data').notNull().default({}).$type<Record<string, unknown>>(),
    createdById: uuid('created_by_id'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('tasks_tenant_number_uq').on(t.tenantId, t.taskNumber),
    index('tasks_queue_idx').on(t.tenantId, t.warehouseId, t.type, t.status, t.priority, t.sequence),
    index('tasks_assignee_idx').on(t.tenantId, t.assignedToId, t.status),
    index('tasks_group_idx').on(t.tenantId, t.groupKey),
    index('tasks_ref_idx').on(t.tenantId, t.refType, t.refId),
  ],
);