import {
  ChangeDetectorRef,
  Component,
  ElementRef,
  HostBinding,
  OnInit,
  AfterViewInit,
  ViewChild,
  ViewEncapsulation
} from '@angular/core'
import {
  EventService,
  HelperService,
  LicenceService,
  SasService,
  SasStoreService
} from '../services'
import { globals } from '../_globals'
import { EditorsGetDataServiceResponse } from '../models/sas/editors-getdata.model'
import { DcValidator } from '../shared/dc-validator/dc-validator'
import {
  prepareDeleteRecordColumn,
  seedDeleteRecordColumn
} from '../shared/dc-validator/utils/deleteRecordColumn'
import { DELETE_RECORD_COLUMN_NAME } from '../shared/dc-validator/utils/deleteRecordColumnRule'
import { ExcelRule } from '../models/TableData'
import { HotTableInterface } from '../models/HotTable.interface'
import { Col } from '../shared/dc-validator/models/col.model'
import { SpreadsheetService } from '../services/spreadsheet.service'
import Handsontable, { CellChange, ChangeSource } from 'handsontable'
import { HotTableComponent } from '@handsontable/angular-wrapper'
import { EditorsStageDataSASResponse } from '../models/sas/editors-stagedata.model'
import { baseAfterGetColHeader } from '../shared/utils/hot.utils'
import { ColumnSettings } from 'handsontable/settings'
import { UploadFile } from '@sasjs/adapter'
import { UploadFileResponse } from '../models/UploadFile'
import { RequestWrapperResponse } from '../models/request-wrapper/RequestWrapperResponse'
import { ParseResult } from '../models/ParseResult.interface'
import XLSX from 'xlsx'

enum FileLoadingState {
  reading = 'Reading the file',
  parsing = 'Searching for the data in the file',
  parsed = 'Searching for the data finished',
  /**
   * Defualt value
   */
  notSelected = 'File not selected'
}

@Component({
  selector: 'app-multi-dataset',
  templateUrl: './multi-dataset.component.html',
  styleUrls: ['./multi-dataset.component.scss'],
  encapsulation: ViewEncapsulation.None,
  standalone: false
})
export class MultiDatasetComponent implements OnInit, AfterViewInit {
  @HostBinding('class.content-container') contentContainerClass = true
  @ViewChild('contentArea', { static: true }) contentAreaRef!: ElementRef

  public licenceState = this.licenceService.licenceState
  public Infinity = Infinity

  public workbookInterval: any
  public fileLoadingState: FileLoadingState = FileLoadingState.notSelected

  public FileLoadingState = FileLoadingState

  public hotTableLicenseKey: string | undefined = undefined
  public hotTableMaxRows =
    this.licenceState.value.viewer_rows_allowed || Infinity

  public csvFiles: UploadFile[] = []
  public csvSubmitting: boolean = false

  public autoDetectingColumns: boolean = false

  public selectedFile: SelectedFile | null = null
  public parsedDatasets: ParsedDataset[] = []
  public submittedCsvDatasets: SubmittedCsvDatasetResult[] = []

  public datasetsLoading: boolean = false
  public uploadLoading: boolean = false
  public submitLoading: boolean = false

  public matchedDatasets: string[] = []
  public sheetNames: string[] = []

  public userInputDatasets: string = ''

  public libsAndTables: {
    [key: string]: string[]
  } = {}

  public hotInstance!: Handsontable
  public hotInstanceUserDataset!: Handsontable
  @ViewChild('hotInstanceMain', { static: false })
  hotTableMainComponent!: HotTableComponent
  @ViewChild('hotInstanceUserDataset', { static: false })
  hotTableUserDatasetComponent!: HotTableComponent

  public hotMainTableSettings: Handsontable.GridSettings = {}
  public hotUserDatasetsSettings: Handsontable.GridSettings = {}

  public showSubmitReasonModal: boolean = false
  public submitReasonMessage: string = ''

  public hotUserDatasets: Handsontable.GridSettings = {
    colHeaders: ['Library', 'Table'],
    data: [
      ['', ''],
      ['', ''],
      ['', ''],
      ['', ''],
      ['', ''],
      ['', ''],
      ['', ''],
      ['', ''],
      ['', ''],
      ['', ''],
      ['', ''],
      ['', '']
    ],
    columns: [
      {
        type: 'autocomplete',
        filter: false,
        source: []
      },
      {
        type: 'autocomplete',
        filter: false,
        source: []
      }
    ],
    width: '100%',
    height: '305px',
    className: ['htDark'],
    contextMenu: {
      items: {
        row_above: {
          name: 'Insert Row above'
        },
        row_below: {
          name: 'Insert Row below'
        }
      }
    },
    manualRowMove: true,
    columnSorting: true,
    afterGetColHeader: baseAfterGetColHeader,
    stretchH: 'all'
  }

