### ----------------------------------------------------
### -- SQL::Builder
### -- Licenses: Artistic-2.0
### -- Authors: Adrian Kreher
### -- File: lib/SQL/Builder.rakumod
### ----------------------------------------------------
### -- Chunk 1 of 2
unit class SQL::Builder:ver<0.2.0>;

my multi sub quote-value(Str $s) {
    my $q = $s.subst("'", "''");
    return qq!'$s'!;
}

my multi sub quote-value(Numeric $s) {
    return $s.Str;
}

my sub quote-column(Str $s) {
    my $q = $s.subst('"', '""');
    return qq!"$s"!;
}

class NoSinkError is Exception {
    has $.name = '(an unknown class)';

    method message() {
        "$.name() objects cannot be used in sink context - did you forget to use the result?";
    }
}

class SQLFragment {
    has $.sql;
    has @.bind;

    method sink() {
        NoSinkError.new(:name<SQLFragment>).throw;
    }
}

my role SQLSyntax {
    method build-fragment(--> SQLFragment) {...}
}

my role SQLStatement {
    method build(--> SQLFragment) {...}
}

my sub fragment(SQLSyntax:U $default-class, $value) {
    $value.does(SQLSyntax) ?? $value !! $default-class.new($value);
}

# SQLSyntax representing a column or table name, quoted with the column rules (also works on tables)
class Identifier does SQLSyntax is export {
    has Str() $.relation;
    has Str() $.column;
    has Str() $.cast;

    constant relation-pattern = rx/^
        [$<relation>=(<-[.]>+) '.']? # optional table/relation name
        $<column>=(<-[:]>+) # required column name
        ['::' $<cast>=(\S+)]? # optional postgres-style cast
    $/;

    method new(Str $string) {
        $string ~~ relation-pattern;
        self.bless(:$<relation>, :$<column>, :$<cast>);
    }

    method build-fragment {
        my $s = "";
        $s ~= "{quote-column($_)}." with $!relation;
        $s ~= quote-column($!column);
        $s ~= "::$_" with $!cast;
        SQLFragment.new(:sql($s));
    }
}

# SQLSyntax representing a literal value. Numbers and booleans are formatted as expected. Strings are escaped by string rules (double quotes).
class Value does SQLSyntax is export {
    has $.value;

    method new($value) {
        self.bless(:$value);
    }

    method build-fragment {
        SQLFragment.new(:sql(quote-value($!value)));
    }
}

# SQLSyntax representing a single placeholder. Always emits a '?' token
class Placeholder does SQLSyntax is export {
    has $.bind;

    method new($bind) {
        self.bless(:$bind);
    }

    method build-fragment {
        SQLFragment.new(:sql('?'), :$.bind);
    }
}

my sub append-fragments(Str $sql is rw, @bind, SQLSyntax:U $default-class, @items, Str :$join = '') {
    for @items.kv -> $i, $value {
        my $item = $value.does(SQLSyntax) ?? $value.build-fragment !! $default-class.new($value).build-fragment;
        $sql ~= $join unless $i == 0;
        $sql ~= $item.sql;
        append @bind, $item.bind;
    }
}

# SQLSyntax representing a raw value, unescaped in any way
class Raw does SQLSyntax is export {
    has $.fmt;
    has @.parts;

    has $.value;
    has @.bind;

    method new($value, :@bind) {
        self.bless(:$value, :@bind);
    }

    method build-fragment {
        if $!fmt {
            my @bind;
            my $i = 0;
            my $value = $!fmt.subst(:g, '{}', sub {
                my $sql = @!parts[$i++].build-fragment;
                append @bind, $sql.bind;
                $sql.sql;
            });

            SQLFragment.new(:sql($value), :@bind);
        } else {
            SQLFragment.new(:sql($!value), :@!bind);
        }
    }

