/**
 * Proposals queries — marketing-catalogs-campaigns (wave-9 leaf 3).
 * Proposal CRUD + status lifecycle (send, accept, reject).
 *
 * All functions take (db, tenantId, ...) — tenant isolation enforced here.
 * No raw Drizzle from routes (no-raw-drizzle-from-routes ESLint rule).
 */
import { eq, and, desc, asc, sql, isNull, or, lte, gte, count } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { proposals, proposalViewEvents } from '../schema/proposals'
import { proposalTemplates } from '../schema/proposal-templates'
import { customers } from '../schema/customers'
import { leadActivities, leads } from '../schema/marketing'
import {
  proposalContentSchema,
  createProposalSchema as createProposalEditorSchemaImport,
  updateProposalSchema as updateProposalEditorSchemaImport,
  computeProposalTotal,
  generateProposalPublicToken,
} from '@zync/types'
import type { ProposalContent } from '@zync/types'
import { auditLog } from './_audit-forward'
import { assertTenantOwnsOrThrow, assertTenantOwnsCustomer, assertTenantOwnsLead } from './tenant-guards'

// ── Line item type ────────────────────────────────────────────────────────────

export interface ProposalLineItem {
  catalogItemId?: string
  name: string
  qty: number
  unitPrice: string
  total: string
}

export function getLeadStageChangeForProposalStatus(
  currentStage: string,
  proposalStatus: 'sent' | 'accepted' | 'rejected',
): { nextStage: 'PROPOSAL' | 'WON' | 'LOST'; activityContent: string } | null {
  if (proposalStatus === 'sent') {
    if (!['NEW', 'CONTACTED', 'QUALIFIED'].includes(currentStage)) return null
    return { nextStage: 'PROPOSAL', activityContent: 'Proposal sent' }
  }
  if (proposalStatus === 'accepted') {
    return { nextStage: 'WON', activityContent: 'Proposal accepted — lead marked Won' }
  }
  return { nextStage: 'LOST', activityContent: 'Proposal rejected — lead marked Lost' }
}

async function syncLeadForProposalStatus(
  db: Db,
  tenantId: string,
  proposal: { id: string; leadId: string | null },
  proposalStatus: 'sent' | 'accepted' | 'rejected',
): Promise<void> {
  if (!proposal.leadId) return

  const [lead] = await db
    .select({ id: leads.id, stage: leads.stage })
    .from(leads)
    .where(and(eq(leads.tenantId, tenantId), eq(leads.id, proposal.leadId)))
    .limit(1)

  if (!lead) return

  const stageChange = getLeadStageChangeForProposalStatus(lead.stage, proposalStatus)
  if (stageChange) {
    await db
      .update(leads)
      .set({ stage: stageChange.nextStage, updatedAt: new Date() })
      .where(and(eq(leads.tenantId, tenantId), eq(leads.id, lead.id)))
  }

  await db.insert(leadActivities).values({
    tenantId,
    leadId: lead.id,
    userId: null,
    type: 'note',
    content: stageChange?.activityContent ?? 'Proposal sent',
    metadata: { proposalId: proposal.id, status: proposalStatus },
  })
}

// ── Zod schemas ───────────────────────────────────────────────────────────────

const lineItemSchema = z.object({
  catalogItemId: z.string().uuid().optional(),
  name: z.string().min(1),
  qty: z.number().positive(),
  unitPrice: z.string().regex(/^\d+(\.\d{1,2})?$/),
  total: z.string().regex(/^\d+(\.\d{1,2})?$/),
})

export const createProposalSchema = z.object({
  customerId: z.string().uuid().optional(),
  leadId: z.string().uuid().optional(),
  title: z.string().min(1).max(500),
  lineItems: z.array(lineItemSchema).optional(),
  subtotal: z.string().regex(/^\d+(\.\d{1,2})?$/).optional(),
  discount: z.string().regex(/^\d+(\.\d{1,2})?$/).optional(),
  total: z.string().regex(/^\d+(\.\d{1,2})?$/).optional(),
  notes: z.string().max(5000).optional(),
  validUntil: z.string().datetime().optional(),
})

