import { AwsClient } from 'aws4fetch'
import ExcelJS from 'exceljs'
import { Hono } from 'hono'
import { z } from 'zod'
import { TenantTier } from '@zync/types'
import type { AppEnv } from '../../types'
import { authMiddleware } from '../../middleware/auth'
import { requirePermission, requireTier } from '../../middleware/guards'
import {
  createImportJob,
  deleteImportJob,
  getImportJob,
  listImportJobResults,
  listImportJobs,
  setImportJobProcessing,
  updateImportJobMappings,
} from '@zync/db/queries'

const MAX_IMPORT_BYTES = 10 * 1024 * 1024
const MAX_IMPORT_ROWS = 5_000
const PREVIEW_ROW_LIMIT = 5
const HISTORY_PAGE_SIZE = 20

const IMPORT_TYPES = ['customers', 'invoices', 'products', 'time_entries', 'bulk_action'] as const
const ALLOWED_IMPORT_MIME_TYPES = new Set([
  'text/csv',
  'text/plain',
  'application/csv',
  'application/vnd.ms-excel',
  'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
])

const UploadRequestSchema = z.object({
  filename: z.string().min(1),
  content_type: z.string().min(1),
  size_bytes: z.number().int().positive().max(MAX_IMPORT_BYTES),
})

const CreateImportJobSchema = z.object({
  type: z.enum(IMPORT_TYPES),
  r2_key: z.string().min(1),
  original_filename: z.string().min(1),
})

const MappingSchema = z.record(z.string(), z.string().nullable())

const PreviewSchema = z.object({
  mapping: MappingSchema,
})

const StartSchema = z.object({
  mapping: MappingSchema,
})

const ListImportsQuerySchema = z.object({
  cursor: z.string().uuid().optional(),
  limit: z.coerce.number().int().min(1).max(100).default(HISTORY_PAGE_SIZE),
})

const ListResultsQuerySchema = z.object({
  status: z.enum(['success', 'skipped', 'error']).optional(),
  limit: z.coerce.number().int().min(1).max(500).default(100),
})

const TEMPLATE_CONTENT = {
  customers:
    'name,email,phone,company,address_street,address_city,address_zip,address_country,notes\n' +
    'Rivka Cohen,rivka@example.com,050-1234567,Cohen Ltd,הרצל 1,תל אביב,6120101,IL,לקוח VIP\n',
  invoices:
    'customer_name,customer_email,invoice_number,issue_date,due_date,status,line_description,line_qty,line_unit_price,line_tax_rate\n' +
    'Cohen Ltd,rivka@example.com,INV-001,2026-01-15,2026-02-15,draft,ייעוץ עסקי,2,500,0.18\n' +
    'Cohen Ltd,rivka@example.com,INV-001,,,,תוכנה,1,1200,0.18\n',
  products:
    'name,description,price,currency,unit,tax_rate,sku,active\n' +
    'Web Design,Full website design and build,3500,ILS,item,17,SKU-001,true\n' +
    'Hourly Consulting,Strategy consulting,350,ILS,hour,17,SKU-002,true\n',
  'time-entries':
    'date,start_time,end_time,duration_minutes,project_name,task_name,user_email,description,billable\n' +
    '2026-05-31,09:00,11:30,,Project Alpha,API integration,dev@example.com,Backend work,true\n' +
    '2026-05-31,,,90,Project Beta,,pm@example.com,Planning session,false\n',
} as const satisfies Record<string, string>

type ImportType = (typeof IMPORT_TYPES)[number]
type TemplateType = keyof typeof TEMPLATE_CONTENT
type Mapping = Record<string, string | null>
type ParsedFile = { columns: string[]; rows: string[][]; totalRows: number }
type PreviewRow = {
  row: number
  status: 'valid' | 'warning' | 'error'
  data: Record<string, string>
  message?: string
}