    method fmt($fmt, *@parts) {
        if @parts.grep({$_ !~~ SQLSyntax}) {
            die "Raw.fmt must receive only SQLSyntax as parameters";
        }

        my $pattern-count = $fmt.comb('{}').elems;
        if $pattern-count != @parts.elems {
            die "Raw.fmt: got $pattern-count replacement elements (\{\}), expected {@parts.elems}";
        }

        if $fmt ~~ /('{' <[^}]>+ '}')/ {
            die "Raw.fmt: found unsupported replacement element '{$/.Str}' (this syntax is reserved)";
        }

        self.bless(:$fmt, :@parts);
    }
}

# SQLSyntax representing a function call. First argument is unescaped, all subsequent arguments are treated as columns (if not otherwise specified)
class Fn does SQLSyntax is export {
    has $.fn;
    has @.params;

    method new(Str $fn, *@params) {
        self.bless(:$fn, :@params);
    }

    method build-fragment {
        # XXX: should we render this at construction time? or later?
        my @bind;
        my $sql = $.fn ~ '(';
        append-fragments($sql, @bind, Identifier, @.params, :join(', '));
        $sql ~= ')';

        SQLFragment.new(:$sql, :@bind);
    }
}

# aliased-column-list handles the Capture syntax used in the SELECT and RETURNING clauses to allow
# creation of column aliases.
sub aliased-column-list(@columns) {
    return @columns.map({
        when Pair {
            die 'Unexpected Pair as argument in column list';
        }
        when Capture {
            die 'unknown argument in column list, expected: \\($column-name, :as<alias>)' unless $_ ~~ :($, :$as!);
            Raw.fmt('{} AS {}', fragment(Identifier, .[0]), fragment(Identifier, .<as>));
        }
        default {
            $_;
        }
    });
}

# order-by-column-list handles the Capture syntax used in the ORDER BY clause to implement options
# for ordering (e.g. DESC, ASC)
sub order-by-column-list(@columns) {
    return @columns.map({
        when Capture {
            die 'unknown argument in order-by list, expected: \\($column-name, :desc|:asc)' unless $_ ~~ :($, :$desc) || $_ ~~ :($, :$asc);
            Raw.fmt('{} {}', fragment(Identifier, .[0]), fragment(Raw, .Hash.keys[0].uc));
        }
        default {
            $_;
        }
    });
}

class ConditionClause does SQLSyntax {
    has Str $.mode = 'none';
    has @.clauses;

    method new(+@c, *%k) {
        if @c.elems == 3 && @c[0] !~~ Positional|Pair|Capture {
            # If this is a simple ["foo", "=", "bar"] clause, it's already been flattened enough
            self.bless(:mode<and>, :clauses[@c, |%k.pairs.sort]);
        } else {
            self.bless(:mode<and>, :clauses[|@c, |%k.pairs.sort]);
        }
    }

    method new-or(+@c, *%k) {
        if @c.elems == 3 && @c[0] !~~ Positional|Pair|Capture {
            # If this is a simple ["foo", "=", "bar"] clause, it's already been flattened enough
            self.bless(:mode<or>, :clauses[@c, |%k.pairs.sort]);
        } else {
            self.bless(:mode<or>, :clauses[|@c, |%k.pairs.sort]);
        }
    }

    submethod BUILD(:$mode = 'none', :$clauses) {
        $!mode = $mode;
        @!clauses = $clauses.list;

        if $mode eq 'none' && @!clauses > 1 {
            die "where clause has multiple clauses, but no mode (e.g. 'and' or 'or')";
        }
    }

    method !make-pair(Pair $clause, @parts, @bind) {
        my $key = fragment(Identifier, $clause.key).build-fragment;

        if $clause.value === Nil {
            push @parts, "{$key.sql} IS NULL";
            append @bind, $key.bind;
        } else {
            my $value = fragment(Placeholder, $clause.value).build-fragment;
            push @parts, "{$key.sql} = {$value.sql}";
            append @bind, $key.bind;
            append @bind, $value.bind;
        }
    }

