/**
 * Project template query helpers — project-template-gallery.
 *
 * All write helpers are tenant-scoped. System templates (tenant_id IS NULL)
 * are readable by any tenant but are not editable via these helpers.
 *
 * instantiateTemplate: opens a single db.transaction that:
 *   1. Direct-inserts a project row (avoids createProject's nested tx)
 *   2. Resolves the first non-terminal status for the new project
 *   3. Direct-inserts each task row + label rows
 *   4. Increments template usage_count
 *
 */
import { and, eq, ilike, inArray, isNull, or, asc, desc, sql } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { projectTemplates, projectTemplateTasks } from '../schema/project-templates'
import type {
  ProjectTemplateRow,
  ProjectTemplateTaskRow,
  NewProjectTemplate,
  NewProjectTemplateTask,
} from '../schema/project-templates'
import { projects, projectMembers } from '../schema/projects'
import { tasks, taskLabels, taskStatuses } from '../schema/tasks'
import { tenantMemberships, roles } from '../schema/rbac'
import { users } from '../schema/users'
import type { NewProject } from '../schema/projects'
import { auditLog } from './_audit-forward'

// ── Re-export zod for route-layer convenience ─────────────────────────────────
export { z }

type DbTx = Parameters<Parameters<Db['transaction']>[0]>[0]
type QueryDb = Db | DbTx

// ── Serializers ───────────────────────────────────────────────────────────────

export interface TemplateObject {
  id: string
  tenant_id: string | null
  name: string
  description: string | null
  category: string | null
  estimated_days: number | null
  billing_type: string | null
  thumbnail_emoji: string
  is_public: boolean
  usage_count: number
  created_by: string | null
  created_at: string
  updated_at: string
  is_system: boolean
}

export interface TemplateTaskObject {
  id: string
  template_id: string
  title: string
  description: string | null
  phase: string | null
  position: number
  relative_start_days: number | null
  relative_due_days: number | null
  is_milestone: boolean
  checklist_items: unknown
  estimated_hours: number | null
  assigned_role: string | null
  tags: string[] | null
}

export interface TemplateListPage {
  items: TemplateObject[]
  total: number
  page: number
  pageSize: number
}

function serializeTemplate(row: ProjectTemplateRow): TemplateObject {
  return {
    id: row.id,
    tenant_id: row.tenantId ?? null,
    name: row.name,
    description: row.description ?? null,
    category: row.category ?? null,
    estimated_days: row.estimatedDays ?? null,
    billing_type: row.billingType ?? null,
    thumbnail_emoji: row.thumbnailEmoji,
    is_public: row.isPublic,
    usage_count: row.usageCount,
    created_by: row.createdBy ?? null,
    created_at: row.createdAt.toISOString(),
    updated_at: row.updatedAt.toISOString(),
    is_system: row.tenantId === null,
  }
}

function serializeTemplateTask(row: ProjectTemplateTaskRow): TemplateTaskObject {
  return {
    id: row.id,
    template_id: row.templateId,
    title: row.title,
    description: row.description ?? null,
    phase: row.phase ?? null,
    position: row.position,
    relative_start_days: row.relativeStartDays ?? null,
    relative_due_days: row.relativeDueDays ?? null,
    is_milestone: row.isMilestone,
    checklist_items: row.checklistItems,
    estimated_hours:
      row.estimatedHours !== null && row.estimatedHours !== undefined
        ? parseFloat(String(row.estimatedHours))
        : null,
    assigned_role: row.assignedRole ?? null,
    tags: row.tags ?? null,
  }
}

// ── listTemplates ─────────────────────────────────────────────────────────────

/**
 * Returns system templates (tenant_id IS NULL) plus the calling tenant's own
 * templates, ordered by usage_count DESC, name ASC.
 */