  private initializeHotSettings() {
    this.hotMainTableSettings = {
      className: ['htDark'],
      licenseKey: this.hotTableLicenseKey,
      multiColumnSorting: true,
      viewportRowRenderingOffset: 50,
      manualColumnResize: true,
      autoColumnSize: true,
      filters: true,
      stretchH: 'all',
      afterGetColHeader: baseAfterGetColHeader,
      modifyColWidth: this.maxWidthCheker,
      theme: 'ht-theme-classic'
    }

    // Exclude data from settings for HOT v16 - it will be loaded manually
    const { data, ...settingsWithoutData } = this.hotUserDatasets
    this.hotUserDatasetsSettings = {
      ...settingsWithoutData,
      licenseKey: this.hotTableLicenseKey,
      theme: 'ht-theme-classic'
    }
  }

  public maxWidthCheker(width: any, col: any) {
    if (width > 200) return 200
    else return width
  }

  public afterGetColHeader = baseAfterGetColHeader

  constructor(
    private eventService: EventService,
    private licenceService: LicenceService,
    private helperService: HelperService,
    private sasStoreService: SasStoreService,
    private spreadsheetService: SpreadsheetService,
    private sasService: SasService,
    private cdr: ChangeDetectorRef
  ) {}

  ngOnInit() {
    this.licenceService.hot_license_key.subscribe(
      (hot_license_key: string | undefined) => {
        this.hotTableLicenseKey = hot_license_key
        this.initializeHotSettings()
      }
    )
    this.initializeHotSettings()
  }

  ngAfterViewInit() {
    // Ensure HOT instances are properly initialized after view is ready
    setTimeout(() => {
      if (this.hotTableUserDatasetComponent && !this.hotInstanceUserDataset) {
        this.initUserInputHot()
      }
      if (this.hotTableMainComponent && !this.hotInstance) {
        this.initHot()
      }
    }, 50)
  }

  ngAfterContentInit(): void {
    if (globals.editor.startupSet) {
      this.getFromGlobals()
    } else {
      this.eventService.onStartupDataLoaded.subscribe(() => {
        this.getFromGlobals()
      })
    }
  }

  public getFromGlobals() {
    this.libsAndTables = globals.editor.libsAndTables

    const libs: string[] = Object.keys(this.libsAndTables)

    if (this.hotUserDatasets?.columns) {
      ;(this.hotUserDatasets.columns as ColumnSettings[])[0].source = libs
    }
  }

  async onFileChange(event: any) {
    const files = event?.target?.files || []

    if (files.length < 1) {
      this.eventService.showAbortModal(
        null,
        'No file found.',
        null,
        'File Upload'
      )
      return
    }

    let matchedExtension = ''

    for (let file of files) {
      const fileExtension = file.name.split('.').pop()

      if (!matchedExtension) {
        matchedExtension = fileExtension
      }

      if (matchedExtension !== fileExtension) {
        this.eventService.showInfoModal(
          'Mixed extensions error',
          'Please select files with same extension.'
        )
        return
      }

      matchedExtension = fileExtension
    }

    if (['xlsx', 'xlsm', 'xlm'].includes(matchedExtension)) {
      // For EXCEL if multiple files, we only take one (the first one)
      this.selectedFile = event.target.files[0]

      if (this.selectedFile) {
        this.fileLoadingState = FileLoadingState.reading

        this.selectedFile.sizeMB = this.spreadsheetService.bytesToMB(
          this.selectedFile.size
        )

        // Read the excel file to be ready
        this.spreadsheetService.xlsxReadFile(this.selectedFile!).then((wb) => {
          this.fileLoadingState = FileLoadingState.parsing
          this.selectedFile!.workbook = wb
        })
      }

      this.initUserInputHot()
      // Call onAutoDetectColumns after HOT is initialized
      setTimeout(() => {
        this.onAutoDetectColumns()
      }, 100)
    } else if (matchedExtension === 'csv') {
      this.onMultiCsvFiles(event.target.files)
    } else {
      this.eventService.showAbortModal(
        null,
        'Only excel extensions are allowed. (xlsx)',
        null,
        'Extension Error'
      )
      return
    }

    event.target.value = '' // Reset the upload input
  }

