package LATMOS::Accounts::Bases::Sql::objects;

use 5.010000;
use strict;
use warnings;

use base qw(LATMOS::Accounts::Bases::Objects);
use LATMOS::Accounts::Log;
use Crypt::RSA;
use Crypt::RSA::Key::Public::SSH;

our $VERSION = (q$Rev$ =~ /^Rev: (\d+) /)[0];

=head1 NAME

LATMOS::Accounts::Bases::Sql::objects - Parent class for SQL object

=cut

sub _attributes_table { $_[0]->_object_table . '_attributes_list' }

sub list {
    my ($class, $base) = @_;

    my $sth = $base->db->prepare_cached(
        sprintf(
            q{select %s as k from %s %s order by %s},
            $base->db->quote_identifier($class->_key_field),
            $base->db->quote_identifier($class->_object_table),
            ($base->{wexported} ? '' : 'where exported = true'),
            $base->db->quote_identifier($class->_key_field),
        )
    );
    $sth->execute;
    my @keys;
    while(my $res = $sth->fetchrow_hashref) {
        push(@keys, $res->{k});
    }
    @keys
}

sub list_from_rev {
    my ($class, $base, $rev) = @_;
    my $sth = $base->db->prepare_cached(
        sprintf(
            q{select %s as k from %s where rev > ? %s order by %s},
            $base->db->quote_identifier($class->_key_field),
            $base->db->quote_identifier($class->_object_table),
            ($base->{wexported} ? '' : 'and exported = true'),
            $base->db->quote_identifier($class->_key_field),
        )
    );
    $sth->execute($rev);
    my @keys;
    while(my $res = $sth->fetchrow_hashref) {
        push(@keys, $res->{k});
    }
    @keys
}

sub _has_extended_attributes { 0 }

sub _get_attr_schema {
    my ($class, $base, $info) = @_;
    $info ||= {};
    if (!$base->{__cache}{$class->_object_table}{inline}) {
        $base->{__cache}{$class->_object_table}{inline} = [];
        my $sth = $base->db->prepare(
            q{SELECT column_name FROM information_schema.columns
              WHERE table_name = ?}
        );
        $sth->execute($class->_object_table);
        while (my $res = $sth->fetchrow_hashref) {
            push(@{$base->{__cache}{$class->_object_table}{inline}},
                $res->{column_name});
        }
    }
    foreach (@{$base->{__cache}{$class->_object_table}{inline}}) {
        $info->{$_}{inline} = 1;
        if (m/^(rev|date|create|ikey)$/) {
            $info->{$_}{ro} = 1
        }
    }
    if ($class->_has_extended_attributes) {
        if (!$base->{__cache}{$class->_object_table}{extend}) {
            $base->{__cache}{$class->_object_table}{extend} = [];
            my $sth = $base->db->prepare_cached(
                sprintf(
                    q{select canonical from %s order by canonical},
                    $base->db->quote_identifier($class->_attributes_table),
                )
            );
            $sth->execute;
            while (my $res = $sth->fetchrow_hashref) {
                push(@{$base->{__cache}{$class->_object_table}{extend}},
                        $res->{canonical});
            }
        }
        foreach (@{$base->{__cache}{$class->_object_table}{extend}}) {
            $info->{$_} ||= {};
        }
    }
    $info->{exported}   = { inline => 1, formtype => 'CHECKBOX', hide => 1, notify => 1 };
    $info->{unexported} = { inline => 1, formtype => 'CHECKBOX', };

    $info
}

# Everything managed by the perl code

sub _managed_fields {
    my ($class, $for, $base) = @_;
    return();
}

sub new {
    my ($class, $base, $id) = @_;
    my $sth = $base->db->prepare_cached(
        sprintf(q{select 1 from %s where %s = ? %s},
            $base->db->quote_identifier($class->_object_table),
            $base->db->quote_identifier($class->_key_field),
            ($base->{wexported} ? '' : 'and exported = true'),
        ),
    );
    my $count = $sth->execute($id);
    $sth->finish;
    ($count || 0) == 1 or return;
    $class->SUPER::new($base, $id);
}

