<commit_before>package db

import com.gilt.apidoc.api.v0.models.{Application, Reference}
import com.gilt.apidoc.internal.v0.models.{Change, ChangeVersion, Difference, DifferenceBreaking, DifferenceNonBreaking, DifferenceUndefinedType}
import com.gilt.apidoc.internal.v0.models.json._
import com.gilt.apidoc.api.v0.models.{User, Version}
import anorm._
import play.api.db._
import play.api.Play.current
import play.api.libs.json._
import java.util.UUID
import org.postgresql.util.PSQLException
import scala.util.{Try, Success, Failure}

object ChangesDao {

  private val BaseQuery = """
    select changes.guid,
           applications.guid as application_guid,
           applications.key as application_key,
           from_version.guid::varchar as from_guid,
           from_version.version as from_version,
           to_version.guid::varchar as to_guid,
           to_version.version as to_version,
           changes.type,
           changes.description
      from changes
      join applications on applications.guid = changes.application_guid and applications.deleted_at is null
      join organizations on organizations.guid = applications.organization_guid and organizations.deleted_at is null
      join versions from_version on from_version.guid = changes.from_version_guid
      join versions to_version on to_version.guid = changes.to_version_guid
     where true
  """

  private val InsertQuery = """
    insert into changes
    (guid, application_guid, from_version_guid, to_version_guid, type, description, created_by_guid)
    values
    ({guid}::uuid, {application_guid}::uuid, {from_version_guid}::uuid, {to_version_guid}::uuid, {type}, {description}, {created_by_guid}::uuid)
  """

  def upsert(createdBy: User, fromVersion: Version, toVersion: Version, differences: Seq[Difference]) {
    assert(
      fromVersion.guid != toVersion.guid,
      "Versions must be different"
    )

    assert(
      fromVersion.application.guid == toVersion.application.guid,
      "Versions must belong to same application"
    )

    DB.withTransaction { implicit c =>

      differences.map { d =>
        d match {
          case DifferenceBreaking(desc) => ("breaking", desc)
          case DifferenceNonBreaking(desc) => ("non_breaking", desc)
          case DifferenceUndefinedType(desc) => {
            sys.error(s"Unrecognized difference type: $desc")
          }
        }
      }.distinct.foreach {
        case (differenceType, description) => {
          Try(
            SQL(InsertQuery).on(
              'guid -> UUID.randomUUID,
              'application_guid -> fromVersion.application.guid,
              'from_version_guid -> fromVersion.guid,
              'to_version_guid -> toVersion.guid,
              'type -> differenceType,
              'description -> description,
              'created_by_guid -> createdBy.guid
            ).execute()
          ) match {
            case Success(_) => {}
            case Failure(e) => e match {
              case e: PSQLException => {
                findAll(
                  Authorization.All,
                  fromVersionGuid = Some(fromVersion.guid),
                  toVersionGuid = Some(toVersion.guid),
                  description = Some(description)
                ).headOption.getOrElse {
                  sys.error("Failed to create change: " + e)
                }
              }
            }
          }
        }
      }
    }
  }

  def findByGuid(authorization: Authorization, guid: UUID): Option[Change] = {
    findAll(authorization, guid = Some(guid), limit = 1).headOption
  }

