account.rb

class Account < ApplicationRecord
  # Include default devise modules. Others available are:
  # :lockable, :timeoutable and :omniauthable
  devise :database_authenticatable, :registerable, :confirmable,
         :recoverable, :rememberable, :trackable, :validatable

  has_one :profile, dependent: :destroy
  accepts_nested_attributes_for :profile

  validates_presence_of :email
  validates_associated :profile

  def profile
    super || build_profile
  end

  protected
  def password_required?
    confirmed? ? super : false
  end

  def confirmation_required?
    true # self.created_at < 1.day.ago
  end
end

activity.rb

class Activity < ApplicationRecord
  has_many :likes, as: :likeable
  has_many :placings, as: :placeable
  has_many :places, through: :placings
  has_many :bookings, through: :placings

  acts_as_taggable_on :tags
  #acts_as_taggable_on :categories




  def self.search(search)
    where("name ILIKE ?", "%#{search}%")
  end

  def self.by_place_id(place_id)
    where("placings.place_id = #{place_id} AND placings.placeable_id = activities.id AND placings.placeable_type = 'Activity'").left_joins(:placings)
  end

  def self.with_place_counts_info(profile)
    subqueries = [availability_count(profile), liked(profile), place_count, likes_count, bookings_count].compact.join(',')
    select("activities.*, #{subqueries}").left_joins(:likes).group(:id).order('place_count DESC, likes_count DESC, bookings_count DESC, id ASC')
  end

  def self.with_counts_info(profile)
    subqueries = [availability_count(profile), liked(profile), likes_count, bookings_count].compact.join(',')
    select("activities.*, #{subqueries}").left_joins(:likes).group(:id).order('likes_count DESC, bookings_count DESC, id ASC')
  end

  def self.with_likes_info(profile)
    subqueries = [availability_count(profile), liked(profile), likes_count].compact.join(',')
    select("activities.*, #{subqueries}").left_joins(:likes).group(:id)
  end

  def as_json(options = { })
    h = super(options)
    h['likes_count'] = self.attributes['likes_count'] || self.likes.count
    h['bookings_count'] = self.attributes['bookings_count'] || self.bookings.count
    h['availability_count'] = self.attributes['availability_count'] || 0
    h
  end

  private
  def self.liked(profile)
    return unless profile
    "BOOL_OR(likes.profile_id = #{profile.id}) AS liked"
  end

  def self.likes_count
    #likes_count = "(SELECT count(*) FROM likes WHERE likes.likeable_id = activities.id AND likes.likeable_type = 'Place' ) AS likes_count"
    "COUNT(DISTINCT likes.id) AS likes_count"
  end

  def self.place_count
    "COUNT(placings.place_id) AS place_count"
  end

  def self.placings_count
    "(SELECT COUNT(*) FROM placings WHERE placings.placeable_id = activities.id AND placings.placeable_type = 'Activity') AS placings_count"
  end

  def self.bookings_count
    "(SELECT COUNT(*) FROM bookings INNER JOIN placings
       ON bookings.bookable_id = placings.id
       WHERE placings.placeable_id = activities.id
       AND bookings.bookable_type = 'Placing'
       AND bookings.end_at >= '#{Time.current}'
    ) AS bookings_count"
  end

  def self.availability_count(profile)
    return unless profile
    return "false AS availability_count" if profile.liked_time_slots.count == 0
    "(SELECT COUNT(DISTINCT al.id)
      FROM likes al

      INNER JOIN likes plts -- profile likes time slots
      ON plts.likeable_type = 'TimeSlot' AND plts.profile_id = #{profile.id}

      INNER JOIN likes olts -- others likes time slots
      ON plts.likeable_type = 'TimeSlot' AND olts.profile_id <> #{profile.id}
      AND olts.likeable_id = plts.likeable_id

      WHERE al.likeable_type = 'Activity'
      AND al.likeable_id = activities.id
      AND al.profile_id = olts.profile_id
    ) AS availability_count"
  end
end

application_record.rb

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
end

booking.rb