type R2BucketWithPresign = R2Bucket & {
  createPresignedUrl?: (
    request: Request,
    options?: { expiresIn?: number },
  ) => Promise<URL | Request>
}

function sanitizeFilename(filename: string): string {
  return filename
    .toLowerCase()
    .replace(/\s+/g, '-')
    .replace(/[^a-z0-9.-]/g, '')
    .replace(/-+/g, '-')
}

function importR2Key(tenantId: string, filename: string): string {
  return `${tenantId}/imports/${crypto.randomUUID()}-${sanitizeFilename(filename)}`
}

function csvEscape(value: string): string {
  return `"${value.replace(/"/g, '""')}"`
}

function isValidEmail(value: string): boolean {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(value)
}

function parseBoolean(value: string): boolean | null {
  const normalized = value.trim().toLowerCase()
  if (['true', '1'].includes(normalized)) return true
  if (['false', '0'].includes(normalized)) return false
  return null
}

function parseDate(value: string): Date | null {
  if (/^\d{4}-\d{2}-\d{2}$/.test(value)) {
    const date = new Date(`${value}T00:00:00Z`)
    return Number.isNaN(date.getTime()) ? null : date
  }
  const match = /^(\d{2})\/(\d{2})\/(\d{4})$/.exec(value)
  if (!match) return null
  const [, dd, mm, yyyy] = match
  const date = new Date(`${yyyy}-${mm}-${dd}T00:00:00Z`)
  return Number.isNaN(date.getTime()) ? null : date
}

function parseCsv(text: string): string[][] {
  const rows: string[][] = []
  let row: string[] = []
  let cell = ''
  let inQuotes = false

  for (let i = 0; i < text.length; i += 1) {
    const char = text[i]
    const next = text[i + 1]

    if (char === '"') {
      if (inQuotes && next === '"') {
        cell += '"'
        i += 1
      } else {
        inQuotes = !inQuotes
      }
      continue
    }

    if (char === ',' && !inQuotes) {
      row.push(cell)
      cell = ''
      continue
    }

    if ((char === '\n' || char === '\r') && !inQuotes) {
      if (char === '\r' && next === '\n') i += 1
      row.push(cell)
      rows.push(row)
      row = []
      cell = ''
      continue
    }

    cell += char
  }

  if (cell.length > 0 || row.length > 0) {
    row.push(cell)
    rows.push(row)
  }

  return rows
}

function applyMapping(source: string[], columns: string[], mapping: Mapping): Record<string, string> {
  const data: Record<string, string> = {}
  for (let index = 0; index < columns.length; index += 1) {
    const mappedField = mapping[columns[index] ?? '']
    if (!mappedField) continue
    const nextValue = (source[index] ?? '').trim()
    if (!nextValue) continue
    if (mappedField === 'notes' && data[mappedField]) {
      data[mappedField] = `${data[mappedField]}; ${nextValue}`
    } else {
      data[mappedField] = nextValue
    }
  }
  return data
}

function requiredFieldsForType(type: ImportType): string[][] {
  switch (type) {
    case 'customers':
      return [['name']]
    case 'invoices':
      return [['customer_name', 'customer_email']]
    case 'products':
      return [['name'], ['price']]
    case 'time_entries':
      return [['date'], ['user_email']]
    default:
      return []
  }
}

function validateRequiredMapping(type: ImportType, mapping: Mapping): string | null {
  const mappedFields = new Set(Object.values(mapping).filter((value): value is string => Boolean(value)))
  for (const group of requiredFieldsForType(type)) {
    if (!group.some((field) => mappedFields.has(field))) {
      return group.join(' or ')
    }
  }
  return null
}

