#!/usr/bin/perl -w
#
# mysociety-create-database:
# Creates databases that are listed in vhosts.pl. Creates corresponding users
# and gives them permissions and right password. Testharness databases/users
# get the extra ownership and permissions they need.

package main;

use strict;
require 5.8.0;

use FindBin;
use lib "$FindBin::RealBin/../perllib";
my $mysociety_bin = $FindBin::RealBin;
my $servers_dir = "$FindBin::RealBin/../../servers";
my $mysociety_dir = "$FindBin::RealBin/../../mysociety";

use mySociety::SystemMisc qw(shell);
use mySociety::Config;

use Data::Dumper;
use Sys::Hostname;
use IO::Socket::INET;
use DBI

our $verbose = $ENV{VERBOSE} ? 1 : 0;

#####################################################################
# General functions

sub pgpw {
    $_ = shift;
    $_ = `/data/mysociety/bin/pgpw $_`;
    s/\s+$//;
    return $_;
}

#####################################################################
# Main code

if (getpwuid($>) ne 'postgres' && getpwuid($>) ne 'root') {
    die "Please run as user root, script will be automatically run also as user postgres";
}

# Get postgres version
my $pg_version;

open CLUSTERS, '/usr/bin/pg_lsclusters -h|' or die "can't run pg_lsclusters: $!";
while(<CLUSTERS>) {
    my ($version, $cluster, $port, $status, $owner, $dir, $logfile) = split /\s+/;
    $pg_version = $version if($cluster eq 'main');
}
close CLUSTERS;

die 'no "main" cluster found in pg_lsclusters output' if(! $pg_version);

# Read in configuration file
our ($vhosts, $sites, $databases);
# postgres user can't access /data/servers but we create a copy at the bottom
if (getpwuid($>) eq 'postgres' ) {
    require "/data/vhosts.pl";
} else {
    require "$servers_dir/vhosts.pl";
}

# Various other parameters
my $myhostname = hostname;

# Go through each database
my $database_configs;
foreach my $database (keys %{$databases}) {
    my $params = $databases->{$database};
    if ($params->{type} eq 'psql') {
        # Is it for our server? and we are right user?
        if ($params->{host} eq $myhostname && getpwuid($>) eq 'postgres') {

            my @out;

            # Testharness databases get dropped and recreated by the test script, so
            # give them more permissions.
            my $testharness = $database =~ m/testharness$/;

            # Connect to server
            my $dbh = DBI->connect("dbi:Pg:dbname=template1;port=$params->{port}", 'postgres', undef, { PrintWarn => 1, PrintError => 1, RaiseError => 0, AutoCommit => 1 }) || die DBI->errstr();

            # Create user with same name as database
            if (!$dbh->selectrow_array("select count(*) from pg_catalog.pg_user where usename = '$database'")) {
                my $password = $params->{new_pgpw} ? pgpw("-n $database") : pgpw($database);
                push @out, "creating user";
                $dbh->do("create user \"$database\" with password '$password'");
            }

            # Create database
            if (!$dbh->selectrow_array("select count(*) from pg_catalog.pg_database where datname = '$database'")) {
                push @out, "creating db";
                my $owner_clause = $testharness ? qq{owner "$database"} : "";
                # template1 is UTF-8, so we have to use template0 for different encodings
                my $template_clause = $params->{encoding} ? 'template "template0"' : '';
                my $encoding = $params->{encoding} || 'UTF8';
                $dbh->do("create database \"$database\" $owner_clause $template_clause encoding '$encoding'");
                if ($params->{geo}) {
                    # Need to reconnect to get search_path to be used properly
                    my $one = [ "ALTER DATABASE \"$database\" SET search_path TO public,postgis" ];
                    my $two = [
                        "CREATE SCHEMA postgis",
                        "CREATE EXTENSION postgis SCHEMA postgis",
                        "CREATE EXTENSION postgis_topology",
                        "GRANT USAGE ON SCHEMA postgis TO \"$database\"",
                    ];
                    foreach my $queries ($one, $two) {
                        my $dbh_db = DBI->connect("dbi:Pg:dbname=$database;port=$params->{port}", 'postgres', undef, { PrintWarn => 1, PrintError => 1, RaiseError => 0, AutoCommit => 1 }) || die DBI->errstr();
                        $dbh_db->do($_) foreach @$queries;
                        $dbh_db->disconnect;
                    }
                }
                if ($params->{extensions}) {
                    my $dbh_db = DBI->connect("dbi:Pg:dbname=$database;port=$params->{port}", 'postgres', undef, { PrintWarn => 1, PrintError => 1, RaiseError => 0, AutoCommit => 1 }) || die DBI->errstr();
                    foreach (@{$params->{extensions}}) {
                        $dbh_db->do("create extension $_");
                    }
                    $dbh_db->disconnect;
                }
            }

            # Give user permissions on database
            $dbh->do("grant all on database \"$database\" to \"$database\" ");
            if ($testharness) {
                $dbh->do("alter user \"$database\" with createdb");
            }

            # Give user permissions on public schema on PostgreSQL 15+
            if ($pg_version >= 15) {
                my $dbh_db = DBI->connect("dbi:Pg:dbname=$database;port=$params->{port}", 'postgres', undef, { PrintWarn => 1, PrintError => 1, RaiseError => 0, AutoCommit => 1 }) || die DBI->errstr();
                $dbh_db->do("grant all on schema public to \"$database\" ");
                $dbh_db->disconnect;
            }

            push @out, "no change" if $::verbose && !@out;
            print "$database (psql", ($params->{port} == 5432 ? "" : " port $params->{port}"), "): ", join('; ', @out), "\n" if @out;

            $dbh->disconnect();
        }
    } elsif ($params->{type} eq 'mysql') {
        if ($params->{host} eq $myhostname && getpwuid($>) eq 'root') {
            print "mysql database '$database' on host $myhostname\n" if $::verbose;

            # get the root db password
            my $root_password = pgpw('root');
            # Connect to server
            my $dbh = DBI->connect("dbi:mysql:dbname=mysql", 'root', $root_password, { PrintWarn => 1, PrintError => 1, RaiseError => 0, AutoCommit => 1 }) || die DBI->errstr();

            # Create database
            $dbh->do("create database if not exists `$database`");

            # Create user with same name as database
            my $password = $params->{new_pgpw} ? pgpw("-n $database") : pgpw($database);
            $dbh->do("grant all on `$database`.* to `$database` identified by '$password'");
        }
    } else {
        die "unknown database type '$params->{type}'";
    }
}

# Become user postgresql, and run script again to do postgresql ones
if (getpwuid($>) eq 'root') {
    # XXX yeuch, but seems easiest way to give postgres access to vhosts.pl, and postgres_secret
    system("/data/mysociety/bin/copy-vhosts-pl");
    chown((getpwnam("root"))[2], (getgrnam("postgres"))[2], "/etc/mysociety/postgres_secret");
    chmod(0755, '/data/mysociety/bin/pgpw');
    exit system("su postgres -c \"$0\"") >> 8;
}
