### ----------------------------------------------------
### -- Red
### -- Licenses: Artistic-2.0
### -- Authors: Fernando Correa de Oliveira
### -- File: lib/Red/Driver/CommonSQL.rakumod
### ----------------------------------------------------
### -- Chunk 2 of 2
 "nullable-column"
        ),
        (|(
            "column-pk",
            "column-auto-increment",
        ) if .class.^id <= 1),
        |("column-references" unless $*RED-IGNORE-REFERENCE),
        |("column-comment" if self.comment-on-same-statement),
    )
        .map(-> $context {
            self.translate($_, $context).key
        })
        .grep( *.defined )
        .join(" ")
        .subst(/\s ** 2..*/, " ", :g) => []
}

multi method translate(Red::Column $_, "column-name")           { .name // "" => [] }

multi method translate(Red::Column $_, "column-type")           {
    if .attr.type.?red-type-column-type -> $type {
        return self.type-by-name($type) => []
    }
    if .attr.type =:= Mu && ! .type.defined {
        return self.type-by-name("int")  => [] if .auto-increment;
        return self.type-by-name("string") => []
    }
    (.type.defined ?? self.type-by-name(.type) !! self.default-type-for: $_) => []
}

multi method translate(Red::Column $_, "nullable-column")       { (.nullable ?? "NULL" !! "NOT NULL") => [] }

multi method translate(Red::Column $_, "column-default")        {
    my ($str, @bind);
    :(:key($str), :value(@bind)) := self.translate: do given .default.($_) {
        do if $_ !~~ Red::AST {
            .&ast-value
        } else {
            $_
        }
    }
    "DEFAULT $str" => @bind
}

multi method translate(Red::Column $_, "column-pk")             { (.id ?? "primary key" !! "") => [] }

multi method translate(Red::Column $_, "column-auto-increment") { (.auto-increment ?? "auto_increment" !! "") => [] }

multi method translate(Red::Column $_, "column-references") {
    ("references { .class.^table }({ .name })" with .ref) => []
}

multi method translate(Red::Column $_, "table-dot-column") {
    "{ .class.^table }.{ .name }" => []
}

multi method translate(Red::Column $_, "column-comment") {
    (" COMMENT '$_'") => [] if .comment
}

multi method translate(Red::Column $_, "update-lval") {
    .name // "" => []
}

multi method translate(Red::AST::CreateView $_, $context?) {
    my ($select, @sbind) := do given (.?select => []) // self.translate(.ast) { .key, .value };
    "CREATE{ " TEMPORARY" if .temp } VIEW {
        self.table-name-wrapper: .name
    } AS {
        $select
    }" => @sbind,
    |do if not self.comment-on-same-statement {
        self.translate($_).key => [] with .comment
    },
}

multi method translate(Red::AST::CreateTable $_, $context?) {
    "CREATE{ " TEMPORARY" if .temp } TABLE {
        self.table-name-wrapper: .name
    } (\n{
        (
            |.columns.map({ self.translate($_, "create-table").key }),
            |.constraints.map({ self.translate($_, "create-table").key })
        ).join(",\n").indent: 3
    }\n)" => [],
    |do if not self.comment-on-same-statement {
        self.translate($_).key => [] with .comment
    },
    |(.columns.map({ self.translate: $_, "column-comment" }) if not self.comment-on-same-statement)
}

multi method translate(Red::AST::TableComment $_, $context?) {
        (" COMMENT '{ .msg }'" => []) with $_
}

multi method translate(Red::AST::Pk $_, $context?) {
    "PRIMARY KEY ({ .columns.map({ self.translate($_, "pk").key }).join: ", " })" => []
}

multi method translate(Red::AST::Unique $_, $context?) {
    "UNIQUE ({ .columns.map({ self.translate($_, "unique").key }).join: ", " })" => []
}

multi method translate(Red::AST::Insert $_, $context?) {
    my @values = .values.grep({ .value.value.defined });
    return "INSERT INTO { self.table-name-wrapper: .into.^table } DEFAULT VALUES" => [] unless @values;
    # TODO: Use translation
    my @bind = @values.map: *.value.get-value;
    "INSERT INTO {
        self.table-name-wrapper: .into.^table
    }(\n{
        @values>>.key.join(",\n").indent: 3
    }\n)\nVALUES(\n{
        (self.wildcard xx @values).join(",\n").indent: 3
    }\n)" => @bind
}

multi method translate($, "delete-returning") {
    "" => []
}

multi method translate($, "update-returning") {
    "" => []
}

multi method translate(Red::AST::Delete $_, $context?) {
    my ($key, @binds) := do given self.translate(.filter) { .key, .value }
    my ($ret, @rb)    := do given self.translate(Red::AST, "delete-returning") { .key, .value }
    "DELETE FROM {
        .from
    }{
        "\nWHERE { $key }" if $key
    }{
        "\n$ret" if $ret
    }" => [|@binds, |@rb]
}

