#!/usr/bin/env python
# -*- coding: utf-8 -*-
##
#
# Vaisala software source code file
#
# Copyright (c) Vaisala Oyj 2014, 2015. All rights reserved.
#
##
"""
Tool for radar system database maintenance.
"""
from __future__ import print_function

import ConfigParser
import argparse
import collections
import errno
import glob
import logging
import os
import platform
import string
import subprocess
import sys

from hashlib import sha512
from os import environ as env
from os import path


logging.basicConfig(format='%(asctime)s - %(levelname)s - %(message)s',
                    level=logging.DEBUG)
logger = logging.getLogger(__name__)


DATABASE_PACKAGE_LIB_DIR = "/usr/vaisala/radarsw/database/lib"
DEFAULT_CONFIG_DIRECTORY = env.get("VAISALA_RADARSW_CONFIG_DIR",
                                   '/etc/vaisala/radarsw/configuration')


DBConfig = collections.namedtuple('DBConfig', ['username', 'password', 'database'])


class DbToolException(Exception): pass


def is_windows():
    return platform.system() == 'Windows' or \
        "cygwin" in platform.system().lower()


def read_config(config_directory=DEFAULT_CONFIG_DIRECTORY):
    '''Reads in configuration INI files such as:

    [DATASOURCE]
    datasource.url = jdbc:postgresql://localhost:5432/db_name
    datasource.username = user
    datasource.password = password

    Returns:
        A DBConfig instance.
    '''
    override_config_file = path.join(config_directory, 'vsoweb-override.ini')
    default_config_file = path.join(config_directory, 'default', 'vsoweb-default.ini')
    configs = [override_config_file, default_config_file]

    if any(map(path.isfile, configs)) is not True:
        raise DbToolException(
            u"No valid configuration files found, tried {0}".format(
                ", ".join(configs)))

    username, password, database = None, None, None
    for config_path in configs:
        if not path.isfile(config_path):
            continue

        logger.debug("Reading config file %s" % config_path)
        config = ConfigParser.RawConfigParser()
        config.read(config_path)

        username_option = 'DATASOURCE', 'datasource.username'
        if username is None and config.has_option(*username_option):
            username = config.get(*username_option)

        password_option = 'DATASOURCE', 'datasource.password'
        if password is None and config.has_option(*password_option):
            password = config.get(*password_option)

        url_option = 'DATASOURCE', 'datasource.url'
        if database is None and config.has_option(*url_option):
            db_url = config.get(*url_option)
            # See http://jdbc.postgresql.org/documentation/head/connect.html for details.
            if '/' in db_url:
                database = db_url.split('/')[-1]
            else:
                database = db_url.split(':')[-1]

    if password is None:
        raise DbToolException("No password found for database!")

    result = DBConfig(username=username, password=password, database=database)
    logger.info("Using database configuration: %s" % repr(result))
    return result


def handle_error(step_description, tpl):
    """Error handler and reporter used in calling external executables.

    Args:
        step_description: A human-readable description of the step being
            executed fit for inclusion into an error message, in the form of
            'creating user' or 'dropping database'.
        tpl: A four-tuple consisting of 
            (command executed,
             command return code,
             stdout as string,
             stderr as string); basically something run_as_superuser returns.
    """
    command, retcode, out, err = tpl
    if retcode == 0:
        return

    logger.error(u"""Command output:
----
{}
----
Command error output:
----
{}
----""".format(out, err))

    raise DbToolException(
        u"Encountered error while {0}. Command exited with code {1}.".format(
            step_description, retcode))


