package OpenResty::Backend::Pg; use strict; use warnings; 
#use Smart::Comments;
use DBI;
use OpenResty::SQL::Select;
use OpenResty::Limits;
use base 'OpenResty::Backend::Base';

our $Recording;
our ($Host, $User, $Password, $Port, $Database);

sub new {
    my $class = shift;
    my $opts = shift || {};

    $Host ||= $OpenResty::Config{'backend.host'}  or
        die "No backend.host specified in the config files.\n";
    $User ||= $OpenResty::Config{'backend.user'} or
        die "No backend.user specified in the config files.\n";
    $Password ||= $OpenResty::Config{'backend.password'} || '';
    $Port ||= $OpenResty::Config{'backend.port'};
    $Database ||= $OpenResty::Config{'backend.database'} or
        die "No backend.database specified in the config files.\n";

    my $dbh = DBI->connect(
        "dbi:Pg:dbname=$Database;host=$Host".
            ($Port ? ";port=$Port" : ""),
        $User, $Password,
        {AutoCommit => 1, RaiseError => 1, pg_enable_utf8 => 0, %$opts, PrintError => 0}
    );

    $Recording = $OpenResty::Config{'backend.recording'} && ! $OpenResty::Config{'test_suite.use_http'};
    if ($Recording) {
        if ($0 =~ m{([^/]+)/([^/]+\.t)$}) {
            my $dir = '';
            if ($1 ne 't') { $dir = $1; }
            my $file = $2;
            require OpenResty::Backend::PgMocked;
            OpenResty::Backend::PgMocked->start_recording_file($file, $dir);
        } else {
            warn "Config setting backend.recording ignored.";
            undef $Recording;
        }
    }

    return bless {
        dbh => $dbh
    }, $class;
}

END {
    if ($Recording) {
        OpenResty::Backend::PgMocked->stop_recording_file();
    }
}

sub select {
    my ($self, $sql, $opts) = @_;
    $opts ||= {};
    my $dbh = $self->{dbh};
    if ($Recording) {
        my $res;
        eval {
            $res = $dbh->selectall_arrayref(
                $sql,
                $opts->{use_hash} ? {Slice=>{}} : ()
            );
        };
        if ($@) {
            my $err = $@;
            OpenResty::Backend::PgMocked->record($sql => bless \$err, 'die');
            die $err;
        }
        OpenResty::Backend::PgMocked->record($sql => $res);
        return $res;
    } else {
        #warn "==== ", $OpenResty::Dumper->($sql), "\n";
        return $dbh->selectall_arrayref(
            $sql,
            $opts->{use_hash} ? {MaxRows => $MAX_SELECT_LIMIT, Slice=>{}} : ()
        );
    }
}

sub do {
    my ($self, $sql) = @_;
    if ($Recording) {
        my $res;
        eval { $res = $self->{dbh}->do($sql); };
        if ($@) {
            my $err = $@;
            OpenResty::Backend::PgMocked->record($sql => $err => 'die');
            die $err;
        }
        OpenResty::Backend::PgMocked->record($sql => $res);
        return $res;
    } else {
        #warn "==== ", $OpenResty::Dumper->($sql), "\n";
         $self->{dbh}->do($sql);
    }

}

sub quote {
    my ($self, $val) = @_;
    return $self->{dbh}->quote($val);
}

sub quote_identifier {
    my ($self, $val) = @_;
    return $self->{dbh}->quote_identifier($val);
}

sub last_insert_id {
    my ($self, $table) = @_;
    #die "Found table!!! $table";
    my $res = $self->select("select max(id) from \"$table\"");
    if ($res && @$res) { return $res->[0][0]; }
}

sub has_user {
    my ($self, $user) = @_;
    my $select = OpenResty::SQL::Select->new('nspname')
        ->from('pg_namespace')
        ->where(nspname => $self->quote($user))
        ->limit(1);
    my $retval;
    eval {
        $retval = $self->do("$select");
    };
    if ($@) { warn $@; }
    no warnings 'uninitialized';
    return $retval + 0;
}

sub set_user {
    my ($self, $user) = @_;
    my $old_user = $self->{user};
    if (!$old_user or $user ne $old_user) {
        $self->do("set search_path to $user,public");
        $self->{user} = $user;
    }
}

sub add_user {
    my ($self, $user, $password, $has_schema) = @_;
    if ($has_schema) {
       die "Schema $user not found.\n" if !$self->has_user($user);
       $self->add_schema_user($user);
    } else {
        $self->add_empty_user($user);
    }
    $self->SUPER::add_user($user, $password);
}

sub add_empty_user {
    my ($self, $user) = @_;
    $self->do(<<"_EOC_");
create schema $user;
set search_path to $user;
_EOC_
}

sub add_schema_user {
    my ($self, $user) = @_;
    $self->do(<<"_EOC_");
set search_path to $user;
_EOC_
}

sub drop_user {
    my ($self, $user) = @_;
    $self->SUPER::drop_user($user);
    $self->do(<<"_EOC_");
drop schema $user cascade
_EOC_
}

sub login {
    my ($self, $account, $role, $captcha, $pass) = @_;

    $account = $self->quote($account);
    $role = $self->quote($role);
    $captcha = $self->quote($captcha);
    $pass = $self->quote($pass);

    eval {
        $self->do(<<"_EOC_");
create or replace function login(account text, role text,
  captcha text, pass text) returns integer as \$\$
declare
  u text;
begin
  execute 'set search_path to '$account'';
  if captcha is not null then

    if captcha !~ '[^ ]:[^ ]' then
      raise exception 'Bad captcha parameter: %', captcha;
    end if;
    execute 'select name from _roles where name = '''||role||''' and login = ''captcha''' into u;
    if u is null then
      raise exception 'Cannot login as %.% via captchas.', account , role;
    end if;
  elsif pass is not null then

    execute 'select name from _roles where name = '''||role||''' and login = ''password'' and password = '''||pass||'''' into u;
    if u is null then
      raise exception 'Password for %.% is incorrect.', account , role;
    end if;
  else

    execute 'select name from _roles where name = '''||role||''' and login = ''anonymous''' into u;
    if u is null then
      raise exception 'Password for %.% is required.', account , role;
    end if;
  end if;
  return 0;
end;
\$\$ language plpgsql;
select login($account, $role, $captcha, $pass);
_EOC_
    };
    if ($@) {
        (my $error = $@) =~ s/^DBD::Pg::db do failed: ERROR:  //g;
        die $error;
    }
}

1;
__END__

=head1 NAME

OpenResty::Backend::Pg - OpenResty backend for PostgreSQL standalone databases

=head1 INHERITANCE

    OpenResty::Backend::Pg
        ISA OpenResty::Backend::Base

=head1 SYNOPSIS

=head1 DESCRIPTION

=head1 AUTHOR

Agent Zhang (agentzh) C<< <agentzh@yahoo.cn> >>

=head1 SEE ALSO

L<OpenResty::Backend::Base>, L<OpenResty::Backend::PgFarm>, L<OpenResty::Backend::PLPerl>, L<OpenResty::Backend::PgMocked>, L<OpenResty>.