import { RelationalTable, QueryResult, PivotConfig, PivotResult, PivotCell } from '../types/database';

/**
 * In-Memory Relational SQL Execution Engine
 * Supports SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET, Aggregations
 */
export class SqlEngine {
  private tables: Map<string, RelationalTable> = new Map();

  constructor(tables?: RelationalTable[]) {
    if (tables) {
      tables.forEach(t => this.registerTable(t));
    }
  }

  public registerTable(table: RelationalTable): void {
    this.tables.set(table.name.toLowerCase(), table);
  }

  public getTables(): RelationalTable[] {
    return Array.from(this.tables.values());
  }

  public getTable(name: string): RelationalTable | undefined {
    return this.tables.get(name.toLowerCase());
  }

  public execute(sqlQuery: string): QueryResult {
    const startTime = performance.now();
    const cleanSql = sqlQuery.trim().replace(/;+$/, '');

    try {
      if (!cleanSql) {
        return {
          columns: [],
          rows: [],
          rowCount: 0,
          executionTimeMs: 0,
          query: sqlQuery,
          error: 'Empty SQL query.',
        };
      }

      // Check if it's a SELECT statement
      if (!cleanSql.toLowerCase().startsWith('select')) {
        // Handle CREATE VIEW / CREATE TABLE AS SELECT
        const createMatch = cleanSql.match(/^create\s+(?:view|table)\s+([a-zA-Z0-9_]+)\s+as\s+(select[\s\S]+)$/i);
        if (createMatch) {
          const tableName = createMatch[1];
          const innerSelect = createMatch[2];
          const innerResult = this.execute(innerSelect);
          if (innerResult.error) return innerResult;

          const newTable: RelationalTable = {
            id: `custom_${Date.now()}`,
            name: tableName,
            displayName: `Custom View (${tableName})`,
            description: `Generated from query: ${innerSelect.substring(0, 40)}...`,
            rowCount: innerResult.rowCount,
            columns: innerResult.columns.map(c => ({
              name: c,
              type: typeof (innerResult.rows[0]?.[c]) === 'number' ? 'number' : typeof (innerResult.rows[0]?.[c]) === 'boolean' ? 'boolean' : 'string',
              description: `Derived column ${c}`,
            })),
            rows: innerResult.rows,
          };
          this.registerTable(newTable);

          const endTime = performance.now();
          return {
            columns: ['status', 'table_name', 'rows_created'],
            rows: [{ status: 'SUCCESS', table_name: tableName, rows_created: innerResult.rowCount }],
            rowCount: 1,
            executionTimeMs: Number((endTime - startTime).toFixed(2)),
            query: sqlQuery,
            queryPlan: `CREATE VIEW ${tableName} -> ${innerResult.rowCount} rows materialized`,
          };
        }

        return {
          columns: [],
          rows: [],
          rowCount: 0,
          executionTimeMs: 0,
          query: sqlQuery,
          error: 'Only SELECT queries (and CREATE VIEW/TABLE ... AS SELECT) are supported.',
        };
      }

      const parsed = this.parseQuery(cleanSql);
      const executionPlan = this.buildPlan(parsed);
      const resultRows = this.executePlan(parsed);

      const endTime = performance.now();
      const elapsed = Number((endTime - startTime).toFixed(2));

      const columns = resultRows.length > 0 ? Object.keys(resultRows[0]) : (parsed.selectFields.map(f => f.alias || f.raw) || []);

      return {
        columns,
        rows: resultRows,
        rowCount: resultRows.length,
        executionTimeMs: elapsed,
        query: sqlQuery,
        queryPlan: executionPlan,
      };
    } catch (err: any) {
      const endTime = performance.now();
      return {
        columns: [],
        rows: [],
        rowCount: 0,
        executionTimeMs: Number((endTime - startTime).toFixed(2)),
        query: sqlQuery,
        error: err.message || 'SQL Execution Error',
      };
    }
  }