export const updateProposalSchema = createProposalSchema.partial()

export type CreateProposalInput = z.infer<typeof createProposalSchema>
export type UpdateProposalInput = z.infer<typeof updateProposalSchema>

// ── Serialization ─────────────────────────────────────────────────────────────

export interface ProposalObject {
  id: string
  tenantId: string
  customerId: string | null
  leadId: string | null
  contractId: string | null
  title: string
  // wave-11 editor fields (optional for wave-9 invoice-bridge compat)
  name: string | null
  /** wave-11 alias for name — canonical display field used by editor/list/detail pages */
  subject: string
  status: string
  /** wave-11: structured JSONB content (ProposalContent) */
  content: unknown | null
  lineItems: ProposalLineItem[]
  subtotal: string | null
  discount: string | null
  total: string | null
  /** wave-11: denormalized total from content line_items sections */
  totalAmount: string | null
  notes: string | null
  validUntil: string | null
  /** wave-11: proposal expiry date (editor uses this instead of validUntil) */
  expiresAt: string | null
  sentAt: string | null
  /** wave-11: opaque public URL token for /p/{token} */
  publicToken: string | null
  /** wave-11: view tracking */
  viewCount: number
  firstViewedAt: string | null
  lastViewedAt: string | null
  acceptedAt: string | null
  acceptedByName: string | null
  rejectedAt: string | null
  rejectedReason: string | null
  /** wave-11: per-proposal locale */
  locale: string
  /** wave-11: staff user who created the proposal */
  createdBy: string | null
  createdAt: string
  updatedAt: string
}

/** @internal — exported for lead-proposal and proposal-contract helpers only */
export function serializeProposalRow(row: typeof proposals.$inferSelect): ProposalObject {
  return serializeProposal(row)
}

function serializeProposal(row: typeof proposals.$inferSelect): ProposalObject {
  return {
    id: row.id,
    tenantId: row.tenantId,
    customerId: row.customerId ?? null,
    leadId: row.leadId ?? null,
    contractId: row.contractId ?? null,
    title: row.title,
    name: row.name ?? null,
    subject: row.name ?? row.title,
    status: row.status,
    content: row.content ?? null,
    lineItems: (row.lineItems as ProposalLineItem[]) ?? [],
    subtotal: row.subtotal ?? null,
    discount: row.discount ?? null,
    total: row.total ?? null,
    totalAmount: row.totalAmount ?? null,
    notes: row.notes ?? null,
    validUntil: row.validUntil ? row.validUntil.toISOString() : null,
    expiresAt: row.expiresAt ? row.expiresAt.toISOString() : null,
    sentAt: row.sentAt ? row.sentAt.toISOString() : null,
    publicToken: row.publicToken ?? null,
    viewCount: row.viewCount,
    firstViewedAt: row.firstViewedAt ? row.firstViewedAt.toISOString() : null,
    lastViewedAt: row.lastViewedAt ? row.lastViewedAt.toISOString() : null,
    acceptedAt: row.acceptedAt ? row.acceptedAt.toISOString() : null,
    acceptedByName: row.acceptedByName ?? null,
    rejectedAt: row.rejectedAt ? row.rejectedAt.toISOString() : null,
    rejectedReason: row.rejectedReason ?? null,
    locale: row.locale ?? 'he',
    createdBy: row.createdBy ?? null,
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
  }
}

// ── Query functions ───────────────────────────────────────────────────────────

export async function listProposals(
  db: Db,
  tenantId: string,
  opts: { status?: string; customerId?: string; expiringSoon?: boolean } = {},
): Promise<ProposalObject[]> {
  const conditions = [eq(proposals.tenantId, tenantId)]
  if (opts.status) conditions.push(eq(proposals.status, opts.status))
  if (opts.customerId) conditions.push(eq(proposals.customerId, opts.customerId))
  if (opts.expiringSoon) {
    // SENT/VIEWED proposals expiring within next 7 days
    const now = new Date()
    const in7Days = new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000)
    conditions.push(
      or(eq(proposals.status, 'sent'), eq(proposals.status, 'viewed'))!,
      gte(proposals.expiresAt, now),
      lte(proposals.expiresAt, in7Days),
    )
  }

  const rows = await db
    .select()
    .from(proposals)
    .where(and(...conditions))
    .orderBy(desc(proposals.createdAt))

  return rows.map(serializeProposal)
}

