package com.epam.grandhackathon.deployment.sphere.plugin.metadata.persistence.dao;

import javax.sql.DataSource;

import org.h2.jdbcx.JdbcConnectionPool;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;

public class DatabaseProvider {

    private static final DataSource DATA_SOURCE = JdbcConnectionPool
            .create("jdbc:h2:.data-deployment-sphere",
                    "username",
                    "password");
    private static final DBI DBI = new DBI(DATA_SOURCE);

    static {
        Handle handle = DBI.open();
        handle.execute("CREATE TABLE IF NOT EXISTS BUILDS (application_name varchar(255) NOT NULL, build_version varchar(255) NOT NULL, build_number long, built_at long, PRIMARY KEY(application_name, build_version))");

        handle.execute("CREATE TABLE IF NOT EXISTS DEPLOYMENTS (identity long primary key, applicationName varchar(255))");
        handle.execute("CREATE TABLE IF NOT EXISTS ENVIRONMENTS (identity long primary key, title varchar(255))");


//        handle.execute("INSERT INTO  ENVIRONMENTS (identity,title ) VALUES (1, 'ci')");
//        handle.execute("INSERT INTO  ENVIRONMENTS (identity,title ) VALUES (2, 'qa')");
//        handle.execute("INSERT INTO  ENVIRONMENTS (identity,title ) VALUES (3, 'st')");
//        handle.execute("INSERT INTO  ENVIRONMENTS (identity,title ) VALUES (4, 'pre-prod')");
//        handle.execute("INSERT INTO  ENVIRONMENTS (identity,title ) VALUES (5, 'prod')");

        handle.close();
    }

    public DBI database() {
        return DBI;
    }
}

@@ -81,38 +81,8 @@
 o;%0A%0A
-import javax.sql.DataSource;%0A%0A
 impo
@@ -179,16 +179,113 @@
 .Handle;
+%0Aimport org.skife.jdbi.v2.util.StringMapper;%0A%0Aimport javax.sql.DataSource;%0Aimport java.util.List;
 %0A%0Apublic
@@ -1067,19 +1067,217 @@
 5))%22);%0A%0A
-%0A//
+        List%3CString%3E rs = handle.createQuery(%22SELECT title FROM ENVIRONMENTS ORDER BY identity%22)%0A                .map(StringMapper.FIRST)%0A                .list();%0A%0A        if(!rs.contains(%22ci%22)) %7B%0A    
         
@@ -1352,26 +1352,71 @@
 1, 'ci')%22);%0A
-//
+        %7D%0A        if(!rs.contains(%22qa%22)) %7B%0A    
         hand
@@ -1487,26 +1487,71 @@
 2, 'qa')%22);%0A
-//
+        %7D%0A        if(!rs.contains(%22st%22)) %7B%0A    
         hand
@@ -1622,26 +1622,78 @@
 3, 'st')%22);%0A
-//
+        %7D%0A%0A        if(!rs.contains(%22pre-prod%22)) %7B%0A    
         hand
@@ -1778,10 +1778,57 @@
 %22);%0A
-//
+        %7D%0A        if(!rs.contains(%22prod%22)) %7B%0A    
     
@@ -1909,24 +1909,35 @@
  'prod')%22);%0A
+        %7D%0A%0A
 %0A        han

validate sql before insert