# frozen_string_literal: true

require 'json'
require 'uri'
require 'set'

# Main SQLUI class responsible for producing web content. This class needs to die.
class SQLUI
  MAX_ROWS = 1_000

  def initialize(client:, name:, saved_path:, table_schema: nil, max_rows: MAX_ROWS)
    @client = client
    @table_schema = table_schema
    @name = name
    @saved_path = saved_path
    @max_rows = max_rows
    @resources_dir = File.join(File.expand_path('..', File.dirname(__FILE__)), 'client', 'resources')
  end

  def get(params)
    case params[:route]
    when 'app'
      { body: html, status: 200, content_type: 'text/html' }
    when 'sqlui.css'
      { body: css, status: 200, content_type: 'text/css' }
    when 'sqlui.js'
      { body: javascript, status: 200, content_type: 'text/javascript' }
    when 'metadata'
      { body: metadata.to_json, status: 200, content_type: 'application/json' }
    when 'query_file'
      { body: query_file(params).to_json, status: 200, content_type: 'application/json' }
    else
      { body: "unknown route: #{params[:route]}", status: 404, content_type: 'text/plain' }
    end
  end

  def post(params)
    case params[:route]
    when 'query'
      { body: query(params).to_json, status: 200, content_type: 'application/json' }
    else
      { body: "unknown route: #{params[:route]}", status: 404, content_type: 'text/plain' }
    end
  end

  private

  def html
    @html ||= File.read(File.join(@resources_dir, 'sqlui.html'))
  end

  def css
    @css ||= File.read(File.join(@resources_dir, 'sqlui.css'))
  end

  def javascript
    @javascript ||= File.read(File.join(@resources_dir, 'sqlui.js'))
  end

  def query(params)
    raise 'missing sql' unless params[:sql]
    raise 'missing cursor' unless params[:cursor]

    sql = find_query_at_cursor(params[:sql], Integer(params[:cursor]))
    raise "can't find query at cursor" unless sql

    execute_query(sql)
  end

  def query_file(params)
    raise 'missing file param' unless params['file']

    sql = File.read("#{@saved_path}/#{params['file']}")
    execute_query(sql).tap { |r| r[:file] = params[:file] }
  end

  def metadata
    load_metadata
  end

  def load_metadata
    result = {
      server: @name,
      schemas: {},
      saved: Dir.glob("#{@saved_path}/*.sql").map do |path|
        {
          filename: File.basename(path),
          description: File.readlines(path).take_while { |l| l.start_with?('--') }.map { |l| l.sub(/^-- */, '') }.join
        }
      end
    }

    where_clause = if @table_schema
                     "where table_schema = '#{@table_schema}'"
                   else
                     "where table_schema not in('mysql', 'sys', 'information_schema', 'performance_schema')"
                   end
    column_result = @client.query(
      <<~SQL
        select
          table_schema,
          table_name,
          column_name,
          data_type,
          character_maximum_length,
          is_nullable,
          column_key,
          column_default,
          extra
        from information_schema.columns
        #{where_clause}
        order by table_schema, table_name, column_name, ordinal_position;
      SQL
    )
    column_result.each do |row|
      row = row.transform_keys(&:downcase).transform_keys(&:to_sym)
      table_schema = row[:table_schema]
      unless result[:schemas][table_schema]
        result[:schemas][table_schema] = {
          tables: {}
        }
      end
      table_name = row[:table_name]
      tables = result[:schemas][table_schema][:tables]
      unless tables[table_name]
        tables[table_name] = {
          indexes: {},
          columns: {}
        }
      end
      columns = result[:schemas][table_schema][:tables][table_name][:columns]
      column_name = row[:column_name]
      columns[column_name] = {} unless columns[column_name]
      column = columns[column_name]
      column[:name] = column_name
      column[:data_type] = row[:data_type]
      column[:length] = row[:character_maximum_length]
      column[:allow_null] = row[:is_nullable]
      column[:key] = row[:column_key]
      column[:default] = row[:column_default]
      column[:extra] = row[:extra]
    end

    where_clause = if @table_schema
                     "where table_schema = '#{@table_schema}'"
                   else
                     "where table_schema not in('mysql', 'sys', 'information_schema', 'performance_schema')"
                   end
    stats_result = @client.query(
      <<~SQL
        select
          table_schema,
          table_name,
          index_name,
          seq_in_index,
          non_unique,
          column_name
        from information_schema.statistics
        #{where_clause}
        order by table_schema, table_name, if(index_name = "PRIMARY", 0, index_name), seq_in_index;
      SQL
    )
    stats_result.each do |row|
      row = row.transform_keys(&:downcase).transform_keys(&:to_sym)
      table_schema = row[:table_schema]
      tables = result[:schemas][table_schema][:tables]
      table_name = row[:table_name]
      indexes = tables[table_name][:indexes]
      index_name = row[:index_name]
      indexes[index_name] = {} unless indexes[index_name]
      index = indexes[index_name]
      column_name = row[:column_name]
      index[column_name] = {}
      column = index[column_name]
      column[:name] = index_name
      column[:seq_in_index] = row[:seq_in_index]
      column[:non_unique] = row[:non_unique]
      column[:column_name] = row[:column_name]
    end

    result
  end

  def execute_query(sql)
    result = @client.query(sql)
    rows = result.map(&:values)
    columns = result.first&.keys || []
    column_types = columns.map { |_| 'string' }
    unless rows.empty?
      maybe_non_null_column_value_exemplars = columns.each_with_index.map do |_, index|
        row = rows.find do |current|
          !current[index].nil?
        end
        row.nil? ? nil : row[index]
      end
      column_types = maybe_non_null_column_value_exemplars.map do |value|
        case value
        when String, NilClass
          'string'
        when Integer
          'number'
        when Date, Time
          'date'
        else
          value.class.to_s
        end
      end
    end
    {
      query: sql,
      columns: columns,
      column_types: column_types,
      total_rows: rows.size,
      rows: rows.take(@max_rows)
    }
  end

  def find_query_at_cursor(sql, cursor)
    parts_with_ranges = []
    sql.scan(/[^;]*;[ \n]*/) { |part| parts_with_ranges << [part, 0, part.size] }
    parts_with_ranges.inject(0) do |pos, current|
      current[1] += pos
      current[2] += pos
    end
    part_with_range = parts_with_ranges.find do |current|
      cursor >= current[1] && cursor < current[2]
    end || parts_with_ranges[-1]
    part_with_range[0]
  end
end