// ── wave-13: proposals-list paginated query ───────────────────────────────────

export interface ProposalListItem {
  id: string
  title: string
  customer_id: string | null
  customer_name: string | null
  status: string
  total_value: number
  expires_at: string | null
  sent_at: string | null
  created_at: string
  public_token: string
}

export interface ProposalListOpts {
  status?: string
  customer_id?: string
  expires_before?: string
  expires_after?: string
  created_after?: string
  created_before?: string
  sort?: string
  page?: number
  per_page?: number
}

export interface ProposalListPage {
  data: ProposalListItem[]
  page: number
  per_page: number
  total: number
}

export async function listProposalsPaginated(
  db: Db,
  tenantId: string,
  opts: ProposalListOpts = {},
): Promise<ProposalListPage> {
  const page = Math.max(1, opts.page ?? 1)
  const perPage = Math.min(100, Math.max(1, opts.per_page ?? 25))
  const offset = (page - 1) * perPage

  const conditions = [eq(proposals.tenantId, tenantId)]

  if (opts.status) conditions.push(eq(proposals.status, opts.status))
  if (opts.customer_id) conditions.push(eq(proposals.customerId, opts.customer_id))
  if (opts.expires_before) conditions.push(lte(proposals.expiresAt, new Date(opts.expires_before)))
  if (opts.expires_after) conditions.push(gte(proposals.expiresAt, new Date(opts.expires_after)))
  if (opts.created_after) conditions.push(gte(proposals.createdAt, new Date(opts.created_after)))
  if (opts.created_before) conditions.push(lte(proposals.createdAt, new Date(opts.created_before)))

  // Sort mapping: prefix '-' = DESC
  const SORT_COLUMNS: Record<string, typeof proposals.createdAt | typeof proposals.name | typeof proposals.totalAmount | typeof proposals.status | typeof proposals.expiresAt | typeof proposals.sentAt> = {
    title: proposals.name,
    customer_name: proposals.name, // fallback; customer JOIN is handled below
    total_value: proposals.totalAmount,
    status: proposals.status,
    expires_at: proposals.expiresAt,
    sent_at: proposals.sentAt,
    created_at: proposals.createdAt,
  }

  const sortParam = opts.sort ?? '-created_at'
  const sortDir = sortParam.startsWith('-') ? 'desc' : 'asc'
  const sortKey = sortParam.replace(/^-/, '')
  const sortCol = SORT_COLUMNS[sortKey] ?? proposals.createdAt
  const orderExpr = sortDir === 'desc' ? desc(sortCol) : asc(sortCol)

  const whereClause = and(...conditions)

  const [rows, totalRows] = await Promise.all([
    db
      .select({
        id: proposals.id,
        name: proposals.name,
        title: proposals.title,
        customer_id: proposals.customerId,
        customer_name: customers.name,
        status: proposals.status,
        total_amount: proposals.totalAmount,
        expires_at: proposals.expiresAt,
        sent_at: proposals.sentAt,
        created_at: proposals.createdAt,
        public_token: proposals.publicToken,
      })
      .from(proposals)
      .leftJoin(customers, eq(customers.id, proposals.customerId))
      .where(whereClause)
      .orderBy(orderExpr)
      .limit(perPage)
      .offset(offset),
    db
      .select({ total: count() })
      .from(proposals)
      .where(whereClause),
  ])

  const data: ProposalListItem[] = rows.map((row) => ({
    id: row.id,
    title: row.name ?? row.title,
    customer_id: row.customer_id ?? null,
    customer_name: row.customer_name ?? null,
    status: row.status,
    total_value: row.total_amount ? parseFloat(row.total_amount) : 0,
    expires_at: row.expires_at ? row.expires_at.toISOString() : null,
    sent_at: row.sent_at ? row.sent_at.toISOString() : null,
    created_at: row.created_at.toISOString(),
    public_token: row.public_token ?? '',
  }))

  return {
    data,
    page,
    per_page: perPage,
    total: totalRows[0]?.total ?? 0,
  }
}

