package com.mavlushechka.a1qa.utils;

import com.mavlushechka.a1qa.models.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class Database {

    private static final String name = JsonParser.parseData("config", "database.name");
    private static final Connection connection;
    private static final User user = new User(JsonParser.parseData("config", "database.user.name"),
            JsonParser.parseData("config", "database.user.password"));


    static {
        try {
            Class.forName(JsonParser.parseData("config", "database.driver"));
            connection = DriverManager.getConnection(JsonParser.parseData("config", "database.url") + "/" + name, user.name(), user.password());
        } catch (SQLException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }


    private Database() {
    }

    public static List<String> getTests() {
        return executeQuery("SELECT project.name, test.name, TIMESTAMPDIFF(MICROSECOND, test.start_time, test.end_time) as min_working_time"
                + " FROM project INNER JOIN test ON project.id = test.project_id ORDER BY project.name, test.name;",
                Map.of("project.name", String.class,"test.name", String.class,"min_working_time", Long.class));
    }

    public static List<String> getTestsCount() {
        return executeQuery("SELECT project.name, (SELECT COUNT(*) FROM test WHERE test.project_id = project.id) AS `tests_count` FROM project;",
                Map.of("project.name", String.class, "tests_count", Long.class));
    }

    public static List<String> getTestsByLowerDate(LocalDate localDate) {
        return executeQuery("SELECT project.name, test.name, test.start_time FROM project JOIN test ON project.id = test.project_id"
                + " WHERE test.start_time >= '%s' ORDER BY project.name, test.name;".formatted(localDate),
                Map.of("project.name", String.class, "test.name", String.class, "start_time", LocalDate.class));
    }

    public static List<String> getBrowserTestsCount() {
        return executeQuery("SELECT test.browser, COUNT(*) as count FROM test WHERE test.browser = 'firefox'"
                + " UNION"
                + " SELECT test.browser, COUNT(*) as count FROM test WHERE test.browser = 'chrome';",
                Map.of("browser", String.class, "count", Long.class));
    }

    private static List<String> executeQuery(String sql, Map<String, Class<?>> expectedData) {
        ArrayList<String> foundData = new ArrayList<>();

        try (PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                StringBuilder dataStringRepresentationBuilder = new StringBuilder();

                dataStringRepresentationBuilder.append("{");
                for (String expectedDataKey : expectedData.keySet()) {
                    dataStringRepresentationBuilder
                            .append(expectedDataKey)
                            .append("=")
                            .append(resultSet.getObject(expectedDataKey, expectedData.get(expectedDataKey)))
                            .append(",");
                }
                dataStringRepresentationBuilder.deleteCharAt(dataStringRepresentationBuilder.toString().length() - 1);
                dataStringRepresentationBuilder.append("}");

                foundData.add(dataStringRepresentationBuilder.toString());
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }

        return foundData;
    }

}