require 'active_record'
require 'active_support/hash_with_indifferent_access'
require 'rspec'
require 'mysql2'

DATABASE_NAME = :sql_playground_database.to_s
DATABASE_CONFIGURATION_BASE = {
  adapter: :mysql2,
  host: :localhost,
  username: ENV['MYSQL_USER_NAME'],
  password: ENV['MYSQL_USER_PASSWORD'],
}

class DummyCreator < ActiveRecord::Migration
  class << self
    def up
      create_table(:sql_playground_student) do |t|
        t.string :name
      end

      create_table(:sql_playground_classroom) do |t|
        t.string :name
      end

      create_table(:sql_playground_student_classroom) do |t|
        t.integer :student_id
        t.integer :classroom_id
      end
    rescue
      nil
    end

    def down
      drop_table(:sql_playground_student)
      drop_table(:sql_playground_classroom)
      drop_table(:sql_playground_student_classroom)
    rescue
      nil
    end
  end
end

RSpec.configure do |config|
  config.before :suite do
    ActiveRecord::Base.establish_connection(DATABASE_CONFIGURATION_BASE)
    begin
      ActiveRecord::Base.connection.create_database(DATABASE_NAME)
    rescue ActiveRecord::StatementInvalid => e
      pp e
    end

    ActiveRecord::Base.establish_connection(DATABASE_CONFIGURATION_BASE.merge(
      database: DATABASE_NAME,
    ))

    DummyCreator.up rescue nil
  end

  config.after :suite do
    DummyCreator.down
  end
end

class StudentClassroom < ActiveRecord::Base
  self.table_name = :sql_playground_student_classroom

  belongs_to :student
  belongs_to :classroom
end

class Student < ActiveRecord::Base
  self.table_name = :sql_playground_student

  has_one :student_classrooms, class_name: StudentClassroom
  has_many :classrooms, through: :student_classrooms

  scope :each_classroom_student, -> { Student.left_outer_joins(:classrooms) }
end

class Classroom < ActiveRecord::Base
  self.table_name = :sql_playground_classroom

  has_one :student_classrooms, class_name: StudentClassroom
  has_many :students, through: :student_classrooms
end


describe 'sql' do
  before :all do
    students = (0..3).map do |n|
      Student.create(name: "Student #{n}")
    end

    classes = (0..2).map do |n|
      Classroom.create(name: "Classroom #{n}")
    end

    StudentClassroom.create(student: students[0], classroom: classes[0])
    StudentClassroom.create(student: students[0], classroom: classes[1])
    StudentClassroom.create(student: students[0], classroom: classes[2])

    StudentClassroom.create(student: students[1], classroom: classes[0])
    StudentClassroom.create(student: students[1], classroom: classes[1])

    StudentClassroom.create(student: students[2], classroom: classes[1])
    StudentClassroom.create(student: students[2], classroom: classes[2])

    StudentClassroom.create(student: students[3], classroom: classes[2])

    @students = students
    @classes = classes
  end

  let(:students) { @students }
  let(:classes) { @classes }

  it 'premise' do
    expect(students[0].classrooms).to match_array([classes[0], classes[1], classes[2]])
    expect(students[1].classrooms).to match_array([classes[0], classes[1]])
    expect(students[2].classrooms).to match_array([classes[1], classes[2]])
    expect(students[3].classrooms).to match_array([classes[2]])

    expect(classes[0].students).to match_array([students[0], students[1]])
    expect(classes[1].students).to match_array([students[0], students[1], students[2]])
    expect(classes[2].students).to match_array([students[0], students[2], students[3]])
  end

  it do
    expect(
      Student
        .left_outer_joins(:classrooms)
        .select(
          :id,
          :name,
          Classroom.arel_table[:id].as('as_classroom_id'),
          Classroom.arel_table[:name].as('as_classroom_name'),
        )
        .as_json
        .map(&:symbolize_keys)
    ).to eq([
              { id: 1, name: 'Student 0', as_classroom_id: 1, as_classroom_name: 'Classroom 0' },
              { id: 1, name: 'Student 0', as_classroom_id: 2, as_classroom_name: 'Classroom 1' },
              { id: 1, name: 'Student 0', as_classroom_id: 3, as_classroom_name: 'Classroom 2' },

              { id: 2, name: 'Student 1', as_classroom_id: 1, as_classroom_name: 'Classroom 0' },
              { id: 2, name: 'Student 1', as_classroom_id: 2, as_classroom_name: 'Classroom 1' },

              { id: 3, name: 'Student 2', as_classroom_id: 2, as_classroom_name: 'Classroom 1' },
              { id: 3, name: 'Student 2', as_classroom_id: 3, as_classroom_name: 'Classroom 2' },

              { id: 4, name: 'Student 3', as_classroom_id: 3, as_classroom_name: 'Classroom 2' },
            ])
  end
end