sub _get_ikey {
    my ($class, $base, $id) = @_;
    $base ||= $class->base;
    $id ||= $class->id;
    my $sth = $base->db->prepare_cached(
        sprintf(
            q{select ikey from %s where %s = ?},
            $base->db->quote_identifier($class->_object_table),
            $base->db->quote_identifier($class->_key_field),
        )
    );
    $sth->execute($id);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{ikey}
}

sub _create {
    my ($class, $base, $id, %data) = @_;

    # splitting inline from extended
    my (%first, %second);
    # Ensure object is exported if not specified
    $data{exported} = 1 if (!exists($data{exported}));
    if (exists $data{unexported}) {
        $data{exported} = $data{unexported} ? 0 : 1;
        delete $data{unexported}
    }
    foreach (keys %data) {
        my $attr = $base->attribute($class->type, $_) or next;
        $_ =~ /^exported$/ and $data{$_} = $data{$_} ? 1 : 0;
        if ($attr->{inline}) {
            $first{$_} = $data{$_};
        } else {
            $second{$_} = $data{$_};
        }
    }
    $first{$class->_key_field} = $id;

    my $sth = $base->db->prepare(
        sprintf(
            q{insert into %s (%s) values (%s)},
            $base->db->quote_identifier($class->_object_table),
            join(', ', map { $base->db->quote_identifier($_) } sort keys %first),
            join(',', qw(?) x scalar(keys %first)),
        )
    );
    $sth->execute(map { $first{$_} || undef } sort keys %first) or return;

    my $obj = $class->new($base, $id);
    $obj->objectlog('Create', 'Object created');
    foreach (keys %first) {
        my $attr = $obj->attribute($_);
        $obj->objectlog('Attribute', '%s set to %s', $_, (
                defined($data{$_}) 
                ? (ref $data{$_}
                    ? join(', ', @{ $data{$_} })
                    : $data{$_})
                : '(none)'))
            if ($attr->{notify});
    }
    $obj->set_fields(%second);

    1;
}

sub _delete {
    my ($class, $base, $id) = @_;

    my $obj = $base->get_object($class->type, $id)
        or return;
    $obj->objectlog('Delete', 'Object deleted');

    my $sthd = $base->db->prepare_cached(
        sprintf(
            q{delete from %s where %s = ?},
            $base->db->quote_identifier($class->_object_table),
            $base->db->quote_identifier($class->_key_field),
        )
    );
    $sthd->execute($id);
}

sub _rename {
    my ($class, $base, $id, $newid) = @_;

    {
        my $obj = $base->get_object($class->type, $id)
            or return;
        $obj->objectlog('Rename', 'Object rename to %s', $newid);
    }

    my $sthr = $base->db->prepare_cached(
        sprintf(
            q{update %s set %s = ? where %s = ?},
            $base->db->quote_identifier($class->_object_table),
            $base->db->quote_identifier($class->_key_field),
            $base->db->quote_identifier($class->_key_field),
        )
    );

    if (($sthr->execute($newid, $id) || 0) != 1) {
        $base->log(LA_ERR, "Erreur renaming %s %s to %s",
            $class->type,
            $id, $newid,
        );
        return;
    }

    {
        my $obj = $base->get_object($class->type, $newid)
            or return;
        $obj->objectlog('Rename', 'Object renamed from %s', $id);
    }
    1;
}

=head2 db

Return reference to L<DBI> object.

=cut

sub db {
    return $_[0]->base->db;
}

sub _quote_object_table {
    my ($self) = @_;
    my $table = $self->_object_table or return;
    $self->db->quote_identifier($table);
}
sub _quote_key_field {
    my ($self) = @_;
    my $key_field = $self->_key_field or return;
    $self->db->quote_identifier($key_field);
}