class Booking < ApplicationRecord
  acts_as_taggable_on :tags

  belongs_to :profile, inverse_of: :bookings
  belongs_to :bookable, polymorphic: true

  validates_presence_of :start_at, :end_at
  validates_uniqueness_of :profile_id, scope: [:bookable_type, :bookable_id, :start_at, :end_at]

  before_validation :init
  # detect if bookings overlap and handle somehow?  merge bookings?

  def init
    self.start_at, self.end_at = [start_at, end_at].sort
  end

  def place
    self.bookable.try(:place) || self.bookable
  end

  def as_json(options = { })
    h = super(options)
    #h[bookable_type.to_s.parameterize.to_sym] = self.bookable
    h[:bookable] = self.bookable
    h
  end

  def self.going
    where('going = true')
  end

  def self.not_going
    where('going = false')
  end

  def self.before
    where('end_at < ?', Time.current)
  end

  def self.current
    where('end_at >= ?', Time.current)
  end

  def current?
    self.end_at >= Time.current
  end

  def self.search(search)
    where("name ILIKE ?", "%#{search}%")
  end

  def joinups
    Booking.joins("INNER JOIN bookings as time_match ON time_match.bookable_id = bookings.bookable_id")
    .where("time_match.id != bookings.id AND time_match.id = ?", id)
    .where("bookings.going = true")
    .where('NOT (bookings.start_at > time_match.end_at OR bookings.end_at < time_match.start_at)')
  end

  def self.with_joinups_info
    select('bookings.*, COUNT(time_match.id) as joinups_count, ARRAY_AGG(time_match.id) AS match_ids')
    .joins('LEFT OUTER JOIN bookings AS time_match
           ON time_match.bookable_id = bookings.bookable_id AND bookings.id != time_match.id
           AND NOT (bookings.start_at > time_match.end_at OR bookings.end_at < time_match.start_at)')
    .group(:id).order('joinups_count DESC, start_at ASC')
  end

  def self.with_counts_info
    select("bookings.*, #{joinups_count} + 1 AS joinups_count").order('joinups_count DESC, start_at ASC') #.group(:id).having("#{joinups_count} > 0")
  end

  private

  def self.joinups_count
    "(SELECT COUNT(*) FROM bookings b2 WHERE
    NOT (bookings.start_at > b2.end_at OR bookings.end_at < b2.start_at)
    AND bookings.bookable_id = b2.bookable_id
    AND bookings.id != b2.id)"
  end
end

image.rb

class Image < ApplicationRecord
  #include ImageUploader::Attachment.new(:image)

  has_many :likes, as: :likeable
end

like.rb

class Like < ApplicationRecord
  belongs_to :likeable, polymorphic: true
  belongs_to :profile, inverse_of: :likes

  validates_uniqueness_of :profile_id, scope: [:likeable_type, :likeable_id]
end

place.rb