  private parseQuery(sql: string): ParsedQuery {
    // Regex tokenize SQL clauses
    // Format: SELECT ... FROM ... [JOIN ... ON ...] [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...] [LIMIT ... [OFFSET ...]]
    const selectMatch = sql.match(/select\s+([\s\S]+?)\s+from\s+/i);
    if (!selectMatch) {
      throw new Error("Syntax Error: Missing 'SELECT' or 'FROM' clause.");
    }
    const selectStr = selectMatch[1].trim();

    const afterFrom = sql.substring(selectMatch[0].length);
    
    // Extract main clauses
    const whereIdx = this.findKeywordIndex(afterFrom, 'WHERE');
    const groupByIdx = this.findKeywordIndex(afterFrom, 'GROUP BY');
    const havingIdx = this.findKeywordIndex(afterFrom, 'HAVING');
    const orderByIdx = this.findKeywordIndex(afterFrom, 'ORDER BY');
    const limitIdx = this.findKeywordIndex(afterFrom, 'LIMIT');

    // From / Join segment
    let fromSegmentEnd = afterFrom.length;
    [whereIdx, groupByIdx, havingIdx, orderByIdx, limitIdx].forEach(idx => {
      if (idx !== -1 && idx < fromSegmentEnd) fromSegmentEnd = idx;
    });
    const fromSegment = afterFrom.substring(0, fromSegmentEnd).trim();

    // Parse From & Joins
    const { primaryTable, joins } = this.parseFromAndJoins(fromSegment);

    // Parse WHERE
    let whereStr: string | undefined;
    if (whereIdx !== -1) {
      let whereEnd = afterFrom.length;
      [groupByIdx, havingIdx, orderByIdx, limitIdx].forEach(idx => {
        if (idx !== -1 && idx > whereIdx && idx < whereEnd) whereEnd = idx;
      });
      whereStr = afterFrom.substring(whereIdx + 5, whereEnd).trim();
    }

    // Parse GROUP BY
    let groupByStr: string | undefined;
    if (groupByIdx !== -1) {
      let gbEnd = afterFrom.length;
      [havingIdx, orderByIdx, limitIdx].forEach(idx => {
        if (idx !== -1 && idx > groupByIdx && idx < gbEnd) gbEnd = idx;
      });
      groupByStr = afterFrom.substring(groupByIdx + 8, gbEnd).trim();
    }

    // Parse HAVING
    let havingStr: string | undefined;
    if (havingIdx !== -1) {
      let hEnd = afterFrom.length;
      [orderByIdx, limitIdx].forEach(idx => {
        if (idx !== -1 && idx > havingIdx && idx < hEnd) hEnd = idx;
      });
      havingStr = afterFrom.substring(havingIdx + 6, hEnd).trim();
    }

    // Parse ORDER BY
    let orderByStr: string | undefined;
    if (orderByIdx !== -1) {
      let obEnd = afterFrom.length;
      if (limitIdx !== -1 && limitIdx > orderByIdx && limitIdx < obEnd) obEnd = limitIdx;
      orderByStr = afterFrom.substring(orderByIdx + 8, obEnd).trim();
    }

    // Parse LIMIT & OFFSET
    let limitVal: number | undefined;
    let offsetVal: number | undefined;
    if (limitIdx !== -1) {
      const limitSegment = afterFrom.substring(limitIdx + 5).trim();
      const limitMatch = limitSegment.match(/^(\d+)(?:\s+offset\s+(\d+))?/i);
      if (limitMatch) {
        limitVal = parseInt(limitMatch[1], 10);
        if (limitMatch[2]) offsetVal = parseInt(limitMatch[2], 10);
      }
    }

    // Parse SELECT expressions
    const selectFields = this.parseSelectFields(selectStr);

    return {
      selectFields,
      isSelectAll: selectStr === '*' || selectFields.some(f => f.raw === '*'),
      primaryTable,
      joins,
      whereClause: whereStr,
      groupByFields: groupByStr ? groupByStr.split(',').map(s => s.trim()) : undefined,
      havingClause: havingStr,
      orderBy: orderByStr ? this.parseOrderBy(orderByStr) : undefined,
      limit: limitVal,
      offset: offsetVal,
    };
  }

  private findKeywordIndex(str: string, keyword: string): number {
    const regex = new RegExp(`\\b${keyword}\\b`, 'i');
    const match = str.match(regex);
    return match ? match.index! : -1;
  }

