cake

//// A DSL to build `SELECT` queries.
////

import cake/internal/read_query.{
  AndWhere, Comment, Epilog, FromSubQuery, FromTable, GroupBy, Joins, Limit,
  NoComment, NoEpilog, NoFrom, NoGroupBy, NoJoins, NoLimit, NoOffset, NoOrderBy,
  NoSelects, NoWhere, Offset, OrWhere, OrderBy, OrderByColumn, Select,
  SelectAlias, SelectAll, SelectColumn, SelectDistinct, SelectFragment,
  SelectParam, SelectQuery, Selects, XorWhere,
}
import cake/param.{BoolParam, FloatParam, IntParam, NullParam, StringParam}
import gleam/list
import gleam/string

// ┌───────────────────────────────────────────────────────────────────────────┐
// │  read_query type re-exports                                               │
// └───────────────────────────────────────────────────────────────────────────┘

pub type Comment =
  read_query.Comment

pub type Epilog =
  read_query.Epilog

pub type Fragment =
  read_query.Fragment

pub type From =
  read_query.From

pub type GroupBy =
  read_query.GroupBy

pub type Join =
  read_query.Join

pub type Joins =
  read_query.Joins

pub type Limit =
  read_query.Limit

pub type Offset =
  read_query.Offset

pub type OrderBy =
  read_query.OrderBy

pub type OrderByDirection =
  read_query.OrderByDirection

pub type ReadQuery =
  read_query.ReadQuery

pub type Select =
  read_query.Select

pub type SelectKind =
  read_query.SelectKind

pub type SelectValue =
  read_query.SelectValue

pub type Selects =
  read_query.Selects

pub type Where =
  read_query.Where

/// Creates a `Query` from a `Select` query.
///
pub fn to_query(query qry: Select) -> ReadQuery {
  qry |> SelectQuery
}

/// Creates a column identifier off a `String`.
///
pub fn col(name nm: String) -> SelectValue {
  nm |> SelectColumn
}

/// Creates an alias off a `String`.
///
pub fn alias(value vl: SelectValue, alias als: String) -> SelectValue {
  vl |> SelectAlias(alias: als)
}

/// Creates a boolean `Param` off a `Bool`.
///
pub fn bool(value vl: Bool) -> SelectValue {
  vl |> BoolParam |> SelectParam
}

/// Creates a float `Param` off a `Float`.
///
pub fn float(value vl: Float) -> SelectValue {
  vl |> FloatParam |> SelectParam
}

/// Creates an integer `Param` off an `Int`.
///
pub fn int(value vl: Int) -> SelectValue {
  vl |> IntParam |> SelectParam
}

/// Creates a string `Param` off a `String`.
///
pub fn string(value vl: String) -> SelectValue {
  vl |> StringParam |> SelectParam
}

/// Creates an SQL `NULL` `Param`.
///
pub fn null() -> SelectValue {
  NullParam |> SelectParam
}

/// Creates a `SelectFragment` off a `Fragment`.
pub fn fragment(fragment frgmt: Fragment) -> SelectValue {
  frgmt |> SelectFragment
}

// ▒▒▒ NEW ▒▒▒

/// Creates an empty `Select` query.
///
pub fn new() -> Select {
  Select(
    kind: SelectAll,
    select: NoSelects,
    from: NoFrom,
    join: NoJoins,
    where: NoWhere,
    group_by: NoGroupBy,
    having: NoWhere,
    order_by: NoOrderBy,
    limit: NoLimit,
    offset: NoOffset,
    epilog: NoEpilog,
    comment: NoComment,
  )
}

// ▒▒▒ KIND ▒▒▒

/// Sets the kind of the `Select` query to
/// return duplicates which is the default.
///
pub fn all(query qry: Select) -> Select {
  Select(..qry, kind: SelectAll)
}

/// Sets the kind of the `Select` query to
/// return distinct rows only.
///
pub fn distinct(query qry: Select) -> Select {
  Select(..qry, kind: SelectDistinct)
}

