# Copyright 1999-2012. Parallels IP Holdings GmbH. All Rights Reserved.
package Dumper;

use strict;
use warnings;

use ConfixxConfig;
use SimpleDbConnect;
use DumperUtils;

# domains.richtigedomain constants
use constant DOMAIN_TYPE_SUB       => 0;
use constant DOMAIN_TYPE_STANDARD  => 1;
use constant DOMAIN_TYPE_WWW       => 2; # not used for emails
use constant DOMAIN_TYPE_SYSTEM    => 3;
use constant DOMAIN_TYPE_BLACKLIST => 5;
use constant DOMAIN_TYPE_SHARED    => 7;
# no much sense to declare this as a constant
my @DEFAULT_DOMAIN_TYPES = (DOMAIN_TYPE_SUB, DOMAIN_TYPE_STANDARD, DOMAIN_TYPE_WWW);

my $_db;

sub _getDb {
  unless (defined($_db)) {
    $_db = SimpleDbConnect->new(
      ConfixxConfig::getValue('dbType'), 
      ConfixxConfig::getValue('dbUser'), 
      ConfixxConfig::getValue('dbPw'), 
      ConfixxConfig::getValue('dbDB'), 
      ConfixxConfig::getValue('dbServer')
    );
  }

  return $_db;
}

sub getResellers {
  return _getDb()->queryList("SELECT anbieter FROM anbieter");
}

sub getClients {
  my ($reseller) = @_;
  return _getDb()->queryList("SELECT kunde FROM kunden WHERE anbieter = '$reseller'");
}

# @return client's system domain - the one that is created automatically by Confixx in provider's dns zone
sub getClientSystemDomain {
  my ($clientName) = @_;

  my @systemDomains = _getDb()->queryList("SELECT domain FROM domains WHERE kunde = '$clientName' AND richtigedomain = 3");

  if (scalar(@systemDomains) == 0) {
    return undef;
  }

  if (scalar(@systemDomains) > 1) {
    Logging::warning("Client '$clientName' has more then one system domains. Migrator will use first record.");
  }

  return $systemDomains[0];
}

##
# @return list of domains that can be selected for migration;
# All this domains are ordered by length, then id (which is the same as ordered by creation date);
# Ordering by ength is necessary to keep possible subdomains after their domains (most notably
# "webmail" subdomain)
##
sub getDomains {
  my ($client) = @_;
  my @domainTypes = @DEFAULT_DOMAIN_TYPES;

  return _getDb()->queryList("SELECT domain FROM domains "
    . "WHERE kunde = '$client' AND domain NOT LIKE '*%' AND richtigedomain IN "
    . ' (' . join(', ', @domainTypes) . ') '
    . "ORDER BY CHAR_LENGTH(domain), id");
}

# get wildcards subdomains of a specified domain
sub getWildcardSubdomains {
  my ($parentDomain) = @_;

  return _getDb()->queryList(
    "SELECT domains.domain FROM domains "
    . "JOIN domains main_domains ON main_domains.id = domains.grp "
    . "WHERE main_domains.domain = '$parentDomain' AND domains.domain LIKE '*%'"
  );
}

sub getResellerExclusiveIPs {
  my ($reseller) = @_;
  return _getDb()->queryTable("SELECT ip, kunde FROM ipadressen WHERE anbieter = '$reseller'");
}

sub getClientExclusiveIPs {
  my ($client) = @_;
  return _getDb()->queryList("SELECT ip FROM ipadressen WHERE kunde = '$client'");
}

sub getDomainInfo {
  my ($domain) = @_;
  my @domainTypes = (@DEFAULT_DOMAIN_TYPES, DOMAIN_TYPE_SYSTEM);

  if (defined($domain)) {
    my $domainInfo = _getDb()->queryMaxOneRow("SELECT * FROM domains WHERE domain = '$domain' " 
      . ' AND richtigedomain IN '
      . ' (' . join(', ', @domainTypes) . ') ');
    if (not defined($domainInfo)) { # we actually crafted this system domain, so craft the info
      $domainInfo = {
        'id' => 0, # we need the id for filenames (ugh), and in hope of system domain uniqueness let it be 0
        'richtigedomain' => DOMAIN_TYPE_SYSTEM,
        'domain' => $domain,
        'dns' => 0, # no domain - no DNS
        # TODO add other parameters if needed
      };
      # determine client/reseller
      if ($domain =~ /^(web\d+)\./) {
        my $client = $1;
        my $clientRow = _getDb()->queryOneRow("SELECT * FROM kunden where kunde = '$client'");
        $domainInfo->{'kunde'} = $clientRow->{'kunde'};
        $domainInfo->{'anbieter'} = $clientRow->{'anbieter'};
      }
    }
    return $domainInfo;
  } else {
    return;
  }
}

