#!/usr/bin/perl

#
# Copyright (C) 2017 Nethesis S.r.l.
# http://www.nethesis.it - nethserver@nethesis.it
#
# This script is part of NethServer.
#
# NethServer is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License,
# or any later version.
#
# NethServer is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with NethServer.  If not, see COPYING.
#

use esmith::ConfigDB;
use File::Temp;
use NethServer::SSSD;
use NethServer::Password;
use JSON;
my $sssd = new NethServer::SSSD();

my $db = esmith::ConfigDB->open_ro() or die "Could not open config db";

$fh = File::Temp->new();
my ($login,$pass,$uid,$gid) = getpwnam('postgres') or die "postgres not in passwd file";
chown $uid, $gid, $fh->filename;
print $fh "DELETE FROM core.domains WHERE domain_id = 'NethServer';\n";

my $encoded = '';
my $secret=$sssd->bindPassword();

# Some obsucre perl magic, thanks to Davidep
pipe RH, WH;
open(OLDIN, "<&STDIN");
open(STDIN, "<&RH");
if(open(PIPE, "-|")) {
    close(RH);
    print WH $secret;
    close(WH);
    {
        local $/ = undef;
        $encoded = <PIPE>;
    }
    chomp($encoded);
} else {
    exec("openssl enc -des-ecb -a -K 70617373776f7264");
}
close(PIPE);
close(RH);
open(STDIN, "<&OLDIN");

my $domain = $db->get('DomainName')->prop('type');
my $user = $sssd->bindDN();
my $uri = $sssd->ldapURI();
my $port = "389";
my $ssl = "null";
my $dir_params;
my $prefix = '';
my $spamFolder = $db->get_prop('dovecot','SpamFolder');

if ( $uri =~ /^ldaps/ ) {
    $ssl = "'SSL'";
    $port = "636";
}
if ( $sssd->startTls() ) {
    $ssl = "'STARTTLS'";
}

if ($sssd->isAD()) {
    $prefix = 'ldapneth://';
    $dir_params =  {
        loginDn => $sssd->userDN(),
        loginFilter => '&(objectCategory=person)(objectClass=user)',
        userIdField => 'sAMAccountName',
        userDn => $sssd->userDN(),
        userFilter => '(&(objectClass=user)(objectCategory=person)(!(isCriticalSystemObject=TRUE)))',
        userFirstnameField => 'givenName',
        userLastnameField => 'sn',
        userDisplayNameField => 'displayName'
    };
} else {
    $prefix = 'ldapneth://';
    $dir_params =  {  
        loginDn => $sssd->userDN(),
        loginFilter => undef,
        userIdField => 'uid',
        userDn => $sssd->userDN(),
        userFilter => undef,
        userFirstnameField => 'givenName',
        userLastnameField => 'sn',
        userDisplayNameField => 'gecos'
    };
}

$uri = $prefix . $sssd->host() . ":" . $port;

my $query = "INSERT INTO \"core\".\"domains\" (\"domain_id\", \"internet_name\", \"enabled\", \"description\", \"user_auto_creation\", \"dir_uri\", \"dir_admin\", \"dir_password\", \"dir_connection_security\", \"dir_case_sensitive\", \"dir_password_policy\", \"dir_parameters\") VALUES ('NethServer', '$domain', 't', 'NethServer', 't', '$uri', '$user', '$encoded', $ssl, 'f', 'f', '". encode_json($dir_params)."');\n";

print $fh $query;

# Insert zip media type - HACK for JavaMail bug
print $fh "DELETE FROM \"core\".\"media_types\" WHERE extension = 'zip' AND media_type = 'application/zip';\n";
print $fh "INSERT INTO \"core\".\"media_types\" (\"extension\", \"media_type\") VALUES ('zip', 'application/zip');\n";

# Create tablefunc extension if not already exist
print $fh "CREATE EXTENSION IF NOT EXISTS tablefunc;\n";

# Set also public url
my $public_url_or_vhost = '';

if ($db->get_prop('webtop','PublicUrl') ne "") {
    $public_url_or_vhost = $db->get_prop('webtop','PublicUrl');
} elsif ($db->get_prop('webtop','VirtualHost') ne "") {
    $public_url_or_vhost = 'https://'.$db->get_prop('webtop','VirtualHost').'/webtop';
}
my $public_url = $public_url_or_vhost || 'https://'.$db->get('SystemName')->prop('type').'.'.$domain.'/webtop';

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'public.url';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'public.url', '$public_url');\n";

# Set dav server url
my $davserver_url_or_vhost = '';