/// Gets the kind of the `Select` query.
///
pub fn get_kind(query qry: Select, kind knd: SelectKind) -> Select {
  Select(..qry, kind: knd)
}

// ▒▒▒ FROM ▒▒▒

/// Sets the `FROM` clause of the `Select` query to a table name.
///
pub fn from_table(query qry: Select, name tbl_nm: String) -> Select {
  Select(..qry, from: FromTable(name: tbl_nm))
}

/// Sets the `FROM` clause of the `Select` query to an aliased sub-query.
///
pub fn from_sub_query(
  query qry: Select,
  sub_query sb_qry: ReadQuery,
  alias als: String,
) -> Select {
  Select(..qry, from: FromSubQuery(sub_query: sb_qry, alias: als))
}

/// Removes the `FROM` clause of the `Select` query.
///
pub fn no_from(query qry: Select) -> Select {
  Select(..qry, from: NoFrom)
}

/// Gets the `FROM` clause of the `Select` query.
///
pub fn get_from(query qry: Select) -> From {
  qry.from
}

// ▒▒▒ SELECT ▒▒▒

/// Add a `SelectValue` to the `Select` query.
///
/// If the query already has any `SelectValue`s, the new one is appended.
///
pub fn select(query qry: Select, select_value sv: SelectValue) -> Select {
  case qry.select {
    NoSelects -> Select(..qry, select: Selects([sv]))
    Selects(qry_slcts) ->
      Select(..qry, select: qry_slcts |> list.append([sv]) |> Selects)
  }
}

/// Add a `SelectValue`s to the `Select` query.
///
/// If the query already has any `SelectValue`s, they are replaced.
///
pub fn replace_select(query qry: Select, select_value sv: SelectValue) -> Select {
  case qry.select {
    NoSelects -> Select(..qry, select: Selects([sv]))
    Selects(qry_slcts) ->
      Select(..qry, select: qry_slcts |> list.append([sv]) |> Selects)
  }
}

/// Adds `SelectValue`s to the `Select` query.
///
/// If the query already has any `SelectValue`s, the new ones are appended.
///
pub fn selects(
  query qry: Select,
  select_values svs: List(SelectValue),
) -> Select {
  case svs, qry.select {
    [], _ -> qry
    svs, NoSelects -> Select(..qry, select: Selects(svs))
    svs, Selects(qry_slcts) ->
      Select(..qry, select: qry_slcts |> list.append(svs) |> Selects)
  }
}

/// Adds `SelectValue`s to the `Select` query.
///
/// If the query already has any `SelectValue`s, they are replaced.
///
pub fn replace_selects(
  query qry: Select,
  select_values svs: List(SelectValue),
) -> Select {
  case svs, qry.select {
    [], _ -> qry
    svs, NoSelects -> Select(..qry, select: Selects(svs))
    svs, Selects(qry_slcts) ->
      Select(..qry, select: qry_slcts |> list.append(svs) |> Selects)
  }
}

/// Gets the `SelectValue`s of the `Select` query.
///
pub fn get_select(query qry: Select) -> Selects {
  qry.select
}

// ▒▒▒ JOIN ▒▒▒

/// Adds a `Join` to the `Select` query.
///
pub fn join(query qry: Select, join jn: Join) -> Select {
  case qry.join {
    Joins(jns) -> Select(..qry, join: jns |> list.append([jn]) |> Joins)
    NoJoins -> Select(..qry, join: [jn] |> Joins)
  }
}

/// Replaces any `Join`s of the `Select` query with a signle `Join`.
///
pub fn replace_join(query qry: Select, join jn: Join) -> Select {
  Select(..qry, join: [jn] |> Joins)
}

/// Adds `Join`s to the `Select` query.
///
pub fn joins(query qry: Select, joins jns: List(Join)) -> Select {
  case jns, qry.join {
    [], _ -> Select(..qry, join: Joins(jns))
    jns, Joins(qry_joins) ->
      Select(..qry, join: qry_joins |> list.append(jns) |> Joins)
    jns, NoJoins -> Select(..qry, join: jns |> Joins)
  }
}