sub checkExistsDomain {
  my ($domainName) = @_;

  return _getDb()->queryOneValue(
    "SELECT EXISTS (SELECT * FROM domains WHERE domain = '$domainName')"
  );
}

sub getClientInfo {
  my ($client) = @_;

  my $clientInfo = _getDb()->queryOneRow("SELECT * FROM kunden WHERE kunde = '$client'");

  # strip leading exclamation mark, if any, to get real value of password
  $clientInfo->{'longpw'} =~ s/^!//;

  return $clientInfo;
}

sub canClientEditPhpSafeMode {
  my ($client) = @_;
  return _getDb()->queryOneValue(
    "SELECT COUNT(*) > 0 "
    . "FROM httpd_permissions JOIN httpd_definitions ON httpd_permissions.httpd_id = httpd_definitions.id "
    . "WHERE httpd_definitions.label = 'PHP safe_mode' AND httpd_permissions.user = '$client'"
  );
}

sub getClientSSLRecords {
  my ($client) = @_;
  return _getDb()->queryTable("SELECT crt, privatekey FROM cssl WHERE kunde='$client'");
}

sub getResellerInfo {
  my ($reseller) = @_;
  return _getDb()->queryOneRow("SELECT * FROM anbieter WHERE anbieter = '$reseller'");
}

sub canResellerEditPhpSafeMode {
  my ($reseller) = @_;
  return _getDb()->queryOneValue(
    "SELECT COUNT(*) > 0 "
    . "FROM httpd_permissions JOIN httpd_definitions ON httpd_permissions.httpd_id = httpd_definitions.id "
    . "WHERE httpd_definitions.label = 'PHP safe_mode' AND httpd_permissions.user = '' AND httpd_permissions.reseller  = '$reseller'"
  );
}

sub getClientTemplates {
  my ($reseller) = @_;
  return _getDb()->queryTable(
    "SELECT * FROM angebote WHERE anbieter = '$reseller'"
  );
}

sub getAdminPersonalInfo {
  return _getDb()->queryOneRow("SELECT * FROM personalinfo");
}

sub getAdminInfo {
  return _getDb()->queryOneRow("SELECT * FROM admin");
}

sub isResellerId {
  my ($id) = @_;
  return defined($id) && $id =~ /^res\d+$/;
}

sub isClientId {
  my ($id) = @_;
  return defined($id) && $id =~ /^web\d+$/;
}

sub getResellerForClient($) {
  my ($clientName) = @_;

  return _getDb()->queryOneValue(
    "SELECT anbieter FROM kunden WHERE kunde = '$clientName'"
  );
}

sub getDnsRecords {
  my ($domainName) = @_;

  # TODO use named_checkzone -D if available?
  my @dnsdata = _getDb()->queryTable("SELECT zonefile FROM dns WHERE domain = '$domainName'");
  if (@dnsdata == 0) {
      return;
  } 
  return split "\n", $dnsdata[0]->{'zonefile'};
}

sub parseDnsRecords {
  my @items = @_;
  my @lastRec;
  my @records;

  foreach (@items) {
    s/\s+$//;
    my @rawRec;
    my @rec;
    # check if we found a resource record such as "www 86400 IN A 10.0.2.15"
    if ( @rawRec = /^\s*(\S+)?\s+(\d+)?\s+(IN)?\s+(SOA|NS|A|CNAME|MX|PTR|TXT|SRV)\s+(.*)$/o) {
        for (my $i = 0; $i < @rawRec - 1; $i++) {
            # first three fields are optional, fill in the data from previous record if needed
            push @rec, ($rawRec[$i] ? $rawRec[$i] : $lastRec[$i]);
        }
        # split the last field
        push @rec, (split /\s+/, $rawRec[-1]);
        # store optional fields for future use
        @lastRec = @rec;
        push @records, \@rec;
    }
  }
  return @records;
}

