import { Injectable } from '@angular/core'
import { HelperService } from './helper.service'
import { VaMessage } from './va-messaging.service'
import { QueryClause } from '../models/TableData'

/** SAS data-type kind used to format a VA filter value into a DC RAW_VALUE. */
type VaColumnKind = 'char' | 'numeric' | 'time' | 'date' | 'datetime'

/**
 * Translates SAS Visual Analytics data-driven content `parameters` into DC
 * filter clauses. Independent of editor/HOT state — the editor passes the
 * loaded column metadata in — so it is unit-testable on its own. Temporal
 * values are converted to SAS internal numbers (matching DC's native filters)
 * via the shared HelperService.
 */
@Injectable({ providedIn: 'root' })
export class VaFilterService {
  private static readonly SAS_MONTHS = [
    'JAN',
    'FEB',
    'MAR',
    'APR',
    'MAY',
    'JUN',
    'JUL',
    'AUG',
    'SEP',
    'OCT',
    'NOV',
    'DEC'
  ]

  constructor(private helperService: HelperService) {}

  /** label/name (lower-cased) -> DC variable (prop) name, skipping the delete col. */
  buildLabelToVarMap(
    cellValidation: any[],
    columnHeader: string[]
  ): Map<string, string> {
    const map = new Map<string, string>()
    for (let i = 1; i < cellValidation.length; i++) {
      const varName = cellValidation[i]?.data
      if (!varName) continue
      map.set(
        (columnHeader[i] ?? varName).toString().trim().toLowerCase(),
        varName
      )
      map.set(varName.toString().trim().toLowerCase(), varName)
    }
    return map
  }

  /**
   * Translates VA `parameters` into DC filter clauses. Each parameter is matched
   * to a DC column by label; a single value becomes `col = value`, multiple
   * values become `col IN (...)`. Values are formatted for the column's SAS kind.
   */
  buildClauses(
    msg: VaMessage,
    labelToVar: Map<string, string>,
    cols: any[]
  ): QueryClause[] {
    const clauses: QueryClause[] = []
    for (const param of msg.parameters || []) {
      if (!param || param.value === undefined || param.value === null) continue
      const key = (param.label ?? param.name ?? '')
        .toString()
        .trim()
        .toLowerCase()
      // A VA param whose label isn't a DC column (beyond case, which is handled)
      // simply doesn't map — skip it.
      const varName = labelToVar.get(key)
      if (!varName) continue

      const col = this.findCol(varName, cols)
      const kind = this.columnKind(col)
      const order = this.dateOrderFromFmt(col?.FMTNAME || col?.FORMAT)
      const rawValues = Array.isArray(param.value) ? param.value : [param.value]
      // Format/validate every value for the column's SAS type. Numeric columns
      // must receive a finite number (untrusted postMessage input is
      // interpolated into a SAS WHERE clause, so a non-numeric value here would
      // be an injection vector); temporal -> SAS internal number; char ->
      // escaped. Invalid values are dropped.
      const formatted = rawValues
        .filter((v) => v !== undefined && v !== null && v !== '')
        .map((v) => this.formatValueByKind(v, kind, order))
        .filter((v): v is string => v !== null)
      if (formatted.length === 0) continue
      clauses.push(this.clause(varName, formatted))
    }
    return clauses
  }

  /**
   * Pre-getdata variant used on the deferred initial load, when DC column
   * metadata isn't available. Uses the VA `label` as the column name and infers
   * the SAS kind from the value shape, so the clause matches what the metadata
   * build later produces (no reconcile reload).
   */
  buildInitialClauses(msg: VaMessage): QueryClause[] {
    const clauses: QueryClause[] = []
    for (const param of msg.parameters || []) {
      if (!param || param.value === undefined || param.value === null) continue
      // The DC column name is the VA `label`; `name` is VA's internal id (pr###).
      const varName = (param.label ?? param.name ?? '').toString().trim()
      if (!varName) continue

      const rawValues = Array.isArray(param.value) ? param.value : [param.value]
      const nonEmpty = rawValues.filter(
        (v) => v !== undefined && v !== null && v !== ''
      )
      if (nonEmpty.length === 0) continue

      const formatted = nonEmpty
        .map((v) =>
          this.formatValueByKind(
            v,
            this.inferKindFromValue(String(v).trim(), param.dataType)
          )
        )
        .filter((v): v is string => v !== null)
      if (formatted.length === 0) continue
      clauses.push(this.clause(varName, formatted))
    }
    return clauses
  }