  async onMultiCsvFiles(files: File[]) {
    for (let file of files) {
      const fileNameNoExtension = this.parseDatasetFromCsvName(file.name)

      if (
        this.isValidDatasetFormat(fileNameNoExtension) &&
        this.isValidDatasetReference(fileNameNoExtension)
      ) {
        this.csvFiles.push({
          file: file,
          fileName: file.name
        })
      }
    }

    if (!this.csvFiles.length) {
      this.eventService.showInfoModal(
        'CSV Upload',
        'None of the attached CSV file names matched an actual dataset.'
      )
      return
    }

    this.csvSubmitting = true

    await this.submitCsvFiles()

    this.csvSubmitting = false
  }

  onDiscardFile() {
    this.selectedFile = null
    this.userInputDatasets = ''
  }

  async onStartParsingFile() {
    this.uploadLoading = true

    const datasetFetchingPromises: Promise<
      EditorsGetDataServiceResponse | undefined
    >[] = []

    let datasets: EditorsGetDataServiceResponse[] = []

    for (let datasetLibds of this.matchedDatasets) {
      const promise = this.fetchDataset(datasetLibds)

      datasetFetchingPromises.push(promise)
    }

    Promise.allSettled(datasetFetchingPromises).then((res) => {
      res.forEach((promise) => {
        if (promise.status === 'fulfilled' && promise.value)
          datasets.push(promise.value)
      })

      this.uploadLoading = false

      // buildDatasetsObjects throws when a getdata response carries the
      // reserved delete-record column (see deleteRecordColumn.ts). This chain
      // has no .catch, so an uncaught throw here would become an unhandled
      // rejection that aborts the loop with nothing shown. Surface it on
      // screen, the way the editor flow refuses the same response, rather
      // than leaving the user on an empty table selection with the file
      // still attached.
      let datasetObjects: DatasetsObject[] = []
      try {
        datasetObjects = this.buildDatasetsObjects(datasets)
      } catch (err) {
        console.warn('Error building dataset objects', err)
        this.eventService.showInfoModal('Upload error', (err as Error).message)
        return
      }

      datasetObjects.forEach((datasetObject) => {
        this.parsedDatasets.push({
          libds: datasetObject.libds,
          includeInSubmission: true,
          datasetInfo: datasetObject,
          parsingTable: true
        })
      })

      this.workbookLoaded().then((workbook) => {
        for (let parsedDataset of this.parsedDatasets) {
          this.spreadsheetService
            .parseExcelFile({
              file: this.selectedFile!,
              workbook: workbook,
              password: this.selectedFile!.password || undefined,
              dcValidator: parsedDataset.datasetInfo.dcValidator!,
              headerPks: parsedDataset.datasetInfo.headerPks,
              headerArray: parsedDataset.datasetInfo.headerArray,
              headerShow: [],
              timeHeaders: parsedDataset.datasetInfo.timeHeaders,
              dateHeaders: parsedDataset.datasetInfo.dateHeaders,
              dateTimeHeaders: parsedDataset.datasetInfo.dateTimeHeaders,
              xlRules: parsedDataset.datasetInfo.xlRules
            })
            .then((parseResult: ParseResult | undefined) => {
              this.fileLoadingState = FileLoadingState.parsed

              if (parseResult && parseResult.data) {
                let datasource: any[] = []

                parseResult.data.map((item) => {
                  let itemObject: any = {}

                  parseResult.headerShow!.map((header: any, index: number) => {
                    itemObject[header] = item[index]
                  })

                  // If Delete? column is not set in the file, we set it to NO
                  if (!itemObject['_____DELETE__THIS__RECORD_____'])
                    itemObject['_____DELETE__THIS__RECORD_____'] = 'No'

                  datasource.push(itemObject)
                })

                parsedDataset.datasource = datasource
                parsedDataset.parseResult = parseResult
                parsedDataset.parsingTable = false
              }
            })
            .catch((error: string) => {
              console.warn('Parsing excel file error.', error)

              parsedDataset.datasource = []
              parsedDataset.includeInSubmission = false
              parsedDataset.parsingTable = false
            })
        }
      })
    })
  }

  onSubmitAll() {
    if (this.tablesToSubmit.length) {
      this.showSubmitReasonModal = true
    } else {
      this.eventService.showInfoModal(
        'No tables to submit',
        'Please include at least one table to proceed.'
      )
    }
  }