/// Replaces any `Join`s of the `Select` query with the given `Join`s.
///
pub fn replace_joins(query qry: Select, joins jns: List(Join)) -> Select {
  Select(..qry, join: jns |> Joins)
}

/// Removes any `Joins` from the `Select` query.
///
pub fn no_join(query qry: Select) -> Select {
  Select(..qry, join: NoJoins)
}

/// Gets the `Joins` of the `Select` query.
///
pub fn get_joins(query qry: Select) -> Joins {
  qry.join
}

// ▒▒▒ WHERE ▒▒▒

/// Sets an `AndWhere` or appends into an existing `AndWhere`.
///
/// - If the outermost `Where` is an `AndWhere`, the new `Where` is appended
///   to the list within `AndWhere`.
/// - If the query does not have a `Where` clause, the given `Where` is set
///   instead.
/// - If the outermost `Where` is any other kind of `Where`, this and the
///   current outermost `Where` are wrapped in an `AndWhere`.
///
pub fn where(query qry: Select, where whr: Where) -> Select {
  case qry.where {
    NoWhere -> Select(..qry, where: whr)
    AndWhere(wheres) ->
      Select(..qry, where: AndWhere(wheres |> list.append([whr])))
    _ -> Select(..qry, where: AndWhere([qry.where, whr]))
  }
}

/// Sets an `OrWhere` or appends into an existing `OrWhere`.
///
/// - If the outermost `Where` is an `OrWhere`, the new `Where` is appended
///   to the list within `OrWhere`.
/// - If the query does not have a `Where` clause, the given `Where` is set
///   instead.
/// - If the outermost `Where` is any other kind of `Where`, this and the
///   current outermost `Where` are wrapped in an `OrWhere`.
///
pub fn or_where(query qry: Select, where whr: Where) -> Select {
  case qry.where {
    NoWhere -> Select(..qry, where: whr)
    OrWhere(wheres) ->
      Select(..qry, where: OrWhere(wheres |> list.append([whr])))
    _ -> Select(..qry, where: OrWhere([qry.where, whr]))
  }
}

/// Sets an `XorWhere` or appends into an existing `XorWhere`.
///
/// - If the outermost `Where` is an `XorWhere`, the new `Where` is appended
///   to the list within `XorWhere`.
/// - If the query does not have a `Where` clause, the given `Where` is set
///   instead.
/// - If the outermost `Where` is any other kind of `Where`, this and the
///   current outermost `Where` are wrapped in an `XorWhere`.
///
/// NOTICE: This operator does not exist in Postgres or SQLite,
/// and *Cake* generates equivalent SQL using `OR` and `AND` and `NOT`.
/// This operator exists in MariaDB/MySQL.
///
pub fn xor_where(query qry: Select, where whr: Where) -> Select {
  case qry.where {
    NoWhere -> Select(..qry, where: whr)
    XorWhere(wheres) ->
      Select(..qry, where: XorWhere(wheres |> list.append([whr])))
    _ -> Select(..qry, where: XorWhere([qry.where, whr]))
  }
}

/// Replaces the `Where` in the `Select` query.
///
pub fn replace_where(query qry: Select, where whr: Where) -> Select {
  Select(..qry, where: whr)
}

/// Removes the `Where` from the `Select` query.
///
pub fn no_where(query qry: Select) -> Select {
  Select(..qry, where: NoWhere)
}

/// Gets the `Where` of the `Select` query.
///
pub fn get_where(query qry: Select) -> Where {
  qry.where
}

// ▒▒▒ HAVING ▒▒▒