  /**
   * Stable signature of filter clauses for cross-reload dedup. VARIABLE_NM is
   * upper-cased because the metadata-less deferred build uses VA's label case
   * while the metadata build uses DC's stored column case — SAS WHERE clauses
   * are case-insensitive, so those are the same filter and must not trigger a
   * reconcile reload. A genuine value difference still changes the signature.
   */
  signature(clauses: QueryClause[]): string {
    return JSON.stringify(
      clauses.map((c) => ({
        ...c,
        VARIABLE_NM: (c.VARIABLE_NM ?? '').toString().toUpperCase()
      }))
    )
  }

  private clause(varName: string, formatted: string[]): QueryClause {
    return {
      GROUP_LOGIC: 'AND',
      SUBGROUP_LOGIC: 'AND',
      SUBGROUP_ID: 0,
      VARIABLE_NM: varName,
      OPERATOR_NM: formatted.length === 1 ? '=' : 'IN',
      RAW_VALUE:
        formatted.length === 1 ? formatted[0] : `(${formatted.join(',')})`
    }
  }

  /** The loaded column spec whose NAME matches varName (case-insensitive). */
  private findCol(varName: string, cols: any[]): any {
    return (cols || []).find(
      (x: any) =>
        (x?.NAME ?? '').toString().toUpperCase() === varName.toUpperCase()
    )
  }

  /**
   * SAS data-type kind of a column spec. DDTYPE carries
   * TIME/DATE/DATETIME in full, with C/N for CHARACTER/NUMERIC (legacy
   * responses may still carry the full strings); checked DATETIME-before-DATE
   * (substring).
   */
  private columnKind(col: any): VaColumnKind {
    const ddtype = (col?.DDTYPE ?? '').toString().toUpperCase()
    if (ddtype.includes('DATETIME')) return 'datetime'
    if (ddtype.includes('DATE')) return 'date'
    if (ddtype.includes('TIME')) return 'time'
    if (
      ddtype === 'N' ||
      ddtype.includes('NUMERIC') ||
      (col?.TYPE ?? '') === 'num'
    ) {
      return 'numeric'
    }
    return 'char'
  }

  /**
   * Day/month/year order declared by a column's SAS format, used to resolve
   * ambiguous numeric dates. Accepts either the base FMTNAME (e.g. `DDMMYY`) or
   * the full FORMAT (e.g. `DDMMYY10.`) — the leading name is the same and is all
   * that matters for order (width is irrelevant). This is the SOURCE column's
   * format; the VA report can in principle display a different one, so
   * value-based disambiguation (a group > 12 is the day) still takes precedence —
   * this only decides the genuinely-ambiguous case. `undefined` when the format
   * is absent, month-name (DATE*), or locale-dependent (NLDATE).
   */
  private dateOrderFromFmt(fmtname: any): 'YMD' | 'DMY' | 'MDY' | undefined {
    const f = (fmtname ?? '').toString().toUpperCase()
    if (/^MMDDYY/.test(f)) return 'MDY'
    if (/^(DDMMYY|EURDF)/.test(f)) return 'DMY'
    if (/^(YYMMDD|E8601DA|B8601DA)/.test(f)) return 'YMD'
    return undefined
  }

  /**
   * Infers a column's SAS kind from a VA value string (deferred load, no
   * metadata). VA's temporal strings are distinctive — DATE9 `ddMMMyyyy` and ISO
   * `yyyy-MM-dd`/`yyyy/MM/dd`, optionally with a `[ T:]HH:MM[:SS]` time part, and
   * `H:MM:SS` — so they're detected by shape; otherwise fall back to the VA
   * `dataType`.
   */
  private inferKindFromValue(value: string, dataType?: string): VaColumnKind {
    const monName = String.raw`\d{1,2}[A-Za-z]{3}\d{2,4}`
    const ymd = String.raw`\d{4}[-/.]\d{1,2}[-/.]\d{1,2}` // 4-digit year first
    const dmy = String.raw`\d{1,2}[-/.]\d{1,2}[-/.]\d{4}` // 4-digit year last
    const date = `${monName}|${ymd}|${dmy}`
    if (new RegExp(`^(?:${date})[ T:]\\d{1,2}:\\d{2}(:\\d{2})?$`).test(value)) {
      return 'datetime'
    }
    if (new RegExp(`^(?:${date})$`).test(value)) return 'date'
    if (/^\d{1,2}:\d{2}(:\d{2})?(\.\d+)?$/.test(value)) return 'time'
    if (dataType === 'number') return 'numeric'
    return 'char'
  }