##
# Get list of protected directories
# @param customerInfo_ptr - pointer to customer info, for whom to search directories
# @param pathPrefix - directory where to start searching protected directories from
#                     it can be domain's directory or cgi-bin directory
# @return hash of { path => { title => title, users => { user1 => password1, user2 => password2 ... }} }
##
sub getProtectedDirectories {
  my ($customerInfo_ptr, $pathPrefix) = @_;

  my %pDirs;
  if ($customerInfo_ptr->{'pwschutz'}) {

    # fetch list of protected directories, with their users - if any
    my $wrapDbh = _getDb()->getDbh();
    if ($wrapDbh->{'EXECUTE'}->("SELECT pfad, bereich, login, longpw "
                               ."FROM pwschutz LEFT JOIN users ON users.parent = pwschutz.ident "
                               ."WHERE pwschutz.kunde = '" . $customerInfo_ptr->{'kunde'} . "'")) {
      while (my $record_ptr = $wrapDbh->{'FETCHHASH'}->()) {
        my $path = $record_ptr->{'pfad'};

        # cut domain's path prefix, other than '/', from protected directory path
        if ($pathPrefix ne '/' && $path !~ s/^$pathPrefix//) {
            next; # given protected directory is not under domain's directory
        }

        $pDirs{$path}{'title'} = $record_ptr->{'bereich'};
        if ($record_ptr->{'login'}) {
          $pDirs{$path}{'users'}{$record_ptr->{'login'}} = $record_ptr->{'longpw'};
        }
      }
    }
    $wrapDbh->{'FINISH'}->();
  }

  return %pDirs;
}

sub getClientDatabaseNames {
  my ($client) = @_;
  return _getDb()->queryList("SELECT dbname FROM mysql_datenbanken WHERE kunde = '$client'");
}

sub getMboxesWithoutEmails {
  my ($client) = @_;

  # catch-all email addresses don't count as email.
  return _getDb()->queryList(
    "SELECT account FROM pop3 "
    . "WHERE kunde = '$client' "
    . "AND NOT EXISTS (SELECT * FROM email e, email_forward ef WHERE e.ident=ef.email_ident AND e.prefix <> '*' AND ef.pop3 = pop3.account)"
  );
}

# Get email prefixes and theirs destinations for $domainName
sub getEmailPrefixToDestinationTable {
  my ($client) = @_;

  return _getDb()->queryTable(
    "SELECT email.prefix, email.domain, email_forward.pop3 "
    . "FROM email JOIN email_forward ON email.ident = email_forward.email_ident "
    . "WHERE email.kunde = '$client' AND email.prefix != '*' "
    . "ORDER BY email.ident, email_forward.id"
  );
}

sub getCatchAllDestinationsOfDomain {
  my ($domainName) = @_;

  return _getDb()->queryList(
    "SELECT email_forward.pop3 "
    . "FROM email JOIN email_forward ON email.ident = email_forward.email_ident "
    . "WHERE email.domain = '$domainName' AND email.prefix = '*' "
    . "ORDER BY email.ident, email_forward.id"
  );
}

sub getCatchAllDestinationsOfClient {
  my ($client) = @_;

  return _getDb()->queryList(
    "SELECT email_forward.pop3 "
    . "FROM email JOIN email_forward ON email.ident = email_forward.email_ident "
    . "WHERE email.kunde = '$client' AND email.prefix = '*' "
    . "ORDER BY email.ident, email_forward.id"
  );
}

###
#   return value is a hash,
#   with keys - mbox names, and values - the first e-mail referencing corresponding box
###
sub getMboxToEmailMap {
  my ($client) = @_;
  my %mboxToEmailMap;

  foreach my $mbox (_getDb()->queryList("SELECT account FROM pop3 WHERE kunde = '$client'")) {
    my (@mainEmailTable) = _getDb()->queryTable(
      "SELECT email.prefix, email.domain "
      . "FROM email_forward "
      . "JOIN email ON email.ident = email_forward.email_ident "
      . "JOIN domains ON domains.domain = email.domain "
      . "WHERE email_forward.pop3 = '$mbox' AND email.kunde = '$client' AND email.prefix NOT LIKE '*' "
      . "ORDER BY domains.id, email.ident LIMIT 1"
    );
    if (@mainEmailTable) {
      $mboxToEmailMap{$mbox} = "$mainEmailTable[0]->{'prefix'}\@$mainEmailTable[0]->{'domain'}";
    }
  }

  return \%mboxToEmailMap;
}

sub getMboxes {
    my ($client) = @_;
    return _getDb()->queryList("SELECT account FROM pop3 WHERE kunde = '$client'");
}

sub checkExistEmail {
  my ($emailPrefix, $domainName) = @_;

  return _getDb()->queryOneValue(
    "SELECT EXISTS (SELECT * FROM email WHERE domain = '$domainName' AND prefix = '$emailPrefix')"
  );
}

sub getMboxInfo {
  my ($mboxName) = @_;
  return _getDb()->queryOneRow("SELECT * FROM pop3 WHERE account = '$mboxName'");
}

