/**
 * Task status CRUD + reorder queries — tasks-board-engine.
 *
 * listStatuses: returns project-specific statuses when projectId is provided,
 * else falls back to tenant-global (project_id IS NULL) statuses, ordered by position.
 *
 * deleteStatus requires reassignToStatusId when tasks still reference the status
 * to prevent orphaned task_status FKs.
 *
 * reorderStatuses: updates positions in a single transaction.
 */
import { eq, and, isNull, asc } from 'drizzle-orm'
import type { Db } from '../client'
import { taskStatuses, tasks } from '../schema/tasks'
import { auditLog } from './_audit-forward'
import type { TaskStatusObject } from '@zync/types'
import {
  assertTenantOwnsOrThrow,
  assertTenantOwnsProject,
} from './tenant-guards'

// ── Serializer ────────────────────────────────────────────────────────────────

function serializeStatus(row: typeof taskStatuses.$inferSelect): TaskStatusObject {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    project_id: row.projectId ?? null,
    name: row.name,
    color: row.color,
    position: row.position,
    is_terminal: row.isTerminal,
    created_at: row.createdAt.toISOString(),
  }
}

// ── listStatuses ──────────────────────────────────────────────────────────────

/**
 * Return statuses for a tenant.
 * When projectId is provided, first checks for project-specific statuses;
 * if none exist, falls back to tenant-global (project_id IS NULL).
 */
export async function listStatuses(
  db: Db,
  tenantId: string,
  projectId?: string,
): Promise<TaskStatusObject[]> {
  if (projectId) {
    const projectRows = await db
      .select()
      .from(taskStatuses)
      .where(
        and(
          eq(taskStatuses.tenantId, tenantId),
          eq(taskStatuses.projectId, projectId),
        ),
      )
      .orderBy(asc(taskStatuses.position))

    if (projectRows.length > 0) return projectRows.map(serializeStatus)
  }

  // Fallback: tenant-global statuses
  const rows = await db
    .select()
    .from(taskStatuses)
    .where(
      and(
        eq(taskStatuses.tenantId, tenantId),
        isNull(taskStatuses.projectId),
      ),
    )
    .orderBy(asc(taskStatuses.position))

  return rows.map(serializeStatus)
}

// ── getTaskStatusById ─────────────────────────────────────────────────────────

export async function getTaskStatusById(
  db: Db,
  tenantId: string,
  id: string,
): Promise<TaskStatusObject | null> {
  const [row] = await db
    .select()
    .from(taskStatuses)
    .where(and(eq(taskStatuses.tenantId, tenantId), eq(taskStatuses.id, id)))
    .limit(1)

  return row ? serializeStatus(row) : null
}

// ── createStatus ──────────────────────────────────────────────────────────────

export async function createStatus(
  db: Db,
  tenantId: string,
  input: {
    name: string
    color: string
    projectId?: string | null
    isTerminal?: boolean
  },
): Promise<TaskStatusObject> {
  assertTenantOwnsOrThrow(
    'project_id',
    await assertTenantOwnsProject(db, tenantId, input.projectId),
  )

  // Determine next position
  const existing = await listStatuses(db, tenantId, input.projectId ?? undefined)
  const nextPosition = existing.length

  const [row] = await db
    .insert(taskStatuses)
    .values({
      tenantId,
      projectId: input.projectId ?? null,
      name: input.name,
      color: input.color,
      position: nextPosition,
      isTerminal: input.isTerminal ?? false,
    })
    .returning()

  if (!row) throw new Error('Status not found after insert')
  return serializeStatus(row)
}

// ── updateStatus ──────────────────────────────────────────────────────────────

export async function updateStatus(
  db: Db,
  tenantId: string,
  id: string,
  patch: Partial<{ name: string; color: string; isTerminal: boolean }>,
): Promise<TaskStatusObject> {
  const setValues: Partial<typeof taskStatuses.$inferInsert> = {}
  if (patch.name !== undefined) setValues.name = patch.name
  if (patch.color !== undefined) setValues.color = patch.color
  if (patch.isTerminal !== undefined) setValues.isTerminal = patch.isTerminal

  const [row] = await db
    .update(taskStatuses)
    .set(setValues)
    .where(and(eq(taskStatuses.tenantId, tenantId), eq(taskStatuses.id, id)))
    .returning()

  if (!row) throw new Error('Status not found')
  return serializeStatus(row)
}

// ── deleteStatus ──────────────────────────────────────────────────────────────

