-module(cake@join).
-compile([no_auto_import, nowarn_unused_vars, nowarn_unused_function, nowarn_nomatch, inline]).
-define(FILEPATH, "src/cake/join.gleam").
-export([table/1, sub_query/1, inner/3, left/3, right/3, full/3, cross/2, inner_lateral/2, left_lateral/2, cross_lateral/2]).

-if(?OTP_RELEASE >= 27).
-define(MODULEDOC(Str), -moduledoc(Str)).
-define(DOC(Str), -doc(Str)).
-else.
-define(MODULEDOC(Str), -compile([])).
-define(DOC(Str), -compile([])).
-endif.

?MODULEDOC(
    " Functions to build `JOIN` clauses of SQL queries.\n"
    "\n"
    " Builds `JOIN` clauses for `SELECT`, `UPDATE`, and `DELETE` queries.\n"
    " Tables, views and sub-queries can be joined together.\n"
    "\n"
    " ## Aliases\n"
    "\n"
    " ```gleam\n"
    " import cake/join as j\n"
    " import cake/where as w\n"
    " ```\n"
    "\n"
    " ---\n"
    "\n"
    " ## Concept\n"
    "\n"
    " A `Join` is constructed in two steps:\n"
    "\n"
    " 1. Build a **target** — the table or sub-query to join against.\n"
    " 2. Wrap it in a **join type** with an `ON` condition and an alias.\n"
    "\n"
    " ```mermaid\n"
    " flowchart LR\n"
    "     A[j.table / j.sub_query] --> B[JoinTarget]\n"
    "     B --> C[j.inner / j.left / j.right / j.full / j.cross]\n"
    "     C --> D[Join]\n"
    "     D --> E[s.join / u.join / d.join]\n"
    " ```\n"
    "\n"
    " ---\n"
    "\n"
    " ## Step 1: Build a JoinTarget\n"
    "\n"
    " ### `table(table_name) -> JoinTarget`\n"
    "\n"
    " ```gleam\n"
    " j.table(\"users\")\n"
    " ```\n"
    "\n"
    " ### `sub_query(query) -> JoinTarget`\n"
    "\n"
    " ```gleam\n"
    " import cake/select as s\n"
    "\n"
    " let sub =\n"
    "   s.new()\n"
    "   |> s.from_table(\"orders\")\n"
    "   |> s.select_cols([\"user_id\", \"SUM(amount)\"])\n"
    "   |> s.group_by(\"user_id\")\n"
    "   |> s.to_query\n"
    "\n"
    " j.sub_query(sub)\n"
    " ```\n"
    "\n"
    " ---\n"
    "\n"
    " ## Step 2: Choose a Join Type\n"
    "\n"
    " All join constructors take:\n"
    "\n"
    " - `with` — a `JoinTarget`\n"
    " - `on` — a `Where` condition (from `cake/where`)\n"
    " - `alias` — a SQL alias for the joined table/sub-query\n"
    "\n"
    " ### `inner(with, on, alias) -> Join`\n"
    "\n"
    " Returns only rows with matches on both sides.\n"
    "\n"
    " ```gleam\n"
    " j.inner(\n"
    "   with: j.table(\"orders\"),\n"
    "   on: w.eq(w.col(\"users.id\"), w.col(\"orders.user_id\")),\n"
    "   alias: \"orders\",\n"
    " )\n"
    " // INNER JOIN orders ON users.id = orders.user_id\n"
    " ```\n"
    "\n"
    " ### `left(with, on, alias) -> Join`\n"
    "\n"
    " Returns all rows from the left table, with `NULL` for non-matching right rows.\n"
    " To make it **exclusive** (only left rows with no right match), use the normal\n"
    " join condition in `on` and add `WHERE right.key IS NULL` as a query `WHERE`\n"
    " filter.\n"
    "\n"
    " ```gleam\n"
    " j.left(\n"
    "   with: j.table(\"profiles\"),\n"
    "   on: w.eq(w.col(\"users.id\"), w.col(\"profiles.user_id\")),\n"
    "   alias: \"profiles\",\n"
    " )\n"
    " // LEFT JOIN profiles ON users.id = profiles.user_id\n"
    " ```\n"
    "\n"
    " ### `right(with, on, alias) -> Join`\n"
    "\n"
    " Returns all rows from the right table, with `NULL` for non-matching left rows.\n"
    "\n"
    " ### `full(with, on, alias) -> Join`\n"
    "\n"
    " Returns all rows from both tables, with `NULL` fill on non-matching sides.\n"
    "\n"
    " ### `cross(with, alias) -> Join`\n"
    "\n"
    " Cartesian product — every combination of rows. No `ON` condition.\n"
    "\n"
    " ```gleam\n"
    " j.cross(with: j.table(\"sizes\"), alias: \"sizes\")\n"
    " // CROSS JOIN sizes\n"
    " ```\n"
    "\n"
    " ---\n"
    "\n"
    " ## Join type summary\n"
    "\n"
    " ```mermaid\n"
    " flowchart TD\n"
    "     A[Join types] --> B[INNER JOIN\\nMatching rows only]\n"
    "     A --> C[LEFT JOIN\\nAll left + matching right]\n"
    "     A --> D[RIGHT JOIN\\nAll right + matching left]\n"
    "     A --> E[FULL JOIN\\nAll rows both sides]\n"
    "     A --> F[CROSS JOIN\\nCartesian product]\n"
    "     A --> G[LATERAL joins\\nPG + MySQL only]\n"
    " ```\n"
    "\n"
    " | Function             | SQL                              | Notes              |\n"
    " | ----------           | -------------------------------- | ----------         |\n"
    " | `inner(with, on, alias)`     | `INNER JOIN`                     |                       |\n"
    " | `left(with, on, alias)`      | `LEFT JOIN`                      |                       |\n"
    " | `right(with, on, alias)`     | `RIGHT JOIN`                     |                       |\n"
    " | `full(with, on, alias)`      | `FULL JOIN`                      |                       |\n"
    " | `cross(with, alias)`         | `CROSS JOIN`                     | No ON clause          |\n"
    " | `inner_lateral(with, alias)` | `INNER JOIN LATERAL ... ON TRUE` | 🐘 PG 9.3+ / 🐬 MySQL |\n"
    " | `left_lateral(with, alias)`  | `LEFT JOIN LATERAL ... ON TRUE`  | 🐘 PG 9.3+ / 🐬 MySQL |\n"
    " | `cross_lateral(with, alias)` | `CROSS JOIN LATERAL`             | 🐘 PG 9.3+ / 🐬 MySQL |\n"
    "\n"
    " ---\n"
    "\n"
    " ## LATERAL Joins\n"
    "\n"
    " `LATERAL` joins allow the right-hand sub-query to reference columns from the\n"
    " left-hand table. This is particularly useful for per-row sub-queries such as\n"
    " finding the N most recent related records.\n"
    "\n"
    " > ⚠️ `LATERAL` joins are not optimised by the query planner and can be very\n"
    " > slow on large datasets, especially when the sub-query returns many rows.\n"
    "\n"
    " ```gleam\n"
    " import cake/select as s\n"
    " import cake/join as j\n"
    " import cake/where as w\n"
    "\n"
    " let latest_order =\n"
    "   s.new()\n"
    "   |> s.from_table(\"orders\")\n"
    "   |> s.col(\"amount\")\n"
    "   |> s.where(w.eq(w.col(\"orders.user_id\"), w.col(\"users.id\")))\n"
    "   |> s.order_by_desc(\"created_at\")\n"
    "   |> s.limit(1)\n"
    "   |> s.to_query\n"
    "\n"
    " s.new()\n"
    " |> s.from_table(\"users\")\n"
    " |> s.join(j.left_lateral(with: j.sub_query(latest_order), alias: \"lo\"))\n"
    " |> s.select_cols([\"users.name\", \"lo.amount\"])\n"
    " |> s.to_query\n"
    " ```\n"
    "\n"
    " ---\n"
    "\n"
    " ## Exclusive Join Patterns\n"
    "\n"
    " Exclusive joins return rows that exist in **only one** side of the join.\n"
    " They are expressed using a standard `left`/`right`/`full` join with the\n"
    " normal `ON` condition, then filtering rows in the query `WHERE` clause with\n"
    " an `IS NULL` check on the outer side's key.\n"
    "\n"
    " ```mermaid\n"
    " flowchart LR\n"
    "     A[Exclusive LEFT\\nonly left has no right match] -->|add WHERE b.key IS NULL| B[j.left ... + w.is_null]\n"
    "     C[Exclusive RIGHT\\nonly right has no left match] -->|add WHERE a.key IS NULL| D[j.right ... + w.is_null]\n"
    "     E[Exclusive FULL\\nrows with no match on either side] -->|add WHERE a.key IS NULL OR b.key IS NULL| F[j.full ... + or-is-null]\n"
    " ```\n"
    "\n"
    " ```gleam\n"
    " // Users who have NO orders\n"
    " j.left(\n"
    "   with: j.table(\"orders\"),\n"
    "   on: w.eq(w.col(\"users.id\"), w.col(\"orders.user_id\")),\n"
    "   alias: \"orders\",\n"
    " )\n"
    " // Then filter in WHERE: w.is_null(w.col(\"orders.user_id\"))\n"
    " ```\n"
    "\n"
    " ---\n"
    "\n"
    " ## Self Join\n"
    "\n"
    " Join a table to itself by using the same table name with a different alias.\n"
    "\n"
    " ```gleam\n"
    " j.inner(\n"
    "   with: j.table(\"employees\"),\n"
    "   on: w.eq(w.col(\"e.manager_id\"), w.col(\"manager.id\")),\n"
    "   alias: \"manager\",\n"
    " )\n"
    " ```\n"
    "\n"
    " ---\n"
    "\n"
    " ## Full Example\n"
    "\n"
    " ```gleam\n"
    " import cake/select as s\n"
    " import cake/join as j\n"
    " import cake/where as w\n"
    "\n"
    " s.new()\n"
    " |> s.from_table(\"orders\")\n"
    " |> s.select_cols([\"orders.id\", \"users.name\", \"products.title\"])\n"
    " |> s.joins([\n"
    "   j.inner(\n"
    "     with: j.table(\"users\"),\n"
    "     on: w.eq(w.col(\"orders.user_id\"), w.col(\"users.id\")),\n"
    "     alias: \"users\",\n"
    "   ),\n"
    "   j.left(\n"
    "     with: j.table(\"products\"),\n"
    "     on: w.eq(w.col(\"orders.product_id\"), w.col(\"products.id\")),\n"
    "     alias: \"products\",\n"
    "   ),\n"
    " ])\n"
    " |> s.where(w.eq(w.col(\"orders.status\"), w.string(\"paid\")))\n"
    " |> s.to_query\n"
    " ```\n"
    "\n"
    "\n"
    " ## Supported join kinds\n"
    "\n"
    " - `INNER JOIN`\n"
    " - `LEFT JOIN`, inclusive, same as `LEFT OUTER JOIN`,\n"
    " - `RIGHT JOIN`, inclusive, same as `RIGHT OUTER JOIN`,\n"
    " - `FULL JOIN`, inclusive, same as `FULL OUTER JOIN`,\n"
    " - `CROSS JOIN`\n"
    "\n"
    " You can also build following joins using the provided query builder\n"
    " functions combined with a `WHERE` filter on the outer side:\n"
    "\n"
    " - `SELF JOIN`: Use the same table, view, or sub-query with a different\n"
    "    alias.\n"
    " - `EXCLUSIVE LEFT JOIN`: `LEFT JOIN` + `WHERE b.key IS NULL` filter\n"
    " - `EXCLUSIVE RIGHT JOIN`: `RIGHT JOIN` + `WHERE a.key IS NULL` filter\n"
    " - `EXCLUSIVE FULL JOIN`: `FULL JOIN` + `WHERE a.key IS NULL OR b.key IS NULL` filter\n"
    "\n"
    "\n"
    " <!-- html assets for docs gen -->\n"
    " <style>\n"
    "  .page {\n"
    "    display: block;\n"
    "  }\n"
    "  .content {\n"
    "    width: auto;\n"
    "    max-width: none;\n"
    "  }\n"
    " </style>\n"
    " <!--<script src=\"https://cdn.jsdelivr.net/npm/@mermaid-js/tiny@11/dist/mermaid.tiny.js\"></script>-->\n"
    " <script\n"
    "   src=\"https://cdn.jsdelivr.net/npm/mermaid/dist/mermaid.min.js\"\n"
    "   integrity=\"sha256-cBN+d7snO7LvlyuG6LBADMqL5TyyW/xFkRoYbcmGZd4=\"\n"
    "   crossorigin=\"anonymous\"\n"
    " ></script>\n"
    " <script>\n"
    " (callback => document.readyState !== 'loading' ? callback() : document.addEventListener('DOMContentLoaded', callback, { once: true }))(() => {\n"
    "   mermaid.initialize({ startOnLoad: false })\n"
    "   mermaid.run({\n"
    "     querySelector: \".language-mermaid\",\n"
    "   })\n"
    " })\n"
    " </script>\n"
    "\n"
).