sub getMaillists {
  my ($domainName) = @_;

  my @maillistInfos = _getDb()->queryTable(
    "SELECT name, gesperrt, owner_mail, pwd, domain_id "
    . "FROM maillist JOIN domains ON domains.id = maillist.domain_id "
    . "WHERE domains.domain = '$domainName'");

  for my $maillistInfo_ptr (@maillistInfos) {
    my $recipientsFile = "$ConfixxConfig::majordomo_ldir/" . $maillistInfo_ptr->{'domain_id'} . "/lists/" . $maillistInfo_ptr->{'name'};
    my @recipients = grep(!/^$/, @{DumperUtils::getFileLines($recipientsFile)});
    $maillistInfo_ptr->{'recipients'} = \@recipients;
  }

  return \@maillistInfos;
}

sub getMboxSpamPreferences {
  my ($mboxName) = @_;
  return _getDb()->queryTable("SELECT preference, value FROM spampref WHERE username='$mboxName'");
}

sub getEmailAutoresponder {
  my ($emailPrefix, $domainName) = @_;

  my @autoresponders = _getDb()->queryTable(
    "SELECT a.emailbetreff AS subject, a.emailtext AS text, "
    . "a.absendername AS fromname, a.absenderemail AS fromemail "
    . "FROM autoresponder a JOIN email e ON a.ident = e.ident "
    . "WHERE e.prefix = '$emailPrefix' AND e.domain = '$domainName'"
  );

  if (@autoresponders) {
    return $autoresponders[0];  # return hash from 1st string
  }

  return undef;
}

sub getEmailAddressBook {
  my ($mboxName) = @_;

  return _getDb()->queryTable(
    "SELECT * FROM webmail_ab WHERE pop3 = '$mboxName'"
  );
}

sub getTraffic {
  my ($client) = @_;

  return _getDb()->queryTable(
    "SELECT tag AS day, monat AS month, jahr AS year, ftp, web, email "
    . " FROM transfer WHERE kunde = '$client' ORDER BY jahr, monat, tag"
  );
}

# @return array of strings - HTTPD config parts
sub getDomainSelectableHttpdSpecials {
  my ($domainName) = @_;

  return _getDb()->queryList(
    "SELECT data FROM httpd_entries "
    . "JOIN httpd_states ON httpd_entries.state = httpd_states.id "
    . "JOIN domains ON domains.id = httpd_entries.domain_id " 
    . "WHERE domains.domain = '$domainName'"
  );
}

# @return string
sub getDomainAdvancedModeHttpdSpecials {
  my ($domainName) = @_;

  my $httpdSpecialsText = _getDb()->queryOneValue(
    "SELECT zusatz FROM domains WHERE domain = '$domainName'"
  );

  return $httpdSpecialsText ne ''? $httpdSpecialsText : undef;
}

sub getFtpRecords {
  my ($clientName) = @_;

  return _getDb()->queryTable("SELECT * FROM ftp WHERE kunde = '$clientName'");
}

sub getSharedDomains {
  my ($client) = @_;

  return _getDb()->queryList("SELECT domain FROM domains WHERE richtigedomain = 7 AND kunde = '$client'");
}

sub getAtDomains {
  my ($client) = @_;

  return _getDb()->queryList("SELECT CONCAT(atdomains.name, '\@', domains.domain) "
    . "FROM atdomains JOIN domains ON atdomains.id_from = domains.id "
    . "WHERE domains.kunde = '$client'"
  );
}

sub hasClientCustomErrorDocs {
  my ($client) = @_;

  return _getDb()->queryOneValue("SELECT COUNT(*) > 0 FROM fehlermeldungen WHERE kunde = '$client' AND seite != ''");
}

sub hasIpRestrictions {
  return _getDb()->queryOneValue("SELECT COUNT(*) > 0 FROM iprestriction");
}

sub hasBlackListedDomains {
  return _getDb()->queryOneValue("SELECT COUNT(*) > 0 FROM domains WHERE richtigedomain = 5 AND domain != (SELECT confixx_domain FROM admin)");
}

sub getEmailsWithPercentSign {
  my ($client) = @_;

  return _getDb()->queryList("SELECT CONCAT(prefix, '\@', domain) FROM email WHERE prefix LIKE '\%\\\%\%' AND kunde = '$client'");
}

sub hasClientCronJobs {
  my ($client) = @_;

  return _getDb()->queryOneValue("SELECT COUNT(*) FROM cronjobs WHERE kunde = '$client'");
}

sub hasClientAddressbookEntries {
  my ($client) = @_;

  return _getDb()->queryOneValue("SELECT COUNT(*) > 0 FROM pop3 JOIN webmail_ab ON webmail_ab.pop3 = account AND kunde = '$client'");
}

sub getCronJobs {
  my ($client) = @_;
  return _getDb()->queryList("SELECT cronfile FROM cronjobs WHERE kunde = '$client'");
}

1;
