#!/usr/bin/env python
""" A tool to create initial database, users etc for DW """

# Run this script as a PostgreSQL superuser - the user postgres works fine normally.
import sys, os
import sc

db = ''
user = ''
passwd = ''
role = 'dwuser'

def get_params():
    global sc, db, user, passwd
    sc = sc.SystemConfiguration()

    db = sc.dwdatabase
    user = sc.dwuser
    passwd = sc.dwpassword
    
def create_user():
    print 'Creating user',user
    cmd = 'createuser --encrypted --pwprompt --no-adduser --no-createdb --createrole %s' % user
    os.system(cmd)
    print 'Creaing user',role
    cmd = 'createuser --encrypted --pwprompt --no-adduser --no-createdb --no-createrole %s' % role
    os.system(cmd)    

def create_db():
    print 'Creating database',db
    cmd = 'createdb --owner %s %s' % (user, db)
    os.system(cmd)
    cmd = 'createlang plpgsql %s' % db
    os.system(cmd)

def grant_privileges():
    
    print 'Granting privileges to %s. Connecting as %s.' % (user, os.environ.get('USER'))
    cmd = "psql --command 'grant all on database %s to %s with grant option;' %s" % (db, user, db)
    os.system(cmd)

def create_schemas_and_tables():

    print 'Creating schemas and tables in database %s. Connecting as %s' % (db, user)
    print 'Data staging area'
    cmd = 'psql --file DataStagingSchemaR20.sql %s %s' % (db, user)
    os.system(cmd)
    print 'Data warehouse'
    cmd = 'psql --file EIAODWR20Schema.sql %s %s' % (db, user)
    os.system(cmd)    
    print 'Materialized views'
    cmd = 'psql --file MatViewsR20.sql %s %s' % (db, user)
    os.system(cmd)    
    print 'Prefilling...'
    cmd = 'psql -f PrefillDataStaging_R20.sql %s %s' % (db, user)
    os.system(cmd)    
    print 'Creating stored procedures'
    cmd = 'psql --file StoredProcedures.sql %s %s' % (db, user)
    os.system(cmd)

def main():
    get_params()
    create_user()
    create_db()
    grant_privileges()
    create_schemas_and_tables()

if __name__ == "__main__":
    main()