  private parseFromAndJoins(segment: string): { primaryTable: TableRef; joins: JoinClause[] } {
    // Splits on [INNER|LEFT|RIGHT|FULL] JOIN
    const joinRegex = /\b(inner\s+join|left\s+join|join)\b/gi;
    const parts = segment.split(joinRegex);

    const primaryPart = parts[0].trim();
    const primaryTable = this.parseTableAlias(primaryPart);

    const joins: JoinClause[] = [];
    for (let i = 1; i < parts.length; i += 2) {
      const joinTypeStr = parts[i].trim().toLowerCase();
      const joinBody = parts[i + 1]?.trim() || '';

      const onIdx = this.findKeywordIndex(joinBody, 'ON');
      if (onIdx === -1) {
        throw new Error(`Syntax Error: Missing 'ON' condition for JOIN '${joinBody}'`);
      }

      const tablePart = joinBody.substring(0, onIdx).trim();
      const onCondition = joinBody.substring(onIdx + 2).trim();

      const joinTable = this.parseTableAlias(tablePart);
      joins.push({
        type: joinTypeStr.includes('left') ? 'LEFT' : 'INNER',
        table: joinTable,
        onCondition,
      });
    }

    return { primaryTable, joins };
  }

  private parseTableAlias(str: string): TableRef {
    const parts = str.trim().split(/\s+(?:as\s+)?/i);
    return {
      tableName: parts[0],
      alias: parts[1] || parts[0],
    };
  }

  private parseSelectFields(selectStr: string): SelectField[] {
    const fields: SelectField[] = [];
    const splitRegex = /,(?![^\(]*\))/g;
    const parts = selectStr.split(splitRegex);

    for (const part of parts) {
      const trimmed = part.trim();
      if (!trimmed) continue;

      if (trimmed === '*') {
        fields.push({ raw: '*', field: '*' });
        continue;
      }

      // Check AS alias
      const asMatch = trimmed.match(/^([\s\S]+?)\s+as\s+([a-zA-Z0-9_]+)$/i);
      let expr = trimmed;
      let alias: string | undefined;

      if (asMatch) {
        expr = asMatch[1].trim();
        alias = asMatch[2].trim();
      } else {
        // Space-based alias: expr alias
        const spaceMatch = trimmed.match(/^([^\s]+)\s+([a-zA-Z0-9_]+)$/);
        if (spaceMatch && !spaceMatch[1].endsWith(')')) {
          expr = spaceMatch[1];
          alias = spaceMatch[2];
        }
      }

      // Check Aggregate Function: SUM(x), COUNT(*), AVG(x), MIN(x), MAX(x), ROUND(x, 2)
      const aggMatch = expr.match(/^(COUNT|SUM|AVG|MIN|MAX|ROUND|UPPER|LOWER)\s*\(\s*(distinct\s+)?([\s\S]*?)\s*\)$/i);
      if (aggMatch) {
        const funcName = aggMatch[1].toUpperCase() as any;
        const isDistinct = !!aggMatch[2];
        const innerArg = aggMatch[3].trim();
        fields.push({
          raw: trimmed,
          aggregate: funcName,
          isDistinct,
          field: innerArg,
          alias: alias || `${funcName.toLowerCase()}_${innerArg.replace(/[^a-zA-Z0-9_]/g, '')}`,
        });
      } else {
        fields.push({
          raw: trimmed,
          field: expr,
          alias: alias || expr.replace(/^[a-zA-Z0-9_]+\./, ''),
        });
      }
    }

    return fields;
  }

  private parseOrderBy(str: string): OrderByClause[] {
    const parts = str.split(',');
    return parts.map(p => {
      const sub = p.trim().split(/\s+/);
      const field = sub[0];
      const direction = sub[1] && sub[1].toLowerCase() === 'desc' ? 'DESC' : 'ASC';
      return { field, direction };
    });
  }

  private buildPlan(query: ParsedQuery): string {
    const lines: string[] = [];
    lines.push(`1. SCAN table '${query.primaryTable.tableName}' (alias: ${query.primaryTable.alias})`);
    if (query.joins.length > 0) {
      query.joins.forEach(j => {
        lines.push(`2. ${j.type} JOIN '${j.table.tableName}' (alias: ${j.table.alias}) ON ${j.onCondition}`);
      });
    }
    if (query.whereClause) {
      lines.push(`3. FILTER WHERE ${query.whereClause}`);
    }
    if (query.groupByFields) {
      lines.push(`4. GROUP BY [${query.groupByFields.join(', ')}] with AGGREGATION`);
    }
    if (query.havingClause) {
      lines.push(`5. FILTER HAVING ${query.havingClause}`);
    }
    if (query.orderBy) {
      lines.push(`6. SORT BY ${query.orderBy.map(o => `${o.field} ${o.direction}`).join(', ')}`);
    }
    if (query.limit !== undefined) {
      lines.push(`7. LIMIT ${query.limit}${query.offset ? ` OFFSET ${query.offset}` : ''}`);
    }
    return lines.join(' -> ');
  }