-file("src/cake/join.gleam", 304).
?DOC(" Create a `JOIN` target from a table name.\n").
-spec table(binary()) -> cake@internal@read_query:join_target().
table(Table_name) ->
    _pipe = Table_name,
    {join_table, _pipe}.

-file("src/cake/join.gleam", 310).
?DOC(" Create a `JOIN` target from a sub-query.\n").
-spec sub_query(cake@internal@read_query:read_query()) -> cake@internal@read_query:join_target().
sub_query(Sub_query) ->
    _pipe = Sub_query,
    {join_sub_query, _pipe}.

-file("src/cake/join.gleam", 316).
?DOC(" Create an `INNER JOIN`.\n").
-spec inner(
    cake@internal@read_query:join_target(),
    cake@internal@read_query:where(),
    binary()
) -> cake@internal@read_query:join().
inner(With, On, Alias) ->
    _pipe = With,
    {inner_join, _pipe, Alias, On}.

-file("src/cake/join.gleam", 330).
?DOC(
    " Creates a `LEFT JOIN`.\n"
    "\n"
    " Also called `LEFT OUTER JOIN`.\n"
    "\n"
    " _Inclusive_ by default.\n"
    "\n"
    " To make it _exclusive_ (only left rows with no right match), use the\n"
    " normal join condition in `on` and add `WHERE b.key IS NULL` as a\n"
    " `WHERE` filter on the query.\n"
).
-spec left(
    cake@internal@read_query:join_target(),
    cake@internal@read_query:where(),
    binary()
) -> cake@internal@read_query:join().
left(With, On, Alias) ->
    _pipe = With,
    {left_join, _pipe, Alias, On}.