    method build-fragment {
        my @parts;
        my @bind;

        for @!clauses -> $clause {
            given $clause {
                when Pair {
                    self!make-pair($clause, @parts, @bind);
                }
                when $_ ~~ Positional && .elems == 1 && .[0] ~~ Pair {
                    self!make-pair($clause[0], @parts, @bind);
                }
                when Capture {
                    die "unexpected arguments (expected only one of :and/:or): {$clause.raku}" if $clause.elems;
                    my $pairs = $clause.pairs;
                    die "unexpected arguments (expected only one of :and/:or): {$clause.raku}" if $pairs.elems != 1;
                    my $pair = $pairs.head;
                    my $key = $pair.key;

                    if $key eq 'or' {
                        my $inner = ConditionClause.new-or($pair.value).build-fragment;
                        push @parts, '(' ~ $inner.sql ~ ')';
                        append @bind, $inner.bind;
                    } elsif $key eq 'and' {
                        my $inner = ConditionClause.new($pair.value).build-fragment;
                        push @parts, '(' ~ $inner.sql ~ ')';
                        append @bind, $inner.bind;
                    } else {
                        die "unknown key (expected 'and' or 'or'): $key";
                    }
                }
                when Positional {
                    die "wrong number of args (needed 3): {$clause.raku}" unless $clause.elems == 3;

                    my $col = fragment(Identifier, $clause[0]).build-fragment;
                    my $op = fragment(Raw, $clause[1]).build-fragment;
                    my $value = fragment(Placeholder, $clause[2]).build-fragment;

                    push @parts, "{$col.sql} {$op.sql} {$value.sql}";
                    append @bind, $col.bind;
                    append @bind, $op.bind;
                    append @bind, $value.bind;
                }
                when Hash {
                    die "Hash not implemented - did you mean a List of Pairs?";
                }
                default {
                    die "Don't know how to handle parameter of type {$clause.^name}: {$clause.raku}";
                }
            }
        }

        return SQLFragment.new(:sql(@parts.join(" {$.mode.uc} ")), :@bind);
    }
}

my class Join does SQLSyntax {
    has $.mode;
    has $.table;
    has $.alias;
    has $.join-type;
    has ConditionClause $.on;
    has SQLSyntax $.using;

    submethod BUILD(:$mode, :$table, :$on, :$alias, :$using) {
        $!table = $table;
        $!mode = $mode;
        $!alias = $alias;

        if $using {
            $!using = fragment(Identifier, $using);
        } elsif $on ~~ Capture {
            $!on = ConditionClause.new($on);
        } else {
            $!on = ConditionClause.new($on.List);
        }

    }

    method build-fragment {
        my @bind;
        my $sql = $!mode ?? "$!mode JOIN " !! 'JOIN ';

        given fragment(Identifier, $!table).build-fragment {
            $sql ~= .sql;
            append @bind, .bind;
        }

        if $!alias {
            $sql ~= " AS {quote-column($!alias)}";
        }

        if $!on {
            given $!on.build-fragment {
                $sql ~= ' ON ' ~ .sql;
                append @bind, .bind;
            }
        }

        if $!using {
            given $!using.build-fragment {
                $sql ~= " USING ({.sql})";
                append @bind, .bind;
            }
        }

        return SQLFragment.new(:$sql, :@bind);
    }
}

class SelectBuilder does SQLSyntax does SQLStatement {
    has @.select-columns;
    has SQLSyntax $.from;
    has Join @.join-items;
    has ConditionClause $.where;
    has @.order-by-columns;
    has @.group-by-columns;
    has ConditionClause $.having;
    has SQLSyntax $.limit-count;
    has SQLSyntax $.offset-count;

    proto method new(|) {*}

    # Multiple FROM sources NYI
    multi method new(SQLStatement $inner, :$as!) {
        my $from = Raw.fmt('{} AS {}', $inner, fragment(Identifier, $as));
        return self.bless(:$from);
    }

    multi method new(*%pairs where *.elems == 1) {
        die 'Unsupported use of Pair for query aliasing';
    }

    multi method new(Str $table) {
        return self.bless(:from(Identifier.new($table)));
    }

    multi method new(Identifier $table) {
        return self.bless(:from($table));
    }

