package org.cloudfoundry.community.servicebroker.postgresql.service;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.security.SecureRandom;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;


@Component
public class Role {
    private static final Logger logger = LoggerFactory.getLogger(Role.class);
    private final Connection conn;

    @Autowired
    public Role(Connection conn) {
        this.conn = conn;
    }

    public void createRoleForInstance(String instanceId) throws SQLException {
        Utils.checkValidUUID(instanceId);

        Statement statement = this.conn.createStatement();

        try {
            statement.execute("CREATE ROLE \"" + instanceId + "\"");
            statement.execute("ALTER DATABASE \"" + instanceId + "\" OWNER TO \"" + instanceId + "\"");
        } catch (SQLException e) {
            logger.warn(e.getMessage());
        } finally {
            statement.close();
        }
    }

    public void deleteRole(String instanceId) throws SQLException {
        Utils.checkValidUUID(instanceId);

        Statement statement = this.conn.createStatement();

        try {
            statement.execute("DROP ROLE \"" + instanceId + "\"");
        } catch (SQLException e) {
            logger.warn(e.getMessage());
        } finally {
            statement.close();
        }
    }

    public String bindRoleToDatabase(String dbInstanceId) throws SQLException {
        Utils.checkValidUUID(dbInstanceId);

        SecureRandom random = new SecureRandom();
        String passwd = new BigInteger(130, random).toString(32);

        Statement statement = this.conn.createStatement();
        try {
            statement.execute("ALTER ROLE \"" + dbInstanceId + "\" LOGIN password '" + passwd + "'");
        } catch (SQLException e) {
            logger.warn(e.getMessage());
        } finally {
            statement.close();
        }
        return passwd;
    }

    public void unBindRoleFromDatabase(String dbInstanceId) throws SQLException{
        Utils.checkValidUUID(dbInstanceId);

        Statement statement = this.conn.createStatement();

        try {
            statement.execute("ALTER ROLE \"" + dbInstanceId + "\" NOLOGIN");
        } catch (SQLException e) {
            logger.warn(e.getMessage());
        } finally {
            statement.close();
        }
    }
}

@@ -112,232 +112,57 @@
 sql.
-Connection;%0Aimport java.sql.SQLException;%0Aimport java.sql.Statement;%0Aimport java.security.SecureRandom;%0A%0Aimport org.slf4j.Logger;%0Aimport org.slf4j.LoggerFactory;%0Aimport org.springframework.beans.factory.annotation.Autowired;
+SQLException;%0Aimport java.security.SecureRandom;%0A
 %0Aimp
@@ -208,17 +208,16 @@
 onent;%0A%0A
-%0A
 @Compone
@@ -243,205 +243,8 @@
 e %7B%0A
-    private static final Logger logger = LoggerFactory.getLogger(Role.class);%0A    private final Connection conn;%0A%0A    @Autowired%0A    public Role(Connection conn) %7B%0A        this.conn = conn;%0A    %7D%0A%0A
     
@@ -356,33 +356,32 @@
 nstanceId);%0A
-%0A
         
 Statement st
@@ -372,101 +372,25 @@
     
-Statement statement = this.conn.createStatement();%0A%0A        try %7B%0A            statement.execu
+Utils.executeUpda
 te(%22
@@ -435,35 +435,33 @@
         
-    statement.execu
+Utils.executeUpda
 te(%22ALTE
@@ -533,145 +533,8 @@
 %22);%0A
-        %7D catch (SQLException e) %7B%0A            logger.warn(e.getMessage());%0A        %7D finally %7B%0A            statement.close();%0A        %7D%0A
     
@@ -642,33 +642,32 @@
 nstanceId);%0A
-%0A
         
 Statement st
@@ -658,838 +658,413 @@
     
-Statement statement = this.conn.createStatement();%0A%0A        try %7B%0A            statement.execute(%22DROP ROLE %5C%22%22 + instanceId + %22%5C%22%22);%0A        %7D catch (SQLException e) %7B%0A            logger.warn(e.getMessage());%0A        %7D finally %7B%0A            statement.close();%0A        %7D%0A    %7D%0A%0A    public String bindRoleToDatabase(String dbInstanceId) throws SQLException %7B%0A        Utils.checkValidUUID(dbInstanceId);%0A%0A        SecureRandom random = new SecureRandom();%0A        String passwd = new BigInteger(130, random).toString(32);%0A%0A        Statement statement = this.conn.createStatement();%0A        try %7B%0A            statement.execute(%22ALTER ROLE %5C%22%22 + dbInstanceId + %22%5C%22 LOGIN password '%22 + passwd + %22'%22);%0A        %7D catch (SQLException e) %7B%0A            logger.warn(e.getMessage());%0A        %7D finally %7B%0A            statement.close();%0A        %7D
+Utils.executeUpdate(%22DROP ROLE %5C%22%22 + instanceId + %22%5C%22%22);%0A    %7D%0A%0A    public String bindRoleToDatabase(String dbInstanceId) throws SQLException %7B%0A        Utils.checkValidUUID(dbInstanceId);%0A%0A        SecureRandom random = new SecureRandom();%0A        String passwd = new BigInteger(130, random).toString(32);%0A%0A        Utils.executeUpdate(%22ALTER ROLE %5C%22%22 + dbInstanceId + %22%5C%22 LOGIN password '%22 + passwd + %22'%22);
 %0A   
@@ -1215,25 +1215,24 @@
 nceId);%0A
-%0A
         
 Statemen
@@ -1227,103 +1227,21 @@
     
-Statement statement = this.conn.createStatement();%0A%0A        try %7B%0A            statement
+Utils
 .execute
 (%22AL
@@ -1236,16 +1236,22 @@
 .execute
+Update
 (%22ALTER 
@@ -1295,149 +1295,11 @@
 %22);%0A
-        %7D catch (SQLException e) %7B%0A            logger.warn(e.getMessage());%0A        %7D finally %7B%0A            statement.close();%0A        %7D%0A
     %7D%0A%7D
-%0A

use Statement logic from Utils