sub get_field {
    my ($self, $field) = @_;
    if ($field eq 'unexported') {
        return $self->get_field('exported') ? undef : 1;
    }
    my $attr = $self->attribute($field) or return;
    if ($attr->{inline}) {
    my $sth = $self->db->prepare_cached(
        sprintf(
            q{select %s from %s where %s = ?},
            $self->db->quote_identifier(lc($field)),
            $self->_quote_object_table,
            $self->_quote_key_field,
        )
    );
    $sth->execute($self->id);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    return $res->{$field};
    } elsif ($self->_has_extended_attributes) { # else, then we mandatory have extend attr
        $self->base->{__cache}{"_" . $self->type} ||= {};
        my $__cache = $self->base->{__cache}{"_" . $self->type};
        if (!(exists($__cache->{$self->id})
            && $__cache->{$self->id}{__time} >= time - 1)) {
        my $sth = $self->db->prepare_cached(
            sprintf(
                q{
                select attr, value from %s 
                join %s on okey = ikey
                where %s = ?
                },
                $self->db->quote_identifier($self->_object_table. '_attributes'),
                $self->db->quote_identifier($self->_object_table),
                $self->db->quote_identifier($self->_key_field),
            )
        );
        $sth->execute($self->id);
        delete($__cache->{$self->id});
        $__cache->{$self->id}{__time} = time;
        while(my $res = $sth->fetchrow_hashref) {
            push(@{$__cache->{$self->id}{$res->{attr}}}, $res->{value});
        }
        #return @values > 1 ? \@values : $values[0];
        }
        my $val = $__cache->{$self->id}{$field};
        return @{$val || []} > 1 ? $val : $val->[0];
    }
}