class Place < ApplicationRecord
  has_many :likes, as: :likeable
  has_many :bookings, as: :bookable
  has_many :placings, inverse_of: :place, dependent: :destroy
  has_many :activities, through: :placings, source: :placeable, source_type: "Activity"

  # place could get tags from activities associated with it ..
  # has_many :tags, through: :activities

  reverse_geocoded_by :latitude, :longitude

  #validates_presence_of :name
  validates_uniqueness_of :g_place_id, :if => Proc.new{|f| f.g_place_id.present? }
  #validates_uniqueness_of :address

  def location=(string)
    return if string.blank?

    parsed = JSON.parse(string)
    self.g_place_id = parsed["place_id"]
    self.latitude   = parsed["geometry"]["location"]["lat"]
    self.longitude  = parsed["geometry"]["location"]["lng"]
  end

  def geo
    @geo ||= Geocoder.search(self.g_place_id, lookup: :google_places_details).first
  end

  def photo(opts = {})
    key = Rails.application.credentials.dig(:google, :places_api_key)
    geometry = opts[:height] ? "&maxheight=#{opts[:height]}" : "&maxwidth=#{opts[:width] || 1600}"
    if geo.photos
      mod = opts[:tag_id] && (opts[:tag_id] % geo.photos.count) || 0
      "https://maps.googleapis.com/maps/api/place/photo?key=#{key}&photoreference=#{geo.photos[mod]["photo_reference"]}#{geometry}"
    end
  end

  def name
    super || geo.data["name"]
  end

  def address
    super || geo.data["vicinity"]
  end

  def city
    super || geo.city
  end

  def state_code
    super || geo.state_code
  end

  def country_code
    super || geo.country_code
  end

  def u_address
    self.address.gsub(' ','+') unless self.address.nil?
  end

  acts_as_taggable_on :tags
  #acts_as_taggable_on :categories

  def self.search(search)
    where("name ILIKE ?", "%#{search}%")
  end

  def self.by_activity_id(activity_id)
    where("placings.place_id = places.id AND placings.placeable_id = #{activity_id} AND placings.placeable_type = 'Activity'").left_joins(:placings)
  end

  def self.with_activity_counts_info(profile)
    subqueries = [availability_count(profile), liked(profile), activity_count, likes_count, bookings_count].compact.join(',')
    select("places.*, #{subqueries}").left_joins(:likes).group(:id).order('activity_count DESC, likes_count DESC, bookings_count DESC, id ASC')
  end

  def self.with_counts_info(profile)
    subqueries = [availability_count(profile), liked(profile), likes_count, bookings_count].compact.join(',')
    select("places.*, #{subqueries}").left_joins(:likes).group(:id).order('likes_count DESC, bookings_count DESC, id ASC')
  end

  def self.with_likes_info(profile)
    subqueries = [availability_count(profile), liked(profile), likes_count].compact.join(',')
    select("places.*, #{subqueries}").left_joins(:likes).group(:id)
  end

  def as_json(options = { })
    h = super(options)
    h['likes_count'] = likes_count
    h['bookings_count'] = bookings_count
    h['availability_count'] = availability_count
    h
  end

  def likes_count
    self.attributes['likes_count'] || 0 #self.likes.count
  end
  def bookings_count
    self.attributes['bookings_count'] || 0 #self.bookings.current.count
  end
  def availability_count
    self.attributes['availability_count'] || 0
  end

  private
  def self.liked(profile)
    return unless profile
    "BOOL_OR(likes.profile_id = #{profile.id}) AS liked"
  end

  def self.likes_count
    #likes_count = "(SELECT count(*) FROM likes WHERE likes.likeable_id = places.id AND likes.likeable_type = 'Activity' ) AS likes_count"
    "COUNT(DISTINCT likes.id) AS likes_count"
  end

  def self.activity_count
    "COUNT(placings.placeable_id) AS activity_count"
  end

  def self.activities_count
    "(SELECT COUNT(*) FROM placings WHERE placings.place_id = places.id AND placings.placeable_type = 'Activity') AS activities_count"
  end

  def self.bookings_count
    "(SELECT COUNT(*) FROM bookings
      WHERE bookings.bookable_id = places.id
      AND bookings.bookable_type = 'Place'
      AND bookings.end_at >= '#{Time.current}'
    ) AS bookings_count"
  end

  def self.bookings_count_through_placings
    "(SELECT COUNT(*) FROM bookings INNER JOIN placings
      ON bookings.bookable_id = placings.id
      WHERE placings.place_id = places.id
      AND bookings.bookable_type = 'Placing'
      AND bookings.end_at >= '#{Time.current}'
    ) AS bookings_count"
  end

  def self.availability_count(profile)
    return unless profile
    return "false AS availability_count" if profile.liked_time_slots.count == 0
    "(SELECT COUNT(DISTINCT pl.id)
      FROM likes pl

      INNER JOIN likes plts -- profile likes time slots
      ON plts.likeable_type = 'TimeSlot' AND plts.profile_id = #{profile.id}

      INNER JOIN likes olts -- others likes time slots
      ON plts.likeable_type = 'TimeSlot' AND olts.profile_id <> #{profile.id}
      AND olts.likeable_id = plts.likeable_id

      WHERE pl.likeable_type = 'Place'
      AND pl.likeable_id = places.id
      AND pl.profile_id = olts.profile_id
    ) AS availability_count"
  end
end

placing.rb

class Placing < ApplicationRecord
  belongs_to :placeable, polymorphic: true
  belongs_to :place, inverse_of: :placings

  has_many :bookings, as: :bookable

  def as_json(options = { })
    h = super(options)
    h[:place] = self.place
    h[placeable_type.to_s.parameterize.to_sym] = self.placeable
    h
  end

  validates_uniqueness_of :place_id, scope: [:placeable_type, :placeable_id]
end

profile.rb

class Profile < ApplicationRecord
  belongs_to :account, dependent: :destroy
  accepts_nested_attributes_for :account

  enum gender: {'Male':'m', 'Female':'f', 'Trans':'t'}

  has_many :bookings, inverse_of: :profile, dependent: :destroy

  has_many :placings, as: :placeable
  has_many :places, through: :placings

  has_many :likes, inverse_of: :profile, dependent: :destroy
  has_many :liked_places,     through: :likes, source: :likeable, source_type: 'Place'
  has_many :liked_bookings,   through: :likes, source: :likeable, source_type: 'Booking'
  has_many :liked_profiles,   through: :likes, source: :likeable, source_type: 'Profile'
  has_many :liked_activities, through: :likes, source: :likeable, source_type: 'Activity'
  has_many :liked_time_slots,      through: :likes, source: :likeable, source_type: 'TimeSlot'

  has_many :inverse_likes, class_name: "Like", foreign_key: "likeable_id"
  has_many :likers, through: :inverse_likes, source: :profile

  validates_presence_of :name

  def name
    [first_name, last_name].join(' ')
  end

  def name=(name)
    self.first_name, self.last_name = name.split(' ', 2)
  end

  def as_json(options = { })
    h = super(options)
    h[:email] = account.email
    h
  end
