// ==============================================================================
//
// Copyright (C) 2006-2017 Talend Inc. - www.talend.com
//
// This source code is available under agreement available at
// %InstallDIR%\features\org.talend.rcp.branding.%PRODUCTNAME%\%PRODUCTNAME%license.txt
//
// You should have received a copy of the agreement
// along with this program; if not, write to Talend SA
// 9 rue Pages 92150 Suresnes, France
//
// ==============================================================================

package org.talend.components.service.rest;

import static io.restassured.RestAssured.given;
import static java.util.Collections.singletonList;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.springframework.boot.test.context.SpringBootTest.WebEnvironment.RANDOM_PORT;
import static org.springframework.http.MediaType.APPLICATION_JSON_UTF8_VALUE;

import io.restassured.RestAssured;
import io.restassured.response.Response;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Stream;

import org.apache.avro.Schema;
import org.apache.avro.generic.GenericDatumReader;
import org.apache.avro.generic.GenericRecord;
import org.apache.avro.io.Decoder;
import org.apache.avro.io.DecoderFactory;
import org.apache.commons.io.IOUtils;
import org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.context.embedded.LocalServerPort;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
import org.springframework.test.context.TestPropertySource;
import org.springframework.test.context.junit4.SpringRunner;
import org.talend.components.jdbc.dataset.JDBCDatasetProperties;
import org.talend.components.jdbc.datastore.JDBCDatastoreProperties;
import org.talend.components.service.rest.dto.DefinitionDTO;
import org.talend.components.service.rest.dto.SerPropertiesDto;
import org.talend.components.service.rest.dto.UiSpecsPropertiesDto;
import org.talend.components.service.rest.impl.ApiError;
import org.talend.daikon.properties.test.PropertiesTestUtils;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.fasterxml.jackson.databind.type.TypeFactory;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class, webEnvironment = RANDOM_PORT)
@TestPropertySource(properties = { "server.contextPath=" })
public class JdbcComponentTestIT {
    static {
        if (System.getProperty("sun.boot.class.path") == null) {
            System.setProperty("sun.boot.class.path", System.getProperty("java.class.path"));
        }
    }

    private static final Logger log = LoggerFactory.getLogger(JdbcComponentTestIT.class);

    public static final String DATA_STORE_DEFINITION_NAME = "JDBCDatastore";

    @LocalServerPort
    private int localServerPort;

    protected EmbeddedDatabase db;

    protected String dbUrl;

    private ObjectMapper mapper = new ObjectMapper();

    protected String getVersionPrefix() {
        return ServiceConstants.V0;
    }

    @BeforeClass
    public static void registerPaxUrlMavenHandler() {
        PropertiesTestUtils.setupPaxUrlFromMavenLaunch();
    }

    @Before
    public void setUp() throws Exception {
        db = new EmbeddedDatabaseBuilder().generateUniqueName(true).setType(EmbeddedDatabaseType.DERBY).setName("testdb")
                .setScriptEncoding("UTF-8").addScript("/org/talend/components/service/rest/schema.sql")
                .addScript("/org/talend/components/service/rest/data_users.sql").build();
        // addresss: Starting embedded database:
        // url='jdbc:derby:memory:2dc86c66-5d3a-48fd-b903-56aa27d20e3b;create=true',
        // username='sa'
        try (Connection connection = db.getConnection()) {
            dbUrl = connection.getMetaData().getURL();
        }

        RestAssured.port = localServerPort;
    }

    @After
    public void tearDown() {
        db.shutdown();
    }

    // TODO the test will fail if the writer is case sensitive, please see org.talend.components.jdbc.runtime.type.RowWriter for
    // DI, we can make the test success by change jdbc_component_write_properties_on_DI.json, but not sure should do it
    @Test
    public void setDatasetData_DiRuntime() throws Exception {
        // given
        String payload = IOUtils.toString(getClass().getResourceAsStream("jdbc_component_write_properties_on_DI.json"))
                .replace("{jdbc_url}", dbUrl);

        // when
        given().body(payload).contentType(APPLICATION_JSON_UTF8_VALUE).put(getVersionPrefix() + "/runtimes/data")
                .then().statusCode(200).log().ifError();

        // then
        Statement statement = db.getConnection().createStatement();
        ResultSet countRS = statement.executeQuery("SELECT COUNT(*) AS count FROM users");
        countRS.next();
        assertEquals(101, countRS.getInt("count"));

        ResultSet resultSet = statement.executeQuery("SELECT * FROM users WHERE email='[email protected]'");

        while (resultSet.next()) {
            assertEquals("1", resultSet.getString("id"));
            assertEquals("David", resultSet.getString("first_name"));
            assertEquals("Bowie", resultSet.getString("last_name"));
            assertEquals("[email protected]", resultSet.getString("email"));
            assertEquals("male", resultSet.getString("gender"));
            assertEquals("127.0.0.1", resultSet.getString("ip_address"));
        }
    }

