import R from 'ramda';
import moment from 'moment-timezone';

import { getEnv, QueryAlias, parseSqlInterval } from '@cubejs-backend/shared';
import { BaseQuery } from './BaseQuery';
import { BaseFilter } from './BaseFilter';
import { BaseSegment } from './BaseSegment';
import { ParamAllocator } from './ParamAllocator';
import { resolveWindowsTimezone } from './windows-iana';

const abbrs = {
  EST: 'Eastern Standard Time',
  EDT: 'Eastern Standard Time',
  CST: 'Central Standard Time',
  CDT: 'Central Standard Time',
  MST: 'Mountain Standard Time',
  MDT: 'Mountain Standard Time',
  PST: 'Pacific Standard Time',
  PDT: 'Pacific Standard Time',
};

moment.fn.zoneName = () => {
  // @ts-ignore
  const abbr = this.zoneAbbr();
  return abbrs[abbr] || abbr;
};

class MssqlParamAllocator extends ParamAllocator {
  public paramPlaceHolder(paramIndex) {
    return `@_${paramIndex + 1}`;
  }
}

const GRANULARITY_TO_INTERVAL = {
  day: (date) => `dateadd(day, DATEDIFF(day, 0, ${date}), 0)`,
  week: (date) => `dateadd(week, DATEDIFF(week, 0, ${date}), 0)`,
  hour: (date) => `dateadd(hour, DATEDIFF(hour, 0, ${date}), 0)`,
  minute: (date) => `dateadd(minute, DATEDIFF(minute, 0, ${date}), 0)`,
  second: (date) => `CAST(FORMAT(${date}, 'yyyy-MM-ddTHH:mm:ss.000') AS DATETIME2)`, // until SQL 2016, this causes an int overflow; in SQL 2016 these calls can be changed to DATEDIFF_BIG
  month: (date) => `dateadd(month, DATEDIFF(month, 0, ${date}), 0)`,
  quarter: (date) => `dateadd(quarter, DATEDIFF(quarter, 0, ${date}), 0)`,
  year: (date) => `dateadd(year, DATEDIFF(year, 0, ${date}), 0)`,
};

class MssqlFilter extends BaseFilter {
  // noinspection JSMethodCanBeStatic
  public escapeWildcardChars(param) {
    return typeof param === 'string' ? param.replace(/([_%])/gi, '[$1]') : param;
  }

  public likeIgnoreCase(column, not, param, type) {
    const p = (!type || type === 'contains' || type === 'ends') ? '%' : '';
    const s = (!type || type === 'contains' || type === 'starts') ? '%' : '';
    return `LOWER(${column})${not ? ' NOT' : ''} LIKE CONCAT('${p}', LOWER(${this.allocateParam(param)}) , '${s}')`;
  }
}

class MssqlSegment extends BaseSegment {
  public filterToWhere(): string {
    const where = super.filterToWhere();

    const context = this.query.safeEvaluateSymbolContext();
    if (context.rollupQuery) {
      // Segment itself will be rendered as reference for rollupQuery
      // In MSSQL using just `WHERE (segment_column) AND (other_filter)` is incorrect, because
      // `segment_column` is not of boolean type, but of `BIT` type
      // Correct way to work with them is to use `WHERE segment_column = 1`
      // This relies on `wrapSegmentForDimensionSelect` mapping segment to a `BIT` data type
      return `${where} = 1`;
    }

    return where;
  }
}

export class MssqlQuery extends BaseQuery {
  private readonly useNamedTimezones: boolean;

  public constructor(compilers: any, options: any) {
    super(compilers, options);

    this.useNamedTimezones = getEnv('mssqlUseNamedTimezones', { dataSource: this.dataSource });
  }

  public newFilter(filter) {
    return new MssqlFilter(this, filter);
  }

  public newSegment(segment): BaseSegment {
    return new MssqlSegment(this, segment);
  }

  public castToString(sql) {
    return `CAST(${sql} as VARCHAR)`;
  }

  public concatStringsSql(strings: string[]) {
    return strings.join(' + ');
  }

  public convertTz(field) {
    if (this.useNamedTimezones) {
      const windowsTz = resolveWindowsTimezone(this.timezone);
      return `CAST(${field} AT TIME ZONE 'UTC' AT TIME ZONE '${windowsTz}' AS DATETIME2)`;
    }

    const offset = moment().tz(this.timezone).format('Z');

    // 1. Treating the field as UTC (add '+00:00' offset)
    // 2. Switch to target timezone offset
    // 3. Cast to DATETIME2 to get naive timestamp in target timezone
    return `CAST(SWITCHOFFSET(TODATETIMEOFFSET(${field}, '+00:00'), '${offset}') AS DATETIME2)`;
  }