    method clone {
        nextwith :select-columns(@!select-columns.clone),
            :from($!from.clone),
            :join-items(@!join-items.clone),
            :where($!where.clone),
            :order-by-columns(@!order-by-columns.clone),
            :group-by-columns(@!group-by-columns.clone),
            :having($!having.clone),
            |(:limit-count(.clone) with $!limit-count),
            |(:offset-count(.clone) with $!offset-count),
            |%_;
    }

    method where(|c) {
        $!where = ConditionClause.new(|c);
        self;
    }

    method where2(|c) {
        $!where = ConditionClause.new(|c);
        self;
    }

    method having(|c) {
        $!having = ConditionClause.new(|c);
        self;
    }

    # XXX: replace with either `select-all` or `select(Whatever)`?
    multi method select(:$all!) {
        @!select-columns = Raw.new('*');
        self;
    }

    # No named parameters permitted
    multi method select(*@c, *% where .elems == 0) {
        @!select-columns = |@c;
        self;
    }

    # multi method select(*@columns, *%pairs) {
    #     @!select-columns = |@columns, |%pairs.sort;
    #     self;
    # }

    method order-by(*@columns) {
        @!order-by-columns = @columns;
        self;
    }

    method limit(Int $limit) {
        $!limit-count = Placeholder.new($limit);
        self;
    }

    method offset(Int $offset) {
        $!offset-count = Placeholder.new($offset);
        self;
    }

    method group-by(*@columns) {
        @!group-by-columns = @columns;
        self;
    }

    # Don't use multi method here to avoid inheriting Cool.join
    # TODO: add proto method to let us use multi methods without calling Cool.join
    method join($table, :$on, :$using, Str :$as, Bool :$inner, Bool :$left, Bool :$right, Bool :$full) {
        my @modes = (:$inner, :$left, :$right, :$full).grep({.value});
        if @modes > 1 {
            die "Multiple join types passed: must use only one of :inner :left :right :full (you used @modes[])";
        }

        unless one($on, $using) {
            die "Must specify exactly one of :on or :using for join criteria";
        }

        my $mode = @modes[0].key.uc if @modes;

        push @!join-items, Join.new(:$mode, :$table, :$on, :$using, :alias($as));
        self;
    }

    method build-fragment() {
        my $indent = $*pretty ?? "\n" ~ "\t" x $*depth !! '';
        my $indent2 = $*pretty ?? $indent ~ "\t" !! '';

        $*depth++ if $*pretty;
        my $fragment = self.build;
        SQLFragment.new(:sql('(' ~ $indent2 ~ $fragment.sql ~ $indent ~ ')'), :bind($fragment.bind));
    }

    method build(Bool :$pretty) {
        die "no columns to select" unless @!select-columns;

        our $*pretty = CALLERS::<$*pretty> // $pretty;
        our $*depth = CALLERS::<$*depth> // 0;
        my $indent = $*pretty ?? "\n" ~ "\t" x $*depth !! ' ';

        my @bind;
        my $sql = 'SELECT ';

        append-fragments($sql, @bind, Identifier, aliased-column-list(@!select-columns), :join(', '));

        given $!from.build-fragment {
            $sql ~= $indent ~ 'FROM ' ~ .sql;
            append @bind, .bind;
        }

        if @!join-items {
            $sql ~= $indent;
            append-fragments($sql, @bind, Identifier, @!join-items, :join($indent));
        }

        if $!where {
            given $!where.build-fragment {
                $sql ~= $indent ~ 'WHERE ' ~ .sql;
                append @bind, .bind;
            }
        }

        if @!group-by-columns {
            $sql ~= $indent ~ 'GROUP BY ';
            append-fragments($sql, @bind, Identifier, @!group-by-columns, :join(', '));
        }

        if $!having {
            given $!having.build-fragment {
                $sql ~= $indent ~ 'HAVING ' ~ .sql;
                append @bind, .bind;
            }
        }

        if @!order-by-columns {
            $sql ~= $indent ~ 'ORDER BY ';
            append-fragments($sql, @bind, Identifier, order-by-column-list(@!order-by-columns), :join(', '));
        }

        if $!limit-count {
            given $!limit-count.build-fragment {
                $sql ~= $indent ~ 'LIMIT ' ~ .sql;
                append @bind, .bind;
            }
        }

        if $!offset-count {
            given $!offset-count.build-fragment {
                $sql ~= $indent ~ 'OFFSET ' ~ .sql;
                append @bind, .bind;
            }
        }

        return SQLFragment.new(:$sql, :@bind);
    }
}