    @Test
    public void testGetDataBinary() throws java.io.IOException {
        // given
        UiSpecsPropertiesDto propertiesDto = new UiSpecsPropertiesDto();
        propertiesDto.setProperties(getFileAsObjectNode("jdbc_data_set_properties_with_schema.json"));
        propertiesDto.setDependencies(singletonList(getJdbcDataStoreProperties()));

        String dataSetDefinitionName = "JDBCDataset";

        // when
        Response schemaResponse = given().body(propertiesDto).contentType(APPLICATION_JSON_UTF8_VALUE) //
                                         .accept(APPLICATION_JSON_UTF8_VALUE) //
                                         .post(getVersionPrefix() + "/runtimes/schema");
        schemaResponse.then().statusCode(200).log().ifError();

        Schema schema = new Schema.Parser().parse(schemaResponse.asInputStream());

        Response response = given().body(propertiesDto).contentType(APPLICATION_JSON_UTF8_VALUE) //
                .accept(RuntimesController.AVRO_BINARY_MIME_TYPE_OFFICIAL_INVALID).post(getVersionPrefix() + "/runtimes/data"); //
        response.then().statusCode(200).log().ifError();

        // then
        GenericDatumReader<GenericRecord> reader = new GenericDatumReader<>(schema);
        DecoderFactory decoderFactory = DecoderFactory.get();
        Decoder decoder = decoderFactory.binaryDecoder(response.asInputStream(), null);
        assertRecordsEqualsToTestValues(reader, decoder);
    }

    @Test
    public void testGetData() throws java.io.IOException {
        // given
        UiSpecsPropertiesDto propertiesDto = new UiSpecsPropertiesDto();
        propertiesDto.setProperties(getFileAsObjectNode("jdbc_data_set_properties_with_schema.json"));
        propertiesDto.setDependencies(singletonList(getJdbcDataStoreProperties()));

        String dataSetDefinitionName = "JDBCDataset";

        Response schemaResponse = given().body(propertiesDto).contentType(APPLICATION_JSON_UTF8_VALUE) //
                .accept(APPLICATION_JSON_UTF8_VALUE).post(getVersionPrefix() + "/runtimes/schema");
        schemaResponse.then().statusCode(200).log().ifError();

        Schema schema = new Schema.Parser().parse(schemaResponse.asInputStream());

        // when
        Response response = given().body(propertiesDto).contentType(APPLICATION_JSON_UTF8_VALUE) //
                .accept(RuntimesController.AVRO_JSON_MIME_TYPE_OFFICIAL_INVALID)
                .post(getVersionPrefix() + "/runtimes/data");
        response.then().statusCode(200).log().ifError();

        // then
        GenericDatumReader<GenericRecord> reader = new GenericDatumReader<>(schema);
        DecoderFactory decoderFactory = DecoderFactory.get();
        Decoder decoder = decoderFactory.jsonDecoder(schema, response.asInputStream());

        assertRecordsEqualsToTestValues(reader, decoder);
    }

    private void assertRecordsEqualsToTestValues(GenericDatumReader<GenericRecord> reader, Decoder decoder) throws IOException {
        try (Stream<String[]> insertedValues = getInsertedValues()) {
            insertedValues.forEach((value) -> {
                try {
                    GenericRecord record = reader.read(null, decoder);
                    assertRecordEquals(value, record);
                } catch (IOException e) {
                    // When reading is done...
                }
            });
        }
    }

    private void assertRecordEquals(String[] expected, GenericRecord record) {
        String errorMessage = "Record " + record + " is incorrect. Expected " + Arrays.toString(expected);
        assertEquals(errorMessage, expected[0], record.get(0).toString());
        assertEquals(errorMessage, expected[1], record.get(1).toString());
        assertEquals(errorMessage, expected[2], record.get(2).toString());
        assertEquals(errorMessage, expected[3], record.get(3).toString());
        assertEquals(errorMessage, expected[4], record.get(4).toString());
        assertEquals(errorMessage, expected[5], record.get(5).toString());
    }