export async function listTemplates(
  db: Db,
  tenantId: string,
  opts: {
    category?: string
    q?: string
    page?: number
    pageSize?: number
  } = {},
): Promise<TemplateListPage> {
  const page = Math.max(1, opts.page ?? 1)
  const pageSize = Math.min(Math.max(1, opts.pageSize ?? 20), 100)
  const filters = and(
    or(isNull(projectTemplates.tenantId), eq(projectTemplates.tenantId, tenantId)),
    opts.category ? eq(projectTemplates.category, opts.category) : undefined,
    opts.q ? ilike(projectTemplates.name, `%${opts.q}%`) : undefined,
  )

  const rows = await db
    .select()
    .from(projectTemplates)
    .where(filters)
    .orderBy(
      asc(sql`CASE WHEN ${projectTemplates.tenantId} IS NULL THEN 0 ELSE 1 END`),
      desc(projectTemplates.usageCount),
      asc(projectTemplates.name),
    )

  const items = rows.map(serializeTemplate)
  const start = (page - 1) * pageSize
  return {
    items: items.slice(start, start + pageSize),
    total: items.length,
    page,
    pageSize,
  }
}

// ── getTemplate ───────────────────────────────────────────────────────────────

export async function getTemplate(
  db: Db,
  templateId: string,
  tenantId: string,
): Promise<TemplateObject | null> {
  const [row] = await db
    .select()
    .from(projectTemplates)
    .where(
      and(
        eq(projectTemplates.id, templateId),
        or(isNull(projectTemplates.tenantId), eq(projectTemplates.tenantId, tenantId)),
      ),
    )
    .limit(1)
  return row ? serializeTemplate(row) : null
}

// ── getTemplateTasks ──────────────────────────────────────────────────────────

export async function getTemplateTasks(
  db: Db,
  templateId: string,
  tenantId: string,
): Promise<TemplateTaskObject[]> {
  // Verify the caller has access to this template first
  const template = await getTemplate(db, templateId, tenantId)
  if (!template) return []

  const rows = await db
    .select()
    .from(projectTemplateTasks)
    .where(eq(projectTemplateTasks.templateId, templateId))
    .orderBy(asc(projectTemplateTasks.position))
  return rows.map(serializeTemplateTask)
}

// ── createTemplate ────────────────────────────────────────────────────────────

export const createTemplateSchema = z.object({
  name: z.string().min(1).max(200),
  description: z.string().max(2000).optional(),
  category: z.enum(['design', 'development', 'marketing', 'consulting', 'other']).optional(),
  estimated_days: z.number().int().positive().optional(),
  billing_type: z.enum(['fixed', 'hourly', 'retainer']).optional(),
  thumbnail_emoji: z.string().max(8).optional(),
  is_public: z.boolean().optional(),
})

export type CreateTemplateInput = z.infer<typeof createTemplateSchema>

export async function createTemplate(
  db: Db,
  tenantId: string,
  input: CreateTemplateInput,
  createdBy: string,
): Promise<TemplateObject> {
  const values: NewProjectTemplate = {
    tenantId,
    name: input.name,
    description: input.description ?? null,
    category: input.category ?? null,
    estimatedDays: input.estimated_days ?? null,
    billingType: input.billing_type ?? null,
    thumbnailEmoji: input.thumbnail_emoji ?? '📋',
    isPublic: input.is_public ?? false,
    createdBy,
  }
  const [row] = await db.insert(projectTemplates).values(values).returning()
  if (!row) throw new Error('Template not found after insert')
  return serializeTemplate(row)
}

// ── updateTemplate ────────────────────────────────────────────────────────────

export const updateTemplateSchema = z.object({
  name: z.string().min(1).max(200).optional(),
  description: z.string().max(2000).optional(),
  category: z.enum(['design', 'development', 'marketing', 'consulting', 'other']).optional(),
  estimated_days: z.number().int().positive().nullable().optional(),
  billing_type: z.enum(['fixed', 'hourly', 'retainer']).nullable().optional(),
  thumbnail_emoji: z.string().max(8).optional(),
  is_public: z.boolean().optional(),
})

export type UpdateTemplateInput = z.infer<typeof updateTemplateSchema>

