/**
 * P&L aggregation service tests — financial-statements (wave-13).
 *
 * NOTE: These tests require a live Neon branch DB. They are written to spec
 * and run via `pnpm --filter @zync/db test` with DATABASE_URL set.
 * Seed/teardown is per-test with unique tenant UUIDs for isolation.
 */
import { describe, it, expect, vi } from 'vitest'
import type { ProfitLossReport } from '../../src/reports/profit-loss'

// Mock db.execute for unit-level tests (no live DB required)
function makeDb(rows: Record<string, unknown[]>) {
  return {
    execute: vi.fn().mockImplementation((sqlQuery: unknown) => {
      // Return different rows based on SQL content inspection (simplified mock)
      const q = String(sqlQuery)
      for (const [key, data] of Object.entries(rows)) {
        if (q.includes(key)) {
          return Promise.resolve({ rows: data })
        }
      }
      return Promise.resolve({ rows: [{ gross: '0', credit_notes: '0', bad_debts: '0', expenses_paid: '0', contractor_payouts: '0', received: '0', tax_issued: '0', partially_paid_balance: '0' }] })
    }),
  }
}

describe('getProfitLoss (unit — mocked db)', () => {
  it('returns zero report for empty tenant', async () => {
    const { getProfitLoss } = await import('../../src/reports/profit-loss')
    const db = {
      execute: vi.fn().mockResolvedValue([
        { gross: '0', credit_notes: '0', bad_debts: '0', expenses_paid: '0', contractor_payouts: '0', category: null, total: '0', received: '0', tax_issued: '0', partially_paid_balance: '0' },
      ]),
    }
    const report = await getProfitLoss(db as never, 'tenant-uuid', {
      from: '2024-01-01',
      to: '2024-01-31',
    })
    expect(report.revenue.gross).toBe(0)
    expect(report.net_profit).toBe(0)
    expect(report.gross_margin_pct).toBe(0)
  })

  it('computes gross_margin_pct correctly', () => {
    // gross = 1000, credit_notes = 0, bad_debts = 0 → net = 1000
    // expenses = 400, contractor_payouts = 100
    // gross_profit = 1000 - 400 = 600
    // gross_margin_pct = 600 / 1000 * 100 = 60.0
    const net = 1000
    const grossProfit = 600
    const pct = Math.round((grossProfit / net) * 100 * 10) / 10
    expect(pct).toBe(60)
  })

  it('returns zero gross_margin_pct when net revenue is zero', () => {
    const net = 0
    const grossProfit = 0
    const pct = net === 0 ? 0 : Math.round((grossProfit / net) * 100 * 10) / 10
    expect(pct).toBe(0)
  })

  it('comparison period covers equal duration', () => {
    const from = '2024-01-01'
    const to = '2024-01-31'
    const fromDate = new Date(from)
    const toDate = new Date(to)
    const durationMs = toDate.getTime() - fromDate.getTime()
    const prevTo = new Date(fromDate.getTime() - 24 * 60 * 60 * 1000)
    const prevFrom = new Date(prevTo.getTime() - durationMs)

    expect(prevTo.toISOString().slice(0, 10)).toBe('2023-12-31')
    expect(prevFrom.toISOString().slice(0, 10)).toBe('2023-12-01')
    // Duration should be equal
    expect(prevTo.getTime() - prevFrom.getTime()).toBe(durationMs)
  })

  it('omits comparison when compare is false', async () => {
    const { getProfitLoss } = await import('../../src/reports/profit-loss')
    const db = {
      execute: vi.fn().mockResolvedValue([{ gross: '1000', credit_notes: '0', bad_debts: '0' }]),
    }
    const report = await getProfitLoss(db as never, 'tenant-uuid', {
      from: '2024-01-01',
      to: '2024-01-31',
      compare: false,
    })
    expect(report.comparison).toBeUndefined()
  })

  it('uses only the business portion of split expenses in category totals', async () => {
    const { getProfitLoss } = await import('../../src/reports/profit-loss')
    const db = {
      execute: vi.fn().mockImplementation((query: unknown) => {
        const sqlText = `${String(query)} ${JSON.stringify(query)}`
        if (sqlText.includes('FROM expenses') && sqlText.includes('GROUP BY')) {
          return Promise.resolve([
            {
              category: 'Travel',
              total: sqlText.includes('business_percent') ? '70' : '100',
            },
          ])
        }
        if (sqlText.includes('contractor_payouts')) {
          return Promise.resolve([{ contractor_payouts: '0' }])
        }
        if (sqlText.includes('credit_notes')) {
          return Promise.resolve([{ credit_notes: '0' }])
        }
        if (sqlText.includes('bad_debts')) {
          return Promise.resolve([{ bad_debts: '0' }])
        }
        if (sqlText.includes('gross')) {
          return Promise.resolve([{ gross: '0' }])
        }
        return Promise.resolve([{ gross: '0' }])
      }),
    }

    const report = await getProfitLoss(db as never, 'tenant-uuid', {
      from: '2024-01-01',
      to: '2024-01-31',
    })

    expect(report.expenses.total).toBe(70)
    expect(report.expenses.by_category['Travel']).toBe(70)
  })
})

describe('ProfitLossReport shape', () => {
  it('has correct structure', () => {
    const report: ProfitLossReport = {
      period: { from: '2024-01-01', to: '2024-01-31' },
      revenue: { gross: 1000, credit_notes: 50, bad_debts: 0, net: 950 },
      expenses: { total: 300, by_category: { Software: 200, Travel: 100 } },
      contractor_payouts: 100,
      gross_profit: 650,
      net_profit: 550,
      gross_margin_pct: 68.4,
    }
    expect(report.revenue.net).toBe(950)
    expect(report.expenses.by_category['Software']).toBe(200)
  })
})