  onDiscard() {
    this.parsedDatasets = []
    this.matchedDatasets = []
    this.selectedFile = null
    this.userInputDatasets = ''
    this.submitReasonMessage = ''
  }

  initHot() {
    setTimeout(() => {
      if (this.hotTableMainComponent?.hotInstance) {
        this.hotInstance = this.hotTableMainComponent.hotInstance

        // Set height of parsed data to full height of the page content area
        const contentAreaHeight = this.contentAreaRef.nativeElement.clientHeight
        const hotHeight = `${contentAreaHeight - 160}px`

        if (this.activeParsedDataset) {
          // Update settings without data - data will be loaded manually
          this.hotInstance.updateSettings({
            colHeaders: this.activeParsedDataset.datasetInfo.headerColumns,
            columns:
              this.activeParsedDataset.datasetInfo.dcValidator?.getRules(),
            readOnly: true,
            height: hotHeight || '300px',
            className: ['htDark']
          })

          // Trigger change detection to avoid ExpressionChangedAfterItHasBeenCheckedError
          this.cdr.detectChanges()

          // Load data manually - this is required for HOT v16 Angular wrapper
          setTimeout(() => {
            if (
              this.activeParsedDataset &&
              this.activeParsedDataset.datasource
            ) {
              this.hotInstance.loadData(this.activeParsedDataset.datasource)
              this.hotInstance.render()
            }
          }, 100)
        }
      }
    }, 100)
  }

  initUserInputHot() {
    setTimeout(() => {
      if (this.hotTableUserDatasetComponent?.hotInstance) {
        this.hotInstanceUserDataset =
          this.hotTableUserDatasetComponent.hotInstance

        // Load initial data manually after instance is ready
        setTimeout(() => {
          if (this.hotUserDatasets.data) {
            this.hotInstanceUserDataset.loadData(this.hotUserDatasets.data)
            this.hotInstanceUserDataset.render()
          }
        }, 50)

        this.hotInstanceUserDataset.addHook(
          'beforeChange',
          (changes: (CellChange | null)[], source: ChangeSource) => {
            if (changes) {
              for (let change of changes) {
                if (change && change[3]) {
                  // CellValue is `unknown` in HOT 18 (was a concrete union before);
                  // this column is always a dataset name string.
                  change[3] = (change[3] as string).toUpperCase()
                }
              }
            }
          }
        )

        this.hotInstanceUserDataset.addHook(
          'afterChange',
          async (changes: CellChange[] | null, source: ChangeSource) => {
            if (changes) {
              if (source === 'edit') {
                await this.onUserInputDatasetsChange()
              }

              for (let change of changes) {
                const row = change[0] as number

                this.markUnmatchedRows(row)
              }

              this.dynamicCellValidations()

              this.hotInstanceUserDataset.render()
            }
          }
        )

        this.hotInstanceUserDataset.addHook(
          'afterRemoveRow',
          async (
            index: number,
            amount: number,
            physicalRows: number[],
            source?: Handsontable.ChangeSource | undefined
          ) => {
            await this.onUserInputDatasetsChange()

            for (let row of physicalRows) {
              this.markUnmatchedRows(row)
            }
          }
        )
      }
    }, 100)
  }

  dynamicCellValidations() {
    if (!this.hotInstanceUserDataset) return

    // getData() returns unknown[][] in HOT 18 (was CellValue[][]); this grid's
    // data is always library/table name strings.
    const hotData = this.hotInstanceUserDataset.getData() as string[][]

    hotData.forEach((row, rowIndex) => {
      const library = row[0]

      if (library && library.length) {
        const tables = this.libsAndTables[library]
        this.hotInstanceUserDataset.setCellMeta(rowIndex, 1, 'source', tables)
      }
    })
  }

  markUnmatchedRows(row: number) {
    if (!this.hotInstanceUserDataset) return

    const dataAtRow = this.hotInstanceUserDataset.getDataAtRow(row) as number[]
    const dataset = `${dataAtRow[0]}.${dataAtRow[1]}`
    const cellMetaAtRow = this.hotInstanceUserDataset.getCellMetaAtRow(row)

    if (dataAtRow && dataAtRow[0] && dataAtRow[1]) {
      if (!this.matchedDatasets.includes(dataset)) {
        // getCellMetaAtRow() returns Record<string, unknown>[] in HOT 18, so
        // `.col` needs casting back to number for setCellMeta() (same below).
        cellMetaAtRow.forEach((cellMeta) => {
          this.hotInstanceUserDataset.setCellMeta(
            row,
            cellMeta.col as number,
            'className',
            'not-matched'
          )
        })
      } else {
        cellMetaAtRow.forEach((cellMeta) => {
          this.hotInstanceUserDataset.setCellMeta(
            row,
            cellMeta.col as number,
            'className',
            ''
          )
        })
      }
    } else {
      cellMetaAtRow.forEach((cellMeta) => {
        this.hotInstanceUserDataset.setCellMeta(
          row,
          cellMeta.col as number,
          'className',
          ''
        )
      })
    }
  }