def run_as_superuser(sql, database="postgres"):
    """Execute SQL as database super-user. Works on both Windows and Linux.

    Args:
        sql: SQL to execute as string.
        database: Database to run the SQL in. Default is 'postgres' as this
            tool is mainly used for managing databases.

    Returns:
        A four-tuple of
            (command executed,
             command return code,
             stdout as string,
             stderr as string)
    """
    if platform.system() == 'Linux':
        cmd = ['su', '-', 'postgres', '-c', "psql -t -v ON_ERROR_STOP=1 -d " + database + "; exit $?"]
        logger.info(u"Attempting to run {0}".format(' '.join(cmd)))
        p = subprocess.Popen(cmd, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
        out_data, err_data = p.communicate(sql)
        return cmd, p.returncode, out_data, err_data
    elif is_windows():
        cmd = ['java', '-jar', 'jx-migrationtool.jar', '--sql', '"' + sql + '"', '--url',
               "jdbc:postgresql://localhost:5432/" + database + "?user=postgres&password=postgres"]
        logger.info(u"Attempting to run {0}".format(' '.join(cmd)))
        p = subprocess.Popen(cmd,
                             stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
        out_data, err_data = p.communicate(sql)
        return cmd, p.returncode, out_data, err_data


def create_db_cli(db_config):
    sql = '''
CREATE DATABASE %s
  WITH OWNER = %s
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       CONNECTION LIMIT = -1;
''' % (db_config.database, db_config.username)
    return run_as_superuser(sql)


def create_user_cli(db_config):
    sql = '''
CREATE ROLE %s
  WITH
    LOGIN PASSWORD '%s'
    NOSUPERUSER
    INHERIT
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION;
''' % (db_config.username, db_config.password)
    return run_as_superuser(sql)


def drop_db_cli(db_config):
    sql = 'DROP DATABASE IF EXISTS %s;' % db_config.database
    return run_as_superuser(sql)


def drop_user_cli(db_config):
    sql = 'DROP USER IF EXISTS %s;' % db_config.username
    return run_as_superuser(sql)


def migrate_cli(db_config, migration_tool_name='radarsw-db'):
    script_dir = os.path.dirname(os.path.abspath(__file__))
    if script_dir == os.path.abspath('.'):
        script_dir = '.'
    schema_migration_tool_dir = path.join(script_dir, migration_tool_name, 'target')

    if not path.exists(schema_migration_tool_dir):
        schema_migration_tool_dir = DATABASE_PACKAGE_LIB_DIR
    if not path.exists(schema_migration_tool_dir):
        raise Exception("Cannot find database migration tools directory.")

    file_name_list = glob.glob(path.join(schema_migration_tool_dir, migration_tool_name + '-[D2]*-cli-exec.jar'))

    if len(file_name_list) != 1:
        raise Exception("Cannot find proper database migration tool.")

    schema_migration_tool_path = file_name_list[0]

    cmd = ['java', '-jar', schema_migration_tool_path, '-nodbinitialization',
           '-dbname', db_config.database,
           '-dbuser', db_config.username,
           '-dbpass', db_config.password]
    logger.info(u"Attempting to run {0}".format(' '.join(cmd)))
    p = subprocess.Popen(cmd, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    out_data, err_data = p.communicate()
    return cmd, p.returncode, out_data, err_data


def migrate_c5_data(db_config):
    create_user(db_config)
    create_db(db_config)
    handle_error("migrating database to wx 3.0.0", migrate_cli(db_config, 'radarsw-db-wx-3.0.0'))
    return handle_error("migrating Carbon Five data to Liquibase", migrate_c5_data_cli())


def migrate_c5_data_cli():
    script_dir = os.path.dirname(os.path.abspath(__file__))
    # From 1.0 to 2.0, we went from CarbonFive to Liquibase, so a new db was created
    # and thus the data needs to be moved from one db to the other.
    data_migration_tool_name = 'radarsw-db-migration-carbonfive-to-liquibase-*.jar'
    data_migration_tool_dir = path.join(script_dir,
                                        'radarsw-db-migration-carbonfive-to-liquibase', 'target')

    if not path.exists(data_migration_tool_dir):
        data_migration_tool_dir = DATABASE_PACKAGE_LIB_DIR
    if not path.exists(data_migration_tool_dir):
        raise Exception("Cannot find database data migration tools directory.")

    file_name_list = glob.glob(path.join(data_migration_tool_dir, data_migration_tool_name))

    if len(file_name_list) < 1:
        raise Exception("Cannot find database data migration tool.")
    elif len(file_name_list) > 1:
        # This protects against the radarsw-db-migration-carbonfive-to-liquibase-*.jar
        # pattern finding more than one file.
        raise Exception("More than one database data migration tool found.")

    data_migration_tool_name = file_name_list[0]
    data_migration_tool_path = path.join(data_migration_tool_dir, data_migration_tool_name)

    cmd = ['java', '-jar', data_migration_tool_path]
    logger.info(u"Attempting to run {0}".format(' '.join(cmd)))
    p = subprocess.Popen(cmd, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    out_data, err_data = p.communicate()
    if p.returncode != 0:
        logger.info("C5 to Liquibase migration failed.  Migration requires proper "
                    "application.properties file in CWD or equivalent in env.\n"
                    "Example of required properties:\n"
                    "datasource.driverClassName=org.postgresql.Driver\n"
                    "export.url=jdbc:postgresql://localhost:5432/vsowebdb\n"
                    "export.username=vsowebuser\n"
                    "export.password=xxx\n"
                    "import.url=jdbc:postgresql://localhost:5432/wxdb2\n"
                    "import.username=wxuser\n"
                    "import.password=xxx")
    return cmd, p.returncode, out_data, err_data


### Functions with error reporting:
def create_db(db_config):
    return handle_error("creating database", create_db_cli(db_config))


def create_user(db_config):
    return handle_error("creating user", create_user_cli(db_config))


def migrate(db_config):
    return handle_error("migrating database", migrate_cli(db_config))


def drop_db(db_config):
    return handle_error("dropping (removing) database", drop_db_cli(db_config))


def drop_user(db_config):
    return handle_error("dropping (removing) user", drop_user_cli(db_config))


def db_exists(db_config):
    assert is_windows() is False, "This command can only be run on Linux."
    sql = "SELECT 1 from pg_database WHERE datname='%s';" % (db_config.database)
    command, retcode, out, err = run_as_superuser(sql)
    print(out)
    print(err, file=sys.stderr)

    if out.strip() != "1":
        sys.exit(u"Database {0} doesn't exist".format(db_config.database))
    else:
        logger.info(u"Database {0} exists".format(db_config.database))


def user_exists(db_config):
    assert is_windows() is False, "This command can only be run on Linux."
    sql = "SELECT 1 FROM pg_roles WHERE rolname='%s';" % db_config.username
    command, retcode, out, err = run_as_superuser(sql)

    if out.strip() != "1":
        sys.exit(u"User {0} doesn't exist".format(db_config.username))
    else:
        logger.info(u"User {0} exists".format(db_config.username))


def create(db_config):
    create_user(db_config)
    create_db(db_config)
    migrate(db_config)
    logger.info("User and database created successfully!")


def recreate(db_config):
    if env.get('RECREATE', None) != 'yes':
        print("Run with RECREATE environment variable set to yes", file=sys.stderr)
        print("  In Linux, try RECREATE=yes %s recreate" % sys.argv[0], file=sys.stderr)
        print("  In Windows/Powershell, try & { $env:RECREATE = 'yes'; %s recreate }" % sys.argv[0], file=sys.stderr)
        sys.exit(1)
    else:
        drop_db(db_config)
        logger.info("Database dropped (removed) succesfully!")
        drop_user(db_config)
        logger.info("User dropped (removed) successfully!")
        create(db_config)


def random_password(length=32):
    chars = string.ascii_uppercase + string.digits + string.ascii_lowercase
    password = ''
    for i in range(length):
        password += chars[ord(os.urandom(1)) % len(chars)]
    return password


def reset_admin_password(db_config):
    password = random_password(10)
    password_hash = sha512(password).hexdigest()
    sql = '''INSERT INTO identity.identity_password (identity_id, password, salt, updated) VALUES ((SELECT id FROM identity.identity WHERE email = '%s'), '%s', '', LOCALTIMESTAMP);''' % ('admin', password_hash)
    handle_error("resetting admin password", run_as_superuser(sql, db_config.database))
    print("'admin' password set to '" + password + "'")
    print("Please change this temporary password in the web user interface: 'Preferences' -> 'User Settings' -> 'Change password'")


def main():
    parser = argparse.ArgumentParser('rsw-db-tool',
                                     formatter_class=argparse.ArgumentDefaultsHelpFormatter)
    subparsers = parser.add_subparsers(title="Commands", description="Supported commands")

    for command in ['create-db', 'drop-db',
                    'create-user', 'migrate',
                    'migrate-c5-data',
                    'recreate', 'create',
                    'reset-admin-password',
                    'db-exists', 'user-exists']:
        cmd_parser = subparsers.add_parser(command, help=command.replace("-", " ").capitalize())
        cmd_parser.set_defaults(func=globals()[command.replace("-", "_")])

    args = parser.parse_args()
    args.func(read_config())


if __name__ == '__main__':
    main()