# TODO: implement table/column helpers
# class Condition {
#     has $.left;
#     has $.op;
#     has $.right;
# }
# class Column is Identifier {
#     method EQ($other) {
#         if $other.defined {
#             return Condition.new(:left(self), :op('='), :right(fragment(Placeholder, $other)));
#         } else {
#             return Condition.new(:left(self), :op('IS'), :right(Raw.new('NULL')));
#         }
#     }
# 
#     method NE($other) {
#         if $other.defined {
#             return Condition.new(:left(self), :op('!='), :right(fragment(Placeholder, $other)));
#         } else {
#             return Condition.new(:left(self), :op('IS NOT'), :right(Raw.new('NULL')));
#         }
#     }
# 
#     method GT($other) {
#         Condition.new(:left(self), :op('>'), :right(fragment(Placeholder, $other)));
#     }
# }
# 
# class ColumnSpec is export {
#     has %!columns is built;
# 
#     method new($table-name, :@columns) {
#         my %columns = @columns.map({$_ => Column.new($table-name ~ '.' ~ $_)});
#         self.bless(:%columns);
#     }
# 
#     method FALLBACK($name) {
#         if %!columns{$name}:exists {
#             return %!columns{$name};
#         }
# 
#         die "no such column: '$name'";
#
#     }
# }

method from(|c) {
    SelectBuilder.new(|c);
}

class UpdateBuilder does SQLSyntax {
    has SQLSyntax $.table;
    has @!values;
    has @!columns;
    has $!query;
    has @!returning;
    has ConditionClause $.where;

    proto method new(|) {*}
    multi method new(Str $table) {
        return self.bless(:table(Identifier.new($table)));
    }

    multi method new(Identifier $table) {
        return self.bless(:$table);
    }

    method set(*@values, *%values) {
        @!values = @values;
        append @!values, %values.sort;
        self;
    }

    method returning(*@columns, *%pairs) {
        @!returning = |@columns, |%pairs.pairs.sort;
        self;
    }

    method where(|c) {
        $!where = ConditionClause.new(|c);
        self;
    }

    method build-fragment {
        my $fragment = self.build;
        SQLFragment.new(:sql('(' ~ $fragment.sql ~ ')'), :bind($fragment.bind));
    }

    method build(Bool :$pretty) {
        die "refusing to UPDATE without a WHERE clause" unless $!where;

        our $*pretty = CALLERS::<$*pretty> // $pretty;
        our $*depth = CALLERS::<$*depth> // 0;
        my $indent = $*pretty ?? "\n" ~ "\t" x $*depth !! ' ';

        my @bind;
        my $sql = 'UPDATE ';

        given $!table.build-fragment {
            $sql ~= .sql;
            append @bind, .bind;
        }

        $sql ~= $indent ~ 'SET ';

        for @!values.kv -> $i, $pair {
            my $col = fragment(Identifier, $pair.key).build-fragment;
            my $val = fragment(Placeholder, $pair.value).build-fragment;

            $sql ~= ', ' if $i > 0;
            $sql ~= "{$col.sql} = {$val.sql}";
            append @bind, $col.bind;
            append @bind, $val.bind;
        }

        given $!where.?build-fragment {
            $sql ~= $indent ~ 'WHERE ' ~ .sql;
            append @bind, .bind;
        }

        if @!returning {
            $sql ~= $indent ~ 'RETURNING ';
            append-fragments($sql, @bind, Identifier, aliased-column-list(@!returning), :join(', '));
        }

        return SQLFragment.new(:$sql, :@bind);
    }
}

method update(|c) {
    UpdateBuilder.new(|c);
}

class InsertBuilder does SQLSyntax does SQLStatement {
    has SQLSyntax $.table;
    has @!values;
    has @!columns;
    has $!query;
    has @!returning;
    # has ... $.on-conflict;

