/**
 * Recurring Tasks & Templates query helpers — recurring-tasks-templates.
 *
 * All tenant-scoped helpers carry a tenant_id WHERE clause.
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 *
 * listActiveRecurringTasks() is system-scoped (all tenants) for the cron.
 * generateTaskFromRecurring() uses ON CONFLICT DO NOTHING backed by the
 * partial unique index idx_tasks_recurring_due on tasks(recurring_task_id, due_date).
 */
import { and, eq, desc, asc, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { recurringTasks, taskTemplates } from '../schema/recurring-tasks'
import { auditLog } from './_audit-forward'
import {
  assertTenantOwnsOrThrow,
  assertTenantOwnsProject,
  assertActiveTenantAssignee,
  assertTenantOwnsTaskStatus,
} from './tenant-guards'
import { tasks, taskLabels, taskStatuses } from '../schema/tasks'
import { serializeRecurringTask, serializeTaskTemplate } from '../serialize/recurring-tasks'
import type {
  RecurringTaskObject,
  TaskTemplateObject,
  CreateRecurringTaskInput,
  UpdateRecurringTaskInput,
  CreateTaskTemplateInput,
} from '@zync/types'
import type { RecurringTaskRow } from '../schema/recurring-tasks'

// ── Recurring tasks — tenant-scoped ──────────────────────────────────────────

export async function listRecurringTasks(
  db: Db,
  tenantId: string,
): Promise<RecurringTaskObject[]> {
  const rows = await db
    .select()
    .from(recurringTasks)
    .where(eq(recurringTasks.tenantId, tenantId))
    .orderBy(desc(recurringTasks.createdAt))

  return rows.map(serializeRecurringTask)
}

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

  return row ? serializeRecurringTask(row) : null
}

export async function createRecurringTask(
  db: Db,
  tenantId: string,
  input: CreateRecurringTaskInput,
  createdBy: string,
): Promise<RecurringTaskObject> {
  assertTenantOwnsOrThrow(
    'project_id',
    await assertTenantOwnsProject(db, tenantId, input.project_id),
  )
  assertTenantOwnsOrThrow(
    'assignee_id',
    await assertActiveTenantAssignee(db, tenantId, input.assignee_id),
  )
  assertTenantOwnsOrThrow(
    'status_id',
    await assertTenantOwnsTaskStatus(db, tenantId, input.status_id),
  )

  const [row] = await db
    .insert(recurringTasks)
    .values({
      tenantId,
      projectId: input.project_id ?? null,
      title: input.title,
      description: input.description ?? null,
      assigneeId: input.assignee_id ?? null,
      estimatedHours: input.estimated_hours != null ? String(input.estimated_hours) : null,
      priority: input.priority ?? 'medium',
      labels: (input.labels ?? []) as string[],
      recurrence: input.recurrence,
      advanceDays: input.advance_days,
      statusId: input.status_id ?? null,
      createdBy,
    })
    .returning()

  if (!row) throw new Error('Failed to create recurring task')
  return serializeRecurringTask(row)
}

export async function updateRecurringTask(
  db: Db,
  tenantId: string,
  id: string,
  patch: UpdateRecurringTaskInput,
): Promise<RecurringTaskObject> {
  const setValues: Partial<typeof recurringTasks.$inferInsert> = {}

  if (patch.title !== undefined) setValues.title = patch.title
  if (patch.description !== undefined) setValues.description = patch.description
  if (patch.project_id !== undefined) setValues.projectId = patch.project_id
  if (patch.assignee_id !== undefined) setValues.assigneeId = patch.assignee_id
  if (patch.estimated_hours !== undefined)
    setValues.estimatedHours =
      patch.estimated_hours != null ? String(patch.estimated_hours) : null
  if (patch.priority !== undefined) setValues.priority = patch.priority
  if (patch.labels !== undefined) setValues.labels = patch.labels as string[]
  if (patch.recurrence !== undefined) setValues.recurrence = patch.recurrence
  if (patch.advance_days !== undefined) setValues.advanceDays = patch.advance_days
  if (patch.status_id !== undefined) setValues.statusId = patch.status_id
  if (patch.is_active !== undefined) setValues.isActive = patch.is_active

  if (patch.project_id !== undefined) {
    assertTenantOwnsOrThrow(
      'project_id',
      await assertTenantOwnsProject(db, tenantId, patch.project_id),
    )
  }
  if (patch.assignee_id !== undefined) {
    assertTenantOwnsOrThrow(
      'assignee_id',
      await assertActiveTenantAssignee(db, tenantId, patch.assignee_id),
    )
  }
  if (patch.status_id !== undefined) {
    assertTenantOwnsOrThrow(
      'status_id',
      await assertTenantOwnsTaskStatus(db, tenantId, patch.status_id),
    )
  }

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

  if (!row) throw new Error('Recurring task not found')
  return serializeRecurringTask(row)
}