function validatePreviewRow(
  type: ImportType,
  rowNumber: number,
  data: Record<string, string>,
): PreviewRow {
  switch (type) {
    case 'customers': {
      if (!data.name?.trim()) {
        return { row: rowNumber, status: 'error', data, message: 'Name is required' }
      }
      if (data.email && !isValidEmail(data.email)) {
        return { row: rowNumber, status: 'error', data, message: 'Invalid email format' }
      }
      return { row: rowNumber, status: 'valid', data }
    }
    case 'invoices': {
      if (!data.customer_name?.trim() && !data.customer_email?.trim()) {
        return { row: rowNumber, status: 'error', data, message: 'Customer required' }
      }
      if (data.customer_email && !isValidEmail(data.customer_email)) {
        return { row: rowNumber, status: 'error', data, message: 'Invalid email format' }
      }
      if (data.issue_date && !parseDate(data.issue_date)) {
        return { row: rowNumber, status: 'error', data, message: 'Issue date must be YYYY-MM-DD' }
      }
      if (data.line_unit_price && Number.isNaN(Number(data.line_unit_price))) {
        return { row: rowNumber, status: 'error', data, message: 'Line unit price must be numeric' }
      }
      return { row: rowNumber, status: 'valid', data }
    }
    case 'products': {
      if (!data.name?.trim()) return { row: rowNumber, status: 'error', data, message: 'Name is required' }
      if (!data.price?.trim() || Number.isNaN(Number(data.price)) || Number(data.price) < 0) {
        return { row: rowNumber, status: 'error', data, message: 'Price must be a non-negative number' }
      }
      if (data.tax_rate && (!Number.isFinite(Number(data.tax_rate)) || Number(data.tax_rate) < 0 || Number(data.tax_rate) > 100)) {
        return { row: rowNumber, status: 'error', data, message: 'Tax rate must be between 0 and 100' }
      }
      return { row: rowNumber, status: 'valid', data }
    }
    case 'time_entries': {
      if (!data.date?.trim()) return { row: rowNumber, status: 'error', data, message: 'Date is required' }
      if (!parseDate(data.date)) return { row: rowNumber, status: 'error', data, message: 'Date must be YYYY-MM-DD or DD/MM/YYYY' }
      if (!data.user_email?.trim()) return { row: rowNumber, status: 'error', data, message: 'User email is required' }
      if (!isValidEmail(data.user_email)) return { row: rowNumber, status: 'error', data, message: 'Invalid email format' }
      if (data.billable) {
        const parsed = parseBoolean(data.billable)
        if (parsed === null) return { row: rowNumber, status: 'warning', data, message: 'Invalid billable value will default to true' }
      }
      return { row: rowNumber, status: 'valid', data }
    }
    default:
      return { row: rowNumber, status: 'valid', data }
  }
}

async function readR2Text(object: R2ObjectBody): Promise<string> {
  return object.text()
}

async function parseXlsx(buffer: ArrayBuffer): Promise<ParsedFile> {
  const workbook = new ExcelJS.Workbook()
  await workbook.xlsx.load(buffer)
  const worksheet = workbook.worksheets[0]
  if (!worksheet) throw new Error('PARSE_ERROR')

  const rawRows: string[][] = []
  worksheet.eachRow({ includeEmpty: false }, (row) => {
    const cells = Array.isArray(row.values) ? row.values.slice(1) : []
    const values = cells.map((value: unknown) => (value == null ? '' : String(value)))
    rawRows.push(values)
  })

  const nonEmptyRows = rawRows.filter((row) => row.some((cell) => cell.trim() !== ''))
  const columns = nonEmptyRows[0] ?? []
  const rows = nonEmptyRows.slice(1)
  return { columns, rows, totalRows: rows.length }
}

async function parseImportFile(
  object: R2ObjectBody,
  filename: string,
  mimeType: string | null,
): Promise<ParsedFile> {
  const lowerFilename = filename.toLowerCase()
  const isXlsx = lowerFilename.endsWith('.xlsx')
    || mimeType === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

  if (isXlsx) {
    const buffer = await object.arrayBuffer()
    return parseXlsx(buffer)
  }

  const text = await readR2Text(object)
  const rawRows = parseCsv(text).filter((row) => row.some((cell) => cell.trim() !== ''))
  const columns = rawRows[0] ?? []
  const rows = rawRows.slice(1)
  return { columns, rows, totalRows: rows.length }
}

