require 'net/http'
require 'uri'
require 'json'

require_relative 'human_sql/version'

module HumanSQL
  class QueryBuilder
    OPENAI_API_URL = 'https://api.openai.com/v1/chat/completions'

    def initialize(user_input, history = [])
      @user_input = user_input
      @history = history
      @schema_content = summarize_schema_with_ignored_tables
    end

    def self.run_with_history(user_input, history)
      new(user_input, history).get_results
    end

    def self.run(user_input)
      new(user_input).get_results
    end

    def get_results
      response = generate_query_or_response
      
      if response.is_a?(String)
        response
      else
        format_result_message(response)
      end
    rescue StandardError => e
      process_error_in_natural_language(e.message)
    end

    private

 def summarize_schema_with_ignored_tables
      schema_content = File.read(Rails.root.join('db', 'schema.rb'))
      ignored_tables = HumanSQLConfig[:ignored_tables] || []

      schema_summary = {}
      schema_content.split("\n").each do |line|
        if line.strip.start_with?("create_table")
          table_name = line.match(/"([^"]+)"/)[1]
          next if ignored_tables.include?(table_name)

          schema_summary[table_name] = { columns: [], has_many: [], belongs_to: [] }
        end

        current_table = schema_summary.keys.last if schema_summary.any?

        if line.strip.match?(/^t\.(\w+)\s+"([^"]+)"/) && current_table
          next if ignored_tables.include?(current_table)

          column_type = line.strip.match(/^t\.(\w+)\s+"([^"]+)"/)[1]
          column_name = line.strip.match(/^t\.(\w+)\s+"([^"]+)"/)[2]
          schema_summary[current_table][:columns] << "#{column_name}=#{column_type}"
        end

        if line.strip.start_with?("add_foreign_key")
          match_data = line.strip.match(/"([^"]+)", "([^"]+)"(?:, column: "([^"]+)")?/)
          if match_data
            parent_table = match_data[1]
            child_table = match_data[2]
            next if ignored_tables.include?(parent_table) || ignored_tables.include?(child_table)

            schema_summary[child_table] ||= { columns: [], has_many: [], belongs_to: [] }
            schema_summary[parent_table] ||= { columns: [], has_many: [], belongs_to: [] }
            schema_summary[child_table][:belongs_to] << parent_table
            schema_summary[parent_table][:has_many] << child_table
          end
        end
      end

      schema_summary.map do |table, details|
        columns = details[:columns].join(",")
        has_many = details[:has_many].join(",")
        belongs_to = details[:belongs_to].join(",")
        %Q{#{table}={#{columns},:has_many=>[#{has_many}],:belongs_to=>[#{belongs_to}]}}
      end.join("\n")
    rescue StandardError => e
      raise "Error processing schema: #{e.message}"
    end

    def generate_query_or_response
      prompt = build_query_prompt(@user_input, @schema_content)
      @history << { role: "user", content: prompt }

      response = call_openai_service(@history)&.strip
      raise 'No response received from OpenAI.' if response.nil? || response.empty?

      begin
        parsed_response = JSON.parse(response)
        
        case parsed_response["action"]
        when "query"
          execute_query(parsed_response["content"])
        when "response"
          parsed_response["content"]
        else
          raise "Invalid action type"
        end
      rescue JSON::ParserError
        response # Return conversational response directly
      end
    end

    def format_result_message(result)
      case result
      when Numeric
        generate_count_response(result)
      when ActiveRecord::Base
        generate_record_response(result)
      when Array, ActiveRecord::Relation
        generate_collection_response(result)
      else
        result.to_s
      end
    end

    def generate_count_response(count)
      prompt = <<-PROMPT
        Please format this response in #{HumanSQLConfig[:default_language]}:
        There are #{count} #{count == 1 ? 'record' : 'records'} in the database.
      PROMPT
      
      call_openai_service([{ role: "user", content: prompt }])
    end

    def generate_record_response(record)
      attributes = record.attributes.map { |k, v| "#{k}: #{v}" }.join("\n")
      prompt = <<-PROMPT
        Please format this response in #{HumanSQLConfig[:default_language]}:
        A record has been created/updated with the following data:
        #{attributes}
      PROMPT
      
      call_openai_service([{ role: "user", content: prompt }])
    end

    def generate_collection_response(collection)
      if collection.empty?
        prompt = "Please translate to #{HumanSQLConfig[:default_language]}: No records found."
        call_openai_service([{ role: "user", content: prompt }])
      else
        records = collection.map { |r| format_record(r) }.join("\n")
        prompt = <<-PROMPT
          Please format this response in #{HumanSQLConfig[:default_language]}:
          Found #{collection.size} records:
          #{records}
        PROMPT
        
        call_openai_service([{ role: "user", content: prompt }])
      end
    end

    def format_record(record)
      if record.respond_to?(:attributes)
        record.attributes.map { |k, v| "#{k}: #{v}" }.join(", ")
      else
        record.to_s
      end
    end

    def build_query_prompt(user_input, schema_content)
      <<-PROMPT
        You are an assistant that helps users interact with a database.
        The user has requested: "#{user_input}".

        This is the database schema:

        #{schema_content}

        Instructions:
        - For general greetings or conversation, respond directly with a message in #{HumanSQLConfig[:default_language]}.
        - For database queries, respond with a JSON containing:
          {
            "action": "query",
            "content": "The ActiveRecord command here"
          }
        - For informative responses without queries, respond with:
          {
            "action": "response",
            "content": "Your message here in #{HumanSQLConfig[:default_language]}"
          }
        
        Ensure queries are precise and use ActiveRecord.
        #{HumanSQLConfig[:additional_instructions]}
      PROMPT
    end

    def call_openai_service(history)
      body = {
        model: "gpt-4o-mini",
        messages: history
      }

      uri = URI.parse(OPENAI_API_URL)
      request = Net::HTTP::Post.new(uri)
      request["Content-Type"] = "application/json"
      request["Authorization"] = "Bearer #{HumanSQLConfig[:api_key]}"
      request.body = body.to_json

      response = Net::HTTP.start(uri.host, uri.port, use_ssl: true) do |http|
        http.request(request)
      end

      parse_openai_response(response)
    end

    def parse_openai_response(response)
      if response.is_a?(Net::HTTPSuccess)
        response_body = JSON.parse(response.body)
        response_body.dig('choices', 0, 'message', 'content')
      else
        raise "Error in OpenAI API: #{response.code} - #{response.body}"
      end
    end

    def execute_query(query)
      model_name = query.split(/[.(]/).first
      unless Object.const_defined?(model_name)
        raise "Model '#{model_name}' is not defined."
      end

      eval(query)
    rescue StandardError => e
      raise "Error executing query: #{e.message}"
    end

    def process_error_in_natural_language(error_message)
      prompt = <<-PROMPT
        Please format this error message in #{HumanSQLConfig[:default_language]}:
        Sorry, an error occurred: #{error_message}
      PROMPT
      
      call_openai_service([{ role: "user", content: prompt }])
    end
  end

  class ChatInterface
    def initialize
      @history = []
      show_welcome_message
      start_chat_loop
    end

    private

    def show_welcome_message
      puts "Welcome to your interactive database chat!"
      puts "You can ask questions or request data in natural language."
      puts "Example: 'Show all users created this week'."
      puts "Type 'exit' to end the conversation."
      puts "--------------------------"
    end

    def start_chat_loop
      loop do
        print "You: "
        user_input = gets.chomp.strip
        break if user_input.downcase == "exit"

        process_user_input(user_input)
      end

      puts "Thank you for using HumanSQL. Goodbye!"
    end

    def process_user_input(user_input)
      @history << { role: "user", content: user_input }

      begin
        response = QueryBuilder.run_with_history(user_input, @history)
        @history << { role: "assistant", content: response }
        puts "Response: #{response}"
      rescue StandardError => e
        error_message = "Error: #{e.message}"
        puts error_message
        @history << { role: "assistant", content: error_message }
      end
    end
  end
end