export async function updateTemplate(
  db: Db,
  tenantId: string,
  templateId: string,
  input: UpdateTemplateInput,
): Promise<TemplateObject | null> {
  const [row] = await db
    .update(projectTemplates)
    .set({
      name: input.name,
      description: input.description,
      category: input.category,
      estimatedDays: input.estimated_days ?? undefined,
      billingType: input.billing_type ?? undefined,
      thumbnailEmoji: input.thumbnail_emoji,
      isPublic: input.is_public,
      updatedAt: new Date(),
    })
    .where(
      and(
        eq(projectTemplates.id, templateId),
        eq(projectTemplates.tenantId, tenantId),
      ),
    )
    .returning()
  return row ? serializeTemplate(row) : null
}

// ── deleteTemplate ────────────────────────────────────────────────────────────

export async function deleteTemplate(
  db: Db,
  tenantId: string,
  templateId: string,
): Promise<boolean> {
  const result = await db
    .delete(projectTemplates)
    .where(
      and(
        eq(projectTemplates.id, templateId),
        eq(projectTemplates.tenantId, tenantId),
      ),
    )
    .returning({ id: projectTemplates.id })
  return result.length > 0
}

// ── Template tasks CRUD ───────────────────────────────────────────────────────

export const createTemplateTaskSchema = z.object({
  title: z.string().min(1).max(500),
  description: z.string().max(5000).optional(),
  phase: z.string().max(200).optional(),
  position: z.number().int().min(0).optional(),
  relative_start_days: z.number().int().min(0).optional(),
  relative_due_days: z.number().int().min(0).optional(),
  is_milestone: z.boolean().optional(),
  checklist_items: z.array(z.object({ text: z.string(), required: z.boolean().optional() })).optional(),
  estimated_hours: z.number().positive().optional(),
  assigned_role: z.enum(['MEMBER', 'ADMIN', 'CONTRACTOR']).optional(),
  tags: z.array(z.string()).optional(),
})

export type CreateTemplateTaskInput = z.infer<typeof createTemplateTaskSchema>

export async function addTemplateTask(
  db: Db,
  tenantId: string,
  templateId: string,
  input: CreateTemplateTaskInput,
): Promise<TemplateTaskObject | null> {
  // Verify access
  const template = await getTemplate(db, templateId, tenantId)
  if (!template || template.is_system) return null

  // Default position: max + 1
  let position = input.position
  if (position === undefined) {
    const [maxRow] = await db
      .select({ pos: projectTemplateTasks.position })
      .from(projectTemplateTasks)
      .where(eq(projectTemplateTasks.templateId, templateId))
      .orderBy(sql`${projectTemplateTasks.position} DESC`)
      .limit(1)
    position = maxRow ? maxRow.pos + 1 : 0
  }

  const values: NewProjectTemplateTask = {
    templateId,
    title: input.title,
    description: input.description ?? null,
    phase: input.phase ?? null,
    position,
    relativeStartDays: input.relative_start_days ?? null,
    relativeDueDays: input.relative_due_days ?? null,
    isMilestone: input.is_milestone ?? false,
    checklistItems: input.checklist_items ?? [],
    estimatedHours: input.estimated_hours != null ? String(input.estimated_hours) : null,
    assignedRole: input.assigned_role ?? null,
    tags: input.tags ?? null,
  }

  const [row] = await db.insert(projectTemplateTasks).values(values).returning()
  if (!row) throw new Error('Template task not found after insert')
  return serializeTemplateTask(row)
}

export const updateTemplateTaskSchema = z.object({
  title: z.string().min(1).max(500).optional(),
  description: z.string().max(5000).optional(),
  phase: z.string().max(200).optional(),
  position: z.number().int().min(0).optional(),
  relative_start_days: z.number().int().min(0).nullable().optional(),
  relative_due_days: z.number().int().min(0).nullable().optional(),
  is_milestone: z.boolean().optional(),
  checklist_items: z.array(z.object({ text: z.string(), required: z.boolean().optional() })).optional(),
  estimated_hours: z.number().positive().nullable().optional(),
  assigned_role: z.enum(['MEMBER', 'ADMIN', 'CONTRACTOR']).nullable().optional(),
  tags: z.array(z.string()).optional(),
})