export async function deleteRecurringTask(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  const [row] = await db
    .delete(recurringTasks)
    .where(and(eq(recurringTasks.tenantId, tenantId), eq(recurringTasks.id, id)))
    .returning({ id: recurringTasks.id })

  if (!row) throw new Error('Recurring task not found')
}

// ── Recurring tasks — system-scoped (cron) ────────────────────────────────────

/**
 * List all active recurring task definitions across all tenants.
 * Used exclusively by the cron route — no tenant filtering.
 */
export async function listActiveRecurringTasks(db: Db): Promise<RecurringTaskRow[]> {
  return db
    .select()
    .from(recurringTasks)
    .where(eq(recurringTasks.isActive, true))
}

/**
 * Update last_generated_at to now() after cron processing.
 */
export async function touchRecurringGenerated(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  await db
    .update(recurringTasks)
    .set({ lastGeneratedAt: new Date() })
    .where(and(eq(recurringTasks.tenantId, tenantId), eq(recurringTasks.id, id)))
}

// ── Task generation ───────────────────────────────────────────────────────────

/**
 * Insert a task generated from a recurring definition.
 *
 * Uses raw SQL INSERT with ON CONFLICT (recurring_task_id, due_date) DO NOTHING
 * backed by idx_tasks_recurring_due partial unique index.
 * This makes cron re-runs idempotent without transaction-level dedup.
 *
 * Also inserts one task_labels row per label in the recurring definition.
 * Returns { taskId: string | null } — null if the slot was already filled.
 */
