#!/usr/bin/env ruby
# frozen_string_literal: true

require 'bundler/setup'
require 'forwardable'
require 'rack'
require 'sqlite3'

$LOAD_PATH.unshift(File.expand_path(File.join('..', 'lib'), __dir__))
require 'rack/queries'

class Database
  class IncomprehensibleReturnValueError < ArgumentError
    def initialize(*)
      super('SQLite3 return value not understood')
    end
  end

  attr_reader :db

  def initialize
    @db = SQLite3::Database.new(':memory:')

    DATA.read.split(';')[0...-1].each do |query|
      db.execute(query)
    end
  end

  def value_from(query, binds = [])
    results = execute(query, binds)

    case results
    when Array
      results[0][0]
    when SQLite3::ResultSet
      results.next[0]
    else
      raise IncomprehensibleReturnValueError
    end
  end

  def values_from(query, binds = [])
    execute(query, binds).map(&:first)
  end

  def rows_from(query, binds = [])
    execute(query, binds).to_a
  end

  class << self
    def instance
      @instance ||= new
    end

    extend Forwardable
    def_delegators :instance, :value_from, :values_from, :rows_from
  end

  private

  def execute(query, binds)
    return db.execute(query) if binds.empty?

    stmt = db.prepare(query)
    stmt.execute(binds)
  end
end

module Rack
  module Queries
    create do
      name 'Organization count'
      desc 'The total count of organizations'

      run { |_opts| Database.value_from('SELECT COUNT(*) FROM orgs') }
    end

    create do
      name 'User count'
      desc 'The total count of users'

      run { |_opts| Database.value_from('SELECT COUNT(*) FROM users') }
    end

    create do
      name 'Users'
      desc 'The list of all users'

      run do |_opts|
        [%w[id org_id active name]] +
          Database.rows_from('SELECT id, org_id, active, name FROM users')
      end
    end

    create do
      name 'Users in organizations count'
      desc 'The total count of users in a given organization'

      opt(:org_name) { Database.values_from('SELECT name FROM orgs') }

      run do |opts|
        query = <<~SQL
          SELECT COUNT(*) FROM users
          WHERE org_id = (SELECT id FROM orgs WHERE name = :org_name)
        SQL

        Database.value_from(query, opts)
      end
    end

    create do
      name 'Active users in organizations count'
      desc 'The total count of active users in a given organization'

      opt(:org_name) { Database.values_from('SELECT name FROM orgs') }

      run do |opts|
        query = <<~SQL
          SELECT COUNT(*) FROM users
          WHERE org_id = (SELECT id FROM orgs WHERE name = :org_name)
          AND active = 1
        SQL

        Database.value_from(query, opts)
      end
    end

    create do
      name 'Name search'
      desc 'Search for a user by name'

      opt(:org_name) { Database.values_from('SELECT name FROM orgs') }
      opt(:user_name, type: :text)

      run do |opts|
        query = <<~SQL
          SELECT id, org_id, active, name FROM users
          WHERE org_id = (SELECT id FROM orgs WHERE name = :org_name)
            AND name LIKE :user_name || '%'
        SQL

        [%w[id org_id active name]] + Database.rows_from(query, opts)
      end
    end
  end
end

Rack::Server.start(app: Rack::Queries::App, server: 'webrick')

__END__
CREATE TABLE orgs (
  id INTEGER,
  name VARCHAR(255),
  PRIMARY KEY(id)
);

CREATE TABLE users (
  id INTEGER,
  org_id INTEGER,
  active BOOLEAN NOT NULL,
  name VARCHAR(255),
  PRIMARY KEY(id)
);

INSERT INTO orgs(name) VALUES
  ("Parks and Recreation"),
  ("Dunder Mifflin");

INSERT INTO users(org_id, active, name) VALUES
  (1, 1, "Leslie Knope"),
  (1, 1, "Ron Swanson"),
  (1, 1, "April Ludgate"),
  (1, 1, "Andy Dwyer"),
  (1, 1, "Ben Wyatt"),
  (1, 1, "Tom Haverford"),
  (1, 1, "Donna Meagle"),
  (1, 0, "Jerry Gergich"),
  (2, 1, "Michael Scott"),
  (2, 1, "Pam Beesley"),
  (2, 1, "Jim Halpert"),
  (2, 1, "Dwight Schrute"),
  (2, 1, "Angela Martin"),
  (2, 1, "Andy Bernard"),
  (2, 1, "Kevin Malone"),
  (2, 0, "Roy Anderson"),
  (2, 1, "Kelly Kapoor"),
  (2, 1, "Ryan Howard");