/**
 * Delete a status. If any tasks reference it, all are reassigned to
 * reassignToStatusId before deletion.
 */
export async function deleteStatus(
  db: Db,
  tenantId: string,
  id: string,
  reassignToStatusId: string,
): Promise<void> {
  return db.transaction(async (tx) => {
    // Verify both statuses belong to the tenant
    const [statusRow] = await tx
      .select({ id: taskStatuses.id })
      .from(taskStatuses)
      .where(and(eq(taskStatuses.tenantId, tenantId), eq(taskStatuses.id, id)))
      .limit(1)

    if (!statusRow) throw new Error('Status not found')

    const [reassignRow] = await tx
      .select({ id: taskStatuses.id })
      .from(taskStatuses)
      .where(
        and(eq(taskStatuses.tenantId, tenantId), eq(taskStatuses.id, reassignToStatusId)),
      )
      .limit(1)

    if (!reassignRow) throw new Error('Reassign target status not found')

    // Reassign tasks
    await tx
      .update(tasks)
      .set({ statusId: reassignToStatusId, updatedAt: new Date() })
      .where(and(eq(tasks.tenantId, tenantId), eq(tasks.statusId, id)))

    // Delete the status
    await tx
      .delete(taskStatuses)
      .where(and(eq(taskStatuses.tenantId, tenantId), eq(taskStatuses.id, id)))

    await tx.insert(auditLog).values({
      tenantId,
      actorId: null,
      actorType: 'system',
      entityType: 'task_status',
      entityId: id,
      action: 'task_status.deleted',
    })
  })
}

// ── reorderStatuses ───────────────────────────────────────────────────────────

/**
 * Update positions for a set of statuses in a single transaction.
 * Caller provides the full desired order as { id, position }[].
 */
export async function reorderStatuses(
  db: Db,
  tenantId: string,
  order: { id: string; position: number }[],
): Promise<void> {
  return db.transaction(async (tx) => {
    for (const { id, position } of order) {
      await tx
        .update(taskStatuses)
        .set({ position })
        .where(and(eq(taskStatuses.tenantId, tenantId), eq(taskStatuses.id, id)))
    }

    if (order.length > 0) {
      await tx.insert(auditLog).values({
        tenantId,
        actorId: null,
        actorType: 'system',
        entityType: 'task_status',
        entityId: order[0]!.id,
        action: 'task_status.reordered',
      })
    }
  })
}

// ── task sync settings helpers ────────────────────────────────────────────────

import { taskSyncSettings, type TaskSyncSettingsRow } from '../schema/tasks'

/** Direct schema re-export for use in routes/queue (avoids @zync/db/schema path) */
export { taskSyncSettings }
export type { TaskSyncSettingsRow }

/**
 * Load task_sync_settings for a tenant (or null if not configured).
 */
export async function getTaskSyncSettings(
  db: Db,
  tenantId: string,
): Promise<TaskSyncSettingsRow | null> {
  const [row] = await db
    .select()
    .from(taskSyncSettings)
    .where(eq(taskSyncSettings.tenantId, tenantId))
    .limit(1)
  return row ?? null
}

/**
 * Upsert task_sync_settings for a tenant.
 */
export async function upsertTaskSyncSettings(
  db: Db,
  tenantId: string,
  patch: Partial<{
    syncIntervalMinutes: number
    autoCreateTicketsFrom: Record<string, boolean>
    defaultProjectId: string | null
  }>,
): Promise<TaskSyncSettingsRow> {
  const [row] = await db
    .insert(taskSyncSettings)
    .values({
      tenantId,
      syncIntervalMinutes: patch.syncIntervalMinutes ?? 120,
      autoCreateTicketsFrom: patch.autoCreateTicketsFrom ?? {},
      defaultProjectId: patch.defaultProjectId ?? null,
    })
    .onConflictDoUpdate({
      target: [taskSyncSettings.tenantId],
      set: {
        ...(patch.syncIntervalMinutes !== undefined
          ? { syncIntervalMinutes: patch.syncIntervalMinutes }
          : {}),
        ...(patch.autoCreateTicketsFrom !== undefined
          ? { autoCreateTicketsFrom: patch.autoCreateTicketsFrom }
          : {}),
        ...(patch.defaultProjectId !== undefined
          ? { defaultProjectId: patch.defaultProjectId }
          : {}),
        updatedAt: new Date(),
      },
    })
    .returning()

  if (!row) throw new Error('task_sync_settings not found after upsert')
  return row
}