/// Sets an `AndWhere` or appends into an existing `AndWhere`.
///
/// - If the outermost `Where` is an `AndWhere`, the new `Where` is appended
///   to the list within `AndWhere`.
/// - If the query does not have a `Where` clause, the given `Where` is set
///   instead.
/// - If the outermost `Where` is any other kind of `Where`, this and the
///   current outermost `Where` are wrapped in an `AndWhere`.
///
/// NOTICE: `HAVING` allows to specify constraints much like `WHERE`, but
/// filters the results after `GROUP BY` is applied instead of before. Because
/// `HAVING` uses the same semantics as `WHERE`, it
///         takes a `Where`.
///
pub fn having(query qry: Select, having whr: Where) -> Select {
  case qry.having {
    NoWhere -> Select(..qry, having: whr)
    AndWhere(wheres) ->
      Select(..qry, having: AndWhere(wheres |> list.append([whr])))
    _ -> Select(..qry, having: AndWhere([qry.having, whr]))
  }
}

/// Sets an `OrWhere` or appends into an existing `OrWhere`.
///
/// - If the outermost `Where` is an `OrWhere`, the new `Where` is appended
///   to the list within `OrWhere`.
/// - If the query does not have a `Where` clause, the given `Where` is set
///   instead.
/// - If the outermost `Where` is any other kind of `Where`, this and the
///   current outermost `Where` are wrapped in an `OrWhere`.
///
/// See function `having` on details why this takes a `Where`.
///
pub fn or_having(query qry: Select, having whr: Where) -> Select {
  case qry.having {
    NoWhere -> Select(..qry, having: whr)
    OrWhere(wheres) ->
      Select(..qry, having: OrWhere(wheres |> list.append([whr])))
    _ -> Select(..qry, having: OrWhere([qry.having, whr]))
  }
}

/// Sets an `XorWhere` or appends into an existing `XorWhere`.
///
/// - If the outermost `Where` is an `XorWhere`, the new `Where` is appended
///   to the list within `XorWhere`.
/// - If the query does not have a `Where` clause, the given `Where` is set
///   instead.
/// - If the outermost `Where` is any other kind of `Where`, this and the
///   current outermost `Where` are wrapped in an `XorWhere`.
///
/// See function `having` on details why this takes a `Where`.
///
/// NOTICE: This operator does not exist in Postgres or SQLite,
/// and *Cake* generates equivalent SQL using `OR` and `AND` and `NOT`.
/// This operator exists in MariaDB/MySQL.
///
pub fn xor_having(query qry: Select, having whr: Where) -> Select {
  case qry.having {
    NoWhere -> Select(..qry, having: whr)
    XorWhere(wheres) ->
      Select(..qry, having: XorWhere(wheres |> list.append([whr])))
    _ -> Select(..qry, having: XorWhere([qry.having, whr]))
  }
}

/// Replaces `HAVING` in the `Select` query.
///
/// See function `having` on details why this takes a `Where`.
///
pub fn replace_having(query qry: Select, having whr: Where) -> Select {
  Select(..qry, having: whr)
}

/// Removes `HAVING` from the `Select` query.
///
pub fn no_having(query qry: Select) -> Select {
  Select(..qry, having: NoWhere)
}

/// Gets`HAVING` in the `Select` query.
///
/// See function `having` on details why this returns a `Where`.
///
pub fn get_having(query qry: Select) -> Where {
  qry.having
}

// ▒▒▒ GROUP BY ▒▒▒

/// Sets or appends `GroupBy` a single into an existing `GroupBy`.
///
pub fn group_by(query qry: Select, group_by grpb: String) -> Select {
  case qry.group_by {
    NoGroupBy -> Select(..qry, group_by: GroupBy([grpb]))
    GroupBy(grpbs) ->
      Select(..qry, group_by: GroupBy(grpbs |> list.append([grpb])))
  }
}

/// Replaces `GroupBy` with a single `GroupBy`.
///
pub fn replace_group_by(query qry: Select, group_by grpb: String) -> Select {
  Select(..qry, group_by: GroupBy([grpb]))
}

/// Sets or appends a list of `GroupBy` into an existing `GroupBy`.
///
pub fn group_bys(query qry: Select, group_bys grpbs: List(String)) -> Select {
  case qry.group_by {
    NoGroupBy -> Select(..qry, group_by: GroupBy(grpbs))
    GroupBy(grpbs) ->
      Select(..qry, group_by: GroupBy(grpbs |> list.append(grpbs)))
  }
}