  onUserInputDatasetsChange() {
    return new Promise((resolve, reject) => {
      this.helperService.debounceCall(100, () => {
        // Parse datasets
        const inputDatasets = this.getDatasetsFromHot()

        this.matchedDatasets = []

        inputDatasets.forEach((dataset: string) => {
          const trimmedDataset = dataset.trim()

          if (
            this.isValidDatasetFormat(trimmedDataset) &&
            this.isValidDatasetReference(trimmedDataset) &&
            !this.matchedDatasets.includes(trimmedDataset)
          ) {
            this.matchedDatasets.push(trimmedDataset)
          } else {
            console.warn(
              `Sheet name: ${trimmedDataset} is not an actual dataset reference.`
            )
          }
        })

        this.cdr.detectChanges()

        resolve(undefined)
      })
    })
  }

  /**
   * Try to find the datasets in the provided file by looking in the
   * sheet names and testing if they are in the scope of the dataset naming
   * convention. {@link isValidDatasetFormat}
   */
  async onAutoDetectColumns() {
    // Wait for hotInstanceUserDataset to be ready
    if (!this.hotInstanceUserDataset) {
      let attempts = 0
      const maxAttempts = 20
      while (!this.hotInstanceUserDataset && attempts < maxAttempts) {
        await new Promise((resolve) => setTimeout(resolve, 100))
        attempts++
      }
      if (!this.hotInstanceUserDataset) {
        console.warn('hotInstanceUserDataset not ready after waiting')
        return
      }
    }

    let passwordError = false

    await this.parseExcelSheetNames()
      .then((sheetNames) => {
        this.sheetNames = sheetNames
      })
      .catch((err) => {
        if (err.includes('password')) {
          passwordError = true
        }
      })

    if (passwordError) {
      this.onDiscardFile()
      this.eventService.showInfoModal(
        'Locked file',
        'We failed to unlock the file.'
      )
      return
    }

    if (this.sheetNames) {
      this.matchedDatasets = []
      this.userInputDatasets = ''

      this.sheetNames.forEach((sheetName: string, index: number) => {
        const trimmedSheetname = sheetName.trim()

        if (
          this.isValidDatasetFormat(trimmedSheetname) &&
          this.isValidDatasetReference(trimmedSheetname)
        ) {
          this.matchedDatasets.push(trimmedSheetname)
        } else {
          console.warn(
            `Sheet name: ${trimmedSheetname} is not an actual dataset reference.`
          )
        }
      })
    }

    // Set matched datasets to textarea, dataset per row
    this.userInputDatasets = this.matchedDatasets.join('\n')

    const hotReadyData = this.matchedDatasets.map((matchedDs) => {
      return [matchedDs.split('.')[0], matchedDs.split('.')[1]]
    })

    // Add empty rows to fill initial number of rows if data has less rows
    // The reason is to fill the height of the table
    const initialNumberOfRows = this.hotUserDatasets.data!.length

    if (hotReadyData.length < initialNumberOfRows) {
      const missingRows = initialNumberOfRows - hotReadyData.length

      for (let i = 0; i < missingRows; i++) {
        hotReadyData.push(['', ''])
      }
    }

    if (this.hotInstanceUserDataset) {
      // Load data manually - this is required for HOT v16 Angular wrapper
      setTimeout(() => {
        this.hotInstanceUserDataset.loadData(hotReadyData)
        this.hotInstanceUserDataset.render()
      }, 100)
    }
    this.dynamicCellValidations()
  }

  onParsedDatasetClick(parsedDataset: ParsedDataset) {
    this.deselectAllParsedDatasets()

    parsedDataset.active = true

    this.cdr.detectChanges()
    this.initHot()
  }

  onSubmittedCsvDatasetClick(submittedDataset: SubmittedCsvDatasetResult) {
    this.deselectAllSubmittedCsvDatasets()

    submittedDataset.active = true
  }

  public get activeParsedDataset(): ParsedDataset | undefined {
    return this.parsedDatasets.find((dataset) => dataset.active)
  }