-file("src/cake/join.gleam", 344).
?DOC(
    " Creates a `RIGHT JOIN`.\n"
    "\n"
    " Also called `RIGHT OUTER JOIN`.\n"
    "\n"
    " _Inclusive_ by default.\n"
    "\n"
    " To make it _exclusive_ (only right rows with no left match), use the\n"
    " normal join condition in `on` and add `WHERE a.key IS NULL` as a\n"
    " `WHERE` filter on the query.\n"
).
-spec right(
    cake@internal@read_query:join_target(),
    cake@internal@read_query:where(),
    binary()
) -> cake@internal@read_query:join().
right(With, On, Alias) ->
    _pipe = With,
    {right_join, _pipe, Alias, On}.

-file("src/cake/join.gleam", 358).
?DOC(
    " Creates a `FULL JOIN`.\n"
    "\n"
    " Also called `FULL OUTER JOIN`.\n"
    "\n"
    " _Inclusive_ by default.\n"
    "\n"
    " To make it _exclusive_ (only rows with no match on either side), use\n"
    " the normal join condition in `on` and add\n"
    " `WHERE a.key IS NULL OR b.key IS NULL` as a `WHERE` filter on the query.\n"
).
-spec full(
    cake@internal@read_query:join_target(),
    cake@internal@read_query:where(),
    binary()
) -> cake@internal@read_query:join().
full(With, On, Alias) ->
    _pipe = With,
    {full_join, _pipe, Alias, On}.