    proto method new(|) {*}
    multi method new(Str $table) {
        return self.bless(:table(Identifier.new($table)));
    }

    multi method new(Identifier $table) {
        return self.bless(:$table);
    }

    method returning(*@columns, *%pairs) {
        @!returning = |@columns, |%pairs.pairs.sort;
        self;
    }

    method data(*@values, *%values) {
        my @data = |@values, |%values.sort;
        die "all arguments passed to 'data' must be Pairs" if @data.first({$_ !~~ Pair}, :k);
        @!columns = @data.map(*.key);
        @!values = [@data.map(*.value),];
        $!query = Any;
        self;
    }

    method columns(*@columns) {
        @!columns = @columns;
        self;
    }

    method values(*@values) {
        $!query = Any;
        @!values = [@values,];
        self;
    }

    method multi-values(@multi-values) {
        die "argument to 'multi-values' must be list of lists" unless @multi-values.all ~~ Positional;
        $!query = Any;
        @!values = @multi-values;
        self;
    }

    method query(SQLStatement $query) {
        # XXX: should we validate that this query is either a SELECT statement,
        # or has a RETURNING clause somehow?
        $!query = $query;
        @!values = ();
        self;
    }

    method build-fragment {
        my $fragment = self.build;
        SQLFragment.new(:sql('(' ~ $fragment.sql ~ ')'), :bind($fragment.bind));
    }

    method build(Bool :$pretty) {
        our $*pretty = CALLERS::<$*pretty> // $pretty;
        our $*depth = CALLERS::<$*depth> // 0;
        my $indent = $*pretty ?? "\n" ~ "\t" x $*depth !! ' ';

        my @bind;
        my $sql = 'INSERT INTO ';

        given $!table.build-fragment {
            $sql ~= .sql;
            append @bind, .bind;
        }

        if @!columns && @!values {
            $sql ~= ' (';
            append-fragments($sql, @bind, Identifier, @!columns, :join(', '));
            $sql ~= ')' ~ $indent ~  'VALUES ';

            # Values can be a list-of-lists so we can insert multiple rows in a single statement
            for @!values.kv -> $i, $v {
                $sql ~= ', ' if $i != 0;
                $sql ~= '(';
                append-fragments($sql, @bind, Placeholder, $v, :join(', '));
                $sql ~= ')';
            }
        } elsif @!columns && $!query {
            # since we're not using `build-fragment` here, manually increase the indent level
            $*depth++;
            my $indent = $*pretty ?? "\n" ~ "\t" x $*depth !! ' ';

            $sql ~= ' (';
            append-fragments($sql, @bind, Identifier, @!columns, :join(', '));
            $sql ~= ')' ~ $indent;
            given $!query.build {
                $sql ~= .sql;
                append @bind, .bind;
            }
            $*depth--;
        } else {
            die "cannot INSERT without values (or a query)";
        }

        if @!returning {
            $sql ~= $indent ~ 'RETURNING ';
            append-fragments($sql, @bind, Identifier, aliased-column-list(@!returning), :join(', '));
        }

        return SQLFragment.new(:$sql, :@bind);
    }
}

method insert-into(|c) {
    InsertBuilder.new(|c);
}

class DeleteBuilder does SQLSyntax does SQLStatement {
    has SQLSyntax $.table;
    has @.returning;
    has ConditionClause $.where;

    proto method new(|) {*}
    multi method new(Str $table) {
        return self.bless(:table(Identifier.new($table)));
    }

    multi method new(Identifier $table) {
        return self.bless(:$table);
    }

    method returning(*@columns, *%pairs) {
        @!returning = |@columns, |%pairs.pairs.sort;
        self;
    }

    method where(|c) {
        $!where = ConditionClause.new(|c);
        self;
    }

    method build-fragment {
        my $fragment = self.build;
        SQLFragment.new(:sql('(' ~ $fragment.sql ~ ')'), :bind($fragment.bind));
    }