export type UpdateTemplateTaskInput = z.infer<typeof updateTemplateTaskSchema>

export async function updateTemplateTask(
  db: Db,
  tenantId: string,
  templateId: string,
  taskId: string,
  input: UpdateTemplateTaskInput,
): Promise<TemplateTaskObject | null> {
  // Verify access to template
  const template = await getTemplate(db, templateId, tenantId)
  if (!template || template.is_system) return null

  const [row] = await db
    .update(projectTemplateTasks)
    .set({
      title: input.title,
      description: input.description,
      phase: input.phase,
      position: input.position,
      relativeStartDays: input.relative_start_days ?? undefined,
      relativeDueDays: input.relative_due_days ?? undefined,
      isMilestone: input.is_milestone,
      checklistItems: input.checklist_items,
      estimatedHours:
        input.estimated_hours !== undefined
          ? input.estimated_hours !== null
            ? String(input.estimated_hours)
            : null
          : undefined,
      assignedRole: input.assigned_role ?? undefined,
      tags: input.tags,
    })
    .where(
      and(
        eq(projectTemplateTasks.id, taskId),
        eq(projectTemplateTasks.templateId, templateId),
        inArray(
          projectTemplateTasks.templateId,
          db
            .select({ id: projectTemplates.id })
            .from(projectTemplates)
            .where(and(eq(projectTemplates.id, templateId), eq(projectTemplates.tenantId, tenantId))),
        ),
      ),
    )
    .returning()
  return row ? serializeTemplateTask(row) : null
}

export async function deleteTemplateTask(
  db: Db,
  tenantId: string,
  templateId: string,
  taskId: string,
): Promise<boolean> {
  const template = await getTemplate(db, templateId, tenantId)
  if (!template || template.is_system) return false

  const result = await db
    .delete(projectTemplateTasks)
    .where(
      and(
        eq(projectTemplateTasks.id, taskId),
        eq(projectTemplateTasks.templateId, templateId),
        inArray(
          projectTemplateTasks.templateId,
          db
            .select({ id: projectTemplates.id })
            .from(projectTemplates)
            .where(and(eq(projectTemplates.id, templateId), eq(projectTemplates.tenantId, tenantId))),
        ),
      ),
    )
    .returning({ id: projectTemplateTasks.id })
  return result.length > 0
}

// ── instantiateTemplate ───────────────────────────────────────────────────────
// Creates a new project + tasks from a template in a single transaction.
// Does NOT use createProject/createTask helpers (both open their own tx).

export const instantiateTemplateSchema = z.object({
  template_id: z.string().uuid(),
  project: z.object({
    name: z.string().min(1).max(200),
    customer_id: z.string().uuid().optional(),
    start_date: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
    billing_type: z.enum(['fixed', 'hourly', 'retainer']),
    currency: z.string().length(3).optional(),
    description: z.string().max(2000).optional(),
  }),
})

export type InstantiateTemplateInput = z.infer<typeof instantiateTemplateSchema>

export interface InstantiateResult {
  project_id: string
  tasks_created: number
}

function addDays(date: Date, days: number): Date {
  const d = new Date(date)
  d.setUTCDate(d.getUTCDate() + days)
  return d
}

function toDateString(date: Date): string {
  return date.toISOString().slice(0, 10)
}

export async function resolveFirstMemberWithRole(
  db: QueryDb,
  tenantId: string,
  role: 'MEMBER' | 'ADMIN' | 'CONTRACTOR',
): Promise<string | null> {
  const [member] = await db
    .select({ userId: tenantMemberships.userId })
    .from(tenantMemberships)
    .innerJoin(roles, eq(tenantMemberships.roleId, roles.id))
    .innerJoin(users, eq(tenantMemberships.userId, users.id))
    .where(
      and(
        eq(tenantMemberships.tenantId, tenantId),
        eq(tenantMemberships.status, 'active'),
        eq(roles.name, role),
        eq(users.status, 'active'),
      ),
    )
    .orderBy(asc(users.createdAt))
    .limit(1)

  return member?.userId ?? null
}