sub set_fields {
    my ($self, %data) = @_;
    my @updated_attributes = ();
    my @fields;
    my @vals;
    my %ext;
    if (exists($data{unexported})) {
        $data{exported} = $data{unexported} ? 0 : 1;
    }
    if (exists($data{exported})) {
        $data{exported} ||= 0;
    }
    foreach my $field (keys %data) {
        my $attr = $self->attribute($field);
        next if ($field eq 'unexported');
        my $oldval = $self->get_field($field);
        next if (($data{$field} || '') eq ($oldval || ''));
        if ($attr->{inline}) {
        # TODO check fields exists !
            push(@fields, sprintf("%s = ?", $self->db->quote_identifier($field)));
            push(@vals, $data{$field});
            push(@updated_attributes, $field);
        } else {
            $ext{$field} = $data{$field};
        }
    }
    if (@fields) {
        my $sth = $self->db->prepare_cached(
            sprintf(
                q{update %s set %s where %s = ?},
                $self->_quote_object_table,
                join(', ', @fields),
                $self->_quote_key_field,
            )
        );
        $sth->execute(@vals, $self->id) or do {
            $self->base->log(LA_ERR,
                "Cannot update inline field for object %s, %s: %s",
                $self->type,
                $self->id,
                $self->base->db->errstr);
            return;
        };
    }
    
    if ($self->_has_extended_attributes) {
        my $sthd = $self->db->prepare_cached(
            sprintf(
                q{delete from %s where okey = ? and attr = ?},
                $self->db->quote_identifier($self->_object_table. '_attributes'),
            ),
        );
        my $sthd1 = $self->db->prepare_cached(
            sprintf(
                q{delete from %s where okey = ? and attr = ? and value = ?},
                $self->db->quote_identifier($self->_object_table. '_attributes'),
            ),
        );
        my $sthx = $self->db->prepare_cached(
            sprintf(
                q{insert into %s (okey, attr, value) values (?,?,?)},
                $self->db->quote_identifier($self->_object_table. '_attributes'),
            )
        );
        my $sthu = $self->db->prepare_cached(
            sprintf(
                q{update %s set value = ? where okey = ? and attr = ?},
                $self->db->quote_identifier($self->_object_table. '_attributes'),
            )
        );

        my $okey = $self->_get_ikey($self->base, $self->id);
        foreach my $uattr (keys %ext) {
            my $attr = $self->attribute($uattr);
            if ($ext{$uattr}) {
                if ($attr->{multiple}) {
                    my $updated = 0;
                    my $oldvalue = $self->get_field($uattr);
                    my %newvalues = map { $_ => 1 } (ref $ext{$uattr}
                        ? @{$ext{$uattr}}
                        : $ext{$uattr});
                    foreach (grep { $_ } ref $oldvalue ? @{$oldvalue} : $oldvalue) {
                        if(exists($newvalues{$_})) {
                            $newvalues{$_} = 0;
                        } else {
                            defined($sthd1->execute($okey, $uattr, $_)) or do {
                                $self->base->log(LA_ERR,
                                    "Error while updating attributes on %s/%s %s: %s",
                                    $self->type,
                                    $self->id,
                                    $uattr,
                                    $self->base->db->errstr
                                );
                                return;
                            };
                            $updated++;
                        }
                    }
                    foreach (grep { $newvalues{$_} } keys %newvalues) {
                        $sthx->execute($okey, $uattr, $_) or do {
                            $self->base->log(LA_ERR,
                                "Error while updating attributes: %s/%s %s: %s",
                                $self->type,
                                $self->id,
                                $uattr,
                                $self->base->db->errstr
                            );
                            return;
                        };
                        $updated++;
                    }
                    push(@updated_attributes, $uattr) if ($updated);
                } else {
                    my $res = $sthu->execute($ext{$uattr}, $okey, $uattr);
                    defined($res) or do {
                        $self->base->log(LA_ERR,
                            "Error while udapting attributes: %s/%s %s: %s",
                            $self->type,
                            $self->id,
                            $uattr,
                            $self->base->db->errstr
                        );
                        return;
                    };
                    if ($res == 0) {
                        $res = $sthx->execute($okey, $uattr, $ext{$uattr});
                        defined($res) or do {
                            $self->base->log(LA_ERR,
                                "Error while updating attributes: %s/%s %s: %s",
                                $self->type,
                                $self->id,
                                $uattr,
                                $self->base->db->errstr
                            );
                            return;
                        };
                    }
                    push(@updated_attributes, $uattr);
                }
            } else {
                defined($sthd->execute($okey, $uattr)) or do {
                    $self->base->log(LA_ERR,
                        "Error while deleting attributes: %s/%s %s: %s",
                        $self->otype,
                        $self->id,
                        $uattr,
                        $self->base->db->errstr
                    );
                    return;
                };
            }
        }
    }

    delete($self->base->{__cache}{"_" . $self->type}{$self->id});
    $self->objectlog('Update', 'Attributes %s where updated', join(', ', @updated_attributes))
        if(@updated_attributes);
    foreach (@updated_attributes) {
        my $attr = $self->attribute($_);
        $self->objectlog('Attribute', '%s set to %s', $_, (
                defined($data{$_}) 
                ? (ref $data{$_}
                    ? join(', ', @{ $data{$_} })
                    : $data{$_})
                : '(none)'))
            if ($attr->{notify});
    }
    scalar(@updated_attributes);
}

sub attributes_summary {
    my ($class, $base, $attribute) = @_;
    my $attr = $base->attribute($class->type, $attribute);
    if ($attr->{managed}) { 
        return $class->SUPER::attributes_summary($base, $attribute);
    }
    my $sth = $base->db->prepare_cached(
        $attr->{inline}
            ? sprintf(
                q{select %s as value from %s},
                $base->db->quote_identifier($attr->iname),
                $base->db->quote_identifier($class->_object_table),
            )
            : sprintf(
                q{select value from %s where attr = ? group by value},
                $base->db->quote_identifier($class->_object_table .
                    '_attributes'),
            )
    );
    $sth->execute($attr->{inline} ? () : ($attribute));

    my @values;
    while (my $res = $sth->fetchrow_hashref) {
        push(@values, $res->{value});
    }
    @values
}

