Feedbacks.scala

package hod.btlg.persistence

import hod.btlg.DBFiddle
import io.getquill.*
import io.getquill.extras.OffsetDateTimeOps // Add this import

import java.time.OffsetDateTime

case class Feedback(
  internalId: Long,
  userId: Option[Long],
  feedbackType: String,
  message: String,
  created: OffsetDateTime
)

object Feedbacks {
  lazy val ctx = DBFiddle.pctx

  import ctx.*

  val createFeedbackTable = quote {
    sql"""
         CREATE TABLE IF NOT EXISTS feedback (
           internal_id SERIAL PRIMARY KEY,
           user_id INTEGER REFERENCES brain_lift_user(internal_id),
           feedback_type VARCHAR(255) NOT NULL,
           message TEXT,
           created TIMESTAMP WITH TIME ZONE NOT NULL
         );
      """.as[Action[Int]]
  }

  val createIndexOnFeedbackTable = quote {
    sql"""
         CREATE INDEX IF NOT EXISTS idx_feedback_user_id ON feedback (user_id);
         CREATE INDEX IF NOT EXISTS idx_feedback_type ON feedback (feedback_type);
         CREATE INDEX IF NOT EXISTS idx_feedback_created ON feedback (created);
      """.as[Action[Int]]
  }

  def init(): Unit = {
    ctx.run(createFeedbackTable)
    ctx.run(createIndexOnFeedbackTable)
  }

  def insertFeedback(feedback: Feedback) = {
    val insert = quote {
      query[Feedback].insertValue(lift(feedback)).returningGenerated(_.internalId)
    }
    ctx.run(insert)
  }

  def findFeedbackByUser(userId: Long) = {
    val find = quote {
      query[Feedback].filter(_.userId.contains(lift(userId)))
    }
    ctx.run(find)
  }

  def findFeedbackByType(feedbackType: String) = {
    val find = quote {
      query[Feedback].filter(_.feedbackType == lift(feedbackType))
    }
    ctx.run(find)
  }

  def findFeedbackByDateRange(start: OffsetDateTime, end: OffsetDateTime) = {
    val find = quote {
      query[Feedback].filter(f => f.created >= lift(start) && f.created <= lift(end))
    }
    ctx.run(find)
  }

  def deleteFeedback(internalId: Long) = {
    val delete = quote {
      query[Feedback].filter(_.internalId == lift(internalId)).delete
    }
    ctx.run(delete)
  }

  def findAllFeedback() = {
    val find = quote {
      query[Feedback]
    }
    ctx.run(find)
  }
}