/// Replaces `GroupBy` with a list of `GroupBy`s.
///
pub fn replace_group_bys(
  query qry: Select,
  group_bys grpbs: List(String),
) -> Select {
  Select(..qry, group_by: GroupBy(grpbs))
}

/// Removes `GroupBy` from the `Select` query.
///
pub fn no_group_by(query qry: Select) -> Select {
  Select(..qry, group_by: NoGroupBy)
}

/// Gets `GroupBy` in the `Select` query.
///
pub fn get_group_by(query qry: Select) -> GroupBy {
  qry.group_by
}

// ▒▒▒ LIMIT & OFFSET ▒▒▒

/// Sets a `Limit` in the `Select` query.
///
pub fn limit(query qry: Select, limit lmt: Int) -> Select {
  let lmt = lmt |> read_query.limit_new
  Select(..qry, limit: lmt)
}

/// Removes `Limit` from the `Select` query.
///
pub fn no_limit(query qry: Select) -> Select {
  Select(..qry, limit: NoLimit)
}

/// Gets `Limit` in the `Select` query.
///
pub fn get_limit(query qry: Select) -> Limit {
  qry.limit
}

/// Sets an `Offset` in the `Select` query.
///
pub fn offset(query qry: Select, offst offst: Int) -> Select {
  let offst = offst |> read_query.offset_new
  Select(..qry, offset: offst)
}

/// Removes `Offset` from the `Select` query.
///
pub fn no_offset(query qry: Select) -> Select {
  Select(..qry, offset: NoOffset)
}

/// Gets `Offset` in the `Select` query.
///
pub fn get_offset(query qry: Select) -> Offset {
  qry.offset
}

// ▒▒▒ ORDER BY ▒▒▒

/// Defines the direction of an `OrderBy`.
///
pub type Direction {
  Asc
  Desc
}

fn map_order_by_direction_constructor(in: Direction) -> OrderByDirection {
  case in {
    Asc -> read_query.Asc
    Desc -> read_query.Desc
  }
}

/// Creates or appends an ascending `OrderBy`.
///
pub fn order_by_asc(query qry: Select, by ordb: String) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.Asc)]),
    append: True,
  )
}

/// Creates or appends an ascending `OrderBy` with `NULLS FIRST`.
///
/// NOTICE: MariaDB/MySQL do not support `NULLS FIRST` out of the box.
///
pub fn order_by_asc_nulls_first(query qry: Select, by ordb: String) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.AscNullsFirst)]),
    append: True,
  )
}

/// Creates or appends an ascending `OrderBy` with `NULLS LAST`.
///
/// NOTICE: MariaDB/MySQL do not support `NULLS LAST` out of the box.
///
pub fn order_by_asc_nulls_last(query qry: Select, by ordb: String) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.AscNullsFirst)]),
    append: True,
  )
}

/// Replaces the `OrderBy` a single ascending `OrderBy`.
///
pub fn replace_order_by_asc(query qry: Select, by ordb: String) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.Asc)]),
    append: False,
  )
}

/// Replaces the `OrderBy` a single ascending `OrderBy` with `NULLS FIRST`.
///
/// NOTICE: MariaDB/MySQL do not support `NULLS FIRST` out of the box.
///
pub fn replace_order_by_asc_nulls_first(
  query qry: Select,
  by ordb: String,
) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.AscNullsFirst)]),
    append: False,
  )
}

/// Replaces the `OrderBy` a single ascending `OrderBy` with `NULLS LAST`.
///
/// NOTICE: MariaDB/MySQL do not support `NULLS LAST` out of the box.
///
pub fn replace_order_by_asc_nulls_last(
  query qry: Select,
  by ordb: String,
) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.AscNullsFirst)]),
    append: False,
  )
}

/// Creates or appends a descending `OrderBy`.
///
pub fn order_by_desc(query qry: Select, by ordb: String) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.Desc)]),
    append: True,
  )
}