  public timeStampCast(value: string) {
    return `CAST(${value} AS DATETIMEOFFSET)`;
  }

  public dateTimeCast(value: string) {
    return `CAST(${value} AS DATETIME2)`;
  }

  public timeGroupedColumn(granularity: string, dimension: string): string {
    return GRANULARITY_TO_INTERVAL[granularity](dimension);
  }

  /**
   * Returns SQL for source expression floored to timestamps aligned with
   * intervals relative to the origin timestamp point.
   * The formula operates with seconds diffs, so it won't produce human-expected dates aligned with offset date parts.
   */
  public dateBin(interval: string, source: string, origin: string): string {
    // Both source and origin are now DATETIME2 in the query timezone (naive timestamps),
    // ensuring their in the same timezone context for correct date bin calculation.
    const originAligned = this.dateTimeCast(`'${origin}'`);
    const beginOfTime = this.dateTimeCast('DATEFROMPARTS(1970, 1, 1)');
    const timeUnit = this.diffTimeUnitForInterval(interval);

    // Need to explicitly cast one argument of floor to float to trigger correct sign logic
    return `DATEADD(${timeUnit},
        FLOOR(
          CAST(DATEDIFF(${timeUnit}, ${originAligned}, ${source}) AS FLOAT) /
          DATEDIFF(${timeUnit}, ${beginOfTime}, ${this.addInterval(beginOfTime, interval)})
        ) * DATEDIFF(${timeUnit}, ${beginOfTime}, ${this.addInterval(beginOfTime, interval)}),
        ${originAligned}
    )`;
  }

  public newParamAllocator(expressionParams) {
    return new MssqlParamAllocator(expressionParams);
  }

  // TODO replace with limitOffsetClause override
  public groupByDimensionLimit() {
    if (this.rowLimit) {
      return this.offset ? ` OFFSET ${parseInt(this.offset, 10)} ROWS FETCH NEXT ${parseInt(this.rowLimit, 10)} ROWS ONLY` : '';
    } else {
      return this.offset ? ` OFFSET ${parseInt(this.offset, 10)} ROWS` : '';
    }
  }

  public topLimit() {
    if (this.offset) {
      return '';
    }
    return this.rowLimit === null ? '' : ` TOP ${this.rowLimit && parseInt(this.rowLimit, 10) || 10000}`;
  }

  /**
   * Overrides `BaseQuery#groupByClause` method and returns `GROUP BY` clause
   * with the column names instead of column numeric sequences as MSSQL does
   * not support this format.
   * @returns {string}
   * @override
   */
  public groupByClause() {
    if (this.ungrouped) {
      return '';
    }
    const dimensionsForSelect = this.dimensionsForSelect();
    const dimensionColumns = R.flatten(
      dimensionsForSelect.map(s => s.selectColumns() && s.dimensionSql())
    ).filter(s => !!s);
    return dimensionColumns.length ? ` GROUP BY ${dimensionColumns.join(', ')}` : '';
  }

  /**
   * Overrides `BaseQuery#aggregateSubQueryGroupByClause` method and returns
   * `GROUP BY` clause for the "aggregating on top of sub-queries" uses cases.
   * @returns {string}
   * @override
   */
  public aggregateSubQueryGroupByClause() {
    const dimensionColumns = this.dimensionColumns(this.escapeColumnName(QueryAlias.AGG_SUB_QUERY_KEYS));
    return dimensionColumns.length ? ` GROUP BY ${dimensionColumns.join(', ')}` : '';
  }

  public overTimeSeriesSelect(cumulativeMeasures, dateSeriesSql, baseQuery, dateJoinConditionSql, baseQueryAlias) {
    // Group by time dimensions
    const timeDimensionsColumns = this.timeDimensions.map(
      (t) => `${t.dateSeriesAliasName()}.${this.escapeColumnName('date_from')}`
    );

    // Group by regular dimensions
    const dimensionColumns = R.flatten(
      this.dimensions.map(s => s.selectColumns() && s.dimensionSql() && s.aliasName())
    ).filter(s => !!s);

    // Combine time dimensions and regular dimensions for GROUP BY clause
    const allGroupByColumns = timeDimensionsColumns.concat(dimensionColumns);

    const forSelect = this.overTimeSeriesForSelect(cumulativeMeasures);
    return (
      `SELECT ${forSelect} FROM ${dateSeriesSql}` +
      ` LEFT JOIN (${baseQuery}) ${this.asSyntaxJoin} ${baseQueryAlias} ON ${dateJoinConditionSql}` +
      ` GROUP BY ${allGroupByColumns.join(', ')}`
    );
  }

  public nowTimestampSql() {
    return 'CURRENT_TIMESTAMP';
  }

