import { BehaviorSubject } from 'rxjs'
import * as XLSX from '@sheet/crypto'
import { SpreadsheetUtil } from './spreadsheet-util'
import { LicenceState } from 'src/app/models/LicenceState'
import { ParseParams } from 'src/app/models/ParseParams.interface'
import { SearchDataExcelResult } from 'src/app/models/SearchDataExcelResult.interface'

describe('SpreadsheetUtil - header matching', () => {
  // licenceState is unused by searchDataInExcel, so an empty stub is fine.
  const buildSpreadsheetUtil = () =>
    new SpreadsheetUtil({
      licenceState: new BehaviorSubject<LicenceState>({} as LicenceState)
    })

  const buildWorkbook = (headerRow: string[], dataRow: any[]) => {
    const ws = XLSX.utils.aoa_to_sheet([headerRow, dataRow])
    const wb = XLSX.utils.book_new()

    XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')

    return wb
  }

  // searchDataInExcel only reads headerArray/headerPks off ParseParams; the
  // other required fields (dcValidator, etc.) are irrelevant here, so a
  // minimal cast object stands in for a full ParseParams.
  const buildParseParams = (): ParseParams =>
    ({
      headerArray: ['PRIMARY_KEY_FIELD', 'SOME_CHAR', 'SOME_NUM'],
      headerPks: ['PRIMARY_KEY_FIELD']
    }) as any as ParseParams

  // searchDataInExcel is private; there's no existing precedent in this
  // codebase for testing private methods, so this casts through `any` to
  // reach it directly rather than exercising it via the public
  // parseSpreadsheetFile (which would need a real File/FileReader).
  const searchDataInExcel = (
    spreadsheetUtil: SpreadsheetUtil,
    wb: XLSX.WorkBook,
    parseParams: ParseParams
  ): SearchDataExcelResult =>
    (spreadsheetUtil as any).searchDataInExcel(wb, parseParams)

  it('matches headers with leading whitespace against the expected clean header names', () => {
    const wb = buildWorkbook(
      ['PRIMARY_KEY_FIELD', ' SOME_CHAR', ' SOME_NUM'],
      [1, 'a value', 42]
    )

    const result = searchDataInExcel(
      buildSpreadsheetUtil(),
      wb,
      buildParseParams()
    )

    expect(result.missing).toBeUndefined()
    expect(result.found).toBeDefined()
    expect(result.found?.headers).toContain('some_char')
    expect(result.found?.headers).toContain('some_num')
  })

  it('still reports a genuinely different header as missing (no over-broadening)', () => {
    const wb = buildWorkbook(
      ['PRIMARY_KEY_FIELD', ' SOME_CHARX', ' SOME_NUM'],
      [1, 'a value', 42]
    )

    const result = searchDataInExcel(
      buildSpreadsheetUtil(),
      wb,
      buildParseParams()
    )

    expect(result.found).toBeUndefined()
    expect(result.missing).toBeDefined()
    expect(result.missing?.[0].missingHeaders).toContain('SOME_CHAR')
  })
})

describe('SpreadsheetUtil - updateDateTimeCols time normalization', () => {
  const buildSpreadsheetUtil = () =>
    new SpreadsheetUtil({
      licenceState: new BehaviorSubject<LicenceState>({} as LicenceState)
    })

  // updateDateTimeCols is private; cast through `any` (same precedent as above).
  const updateDateTimeCols = (
    spreadsheetUtil: SpreadsheetUtil,
    headers: string[],
    data: any[],
    parseParams: ParseParams
  ): any[] =>
    (spreadsheetUtil as any).updateDateTimeCols(headers, data, parseParams)

  const timeParseParams = (): ParseParams =>
    ({
      dateHeaders: [],
      dateTimeHeaders: [],
      timeHeaders: ['SOME_TIME']
    }) as any as ParseParams

  it('pads unpadded-hour time strings to canonical HH:mm:ss and leaves canonical values alone', () => {
    // The core bug: SAS TIME8. / Excel render a single-digit hour (`0:00:42`),
    // arriving as a plain string (not an Excel serial). Already-canonical values
    // must pass through untouched.
    const data = [[{ v: '0:00:42' }], [{ v: '9:05:03' }], [{ v: '23:00:00' }]]

    const result = updateDateTimeCols(
      buildSpreadsheetUtil(),
      ['SOME_TIME'],
      data,
      timeParseParams()
    )

    expect(result.map((row) => row[0].v)).toEqual([
      '00:00:42',
      '09:05:03',
      '23:00:00'
    ])
  })

  it('reads a 2-part value as HOURS-first and defaults seconds (HHMM/TIME5.): 0:42 -> 00:42:00', () => {
    // TIME5.-7. and HHMM. columns render `HH:mm`. Such values are never `mm:ss`
    // — DC only classifies TIME/HHMM formats as time columns — so the hour is
    // padded and minutes kept, with seconds defaulted to :00.
    const data = [[{ v: '0:42' }], [{ v: '12:25' }]]

    const result = updateDateTimeCols(
      buildSpreadsheetUtil(),
      ['SOME_TIME'],
      data,
      timeParseParams()
    )

    expect(result.map((row) => row[0].v)).toEqual(['00:42:00', '12:25:00'])
  })

  it('reconstructs an Excel serial time to zero-padded HH:mm:ss', () => {
    // 0.0004861111 of a day = 42 seconds past midnight.
    const data = [[{ v: 0.0004861111 }]]

    const result = updateDateTimeCols(
      buildSpreadsheetUtil(),
      ['SOME_TIME'],
      data,
      timeParseParams()
    )

    expect(result[0][0].v).toBe('00:00:42')
  })
})