-file("src/cake/join.gleam", 366).
?DOC(
    " Creates a `CROSS JOIN`.\n"
    "\n"
    " Also called _cartesian product_.\n"
).
-spec cross(cake@internal@read_query:join_target(), binary()) -> cake@internal@read_query:join().
cross(With, Alias) ->
    _pipe = With,
    {cross_join, _pipe, Alias}.

-file("src/cake/join.gleam", 389).
?DOC(
    " Creates a `INNER JOIN LATERAL ... ON TRUE`.\n"
    "\n"
    " ⚠️⚠️⚠️\n"
    "\n"
    " CAUTION: `LATERAL` joins are not optimized by the query planner,\n"
    " and can be very slow on large datasets, especially when the sub-query\n"
    " returns many rows.\n"
    "\n"
    " ⚠️⚠️⚠️\n"
    "\n"
    " See <https://www.postgresql.org/docs/9.3/sql-select.html#SQL-FROM> for an\n"
    " explanation on how `LATERAL` works.\n"
    "\n"
    " Any filtering must be done in WHERE clauses as the JOIN ON clause is always\n"
    " TRUE when calling this function.\n"
    "\n"
    " NOTICE: `LATERAL` is supported by 🐘PostgreSQL 9.3+ and recent 🐬MySQL\n"
    " versions.\n"
).
-spec inner_lateral(cake@internal@read_query:join_target(), binary()) -> cake@internal@read_query:join().
inner_lateral(With, Alias) ->
    _pipe = With,
    {inner_join_lateral_on_true, _pipe, Alias}.