  public unixTimestampSql() {
    // eslint-disable-next-line quotes
    return `DATEDIFF(SECOND,'1970-01-01', GETUTCDATE())`;
  }

  public preAggregationLoadSql(cube, preAggregation, tableName) {
    const sqlAndParams = this.preAggregationSql(cube, preAggregation);
    return [`SELECT * INTO ${tableName} FROM (${sqlAndParams[0]}) AS PreAggregation`, sqlAndParams[1]];
  }

  public wrapSegmentForDimensionSelect(sql) {
    return `CAST((CASE WHEN ${sql} THEN 1 ELSE 0 END) AS BIT)`;
  }

  public seriesSql(timeDimension) {
    const values = timeDimension.timeSeries().map(([from, to]) => `('${from}', '${to}')`);
    return `SELECT ${this.dateTimeCast('date_from')} date_from, ${this.dateTimeCast(
      'date_to'
    )} date_to FROM (VALUES ${values}) ${this.asSyntaxTable} dates (date_from, date_to)`;
  }

  public subtractInterval(date: string, interval: string): string {
    const intervalParsed = parseSqlInterval(interval);
    let res = date;

    for (const [key, value] of Object.entries(intervalParsed)) {
      res = `DATEADD(${key}, ${value * -1}, ${res})`;
    }

    return res;
  }

  public addInterval(date: string, interval: string): string {
    const intervalParsed = parseSqlInterval(interval);
    let res = date;

    for (const [key, value] of Object.entries(intervalParsed)) {
      res = `DATEADD(${key}, ${value}, ${res})`;
    }

    return res;
  }