async function presignUploadUrl(
  env: AppEnv['Bindings'],
  r2Key: string,
  contentType: string,
): Promise<string> {
  const storage = env.STORAGE as R2BucketWithPresign
  if (storage.createPresignedUrl) {
    const signed = await storage.createPresignedUrl(
      new Request(`https://r2.local/${r2Key}`, {
        method: 'PUT',
        headers: { 'content-type': contentType },
      }),
      { expiresIn: 300 },
    )
    return signed instanceof Request ? signed.url : signed.toString()
  }

  const envAny = env as AppEnv['Bindings'] & Record<string, string | undefined>
  const accountId = envAny.CF_ACCOUNT_ID ?? ''
  const accessKeyId = envAny.R2_ACCESS_KEY_ID ?? ''
  const secretAccessKey = envAny.R2_SECRET_ACCESS_KEY ?? ''
  const bucketName = envAny.R2_BUCKET_NAME ?? 'zync-storage'
  if (!accountId || !accessKeyId || !secretAccessKey) {
    throw new Error('R2 presign credentials are missing')
  }

  const aws = new AwsClient({ accessKeyId, secretAccessKey, service: 's3', region: 'auto' })
  const url = new URL(`https://${accountId}.r2.cloudflarestorage.com/${bucketName}/${r2Key}`)
  const presigned = await aws.sign(
    new Request(url.toString(), {
      method: 'PUT',
      headers: { 'content-type': contentType },
    }),
    { aws: { signQuery: true } },
  )
  return presigned.url
}

export const importRouter = new Hono<AppEnv>()

importRouter.use('*', authMiddleware)

importRouter.post('/upload', requirePermission('invoices:write'), requireTier(TenantTier.BUSINESS), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) return c.json({ error: 'Unauthorized' }, 401)

  const body = await c.req.json().catch(() => null)
  const parsed = UploadRequestSchema.safeParse(body)
  if (!parsed.success) return c.json({ error: 'Validation failed', issues: parsed.error.issues }, 400)

  const { filename, content_type, size_bytes } = parsed.data
  if (!ALLOWED_IMPORT_MIME_TYPES.has(content_type)) return c.json({ error: 'Unsupported file type.' }, 415)

  const r2_key = importR2Key(session.tid, filename)
  const upload_url = await presignUploadUrl(c.env, r2_key, content_type)

  return c.json({ upload_url, r2_key, expires_in: 300, size_bytes }, 200)
})

importRouter.post('/', requirePermission('invoices:write'), requireTier(TenantTier.BUSINESS), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) return c.json({ error: 'Unauthorized' }, 401)

  const body = await c.req.json().catch(() => null)
  const parsed = CreateImportJobSchema.safeParse(body)
  if (!parsed.success) return c.json({ error: 'Validation failed', issues: parsed.error.issues }, 400)

  const { type, r2_key, original_filename } = parsed.data
  if (!r2_key.startsWith(`${session.tid}/imports/`)) return c.json({ error: 'Invalid storage key' }, 400)

  const object = await c.env.STORAGE.get(r2_key)
  if (!object) return c.json({ error: 'Uploaded file not found' }, 404)

  const mimeType = object.httpMetadata?.contentType ?? null
  if (!mimeType || !ALLOWED_IMPORT_MIME_TYPES.has(mimeType)) return c.json({ error: 'Unsupported file type.' }, 415)

  try {
    const parsedFile = await parseImportFile(object, original_filename, mimeType)
    if (parsedFile.totalRows > MAX_IMPORT_ROWS) return c.json({ error: 'ROW_LIMIT_EXCEEDED' }, 422)

    const job = await createImportJob(c.get('db'), session.tid, {
      created_by: session.sub,
      type,
      r2_key,
      original_filename,
      mime_type: mimeType,
      file_size_bytes: object.size,
    })

    return c.json({ id: job.id, status: job.status, columns: parsedFile.columns }, 201)
  } catch {
    return c.json({ error: 'PARSE_ERROR' }, 422)
  }
})