export async function instantiateTemplate(
  db: Db,
  tenantId: string,
  input: InstantiateTemplateInput,
  createdBy: string,
): Promise<InstantiateResult> {
  const template = await getTemplate(db, input.template_id, tenantId)
  if (!template) throw new Error('Template not found or not accessible')

  const templateTasks = await getTemplateTasks(db, input.template_id, tenantId)

  const projectStart = input.project.start_date ? new Date(input.project.start_date) : null

  return db.transaction(async (tx) => {
    // 1. Insert project directly (avoids nested transaction from createProject)
    const projectValues: NewProject = {
      tenantId,
      name: input.project.name,
      description: input.project.description ?? null,
      customerId: input.project.customer_id ?? null,
      billingType: input.project.billing_type,
      billingConfig: null,
      currency: input.project.currency ?? 'ILS',
      startDate: input.project.start_date ?? null,
      endDate: null,
      createdBy,
      status: 'active',
    }
    const [projectRowMaybe] = await tx.insert(projects).values(projectValues).returning()
    if (!projectRowMaybe) throw new Error('Project not found after insert')
    const projectRow = projectRowMaybe

    await tx.insert(auditLog).values({
      tenantId,
      actorId: createdBy,
      actorType: 'user',
      entityType: 'project',
      entityId: projectRow.id,
      action: 'project.created_from_template',
      changes: null,
    })

    // 2. Resolve the first non-terminal status for task assignment
    const [firstStatus] = await tx
      .select({ id: taskStatuses.id })
      .from(taskStatuses)
      .where(
        and(
          eq(taskStatuses.tenantId, tenantId),
          isNull(taskStatuses.projectId),
          eq(taskStatuses.isTerminal, false),
        ),
      )
      .orderBy(asc(taskStatuses.position))
      .limit(1)

    const statusId = firstStatus?.id
    if (!statusId) throw new Error('No non-terminal task status found for tenant — create at least one status first')

    // 3. Add the creator as project owner to keep the new project operable.
    await tx.insert(projectMembers).values({
      tenantId,
      projectId: projectRow.id,
      userId: createdBy,
      role: 'owner',
    }).onConflictDoNothing()

    // 4. Insert each template task
    let tasksCreated = 0
    for (const templateTask of templateTasks) {
      const startDate =
        projectStart !== null && templateTask.relative_start_days !== null
          ? toDateString(addDays(projectStart, templateTask.relative_start_days))
          : null
      const dueDate =
        projectStart !== null && templateTask.relative_due_days !== null
          ? toDateString(addDays(projectStart, templateTask.relative_due_days))
          : null

      const assigneeId = templateTask.assigned_role
        ? await resolveFirstMemberWithRole(tx, tenantId, templateTask.assigned_role as 'MEMBER' | 'ADMIN' | 'CONTRACTOR')
        : null

      const [taskRow] = await tx
        .insert(tasks)
        .values({
          tenantId,
          projectId: projectRow.id,
          statusId,
          title: templateTask.title,
          description: templateTask.description ?? null,
          priority: 'medium' as const,
          assigneeId: assigneeId ?? null,
          reporterId: createdBy,
          startDate,
          dueDate: dueDate ?? null,
          estimatedHours:
            templateTask.estimated_hours !== null
              ? String(templateTask.estimated_hours)
              : null,
          checklistItems: templateTask.checklist_items ?? [],
          phase: templateTask.phase ?? null,
          isMilestone: templateTask.is_milestone,
          source: 'manual' as const,
          externalId: null,
          position: String(templateTask.position),
        })
        .returning()

      if (!taskRow) continue

      // Insert tags as task_labels rows
      const tags = templateTask.tags ?? []
      if (tags.length > 0) {
        await tx
          .insert(taskLabels)
          .values(tags.map((label) => ({ taskId: taskRow.id, label })))
          .onConflictDoNothing()
      }

      tasksCreated++
    }

    // 5. Increment usage count on the template
    await tx
      .update(projectTemplates)
      .set({ usageCount: sql`${projectTemplates.usageCount} + 1`, updatedAt: new Date() })
      .where(eq(projectTemplates.id, input.template_id))

    return { project_id: projectRow.id, tasks_created: tasksCreated }
  })
}