end

tag.rb

class Tag < ApplicationRecord
  has_many :taggings
  has_many :likes, as: :likeable
  has_many :places, through: :taggings, source: :taggable, source_type: "Place"
  has_many :bookings, through: :taggings, source: :taggable, source_type: "Booking"


  def photo(opts = {})
    opts[:tag_id] = self.id
    num = self.places.count
    self.places[self.id % num].photo(opts)
  end

  def self.search(search)
    where("name ILIKE ?", "%#{search}%")
  end

  def self.with_place_counts_info(profile)
    subqueries = [availability_count(profile), liked(profile), place_count, likes_count, bookings_count].compact.join(',')
    select("tags.*, #{subqueries}").left_joins(:likes).group(:id).order('place_count DESC, likes_count DESC, bookings_count DESC, id ASC')
  end

  def self.with_counts_info(profile)
    subqueries = [availability_count(profile), liked(profile), likes_count, bookings_count].compact.join(',')
    select("tags.*, #{subqueries}").left_joins(:likes).group(:id).order('likes_count DESC, bookings_count DESC, id ASC')
  end

  def self.with_likes_info(profile)
    subqueries = [availability_count(profile), liked(profile), likes_count].compact.join(',')
    select("tags.*, #{subqueries}").left_joins(:likes).group(:id)
  end

  def as_json(options = { })
    h = super(options)
    h['likes_count'] = self.attributes['likes_count'] || self.likes.count
    h['bookings_count'] = self.attributes['bookings_count'] || self.bookings.count
    h['availability_count'] = self.attributes['availability_count'] || 0
    h
  end

  private
  def self.liked(profile)
    return unless profile
    "BOOL_OR(likes.profile_id = #{profile.id}) AS liked"
  end

  def self.likes_count
    #likes_count = "(SELECT count(*) FROM likes WHERE likes.likeable_id = tags.id AND likes.likeable_type = 'Place' ) AS likes_count"
    "COUNT(DISTINCT likes.id) AS likes_count"
  end


  def self.bookings_count
    "(SELECT COUNT(*) FROM bookings INNER JOIN taggings
       ON bookings.bookable_id = taggings.id
       WHERE taggings.tag_id = tags.id
       AND bookings.bookable_type = 'Place'
       AND bookings.end_at >= '#{Time.current}'
    ) AS bookings_count"
  end

  def self.availability_count(profile)
    return unless profile
    return "false AS availability_count" if profile.liked_time_slots.count == 0
    "(SELECT COUNT(DISTINCT al.id)
      FROM likes al

      INNER JOIN likes plts -- profile likes time slots
      ON plts.likeable_type = 'TimeSlot' AND plts.profile_id = #{profile.id}

      INNER JOIN likes olts -- others likes time slots
      ON plts.likeable_type = 'TimeSlot' AND olts.profile_id <> #{profile.id}
      AND olts.likeable_id = plts.likeable_id

      WHERE al.likeable_type = 'Tag'
      AND al.likeable_id = tags.id
      AND al.profile_id = olts.profile_id
    ) AS availability_count"
  end
end

tagging.rb

class Tagging < ActiveRecord::Base
  belongs_to :tag
  belongs_to :taggable, :polymorphic => true
end

time_slot.rb

class TimeSlot < ApplicationRecord
  has_many :likes, as: :likeable

  def self.with_counts_info
    select("time_slots.*, count(likes.*) as likes_count").left_joins(:likes).group(:id).order('id ASC')

#    TimeSlot.joins(:likes).where(likes: {profile_id: [Tag.first.likes.map(&:profile_id)]}).with_counts_info
  end

  def self.with_likes_info(profile)
    select("time_slots.*, #{likes_info(profile.id)}").left_joins(:likes).group(:id).order('id ASC')
  end

  private
  def self.likes_info(id)
    #likes_count = "(SELECT count(*) FROM likes WHERE likes.likeable_id = time_slots.id AND likes.likeable_type = 'Activity' ) AS likes_count"
    "bool_or(likes.profile_id = #{id}) as liked, count(likes.*) as likes_count"
  end
end