import * as monaco from 'monaco-editor';

const SQL_KEYWORDS = [
  'SELECT',
  'FROM',
  'WHERE',
  'AND',
  'OR',
  'NOT',
  'JOIN',
  'INNER',
  'LEFT',
  'RIGHT',
  'FULL',
  'OUTER',
  'ON',
  'ORDER',
  'BY',
  'GROUP',
  'HAVING',
  'LIMIT',
  'OFFSET',
  'INSERT',
  'INTO',
  'VALUES',
  'UPDATE',
  'SET',
  'DELETE',
  'CREATE',
  'TABLE',
  'ALTER',
  'DROP',
  'PRIMARY',
  'KEY',
  'FOREIGN',
  'CONSTRAINT',
  'UNIQUE',
  'INDEX',
  'VIEW',
  'DATABASE',
  'SCHEMA',
  'AS',
  'DISTINCT',
  'CASE',
  'WHEN',
  'THEN',
  'ELSE',
  'END',
  'CAST',
  'BETWEEN',
  'IN',
  'LIKE',
  'IS',
  'NULL',
  'TRUE',
  'FALSE',
  'WITH',
  'UNION',
  'EXCEPT',
  'INTERSECT',
  'ASC',
  'DESC',
  'ALL',
  'ANY',
  'EXISTS',
  'CROSS',
];

const SQL_FUNCTIONS = [
  { name: 'COUNT', signature: 'COUNT(expression)', description: 'Returns the number of rows' },
  { name: 'SUM', signature: 'SUM(expression)', description: 'Returns the sum of values' },
  { name: 'AVG', signature: 'AVG(expression)', description: 'Returns the average value' },
  { name: 'MIN', signature: 'MIN(expression)', description: 'Returns the minimum value' },
  { name: 'MAX', signature: 'MAX(expression)', description: 'Returns the maximum value' },
  { name: 'UPPER', signature: 'UPPER(string)', description: 'Converts string to uppercase' },
  { name: 'LOWER', signature: 'LOWER(string)', description: 'Converts string to lowercase' },
  { name: 'LENGTH', signature: 'LENGTH(string)', description: 'Returns the length of string' },
  { name: 'SUBSTRING', signature: 'SUBSTRING(string, start, length)', description: 'Extracts substring' },
  { name: 'TRIM', signature: 'TRIM(string)', description: 'Removes leading and trailing spaces' },
  { name: 'ROUND', signature: 'ROUND(number, decimals)', description: 'Rounds a number' },
  { name: 'ABS', signature: 'ABS(number)', description: 'Returns absolute value' },
  { name: 'COALESCE', signature: 'COALESCE(value1, value2, ...)', description: 'Returns first non-null value' },
  { name: 'NULLIF', signature: 'NULLIF(value1, value2)', description: 'Returns null if two values are equal' },
  { name: 'IFNULL', signature: 'IFNULL(value, default)', description: 'Returns alternative if null' },
  { name: 'CONCAT', signature: 'CONCAT(string1, string2, ...)', description: 'Concatenates strings' },
  { name: 'DATE', signature: 'DATE(date)', description: 'Extracts date part' },
  { name: 'NOW', signature: 'NOW()', description: 'Returns current date and time' },
];

export const getSqlCompletionProvider = () => {
  return {
    provideCompletionItems(
      model: monaco.editor.ITextModel,
      position: monaco.Position,
      _context: monaco.languages.CompletionContext,
      _token: monaco.CancellationToken,
    ): monaco.languages.ProviderResult<monaco.languages.CompletionList> {
      const word = model.getWordUntilPosition(position);
      const range = new monaco.Range(position.lineNumber, word.startColumn, position.lineNumber, word.endColumn);

      const suggestions: monaco.languages.CompletionItem[] = [
        ...SQL_KEYWORDS.map((keyword) => ({
          label: keyword,
          kind: monaco.languages.CompletionItemKind.Keyword,
          insertText: keyword,
          range: range,
          sortText: '1' + keyword,
        })),
        ...SQL_FUNCTIONS.map((func) => ({
          label: func.name,
          kind: monaco.languages.CompletionItemKind.Function,
          insertText: func.name,
          detail: func.signature,
          documentation: func.description,
          range: range,
          sortText: '2' + func.name,
        })),
      ];

      return {
        suggestions: suggestions,
      };
    },
  };
};