if ($db->get_prop('webtop','DavServerUrl') ne "") {
    $davserver_url_or_vhost = $db->get_prop('webtop','DavServerUrl');
} elsif ($db->get_prop('webtop','VirtualHost') ne "") {
    $davserver_url_or_vhost = 'https://'.$db->get_prop('webtop','VirtualHost').'/webtop-dav/server.php';
}

my $davserver_url = $davserver_url_or_vhost || 'https://'.$db->get('SystemName')->prop('type').'.'.$domain.'/webtop-dav/server.php';

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'davserver.url';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'davserver.url', '$davserver_url');\n";

# Set pbx provider
my $pbx_provider = $db->get_prop('webtop','PbxProvider') || "";

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'pbx.provider';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'pbx.provider', '$pbx_provider');\n";

if ($pbx_provider eq "nethvoice") {
    # Set NethVoice base url for api calls
    my $pbx_provider_nethvoice_webrest_url = $db->get_prop('webtop','PbxProviderNethvoiceWebrestUrl') || "";

    print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'pbx.provider.nethvoice.webrest.url';\n";
    print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'pbx.provider.nethvoice.webrest.url', '$pbx_provider_nethvoice_webrest_url');\n";
}

# Set vmail password
my $vmail_password = NethServer::Password::store('vmail');
print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'nethtop.vmail.secret';\n";
print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.mail' and key = 'nethtop.vmail.secret';\n";
print $fh "INSERT INTO \"core\".\"settings\" VALUES ('com.sonicle.webtop.mail', 'nethtop.vmail.secret', '$vmail_password');\n";

# Enable folder sorting
print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.mail' and key = 'sort.folders';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.mail', 'sort.folders', 'true');\n";

# Always disable built-in SPAM sieve filter
print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.mail' and key = 'sieve.spamfilter.disabled';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.mail', 'sieve.spamfilter.disabled', 'true');\n";

# Set spam folder name accordingly to Dovecot configuration
print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.mail' and key = 'default.folder.spam';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.mail', 'default.folder.spam', '$spamFolder');\n";

# Delete sent messages from draft folders
print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.mail' and key = 'default.folder.drafts.deletemsgonsend';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.mail', 'default.folder.drafts.deletemsgonsend', 'true');\n";

# Configure Nextcloud, do not replace values if already exist
print $fh "INSERT INTO \"core\".\"settings\" (service_id, key, value) SELECT 'com.sonicle.webtop.vfs', 'nextcloud.default.host', 'localhost' 
              WHERE NOT EXISTS (SELECT value from \"core\".\"settings\" WHERE key = 'nextcloud.default.host' and service_id = 'com.sonicle.webtop.vfs');\n";
print $fh "INSERT INTO \"core\".\"settings\" (service_id, key, value) SELECT 'com.sonicle.webtop.vfs', 'nextcloud.default.path', '/nextcloud/remote.php/webdav' 
              WHERE NOT EXISTS (SELECT value from \"core\".\"settings\" WHERE key = 'nextcloud.default.path' and service_id = 'com.sonicle.webtop.vfs');\n";

# Set ACL IMAP lowercase
print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.mail' and key = 'imap.acl.lowercase';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.mail', 'imap.acl.lowercase', 'true');\n";

# Fix issue NethServer/dev#5570 (see https://redmine.sonicle.com/issues/541)
print $fh "UPDATE calendar.events AS cevts SET href = cevts.public_uid || '.ics' FROM calendar.calendars AS ccals WHERE (cevts.calendar_id = ccals.calendar_id) AND (cevts.href IS NULL);\n";
print $fh "UPDATE contacts.contacts AS ccnts SET href = ccnts.public_uid || '.vcf' FROM contacts.categories AS ccats WHERE (ccnts.category_id = ccats.category_id) AND (ccnts.href IS NULL);\n";

# Set default size icons toolbar
my $default_toolbar_icons_size = $db->get_prop('webtop','DefaultToolBarIconsSize') || "medium";

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'default.viewport.header.scale';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'default.viewport.header.scale', '$default_toolbar_icons_size');\n";

# Set remote calendar automatic synchronization
my $remote_calendar_autosync = $db->get_prop('webtop','RemoteCalendarAutosync') || 'enabled';

if ($remote_calendar_autosync eq "enabled") {
	$remote_calendar_autosync = "true";
} elsif ($remote_calendar_autosync eq "disabled") {
	$remote_calendar_autosync = "false";
}

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.calendar' and key = 'calendar.remote.autosync.enabled';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.calendar', 'calendar.remote.autosync.enabled', '$remote_calendar_autosync');\n";

# Set remote calendar automatic synchronization only when user is online
my $remote_calendar_autosync_onlywhenonline = $db->get_prop('webtop','RemoteCalendarAutosyncOnlywhenonline') || 'disabled';