sub _set_password {
    my ($self, $clear_pass) = @_;
    if (my $attr = $self->base->attribute($self->type, 'userPassword')) {
        my $field = $attr->iname;
        my @salt_char = (('a' .. 'z'), ('A' .. 'Z'), (0 .. 9), '/', '.');
        my $salt = join('', map { $salt_char[rand(scalar(@salt_char))] } (1 .. 8));
        my $res = $self->set_fields($field, crypt($clear_pass, '$1$' . $salt));
        my $asymencrypted = 0;
        if ($res) {

            if (my $serialize = $self->base->get_global_value('rsa_public_key')) {
                my $public = Crypt::RSA::Key::Public::SSH->new;
                $public->deserialize(String => [ $serialize ]);
                my $rsa = new Crypt::RSA ES => 'PKCS1v15';
                my $rsa_password = $rsa->encrypt (
                    Message    => $clear_pass,
                    Key        => $public,
                    Armour     => 1,
                ) || die $self->poll->rsa->errstr();
                if (!$self->_set_c_fields('encryptedPassword', $rsa_password)) {
                    $self->log(LA_ERR,
                        "Cannot set 'encryptedPassword' attribute for object %s/%s",
                        $self->type, $self->id,
                    );
                    return;
                }
                $asymencrypted = 1;
            }
            $self->base->log(LA_NOTICE,
                'Mot de passe changé pour %s',
                $self->id
            );
            $self->objectlog('Password', 'user password has changed%s', ($asymencrypted ? ' and stored using internal key' : ''));
            return 1;
        }
    } else {
        $self->log(LA_WARN,
            "Cannot set password: userPassword attributes is unsupported");
    }
}

sub search {
    my ($class, $base, @filter) = @_;

    my %attrsql;
    my %attrbind;

    while (my $item = shift(@filter)) {
        # attr=foo => no extra white space !
        # \W is false, it is possible to have two char
        my ($attr, $mode, $val) = $item =~ /^(\w+)(?:(\W)(.+))?$/ or next;
        if (!$mode) {
            $mode = '~';
            $val = shift(@filter);
        }
        my $attribute = $base->attribute($class->type, $attr) or do {
            $base->log(LA_ERR, "Unknown attribute $attr");
            next;
        };
        defined($val) or $val =  '';
        $val = $attribute->input($val);

        my $sql;

        # Specific case for unexported attribute, comming from exported value
        if ($attribute->iname eq 'unexported') {
            $sql = sprintf(
                q{select ikey from %s where %s},
                $base->db->quote_identifier($class->_object_table),
                $val ? q{exported='f'} : q{exported='t'}
            )
        } elsif ($attribute->{inline}) {
            $sql = sprintf(
                q{select ikey from %s where %s %s},
                $base->db->quote_identifier($class->_object_table),
                $base->db->quote_identifier($attribute->iname),
                $val eq '*'
                    ? 'is not NULL'
                    : $mode eq '~'
                        ? 'ILIKE ?'
                        : '= ?' 
            );
            push(@{$attrbind{$attr}}, $mode eq '~' ? '%' . $val . '%' : $val) unless($val eq '*');
        } else {
            $sql = sprintf(
                q{select okey from %s where attr = ? %s},
                $base->db->quote_identifier(
                    $class->_object_table . '_attributes'
                ),
                $val eq '*'
                    ? ''
                    : $mode eq '~'
                        ? q{and value ILIKE ?}
                        : q{and value = ?}

            );
            push(@{$attrbind{$attr}}, $attribute->iname);
            push(@{$attrbind{$attr}}, $mode eq '~' ? '%' . $val . '%' : $val) unless($val eq '*');
        }

        push(@{ $attrsql{$attr} }, $sql);
    }
    # building the query
    my @sqlintersec;
    if (!$base->{wexported}) {
        push(@sqlintersec, sprintf(
                q{select ikey from %s where exported = true},
                $base->db->quote_identifier($class->_object_table)
            )
        );
    }
    my @bind;
    foreach (keys %attrsql) {
        push(@sqlintersec, '(' . join(" union ", @{$attrsql{$_}}) . ")\n");
        push(@bind, @{$attrbind{$_} || []});
    }
    my $sth = $base->db->prepare(
        sprintf(q{
            select name from %s
            %s
            order by name
            },
            $base->db->quote_identifier($class->_object_table),
            @sqlintersec 
            ? "where ikey in (\n" . join("\n intersect\n", @sqlintersec) . ")\n"
            : '',
        )
    );
    $sth->execute(@bind);
    my @results;
    while (my $res = $sth->fetchrow_hashref) {
        push(@results, $res->{name});
    }
    return(@results);
}

