import { eq, and, count, sql, type Column, lte, inArray } from 'drizzle-orm'
import type { PostgresJsDatabase } from 'drizzle-orm/postgres-js'
import { invoices, invoiceLineItems, paymentApplications } from '../db/schema/accounting.js'
import { transactions, transactionLineItems } from '../db/schema/pos.js'
import { repairTickets, repairLineItems } from '../db/schema/repairs.js'
import { accounts } from '../db/schema/accounts.js'
import { NotFoundError, ValidationError } from '../lib/errors.js'
import { AccountBalanceService } from './account-balance.service.js'
import type { InvoiceCreateInput, PaginationInput } from '@lunarfront/shared/schemas'
import { buildSearchCondition } from '../utils/pagination.js'

function generateInvoiceNumber(): string {
  const date = new Date()
  const dateStr = date.toISOString().slice(0, 10).replace(/-/g, '')
  const seq = String(Math.floor(Math.random() * 9999) + 1).padStart(4, '0')
  return `INV-${dateStr}-${seq}`
}

export const InvoiceService = {
  async create(db: PostgresJsDatabase<any>, input: InvoiceCreateInput, createdBy: string) {
    const invoiceNumber = generateInvoiceNumber()

    // Calculate totals from line items
    let subtotal = 0
    let discountTotal = 0
    let taxTotal = 0

    const lineItemValues = input.lineItems.map((item) => {
      const lineSubtotal = item.qty * item.unitPrice
      const lineDiscount = item.discountAmount ?? 0
      const lineTaxable = lineSubtotal - lineDiscount
      const lineTax = parseFloat((lineTaxable * (item.taxRate ?? 0)).toFixed(2))
      const lineTotal = parseFloat((lineTaxable + lineTax).toFixed(2))

      subtotal += lineSubtotal
      discountTotal += lineDiscount
      taxTotal += lineTax

      return {
        description: item.description,
        qty: item.qty,
        unitPrice: item.unitPrice.toFixed(2),
        discountAmount: lineDiscount.toFixed(2),
        taxRate: (item.taxRate ?? 0).toFixed(4),
        taxAmount: lineTax.toFixed(2),
        lineTotal: lineTotal.toFixed(2),
        accountCodeId: item.accountCodeId ?? null,
      }
    })

    const total = parseFloat((subtotal - discountTotal + taxTotal).toFixed(2))

    const [invoice] = await db.insert(invoices).values({
      invoiceNumber,
      accountId: input.accountId,
      locationId: input.locationId ?? null,
      status: 'draft',
      issueDate: input.issueDate,
      dueDate: input.dueDate,
      subtotal: subtotal.toFixed(2),
      discountTotal: discountTotal.toFixed(2),
      taxTotal: taxTotal.toFixed(2),
      total: total.toFixed(2),
      balance: total.toFixed(2),
      notes: input.notes ?? null,
      createdBy,
    }).returning()

    // Insert line items
    for (const item of lineItemValues) {
      await db.insert(invoiceLineItems).values({
        invoiceId: invoice.id,
        ...item,
      })
    }

    return invoice
  },

  async createFromTransaction(db: PostgresJsDatabase<any>, transactionId: string, createdBy: string) {
    const [txn] = await db.select().from(transactions).where(eq(transactions.id, transactionId)).limit(1)
    if (!txn) throw new NotFoundError('Transaction')
    if (!txn.accountId) throw new ValidationError('Transaction must be linked to an account for invoicing')

    const lineItems = await db.select().from(transactionLineItems).where(eq(transactionLineItems.transactionId, transactionId))

    const invoiceNumber = generateInvoiceNumber()
    const isPaid = txn.paymentMethod !== 'account_charge'
    const today = new Date().toISOString().slice(0, 10)

    const [invoice] = await db.insert(invoices).values({
      invoiceNumber,
      accountId: txn.accountId,
      locationId: txn.locationId ?? null,
      status: isPaid ? 'paid' : 'sent',
      issueDate: today,
      dueDate: today, // Immediate for POS, could add net-30 logic later
      sourceType: 'transaction',
      sourceId: transactionId,
      subtotal: txn.subtotal,
      discountTotal: txn.discountTotal,
      taxTotal: txn.taxTotal,
      total: txn.total,
      amountPaid: isPaid ? txn.total : '0',
      balance: isPaid ? '0' : txn.total,
      createdBy,
    }).returning()

    // Copy line items
    for (const item of lineItems) {
      await db.insert(invoiceLineItems).values({
        invoiceId: invoice.id,
        description: item.description,
        qty: item.qty,
        unitPrice: item.unitPrice,
        discountAmount: item.discountAmount ?? '0',
        taxRate: item.taxRate ?? '0',
        taxAmount: item.taxAmount ?? '0',
        lineTotal: item.lineTotal ?? '0',
        sourceType: 'transaction_line_item',
        sourceId: item.id,
      })
    }

    // If paid, record the payment application
    if (isPaid) {
      await db.insert(paymentApplications).values({
        invoiceId: invoice.id,
        transactionId,
        amount: txn.total,
        appliedBy: createdBy,
      })
    }

    // If account_charge, update AR balance
    if (!isPaid) {
      await AccountBalanceService.adjustBalance(db, txn.accountId, parseFloat(txn.total), 'invoice')
    }

    return invoice
  },

  async createFromRepairTicket(db: PostgresJsDatabase<any>, ticketId: string, createdBy: string) {
    const [ticket] = await db.select().from(repairTickets).where(eq(repairTickets.id, ticketId)).limit(1)
    if (!ticket) throw new NotFoundError('Repair ticket')
    if (!ticket.accountId) throw new ValidationError('Repair ticket must be linked to an account for invoicing')

    const lineItemRows = await db.select().from(repairLineItems)
      .where(and(eq(repairLineItems.repairTicketId, ticketId), sql`${repairLineItems.itemType} != 'consumable'`))

    if (lineItemRows.length === 0) return null // No billable items

    const invoiceNumber = generateInvoiceNumber()
    const today = new Date().toISOString().slice(0, 10)

    let subtotal = 0
    for (const item of lineItemRows) {
      subtotal += parseFloat(item.totalPrice)
    }

    const [invoice] = await db.insert(invoices).values({
      invoiceNumber,
      accountId: ticket.accountId,
      status: 'sent',
      issueDate: today,
      dueDate: today,
      sourceType: 'repair_ticket',
      sourceId: ticketId,
      subtotal: subtotal.toFixed(2),
      total: subtotal.toFixed(2),
      balance: subtotal.toFixed(2),
      createdBy,
    }).returning()

    for (const item of lineItemRows) {
      await db.insert(invoiceLineItems).values({
        invoiceId: invoice.id,
        description: item.description,
        qty: parseInt(item.qty as any) || 1,
        unitPrice: item.unitPrice,
        lineTotal: item.totalPrice,
        sourceType: 'repair_line_item',
        sourceId: item.id,
      })
    }

    // Update AR balance
    await AccountBalanceService.adjustBalance(db, ticket.accountId, subtotal, 'invoice')

    return invoice
  },

  async applyPayment(
    db: PostgresJsDatabase<any>,
    invoiceId: string,
    input: { transactionId?: string; amount: number },
    appliedBy: string,
  ) {
    const [invoice] = await db.select().from(invoices).where(eq(invoices.id, invoiceId)).limit(1)
    if (!invoice) throw new NotFoundError('Invoice')

    if (['void', 'written_off', 'paid'].includes(invoice.status)) {
      throw new ValidationError(`Cannot apply payment to ${invoice.status} invoice`)
    }

    const currentBalance = parseFloat(invoice.balance)
    if (input.amount > currentBalance) {
      throw new ValidationError(`Payment amount $${input.amount} exceeds invoice balance $${currentBalance}`)
    }

    // Record payment application
    const [application] = await db.insert(paymentApplications).values({
      invoiceId,
      transactionId: input.transactionId ?? null,
      amount: input.amount.toFixed(2),
      appliedBy,
    }).returning()

    // Update invoice
    const newAmountPaid = parseFloat(invoice.amountPaid) + input.amount
    const newBalance = parseFloat(invoice.total) - newAmountPaid
    const newStatus = newBalance <= 0 ? 'paid' : 'partial'

    await db.update(invoices).set({
      amountPaid: newAmountPaid.toFixed(2),
      balance: newBalance.toFixed(2),
      status: newStatus,
      updatedAt: new Date(),
    }).where(eq(invoices.id, invoiceId))

    // Update AR balance
    await AccountBalanceService.adjustBalance(db, invoice.accountId, -input.amount, 'payment')

    return application
  },

  async applyPaymentToOldestInvoices(
    db: PostgresJsDatabase<any>,
    accountId: string,
    amount: number,
    transactionId: string,
    appliedBy: string,
  ) {
    // FIFO: apply payment to oldest outstanding invoices
    const outstanding = await db.select().from(invoices)
      .where(and(
        eq(invoices.accountId, accountId),
        inArray(invoices.status, ['sent', 'partial', 'overdue']),
      ))
      .orderBy(invoices.issueDate)

    let remaining = amount
    const applications = []

    for (const inv of outstanding) {
      if (remaining <= 0) break
      const invBalance = parseFloat(inv.balance)
      const applied = Math.min(remaining, invBalance)

      const app = await this.applyPayment(db, inv.id, { transactionId, amount: applied }, appliedBy)
      applications.push(app)
      remaining -= applied
    }

    return { applications, amountApplied: amount - remaining, remaining }
  },

  async void(db: PostgresJsDatabase<any>, invoiceId: string, reason: string, _voidedBy: string) {
    const [invoice] = await db.select().from(invoices).where(eq(invoices.id, invoiceId)).limit(1)
    if (!invoice) throw new NotFoundError('Invoice')

    if (invoice.status === 'void') throw new ValidationError('Invoice is already void')

    const balance = parseFloat(invoice.balance)

    await db.update(invoices).set({
      status: 'void',
      balance: '0',
      notes: `${invoice.notes ? invoice.notes + '\n' : ''}Voided: ${reason}`,
      updatedAt: new Date(),
    }).where(eq(invoices.id, invoiceId))

    // If there was an outstanding balance, adjust AR
    if (balance > 0) {
      await AccountBalanceService.adjustBalance(db, invoice.accountId, -balance, 'void')
    }

    return { ...invoice, status: 'void' as const, balance: '0' }
  },

  async writeOff(db: PostgresJsDatabase<any>, invoiceId: string, reason: string, _writtenOffBy: string) {
    const [invoice] = await db.select().from(invoices).where(eq(invoices.id, invoiceId)).limit(1)
    if (!invoice) throw new NotFoundError('Invoice')

    const balance = parseFloat(invoice.balance)
    if (balance <= 0) throw new ValidationError('No balance to write off')

    await db.update(invoices).set({
      status: 'written_off',
      balance: '0',
      notes: `${invoice.notes ? invoice.notes + '\n' : ''}Written off: ${reason}`,
      updatedAt: new Date(),
    }).where(eq(invoices.id, invoiceId))

    await AccountBalanceService.adjustBalance(db, invoice.accountId, -balance, 'write_off')

    return { ...invoice, status: 'written_off' as const, balance: '0' }
  },

  async send(db: PostgresJsDatabase<any>, invoiceId: string) {
    const [invoice] = await db.select().from(invoices).where(eq(invoices.id, invoiceId)).limit(1)
    if (!invoice) throw new NotFoundError('Invoice')

    if (invoice.status !== 'draft') throw new ValidationError('Only draft invoices can be sent')

    const [updated] = await db.update(invoices).set({ status: 'sent', updatedAt: new Date() })
      .where(eq(invoices.id, invoiceId)).returning()
    return updated
  },

  async getById(db: PostgresJsDatabase<any>, id: string) {
    const [invoice] = await db.select().from(invoices).where(eq(invoices.id, id)).limit(1)
    if (!invoice) return null

    const lineItemRows = await db.select().from(invoiceLineItems).where(eq(invoiceLineItems.invoiceId, id))
    const payments = await db.select().from(paymentApplications).where(eq(paymentApplications.invoiceId, id))

    return { ...invoice, lineItems: lineItemRows, payments }
  },

  async list(db: PostgresJsDatabase<any>, params: PaginationInput, filters?: {
    accountId?: string
    status?: string
    dateFrom?: string
    dateTo?: string
  }) {
    const conditions = []
    if (filters?.accountId) conditions.push(eq(invoices.accountId, filters.accountId))
    if (filters?.status) conditions.push(eq(invoices.status, filters.status as any))
    if (filters?.dateFrom) conditions.push(sql`${invoices.issueDate} >= ${filters.dateFrom}`)
    if (filters?.dateTo) conditions.push(sql`${invoices.issueDate} <= ${filters.dateTo}`)

    const searchCondition = params.q
      ? buildSearchCondition(params.q, [invoices.invoiceNumber])
      : undefined
    if (searchCondition) conditions.push(searchCondition)

    const where = conditions.length > 0 ? and(...conditions) : undefined
    const offset = ((params.page ?? 1) - 1) * (params.limit ?? 25)

    const sortableColumns: Record<string, Column> = {
      invoice_number: invoices.invoiceNumber,
      issue_date: invoices.issueDate,
      due_date: invoices.dueDate,
      total: invoices.total,
      balance: invoices.balance,
      status: invoices.status,
      created_at: invoices.createdAt,
    }

    const sortColumn = sortableColumns[params.sort ?? 'created_at'] ?? invoices.createdAt
    const orderFn = params.order === 'asc' ? sql`${sortColumn} ASC` : sql`${sortColumn} DESC`

    const [data, [{ total }]] = await Promise.all([
      db.select({
        id: invoices.id,
        invoiceNumber: invoices.invoiceNumber,
        accountId: invoices.accountId,
        status: invoices.status,
        issueDate: invoices.issueDate,
        dueDate: invoices.dueDate,
        total: invoices.total,
        amountPaid: invoices.amountPaid,
        balance: invoices.balance,
        sourceType: invoices.sourceType,
        createdAt: invoices.createdAt,
        accountName: accounts.name,
      })
        .from(invoices)
        .leftJoin(accounts, eq(invoices.accountId, accounts.id))
        .where(where)
        .orderBy(orderFn)
        .limit(params.limit ?? 25)
        .offset(offset),
      db.select({ total: count() }).from(invoices).where(where),
    ])

    return {
      data,
      pagination: {
        page: params.page ?? 1,
        limit: params.limit ?? 25,
        total,
        totalPages: Math.ceil(total / (params.limit ?? 25)),
      },
    }
  },

  async listByAccount(db: PostgresJsDatabase<any>, accountId: string, params: PaginationInput) {
    return this.list(db, params, { accountId })
  },

  async markOverdueInvoices(db: PostgresJsDatabase<any>) {
    const today = new Date().toISOString().slice(0, 10)
    await db.update(invoices).set({ status: 'overdue', updatedAt: new Date() })
      .where(and(
        inArray(invoices.status, ['sent', 'partial']),
        lte(invoices.dueDate, today),
      ))
  },

  async getAgingReport(db: PostgresJsDatabase<any>) {
    const today = new Date()
    const d30 = new Date(today); d30.setDate(d30.getDate() - 30)
    const d60 = new Date(today); d60.setDate(d60.getDate() - 60)
    const d90 = new Date(today); d90.setDate(d90.getDate() - 90)

    const outstanding = await db.select({
      id: invoices.id,
      invoiceNumber: invoices.invoiceNumber,
      accountId: invoices.accountId,
      accountName: accounts.name,
      dueDate: invoices.dueDate,
      total: invoices.total,
      balance: invoices.balance,
    })
      .from(invoices)
      .leftJoin(accounts, eq(invoices.accountId, accounts.id))
      .where(and(
        inArray(invoices.status, ['sent', 'partial', 'overdue']),
        sql`${invoices.balance}::numeric > 0`,
      ))

    let current = 0, days30 = 0, days60 = 0, days90plus = 0

    for (const inv of outstanding) {
      const balance = parseFloat(inv.balance!)
      const dueDate = new Date(inv.dueDate!)
      if (dueDate >= today) current += balance
      else if (dueDate >= d30) days30 += balance
      else if (dueDate >= d60) days60 += balance
      else days90plus += balance
    }

    return {
      current: current.toFixed(2),
      days30: days30.toFixed(2),
      days60: days60.toFixed(2),
      days90plus: days90plus.toFixed(2),
      total: (current + days30 + days60 + days90plus).toFixed(2),
      invoiceCount: outstanding.length,
    }
  },

  async exportCSV(db: PostgresJsDatabase<any>, filters?: { dateFrom?: string; dateTo?: string }) {
    const conditions = []
    if (filters?.dateFrom) conditions.push(sql`${invoices.issueDate} >= ${filters.dateFrom}`)
    if (filters?.dateTo) conditions.push(sql`${invoices.issueDate} <= ${filters.dateTo}`)
    const where = conditions.length > 0 ? and(...conditions) : undefined

    const rows = await db.select({
      invoiceNumber: invoices.invoiceNumber,
      accountName: accounts.name,
      issueDate: invoices.issueDate,
      dueDate: invoices.dueDate,
      status: invoices.status,
      subtotal: invoices.subtotal,
      discountTotal: invoices.discountTotal,
      taxTotal: invoices.taxTotal,
      total: invoices.total,
      amountPaid: invoices.amountPaid,
      balance: invoices.balance,
    })
      .from(invoices)
      .leftJoin(accounts, eq(invoices.accountId, accounts.id))
      .where(where)
      .orderBy(invoices.issueDate)

    const headers = ['Invoice Number', 'Customer', 'Issue Date', 'Due Date', 'Status', 'Subtotal', 'Discount', 'Tax', 'Total', 'Paid', 'Balance']
    const csvRows = [
      headers.join(','),
      ...rows.map(r => [
        r.invoiceNumber,
        `"${(r.accountName ?? '').replace(/"/g, '""')}"`,
        r.issueDate,
        r.dueDate,
        r.status,
        r.subtotal,
        r.discountTotal,
        r.taxTotal,
        r.total,
        r.amountPaid,
        r.balance,
      ].join(',')),
    ]

    return csvRows.join('\n')
  },
}