export async function getProposal(
  db: Db,
  tenantId: string,
  id: string,
): Promise<ProposalObject | null> {
  const [row] = await db
    .select()
    .from(proposals)
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, id)))
  return row ? serializeProposal(row) : null
}

export async function createProposal(
  db: Db,
  tenantId: string,
  input: CreateProposalInput,
): Promise<ProposalObject> {
  assertTenantOwnsOrThrow(
    'customerId',
    await assertTenantOwnsCustomer(db, tenantId, input.customerId),
  )
  assertTenantOwnsOrThrow(
    'leadId',
    await assertTenantOwnsLead(db, tenantId, input.leadId),
  )

  const [row] = await db
    .insert(proposals)
    .values({
      tenantId,
      customerId: input.customerId ?? null,
      leadId: input.leadId ?? null,
      title: input.title,
      status: 'draft',
      lineItems: input.lineItems ?? [],
      subtotal: input.subtotal ?? null,
      discount: input.discount ?? null,
      total: input.total ?? null,
      notes: input.notes ?? null,
      validUntil: input.validUntil ? new Date(input.validUntil) : null,
    })
    .returning()
  if (!row) throw new Error('Proposal not found after insert')
  return serializeProposal(row)
}

export async function updateProposal(
  db: Db,
  tenantId: string,
  id: string,
  input: UpdateProposalInput,
): Promise<ProposalObject> {
  if (input.customerId !== undefined) {
    assertTenantOwnsOrThrow(
      'customerId',
      await assertTenantOwnsCustomer(db, tenantId, input.customerId),
    )
  }
  if (input.leadId !== undefined) {
    assertTenantOwnsOrThrow(
      'leadId',
      await assertTenantOwnsLead(db, tenantId, input.leadId),
    )
  }

  const updates: Partial<typeof proposals.$inferInsert> = { updatedAt: new Date() }
  if (input.customerId !== undefined) updates.customerId = input.customerId ?? null
  if (input.leadId !== undefined) updates.leadId = input.leadId ?? null
  if (input.title !== undefined) updates.title = input.title
  if (input.lineItems !== undefined) updates.lineItems = input.lineItems
  if (input.subtotal !== undefined) updates.subtotal = input.subtotal ?? null
  if (input.discount !== undefined) updates.discount = input.discount ?? null
  if (input.total !== undefined) updates.total = input.total ?? null
  if (input.notes !== undefined) updates.notes = input.notes ?? null
  if (input.validUntil !== undefined) updates.validUntil = input.validUntil ? new Date(input.validUntil) : null

  const [row] = await db
    .update(proposals)
    .set(updates)
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, id)))
    .returning()
  if (!row) throw new Error('Proposal not found')
  return serializeProposal(row)
}

export async function deleteProposal(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  await db
    .delete(proposals)
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, id)))
}

export async function sendProposal(
  db: Db,
  tenantId: string,
  id: string,
): Promise<ProposalObject> {
  const [row] = await db
    .update(proposals)
    .set({ status: 'sent', sentAt: new Date(), updatedAt: new Date() })
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, id)))
    .returning()
  if (!row) throw new Error('Proposal not found')
  await syncLeadForProposalStatus(db, tenantId, { id: row.id, leadId: row.leadId ?? null }, 'sent')
  return serializeProposal(row)
}

export async function acceptProposal(
  db: Db,
  tenantId: string,
  id: string,
): Promise<ProposalObject> {
  const [row] = await db
    .update(proposals)
    .set({ status: 'accepted', updatedAt: new Date() })
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, id)))
    .returning()
  if (!row) throw new Error('Proposal not found')
  await syncLeadForProposalStatus(db, tenantId, { id: row.id, leadId: row.leadId ?? null }, 'accepted')
  return serializeProposal(row)
}