  public get activeSubmittedCsvDataset():
    | SubmittedCsvDatasetResult
    | undefined {
    return this.submittedCsvDatasets.find((dataset) => dataset.active)
  }

  public get notFoundDatasets(): string[] {
    const userDatasets = this.getDatasetsFromHot()

    return userDatasets
      .filter((userDs) => !this.matchedDatasets.includes(userDs.trim()))
      .filter((userDs) => userDs.length)
  }

  public get isHotHidden(): boolean {
    if (!this.hotInstance) return true

    try {
      const className = this.hotInstance.getSettings().className

      return !!className && className.includes('htCustomHidden')
    } catch (err) {
      return true
    }
  }

  public get tablesToSubmit(): ParsedDataset[] {
    return this.parsedDatasets.filter(
      (dataset) =>
        dataset.datasource && dataset.parseResult && dataset.includeInSubmission
    )
  }

  public get submittingCsv(): boolean {
    return this.csvFiles.length > 0
  }

  public get excelsSubmitted(): boolean {
    return !!this.parsedDatasets.filter((ds) => ds.submitResult).length
  }

  public downloadFile(response: any) {
    const filename = `stagedata-${this.activeSubmittedCsvDataset?.libds}-log`
    this.helperService.downloadTextFile(filename, JSON.stringify(response))
  }

  /**
   * Submits attached CSVs which are matched with existing datasets
   */
  async submitCsvFiles() {
    let requestsResults: SubmittedCsvDatasetResult[] = []

    for (let file of this.csvFiles) {
      const libds = this.parseDatasetFromCsvName(file.fileName)

      let error
      let success

      await this.sasService
        .uploadFile('services/editors/loadfile', [file], { table: libds })
        .then(
          (res: UploadFileResponse) => {
            if (typeof res.adapterResponse.sasjsAbort !== 'undefined') {
              error = res.adapterResponse.sasjsAbort
            } else {
              success = res.adapterResponse
            }
          },
          (err: any) => {
            console.error('err', err)

            error = err.adapterResponse
          }
        )

      requestsResults.push({
        success,
        error,
        libds: libds
      })
    }

    this.submittedCsvDatasets = requestsResults
  }

  /**
   * Fetches the table for given datasets params LIBRARY.TABLE
   */
  async fetchDataset(
    libds: string
  ): Promise<EditorsGetDataServiceResponse | undefined> {
    let myParams: any = {
      LIBDS: libds,
      OUTDEST: 'WEB'
    }

    if (libds) {
      // this.getdataError = false
      return this.sasStoreService
        .callService(myParams, 'SASControlTable', 'editors/getdata', libds)
        .then((res: EditorsGetDataServiceResponse) => {
          // getdata serves a selection over DC_MAXOBS_WEBEDIT / DC_MAXCELLS_WEBEDIT
          // truncated, rather than aborting the way it used to. This flow compares
          // the whole table against the upload, so a partial read would compute
          // the changes against the wrong starting state - refuse the table
          // instead of submitting differences nobody asked for.
          const sasParams = res?.data?.sasparams?.[0]

          if (sasParams && Number(sasParams.TRUNCATED) === 1) {
            this.eventService.showInfoModal(
              'Upload error',
              `${libds} holds more than the EDIT limit allows (${sasParams.TRUNC_MSG}), so it cannot be compared with the upload. Raise DC_MAXOBS_WEBEDIT / DC_MAXCELLS_WEBEDIT, or submit the table without the multi-table upload.`
            )
            return undefined
          }

          return res
        })
        .catch((err: any) => {
          console.warn(`Error fetching ${libds}`, err)
          return undefined
        })
    }

    return undefined
  }