  public sqlTemplates() {
    const templates = super.sqlTemplates();
    templates.functions.LEAST = 'LEAST({{ args_concat }})';
    templates.functions.GREATEST = 'GREATEST({{ args_concat }})';
    templates.functions.UTCTIMESTAMP = 'GETUTCDATE()';
    // MSSQL ROUND requires 2 arguments: ROUND(number, length)
    templates.functions.ROUND = 'ROUND({{ args_concat }}{% if args | length < 2 %}, 0{% endif %})';
    // DATEADD is being rewritten to DATE_ADD
    templates.functions.DATE_ADD = 'DATEADD({{ date_part }}, {{ interval }}, {{ args[0] }})';
    // NOTE: MSSQL does not support DISTINCT clause. No workaround is available
    delete templates.functions.STRING_AGG;
    // PERCENTILE_CONT works but requires PARTITION BY
    delete templates.functions.PERCENTILECONT;
    delete templates.functions.WIDTH_BUCKET;
    templates.expressions.like = '{{ expr }} {% if negated %}NOT {% endif %}LIKE {{ pattern }}{% if default_escape %} ESCAPE \'\\\'{% endif %}';
    delete templates.expressions.ilike;
    // MSSQL uses + for string concatenation instead of ||
    templates.expressions.concat_strings = '{{ strings | join(\' + \' ) }}';
    // NOTE: this template contains a comma; two order expressions are being generated
    templates.expressions.sort = '{{ expr }} IS NULL {% if nulls_first %}DESC{% else %}ASC{% endif %}, {{ expr }} {% if asc %}ASC{% else %}DESC{% endif %}';
    // Timestamp constants arrive as ISO-8601 UTC strings ('2021-01-01T00:00:00.000Z');
    // CONVERT style 127 is defined as exactly this format (yyyy-mm-ddThh:mi:ss.mmmZ,
    // "ISO8601 with time zone Z"). The base template renders the value bare, which is
    // invalid T-SQL syntax
    templates.expressions.timestamp_literal = 'CONVERT(DATETIME2, \'{{ value }}\', 127)';
    templates.types.string = 'VARCHAR';
    templates.types.boolean = 'BIT';
    templates.types.integer = 'INT';
    templates.types.float = 'FLOAT(24)';
    templates.types.double = 'FLOAT(53)';
    templates.types.timestamp = 'DATETIME2';
    delete templates.types.interval;
    templates.types.binary = 'VARBINARY';
    templates.params.param = '@_{{ param_index + 1 }}';
    // MSSQL does not support ordinal GROUP BY (GROUP BY 1, 2), must use expressions
    templates.statements.group_by_exprs = '{{ group_by | map(attribute=\'expr\') | join(\', \') }}';
    // MSSQL does not support ordinal ORDER BY (ORDER BY 1, 2), must use expressions
    templates.expressions.order_by = '{{ expr }} {% if asc %}ASC{% else %}DESC{% endif %}';
    // MSSQL uses CAST(...AS DATETIME2) instead of ::timestamp
    templates.statements.time_series_select = 'SELECT CAST(date_from AS DATETIME2) AS "date_from",\n' +
      'CAST(date_to AS DATETIME2) AS "date_to" \n' +
      'FROM(\n' +
      '    VALUES ' +
      '{% for time_item in seria  %}' +
      '(\'{{ time_item[0] }}\', \'{{ time_item[1] }}\')' +
      '{% if not loop.last %}, {% endif %}' +
      '{% endfor %}' +
      ') AS dates (date_from, date_to)';
    // MSSQL uses recursive CTE for time series generation.
    // The template body becomes content of `time_series AS (...)` CTE,
    // so it self-references `time_series` for recursion.
    templates.statements.generated_time_series_select =
      'SELECT CAST({{ start }} AS DATETIME2) AS date_from,\n' +
      '       DATEADD(MILLISECOND, -1, DATEADD({{ minimal_time_unit }}, 1, CAST({{ start }} AS DATETIME2))) AS date_to\n' +
      'UNION ALL\n' +
      'SELECT DATEADD({{ minimal_time_unit }}, 1, date_from),\n' +
      '       DATEADD(MILLISECOND, -1, DATEADD({{ minimal_time_unit }}, 1, DATEADD({{ minimal_time_unit }}, 1, date_from)))\n' +
      'FROM time_series\n' +
      'WHERE DATEADD({{ minimal_time_unit }}, 1, date_from) <= CAST({{ end }} AS DATETIME2)';

    templates.statements.generated_time_series_with_cte_range_source =
      'SELECT {{ range_source }}.{{ min_name }} AS date_from,\n' +
      '       DATEADD(MILLISECOND, -1, DATEADD({{ minimal_time_unit }}, 1, {{ range_source }}.{{ min_name }})) AS date_to,\n' +
      '       {{ range_source }}.{{ max_name }} AS max_date\n' +
      'FROM {{ range_source }}\n' +
      'UNION ALL\n' +
      'SELECT DATEADD({{ minimal_time_unit }}, 1, date_from),\n' +
      '       DATEADD(MILLISECOND, -1, DATEADD({{ minimal_time_unit }}, 1, DATEADD({{ minimal_time_unit }}, 1, date_from))),\n' +
      '       max_date\n' +
      'FROM time_series\n' +
      'WHERE DATEADD({{ minimal_time_unit }}, 1, date_from) <= max_date';

    // MSSQL uses OFFSET/FETCH instead of LIMIT/OFFSET
    // T-SQL has no default LIKE escape character, so the escaping the planner
    // applies to the value (see BaseQuery's `like_escape_char`) only takes
    // effect with an explicit clause. It goes on the predicate rather than in
    // `like_pattern` because the pattern is wrapped in LOWER(...) here.
    templates.tesseract.ilike = 'LOWER({{ expr }}) {% if negated %}NOT {% endif %}LIKE LOWER({{ pattern }}) ESCAPE \'\\\'';
    templates.filters.like_pattern = 'CONCAT({% if start_wild %}\'%\'{% else %}\'\'{% endif %}, LOWER({{ value }}), {% if end_wild %}\'%\'{% else %}\'\'{% endif %})';
    templates.statements.select = '{% if ctes %} WITH \n' +
      '{{ ctes | join(\',\n\') }}\n' +
      '{% endif %}' +
      'SELECT {% if limit is not none and not order_by %}TOP {{ limit }} {% endif %}{% if distinct %}DISTINCT {% endif %}' +
      '{{ select_concat | map(attribute=\'aliased\') | join(\', \') }} {% if from %}\n' +
      'FROM (\n' +
      '{{ from | indent(2, true) }}\n' +
      ') AS {{ from_alias }}{% elif from_prepared %}\n' +
      'FROM {{ from_prepared }}' +
      '{% endif %}' +
      '{% if filter %}\nWHERE {{ filter }}{% endif %}' +
      '{% if group_by %}\nGROUP BY {{ group_by }}{% endif %}' +
      '{% if having %}\nHAVING {{ having }}{% endif %}' +
      '{% if order_by %}\nORDER BY {{ order_by | map(attribute=\'expr\') | join(\', \') }}\nOFFSET {% if offset is not none %}{{ offset }}{% else %}0{% endif %} ROWS' +
      '\nFETCH NEXT {% if limit is not none %}{{ limit }}{% else %}2147483647{% endif %} ROWS ONLY{% endif %}' +
      '{% if ctes %}\nOPTION (MAXRECURSION 0){% endif %}';
    // MSSQL has no boolean type — a segment projected as a dimension must be a BIT.
    templates.expressions.wrap_segment_select = 'CAST((CASE WHEN {{ expr }} THEN 1 ELSE 0 END) AS BIT)';
    // Reading a segment back from a pre-aggregation: it is a stored BIT column,
    // which MSSQL can't use as a bare predicate, so compare it explicitly.
    templates.expressions.wrap_segment_filter = '{{ expr }} = 1';
    return templates;
  }
}