export async function rejectProposal(
  db: Db,
  tenantId: string,
  id: string,
): Promise<ProposalObject> {
  const [row] = await db
    .update(proposals)
    .set({ status: 'rejected', updatedAt: new Date() })
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, id)))
    .returning()
  if (!row) throw new Error('Proposal not found')
  await syncLeadForProposalStatus(db, tenantId, { id: row.id, leadId: row.leadId ?? null }, 'rejected')
  return serializeProposal(row)
}

// ── wave-11: proposal-editor Zod schemas (canonical source: @zync/types) ─────

// Re-export canonical schemas so routes reach them via @zync/db/queries only.
export { proposalContentSchema }
export const createProposalEditorSchema = createProposalEditorSchemaImport
export const updateProposalEditorSchema = updateProposalEditorSchemaImport
export type { ProposalContent, ProposalSection, LineItem } from '@zync/types'

export type CreateProposalEditorInput = z.infer<typeof createProposalEditorSchemaImport>
export type UpdateProposalEditorInput = z.infer<typeof updateProposalEditorSchemaImport>

// ── wave-11: proposal-editor CRUD functions ───────────────────────────────────

/**
 * Editor-aware create: writes name, content JSONB, totalAmount, publicToken, expiresAt, createdBy.
 * title is set to the subject (legacy NOT NULL column requirement).
 */
export async function createProposalDraft(
  db: Db,
  tenantId: string,
  input: CreateProposalEditorInput & { createdBy: string | null },
): Promise<ProposalObject> {
  assertTenantOwnsOrThrow(
    'customer_id',
    await assertTenantOwnsCustomer(db, tenantId, input.customer_id),
  )

  const totalAmount = computeProposalTotal(input.content as ProposalContent)
  const publicToken = generateProposalPublicToken()

  const [row] = await db
    .insert(proposals)
    .values({
      tenantId,
      customerId: input.customer_id,
      title: input.subject,   // legacy NOT NULL satisfied
      name: input.subject,
      content: input.content,
      status: 'draft',
      lineItems: [],
      totalAmount: String(totalAmount),
      expiresAt: input.expires_at ? new Date(input.expires_at) : null,
      publicToken,
      createdBy: input.createdBy ?? undefined,
    })
    .returning()

  if (!row) throw new Error('Proposal not created')
  return serializeProposal(row)
}

/**
 * Editor-aware update: handles name, content, expiresAt, totalAmount.
 * Only allowed on DRAFT proposals (caller must check status before calling).
 */
export async function updateProposalDraft(
  db: Db,
  tenantId: string,
  id: string,
  input: UpdateProposalEditorInput,
): Promise<ProposalObject> {
  const updates: Partial<typeof proposals.$inferInsert> = { updatedAt: new Date() }

  if (input.subject !== undefined) {
    updates.name = input.subject
    updates.title = input.subject
  }
  if (input.content !== undefined) {
    updates.content = input.content
    updates.totalAmount = String(computeProposalTotal(input.content as ProposalContent))
  }
  if (input.expires_at !== undefined) {
    updates.expiresAt = input.expires_at ? new Date(input.expires_at) : null
  }

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

  if (!row) throw new Error('Proposal not found')
  return serializeProposal(row)
}

/**
 * Send an existing DRAFT proposal: set status → 'sent', sentAt = now().
 * Caller verifies status is 'draft'. public_token is already set from create.
 */
export async function sendProposalDraft(
  db: Db,
  tenantId: string,
  id: string,
): Promise<ProposalObject> {
  const [row] = await db
    .update(proposals)
    .set({ status: 'sent', sentAt: new Date(), updatedAt: new Date() })
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, id)))
    .returning()

  if (!row) throw new Error('Proposal not found')
  await syncLeadForProposalStatus(db, tenantId, { id: row.id, leadId: row.leadId ?? null }, 'sent')
  return serializeProposal(row)
}

// ── wave-12: proposal-expiry-deadline extend (reactivate EXPIRED) ────────────

