import type { SupabaseClient } from '@supabase/supabase-js'

export type InvoiceStatus = 'open' | 'sent' | 'paid'

export type Invoice = {
  id: string
  org_id: string
  name: string
  status: InvoiceStatus
  operator_id: string | null
  client_id: string | null
  manual_total_usd: number | null
  notes: string | null
  created_by: string | null
  created_at: string
  /** User-facing accounting date (YYYY-MM-DD). NULL on rows created
   * before this column existed; defaults to the last day of the
   * previous month for newly-created invoices. */
  invoice_date: string | null
  sent_at: string | null
  paid_at: string | null
}

export type InvoiceWithScope = Invoice & {
  operator_name: string | null
  client_name: string | null
}

type RawInvoice = {
  id: string
  org_id: string
  name: string
  status: InvoiceStatus
  operator_id: string | null
  client_id: string | null
  manual_total_usd: number | string | null
  notes: string | null
  created_by: string | null
  created_at: string
  invoice_date: string | null
  sent_at: string | null
  paid_at: string | null
  operator?: { name: string } | null
  client?: { name: string } | null
}

function shape(row: RawInvoice): InvoiceWithScope {
  return {
    id: row.id,
    org_id: row.org_id,
    name: row.name,
    status: row.status,
    operator_id: row.operator_id,
    client_id: row.client_id,
    manual_total_usd:
      row.manual_total_usd == null ? null : Number(row.manual_total_usd),
    notes: row.notes,
    created_by: row.created_by,
    created_at: row.created_at,
    invoice_date: row.invoice_date,
    sent_at: row.sent_at,
    paid_at: row.paid_at,
    operator_name: row.operator?.name ?? null,
    client_name: row.client?.name ?? null,
  }
}

/**
 * Default invoice_date for a newly-created invoice: the last day of
 * the previous month. Lets the user create the April 30 invoice
 * anytime in May without rewriting the date.
 */
export function defaultInvoiceDate(today: Date = new Date()): string {
  // new Date(year, monthIndex, 0) → last day of previous month.
  const d = new Date(today.getFullYear(), today.getMonth(), 0)
  const y = d.getFullYear()
  const m = (d.getMonth() + 1).toString().padStart(2, '0')
  const dd = d.getDate().toString().padStart(2, '0')
  return `${y}-${m}-${dd}`
}

/**
 * All invoices visible to the caller (RLS gates org membership).
 * Includes the joined operator + client name so the list view can show
 * the scope chip without an extra round-trip.
 */
export async function listInvoices(
  supabase: SupabaseClient,
  orgId: string,
): Promise<InvoiceWithScope[]> {
  const { data } = await supabase
    .from('invoices')
    .select(
      'id, org_id, name, status, operator_id, client_id, manual_total_usd, notes, created_by, created_at, invoice_date, sent_at, paid_at, operator:operators(name), client:clients(name)',
    )
    .eq('org_id', orgId)
    .order('created_at', { ascending: false })
    .returns<RawInvoice[]>()
  return (data ?? []).map(shape)
}

export async function getInvoice(
  supabase: SupabaseClient,
  orgId: string,
  invoiceId: string,
): Promise<InvoiceWithScope | null> {
  const { data } = await supabase
    .from('invoices')
    .select(
      'id, org_id, name, status, operator_id, client_id, manual_total_usd, notes, created_by, created_at, invoice_date, sent_at, paid_at, operator:operators(name), client:clients(name)',
    )
    .eq('org_id', orgId)
    .eq('id', invoiceId)
    .maybeSingle<RawInvoice>()
  return data ? shape(data) : null
}

/** Aggregate totals across all entries attached to an invoice. */
export type InvoiceTotals = {
  row_count: number
  source_seconds: number
  cost_usd: number
  billout_amount_usd: number
}

