import argparse
import datetime
import os
import socket
import cx_Oracle

import paramiko
from colorama import Fore
from colorama import Style


class DatabaseBuilder:
    create_template = '''dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname {0} -sid {0} -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword {1} \
 -systemPassword {1} \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName {2} \
 -pdbAdminPassword {1} \
 -databaseType MULTIPURPOSE \
 -totalMemory {3} \
 -memoryMgmtType AUTO_SGA \
 -recoveryAreaDestination {7}
 -storageType FS \
 -useOMF true \
 -datafileDestination "{4}" \
 -redoLogFileSize {5} \
 -emConfiguration NONE \
 {6} \
 -ignorePreReqs \
 '''

    delete_template = '''dbca -silent -deleteDatabase \
-sourceDB {0} \
-sysDBAUserName sys \
-sysDBAPassword {1}'''

    @staticmethod
    def print_comments(text):
        print(Fore.RED + text + Style.RESET_ALL)

    @staticmethod
    def print_command(text):
        print(Fore.BLUE + text + Style.RESET_ALL)

    def run_command(self, command, envron_variables=None):
        self.print_command(command)
        ssh = paramiko.SSHClient()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ssh.connect(self.hostname, 22, username=self.remote_user, password=self.remote_password, )
        stdin, stdout, stderr = ssh.exec_command(command, environment=envron_variables)
        print ("".join(stdout.readlines()))
        ssh.close()

    def run_dba_command(self, command):
        try:
            connection = cx_Oracle.connect(mode=cx_Oracle.SYSDBA)
            cursor = connection.cursor()
            cursor.execute(command)
            cursor.close()
            connection.close()
        except cx_Oracle.DatabaseError as exc:
            error, = exc.args
            print('Failed to execute : {}'.format(command))
            print("Oracle-Error-Code:", error.code)
            print("Oracle-Error-Message:", error.message)

    def create_database(self, hostname, os_user, os_password, oracle_sid, oracle_base, sys_password, pdb_name, sga_size, location, redo_size, dont_create_listener):
        self.hostname = hostname
        self.remote_user = os_user
        self.remote_password = os_password

        start = datetime.datetime.now()
        self.print_comments('Creating database')
        create_listener = '-createListener LISTENER:1521'
        if dont_create_listener:
            create_listener = ''

        command = self.create_template.format(oracle_sid, sys_password, pdb_name, sga_size, location, redo_size, create_listener, os.path.join(oracle_base, 'fast_recovery_area'))
        self.run_command(command)
        if not dont_create_listener:
            self.print_comments("Starting listener")
            self.run_command('lsnrctl start')
        self.run_dba_command("alter system set db_create_file_dest = '{}'".format(location))
        end = datetime.datetime.now()
        self.print_comments('Completed creation of database in {}'.format(end - start))

    def delete_database(self, hostname, os_user, os_password, oracle_sid, sys_password):
        self.hostname = hostname
        self.remote_user = os_user
        self.remote_password = os_password

        start = datetime.datetime.now()
        self.print_comments('Deleting database {}'.format(oracle_sid))
        command = self.delete_template.format(oracle_sid, sys_password)
        self.run_command(command)
        end = datetime.datetime.now()
        self.print_comments('Deleted database {} in {}'.format(oracle_sid, end - start))




if __name__ == '__main__':
    oracle_sid, oracle_base = None, None
    try:
        oracle_sid = os.environ['ORACLE_SID']
    except:
        oracle_sid = 'orcl'

    try:
        oracle_base = os.environ['ORACLE_BASE']
    except:
        oracle_base = '/u01/app/oracle'

    local_hostname = socket.gethostname()

    parser = argparse.ArgumentParser(description='Create database...')
    parser.add_argument('-s', '--server', help='server to create database on', required=False, default=local_hostname)
    parser.add_argument('-ou', '--os_user', help='os user to create the database on', required=False, default='oracle')
    parser.add_argument('-op', '--os_password', help='os user to create the database on', required=False, default='oracle')
    parser.add_argument('-o', '--ora_sid', help='sid and global name of database (will be the same)', required=False, default=oracle_sid)
    parser.add_argument('-b', '--ora_base', help='oracle base location', required=False, default=oracle_base)
    parser.add_argument('-p', '--sys_password', help='sys password', required=False, default='welcome1')
    parser.add_argument('-n', '--pdb_name', help='PDB name', required=False, default='soe')
    parser.add_argument('-m', '--sga_size', help='memory to be allocated to database', required=False, default=1024)
    parser.add_argument('-l', '--location', help='location of data files', required=False, default=os.path.join(oracle_base, 'oradata'))
    parser.add_argument('-r', '--redo_size', help='redo log size', required=False, default=200)
    parser.add_argument('-nl', '--dont_create_listener', help='dont create a listener', action='store_true', required=False, default=False)
    parser.add_argument('-operation', '--operation', help='operation to perform', choices=['create', 'delete'], default='create', required=False)

    args = parser.parse_args()

    db = DatabaseBuilder()

    if args.operation == 'create':
        db.create_database(args.server, args.os_user, args.os_password, args.ora_sid, args.ora_base, args.sys_password, args.pdb_name, args.sga_size, args.location, args.redo_size, args.dont_create_listener)
    else:
        db.delete_database(args.server, args.os_user, args.os_password, args.ora_sid, args.sys_password)