/**
 * Extend / reactivate an EXPIRED proposal:
 *   - status → 'sent', expires_at = newExpiresAt, sentAt = now (if null), updatedAt = now
 *   - Preserves publicToken, viewCount, firstViewedAt/lastViewedAt, acceptedByName, customerEmail
 *
 * Throws 'ProposalNotExpired' if status !== 'expired'.
 */
export async function extendProposal(
  db: Db,
  tenantId: string,
  id: string,
  newExpiresAt: Date,
): Promise<ProposalObject> {
  const [existing] = await db
    .select({ status: proposals.status, sentAt: proposals.sentAt })
    .from(proposals)
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, id)))
    .limit(1)

  if (!existing) throw new Error('Proposal not found')
  if (existing.status !== 'expired') throw new Error('ProposalNotExpired')

  const [row] = await db
    .update(proposals)
    .set({
      status: 'sent',
      expiresAt: newExpiresAt,
      sentAt: existing.sentAt ?? new Date(),
      updatedAt: new Date(),
    })
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, id)))
    .returning()

  if (!row) throw new Error('Proposal not found')
  return serializeProposal(row)
}

// ── wave-11: public proposal view ─────────────────────────────────────────────

export interface ProposalPublicData {
  id: string
  tenantId: string
  title: string
  name: string | null
  status: string
  content: unknown
  locale: string
  expiresAt: string | null
  totalAmount: string | null
  customerId: string | null
  leadId: string | null
  viewCount: number
  sentAt: string | null
  acceptedAt: string | null
  acceptedByName: string | null
  rejectedAt: string | null
}

export async function getProposalByPublicToken(
  db: Db,
  publicToken: string,
): Promise<ProposalPublicData | null> {
  const rows = await db
    .select()
    .from(proposals)
    .where(eq(proposals.publicToken, publicToken))
    .limit(1)

  if (rows.length === 0) return null
  const row = rows[0]!
  return {
    id: row.id,
    tenantId: row.tenantId,
    title: row.title,
    name: row.name ?? null,
    status: row.status,
    content: row.content ?? null,
    locale: row.locale ?? 'he',
    expiresAt: row.expiresAt ? row.expiresAt.toISOString() : null,
    totalAmount: row.totalAmount ?? null,
    customerId: row.customerId ?? null,
    leadId: row.leadId ?? null,
    viewCount: row.viewCount,
    sentAt: row.sentAt ? row.sentAt.toISOString() : null,
    acceptedAt: row.acceptedAt ? row.acceptedAt.toISOString() : null,
    acceptedByName: row.acceptedByName ?? null,
    rejectedAt: row.rejectedAt ? row.rejectedAt.toISOString() : null,
  }
}

export async function recordProposalView(
  db: Db,
  proposalId: string,
  tenantId: string,
  viewData: {
    ip: string | null
    userAgent: string | null
    referrer: string | null
    utmSource: string | null
    utmMedium: string | null
    utmCampaign: string | null
  },
): Promise<{ firstView: boolean }> {
  let firstView = false

  await db.transaction(async (tx) => {
    await tx.insert(proposalViewEvents).values({
      proposalId,
      tenantId,
      ip: viewData.ip,
      userAgent: viewData.userAgent,
      referrer: viewData.referrer,
      utmSource: viewData.utmSource,
      utmMedium: viewData.utmMedium,
      utmCampaign: viewData.utmCampaign,
    })

    const current = await tx
      .select({ status: proposals.status, firstViewedAt: proposals.firstViewedAt })
      .from(proposals)
      .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, proposalId)))
      .limit(1)

    if (current.length === 0) return

    const isFirstView = current[0]!.firstViewedAt === null
    firstView = isFirstView

    const updates: Partial<typeof proposals.$inferInsert> = {
      viewCount: sql`view_count + 1` as unknown as number,
      lastViewedAt: new Date(),
    }
    if (isFirstView) updates.firstViewedAt = new Date()
    if (current[0]!.status === 'sent') updates.status = 'viewed'

    await tx.update(proposals).set(updates).where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, proposalId)))

    await tx.insert(auditLog).values({
      tenantId,
      actorId: null,
      actorType: 'portal_customer',
      entityType: 'proposal',
      entityId: proposalId,
      action: 'proposal.viewed',
    })
  })

  return { firstView }
}