    method build(Bool :$pretty) {
        die "refusing to DELETE without a WHERE clause" unless $!where;

        our $*pretty = CALLERS::<$*pretty> // $pretty;
        our $*depth = CALLERS::<$*depth> // 0;
        my $indent = $*pretty ?? "\n" ~ "\t" x $*depth !! ' ';

        my @bind;
        my $sql = 'DELETE FROM ';

        given $!table.build-fragment {
            $sql ~= .sql;
            append @bind, .bind;
        }

        given $!where.build-fragment {
            $sql ~= $indent ~ 'WHERE ' ~ .sql;
            append @bind, .bind;
        }

        if @!returning {
            $sql ~= $indent ~ 'RETURNING ';
            append-fragments($sql, @bind, Identifier, aliased-column-list(@!returning), :join(', '));
        }

        return SQLFragment.new(:$sql, :@bind);
    }
}


method delete-from(|c) {
    DeleteBuilder.new(|c);
}

method fn {
    return Fn;
}

method raw {
    return Raw;
}

method placeholder {
    return Placeholder;
}

=begin pod

=head1 NAME

SQL::Builder - build SQL statements

=head1 SYNOPSIS

=begin code :lang<raku>
use SQL::Builder;

my $sql = SQL::Builder.new;

# all SELECT queries start with from
my $q1 = $sql.from('users').select(<name address>).where(['email', '=', 'foo@example.com']);
my $statement = $q1.build;
# $statement.sql: SELECT name, address FROM users WHERE email = ?
# $db.execute($statement.sql, |$statement.bind);

# many SQL fragments are supported:
my $q2 = $sql.from('songs').
    select(['album', :albumlength(Fn.new('SUM', 'length'))]).
    where([:online(True), ['year', '>=', 2020]]).
    group-by('album').
    order-by('album');

# subselects too:
my $q3 = $sql.from($q2, :as<inner>).select(Fn.new('MAX', 'inner.albumlength'));

# joins:
my $q4 = $sql.from('songs').
    join(:left, 'ratings', :using('song-id')).
    select(<title artist album rating>);
=end code

=head1 DESCRIPTION

SQL::Builder generates SQL statements using an approach modeled after the 'builder' pattern. This
allows us to support a wide variety of SQL syntax, including sub-queries, while allowing each option
to remain intuitive.

This module is partially inspired by Python's SQLAlchemy Core as well as Perl 5's C<SQL::Abstract>.
This module forgoes an ORM in favor of generating raw SQL, giving you the power to build complex
queries and not lose execution speed.

This module also prioritizes safety. Each option has rules on how it handles an untyped string, and
it defaults to either a quoted C<Identifier> or a C<Placeholder>, depending on the ergonomics of
each function.

SQL::Builder can represent a large amount of SQL statements, and is best suited for statements of
medium complexity with reasonably high amounts of runtime changes (e.g. where clause, optional joins
or sub-selects).

=head1 TYPE SYSTEM

SQL::Builder has a basic type system to ensure the right behavior when a value is provided. Each
builder option has a default way to handle a string, typically either as either Identifier or
Placeholder and each method documents its expectations.

These are all subclasses of C<SQLSyntax> and are:

=item C<Identifier>: used for table and column names, and will be split into pieces and quoted. See C<Identifier> below for full details.

=item C<Placeholder>: always generates a single C<?> placeholder and puts its value into the list of bind values.

=item C<Value>: used for a string value when C<Placeholder> is not an option. Always fully quoted.

=item C<Raw>: this is a raw fragment of SQL. Use C<Raw.fmt> to safely write raw fragments.

=item C<Fn>: used to help write function calls. The first argument is unquoted. Subsequent arguments default to C<Identifier>, and are put in a parenthesized list.

=head2 Identifier

The C<Identifier> type is the most common way to handle untyped input. It is used to represent
tables and columns, with an optional type cast. If a dotted value is specified (e.g.
C<table.column>), then this is split into two bits before quoting. Additionally, if it ends with
C<::>, then that suffix is allowed as a PostgreSQL-style type cast.

Example input and output for the SQL portion:

=item C<Identifier.new("foo")> -> C<"foo">
=item C<Identifier.new("foo.bar")> -> C<"foo"."bar">
=item C<Identifier.new("foo::int")> -> C<"foo"::int>
=item C<Identifier.new("foo.bar::uuid[]")> -> C<"foo"."bar"::uuid[]>

=head2 Placeholder

This is a simple SQL placeholder. It is used as in the right-hand side of various conditionals. The resulting fragment is always a single C<?>, and the value is put in the bind list.

No conversion is done to the value, so you can pass in values of type C<Str>, C<Int>, C<DateTime>, C<Bool>, as well as C<Array>. Currently, Arrays do not have any special logic to create additional placeholders or otherwise flatten the array. See the Cookbook section to see how to handle this.

=head2 Value

The value is escaped and wrapped in single quotes.

=head2 Raw

No escaping is performed. The C<Raw.fmt> method provides a safer way to use this.

=head2 Raw.fmt(Str $template, SQLSyntax $a, $b, ...)

The C<fmt> method lets you safely build bits of SQL by providing a template containing C<{}>
sequences that are replaced by the following arguments. You must have the same number of C<{}>
replacements as arguments, and all arguments must be one of the types in this section (e.g.
subclasses of C<SQLSyntax>).

Examples:

=begin code :lang<raku>
Raw.fmt('COUNT({}) AS {}', Identifier.new("artist"), Identifier.new("artistcount"));
# sql: COUNT("artist") AS "artistcount"
# bind: []

Raw.fmt('unnest({}::uuid[]) WITH ORDINALITY t(id, ord)', Placeholder.new([1, 2, 3]));
# sql: unnest(?::uuid[]) WITH ORDINALITY t(id, ord)
# bind: [@ids,]

Raw.fmt('date_trunc({}, {})', Value.new('day'), Identifier.new('song-start'));
# sql: date_trunc('day', "song-start")
# bind: []
=end code

=head2 Fn

Fn (function) is a helper to make function calls. The first item is taken as a Raw value, and all
following items default to Identifiers. This can be used for any function-like syntax in SQL, not
just real functions.

Examples:

=begin code :lang<raku>
Fn.new('COUNT', 'artists');
# sql: COUNT("artists")
# bind: []

Fn.new('ANY', my @stuff);
# sql: ANY(?)
# bind: [@my-stuff,]
=end code

=head1 SELECT QUERIES

Select queries are created with the C<from> method on the C<SQL::Builder> object. All other options
can be passed in any order. All options except C<join> overwrite the current value. Each option
returns the SelectBuilder instance, allowing for a chain style:
C<$sql.from('foo').select('bar').group-by('baz')>

Multiple tables in the C<from> clause are not yet supported. Use C<join> instead.

Select queries support the following options:

=head2 from(Str $table)

Creates a C<SelectBuilder> from the given table.

=head2 from(SQLStatement, :$as!)

Creates a subselect from the provided SQLStatement, aliased to the value of C<$as>. Contrived example:

=begin code :lang<raku>
my $inner-q = $sql.from('foo').select('bar');
my $q = $sql.from($inner-q, :as<inner>).select('bar');
# SELECT "bar" FROM (SELECT "bar" FROM "foo") AS "inner"
=end code

=head2 select(:all)

Emits a C<SELECT *>.

=head2 select(*@columns)

Specifies the list of values to return. Each column defaults to C<Identifier>.

A column may be aliased with the use of a Capture in the form C<\("column-name", :as<alias>)>.

=begin code :lang<raku>
# Preferred form: using a capture explicitly:
$sql.from('table').select(\("bar", :as<foo>));
# sql: SELECT "bar" AS "foo" FROM "table"
=end code

Note that due to Raku's handling of Pairs, if you mix Positional and non-Positional arguments, the
Pairs will always be at the end. You can avoid this by passing an Array, or parenthesizing the
Pairs:

=begin code :lang<raku>
$sql.from('table').select(<foo bar>, :a<b>, :c<d>);
# SELECT "foo", "bar", "b" AS "a", "d" AS "c" FROM table

$sql.from('table').select(:a<b