  /**
   * Formats one VA filter value into a DC RAW_VALUE for the column kind, or null
   * when invalid (dropped). Matches DC's native filters, which send a SAS
   * literal for a temporal column and the raw value otherwise:
   *  - numeric  -> finite number, raw (else null - injection guard)
   *  - time     -> seconds since midnight, as a SAS time literal
   *                (VA: "0:00:02" -> '00:00:02't)
   *  - date     -> days since 1960-01-01, as a SAS date literal
   *                (VA: "01JUL1960" -> '01JUL1960'd)
   *  - datetime -> seconds since 1960-01-01, as a SAS datetime literal
   *                (VA: "01JAN1960:00:06:05" -> '01JAN1960:00:06:05'dt)
   *  - char     -> single-quote escaped
   * Temporal strings are regex-parsed (digits/letters/colons only), so a value
   * either yields a finite number or is dropped - also the injection guard.
   * Date/datetime reuse HelperService.convertJsDateToSasDate so the value is
   * byte-identical to what DC's own pickers produce.
   *
   * A temporal value that is already a bare number is the SAS internal value
   * itself (VA sends it unformatted when the data item carries no format / is
   * used as a measure), so it is passed through raw. This is unambiguous because
   * the only date formats in use (DATE9, ISO) always contain a letter or a
   * separator - a plain integer is never a formatted date.
   */
  private formatValueByKind(
    value: any,
    kind: VaColumnKind,
    order?: 'YMD' | 'DMY' | 'MDY'
  ): string | null {
    const s = String(value).trim()
    if (s === '') return null
    switch (kind) {
      case 'numeric': {
        const num = Number(s)
        return Number.isFinite(num) ? `${num}` : null
      }
      case 'time': {
        const secs = this.isBareNumber(s) ? Number(s) : this.timeToSeconds(s)
        return secs === null
          ? null
          : this.helperService.sasTemporalLiteral(secs, 'TIME')
      }
      case 'date': {
        let days: number | null = null
        if (this.isBareNumber(s)) days = Number(s)
        else {
          const d = this.parseDate(s, order)
          if (d) days = this.helperService.convertJsDateToSasDate(d, 'days')
        }
        return days === null
          ? null
          : this.helperService.sasTemporalLiteral(days, 'DATE')
      }
      case 'datetime': {
        let secs: number | null = null
        if (this.isBareNumber(s)) secs = Number(s)
        else {
          const d = this.parseDatetime(s, order)
          if (d) secs = this.helperService.convertJsDateToSasDate(d, 'seconds')
        }
        return secs === null
          ? null
          : this.helperService.sasTemporalLiteral(secs, 'DATETIME')
      }
      default:
        return `'${s.replace(/'/g, "''")}'`
    }
  }

  /** "H:MM[:SS][.f]" -> seconds since midnight, or null if not a valid time. */
  private timeToSeconds(value: string): number | null {
    if (!/^\d{1,2}:\d{2}(:\d{2})?(\.\d+)?$/.test(value)) return null
    const p = value.split('.')[0].split(':')
    const h = Number(p[0])
    const m = Number(p[1])
    const sec = p.length === 3 ? Number(p[2]) : 0
    if (![h, m, sec].every(Number.isFinite)) return null
    return h * 3600 + m * 60 + sec
  }

  /**
   * VA date string -> local JS Date, or null. VA sends no format metadata, so the
   * shape is sniffed. Accepted forms (separator may be `-`, `/`, or `.`):
   *  - DATE9    "ddMMM[yy]yy" (e.g. 01AUG1962)
   *  - ISO/YMD  "yyyy-MM-dd" (4-digit year first, e.g. 2025-03-31)
   *  - year-last "dd/mm/yyyy" or "mm/dd/yyyy" (4-digit year last)
   *
   * Day/month order for the year-last form is resolved in priority order:
   *   1. by value  — a group > 12 must be the day (ground truth);
   *   2. by `order` — the column's declared FMTNAME order (DMY/MDY/YMD);
   *   3. default DAY-FIRST — never US month-first — matching SAS's DDMMYY
   *      default and non-US convention.
   * `new Date(y, m-1, d)` is local, matching HelperService.convertJsDateToSasDate
   * so the value is byte-identical to DC's own pickers. The constructed date is
   * round-trip validated to reject impossible values (e.g. 31/02) rather than
   * silently roll over.
   */
  private parseDate(value: string, order?: 'YMD' | 'DMY' | 'MDY'): Date | null {
    return this.parseMonthNameDate(value) ?? this.parseNumericDate(value, order)
  }