=head2 register_attribute

Register attribute into base

=cut

sub register_attribute {
    my ($class, $base, $attribute, $comment) = @_;

    $class->is_registered_attribute($base, $attribute) and do {
        $base->log(LA_ERR, "The attribute $attribute already exists");
        return;
    };
    my $sth = $base->db->prepare(
        sprintf(q{
            insert into %s (canonical, description)
            values (?,?)
            }, $class->_attributes_table)
    );
    my $res = $sth->execute($attribute, $comment);
}

=head2 is_registered_attribute ($base, $attribute)

Return true is attribute is registered into base

=cut

sub is_registered_attribute {
    my ($class, $base, $attribute) = @_;

    my $sth = $base->db->prepare(
        sprintf(q{
            select 1 from %s where canonical = ?
            }, $class->_attributes_table
        )
    );
    $sth->execute($attribute);
    my $res = $sth->fetchrow_hashref;
    return $res ? 1 : 0;
}

=head2 get_attribute_comment $base, $attribute)

Return comment for C<$attribute>

=cut

# TODO: get_attribute_comment($attr, $base) { $base ||= $self->base ...

sub get_attribute_comment {
    my ($class, $base, $attribute) = @_;
    $base->attribute($class->type, $attribute) or do {
        $base->log(LA_ERR, "The attribute $attribute does not exists");
        return;
    };
    my $sth = $base->db->prepare(
        sprintf(q{
            select description from %s
            where canonical = ?
            }, $class->_attributes_table)
    );
    $sth->execute($attribute);
    if (my $res = $sth->fetchrow_hashref) {
        $sth->finish;
        return $res->{description};
    } else {
        return;
    }
}

=head2 set_attribute_comment ($base, $attribute, $comment)

Set comment to attribute

=cut

sub set_attribute_comment {
    my ($class, $base, $attribute, $comment) = @_;

    my $attr = $base->attribute($class->type, $attribute) or do {
        $base->log(LA_ERR, "The attribute $attribute does not exists");
        return;
    };
    $attr->{inline} and do {
        $base->log(LA_ERR,
            "Cannot set comment to inline attribute, sorry, blame the author !"
        );
        return;
    };
    my $sth = $base->db->prepare(
        sprintf(q{
            update %s set description = ?
            where canonical = ?
            }, $class->_attributes_table)
    );
    my $res = $sth->execute($comment, $attribute);
}

=head2 objectlog

=cut

sub objectlog {
    my ($self, $changetype, $message, @args) = @_;

    my $sth = $self->base->db->prepare(q{
        INSERT into objectslogs (ikey, otype, name, changetype, username, message)
        VALUES (?,?,?,?,?,?)
    });

    $sth->execute(
        $self->_get_ikey,
        $self->type,
        $self->id,
        $changetype,
        $self->base->user || '@Console',
        sprintf($message, @args),
    );
}

1;

__END__

=head1 SEE ALSO

L<LATMOS::Accounts::Bases::Sql>

L<LATMOS::Accounts::Bases::Objects>

=head1 AUTHOR

Olivier Thauvin, E<lt>olivier.thauvin@latmos.ipsl.frE<gt>

=head1 COPYRIGHT AND LICENSE

Copyright (C) 2008, 2009 CNRS SA/CETP/LATMOS

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself, either Perl version 5.10.0 or,
at your option, any later version of Perl 5 you may have available.


=cut