    /**
     * Quick and dirty to parse the test SQL for the inserted records. The stream must be closed to close the source.
     **/
    private Stream<String[]> getInsertedValues() throws IOException {
        BufferedReader bufferedReader = new BufferedReader(
                new InputStreamReader(getClass().getResourceAsStream("data_users.sql")));
        return bufferedReader.lines().map(in -> in.substring(in.indexOf("VALUES (") + "VALUES (".length(), in.lastIndexOf("');")))
                .map(in -> in.split("'?\\s*,\\s*'?"));
    }

    @Test
    public void testGetSchema() throws java.io.IOException {
        // given
        UiSpecsPropertiesDto propertiesDto = new UiSpecsPropertiesDto();
        propertiesDto.setProperties(getFileAsObjectNode("jdbc_data_set_properties_no_schema.json"));
        propertiesDto.setDependencies(singletonList(getJdbcDataStoreProperties()));
        String dataSetDefinitionName = "JDBCDataset";

        // when
        Response response = given().body(propertiesDto).contentType(APPLICATION_JSON_UTF8_VALUE) //
                .accept(APPLICATION_JSON_UTF8_VALUE).post(getVersionPrefix() + "/runtimes/schema");
        response.then().statusCode(200).log().ifError();

        // then
        ObjectNode result = getResponseAsObjectNode(response);
        ObjectNode expected = getFileAsObjectNode("jdbc_data_set_schema.json");
        assertEquals(expected, result);
    }

    @Test
    public void testGetSchema_wrongSql() throws java.io.IOException {
        // given

        UiSpecsPropertiesDto datasetConnectionInfo = new UiSpecsPropertiesDto();
        datasetConnectionInfo.setProperties(mapper.readValue(
                getClass().getResourceAsStream("jdbc_data_set_properties_no_schema_wrong_table_name.json"), ObjectNode.class));
        datasetConnectionInfo.setDependencies(singletonList(getJdbcDataStoreProperties()));

        // when
        final Response responseApiError = given().body(datasetConnectionInfo)
                                     .contentType(APPLICATION_JSON_UTF8_VALUE) //
                                     .accept(APPLICATION_JSON_UTF8_VALUE)
                                     .post(getVersionPrefix() + "/runtimes/schema");
        responseApiError.then().statusCode(400).log().ifValidationFails();
        ApiError response = responseApiError.as(ApiError.class);

        // then
        assertEquals("TCOMP_JDBC_SQL_SYNTAX_ERROR", response.getCode());
        assertEquals("Table/View 'TOTO' does not exist.", response.getMessage());
    }

    @Test
    public void getJdbcDataSetProperties() throws java.io.IOException {
        // given
        UiSpecsPropertiesDto properties = new UiSpecsPropertiesDto();
        properties.setProperties(getJdbcDataStoreProperties());

        // when
        Response response = given().body(properties).contentType(ServiceConstants.UI_SPEC_CONTENT_TYPE) //
                .accept(ServiceConstants.UI_SPEC_CONTENT_TYPE).post(getVersionPrefix() + "/properties/dataset");
        response.then().statusCode(200).log().ifError();

        // then
        ObjectNode dataSetProperties = mapper.readerFor(ObjectNode.class).readValue(response.asInputStream());
        assertNotNull(dataSetProperties);
    }

    @Test
    public void validateDataStoreConnection() throws java.io.IOException {
        // given
        UiSpecsPropertiesDto properties = new UiSpecsPropertiesDto();
        properties.setProperties(getJdbcDataStoreProperties());

        // when
        given().body(properties).contentType(APPLICATION_JSON_UTF8_VALUE) //
                .accept(APPLICATION_JSON_UTF8_VALUE) //
                .post(getVersionPrefix() + "/runtimes/check")//
                .then().statusCode(200).log().ifError();
    }

    private ObjectNode getJdbcDataStoreProperties() throws java.io.IOException {
        return mapper.readValue(
                IOUtils.toString(getClass().getResourceAsStream("jdbc_data_store_properties.json")).replace("JDBC_URL", dbUrl),
                ObjectNode.class);
    }

    @Test
    public void testTrigger() throws java.io.IOException {
        // given
        String triggerName = "after";
        String triggerProperty = "dbTypes";

        UiSpecsPropertiesDto properties = new UiSpecsPropertiesDto();
        properties.setProperties(getFileAsObjectNode("jdbc_data_store_properties.json"));

        // when
        Response response = given().body(properties).contentType(ServiceConstants.UI_SPEC_CONTENT_TYPE) //
                .accept(ServiceConstants.UI_SPEC_CONTENT_TYPE) //
                .post(getVersionPrefix() + "/properties/trigger/{trigger}/{property}", triggerName, triggerProperty);
        response.then().statusCode(200).log().ifError();

        ObjectNode jdbcPropertiesAfterTrigger = getResponseAsObjectNode(response);

        // then
        // should resemble jdbc_data_store_form_after_trigger.json
        assertNotNull(jdbcPropertiesAfterTrigger.get("jsonSchema"));
        assertNotNull(jdbcPropertiesAfterTrigger.get("properties"));
        assertNotNull(jdbcPropertiesAfterTrigger.get("uiSchema"));
        assertEquals("JDBCDatastore", jdbcPropertiesAfterTrigger.get("properties").get("@definitionName").textValue());
    }

