/**
 * Reports query helpers — reports-navigation-hub (wave-13, spec 103).
 *
 * Exports:
 *  - listReportShortcuts
 *  - createReportShortcut / createReportShortcutInputSchema
 *  - deleteReportShortcut
 *  - getReportsSummary
 *
 * All helpers are tenant-filtered. Route files MUST NOT import raw Drizzle
 * tables; they import from this module via @zync/db/queries.
 */
import { and, eq, gte, lte, sql, count, sum, desc } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { reportShortcuts } from '../schema/report-shortcuts'
import { invoices } from '../schema/invoices'
import { invoicePayments } from '../schema/invoice-payments'
import { timeEntries } from '../schema/time'
import { expenses } from '../schema/expenses'
import { leads } from '../schema/marketing'
import { proposals } from '../schema/proposals'
import { tenantAuditLog } from '../schema/audit'

// ── Env type for AE access ────────────────────────────────────────────────────

export interface ReportsSummaryEnv {
  CF_ANALYTICS_READ_TOKEN: string
  CF_ACCOUNT_ID: string
}

// ── ReportType constant ───────────────────────────────────────────────────────

export const REPORT_TYPES = [
  'revenue', 'invoices', 'payments', 'time', 'expenses',
  'profitability', 'revenue_forecast', 'leads', 'proposals',
  'ar_aging', 'bad_debt', 'audit', 'api_usage',
  'vat', 'pnl', 'cashflow', 'advance_tax', 'withholding',
  'bituach_leumi', 'uniform_format',
] as const

export type ReportType = typeof REPORT_TYPES[number]

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

export const createReportShortcutInputSchema = z.object({
  name: z.string().min(1).max(200),
  reportType: z.enum(REPORT_TYPES),
  params: z.record(z.unknown()),
})
export type CreateReportShortcutInput = z.infer<typeof createReportShortcutInputSchema>

// ── ReportShortcut helpers ────────────────────────────────────────────────────

export async function listReportShortcuts(
  db: Db,
  tenantId: string,
  userId: string,
) {
  return db
    .select()
    .from(reportShortcuts)
    .where(
      and(
        eq(reportShortcuts.tenantId, tenantId),
        eq(reportShortcuts.userId, userId),
      ),
    )
    .orderBy(desc(reportShortcuts.createdAt))
}

export async function createReportShortcut(
  db: Db,
  tenantId: string,
  userId: string,
  input: CreateReportShortcutInput,
) {
  const [row] = await db
    .insert(reportShortcuts)
    .values({
      tenantId,
      userId,
      name: input.name,
      reportType: input.reportType,
      params: input.params,
    })
    .returning()
  return row
}

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

// ── Summary query ─────────────────────────────────────────────────────────────

export interface ReportsSummaryScope {
  scope: 'own' | 'org'
  userId: string
}

export interface ReportsSummary {
  revenue: { total: string | null; currency: string }
  invoices: { sent: number; overdue: number }
  payments: { received: string | null; pending_count: number }
  time: { hours_logged: number | null }
  expenses: { total: string | null; unbilled_count: number }
  profitability: { margin_pct: number | null }
  leads: { new_count: number; converted_count: number }
  proposals: { sent_count: number; pipeline_value: string | null }
  audit: { event_count: number }
  api_usage: { call_count: number }
}

/**
 * Runs ONE aggregate round-trip over the upstream tables plus one AE query.
 *
 * scope = 'own'  (MEMBER): org financial totals are null; time/expenses filter to userId.
 * scope = 'org'  (ADMIN/OWNER): org-wide aggregates.
 */
