class NewsArticle < Base::NewsArticle
  belongs_to :school, foreign_key: :SchoolID
  belongs_to :user, foreign_key: :AuthorID
  belongs_to :classroom, foreign_key: :ClassID, optional: true
  belongs_to :parent_article, foreign_key: :ParentNewsID, class_name: 'NewsArticle', optional: true

  has_many :news_readers, foreign_key: :NewsID
  has_many :news_attachments, foreign_key: :NewsID
  has_many :documents, through: :news_attachments

  validates :title, presence: true, length: { maximum: 64 }
  validates :content, presence: true

  scope :ordered, -> { order(datetime: :desc) }
  scope :archived, ->(flag) { where(archive: !!flag) }
  scope :include_internal, ->(flag) { where(internal: 0) unless flag }
  scope :with_classroom_id, ->(id) { where(class_id: id) if id }
  scope :recent, ->(flag) { where('Date > ?', 3.months.ago) if flag }
  scope :exclude_future, -> { where('Date <= ?', Time.current) }

  scope :read_or_unread, ->(flag) do
    if flag
      joins(:news_readers)
    elsif flag == false
      left_join = left_joins(:news_readers)
      left_join.where(NewsRead: { NewsReadID: nil })
        .or(left_join.where(top_story: true, datetime: 3.months.ago..Float::INFINITY))
    end
  end

  def self.school_level
    where(<<~SQL)
      News.ClassID = 0 OR
      News.Exposure = 2
    SQL
  end

  def self.all_articles(role, id)
    conditions = []
    query =
      if role == :employee
        conditions << "ClassTeachers.UserID = #{id}"
        <<~SQL
          LEFT JOIN ClassTeachers
          ON ClassTeachers.ClassID = News.ClassID
        SQL
      else
        field = role == :family ? 'FamilyID' : 'StudentID'
        conditions << "Students.#{field} = #{id}"
        <<~SQL
          LEFT JOIN ClassStudents
          ON ClassStudents.ClassID = News.ClassID
          LEFT JOIN Students
          ON Students.StudentID = ClassStudents.StudentID
        SQL
      end

    conditions += ['News.ClassID = 0', 'News.Exposure = 2']
    joins(query).where(conditions.join(' OR ')).distinct
  end
end