  private executePlan(query: ParsedQuery): Record<string, any>[] {
    const primaryTable = this.tables.get(query.primaryTable.tableName.toLowerCase());
    if (!primaryTable) {
      throw new Error(`Table '${query.primaryTable.tableName}' not found in workbook database.`);
    }

    // 1. Initial Dataset with Alias prefixing
    let workingRows: Record<string, any>[] = primaryTable.rows.map(row => {
      const aliased: Record<string, any> = {};
      // Add plain properties and aliased properties
      for (const [k, v] of Object.entries(row)) {
        aliased[k] = v;
        aliased[`${query.primaryTable.alias}.${k}`] = v;
        aliased[`${query.primaryTable.tableName}.${k}`] = v;
      }
      return aliased;
    });

    // 2. Process JOINs
    for (const join of query.joins) {
      const joinTable = this.tables.get(join.table.tableName.toLowerCase());
      if (!joinTable) {
        throw new Error(`Joined table '${join.table.tableName}' not found in database.`);
      }

      const joinedRows: Record<string, any>[] = [];
      const { leftField, rightField } = this.parseJoinCondition(join.onCondition);

      for (const leftRow of workingRows) {
        let matched = false;
        const leftVal = this.getFieldValue(leftRow, leftField);

        for (const rightRow of joinTable.rows) {
          const rightVal = this.getFieldValue(rightRow, rightField);
          if (leftVal !== undefined && rightVal !== undefined && String(leftVal) === String(rightVal)) {
            matched = true;
            const combined: Record<string, any> = { ...leftRow };
            for (const [k, v] of Object.entries(rightRow)) {
              combined[k] = v;
              combined[`${join.table.alias}.${k}`] = v;
              combined[`${join.table.tableName}.${k}`] = v;
            }
            joinedRows.push(combined);
          }
        }

        if (!matched && join.type === 'LEFT') {
          const combined: Record<string, any> = { ...leftRow };
          for (const col of joinTable.columns) {
            combined[col.name] = null;
            combined[`${join.table.alias}.${col.name}`] = null;
            combined[`${join.table.tableName}.${col.name}`] = null;
          }
          joinedRows.push(combined);
        }
      }

      workingRows = joinedRows;
    }

    // 3. Filter WHERE
    if (query.whereClause) {
      workingRows = workingRows.filter(row => this.evaluateCondition(row, query.whereClause!));
    }

    // 4. GROUP BY & Aggregation
    const hasAggregates = query.selectFields.some(f => !!f.aggregate);
    if (query.groupByFields || hasAggregates) {
      workingRows = this.executeGroupBy(workingRows, query);
    } else {
      // Direct projection
      workingRows = workingRows.map(row => {
        const out: Record<string, any> = {};
        if (query.isSelectAll) {
          // Return non-prefixed fields
          for (const [k, v] of Object.entries(row)) {
            if (!k.includes('.')) out[k] = v;
          }
        } else {
          for (const f of query.selectFields) {
            const outKey = f.alias || f.field;
            out[outKey] = this.getFieldValue(row, f.field);
          }
        }
        return out;
      });
    }

    // 5. Filter HAVING
    if (query.havingClause) {
      workingRows = workingRows.filter(row => this.evaluateCondition(row, query.havingClause!));
    }

    // 6. ORDER BY
    if (query.orderBy && query.orderBy.length > 0) {
      workingRows.sort((a, b) => {
        for (const ord of query.orderBy!) {
          const valA = this.getFieldValue(a, ord.field);
          const valB = this.getFieldValue(b, ord.field);

          if (valA === valB) continue;
          if (valA === null || valA === undefined) return 1;
          if (valB === null || valB === undefined) return -1;

          if (typeof valA === 'number' && typeof valB === 'number') {
            return ord.direction === 'ASC' ? valA - valB : valB - valA;
          }

          const comp = String(valA).localeCompare(String(valB));
          return ord.direction === 'ASC' ? comp : -comp;
        }
        return 0;
      });
    }

    // 7. LIMIT & OFFSET
    if (query.offset || query.limit !== undefined) {
      const offset = query.offset || 0;
      const limit = query.limit !== undefined ? query.limit : workingRows.length;
      workingRows = workingRows.slice(offset, offset + limit);
    }

    return workingRows;
  }