// ── saveProjectAsTemplate ─────────────────────────────────────────────────────
// Reads tasks from an existing project, converts absolute dates → relative days,
// and inserts a new template + template tasks.

export const saveAsTemplateSchema = z.object({
  name: z.string().min(1).max(200),
  description: z.string().max(2000).optional(),
  category: z.enum(['design', 'development', 'marketing', 'consulting', 'other']).optional(),
})

export type SaveAsTemplateInput = z.infer<typeof saveAsTemplateSchema>

export async function saveProjectAsTemplate(
  db: Db,
  tenantId: string,
  projectId: string,
  input: SaveAsTemplateInput,
  createdBy: string,
): Promise<TemplateObject> {
  // Fetch project to get start_date + billing_type
  const [projectRow] = await db
    .select()
    .from(projects)
    .where(and(eq(projects.id, projectId), eq(projects.tenantId, tenantId)))
    .limit(1)
  if (!projectRow) throw new Error('Project not found')

  const projectStartMs = projectRow.startDate ? new Date(projectRow.startDate).getTime() : null

  // Fetch tasks for this project (ordered by position)
  const taskRows = await db
    .select()
    .from(tasks)
    .where(and(eq(tasks.projectId, projectId), eq(tasks.tenantId, tenantId)))
    .orderBy(asc(tasks.position))

  return db.transaction(async (tx) => {
    const templateValues: NewProjectTemplate = {
      tenantId,
      name: input.name,
      description: input.description ?? null,
      category: input.category ?? null,
      estimatedDays: null,
      billingType: projectRow.billingType,
      thumbnailEmoji: '📋',
      isPublic: false,
      createdBy,
    }
    const [templateRow] = await tx.insert(projectTemplates).values(templateValues).returning()
    if (!templateRow) throw new Error('Template not found after insert')

    for (let i = 0; i < taskRows.length; i++) {
      const t = taskRows[i]!
      const relativeStartDays =
        projectStartMs !== null && t.startDate !== null
          ? Math.max(0, Math.round((new Date(t.startDate).getTime() - projectStartMs) / 86_400_000))
          : null
      const relativeDueDays =
        projectStartMs !== null && t.dueDate !== null
          ? Math.max(0, Math.round((new Date(t.dueDate).getTime() - projectStartMs) / 86_400_000))
          : null

      const [extraRow] = await tx.execute(
        sql`SELECT checklist_items, phase, is_milestone FROM tasks WHERE id = ${t.id}`,
      ) as unknown as [{ checklist_items: unknown; phase: string | null; is_milestone: boolean } | undefined]
      const labelRows = await tx
        .select({ label: taskLabels.label })
        .from(taskLabels)
        .where(eq(taskLabels.taskId, t.id))

      const ttValues: NewProjectTemplateTask = {
        templateId: templateRow.id,
        title: t.title,
        description: t.description ? String(t.description) : null,
        phase: extraRow?.phase ?? null,
        position: i,
        relativeStartDays,
        relativeDueDays,
        isMilestone: extraRow?.is_milestone ?? false,
        checklistItems: extraRow?.checklist_items ?? [],
        estimatedHours:
          t.estimatedHours !== null && t.estimatedHours !== undefined
            ? String(t.estimatedHours)
            : null,
        assignedRole: null,
        tags: labelRows.map((row) => row.label),
      }
      await tx.insert(projectTemplateTasks).values(ttValues)
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId: createdBy,
      actorType: 'user',
      entityType: 'project_template',
      entityId: templateRow.id,
      action: 'project_template.created',
    })

    return serializeTemplate(templateRow)
  })
}