  def findAll(
    authorization: Authorization,
    guid: Option[UUID] = None,
    organizationGuid: Option[UUID] = None,
    applicationKey: Option[String] = None,
    applicationGuid: Option[UUID] = None,
    fromVersionGuid: Option[UUID] = None,
    toVersionGuid: Option[UUID] = None,
    description: Option[String] = None,
    limit: Long = 25,
    offset: Long = 0
  ): Seq[Change] = {
    val sql = Seq(
      Some(BaseQuery.trim),
      authorization.applicationFilter().map(v => "and " + v),
      guid.map { v => "and changes.guid = {guid}::uuid" },
      organizationGuid.map { v => "and organizations.guid = {organization_guid}::uuid" },
      applicationGuid.map { v => "and applications.guid = {application_guid}::uuid" },
      applicationKey.map { v => "and applications.key = lower(trim({application_key}))" },
      fromVersionGuid.map { v => "and changes.from_version_guid = {from_version_guid}::uuid" },
      toVersionGuid.map { v => "and changes.to_version_guid = {to_version_guid}::uuid" },
      description.map { v => "and lower(changes.description) = lower(trim({description}))" },
      Some(s"order by changes.created_at limit ${limit} offset ${offset}")
    ).flatten.mkString("\n   ")

    val bind = Seq[Option[NamedParameter]](
      guid.map('guid -> _.toString),
      organizationGuid.map('organization_guid -> _.toString),
      applicationGuid.map('application_guid -> _.toString),
      applicationKey.map('application_key -> _.toString),
      fromVersionGuid.map('from_version_guid -> _.toString),
      toVersionGuid.map('to_version_guid -> _.toString),
      description.map('description -> _)
    ).flatten ++ authorization.bindVariables

    DB.withConnection { implicit c =>
      SQL(sql).on(bind: _*)().toList.map { fromRow(_) }.toSeq
    }
  }

  private[db] def fromRow(
    row: anorm.Row
  ): Change = {
    Change(
      guid = row[UUID]("guid"),
      application = Reference(row[UUID]("application_guid"), row[String]("application_key")),
      fromVersion = ChangeVersion(row[UUID]("from_guid"), row[String]("from_version")),
      toVersion = ChangeVersion(row[UUID]("to_guid"), row[String]("to_version")),
      difference = row[String]("type") match {
        case "breaking" => DifferenceBreaking(row[String]("description"))
        case "non_breaking" => DifferenceNonBreaking(row[String]("description"))
        case other => DifferenceUndefinedType(other)
      }
    )<commit_msg>Change default sort order so most recent change is first<commit_after>package db

import com.gilt.apidoc.api.v0.models.{Application, Reference}
import com.gilt.apidoc.internal.v0.models.{Change, ChangeVersion, Difference, DifferenceBreaking, DifferenceNonBreaking, DifferenceUndefinedType}
import com.gilt.apidoc.internal.v0.models.json._
import com.gilt.apidoc.api.v0.models.{User, Version}
import anorm._
import play.api.db._
import play.api.Play.current
import play.api.libs.json._
import java.util.UUID
import org.postgresql.util.PSQLException
import scala.util.{Try, Success, Failure}

object ChangesDao {

  private val BaseQuery = """
    select changes.guid,
           applications.guid as application_guid,
           applications.key as application_key,
           from_version.guid::varchar as from_guid,
           from_version.version as from_version,
           to_version.guid::varchar as to_guid,
           to_version.version as to_version,
           changes.type,
           changes.description
      from changes
      join applications on applications.guid = changes.application_guid and applications.deleted_at is null
      join organizations on organizations.guid = applications.organization_guid and organizations.deleted_at is null
      join versions from_version on from_version.guid = changes.from_version_guid
      join versions to_version on to_version.guid = changes.to_version_guid
     where true
  """

  private val InsertQuery = """
    insert into changes
    (guid, application_guid, from_version_guid, to_version_guid, type, description, created_by_guid)
    values
    ({guid}::uuid, {application_guid}::uuid, {from_version_guid}::uuid, {to_version_guid}::uuid, {type}, {description}, {created_by_guid}::uuid)
  """

  def upsert(createdBy: User, fromVersion: Version, toVersion: Version, differences: Seq[Difference]) {
    assert(
      fromVersion.guid != toVersion.guid,
      "Versions must be different"
    )

    assert(
      fromVersion.application.guid == toVersion.application.guid,
      "Versions must belong to same application"
    )

    DB.withTransaction { implicit c =>

      differences.map { d =>
        d match {
          case DifferenceBreaking(desc) => ("breaking", desc)
          case DifferenceNonBreaking(desc) => ("non_breaking", desc)
          case DifferenceUndefinedType(desc) => {
            sys.error(s"Unrecognized difference type: $desc")
          }
        }
      }.distinct.foreach {
        case (differenceType, description) => {
          Try(
            SQL(InsertQuery).on(
              'guid -> UUID.randomUUID,
              'application_guid -> fromVersion.application.guid,
              'from_version_guid -> fromVersion.guid,
              'to_version_guid -> toVersion.guid,
              'type -> differenceType,
              'description -> description,
              'created_by_guid -> createdBy.guid
            ).execute()
          ) match {
            case Success(_) => {}
            case Failure(e) => e match {
              case e: PSQLException => {
                findAll(
                  Authorization.All,
                  fromVersionGuid = Some(fromVersion.guid),
                  toVersionGuid = Some(toVersion.guid),
                  description = Some(description)
                ).headOption.getOrElse {
                  sys.error("Failed to create change: " + e)
                }
              }
            }
          }
        }
      }
    }
  }

  def findByGuid(authorization: Authorization, guid: UUID): Option[Change] = {
    findAll(authorization, guid = Some(guid), limit = 1).headOption
  }

  def findAll(
    authorization: Authorization,
    guid: Option[UUID] = None,
    organizationGuid: Option[UUID] = None,
    applicationKey: Option[String] = None,
    applicationGuid: Option[UUID] = None,
    fromVersionGuid: Option[UUID] = None,
    toVersionGuid: Option[UUID] = None,
    description: Option[String] = None,
    limit: Long = 25,
    offset: Long = 0
  ): Seq[Change] = {
    val sql = Seq(
      Some(BaseQuery.trim),
      authorization.applicationFilter().map(v => "and " + v),
      guid.map { v => "and changes.guid = {guid}::uuid" },
      organizationGuid.map { v => "and organizations.guid = {organization_guid}::uuid" },
      applicationGuid.map { v => "and applications.guid = {application_guid}::uuid" },
      applicationKey.map { v => "and applications.key = lower(trim({application_key}))" },
      fromVersionGuid.map { v => "and changes.from_version_guid = {from_version_guid}::uuid" },
      toVersionGuid.map { v => "and changes.to_version_guid = {to_version_guid}::uuid" },
      description.map { v => "and lower(changes.description) = lower(trim({description}))" },
      Some(s"order by lower(organizations.name), lower(applications.name), changes.created_at desc limit ${limit} offset ${offset}")
    ).flatten.mkString("\n   ")

    val bind = Seq[Option[NamedParameter]](
      guid.map('guid -> _.toString),
      organizationGuid.map('organization_guid -> _.toString),
      applicationGuid.map('application_guid -> _.toString),
      applicationKey.map('application_key -> _.toString),
      fromVersionGuid.map('from_version_guid -> _.toString),
      toVersionGuid.map('to_version_guid -> _.toString),
      description.map('description -> _)
    ).flatten ++ authorization.bindVariables

    DB.withConnection { implicit c =>
      SQL(sql).on(bind: _*)().toList.map { fromRow(_) }.toSeq
    }
  }

  private[db] def fromRow(
    row: anorm.Row
  ): Change = {
    Change(
      guid = row[UUID]("guid"),
      application = Reference(row[UUID]("application_guid"), row[String]("application_key")),
      fromVersion = ChangeVersion(row[UUID]("from_guid"), row[String]("from_version")),
      toVersion = ChangeVersion(row[UUID]("to_guid"), row[String]("to_version")),
      difference = row[String]("type") match {
        case "breaking" => DifferenceBreaking(row[String]("description"))
        case "non_breaking" => DifferenceNonBreaking(row[String]("description"))
        case other => DifferenceUndefinedType(other)
      }
    )
{'fraction_non_alphanumeric': 0.1213715977304498, 'fraction_numerical': 0.001315681276210838, 'mean_word_length': 2.852391510928096, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 73, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4793763', 'n_tokens_mistral': 3487, 'n_tokens_neox': 3453, 'n_words': 720}