  /**
   * Sends tables found in an excel to the SAS sequentially
   *
   * @param explicitDatasets if empty all datasets will be sent, otherwise only datasets
   * in the array will be sent. eg. ['lib1.table_1', 'lib1.table_2']
   */
  async submitTables(explicitDatasets?: string[]) {
    console.info('Submitting multiple tables', this.parsedDatasets)

    this.submitLoading = true

    for (let table of this.parsedDatasets) {
      // Skip the table if no data inside
      if (!table.parseResult || !table.datasource) continue
      // Skip the table if toggle switch is off
      if (!table.includeInSubmission) continue
      // Skip the table if datasets is present and this table not defined in it
      if (explicitDatasets && !explicitDatasets.includes(table.libds)) continue

      let updateParams: any = {}

      this.submitReasonMessage = this.submitReasonMessage.replace(/\n/g, '. ')
      updateParams.ACTION = 'LOAD'
      updateParams.MESSAGE = this.submitReasonMessage
      updateParams.LIBDS = table.libds

      let data = table.datasource

      if (data) {
        data = data.map((row: any) => {
          let deleteColValue = row['_____DELETE__THIS__RECORD_____']

          delete row['_____DELETE__THIS__RECORD_____']
          row['_____DELETE__THIS__RECORD_____'] = deleteColValue

          // If cell is numeric and value is dot `.` we change it to `null`
          Object.keys(row).map((key: string) => {
            const colRule = table.datasetInfo.dcValidator?.getRule(key)

            if (colRule?.type === 'numeric' && row[key] === '.') row[key] = null
          })

          return row
        })

        const submitData = data.slice(
          0,
          this.licenceState.value.submit_rows_limit
        )

        let error
        let success
        let log

        await this.sasStoreService
          .updateTable(
            updateParams,
            submitData,
            'SASControlTable',
            'editors/stagedata',
            table.datasetInfo.data.$sasdata,
            true,
            {
              debug: true
            }
          )
          .then((res: RequestWrapperResponse<EditorsStageDataSASResponse>) => {
            success = res.adapterResponse
            log = res.log
          })
          .catch((err: any) => {
            console.error('err', err)

            error = err.adapterResponse
            log = err.log
          })

        const requestResult = {
          success,
          error,
          log,
          parseResult: table.parseResult,
          libds: table.libds
        }

        table.submitResult = requestResult
      }
    }
    this.showSubmitReasonModal = false
    this.submitLoading = false
    this.deselectAllParsedDatasets()
  }

  async reSubmitTable(activeSubmittedDataset: ParsedDataset) {
    // Submit only particular table
    await this.submitTables([activeSubmittedDataset.libds])

    // Activate new resubmitted table
    const newSubmittedDataset = this.parsedDatasets.find(
      (sd) => sd.libds === activeSubmittedDataset.libds
    )

    if (newSubmittedDataset) newSubmittedDataset.active = true
  }

  /**
   *
   * @returns Promise once workbook is loaded because use XLSX.read in the background
   */
  private workbookLoaded(): Promise<XLSX.WorkBook> {
    return new Promise((resolve, reject) => {
      if (!this.selectedFile) reject('No file selected')

      this.workbookInterval = setInterval(() => {
        if (this.selectedFile!.workbook) {
          clearInterval(this.workbookInterval)
          resolve(this.selectedFile!.workbook)
        }
      }, 500)
    })
  }

  private parseDatasetFromCsvName(fileName: string) {
    const fileNameArr = fileName.split('.')
    fileNameArr.pop()
    return fileNameArr.join('.')
  }

  /**
   *
   * @returns list of strings containing datasets in the HOT user input
   * Format: LIBRARY.TABLE
   */
  private getDatasetsFromHot(): string[] {
    if (!this.hotInstanceUserDataset) return []

    // getData() returns unknown[][] in HOT 18; see dynamicCellValidations() above.
    const hotData = this.hotInstanceUserDataset.getData() as string[][]

    return hotData
      .filter((row) => row[0]?.length && row[1]?.length)
      .map((row) => (row ? `${row[0]}.${row[1]}` : ''))
  }

  /**
   * Read the file minimally just to get the sheet names, not reading full file
   * to help boost the performance
   *
   * @returns sheet names in string array
   */
  private async parseExcelSheetNames(): Promise<string[]> {
    return new Promise((resolve, reject) => {
      if (!this.selectedFile) return resolve([])

      this.spreadsheetService
        .parseExcelSheetNames(this.selectedFile)
        .then((parsed) => {
          if (parsed.password) this.selectedFile!.password = parsed.password

          return resolve(parsed.sheetNames)
        })
        .catch((err) => {
          return reject(err)
        })
    })
  }

  /**
   * Valid dataset format includes:
   * - Name must contain a single period (.)
   * - First part (before period) can be no more than 8 chars
   * - Second part (after period) can be no more than 32 chars
   * - (start with letter or underscore, and contain only letters / underscores / numbers)
   * - can't start with a number
   * - both left and right parts must be valid variable names
   *
   *  example: LIB123.TABLE_123
   */
  private isValidDatasetFormat(name: string) {
    const regex = /^\w{1,8}\.\w{1,32}$/gim
    const correctFormat = regex.test(name)

    return correctFormat
  }