export async function getReportsSummary(
  db: Db,
  env: ReportsSummaryEnv,
  tenantId: string,
  { scope, userId }: ReportsSummaryScope,
  from: string,
  to: string,
): Promise<ReportsSummary> {
  const fromDate = from
  const toDate = to

  // ── Revenue (invoices total where PAID|PARTIALLY_PAID in window) ──────────
  const revenueRows = scope === 'org'
    ? await db
        .select({ total: sum(invoices.total) })
        .from(invoices)
        .where(
          and(
            eq(invoices.tenantId, tenantId),
            sql`${invoices.status} IN ('PAID','PARTIALLY_PAID')`,
            gte(invoices.issueDate, fromDate),
            lte(invoices.issueDate, toDate),
          ),
        )
    : [{ total: null }]

  // ── Invoices: sent count + overdue count ──────────────────────────────────
  const invoicesSentRows = await db
    .select({ cnt: count() })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.status} IN ('SENT','APPROVED','TAX_ISSUED','PARTIALLY_PAID')`,
        gte(invoices.issueDate, fromDate),
        lte(invoices.issueDate, toDate),
      ),
    )

  const today = new Date().toISOString().slice(0, 10)
  const overdueRows = await db
    .select({ cnt: count() })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.status} IN ('SENT','APPROVED','TAX_ISSUED','PARTIALLY_PAID')`,
        sql`${invoices.dueDate} < ${today}`,
      ),
    )

  // ── Payments received in window ───────────────────────────────────────────
  const paymentsRows = scope === 'org'
    ? await db
        .select({ received: sum(invoicePayments.amount) })
        .from(invoicePayments)
        .where(
          and(
            eq(invoicePayments.tenantId, tenantId),
            gte(invoicePayments.paidAt, new Date(fromDate + 'T00:00:00Z')),
            lte(invoicePayments.paidAt, new Date(toDate + 'T23:59:59Z')),
          ),
        )
    : [{ received: null }]

  const pendingCountRows = await db
    .select({ cnt: count() })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.status} IN ('SENT','APPROVED','TAX_ISSUED','PARTIALLY_PAID')`,
        gte(invoices.issueDate, fromDate),
        lte(invoices.issueDate, toDate),
      ),
    )

  // ── Time: hours logged ───────────────────────────────────────────────────
  const timeFilter = scope === 'own'
    ? and(
        eq(timeEntries.tenantId, tenantId),
        eq(timeEntries.userId, userId),
        gte(timeEntries.startedAt, new Date(fromDate + 'T00:00:00Z')),
        lte(timeEntries.startedAt, new Date(toDate + 'T23:59:59Z')),
      )
    : and(
        eq(timeEntries.tenantId, tenantId),
        gte(timeEntries.startedAt, new Date(fromDate + 'T00:00:00Z')),
        lte(timeEntries.startedAt, new Date(toDate + 'T23:59:59Z')),
      )

  const timeRows = await db
    .select({ totalSeconds: sum(timeEntries.durationSeconds) })
    .from(timeEntries)
    .where(timeFilter)

  // ── Expenses ─────────────────────────────────────────────────────────────
  const expensesFilter = scope === 'own'
    ? and(
        eq(expenses.tenantId, tenantId),
        eq(expenses.createdBy, userId),
        gte(expenses.expenseDate, fromDate),
        lte(expenses.expenseDate, toDate),
      )
    : and(
        eq(expenses.tenantId, tenantId),
        gte(expenses.expenseDate, fromDate),
        lte(expenses.expenseDate, toDate),
      )

  const expensesRows = scope === 'org'
    ? await db
        .select({ total: sum(expenses.invoiceTotal) })
        .from(expenses)
        .where(expensesFilter)
    : [{ total: null }]

  const unbilledExpensesRows = await db
    .select({ cnt: count() })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        sql`${expenses.billedAt} IS NULL`,
        sql`${expenses.status} = 'COMPLETED'`,
        gte(expenses.expenseDate, fromDate),
        lte(expenses.expenseDate, toDate),
      ),
    )

  // ── Profitability (margin_pct): (revenue - expenses) / revenue * 100 ─────
  let marginPct: number | null = null
  if (scope === 'org') {
    const revTotal = parseFloat(revenueRows[0]?.total ?? '0') || 0
    const expTotal = parseFloat(expensesRows[0]?.total ?? '0') || 0
    if (revTotal > 0) {
      marginPct = Math.round(((revTotal - expTotal) / revTotal) * 100 * 100) / 100
    }
  }

  // ── Leads ────────────────────────────────────────────────────────────────
  const newLeadsRows = await db
    .select({ cnt: count() })
    .from(leads)
    .where(
      and(
        eq(leads.tenantId, tenantId),
        gte(leads.createdAt, new Date(fromDate + 'T00:00:00Z')),
        lte(leads.createdAt, new Date(toDate + 'T23:59:59Z')),
      ),
    )

  const convertedLeadsRows = await db
    .select({ cnt: count() })
    .from(leads)
    .where(
      and(
        eq(leads.tenantId, tenantId),
        eq(leads.stage, 'WON'),
        gte(leads.updatedAt, new Date(fromDate + 'T00:00:00Z')),
        lte(leads.updatedAt, new Date(toDate + 'T23:59:59Z')),
      ),
    )

  // ── Proposals ───────────────────────────────────────────────────────────
  const proposalsSentRows = await db
    .select({ cnt: count() })
    .from(proposals)
    .where(
      and(
        eq(proposals.tenantId, tenantId),
        sql`${proposals.status} IN ('sent','viewed','accepted')`,
        gte(proposals.createdAt, new Date(fromDate + 'T00:00:00Z')),
        lte(proposals.createdAt, new Date(toDate + 'T23:59:59Z')),
      ),
    )

  const proposalsPipelineRows = await db
    .select({ pipeline: sum(proposals.totalAmount) })
    .from(proposals)
    .where(
      and(
        eq(proposals.tenantId, tenantId),
        sql`${proposals.status} IN ('sent','viewed')`,
      ),
    )

  // ── Audit log event count ────────────────────────────────────────────────
  const auditRows = await db
    .select({ cnt: count() })
    .from(tenantAuditLog)
    .where(
      and(
        eq(tenantAuditLog.tenantId, tenantId),
        gte(tenantAuditLog.createdAt, new Date(fromDate + 'T00:00:00Z')),
        lte(tenantAuditLog.createdAt, new Date(toDate + 'T23:59:59Z')),
      ),
    )

  // ── API usage count via AE SQL HTTP API ──────────────────────────────────
  let apiCallCount = 0
  try {
    const aeUrl = `https://api.cloudflare.com/client/v4/accounts/${env.CF_ACCOUNT_ID}/analytics_engine/sql`
    const aeQuery = `
      SELECT SUM(_sample_interval * double1) AS total
      FROM api_usage
      WHERE index1 = '${tenantId}'
        AND timestamp >= toDateTime('${fromDate} 00:00:00')
        AND timestamp <= toDateTime('${toDate} 23:59:59')
    `
    const aeRes = await fetch(aeUrl, {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${env.CF_ANALYTICS_READ_TOKEN}`,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({ query: aeQuery }),
    })
    if (aeRes.ok) {
      const aeJson = (await aeRes.json()) as { data: { total: number }[] }
      apiCallCount = Math.round(aeJson.data?.[0]?.total ?? 0)
    }
  } catch {
    // AE unavailable — best-effort, return 0
  }

  // ── Assemble response ────────────────────────────────────────────────────
  const totalSeconds = timeRows[0]?.totalSeconds != null
    ? Math.round(parseFloat(String(timeRows[0].totalSeconds)) / 3600 * 100) / 100
    : null

  return {
    revenue: {
      total: scope === 'org' ? (revenueRows[0]?.total ?? null) : null,
      currency: 'ILS',
    },
    invoices: {
      sent: Number(invoicesSentRows[0]?.cnt ?? 0),
      overdue: Number(overdueRows[0]?.cnt ?? 0),
    },
    payments: {
      received: scope === 'org' ? (paymentsRows[0]?.received ?? null) : null,
      pending_count: Number(pendingCountRows[0]?.cnt ?? 0),
    },
    time: {
      hours_logged: totalSeconds,
    },
    expenses: {
      total: scope === 'org' ? (expensesRows[0]?.total ?? null) : null,
      unbilled_count: Number(unbilledExpensesRows[0]?.cnt ?? 0),
    },
    profitability: { margin_pct: marginPct },
    leads: {
      new_count: Number(newLeadsRows[0]?.cnt ?? 0),
      converted_count: Number(convertedLeadsRows[0]?.cnt ?? 0),
    },
    proposals: {
      sent_count: Number(proposalsSentRows[0]?.cnt ?? 0),
      pipeline_value: proposalsPipelineRows[0]?.pipeline ?? null,
    },
    audit: { event_count: Number(auditRows[0]?.cnt ?? 0) },
    api_usage: { call_count: apiCallCount },
  }
}

// ── Financial report queries (reports-analytics wave B) ───────────────────────

export interface RevenueLedgerRow {
  invoice_id: string
  invoice_number: string
  date: string
  customer_name: string
  customer_tax_id: string | null
  subtotal: string
  vat_amount: string
  total: string
  vat_rate: string | null
  currency: string
}

export interface RevenueLedgerTotals {
  subtotal: string
  vat_amount: string
  total: string
}

export interface RevenueLedgerReport {
  from: string
  to: string
  rows: RevenueLedgerRow[]
  totals: RevenueLedgerTotals
}

export interface InvoiceReportRow {
  invoice_id: string
  invoice_number: string | null
  issue_date: string | null
  due_date: string | null
  customer_name: string
  status: string
  subtotal: string
  vat_amount: string
  total: string
  currency: string
  balance_due: string
  days_overdue: number
}

export interface InvoiceReportSummary {
  count_sent: number
  count_overdue: number
  total_billed: string
  total_outstanding: string
}

export interface InvoiceReportTotals {
  subtotal: string
  vat_amount: string
  total: string
  balance_due: string
}

export interface InvoiceReport {
  from: string
  to: string
  rows: InvoiceReportRow[]
  summary: InvoiceReportSummary
  totals: InvoiceReportTotals
}

export interface PaymentReportRow {
  payment_id: string
  paid_at: string
  amount: string
  currency: string
  source: string
  reference: string | null
  invoice_number: string | null
  invoice_id: string
  customer_name: string
  receipt_id: string | null
  receipt_issued: boolean
}

export interface PaymentReportSummary {
  total_received: string
  payment_count: number
  pending_receipt_count: number
}

export interface PaymentReportTotals {
  amount: string
  by_source: Record<string, string>
}

export interface PaymentReport {
  from: string
  to: string
  rows: PaymentReportRow[]
  summary: PaymentReportSummary
  totals: PaymentReportTotals
}

function sumNumericStrings(values: string[]): string {
  const sum = values.reduce((acc, v) => acc + (parseFloat(v) || 0), 0)
  return sum.toFixed(2)
}

export type InvoiceStatusFilter =
  | 'DRAFT'
  | 'SENT'
  | 'APPROVED'
  | 'REJECTED'
  | 'TAX_ISSUED'
  | 'PAID'
  | 'PARTIALLY_PAID'
  | 'VOID'
  | 'WRITTEN_OFF'
  | 'BAD_DEBT'

export interface RevenueLedgerFilters {
  from: string
  to: string
  customerId?: string
  status?: InvoiceStatusFilter
}

export async function getRevenueLedger(
  db: Db,
  tenantId: string,
  { from, to, customerId, status }: RevenueLedgerFilters,
): Promise<RevenueLedgerReport> {
  const statusFilter = status
    ? sql`AND i.status = ${status}`
    : sql``

  const customerFilter = customerId
    ? sql`AND i.customer_id = ${customerId}::uuid`
    : sql``

  const rows = await db.execute(sql`
    SELECT
      i.id::text                                              AS invoice_id,
      i.invoice_number,
      i.tax_issue_date::text                                  AS date,
      c.name                                                  AS customer_name,
      c.tax_id                                                AS customer_tax_id,
      i.subtotal::text                                        AS subtotal,
      i.vat_amount::text                                      AS vat_amount,
      i.total::text                                           AS total,
      i.vat_rate::text                                        AS vat_rate,
      i.currency
    FROM invoices i
    JOIN customers c ON c.id = i.customer_id
    WHERE i.tenant_id = ${tenantId}
      AND i.invoice_number IS NOT NULL
      ${statusFilter}
      AND i.tax_issue_date BETWEEN ${from}::date AND ${to}::date
      ${customerFilter}
    ORDER BY i.tax_issue_date ASC, i.invoice_number ASC
  `)

  const mapped = (rows as Array<Record<string, unknown>>).map((r) => ({
    invoice_id: String(r.invoice_id),
    invoice_number: String(r.invoice_number ?? ''),
    date: String(r.date ?? ''),
    customer_name: String(r.customer_name ?? ''),
    customer_tax_id: r.customer_tax_id != null ? String(r.customer_tax_id) : null,
    subtotal: String(r.subtotal ?? '0'),
    vat_amount: String(r.vat_amount ?? '0'),
    total: String(r.total ?? '0'),
    vat_rate: r.vat_rate != null ? String(r.vat_rate) : null,
    currency: String(r.currency ?? 'ILS'),
  }))

  return {
    from,
    to,
    rows: mapped,
    totals: {
      subtotal: sumNumericStrings(mapped.map((r) => r.subtotal)),
      vat_amount: sumNumericStrings(mapped.map((r) => r.vat_amount)),
      total: sumNumericStrings(mapped.map((r) => r.total)),
    },
  }
}

export interface InvoiceReportFilters {
  from: string
  to: string
  customerId?: string
  status?: string
  overdue?: boolean
}

export async function getInvoiceReport(
  db: Db,
  tenantId: string,
  { from, to, customerId, status, overdue }: InvoiceReportFilters,
): Promise<InvoiceReport> {
  const today = new Date().toISOString().slice(0, 10)

  const customerFilter = customerId
    ? sql`AND i.customer_id = ${customerId}::uuid`
    : sql``

  const statusFilter = status
    ? sql`AND i.status = ${status}`
    : sql``

  const overdueFilter = overdue
    ? sql`AND i.status NOT IN ('PAID', 'VOID') AND i.due_date < ${today}::date`
    : sql``

  const rows = await db.execute(sql`
    SELECT
      i.id::text                                              AS invoice_id,
      i.invoice_number,
      i.issue_date::text                                      AS issue_date,
      i.due_date::text                                        AS due_date,
      c.name                                                  AS customer_name,
      i.status,
      i.subtotal::text                                        AS subtotal,
      i.vat_amount::text                                      AS vat_amount,
      i.total::text                                           AS total,
      i.currency,
      (i.total - COALESCE(p.paid, 0))::text                   AS balance_due,
      CASE
        WHEN i.status != 'PAID' AND i.due_date < ${today}::date
        THEN (CURRENT_DATE - i.due_date)::int
        ELSE 0
      END                                                     AS days_overdue
    FROM invoices i
    JOIN customers c ON c.id = i.customer_id
    LEFT JOIN (
      SELECT invoice_id, SUM(amount) AS paid
      FROM invoice_payments
      GROUP BY invoice_id
    ) p ON p.invoice_id = i.id
    WHERE i.tenant_id = ${tenantId}
      AND i.issue_date BETWEEN ${from}::date AND ${to}::date
      ${customerFilter}
      ${statusFilter}
      ${overdueFilter}
    ORDER BY i.issue_date DESC, i.invoice_number DESC
  `)

  const mapped = (rows as Array<Record<string, unknown>>).map((r) => ({
    invoice_id: String(r.invoice_id),
    invoice_number: r.invoice_number != null ? String(r.invoice_number) : null,
    issue_date: r.issue_date != null ? String(r.issue_date) : null,
    due_date: r.due_date != null ? String(r.due_date) : null,
    customer_name: String(r.customer_name ?? ''),
    status: String(r.status ?? ''),
    subtotal: String(r.subtotal ?? '0'),
    vat_amount: String(r.vat_amount ?? '0'),
    total: String(r.total ?? '0'),
    currency: String(r.currency ?? 'ILS'),
    balance_due: String(r.balance_due ?? '0'),
    days_overdue: Number(r.days_overdue ?? 0),
  }))

  const countSent = mapped.filter((r) =>
    ['SENT', 'APPROVED', 'TAX_ISSUED', 'PARTIALLY_PAID', 'PAID'].includes(r.status),
  ).length

  const countOverdue = mapped.filter(
    (r) => r.status !== 'PAID' && r.status !== 'VOID' && (r.days_overdue ?? 0) > 0,
  ).length

  return {
    from,
    to,
    rows: mapped,
    summary: {
      count_sent: countSent,
      count_overdue: countOverdue,
      total_billed: sumNumericStrings(mapped.map((r) => r.total)),
      total_outstanding: sumNumericStrings(mapped.map((r) => r.balance_due)),
    },
    totals: {
      subtotal: sumNumericStrings(mapped.map((r) => r.subtotal)),
      vat_amount: sumNumericStrings(mapped.map((r) => r.vat_amount)),
      total: sumNumericStrings(mapped.map((r) => r.total)),
      balance_due: sumNumericStrings(mapped.map((r) => r.balance_due)),
    },
  }
}

export interface PaymentReportFilters {
  from: string
  to: string
  customerId?: string
  source?: string
  receiptIssued?: boolean
}

export async function getPaymentReport(
  db: Db,
  tenantId: string,
  { from, to, customerId, source, receiptIssued }: PaymentReportFilters,
): Promise<PaymentReport> {
  const customerFilter = customerId
    ? sql`AND i.customer_id = ${customerId}::uuid`
    : sql``

  const sourceFilter = source
    ? sql`AND ip.source = ${source}`
    : sql``

  const receiptFilter =
    receiptIssued === true
      ? sql`AND ip.receipt_id IS NOT NULL`
      : receiptIssued === false
        ? sql`AND ip.receipt_id IS NULL`
        : sql``

  const rows = await db.execute(sql`
    SELECT
      ip.id::text                                             AS payment_id,
      ip.paid_at::text                                        AS paid_at,
      ip.amount::text                                         AS amount,
      ip.currency,
      ip.source,
      ip.reference,
      i.invoice_number,
      i.id::text                                              AS invoice_id,
      c.name                                                  AS customer_name,
      ip.receipt_id::text                                     AS receipt_id
    FROM invoice_payments ip
    JOIN invoices i ON i.id = ip.invoice_id
    JOIN customers c ON c.id = i.customer_id
    WHERE ip.tenant_id = ${tenantId}
      AND ip.paid_at >= ${from + 'T00:00:00Z'}::timestamptz
      AND ip.paid_at <= ${to + 'T23:59:59.999Z'}::timestamptz
      ${customerFilter}
      ${sourceFilter}
      ${receiptFilter}
    ORDER BY ip.paid_at DESC
  `)

  const mapped = (rows as Array<Record<string, unknown>>).map((r) => ({
    payment_id: String(r.payment_id),
    paid_at: String(r.paid_at ?? ''),
    amount: String(r.amount ?? '0'),
    currency: String(r.currency ?? 'ILS'),
    source: String(r.source ?? 'manual'),
    reference: r.reference != null ? String(r.reference) : null,
    invoice_number: r.invoice_number != null ? String(r.invoice_number) : null,
    invoice_id: String(r.invoice_id),
    customer_name: String(r.customer_name ?? ''),
    receipt_id: r.receipt_id != null ? String(r.receipt_id) : null,
    receipt_issued: r.receipt_id != null,
  }))

  const bySource: Record<string, string> = {}
  for (const r of mapped) {
    bySource[r.source] = sumNumericStrings([
      bySource[r.source] ?? '0',
      r.amount,
    ])
  }

  return {
    from,
    to,
    rows: mapped,
    summary: {
      total_received: sumNumericStrings(mapped.map((r) => r.amount)),
      payment_count: mapped.length,
      pending_receipt_count: mapped.filter((r) => !r.receipt_issued).length,
    },
    totals: {
      amount: sumNumericStrings(mapped.map((r) => r.amount)),
      by_source: bySource,
    },
  }
}