/**
 * Customer portal query helpers — tenant-portals (wave-9 leaf 5 + wave 9d).
 *
 * All helpers are tenant-scoped and customer-scoped.
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 */
import { and, eq, inArray, isNull, desc, sql, count } from 'drizzle-orm'
import { z } from 'zod'
import type { Db, DbTx } from '../client'
import { invoices } from '../schema/invoices'
import { projects } from '../schema/projects'
import { tickets, ticketMessages } from '../schema/support'
import { customerContacts, customerPortalUsers } from '../schema/customers'
import { tenants } from '../schema/tenants'
import { kbArticles, kbSpaces } from '../schema/kb'
import { tasks, taskStatuses } from '../schema/tasks'
import { proposals } from '../schema/proposals'
import { users } from '../schema/users'
import { auditLog } from './_audit-forward'
import { createTicketMessage } from './support'
import type { UserId } from '@zync/types'
import { resetUserPassword } from './auth-writes'
import { listMilestones } from './project-milestones'

// ── Legacy Zod (wave-9 leaf 5 interim — superseded by portalTickets schemas) ──

export const createPortalTicketSchema = z.object({
  title: z.string().min(1).max(200),
  description: z.string().min(1).max(10000),
  priority: z.enum(['low', 'medium', 'high']).optional(),
})

export type CreatePortalTicketInput = z.infer<typeof createPortalTicketSchema>

// ── Portal-visible invoice statuses (tp-006 whitelist) ─────────────────────────

export const PORTAL_INVOICE_STATUSES = ['TAX_ISSUED', 'PAID', 'PARTIALLY_PAID'] as const
export type PortalInvoiceStatus = (typeof PORTAL_INVOICE_STATUSES)[number]

// ── Public interfaces ─────────────────────────────────────────────────────────

export interface PortalInvoice {
  id: string
  invoiceNumber: string | null
  proformaNumber: string | null
  status: string
  currency: string
  subtotal: string
  vatAmount: string
  total: string
  amountPaid: string
  issueDate: string | null
  dueDate: string | null
  createdAt: string
}

export interface PortalProject {
  id: string
  name: string
  description: string | null
  status: string
  billingType: string
  currency: string
  startDate: string | null
  endDate: string | null
  createdAt: string
}

export interface PortalProjectListItem {
  id: string
  name: string
  type: string
  status: string
  startDate: string | null
  endDate: string | null
  description: string | null
  progressPct: number | null
}

export interface PortalProjectDetail extends PortalProjectListItem {
  milestones: Awaited<ReturnType<typeof listMilestones>>
}

export interface PortalDashboard {
  outstandingBalance: { amount: string; currency: string }
  activeProjects: number
  openTickets: number
  unreadKbArticles: number
}

export interface PortalTicketListItem {
  id: string
  subject: string
  status: string
  createdAt: string
  lastMessageAt: string | null
}

export interface PortalTicket {
  id: string
  title: string
  description: string
  status: string
  priority: string
  source: string
  createdAt: string
  updatedAt: string
}

export interface PortalContact {
  id: string
  customerId: string
  tenantId: string
  email: string
}

export interface PortalProposalListItem {
  publicToken: string | null
  status: string
  customerId: string | null
  name: string | null
  sentAt: string | null
  expiresAt: string | null
}

export interface PortalProfileRow {
  portalUserId: string
  contactId: string
  userId: string
  name: string
  email: string
  locale: string | null
  passwordHash: string
}

// ── Contact lookup ────────────────────────────────────────────────────────────

export async function findCustomerContactByEmail(
  db: Db,
  tenantId: string,
  email: string,
): Promise<PortalContact | null> {
  const [row] = await db
    .select({
      id: customerContacts.id,
      customerId: customerContacts.customerId,
      tenantId: customerContacts.tenantId,
      email: customerContacts.email,
    })
    .from(customerContacts)
    .where(and(eq(customerContacts.tenantId, tenantId), eq(customerContacts.email, email)))
    .limit(1)

  return row ?? null
}

// ── Dashboard ─────────────────────────────────────────────────────────────────