multi method translate(Red::AST::Update $_, $context?) {
    my @bind;
    my $str = .values.map({
        my ($c, @c) := do given self.translate: .&ast-value, "update" { .key, .value };
        @bind.append: @c;
        $c
    }).join(",\n").indent: 3;

    my $into   = .into;
    my $model  = .model;
    my $filter = .filter;

    with $filter {
        die "Internal error" unless $model ~~ Red::Model;
        if .tables.map({ .HOW.?join-on($_) }).any {
            $filter = Red::AST::In.new:
                $model.^id».column.head,
                Red::AST::Select.new:
                    :of($model.^specialise: $model.^id».column),
                    :$filter
        }
    }

    my ($wstr, @wbind) := do given self.translate: $filter { .key, .value };
    my ($ret, @rb)     := do given self.translate(Red::AST, "update-returning") { .key, .value };

    qq:to/END/ => [|@bind, |@wbind, |@rb];
    UPDATE {
        .into
    } SET
    $str
    {
        "WHERE $wstr" with .filter
    }{
        "\n$ret" if $ret
    }
    END

}

multi method translate(Red::AST::Value $_ where .type ~~ Pair, "update") {
    my ($c, @c) := do given self.translate: .value.key, 'update-lval' { .key, .value }
    my ($s, @b) := do given self.translate: .value.value, 'update-rval' { .key, .value }
    "{ $c } = { $s }" => [|@c, |@b]
}

multi method translate(Red::AST::Value $_ where { !.value.DEFINITE }, "update-rval") {
    NULL => []
}

multi method translate(Red::AST::Value $_, "update-rval") {
    self.wildcard => [ self.wildcard-value: $_ ]
}

multi method translate(Red::AST::LastInsertedRow $_, $context?) { "" => [] }

multi method translate(Red::AST:U $_, $context?) { "" => [] }

multi method default-type-for(Red::Column $_ --> Str:D) { self.default-type-for-type: .attr.type }

multi method default-type-for-type(Json     --> Str:D) {"json"}
multi method default-type-for-type(Rat      --> Str:D) {"real"}
multi method default-type-for-type(Num      --> Str:D) {"real"}
multi method default-type-for-type(Numeric  --> Str:D) {"real"}
multi method default-type-for-type(Instant  --> Str:D) {"real"}
multi method default-type-for-type(DateTime --> Str:D) {"varchar(32)"}
multi method default-type-for-type(Duration --> Str:D) {"interval"}
multi method default-type-for-type(Mu       --> Str:D) {"varchar(255)"}
multi method default-type-for-type(Str      --> Str:D) {"text"}
multi method default-type-for-type(Int      --> Str:D) {"integer"}
multi method default-type-for-type(Bool     --> Str:D) {"boolean"}
multi method default-type-for-type(UUID     --> Str:D) {"varchar(36)"}
#multi method default-type-for(Red::Column                                   --> Str:D) {"varchar(255)"}

multi method type-for-sql("real"     --> "Num"     ) {}
multi method type-for-sql("blob"     --> "Blob"    ) {}
multi method type-for-sql("text"     --> "Str"     ) {}
multi method type-for-sql("varchar"  --> "Str"     ) {}
multi method type-for-sql("interval" --> "Duration") {}
multi method type-for-sql("integer"  --> "Int"     ) {}
multi method type-for-sql("serial"   --> "UInt"    ) {}
multi method type-for-sql("boolean"  --> "Bool"    ) {}
multi method type-for-sql("json"     --> "Json"    ) {}
multi method type-for-sql(Str $ where /^ "varchar(" ~ ")" \d+ $/ --> "Str") {}

multi method inflate(Num $value, Int      :$to!) { $to.new:        $value }
multi method inflate(Num $value, Instant  :$to!) { $to.from-posix: $value }
multi method inflate(Str $value, DateTime :$to!) { $to.new:        $value }
multi method inflate(Str $value, Date     :$to!) { $to.new:        $value }
multi method inflate(Num $value, Duration :$to!) { $to.new:        $value }
multi method inflate(Int $value, Duration :$to!) { $to.new:        $value }
multi method inflate(Str $value, Version  :$to!) { $to.new:        $value }
multi method inflate(Str $value, Duration :$to!) { $to.new: [+] $value.comb(/\d+/).reverse Z[*] (1, 10, 100 ... *) }
multi method inflate(@value, :@to!) {
    do if @to.of =:= Mu {
        @value
    } else {
        Array[@to.of].new: @value
    }
}

multi method deflate(Instant:D  $value) { +$value }
multi method deflate(Date:D     $value) { ~$value }
multi method deflate(DateTime:D $value) { ~$value }
multi method deflate(Duration:D $value) { +$value }
multi method deflate(Version:D  $value) { ~$value }

multi method deflate($value) { $value }

multi method type-by-name("string" --> "varchar(255)") {}
multi method type-by-name(Str $type) { $type }

#multi method is-valid-table-name(Str $ where .fc ~~ self.reserved-words.any.fc) { False }
multi method is-valid-table-name(Str $str) is default {
    so $str ~~ /^ <[\w_]>+ $/
}
method wildcard { "?" }

multi method prepare-json-path-item(@items) {
    @items.map({ self.prepare-json-path-item: $_ }).join;
}
multi method prepare-json-path-item(Red::AST::Value $_) { self.prepare-json-path-item: .value }
multi method prepare-json-path-item(Int $_) { "[{ $_ }]" }
multi method prepare-json-path-item(Str $_) { ".{ $_ }"  }