if ($remote_calendar_autosync_onlywhenonline eq "enabled") {
	$remote_calendar_autosync_onlywhenonline = "true";
} elsif ($remote_calendar_autosync_onlywhenonline eq "disabled") {
	$remote_calendar_autosync_onlywhenonline = "false";
}

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.calendar' and key = 'calendar.remote.autosync.onlywhenonline';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.calendar', 'calendar.remote.autosync.onlywhenonline', '$remote_calendar_autosync_onlywhenonline');\n";

# Set remote category automatic synchronization
my $remote_category_autosync = $db->get_prop('webtop','RemoteCategoryAutosync') || 'enabled';

if ($remote_category_autosync eq "enabled") {
	$remote_category_autosync = "true";
} elsif ($remote_category_autosync eq "disabled") {
	$remote_category_autosync = "false";
}

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.contacts' and key = 'category.remote.autosync.enabled';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.contacts', 'category.remote.autosync.enabled', '$remote_category_autosync');\n";

# Set remote category automatic synchronization only when user is online
my $remote_category_autosync_onlywhenonline = $db->get_prop('webtop','RemoteCategoryAutosyncOnlywhenonline') || 'disabled';

if ($remote_category_autosync_onlywhenonline eq "enabled") {
	$remote_category_autosync_onlywhenonline = "true";
} elsif ($remote_category_autosync_onlywhenonline eq "disabled") {
	$remote_category_autosync_onlywhenonline = "false";
}

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.contacts' and key = 'category.remote.autosync.onlywhenonline';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.contacts', 'category.remote.autosync.onlywhenonline', '$remote_category_autosync_onlywhenonline');\n";

# Set default field to use for ordering contacts to lastname
print $fh "INSERT INTO \"core\".\"settings\" (service_id, key, value) SELECT 'com.sonicle.webtop.contacts', 'default.showby', 'lnfn'
              WHERE NOT EXISTS (SELECT value from \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.contacts' and key = 'default.showby');\n";

# Migrate old SMTP defaults
my $sonicle_password = NethServer::Password::store('webtop5');
my $out = `PGPASSWORD=$sonicle_password psql -U sonicle -c "COPY(select value from core.settings where key = 'smtp.host' or key = 'smtp.port') TO STDOUT" webtop5`;
chomp $out;
my ($smtp_host,$smtp_port) = split(/\n/,$out);
if ($smtp_host eq 'localhost' && $smtp_port eq '587') {
    print $fh "UPDATE \"core\".\"settings\" SET value = '10587' WHERE key = 'smtp.port' AND service_id = 'com.sonicle.webtop.core' AND value = '587';\n";
    print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'smtp.auth';\n";
    print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'smtp.auth', 'true');\n";
    print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'smtp.port';\n";
    print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'smtp.port', '10587');\n";
    print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'smtp.starttls';\n";
    print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'smtp.starttls', 'false');\n";
}


# Enable/Disable new device notifications alerts
my $known_device_verification = $db->get_prop('webtop','KnownDeviceVerification') || 'disabled';

if ($known_device_verification eq "enabled") {
	$known_device_verification = "true";
} elsif ($known_device_verification eq "disabled") {
	$known_device_verification = "false";
}

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'security.knowndeviceverification.enabled';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'security.knowndeviceverification.enabled', '$known_device_verification');\n";

# Configure the new device notifications alerts's recipients
my $known_device_verification_recipients = $db->get_prop('webtop','KnownDeviceVerificationRecipients') || "";

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'security.knowndeviceverification.recipients';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'security.knowndeviceverification.recipients', '$known_device_verification_recipients');\n";

# Configure the network whitelists for the new device notifications alerts
my $known_device_verification_netwhitelist = $db->get_prop('webtop','KnownDeviceVerificationNetWhitelist') || "";

print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'security.knowndeviceverification.netwhitelist';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'security.knowndeviceverification.netwhitelist', '$known_device_verification_netwhitelist');\n";

# Set the name for the Jitsi Meet instance
print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'meeting.jitsi.name';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'meeting.jitsi.name', 'WebTop Meet');\n";

# Prepend username of the creator into the  Jitsi Meet conference room's name
print $fh "DELETE FROM \"core\".\"settings\" WHERE service_id = 'com.sonicle.webtop.core' and key = 'meeting.jitsi.meetingid.prependusername';\n";
print $fh "INSERT INTO \"core\".\"settings\" (\"service_id\", \"key\", \"value\") VALUES ('com.sonicle.webtop.core', 'meeting.jitsi.meetingid.prependusername', 'true');\n";

# Execute SQL script
system("su - postgres  -c 'psql webtop5 < ".$fh->filename."' >/dev/null");