/// Creates or appends a descending order with `NULLS FIRST`.
///
/// NOTICE: MariaDB/MySQL do not support `NULLS FIRST` out of the box.
///
pub fn order_by_desc_nulls_first(query qry: Select, by ordb: String) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.DescNullsFirst)]),
    append: True,
  )
}

/// Creates or appends a descending `OrderBy` with `NULLS LAST`.
///
/// NOTICE: MariaDB/MySQL do not support `NULLS LAST` out of the box.
///
pub fn order_by_desc_nulls_last(query qry: Select, by ordb: String) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.DescNullsFirst)]),
    append: True,
  )
}

/// Replaces the `OrderBy` a single descending order.
///
pub fn replace_order_by_desc(query qry: Select, by ordb: String) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.Desc)]),
    append: False,
  )
}

/// Replaces the `OrderBy` a single descending order with `NULLS FIRST`.
///
/// NOTICE: MariaDB/MySQL do not support `NULLS FIRST` out of the box.
///
pub fn replace_order_by_desc_nulls_first(
  query qry: Select,
  by ordb: String,
) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.DescNullsFirst)]),
    append: False,
  )
}

/// Replaces the `OrderBy` a single descending `OrderBy` with `NULLS LAST`.
///
/// NOTICE: MariaDB/MySQL do not support `NULLS LAST` out of the box.
///
pub fn replace_order_by_desc_nulls_last(
  query qry: Select,
  by ordb: String,
) -> Select {
  qry
  |> read_query.select_order_by(
    by: OrderBy(values: [OrderByColumn(ordb, read_query.DescNullsFirst)]),
    append: False,
  )
}

/// Creates or appends an `OrderBy` a column with a direction.
///
/// The direction can either `ASC` or `DESC`.
///
pub fn order_by(
  query qry: Select,
  by ordb: String,
  direction dir: Direction,
) -> Select {
  let dir = dir |> map_order_by_direction_constructor
  qry
  |> read_query.select_order_by(
    OrderBy(values: [OrderByColumn(ordb, dir)]),
    True,
  )
}

/// Replaces the `OrderBy` a column with a direction.
///
pub fn replace_order_by(
  query qry: Select,
  by ordb: String,
  direction dir: Direction,
) -> Select {
  let dir = dir |> map_order_by_direction_constructor
  qry
  |> read_query.select_order_by(
    OrderBy(values: [OrderByColumn(ordb, dir)]),
    False,
  )
}

/// Removes the `OrderBy` from the `Select` query.
///
pub fn no_order_by(query qry: Select) -> Select {
  Select(..qry, order_by: NoOrderBy)
}

/// Gets the `OrderBy` from the `Select` query.
///
pub fn get_order_by(query qry: Select) -> OrderBy {
  qry.order_by
}

// ▒▒▒ EPILOG ▒▒▒

/// Appends an `Epilog` to the `Select` query.
///
pub fn epilog(query qry: Select, epilog eplg: String) -> Select {
  let eplg = eplg |> string.trim
  case eplg {
    "" -> Select(..qry, epilog: NoEpilog)
    _ -> Select(..qry, epilog: { " " <> eplg } |> Epilog)
  }
}

/// Removes the `Epilog` from the `Select` query.
///
pub fn no_epilog(query qry: Select) -> Select {
  Select(..qry, epilog: NoEpilog)
}

/// Gets the `Epilog` from the `Select` query.
///
pub fn get_epilog(query qry: Select) -> Epilog {
  qry.epilog
}

// ▒▒▒ COMMENT ▒▒▒

/// Appends a `Comment` to the `Select` query.
///
pub fn comment(query qry: Select, comment cmmnt: String) -> Select {
  let cmmnt = cmmnt |> string.trim
  case cmmnt {
    "" -> Select(..qry, comment: NoComment)
    _ -> Select(..qry, comment: { " " <> cmmnt } |> Comment)
  }
}

/// Removes the `Comment` from the `Select` query.
///
pub fn no_comment(query qry: Select) -> Select {
  Select(..qry, comment: NoComment)
}

/// Gets the `Comment` from the `Select` query.
///
pub fn get_comment(query qry: Select) -> Comment {
  qry.comment
}