importRouter.get('/', requirePermission('invoices:read'), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) return c.json({ error: 'Unauthorized' }, 401)

  const query = ListImportsQuerySchema.safeParse(Object.fromEntries(new URL(c.req.url).searchParams))
  if (!query.success) return c.json({ error: 'Invalid query', issues: query.error.issues }, 400)

  const rows = await listImportJobs(c.get('db'), session.tid, query.data.limit + 1, query.data.cursor)
  const jobs = rows.slice(0, query.data.limit)
  const next_cursor = rows.length > query.data.limit ? rows[query.data.limit]?.id ?? null : null
  return c.json({ jobs, next_cursor })
})

importRouter.get('/templates/:type', requirePermission('invoices:read'), async (c) => {
  const type = c.req.param('type') as TemplateType
  if (!(type in TEMPLATE_CONTENT)) return c.json({ error: 'Not found' }, 404)
  c.header('content-type', 'text/csv; charset=utf-8')
  c.header('content-disposition', `attachment; filename="${type}-template.csv"`)
  c.header('cache-control', 'public, max-age=3600')
  return c.body(TEMPLATE_CONTENT[type])
})

importRouter.get('/:id', requirePermission('invoices:read'), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) return c.json({ error: 'Unauthorized' }, 401)

  const job = await getImportJob(c.get('db'), session.tid, c.req.param('id'))
  if (!job) return c.json({ error: 'Not found' }, 404)
  return c.json(job)
})

importRouter.patch('/:id/mappings', requirePermission('invoices:write'), requireTier(TenantTier.BUSINESS), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) return c.json({ error: 'Unauthorized' }, 401)

  const body = await c.req.json().catch(() => null)
  const parsed = z.object({ column_mapping: MappingSchema }).safeParse(body)
  if (!parsed.success) return c.json({ error: 'Validation failed', issues: parsed.error.issues }, 400)

  const updated = await updateImportJobMappings(c.get('db'), session.tid, c.req.param('id'), parsed.data.column_mapping)
  if (!updated) return c.json({ error: 'Import job not found or not in pending state' }, 404)
  return c.json({ import_job: updated })
})

importRouter.post('/:id/preview', requirePermission('invoices:write'), requireTier(TenantTier.BUSINESS), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) return c.json({ error: 'Unauthorized' }, 401)

  const body = await c.req.json().catch(() => null)
  const parsed = PreviewSchema.safeParse(body)
  if (!parsed.success) return c.json({ error: 'Validation failed', issues: parsed.error.issues }, 400)

  const job = await getImportJob(c.get('db'), session.tid, c.req.param('id'))
  if (!job) return c.json({ error: 'Not found' }, 404)

  const object = await c.env.STORAGE.get(job.r2_key)
  if (!object) return c.json({ error: 'Uploaded file not found' }, 404)

  try {
    const parsedFile = await parseImportFile(object, job.original_filename, job.mime_type)
    const rows = parsedFile.rows
      .slice(0, PREVIEW_ROW_LIMIT)
      .map((source, index) => validatePreviewRow(job.type as ImportType, index + 1, applyMapping(source, parsedFile.columns, parsed.data.mapping)))

    return c.json({ rows, total_rows: parsedFile.totalRows })
  } catch {
    return c.json({ error: 'PARSE_ERROR' }, 422)
  }
})

