/*
 * Copyright 2014 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.cloudfoundry.community.servicebroker.postgresql.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.UUID;

import org.cloudfoundry.community.servicebroker.model.ServiceInstance;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.google.common.collect.Lists;

@Component
public class Database {

    private static final Logger logger = LoggerFactory.getLogger(Database.class);
    private final Connection conn;

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

    public void createDatabaseForInstance(String instanceId, String serviceId, String planId, String organizationGuid, String spaceGuid) throws SQLException {
        checkValidUUID(instanceId);

        Statement createDatabase = this.conn.createStatement();
        Statement makePrivate = this.conn.createStatement();

        PreparedStatement insertService = this.conn.prepareStatement("INSERT INTO service (serviceinstanceid, servicedefinitionid, planid, organizationguid, spaceguid) VALUES (?, ?, ?, ?, ?)");
        insertService.setString(1, instanceId);
        insertService.setString(2, serviceId);
        insertService.setString(3, planId);
        insertService.setString(4, organizationGuid);
        insertService.setString(5, spaceGuid);

        try {
            createDatabase.execute("CREATE DATABASE \"" + instanceId + "\" ENCODING 'UTF8'");
            makePrivate.execute("REVOKE all on database \"" + instanceId + "\" from public");
            insertService.executeUpdate();
        } catch (SQLException e) {
            logger.warn(e.getMessage());
        } finally {
            createDatabase.close();
            makePrivate.close();
            insertService.close();
        }
    }

    public void deleteDatabase(String instanceId) throws SQLException {
        checkValidUUID(instanceId);

        Statement deleteDatabase = this.conn.createStatement();
        Statement takeOwnership = this.conn.createStatement();

        PreparedStatement getCurrentUser = this.conn.prepareStatement("SELECT current_user");

        PreparedStatement terminateConnections = this.conn.prepareStatement("SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = ? AND pid <> pg_backend_pid()");
        terminateConnections.setString(1, instanceId);

        PreparedStatement deleteService = this.conn.prepareStatement("DELETE FROM service WHERE serviceinstanceid=?");
        deleteService.setString(1, instanceId);

        try {
            String currentUser = "";
            ResultSet result = getCurrentUser.executeQuery();
            if(result.next()) {
                currentUser = result.getString("current_user");
            } else {
                logger.warn("Current user could not be found?");
            }
            terminateConnections.executeQuery();
            takeOwnership.execute("ALTER DATABASE \"" + instanceId + "\" OWNER TO \"" + currentUser + "\"");
            deleteDatabase.execute("DROP DATABASE \"" + instanceId + "\"");
            deleteService.executeUpdate();
        } catch (SQLException e) {
            logger.warn(e.getMessage());
        } finally {
            deleteDatabase.close();
            deleteService.close();
        }
    }

    public ServiceInstance findServiceInstance(String instanceId) throws SQLException {
        checkValidUUID(instanceId);
        PreparedStatement findDatabase = this.conn.prepareStatement("SELECT * FROM service WHERE serviceinstanceid = ?");
        findDatabase.setString(1, instanceId);

        ResultSet result = null;
        try {
            result = findDatabase.executeQuery();
            if (result.next()) {
                String serviceDefinitionId = result.getString("servicedefinitionid");
                String organizationGuid = result.getString("organizationguid");
                String planId = result.getString("planid");
                String spaceGuid = result.getString("spaceguid");

                return new ServiceInstance(instanceId, serviceDefinitionId, planId, organizationGuid, spaceGuid, null);
            }
        } catch (SQLException e) {
            logger.warn(e.getMessage());
        } finally {
            findDatabase.close();
            if (result != null)
                result.close();
        }
        return null;
    }

    public List<ServiceInstance> getAllServiceInstances() {
        List<ServiceInstance> serviceInstances = Lists.newArrayList();
        return serviceInstances;
    }

    private ServiceInstance createServiceInstance(String instanceId) {
        return null;
    }

    private void checkValidUUID(String instanceId) throws SQLException{
        UUID uuid = UUID.fromString(instanceId);

        if(!instanceId.equals(uuid.toString())) {
            throw new SQLException("UUID '" + instanceId + "' is not an UUID.");
        }
    }
}

@@ -5414,107 +5414,8 @@
  %7D%0A%0A
-    private ServiceInstance createServiceInstance(String instanceId) %7B%0A        return null;%0A    %7D%0A%0A
     

remove unused function