    @Test
    public void getJdbcProperties() throws java.io.IOException {
        // when
        Response response = given().accept(ServiceConstants.UI_SPEC_CONTENT_TYPE) //
                                   .get(getVersionPrefix() + "/properties/{definitionName}", DATA_STORE_DEFINITION_NAME);
        response.then().statusCode(200).log().ifError();

        // then
        ObjectNode jdbcProperties = mapper.readerFor(ObjectNode.class).readValue(response.asInputStream());
        // should resemble jdbc_data_store_form.json
        assertNotNull(jdbcProperties.get("jsonSchema"));
        assertNotNull(jdbcProperties.get("properties"));
        assertNotNull(jdbcProperties.get("uiSchema"));
        assertEquals("JDBCDatastore", jdbcProperties.get("properties").get("@definitionName").textValue());
    }

    @Test
    public void initializeJDBCDatastoreProperties() throws java.io.IOException {
        // given
        SerPropertiesDto propDto = new SerPropertiesDto();
        propDto.setProperties(new JDBCDatastoreProperties("").init().toSerialized());

        // when
        Response response = given().body(propDto).contentType(ServiceConstants.JSONIO_CONTENT_TYPE) //
                .accept(ServiceConstants.UI_SPEC_CONTENT_TYPE) //
                .post(getVersionPrefix() + "/properties/uispec");
        response.then().statusCode(200).log().ifError();

        // then
        ObjectNode jdbcProperties = mapper.readerFor(ObjectNode.class).readValue(response.asInputStream());
        // should resemble jdbc_data_store_form.json
        assertNotNull(jdbcProperties.get("jsonSchema"));
        assertNotNull(jdbcProperties.get("properties"));
        assertNotNull(jdbcProperties.get("uiSchema"));
        assertEquals("JDBCDatastore", jdbcProperties.get("properties").get("@definitionName").textValue());
    }

    @Test
    public void initializeJDBCDatasetProperties() throws java.io.IOException {
        // given
        SerPropertiesDto propDto = new SerPropertiesDto();
        propDto.setProperties(new JDBCDatasetProperties("").init().toSerialized());
        propDto.setDependencies(singletonList(new JDBCDatastoreProperties("").init().toSerialized()));

        // when
        Response response = given().body(propDto).contentType(ServiceConstants.JSONIO_CONTENT_TYPE) //
                .accept(ServiceConstants.UI_SPEC_CONTENT_TYPE) //
                .post(getVersionPrefix() + "/properties/uispec");
        response.then().statusCode(200).log().ifError();

        // then
        ObjectNode jdbcProperties = mapper.readerFor(ObjectNode.class).readValue(response.asInputStream());
        assertNotNull(jdbcProperties.get("jsonSchema"));
        assertNotNull(jdbcProperties.get("properties"));
        assertNotNull(jdbcProperties.get("uiSchema"));
        assertEquals("JDBCDataset", jdbcProperties.get("properties").get("@definitionName").textValue());
    }

    @Test
    public void getJdbcDefinition() throws java.io.IOException {
        // when
        Response response = given()
                .accept(APPLICATION_JSON_UTF8_VALUE).get(getVersionPrefix() + "/definitions/DATA_STORE");
        response.then()
                .statusCode(200).log().ifError();

        // then
        List<DefinitionDTO> definitions = mapper
                .readerFor(TypeFactory.defaultInstance().constructCollectionType(List.class, DefinitionDTO.class))
                .readValue(response.asInputStream());

        DefinitionDTO jdbcDef = null;

        for (DefinitionDTO definition : definitions) {
            if (DATA_STORE_DEFINITION_NAME.equals(definition.getName())) {
                jdbcDef = definition;
                break;
            }
        }
        assertNotNull(jdbcDef);
    }

    private ObjectNode getResponseAsObjectNode(Response response) throws java.io.IOException {
        return mapper.readerFor(ObjectNode.class).readValue(response.asInputStream());
    }

    private ObjectNode getFileAsObjectNode(String file) throws java.io.IOException {
        return mapper.readerFor(ObjectNode.class).readValue(getClass().getResourceAsStream(file));
    }

}