export async function generateTaskFromRecurring(
  db: Db,
  tenantId: string,
  recurring: RecurringTaskRow,
  dueDate: string, // YYYY-MM-DD
): Promise<{ taskId: string | null }> {
  return db.transaction(async (tx) => {
    // Resolve status: use definition's status if set; else find lowest-position
    // non-terminal status for the tenant (project-scoped first, then global).
    // tasks.status_id is NOT NULL — we must always resolve a valid status.
    let resolvedStatusId: string | null = recurring.statusId ?? null

    if (!resolvedStatusId) {
      // Prefer a status scoped to the same project; fall back to tenant-global
      const projectId = recurring.projectId ?? null
      const whereConditions = projectId
        ? and(eq(taskStatuses.tenantId, tenantId), eq(taskStatuses.projectId, projectId))
        : and(eq(taskStatuses.tenantId, tenantId))

      const [defaultStatus] = await tx
        .select({ id: taskStatuses.id })
        .from(taskStatuses)
        .where(
          and(
            whereConditions,
            eq(taskStatuses.isTerminal, false),
          ),
        )
        .orderBy(asc(taskStatuses.position))
        .limit(1)

      if (!defaultStatus) {
        // Absolute fallback: any status for this tenant (even terminal), lowest position
        const [anyStatus] = await tx
          .select({ id: taskStatuses.id })
          .from(taskStatuses)
          .where(eq(taskStatuses.tenantId, tenantId))
          .orderBy(asc(taskStatuses.position))
          .limit(1)
        if (!anyStatus) {
          // No statuses at all — cannot insert (tasks.status_id NOT NULL)
          return { taskId: null }
        }
        resolvedStatusId = anyStatus.id
      } else {
        resolvedStatusId = defaultStatus.id
      }
    }

    // Get the max position in the resolved status column
    let position = 1
    {
      const [maxRow] = await tx
        .select({ pos: tasks.position })
        .from(tasks)
        .where(
          and(
            eq(tasks.tenantId, tenantId),
            eq(tasks.statusId, resolvedStatusId!),
          ),
        )
        .orderBy(desc(tasks.position))
        .limit(1)

      if (maxRow) {
        position = parseFloat(String(maxRow.pos)) + 1
      }
    }

    // INSERT with ON CONFLICT DO NOTHING on (recurring_task_id, due_date)
    // The partial unique index idx_tasks_recurring_due enforces dedup.
    // We use raw SQL here because recurring_task_id is added to the tasks table
    // via ALTER TABLE (schema delta) and is not yet in the Drizzle tasks model.
    // Column names verified against packages/db/src/schema/tasks.ts:
    //   tenant_id, project_id, status_id, title, description, priority,
    //   assignee_id, reporter_id, due_date, estimated_hours, source, position
    // Plus the schema-delta column: recurring_task_id
    const result = await tx.execute(
      sql`INSERT INTO tasks (
        tenant_id, project_id, status_id, title, description,
        assignee_id, reporter_id, estimated_hours, priority,
        due_date, recurring_task_id, source, position
      )
      VALUES (
        ${tenantId},
        ${recurring.projectId ?? null},
        ${resolvedStatusId},
        ${recurring.title},
        ${recurring.description ?? null},
        ${recurring.assigneeId ?? null},
        ${recurring.createdBy},
        ${recurring.estimatedHours ?? null},
        ${recurring.priority},
        ${dueDate}::date,
        ${recurring.id},
        'manual',
        ${String(position)}
      )
      ON CONFLICT (recurring_task_id, due_date) WHERE recurring_task_id IS NOT NULL
      DO NOTHING
      RETURNING id`,
    )

    const taskId = (result?.[0] as { id?: string } | undefined)?.id ?? null

    // If the task was inserted (not a duplicate), expand labels and audit
    if (taskId) {
      if (recurring.labels && (recurring.labels as string[]).length > 0) {
        const labelRows = (recurring.labels as string[]).map((label) => ({ taskId, label }))
        await tx.insert(taskLabels).values(labelRows).onConflictDoNothing()
      }

      await tx.insert(auditLog).values({
        tenantId,
        actorId: null,
        actorType: 'system',
        entityType: 'task',
        entityId: taskId,
        action: 'task.generated_from_recurring',
      })
    }

    return { taskId }
  })
}

// ── Task templates — tenant-scoped ────────────────────────────────────────────

export async function listTaskTemplates(
  db: Db,
  tenantId: string,
): Promise<TaskTemplateObject[]> {
  const rows = await db
    .select()
    .from(taskTemplates)
    .where(eq(taskTemplates.tenantId, tenantId))
    .orderBy(desc(taskTemplates.createdAt))

  return rows.map(serializeTaskTemplate)
}

export async function createTaskTemplate(
  db: Db,
  tenantId: string,
  input: CreateTaskTemplateInput,
  createdBy: string,
): Promise<TaskTemplateObject> {
  const [row] = await db
    .insert(taskTemplates)
    .values({
      tenantId,
      name: input.name,
      title: input.title,
      description: input.description ?? null,
      estimatedHours: input.estimated_hours != null ? String(input.estimated_hours) : null,
      priority: input.priority ?? 'medium',
      labels: (input.labels ?? []) as string[],
      createdBy,
    })
    .returning()

  if (!row) throw new Error('Failed to create task template')
  return serializeTaskTemplate(row)
}

export async function deleteTaskTemplate(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  const [row] = await db
    .delete(taskTemplates)
    .where(and(eq(taskTemplates.tenantId, tenantId), eq(taskTemplates.id, id)))
    .returning({ id: taskTemplates.id })

  if (!row) throw new Error('Task template not found')
}