  /**
   * Checks if @param datasetRef is valid variable which references library and table
   */
  private isValidDatasetReference(datasetRef: string) {
    const library = datasetRef.split('.')[0]
    const table = datasetRef.split('.')[1]

    const libTable = this.libsAndTables[library]?.includes(table)

    if (libTable) return true

    return false
  }

  /**
   * Creates array of objects, containing all `getdata` responses per item
   * every object will have headers and validations parsed, to be used for
   * parsing sheet file and uploading it
   *
   * @param response
   * @returns
   */
  private buildDatasetsObjects(responses: EditorsGetDataServiceResponse[]) {
    if (!responses) return []

    const datasetObjects: DatasetsObject[] = []

    for (let response of responses) {
      if (response.data) {
        // The delete column is client-owned (see deleteRecordColumnRule.ts):
        // asserts getdata did not send it, then prepends it to COLHEADERS so
        // it is index 0 for everything below.
        prepareDeleteRecordColumn(response)
        seedDeleteRecordColumn(response.data.sasdata)

        const datasetObject: DatasetsObject = {
          ...response,
          hotTable: {
            data: response.data.sasdata,
            settings: {}
          },
          cols: [],
          headerColumns: [],
          headerPks: [],
          headerArray: [],
          dateHeaders: [],
          timeHeaders: [],
          dateTimeHeaders: [],
          xlRules: [],
          columnHeader: []
        }

        datasetObject.cols = response.data.cols
        datasetObject.headerColumns =
          response.data.sasparams[0].COLHEADERS.split(',')
        datasetObject.headerPks = response.data.sasparams[0].PK.split(' ')

        if (
          datasetObject.headerColumns.indexOf(DELETE_RECORD_COLUMN_NAME) !== -1
        ) {
          datasetObject.headerColumns[
            datasetObject.headerColumns.indexOf(DELETE_RECORD_COLUMN_NAME)
          ] = 'Delete?'
        }

        datasetObject.headerArray = datasetObject.headerColumns.slice(1)

        if (response.data.sasparams[0].DTVARS !== '') {
          datasetObject.dateHeaders =
            response.data.sasparams[0].DTVARS.split(' ')
        }
        if (response.data.sasparams[0].TMVARS !== '') {
          datasetObject.timeHeaders =
            response.data.sasparams[0].TMVARS.split(' ')
        }
        if (response.data.sasparams[0].DTTMVARS !== '') {
          datasetObject.dateTimeHeaders =
            response.data.sasparams[0].DTTMVARS.split(' ')
        }
        if (response.data.xl_rules.length > 0) {
          datasetObject.xlRules = this.helperService.deepClone(
            response.data.xl_rules
          )
        }

        datasetObject.dcValidator = new DcValidator(
          response.data.sasparams[0],
          response.data.$sasdata,
          response.data.cols,
          response.data.dqrules,
          response.data.dqdata
        )

        datasetObject.columnHeader =
          response.data.sasparams[0].COLHEADERS.split(',')

        datasetObjects.push(datasetObject)
      }
    }

    return datasetObjects
  }

  private deselectAllParsedDatasets() {
    for (let parsedDataset of this.parsedDatasets) {
      parsedDataset.active = false
    }
  }

  private deselectAllSubmittedCsvDatasets() {
    for (let submittedDataset of this.submittedCsvDatasets) {
      submittedDataset.active = false
    }
  }
}

export interface DatasetsObject extends EditorsGetDataServiceResponse {
  hotTable: HotTableInterface
  cols: Col[]
  headerColumns: string[]
  headerPks: string[]
  headerArray: string[]
  dateHeaders: string[]
  timeHeaders: string[]
  dateTimeHeaders: string[]
  xlRules: ExcelRule[]
  dcValidator?: DcValidator
  columnHeader: string[]
}

export interface ParsedDataset {
  libds: string
  parseResult?: ParseResult
  datasetInfo: DatasetsObject
  submitResult?: SubmittedDatasetResult
  datasource?: any[]
  includeInSubmission: boolean
  status?: 'success' | 'error'
  active?: boolean
  parsingTable?: boolean
}

export interface SubmittedDatasetResult {
  success: EditorsStageDataSASResponse | undefined
  error: any
  log?: string
}

export interface SubmittedCsvDatasetResult {
  libds: string
  success: EditorsStageDataSASResponse | undefined
  error: any
  active?: boolean
}

export interface SelectedFile extends File {
  sizeMB?: number
  password?: string
  workbook?: XLSX.WorkBook
}