-file("src/cake/join.gleam", 412).
?DOC(
    " Creates a `LEFT JOIN LATERAL ... ON TRUE`.\n"
    "\n"
    " ⚠️⚠️⚠️\n"
    "\n"
    " CAUTION: `LATERAL` joins are not optimized by the query planner,\n"
    " and can be very slow on large datasets, especially when the sub-query\n"
    " returns many rows.\n"
    "\n"
    " ⚠️⚠️⚠️\n"
    "\n"
    " See <https://www.postgresql.org/docs/9.3/sql-select.html#SQL-FROM> for an\n"
    " explanation on how `LATERAL` works.\n"
    "\n"
    " Any filtering must be done in WHERE clauses as the JOIN ON clause is always\n"
    " TRUE when calling this function.\n"
    "\n"
    " NOTICE: `LATERAL` is supported by 🐘PostgreSQL 9.3+ and recent 🐬MySQL\n"
    " versions.\n"
).
-spec left_lateral(cake@internal@read_query:join_target(), binary()) -> cake@internal@read_query:join().
left_lateral(With, Alias) ->
    _pipe = With,
    {left_join_lateral_on_true, _pipe, Alias}.

-file("src/cake/join.gleam", 432).
?DOC(
    " Creates a `CROSS JOIN LATERAL`.\n"
    "\n"
    " ⚠️⚠️⚠️\n"
    "\n"
    " CAUTION: `LATERAL` joins are not optimized by the query planner,\n"
    " and can be very slow on large datasets, especially when the sub-query\n"
    " returns many rows.\n"
    "\n"
    " ⚠️⚠️⚠️\n"
    "\n"
    " See <https://www.postgresql.org/docs/9.3/sql-select.html#SQL-FROM> for an\n"
    " explanation on how `LATERAL` works.\n"
    "\n"
    " NOTICE: `LATERAL` is supported by 🐘PostgreSQL 9.3+ and recent 🐬MySQL\n"
    " versions.\n"
).
-spec cross_lateral(cake@internal@read_query:join_target(), binary()) -> cake@internal@read_query:join().
cross_lateral(With, Alias) ->
    _pipe = With,
    {cross_join_lateral, _pipe, Alias}.