importRouter.post('/:id/start', requirePermission('invoices:write'), requireTier(TenantTier.BUSINESS), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) return c.json({ error: 'Unauthorized' }, 401)

  const body = await c.req.json().catch(() => null)
  const parsed = StartSchema.safeParse(body)
  if (!parsed.success) return c.json({ error: 'Validation failed', issues: parsed.error.issues }, 400)

  const job = await getImportJob(c.get('db'), session.tid, c.req.param('id'))
  if (!job) return c.json({ error: 'Not found' }, 404)
  if (job.status !== 'pending') return c.json({ error: 'Job already started or completed' }, 409)

  const missingField = validateRequiredMapping(job.type as ImportType, parsed.data.mapping)
  if (missingField) return c.json({ error: 'REQUIRED_FIELD_UNMAPPED', field: missingField }, 422)

  const object = await c.env.STORAGE.get(job.r2_key)
  if (!object) return c.json({ error: 'Uploaded file not found' }, 404)

  const parsedFile = await parseImportFile(object, job.original_filename, job.mime_type)
  await updateImportJobMappings(c.get('db'), session.tid, job.id, parsed.data.mapping)
  await setImportJobProcessing(c.get('db'), session.tid, job.id, parsedFile.totalRows)

  await c.env.QUEUE.send({
    type: 'import.process',
    import_job_id: job.id,
    tenant_id: session.tid,
    type_name: job.type,
    r2_key: job.r2_key,
    column_mapping: parsed.data.mapping,
  })

  return c.json({ id: job.id, status: 'processing' }, 202)
})

importRouter.get('/:id/results', requirePermission('invoices:read'), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) return c.json({ error: 'Unauthorized' }, 401)

  const job = await getImportJob(c.get('db'), session.tid, c.req.param('id'))
  if (!job) return c.json({ error: 'Not found' }, 404)

  const query = ListResultsQuerySchema.safeParse(Object.fromEntries(new URL(c.req.url).searchParams))
  if (!query.success) return c.json({ error: 'Invalid query', issues: query.error.issues }, 400)

  const results = await listImportJobResults(c.get('db'), job.id, query.data.status, query.data.limit)
  return c.json({ results })
})

importRouter.get('/:id/errors.csv', requirePermission('invoices:read'), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) return c.json({ error: 'Unauthorized' }, 401)

  const job = await getImportJob(c.get('db'), session.tid, c.req.param('id'))
  if (!job) return c.json({ error: 'Not found' }, 404)
  if (job.status !== 'completed' || (job.error_count ?? 0) + (job.skipped_count ?? 0) <= 0) {
    return c.json({ error: 'No error report available' }, 404)
  }

  const [errors, skipped] = await Promise.all([
    listImportJobResults(c.get('db'), job.id, 'error', 500),
    listImportJobResults(c.get('db'), job.id, 'skipped', 500),
  ])

  const rows = [...errors, ...skipped].sort((a, b) => a.row_number - b.row_number)
  const csv = [
    'row_number,original_data,status,message',
    ...rows.map((row) =>
      [
        String(row.row_number),
        csvEscape(row.original_data ?? ''),
        row.status,
        csvEscape(row.message ?? ''),
      ].join(','),
    ),
  ].join('\n')

  c.header('content-type', 'text/csv; charset=utf-8')
  c.header('content-disposition', `attachment; filename="import-errors-${job.id}.csv"`)
  return c.body(csv)
})

importRouter.delete('/:id', requirePermission('invoices:write'), async (c) => {
  const session = c.get('session')
  if (!session || session.type !== 'user' || !session.tid) return c.json({ error: 'Unauthorized' }, 401)

  const job = await getImportJob(c.get('db'), session.tid, c.req.param('id'))
  if (!job) return c.json({ error: 'Not found' }, 404)
  if (job.status === 'processing') return c.json({ error: 'Cannot delete an import job that is currently processing' }, 409)

  await c.env.STORAGE.delete(job.r2_key).catch(() => {})
  const deleted = await deleteImportJob(c.get('db'), session.tid, job.id)
  if (!deleted) return c.json({ error: 'Delete failed' }, 500)
  return c.body(null, 204)
})

import { bankStatementImportRoute } from './bank-statement'
importRouter.route('/bank-statement', bankStatementImportRoute)