  private parseJoinCondition(cond: string): { leftField: string; rightField: string } {
    const parts = cond.split('=');
    if (parts.length !== 2) {
      throw new Error(`Invalid JOIN condition: ${cond}. Must be 'table1.col = table2.col'`);
    }
    return {
      leftField: parts[0].trim(),
      rightField: parts[1].trim(),
    };
  }

  private evaluateCondition(row: Record<string, any>, conditionStr: string): boolean {
    // Handle OR expressions
    const orParts = conditionStr.split(/\s+\bOR\b\s+/i);
    if (orParts.length > 1) {
      return orParts.some(part => this.evaluateCondition(row, part.trim()));
    }

    // Handle AND expressions
    const andParts = conditionStr.split(/\s+\bAND\b\s+/i);
    if (andParts.length > 1) {
      return andParts.every(part => this.evaluateCondition(row, part.trim()));
    }

    const trimmed = conditionStr.trim();

    // BETWEEN x AND y (Check before standard single operator match)
    const betweenMatch = trimmed.match(/^([\w\.\_]+)\s+BETWEEN\s+([\d\.\-]+)\s+AND\s+([\d\.\-]+)$/i);
    if (betweenMatch) {
      const val = Number(this.getFieldValue(row, betweenMatch[1]));
      const min = Number(betweenMatch[2]);
      const max = Number(betweenMatch[3]);
      return !isNaN(val) && val >= min && val <= max;
    }

    // IS NULL / IS NOT NULL
    const nullMatch = trimmed.match(/^([\w\.\_]+)\s+IS\s+(NOT\s+)?NULL$/i);
    if (nullMatch) {
      const val = this.getFieldValue(row, nullMatch[1]);
      const isNot = !!nullMatch[2];
      const isNull = val === null || val === undefined;
      return isNot ? !isNull : isNull;
    }

    // IN (...)
    const inMatch = trimmed.match(/^([\w\.\_]+)\s+(NOT\s+)?IN\s*\(([\s\S]+)\)$/i);
    if (inMatch) {
      const val = String(this.getFieldValue(row, inMatch[1]));
      const isNot = !!inMatch[2];
      const options = inMatch[3].split(',').map(s => s.trim().replace(/^['"]|['"]$/g, ''));
      const found = options.includes(val);
      return isNot ? !found : found;
    }

    // LIKE / ILIKE
    const likeMatch = trimmed.match(/^([\w\.\_]+)\s+(NOT\s+)?(I?LIKE)\s+['"]([\s\S]+?)['"]$/i);
    if (likeMatch) {
      const val = String(this.getFieldValue(row, likeMatch[1]) || '');
      const isNot = !!likeMatch[2];
      const isCaseInsensitive = likeMatch[3].toUpperCase() === 'ILIKE';
      let pattern = likeMatch[4];

      // Convert SQL % and _ to Regex
      pattern = pattern.replace(/[\-\[\]\/\{\}\(\)\*\+\?\.\\\^\$\|]/g, "\\$&");
      pattern = pattern.replace(/%/g, '.*').replace(/_/g, '.');
      const regex = new RegExp(`^${pattern}$`, isCaseInsensitive ? 'i' : '');
      const matches = regex.test(val);
      return isNot ? !matches : matches;
    }

    // Standard Comparison Operators: >=, <=, !=, <>, =, >, <
    const compMatch = trimmed.match(/^([\w\.\_]+)\s*(=|!=|<>|>=|<=|>|<)\s*([\s\S]+)$/);
    if (compMatch) {
      const field = compMatch[1].trim();
      const op = compMatch[2].trim();
      let rawVal = compMatch[3].trim().replace(/^['"]|['"]$/g, '');

      const actualVal = this.getFieldValue(row, field);

      // Handle Boolean
      if (rawVal.toLowerCase() === 'true' || rawVal.toLowerCase() === 'false') {
        const boolVal = rawVal.toLowerCase() === 'true';
        return op === '=' ? actualVal === boolVal : actualVal !== boolVal;
      }

      // Handle Numeric
      if (!isNaN(Number(rawVal)) && typeof actualVal === 'number') {
        const numA = Number(actualVal);
        const numB = Number(rawVal);
        switch (op) {
          case '=': return numA === numB;
          case '!=':
          case '<>': return numA !== numB;
          case '>': return numA > numB;
          case '<': return numA < numB;
          case '>=': return numA >= numB;
          case '<=': return numA <= numB;
        }
      }

      // String Comparison
      const strA = String(actualVal || '').toLowerCase();
      const strB = rawVal.toLowerCase();
      switch (op) {
        case '=': return strA === strB;
        case '!=':
        case '<>': return strA !== strB;
        case '>': return strA > strB;
        case '<': return strA < strB;
        case '>=': return strA >= strB;
        case '<=': return strA <= strB;
      }
    }

    return true;
  }

  private executeGroupBy(rows: Record<string, any>[], query: ParsedQuery): Record<string, any>[] {
    const groups: Map<string, Record<string, any>[]> = new Map();

    // Grouping
    if (query.groupByFields && query.groupByFields.length > 0) {
      for (const row of rows) {
        const groupKey = query.groupByFields
          .map(f => String(this.getFieldValue(row, f)))
          .join(':::');
        
        if (!groups.has(groupKey)) {
          groups.set(groupKey, []);
        }
        groups.get(groupKey)!.push(row);
      }
    } else {
      // All rows in single group for global aggregate (e.g. SELECT COUNT(*) FROM table)
      groups.set('__GLOBAL__', rows);
    }

    const aggregatedRows: Record<string, any>[] = [];

    groups.forEach((groupRows) => {
      const outRow: Record<string, any> = {};

      for (const field of query.selectFields) {
        const outKey = field.alias || field.field;

        if (field.aggregate) {
          outRow[outKey] = this.computeAggregate(groupRows, field);
        } else {
          // Non-aggregated field -> pick first row's value
          outRow[outKey] = this.getFieldValue(groupRows[0], field.field);
        }
      }

      aggregatedRows.push(outRow);
    });

    return aggregatedRows;
  }

  private computeAggregate(rows: Record<string, any>[], field: SelectField): any {
    const values = rows.map(r => field.field === '*' ? 1 : this.getFieldValue(r, field.field));

    switch (field.aggregate) {
      case 'COUNT': {
        if (field.field === '*') return rows.length;
        if (field.isDistinct) {
          return new Set(values.filter(v => v !== null && v !== undefined)).size;
        }
        return values.filter(v => v !== null && v !== undefined).length;
      }
      case 'SUM': {
        const sum = values.reduce((acc, v) => acc + (Number(v) || 0), 0);
        return Number(sum.toFixed(2));
      }
      case 'AVG': {
        const valid = values.filter(v => v !== null && !isNaN(Number(v)));
        if (valid.length === 0) return 0;
        const sum = valid.reduce((acc, v) => acc + Number(v), 0);
        return Number((sum / valid.length).toFixed(2));
      }
      case 'MIN': {
        const nums = values.map(v => Number(v)).filter(v => !isNaN(v));
        return nums.length > 0 ? Math.min(...nums) : null;
      }
      case 'MAX': {
        const nums = values.map(v => Number(v)).filter(v => !isNaN(v));
        return nums.length > 0 ? Math.max(...nums) : null;
      }
      case 'ROUND': {
        const val = this.getFieldValue(rows[0], field.field);
        return typeof val === 'number' ? Math.round(val) : val;
      }
      default:
        return values[0];
    }
  }

  private getFieldValue(row: Record<string, any>, field: string): any {
    if (row[field] !== undefined) return row[field];
    
    // Strip table prefix if present (e.g. t.amount_usd -> amount_usd)
    const stripped = field.replace(/^[a-zA-Z0-9_]+\./, '');
    if (row[stripped] !== undefined) return row[stripped];

    return undefined;
  }
}

/**
 * Pivot Table Engine
 * Transforms tabular rows into a 2D matrix with customizable aggregations and heatmap weights
 */
export function computePivotTable(
  rows: Record<string, any>[],
  config: PivotConfig
): PivotResult {
  const filtered = config.filterField && config.filterValue
    ? rows.filter(r => String(r[config.filterField!]) === config.filterValue)
    : rows;

  const rowKeySet = new Set<string>();
  const colKeySet = new Set<string>();

  filtered.forEach(r => {
    rowKeySet.add(String(r[config.rowField] ?? 'N/A'));
    colKeySet.add(String(r[config.colField] ?? 'N/A'));
  });

  const rowKeys = Array.from(rowKeySet).sort();
  const colKeys = Array.from(colKeySet).sort();

  const cellBuckets: Record<string, Record<string, number[]>> = {};

  rowKeys.forEach(rk => {
    cellBuckets[rk] = {};
    colKeys.forEach(ck => {
      cellBuckets[rk][ck] = [];
    });
  });

  filtered.forEach(r => {
    const rk = String(r[config.rowField] ?? 'N/A');
    const ck = String(r[config.colField] ?? 'N/A');
    const val = Number(r[config.valField]);
    const num = isNaN(val) ? 1 : val;

    if (cellBuckets[rk] && cellBuckets[rk][ck]) {
      cellBuckets[rk][ck].push(num);
    }
  });

  let maxVal = 0;
  let minVal = Infinity;
  let grandTotal = 0;
  const rowTotals: Record<string, number> = {};
  const colTotals: Record<string, number> = {};

  colKeys.forEach(ck => { colTotals[ck] = 0; });
  rowKeys.forEach(rk => { rowTotals[rk] = 0; });

  const matrix: Record<string, Record<string, PivotCell>> = {};

  rowKeys.forEach(rk => {
    matrix[rk] = {};
    colKeys.forEach(ck => {
      const items = cellBuckets[rk][ck];
      let val = 0;

      if (items.length > 0) {
        switch (config.aggFunc) {
          case 'SUM':
            val = items.reduce((a, b) => a + b, 0);
            break;
          case 'AVG':
            val = items.reduce((a, b) => a + b, 0) / items.length;
            break;
          case 'COUNT':
          case 'COUNT_DISTINCT':
            val = items.length;
            break;
          case 'MIN':
            val = Math.min(...items);
            break;
          case 'MAX':
            val = Math.max(...items);
            break;
        }
      }

      val = Number(val.toFixed(2));
      if (val > maxVal) maxVal = val;
      if (val < minVal && val > 0) minVal = val;

      rowTotals[rk] += val;
      colTotals[ck] += val;
      grandTotal += val;

      matrix[rk][ck] = {
        value: val,
        count: items.length,
        formatted: val >= 1000 ? `$${val.toLocaleString()}` : val.toString(),
        ratio: 0,
      };
    });
  });

  // Calculate intensity ratios
  rowKeys.forEach(rk => {
    colKeys.forEach(ck => {
      const cell = matrix[rk][ck];
      cell.ratio = maxVal > 0 ? cell.value / maxVal : 0;
    });
  });

  return {
    rowKeys,
    colKeys,
    matrix,
    rowTotals,
    colTotals,
    grandTotal: Number(grandTotal.toFixed(2)),
    totalRecords: filtered.length,
    maxVal,
    minVal: minVal === Infinity ? 0 : minVal,
  };
}

interface TableRef {
  tableName: string;
  alias: string;
}

interface JoinClause {
  type: 'INNER' | 'LEFT';
  table: TableRef;
  onCondition: string;
}

interface SelectField {
  raw: string;
  field: string;
  alias?: string;
  aggregate?: 'COUNT' | 'SUM' | 'AVG' | 'MIN' | 'MAX' | 'ROUND' | 'UPPER' | 'LOWER';
  isDistinct?: boolean;
}

interface OrderByClause {
  field: string;
  direction: 'ASC' | 'DESC';
}

interface ParsedQuery {
  selectFields: SelectField[];
  isSelectAll: boolean;
  primaryTable: TableRef;
  joins: JoinClause[];
  whereClause?: string;
  groupByFields?: string[];
  havingClause?: string;
  orderBy?: OrderByClause[];
  limit?: number;
  offset?: number;
}