  /** DATE9 "ddMMM[yy]yy" (e.g. 01AUG1962, 01AUG62) -> local Date, or null. */
  private parseMonthNameDate(value: string): Date | null {
    const m = /^(\d{1,2})([A-Za-z]{3})(\d{2}|\d{4})$/.exec(value)
    if (!m) return null
    const [, day, monthAbbr, year] = m
    const monthIndex = VaFilterService.SAS_MONTHS.indexOf(
      monthAbbr.toUpperCase()
    )
    if (monthIndex < 0) return null
    return this.buildDate(this.fullYear(year), monthIndex + 1, Number(day))
  }

  /**
   * Numeric date with `-`, `/`, or `.` separators -> local Date, or null:
   *  - year-first "yyyy-MM-dd"  -> YMD directly;
   *  - year-last  "dd/mm/yyyy" or "mm/dd/yyyy" -> day/month resolved by
   *    resolveDayMonth (value, then column order, then day-first default).
   * A 4-digit year (leading or trailing) is required to anchor the parse;
   * an all-2-digit value (e.g. "03/04/05") is too ambiguous and is dropped.
   */
  private parseNumericDate(
    value: string,
    order?: 'YMD' | 'DMY' | 'MDY'
  ): Date | null {
    const m = /^(\d{1,4})[-/.](\d{1,2})[-/.](\d{1,4})$/.exec(value)
    if (!m) return null
    const [, first, middle, last] = m
    const yearIsFirst = first.length === 4
    const yearIsLast = last.length === 4

    if (yearIsFirst) {
      return this.buildDate(Number(first), Number(middle), Number(last))
    }
    if (!yearIsLast) return null

    const { day, month } = this.resolveDayMonth(
      Number(first),
      Number(middle),
      order
    )
    return this.buildDate(Number(last), month, day)
  }

  /**
   * Resolves the day/month of a year-last date whose two leading groups (`a`,
   * `b`) are in unknown order, in priority:
   *   1. by value  — a group > 12 must be the day (ground truth);
   *   2. by `order` — only MDY flips to month-first; DMY/YMD keep day-first;
   *   3. default   — DAY-first (`a` is the day), never US month-first.
   */
  private resolveDayMonth(
    a: number,
    b: number,
    order?: 'YMD' | 'DMY' | 'MDY'
  ): { day: number; month: number } {
    if (a > 12 && b <= 12) return { day: a, month: b }
    if (b > 12 && a <= 12) return { day: b, month: a }
    if (order === 'MDY') return { day: b, month: a }
    return { day: a, month: b }
  }

  /** Local Date from 1-based month, round-trip validated (null if impossible). */
  private buildDate(
    year: number,
    month: number,
    day: number,
    hours = 0,
    minutes = 0,
    seconds = 0
  ): Date | null {
    if (month < 1 || month > 12 || day < 1 || day > 31) return null
    const d = new Date(year, month - 1, day, hours, minutes, seconds, 0)
    // Reject rollovers (e.g. 31 Feb -> 3 Mar) so a bad value is dropped, not
    // silently converted to the wrong SAS date.
    if (
      d.getFullYear() !== year ||
      d.getMonth() !== month - 1 ||
      d.getDate() !== day
    ) {
      return null
    }
    return d
  }

  /**
   * VA datetime string -> local JS Date, or null. The date part is any shape
   * accepted by parseDate; the time part is "HH:MM[:SS]". They are joined by the
   * SAS ":" separator (e.g. 01JAN1960:05:13:03), an ISO "T", or a space
   * (e.g. 2025-03-31T05:13:03, 2025-03-31 05:13:03).
   */
  private parseDatetime(
    value: string,
    order?: 'YMD' | 'DMY' | 'MDY'
  ): Date | null {
    const m = /^(.+?)[ T:](\d{1,2}:\d{2}(?::\d{2})?)$/.exec(value)
    if (!m) return null
    const date = this.parseDate(m[1], order)
    if (!date) return null
    const t = m[2].split(':')
    date.setHours(Number(t[0]), Number(t[1]), Number(t[2] || 0), 0)
    return date
  }

  /** True when the value is a bare (already-internal) SAS number: optional sign,
   * digits, optional fraction. No letters/separators — so never a formatted date. */
  private isBareNumber(value: string): boolean {
    return /^-?\d+(\.\d+)?$/.test(value) && Number.isFinite(Number(value))
  }

  /** 2-digit SAS year -> 4-digit (SAS default cutoff: 00-19 -> 20xx, else 19xx). */
  private fullYear(year: string): number {
    if (year.length === 4) return Number(year)
    const y = Number(year)
    return y <= 19 ? 2000 + y : 1900 + y
  }
}