export async function acceptProposalByToken(
  db: Db,
  proposalId: string,
  tenantId: string,
  opts: { acceptedByName: string | null },
): Promise<{ ok: boolean; status: 'accepted' | 'rejected' | 'expired' | 'missing' }> {
  const current = await db
    .select({ status: proposals.status, expiresAt: proposals.expiresAt })
    .from(proposals)
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, proposalId)))
    .limit(1)

  if (current.length === 0) return { ok: false, status: 'missing' }
  if (current[0]!.status === 'accepted') return { ok: false, status: 'accepted' }
  if (current[0]!.status === 'rejected') return { ok: false, status: 'rejected' }
  if (current[0]!.expiresAt && current[0]!.expiresAt < new Date()) return { ok: false, status: 'expired' }

  const now = new Date()
  await db
    .update(proposals)
    .set({
      status: 'accepted',
      acceptedAt: now,
      acceptedByName: opts.acceptedByName,
      rejectedAt: null,
      rejectedReason: null,
      updatedAt: now,
    })
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, proposalId)))
  return { ok: true, status: 'accepted' }
}

export async function rejectProposalByToken(
  db: Db,
  proposalId: string,
  tenantId: string,
  opts: { reason: string | null },
): Promise<{ ok: boolean; status: 'accepted' | 'rejected' | 'expired' | 'missing' }> {
  const current = await db
    .select({ status: proposals.status, expiresAt: proposals.expiresAt })
    .from(proposals)
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, proposalId)))
    .limit(1)

  if (current.length === 0) return { ok: false, status: 'missing' }
  if (current[0]!.status === 'rejected') return { ok: false, status: 'rejected' }
  if (current[0]!.status === 'accepted') return { ok: false, status: 'accepted' }
  if (current[0]!.expiresAt && current[0]!.expiresAt < new Date()) return { ok: false, status: 'expired' }

  const now = new Date()
  await db
    .update(proposals)
    .set({
      status: 'rejected',
      rejectedAt: now,
      rejectedReason: opts.reason,
      updatedAt: now,
    })
    .where(and(eq(proposals.tenantId, tenantId), eq(proposals.id, proposalId)))
  return { ok: true, status: 'rejected' }
}

// ── wave-11: proposal templates ───────────────────────────────────────────────

export interface ProposalTemplateObject {
  id: string
  tenantId: string | null
  name: string
  content: unknown
  createdBy: string | null
  createdAt: string
}

export async function listProposalTemplates(
  db: Db,
  tenantId: string,
): Promise<ProposalTemplateObject[]> {
  const rows = await db
    .select()
    .from(proposalTemplates)
    .where(
      or(isNull(proposalTemplates.tenantId), eq(proposalTemplates.tenantId, tenantId)),
    )
    .orderBy(proposalTemplates.createdAt)

  return rows.map((row) => ({
    id: row.id,
    tenantId: row.tenantId ?? null,
    name: row.name,
    content: row.content,
    createdBy: row.createdBy ?? null,
    createdAt: row.createdAt.toISOString(),
  }))
}

export async function createProposalTemplate(
  db: Db,
  tenantId: string,
  input: { name: string; content: unknown; createdBy: string },
): Promise<ProposalTemplateObject> {
  const rows = await db
    .insert(proposalTemplates)
    .values({ tenantId, name: input.name, content: input.content, createdBy: input.createdBy })
    .returning()

  const row = rows[0]!
  return { id: row.id, tenantId: row.tenantId ?? null, name: row.name, content: row.content, createdBy: row.createdBy ?? null, createdAt: row.createdAt.toISOString() }
}

export async function deleteProposalTemplate(
  db: Db,
  tenantId: string,
  id: string,
): Promise<boolean> {
  const rows = await db
    .delete(proposalTemplates)
    .where(and(eq(proposalTemplates.id, id), eq(proposalTemplates.tenantId, tenantId)))
    .returning({ id: proposalTemplates.id })

  return rows.length > 0
}