export async function getPortalDashboard(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<PortalDashboard> {
  const [balanceRows, tenantRows] = await Promise.all([
    db
      .select({
        amount: sql<string>`COALESCE(SUM(${invoices.total}::numeric - ${invoices.amountPaid}::numeric), 0)::text`,
      })
      .from(invoices)
      .where(
        and(
          eq(invoices.tenantId, tenantId),
          eq(invoices.customerId, customerId),
          inArray(invoices.status, ['TAX_ISSUED', 'PARTIALLY_PAID']),
          sql`${invoices.total}::numeric > ${invoices.amountPaid}::numeric`,
          eq(invoices.isTemplate, false),
        ),
      ),
    db
      .select({ defaultCurrency: tenants.defaultCurrency })
      .from(tenants)
      .where(eq(tenants.id, tenantId))
      .limit(1),
  ])

  const balanceRow = balanceRows[0]
  const tenantRow = tenantRows[0]

  const [activeProjectsRow] = await db
    .select({ count: count() })
    .from(projects)
    .where(
      and(
        eq(projects.tenantId, tenantId),
        eq(projects.customerId, customerId),
        eq(projects.status, 'active'),
      ),
    )

  const [openTicketsRow] = await db
    .select({ count: count() })
    .from(tickets)
    .where(
      and(
        eq(tickets.tenantId, tenantId),
        eq(tickets.customerId, customerId),
        isNull(tickets.deletedAt),
        inArray(tickets.status, ['open', 'in_progress', 'pending_customer']),
      ),
    )

  const [kbRow] = await db
    .select({ count: count() })
    .from(kbArticles)
    .innerJoin(kbSpaces, eq(kbArticles.spaceId, kbSpaces.id))
    .where(
      and(
        eq(kbArticles.tenantId, tenantId),
        eq(kbArticles.status, 'PUBLISHED'),
        sql`(
          (${kbSpaces.type} = 'vault' AND ${kbSpaces.customerId} = ${customerId})
          OR ${kbSpaces.isPublic} = true
        )`,
      ),
    )

  return {
    outstandingBalance: {
      amount: balanceRow?.amount ?? '0',
      currency: tenantRow?.defaultCurrency ?? 'ILS',
    },
    activeProjects: activeProjectsRow?.count ?? 0,
    openTickets: openTicketsRow?.count ?? 0,
    unreadKbArticles: kbRow?.count ?? 0,
  }
}

// ── Invoices ──────────────────────────────────────────────────────────────────

export async function getPortalInvoices(
  db: Db,
  tenantId: string,
  customerId: string,
  statusFilter?: PortalInvoiceStatus,
): Promise<PortalInvoice[]> {
  const statuses = statusFilter ? [statusFilter] : [...PORTAL_INVOICE_STATUSES]

  const rows = await db
    .select({
      id: invoices.id,
      invoiceNumber: invoices.invoiceNumber,
      proformaNumber: invoices.proformaNumber,
      status: invoices.status,
      currency: invoices.currency,
      subtotal: invoices.subtotal,
      vatAmount: invoices.vatAmount,
      total: invoices.total,
      amountPaid: invoices.amountPaid,
      issueDate: invoices.issueDate,
      dueDate: invoices.dueDate,
      createdAt: invoices.createdAt,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        eq(invoices.customerId, customerId),
        inArray(invoices.status, statuses),
        eq(invoices.isTemplate, false),
      ),
    )
    .orderBy(desc(invoices.createdAt))
    .limit(100)

  return rows.map((r) => ({
    id: r.id,
    invoiceNumber: r.invoiceNumber,
    proformaNumber: r.proformaNumber,
    status: r.status,
    currency: r.currency,
    subtotal: r.subtotal,
    vatAmount: r.vatAmount ?? '0',
    total: r.total,
    amountPaid: r.amountPaid,
    issueDate: r.issueDate,
    dueDate: r.dueDate,
    createdAt: r.createdAt.toISOString(),
  }))
}

// ── Projects ──────────────────────────────────────────────────────────────────

async function projectProgressPct(
  db: Db,
  tenantId: string,
  projectId: string,
): Promise<number | null> {
  const [row] = await db
    .select({
      total: count(tasks.id),
      done: sql<number>`COUNT(*) FILTER (WHERE ${taskStatuses.isTerminal} = true)`,
    })
    .from(tasks)
    .innerJoin(taskStatuses, eq(tasks.statusId, taskStatuses.id))
    .where(and(eq(tasks.tenantId, tenantId), eq(tasks.projectId, projectId)))
    .limit(1)

  if (!row || row.total === 0) return null
  return Math.round((Number(row.done) / row.total) * 100)
}

export async function getPortalProjects(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<PortalProject[]> {
  const rows = await db
    .select({
      id: projects.id,
      name: projects.name,
      description: projects.description,
      status: projects.status,
      billingType: projects.billingType,
      currency: projects.currency,
      startDate: projects.startDate,
      endDate: projects.endDate,
      createdAt: projects.createdAt,
    })
    .from(projects)
    .where(and(eq(projects.tenantId, tenantId), eq(projects.customerId, customerId)))
    .orderBy(desc(projects.createdAt))
    .limit(100)

  return rows.map((r) => ({
    id: r.id,
    name: r.name,
    description: r.description ?? null,
    status: r.status,
    billingType: r.billingType,
    currency: r.currency,
    startDate: r.startDate ?? null,
    endDate: r.endDate ?? null,
    createdAt: r.createdAt.toISOString(),
  }))
}

export async function listPortalProjectsDetailed(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<PortalProjectListItem[]> {
  const rows = await getPortalProjects(db, tenantId, customerId)
  const result: PortalProjectListItem[] = []
  for (const row of rows) {
    const progressPct = await projectProgressPct(db, tenantId, row.id)
    result.push({
      id: row.id,
      name: row.name,
      type: row.billingType,
      status: row.status,
      startDate: row.startDate,
      endDate: row.endDate,
      description: row.description,
      progressPct,
    })
  }
  return result
}

export async function getPortalProjectDetail(
  db: Db,
  tenantId: string,
  customerId: string,
  projectId: string,
): Promise<PortalProjectDetail | null> {
  const [project] = await listPortalProjectsDetailed(db, tenantId, customerId)
    .then((items) => items.filter((item) => item.id === projectId))
  if (!project) return null
  const milestones = await listMilestones(db, tenantId, projectId)
  return {
    ...project,
    milestones,
  }
}

// ── Tickets ───────────────────────────────────────────────────────────────────

export async function listPortalTicketSummaries(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<PortalTicketListItem[]> {
  const rows = await db
    .select({
      id: tickets.id,
      title: tickets.title,
      status: tickets.status,
      createdAt: tickets.createdAt,
      lastMessageAt: sql<Date | null>`(
        SELECT MAX(${ticketMessages.createdAt})
        FROM ${ticketMessages}
        WHERE ${ticketMessages.ticketId} = ${tickets.id}
          AND ${ticketMessages.tenantId} = ${tenantId}
          AND ${ticketMessages.deletedAt} IS NULL
      )`,
    })
    .from(tickets)
    .where(
      and(
        eq(tickets.tenantId, tenantId),
        eq(tickets.customerId, customerId),
        isNull(tickets.deletedAt),
      ),
    )
    .orderBy(desc(tickets.createdAt))
    .limit(100)

  return rows.map((r) => ({
    id: r.id,
    subject: r.title,
    status: r.status,
    createdAt: r.createdAt.toISOString(),
    lastMessageAt: r.lastMessageAt ? r.lastMessageAt.toISOString() : null,
  }))
}

export async function getPortalTickets(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<PortalTicket[]> {
  const rows = await db
    .select({
      id: tickets.id,
      title: tickets.title,
      description: tickets.description,
      status: tickets.status,
      priority: tickets.priority,
      source: tickets.source,
      createdAt: tickets.createdAt,
      updatedAt: tickets.updatedAt,
    })
    .from(tickets)
    .where(
      and(
        eq(tickets.tenantId, tenantId),
        eq(tickets.customerId, customerId),
        isNull(tickets.deletedAt),
      ),
    )
    .orderBy(desc(tickets.createdAt))
    .limit(100)

  return rows.map((r) => ({
    id: r.id,
    title: r.title,
    description: r.description,
    status: r.status,
    priority: r.priority,
    source: r.source,
    createdAt: r.createdAt.toISOString(),
    updatedAt: r.updatedAt.toISOString(),
  }))
}

export async function getPortalTicketForCustomer(
  db: Db,
  tenantId: string,
  customerId: string,
  ticketId: string,
): Promise<PortalTicket | null> {
  const [row] = await db
    .select({
      id: tickets.id,
      title: tickets.title,
      description: tickets.description,
      status: tickets.status,
      priority: tickets.priority,
      source: tickets.source,
      createdAt: tickets.createdAt,
      updatedAt: tickets.updatedAt,
    })
    .from(tickets)
    .where(
      and(
        eq(tickets.tenantId, tenantId),
        eq(tickets.id, ticketId),
        eq(tickets.customerId, customerId),
        isNull(tickets.deletedAt),
      ),
    )
    .limit(1)

  if (!row) return null
  return {
    id: row.id,
    title: row.title,
    description: row.description,
    status: row.status,
    priority: row.priority,
    source: row.source,
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
  }
}

/** @deprecated wave-9 interim — use createPortalTicketWithMessage */
export async function createPortalTicket(
  db: Db,
  tenantId: string,
  customerId: string,
  data: CreatePortalTicketInput,
): Promise<PortalTicket> {
  return db.transaction(async (tx: DbTx) => {
    const rows = await tx
      .insert(tickets)
      .values({
        tenantId,
        customerId,
        title: data.title,
        description: data.description,
        priority: data.priority ?? 'medium',
        status: 'open',
        source: 'portal',
      })
      .returning()

    const row = rows[0]
    if (!row) throw new Error('Insert did not return a row')

    await tx.insert(auditLog).values({
      tenantId,
      actorType: 'portal_customer',
      actorId: customerId,
      entityType: 'ticket',
      entityId: row.id,
      action: 'ticket.created_via_portal',
      changes: null,
    })

    return {
      id: row.id,
      title: row.title,
      description: row.description,
      status: row.status,
      priority: row.priority,
      source: row.source,
      createdAt: row.createdAt.toISOString(),
      updatedAt: row.updatedAt.toISOString(),
    }
  })
}

export async function createPortalTicketWithMessage(
  db: Db,
  tenantId: string,
  customerId: string,
  input: {
    subject: string
    body: string
    categoryId: string
    authorName?: string | null
  },
): Promise<{ ticket: PortalTicket; messageId: string }> {
  return db.transaction(async (tx: DbTx) => {
    const rows = await tx
      .insert(tickets)
      .values({
        tenantId,
        customerId,
        title: input.subject,
        description: '',
        categoryId: input.categoryId,
        status: 'open',
        source: 'portal',
      })
      .returning()

    const row = rows[0]
    if (!row) throw new Error('Insert did not return a row')

    const message = await createTicketMessage(tx, tenantId, row.id, {
      author_type: 'customer',
      author_name: input.authorName ?? null,
      content: input.body,
      source: 'portal',
    })

    await tx.insert(auditLog).values({
      tenantId,
      actorType: 'portal_customer',
      actorId: customerId,
      entityType: 'ticket',
      entityId: row.id,
      action: 'ticket.created_via_portal',
      changes: null,
    })

    return {
      ticket: {
        id: row.id,
        title: row.title,
        description: row.description,
        status: row.status,
        priority: row.priority,
        source: row.source,
        createdAt: row.createdAt.toISOString(),
        updatedAt: row.updatedAt.toISOString(),
      },
      messageId: message.id,
    }
  })
}

// ── Proposals ─────────────────────────────────────────────────────────────────

export async function listPortalProposals(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<PortalProposalListItem[]> {
  try {
    const rows = await db
      .select({
        publicToken: proposals.publicToken,
        status: proposals.status,
        customerId: proposals.customerId,
        name: proposals.name,
        sentAt: proposals.sentAt,
        expiresAt: proposals.expiresAt,
      })
      .from(proposals)
      .where(
        and(
          eq(proposals.tenantId, tenantId),
          eq(proposals.customerId, customerId),
          sql`lower(${proposals.status}) <> 'draft'`,
        ),
      )
      .orderBy(desc(proposals.createdAt))
      .limit(100)

    return rows.map((r) => ({
      publicToken: r.publicToken,
      status: r.status,
      customerId: r.customerId,
      name: r.name,
      sentAt: r.sentAt?.toISOString() ?? null,
      expiresAt: r.expiresAt?.toISOString() ?? null,
    }))
  } catch {
    return []
  }
}

// ── Profile ───────────────────────────────────────────────────────────────────

export async function getPortalProfile(
  db: Db,
  tenantId: string,
  customerId: string,
  userId: string,
): Promise<PortalProfileRow | null> {
  const [row] = await db
    .select({
      portalUserId: customerPortalUsers.id,
      contactId: customerPortalUsers.contactId,
      userId: customerPortalUsers.userId,
      name: customerContacts.name,
      email: customerContacts.email,
      locale: customerPortalUsers.locale,
      passwordHash: users.passwordHash,
    })
    .from(customerPortalUsers)
    .innerJoin(customerContacts, eq(customerContacts.id, customerPortalUsers.contactId))
    .innerJoin(users, eq(users.id, customerPortalUsers.userId))
    .where(
      and(
        eq(customerPortalUsers.tenantId, tenantId),
        eq(customerPortalUsers.customerId, customerId),
        eq(customerPortalUsers.userId, userId),
        eq(customerPortalUsers.status, 'active'),
      ),
    )
    .limit(1)

  return row ?? null
}

export async function updatePortalProfile(
  db: Db,
  tenantId: string,
  customerId: string,
  userId: string,
  patch: { name?: string; locale?: 'he' | 'en' | null },
): Promise<PortalProfileRow | null> {
  const profile = await getPortalProfile(db, tenantId, customerId, userId)
  if (!profile) return null

  if (patch.name !== undefined) {
    await db
      .update(customerContacts)
      .set({ name: patch.name })
      .where(
        and(
          eq(customerContacts.tenantId, tenantId),
          eq(customerContacts.id, profile.contactId),
        ),
      )
  }

  if (patch.locale !== undefined) {
    await db
      .update(customerPortalUsers)
      .set({ locale: patch.locale })
      .where(
        and(
          eq(customerPortalUsers.tenantId, tenantId),
          eq(customerPortalUsers.id, profile.portalUserId),
        ),
      )
  }

  return getPortalProfile(db, tenantId, customerId, userId)
}

export async function updatePortalUserPassword(
  db: Db,
  userId: string,
  passwordHash: string,
): Promise<void> {
  await resetUserPassword(db, userId as UserId, passwordHash)
}