export async function getInvoiceTotals(
  supabase: SupabaseClient,
  orgId: string,
  invoiceId: string,
): Promise<InvoiceTotals> {
  const { data } = await supabase
    .from('time_entries')
    .select('duration_seconds, billout_cost_usd, billout_amount_usd')
    .eq('org_id', orgId)
    .eq('invoice_id', invoiceId)
    .returns<
      {
        duration_seconds: number | null
        billout_cost_usd: number | string | null
        billout_amount_usd: number | string | null
      }[]
    >()
  const rows = data ?? []
  return {
    row_count: rows.length,
    source_seconds: rows.reduce((s, r) => s + (r.duration_seconds ?? 0), 0),
    cost_usd: rows.reduce((s, r) => s + Number(r.billout_cost_usd ?? 0), 0),
    billout_amount_usd: rows.reduce(
      (s, r) => s + Number(r.billout_amount_usd ?? 0),
      0,
    ),
  }
}

/**
 * Per (client, project) breakdown for the invoice detail page. One row
 * per distinct (client, project) tuple under the invoice. Sums respect
 * RLS — anything the caller can't see is excluded.
 */
export type InvoiceProjectRow = {
  client: string | null
  project: string | null
  row_count: number
  source_seconds: number
  cost_usd: number
  billout_amount_usd: number
}

export async function getInvoiceProjectBreakdown(
  supabase: SupabaseClient,
  orgId: string,
  invoiceId: string,
): Promise<InvoiceProjectRow[]> {
  const { data } = await supabase
    .from('time_entries')
    .select(
      'client, project, duration_seconds, billout_cost_usd, billout_amount_usd',
    )
    .eq('org_id', orgId)
    .eq('invoice_id', invoiceId)
    .returns<
      {
        client: string | null
        project: string | null
        duration_seconds: number | null
        billout_cost_usd: number | string | null
        billout_amount_usd: number | string | null
      }[]
    >()
  const byKey = new Map<string, InvoiceProjectRow>()
  for (const r of data ?? []) {
    const key = `${r.client ?? ''}::${r.project ?? ''}`
    const cur = byKey.get(key) ?? {
      client: r.client,
      project: r.project,
      row_count: 0,
      source_seconds: 0,
      cost_usd: 0,
      billout_amount_usd: 0,
    }
    cur.row_count += 1
    cur.source_seconds += r.duration_seconds ?? 0
    cur.cost_usd += Number(r.billout_cost_usd ?? 0)
    cur.billout_amount_usd += Number(r.billout_amount_usd ?? 0)
    byKey.set(key, cur)
  }
  return Array.from(byKey.values()).sort((a, b) => {
    const ca = (a.client ?? '').toLowerCase()
    const cb = (b.client ?? '').toLowerCase()
    if (ca !== cb) return ca.localeCompare(cb)
    return (a.project ?? '').localeCompare(b.project ?? '')
  })
}

/**
 * Render the per (client × project) breakdown as a tab-separated
 * paste block for the legacy PlusROI Google Sheet. One line per row.
 *
 * Column layout (per user direction 2026-05-25):
 *   1. {client} : {project}    — colon with spaces, matches the
 *                                 Toggl export project format (ADR #029).
 *   2. Bowden Works Team       — fixed vendor string in the destination.
 *   3. {amount}                 — `XX.XX`, no currency symbol.
 *   4. {invoice_date}           — YYYY-MM-DD.
 *   5. (empty)                  — intentional; destination column is blank.
 *   6. Bowden Works             — second fixed string (the operator).
 *   7. Labour                   — fixed category string.
 *
 * Returns the lines joined with `\n`. Empty string if no breakdown rows.
 */
export function formatPasteBlock(
  breakdown: InvoiceProjectRow[],
  invoiceDate: string,
): string {
  return breakdown
    .map((r) => {
      const client = r.client ?? '(no client)'
      const project = r.project ?? '(no project)'
      const amount = r.billout_amount_usd.toFixed(2)
      return [
        `${client} : ${project}`,
        'Bowden Works Team',
        amount,
        invoiceDate,
        '',
        'Bowden Works',
        'Labour',
      ].join('\t')
    })
    .join('\n')
}